'How to get data with JSON format in Clickhouse-driver
I'm trying to get my Clickhouse data in my Django project. I'm using clickhouse_driver
and :
client.execute('SELECT * FROM myTable LIMIT 5 FORMAT JSON')
When I execute this command in my Clickhouse server SELECT * FROM myTable LIMIT 5 FORMAT JSON
it outputs in JSON
format. But in python, when I try it with clickhouse_driver it outputs only fields like:
[('2020213','qwerty','asdfg'),('2030103','qweasd','asdxv')]
But I want key-value json format..like
{"logdate":"2020213","host":"qwerty","cef":"asdfg"}
Any suggestions to resolve this problem? Or maybe I have to search for an alternate clickhouse_driver..
Thx.
Solution 1:[1]
I did not try Vladimir solution but here is the my solution :
client.execute commands gives us " with_column_types=True " parameters.. it gives us metadata for table. after :
result , columns = client.execute('SELECT * FROM myTbl LIMIT 5',with_column_types=True)
df=pandas.DataFrame(result,columns=[tuple[0] for tuple in columns])
dfJson=df.to_json(orient='records')
and this gives us our what we want.
Thx for suggestions :)
Solution 2:[2]
clickhouse-driver ignores FORMAT-clause (see Selecting data).
It can be done either manually by combining column name with related value:
from clickhouse_driver import Client
from json import dumps
client = Client(host='localhost')
data = client.execute_iter('SELECT * FROM system.functions LIMIT 5', with_column_types=True)
columns = [column[0] for column in next(data)]
for row in data:
json = dumps(dict(zip(columns, [value for value in row])))
print(f'''{json}''')
# Result:
# {"name": "fromUnixTimestamp64Nano", "is_aggregate": 0, "case_insensitive": 0, "alias_to": ""}
# {"name": "toUnixTimestamp64Nano", "is_aggregate": 0, "case_insensitive": 0, "alias_to": ""}
# {"name": "toUnixTimestamp64Micro", "is_aggregate": 0, "case_insensitive": 0, "alias_to": ""}
# {"name": "sumburConsistentHash", "is_aggregate": 0, "case_insensitive": 0, "alias_to": ""}
# {"name": "yandexConsistentHash", "is_aggregate": 0, "case_insensitive": 0, "alias_to": ""}
or using pandas:
from clickhouse_driver import Client
import pandas as pd
client = Client(host='localhost')
data = client.execute_iter('SELECT * FROM system.functions LIMIT 5', with_column_types=True)
columns = [column[0] for column in next(data)]
df = pd.DataFrame.from_records(data, columns=columns)
print(df.to_json(orient='records'))
# Result
# [{"name":"fromUnixTimestamp64Nano","is_aggregate":0,"case_insensitive":0,"alias_to":""},{"name":"toUnixTimestamp64Nano","is_aggregate":0,"case_insensitive":0,"alias_to":""},{"name":"toUnixTimestamp64Micro","is_aggregate":0,"case_insensitive":0,"alias_to":""},{"name":"sumburConsistentHash","is_aggregate":0,"case_insensitive":0,"alias_to":""},{"name":"yandexConsistentHash","is_aggregate":0,"case_insensitive":0,"alias_to":""}]
Solution 3:[3]
client.query_dataframe(sql).to_json(orient='records',default_handler=str)
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|---|
Solution 1 | nebuchadnezzar |
Solution 2 | |
Solution 3 | Suraj Rao |