BigQueryからGCSにエクスポートし、外部ステージ経由でSnowflakeのテーブルにCOPY INTOするときの具体的クエリと注意点

前提

• BigQuery上、Snowflake上での権限周りの設定は済んでいること
• Snowflake側の外部ステージの設定は済んでいること
• BigQuery上とSnowflake上に同じスキーマのテーブルがすでに作成済みであること

コマンド

BigQuery→GCSエクスポート

export data文をBigQuery上で発行して行います。
この例ではgzip圧縮したCSV形式で行います。

EXPORT DATA OPTIONS(
  -- GCSのパス。適宣変えてください。
  uri='gs://some_bucket/path_prefix/*.csv.gz',
  -- 圧縮形式を指定(GZIP, DEFLATE, SNAPPY)。未指定の場合は圧縮無し
  compression='GZIP',
  -- ファイルフォーマットを指定。CSV, JSON, AVROが選択可能
  format='CSV',
  -- ファイルが存在していた場合は上書きたいのでtrue。未指定の場合はfalseになる
  overwrite=true,
  -- ヘッダ行はつけないのでfalse
  header=false,
  -- 区切り文字を指定。デフォルトは「,」だがわかりやすいように明示的に指定
  field_delimiter=','
) AS
-- クエリ。適宜変える。ソーステーブルにカラム追加されたときにエラーにならないようにするにはカラムを明示的に指定する。今回は簡易的に行うだけなので*を指定。洗い替えを想定しているのでWHERE句はつけていないが、追記であれば更新日時などの条件をつけること
select 
  *
from
  `project_id.dataset.src_table_name`

Snowflake側

-- 削除。洗い替えを想定しているため削除している。追記であれば不要
truncate table database_name.schama_name.dst_table_name;

-- 全量投入。カラムは適宣追加・削除する
copy into database_name.schama_name.dst_table_name 
from @database_name.schema.external_storage_name/path/prefix/
-- ファイルフォーマット設定
FILE_FORMAT = (
    -- CSV形式
    TYPE = CSV
    -- gzipしているので指定
    , COMPRESSION = GZIP
    -- export data文ではカラムにエスケープが必要な値が入っているときは「"」で囲われるのでこの指定が必要
    , FIELD_OPTIONALLY_ENCLOSED_BY = '"'
    -- ↓にNONEを指定しないと、BigQuery上のカラム値に「abc\」のようなバックスラッシュが末尾に来る文字列があったときに、その後ろの「,」が区切り文字として認識されずにCOPY INTO時にエラーとなってしまうため必ず指定する
    , ESCAPE_UNENCLOSED_FIELD = NONE
)
-- 再取り込みのことを考え、必ず読み込むようにFORCEをつける
FORCE = true;

注意点

• GCSのAPIコール数
  • 大きなテーブル（数百GB〜数TB）を転送するときにはGCSに大量のファイルが作成される。1日に何回も転送するなど高頻度で転送する場合にはGCSのAPIコール課金に注意が必要。料金計算ツールやCloud Storage の料金
    で試算をすること
• 実行前後での外部ステージクリーンアップ
  • 外部ステージにエクスポートしたファイルをそのまま残しておくとストレージ料金がかかったり、次回読み込み時の邪魔になるため、実行後に削除すること
  • 前回の転送が途中でエラーとなった場合にGCSパス上に中途半端にファイルが残っているかもしれない。実行直前にもクリーンアップすること
  • クリーンアップ用クエリ例：Snowflake上で rm @database_name.schema.external_storage_name/path/prefix/;