Skip to content
ClickHouse Docs
ClickHouse DocsClickHouse Docs
49.6kGet Started

Hacker News データセット

View as MarkdownEdit this pageReport an issueSet up docs MCP

このチュートリアルでは、CSV フォーマットと Parquet フォーマットの両方から 2,800 万行の Hacker News データを ClickHouse テーブルに挿入し、簡単なクエリをいくつか実行してデータを確認します。

CSV

CSV をダウンロード

このデータセットの CSV 版は、公開 S3 bucket からダウンロードするか、次のコマンドを実行して取得できます。

wget https://datasets-documentation.s3.eu-west-3.amazonaws.com/hackernews/hacknernews.csv.gz

4.6GB、2,800万行のこの圧縮ファイルのダウンロードには、5〜10分かかるはずです。

データをサンプリングする

clickhouse-local を使うと、ClickHouse server をデプロイして設定しなくても、 ローカルファイルを高速に処理できます。

データを ClickHouse に保存する前に、clickhouse-local を使ってファイルをサンプリングしてみましょう。 コンソールから次を実行します。

clickhouse-local

次に、データを確認するには、次のコマンドを実行します。

Querysql
SELECT *
FROM file('hacknernews.csv.gz', CSVWithNames)
LIMIT 2
SETTINGS input_format_try_infer_datetimes = 0
FORMAT Vertical
Responseresponse
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 operator を使うと、CSVWithNames フォーマットを指定するだけで、ローカルディスク上のファイルを読み込めます。 特に重要なのは、ファイルの内容からスキーマが自動的に推論されることです。 また、clickhouse-local は圧縮ファイルも読み込むことができ、拡張子から gzip フォーマットを推論している点にも注目してください。 Vertical フォーマットを使うと、各カラムのデータをより見やすく表示できます。

スキーマ推論を使ってデータをロードする

データのロードに最も簡単で強力なツールは、clickhouse-client です。これは、多機能なネイティブのコマンドラインクライアントです。 データをロードする際は、再度スキーマ推論を活用し、ClickHouse にカラムの型の判定を任せることができます。

以下のコマンドを実行すると、url 関数を使ってリモートの CSV ファイルの内容にアクセスし、テーブルを作成してデータを直接挿入できます。 スキーマは自動的に推論されます。

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 TABLE コマンドを使うと、これらの型がどのように割り当てられたかを確認できます。

Querysql
DESCRIBE TABLE hackernews
Responsetext
┌─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')

たった1つのコマンドで、2,800万行をClickHouseに正常に挿入できました！

データを確認する

以下のクエリを実行して、Hacker News の記事と特定のカラムをサンプルとして表示します。

Querysql
SELECT
    id,
    title,
    type,
    by,
    time,
    url,
    score
FROM hackernews
WHERE type = 'story'
LIMIT 3
FORMAT Vertical
Responseresponse
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 型として宣言するだけでなく、既存のデータセットを削除したうえで、以下の各フィールドにも適切な型を定義します。 ClickHouse では、データの主キーは ORDER BY 句で定義されます。

適切な型を選び、どのカラムを ORDER BY 句に含めるかを決めることで、クエリ速度と圧縮の向上につながります。

以下のクエリを実行して、古いスキーマを削除し、改善したスキーマを作成します。

Querysql
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 を使ってファイル内のデータを挿入します。

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

サンプルクエリを実行する

以下にサンプルクエリをいくつか示します。独自のクエリを作成する際の参考にしてください。

Hacker Newsで「ClickHouse」はどれほど話題になっているか？

scoreフィールドは記事の人気度を示す指標であり、idフィールドと ||連結演算子を使うと元の投稿へのリンクを生成できます。

Querysql
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
Responseresponse
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() 関数を活用できます：

Querysql
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
Responseresponse
┌─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関連記事で最もコメントが多いユーザーは誰ですか？

Querysql
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
Responseresponse
┌─by──────────┬─comments─┐
│ hodgesrm    │       78 │
│ zX41ZdbW    │       45 │
│ manigandham │       39 │
│ pachico     │       35 │
│ valyala     │       27 │
└─────────────┴──────────┘

どのコメントが最も注目を集めているか？

Querysql
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
Responseresponse
┌─by───────┬─total_score─┬─total_sub_comments─┐
│ zX41ZdbW │        571  │              50    │
│ jetter   │        386  │              30    │
│ hodgesrm │        312  │              50    │
│ mechmind │        243  │              16    │
│ tosh     │        198  │              12    │
└──────────┴─────────────┴────────────────────┘

Parquet

ClickHouse の強みの 1 つは、さまざまなフォーマットを扱えることです。 CSV は非常に理想的なユースケースではありますが、データ交換の手段としては最も効率的とはいえません。

次に、効率的な列指向フォーマットである Parquet ファイルからデータを読み込みます。

Parquet の型は最小限に抑えられており、ClickHouse はそれに従う必要があります。この型情報はフォーマット自体にエンコードされています。 Parquet ファイルに対する型推論を行うと、CSV ファイルの場合とはわずかに異なるスキーマになります。

データを挿入する

次のクエリを実行して、同じデータを Parquet フォーマットで読み取ります。リモートデータの読み取りには、再び url 関数を使用します。

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

推定されたスキーマを表示するには、次のコマンドを実行します。

Querysql
DESCRIBE TABLE hackernews;
Responseresponse
┌─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 などのよりわかりやすいカラム名を使用するため、手動で指定したスキーマを使用します。 まず推論されたテーブルを削除し、次にテーブルを作成して、パブリック S3 バケットから直接データを挿入します。

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" に言及しているコメント数を確認するには、次のクエリを実行します。

Querysql
SELECT count(*)
FROM hackernews
WHERE hasAnyTokens(lower(comment), 'clickhouse');
Responseresponse
┌─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カラムにtext indexを作成します。テキスト索引では、トークンとそれを含む行を対応付ける転置索引を使用します。 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 テーブルで確認できます。

索引のマテリアライズ後に、同じクエリを再度実行します。

Querysql
SELECT count(*)
FROM hackernews
WHERE hasAnyTokens(lower(comment), 'clickhouse');
Responseresponse
┌─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が索引を適用する予定であることを確認します。

Querysql
EXPLAIN indexes = 1
SELECT count(*)
FROM hackernews
WHERE hasAnyTokens(lower(comment), 'clickhouse');
Responseresponse
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個を選択し、調査するデータ量を大幅に削減します。

複数のトークンについて、いずれかまたはすべてを検索することもできます。これらの関数は、索引トークナイザーによって生成された完全なトークンに一致します。 少なくとも1つのトークンが一致する必要がある場合は、hasAnyTokensを使用します。

Querysql
SELECT count(*)
FROM hackernews
WHERE hasAnyTokens(lower(comment), 'oltp olap');
Responseresponse
┌─count()─┐
│    2020 │
└─────────┘

すべてのトークンが順不同で一致する必要がある場合は、hasAllTokens を使用します。

Querysql
SELECT count(*)
FROM hackernews
WHERE hasAllTokens(lower(comment), 'avx sve');
Responseresponse
┌─count()─┐
│      22 │
└─────────┘
Navigation