Le jeu de données sur les prix de l’immobilier au Royaume-Uni

Ces données contiennent les prix payés pour des biens immobiliers en Angleterre et au pays de Galles. Elles sont disponibles depuis 1995, et la taille du jeu de données au format non compressé est d’environ 4 GiB (ce qui ne représentera qu’environ 278 MiB dans ClickHouse).

• Source : https://www.gov.uk/government/statistical-data-sets/price-paid-data-downloads
• Description des champs : https://www.gov.uk/guidance/about-the-price-paid-data
• Contient des données du HM Land Registry © Crown copyright et droit sur la base de données 2021. Ces données sont publiées sous la licence Open Government Licence v3.0.

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

Nous allons utiliser la fonction url pour ingérer les données dans ClickHouse en flux. Nous devons d'abord prétraiter une partie des données entrantes, ce qui comprend :

• scinder postcode en deux colonnes distinctes, postcode1 et postcode2, ce qui est préférable pour le stockage et les requêtes
• convertir le champ time en Date, puisqu'il ne contient que l'heure 00:00
• ignorer le champ UUid, car nous n'en avons pas besoin pour l'analyse
• convertir type et duration en champs Enum plus explicites à l'aide de la fonction transform
• convertir le champ is_new d'une chaîne d'un seul caractère (Y/N) en un champ UInt8 avec 0 ou 1
• supprimer les deux dernières colonnes, puisqu'elles ont toutes la même valeur (0)

La fonction url transmet les données en flux depuis le serveur web vers votre table ClickHouse. La commande suivante insère 5 millions de lignes dans la table 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;

Attendez la fin de l’insertion des données : cela prendra une à deux minutes, selon la vitesse du réseau.

Vérifions que tout a bien fonctionné en regardant combien de lignes ont été insérées :

SELECT count()
FROM uk.uk_price_paid

Au moment de l’exécution de cette requête, le jeu de données contenait 27,450,499 lignes. Voyons maintenant quelle est la taille de stockage de la table dans ClickHouse :

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

Remarquez que la table n’occupe que 221.43 MiB !

Lançons quelques requêtes pour analyser les données :

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

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

Les prix de l’immobilier ont connu quelque chose d’inhabituel en 2020 ! Mais ce n’est probablement pas une surprise…

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

Ces requêtes peuvent être accélérées à l’aide de projections. Voir « Projections » pour des exemples avec ce jeu de données.

Le jeu de données est également disponible dans le Playground en ligne.