Export data from clickhouse

Question

Please, suggest the best way to export data from clickhouse using python.

Right now I am using this code but have an error stating that hotsname can't be reached.

from clickhouse_driver import Client
client=Client(host='http://ipaddress',user='user',password='pass',port=8123)
print(client.execute('select * from table limit 5'))

Are there any other ways to do that?

Answer 1

In the code sample you provided,

from clickhouse_driver import Client
client=Client(host='http://ipaddress',user='user',password='pass',port=8123)
print(client.execute('select * from table limit 5'))

you put http://ipaddress which is not a valid hostname, if you change this to a valid hostname, it should work fine.

Answer 2

clickhouse-driver communicates with ClickHouse over native protocol not HTTP, so:

• host should contain hostname or IP (not HTTP URL)
• port should be 9000 (or 9440 for secure connection)

I would rely on the generator function execute-iter to stream an export data:

from clickhouse_driver import Client

client = Client(host='localhost')

data = client.execute_iter('SELECT * FROM numbers(1 * 1000 * 1000)')
row_count = 0

for row in data:
    # do smth per row
    row_count += 1

print(f"Row count is {row_count}.")
# Row count is 1000000.