Consultas analíticas sobre datos de precios de inmuebles del Reino Unido

Explore ClickHouse con datos de precios de inmuebles del Reino Unido

En este tutorial explorará ClickHouse con el conjunto de datos UK Property Price, que recoge los precios pagados por bienes inmuebles en Inglaterra y Gales desde 1995.

​

Requisitos previos

Para este tutorial, necesitará:

• Una cuenta de ClickHouse Cloud (300 $ en créditos gratuitos al registrarse)
• Un servicio de ClickHouse Cloud

1. Cree la tabla

   1. Seleccione SQL console en el menú de la izquierda
   2. Haga clic en la pestaña + junto al icono de inicio para crear una nueva consulta
   3. En el editor SQL, escriba la siguiente consulta y haga clic en Run:

   CREATE DATABASE uk;
   
   CREATE TABLE uk.uk_price_paid
   (
     price UInt32,
     date Date,
     postcode1 LowCardinality(String),
     postcode2 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
   ORDER BY (postcode1, postcode2, addr1, addr2);
   

   Fíjese en ORDER BY (postcode1, postcode2, addr1, addr2), que define cómo ClickHouse ordena los datos en disco. En ClickHouse, elegir una clave primaria eficaz que se ajuste a sus patrones de acceso es fundamental para el rendimiento de las consultas y la eficiencia del almacenamiento. Consulte “Elegir una clave primaria” para obtener más información.

   Consulte https://www.gov.uk para ver una descripción de los campos.

2. Preprocese e inserte los datos

   Puede usar la función url para transmitir datos a ClickHouse. Antes es necesario realizar cierto preprocesamiento. La siguiente consulta inserta más de 25 millones de filas en la tabla uk_price_paid y realiza estos pasos de preprocesamiento:

   • divide postcode en dos columnas distintas, postcode1 y postcode2, lo que resulta más conveniente para el almacenamiento y las consultas
   • convierte el campo time a fecha, ya que solo contiene la hora 00:00
   • ignora el campo UUID porque no es necesario para el análisis
   • transforma type y duration en campos Enum más legibles mediante la función transform
   • transforma el campo is_new de una cadena de un solo carácter (Y/N) a un campo UInt8 con valor 0 o 1
   • elimina las dos últimas columnas, ya que todas sus entradas tienen el mismo valor (0)

   INSERT INTO uk.uk_price_paid
   SELECT
     toUInt32(price_string) AS price,
     parseDateTimeBestEffortUS(time) AS date,
     splitByChar(' ', postcode)[1] AS postcode1,
     splitByChar(' ', postcode)[2] AS postcode2,
     transform(a, ['T', 'S', 'D', 'F', 'O'], ['terraced', 'semi-detached', 'detached', 'flat', 'other']) AS type,
     b = 'Y' AS is_new,
     transform(c, ['F', 'L', 'U'], ['freehold', 'leasehold', 'unknown']) AS duration,
     addr1,
     addr2,
     street,
     locality,
     town,
     district,
     county
   FROM url(
     'http://prod1.publicdata.landregistry.gov.uk.s3-website-eu-west-1.amazonaws.com/pp-complete.csv',
     'CSV',
     'uuid_string String,
     price_string String,
     time String,
     postcode String,
     a String,
     b String,
     c String,
     addr1 String,
     addr2 String,
     street String,
     locality String,
     town String,
     district String,
     county String,
     d String,
     e String'
   ) SETTINGS max_http_get_redirects=10;
   

   Espere a que se inserten los datos; el proceso tardará uno o dos minutos, según la velocidad de la red.

3. Valide los datos

   Comprobemos que ha funcionado consultando cuántas filas se han insertado:

   SELECT count()
   FROM uk.uk_price_paid
   

   Cuando se ejecutó esta consulta, el conjunto de datos tenía 27.450.499 filas. Veamos cuánto espacio de almacenamiento ocupa la tabla en ClickHouse:

   SELECT formatReadableSize(total_bytes)
   FROM system.tables
   WHERE name = 'uk_price_paid'
   

   Observe que el tamaño de la tabla es de apenas 221.43 MiB, mientras que el conjunto de datos original sin comprimir ocupa aproximadamente 4 GiB. ClickHouse ofrece una excelente compresión de datos de forma predeterminada, pero también le permite ajustar aún más la compresión de cada columna si lo necesita.

4. Ejecute algunas consultas

   Con los datos ya cargados, pruebe las siguientes consultas para hacerse una idea de lo rápido que las consultas analíticas devuelven resultados. La siguiente consulta calcula el precio medio por año con todos los datos:

   SELECT
     toYear(date) AS year,
     round(avg(price)) AS price,
     bar(price, 0, 1000000, 80
   )
   FROM uk.uk_price_paid
   GROUP BY year
   ORDER BY year
   

   La siguiente consulta aplica un filtro para obtener el precio medio por año en Londres:

   SELECT
     toYear(date) AS year,
     round(avg(price)) AS price,
     bar(price, 0, 2000000, 100
   )
   FROM uk.uk_price_paid
   WHERE town = 'LONDON'
   GROUP BY year
   ORDER BY year
   

   ¡Parece que algo les pasó a los precios de la vivienda en 2020! Aunque probablemente no sea ninguna sorpresa…

   La siguiente consulta obtiene los barrios más caros:

   SELECT
     town,
     district,
     count() AS c,
     round(avg(price)) AS price,
     bar(price, 0, 5000000, 100)
   FROM uk.uk_price_paid
   WHERE date >= '2020-01-01'
   GROUP BY
     town,
     district
   HAVING c >= 100
   ORDER BY price DESC
   LIMIT 100
   

​

Próximos pasos

En este tutorial creaste una tabla, preprocesaste y cargaste en ClickHouse los datos de precios de inmuebles del Reino Unido y, después, ejecutaste algunas consultas analíticas sobre esos datos.

A continuación, puedes:

• Aprender a acelerar estas consultas con proyecciones. Consulta “Proyecciones” para ver ejemplos que utilizan el mismo conjunto de datos.
• Obtener más información sobre los conceptos fundamentales de ClickHouse
• Explorar las buenas prácticas de ClickHouse
• Explorar otros conjuntos de datos de ejemplo