LoginSignup
16
9

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

@esakik

BigQuery でデータ基盤を構築する際に知っておくべき機能

Last updated at Posted at 2020-09-29

はじめに

BigQuery は、主に分析用途で素早くビッグデータを利活用したい場合に選択肢としてあがる GCP のフルマネージドなデータウェアハウスです。サーバーレスのため、インスタンスの料金はかからず、利用した分だけ課金されるので低料金で利用を開始できるのが特徴です。

BigQuery には様々な機能が存在するため、**データ基盤を構築・運用する際に知っておくと良い機能を用途毎に簡易的にまとめています。**機能のより詳細な部分については Google Cloud のドキュメントにまとまっているため、そちらに任せています。

314-3143797_google-big-query-logo-google-bigquery-logo-clipart.png

BigQuery の料金

BigQuery でデータ基盤を構築・運用していく上で料金を把握しておくことは当たり前ですが重要なのでここで少しだけ記述しています。主にはストレージとクエリの料金が発生します。その他には BigQuery Storage API やストリーミングインサートを利用する場合等に料金が発生しますが、ここでは記述していません。

ストレージ料金

ストレージ料金はデータの保存期間によって 2 種類の料金体系が存在します。東京リージョンの場合のストレージ料金は次の表の通りです。

  • アクティブ ストレージ：過去 90 日間で変更されたデータに対する料金
  • 長期保存：過去 90 日間変更されていないデータに対する割引料金
料金体系 料金 詳細
アクティブ ストレージ $0.023 per GB 毎月 10 GB まで無料
長期保存 $0.010 per GB 毎月 10 GB まで無料

テーブルに保存されたデータが 90 日間変更がなかった場合に、そのテーブルに対するストレージ料金は自動的に約 50% 値引きされます。

後述のパーティション分割テーブルの場合は、各パーティション毎に割引料金が適用されるため、90 日間変更がなかったパーティションのデータが長期保存料金の対象となります。

クエリ料金

クエリ料金は次の 2 種類の料金体系が存在します。後述のパーティション分割テーブルやクラスタ化テーブルを利用することで、クエリ料金は抑えることができます。

  • オンデマンド：クエリの実行で処理されたバイト数に応じて課金されます
  • 定額：固定料金のため課金金額の予測が可能です
料金体系 料金 詳細
オンデマンド $6.00 per TB 毎月 1 TB まで無料
定額 定額料金 詳細は割愛

テーブル設計

BigQuery でテーブル設計を行う場合に知っておくと良い機能やアーキテクチャ等をまとめています。クエリ・ストレージ等のコストやテーブルの利活用のしやすさ、セキュリティ等を考慮する必要がありますが、特にコストを節約するために必須の機能を BigQuery は提供してくれています。

BigQuery スロット

BigQuery スロットは、クエリのパフォーマンスと料金を把握するための重要な概念で、BigQuery でクエリを実行する際に使用される**「CPU と RAM で構成されるコンピューティング能力」**の単位です。クエリのサイズと複雑さに応じて、クエリごとに必要なスロットの数が自動的に計算されます。

ドキュメントよりもこちらの記事の説明がよりわかりやすかったので引用させていただきます。

なぜスロットが重要なのか：

なぜスロットが重要なのかと言うと、BigQueryではクエリを実行する際に、処理を一度キューに積んで、同時実行可能なスロット数に応じた実行制御が行われる仕組みになっているからです。

新しいクエリが到着した際に、既に利用可能なスロットがいっぱいだった場合でも、実行中のクエリ間でスロットの再割当てが行われるため、まったく処理が進まないということはなさそうですが、同時実行されるクエリが多ければ多いほど1クエリあたりのスロットが少なくため、結果としてクエリの実行完了までの所要時間が伸びてしまいます。

つまりスロットとはどういった概念なのか：

スロットとは、いわゆるクエリの重さをはかる単位であり、重いクエリ（＝スロット消費の多いクエリ）が同時にたくさん実行されると、クエリの実行完了までの時間が伸びてしまうと理解しておけば良いのではないでしょうか。

BigQuery スロットの消費量をモニタリングするには、こちらのドキュメントを参考に Slot Utilization グラフを確認します。

また、BigQuery ではクエリのパフォーマンスを確認する際に EXPLAIN を利用することができないため、代わりにクエリプランとタイムラインを参考にクエリ実行の詳細を確認します。

パーティション分割テーブル

パーティション分割テーブルは、BigQuery テーブルを時間や日付などの単位でパーティションと呼ばれるセグメントに分割して保存することができる機能です。テーブルを分割することによって、データ読み取り時のスロット消費量とクエリ料金を抑えることができます。

パーティション分割テーブルを作成するには、BigQuery コンソールから作成する場合、テーブル作成の画面で**「パーティションとクラスタの設定」から「取り込み時間により分割」もしくは「フィールドにより分割」**を選択します。

スクリーンショット 2020-09-24 3.20.19.png

パーティショニング タイプからは、どういった粒度（時間なのか日付なのか）でテーブルをパーティションに分割するかを選択することができます。

また、パーティショニング フィルタでは、「パーティショション フィルタを要求」にチェックを入れると、データ読み取り時に WHERE 句でパーティションフィールドを指定することを必須にできます。これによって、間違ってテーブル全体を読み込んでしまうことを防ぐことができます。

取り込み時間により分割を選択

取り込み時間 分割テーブルでは、データの取り込み時間に基づいてテーブルを分割します。このタイプの分割テーブルでは、_PARTITIONTIME という擬似列が存在し、取り込み時間のタイムスタンプがこの列に格納されています。

取り込み時間分割テーブルへのデータ挿入
INSERT INTO `project-id.dataset_id.table_id` (
  _PARTITIONTIME,
  hoge,
  ...
)

SELECT TIMESTAMP("2020-09-24"), "", ...
取り込み時間分割テーブルのデータ読み取り
SELECT
  _PARTITIONTIME AS dt,
  hoge
FROM `project-id.dataset_id.table_id`
WHERE DATE(_PARTITIONTIME) = "2020-09-24"

フィールドにより分割を選択

日付 / タイムスタンプ 分割テーブルでは、テーブルに存在する TIMESTAMP または DATE 型のフィールドに基づいてテーブルを分割します。そのため、このタイプの分割テーブルでは、TIMESTAMP または DATE 型のフィールドが存在することが必須になります。

日付/タイムスタンプ分割テーブルへのデータ挿入
INSERT INTO `project-id.dataset_id.table_id` (
  hoge,
  created_at,
  ...
)

SELECT "", TIMESTAMP("2020-09-24"), ...
日付/タイムスタンプ分割テーブルのデータ読み取り
SELECT
  hoge,
  created_at
FROM `project-id.dataset_id.table_id`
WHERE DATE(created_at) = "2020-09-24"

パーティション有効期限の更新

パーティション分割テーブルを利用することで、データ読み取り時の料金を節約することができます。**ストレージ料金も節約したい場合は、パーティション有効期限を設定することをおすすめします。**パーティション有効期限を設定することで、パーティションの日付が、設定した日数を経過した（または既に経過している）場合に、そのパーティションは自動的に削除されます。

パーティション有効期限の更新
ALTER TABLE `project-id.dataset_id.table_id`
  SET OPTIONS (
    -- Sets partition expiration to 5 days
    partition_expiration_days=5
  )

クラスタ化テーブル

クラスタ化テーブルは、指定した1つ以上のフィールドの値に応じてデータを並び替えることで、不要なデータ読み取りの省略とクエリの高速化を行うことができる機能です。****WHERE 句や GROUP BY 句 などを利用したクエリを実行した際にその効果が発揮されます。

クラスタ化フィールドを指定するには、テーブル作成の画面で**「クラスタリング順序（オプション）」**にクラスタ化したいフィールドを入力します（BigQuery コンソールの場合）。

スクリーンショット 2020-09-24 4.51.40.png

次の例では、 テーブルが event_date でパーティション分割され、 user_id でクラスタ化されています。 クエリは特定の範囲でパーティションを検索するため、 5 つのうち 2 つのパーティションのみが考慮されます。また、user_id の検索もクラスタ化によって特定の範囲で行われるため BigQuery はその範囲内のフィールドだけを読み取ります。

スクリーンショット 2020-09-25 2.33.20.png

-- user_id を絞っても見積もり時の消費量に変化はないが、実行後の詳細をみると消費が抑えられていることを確認できる
SELECT c1, c3
FROM `project-id.dataset_id.table_id`
WHERE user_id BETWEEN 50 AND 60 -- クラスタ化フィールド
AND event_date BETWEEN "2020-01-03" AND "2020-01-04" -- 分割フィールド

パーティション分割テーブルでは、各パーティションの範囲内のデータに対してクラスタリングが維持されます。また、複数のフィールドでテーブルをクラスタ化する場合は、指定するフィールドの順序が重要です。この順序によってデータの並べ替え順が決まるからです。

補足として、クラスタ化テーブルのパフォーマンスを維持する（データの挿入によって弱まったデータの並び替えの精度を復元する）ため、BigQuery は自動で再度クラスタリングを実行しています。

マテリアライズド ビュー

マテリアライズド ビューは、通常のビューとは違ってクエリを保存するだけでなく、クエリの結果を定期的にキャッシュします。これにより、より高速でリソースの消費も少ないクエリを実行することが可能です。特に実行頻度の高いクエリが存在する場合に、マテリアライズド ビューを利用することでパフォーマンスが大きく向上する可能性があります。

マテリアライズドビューの作成
CREATE MATERIALIZED VIEW `project-id.dataset_id.table_id` AS
SELECT product_id, SUM(clicks) AS sum_clicks
FROM `project-id.dataset_id.table_id`
GROUP BY product_id

ただし、集約関数を含まないマテリアライズド ビューは作成できない、JOIN や UNNEST が利用できないなど、制限事項が結構多いので使い所には注意が必要です。

前処理 / 変換処理

クエリで少し複雑な処理を実行したい場合の方法についてまとめています。BigQuery からデータを読み取り、BigQuery に中間テーブルを作成するような場合に便利です。

ユーザー定義関数（UDF）

BigQuery はユーザー定義関数（UDF）をサポートしています。UDF を利用すると、SQL または JavaScript を使用して関数を作成できます。こちらの GitHub には、コミュニティ提供の UDF が保存されているので、自身で作成する前に既にユースケースに合いそうな UDF が存在しないか確認した方が良いかもしれません。

UDF には一時的に利用可能なものと永続的に利用可能なものが存在します。

一時的に利用可能な UDF

一時的に利用可能な UDF は、1 つのクエリ内で処理を再利用したい場合に便利です。

一時的に利用可能なUDFの構文
CREATE [OR REPLACE] {TEMPORARY | TEMP} FUNCTION [IF NOT EXISTS]
    function_name
    ([named_parameter[, ...]])
  [RETURNS data_type]
  { sql_function_definition | javascript_function_definition }

次のクエリは、URL デコードを行う一時的に利用可能な UDF を SQL で作成し、それを利用する場合の例です。

一時的に利用可能なUDFの作成と呼び出し
-- UDF の作成
CREATE TEMPORARY FUNCTION URL_DECODE(url STRING) RETURNS STRING AS ((
  SELECT
    STRING_AGG(
      IF(REGEXP_CONTAINS(parsed_url, r'^%[0-9a-fA-F]{2}'), SAFE_CONVERT_BYTES_TO_STRING(FROM_HEX(REPLACE(parsed_url, '%', ''))), parsed_url), '' ORDER BY row_number
    )
  FROM UNNEST(REGEXP_EXTRACT_ALL(url, r'%[0-9a-fA-F]{2}(?:%[0-9a-fA-F]{2})*|[^%]+')) AS parsed_url
  WITH OFFSET AS row_number
));

-- UDF の利用
SELECT URL_DECODE('https://~');

永続的に利用可能な UDF

永続的に利用可能な UDF は、複数のクエリやユーザーで処理を再利用したい場合に便利です。このタイプの UDF を利用するには、まず永続的に利用可能な UDF を作成するクエリを実行します。次の例では URL デコードを行う UDF を永続的に利用できるようにしています。

永続的に利用可能なUDFの構文
CREATE [OR REPLACE] FUNCTION [IF NOT EXISTS]
    [[project_name.]dataset_name.]function_name
    ([named_parameter[, ...]])
  [RETURNS data_type]
  { sql_function_definition | javascript_function_definition }
永続的に利用可能なUDFの作成
CREATE OR REPLACE FUNCTION `project-id.dataset_id.URL_DECODE`(url STRING) RETURNS STRING AS ((
  SELECT
    STRING_AGG(
      IF(REGEXP_CONTAINS(parsed_url, r'^%[0-9a-fA-F]{2}'), SAFE_CONVERT_BYTES_TO_STRING(FROM_HEX(REPLACE(parsed_url, '%', ''))), parsed_url), '' ORDER BY row_number
    )
  FROM UNNEST(REGEXP_EXTRACT_ALL(url, r'%[0-9a-fA-F]{2}(?:%[0-9a-fA-F]{2})*|[^%]+')) AS parsed_url
  WITH OFFSET AS row_number
));

上のクエリを実行すると、次のように 1 行目で指定したデータセット以下に UDF が作成されます。

スクリーンショット 2020-09-24 12.26.37.png
スクリーンショット 2020-09-24 12.26.52.png

永続的に利用可能な UDF は次のように呼び出すことができます。

永続的に利用可能なUDFの呼び出し
SELECT `project-id.dataset_id.URL_DECODE`('https://~');

BigQuery スクリプト / ストアドプロシージャ

BigQuery スクリプトを利用すると、UDF を利用する場合よりもさらに複雑な処理を記述することができます。

BigQuery スクリプトを使用すると、1 回のリクエストで複数のステートメントを BigQuery に送信して、変数を使用したり、IF、WHILE などの制御フロー ステートメントを使用できます。たとえば、変数を宣言し、値を代入し、第 3 の場所にあるステートメントからその変数を参照できます。

次のように変数の宣言や変数に式の結果を格納することができます。

-- 変数宣言 - 構文：DECLARE variable_name[, ...] [variable_type] [DEFAULT expression];
DECLARE target_word STRING DEFAULT 'methinks';
DECLARE corpus_count, word_count INT64;

-- 変数に式の結果を格納 - 構文：SET (variable_name_1, variable_name_2) = expression;
SET (corpus_count, word_count) = (
  SELECT AS STRUCT COUNT(DISTINCT corpus), SUM(word_count)
  FROM `bigquery-public-data`.samples.shakespeare
  WHERE LOWER(word) = target_word
);

SELECT
  FORMAT('Found %d occurrences of "%s" across %d Shakespeare works',
         word_count, target_word, corpus_count) AS result;

また、次のように IF 文 や FOR（LOOP）文を使用することもできます。

DECLARE x INT64 DEFAULT 0;
LOOP
  SET x = x + 1;
  IF x >= 10 THEN
    BREAK;
  END IF;
END LOOP;
SELECT x;

詳細は割愛しますが、そのほかにも様々な構文を使用することが可能なので、是非ドキュメントを参照してみてください。また、ストアドプロシージャを利用すると、このようなスクリプトを保存して利用することもできます。

JSON 関数

BigQuery のクエリは比較的高速に実行されるので、生データが JSON 文字列の場合に、後の整形を前提として BigQuery を生データの保存場所として利用することも可能です。その際には JSON 関数を利用すれば、フィールドに格納された JSON 文字列から値を取得することができます。

JSON文字列から値を取得
SELECT
  JSON_EXTRACT(json_text, "$.class['students'][0]['name']") AS string_name, -- クォート付き文字列
  JSON_EXTRACT_SCALAR(json_text, "$.class['students'][0]['name']") AS scalar_name, -- 文字列
  JSON_EXTRACT_ARRAY(json_text, "$.class['students']") as string_array -- 配列
FROM UNNEST([
  '{"class": {"students": [{"name": "Jane"}, {"name": "Mike"}]}}'
  ]) AS json_text;

+-------------+-------------+-----------------+
| string_name | scalar_name | string_array    |
+-------------+-------------+-----------------+
| "Jane"      | Jane        | {"name":"Jane"} |
+-------------+-------------+-----------------+
|             |             | {"name":"Mike"} |
+-------------+-------------+-----------------+

スケジューリング

BigQuery では、クエリを定期的に実行（スケジューリング）する機能が存在します。クエリの記述が完了したら、「クエリのスケジュール」から設定を行うことが可能です。

スクリーンショット 2020-09-24 17.59.51.png

複数のクエリを定期実行する場合に、クエリに依存関係を持たせたい場合は Airflow, Digdag などの他のツールを検討する必要があります。

まとめ

本記事では、BigQuery でデータ基盤を構築・運用する際に知っておくと良い機能を簡易的にまとめました。これらの機能を上手く利用すれば、効果的にデータウェアハウスの構築や運用を行うことができるかと思います。

16
9
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
16
9

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?