@depyronick/clickhouse-client

clickhouse-client for NodeJS

ClickHouse® is an open-source, high performance columnar OLAP database management system for real-time analytics using SQL. ClickHouse combined with TypeScript helps you develop better type safety with your ClickHouse queries, giving you end-to-end typing.

Installation

Install the following package:

``bash
$ npm i --save @depyronick/clickhouse-client
`

Quick Start

- Importing the module
- Methods
- Query
- ClickHouseClient.query(query: string): Observable
- [ClickHouseClient.queryPromise(query: string): Promise](#clickhouseclientquerypromisetquery-string-promisestring--t)
- Query with parameters
- ClickHouseClient.query(query: string, params: Record): Observable
- [ClickHouseClient.queryPromise(query: string, params: Record): Promise](#clickhouseclientquerypromisetquery-string-params-recordstring-string--number-promisestring--t)
- Insert
- [ClickHouseClient.insert(table: string, data: T[]): Observable](#clickhouseclientinsertttable-string-data-t-observablevoid)
- [ClickHouseClient.insertPromise(table: string, data: T[]): Promise](#clickhouseclientinsertpromisettable-string-data-t-promisevoid)
- ClickHouseClient.insertRaw(table: string, data: string | Buffer | Readable, format: ClickHouseDataFormat): Observable
- Other
- ClickHouseClient.ping(timeout: number = 3000): Promise
- Notes

$3

Once the installation process is complete, you can import the ClickHouseClient

`javascript
const { ClickHouseClient } = require('@depyronick/clickhouse-client');

// or:
// import { ClickHouseClient } from '@depyronick/clickhouse-client';

const analyticsServer = new ClickHouseClient({
host: '127.0.0.1',
password: '7h3ul71m473p4555w0rd'
});

// you can create multiple clients
const chatServer = new ClickHouseClient({
host: '127.0.0.2',
password: '7h3ul71m473p4555w0rd'
});
`

new ClickHouseClient(options: ClickHouseOptions) will create a ClickHouse client with the specified connection options.

See ClickHouseOptions object for more information.

$3

#### Query

##### ClickHouseClient.query(query: string): Observable

`javascript
this.analyticsServer.query('SELECT * FROM visits LIMIT 10').subscribe({
error: (err) => {
// called when an error occurred during query
},
next: (row) => {
// if specified format is any of JSON formats, row here is the json representation of the row
// if format is not any of JSON, then row represents string chunk from http stream
},
complete: () => {
// called when stream is completed
}
});
`

##### ClickHouseClient.queryPromise(query: string): Promise

`javascript
this.analyticsServer
.queryPromise('SELECT * FROM visits LIMIT 10')
.then((rows) => {
// if specified format is any of JSON formats, rows is an array of all retrieved rows
// if not, then rows is the raw string result from clickhouse-server
})
.catch((err) => {
// called when an error occurred during query
});

// or

const rows = await this.analyticsServer.queryPromise(
'SELECT * FROM visits LIMIT 10'
);
`

#### Query with params

Clickhouse-server supports performing queries with paramaters. Both query and queryPromise accept a second argument which respresents the query paramaters value as a Record.

The query can contain parameters placeholders that have the following syntax:

`sql
{:}
-- a parameter called "limit" that will be interpreted as an 8-bit unsigned integer
{limit:UInt8}
`

So, you can pass parameters as the following:

`sql
SELECT * FROM visits LIMIT {limit:UInt8}
`

Official documentation (HTTP Interface - Query with paramters)

##### ClickHouseClient.query(query: string, params: Record): Observable

`javascript
const yersterday = new Date();
yesterday.setDate(yesterday.getDate() - 1);

const params = {
yesterday: yesterday.getTime(),
osName: 'OSX'
};

const query =
'SELECT * FROM visits WHERE timestamp >= {yesterday:DateTime} AND os = {osName:String} LIMIT 10';

this.analyticsServer.query(query, params).subscribe({
error: (err) => {
// called when an error occurred during query
},
next: (row) => {
// if specified format is any of JSON formats, row here is the json representation of the row
// if format is not any of JSON, then row represents string chunk from http stream
},
complete: () => {
// called when stream is completed
}
});
`

##### ClickHouseClient.queryPromise(query: string, params: Record): Promise

`javascript
const yersterday = new Date();
yesterday.setDate(yesterday.getDate() - 1);

const params = {
yesterday: yesterday.getTime(),
osName: 'OSX'
};

const query =
'SELECT * FROM visits WHERE timestamp >= {yesterday:DateTime} AND os = {osName:String} LIMIT 10';

this.analyticsServer
.queryPromise(query, params)
.then((rows) => {
// if specified format is any of JSON formats, rows is an array of all retrieved rows
// if not, then rows is the raw string result from clickhouse-server
})
.catch((err) => {
// called when an error occurred during query
});

// or

const rows = await this.analyticsServer.queryPromise(query, params);
`

#### Insert

##### ClickHouseClient.insert(table: string, data: T[]): Observable

The insert method accepts two inputs.

- table is the name of the table that you'll be inserting data to.
- Table value could be prefixed with database like analytics_db.visits.
- data: T[] array of JSON objects to insert.

`javascript
analyticsServer
.insert('visits', [
{
timestamp: new Date().getTime(),
ip: '127.0.0.1',
os: 'OSX',
userAgent:
'Mozilla/5.0 (Windows NT 10.0; Win64; x64) Chrome/95.0.4638.69 Safari/537.36',
version: '1.0.0'
}
])
.subscribe({
error: (err) => {
// called when an error occurred during insert
},
next: () => {
// currently next does not emits anything for inserts
},
complete: () => {
// called when insert is completed
}
});
`

#### ClickHouseClient.insertPromise(table: string, data: T[]): Promise

The insertPromise method accepts the same inputs as insert but returns a Promise, instead of Observable.

`javascript
analyticsServer
.insertPromise('visits', [
{
timestamp: new Date().getTime(),
ip: '127.0.0.1',
os: 'OSX',
userAgent:
'Mozilla/5.0 (Windows NT 10.0; Win64; x64) Chrome/95.0.4638.69 Safari/537.36',
version: '1.0.0'
}
])
.then(() => {
// insert was success
})
.catch((err) => {
// called when an error occurred during insert
});
`

##### ClickHouseClient.insertRaw(table: string, data: string | Buffer | Readable, format: ClickHouseDataFormat): Observable

Use insertRaw to send pre-formatted data such as CSV or JSONAsString.

`javascript
// insert a raw JSON object into a single String column
await analyticsServer.insertRawPromise(
'sensor_raw',
'{"source":"test","uid":"test"}',
ClickHouseDataFormat.JSONAsString
);

// insert CSV payload
await analyticsServer.insertRawPromise(
'visits',
'timestamp,ip,os\n1700000000,127.0.0.1,OSX\n',
ClickHouseDataFormat.CSV
);
`

#### Other

##### ClickHouseClient.ping(timeout: number = 3000): Promise

The ping method accepts one input.

- timeout is the time in milliseconds to wait for the server to send ping response Ok.\n.

`javascript
// if you're using async/await
const ping = await analyticsServer.ping();

// or

analyticsServer
.then((pingResult) => {
// ping result is a boolean
// it will return true if we were able to receive Ok.\n
// and false if anything but Ok.\n
})
.catch((reason) => {
// reason is the full response of the error
// see more details at https://axios-http.com/docs/handling_errors
});
``

Notes

- This repository will be actively maintained and improved.
- Planning to implement TCP protocol, if ClickHouse decides to documentate it.
- Planning to implement inserts with streams.
- This library supports http response compressions such as brotli, gzip and deflate.

Stay in touch

- Author - Ali Demirci

License

MIT licensed.