本博客文章是系列文章的一部分
ClickHouse 数据导入
刚开始使用 ClickHouse 的用户经常会问到一个问题，即如何高效地将数据加载到 ClickHouse 中。在本博客系列中，我们将演示实现此任务的几种选项：从 clickhouse-client 到官方支持的客户端库。在这篇特定的文章中，我们从简单入手，依靠模式推断，并假设我们的数据集结构足够良好、非常干净（几乎如此），并且可以立即插入。后续文章将介绍用于数据清理和模式优化的高级技术。
作为 ClickHouse 用户和数据爱好者，我们一直在寻找足够大的有趣数据集来挑战 ClickHouse：我们甚至在主存储库上使用特定的 GitHub 标签 来跟踪潜在的趣味机会。这篇文章源于 一个 issue，旨在探索 Hacker News 数据。为了保持简单，我们以多种格式分发此数据集的干净版本。用户应该能够重现所有示例。
所有示例都使用 ClickHouse Cloud 实例，客户端托管在具有 16 个内核和 32GB RAM 的 c5ad.4xlarge 上。所有命令也将与自管理的 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
虽然模式推断是初始数据探索的绝佳工具，但它是“尽力而为”的，不能长期替代为您的数据定义最佳模式。
定义模式
一个明显的即时优化是为每个字段定义一个类型。除了将 time 字段声明为 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 产生的声音是否随着时间的推移而增大？请注意，我们确保 time 字段为 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 对话的能力方面正朝着正确的方向前进。尽管在 text 字段上使用了相当低效的 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 已成为半结构化信息和 Web 数据传输的主要方式。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。我们利用了模式推断，并演示了对几种流行文件格式的支持。在以后的文章中，我们将在演示使用其他客户端加载数据之前，先着眼于优化模式和插入/读取性能。
