Set up docs MCP在本教程中，你将把 CSV 和 Parquet 格式的 2800 万行 Hacker News 数据插入 ClickHouse 表中，并运行一些简单查询来探索这些数据。
CSV
下载 CSV
可从我们的公开 S3 存储桶 下载该数据集的 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接下来，运行以下命令以查看数据：
SELECT *
FROM file('hacknernews.csv.gz', CSVWithNames)
LIMIT 2
SETTINGS input_format_try_infer_datetimes = 0
FORMAT VerticalRow 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 格式。
最重要的是，系统会根据文件内容自动为你推断 schema。
还要注意，clickhouse-local 能够读取压缩文件，并根据扩展名推断出 gzip 格式。
这里使用 Vertical 格式，以便更直观地查看每一列的数据。
通过 schema inference 加载数据
用于加载数据的最简单且最强大的工具是 clickhouse-client：一款功能丰富的原生命令行客户端。
要加载数据，你也可以再次利用 schema inference，由 ClickHouse 自动确定各列的类型。
运行以下命令来创建表，并通过 url 函数直接从远程 CSV 文件插入数据。
schema 会自动推断：
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');这会使用从数据中推断出的 schema 创建一个空表。
DESCRIBE TABLE 命令可帮助我们了解这些已分配的类型。
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 函数，数据将直接从该 URL 导入：
INSERT INTO hackernews SELECT *
FROM url('https://datasets-documentation.s3.eu-west-3.amazonaws.com/hackernews/hacknernews.csv.gz', 'CSVWithNames')你已成功用一条命令将 2800 万行数据插入 ClickHouse！
查看数据
运行以下查询，对 Hacker News 的新闻条目和特定列进行采样：
SELECT
id,
title,
type,
by,
time,
url,
score
FROM hackernews
WHERE type = 'story'
LIMIT 3
FORMAT VerticalRow 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虽然 schema inference 是初期数据探索的利器，但它只是一种“尽力而为”的方法，不能作为长期方案来替代为您的数据定义最佳 schema。
定义 schema
一个显而易见的优化方式，就是为每个字段定义类型。
除了将时间字段声明为 DateTime 类型之外，在删除现有数据集后，我们还将为下列每个字段定义合适的类型。
在 ClickHouse 中，数据的主键 id 是通过 ORDER BY 子句定义的。
选择合适的类型，并确定在 ORDER BY
子句中包含哪些列，有助于提升查询速度和压缩效果。
运行以下查询以删除旧 schema 并创建优化后的 schema：
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有了优化后的 schema，现在你可以从本地文件系统中插入数据了。
再次使用 clickhouse-client，通过带有 INFILE 子句的显式 INSERT INTO 语句插入该文件。
INSERT INTO hackernews FROM INFILE '/data/hacknernews.csv.gz' FORMAT CSVWithNames运行示例查询
以下提供了一些示例查询，希望能为您编写自己的查询提供参考。
"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 VerticalRow 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=27310247ClickHouse 随时间推移是否产生了更多噪声？这里体现了将 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┌─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 │ ▏ │
└───────────┴──────────────────────────┘看来 "ClickHouse" 的热度正随着时间的推移持续上升。
谁是 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┌─by──────────┬─comments─┐
│ hodgesrm │ 78 │
│ zX41ZdbW │ 45 │
│ manigandham │ 39 │
│ pachico │ 35 │
│ valyala │ 27 │
└─────────────┴──────────┘哪些评论最能引发关注？
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┌─by───────┬─total_score─┬─total_sub_comments─┐
│ zX41ZdbW │ 571 │ 50 │
│ jetter │ 386 │ 30 │
│ hodgesrm │ 312 │ 50 │
│ mechmind │ 243 │ 16 │
│ tosh │ 198 │ 12 │
└──────────┴─────────────┴────────────────────┘Parquet
ClickHouse 的优势之一在于它能够处理多种格式。 CSV 是一种相当理想的使用场景，但并不是数据交换的最高效格式。
接下来，你将从 Parquet 文件加载数据，它是一种高效的列式格式。
Parquet 的类型很少，ClickHouse 必须遵循这些类型，而且这些类型信息就编码在格式本身中。 对 Parquet 文件进行类型推断，得到的 schema 往往会与 CSV 文件的 schema 略有不同。
插入数据
运行以下查询，使用 url 函数再次读取远程数据，并以 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');运行以下命令查看推断出的 schema：
DESCRIBE TABLE hackernews;┌─name────────┬─type───────────────────┬
│ id │ Nullable(Int64) │
│ deleted │ Nullable(UInt8) │
│ type │ Nullable(String) │
│ by │ 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) │
└─────────────┴────────────────────────┴后续步骤将使用更清晰的列名，例如 author 和 comment，因此请继续使用手动指定的 schema。
首先删除自动推断出的表，然后创建该表，并直接从公网 S3 bucket 插入数据：
DROP TABLE IF EXISTS hackernews;
CREATE TABLE hackernews
(
`id` UInt64,
`deleted` UInt8,
`type` String,
`author` String,
`timestamp` DateTime,
`comment` String,
`dead` UInt8,
`parent` UInt64,
`poll` UInt64,
`children` Array(UInt32),
`url` String,
`score` UInt32,
`title` String,
`parts` Array(UInt32),
`descendants` UInt32
)
ENGINE = MergeTree
ORDER BY (type, author);
INSERT INTO hackernews
SELECT * FROM s3(
'https://datasets-documentation.s3.eu-west-3.amazonaws.com/hackernews/hacknernews.parquet',
NOSIGN,
'Parquet',
'id UInt64,
deleted UInt8,
type String,
by String,
time DateTime,
text String,
dead UInt8,
parent UInt64,
poll UInt64,
kids Array(UInt32),
url String,
score UInt32,
title String,
parts Array(UInt32),
descendants UInt32');添加文本索引以加快搜索
要查看有多少条评论提到了 "ClickHouse"，请运行以下查询：
SELECT count(*)
FROM hackernews
WHERE hasAnyTokens(lower(comment), 'clickhouse');┌─count()─┐
│ 1145 │
└─────────┘
1 row in set. Elapsed: 3.251 sec. Processed 28.74 million rows, 9.60 GB (8.84 million rows/s., 2.95 GB/s.)接下来，在 comment 列上创建一个文本索引，
以加快此查询。文本索引使用倒排索引，将标记映射到包含这些标记的行。
splitByNonAlpha 分词器会按非字母数字字符拆分文本。索引和查询均使用 lower(comment)，
并采用小写搜索词，因此匹配时不区分大小写。查询表达式必须与索引表达式一致。
运行以下命令创建索引：
ALTER TABLE hackernews
ADD INDEX comment_idx lower(comment)
TYPE text(tokenizer = splitByNonAlpha);
ALTER TABLE hackernews
MATERIALIZE INDEX comment_idx
SETTINGS mutations_sync = 2;物化会为现有数据构建索引。mutations_sync 设置会等待物化完成。
您可以在 system.data_skipping_indices 表中查看索引定义。
索引物化完成后，再次运行相同的查询：
SELECT count(*)
FROM hackernews
WHERE hasAnyTokens(lower(comment), 'clickhouse');┌─count()─┐
│ 1145 │
└─────────┘
1 row in set. Elapsed: 0.019 sec. Processed 4.48 million rows, 4.48 MB (232.23 million rows/s., 232.23 MB/s.)结果保持不变，因为索引改变的是 ClickHouse 查找匹配行的方式，而不是决定哪些行匹配。使用索引的
查询处理的数据量显著减少，执行速度也快得多。
使用 EXPLAIN 确认 ClickHouse 计划使用该索引：
EXPLAIN indexes = 1
SELECT count(*)
FROM hackernews
WHERE hasAnyTokens(lower(comment), 'clickhouse');Output: count()
Aggregating
│ Keys:
│ Aggregates: count()
│ Skip merging: 0
└──Filter
│ Filter column: __text_index_comment_idx_hasAnyTokens_7b9491bd22343c822f64c95a9bda20f9
└──ReadFromMergeTree (default.hackernews)
Read type: Default
Parts: 4 | Granules: 547
Output: __text_index_comment_idx_hasAnyTokens_7b9491bd22343c822f64c95a9bda20f9
Indexes:
PrimaryKey
Condition: true
Parts: 4/4
Granules: 3527/3527
Skip
Name: comment_idx
Description: text GRANULARITY 100000000
Condition: (mode: Any; tokens: ["clickhouse"])
Parts: 4/4
Granules: 547/3527
Ranges: 437comment_idx 条目表明 ClickHouse 计划使用该文本索引。在此示例中，执行计划从 3527 个
粒度中选取了 547 个，大幅减少了需要扫描的数据量。
您还可以搜索多个标记中的任意一个或全部。这些函数匹配由索引分词器生成的完整标记。
当至少有一个标记需要匹配时，请使用 hasAnyTokens：
SELECT count(*)
FROM hackernews
WHERE hasAnyTokens(lower(comment), 'oltp olap');┌─count()─┐
│ 2020 │
└─────────┘当所有标记都必须匹配且顺序不限时，使用 hasAllTokens：
SELECT count(*)
FROM hackernews
WHERE hasAllTokens(lower(comment), 'avx sve');┌─count()─┐
│ 22 │
└─────────┘