Crea tu primera tabla MergeTree

Aprende cómo funciona el principal motor de tablas de ClickHouse creando una tabla MergeTree, cargando datos sobre precios de viviendas del Reino Unido y observando cómo las partes y las fusiones afectan al almacenamiento y al rendimiento de las consultas.

Todas las guías de inicio rápido

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

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 Create your first Cloud service quickstart 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.

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

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

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

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

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

6. 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:

• Referencia del motor MergeTree
• Referencia de system.parts
• Cómo elegir los tipos de columna adecuados

Todas las guías de inicio rápido