这篇文章是系列文章的一部分
将数据导入 ClickHouse
刚开始使用 ClickHouse 的用户经常会问如何高效地将数据加载到 ClickHouse 中。在本系列博文中，我们将演示实现此任务的几种方法：从 clickhouse-client 到官方支持的客户端库。在本篇博文中，我们将从最简单的开始，依赖于模式推断，并假设我们的数据集结构良好，完全干净（几乎），并已准备好立即插入。后面的博文将介绍数据清洗和模式优化的高级技巧。
作为 ClickHouse 用户和数据爱好者，我们一直在寻找足够大的有趣数据集来挑战 ClickHouse：我们甚至在主要仓库中使用专门的 GitHub 标签 来跟踪潜在的娱乐机会。这篇文章源于一个 问题，即探索 Hacker News 数据。为了简化操作，我们以多种格式分发了此数据集的干净版本。用户应该能够复现所有示例。
所有示例都使用 ClickHouse Cloud 实例，客户端托管在 c5ad.4xlarge 上，拥有 16 个核心和 32GB 内存。所有命令也与自托管的 ClickHouse 集群兼容。高端机器会加快处理速度，但您可以在普通笔记本电脑上执行这些步骤。
下载
可以从 这里 下载数据集的 CSV 版本，或者通过运行以下命令来下载
wget https://datasets-documentation.s3.eu-west-3.amazonaws.com/hackernews/hacknernews.csv.gz
这个压缩文件大小为 4.6GB，包含 2800 万行，下载需要 5-10 分钟。
采样
clickhouse-local 允许用户对本地文件执行快速处理，无需部署和配置 ClickHouse 服务器。
在将任何数据存储到 ClickHouse 之前，让我们使用 clickhouse-local 对文件进行采样。从 clickhouse-local 控制台
clickhouse-local
SELECT *
FROM file('hacknernews.csv.gz', CSVWithNames)
LIMIT 2
SETTINGS input_format_try_infer_datetimes = 0
FORMAT Vertical
Row 1:
──────
id: 344065
deleted: 0
type: comment
by: callmeed
time: 2008-10-26 05:06:58
text: What kind of reports do you need?<p>ActiveMerchant just connects your app to a gateway for cc approval and processing.<p>Braintree has very nice reports on transactions and it's very easy to refund a payment.<p>Beyond that, you are dealing with Rails after all–it's pretty easy to scaffold out some reports from your subscriber base.
dead: 0
parent: 344038
poll: 0
kids: []
url:
score: 0
title:
parts: []
descendants: 0
Row 2:
──────
id: 344066
deleted: 0
type: story
by: acangiano
time: 2008-10-26 05:07:59
text:
dead: 0
parent: 0
poll: 0
kids: [344111,344202,344329,344606]
url: http://antoniocangiano.com/2008/10/26/what-arc-should-learn-from-ruby/
score: 33
title: What Arc should learn from Ruby
parts: []
descendants: 10
这个命令有很多微妙的功能。file 运算符允许我们从本地磁盘读取文件，只需指定格式“CSVWithNames”。最重要的是，模式会从文件内容中自动推断出来。还要注意 clickhouse-local 如何能够读取压缩文件，从扩展名推断出 gzip 格式。我们为了渲染的目的以垂直方式格式化。
除了推断结构之外，模式推断还会为每一列确定一个类型。
敏锐的读者可能已经注意到我们使用了设置 input_format_try_infer_datetimes=0。此设置在模式推断期间会禁用日期解析，因为在撰写本文时（22.8），此特定 CSV 文件中的日期时间无法自动解析。此问题已在更高版本中解决。
加载数据
我们最简单、最强大的数据加载工具是 clickhouse-client：功能丰富的原生命令行客户端。要加载数据，我们也可以再次利用模式推断，依赖于 ClickHouse 来确定列的类型。
在以下命令中，我们将创建一个表并将数据直接从远程 CSV 文件插入，通过 url 函数访问内容。模式被推断出来，数据被轻松地插入到表中。从 clickhouse-client 控制台执行以下操作。
CREATE TABLE hackernews ENGINE = MergeTree ORDER BY tuple
(
) EMPTY AS SELECT * FROM url('https://datasets-documentation.s3.eu-west-3.amazonaws.com/hackernews/hacknernews.csv.gz', 'CSVWithNames');
这将使用从数据中推断出的模式创建一个空表。DESCRIBE 命令允许我们了解这些分配的类型。
DESCRIBE TABLE hackernews
┌─name────────┬─type─────────────────────┬
│ id │ Nullable(Float64) │
│ deleted │ Nullable(Float64) │
│ type │ Nullable(String) │
│ by │ Nullable(String) │
│ time │ Nullable(String) │
│ text │ Nullable(String) │
│ dead │ Nullable(Float64) │
│ parent │ Nullable(Float64) │
│ poll │ Nullable(Float64) │
│ kids │ Array(Nullable(Float64)) │
│ url │ Nullable(String) │
│ score │ Nullable(Float64) │
│ title │ Nullable(String) │
│ parts │ Array(Nullable(Float64)) │
│ descendants │ Nullable(Float64) │
└─────────────┴──────────────────────────┴
对于插入操作，我们可以利用 INSERT INTO, SELECT，直接从 URL 流式传输数据。
INSERT INTO hackernews SELECT *
FROM url('https://datasets-documentation.s3.eu-west-3.amazonaws.com/hackernews/hacknernews.csv.gz', 'CSVWithNames')
0 rows in set. Elapsed: 203.682 sec. Processed 28.74 million rows, 15.15 GB (141.09 thousand rows/s., 74.40 MB/s.)
此处的表模式远非最佳，插入速度可以通过一些简单的设置得到显著提高。我们将把这项任务留到后面的博文中进行。但是，我们已经成功地使用一个命令将 2800 万行插入了 ClickHouse！
让我们对 Hacker News 故事和特定列进行采样。
SELECT
id,
title,
type,
by,
time,
url,
score
FROM hackernews
WHERE type = 'story'
LIMIT 3
FORMAT Vertical
Row 1:
──────
id: 2596866
title:
type: story
by:
time: 1306685152
url:
score: 0
Row 2:
──────
id: 2596870
title: WordPress capture users last login date and time
type: story
by: wpsnipp
time: 1306685252
url: http://wpsnipp.com/index.php/date/capture-users-last-login-date-and-time/
score: 1
Row 3:
──────
id: 2596872
title: Recent college graduates get some startup wisdom
type: story
by: whenimgone
time: 1306685352
url: http://articles.chicagotribune.com/2011-05-27/business/sc-cons-0526-started-20110527_1_business-plan-recession-college-graduates
score: 1
虽然模式推断对于初始数据探索是一个很棒的工具，但它属于“尽力而为”的范畴，不能长期替代为您的数据定义最佳模式。
定义模式
一个明显的直接优化是为每个字段定义一个类型。除了将时间字段声明为 DateTime 之外，我们还为以下每个字段定义了适当的类型，然后删除了我们现有的数据集。此外，请注意，我们通过 ORDER BY 子句为数据定义了主键 id。这超出了这篇博文的范围，但建议读者阅读我们关于 ClickHouse 主键 的介绍。总之，这将使某些查询更快，并有助于压缩。
DROP TABLE IF EXISTS hackernews;
CREATE TABLE hackernews
(
`id` UInt32,
`deleted` UInt8,
`type` Enum('story' = 1, 'comment' = 2, 'poll' = 3, 'pollopt' = 4, 'job' = 5),
`by` LowCardinality(String),
`time` DateTime,
`text` String,
`dead` UInt8,
`parent` UInt32,
`poll` UInt32,
`kids` Array(UInt32),
`url` String,
`score` Int32,
`title` String,
`parts` Array(UInt32),
`descendants` Int32
)
ENGINE = MergeTree
ORDER BY id
有了优化的模式，我们现在可以演示从本地文件系统插入数据。我们再次使用 clickhouse-client，使用 INFILE 子句和显式的 INSERT INTO 插入文件。这同样能够读取 gz 文件。
INSERT INTO hackernews FROM INFILE '/data/hacknernews.csv.gz' FORMAT CSVWithNames
Query id: 35fa5a90-68ea-466f-bdb5-cdd24f33f047
Ok.
28737557 rows in set. Elapsed: 93.824 sec.
我们还可以在这里做更多事情来优化此模式 - 编解码器和对文本列的文本标记支持将是一个开始，但我们将探索这些内容在以后的博文中。
简单查询
我们最初的想法是调查 ClickHouse 在 Hacker News 中的普及程度，以及这种普及程度是否随着时间的推移而增加。score 字段为我们提供了故事流行度的度量标准，而 id 字段和 || 连接运算符 可以用来生成指向原始帖子的链接。
SELECT
time,
score,
descendants,
title,
url,
'https://news.ycombinator.com/item?id=' || toString(id) AS hn_url
FROM hackernews
WHERE (type = 'story') AND (title ILIKE '%ClickHouse%')
ORDER BY score DESC
LIMIT 5 FORMAT Vertical
Row 1:
──────
time: 1632154428
score: 519
descendants: 159
title: ClickHouse, Inc.
url: https://github.com/ClickHouse/ClickHouse/blob/master/website/blog/en/2021/clickhouse-inc.md
hn_url: https://news.ycombinator.com/item?id=28595419
Row 2:
──────
time: 1614699632
score: 383
descendants: 134
title: ClickHouse as an alternative to Elasticsearch for log storage and analysis
url: https://pixeljets.com/blog/clickhouse-vs-elasticsearch/
hn_url: https://news.ycombinator.com/item?id=26316401
Row 3:
──────
time: 1465985177
score: 243
descendants: 70
title: ClickHouse – high-performance open-source distributed column-oriented DBMS
url: https://clickhouse.yandex/reference_en.html
hn_url: https://news.ycombinator.com/item?id=11908254
Row 4:
──────
time: 1578331410
score: 216
descendants: 86
title: ClickHouse cost-efficiency in action: analyzing 500B rows on an Intel NUC
url: https://www.altinity.com/blog/2020/1/1/clickhouse-cost-efficiency-in-action-analyzing-500-billion-rows-on-an-intel-nuc
hn_url: https://news.ycombinator.com/item?id=21970952
Row 5:
──────
time: 1622160768
score: 198
descendants: 55
title: ClickHouse: An open-source column-oriented database management system
url: https://github.com/ClickHouse/ClickHouse
hn_url: https://news.ycombinator.com/item?id=27310247
ClickHouse 是否随着时间的推移产生了更多噪音？请注意我们在此进行工作的意义，以确保时间字段是一个 DateTime，使用适当的数据类型允许我们使用 toYYYYMM() 函数。
SELECT
toYYYYMM(time) AS monthYear,
bar(count(), 0, 120, 20)
FROM hackernews
WHERE (type IN ('story', 'comment')) AND ((title ILIKE '%ClickHouse%') OR (text ILIKE '%ClickHouse%'))
GROUP BY monthYear
ORDER BY monthYear ASC
Query id: 9c7a0bb3-2a16-42dd-942f-fa9e03cad2c9
┌─monthYear─┬─bar(count(), 0, 120, 20)─┐
│ 201606 │ ██▎ │
│ 201607 │ ▏ │
│ 201610 │ ▎ │
│ 201612 │ ▏ │
│ 201701 │ ▎ │
│ 201702 │ █ │
│ 201703 │ ▋ │
│ 201704 │ █ │
│ 201705 │ ██ │
│ 201706 │ ▎ │
│ 201707 │ ▎ │
│ 201708 │ ▏ │
│ 201709 │ ▎ │
│ 201710 │ █▌ │
│ 201711 │ █▌ │
│ 201712 │ ▌ │
│ 201801 │ █▌ │
│ 201802 │ ▋ │
│ 201803 │ ███▏ │
│ 201804 │ ██▏ │
│ 201805 │ ▋ │
│ 201806 │ █▏ │
│ 201807 │ █▌ │
│ 201808 │ ▋ │
│ 201809 │ █▌ │
│ 201810 │ ███▌ │
│ 201811 │ ████ │
│ 201812 │ █▌ │
│ 201901 │ ████▋ │
│ 201902 │ ███ │
│ 201903 │ ▋ │
│ 201904 │ █ │
│ 201905 │ ███▋ │
│ 201906 │ █▏ │
│ 201907 │ ██▎ │
│ 201908 │ ██▋ │
│ 201909 │ █▋ │
│ 201910 │ █ │
│ 201911 │ ███ │
│ 201912 │ █▎ │
│ 202001 │ ███████████▋ │
│ 202002 │ ██████▌ │
│ 202003 │ ███████████▋ │
│ 202004 │ ███████▎ │
│ 202005 │ ██████▏ │
│ 202006 │ ██████▏ │
│ 202007 │ ███████▋ │
│ 202008 │ ███▋ │
│ 202009 │ ████ │
│ 202010 │ ████▌ │
│ 202011 │ █████▏ │
│ 202012 │ ███▋ │
│ 202101 │ ███▏ │
│ 202102 │ █████████ │
│ 202103 │ █████████████▋ │
│ 202104 │ ███▏ │
│ 202105 │ ████████████▋ │
│ 202106 │ ███ │
│ 202107 │ █████▏ │
│ 202108 │ ████▎ │
│ 202109 │ ██████████████████▎ │
│ 202110 │ ▏ │
└───────────┴──────────────────────────┘
62 rows in set. Elapsed: 1.725 sec. Processed 28.74 million rows, 10.35 GB (16.66 million rows/s., 6.00 GB/s.)
看来我们在生成 Hacker News 讨论的能力方面正朝着正确的方向前进。尽管对文本字段使用了效率较低的 ILIKE 条件，但这仍然相当快，仅需 1.7 秒。后面的博文将探讨如何优化此标记化用例。
关于 ClickHouse 帖子，谁是最主要的评论者？
SELECT
by,
count() AS comments
FROM hackernews
WHERE (type IN ('story', 'comment')) AND ((title ILIKE '%ClickHouse%') OR (text ILIKE '%ClickHouse%'))
GROUP BY by
ORDER BY comments DESC
LIMIT 5
Query id: 1460bb8d-3263-4521-ace5-9e2300289bb0
┌─by──────────┬─comments─┐
│ hodgesrm │ 78 │
│ zX41ZdbW │ 45 │
│ manigandham │ 39 │
│ pachico │ 35 │
│ valyala │ 27 │
└─────────────┴──────────┘
5 rows in set. Elapsed: 1.809 sec. Processed 28.74 million rows, 10.72 GB (15.88 million rows/s., 5.92 GB/s.)
一些知名的社区成员，但最重要的是，谁的评论最受关注？
SELECT
by,
sum(score) AS total_score,
sum(length(kids)) AS total_sub_comments
FROM hackernews
WHERE (type IN ('story', 'comment')) AND ((title ILIKE '%ClickHouse%') OR (text ILIKE '%ClickHouse%'))
GROUP BY by
ORDER BY total_score DESC
LIMIT 5
Query id: e9500734-469e-402b-a22a-153914cbe72f
┌─by───────┬─total_score─┬─total_sub_comments─┐
│ zX41ZdbW │ 571 │ 50 │
│ jetter │ 386 │ 30 │
│ hodgesrm │ 312 │ 50 │
│ mechmind │ 243 │ 16 │
│ tosh │ 198 │ 12 │
└──────────┴─────────────┴────────────────────┘
5 rows in set. Elapsed: 1.864 sec. Processed 28.74 million rows, 11.16 GB (15.42 million rows/s., 5.99 GB/s.)
其他格式
ClickHouse 的优势之一是它能够处理多种格式。我们认识到 CSV 代表了一个相当理想的用例，但它并不是数据交换最有效的方式。Parquet 和 JSON 出于不同的原因而成为常见的交换格式。Parquet 是一种高效的列式格式，而 JSON 已成为半结构化信息和网络数据传输的主要手段。ClickHouse 支持这两种格式。
Parquet 具有最少的类型，ClickHouse 需要遵守这些类型。此类型信息在格式本身中进行编码。对 Parquet 文件进行类型推断必然会导致略微不同的模式。下面我们演示使用 Parquet 格式读取相同数据，再次使用 url 函数读取远程数据。请注意，我们必须接受密钥可能为 Null（尽管数据中没有）作为 Parquet 格式的条件。
DROP TABLE IF EXISTS hackernews;
CREATE TABLE hackernews
ENGINE = MergeTree
ORDER BY id
SETTINGS allow_nullable_key = 1 EMPTY AS
SELECT *
FROM url('https://datasets-documentation.s3.eu-west-3.amazonaws.com/hackernews/hacknernews.parquet', 'Parquet')
INSERT INTO hackernews SELECT *
FROM url('https://datasets-documentation.s3.eu-west-3.amazonaws.com/hackernews/hacknernews.parquet', 'Parquet')
Ok.
0 rows in set. Elapsed: 314.165 sec. Processed 28.74 million rows, 13.95 GB (91.47 thousand rows/s., 44.39 MB/s.)
以及生成的模式：
DESCRIBE TABLE hackernews
┌─name────────┬─type───────────────────┬
│ id │ Nullable(Int64) │
│ deleted │ Nullable(UInt8) │
│ type │ Nullable(String) │
│ time │ Nullable(Int64) │
│ text │ Nullable(String) │
│ dead │ Nullable(UInt8) │
│ parent │ Nullable(Int64) │
│ poll │ Nullable(Int64) │
│ kids │ Array(Nullable(Int64)) │
│ url │ Nullable(String) │
│ score │ Nullable(Int32) │
│ title │ Nullable(String) │
│ parts │ Array(Nullable(Int64)) │
│ descendants │ Nullable(Int32) │
└─────────────┴────────────────────────┴
ndJson 代表一种常见的交换格式。ClickHouse 支持通过 JSONEachRow 格式解析此数据。为了举例说明，我们从磁盘加载文件的 ndjson 版本。
wget https://datasets-documentation.s3.eu-west-3.amazonaws.com/hackernews/hacknernews.json.gz
DROP TABLE IF EXISTS hackernews;
CREATE TABLE hackernews
(
`id` UInt32,
`deleted` UInt8,
`type` Enum('story' = 1, 'comment' = 2, 'poll' = 3, 'pollopt' = 4, 'job' = 5),
`by` LowCardinality(String),
`time` DateTime,
`text` String,
`dead` UInt8,
`parent` UInt32,
`poll` UInt32,
`kids` Array(UInt32),
`url` String,
`score` Int32,
`title` String,
`parts` Array(UInt32),
`descendants` Int32
)
ENGINE = MergeTree
ORDER BY id;
INSERT INTO hackernews FROM INFILE '/data/hacknernews.json.gz' FORMAT JSONEachRow
Ok.
28737557 rows in set. Elapsed: 94.491 sec.
总结
在本篇文章中，我们探讨了将数据从本地和远程文件加载到 ClickHouse 中。我们利用了模式推断，并演示了对几种流行文件格式的支持。在以后的文章中，我们将探讨优化模式和插入/读取性能，然后演示使用其他客户端加载数据。
