Clickhouse does not return column headers

user_1443993 0
Viewed 3842

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:mypassG@domain.my:PORT/schema')
qry2 = '''select * from myschema.mytable order by a_date desc limit 10'''

dt = pd.read_sql_query(qry, cheng)
dt

enter image description here

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

clickhouse pandas sqlalchemy
3 Answers

Look at this question: Right way to implement pandas.read_sql with ClickHouse.

I cannot reproduce this behavior on the latest versions of modules:

sqlalchemy==1.3.16
sqlalchemy-clickhouse==0.1.5.post0
pandas==1.0.3

This code:

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine('clickhouse://default:@localhost/test')
query = 'select * from call_center'

dt = pd.read_sql_query(query, engine)

print(dt)

returns:

   cc_call_center_sk cc_call_center_id  ... cc_gmt_offset cc_tax_percentage
0                  1  AAAAAAAABAAAAAAA  ...          -5.0              0.11
1                  2  AAAAAAAACAAAAAAA  ...          -5.0              0.12
2                  3  AAAAAAAACAAAAAAA  ...          -5.0              0.01
3                  4  AAAAAAAAEAAAAAAA  ...          -5.0              0.05
4                  5  AAAAAAAAEAAAAAAA  ...          -5.0              0.12
5                  6  AAAAAAAAEAAAAAAA  ...          -5.0              0.11

[6 rows x 31 columns]

PyCharm DataFrame view looks good too:

enter image description here

user_3032980

Please check out this python package: https://pypi.org/project/pandahouse/

connection = {'host': 'http://clickhouse-host:8123',
              'database': 'test'
affected_rows = to_clickhouse(df, table='name', connection=connection)

df = read_clickhouse('SELECT * FROM {db}.table', index_col='id',
                     connection=connection)
user_92008340

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.

user_23502870
Related