Login
Start Trial
Request Demo

ClickHouse Analytics & Reporting - Knowi Integration

Knowi enables real-time analytics, visualization, and reporting automation from ClickHouse along with 30+ other structured and unstructured data sources. Connect to ClickHouse Cloud, Altinity Cloud, or self-hosted clusters and start building dashboards in minutes.

Overview

  1. Connect to your ClickHouse database using one of the following options:

    a. Through our UI to connect directly, if the database is accessible from the cloud.

    b. Using our Cloud9Agent. This can securely pull data inside your network.

  2. Query ClickHouse data using SQL, the visual query builder, or AI-powered natural language queries.

  3. Join ClickHouse data with MongoDB, PostgreSQL, REST APIs, and 30+ other sources without ETL.

  4. Visualize and automate your reporting instantly.

UI Based Approach

Connecting

  1. Log in to Knowi and select Queries from the left sidebar.

  2. Click on New Datasource + button and select ClickHouse from the list of datasources under Data Warehouses.

  3. Configure the following connection details:

    a. Datasource Name: Enter a name for your datasource (e.g., "Production ClickHouse")
    b. Host Name: Enter the ClickHouse server hostname or IP address
    c. Port: Enter the HTTP port (default: 8123) or HTTPS port (default: 8443)
    d. Database Name: Enter the name of the ClickHouse database
    e. User ID: Enter the username to connect
    f. Password: Enter the password
    g. Database Properties: Additional connection properties as URL parameters. Common options:

    • ssl=true — Enable SSL/TLS connection (required for ClickHouse Cloud)
    • sslmode=strict — Enforce certificate validation
    • compress=1 — Enable compression for faster data transfer
    • socket_timeout=300000 — Socket timeout in milliseconds
    • connect_timeout=10000 — Connection timeout in milliseconds

  4. Click on the Test Connection button to verify connectivity.

    Note 1: The connection can be tested only if established via direct connectivity or an SSH tunnel. For more information, see Connectivity & Datasources.

    Note 2: For databases behind a firewall, check Internal Datasource to assign it to your Cloud9Agent. The agent (running inside your network) will synchronize automatically.

  5. Click on Save and start querying.

Connecting to ClickHouse Cloud

For ClickHouse Cloud, use these settings:

Query

Set up queries using a visual builder, query editor, or AI assistant.

Visual Builder

After connecting to the ClickHouse datasource, Knowi will pull out a list of tables along with field samples.

Step 1: Generate queries through our visual builder in a no-code environment by either dragging and dropping fields or making your selections through the drop-down.

Tip: You can also write queries directly in the Query Editor, a versatile text editor that supports ClickHouse SQL, Cloud9QL, and more.

Step 2: Define data execution strategy:

Step 3: Click on the Preview button to analyze the results and fine-tune the output.

The result of your query is called a Dataset. After reviewing the results, name your dataset and click Create & Run.

Query Editor

A versatile text editor for writing ClickHouse SQL directly. Supports ClickHouse-specific syntax including:

AI Assistant

The AI assistant automatically generates ClickHouse SQL from plain English statements.

Step 1: Select Generate Query from AI Assistant dropdown and enter your request in plain English.

Example: Show me daily revenue by product category for the last 30 days, ordered by revenue descending

The AI will generate optimized ClickHouse SQL: ```sql SELECT toDate(event_time) AS day, product_category, sum(revenue) AS total_revenue FROM events WHERE event_time >= today() - 30 GROUP BY day, product_category ORDER BY total_revenue DESC ```

Note: OpenAI must be enabled by the admin before using the AI Query Generator.

{Account Settings > Customer Settings > OpenAI Integration}

Additional AI Assistant features:

Cross-Source Joins with ClickHouse

Knowi can join ClickHouse data with any other connected datasource. Common patterns:

To set up a cross-source join, see Joining Across Multiple Databases.

Cloud9QL Transformations

After querying ClickHouse, you can apply Cloud9QL transformations to further process the results. Common use cases:

Supported ClickHouse Data Types

ClickHouse Type Knowi Mapping
UInt8, UInt16, UInt32, Int8, Int16, Int32Integer
UInt64, Int64Long
Int128, Int256, UInt128, UInt256BigDecimal
Float32, Float64Double
Decimal, Decimal32, Decimal64, Decimal128, Decimal256Double
String, FixedStringString
Date, Date32, DateTime, DateTime64Date
UUIDString
IPv4, IPv6String (IP address format)
Array(T)List
NestedNested object
Map(K, V)Map
Tuple(T1, T2, ...)List
Enum8, Enum16String
BoolBoolean

Cloud9Agent Configuration

As an alternative to UI-based connectivity, you can use Cloud9Agent inside your network to pull from ClickHouse securely. See Cloud9Agent to download your agent.

Highlights:

Datasource Configuration:

Parameter Comments
name Unique Datasource Name
datasource Set value to clickhouse
url Host and port to connect. Example: clickhouse-server:8123/analytics_db
userId User ID to connect
password Password to connect

Query Configuration:

Query Config Params Comments
entityName Dataset Name Identifier
identifier A unique identifier for the dataset. Either identifier or entityName must be specified.
dsName Name of the datasource configured in the datasource_XXX.json file. Required.
queryStr ClickHouse SQL query to execute. Required.
frequencyType One of minutes, hours, days, weeks, months. If not specified, treated as a one-time query.
frequency Frequency value. For example, 10 with minutes = every 10 minutes.
startTime Optional. When to run the first execution. Formats: MM/dd/yyyy HH:mm, MM/dd/yy HH:mm, HH:mm
c9QLFilter Optional Cloud9QL post-processing of results.
overrideVals Data storage strategy. {"replaceAll":true} replaces all data. "replaceValuesForKey":["fieldA"] upserts by key.

Datasource Example:

[
  {
     "name":"prodClickHouse",
     "url":"clickhouse-server:8123/analytics",
     "datasource":"clickhouse",
     "userId":"default",
     "password":"your_password"
  }
]

Query Examples:

[
  {
    "entityName":"Daily Revenue",
    "dsName":"prodClickHouse",
    "queryStr":"SELECT toDate(event_time) AS day, sum(revenue) AS total_revenue FROM events GROUP BY day ORDER BY day DESC LIMIT 90",
    "frequencyType":"hours",
    "frequency":1,
    "overrideVals":{
      "replaceAll":true
    }
  },
  {
    "entityName":"User Sessions",
    "dsName":"prodClickHouse",
    "queryStr":"SELECT user_id, count() AS sessions, sum(page_views) AS total_views FROM sessions WHERE event_date >= today() - 7 GROUP BY user_id",
    "overrideVals":{
      "replaceValuesForKey":["user_id"]
    },
    "startTime":"06:00",
    "frequencyType":"daily",
    "frequency":1
  }
]

The first query runs every hour and replaces all data for the dataset. The second runs daily at 6:00 AM and upserts by user_id.

Embedded Analytics with ClickHouse

Knowi supports white-label embedding of ClickHouse-powered dashboards. Key capabilities:

See Embeddable Analytics & SSO for full documentation.