> ## Documentation Index
> Fetch the complete documentation index at: https://clickhouse.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.
# JupySQL と chDB
> chDB for Bun のインストール方法
export const Image = ({img, alt, size = "lg"}) => {
const normalizedSize = ["sm", "md", "lg"].includes(size) ? size : "lg";
return ;
};
[JupySQL](https://jupysql.readthedocs.io/en/latest/quick-start.html) は、Jupyter ノートブックや IPython シェルで SQL を実行できる Python ライブラリです。
このガイドでは、chDB と JupySQL を使用してデータにクエリを実行する方法を学びます。
VIDEO
## セットアップ
まず、仮想環境を作成します：
```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 を使用している場合は、このガイドの残りの手順に進む前に、ノートブックを作成してください。
## データセットのダウンロード
ここでは、選手とそのランキングの推移に関するメタデータを含む [Jeff Sackmann's tennis\_atp](https://github.com/JeffSackmann/tennis_atp) データセットの1つを使用します。
まずはランキングファイルをダウンロードしましょう。
```python theme={null}
from urllib.request import urlretrieve
```
```python theme={null}
files = ['00s', '10s', '20s', '70s', '80s', '90s', 'current']
base = "https://raw.githubusercontent.com/JeffSackmann/tennis_atp/master"
for file in files:
_ = urlretrieve(
f"{base}/atp_rankings_{file}.csv",
f"atp_rankings_{file}.csv",
)
```
## chDB と JupySQL の設定
次に、chDB 用の `dbapi` モジュールをインポートします。
```python theme={null}
from chdb import dbapi
```
次に、chDB への接続を作成します。
永続化したデータはすべて `atp.chdb` ディレクトリに保存されます。
```python theme={null}
conn = dbapi.connect(path="atp.chdb")
```
では、`sql`マジックをロードし、chDB への接続を確立します:
```python theme={null}
%load_ext sql
%sql conn --alias chdb
```
次に、クエリ結果が切り捨てられないよう、表示上限を表示します：
```python theme={null}
%config SqlMagic.displaylimit = None
```
\## CSVファイル内のデータをクエリする
`atp_rankings` プレフィックスを持つファイルをいくつかダウンロードしました。
`DESCRIBE` 句を使ってスキーマを確認してみましょう。
```python theme={null}
%%sql
DESCRIBE file('atp_rankings*.csv')
SETTINGS describe_compact_output=1,
schema_inference_make_columns_nullable=0
```
```text theme={null}
+--------------+-------+
| name | type |
+--------------+-------+
| ranking_date | Int64 |
| rank | Int64 |
| player | Int64 |
| points | Int64 |
+--------------+-------+
```
これらのファイルに対して `SELECT` クエリを直接実行して、データの内容を確認することもできます。
```python theme={null}
%sql SELECT * FROM file('atp_rankings*.csv') LIMIT 1
```
```text theme={null}
+--------------+------+--------+--------+
| ranking_date | rank | player | points |
+--------------+------+--------+--------+
| 20000110 | 1 | 101736 | 4135 |
+--------------+------+--------+--------+
```
データのフォーマットが少し変です。
その日付を整えて、`REPLACE` 句を使って整形した `ranking_date` を返しましょう：
```python theme={null}
%%sql
SELECT * REPLACE (
toDate(parseDateTime32BestEffort(toString(ranking_date))) AS ranking_date
)
FROM file('atp_rankings*.csv')
LIMIT 10
SETTINGS schema_inference_make_columns_nullable=0
```
```text theme={null}
+--------------+------+--------+--------+
| ranking_date | rank | player | points |
+--------------+------+--------+--------+
| 2000-01-10 | 1 | 101736 | 4135 |
| 2000-01-10 | 2 | 102338 | 2915 |
| 2000-01-10 | 3 | 101948 | 2419 |
| 2000-01-10 | 4 | 103017 | 2184 |
| 2000-01-10 | 5 | 102856 | 2169 |
| 2000-01-10 | 6 | 102358 | 2107 |
| 2000-01-10 | 7 | 102839 | 1966 |
| 2000-01-10 | 8 | 101774 | 1929 |
| 2000-01-10 | 9 | 102701 | 1846 |
| 2000-01-10 | 10 | 101990 | 1739 |
+--------------+------+--------+--------+
```
## CSVファイルをchDBにインポートする
次に、これらのCSVファイルのデータをテーブルに保存します。
デフォルトのデータベースはデータをディスクに永続保存しないため、まず別のデータベースを作成する必要があります。
```python theme={null}
%sql CREATE DATABASE atp
```
それでは次に、CSVファイル内のデータ構造に基づいてスキーマを決定する `rankings` という名前のテーブルを作成します：
```python theme={null}
%%sql
CREATE TABLE atp.rankings
ENGINE=MergeTree
ORDER BY ranking_date AS
SELECT * REPLACE (
toDate(parseDateTime32BestEffort(toString(ranking_date))) AS ranking_date
)
FROM file('atp_rankings*.csv')
SETTINGS schema_inference_make_columns_nullable=0
```
テーブル内のデータを手早く確認してみましょう：
```python theme={null}
%sql SELECT * FROM atp.rankings LIMIT 10
```
```text theme={null}
+--------------+------+--------+--------+
| ranking_date | rank | player | points |
+--------------+------+--------+--------+
| 2000-01-10 | 1 | 101736 | 4135 |
| 2000-01-10 | 2 | 102338 | 2915 |
| 2000-01-10 | 3 | 101948 | 2419 |
| 2000-01-10 | 4 | 103017 | 2184 |
| 2000-01-10 | 5 | 102856 | 2169 |
| 2000-01-10 | 6 | 102358 | 2107 |
| 2000-01-10 | 7 | 102839 | 1966 |
| 2000-01-10 | 8 | 101774 | 1929 |
| 2000-01-10 | 9 | 102701 | 1846 |
| 2000-01-10 | 10 | 101990 | 1739 |
+--------------+------+--------+--------+
```
問題なさそうです。予想どおり、出力は CSV ファイルを直接クエリした場合と同じです。
プレイヤーのメタデータについても、同じ手順で進めます。
今回はデータが 1 つの CSV ファイルにまとまっているので、そのファイルをダウンロードしましょう:
```python theme={null}
_ = urlretrieve(
f"{base}/atp_players.csv",
"atp_players.csv",
)
```
次に、CSVファイルの内容に基づいて、`players` という名前のテーブルを作成します。
また、`dob` フィールドが `Date32` 型になるように整形します。
> ClickHouse では、`Date` 型は 1970 年以降の日付しかサポートしていません。`dob` カラムには 1970 年より前の日付が含まれているため、代わりに `Date32` 型を使用します。
```python theme={null}
%%sql
CREATE TABLE atp.players
Engine=MergeTree
ORDER BY player_id AS
SELECT * REPLACE (
makeDate32(
toInt32OrNull(substring(toString(dob), 1, 4)),
toInt32OrNull(substring(toString(dob), 5, 2)),
toInt32OrNull(substring(toString(dob), 7, 2))
)::Nullable(Date32) AS dob
)
FROM file('atp_players.csv')
SETTINGS schema_inference_make_columns_nullable=0
```
実行が完了したら、取り込んだデータを確認してみましょう。
```python theme={null}
%sql SELECT * FROM atp.players LIMIT 10
```
```text theme={null}
+-----------+------------+-----------+------+------------+-----+--------+-------------+
| player_id | name_first | name_last | hand | dob | ioc | height | wikidata_id |
+-----------+------------+-----------+------+------------+-----+--------+-------------+
| 100001 | Gardnar | Mulloy | R | 1913-11-22 | USA | 185 | Q54544 |
| 100002 | Pancho | Segura | R | 1921-06-20 | ECU | 168 | Q54581 |
| 100003 | Frank | Sedgman | R | 1927-10-02 | AUS | 180 | Q962049 |
| 100004 | Giuseppe | Merlo | R | 1927-10-11 | ITA | 0 | Q1258752 |
| 100005 | Richard | Gonzalez | R | 1928-05-09 | USA | 188 | Q53554 |
| 100006 | Grant | Golden | R | 1929-08-21 | USA | 175 | Q3115390 |
| 100007 | Abe | Segal | L | 1930-10-23 | RSA | 0 | Q1258527 |
| 100008 | Kurt | Nielsen | R | 1930-11-19 | DEN | 0 | Q552261 |
| 100009 | Istvan | Gulyas | R | 1931-10-14 | HUN | 0 | Q51066 |
| 100010 | Luis | Ayala | R | 1932-09-18 | CHI | 170 | Q1275397 |
+-----------+------------+-----------+------+------------+-----+--------+-------------+
```
## chDBをクエリする
データのインジェストは完了しました。いよいよ楽しいパート、データのクエリです！
テニス選手は、出場したトーナメントでの成績に応じてポイントを獲得します。
各選手のポイントは、直近52週間のローリング期間で集計されます。
各選手が積み上げた最大ポイントと、その時点での順位を見つけるクエリを書いてみましょう。
```python theme={null}
%%sql
SELECT name_first, name_last,
max(points) as maxPoints,
argMax(rank, points) as rank,
argMax(ranking_date, points) as date
FROM atp.players
JOIN atp.rankings ON rankings.player = players.player_id
GROUP BY ALL
ORDER BY maxPoints DESC
LIMIT 10
```
```text theme={null}
+------------+-----------+-----------+------+------------+
| name_first | name_last | maxPoints | rank | date |
+------------+-----------+-----------+------+------------+
| Novak | Djokovic | 16950 | 1 | 2016-06-06 |
| Rafael | Nadal | 15390 | 1 | 2009-04-20 |
| Andy | Murray | 12685 | 1 | 2016-11-21 |
| Roger | Federer | 12315 | 1 | 2012-10-29 |
| Daniil | Medvedev | 10780 | 2 | 2021-09-13 |
| Carlos | Alcaraz | 9815 | 1 | 2023-08-21 |
| Dominic | Thiem | 9125 | 3 | 2021-01-18 |
| Jannik | Sinner | 8860 | 2 | 2024-05-06 |
| Stefanos | Tsitsipas | 8350 | 3 | 2021-09-20 |
| Alexander | Zverev | 8240 | 4 | 2021-08-23 |
+------------+-----------+-----------+------+------------+
```
この一覧を見ると、そのポイント総数では1位ではないにもかかわらず、多くのポイントを獲得している選手がいるのは、なかなか興味深い点です。
## クエリの保存
`%%sql` マジックと同じ行で `--save` パラメータを使用すると、クエリを保存できます。
`--no-execute` パラメータを指定すると、クエリは実行されません。
```python theme={null}
%%sql --save best_points --no-execute
SELECT name_first, name_last,
max(points) as maxPoints,
argMax(rank, points) as rank,
argMax(ranking_date, points) as date
FROM atp.players
JOIN atp.rankings ON rankings.player = players.player_id
GROUP BY ALL
ORDER BY maxPoints DESC
```
保存クエリを実行すると、実行前に Common Table Expression (CTE) に変換されます。
次のクエリでは、プレイヤーが1位だったときに獲得したポイントの最大値を計算します。
```python theme={null}
%sql select * FROM best_points WHERE rank=1
```
```text theme={null}
+-------------+-----------+-----------+------+------------+
| name_first | name_last | maxPoints | rank | date |
+-------------+-----------+-----------+------+------------+
| Novak | Djokovic | 16950 | 1 | 2016-06-06 |
| Rafael | Nadal | 15390 | 1 | 2009-04-20 |
| Andy | Murray | 12685 | 1 | 2016-11-21 |
| Roger | Federer | 12315 | 1 | 2012-10-29 |
| Carlos | Alcaraz | 9815 | 1 | 2023-08-21 |
| Pete | Sampras | 5792 | 1 | 1997-08-11 |
| Andre | Agassi | 5652 | 1 | 1995-08-21 |
| Lleyton | Hewitt | 5205 | 1 | 2002-08-12 |
| Gustavo | Kuerten | 4750 | 1 | 2001-09-10 |
| Juan Carlos | Ferrero | 4570 | 1 | 2003-10-20 |
| Stefan | Edberg | 3997 | 1 | 1991-02-25 |
| Jim | Courier | 3973 | 1 | 1993-08-23 |
| Ivan | Lendl | 3420 | 1 | 1990-02-26 |
| Ilie | Nastase | 0 | 1 | 1973-08-27 |
+-------------+-----------+-----------+------+------------+
```
## パラメータを使ったクエリ
クエリでもパラメータを使用できます。
パラメータは通常の変数と同じです。
```python theme={null}
rank = 10
```
そして、クエリでは `{{variable}}` 構文を使用できます。
次のクエリは、選手が初めてトップ10入りしてから最後にトップ10入りするまでの日数が最も短かった選手を見つけます。
```python theme={null}
%%sql
SELECT name_first, name_last,
MIN(ranking_date) AS earliest_date,
MAX(ranking_date) AS most_recent_date,
most_recent_date - earliest_date AS days,
1 + (days/7) AS weeks
FROM atp.rankings
JOIN atp.players ON players.player_id = rankings.player
WHERE rank <= {{rank}}
GROUP BY ALL
ORDER BY days
LIMIT 10
```
```text theme={null}
+------------+-----------+---------------+------------------+------+-------+
| name_first | name_last | earliest_date | most_recent_date | days | weeks |
+------------+-----------+---------------+------------------+------+-------+
| Alex | Metreveli | 1974-06-03 | 1974-06-03 | 0 | 1 |
| Mikael | Pernfors | 1986-09-22 | 1986-09-22 | 0 | 1 |
| Felix | Mantilla | 1998-06-08 | 1998-06-08 | 0 | 1 |
| Wojtek | Fibak | 1977-07-25 | 1977-07-25 | 0 | 1 |
| Thierry | Tulasne | 1986-08-04 | 1986-08-04 | 0 | 1 |
| Lucas | Pouille | 2018-03-19 | 2018-03-19 | 0 | 1 |
| John | Alexander | 1975-12-15 | 1975-12-15 | 0 | 1 |
| Nicolas | Massu | 2004-09-13 | 2004-09-20 | 7 | 2 |
| Arnaud | Clement | 2001-04-02 | 2001-04-09 | 7 | 2 |
| Ernests | Gulbis | 2014-06-09 | 2014-06-23 | 14 | 3 |
+------------+-----------+---------------+------------------+------+-------+
```
## ヒストグラムの作成
JupySQL には、限定的ではありますが、グラフ作成機能もあります。
箱ひげ図やヒストグラムを作成できます。
ここではヒストグラムを作成しますが、その前に、まず各プレイヤーがトップ100内で獲得した順位を算出するクエリを書いて (保存して) おきましょう。
これを使って、各順位を獲得したプレイヤー数を数えるヒストグラムを作成できます。
```python theme={null}
%%sql --save players_per_rank --no-execute
select distinct player, rank
FROM atp.rankings
WHERE rank <= 100
```
次に、以下を実行してヒストグラムを作成します。
```python theme={null}
from sql.ggplot import ggplot, geom_histogram, aes
plot = (
ggplot(
table="players_per_rank",
with_="players_per_rank",
mapping=aes(x="rank", fill="#69f0ae", color="#fff"),
) + geom_histogram(bins=100)
)
```