Набор данных Hacker News

В этом руководстве вы загрузите 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При выводе схемы столбцы получают тип Nullable, поэтому требуется allow_nullable_key, хотя в этом наборе данных нет ID со значением NULL.

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

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

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 планирует использовать текстовый индекс. В этом примере план выбирает 547 из 3527 гранул, значительно сокращая объём обрабатываемых данных.Можно также искать один или все из нескольких токенов. Эти функции сопоставляют полные токены, сформированные токенизатором индекса. Используйте 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