PythonでClickHouseのSELECT結果をpandas.DataFrameに入れる

Qiitaからの移植です。

はじめに

ClickHouseとはカラム指向型DB管理システムの1つです。日本ではまだまだマイナーな存在【要出典】です。詳しくは、公式サイトを見たり、Qiita内を検索したり、Google先生を頼ったりして、調べてください。

今回は、Pythonで 1. ClickHouseからSELECTクエリでデータを取得 2. 取得データをpandas.DataFrameに入れる を実現する方法を2つ紹介したいと思います。

（その1）pandahouseを使用する方法

pandahouseというライブラリを使用します。名前の通り、ClickHouseのSELECT結果をpandas.DataFrameに入れるためのライブラリです。インストール方法や使用方法は、GitHubリポジトリのREADME.mdを読んでください。簡単なので、ここで改めて説明しません。

ただしこのライブラリでは、HTTPインターフェースを利用してClickHouseへの問い合わせしています。データ取得はClickHouseにネイティブに接続して行いたいという場合は、次のセクションを見てください。

（その2）clickhouse-driverを使用する方法

ClickHouseにはネイティブ接続して問い合わせたいという場合はこちら。ClickHouse用のドライバーとしてclickhouse-driver¹を使用します。

コード例は次の通りです。

import pandas as pd
from clickhouse_driver import Client

# ClickHouseに接続するためのClientオブジェクトを作成
client = Client(
    host='localhost',
    user='someone',
    password='XXXXXXXX',
    port=9000,
    database='hoge'
)

# カラムの情報付きでSELECT結果を取得
query = 'SELECT * FROM test'
results, column_types = client.execute(query, with_column_types=True)

# カラムの情報からカラム名だけを抽出
columns = [ct[0] for ct in column_types]

# pandas.DataFrameに変換
df = pd.DataFrame(results, columns=columns)

サンプル用のデータを用意するのが面倒だったので、コード中のコメントだけで説明を済ませます。すみません…

(追記その1)pandas.read_sql_queryを使用する

そもそもpandasにはDBでのselect結果をDataFrameにするための関数pandas.read_sql_queryがあるので、これを利用します。

この関数を使用するためにはClickHouseに接続できるSQLAlchemy connectableオブジェクトを用意する必要があります。幸運なことにsqlalchemy-clickhouseでそのオブジェクトを生成できます。

コード例は次の通りです。事前にsqlalchemyとsqlalchemy-clickhouseをインストールする必要があります。

import pandas
from sqlalchemy.engine import create_engine

engine = create_engine('clickhouse://username:password@hostname:port/database')
query = 'SELECT * FROM test'
df = pandas.read_sql_query(query, engine)

情報提供者: @knoguchi

(追記その2)SQLAlchemyでのselect結果を使用する

ORMのSQLAlchemyでselectした結果を使って、pandas.DataFrameオブジェクトを生成する方法です。追記その1と同様に、sqlalchemyとsqlalchemy-clickhouseをインストールする必要があります。

from sqlalchemy.engine import create_engine
from sqlalchemy import Table, MetaData

engine = create_engine('clickhouse://username:password@hostname:port/database')
test = Table('test', MetaData(bind=engine), autoload=True)
cursor = engine.execute(test.select())

df = pandas.DataFrame(cursor.fetchall(), columns=cursor.keys())

情報提供者: @knoguchi

最後に

• 手抜きの記事ですみません
• 他に良い方法があったら教えてください

---

1. GitHubリポジトリのREADME.md以外には、公式ドキュメントがないっぽいです。あったら教えてください。↩