こんにちは、香田です。
今回はBigQueryで日付別テーブルから分割テーブルへ移行する方法について紹介していきます。
BigQuery 日付別テーブルとは
移行元になる日付別テーブルについて簡単に紹介すると、日付別にシャーディングされたテーブルで、下記のよう命名方法でテーブルを作成していく方式になります。
テーブル名_yyyymmdd現在はBigQueryのベストプラクティスとして分割テーブルの使用が推奨されているようです。
BigQuery 分割テーブルとは
BigQueryで分割テーブルを利用する場合、おおきく3つの方法があります。
- データの取り込み時間などに基づいて分割される取り込み時間パーティション分割テーブル
- DATE列、TIMESTAMP列、DATETIME列に基づいて分割されるた時間単位の列パーティション分割テーブル
- 整数列に基づいて分割される整数範囲パーティション分割テーブル
それぞれの詳細については下記のドキュメントを参考にしてみてください。
今回は利用ケースが多いと思われる時間単位の列パーティション分割テーブルを紹介していきます。
移行元の日付別テーブルについて
本記事の中で利用する移行元の日付別テーブルですが、BigQueryの一般公開データセットとして提供されているGoogle Analytics 360のga_sessionsテーブルを利用していきます。
bigquery-public-data プロジェクトよりデータセットとしてgoogle_analytics_sampleを選択するとga_sessions_<_yyyymmdd>テーブルが確認できるはずです。
分割テーブルのスキーマ作成
テーブル作成の前に事前にスキーマ定義ファイルを作成していきます。
日付別テーブルから分割テーブルへ移行する為、移行元の日付別テーブルのスキーマ定義を元に分割テーブルのスキーマ定義を作成していきます。
日付別テーブルga_sessionsテーブルのスキーマ定義をJSON形式で取得します
$ bq show --schema --format=prettyjson \
'bigquery-public-data:google_analytics_sample.ga_sessions_20170801' > ga_sessions_schema.jsonパーティショニング列として追加する列のJSONファイルをcreate_date.jsonという名前で作成します。
[
{
"mode": "NULLABLE",
"name": "create_date",
"type": "DATE"
}
]次にjqコマンドを利用してga_sessionsのスキーマJSONファイルへ追加します。
$ jq -s add ga_sessions_schema.json create_date.json > schema.json分割テーブルの作成
次に分割テーブルを作成していきます。先にデータセットを作成します。
$ bq mk --dataset sample_dataset作成したスキーマ定義を利用し分割テーブルを作成します
$ bq mk --table \
--schema ./schema.json \
--time_partitioning_field create_date \
--require_partition_filter \
sample_dataset.ga_sessions下記のような内容で作成されていれば成功です。
日付別テーブルのデータを分割テーブルへ移行する
日付別テーブルのデータ移行はINSERT SELECT文を利用して分割テーブルへ移行していきます。
移行元テーブルの行数を確認。
$ bq query \
--use_legacy_sql=false \
"SELECT
COUNT(*) as count
FROM
bigquery-public-data.google_analytics_sample.ga_sessions_20170801"移行先テーブルへINSERT SELECTでデータを挿入します。
$ bq query \
--use_legacy_sql=false \
"INSERT INTO sample_dataset.ga_sessions
SELECT *, PARSE_DATE('%Y%m%d', '20170801') as create_date
FROM bigquery-public-data.google_analytics_sample.ga_sessions_20170801"移行先テーブルの行数を確認。
$ bq query \
--use_legacy_sql=false \
"SELECT
COUNT(*) as count
FROM
sample_dataset.ga_sessions
WHERE
create_date = '2017-08-01'"移行作業の冪等性を担保する為、事前にテーブル削除する場合は下記で削除可能です。
$ bq rm -f 'sample_dataset.ga_sessions$20170801'さいごに
BigQueryで日付別テーブルから分割テーブルへ移行する方法いかがでしたでしょうか。
分割テーブルへの移行時に本記事が参考になれば幸いでございます。
最後までご覧いただきありがとうございます。