Dataset de Hacker News | ClickHouse Docs

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:

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

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.

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

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:

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

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:

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

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.

Querysql

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.

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

¿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():

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

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

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

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

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

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.

Inserte los datos

Ejecute la siguiente consulta para leer los mismos datos en formato Parquet, utilizando de nuevo 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');

Ejecute el siguiente comando para ver el esquema inferido:

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

Los pasos restantes usan nombres de columna más claros, como author y comment, por lo que debe continuar con un esquema especificado manualmente. Primero, elimine la tabla inferida; a continuación, cree la tabla e inserte los datos directamente desde el bucket público de 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');

Añada un índice de texto para agilizar las búsquedas

Para saber cuántos comentarios mencionan "ClickHouse", ejecuta la siguiente consulta:

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

A continuación, cree un índice de texto en la columna comment para acelerar esta consulta. Un índice de texto utiliza un índice invertido que asigna los tokens a las filas que los contienen. El tokenizador splitByNonAlpha divide el texto por caracteres no alfanuméricos. El índice y las consultas usan lower(comment) con términos de búsqueda en minúsculas, por lo que las coincidencias no distinguen entre mayúsculas y minúsculas. La expresión de la consulta debe coincidir con la expresión indexada.

Ejecute los siguientes comandos para crear el índice:

ALTER TABLE hackernews
    ADD INDEX comment_idx lower(comment)
    TYPE text(tokenizer = splitByNonAlpha);

ALTER TABLE hackernews
    MATERIALIZE INDEX comment_idx
    SETTINGS mutations_sync = 2;

La materialización crea el índice para los datos existentes. La configuración mutations_sync espera a que finalice la materialización. Puede consultar la definición del índice en la tabla system.data_skipping_indices.

Ejecute de nuevo la misma consulta una vez materializado el índice:

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

El resultado sigue siendo el mismo porque el índice cambia la forma en que ClickHouse encuentra las filas coincidentes, no cuáles coinciden. La consulta indexada procesa considerablemente menos datos y se completa mucho más rápido. Use EXPLAIN para confirmar que ClickHouse tiene previsto aplicar el índice:

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

La entrada comment_idx muestra que ClickHouse prevé aplicar el índice de texto. En este ejemplo, el plan selecciona 547 de 3527 gránulos, lo que reduce considerablemente la cantidad de datos examinados.

También puede buscar uno o varios tokens. Estas funciones buscan coincidencias con tokens completos generados por el tokenizador del índice. Use hasAnyTokens cuando deba coincidir al menos un token:

Querysql

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

Responseresponse

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

Use hasAllTokens cuando todos los tokens deban coincidir, en cualquier orden:

Querysql

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

Responseresponse

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