ClickHouse Client for NodeJS.
npm install @depyronick/clickhouse-clientClickHouse® 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.
Install the following package:
``bash`
$ npm i --save @depyronick/clickhouse-client
- Importing the module
- Methods
- Query
- ClickHouseClient.query
- [ClickHouseClient.queryPromise](#clickhouseclientquerypromisetquery-string-promisestring--t)ClickHouseClient.query
- Query with parameters
- ClickHouseClient.queryPromise
- [](#clickhouseclientquerypromisetquery-string-params-recordstring-string--number-promisestring--t)ClickHouseClient.insert
- Insert
- [](#clickhouseclientinsertttable-string-data-t-observablevoid)ClickHouseClient.insertPromise
- [](#clickhouseclientinsertpromisettable-string-data-t-promisevoid)ClickHouseClient.insertRaw(table: string, data: string | Buffer | Readable, format: ClickHouseDataFormat): Observable
- ClickHouseClient.ping(timeout: number = 3000): Promise
- Other
-
- Notes
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.
#### Query
##### ClickHouseClient.query
`javascriptrow
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, here is the json representation of the rowrow
// if format is not any of JSON, then represents string chunk from http stream`
},
complete: () => {
// called when stream is completed
}
});
##### ClickHouseClient.queryPromise
`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
`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 rowrow
// if format is not any of JSON, then represents string chunk from http stream`
},
complete: () => {
// called when stream is completed
}
});
##### ClickHouseClient.queryPromise
`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
The insert method accepts two inputs.
- table is the name of the table that you'll be inserting data to.analytics_db.visits
- Table value could be prefixed with database like .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
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.\nfalse
// and 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
});
- 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.
- Author - Ali Demirci