Dataset de Hacker News - ClickHouse Documentation

En este tutorial, insertarás 28 millones de filas de datos de Hacker News en una tabla de ClickHouse desde archivos CSV y Parquet, y ejecutarás algunas consultas sencillas para explorar los datos.

CSV

Descargar CSV

Se puede descargar una versión en CSV del conjunto de datos desde nuestro bucket público de S3, o ejecutando este comando:

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

Con 4,6 GB y 28 millones de filas, este archivo comprimido debería tardar entre 5 y 10 minutos en descargarse.

Tomar una muestra de los datos

clickhouse-local permite procesar rápidamente archivos locales sin tener que desplegar y configurar el servidor de ClickHouse.Antes de almacenar datos en ClickHouse, tomemos una muestra del archivo con clickhouse-local. En la consola, ejecute:

clickhouse-local

A continuación, ejecute este comando para explorar los datos:

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

Este comando tiene muchas funciones sutiles. El operador file le permite leer el archivo desde un disco local, especificando solo el formato CSVWithNames. Lo más importante es que el esquema se infiere automáticamente a partir del contenido del archivo. Observe también cómo clickhouse-local puede leer el archivo comprimido, infiriendo el formato gzip a partir de la extensión. El formato Vertical se utiliza para ver más fácilmente los datos de cada columna.

Cargar los datos con inferencia de esquema

La herramienta más sencilla y potente para cargar datos es clickhouse-client: un cliente nativo de línea de comandos con numerosas funciones. Para cargar datos, también puedes aprovechar la inferencia de esquema y dejar que ClickHouse determine los tipos de las columnas.Ejecuta el siguiente comando para crear una tabla e insertar los datos directamente desde el archivo CSV remoto, accediendo al contenido mediante la función url. El esquema se infiere automáticamente:

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

Esto crea una tabla vacía usando el esquema inferido a partir de los datos. El comando DESCRIBE TABLE nos permite ver los tipos asignados.

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

Para insertar los datos en esta tabla, use el comando INSERT INTO, SELECT. Con la función url, los datos se transmitirán directamente desde la URL:

INSERT INTO hackernews SELECT *
FROM url('https://datasets-documentation.s3.eu-west-3.amazonaws.com/hackernews/hacknernews.csv.gz', 'CSVWithNames')

¡Has insertado 28 millones de filas en ClickHouse con un solo comando!

Explorar los datos

Obtenga una muestra de las historias de Hacker News y de columnas concretas ejecutando la siguiente consulta:

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

Aunque la inferencia de esquemas es una excelente herramienta para la exploración inicial de datos, funciona con un criterio de «mejor esfuerzo» y no sustituye a largo plazo la definición de un esquema óptimo para sus datos.

Definir un esquema

Una optimización inmediata y evidente es definir un tipo para cada campo. Además de declarar el campo de tiempo como de tipo DateTime, definimos un tipo adecuado para cada uno de los campos siguientes después de eliminar el dataset existente. En ClickHouse, el id de la clave primaria de los datos se define mediante la cláusula ORDER BY.Seleccionar los tipos adecuados y elegir qué columnas incluir en la cláusula ORDER BY ayudará a mejorar la velocidad de las consultas y la compresión.Ejecute la consulta siguiente para eliminar el esquema anterior y crear el esquema mejorado:

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

Con un esquema optimizado, ahora puedes insertar los datos desde el sistema de archivos local. Una vez más, con clickhouse-client, inserta el archivo mediante la cláusula INFILE con un INSERT INTO explícito.

Query

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

Ejecutar consultas de muestra

A continuación se presentan algunas consultas de ejemplo para que puedas inspirarte al escribir tus propias consultas.

¿Con qué frecuencia aparece el tema “ClickHouse” en Hacker News?

El campo score proporciona una métrica de popularidad para las historias, mientras que el campo id y el operador de concatenación || pueden usarse para generar un enlace a la publicación original.

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

¿Está ClickHouse generando más ruido con el tiempo? Aquí se muestra la utilidad de definir el campo time como DateTime, ya que usar un tipo de dato adecuado permite utilizar la función 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 │ ▏                        │
└───────────┴──────────────────────────┘

Parece que “ClickHouse” está ganando popularidad con el paso del tiempo.

¿Quiénes son los principales comentaristas en los artículos relacionados con 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 │
└─────────────┴──────────┘

¿Qué comentarios generan más interés?

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

Uno de los puntos fuertes de ClickHouse es su capacidad para manejar todo tipo de formatos. CSV representa un caso de uso bastante ideal, pero no es el más eficiente para el intercambio de datos. A continuación, cargarás los datos desde un archivo Parquet, que es un formato eficiente orientado a columna. Parquet tiene un conjunto mínimo de tipos, que ClickHouse debe respetar, y esta información de tipos está codificada en el propio formato. La inferencia de tipos a partir de un archivo Parquet conducirá invariablemente a un esquema ligeramente distinto del archivo CSV.

Insertar los datos

Ejecuta la siguiente consulta para leer los mismos datos en formato Parquet, de nuevo con la función url para leer los datos remotos:

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

Claves nulas en ParquetComo requisito del formato Parquet, tenemos que aceptar que las claves puedan ser NULL, aunque no lo sean en los datos.

Ejecuta el siguiente comando para ver el esquema inferido:

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

Al igual que con el archivo CSV, puede especificar manualmente el esquema para tener un mayor control sobre los tipos elegidos e insertar los datos directamente desde 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');

Añade un índice de descarte para acelerar las consultas

Para averiguar cuántos comentarios mencionan “ClickHouse”, ejecuta la siguiente consulta:

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 │
└─────────┘

A continuación, crearás un índice invertido en la columna “comment” para acelerar esta consulta. Ten en cuenta que se indexarán los comentarios en minúsculas para encontrar términos sin distinguir entre mayúsculas y minúsculas.Ejecuta los siguientes comandos para crear el índice:

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

La materialización del índice tarda un poco (para comprobar si el índice se ha creado, usa la tabla del sistema system.data_skipping_indices).Vuelve a ejecutar la consulta una vez que se haya creado el índice:

Query

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

Observa cómo la consulta ahora tarda solo 0.248 segundos con el índice, frente a los 0.843 segundos anteriores sin él:

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 │
└─────────┘

La cláusula EXPLAIN puede usarse para entender por qué la incorporación de este índice mejoró la consulta aproximadamente 3,4 veces.

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                  │
└─────────────────────────────────────────────────┘

Observa cómo el índice permitió omitir una cantidad considerable de gránulos para acelerar la consulta.Ahora también es posible buscar de forma eficiente uno o varios términos, o todos ellos:

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

​¿Con qué frecuencia aparece el tema “ClickHouse” en Hacker News?

​¿Quiénes son los principales comentaristas en los artículos relacionados con ClickHouse?

​¿Qué comentarios generan más interés?

​Parquet

CSV

¿Con qué frecuencia aparece el tema “ClickHouse” en Hacker News?

¿Quiénes son los principales comentaristas en los artículos relacionados con ClickHouse?

¿Qué comentarios generan más interés?

Parquet