前書き
CSVデータをBigQueryへ新しいテーブルとして作成したり、既存テーブルの上書きや行の追加に用いたりと、そんなユースケースがあるのかもしれない。
今更だが雰囲気でやりがちな既存テーブルへの行の追加という工程をちゃんと理解してみる。どうか私の屍が誰かの小さな幸せにつながることを願うばかりだ。
話さないこと
- CSV以外のロードについて
- ロード時におけるスキーマ比較以外の挙動の仕組み
- BigQueryDTSとは何か
話すこと
- BQへのCSVロードにおけるスキーマ比較の仕組み
- DTSを用いたETLにて実運用上直面する課題
CSVロードの仕組み
CSVロードする際のスキーマにはソースのスキーマと宛先テーブルのスキーマの２つがある。
- BigQueryはソースCSVのヘッダーを特定することでスキーマの列名を把握しようとする
- その後宛先テーブルのスキーマの列名に合わせて同じ列名同士でマッピングを行い、行の追加をする
これだけといえばこれだけだ。
まずはソーススキーマの理解について。
これには種類あり、スキーマの自動検出か手動でのスキーマ設定かだ。
後者については省略するとして前者についてだが、CSVロード時はインターフェースがコンソール上でもCLIでもなんでも、スキーマの自動検出を設定できる。
そして公式ドキュメント にも書かれているが、スキーマ自動検出はデフォルトでは最初の行に注目し、他の行には違うデータ型があるのに最初の行は文字列型のみという場合、最初の行がヘッダーであると認識する。
そうでない場合、ヘッダーを認識できない。
ヘッダー行が見つからない場合はどうなるのだろうか。
新規テーブル作成時はBigQuery側で文字列を割り振る。
既存テーブルへのロード時は、左づめで宛先テーブルのスキーマと同じ列名を割り振る。
これはスキーマの指定も自動検出も設定しなかったケースと同じ挙動となる。
世界は上手く出来ている。
そして紆余曲折を得てソースのスキーマが決まったら満を辞して宛先テーブルの列名とマッピングさせて、対応する列にデータを紐づけていく。
以下のフローチャートを参考にしてほしい。
※完全なる蛇足(書いてて気持ちよくなって手を出してしまった)
ちなみにロード時のオプションとして、 skip_leading_rowsなるものがあるが、これはロード時に省略する先頭の行数を指定するだけで、その後は前述したロジックの通りにスキーマを決定していく。
残念ながら明らかに先頭行がへッダーだとわかっていてもデータが全て文字列型の場合、スキーマの自動検出ではヘッダーが検出されず、データの一部とみなされてしまう。
そのような場合、skip_leading_rowsを適用してヘッダー行を除外させるが、それをヘッダーとして認識してくれるわけではなく、スキーマの自動検出の場合はBigQuery側で列名が割り振られてしまう。
なのでskip_leading_rowsを指定する際はスキーマをこちらが指定できるよう手動で設定することが一般的である。
BigQuery Data Transfer Service (DTS)での挙動
そしてDTSにはスキーマの自動検出機能がなんと存在しないし、手動の設定もない。
これの場合どうなるかというと、ヘッダーが検出されないので、宛先テーブルのスキーマに基づいて左詰めで同じ列を割り振っていく。
これにより実運用にかなりの影響が出るので紹介しよう。
例えば分析要件の都合上、事業システムのデータベースへの変更はログ単位での追跡が必要である場合に、最小単位でのCDCの仕組みを取り入れようと、RDBからAWS DMSや debeziumなどのCDCツール経由でs3にCSVとしてデータを吐き出していた状況を想定する。
実運用においてはソースとなる事業データベースなどでスキーマの変更は日常茶飯事。それに対して変更を検知したらエラーを起こしたいのか、スキーマ変更をシームレスに変更してサイレントで居たいのかは要件によって異なるが、DTSの左詰でのスキーマの強制割り振りは取れる選択肢を限定させてしまう。
列の追加
新規で追加された列がスキーマの最後の場合
宛先テーブル
| a | b |
|---|
ソーススキーマ
| a | b | c |
|---|
この場合は左づめで考えると、新しく追加された列がBigQueryにとってUnknown Valueに当たるので、転送設定で指定している ignore_unknown_valuesに従う
Trueならば新しい列は削ぎ落とされるし、Falseならばエラーとなる
開発側のスキーマ変更を事後対応で検知した後に分析スキーマへ同期しても良いケースなどは、ここでエラーを起こさせる手段も有効だ
もし新しい列を宛先スキーマに追加したい場合は、まず宛先テーブルに列を追加して、転送設定の ignore_unknown_valuesをTrueにした上で転送を行い、ignore_unknown_valuesをFalseに戻す必要があるなら戻す(古いスキーマから発生したソースデータにも対応するため)
新規で追加された列がスキーマの途中にある場合
宛先テーブル
| a | b |
|---|
ソーススキーマ
| a | c | b |
|---|
cという列が途中に追加されると、DTSは左詰になるので
a(ソース) -> a(宛先)
c(ソース) -> b(宛先)
b(ソース) -> Unknown Value
となり、宛先テーブルに列を新規追加する際に列の順番をソースと一致させるように一工夫する必要がある。
しかしソースのCSVファイルが途中から古いスキーマで、途中から新しいスキーマに代わっていた場合、厄介だ。
仮に事前にBQ側のスキーマをアップデートさせると、ソースにある古いスキーマの時に発生したデータの取り込み時において、左づめにより
a(ソース) -> a(宛先)
b(ソース) -> c(宛先)
になるのでエラーになるか、下手をするとサイレントのままDWHが汚染される。
なので、
- 古いスキーマのデータをまずは取り込む
- BQテーブルのスキーマを修正する
- 新しいスキーマのデータを取り込む
というふうに段階を踏む必要があり、データプロダクトとしてのスケーラビリティに乏しい。
列の削除
まぁ似たような類なので割愛する。
まとめ
ETLツールの選択肢において、基本的にツール自体の利用料が無料でありマネージドサービスであるBigQueryDTSは小さな組織において今でも有力な選択肢だ。
しかしソースファイルがCSVという部分が変更できないのであれば、ETLツールの比較の際はDTSという選択肢にこのようなデメリットがあることを十分に理解しておく必要がある。
もしもコスト要件がかなり厳しくてDTSが魅力的な前提で、ソースのformatを十分に調整可能なのであればソースと宛先のスキーママッピングが左詰にならないCSV以外のJSONなどのフォーマットを視野に入れる、
調整不可能であれば他ツールの利用を視野に入れるか運用フローの仕組み化をオペレーションでカバーしたり、スキーマ変更への対応の自動化をフルスクラッチで開発するなど技術でのカバーが必要となり、一手間必要となってくる。