UK 부동산 가격 데이터 분석

UK 부동산 가격 데이터 분석 (Analytical queries on UK property price data)

영국(England와 Wales)에서 1995년 이후 거래된 부동산 가격 데이터를 ClickHouse로 분석하는 튜토리얼입니다. 테이블 생성, 데이터 전처리, 로딩, 분석 쿼리 실행까지 한 번에 경험해 볼 수 있어요.

출처: 문서

본문

이 튜토리얼에서는 1995년 이후 영국(England와 Wales)에서 거래된 부동산 가격 데이터를 포함하는 UK Property Price 데이터셋으로 ClickHouse를 탐색합니다.

전제 조건 (Prerequisites)

이 튜토리얼에는 다음이 필요합니다:

  1. 테이블 생성

  2. 왼쪽 메뉴에서 SQL console을 선택하세요

  3. 홈 아이콘 옆의 + 탭을 클릭해 새 쿼리를 만드세요

  4. 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 기본 키를 선택하는 것은 쿼리 성능과 저장 효율에 매우 중요합니다. 자세한 내용은 "Choosing a primary key"를 참고하세요.

각 필드에 대한 설명은 https://www.gov.uk를 참고하세요.

  1. 데이터 전처리 및 삽입

url 함수를 사용해 데이터를 ClickHouse로 스트리밍할 수 있습니다. 다만 몇 가지 전처리가 먼저 필요합니다. 아래 쿼리는 2500만 개가 넘는 행을 uk_price_paid 테이블에 삽입하면서 다음 전처리 단계를 수행합니다:

  • postcode를 두 개의 다른 컬럼(postcode1, postcode2)으로 분리합니다. 저장과 쿼리에 더 유리합니다
  • time 필드는 00:00 시간만 포함하므로 날짜로 변환합니다
  • 분석에 필요 없으므로 UUID 필드를 무시합니다
  • transform 함수로 type과 duration을 더 읽기 쉬운 Enum 필드로 변환합니다
  • is_new 필드를 한 글자 문자열(Y/N)에서 0 또는 1 값을 갖는 UInt8 필드로 변환합니다
  • 마지막 두 개의 컬럼은 모두 같은 값(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;

데이터가 삽입될 때까지 기다리세요. 네트워크 속도에 따라 1~2분 정도 걸립니다.

  1. 데이터 검증

삽입된 행 수를 확인해 제대로 됐는지 검증해 봅시다:

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는 기본적으로 훌륭한 데이터 압축을 제공하며, 필요하다면 컬럼별로 압축을 더 세밀하게 조정할 수도 있습니다.

  1. 몇 가지 쿼리 실행

데이터가 로드되었으니 아래 쿼리들을 실행해 분석 쿼리가 얼마나 빠르게 결과를 반환하는지 직접 느껴 보세요. 아래 쿼리는 전체 데이터의 연도별 평균 가격을 찾습니다:

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

다음 단계 (Next steps)

이 튜토리얼에서는 테이블을 만들고, UK 부동산 가격 데이터를 전처리해 ClickHouse에 로드한 뒤, 그 데이터에 대해 몇 가지 분석 쿼리를 실행했습니다.

다음으로 할 수 있는 것들:

더 알아보기 (Learn more)