> ## 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
> 如何安装适用于 Bun 的 chDB
export const Image = ({img, alt, size = "lg"}) => {
const normalizedSize = ["sm", "md", "lg"].includes(size) ? size : "lg";
return ;
};
[JupySQL](https://jupysql.ploomber.io/en/latest/quick-start.html) 是一个 Python 库，可让你在 Jupyter 笔记本和 IPython shell 中运行 SQL。
在本指南中，我们将学习如何使用 chDB 和 JupySQL 查询数据。
## 准备工作
先创建一个虚拟环境：
```bash theme={null}
python -m venv .venv
source .venv/bin/activate
```
接下来，我们将安装 JupySQL、IPython 和 Jupyter Lab：
```bash theme={null}
pip install jupysql ipython jupyterlab
```
我们可以在 IPython 中使用 JupySQL，可通过运行以下命令来启动：
```bash theme={null}
ipython
```
或者在 Jupyter Lab 中运行：
```bash theme={null}
jupyter lab
```
如果你使用的是 Jupyter Lab，则需要先创建一个笔记本，然后再按照本指南的后续步骤操作。
## 下载数据集
我们将使用 [Jeff Sackmann's tennis\_atp](https://github.com/JeffSackmann/tennis_atp) 数据集中的一个，该数据集包含球员信息及其排名随时间变化的元数据。
先从下载排名文件开始：
```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` magic，并建立与 chDB 的连接：
```python theme={null}
%load_ext sql
%sql conn --alias chdb
```
接下来，我们将显示结果显示限制，以免查询结果被截断：
```python theme={null}
%config SqlMagic.displaylimit = None
```
\## 查询 CSV 文件中的数据
我们已经下载了一批带有 `atp_rankings` 前缀的文件。
下面使用 `DESCRIBE` 语句来查看 schema：
```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
```
现在我们将创建一个名为 `rankings` 的表，其 schema 将根据 CSV 文件中数据的结构推断得出：
```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 文件时相同。
接下来，我们对球员元数据执行相同的流程。
这次所有数据都在一个 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
```
运行已保存查询时，系统会先将其转换为公用表表达式 (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)
)
```