如何在 ClickHouse 中使用 Ibis

本文字数：8540；估计阅读时间：22 分钟

作者：Mark Needham

本文在公众号【ClickHouseInc】首发

Ibis 是一个开源的数据帧库，旨在兼容任何数据系统使用。它支持超过 20 种后端，包括 Polars、DataFusion 和 ClickHouse，并提供一个 Python 风格的接口，可以将关系操作翻译为 SQL 在底层数据库上执行。

在这篇博客文章中，我们将学习如何在 ClickHouse 中使用 Ibis【https://ibis-project.org/】。

可组合数据生态系统

Ibis 是可组合数据生态系统的一部分。下图展示了这一生态系统：

图中显示 Ibis 作为用户界面。与大多数其他 DataFrame 库不同，Ibis 使用 SQL 作为中间表示语言，这使得它能更轻松地与不同的后端进行通信。

安装 Ibis 和 ClickHouse

首先，我们来安装 Ibis、其示例数据和 ClickHouse。

pip install 'ibis-framework[clickhouse,examples]'

如果还没有运行 ClickHouse 服务器，我们需要先启动一个 ClickHouse 服务器：

curl https://clickhouse.com/ | sh
./clickhouse server

ClickHouse 启动后，我们就可以开始了！

导入 Ibis 示例数据集到 ClickHouse

Ibis 附带了多种示例数据集。我们将导入 nycflights13_flights 数据集到 ClickHouse 中。

首先，我们需要导入 Ibis 并创建与 ClickHouse 的连接：

import ibis
from ibis import _

con = ibis.connect("clickhouse://")

如果想使用远程的 ClickHouse 服务器，可以在连接字符串中提供相应的 URL 和凭证。下一步是创建表：

con.create_table(
    "flights",
    ibis.examples.nycflights13_flights.fetch().to_pyarrow(), 
    overwrite=True
)

此命令将数据集导入名为 flights 的表中，并在表已经存在时进行替换。

接下来，我们在另一个标签页中连接到 ClickHouse 查看此命令的效果：

./clickhouse client -m

ClickHouse client version 24.7.1.2215 (official build).
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 24.7.1.

连接后，我们可以获取表的列表：

SHOW TABLES

   ┌─name────┐
1. │ flights │
   └─────────┘

1 row in set. Elapsed: 0.002 sec.

探索 Ibis 的 flights 数据集

首先来看一下 flights 表中的字段：

DESCRIBE TABLE flights
SETTINGS describe_compact_output = 1

Query id: 7d497dee-ea8d-4b07-8b32-3f32f775ca32

    ┌─name───────────┬─type────────────────────┐
 1. │ year           │ Nullable(Int64)         │
 2. │ month          │ Nullable(Int64)         │
 3. │ day            │ Nullable(Int64)         │
 4. │ dep_time       │ Nullable(String)        │
 5. │ sched_dep_time │ Nullable(Int64)         │
 6. │ dep_delay      │ Nullable(String)        │
 7. │ arr_time       │ Nullable(String)        │
 8. │ sched_arr_time │ Nullable(Int64)         │
 9. │ arr_delay      │ Nullable(String)        │
10. │ carrier        │ Nullable(String)        │
11. │ flight         │ Nullable(Int64)         │
12. │ tailnum        │ Nullable(String)        │
13. │ origin         │ Nullable(String)        │
14. │ dest           │ Nullable(String)        │
15. │ air_time       │ Nullable(String)        │
16. │ distance       │ Nullable(Int64)         │
17. │ hour           │ Nullable(Int64)         │
18. │ minute         │ Nullable(Int64)         │
19. │ time_hour      │ Nullable(DateTime64(6)) │
    └────────────────┴─────────────────────────┘

到目前为止，一切进展顺利。现在返回到 Python REPL，更深入地探索飞行数据。首先，我们将创建一个表的引用：

flights = con.table("flights")
flights.schema()

ibis.Schema {
  year            int64
  month           int64
  day             int64
  dep_time        string
  sched_dep_time  int64
  dep_delay       int64
  arr_time        string
  sched_arr_time  int64
  arr_delay       int64
  carrier         string
  flight          int64
  tailnum         string
  origin          string
  dest            string
  air_time        string
  distance        int64
  hour            int64
  minute          int64
  time_hour       timestamp(6)
}

现在，让我们看一下表中的一行：

flights.head(n=1).to_pandas().T

                                  0
year                           2013
month                             1
day                               1
dep_time                        517
sched_dep_time                  515
dep_delay                         2
arr_time                        830
sched_arr_time                  819
arr_delay                        11
carrier                          UA
flight                         1545
tailnum                      N14228
origin                          EWR
dest                            IAH
air_time                        227
distance                       1400
hour                              5
minute                           15
time_hour       2013-01-01 10:00:00

dep_delay 和 arr_delay 虽然是字符串类型，但包含的是数值数据。我们可以通过在 Ibis 中将这些字段转换为 int 类型来修正这个问题。

请注意，这不会改变数据库中的底层类型。

flights = (flights.mutate(
  dep_delay = _.dep_delay.cast(int).coalesce(0), 
  arr_delay = _.arr_delay.cast(int).coalesce(0)
))

接下来，我们尝试对 flights 表编写一些查询。我们将先通过设置以下参数将 Ibis 置于交互模式：

ibis.options.interactive = True

这个参数的作用是：

在 REPL 中计算表达式时显示前几行。

让我们从找出哪个机场有最多的入港航班开始：

(flights
  .group_by(flights.dest)
  .count()
  .order_by(ibis.desc("CountStar()"))
.limit(5)
)

┏━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━┓
┃ dest   ┃ CountStar(flights) ┃
┡━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━┩
│ string │ int64              │
├────────┼────────────────────┤
│ ORD    │              17283 │
│ ATL    │              17215 │
│ LAX    │              16174 │
│ BOS    │              15508 │
│ MCO    │              14082 │
└────────┴────────────────────┘

根据这个指标，芝加哥奥黑尔机场是最多的。我们可以使用 agg 函数重新编写这个查询，读取如下：

(flights.group_by(flights.dest)
  .agg(flightCount = _.count())
  .order_by(ibis.desc(_.flightCount))
  .limit(5)
)

或者我们可以使用 topk 函数简化它：

flights.dest.topk(k=5)

topk 函数仅在按单列分组时有效。如果要按多列分组，仍需使用 agg 函数。

如果希望查看代码运行时执行的底层 SQL，可以使用 ibis.to_sql 函数：

print(ibis.to_sql(flights.dest.topk(k=5)))

SELECT
  *
FROM (
  SELECT
    "t1"."dest",
    COUNT(*) AS "CountStar()"
  FROM (
    SELECT
      "t0"."year",
      "t0"."month",
      "t0"."day",
      "t0"."dep_time",
      "t0"."sched_dep_time",
      COALESCE(CAST("t0"."dep_delay" AS Nullable(Int64)), 0) AS "dep_delay",
      "t0"."arr_time",
      "t0"."sched_arr_time",
      COALESCE(CAST("t0"."arr_delay" AS Nullable(Int64)), 0) AS "arr_delay",
      "t0"."carrier",
      "t0"."flight",
      "t0"."tailnum",
      "t0"."origin",
      "t0"."dest",
      "t0"."air_time",
      "t0"."distance",
      "t0"."hour",
      "t0"."minute",
      "t0"."time_hour"
    FROM "flights" AS "t0"
  ) AS "t1"
  GROUP BY
    "t1"."dest"
) AS "t2"
ORDER BY
  "t2"."CountStar()" DESC
LIMIT 5

这比手写的复杂得多，并且包含太多子查询，但确实能完成任务。

组合 Ibis 表达式

Ibis 表达式是延迟计算的，这意味着我们可以将表达式存储在变量中，并在后续的程序中应用其他操作。

例如，我们创建了一个名为 routes_by_carrier 的变量，将航班按 dest、origin 和 carrier 分组，并计算每个分组的行数：

routes_by_carrier = (flights
  .group_by([flights.dest,flights.origin, flights.carrier])
  .agg(flightCount = _.count())
)
routes_by_carrier

┏━━━━━━━━┳━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━━━━┓
┃ dest   ┃ origin ┃ carrier ┃ flightCount ┃
┡━━━━━━━━╇━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━━━━┩
│ string │ string │ string  │ int64       │
├────────┼────────┼─────────┼─────────────┤
│ BNA    │ JFK    │ MQ      │         365 │
│ MKE    │ LGA    │ 9E      │         132 │
│ SBN    │ LGA    │ EV      │           6 │
│ CLE    │ LGA    │ EV      │         419 │
│ AVL    │ EWR    │ EV      │         265 │
│ FLL    │ EWR    │ B6      │        1386 │
│ IAH    │ JFK    │ AA      │         274 │
│ SAV    │ EWR    │ EV      │         736 │
│ DFW    │ EWR    │ UA      │        1094 │
│ BZN    │ EWR    │ UA      │          36 │
│ …      │ …      │ …       │           … │
└────────┴────────┴─────────┴─────────────┘

可能稍后我们会决定查找承运人为 American Airlines 或 Delta Airlines 的航班。可以使用以下代码来实现：

(routes_by_carrier
  .filter(_.carrier.isin(["AA", "DL"]))
  .group_by([_.origin, _.dest])
  .agg(flightCount = _.flightCount.sum())
  .order_by(ibis.desc(_.flightCount))
  .limit(5)
)

┏━━━━━━━━┳━━━━━━━━┳━━━━━━━━━━━━━┓
┃ origin ┃ dest   ┃ flightCount ┃
┡━━━━━━━━╇━━━━━━━━╇━━━━━━━━━━━━━┩
│ string │ string │ int64       │
├────────┼────────┼─────────────┤
│ LGA    │ MIA    │        5781 │
│ JFK    │ LAX    │        5718 │
│ LGA    │ ORD    │        5694 │
│ LGA    │ ATL    │        5544 │
│ LGA    │ DFW    │        4836 │
└────────┴────────┴─────────────┘

我们还可以合并 Ibis 表。例如，假设我们为纽约各机场的出港航班分别创建了变量：

jfk_flights = flights.filter(_.origin == "JFK")
lga_flights = flights.filter(_.origin == "LGA")
ewr_flights = flights.filter(_.origin == "EWR")

我们可以对这些表中的每一个进一步构建表达式，也可以使用 union 函数将它们合并起来，然后进行其他操作。如果想计算三个机场的平均出发延误时间，可以这样做：

(jfk_flights
  .union(lga_flights, ewr_flights)
  .agg(avgDepDelay = _.dep_delay.mean())
)

┏━━━━━━━━━━━━━┓
┃ avgDepDelay ┃
┡━━━━━━━━━━━━━┩
│ float64     │
├─────────────┤
│   12.329263 │
└─────────────┘

还可以按机场找到平均延误时间：

(jfk_flights
  .union(lga_flights, ewr_flights)
  .group_by(_.origin)
  .agg(avgDepDelay = _.dep_delay.mean())
)

┏━━━━━━━━┳━━━━━━━━━━━━━┓
┃ origin ┃ avgDepDelay ┃
┡━━━━━━━━╇━━━━━━━━━━━━━┩
│ string │ float64     │
├────────┼─────────────┤
│ EWR    │   14.702983 │
│ JFK    │   11.909381 │
│ LGA    │   10.035170 │
└────────┴─────────────┘

然后，如果只想返回平均延误时间最大和最小的机场，可以编写以下代码：

(jfk_flights
  .union(lga_flights, ewr_flights)
  .group_by(_.origin)
  .agg(avgDepDelay = _.dep_delay.mean())
).agg(
  minDelayOrigin = _.origin.argmin(_.avgDepDelay),
  minDelay = _.avgDepDelay.min(),
  maxDelayOrigin = _.origin.argmax(_.avgDepDelay),
  maxDelay = _.avgDepDelay.max()
)

┏━━━━━━━━━━━━━━━━┳━━━━━━━━━━┳━━━━━━━━━━━━━━━━┳━━━━━━━━━━━┓
┃ minDelayOrigin ┃ minDelay ┃ maxDelayOrigin ┃ maxDelay  ┃
┡━━━━━━━━━━━━━━━━╇━━━━━━━━━━╇━━━━━━━━━━━━━━━━╇━━━━━━━━━━━┩
│ string         │ float64  │ string         │ float64   │
├────────────────┼──────────┼────────────────┼───────────┤
│ LGA            │ 10.03517 │ EWR            │ 14.702983 │
└────────────────┴──────────┴────────────────┴───────────┘

将 Ibis 连接到现有的 ClickHouse 表

我们还可以将 Ibis 连接到现有的 ClickHouse 表。我们在 play.clickhouse.com 上有一个托管的数据集 Playground，让我们创建一个新的连接：

remote_con = ibis.connect(
  "clickhouse://play:clickhouse@play.clickhouse.com:443?secure=True"
)

然后可以列出该服务器上的表：

remote_con.tables

Tables
------
- actors
- all_replicas_metric_log
- benchmark_results
- benchmark_runs
- cell_towers
- checks
- cisco_umbrella
- covid
- dish
- dns
- dns2
- github_events
- hackernews
- hackernews_changes_items
- hackernews_changes_profiles
- hackernews_changes_to_history
- hackernews_history
- hackernews_top
- lineorder
- loc_stats
- menu
- menu_item
- menu_item_denorm
- menu_page
- minicrawl
- newswire
- ontime
- opensky
- pypi
- query_metrics_v2
- rdns
- recipes
- repos
- repos_history
- repos_raw
- run_attributes_v1
- stock
- tranco
- trips
- uk_price_paid
- uk_price_paid_updater
- wikistat
- workflow_jobs

来看一下 uk_price_paid 表，该表包含英国出售房屋的价格。我们将创建一个对该表的引用，然后返回其架构：

uk_price_paid = remote_con.table("uk_price_paid")
uk_price_paid.schema()

ibis.Schema {
  price      !uint32
  date       !date
  postcode1  !string
  postcode2  !string
  type       !string
  is_new     !uint8
  duration   !string
  addr1      !string
  addr2      !string
  street     !string
  locality   !string
  town       !string
  district   !string
  county     !string
}

可以编写以下查询来查找英国房价最高的地方：

(uk_price_paid
  .group_by([_.postcode1, _.postcode2])
  .agg(
    maxPrice = _.price.max(),
    avgPrice = _.price.mean().cast(int)
  )
  .order_by(ibis.desc(_.maxPrice))
  .limit(5)
)

┏━━━━━━━━━━━┳━━━━━━━━━━━┳━━━━━━━━━━━┳━━━━━━━━━━━┓
┃ postcode1 ┃ postcode2 ┃ maxPrice  ┃ avgPrice  ┃
┡━━━━━━━━━━━╇━━━━━━━━━━━╇━━━━━━━━━━━╇━━━━━━━━━━━┩
│ !string   │ !string   │ !uint32   │ int64     │
├───────────┼───────────┼───────────┼───────────┤
│ TN23      │ 7HE       │ 900000000 │ 100115111 │
│ CV33      │ 9FR       │ 620000000 │ 206978541 │
│ W1U       │ 8EW       │ 594300000 │ 297192000 │
│ W1J       │ 7BT       │ 569200000 │  82508532 │
│ NW5       │ 2HB       │ 542540820 │  22848445 │
└───────────┴───────────┴───────────┴───────────┘

总结

希望这篇博客文章能对 Ibis 及其工作原理提供一个清晰的概述。Ibis 最近引入了 Ibis ML，在未来的文章中，我们将学习如何将其与 ClickHouse 数据结合使用。

征稿启示

面向社区长期正文，文章内容包括但不限于关于 ClickHouse 的技术研究、项目实践和创新做法等。建议行文风格干货输出&图文并茂。质量合格的文章将会发布在本公众号，优秀者也有机会推荐到 ClickHouse 官网。请将文章稿件的 WORD 版本发邮件至：Tracy.Wang@clickhouse.com

​​联系我们

手机号：13910395701

邮箱：Tracy.Wang@clickhouse.com

满足您所有的在线分析列式数据库管理需求