timonkk / clickhouse Goto Github PK
View Code? Open in Web Editor NEWNodeJS client for ClickHouse
License: Apache License 2.0
NodeJS client for ClickHouse
License: Apache License 2.0
This is the query that executes with an error:
EXISTS test_db.myTable
Current output:
undefined
Expected output:
0 or 1
I can execute this query over http protocol:
$ curl 'http://localhost:8123/?password=MyPassword&query=EXISTS%20test_db.myTable1'
0
$ curl 'http://localhost:8123/?password=MyPassword&query=EXISTS%20test_db.myTable'
1
Port number is missing in the URL if it's custom port number for HTTPS. Below is an URL generated for the following configuration:
"https://<USERNAME>:<PWD>@my.domain.com/?user=default&session_timeout=60&output_format_json_quote_64bit_integers=0&enable_http_compression=0&ssl=true&database=traffic&query_id=2ac8e527-bf77-4cc1-991c-cb3ce959adff&query=SELECT+1+FORMAT+JSON%3B"
Below is the sample config:
{
url: 'https://my.domain.com:8123',
port: 8123,
debug: false,
basicAuth: {
username:
'USER_NAME',
password:
'PWD',
},
isUseGzip: false,
format: 'json', // "json" || "csv" || "tsv"
config: {
ssl: true,
database: 'traffic',
},
}
It's a perferct project thanks
When using streams, getErrorObj(res)
doesn't return the error message because res
is an unmodified http.incomingMessage
object and so the body hasn't been read yet.
Rs.exec()
needs to read the body first with:
let body = '';
res.on('data', data => body += data);
res.on('end', () => {
console.log('body read', body);
});
Code needs a license. Suggest MIT or BSD.
Module doesn't process socket errors in .on("error") handle (like ECONNREFUSED and others).
Good time of day. When using the ws.writeRow method - I get an error - Error: 500: Internal Server Error.
CREATE TABLE visits2 ( id UInt64, duration Int64 ) ENGINE = MergeTree() PRIMARY KEY id ORDER BY id;
INSERT INTO test.visits2 VALUES (1, 10)
Now I want to record a lot of things in one go, and most importantly, quickly. From the tests I take your method to prepare a request, fill it with my own data
const ws = clickhouse.insert('INSERT INTO test.visits2').stream();
let count = 0;
let rowCount = 50
for(let i = 1; i <= rowCount; i++) {
await ws.writeRow(
[
_.range(0, 50).map(
j => ${i}:${j}
).join('-')
]
);
++count;
}
the value will be about this --- 43:0-43:1-43:2-43:3-43:4-43:5-43:6-43:7-43:8-43:9.......
//wait stream finish
const result = await ws.exec();
and I'm getting a server error.
Error: 500: Internal Server Error
at getErrorObj (//backend/node_modules/clickhouse/index.js:107:12)
at Request. (//backend/node_modules/clickhouse/index.js:205:7)
at emitOne (events.js:116:13)
at Request.emit (events.js:211:7)
at Request.onRequestResponse (/****/backend/node_modules/request/request.js:1066:10)
at emitOne (events.js:116:13)
at ClientRequest.emit (events.js:211:7)
at HTTPParser.parserOnIncomingClient [as onIncoming] (_http_client.js:551:21)
at HTTPParser.parserOnHeadersComplete (_http_common.js:115:23)
at Socket.socketOnData (_http_client.js:440:20)
If you try this code in a node console:
const { ClickHouse } = require('clickhouse')
const ch = new ClickHouse({ url: 'http://localhost:8123' })
async function test() {
let i = 0;
for await (const rs of ch.query('select number from system.numbers').stream()) {
if (++i > 3) break;
console.log(rs.number);
}
}
You get the expected results:
> test()
> 1
2
3
But if you monitor the open connections with netstat -tn | grep 8123
you will see that every time you call test()
it opens a new connection to port 8123 and leaves it hanging.
I looked into the code of stream2asyncIter and I see that destroy() is actually called by the for-async-break, but then the code looks for a this.close (which doesn't exist) and does nothing else.
I think the stream2asyncIter destroy() should call stream.destroy()
on the original stream, and then the original stream "rs" should receive destroy() and close the HTTP stream.
No?
Hello
Can you answer for a question please? How can I set settings for clickhouse that are describing here?
I tried to do select * from tablename
and got all records except last one.
Probably it's because of
// Удаляем строки с заголовками и типами столбцов И завершающую строку rows = rows.slice(2, rows.length - 1);
what's output_format_json_quote_64bit_integers =1,
what's output_format_json_quote_64bit_integers =0
hey there,
according to this line, constructor considers connection port for URLs like https://clickhouse.dev.local
is 8123, not 443.
specifying the port in the URL (like https://clickhouse.dev.local:443
) does not help, i still get ECONNREFUSED 127.0.0.1:8123
.
could you please fix that, that is counter-intuitive.
Now, I have three column. My request data is [{c1:1,c2:2,c3:3},{c1:4,c2:5,c3:''}]
. After transformation, I see the http request body is 1\t2\t3\n4\t5\t
.Then the db response message is:
500: Code: 33, e.displayText() = DB::ParsingException: Unexpected end of stream, while parsing value of Nullable type: Buffer has gone, cannot extract information about what has been parsed.: (at row 2)\n (version 21.3.2.5 (official build))\n"}
If my request data is [{c1:1,c2:2,c3:''},{c1:4,c2:5,c3:6}]
.The request body is 1\t2\t\n4\t5\t6
, It's ok.
If my request data is [{c1:1,c2:2,c3:3},{c1:4,c2:5,c3:''}]
.The request body is 1\t2\t3\n4\t5\t\n
, It's ok.
So the body should not empty in last. You could Insert '\n' at the end of the body.
sir,is there a method for bulk insert?
After upgrade of Clickhouse server to 21.4.3.21 I started getting error:
Invalid authentication: it is not allowed to use Authorization HTTP header and authentication via parameters simultaneously
I am connecting as follows:
const clickhouse = new ClickHouse(
{ basicAuth : {
username: 'xxx',
password: 'yyy'
}}
);
I managed to get it to work again by commenting out the following lines in clickhouse module:
//if (username) {
// url.searchParams.append('user', username);
//}
My user password contain a $
and a %
.
When i run my code i get a (node:64064) UnhandledPromiseRejectionWarning: URIError: URI malformed
I simply manualy url-encode the password and it work ...
but i think this need to be done by the lib...
did you know that npm package of clickhouse have dependency of lodash with vulnerability
https://www.npmjs.com/advisories/1065
and if i fix it package breaks and i get
UnhandledPromiseRejectionWarning: Error: Invalid protocol: clickhouse:
like using with
clause in SQL.
"""
with 'a' as A
select A
"""
Line 479 in 9dea3c0
with () as A select
SQL.Hello
Could you please help me with providing sslCA file to configuration?
Is it possible and if it is, how the field is named and where to put it?
thank you
Hi I have tried to create a column default DateTime now()
and is not working properly.
Currently, I have:
`CREATE TABLE IF NOT EXISTS session_temp (
ClientID UUID,
FirstName String,
Address String,
City String,
StartDate DateTime DEFAULT now()
) ENGINE = MergeTree()
PRIMARY KEY ClientID
ORDER BY ClientID`,
and
const insertSession = await clickhouseClient
.insert(insertIntoTableByName('session_temp'))
.stream();
await insertSession.writeRow([
uuid(),
'firstname1',
'address1',
'city1',
]);
await insertSession.exec();
it's firing an error, but if I inserted date after 'city' it works
Can someone help me ?
Thanks in advance
Carlos Vieira
Using FORMAT JSON
or FORMAT JSONCompact
only return the actual data
. Is there any way to export meta
, data
, extremes
, rows
, etc as well? like the output in https://clickhouse.tech/docs/en/interfaces/formats/#jsoncompact?
Hello!
It is necessary to correct the condition:
if ( ! opts.query.match(/^insert/i) && (!opts.query.match(/^create/i))) {
opts.query += ' FORMAT TabSeparatedWithNamesAndTypes';
}
At least so:
if ( ! opts.query.match(/^insert/i) && (!opts.query.match(/^create/i)) && (!opts.query.match(/^drop/i))) {
opts.query += ' FORMAT TabSeparatedWithNamesAndTypes';
}
does it use TCP transport? or http?
Hello! I have queries with nested queries and with long where statements. And package fails this queries because "query" parameter in http request is to long. Can you move sql query string from http query parameter to request body?
Hello. When i insert date values on DateTime fields, i getting wrong dates.
I use [email protected]
index.js
const { ClickHouse } = require('clickhouse');
start();
async function start() {
const clickhouse = new ClickHouse({
url: 'localhost',
port: 8123,
format: 'json'
});
const tableName = 'testDate';
await clickhouse.query(
`CREATE TABLE IF NOT EXISTS ${tableName}
(
year Int32,
dateTime DateTime
)
ENGINE MergeTree() ORDER BY (year)`
).toPromise();
let ws = clickhouse.insert(`INSERT INTO ${tableName}`).stream();
for (let year = 1980; year <= 2020; year++) {
const date = new Date(`${year}-05-25T10:30:00.000+03:00`);
console.log(date);
await ws.writeRow([year, date]);
}
const result = await ws.exec();
console.log(result);
}
execution result:
select from clickhouse table result:
How can I bulk import CSV data to Clickhouse?
If my sql is too long to send, can the sql be POST's parameter not a part of URL parameter?
For now, default FORMAT when inserting data is TabSeparated
, Can you please support JSONEachRow
or have a way to disable auto append FORMAT TabSeparated
?
Line 511 in 90baf55
Thank you
Bug that happens
I'm using Node's clickhouse module, every time I try to do a query I'm welcomed with the fallowing in the console.
Error: 404: �9?%�J��PG!U/%�� '�2$��DCS�V����ʵ"9��$3?�J�%�$1)�8U!5'5��(391'� �(1�8��H!%?�8O�D!�"��DA�,��83?O��@��H�D�TA#?--3931G!�43'ES�
����2|
1 getErrorObj
/home/REDACTED/Desktop/programming/REDACTED/node_modules/clickhouse/index.js:225
2 Request._callback
/home/REDACTED/Desktop/programming/REDACTED/node_modules/clickhouse/index.js:557
3 Request.self.callback
/home/REDACTED/Desktop/programming/REDACTED/node_modules/request/request.js:185
4 anonymous
/home/REDACTED/Desktop/programming/REDACTED/node_modules/request/request.js:1161
5 anonymous
/home/REDACTED/Desktop/programming/REDACTED/node_modules/request/request.js:1083
Environment setup
ClickHouse server version 20.12.4 revision 54442 running on Debian 10 based Linux distribution inside of Docker.
Things to note
Executing queries via ClickHouse client from inside of the image via CLI works. Also, from outside of Docker I'm able to execute queries via DBeaver.
It seems that the error appears only through Node.
Does the library support update clause?
Is there a way to pass query parameters to Clickhouse?
Query: "SELECT {value:UInt8}", Parameter: {param_value: "2"}
Thank you.
Hi there,
is it possible to get the query progress like the Clickhouse CLI Client does?
Hi,
I am trying to use "external" with the query and it is not working.
after a minute I get a timeout message.
I guess the issue is from the sending because I tried to use the same query as in the example (very simple query with data in length of 10 short strings).
how can I fix it?
and by the way, I noticed that the URL for the request include the format of tabSeparateWithNames while I sending the structure not in the data itself (sent it as structure key in the external object ), it is not going to ignore my first data raw because of it?
this is the URL when I tried to run the test query:
http://***********/?user=default&session_timeout=100&output_format_json_quote_64bit_integers=0&enable_http_compression=true&query_id=772f1903-389d-469e-9736-b9d1ccbe0698&database=default&temp_table_structure=str+String&query=SELECT+count%28%29+AS+count+FROM+temp_table
[email protected]
has multiple vulnerabilities:
https://snyk.io/vuln/SNYK-JS-LODASH-1040724
https://snyk.io/vuln/SNYK-JS-LODASH-590103
Can you please upgrade lodash
to 4.17.21
(the current latest version) to resolve these vulnerabilities?
When a query uses on cluster
, the clickhouse server will report back the status on each node in a tab separated format regardless of whether json was requested. This will cause a parse error.
All packages for this project are defined using exact versions:
Lines 52 to 61 in 9dea3c0
Would it be possible to move these to using version ranges (e.g. `"lodash": "^4.17.19")?
As it stands, for my use case, in adding this package, we're getting some duplication of packages, giving an increase in bundle size. For example, our application uses lodash 4.17.20 overall, but in adding this library, we're now also including lodash 4.17.19 in the bundle.
Happy to open a PR if so desired to do this.
"use strict";
import { ClickHouse } from "clickhouse";
import { assert } from "chai";
const clickHouseInterface = new ClickHouse({
url: "http://localhost",
port: 8123,
debug: false,
basicAuth: {
username: "default",
password: "123456"
},
isUseGzip: false
});
describe('bug', async () => {
it('nullable values exist', async ()=>{
const db = await clickHouseInterface.query("create database if not exists bug_test").toPromise();
assert.deepEqual(db, {r:1});
const tb = await clickHouseInterface.query(`CREATE TABLE IF NOT EXISTS bug_test.test (userId String, eventTime UInt32, eventName String, test Nullable(String))
ENGINE=MergeTree
PARTITION BY (toDate(toDateTime(eventTime)), eventName)
ORDER BY (userId, eventTime)
SETTINGS index_granularity = 8192;`).toPromise();
assert.deepEqual(tb, {r:1});
try{
const insert = await clickHouseInterface.insert(`INSERT INTO bug_test.test (userId , eventTime, eventName, test)`,[
{
userId: "id1",
eventName: "name1",
eventTime: parseInt(Date.now() / 1000),
test: "testValue"
},{
userId: "id1",
eventName: "name1",
eventTime: parseInt(Date.now() / 1000),
test: "testValue"
}
]).toPromise();
assert.deepEqual(insert, {r:1});
console.log("success");
} catch(ex) {
console.log(ex);
assert.equal(1,0)
}
})
it('nullable values does not exist', async ()=>{
const db = await clickHouseInterface.query("create database if not exists bug_test").toPromise();
assert.deepEqual(db, {r:1});
const tb = await clickHouseInterface.query(`CREATE TABLE IF NOT EXISTS bug_test.test (userId String, eventTime UInt32, eventName String, test Nullable(String))
ENGINE=MergeTree
PARTITION BY (toDate(toDateTime(eventTime)), eventName)
ORDER BY (userId, eventTime)
SETTINGS index_granularity = 8192;`).toPromise();
assert.deepEqual(tb, {r:1});
try{
const insert = await clickHouseInterface.insert(`INSERT INTO bug_test.test (userId , eventTime, eventName, test)`,[
{
userId: "id1",
eventName: "name1",
eventTime: parseInt(Date.now() / 1000)
},{
userId: "id1",
eventName: "name1",
eventTime: parseInt(Date.now() / 1000),
test: "testValue"
}
]).toPromise();
assert.deepEqual(insert, {r:1});
console.log("success");
} catch(ex) {
console.log(ex);
assert.equal(1,0)
}
})
it('nullable values does not exist 1', async ()=>{
const db = await clickHouseInterface.query("create database if not exists bug_test").toPromise();
assert.deepEqual(db, {r:1});
const tb = await clickHouseInterface.query(`CREATE TABLE IF NOT EXISTS bug_test.test (userId String, eventTime UInt32, eventName String, test Nullable(String))
ENGINE=MergeTree
PARTITION BY (toDate(toDateTime(eventTime)), eventName)
ORDER BY (userId, eventTime)
SETTINGS index_granularity = 8192;`).toPromise();
assert.deepEqual(tb, {r:1});
try{
const insert = await clickHouseInterface.insert(`INSERT INTO bug_test.test (userId , eventTime, eventName, test)`,[
{
userId: "id1",
eventName: "name1",
eventTime: parseInt(Date.now() / 1000),
test: "testValue"
},{
userId: "id1",
eventName: "name1",
eventTime: parseInt(Date.now() / 1000)
}
]).toPromise();
assert.deepEqual(insert, {r:1});
console.log("success");
} catch(ex) {
console.log(ex);
assert.equal(1,0)
}
})
})
Steps to reproduce
package.json
{
"dependencies": {
"clickhouse": "^2.2.1"
}
}
test.js
const { ClickHouse } = require('clickhouse')
const CH = new ClickHouse({ url: 'http://127.0.0.1:8123/' })
async function main() {
for await (const row of CH.query('select 1+1 as two').stream()) {
console.log(row.two)
}
console.log('done')
}
main()
Run npm i
and then node test.js
Expected results
2
done
Actual results
2
/tmp/asyncbug/node_modules/stream2asynciter/index.js:17
this.close();
^
TypeError: this.close is not a function
at Readable.destroy [as _destroy] (/tmp/asyncbug/node_modules/stream2asynciter/index.js:17:11)
at Readable.destroy (internal/streams/destroy.js:39:8)
at endReadableNT (internal/streams/readable.js:1343:16)
at processTicksAndRejections (internal/process/task_queues.js:80:21)
I'm using node v14.16.0 and npm 6.14.11 if it matters.
it looks like the latest npm package doesn't have the updates to types (and probably some other things?). it would be great to get a new package released.
Queries using WITH TOTALS
don't return the last row which is created by WITH TOTALS
. any ideas?
Is it possible to send custom session_id
with each query, it can allow us to create separate sessions for each client on request basis. Currently the session_id
is the one created while connecting. Once connected it cannot change, however we send it explicitly in each HTTP Request, just confirmed it in debug mode.
If this is not possible, can it be done? It's a minor change because we are already using me.opts.config
in each request, we can just extend the object with a third parameter in _getReqParams(query, data)
In some cases it would be useful to return unparsed raw CSV or TSV data
For example,
const clickhouse = new Clickhouse({...defaultOptions, format: 'csv', raw: true});
const rawData = await clickhouse.query('SELECT * FROM table;').toPromise();
// return this rawData string as a CSV string to anywhere or browser's downloadable CSV file
Is there a connection pool?
Hello. How insert to CH tuple int-s in ws?
There is no description and examples in the documentation
for example
tuple (1,2,3)
example query
CREATE TABLE visits ( id UInt64, test Tuple(UInt8, UInt8, UInt8) ) ENGINE = MergeTree() PRIMARY KEY id ORDER BY id;
simple insert
INSERT INTO test.visits VALUES (1, (10,11,12));
insert in ws
`
const wsCH = clickhouse.insert('INSERT INTO test.visits').stream();
for (let i = 1; i <= 5; i++) {
await wsCH.writeRow(
[
i, (10,11,12)
]
);
}
const result = await wsCH.exec();
`
Hello!
Can you add the "database" option to specify a database? It should not be a hard issue, just one additional query parameter like http://host:port/?database=my_db&query=...
.
Line 352 in d0d19ca
You have written that field "database" should be in the inner object "config" in docs, but actually It is false.
const clickhouse = new ClickHouse({
....
config: {
.....
database : 'my_database_name',
},
});
const clickhouse = new ClickHouse({
....
database : 'my_database_name',
config: {
.....
},
});
Hi!
Example query:
SELECT EventDate, count() AS c
FROM test.hits
GROUP BY EventDate
WITH TOTALS
Where in ResultSet I can get totals?
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.