영국 부동산 가격 데이터 분석 쿼리

영국 부동산 가격 데이터로 ClickHouse 살펴보기

이 튜토리얼에서는 1995년 이후 잉글랜드와 웨일스에서 거래된 부동산의 매매 가격 데이터를 담고 있는 UK Property Price dataset을 사용하여 ClickHouse를 살펴봅니다.

​

사전 요구 사항

이 튜토리얼을 진행하려면 다음이 필요합니다.

• ClickHouse Cloud 계정 (가입 시 $300 상당의 무료 크레딧 제공)
• ClickHouse Cloud 서비스

1. 테이블을 생성하세요

   1. 왼쪽 메뉴에서 SQL 콘솔을 선택하세요
   2. 홈 아이콘 옆의 + 탭을 클릭하여 새 쿼리를 생성하세요
   3. 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);
   

   ClickHouse가 디스크에 데이터를 정렬하는 방식을 정의하는 ORDER BY (postcode1, postcode2, addr1, addr2)에 주목하십시오. 액세스 패턴에 맞는 효과적인 기본 키(primary key)를 선택하는 것은 ClickHouse의 쿼리 성능과 스토리지 효율성을 높이는 데 매우 중요합니다. 자세한 내용은 “기본 키 선택”을 참조하십시오.

   각 필드에 대한 설명은 https://www.gov.uk를 참조하십시오.

2. 데이터를 전처리하고 삽입하세요

   url 함수를 사용하여 데이터를 ClickHouse로 스트리밍할 수 있습니다. 단, 먼저 몇 가지 전처리가 필요합니다. 아래 쿼리는 2,500만 개 이상의 행을 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분 정도 걸립니다.

3. 데이터를 검사하세요

   삽입된 행 수를 조회하여 정상적으로 처리되었는지 확인해 보겠습니다:

   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는 별도 설정 없이도 뛰어난 데이터 압축 성능을 제공하며, 필요한 경우 컬럼별로 압축을 세부 조정할 수도 있습니다.

4. 몇 가지 쿼리를 실행하세요

   데이터 로드가 완료되면 다음 쿼리를 실행하여 분석 쿼리의 결과가 얼마나 빠르게 반환되는지 직접 확인해 보십시오. 아래 쿼리는 전체 데이터를 대상으로 연도별 평균 가격을 구합니다.

   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
   

​

다음 단계

이 튜토리얼에서는 테이블을 생성하고, 영국 부동산 가격 데이터를 전처리하여 ClickHouse에 적재한 다음, 해당 데이터에 대해 몇 가지 분석 쿼리를 실행했습니다.

이제 다음 내용을 진행해 볼 수 있습니다.

• 프로젝션을 사용하여 이러한 쿼리의 속도를 높이는 방법을 알아보십시오. 동일한 데이터셋을 사용하는 예시는 “프로젝션”을 참조하십시오.
• ClickHouse 핵심 개념에 대해 자세히 알아보십시오.
• ClickHouse 모범 사례를 살펴보십시오.
• 다른 샘플 데이터셋을 살펴보십시오.