PostgreSQLのテーブルをBigQueryにとりあえずコピーする雑な方法を調べたのでメモします。

ググっても複雑なソリューションばかりで簡易な方法が無かったので調べたものです。

MySQLなどの他のDBでも同様の方法でいけると思います。

こういうのでいいんだよ、こういうので

1. psql コマンドなどでDBのテーブルを改行区切りJSONで出力
2. 出力を gsutil で GCP Cloud Strage にアップロード
3. bq load コマンドで Cloud Strage からBigQueryテーブルにコピー

uri="gs://mybucket/temp$(date +%FT%T)-$RANDOM.ndjson.gz"
q='select ROW_TO_JSON(mytable) from mytable order by id'
psql $PGMMDB -t -A -c "$q" | gzip | gsutil cp - "$uri"
bq load --replace --source_format=NEWLINE_DELIMITED_JSON mydataset.mytable "$uri"

工夫したところ:

• gzip 圧縮すると回線に優しい
• ファイル形式はCSVでもいいが、データ中に改行か引用符があるとハマる。JSONが無難。
• psql ... | bq load のように直結したいのが人情だが、bq loadはパイプや標準入力からの読み込みに対応していない（っぽい）
• 日次更新ならbq loadに--replaceをつけてデータを総入れ替えするのが手っ取り早い
• psqlにはJSON出力するオプションは無いようなのでROW_TO_JSON関数を使う

「こういうのでいいんだよ、こういうので」の要件

BigQueryなどの新しいツールの導入時に陥りがちなのが、

1. （初めは）まだBigQuery上にデータが無い
2. データが無いので分析に使えない
3. 分析に使っていないので、BigQueryの効果が目に見えない
4. BigQueryの効果が分からないので、データをBigQueryにアップロードする作業より他の作業が優先される
5. まだBigQuery上にデータが無い

という負のスパイラルです。なので、

• アプリDBはPostgreSQL
• テーブルは大きめだが巨大ではない（< 10GBぐらい）
• スキーマの変換は不要
• アップロードは1日1回ぐらいの頻度でよい
• 最悪、アップロードに失敗してもいい

という程度の雑な要件で、「とりあえず、アプリのDBテーブルをそのままアップロードしてみよう」と考えました。

• リアルタイムで同期したい
• テーブルサイズが数百GBになる
• 分析用のカラムを追加したい

といった場合にはもっと堅牢なソリューションを使ってください。

むすび

ハンバーグ定食を食べたくなってきました。