BigQueryでGCS指定フォルダにある最新CSVファイルのみを動的に読み込む方法

どうしてもAPIでのデータ連携ができないシステムにおいてCSVファイルの定期更新で対応する場合など、CSVをGoogle Cloud Strage(GCS)経由でBigQueryに取り込んでデータ分析に利用したいケースがあります。

このような場合にデータ量が少なければ、Googleスプレッドシートのデータを読み込めばよいのですが、データ量が多すぎる場合は他の方法が必要です。今回はそのような場合のオススメの方法になります。完全自動化ができない場合でも少しでも効率的なワークフローを構築できるのが効率化を追い求めるマーケターの責務です!

CSVデータを定期的にGCSへアップロード

GCSに “YYYMMDD.csv” のファイル名で日付ごとの最新の全データ(差分ではない)をアップロードします(差分の場合はまた別の方法になります)。こうすると、

  • 20231028
  • 20231029
  • 20231030

のように日付データがどんどん増えていきます。今回はこの日付データの最新のファイルのみBigQueryで読み込むのが最終的な目的です。あまりGCSについて詳しくないユーザーが操作するのであれば、メールやGoogle DriveやDropboxの指定フォルダにアップロードされたファイルをGCSにアップロードする仕組みを構築しても良いですね。ここはユーザーのリテラシー次第で調整しましょう。

読み込み設定の手順

  1. ワイルドカードを使用してBigQueryからGCSの特定フォルダ配下のCSVファイルを外部テーブルとして指定
  2. “_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で一緒に働いてみませんか?