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.
Install the following package:
$ npm i --save @depyronick/clickhouse-client
Once the installation process is complete, you can import the ClickHouseClient
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.
ClickHouseClient.query<T>(query: string): Observable<string | T>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<T>(query: string): Promise<string | T[]>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'
);
Clickhouse-server supports performing queries with paramaters. Both query and queryPromise accept a second argument which respresents the query paramaters value as a Record<string, string | number>.
The query can contain parameters placeholders that have the following syntax:
{<name>:<data type>}
-- a parameter called "limit" that will be interpreted as an 8-bit unsigned integer
{limit:UInt8}
So, you can pass parameters as the following:
SELECT * FROM visits LIMIT {limit:UInt8}
Official documentation (HTTP Interface - Query with paramters)
ClickHouseClient.query<T>(query: string, params: Record<string, string | number>): Observable<string | T>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<T>(query: string, params: Record<string, string | number>): Promise<string | T[]>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);
ClickHouseClient.insert<T>(table: string, data: T[]): Observable<void>The insert method accepts two inputs.
table is the name of the table that you’ll be inserting data to.
analytics_db.visits.data: T[] array of JSON objects to insert.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<T>(table: string, data: T[]): Promise<void>The insertPromise method accepts the same inputs as insert but returns a Promise, instead of Observable.
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.ping(timeout: number = 3000): Promise<boolean>The ping method accepts one input.
timeout is the time in milliseconds to wait for the server to send ping response Ok.\n.// 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
});