JupySQL 与 chDB

JupySQL 是一个 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

如果你使用的是 Jupyter Lab，则需要先创建一个笔记本，然后再按照本指南的后续步骤操作。

下载数据集

我们将使用纽约市出租车数据集，其中包含约 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 子句查看其 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)
)

大多数行程都是一到三英里的短途，只有少数较长的行程会延伸到机场。

使用 chDB 和 JupySQL 入门 (YouTube)

入门

语言集成

DataStore

配置

调试

开发者指南

技术参考文档

准备工作

下载数据集

配置 chDB 和 JupySQL

查询 TSV 文件中的数据

将 TSV 文件导入 chDB

查询 chDB

保存查询

使用参数查询

绘制直方图

​准备工作

​下载数据集

​配置 chDB 和 JupySQL

​查询 TSV 文件中的数据

​将 TSV 文件导入 chDB

​查询 chDB

​保存查询

​使用参数查询

​绘制直方图

​相关内容

准备工作

下载数据集

配置 chDB 和 JupySQL

查询 TSV 文件中的数据

将 TSV 文件导入 chDB

查询 chDB

保存查询

使用参数查询

绘制直方图

相关内容