ClickHouse es un DBMS de código abierto orientado a columnas, creado por Yandex para su servicio de analítica web (el segundo más grande después de Google Analytics).
El objetivo de este post es introducirte a las bases de datos orientadas a columnas y que tu mismo compruebes si pueden ser útiles en retos que tengas ahora o en el futuro.
# Dataset
Vamos a descargar datos desde Github Archive (opens new window) para realizar las pruebas.
Descargamos todos los eventos para agosto de 2020 son 46 469 374 filas y pesa en total 4 GB.
Utilizamos curl
para descargar, parallel
para levantar varios procesos y jq
para procesar
la respuesta JSON y crear un fichero CSV.
parallel -j 6 curl ::: https://data.gharchive.org/2020-08-{01..31}-{0..23}.json.gz | \
gzip -d | \
jq -r "[.id, .type, .created_at, .actor.login, .repo.name] | @csv" > data.csv
# Contenedores
Mientras se completa la descarga, vamos arrancando los contenedores docker para PostgreSQL y para ClickHouse.
# PostgreSQL
Abrimos una terminal y ejecutamos:
docker run -d -v $(pwd):/data --name postgres -e POSTGRES_PASSWORD=password postgres
docker exec -it postgres psql -U postgres
Ya tenemos lista la consola para ejecutar comandos.
# ClickHouse
Abrimos otra terminal y ejecutamos:
docker run -d -v $(pwd):/data --name clickhouse yandex/clickhouse-server
docker exec -it clickhouse clickhouse-client
También tenemos lista la consola para ejecutar comandos.
# Base de datos y tablas
Vamos a crear una base de datos y una tabla.
# PostgreSQL
CREATE DATABASE test_database;
\c test_database;
CREATE TABLE gh_events (
id bigint,
event varchar,
date timestamp with time zone,
username varchar,
repository varchar
);
# ClickHouse
CREATE DATABASE test_database;
USE test_database;
CREATE TABLE gh_events (
id Int64,
event String,
date DateTime('UTC'),
username String,
repository String
) ENGINE = MergeTree ORDER BY (username);
# Importar el dataset
Importamos el CSV (una vez terminen de descargarse).
# PostgreSQL
COPY gh_events FROM '/data/data.csv' WITH (FORMAT csv);
La tabla pesa 4 599 MB.
# ClickHouse
Para importar en ClickHouse se utiliza el comando clickhouse-client
, por lo que necesitamos una nueva terminal:
docker exec -it clickhouse bash
Y ejecutamos:
clickhouse-client -d test_database --date_time_input_format best_effort --query="INSERT INTO gh_events FORMAT CSV" < /data/data.csv
La tabla pesa 746 MB.
# Queries
¡Empezamos a lanzar queries!
Antes de nada, activamos el
timing
en PostgreSQL con:\timing
# Query 1
SELECT username, COUNT(*)
FROM gh_events
GROUP BY username
ORDER BY COUNT(*)
DESC LIMIT 10;
# Query 2
SELECT event, COUNT(*)
FROM gh_events
GROUP BY event
ORDER BY COUNT(*)
DESC LIMIT 10;
# Query 3
SELECT repository, COUNT(*)
FROM gh_events
GROUP BY repository
ORDER BY COUNT(*)
DESC LIMIT 10;
# Query 4
-- postgresql
SELECT date_trunc('day', "date" AT TIME ZONE 'UTC') AS day, COUNT(*)
FROM gh_events
GROUP BY date_trunc('day', "date" AT TIME ZONE 'UTC')
ORDER BY day ASC, COUNT(*);
-- clickhouse
SELECT toStartOfDay(date) AS day, COUNT(*)
FROM gh_events
GROUP BY toStartOfDay(date)
ORDER BY day ASC, COUNT(*) DESC;
# Query 5
-- postgresql
SELECT date_trunc('hour', "date" AT TIME ZONE 'UTC') AS hour, COUNT(*)
FROM gh_events
WHERE date BETWEEN '2020-08-01 00:00:00' AND '2020-08-01 23:59:59'
GROUP BY date_trunc('hour', "date" AT TIME ZONE 'UTC')
ORDER BY hour ASC, COUNT(*);
-- clickhouse
SELECT toStartOfHour(date) AS hour, COUNT(*)
FROM gh_events
WHERE date BETWEEN '2020-08-01 00:00:00' AND '2020-08-01 23:59:59'
GROUP BY toStartOfHour(date)
ORDER BY hour ASC, COUNT(*) DESC;
# Resultados
Query | PostgreSQL time (ms) | ClickHouse time (ms) |
---|---|---|
1 | 44 420 | 611 |
2 | 9 966 | 274 |
3 | 59 702 | 2 085 |
4 | 17 527 | 252 |
5 | 10 595 | 170 |
La ventaja que ofrece ClickHouse en consultas a grandes cantidades de datos es evidente.
Quizás te estás preguntando si mejoraría el rendimiento en PostgreSQL con unos buenos índices. Puedes probarlo tu mismo, pero te adelanto que no mejora mucho. La diferencia se debe a la cantidad de datos que son leídos de disco.
# Limpiando
Vamos a limpiar todo:
rm -rf data.csv
docker stop postgres clickhouse
docker rm postgres clickhouse
# Para saber más
Tan solo hemos probado superficialmente ClickHouse, recomiendo echar un vistazo a la documentación oficial (opens new window). Por ejemplo, una de las características más interesante de ClickHouse es el motor AggregatingMergeTree (opens new window).
También recomiendo seguir a Javi Santana en Twitch (opens new window), está haciendo unos streams muy interesantes de ClickHouse.
Además, dejo algunos enlaces que me han ayudado con este post: