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

Neste tutorial, você vai explorar o ClickHouse usando o , 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

Crie a tabela

Selecione SQL Console no menu à esquerda
Clique na aba + ao lado do ícone de início para criar uma nova consulta
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.

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.

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.

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

Primeiros passos

Configuração

Conjuntos de dados de exemplo

Migrar

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

Pré-requisitos

Crie a tabela

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

Valide os dados

Execute algumas consultas

Próximos passos

​Pré-requisitos

Crie a tabela

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

Valide os dados

Execute algumas consultas

​Próximos passos

Pré-requisitos

Próximos passos