このページは Cloud Translation API によって翻訳されました。

BigQuery でのパーティショニングとクラスタリング

1. はじめに

BigQuery は、ペタバイト規模の低料金かつフルマネージドの分析用エンタープライズデータウェアハウスです。BigQueryはサーバーレスなのでクラスタを設定して管理する必要はありません。

BigQuery データセットは GCP プロジェクトに存在し、1 つ以上のテーブルを含んでいます。これらのデータセットには SQL でクエリを実行できます。

この Codelab では、GCP コンソールの BigQuery ウェブ UI を使用して、BigQuery でのパーティショニングとクラスタリングについて学習します。BigQuery のテーブルのパーティショニングとクラスタリングは、一般的なデータアクセスパターンに合わせてデータを構造化するのに役立ちます。特定のデータ範囲に対してクエリを実行する際に BigQuery のパフォーマンスと費用を最大限に最大化するには、パーティショニングとクラスタリングが重要です。クエリあたりのスキャンデータが少なくなり、プルーニングはクエリ開始時刻より前に決定されます。

BigQuery の詳細については、BigQuery のドキュメントをご覧ください。

学習内容

パーティション分割テーブルとクラスタ化テーブルを作成してクエリを実行する方法
パーティション分割テーブルとクラスタ化テーブルでクエリのパフォーマンスを比較する

必要なもの

このラボを完了するためには、下記が必要です。

最新バージョンの Google Chrome
Google Cloud Platform 請求先アカウント

2. 設定方法

BigQuery を使用するには、GCP プロジェクトを作成するか、既存のプロジェクトを選択する必要があります。

プロジェクトの作成

新しいプロジェクトを作成する手順は、次のとおりです。

Google アカウント（Gmail や Google アプリ）をまだお持ちでない場合は、アカウントを作成してください。
Google Cloud Platform コンソール（console.cloud.google.com）にログインし、新しいプロジェクトを作成します。
プロジェクトがない場合は、[プロジェクトを作成] ボタンをクリックします。

それ以外の場合は、プロジェクト選択メニューから新しいプロジェクトを作成します。

プロジェクト名を入力し、[作成] を選択します。プロジェクト ID をメモしてください。プロジェクト ID は、すべての Google Cloud プロジェクトで一意の名前です。

3. 一般公開データセットの操作

BigQuery では、BBC News、GitHub リポジトリ、Stack Overflow、米国海洋大気庁（NOAA）のデータセットなどの一般公開データセットを操作できます。これらのデータセットを BigQuery に読み込む必要はありません。データセットを開いて参照し、BigQuery でクエリを実行するだけです。この Codelab では、Stack Overflow 一般公開データセットを使用します。

Stack Overflow データセットを閲覧する

Stack Overflow データセットには、投稿、タグ、バッジ、コメント、ユーザーなどに関する情報が含まれています。BigQuery ウェブ UI で Stack Overflow データセットを閲覧する手順は次のとおりです。

Stack Overflow データセットを開きます。GCP コンソールで BigQuery ウェブ UI が開き、Stackoverflow データセットに関する情報が表示されます。
ナビゲーションパネルで [bigquery-public-data] を選択します。メニューが開き、一般公開データセットのリストが表示されます。各データセットは、1 つ以上のテーブルで構成されています。
下にスクロールして [stackoverflow] を選択します。メニューが開き、Stack Overflow データセット内のテーブルが一覧表示されます。
[badges] を選択して、バッジテーブルのスキーマを表示します。テーブル内のフィールド名をメモします。
フィールド名の上にある [プレビュー] をクリックして、バッジテーブルのサンプルデータを確認します。

BigQuery で利用可能なすべての一般公開データセットの詳細については、Google BigQuery 一般公開データセットをご覧ください。

Stackoverflow データセットに対してクエリを実行する

データセットを閲覧することは、作業しているデータを理解する良い方法ですが、BigQuery が真価を発揮するのはデータセットに対するクエリです。このセクションでは、BigQuery クエリを実行する方法について説明します。この時点で SQL の知識は必要ありません。以下のクエリをコピーして貼り付けることができます。

クエリを実行する手順は次のとおりです。

GCP コンソールの右上にある [クエリを新規作成] を選択します。
次の SQL クエリをコピーして、[クエリエディタ] テキスト領域に貼り付けます。BigQuery によってクエリが検証され、ウェブ UI のテキスト領域の下に緑色のチェックマークが表示され、構文が有効であることを示します。

SELECT
  EXTRACT(YEAR FROM creation_date) AS creation_year,
  COUNT(*) AS total_posts
FROM `bigquery-public-data.stackoverflow.posts_questions`
GROUP BY creation_year
ORDER BY total_posts DESC
LIMIT 10

[実行] を選択します。このクエリは、Stack Overflow で毎年投稿された投稿や質問の数を返します。

4. 新しいテーブルの作成

前のセクションでは、BigQuery で利用できる一般公開データセットに対してクエリを実行しました。このセクションでは、BigQuery で既存のテーブルから新しいテーブルを作成します。Stack Overflow の一般公開データセット posts_questions テーブルからサンプリングしたデータを含む新しいテーブルを作成し、そのテーブルに対してクエリを実行します。

新しいデータセットを作成する

テーブルデータを作成して BigQuery に読み込むには、まず次の手順で、データを保持する BigQuery データセットを作成します。

GCP コンソールのナビゲーションパネルで、設定の一環として作成したプロジェクト名を選択します。
右側の詳細パネルで、[データセットを作成] を選択します。

[データセットを作成] ダイアログの [データセット ID] に「stackoverflow」と入力します。その他のデフォルト設定はすべてそのままにして、[OK] をクリックします。

2018 年の StackOverflow 投稿を使用して新しいテーブルを作成する

これで BigQuery データセットが作成されたので、BigQuery に新しいテーブルを作成できます。既存のテーブルのデータを含むテーブルを作成するには、2018 Stack Overflow 投稿データセットに対してクエリを実行し、その結果を新しいテーブルに書き込みます。手順は次のとおりです。

GCP コンソールの右上にある [クエリを新規作成] を選択します。

次の SQL クエリを [クエリエディタ] テキスト領域にコピーして貼り付け、新しいテーブル（DDL ステートメント）を作成します。

CREATE OR REPLACE TABLE `stackoverflow.questions_2018` AS
SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count, tags
FROM `bigquery-public-data.stackoverflow.posts_questions`
WHERE creation_date BETWEEN '2018-01-01' AND '2019-01-01';

[実行] を選択します。このクエリでは、BigQuery Stack Overflow データセット bigquery-public-data.stackoverflow.posts_questions に対してクエリを実行した結果のデータを使用して、プロジェクトの stackoverflow データセットに新しいテーブル questions_2018 が作成されます。

2018 年の Stack Overflow 投稿を使用して新しいテーブルに対してクエリを実行する

これで BigQuery テーブルが作成されました。クエリを実行して、質問とタイトルを含む Stack Overflow の投稿に加えて、回答、コメント、閲覧数、お気に入りの数など、その他の統計情報を返します。次の手順を行います。

GCP コンソールの右上にある [クエリを新規作成] を選択します。
次の SQL クエリをコピーして [クエリエディタ] テキスト領域に貼り付けます。

SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count 
FROM  `stackoverflow.questions_2018` 
WHERE creation_date BETWEEN '2018-01-01' AND '2018-02-01'
AND tags = 'android';

[実行] を選択します。このクエリは、2018 年 1 月に作成された Stack Overflow の質問で、android としてタグ付けされた質問とその他の統計情報を返します。
デフォルトでは、BigQuery はクエリ結果をキャッシュに保存します。同じクエリを実行すると、BigQuery がキャッシュから結果を返すため、結果を返すまでの時間が大幅に短縮されたことがわかります。
同じクエリをもう一度実行しますが、今回は BigQuery のキャッシュ保存を無効にして実行します。このラボの残りの部分では、パーティション分割テーブルやクラスタ化テーブルと比較してパフォーマンスが公平になるように、キャッシュを無効にします。これについては、次のセクションで実行します。クエリエディタで [展開] をクリックし、[クエリ設定] を選択します。
[キャッシュの設定] で、[キャッシュされた結果を使用] チェックボックスをオフにします。
クエリ結果には、クエリが完了するまでにかかった時間と、結果を取得するために処理されたデータの量が表示されます。

5. パーティション分割テーブルの作成とクエリ

前のセクションでは、Stack Overflow 一般公開データセットを使用して、posts_questions テーブルのデータを含む新しいテーブルを BigQuery に作成しました。キャッシュを無効にしてこのデータセットに対してクエリを実行し、クエリのパフォーマンスを確認しました。このセクションでは、同じ Stack Overflow 一般公開データセットの posts_questions テーブルから新しいパーティション分割テーブルを作成し、クエリのパフォーマンスを確認します。

パーティション分割テーブルとは、パーティションと呼ばれるセグメントに分割された特殊なテーブルです。このテーブルを使用すると、データの管理や照会をより簡単に行うことができます。通常、データの取り込み時間、TIMESTAMP/DATE 列、INTEGER 列を使用して、大きなテーブルを多数の小さなパーティションに分割できます。DATE パーティション分割テーブルを作成します。

パーティション分割テーブルについて詳しくは、こちらをご覧ください。

2018 年の StackOverflow 投稿を含む新しいパーティション分割テーブルを作成する

既存のテーブルまたはクエリのデータでパーティション分割テーブルを作成するには、2018 年の Stackoverflow 使用されているデータセットに対してクエリを実行し、結果を新しいテーブルに書き込みます。手順は次のとおりです。

GCP コンソールの右上にある [クエリを新規作成] を選択します。

次の SQL クエリを [クエリエディタ] テキスト領域にコピーして貼り付け、新しいテーブル（DDL ステートメント）を作成します。

CREATE OR REPLACE TABLE `stackoverflow.questions_2018_partitioned` 
PARTITION BY DATE(creation_date) AS
SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count, tags
FROM `bigquery-public-data.stackoverflow.posts_questions`
WHERE creation_date BETWEEN '2018-01-01' AND '2019-01-01';

[実行] を選択します。このクエリでは、BigQuery Stack Overflow データセット bigquery-public-data.stackoverflow.posts_questions に対してクエリを実行した結果のデータを使用して、プロジェクトの stackoverflow データセットに新しいテーブル questions_2018_partitioned が作成されます。

2018 年の Stack Overflow 投稿を使用してパーティション分割テーブルに対してクエリを実行する

BigQuery パーティション分割テーブルを作成したので、今度はパーティション分割テーブルに対して同じクエリを実行します。質問とタイトルを含む Stack Overflow の投稿に加えて、回答、コメント、ビュー、お気に入りなどのその他の統計情報が返されます。次の手順を行います。

GCP コンソールの右上にある [クエリを新規作成] を選択します。
次の SQL クエリをコピーして [クエリエディタ] テキスト領域に貼り付けます。

SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count 
FROM  `stackoverflow.questions_2018_partitioned` 
WHERE creation_date BETWEEN '2018-01-01' AND '2018-02-01'
AND tags = 'android';

BigQuery キャッシュを無効にして [実行] を選択します（BigQuery キャッシュの無効化については前のセクションを確認してください）。このクエリは、2018 年 1 月に作成された Stack Overflow の質問で、android としてタグ付けされた質問とその他の統計情報を返します。
クエリ結果には、クエリが完了するまでにかかった時間と、結果を取得するために処理されたデータの量が表示されます。

BigQuery ではパーティションがプルーニングされます。つまり、必要なパーティションのみがスキャンされ、処理するデータ量が減り、実行速度が速くなるため、パーティション分割テーブルを使用したクエリのパフォーマンスは、パーティション分割されていないテーブルよりも向上していることがわかります。これにより、クエリの費用とパフォーマンスが最適化されます。

6. クラスタ化テーブルの作成とクエリ

前のセクションでは、Stack Overflow 一般公開データセットの posts_questions テーブルのデータを使用して、BigQuery でパーティション分割テーブルを作成しました。キャッシュを無効にしてこのテーブルに対してクエリを実行し、パーティション分割されていないテーブルとパーティション分割されたテーブルの両方でクエリのパフォーマンスを観測しました。このセクションでは、同じ Stack Overflow 一般公開データセットの posts_questions テーブルから新しいクラスタ化テーブルを作成し、クエリのパフォーマンスを確認します。

BigQuery でテーブルをクラスタ化すると、テーブルのスキーマ内の 1 つ以上の列のコンテンツに基づいて、テーブルデータが自動的に編成されます。指定した列は、関連するデータを並置するために使用されます。クラスタ化テーブルにデータが書き込まれると、BigQuery はクラスタリング列の値を使用してデータを並べ替えます。これらの値は、BigQuery ストレージ内の複数のブロックにデータを整理するために使用されます。クラスタ化列の順序によって、データの並べ替え順序が決まります。新しいデータがテーブルまたは特定のパーティションに追加されると、BigQuery はバックグラウンドで自動再クラスタリングを行い、テーブルまたはパーティションの並べ替えプロパティを復元します。

クラスタ化テーブルの操作について詳しくは、こちらをご覧ください。

2018 年の Stack Overflow 投稿を使用して新しいクラスタ化テーブルを作成する

このセクションでは、creation_date でパーティション分割された新しいテーブルを作成し、クエリのアクセスパターンに基づいて tags 列でクラスタ化します。既存のテーブルまたはクエリのデータでクラスタ化テーブルを作成するには、2018 Stack Overflow テーブルに対してクエリを実行し、その結果を新しいテーブルに書き込みます。手順は次のとおりです。

GCP コンソールの右上にある [クエリを新規作成] を選択します。

次の SQL クエリを [クエリエディタ] テキスト領域にコピーして貼り付け、新しいテーブル（DDL ステートメント）を作成します。

#standardSQL
CREATE OR REPLACE TABLE `stackoverflow.questions_2018_clustered`
PARTITION BY
  DATE(creation_date)
CLUSTER BY
  tags AS
SELECT
  id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count, tags
FROM
  `bigquery-public-data.stackoverflow.posts_questions`
WHERE
  creation_date BETWEEN '2018-01-01' AND '2019-01-01';

[実行] を選択します。このクエリでは、BigQuery Stack Overflow テーブル bigquery-public-data.stackoverflow.posts_questions に対してクエリを実行した結果のデータを使用して、プロジェクトの stackoverflow データセットに新しいテーブル questions_2018_clustered が作成されます。新しいテーブルは create_date でパーティション分割され、 tags 列でクラスタ化されます。

2018 年の Stack Overflow 投稿でクラスタ化テーブルに対してクエリを実行する

BigQuery のクラスタ化テーブルを作成したので、今度はパーティション分割テーブルとクラスタ化テーブルに対して同じクエリを再度実行します。これにより、質問とタイトルを含む Stack Overflow の投稿に加えて、回答、コメント、ビュー、お気に入りなどのその他の統計情報が返されます。次の手順を行います。

GCP コンソールの右上にある [クエリを新規作成] を選択します。
次の SQL クエリをコピーして [クエリエディタ] テキスト領域に貼り付けます。

SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count 
FROM  `stackoverflow.questions_2018_clustered` 
WHERE creation_date BETWEEN '2018-01-01' AND '2018-02-01'
AND tags = 'android';

BigQuery キャッシュを無効にして [実行] を選択します（BigQuery キャッシュの無効化については前のセクションを確認してください）。このクエリは、2018 年 1 月に作成された Stack Overflow の質問で、android としてタグ付けされた質問とその他の統計情報を返します。
クエリ結果には、クエリが完了するまでにかかった時間と、結果を取得するために処理されたデータの量が表示されます。

パーティション分割テーブルとクラスタ化テーブルでは、パーティション分割テーブルや分割なしのテーブルよりもクエリでスキャンされるデータが少なくなります。パーティショニングとクラスタリングによってデータを整理すると、スロットワーカーによってスキャンされるデータの量が最小限に抑えられるため、クエリのパフォーマンスが向上し、コストが最適化されます。

7. クリーンアップ

stackoverflow データセットを引き続き使用する予定でない限り、データセットを削除し、この Codelab 用に作成したプロジェクトも削除する必要があります。

BigQuery データセットの削除

BigQuery データセットを削除するには、以下の手順を実行します。

BigQuery の左側のナビゲーションパネルから [stackoverflow] データセットを選択します。
詳細パネルで [データセットの削除] を選択します。
[データセットの削除] ダイアログで「stackoverflow」と入力し、[削除] を選択してデータセットの削除を確定します。

プロジェクトを削除する

この Codelab 用に作成した GCP プロジェクトを削除するには、次の手順を行います。

GCP のナビゲーションメニューで [IAM と管理] を選択します。
ナビゲーションパネルで [設定] を選択します。
詳細パネルで、現在のプロジェクトがこの Codelab 用に作成したプロジェクトであることを確認し、[シャットダウン] を選択します。
[プロジェクトのシャットダウン] ダイアログで、プロジェクトのプロジェクト ID（プロジェクト名ではない）を入力し、[シャットダウン] を選択して確定します。

これで、ここでは、Google Chat の

BigQuery ウェブ UI を使用して既存のテーブルから新しいテーブルを作成する方法
パーティション分割テーブルとクラスタ化テーブルを作成してクエリを実行する方法
パーティショニングとクラスタリングによってクエリのパフォーマンスとコストを最適化する仕組み

データセットを操作するためにクラスタを設定または管理する必要はありません。