英国の不動産価格データを対象とした分析クエリ

英国の不動産価格データを使って ClickHouse を探索する

このチュートリアルでは、UK Property Price dataset を使って ClickHouse を探索します。このデータセットには、1995 年以降にイングランドおよびウェールズで取引された不動産の支払価格データが収録されています。

​

前提条件

このチュートリアルを進めるには、以下が必要です。

• ClickHouse Cloud アカウント (サインアップ時に 300 ドル分の無料クレジットを利用できます)
• ClickHouse Cloud サービス

1. テーブルを作成する

   1. 左側のメニューから SQL console を選択します
   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);
   

   ORDER BY (postcode1, postcode2, addr1, addr2) に注目してください。これは、ClickHouse がディスク上でデータをどのようにソートするかを定義しています。 ClickHouse では、アクセスパターンに合った効果的な主キーを選択することが、クエリパフォーマンスとストレージ効率の両面で極めて重要です。 詳細は “主キーの選択” を参照してください。

   各フィールドの説明については、https://www.gov.uk を参照してください。

2. データを前処理して挿入する

   url 関数を使用すると、ClickHouse にデータをストリーミングできます。ただし、事前にいくつかの前処理が必要です。 以下のクエリは、uk_price_paid テーブルに 2,500 万行以上を挿入し、その際に次の前処理を行います。

   • postcode を postcode1 と postcode2 の 2 つのカラムに分割します。こうすることで、ストレージ効率とクエリ性能が向上します
   • time フィールドには 00:00 の時刻しか含まれていないため、日付型に変換します
   • UUID フィールドは分析に不要なため、取り込みません
   • transform 関数を使用して、type と duration を可読性の高い Enum フィールドに変換します
   • is_new フィールドを 1 文字の文字列 (Y/N) から、0 または 1 の値を持つ UInt8 フィールドに変換します
   • 最後の 2 つのカラムはすべて同じ値 (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 にロードしたうえで、 そのデータに対していくつかの分析クエリを実行しました。

次のステップとして、以下を行うことができます。

• プロジェクションを使ってこれらのクエリを高速化する方法を学ぶ。同じデータセットを使用した例については、“Projections” を参照してください。
• ClickHouse のコアコンセプトについて詳しく学ぶ
• ClickHouse のベストプラクティスを確認する
• その他のサンプルデータセットを試す