Skip to main content

ClickHouse

ClickHouse is a high-performance, column-oriented SQL database management system designed for online analytical processing (OLAP). It excels at running complex analytical queries over large datasets with sub-second response times. ClickHouse is widely used for log and event data analysis, real-time dashboards, time-series data, business intelligence, and any workload that involves aggregating large volumes of data.

Authentication Types​

ClickHouse supports API Key authentication via HTTP Basic Auth:

  • API Key (Basic Auth) - Authenticate using a ClickHouse username and password sent as HTTP Basic Authentication
    • Pros: Simple to set up, works with all ClickHouse deployments (self-hosted and Cloud)
    • Cons: Requires creating a ClickHouse user with appropriate permissions

General Settings​

Before using the connector, you need to configure:

  • ClickHouse Instance URL - The base URL of your ClickHouse HTTP interface. This URL must be reachable from the Webrix server, not just your local machine.
    • Examples:
      • ClickHouse Cloud: https://your-instance.clickhouse.cloud:8443
      • Self-managed: https://clickhouse.yourcompany.com:8123
    • Important: Include the protocol (http/https) and port. The default HTTP port is 8123, and HTTPS is 8443.
warning

Do not use localhost or 127.0.0.1 as the instance URL. The URL must be a hostname or IP address that the Webrix server can reach over the network. For on-premise ClickHouse behind a firewall, ensure the ClickHouse HTTP port is accessible from the Webrix server (e.g. via VPN or network peering).

tip

For ClickHouse Cloud, you can find your connection details in the Cloud Console under your service's "Connect" tab. Use the HTTPS endpoint with port 8443.

Setting up API Key Authentication​

Creating a ClickHouse User​

  1. Connect to your ClickHouse instance using a client with admin privileges

  2. Create a new user with a password:

    CREATE USER webrix_user IDENTIFIED BY 'your_secure_password';

  3. Grant the necessary permissions. For read-only analytics access:

    GRANT SELECT ON *.* TO webrix_user;
    GRANT SHOW TABLES ON *.* TO webrix_user;
    GRANT SHOW COLUMNS ON *.* TO webrix_user;
    GRANT SHOW DATABASES ON *.* TO webrix_user;

    For full access (read, write, and DDL):

    GRANT ALL ON *.* TO webrix_user;

    You can also restrict access to specific databases:

    GRANT SELECT ON my_database.* TO webrix_user;

  4. Verify the user can connect:

    curl 'https://your-clickhouse-host:8123/?query=SELECT%201' --user webrix_user:your_secure_password

Configuring in Webrix​

  1. In Webrix connector settings, select API Key authentication
  2. Enter the ClickHouse username and password you created
  3. Set the ClickHouse Instance URL in General Settings (e.g., http://localhost:8123)
  4. Click Save Changes

Using ClickHouse Cloud API Keys​

For ClickHouse Cloud, you can also use the Cloud API credentials:

  1. Go to the ClickHouse Cloud Console
  2. Navigate to your service
  3. Click Connect and note the hostname and credentials
  4. Use these credentials in the Webrix connector settings

For full connector functionality, the user should have:

Minimum (read-only analytics):

  • SELECT on relevant databases/tables
  • SHOW TABLES, SHOW COLUMNS, SHOW DATABASES
  • Access to system.databases, system.tables, system.columns, system.parts, system.processes, system.query_log, system.disks

Full access:

  • All of the above, plus:
  • INSERT for data ingestion
  • CREATE, DROP, ALTER for DDL operations
  • KILL QUERY for cancelling queries
  • OPTIMIZE for table maintenance

Common Use Cases​

1. Log and Event Analysis​

Query large volumes of log data with aggregations, filtering by time ranges, grouping by dimensions, and computing percentiles or rates.

2. Real-Time Dashboards​

Power dashboards with sub-second analytical queries over billions of rows. ClickHouse's columnar storage and vectorized execution make this possible.

3. Time-Series Analytics​

Analyze time-series data with ClickHouse's built-in time functions, date partitioning, and efficient compression for time-ordered data.

4. Schema Exploration​

Discover databases, tables, and columns across your ClickHouse deployment. Understand table engines, partition schemes, and storage characteristics.

5. Performance Monitoring​

Monitor running queries, review query logs, check disk usage, and inspect table part health to keep your ClickHouse instance running smoothly.

Troubleshooting​

Connection Issues​

Symptom: "Connection refused" or timeout errors

Cause: Incorrect instance URL, network issues, or ClickHouse not running

Solution:

  1. Verify ClickHouse is running from the server: curl https://your-clickhouse-host:8123/ping
  2. Check the URL includes the correct protocol and port
  3. For ClickHouse Cloud, ensure you're using HTTPS with port 8443
  4. Verify the ClickHouse host is reachable from the Webrix server (not just your local machine)
  5. Check firewall rules and network connectivity

Authentication Errors​

Symptom: "401 Unauthorized" or "Authentication failed" errors

Cause: Invalid username/password or user doesn't exist

Solution:

  1. Verify the username and password are correct
  2. Test the connection manually from the Webrix server: 
    curl 'https://your-clickhouse-host:8123/?query=SELECT%201' --user your_user:your_password
  3. Check if the user exists: SELECT name FROM system.users
  4. Recreate the user if needed

Permission Errors​

Symptom: "Not enough privileges" or "ACCESS_DENIED" errors

Cause: The ClickHouse user lacks required permissions

Solution:

  1. Review the user's grants: SHOW GRANTS FOR webrix_user
  2. Add missing permissions (see "Recommended Permissions" above)
  3. For system table access, grant SELECT on the system database: 
    GRANT SELECT ON system.* TO webrix_user;

Query Timeout​

Symptom: Queries taking too long or timing out

Cause: Complex queries, large data scans, or under-resourced server

Solution:

  1. Use the Explain Query tool to understand the query plan
  2. Add WHERE filters to reduce data scanned
  3. Check if the table has appropriate ORDER BY / partition keys
  4. Use Get Running Queries to see if other queries are competing for resources
  5. Consider increasing ClickHouse server timeout settings

SSL/TLS Issues​

Symptom: "SSL certificate problem" or connection errors on HTTPS

Cause: Self-signed certificates or TLS configuration issues

Solution:

  1. For ClickHouse Cloud, HTTPS should work automatically
  2. For self-managed with self-signed certificates, configure the certificate in your ClickHouse server settings
  3. Verify the port matches the protocol (8123 for HTTP, 8443 for HTTPS)

Additional Resources​