Transfer Big Query table as it is

この記事は [BrainPad Advent Calendar 2020](https://qiita.com/advent-calendar/2020/brainpad) 2日目の記事になります

こんにちは[@nissy0409240](https://twitter.com/nissy0409240)です
BrainPadでエンジニアをしています
最近は[@BrainPadProduct](https://twitter.com/brainpadproduct)の中の人もしています！是非フォローをよろしくお願いします！
![スクリーンショット 2020-12-02 4.06.07.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/100333/a0a393bd-e825-770a-ce50-a47fd18ddbbe.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/100333/2fcd4f6c-0544-e8de-d430-1e343030bd0d.png)


# 背景

昨今、[こちらのエントリー](https://blog.brainpad.co.jp/entry/2019/09/20/120715)のようにデータ基盤として
GCPのBigQueryを採用する事例は珍しいものではないと感じます
しかし、データ基盤は構築して終わりではありません
運用・保守対応が発生します

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

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

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

# 方法検討

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

| 方法 | メリット | デメリット |
|:-:|:-:|:-:|
|  [エクスポート](https://cloud.google.com/bigquery/docs/exporting-data?hl=ja#bq)/変換/[ロード](https://cloud.google.com/bigquery/docs/loading-data-cloud-storage-json) |  [エクスポート自体の料金はかからない](https://cloud.google.com/bigquery/docs/exporting-data?hl=ja#pricing) | スクリプトをまとめて実行するのでシェルの作成や変換処理の実装で多少の手間はかかる  |
|  [データセットコピー](https://cloud.google.com/bigquery/docs/copying-datasets?hl=ja)/[UPDATEステートメント](https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax?hl=ja#update_statement)実行 |  コマンド二つで作業が完了する | データセットのコピーはβ版。UPDATE文実行がフルスキャンになってしまうため[料金](https://cloud.google.com/bigquery/pricing/?hl=ja#dml)が跳ね上がる可能性がある|

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

# スクリプト作成

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

```shell: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では[パーティション分割テーブル](https://cloud.google.com/bigquery/docs/creating-partitioned-tables?hl=ja)を作成することが出来ます
パーティショニングすることで[パフォーマンスの向上とコスト削減を行うことが可能](https://cloud.google.com/bigquery/docs/best-practices-costs?hl=ja#partition_data_by_date)です
パーティション分割テーブルでは`_PARTITIONTIME`や`_PARTITIONDATE`[擬似列](https://cloud.google.com/bigquery/docs/querying-partitioned-tables#the_partitiontime_pseudo_column)が存在し実行時にはこちらを指定することでテーブルに読み込まれた日付を指定することが可能となります

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

そこでエクスポートする際、[パーティションデコレータ](https://cloud.google.com/bigquery/docs/managing-partitioned-table-data#browsing_partitioned_table_data)を指定してエクスポートすることにしました

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

```shell
# 移行元テーブルからエクスポート(修正前)
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日分のテスト以外役に立ちません

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

```shell: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`もそのままに移行することが出来ました
めでたしめでたし！

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/100333/3464ff2b-a551-d874-591e-9e90c8bbb3af.png)


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

## 参考

https://cloud.google.com/bigquery/docs/exporting-data?hl=ja#bq
https://cloud.google.com/bigquery/docs/loading-data-cloud-storage-json
https://cloud.google.com/bigquery/docs/exporting-data?hl=ja#pricing
https://cloud.google.com/bigquery/docs/copying-datasets?hl=ja
https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax?hl=ja#update_statement
https://cloud.google.com/bigquery/pricing/?hl=ja#dml
https://cloud.google.com/bigquery/docs/creating-partitioned-tables?hl=ja
https://cloud.google.com/bigquery/docs/best-practices-costs?hl=ja#partition_data_by_date
https://cloud.google.com/bigquery/docs/managing-partitioned-table-data#browsing_partitioned_table_data