跳到主要内容
跳到主要内容

在 ClickHouse 中使用 JupySQL

Community Maintained

在本指南中，我们将展示如何将 JupySQL 与 ClickHouse 集成。

我们将使用 JupySQL 针对 ClickHouse 运行查询。 数据加载完成后，我们将通过基于 SQL 的绘图对其进行可视化。

JupySQL 与 ClickHouse 之间的集成是通过使用 clickhouse_sqlalchemy 库实现的。该库为这两个系统之间的通信提供了便捷支持，使用户能够连接到 ClickHouse 并指定 SQL 方言。连接成功后，用户可以直接在 ClickHouse 原生 UI 中，或直接在 Jupyter Notebook 中运行 SQL 查询。

# 安装所需软件包
%pip install --quiet jupysql clickhouse_sqlalchemy

注意：可能需要重启内核才能使用更新后的软件包。

import pandas as pd
from sklearn_evaluation import plot

# 导入 jupysql Jupyter 扩展来创建 SQL 单元格
%load_ext sql
%config SqlMagic.autocommit=False

在后续步骤中，你需要确保你的 ClickHouse 已经启动并且可访问。你可以使用本地版本或云端版本。

**注意：**你需要根据要连接的实例类型调整连接字符串（url、user、password）。在下面的示例中，我们使用的是本地实例。要了解更多相关内容，请参阅本指南。

%sql clickhouse://default:@localhost:8123/default

%%sql
CREATE TABLE trips
(
    `trip_id` UInt32,
    `vendor_id` Enum8('1' = 1, '2' = 2, '3' = 3, '4' = 4, 'CMT' = 5, 'VTS' = 6, 'DDS' = 7, 'B02512' = 10, 'B02598' = 11, 'B02617' = 12, 'B02682' = 13, 'B02764' = 14, '' = 15),
    `pickup_date` Date,
    `pickup_datetime` DateTime,
    `dropoff_date` Date,
    `dropoff_datetime` DateTime,
    `store_and_fwd_flag` UInt8,
    `rate_code_id` UInt8,
    `pickup_longitude` Float64,
    `pickup_latitude` Float64,
    `dropoff_longitude` Float64,
    `dropoff_latitude` Float64,
    `passenger_count` UInt8,
    `trip_distance` Float64,
    `fare_amount` Float32,
    `extra` Float32,
    `mta_tax` Float32,
    `tip_amount` Float32,
    `tolls_amount` Float32,
    `ehail_fee` Float32,
    `improvement_surcharge` Float32,
    `total_amount` Float32,
    `payment_type` Enum8('UNK' = 0, 'CSH' = 1, 'CRE' = 2, 'NOC' = 3, 'DIS' = 4),
    `trip_type` UInt8,
    `pickup` FixedString(25),
    `dropoff` FixedString(25),
    `cab_type` Enum8('yellow' = 1, 'green' = 2, 'uber' = 3),
    `pickup_nyct2010_gid` Int8,
    `pickup_ctlabel` Float32,
    `pickup_borocode` Int8,
    `pickup_ct2010` String,
    `pickup_boroct2010` String,
    `pickup_cdeligibil` String,
    `pickup_ntacode` FixedString(4),
    `pickup_ntaname` String,
    `pickup_puma` UInt16,
    `dropoff_nyct2010_gid` UInt8,
    `dropoff_ctlabel` Float32,
    `dropoff_borocode` UInt8,
    `dropoff_ct2010` String,
    `dropoff_boroct2010` String,
    `dropoff_cdeligibil` String,
    `dropoff_ntacode` FixedString(4),
    `dropoff_ntaname` String,
    `dropoff_puma` UInt16
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(pickup_date)
ORDER BY pickup_datetime;
  • clickhouse://default:***@localhost:8123/default 完成。
%%sql
INSERT INTO trips
SELECT * FROM s3(
    'https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/trips_{1..2}.gz',
    'TabSeparatedWithNames', "
    `trip_id` UInt32,
    `vendor_id` Enum8('1' = 1, '2' = 2, '3' = 3, '4' = 4, 'CMT' = 5, 'VTS' = 6, 'DDS' = 7, 'B02512' = 10, 'B02598' = 11, 'B02617' = 12, 'B02682' = 13, 'B02764' = 14, '' = 15),
    `pickup_date` Date,
    `pickup_datetime` DateTime,
    `dropoff_date` Date,
    `dropoff_datetime` DateTime,
    `store_and_fwd_flag` UInt8,
    `rate_code_id` UInt8,
    `pickup_longitude` Float64,
    `pickup_latitude` Float64,
    `dropoff_longitude` Float64,
    `dropoff_latitude` Float64,
    `passenger_count` UInt8,
    `trip_distance` Float64,
    `fare_amount` Float32,
    `extra` Float32,
    `mta_tax` Float32,
    `tip_amount` Float32,
    `tolls_amount` Float32,
    `ehail_fee` Float32,
    `improvement_surcharge` Float32,
    `total_amount` Float32,
    `payment_type` Enum8('UNK' = 0, 'CSH' = 1, 'CRE' = 2, 'NOC' = 3, 'DIS' = 4),
    `trip_type` UInt8,
    `pickup` FixedString(25),
    `dropoff` FixedString(25),
    `cab_type` Enum8('yellow' = 1, 'green' = 2, 'uber' = 3),
    `pickup_nyct2010_gid` Int8,
    `pickup_ctlabel` Float32,
    `pickup_borocode` Int8,
    `pickup_ct2010` String,
    `pickup_boroct2010` String,
    `pickup_cdeligibil` String,
    `pickup_ntacode` FixedString(4),
    `pickup_ntaname` String,
    `pickup_puma` UInt16,
    `dropoff_nyct2010_gid` UInt8,
    `dropoff_ctlabel` Float32,
    `dropoff_borocode` UInt8,
    `dropoff_ct2010` String,
    `dropoff_boroct2010` String,
    `dropoff_cdeligibil` String,
    `dropoff_ntacode` FixedString(4),
    `dropoff_ntaname` String,
    `dropoff_puma` UInt16
") SETTINGS input_format_try_infer_datetimes = 0
  • clickhouse://default:***@localhost:8123/default 已完成。
%sql SELECT count() FROM trips limit 5;
  • clickhouse://default:***@localhost:8123/default 已完成。
count()
1999657
%sql SELECT DISTINCT(pickup_ntaname) FROM trips limit 5;
  • clickhouse://default:***@localhost:8123/default 已完成。
pickup_ntaname
Morningside Heights
Hudson Yards-Chelsea-Flatiron-Union Square
Midtown-Midtown South
SoHo-Tribeca-Civic Center-Little Italy
Murray Hill-Kips Bay
%sql SELECT round(avg(tip_amount), 2) FROM trips
  • clickhouse://default:***@localhost:8123/default 已完成。
round(avg(tip_amount), 2)
1.68
%%sql
SELECT
    passenger_count,
    ceil(avg(total_amount),2) AS average_total_amount
FROM trips
GROUP BY passenger_count
  • clickhouse://default:***@localhost:8123/default 已完成。
passenger_countaverage_total_amount
022.69
115.97
217.15
316.76
417.33
516.35
616.04
759.8
836.41
99.81
%%sql
SELECT
    pickup_date,
    pickup_ntaname,
    SUM(1) AS number_of_trips
FROM trips
GROUP BY pickup_date, pickup_ntaname
ORDER BY pickup_date ASC
limit 5;
  • clickhouse://default:***@localhost:8123/default 已完成。
pickup_datepickup_ntanamenumber_of_trips
2015-07-01Bushwick North2
2015-07-01Brighton Beach1
2015-07-01Briarwood-Jamaica Hills3
2015-07-01Williamsburg1
2015-07-01Queensbridge-Ravenswood-Long Island City9
# %sql DESCRIBE trips;

# %sql SELECT DISTINCT(trip_distance) FROM trips limit 50;

%%sql --save short-trips --no-execute
SELECT *
FROM trips
WHERE trip_distance < 6.3
  • clickhouse://default:***@localhost:8123/default 跳过执行…
%sqlplot histogram --table short-trips --column trip_distance --bins 10 --with short-trips

<AxesSubplot: title={'center': "'trip_distance' from 'short-trips'"}, xlabel='trip_distance', ylabel='计数'>
直方图显示 short-trips 数据集中行程距离的分布，共 10 个区间
ax = %sqlplot histogram --table short-trips --column trip_distance --bins 50 --with short-trips
ax.grid()
ax.set_title("行程距离（行程 < 6.3）")
_ = ax.set_xlabel("行程距离")
显示行程距离分布的直方图，包含 50 个区间和网格，标题为“Trip distance from trips < 6.3”