この記事は BrainPad Advent Calendar 2020 2日目の記事になります

こんにちは@nissy0409240です
BrainPadでエンジニアをしています
最近は@BrainPadProductの中の人もしています！是非フォローをよろしくお願いします！

さて、2020年もあと一ヶ月を切りましたが
皆様いかがお過ごしでしょうか
今年は新型コロナの影響もあり移動について考えさせられた1年だったように思います
ふと移動に思いを巡らすと移動というもの一つとっても様々なものがあります
観光・異動・引越し・データ移行...
本エントリーではBig Queryのテーブルデータを移行した際に調べたこと
またどのように実施したかをお話出来ればと思います

背景

昨今、こちらのエントリーのようにデータ基盤として
GCPのBigQueryを採用する事例は珍しいものではないと感じます
しかし、データ基盤は構築して終わりではありません
運用・保守対応が発生します

テーブルやデータセット、カラムの追加は
イメージ出来る方も多いと思いますが
テーブルデータの移行をしなくてはいけない時もあります
しかも移行だけではなく簡単なデータの変換も必要な状況です

そんな訳で今回やりたいことはこの二つになります

テーブルデータをエクスポートして別のテーブルにインポートしたい
- テーブル名は同じにする
- レコード数はテーブルによって異なる(多いものはめちゃ多い...)
エクスポートしたデータの中身を変換したい
- ひとつのカラムを機械的に全件変換する

方法検討

やると決まったら方法の検討です
ドキュメントを読み漁り
以下の2パターンに絞りました

方法	メリット	デメリット
エクスポート/変換/ロード	エクスポート自体の料金はかからない	スクリプトをまとめて実行するのでシェルの作成や変換処理の実装で多少の手間はかかる
データセットコピー/UPDATEステートメント実行	コマンド二つで作業が完了する	データセットのコピーはβ版。UPDATE文実行がフルスキャンになってしまうため料金が跳ね上がる可能性がある

やはりUPDATEが全件フルスキャンとなりコストがかかる可能性やβ版の機能よりは枯れた技術を使う方がいいという考えから今回は「エクスポート/変換/ロード」を採用しました

スクリプト作成

ここまで決まったらスクリプト作成です
リファレンスを参考にスクリプトを作成していきます
一行一行解説していくのもくどいので処理ごとにコメントを書きつつ
出来上がったスクリプトを下記に記します
(URLやパス、テーブル名などは擬似的なものに書き換えています)

bigquery_table_translate.sh

#!/bin/bash

BEFORE_DATA_SET=$1
AFTER_DATA_SET=$2

# 実行環境に一時ディレクトリ作成
mkdir -p tmp/

while true ;do
    for TARGET_TABLE in hoge huga piyo;do
        # 移行元テーブルからエクスポート
        bq extract --compression GZIP --destination_format NEWLINE_DELIMITED_JSON \
            --print_header=false \
            "${BEFORE_DATA_SET}.${TARGET_TABLE}" \
            gs://aexample-bucket/path/to/export_${TARGET_TABLE}.json.gz

        # GCSからエクスポートしたファイルを取得
        gsutil cp gs://example-bucket/path/to/${TARGET_TABLE}.json.gz tmp/
        gunzip tmp/${TARGET_TABLE}.json.gz

        # sedで置換
        sed -i "s/\"example_key\":\"before\"/\"example_key\":\"after\"/g" tmp/${TARGET_TABLE}.json

        # GCSへアップロード
        gsutil cp tmp/${TARGET_TABLE}.json gs://example-bucket/path/to/load/

        # 一時ディレクトリのファイル削除
        rm tmp/${TARGET_TABLE}.json

        # 移行先テーブルへロード
        bq load --source_format=NEWLINE_DELIMITED_JSON "${AFTER_DATA_SET}.${TARGET_TABLE}" gs://example-bucket/path/to/load/${TARGET_TABLE}.json >/dev/null 2>/dev/null

        # GCSのファイルを削除
        gsutil rm gs://aexample-bucket/path/to/export_${TARGET_TABLE}.json.gz
        gsutil rm gs://example-bucket/path/to/load/${TARGET_TABLE}.json
    done
done

exit

実行結果

実際にテスト用データセットで実行してみると無事、指定したデータセット間で同じ名前のテーブルが作成され置換も成功し、データもロードされていました
件数も想定通り！
めでたしめでたし...とはなりません！

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

Big Queryではパーティション分割テーブルを作成することが出来ます
パーティショニングすることでパフォーマンスの向上とコスト削減を行うことが可能です
パーティション分割テーブルでは_PARTITIONTIMEや_PARTITIONDATE擬似列が存在し実行時にはこちらを指定することでテーブルに読み込まれた日付を指定することが可能となります

先ほどのスクリプトではこちらを考慮出来ておらず過去の_PARTITIONTIMEを指定した際に何も抽出されず、
実行した時点の_PARTITIONTIMEを指定したところ全件出力されしまいました

そこでエクスポートする際、パーティションデコレータを指定してエクスポートすることにしました

先ほどのスクリプトのエクスポート部分をパーティションデコレータを付与する形式に書き換えて上げます

# 移行元テーブルからエクスポート(修正前)
bq extract --compression GZIP --destination_format NEWLINE_DELIMITED_JSON \
    --print_header=false \
    "${DATA_SET}.${TARGET_TABLE}" \
    gs://aexample-bucket/path/to/export_${TARGET_TABLE}.json.gz

# 移行元テーブルからエクスポート(修正後)
bq extract --compression GZIP --destination_format NEWLINE_DELIMITED_JSON \
    --print_header=false \
    "${DATA_SET}.${TARGET_TABLE}\$$20201201" \
    gs://aexample-bucket/path/to/export_${TARGET_TABLE}_$20201201.json.gz

しかし、上記に書いたような_PARTITIONTIMEのハードコーディングは実際のテーブルでは1日分のテスト以外役に立ちません

スクリプト全体を修正します

bigquery_table_translate_partitiontime.sh

#!/bin/bash

BEFORE_DATA_SET=$1
AFTER_DATA_SET=$2

#
# 日付
#
FROM_DATE=$3
TO_DATE=$4
TARGET_DATE=$FROM_DATE

# 実行環境に一時ディレクトリ作成
mkdir -p tmp/

while true ;do
    for TARGET_TABLE in hoge huga piyo;do
        # 移行元テーブルからエクスポート
        bq extract --compression GZIP --destination_format NEWLINE_DELIMITED_JSON \
            --print_header=false \
            "${BEFORE_DATA_SET}.${TARGET_TABLE}\$${TARGET_DATE}" \
            gs://aexample-bucket/path/to/export_${TARGET_TABLE}_${TARGET_DATE}.json.gz

        # GCSからエクスポートしたファイルを取得
        gsutil cp gs://example-bucket/path/to/${TARGET_TABLE}_${TARGET_DATE}.json.gz tmp/
        gunzip tmp/${TARGET_TABLE}_${TARGET_DATE}.json.gz

        # sedで置換
        sed -i "s/\"example_key\":\"before\"/\"example_key\":\"after\"/g" tmp/${TARGET_TABLE}_${TARGET_DATE}.json

        # GCSへアップロード
        gsutil cp tmp/${TARGET_TABLE}_${TARGET_DATE}.json gs://example-bucket/path/to/load/

        # 一時ディレクトリのファイル削除
        rm tmp/${TARGET_TABLE}_${TARGET_DATE}.json

        # 移行先テーブルへロード
        bq load --source_format=NEWLINE_DELIMITED_JSON "${AFTER_DATA_SET}.${TARGET_TABLE}\$${TARGET_DATE}" gs://example-bucket/path/to/load/${TARGET_TABLE}_${TARGET_DATE}.json >/dev/null 2>/dev/null

        # GCSのファイルを削除
        gsutil rm gs://aexample-bucket/path/to/export_${TARGET_TABLE}_${TARGET_DATE}.json.gz
        gsutil rm gs://example-bucket/path/to/load/${TARGET_TABLE}_${TARGET_DATE}.json
    done
    if [ "$TARGET_DATE" = "$TO_DATE" ] ; then
        # 一時ディレクトリ削除
        rm -Rf tmp/
        break
    fi
        TARGET_DATE=$(date -d "$TARGET_DATE 1day" "+%Y%m%d")
done

exit

こちらを実行し、無事過去の_PARTITIONTIMEもそのままに移行することが出来ました
めでたしめでたし！

最後までお付き合い頂きありがとうございました

参考

Transfer Big Query table as it is

背景

方法検討

スクリプト作成

実行結果

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

参考