Crea tu primera tabla MergeTree

Analítica en tiempo realAlmacén de datosObservabilidadIA/MLCloud

Requisitos previos

To successfully follow this guide, you’ll need the following:

A running ClickHouse Cloud service. If you don’t have one yet, complete the ClickHouse Cloud quick start first.

Lo que crearás

En esta guía de inicio rápido crearás una tabla MergeTree para almacenar registros de compraventa de viviendas del Reino Unido desde 1995. Diseñarás un esquema con tipos de columna adecuados, elegirás un ORDER BY y un PARTITION BY apropiados, cargarás datos directamente desde S3 y luego consultarás system.parts para ver cómo ClickHouse organiza físicamente los datos en disco. Al final, entenderás por qué el motor MergeTree es la base de casi todas las tablas de ClickHouse y cómo sus decisiones de ordenación y particionamiento influyen directamente en el rendimiento de las consultas.

Comprende cómo funciona MergeTree

Antes de escribir cualquier SQL, conviene entender qué hace que MergeTree sea diferente de una tabla de base de datos tradicional.Cuando insertas datos en una tabla MergeTree, ClickHouse no escribe las filas una por una. En su lugar, escribe una parte de datos -un pequeño fragmento de filas ordenado y comprimido- directamente en disco. Después, ClickHouse fusiona estas partes gradualmente en segundo plano. De ahí viene el nombre: merge + tree.Cada parte de datos se ordena según la expresión ORDER BY de la tabla. Este orden se convierte en el índice de clave primaria, lo que permite a ClickHouse omitir grandes bloques de datos que no necesita leer durante una consulta (a esto lo llamamos poda de datos). Cuanto más selectivas sean las columnas de ORDER BY para tus consultas más habituales, menos datos leerá ClickHouse.Tres cláusulas controlan cómo MergeTree organiza tus datos:

Cláusula	Qué hace
`ORDER BY`	Ordena físicamente los datos dentro de cada parte. Determina la clave primaria. Es obligatorio.
`PARTITION BY`	Divide los datos en particiones independientes, normalmente por un intervalo de fechas. Las partes de distintas particiones nunca se fusionan entre sí, lo que permite una poda de particiones rápida.
`PRIMARY KEY`	De forma predeterminada, usa `ORDER BY`, a menos que establezcas explícitamente un prefijo más corto. A partir de esto se construye el índice disperso.

Ahora deberías poder explicar la relación entre las partes de datos, la clave primaria y el rendimiento de las consultas en una tabla MergeTree.

Vista previa de los datos de origen

Antes de crear su tabla, inspeccione el archivo de origen con la función de tabla s3. Esto le permite consultar S3 directamente sin tener que escribir antes ningún dato en ClickHouse.Ejecute lo siguiente en su consola SQL:

DESCRIBE s3(
'https://learn-clickhouse.s3.us-east-2.amazonaws.com/uk_property_prices/uk_prices.csv.zst'
);

Ten en cuenta que casi todas las columnas se infieren como Nullable(String). ClickHouse está leyendo un CSV sin procesar, por lo que no conoce los tipos de datos reales; eso es algo que corregirás cuando diseñes el esquema de tu tabla en el siguiente paso.Previsualiza unas cuantas filas:

SELECT *
FROM s3(
'https://learn-clickhouse.s3.us-east-2.amazonaws.com/uk_property_prices/uk_prices.csv.zst'
)
LIMIT 5;

El conjunto de datos contiene ventas de propiedades residenciales en Inglaterra y Gales registradas en HM Land Registry, incluido el id de la transacción, el price de venta, la date, el type de propiedad, campos de dirección e identificadores geográficos. También observarás dos columnas finales (column15, column16) vacías; pueden ignorarse.Verifica esto confirmando que puedes ver filas con columnas que incluyen id, price, date, postcode, type, town y county.

Diseña y crea tu tabla MergeTree

Ahora crea una tabla permanente con un esquema adecuado. Los siguientes tipos de columna se han elegido deliberadamente:

LowCardinality(String) se usa para columnas con un número limitado de valores únicos (códigos postales, nombres de localidades, nombres de condados). Utiliza codificación por diccionario internamente y reduce drásticamente el espacio de almacenamiento, además de mejorar el rendimiento de la agrupación y el filtrado en estas columnas.
Enum8 codifica las columnas type y duration como enteros pequeños en disco, al tiempo que mantiene etiquetas de texto legibles en las consultas. El CSV de origen usa códigos de una sola letra, así que los asignaremos durante la inserción.
PARTITION BY toYYYYMM(date) crea una partición por mes natural, lo que permite a ClickHouse omitir meses completos cuando la cláusula WHERE filtra por date.
ORDER BY (postcode, addr1, addr2) ordena los datos para permitir búsquedas rápidas por dirección de la propiedad, el patrón de acceso más natural para este conjunto de datos.

CREATE TABLE uk_price_paid
(
price      UInt32,
date       Date,
postcode   LowCardinality(String),
type       Enum8('terraced' = 1, 'semi-detached' = 2, 'detached' = 3, 'flat' = 4, 'other' = 0),
is_new     UInt8,
duration   Enum8('freehold' = 1, 'leasehold' = 2, 'unknown' = 0),
addr1      String,
addr2      String,
street     LowCardinality(String),
locality   LowCardinality(String),
town       LowCardinality(String),
district   LowCardinality(String),
county     LowCardinality(String)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(date)
ORDER BY (postcode, addr1, addr2);

Verifica que la tabla se haya creado ejecutando:

SHOW CREATE TABLE uk_price_paid;

Haz doble clic en la celda del resultado para inspeccionar la salida completa. Observa que, aunque especificaste ENGINE = MergeTree, ClickHouse Cloud ha creado la tabla con SharedMergeTree('/clickhouse/tables/{uuid}/{shard}', '{replica}'). Esto es normal: Cloud convierte automáticamente MergeTree en SharedMergeTree, lo que añade compatibilidad con la replicación y el almacenamiento compartido. El comportamiento y la interfaz de consulta siguen siendo los mismos.

Cargar datos desde S3

Inserte el conjunto de datos completo seleccionándolo directamente desde la función de tabla s3(). ClickHouse transmite el archivo comprimido desde S3 y lo escribe en su tabla como partes ordenadas.

INSERT INTO uk_price_paid
SELECT
    toUInt32(price),
    date,
    postcode,
    transform(type, ['T', 'S', 'D', 'F', 'O'],
        ['terraced', 'semi-detached', 'detached', 'flat', 'other'], 'other') AS type,
    if(is_new = 'Y', 1, 0) AS is_new,
    transform(duration, ['F', 'L', 'U'],
        ['freehold', 'leasehold', 'unknown'], 'unknown') AS duration,
    addr1,
    addr2,
    street,
    locality,
    town,
    district,
    county
FROM s3(
'https://learn-clickhouse.s3.us-east-2.amazonaws.com/uk_property_prices/uk_prices.csv.zst'
);

Dado que el CSV de origen almacena todo como cadenas con códigos de una sola letra (p. ej., T para vivienda adosada, F para plena propiedad, Y/N para obra nueva), usamos transform para asignarles etiquetas legibles y toUInt32/if para convertir las columnas numéricas. Las columnas id, column15 y column16 se excluyen porque no las necesitamos.Esto tardará uno o dos minutos, según el tamaño de tu servicio. Cuando termine, confirma el recuento de filas:

SELECT formatReadableQuantity(count())
FROM uk_price_paid;

Deberías ver que se han cargado aproximadamente 30 millones de filas.

Inspeccionar las partes con system.parts

Aquí es donde se hacen visibles los componentes internos de MergeTree. La tabla system.parts realiza un seguimiento de cada parte de datos almacenada en disco de cada tabla MergeTree de su servicio.

SELECT
partition,
name,
rows,
bytes_on_disk,
marks
FROM system.parts
WHERE table = 'uk_price_paid'
AND active = true
ORDER BY partition
LIMIT 20;

Cada fila representa una parte de datos activa. Observa:

partition - el valor YYYYMM derivado de tu expresión PARTITION BY. Los datos de cada mes quedan aislados.
name - el nombre de la parte codifica la partición, el rango de números de bloque y el nivel de fusión (p. ej., 199501_1_4_2 significaría la partición 199501, los bloques 1–4, fusionados dos veces).
marks - el número de gránulos de índice. Cada gránulo cubre 8.192 filas de forma predeterminada, y el índice de clave primaria almacena una entrada por gránulo. Este índice disperso es el que permanece en memoria y permite descartar datos rápidamente.
bytes_on_disk - ClickHouse comprime cada parte columna por columna usando LZ4 de forma predeterminada. Compáralo con el tamaño sin comprimir para apreciar la relación de compresión.

Para ver el número total de partes y el tamaño comprimido total de tu tabla, ejecuta:

SELECT
count()          AS parts,
sum(rows)        AS total_rows,
formatReadableSize(sum(bytes_on_disk)) AS compressed_size
FROM system.parts
WHERE table = 'uk_price_paid'
AND active = true;

Si ejecuta esta consulta de nuevo más adelante, puede que observe que la cantidad de partes ha disminuido. Este es el merge de MergeTree en acción: ClickHouse fusiona continuamente las partes más pequeñas para formar otras más grandes en segundo plano, lo que reduce la cantidad de partes. El filtro active = true garantiza que solo vea las partes actuales, ya fusionadas, en lugar de partes antiguas pendientes de limpieza.

Consulta los datos y observa el comportamiento de la clave primaria

Ahora ejecuta algunas consultas analíticas reales. Primero, identifica las ventas de mayor importe jamás registradas:

SELECT
addr1,
addr2,
town,
county,
price,
date
FROM uk_price_paid
ORDER BY price DESC
LIMIT 5;

Comprueba las estadísticas de la consulta en la consola SQL: observa que se leyeron las 30,033,199 filas. Como price no forma parte de la clave ORDER BY, ClickHouse no puede usar el índice primario para omitir datos y debe recorrer toda la tabla.A continuación, busca el precio medio de venta por condado:

SELECT
county,
round(avg(price)) AS avg_price,
count()           AS sales
FROM uk_price_paid
GROUP BY county
ORDER BY avg_price DESC;

De nuevo, se leen las 30,033,199 filas: county no está en el ORDER BY ni en el PARTITION BY, por lo que ClickHouse escanea toda la tabla.Ahora ejecuta una consulta que combine la agregación con tu ORDER BY. Como los datos están ordenados por (postcode, addr1, addr2), filtrar por un prefijo de código postal permite a ClickHouse omitir gran parte de la tabla. Aquí calculamos el precio medio de venta por año de las propiedades de la zona del código postal SW1A:

SELECT
toYear(date) AS year,
round(avg(price)) AS avg_price,
count() AS sales,
min(price) AS cheapest,
max(price) AS most_expensive
FROM uk_price_paid
WHERE postcode LIKE 'SW1A%'
GROUP BY year
ORDER BY year DESC;

Comprueba las estadísticas de la consulta en la consola SQL después de cada consulta. La agregación filtrada por postcode debería leer solo una fracción de las filas de la tabla, lo que demuestra cómo funciona el índice de clave primaria. Compárala con las consultas anteriores, que escanean la tabla de forma más amplia; la diferencia muestra por qué es importante elegir el ORDER BY adecuado.

Siguientes pasos

En esta guía de inicio rápido, creaste una tabla MergeTree desde cero, cargaste 30 millones de registros de compraventa de propiedades del Reino Unido desde S3, exploraste cómo ClickHouse organiza los datos en partes y particiones ordenadas, y ejecutaste consultas que demuestran la potencia del índice de la clave primaria. El motor MergeTree es la base; a partir de aquí, puedes explorar los motores especializados basados en él o aprender cómo las vistas materializadas amplían aún más este patrón. A continuación, consulta estas guías de inicio rápido:

Introducción a las vistas materializadas

O profundiza con la documentación de referencia:

ClickHouse Academy — Master ClickHouse with expert-designed training for every skill level — Check out the ClickHouse academy for on-demand and live training

All quickstarts

Get started

Setup

Sample datasets

Migrate

Requisitos previos

Lo que crearás

Comprende cómo funciona MergeTree

Vista previa de los datos de origen

Diseña y crea tu tabla MergeTree

Cargar datos desde S3

Inspeccionar las partes con system.parts

Consulta los datos y observa el comportamiento de la clave primaria

Siguientes pasos

​Requisitos previos

​Lo que crearás

Comprende cómo funciona MergeTree

Vista previa de los datos de origen

Diseña y crea tu tabla MergeTree

Cargar datos desde S3

Inspeccionar las partes con system.parts

Consulta los datos y observa el comportamiento de la clave primaria

​Siguientes pasos

Requisitos previos

Lo que crearás

Siguientes pasos