DEV Community

Cover image for Changing the default clickhouse server response format to JSON
Amal Shaji
Amal Shaji Subscriber

Posted on • Originally published at amal.sh

Changing the default clickhouse server response format to JSON

#clickhouse #json

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;
Enter fullscreen mode Exit fullscreen mode

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" } }
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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" } }
Enter fullscreen mode Exit fullscreen mode

Now, you can parse the value of output and get the data key.

JSON.parse(clickhouse_response["result"]["output"])["data"];
Enter fullscreen mode Exit fullscreen mode

returns

[ { "uid": 1231, "name": "John", "age": 33 }, { "uid": 6666, "name": "Ksenia", "age": 48 }, { "uid": 8888, "name": "Alice", "age": 50 } ]
Enter fullscreen mode Exit fullscreen mode

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.

References

Top comments (0)