JupySQL と chDB

JupySQL は、Jupyter ノートブックや IPython シェルで SQL を実行できる Python ライブラリです。このガイドでは、chDB と JupySQL を使用してデータにクエリを実行する方法を学びます。

セットアップ

まず、仮想環境を作成します：

python -m venv .venv
source .venv/bin/activate

次に、JupySQL、IPython、JupyterLabをインストールします。

pip install jupysql ipython jupyterlab

JupySQL は IPython で使用でき、次を実行して起動できます:

ipython

または、Jupyter Lab では次を実行します。

jupyter lab

JupyterLab を使用している場合は、このガイドの残りの手順に進む前に、ノートブックを作成してください。

データセットのダウンロード

運賃、チップ、乗車地区を含む約300万件のタクシー乗車記録で構成されるニューヨーク市のタクシーデータセットを使用します。乗車記録は複数の TSV ファイルに分割されているため、まずこれらをダウンロードします。

from urllib.request import urlretrieve

base = "https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi"
for n in range(3):
  _ = urlretrieve(
    f"{base}/trips_{n}.gz",
    f"trips_{n}.gz",
  )

chDB と JupySQL の設定

次に、chDB 用の dbapi モジュールをインポートします。

from chdb import dbapi

次に、chDB への接続を作成します。永続化したデータはすべて taxi.chdb ディレクトリに保存されます。

conn = dbapi.connect(path="taxi.chdb")

では、sqlマジックをロードし、chDB への接続を確立します:

%load_ext sql
%sql conn --alias chdb

次に、クエリ結果が切り捨てられないよう、表示上限を表示します：

%config SqlMagic.displaylimit = None

TSV ファイル内のデータをクエリする

trips_ プレフィックスが付いた複数のファイルをダウンロードしました。 DESCRIBE 句を使用してスキーマを確認しましょう。

%%sql
DESCRIBE file('trips_*.gz')
SETTINGS describe_compact_output=1,
         schema_inference_make_columns_nullable=0

+--------------------+----------+
|        name        |   type   |
+--------------------+----------+
|      trip_id       |  Int64   |
|     vendor_id      |  Int64   |
|    pickup_date     |   Date   |
|  pickup_datetime   | DateTime |
|    dropoff_date    |   Date   |
|  dropoff_datetime  | DateTime |
| store_and_fwd_flag |  Int64   |
|    rate_code_id    |  Int64   |
+--------------------+----------+
(40 more rows)

これらのファイルに対して直接 SELECT クエリを実行し、データの内容を確認することもできます。

%%sql
SELECT trip_id, pickup_datetime, pickup_ntaname,
       trip_distance, fare_amount, tip_amount
FROM file('trips_*.gz')
LIMIT 3
SETTINGS schema_inference_make_columns_nullable=0

+------------+---------------------+----------------------------------------+---------------+-------------+------------+
|  trip_id   |   pickup_datetime   |             pickup_ntaname             | trip_distance | fare_amount | tip_amount |
+------------+---------------------+----------------------------------------+---------------+-------------+------------+
| 1199999902 | 2015-07-07 19:45:07 |      Lenox Hill-Roosevelt Island       |      2.59     |     14.5    |    3.26    |
| 1199999919 | 2015-07-07 20:26:29 |                Airport                 |      2.4      |      9      |     0      |
| 1199999944 | 2015-07-07 21:25:09 | SoHo-TriBeCa-Civic Center-Little Italy |      5.13     |      20     |     3      |
+------------+---------------------+----------------------------------------+---------------+-------------+------------+

スキーマを振り返ると、金額に関連するカラムであるtrip_distance、fare_amount、tip_amountは、数値型ではなくStringとして推論されています。データをテーブルにインポートする際に、これらを修正します。

chDB への TSV ファイルのインポート

次に、これらの TSV ファイルのデータをテーブルに格納します。 default データベースではデータはディスクに永続化されないため、先に別のデータベースを作成する必要があります。

%sql CREATE DATABASE taxi

次に、TSV ファイル内のデータ構造からスキーマを導出した trips テーブルを作成します。 REPLACE 句を使用して金額関連のカラムを Float64 にキャストし、transform 関数を使用して数値の pickup_borocode カラムを人間が読める borough 名に変換します。

%%sql
CREATE TABLE taxi.trips
ENGINE = MergeTree
ORDER BY pickup_datetime AS
SELECT * REPLACE (
    toFloat64OrZero(trip_distance) AS trip_distance,
    toFloat64OrZero(fare_amount) AS fare_amount,
    toFloat64OrZero(tip_amount) AS tip_amount,
    toFloat64OrZero(total_amount) AS total_amount
  ),
  transform(pickup_borocode, [1, 2, 3, 4, 5],
            ['Manhattan', 'Bronx', 'Brooklyn', 'Queens', 'Staten Island'],
            'Unknown') AS pickup_borough
FROM file('trips_*.gz')
SETTINGS schema_inference_make_columns_nullable=0

テーブル内のデータを簡単に確認してみましょう。

%sql SELECT count() AS trips FROM taxi.trips

+---------+
|  trips  |
+---------+
| 3000317 |
+---------+

300万件強の乗車記録に加え、2つ目のテーブルも取り込みましょう。 New York CityのTaxi & Limousine Commissionは市内をタクシーゾーンに分割しており、ルックアップファイルには各ゾーンと対応するboroughが記載されています。そのファイルをダウンロードしましょう：

_ = urlretrieve(
    f"{base}/taxi_zone_lookup.csv",
    "taxi_zone_lookup.csv",
)

次に、CSV ファイルの内容に基づいて、zones というテーブルを作成します。

%%sql
CREATE TABLE taxi.zones
ENGINE = MergeTree
ORDER BY LocationID AS
SELECT * FROM file('taxi_zone_lookup.csv')
SETTINGS schema_inference_make_columns_nullable=0

実行が完了したら、取り込んだデータを確認してみましょう：

%sql SELECT * FROM taxi.zones LIMIT 5

+------------+---------------+-------------------------+--------------+
| LocationID |    Borough    |           Zone          | service_zone |
+------------+---------------+-------------------------+--------------+
|     1      |      EWR      |      Newark Airport     |     EWR      |
|     2      |     Queens    |       Jamaica Bay       |  Boro Zone   |
|     3      |     Bronx     | Allerton/Pelham Gardens |  Boro Zone   |
|     4      |   Manhattan   |      Alphabet City      | Yellow Zone  |
|     5      | Staten Island |      Arden Heights      |  Boro Zone   |
+------------+---------------+-------------------------+--------------+

chDB をクエリする

データのインジェストが完了したので、いよいよデータをクエリしてみましょう！各 borough は、異なる数のタクシーゾーンで構成されています。 2 つのテーブルを結合するクエリを作成し、各 borough で乗車した乗車記録数と、タクシーゾーンあたりの乗車記録数を調べます。

%%sql
SELECT pickup_borough AS borough,
       zone_count,
       count() AS trips,
       round(count() / zone_count) AS trips_per_zone
FROM taxi.trips
JOIN (
    SELECT Borough, count() AS zone_count
    FROM taxi.zones
    GROUP BY Borough
) AS zones ON pickup_borough = zones.Borough
GROUP BY borough, zone_count
ORDER BY trips DESC

+---------------+------------+---------+----------------+
|    borough    | zone_count |  trips  | trips_per_zone |
+---------------+------------+---------+----------------+
|   Manhattan   |     69     | 2713990 |    39333.0     |
|     Queens    |     69     |  187737 |     2721.0     |
|    Brooklyn   |     61     |  52445  |     860.0      |
|    Unknown    |     2      |  43802  |    21901.0     |
|     Bronx     |     43     |   2300  |      53.0      |
| Staten Island |     20     |    43   |      2.0       |
+---------------+------------+---------+----------------+

マンハッタンとクイーンズのタクシーゾーン数は同じですが、マンハッタンでは乗車記録数が14倍以上です。

クエリの保存

%%sql マジックと同じ行で --save パラメータを使用すると、クエリを保存できます。 --no-execute パラメータを指定すると、クエリの実行をスキップできます。

%%sql --save tips_by_neighborhood --no-execute
SELECT pickup_ntaname AS neighborhood,
       count() AS trips,
       round(avg(tip_amount), 2) AS avg_tip
FROM taxi.trips
WHERE fare_amount > 0 AND pickup_ntaname != ''
GROUP BY neighborhood
ORDER BY avg_tip DESC

保存クエリを実行すると、実行前に共通テーブル式 (CTE) に変換されます。次のクエリでは、平均チップ額が最も高い地区を算出します。

%sql SELECT * FROM tips_by_neighborhood ORDER BY avg_tip DESC LIMIT 5

+-----------------------------------+-------+---------+
|            neighborhood           | trips | avg_tip |
+-----------------------------------+-------+---------+
| New Springville-Bloomfield-Travis |   2   |   35.0  |
|       New Dorp-Midland Beach      |   2   |  23.74  |
|      New Brighton-Silver Lake     |   3   |  16.67  |
|           Newark Airport          |  201  |  11.89  |
|   Grymes Hill-Clifton-Fox Hills   |   1   |   11.3  |
+-----------------------------------+-------+---------+

上位のエントリは乗車記録数がごく少ない地域のため、1回の高額な乗車でも平均が大きく偏ります。これらを除外しましょう。

パラメータを使用したクエリ

クエリではパラメータも使用できます。パラメータは通常の変数です。

min_trips = 10000

次に、クエリ内で {{variable}} 構文を使用できます。次のクエリは、乗車記録数が 10,000 回を超える地域のうち、平均チップ額が最も高い地域を検索します。

%%sql
SELECT * FROM tips_by_neighborhood
WHERE trips >= {{min_trips}}
ORDER BY avg_tip DESC
LIMIT 10

+----------------------------------------+--------+---------+
|              neighborhood              | trips  | avg_tip |
+----------------------------------------+--------+---------+
|                Airport                 | 151171 |   4.92  |
|   Battery Park City-Lower Manhattan    | 89110  |   2.16  |
|         North Side-South Side          | 11152  |   1.79  |
| SoHo-TriBeCa-Civic Center-Little Italy | 144887 |   1.65  |
|               Chinatown                | 54780  |   1.65  |
|            Lower East Side             | 15753  |   1.64  |
|              East Village              | 99881  |   1.61  |
|  Hunters Point-Sunnyside-West Maspeth  | 10054  |   1.58  |
|        Turtle Bay-East Midtown         | 197035 |   1.57  |
|              West Village              | 210369 |   1.54  |
+----------------------------------------+--------+---------+

空港から市内への長距離送迎では、チップ額が積み上がるため、他を大きく引き離して最も多くなります。

ヒストグラムの作成

JupySQL には、限定的ではありますが、グラフ作成機能もあります。箱ひげ図やヒストグラムを作成できます。ここではヒストグラムを作成しますが、その前に、まず20マイル未満の各乗車記録の距離を返すクエリを書いて (保存して) おきましょう。これを使って、各距離バケットに該当する乗車記録数を数えるヒストグラムを作成できます。

%%sql --save trip_distances --no-execute
SELECT trip_distance
FROM taxi.trips
WHERE trip_distance > 0 AND trip_distance < 20

次に、以下を実行してヒストグラムを作成します。

from sql.ggplot import ggplot, geom_histogram, aes

plot = (
  ggplot(
    table="trip_distances",
    with_="trip_distances",
    mapping=aes(x="trip_distance", fill="#69f0ae", color="#fff"),
  ) + geom_histogram(bins=50)
)

ほとんどの乗車記録は1〜3マイルと短く、空港へ向かう乗車記録は長距離側に裾を引いています。

chDB を使用した JupySQL 入門 (YouTube)

はじめに

各言語向けインテグレーション

DataStore

設定

デバッグ

開発者ガイド

技術リファレンス

セットアップ

データセットのダウンロード

chDB と JupySQL の設定

TSV ファイル内のデータをクエリする

chDB への TSV ファイルのインポート

chDB をクエリする

クエリの保存

パラメータを使用したクエリ

ヒストグラムの作成

​セットアップ

​データセットのダウンロード

​chDB と JupySQL の設定

​TSV ファイル内のデータをクエリする

​chDB への TSV ファイルのインポート

​chDB をクエリする

​クエリの保存

​パラメータを使用したクエリ

​ヒストグラムの作成

​関連情報

セットアップ

データセットのダウンロード

chDB と JupySQL の設定

TSV ファイル内のデータをクエリする

chDB への TSV ファイルのインポート

chDB をクエリする

クエリの保存

パラメータを使用したクエリ

ヒストグラムの作成

関連情報