El conjunto de datos de precios de propiedades en el Reino Unido

Aprende a usar proyecciones para mejorar el rendimiento de las consultas que ejecutas con frecuencia con el conjunto de datos de propiedades del Reino Unido, que contiene datos sobre los precios pagados por propiedades inmobiliarias en Inglaterra y Gales

Estos datos incluyen los precios pagados por inmuebles en Inglaterra y Gales. Están disponibles desde 1995, y el tamaño del conjunto de datos sin comprimir es de aproximadamente 4 GiB (que en ClickHouse solo ocupan unos 278 MiB).

• Fuente: https://www.gov.uk/government/statistical-data-sets/price-paid-data-downloads
• Descripción de los campos: https://www.gov.uk/guidance/about-the-price-paid-data
• Incluye datos de HM Land Registry © Crown copyright y derechos sobre la base de datos de 2021. Estos datos se distribuyen bajo la licencia Open Government Licence v3.0.

​

Crear la tabla

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

​

Preprocesar e insertar los datos

Usaremos la función url para cargar los datos en ClickHouse. Primero necesitamos preprocesar parte de los datos entrantes, lo que incluye:

• dividir postcode en dos columnas distintas, postcode1 y postcode2, lo que mejora el almacenamiento y las consultas
• convertir el campo time a fecha, ya que solo contiene la hora 00:00
• ignorar el campo UUid porque no lo necesitamos para el análisis
• transformar type y duration en campos Enum más legibles mediante la función transform
• transformar el campo is_new de una cadena de un solo carácter (Y/N) a un campo UInt8 con valor 0 o 1
• eliminar las dos últimas columnas, ya que todas tienen el mismo valor (0)

La función url envía los datos desde el servidor web a su tabla de ClickHouse. El siguiente comando inserta 5 millones de filas en la tabla uk_price_paid:

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;

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

​

Validar los datos

Verifiquemos que funcionó comprobando cuántas filas se insertaron:

En el momento en que 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:

¡Observa que el tamaño de la tabla es de solo 221.43 MiB!

​

Ejecutar algunas consultas

Vamos a ejecutar algunas consultas para analizar los datos:

​

Consulta 1. Precio medio por año

​

Consulta 2. precio medio por año en Londres

¡Algo pasó con los precios de la vivienda en 2020! Pero probablemente no sorprenda…

​

Consulta 3. Los barrios más caros

​

Acelerar las consultas con proyecciones

Estas consultas pueden acelerarse con proyecciones. Consulta “Proyecciones” para ver ejemplos con este conjunto de datos.

​

Pruébelo en Playground

Este conjunto de datos también está disponible en el Online Playground.