データマート - Google BigQuery

Prev Next

概要

Google Cloud PlatformのBigQueryを用いたデータマート定義の設定のDocsです。

書き込みモードについて

TROCCOでは、SQLと出力先テーブルを指定するだけで、簡単にテーブルへの洗い替えや追記などの操作が可能です。
BigQueryでは、テーブルへの書き込み方法を4つのモードから選択できます。

書き込みモード：全件洗い替え

既存のテーブルのレコードをすべて削除し、クエリ実行結果で置き換える（WRITE_TRUNCATE）モードです。

テーブルの洗い替えを以下の通り行います。

  1. 一時テーブルを作成して、データを投入する
  2. 一時テーブルのデータを対象テーブルにWRITE_TRUNCATEする

シンプルな処理のため、データ量が少ない場合や、毎回全件を再計算しても問題ない場合に適しています。
一方で、全データについて処理を実行するため、クエリのスキャン量や処理時間が長くなる傾向があります。
処理時間が長くなったタイミングなどで、他の書き込みモード「増分更新」「SCD Type 2（履歴保持）」などのご利用を検討ください。

書き込みモード：追記

既存のテーブルのレコードの後に、クエリ実行結果が追記（WRITE_APPEND）されます。

「全件洗い替え」とは異なりデータの追記のみ行うため、増分データ（新しいレコード）だけを追加するシンプルな運用に適しています。
既存データとの重複チェックや正確な履歴保持が必要な場合は、他の書き込みモード「増分更新」「SCD Type 2（履歴保持）」のご利用を検討ください。

書き込みモード：増分更新

増分更新は、キーカラムに基づく既存のデータが存在した場合にはDELETEしてからINSERTを行う書き込みモードです。

日次・週次のセグメント・ユーザー別売上データなど、時系列ごとの統計値などを格納するテーブルを作成する処理に適しています。

増分更新モードの処理イメージ

書き込みモード：SCD Type 2（履歴保持）

SCD Type 2（Slowly Changing Dimension Type 2）モードは、履歴を保持しながらディメンションの変更を追跡するモードです。

SCD Type 2（履歴保持）モードを利用することで、利用者や契約者の「住所」や「契約プラン」といった変化する情報について「いつ」「どうであったか」を単一のテーブルに保持できます。
これにより多様な分析要件に柔軟に対応できるほか、過去時点のテーブル（スナップショット）をすべて保持する方法と比較しても、コスト面での優位性を持つテーブル構成となります。

SCD Type 2（履歴保持）の処理イメージ

プラン上の制約

増分更新およびSCD Type 2（履歴保持）は、Essentialプラン以上の契約アカウントでのみ、ご利用いただけます。

BigQuery側で必要な権限

ご利用いただくにあたって必要なBigQuery側の権限は以下となります。

  • bigquery.datasets.get
  • bigquery.jobs.create
  • bigquery.tables.create
  • bigquery.tables.delete
  • bigquery.tables.get
  • bigquery.tables.getData
  • bigquery.tables.list
  • bigquery.tables.update
  • bigquery.tables.updateData

設定項目

STEP1：基本設定

項目名 必須 内容
データマート定義名 ✓ データマート定義名を入力します。
メモ - TROCCO内部で利用するデータマート定義のメモを入力できます。

STEP2：データ取得設定

STEP2のデータ取得設定では、主に以下の項目を設定します。

  • データ取得設定
  • クエリ設定
  • 転送オプション設定
  • その他データ転送モードに依存する設定

データ取得設定

項目名 必須 内容
Google BigQuery接続情報 ✓ あらかじめ登録してあるGoogle BigQueryの接続情報から、今回のデータマート定義に必要な権限を持つものを選択します。
クエリ実行モード ✓ 以下のいずれかのモードを選択します。
  • データ転送モード
    クエリと出力先テーブルを指定するだけで、簡単にテーブルへの洗い替え・追記を実行できます。
  • 自由記述モード
    接続先のDWHに対して、任意のDML・DDLステートメント(INSERT・DELETEや、CREATE・DROPなど)を記述し、実行できます。CREATE TABLE や DELETE など、既存のTROCCOのデータマート定義の転送モードだと実現できないような処理を実行するのに適したモードです。
書き込みモード ✓ 以下のいずれかのモードを選択します。
  • 追記
    • 既存のテーブルのレコードの後に、クエリ実行結果が追記されます。
  • 全件洗い替え
    • 既存のテーブルのレコードが削除されてクエリ実行結果に置き換えられます。
  • 増分更新
  • SCD Type 2（履歴保持）

クエリ設定

データマートのクエリに関する設定項目です。

レガシーSQL

データマートのクエリ設定では、レガシーSQLは利用できません。

クエリの整形

クエリ編集欄の左下にある クエリを整形をクリックすることで、入力したSQLクエリを自動で整形できます。
整形処理では、キーワードの配置やインデント、改行などが見やすく再配置されます。

データ転送モードの場合

データ取得設定のクエリ実行モードでデータ転送モードを選択した場合の項目です。

項目名 必須 内容
出力先データセット ✓ データ出力先のデータセット名を入力します。
データセットの命名規則について、詳しくはBigQuery公式ドキュメント - データセットに名前を付けるを参照ください。
出力先テーブル ✓ データ出力先のテーブル名を入力します。
テーブルの命名規則について、詳しくはBigQuery公式ドキュメント - テーブルの命名を参照ください。
クエリ ✓ クエリを入力します。
クエリ内でテーブル名を指定するときは、dataset_name.table_nameの形式で記述ください。
プレビュー実行をクリックすることで、実行結果を確認できます。
転送前に実行するクエリ - データ転送モードが追記の場合のみ指定できます。
データ転送前に実行するクエリを入力します。出力先のテーブルが存在する場合のみ実行されます。
クエリ内でテーブル名を指定するときは、dataset_name.table_nameの形式で記述ください。
パーティショニング・クラスタリングの設定 - パーティショニング・クラスタリングの設定項目です。詳しくはパーティショニング・クラスタリングの設定を参照ください。
増分更新のクエリ設定について
  • 増分更新設定でキーカラムや処理対象期間の基準カラムに利用するカラムを指定したクエリを記載します。
    • クエリ設定で入力したクエリは、そのままデータマートジョブでは実行されません。
    • 処理対象期間は増分更新設定で行うため、カスタム変数による日時範囲の指定などは不要です。
  • 「プレビューを実行」ボタンを押下することで、増分更新設定でキーカラムや基準カラムの設定が可能になります。
SCD Type 2のクエリ設定について
  • SCD Type 2 設定でキーカラムや増分基準カラム、処理対象期間の基準カラムに利用するカラムを指定したクエリを記載します。
    • クエリ設定で入力したクエリは、そのままデータマートジョブでは実行されません。
    • 処理対象期間はSCD Type 2 設定で行うため、カスタム変数による日時範囲の指定などは不要です。
  • 「プレビューを実行」ボタンを押下することで、SCD Type 2 設定でキーカラムや増分基準カラム、基準カラムの設定が可能になります。

パーティショニング・クラスタリングの設定

データ転送モードを選択した場合に設定できます。
パーティショニング・クラスタリングについて、詳しくは分割テーブルの概要・クラスタ化テーブルの概要をそれぞれ参照ください。

パーティショニング・クラスタリング設定の適用条件

パーティショニング・クラスタリングの設定はテーブルの新規作成時にのみ有効です。
出力先に既にテーブルが存在する場合は、本設定の内容ではなく既存テーブルの設定内容でジョブが実行されます。

「取り込み時間により分割」におけるパーティションの境界

Google BigQueryの仕様上、パーティションの境界はUTC時間に基づきます。ご注意ください。

項目名 必須 内容
パーティショニング - 以下のいずれかを選択できます。
  • 取り込み時間により分割：TROCCOのジョブ実行時間に基づいてパーティションが切られます。
  • フィールドにより分割：基準となるカラムに基づいてパーティションが切られます。
パーティションフィールド ✓ フィールドにより分割を選択したときに入力します。
DATE、TIMESTAMP、DATETIMEのいずれかの型の列名を入力してください。
パーティションタイプ ✓ パーティショニングでいずれかの分割方式を選択した場合に選択します。
テーブル分割の粒度を以下より選択してください。
  • 1時間ごと
  • 1日ごと
  • 1ヶ月ごと
  • 1年ごと
クラスタリング - クラスタ化テーブルを作成したい場合に設定できます。
クラスタ化列にカラム名を入力することで、該当のカラムに基づいてテーブルがクラスタ化されます。
なお、クラスタ化列は最大で4つまで指定できます。
整数範囲パーティショニング

特定のINTEGER列の値に基づいてテーブルを分割する、整数範囲パーティショニングによるテーブル新規作成には対応していません。
なお、整数範囲パーティショニングによる既存テーブルへの転送は可能です。

自由記述モードの場合

データ取得設定のクエリ実行モードで自由記述モードを選択した場合の項目です。

項目名 必須 内容
クエリ ✓ クエリを入力します。
クエリ内でテーブル名を指定するときは、project_name.dataset_name.table_nameの形式で記述ください。
データ処理ロケーション - クエリを実行するGoogle BigQueryのロケーションを指定します。
クエリ内でロケーションに紐付かないリソースを指定する場合はご指定ください。
未指定の場合はGoogle BigQueryによって自動的にロケーションが判別されます。
詳しくは、BigQueryの公式ドキュメント - ロケーションを指定するを参照ください。

転送オプション設定

設定が以下の場合に表示されます。

  • クエリ実行モードがデータ転送モード
  • 書き込みモードが全件洗い替えまたは追記
項目名 必須 デフォルト値 内容
ジョブの並列実行 ✓ 並列でのジョブ実行はしない ジョブ実行時点で、同一のデータマート定義による他のジョブが実行中の場合に、ジョブを実行するかどうかを選択します。
  • 並列でのジョブ実行はしない：ジョブは実行されずスキップされます。
  • 並列でのジョブ実行を許可：ジョブは実行されます。
「ジョブの並列実行」が設定可能な書き込みモードについて

データマートジョブ実行時に発行するクエリの都合上、書き込みモード「増分更新」「SCD Type 2（履歴保持）」ではジョブの並列実行はできません。

増分更新設定

設定が以下の場合に表示されます。

  • クエリ実行モードがデータ転送モード
  • 書き込みモードが増分更新

クエリ設定のクエリで取得したカラムを利用して、増分更新のデータ更新方法を定義します。

項目名 必須 内容
キーカラム ✓ レコードを一意に識別するキーを指定します。複数のカラムを指定できます。
キーが一致するレコードが存在した場合の挙動 ✓ キーカラムが一致するレコードが既に存在する場合の処理を選択します。
  • 上書き：キーカラムが一致する既存のレコードを一旦DELETEしてから、新しいレコードをINSERTします。データの整合性を重視します。
  • スキップ：既存のレコードをそのまま残し、キーカラムが一致しない新しいレコードだけをINSERTします。
処理対象期間 ✓ 処理対象とするデータの期間を指定します。
  • 範囲を指定する：指定したカラムを基準に、任意の期間でデータを処理します。範囲の指定方法は処理対象期間の範囲指定についてを参照ください。
  • 全データを対象にする：全データを処理対象とします。DWH内のスキャン範囲が広がり、クエリコストが大きく増加する可能性があります。

設定後、実行されるクエリは「実行クエリプレビュー」より確認できます。

SCD Type 2 設定

設定が以下の場合に表示されます。

  • クエリ実行モードがデータ転送モード
  • 書き込みモードがSCD Type 2（履歴保持）

クエリ設定に基づいてデータの更新方法を定義します。

項目名 必須 内容
キーカラム ✓ ビジネスキーとなるカラムを指定します。複数のカラムを指定できます。指定したキーカラムが一致する既存レコードに対して、増分基準カラムの値が更新されている場合は、新しいレコードが作成されます。
増分基準カラム ✓ データの新しさを判定できる「更新日時」や「連番」のカラムを1つ指定します。このカラムに変更があった場合、既存レコードの有効終了日時を更新し、新しいレコードが作成されます。
指定可能なデータ型：DATE、DATETIME、TIMESTAMP、TIME、INT64、INTEGER、NUMERIC、BIGNUMERIC、FLOAT64、FLOAT、DECIMAL
処理対象期間 ✓ 処理対象とするデータの期間を指定します。
  • 範囲を指定する：指定したカラムを基準に、任意の期間でデータを処理します。範囲の指定方法は処理対象期間の範囲指定についてを参照ください。
  • 全データを対象にする：全データを処理対象とします。DWH内のスキャン範囲が広がり、クエリコストが大きく増加する可能性があります。

設定後、実行されるクエリは「実行クエリプレビュー」より確認できます。

増分基準カラムの選定

増分基準カラムの選定に迷われた際は、ソーステーブルに存在する「更新日時」カラムを指定するケースが一般的です。

自動管理カラム

SCD Type 2（履歴保持）モードを使用すると、以下のカラムが出力先テーブルに自動的に追加・管理されます。

カラム名 データ型 内容
trocco_valid_from TIMESTAMP レコードの有効開始時刻。ジョブの実行時刻が入ります。
trocco_valid_to TIMESTAMP レコードの有効終了時刻（NULLは現在有効であることを示します）。nullでない場合は、trocco_is_current が無効にされた時刻が入ります。
trocco_is_current BOOL 現行フラグ（TRUEは現在有効なレコードであることを示します。）
SCD Type 2（履歴保持）モードの処理概要

SCD Type 2（履歴保持）モードでは、トランザクション内で以下の処理を行います。

  1. Step 1：変更があるレコードの既存行をクローズ
    • trocco_valid_toに現在時刻を設定
    • trocco_is_currentをFALSEに更新
  2. Step 2：新規レコードまたは変更レコードの新バージョンを挿入
    • trocco_valid_fromに現在時刻を設定
    • trocco_valid_toにNULLを設定
    • trocco_is_currentをTRUEに設定
出力先テーブルのスキーマ

SCD Type 2（履歴保持）モードを使用する場合、出力先テーブルには自動管理カラム（trocco_valid_from、trocco_valid_to、trocco_is_current）が必要です。
テーブルが存在しない場合は自動的に作成されます。既存のテーブルを使用する場合は、これらのカラムが存在することを確認してください。

出力元テーブルからレコードを削除した場合

SCD Type 2（履歴保持）モードにて、出力元テーブルからレコードを削除しても、出力先テーブルのレコードは削除されずそのまま残ります。

STEP3：品質チェック設定

プラン上の制約

本機能は以下のプランで提供しています。

  • Professionalプラン
  • Essentialプラン、Advancedプラン（有償オプション）

詳しくは、営業担当者またはカスタマーサクセスまでお問い合わせください。

利用できるクエリ実行モード

品質チェック設定が有効にできるのは、クエリ実行モードがデータ転送モードの場合のみです。
自由記述モードでは品質チェック設定は利用できません。

ジョブ実行時に重複する値や不正なNULLのチェックを実施できます。
品質チェックを有効にすると、以下の項目を設定できます。

項目名 必須 内容
単一カラムチェック ✓（※） 各カラム単位での品質チェック（一意性チェック・NOT NULLチェック）の対象カラムを選択します。各チェックの仕様については品質チェックできる内容についてを参照ください。
複合カラム一意性チェック ✓（※） 複数カラムの値の組み合わせに重複がないかチェックします。複数の組み合わせを追加できます。各組み合わせでは2つ以上のカラムを指定する必要があります。各チェックの仕様については品質チェックできる内容についてを参照ください。
失敗時の挙動 ✓ 品質チェックで失敗となった場合の挙動を設定します。
  • 記録のみ：品質チェック失敗時、警告として記録しますがジョブは失敗になりません。
  • ジョブ失敗として扱う：品質チェック失敗時、ジョブ全体を失敗状態にします。
品質チェック対象データ設定 ✓ 品質チェックの対象とするデータの範囲を設定します。
  • 範囲を指定してチェックする：指定したカラムを基準に、任意の期間で品質チェックを行います。範囲の指定方法は処理対象期間の範囲指定についてを参照ください。
  • 全データを対象にチェックする：チェック対象を全データとします。DWH内のスキャン範囲が広がり、クエリコストが大きく増加する可能性があります。

※ 単一カラムチェックまたは複合カラム一意性チェックのいずれか1つ以上の設定が必須です。

品質チェックできる内容について

以下のチェック方法をサポートしています。

項目名 内容 サポート対象外のデータ型
一意性チェック 指定カラムの値、もしくはカラムの組み合わせが一意であることを検証できます。 JSON, GEOGRAPHY
NOT NULLチェック 指定カラムに NULL 値が含まれていないか検証できます。 ARRAY
一意性チェックと品質チェック対象データ設定の組み合わせについて

「範囲を指定してチェックする」を有効にしたうえで単一カラムまたは複合カラムに対して一意性チェックを実行した場合、チェックが行われるのは処理対象期間内のみになります。テーブル全体でのチェックが行われるわけではないのでご注意ください。
テーブル全体でのチェックが必要な場合は「全データを対象にチェックする」を選択してください。

処理対象期間の範囲指定について

項目名 必須 内容
基準カラム ✓ 範囲指定の基準とするカラムを選択します。DATETIME・TIMESTAMP型のカラムのみ選択可能です。
期間 ✓ チェック対象とする範囲を指定します。
タイムゾーン ✓ 範囲の基準とする現在の日時のタイムゾーンを指定します。基準カラムがDATETIME型の場合、指定したタイムゾーンで日時が解釈されます。