Skip to main content

ClickHouse

ToolJet can connect to the ClickHouse to read and write data.

info

ToolJet uses this NodeJS client for ClickHouse.

Connection

To add a new ClickHouse datasource, Go to the Datasource Manager on the left sidebar of the app editor and click on Add datasource button. Select ClickHouse from the modal that pops up.

ToolJet requires the following to connect to your ClickHouse Database:

  • Username
  • Password
  • Host
  • Port
  • Database Name
  • Protocol
  • Use Post
  • Trim Query
  • Use Gzip
  • Debug
  • Raw
ClickHouse connection

Querying ClickHouse

After setting up the ClickHouse datasource, you can click on the + button of the query manager and select the ClickHouse datasource that you added in the previous step to create a new query.

info

For more details on clickhouse visit Clickhouse docs.

Supported Operations:

SQL Query

Use this to operation to enter ClickHouse SQL Statements. These statements represent various kinds of action you can perform using SQL queries.

Example SQL queries

  • SELECT:

    SELECT * from test array;
    ClickHouse SQL Statement operation
  • CREATE:

    CREATE TABLE test array3 (
    date Date,
    str String,
    arr Array(String),
    arr2 Array (Date)
    arr3 Array(UInt32) ,
    id1 UUID
    )ENGINE=MergeTree () ORDER BY(str)
    ClickHouse SQL Statement operation
  • ALTER TABLE(add column)

    ALTER TABLE test array1 ADD COLUMN Added2 UInt32;
    ClickHouse SQL Statement operation
  • SELECT WITH WHERE CLAUSE

    SELECT * FROM test array1 WHERE str='Somethingl...'
    ClickHouse SQL Statement operation
  • UPDATE

    ALTER TABLE test_array1 UPDATE arr = (12] WHERE str='Somethingl...'
    ClickHouse SQL Statement operation
  • DELETE

    ALTER TABLE test_array1 DELETE WHERE str= 'Somethingl...'
    ClickHouse SQL Statement operation
  • NORMAL INSERT

    1) Step 1 - Creating Table

    CREATE TABLE test array4 (
    name String,
    date Date
    )ENGINE=MergeTree () ORDER BY (name)
    ClickHouse SQL Statement operation

    2) Step 2 - Insert

    INSERT INTO test_array4 (*) VALUES ('juvane', '1996-01-13')
    ClickHouse SQL Statement operation
    info

    Giving Primary Key

    CREATE TABLE db.table_name
    (
    name1 type1, name2 type2, ...,
    PRIMARY KEY(expr1[, expr2,...])]
    )
    ENGINE = engine;

    OR

    CREATE TABLE db.table_name
    (
    name1 type1, name2 type2, ...
    )
    ENGINE = engine
    PRIMARY KEY(expr1[, expr2,...]);

Insert array of objects

Use this operation for inserting array of objects.

Required Parameters:

  • Body
  • Fields
  • Table name

Example Body value:

[
{
date: '2018-01-01',
str: 'Something1...',
arr: [],
arr2: ['1985-01-02', '1985-01-03'],
arr3: [1,2,3,4,5],
id1: '102a05cb-8aaf-4f11-a442-20c3558e4384'
},
{
date: '2018-02-01',
str: 'Something2...',
arr: ['5670000000', 'Something3...'],
arr2: ['1985-02-02'],
arr3: [],
id1: 'c2103985-9a1e-4f4a-b288-b292b5209de1'
}
];
ClickHouse Insert array of objects operation