Hacker News 数据集

在本教程中，你将把 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

接下来，运行以下命令以查看数据：

Query

SELECT *
FROM file('hacknernews.csv.gz', CSVWithNames)
LIMIT 2
SETTINGS input_format_try_infer_datetimes = 0
FORMAT Vertical

Response

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 格式。最重要的是，系统会根据文件内容自动为你推断 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 命令可帮助我们了解这些已分配的类型。

Query

DESCRIBE TABLE hackernews

Response

┌─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 的新闻条目和特定列进行采样：

Query

SELECT
    id,
    title,
    type,
    by,
    time,
    url,
    score
FROM hackernews
WHERE type = 'story'
LIMIT 3
FORMAT Vertical

Response

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

虽然 schema inference 是初期数据探索的利器，但它只是一种“尽力而为”的方法，不能作为长期方案来替代为您的数据定义最佳 schema。

定义 schema

一个显而易见的优化方式，就是为每个字段定义类型。除了将时间字段声明为 DateTime 类型之外，在删除现有数据集后，我们还将为下列每个字段定义合适的类型。在 ClickHouse 中，数据的主键 id 是通过 ORDER BY 子句定义的。选择合适的类型，并确定在 ORDER BY 子句中包含哪些列，有助于提升查询速度和压缩效果。运行以下查询以删除旧 schema 并创建优化后的 schema：

Query

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 语句插入该文件。

Query

INSERT INTO hackernews FROM INFILE '/data/hacknernews.csv.gz' FORMAT CSVWithNames

运行示例查询

以下提供了一些示例查询，希望能为您编写自己的查询提供参考。

“ClickHouse” 在 Hacker News 上是一个多热门的话题？

score 字段提供了衡量故事热度的指标，而 id 字段与 || 拼接运算符可用于生成原始帖子的链接。

Query

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

Response

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() 函数：

Query

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

Response

┌─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 相关文章中评论最多的用户？

Query

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

Response

┌─by──────────┬─comments─┐
│ hodgesrm    │       78 │
│ zX41ZdbW    │       45 │
│ manigandham │       39 │
│ pachico     │       35 │
│ valyala     │       27 │
└─────────────┴──────────┘

哪些评论最能引发关注？

Query

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

Response

┌─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');

Parquet 中的空键推断出的 schema 会将列设为 Nullable，因此即使此数据集不包含空 ID，仍需启用 allow_nullable_key。

运行以下命令查看推断出的 schema：

Query

DESCRIBE TABLE hackernews;

Response

┌─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”，请运行以下查询：

Query

SELECT count(*)
FROM hackernews
WHERE hasAnyTokens(lower(comment), 'clickhouse');

Response

┌─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 表中查看索引定义。索引物化完成后，再次运行相同的查询：

Query

SELECT count(*)
FROM hackernews
WHERE hasAnyTokens(lower(comment), 'clickhouse');

Response

┌─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 计划使用该索引：

Query

EXPLAIN indexes = 1
SELECT count(*)
FROM hackernews
WHERE hasAnyTokens(lower(comment), 'clickhouse');

Response

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: 437

comment_idx 条目表明 ClickHouse 计划使用该文本索引。在此示例中，执行计划从 3527 个粒度中选取了 547 个，大幅减少了需要扫描的数据量。您还可以搜索多个标记中的任意一个或全部。这些函数匹配由索引分词器生成的完整标记。当至少有一个标记需要匹配时，请使用 hasAnyTokens：

Query

SELECT count(*)
FROM hackernews
WHERE hasAnyTokens(lower(comment), 'oltp olap');

Response

┌─count()─┐
│    2020 │
└─────────┘

当所有标记都必须匹配且顺序不限时，使用 hasAllTokens：

Query

SELECT count(*)
FROM hackernews
WHERE hasAllTokens(lower(comment), 'avx sve');

Response

┌─count()─┐
│      22 │
└─────────┘

Get started

Setup

Sample datasets

Migrate

CSV

“ClickHouse” 在 Hacker News 上是一个多热门的话题？

谁是 ClickHouse 相关文章中评论最多的用户？

哪些评论最能引发关注？

Parquet

​CSV

​“ClickHouse” 在 Hacker News 上是一个多热门的话题？

​谁是 ClickHouse 相关文章中评论最多的用户？

​哪些评论最能引发关注？

​Parquet

CSV

“ClickHouse” 在 Hacker News 上是一个多热门的话题？

谁是 ClickHouse 相关文章中评论最多的用户？

哪些评论最能引发关注？

Parquet