The clickhouse http interface uses TSV(tab separated) as the default response format.
CREATE TABLE users (uid Int16, name String, age Int16) ENGINE=Memory;
INSERT INTO users VALUES (1231, 'John', 33);
INSERT INTO users VALUES (6666, 'Ksenia', 48);
INSERT INTO users VALUES (8888, 'Alice', 50);
SELECT * FROM users;
The server response:
{
"result": {
"query_run_id": "0180af8f-c9c4-41c7-be52-011a96cc1768",
"output": "1231\tJohn\t33\n8888\tAlice\t50\n6666\tKsenia\t48\n",
"time_elapsed": "117ms"
}
}
Changing the format allows the response to be easily parsed or loaded into js/python objects.
Change the query to
SELECT * FROM users FORMAT JSON;
This outputs:
{
"result": {
"query_run_id": "26732a8a-aac9-4de7-b71a-4e1f35032c17",
"output": "{\n\t\"meta\":\n\t[\n\t\t{\n\t\t\t\"name\": \"uid\",\n\t\t\t\"type\": \"Int16\"\n\t\t},\n\t\t{\n\t\t\t\"name\": \"name\",\n\t\t\t\"type\": \"String\"\n\t\t},\n\t\t{\n\t\t\t\"name\": \"age\",\n\t\t\t\"type\": \"Int16\"\n\t\t}\n\t],\n\n\t\"data\":\n\t[\n\t\t{\n\t\t\t\"uid\": 1231,\n\t\t\t\"name\": \"John\",\n\t\t\t\"age\": 33\n\t\t},\n\t\t{\n\t\t\t\"uid\": 6666,\n\t\t\t\"name\": \"Ksenia\",\n\t\t\t\"age\": 48\n\t\t},\n\t\t{\n\t\t\t\"uid\": 8888,\n\t\t\t\"name\": \"Alice\",\n\t\t\t\"age\": 50\n\t\t}\n\t],\n\n\t\"rows\": 3,\n\n\t\"statistics\":\n\t{\n\t\t\"elapsed\": 0.000923364,\n\t\t\"rows_read\": 3,\n\t\t\"bytes_read\": 54\n\t}\n}\n",
"time_elapsed": "106ms"
}
}
Now, you can parse the value of output and get the data key.
JSON.parse(clickhouse_response["result"]["output"])["data"];
returns
[
{
"uid": 1231,
"name": "John",
"age": 33
},
{
"uid": 6666,
"name": "Ksenia",
"age": 48
},
{
"uid": 8888,
"name": "Alice",
"age": 50
}
]
Check out all the supported clickhouse formats here
To set JSON as the default response format in the clickhouse HTTP interface, pass default_format=JSON as a query parameter. This should be handled automatically if you're using a clickhouse client library.
Top comments (0)