ClickHouse

ClickHouse is an open-source, high performance columnar OLAP database management system for real-time analytics using SQL.

Official Websitehttps://clickhouse.com/

Documentationhttps://clickhouse.com/docs/

NodeJS packagehttps://www.npmjs.com/package/clickhouse

Version2.6.0

Source Codehttps://github.com/TimonKK/clickhouse

TagsDatabase, SQL

Network Connection needs

This integration needs network access to the server where the service is running.

See the Network access page for details about how to achieve that.

Credential configuration

Fill it with the appropiate values for your connection. Fill which you need of: url, port, username and password.

In the extra options field you can pass any of the params you can find here.

Here you have an example of a filled credential configuration form in YepCode:

ClickHouse snippets available in editor

note

The title is the triggering text for YepCode to autocomplete the script

Integration

New integration from credential

const clickhouse = yepcode.integration.clickhouse('credential-slug')

New integration from plain authentication data

const { ClickHouse } = require('clickhouse')

const clickhouse = new ClickHouse({
    url: 'https://play-api.clickhouse.com',
    port: 8443,
    debug: false,
    basicAuth: {
        username: 'playground',
        password: 'clickhouse',
    },
    isUseGzip: false,
    format: "json",
    raw: false,
    config: {
        session_id: 'my_session_id',
        session_timeout: 60,
        output_format_json_quote_64bit_integers: 0,
        enable_http_compression: 0,
        database: 'my_database_name',
    }
});

Execute query

Execute query (promise)

const rows = await clickhouse.query('SHOW DATABASES').toPromise();
console.log(rows);

Execute query (callback)

await clickhouse.query('SHOW GRANTS').exec((err, data) => {
    if (err) {
        console.log(err)
    } else {
        console.log(data)
    }
});

Execute query (stream)

clickhouse.query('SHOW TABLES FROM datasets').stream()
    .on('data', function (data) {
        console.log(data)
    })
    .on('error', err => {
        console.log(err)
    })
    .on('end', () => {

    });

Execute query (async stream)

const query = `
    SELECT
        protocol(Referer) AS Protocol,
        path(Referer) AS Path,
        domain(Referer) AS Domain,
        topLevelDomain(Referer) AS TopLevelDomainDomain,
        queryString(Referer) AS Query,
        extractURLParameters(Referer) AS Params,
        arrayMap(x -> arrayMap(y -> decodeURLComponent(y), splitByChar('=', x)) , Params) as Decoded
    FROM datasets.visits_v1
    WHERE NOT empty(Referer)
                AND StartDate BETWEEN '2014-03-23' AND '2014-03-30'
                AND Params != emptyArrayString()
    LIMIT 10`

for await (const row of clickhouse.query(query).stream()) {
    console.log(row);
}

Insert

Insert

try {
    const ws = clickhouse.insert('INSERT INTO datasets.visits_v1 (CounterID)', {}, { sessionId: 'my-session-id' }).stream();
    await ws.writeRow('151522');
    const exec = await ws.exec();
    console.log(exec)
} catch (error) {
    console.error(error)
}

Set session

Set session

clickhouse.sessionId = 'my-session-id';

Set session (query)

try {
    const result = await clickhouse.query(
        `CREATE TEMPORARY TABLE names
      (_id String, name String)
      ENGINE=Memory`, {}, { sessionId: 'my-session-id' }
    ).toPromise();
    console.log(result)
} catch (error) {
    console.error(error)
}