どうしてもAPIでのデータ連携ができないシステムにおいてCSVファイルの定期更新で対応する場合など、CSVをGoogle Cloud Strage（GCS）経由でBigQueryに取り込んでデータ分析に利用したいケースがあります。
このような場合にデータ量が少なければ、Googleスプレッドシートのデータを読み込めばよいのですが、データ量が多すぎる場合は他の方法が必要です。今回はそのような場合のオススメの方法になります。完全自動化ができない場合でも少しでも効率的なワークフローを構築できるのが効率化を追い求めるマーケターの責務です！
CSVデータを定期的にGCSへアップロード
GCSに “YYYMMDD.csv” のファイル名で日付ごとの最新の全データ（差分ではない）をアップロードします（差分の場合はまた別の方法になります）。こうすると、
- 20231028
- 20231029
- 20231030
のように日付データがどんどん増えていきます。今回はこの日付データの最新のファイルのみBigQueryで読み込むのが最終的な目的です。あまりGCSについて詳しくないユーザーが操作するのであれば、メールやGoogle DriveやDropboxの指定フォルダにアップロードされたファイルをGCSにアップロードする仕組みを構築しても良いですね。ここはユーザーのリテラシー次第で調整しましょう。
読み込み設定の手順
- ワイルドカードを使用してBigQueryからGCSの特定フォルダ配下のCSVファイルを外部テーブルとして指定
- “_FILE_NAME” メタデータカラムを使用してクエリを作成
ワイルドカードを使用してBigQueryからGCSの特定フォルダ配下のCSVファイルを外部テーブルとして指定
以下のようにワイルドカードでフォルダ内にアップロードしたファイルを全て対象に読み込めるようにしておきます。
例: gs://your-bucket/path-to-files/*.csv
“_FILE_NAME” メタデータカラムを使用してクエリを作成
ファイル名から “20231031” のような8桁の数字を抽出し、それを日付として解釈します。
そして、その中で最新の日付を取得して、取得した日付で絞り込みます。
WITH latest_file AS (
-- ファイル名から日付部分を取得し、最新のものを特定
SELECT
MAX(PARSE_DATE('%Y%m%d', REGEXP_EXTRACT(_FILE_NAME, r'(\d{8})\.csv$'))) AS latest_date
FROM
`project_id.dataset_id.external_table`
)
-- 最新のファイルのデータのみをクエリ
SELECT
e.*
FROM
`project_id.dataset_id.external_table` e
JOIN
latest_file lf
ON
PARSE_DATE('%Y%m%d', REGEXP_EXTRACT(e._FILE_NAME, r'(\d{8})\.csv$')) = lf.latest_date;
もしくは、WHERE句を使った以下の方法でも大丈夫です。特に今回のケースですとパフォーマンスも大きく変わらないでしょう。
WITH latest_file AS (
-- ファイル名から日付部分を取得し、最新のものを特定
SELECT
MAX(PARSE_DATE('%Y%m%d', REGEXP_EXTRACT(_FILE_NAME, r'(\d{8})\.csv$'))) AS latest_date
FROM
`project_id.dataset_id.external_table`
)
-- 最新のファイルのデータのみをクエリ
SELECT
e.*
FROM
`project_id.dataset_id.external_table` e
WHERE
PARSE_DATE('%Y%m%d', REGEXP_EXTRACT(e._FILE_NAME, r'(\d{8})\.csv$')) = (SELECT latest_date FROM latest_file);GLASSではマーケティング基盤の構築でマーケターがこのような仕組みを構築して現実的な現場業務を踏まえたワークフローの構築を行っております。このような効率的なワークフローの企画と実装に明るい・興味のある方を募集しております！
株式会社 GLASS代表。広告からSEOなどあらゆるデジタルマーケティングに精通するデータアナリスト。徹底的に現場に根付いた、スピーディーでシンプルなデータ分析環境の構築と施策推進を得意とする。
＜経歴＞
株式会社マイネットでモバイルCRMサービス（後にYahoo! JAPANに売却）のマーケティング部長、モバイルアプリ事業を立ち上げ。Googleで広告代理店営業マネージャーとして国内50社以上の広告代理店のコンサルティング。通信テクノロジー業界シニアアカウントマネージャーとして大手企業向けのマーケティング支援に関わる。
