Requêtes analytiques sur les données des prix de l’immobilier au Royaume-Uni

Dans ce tutoriel, vous allez découvrir ClickHouse à l’aide du , qui recense les prix de vente des biens immobiliers en Angleterre et au pays de Galles depuis 1995.

Prérequis

Pour ce tutoriel, vous aurez besoin de :

Un compte ClickHouse Cloud (300 $ de crédits offerts à l’inscription)
Un service ClickHouse Cloud

Créez la table

Sélectionnez console SQL dans le menu de gauche
Cliquez sur l’onglet + à côté de l’icône d’accueil pour créer une nouvelle requête
Dans l’éditeur SQL, saisissez la requête suivante, puis cliquez sur 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);

Notez la clause ORDER BY (postcode1, postcode2, addr1, addr2), qui définit la manière dont ClickHouse trie les données sur disque. Dans ClickHouse, choisir une clé primaire efficace, adaptée à vos modèles d’accès, est essentiel pour les performances des requêtes et l’efficacité du stockage. Consultez “Choisir une clé primaire” pour plus de détails.

Consultez https://www.gov.uk pour une description des champs.

Prétraitement et insertion des données

Vous pouvez utiliser la fonction url pour charger des données en flux dans ClickHouse. Un prétraitement est toutefois nécessaire au préalable. La requête ci-dessous insère plus de 25 millions de lignes dans la table uk_price_paid et effectue les étapes de prétraitement suivantes :

découpe le champ postcode en deux colonnes distinctes, postcode1 et postcode2, ce qui est plus efficace pour le stockage et les requêtes
convertit le champ time en date, puisqu’il ne contient que l’heure 00:00
ignore le champ UUID, inutile pour l’analyse
transforme type et duration en champs Enum plus lisibles à l’aide de la fonction transform
convertit le champ is_new, une chaîne d’un seul caractère (Y/N), en un champ UInt8 valant 0 ou 1
supprime les deux dernières colonnes, qui contiennent toutes la même valeur (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;

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

Vérifier les données

Vérifions que l’opération a réussi en comptant le nombre de lignes 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 comptait 27 450 499 lignes. Voyons quel espace de stockage occupe la table dans ClickHouse :

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

Notez que la taille de la table n’est que de 221,43 MiB, alors que le jeu de données d’origine non compressé pèse environ 4 GiB. ClickHouse offre nativement une excellente compression des données, tout en vous permettant d’affiner la compression colonne par colonne si nécessaire.

Exécutez quelques requêtes

Une fois les données chargées, exécutez les requêtes suivantes pour vous faire une idée de la rapidité avec laquelle les requêtes analytiques renvoient leurs résultats. La requête ci-dessous calcule le prix moyen par an sur l’ensemble des 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

La requête ci-dessous applique un filter pour calculer le prix moyen par an à 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

On dirait qu’il s’est passé quelque chose avec les prix de l’immobilier en 2020 ! Mais ce n’est probablement pas vraiment une surprise…La requête ci-dessous identifie les quartiers les plus chers :

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

Étapes suivantes

Dans ce tutoriel, vous avez créé une table, prétraité et chargé dans ClickHouse des données sur les prix de l’immobilier au Royaume-Uni, puis exécuté quelques requêtes analytiques sur ces données. Vous pouvez maintenant :

Découvrir comment accélérer ces requêtes à l’aide de projections. Voir « Projections » pour des exemples utilisant le même jeu de données.
Approfondir les concepts fondamentaux de ClickHouse
Explorer les bonnes pratiques de ClickHouse
Explorer d’autres jeux de données d’exemple

Premiers pas

Configuration

Exemples de jeux de données

Migrer

Requêtes analytiques sur les données des prix de l’immobilier au Royaume-Uni

Prérequis

Créez la table

Prétraitement et insertion des données

Vérifier les données

Exécutez quelques requêtes

Étapes suivantes

​Prérequis

Créez la table

Prétraitement et insertion des données

Vérifier les données

Exécutez quelques requêtes

​Étapes suivantes

Prérequis

Étapes suivantes