Consultas analíticas sobre dados de preços de imóveis do Reino Unido

Explore o ClickHouse com dados de preços de imóveis do Reino Unido

Neste tutorial, você vai explorar o ClickHouse usando o UK Property Price dataset, que contém os preços pagos por imóveis na Inglaterra e no País de Gales desde 1995.

​

Pré-requisitos

Para este tutorial, você vai precisar de:

• Uma conta do ClickHouse Cloud (US$ 300 em créditos gratuitos ao se cadastrar)
• Um serviço do ClickHouse Cloud

1. Crie a tabela

   1. Selecione SQL Console no menu à esquerda
   2. Clique na aba + ao lado do ícone de início para criar uma nova consulta
   3. No editor SQL, digite a seguinte consulta e clique em 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);
   

   Observe a cláusula ORDER BY (postcode1, postcode2, addr1, addr2), que define como o ClickHouse ordena os dados em disco. Escolher uma chave primária eficaz no ClickHouse, alinhada aos seus padrões de acesso, é fundamental para o desempenho das consultas e a eficiência de armazenamento. Consulte “Escolhendo uma chave primária” para obter mais detalhes.

   Consulte https://www.gov.uk para ver uma descrição dos campos.

2. Faça o pré-processamento e insira os dados

   Você pode usar a função url para fazer streaming de dados para o ClickHouse. Antes, é necessário fazer um pré-processamento. A consulta abaixo insere mais de 25 milhões de linhas na tabela uk_price_paid e executa as seguintes etapas de pré-processamento:

   • divide o postcode em duas colunas distintas — postcode1 e postcode2 —, o que é melhor para o armazenamento e para as consultas
   • converte o campo time para data, pois ele contém apenas o horário 00:00
   • ignora o campo UUID, pois ele não é necessário para a análise
   • transforma type e duration em campos Enum mais legíveis usando a função transform
   • transforma o campo is_new de uma string de um único caractere (Y/N) em um campo UInt8 com 0 ou 1
   • descarta as duas últimas colunas, já que todas têm o mesmo valor (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;
   

   Aguarde a inserção dos dados — isso deve levar um ou dois minutos, dependendo da velocidade da rede.

3. Valide os dados

   Vamos verificar se funcionou conferindo quantas linhas foram inseridas:

   SELECT count()
   FROM uk.uk_price_paid
   

   No momento em que esta consulta foi executada, o conjunto de dados tinha 27.450.499 linhas. Vamos ver qual é o tamanho de armazenamento da tabela no ClickHouse:

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

   Observe que o tamanho da tabela é de apenas 221,43 MiB, enquanto o conjunto de dados original, sem compressão, tem cerca de 4 GiB. O ClickHouse oferece uma excelente compressão de dados por padrão, mas também permite ajustar ainda mais a compressão por coluna, se necessário.

4. Execute algumas consultas

   Com os dados carregados, experimente as consultas a seguir para ter uma ideia da rapidez com que as consultas analíticas retornam resultados. A consulta abaixo calcula o preço médio por ano considerando todos os dados:

   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
   

   A consulta abaixo aplica um filtro para calcular o preço médio por ano em 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
   

   Parece que algo aconteceu com os preços dos imóveis em 2020! Mas isso provavelmente não é nenhuma surpresa…

   A consulta abaixo retorna os bairros mais caros:

   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
   

​

Próximos passos

Neste tutorial, você criou uma tabela, pré-processou e carregou dados de preços de imóveis do Reino Unido no ClickHouse e, em seguida, executou algumas consultas analíticas sobre esses dados.

A seguir, você pode:

• Aprender como acelerar essas consultas com projeções. Consulte “Projeções” para ver exemplos que usam o mesmo conjunto de dados.
• Saber mais sobre os conceitos fundamentais do ClickHouse
• Explorar as melhores práticas do ClickHouse
• Explorar outros conjuntos de dados de exemplo