ClickHouse is an open-source column-oriented database management system that uses SQL queries to generate reports.

This integration supports the following semi-structured datatypes from ClickHouse: Tuple (and named tuples), Nested, and Array. For more information, see Tuple, Nested data structures, and Array in the ClickHouse SQL reference.

Prerequisites for Integrating with ClickHouse

Any ClickHouse user account with permissions granted to access the relevant databases and tables. For more information about access rights, see ClickHouse documentation.

You can use the following SQL script to set up a ClickHouse User and Role and grant the necessary permissions for the Source in Validio.

• The user profile has readonly permissions, which means that the user can only execute read queries (such as SELECT).
• The role is also granted remote permissions to fetch the query logs needed to build the lineage relationships.

CREATE ROLE IF NOT EXISTS validio_role;

CREATE USER IF NOT EXISTS validio_user
    IDENTIFIED WITH sha256_password BY '**************'
    SETTINGS PROFILE 'readonly'
    DEFAULT ROLE validio_role;

GRANT SELECT, SHOW ON mydatabase.* TO validio_role;
GRANT SELECT ON system.query_log TO validio_role;
GRANT REMOTE ON *.* to validio_role;

For more information about ClickHouse permissions for queries, see ClickHouse documentation.

📘

Note

Relative entropy validators may require more resources to execute on ClickHouse dev environments. For example, if you plan to use a relative entropy validator, you might see errors due to limited RAM.

Add a ClickHouse Credential

To add a credential for ClickHouse,

1. Navigate to Observability > Credentials and click + New Credential.
2. For Credential Type, select ClickHouse Credential.
3. Fill in the credential parameter fields. Refer to the ClickHouse Credential Parameters table.
4. Click Create.

Validio will validate the connection to the ClickHouse account. If validation passes, Validio will automatically start fetching data. If validation fails, check that you provided the correct parameter values and try again.

Once the credential is created, you can add a source to monitor ClickHouse data.

ClickHouse Credential Parameters

Add a ClickHouse Source

To add a source for ClickHouse,

1. Navigate to Observability > Sources and click + New Source.
2. Under Source type, select ClickHouse.
3. Under Credentials, select a valid ClickHouse credential.
4. Under Config, fill in the configuration parameters.
   1. Enter a Name for the source. This name will be used when setting up validators on the source.
   2. Enter the Database and Table to specify the data to read for the source.
   3. (Optional) Select the Cron preset to specify the polling frequency for the data.
5. Under Schema,
   1. Select Fields which will be used to validate the new source.
   2. (Optional) Specify a timestamp field to use as the Cursor field and a Lookback time to indicate how far back to read data from the source. For more information, see Data Warehouses.
6. Under Window, select the Window type and fill in the window configuration fields.
7. Click Continue to create the new source.