Clickhouse does not return column headers

Question

I'm trying to get some relational data from clickhouse and play with in in pandas. It works, but pd.read_sql_query returns dataframe, where column names are the values of the first row. Instead I expected to see columns names as they named in relational table.

I tried the same with Postgress and it works properly.

cheng = create_engine('clickhouse://mylogin:[email protected]:PORT/schema')
qry2 = '''select * from myschema.mytable order by a_date desc limit 10'''

dt = pd.read_sql_query(qry, cheng)
dt

Header of returned dataframe columns consist values of the first row, returned from DB. I expected to see columns names instead.

Answer 1

Clickhouse has a python client that you can install with pip:

pip install clickhouse-connect

Getting the pandas dataframe is as simple as

client = clickhouse_connect.get_client(host, port)
df = client.query_df("select * from mytable")

More at https://clickhouse.com/docs/en/integrations/language-clients/python/driver-api/

Answer 2

You can get column labels in pandas dataframes using clickhouse-driver. Example shown below.

from clickhouse_driver import Client
import pandas
client = Client('localhost')
result, columns = client.execute('SELECT * FROM iris', 
                                 {'species': "Iris-setosa"},
                                 with_column_types=True)
df = pandas.DataFrame(result, columns=[tuple[0] for tuple in columns])
df.tail()

You'll see labels in the df.tail() output.