Set up docs MCPJupySQL 是一个 Python 库，可让你在 Jupyter 笔记本和 IPython shell 中运行 SQL。 在本指南中，我们将学习如何使用 chDB 和 JupySQL 查询数据。
准备工作
先创建一个虚拟环境：
python -m venv .venv
source .venv/bin/activate接下来，我们将安装 JupySQL、IPython 和 Jupyter Lab：
pip install jupysql ipython jupyterlab我们可以在 IPython 中使用 JupySQL，可通过运行以下命令来启动：
ipython或者在 Jupyter Lab 中运行：
jupyter lab下载数据集
我们将使用纽约市出租车数据集，其中包含约 300 万条出租车行程记录，以及每次行程的车费、小费和上车社区信息。 这些行程记录分布在多个 TSV 文件中，因此先下载这些文件：
from urllib.request import urlretrievebase = "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 子句查看其 schema：
%%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 |
+------------+---------------------+----------------------------------------+---------------+-------------+------------+回顾 schema 可以看到，几个与金额相关的列——trip_distance、fare_amount 和 tip_amount——被推断为 String，而不是数值类型。
将数据导入表时，我们会将这些列清理好。
将 TSV 文件导入 chDB
现在，我们将这些 TSV 文件中的数据存储到表中。 默认数据库不会将数据持久化到磁盘，因此需要先创建另一个数据库：
%sql CREATE DATABASE taxi现在，我们将创建一个名为 trips 的表，其 schema 根据 TSV 文件中的数据结构推导而来。
我们将使用 REPLACE 子句将金额相关列转换为 Float64，并使用 transform 函数将数值型 pickup_borocode 列转换为易读的行政区名称：
%%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 万条行程记录——再引入第二张表。 纽约市出租车和豪华轿车委员会将全市划分为出租车区域，一个查找文件将每个区域映射到其所属行政区。 下载该文件：
_ = 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
数据摄取已完成，现在到了最有趣的部分——查询数据！
每个行政区包含的出租车区域数量各不相同。 我们将编写一个查询，联接这两个表，以找出每个行政区的上车行程数，以及平均每个出租车区域的行程数：
%%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 |
+-----------------------------------+-------+---------+排名靠前的社区行程数都很少，因此一次高额行程就会拉高平均值。 我们把这些社区过滤掉。
使用参数查询
我们也可以在查询中使用参数。 参数就是普通变量：
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)
)大多数行程都是一到三英里的短途，只有少数较长的行程会延伸到机场。