Аналитические запросы к данным о ценах на недвижимость в Великобритании

Знакомство с ClickHouse на примере данных о ценах на недвижимость в Великобритании

В этом руководстве вы познакомитесь с ClickHouse на примере набора данных о ценах на недвижимость в Великобритании, который содержит сведения о ценах сделок с недвижимостью в Англии и Уэльсе начиная с 1995 года.

​

Предварительные требования

Для работы с этим руководством вам понадобятся:

• Аккаунт ClickHouse Cloud (при регистрации начисляется 300 $ бесплатных кредитов)
• Сервис ClickHouse Cloud

1. Создайте таблицу

   1. Выберите SQL console в меню слева
   2. Нажмите на вкладку + рядом со значком главной страницы, чтобы создать новый запрос
   3. Введите в редакторе SQL следующий запрос и нажмите 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);
   

   Обратите внимание на ORDER BY (postcode1, postcode2, addr1, addr2): это выражение определяет, как ClickHouse сортирует данные на диске. Для производительности запросов и эффективности хранения критически важно выбрать в ClickHouse первичный ключ, который соответствует типичным сценариям доступа к данным. Подробнее см. “Выбор первичного ключа”.

   Описание полей см. на сайте https://www.gov.uk.

2. Предварительно обработайте данные и вставьте их

   Для потоковой загрузки данных в ClickHouse можно использовать функцию url. Но сначала данные нужно предварительно обработать. Приведённый ниже запрос вставляет более 25 миллионов строк в таблицу uk_price_paid и выполняет следующие шаги предварительной обработки:

   • разделяет postcode на два отдельных столбца — postcode1 и postcode2, что удобнее для хранения и выполнения запросов
   • преобразует поле time в дату, так как оно содержит только время 00:00
   • игнорирует поле UUID, поскольку для анализа оно не нужно
   • преобразует type и duration в более удобочитаемые поля Enum с помощью функции transform
   • преобразует поле is_new из односимвольной строки (Y/N) в поле UInt8 со значением 0 или 1
   • удаляет два последних столбца, поскольку все значения в них одинаковы (равны 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;
   

   Дождитесь завершения вставки данных — это займёт одну-две минуты в зависимости от скорости сети.

3. Проверьте данные

   Убедимся, что всё сработало, — посмотрим, сколько строк было вставлено:

   SELECT count()
   FROM uk.uk_price_paid
   

   На момент выполнения этого запроса набор данных содержал 27 450 499 строк. Давайте посмотрим, сколько места занимает таблица в ClickHouse:

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

   Обратите внимание: размер таблицы составляет всего 221.43 MiB, тогда как исходный набор данных в несжатом виде занимает около 4 GiB. ClickHouse обеспечивает превосходное сжатие данных «из коробки», а при необходимости позволяет дополнительно настроить сжатие для каждого столбца.

4. Выполните несколько запросов

   После загрузки данных выполните следующие запросы, чтобы оценить, насколько быстро выполняются аналитические запросы. Приведённый ниже запрос вычисляет среднюю цену за каждый год по всему набору данных:

   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
   

   Похоже, в 2020 году с ценами на жильё что-то произошло! Впрочем, это вряд ли кого-то удивит…

   Следующий запрос находит самые дорогие районы:

   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
   

​

Дальнейшие шаги

В этом руководстве вы создали таблицу, выполнили предварительную обработку данных о ценах на недвижимость в Великобритании, загрузили их в ClickHouse, а затем выполнили несколько аналитических запросов к этим данным.

Что дальше:

• Узнайте, как ускорить эти запросы с помощью проекций. Примеры для этого же набора данных см. в разделе “Проекции”.
• Узнайте больше об основных концепциях ClickHouse
• Ознакомьтесь с рекомендациями по работе с ClickHouse
• Изучите другие примеры наборов данных