DuckDBとTaskfileで作るBQ×スプレッドシートの使い捨てEDA環境 - エムスリーテックブログ

エムスリーテックブログ

エムスリー(m3)のエンジニア・開発メンバーによる技術ブログです

DuckDBとTaskfileで作るBQ×スプレッドシートの使い捨てEDA環境

こんにちは。AI・機械学習チームの高田です。

他部署からスプレッドシートでデータを受け取って、社内のDWHにあるデータと突き合わせながら探索的に分析したい。こういう場面で毎回環境構築するのは手間なので、さっと始められるEDA環境がほしいと思っていました。DuckDB + Taskfileの組み合わせがちょうどよかったので紹介したいと思います。

DuckDBが解決すること

BQコンソールでクエリを書いて、結果をコピーしてスプレッドシートに貼り付けて、条件を変えてまたBQに戻って……。スプレッドシートで受け取ったデータをDWHと突き合わせて分析したいとき、この行き来が地味にストレスになります。

DuckDBはインストールするだけで使えるインプロセスの分析用データベースです。サーバー不要で、手元のCSVやParquetファイルに直接SQLを投げられます。BigQuery拡張やGoogle Sheets拡張を入れると、BQのテーブルやスプレッドシートにも同じSQLの中からアクセスできるようになります。DuckDBを間に挟めば、BQのデータもスプレッドシートのデータも同じSQLの中で扱えるので、この行き来がなくなります。

duckdb.org

BQとGoogle SheetsからDuckDBで加工し、結果をスプレッドシートに書き出すフロー

BQ + GSSを1つのSQLで横断する

DuckDBにはBigQuery拡張とGoogle Sheets拡張があり、この2つを組み合わせるとBQのテーブルとスプレッドシートを同じSQL内でJOINできます。

まず拡張をセットアップします。

INSTALL bigquery FROM community; LOAD bigquery;
INSTALL gsheets FROM community;  LOAD gsheets;

-- BQ認証（ADCを利用）
ATTACH 'project=bigquery-public-data dataset=samples billing_project=my-project' AS bq (TYPE bigquery);

-- GSS認証（access_tokenを使用）
CREATE SECRET (TYPE gsheet, PROVIDER access_token, TOKEN 'ya29...');

セットアップが終われば、BQとGSSのクロスソースJOINが1つのSQLで書けます。

SELECT
  g.name, g.category,
  s.word, s.total_count
FROM read_gsheet('https://docs.google.com/spreadsheets/d/xxx/edit', sheet='test_data') AS g
CROSS JOIN (
    SELECT word, SUM(word_count) AS total_count
    FROM bq.samples.shakespeare
    GROUP BY word ORDER BY total_count DESC LIMIT 5
) AS s
WHERE g.category = 'A';

結果をそのままスプレッドシートに書き戻すこともできます。

COPY (SELECT * FROM analysis_result)
  TO 'https://docs.google.com/spreadsheets/d/xxx/edit'
  (FORMAT gsheet, SHEET 'output');

既存シートへの書き込みのみ対応で、新規シートは作れない点に注意してください。書き込み先のシートは事前に用意する必要があります。

なぜBQ側からGSSを直接読まないのか

BigQueryにもEXTERNAL TABLEや連携テーブルの仕組みはあります。ただ、EDAの文脈では使いにくいと感じています。

分析のたびにEXTERNAL TABLEを定義するのは手間です。「ちょっとスプレッドシートのマスタとJOINしたい」だけなのに、テーブル定義 → クエリ → 後片付けのサイクルを毎回繰り返すのは面倒です。しかも分析が終わった後の削除を忘れると、BQ上にゴミのEXTERNAL TABLEが残り続けます。チームで使っているプロジェクトでこれをやると、誰が作ったかわからないテーブルが量産されていきます。

DuckDB拡張なら設定なしで即座にスプレッドシートにアクセスできますし、ローカルで完結するので後片付けも不要です。

ローカルキャッシュで高速化 & BQ課金ゼロ

BQに直接クエリを投げると、テーブルサイズによっては実行に数十秒、結果のダウンロードに数分かかることもあります。実行するたびにこの待ち時間が発生するので、EDAのテンポが悪くなります。さらに毎回BQのスキャン料金が積み上がっていくので、気軽に試行錯誤しづらくなります。

一度ローカルにキャッシュしてしまえば、SSDから読むだけなのでほぼ一瞬です。

初回だけBQからデータを取得してParquetに保存し、以降はローカルのParquetファイルだけで分析を回す。いわゆるローカルホットキャッシュの考え方です。Parquetは列指向の圧縮フォーマットなので、元のテーブルに比べてファイルサイズもかなり小さくなります。

出力の柔軟性

bq CLIの出力は基本的にテーブル形式かJSONですが、DuckDBはCOPY TOで様々な形式に出力できます。

COPY result TO 'output.csv'     (HEADER, DELIMITER ',');
COPY result TO 'output.json'    (FORMAT JSON, ARRAY true);
COPY result TO 'output.parquet' (FORMAT PARQUET);
COPY result TO 'https://docs.google.com/.../edit' (FORMAT gsheet, SHEET 'Report');

CSV、JSON、Parquet、Google Sheetsを同じ構文で書き分けられるのは地味に便利です。

Taskfileで組む軽量ワークフロー

TaskfileはYAMLベースの軽量タスクランナーです。Makefileの代替として使えますが、チェックサムベースのキャッシュ管理（sources/generates）が組み込まれていて、ソースが変わっていなければ再実行しないという挙動を宣言的に書けます。

taskfile.dev

なぜTaskfileか

DuckDB + ローカルキャッシュの仕組みを手軽に使うには、何らかのタスクランナーが欲しくなります。dbtやAirflow、Snakemakeのような本格的なパイプラインツールもありますが、EDAの段階ではセットアップが重すぎます。もっと軽量に、SQLファイルの実行とキャッシュ管理だけをやってくれるツールがほしい。そこで今回はMake、just、Taskfileの3つを比較してみました。

Makeはどこにでも入っていますが、TAB必須や$$エスケープなど構文の癖が多く、チームで使い回すとハマりがちです。justは構文がすっきりしていますが、ファイルベースのキャッシュ機構がなく、キャッシュがあればスキップする処理を自前で書く必要があります。

Taskfileはsources/generatesでチェックサムベースのキャッシュ管理ができます。ソースが変わっていなければ再実行しないという挙動が宣言的に書けるので、BQを余計に叩いてしまうのを防げます。YAMLで読みやすく、task --listで一覧が出るのもよいです。

観点 Make just Taskfile
変更検知 タイムスタンプ なし チェックサム
パラメータ 環境変数（扱いにくい） 型付き引数 .envファイル + 変数
キャッシュ ファイルの有無で判定 自前実装が必要 sources/generatesで宣言的
インストール 不要 brew install just brew install go-task
自己文書化 make helpを自作 just --list task --list

サンプルプロジェクトを作ってみた

BQの公開データセット（Shakespeare）とGoogle Sheetsを使ったサンプルを用意しました。

BQ側にはShakespeare全作品の単語出現データ（約16万行）が入っています。Google Sheetsには分析対象の作品リストとジャンル分類を用意しました。こんなデータです。

corpus genre
hamlet tragedy
othello tragedy
macbeth tragedy
asyoulikeit comedy
twelfthnight comedy
tempest comedy
kingrichardiii history
kinghenryv history
... ...

この例では、GSSの作品リストでBQの単語データをフィルタし、ジャンル別の頻出単語を集計して、結果を別のシートに書き出す、という流れです。上位N件はCLI引数で指定できるようにしています。

ディレクトリ構成はこうなっています。

eda_duckdb_taskfile/
├── Taskfile.yml
├── .gitignore
└── sql/
    ├── 01_fetch_from_bq.sql     # BQ → Parquetキャッシュ
    ├── 02_fetch_from_gss.sql    # GSS読み取り確認用
    ├── 10_analyze.sql           # ジャンル別の頻出単語集計
    └── 20_upload_to_gss.sql     # 結果をGSSに書き出し

SQLファイルにはBQのテーブル名やスプレッドシートのIDを直接書いています。EDAプロジェクトは分析ごとに使い捨てなので、わざわざ.envで外出しにする必要はありません。GSSのアクセストークンだけはgcloud auth print-access-tokenで動的に取得し、DuckDBのgetenv()で参照しています。

BQからデータを取得してParquetに保存する

sql/01_fetch_from_bq.sqlはBQのShakespeareテーブルをまるごとParquetにキャッシュするSQLです。bigquery_scan()のbilling_projectに自分のGCPプロジェクトを指定する必要があります。

-- sql/01_fetch_from_bq.sql
LOAD bigquery;

COPY (
  SELECT *
  FROM bigquery_scan(
    'bigquery-public-data.samples.shakespeare',
    billing_project='your-gcp-project-id'
  )
) TO 'data/shakespeare.parquet' (FORMAT PARQUET);

SELECT COUNT(*) AS row_count, COUNT(DISTINCT corpus) AS corpus_count
FROM 'data/shakespeare.parquet';

GSSの作品リストとBQの単語データをJOINして集計する

sql/10_analyze.sqlがこのプロジェクトの本体です。GSSから作品リスト（corpus, genre）を取得し、Parquetキャッシュの単語データとINNER JOINして、ジャンル別の頻出単語を集計します。上位N件はtop_n変数で外から渡せるようにしてあります。

-- sql/10_analyze.sql
INSTALL gsheets FROM community;
LOAD gsheets;

CREATE SECRET (TYPE gsheet, PROVIDER access_token, TOKEN getenv('GSS_TOKEN'));

-- GSSから分析対象の作品リスト（corpus, genre）を取得
CREATE TABLE corpus_list AS
SELECT * FROM read_gsheet(
  'https://docs.google.com/spreadsheets/d/your-spreadsheet-id/edit',
  sheet='test_data'
);

-- Parquetキャッシュから単語データを読み、GSSの作品リストでフィルタ
CREATE TABLE analysis_result AS
WITH word_stats AS (
  SELECT
    cl.genre,
    s.word,
    SUM(s.word_count) AS total_count
  FROM 'data/shakespeare.parquet' s
  INNER JOIN corpus_list cl ON s.corpus = cl.corpus
  GROUP BY cl.genre, s.word
),
ranked AS (
  SELECT
    genre, word, total_count,
    ROW_NUMBER() OVER (PARTITION BY genre ORDER BY total_count DESC) AS rank
  FROM word_stats
)
SELECT genre, word, total_count, rank
FROM ranked
WHERE rank <= getvariable('top_n')::INT
ORDER BY genre, rank;

SELECT * FROM analysis_result;

COPY analysis_result TO 'output/analysis_result.parquet' (FORMAT PARQUET);
COPY analysis_result TO 'output/analysis_result.csv' (HEADER, DELIMITER ',');

DuckDBのgetvariable('top_n')で外から渡された変数を参照しています。Taskfile側でSET VARIABLEしてからSQLファイルを読み込む仕組みです。

Taskfile.ymlの構成

Taskfile.ymlではSQLを直接書かず、すべてduckdb < sql/xxx.sqlでSQLファイルにリダイレクトしています。analyzeタスクだけはCLI引数（top_n）を渡すためにSET VARIABLEをパイプで先頭に挟んでいます。

version: '3'

vars:
  PARQUET_FILE: 'data/shakespeare.parquet'
  BQ_META_FILE: '.cache/bq_meta'

tasks:
  check-bq-freshness:
    desc: BQテーブルの最終更新時刻を取得
    cmds:
      - mkdir -p .cache
      - >-
        bq show --format=json 'bigquery-public-data:samples.shakespeare'
        | jq -r '.lastModifiedTime'
        > {{.BQ_META_FILE}}

  fetch-bq:
    desc: BQからデータ取得 → Parquetキャッシュ（変更時のみ再取得）
    deps: [check-bq-freshness]
    sources:
      - '{{.BQ_META_FILE}}'
    generates:
      - '{{.PARQUET_FILE}}'
    cmds:
      - mkdir -p data
      - duckdb < sql/01_fetch_from_bq.sql

  fetch-gss:
    desc: GSSからデータ取得（毎回最新を取得）
    vars:
      GSS_TOKEN:
        sh: gcloud auth print-access-token
    env:
      GSS_TOKEN: '{{.GSS_TOKEN}}'
    cmds:
      - duckdb < sql/02_fetch_from_gss.sql

  analyze:
    desc: "ジャンル別の頻出単語を集計（usage: task analyze -- 5）"
    deps: [fetch-bq]
    sources:
      - '{{.PARQUET_FILE}}'
      - sql/10_analyze.sql
    generates:
      - output/analysis_result.parquet
      - output/analysis_result.csv
    vars:
      TOP_N: '{{.CLI_ARGS | default "10"}}'
      GSS_TOKEN:
        sh: gcloud auth print-access-token
    env:
      GSS_TOKEN: '{{.GSS_TOKEN}}'
    cmds:
      - mkdir -p output
      - bash -c "echo \"SET VARIABLE top_n = '{{.TOP_N}}';\" | cat - sql/10_analyze.sql | duckdb"

  upload:
    desc: 分析結果をGSSに書き出し
    deps: [analyze]
    sources:
      - output/analysis_result.parquet
    vars:
      GSS_TOKEN:
        sh: gcloud auth print-access-token
    env:
      GSS_TOKEN: '{{.GSS_TOKEN}}'
    cmds:
      - duckdb < sql/20_upload_to_gss.sql

  status:
    desc: キャッシュ状態を確認
    cmds:
      - |
        if [ -f "{{.PARQUET_FILE}}" ]; then
          echo "Parquet: {{.PARQUET_FILE}}"
          ls -lh "{{.PARQUET_FILE}}"
          duckdb -c "SELECT COUNT(*) AS row_count FROM '{{.PARQUET_FILE}}';"
        else
          echo "Parquet: キャッシュなし"
        fi

  clean:
    desc: キャッシュクリア（全体）
    cmds:
      - rm -rf data/* .cache/* output/* .task/checksum/*

  clean-bq:
    desc: BQキャッシュのみクリア
    cmds:
      - rm -f '{{.PARQUET_FILE}}' '{{.BQ_META_FILE}}'
      - rm -f .task/checksum/fetch-bq .task/checksum/check-bq-freshness

  all:
    desc: 全ワークフロー実行（fetch → analyze → upload）
    cmds:
      - task: fetch-bq
      - task: analyze
      - task: upload

ポイントはfetch-bqのsourcesとgeneratesです。sourcesにBQのメタファイル（最終更新時刻）、generatesにParquetファイルを指定しています。Taskfileはsourcesのチェックサムが変わっていなければタスクをスキップするので、BQテーブルが更新されない限り再取得は走りません。

使ってみるとこうなります。

# 何ができるか確認
$ task --list

# BQからデータ取得（初回のみスキャン発生）
$ task fetch-bq

# 2回目はキャッシュヒットでスキップ
$ task fetch-bq
# => task: Task "fetch-bq" is up to date

# ジャンル別の頻出単語top3を集計
$ task analyze -- 3

# 結果をスプレッドシートに書き出し
$ task upload

task analyze -- 3でtop3、task analyze -- 10でtop10、引数なしならデフォルトでtop10になります。

実際にtop3で実行し、task uploadまで通すと、無事結果がスプレッドシートのoutputシートに出力されることを確認できました。

genre word total_count rank
comedy I 2807 1
comedy the 2798 2
comedy and 2161 3
history the 3067 1
history and 2485 2
history I 2323 3
tragedy the 3676 1
tragedy I 2898 2
tragedy and 2801 3

キャッシュ戦略をTaskfileで実装する

キャッシュ戦略はデータソースの性質に合わせて変えています。

BQのデータはサイズが大きく、スキャンのたびに課金されるのでキャッシュが必要です。check-bq-freshnessタスクでbq showからlastModifiedTimeを取得してメタファイルに保存し、fetch-bqのsourcesに指定しています。BQテーブルが更新されるとメタファイルの中身が変わり、チェックサムが変わるので再取得が走ります。更新がなければスキップです。bq showはメタデータの参照だけなのでスキャン料金はかかりません。

強制的にキャッシュを消したいときはtask clean-bqを使います。Parquetとメタファイルに加えて、.task/checksum/配下のチェックサムファイルも消す必要があります。これを忘れるとTaskfileが「up to date」と判定してしまうので注意してください。

一方、GSSのデータはキャッシュしていません。スプレッドシートは人間が随時編集するもので、データ量も小さいです。毎回最新を取得するほうが自然なので、fetch-gssにはsources/generatesを設定していません。

キャッシュの状態はtask statusで確認できます。Parquetファイルの有無や行数、BQの最終更新時刻をまとめて表示します。

まとめ

BQからデータを取得してParquetにキャッシュし、DuckDBでGSSとクロスソースJOINして、結果をスプレッドシートに書き戻す。この一連のフローをTaskfile.ymlとSQLファイルだけで構成できます。

Parquetにキャッシュすれば2回目以降のBQ課金はゼロで、レイテンシも桁違いに速くなります。Taskfileのsources/generatesでキャッシュの有効性を自動判定するので、うっかりBQを余計に叩いてしまうことも防げます。

dbtやAirflowのような仕組みが必要になるのは、分析パイプラインをチームで共有したり本番にデプロイしたりする段階です。「ちょっとBQのデータを手元で探索したい」くらいの場面では、Taskfile + DuckDBで十分に回ると思いました。SQLだけでロジックが完結するので、集計条件の共有もシンプルです。スプレッドシートのセルにSQLを貼っておけば「このデータはこういう条件で抽出しています」が一目でわかります。

Pythonでのデータ加工やML前処理が中心なら、同チームの高橋さんが書いたAgentic Coding時代のデータ分析環境も参考になります。marimoとgokartを組み合わせたアプローチでPythonでゴリゴリ分析したい場合のおすすめの環境構成が紹介されています。

We are hiring!

エムスリーではBigQueryやDuckDBを使ったデータ分析基盤の開発に取り組んでいます。「分析環境をもっと良くしたい」と思っているエンジニアの方、ぜひカジュアル面談でお話ししましょう！

エンジニア採用ページはこちら

jobs.m3.com

カジュアル面談もお気軽にどうぞ

jobs.m3.com

インターンも常時募集しています

open.talentio.com