Набор данных Hacker News - ClickHouse Documentation

В этом руководстве вы загрузите 28 миллионов строк данных Hacker News в таблицу ClickHouse из файлов в форматах CSV и Parquet и выполните несколько простых запросов, чтобы изучить эти данные.

CSV

Скачать CSV

CSV-версию датасета можно скачать из нашего публичного S3 бакета или с помощью этой команды:

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

При размере 4,6 ГБ и 28 млн строк загрузка этого сжатого файла должна занять 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. Что особенно важно, схема автоматически определяется по содержимому файла. Также обратите внимание, что clickhouse-local умеет читать сжатый файл, определяя формат gzip по расширению. Формат Vertical используется, чтобы удобнее было просматривать данные по каждому столбцу.

Загрузите данные с автоматическим определением схемы

Самый простой и мощный инструмент для загрузки данных — clickhouse-client, многофункциональный нативный клиент командной строки. Чтобы загрузить данные, можно снова воспользоваться автоматическим определением схемы и доверить ClickHouse определение типов столбцов.Выполните следующую команду, чтобы создать таблицу и сразу вставить данные из удалённого CSV-файла, обращаясь к его содержимому через функцию url. Схема будет определена автоматически:

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 позволяет понять, какие типы были назначены.

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

Вы успешно вставили 28 миллионов строк в 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

Хотя автоматическое определение схемы — отличный инструмент для первоначального изучения данных, оно работает по принципу «best effort» и в долгосрочной перспективе не заменяет явного определения оптимальной схемы для ваших данных.

Определите схему

Очевидная и простая оптимизация — задать тип для каждого поля. Помимо объявления поля времени с типом DateTime, мы зададим подходящий тип для каждого из перечисленных ниже полей после удаления существующего набора данных. В ClickHouse первичный ключ данных задаётся с помощью предложения ORDER BY.Выбор подходящих типов и определение того, какие столбцы включить в предложение ORDER BY, помогут повысить скорость запросов и улучшить сжатие.Выполните запрос ниже, чтобы удалить старую схему и создать улучшенную схему:

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

С оптимизированной схемой теперь можно выполнить вставку данных из локального файла. Снова используя 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 неизбежно приведёт к схеме, немного отличающейся от схемы 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')

Ключи со значением NULL в ParquetИз-за особенностей формата Parquet нужно учитывать, что ключи могут быть NULL, даже если в самих данных их нет.

Выполните следующую команду, чтобы просмотреть автоматически выведенную схему:

Response

┌─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)        │
└─────────────┴────────────────────────┴

Как и в случае с CSV-файлом, вы можете вручную указать схему, чтобы точнее контролировать выбранные типы, и напрямую вставить данные из S3:

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',
        '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 hasToken(lower(comment), 'ClickHouse');

Response

1 row in set. Elapsed: 0.843 sec. Processed 28.74 million rows, 9.75 GB (34.08 million rows/s., 11.57 GB/s.)
┌─count()─┐
│     516 │
└─────────┘

Далее вы создадите инвертированный индекс по столбцу “comment”, чтобы ускорить этот запрос. Обратите внимание, что комментарии в нижнем регистре будут проиндексированы, чтобы можно было находить термины независимо от регистра.Выполните следующие команды, чтобы создать индекс:

ALTER TABLE hackernews ADD INDEX comment_idx(lower(comment)) TYPE inverted;
ALTER TABLE hackernews MATERIALIZE INDEX comment_idx;

Материализация индекса занимает некоторое время (чтобы проверить, был ли создан индекс, используйте системную таблицу system.data_skipping_indices).После создания индекса снова выполните запрос:

Query

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

Обратите внимание: теперь запрос с индексом выполняется всего за 0.248 секунды вместо прежних 0.843 секунды без него:

Response

1 row in set. Elapsed: 0.248 sec. Processed 4.54 million rows, 1.79 GB (18.34 million rows/s., 7.24 GB/s.)
┌─count()─┐
│    1145 │
└─────────┘

Конструкцию EXPLAIN можно использовать, чтобы понять, почему добавление этого индекса ускорило запрос примерно в 3,4 раза.

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

Response

┌─explain─────────────────────────────────────────┐
│ Expression ((Projection + Before ORDER BY))     │
│   Aggregating                                   │
│     Expression (Before GROUP BY)                │
│       Filter (WHERE)                            │
│         ReadFromMergeTree (default.hackernews)  │
│         Indexes:                                │
│           PrimaryKey                            │
│             Condition: true                     │
│             Parts: 4/4                          │
│             Granules: 3528/3528                 │
│           Skip                                  │
│             Name: comment_idx                   │
│             Description: inverted GRANULARITY 1 │
│             Parts: 4/4                          │
│             Granules: 554/3528                  │
└─────────────────────────────────────────────────┘

Обратите внимание, как индекс позволил пропустить значительное количество гранул, что ускорило выполнение запроса.Теперь также можно эффективно искать по одному или сразу по всем нескольким терминам:

Query

SELECT count(*)
FROM hackernews
WHERE multiSearchAny(lower(comment), ['oltp', 'olap']);

Response

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

Query

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

Response

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

​CSV

​Скачать CSV

​Выборка данных

​Загрузите данные с автоматическим определением схемы

​Изучите данные

​Определите схему

​Выполнение примеров запросов

​Насколько часто обсуждается тема «ClickHouse» на Hacker News?

​Кто больше всего комментирует статьи, связанные с ClickHouse?

​Какие комментарии вызывают наибольший интерес?

​Parquet

​Вставка данных

​Добавьте пропускающий индекс, чтобы ускорить запросы

CSV

Скачать CSV

Выборка данных

Загрузите данные с автоматическим определением схемы

Изучите данные

Определите схему

Выполнение примеров запросов

Насколько часто обсуждается тема «ClickHouse» на Hacker News?

Кто больше всего комментирует статьи, связанные с ClickHouse?

Какие комментарии вызывают наибольший интерес?

Parquet

Вставка данных

Добавьте пропускающий индекс, чтобы ускорить запросы