Analytical queries on UK property price data

In this tutorial you’ll explore ClickHouse using the which contains data of prices paid for real-estate property in England and Wales since 1995.

Prerequisites

For this tutorial, you’ll need:

A ClickHouse Cloud account ($300 in free credits when signing up)
A ClickHouse Cloud service

Create the table

Select SQL console from the the left hand menu
Click the + tab next to the home icon to create a new query
In the SQL editor type the following query, then click 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);

Take note of ORDER BY (postcode1, postcode2, addr1, addr2) which defines how ClickHouse sorts data on disk. Choosing an effective primary key in ClickHouse which matches your access patterns is crucial for query performance and storage efficiency. See “Choosing a primary key” for more details.

See https://www.gov.uk for a description of the fields.

Preprocess and insert the data

You can use the url function to stream data into ClickHouse. Some preprocess is required first. The query below inserts 25+ million rows into the uk_price_paid table and performs the following preprocessing steps:

splits the postcode to two different columns - postcode1 and postcode2, which is better for storage and queries
converts the time field to date as it only contains 00:00 time
ignores the UUID field because it isn’t needed for analysis
transforms type and duration to more readable Enum fields using the transform function
transforms the is_new field from a single-character string (Y/N) to a UInt8 field with 0 or 1
drops the last two columns since they all have the same value (which is 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;

Wait for the data to insert - it will take a minute or two depending on the network speed.

Validate the data

Let’s verify it worked by seeing how many rows were inserted:

SELECT count()
FROM uk.uk_price_paid

At the time this query was run, the dataset had 27,450,499 rows. Let’s see what the storage size is of the table in ClickHouse:

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

Notice the size of the table is just 221.43 MiB, while the original dataset in uncompressed form is about 4 GiB. ClickHouse offers excellent data compression out of the box, but also allows you to further tune compression per column if you need.

Run some queries

With the data loaded, try out the following queries to get a sense of how fast analytical queries return results. The query below finds the average price per year across all of the data:

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

The query below applies a filter to find the average price per year in London:

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

It looks like something happened to home prices in 2020! But that is probably not a surprise…The query below finds the most expensive neighborhoods:

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

Next steps

In this tutorial you created a table, preprocessed and loaded UK property price data into ClickHouse, and then ran some analytical queries on that data. Next you can:

Learn how to speed up these queries with projections. See “Projections” for examples which use the same dataset.
Learn more about ClickHouse core concepts
Explore ClickHouse best practices
Explore other sample datasets

Get started

Setup

Sample datasets

Migrate

Analytical queries on UK property price data

Prerequisites

Create the table

Preprocess and insert the data

Validate the data

Run some queries

Next steps

​Prerequisites

Create the table

Preprocess and insert the data

Validate the data

Run some queries

​Next steps

Prerequisites

Next steps