JupySQL and chDB
JupySQL は、Jupyter ノートブックや IPython シェルで SQL を実行するための Python ライブラリです。このガイドでは、chDB と JupySQL を使用してデータをクエリする方法を学びます。
セットアップ
まず、仮想環境を作成しましょう:
その後、JupySQL、IPython、Jupyter Lab をインストールします:
IPython では JupySQL を使用でき、次のコマンドを実行して起動できます:
または、Jupyter Lab を次のコマンドで起動できます:
Jupyter Lab を使用している場合は、ガイドの残りの部分をフォローする前にノートブックを作成する必要があります。
データセットのダウンロード
Jeff Sackmann の tennis_atp データセットの1つを使用します。このデータセットは、選手とそのランキングに関するメタデータが含まれています。まず、ランキングファイルをダウンロードします:
chDB と JupySQL の設定
次に、chDB の dbapi モジュールをインポートします:
そして、chDB 接続を作成します。永続化するデータは atp.chdb ディレクトリに保存されます:
次に、sql マジックを読み込み、chDB への接続を作成します:
クエリの結果が切り捨てられないように、表示制限を設定します:
CSV ファイル内のデータをクエリする
atp_rankings プレフィックスのついた複数のファイルをダウンロードしました。DESCRIBE 句を使用してスキーマを理解しましょう:
これらのファイルに対して直接 SELECT クエリを書いて、データがどのようなものか見てみましょう:
データの形式は少し変わっています。日付をきれいにして、REPLACE 句を使用してクリーンアップした ranking_date を返します:
chDB に CSV ファイルをインポートする
次に、これらの CSV ファイルからデータをテーブルに格納します。デフォルトのデータベースはディスク上にデータを永続化しないため、まず別のデータベースを作成する必要があります:
そして、CSV ファイルのデータの構造に基づいて rankings という名前のテーブルを作成します:
テーブル内のデータを簡単にチェックします:
良さそうです - 出力は予想通り、CSV ファイルを直接クエリしたときと同じです。
選手のメタデータについても同じプロセスを実行します。今回はデータが1つの CSV ファイルにすべて入っているので、そのファイルをダウンロードしましょう:
その後、CSV ファイルの内容に基づいて players という名前のテーブルを作成します。dob フィールドもクリーンアップして、Date32 型にします。
ClickHouse では、
Date型は 1970 年以降の日付のみをサポートしています。dob列には 1970 年以前の日付が含まれているため、Date32型を代わりに使用します。
これが実行されると、取り込んだデータを確認できます:
chDB をクエリする
データの取り込みが完了し、次は楽しい部分 - データをクエリします！
テニス選手は、参加するトーナメントでのパフォーマンスに基づいてポイントを受け取ります。各選手のポイントは、52 週間のローリング期間にわたって集計されます。各選手が獲得した最大ポイントと、その時のランキングを見つけるクエリを書きます:
このリストにある選手のうち、ポイントが1位でなくても多くのポイントを累積している選手がいるのは非常に興味深いです。
クエリを保存する
--save パラメータを使用して同じ行にクエリを保存できます。--no-execute パラメータは、クエリの実行をスキップすることを意味します。
保存されたクエリを実行すると、実行前に共通テーブル式（CTE）に変換されます。次のクエリでは、選手がランキング1位の時に達成した最大ポイントを計算します:
パラメータを使ったクエリ
クエリ内でパラメータを使用することもできます。パラメータは通常の変数です:
そして、{{variable}} 構文をクエリ内で使用できます。次のクエリは、選手が最初にトップ 10 にランキングされてから最後にランキングがあるまでの日数が最も少ない選手を見つけます:
ヒストグラムのプロット
JupySQL には限られたチャート機能もあります。ボックスプロットやヒストグラムを作成できます。
ヒストグラムを作成しますが、まずは各プレイヤーが達成したトップ100のランキングを計算するクエリを書いて（保存します）、これを使ってヒストグラムを作成します:
次に、以下のコードを実行してヒストグラムを作成できます:
