Skip to main content
Skip to main content
Edit this page

The UK property prices dataset

Projections are a great way to improve the performance of queries that you run frequently. We will demonstrate the power of projections using the UK property dataset, which contains data about prices paid for real-estate property in England and Wales. The data is available since 1995, and the size of the dataset in uncompressed form is about 4 GiB (which will only take about 278 MiB in ClickHouse).

Create the Table​

Preprocess and Insert the Data​

We will use the url function to stream the data into ClickHouse. We need to preprocess some of the incoming data first, which includes:

  • splitting the postcode to two different columns - postcode1 and postcode2, which is better for storage and queries
  • converting the time field to date as it only contains 00:00 time
  • ignoring the UUid field because we don't need it for analysis
  • transforming type and duration to more readable Enum fields using the transform function
  • transforming the is_new field from a single-character string (Y/N) to a UInt8 field with 0 or 1
  • drop the last two columns since they all have the same value (which is 0)

The url function streams the data from the web server into your ClickHouse table. The following command inserts 5 million rows into the uk_price_paid table:

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:

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:

Notice the size of the table is just 221.43 MiB!

Run Some Queries​

Let's run some queries to analyze the data:

Query 1. Average Price Per Year​

The result looks like:

Query 2. Average Price per Year in London​

The result looks like:

Something happened to home prices in 2020! But that is probably not a surprise...

Query 3. The Most Expensive Neighborhoods​

The result looks like:

Let's Speed Up Queries Using Projections​

Projections allow you to improve query speeds by storing pre-aggregated data in whatever format you want. In this example, we create a projection that keeps track of the average price, total price, and count of properties grouped by the year, district and town. At query time, ClickHouse will use your projection if it thinks the projection can improve the performance of the query (you don't have to do anything special to use the projection - ClickHouse decides for you when the projection will be useful).

Build a Projection​

Let's create an aggregate projection by the dimensions toYear(date), district, and town:

Populate the projection for existing data. (Without materializing it, the projection will be created for only newly inserted data):

Test Performance​

Let's run the same 3 queries again:

Query 1. Average Price Per Year​

The result is the same, but the performance is better!

Query 2. Average Price Per Year in London​

Same result, but notice the improvement in query performance:

Query 3. The Most Expensive Neighborhoods​

The condition (date >= '2020-01-01') needs to be modified so that it matches the projection dimension (toYear(date) >= 2020):

Again, the result is the same but notice the improvement in query performance:

Test it in the Playground​

The dataset is also available in the Online Playground.