> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-detect-table-modification.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.
# JupySQL と chDB
> Jupyter ノートブックと IPython で JupySQL を使用して chDB にクエリを実行する方法
[JupySQL](https://github.com/ploomber/jupysql) は、Jupyter ノートブックや IPython シェルで SQL を実行できる Python ライブラリです。
このガイドでは、chDB と JupySQL を使用してデータにクエリを実行する方法を学びます。
## セットアップ
まず、仮想環境を作成します：
```bash theme={null}
python -m venv .venv
source .venv/bin/activate
```
次に、JupySQL、IPython、JupyterLabをインストールします。
```bash theme={null}
pip install jupysql ipython jupyterlab
```
JupySQL は IPython で使用でき、次を実行して起動できます:
```bash theme={null}
ipython
```
または、Jupyter Lab では次を実行します。
```bash theme={null}
jupyter lab
```
JupyterLab を使用している場合は、このガイドの残りの手順に進む前に、ノートブックを作成してください。
## データセットのダウンロード
運賃、チップ、乗車地区を含む約300万件のタクシー乗車記録で構成されるニューヨーク市のタクシーデータセットを使用します。
乗車記録は複数の TSV ファイルに分割されているため、まずこれらをダウンロードします。
```python theme={null}
from urllib.request import urlretrieve
```
```python theme={null}
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` モジュールをインポートします。
```python theme={null}
from chdb import dbapi
```
次に、chDB への接続を作成します。
永続化したデータはすべて `taxi.chdb` ディレクトリに保存されます。
```python theme={null}
conn = dbapi.connect(path="taxi.chdb")
```
では、`sql`マジックをロードし、chDB への接続を確立します:
```python theme={null}
%load_ext sql
%sql conn --alias chdb
```
次に、クエリ結果が切り捨てられないよう、表示上限を表示します：
```python theme={null}
%config SqlMagic.displaylimit = None
```
## TSV ファイル内のデータをクエリする
`trips_` プレフィックスが付いた複数のファイルをダウンロードしました。
`DESCRIBE` 句を使用してスキーマを確認しましょう。
```python theme={null}
%%sql
DESCRIBE file('trips_*.gz')
SETTINGS describe_compact_output=1,
schema_inference_make_columns_nullable=0
```
```text theme={null}
+--------------------+----------+
| 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` クエリを実行し、データの内容を確認することもできます。
```python theme={null}
%%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
```
```text theme={null}
+------------+---------------------+----------------------------------------+---------------+-------------+------------+
| 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 データベースではデータはディスクに永続化されないため、先に別のデータベースを作成する必要があります。
```python theme={null}
%sql CREATE DATABASE taxi
```
次に、TSV ファイル内のデータ構造からスキーマを導出した `trips` テーブルを作成します。
`REPLACE` 句を使用して金額関連のカラムを `Float64` にキャストし、[`transform`](/ja/reference/functions/regular-functions/other-functions#transform) 関数を使用して数値の `pickup_borocode` カラムを人間が読める borough 名に変換します。
```python theme={null}
%%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
```
テーブル内のデータを簡単に確認してみましょう。
```python theme={null}
%sql SELECT count() AS trips FROM taxi.trips
```
```text theme={null}
+---------+
| trips |
+---------+
| 3000317 |
+---------+
```
300万件強の乗車記録に加え、2つ目のテーブルも取り込みましょう。
New York CityのTaxi & Limousine Commissionは市内をタクシーゾーンに分割しており、ルックアップファイルには各ゾーンと対応するboroughが記載されています。
そのファイルをダウンロードしましょう：
```python theme={null}
_ = urlretrieve(
f"{base}/taxi_zone_lookup.csv",
"taxi_zone_lookup.csv",
)
```
次に、CSV ファイルの内容に基づいて、`zones` というテーブルを作成します。
```python theme={null}
%%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
```
実行が完了したら、取り込んだデータを確認してみましょう：
```python theme={null}
%sql SELECT * FROM taxi.zones LIMIT 5
```
```text theme={null}
+------------+---------------+-------------------------+--------------+
| 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 で乗車した乗車記録数と、タクシーゾーンあたりの乗車記録数を調べます。
```python theme={null}
%%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
```
```text theme={null}
+---------------+------------+---------+----------------+
| 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` パラメータを指定すると、クエリの実行をスキップできます。
```python theme={null}
%%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) に変換されます。
次のクエリでは、平均チップ額が最も高い地区を算出します。
```python theme={null}
%sql SELECT * FROM tips_by_neighborhood ORDER BY avg_tip DESC LIMIT 5
```
```text theme={null}
+-----------------------------------+-------+---------+
| 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回の高額な乗車でも平均が大きく偏ります。
これらを除外しましょう。
## パラメータを使用したクエリ
クエリではパラメータも使用できます。
パラメータは通常の変数です。
```python theme={null}
min_trips = 10000
```
次に、クエリ内で `{{variable}}` 構文を使用できます。
次のクエリは、乗車記録数が 10,000 回を超える地域のうち、平均チップ額が最も高い地域を検索します。
```python theme={null}
%%sql
SELECT * FROM tips_by_neighborhood
WHERE trips >= {{min_trips}}
ORDER BY avg_tip DESC
LIMIT 10
```
```text theme={null}
+----------------------------------------+--------+---------+
| 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マイル未満の各乗車記録の距離を返すクエリを書いて (保存して) おきましょう。
これを使って、各距離バケットに該当する乗車記録数を数えるヒストグラムを作成できます。
```python theme={null}
%%sql --save trip_distances --no-execute
SELECT trip_distance
FROM taxi.trips
WHERE trip_distance > 0 AND trip_distance < 20
```
次に、以下を実行してヒストグラムを作成します。
```python theme={null}
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) ](https://www.youtube.com/watch?v=2wjl3OijCto)