最近、業務上でデータがGoogle Sheets（Google スプレッドシート）上にあり、複数のブック間で行の差分を確認したいということがありました。
私はローカルでCSVの行の差分をとるときにDuckDB（CLI）を使ってみた経験があったので、同じような形式のデータならばGoogle Sheetsの比較もDuckDBでできないか？とGSheets拡張機能を試してみたところ想像以上に良かったので、紹介します。
DuckDB GSheets拡張機能とは
DuckDBから直接Google Sheets上のデータを読み込むことを可能にするコミュニティ拡張機能です。
（ご存じない方に補足すると、コミュニティ拡張機能というのはDuckDB公式が開発しているわけではない拡張機能で、配布はDuckDB公式経由ですが開発はコミュニティによって行われているものになります）
コミュニティ拡張機能ではあるもののDuckDB公式サイトのブログで紹介されたこともあり、注目度は高いと思われます。
特筆すべき点として、ブラウザ上で一時トークンを生成する機能があり、インタラクティブに使う場合はローカルに認証情報などを保存することなく使えます！
上記のブログ記事にある以下の例をDuckDB CLIで1行ずつ実行すると、動作を確認できます。
INSTALL gsheets FROM community;
LOAD gsheets;
CREATE OR REPLACE SECRET (TYPE gsheet);
FROM 'https://docs.google.com/spreadsheets/d/1B4RFuOnZ4ITZ-nR9givZ7vWVOTVddC3VTKuSqgifiyE/edit?gid=0#gid=0';
DuckDB UIと一緒に使う方法
UI拡張機能が便利なので、私はDuckDB CLIの-uiオプションで呼び出してよく使っています。
$ duckdb -ui
┌──────────────────────────────────────┐
│ result │
│ varchar │
├──────────────────────────────────────┤
│ UI started at http://localhost:4213/ │
└──────────────────────────────────────┘
DuckDB v1.4.1 (Andium) b390a7c376
Enter ".help" for usage hints.
D
ただ、こちらのUI拡張機能によって提供されるノートブック上では、前述のGSheets拡張機能のインタラクティブなトークン入力機能は上手く動作しません。
なのでノートブック上ではなく、ターミナル側から以下のようにCREATE SECRETコマンド（もしくはCREATE OR REPLACE SECRETコマンド）を入力して認証を完了する必要があります。
load gsheets; CREATE OR REPLACE SECRET (TYPE gsheet);
コマンド実行はUIサーバー起動後で大丈夫です。
ターミナル上で認証を完了したら、あとはノートブック上からもGoogle Sheetsにアクセスできるようになっています。
追記：トークンの有効期限が30分しかないため、30分以上作業する場合はCREATE OR REPLACE SECRETコマンドでもう一度トークンを入力する必要があります。
2つのブック上の同一名シート間の比較を行うクエリ
最終的に実行するクエリは以下のようなものです。新旧2つのテーブルを読み込んで、新側にしかない行と旧側にしかない行をそれぞれ取得し、最後に統合して1つのテーブルにして返す感じです。
read_gsheet関数を使用することで、Google Sheets上のデータを直接読み込めます。
set variable sheet_name = '<読み込み先シート名>';
set variable orig_url = '<ブックその1のURL>';
set variable new_url = '<ブックその2のURL>';
load gsheets;
with
_orig as (
from
read_gsheet (getvariable ('orig_url'), all_varchar = true, sheet = getvariable ('sheet_name'))
select
*
-- exclude ("除外したい列名1") -- 比較対象外の列のある場合にはコメント解除して使用
),
_new as (
from
read_gsheet (getvariable ('new_url'), all_varchar = true, sheet = getvariable ('sheet_name'))
select
*
-- exclude ("除外したい列名2", "除外したい列名3") -- 比較対象外の列のある場合にはコメント解除して使用
),
_a as (
from
_orig
except
from
_new
),
_b as (
from
_new
except
from
_orig
)
select
*,
_where: 'only_on_orig',
from
_a
union all
select
*,
_where: 'only_on_new',
from
_b
order by all
Friendly SQL
上記のクエリ内ではせっかくDuckDBを使うということで、「Friendly SQL」とされている、DuckDB独自の記法をいくつか使用しています。
SET VARIABLE
SET VARIABLEを使うと、クエリ内で変数を定義でき、getvariable関数を使うことでその変数を参照できます。
同じ値を複数箇所で使いたい場合に便利なのと、今回の場合のURLのように、文字列の定義場所を分かりやすくできます。
FROM - first syntax
普通のSQLではSELECT句が最初に来ますが、DuckDBではその前にFROM句を持ってきても良く、SELECTを完全に省略することも可能です。
以下の三つは同等になります。
-
SELECT * FROM foo;: 一般的なSQLの記法。 -
FROM foo SELECT *;:FROM句を最初に置く記法。 -
FROM foo;:SELECT句を省略した記法。
SELECT * EXCLUDE
普通のSQLでは「全ての列からfooだけ除外した列を選択」というようなことをしたければSELECT句でfoo以外の全ての列を列挙しなければなりませんが、DuckDBではSELECT * EXCLUDE (foo)と書けます。
列数の多いテーブルから特定の数列のみを外して読み込みたい場合に便利です。
今回の場合、比較対象外の列をそれぞれのシートに持っている場合もあるので、普段はコメントにしておき必要になったらコメントを外して使えるように残してあります。
Prefix aliases
SELECT foo AS barをSELECT bar: fooと書ける機能です。好み分かれそうですけれど、私は好きなのでよく使います。
ORDER BY ALL
すべての列を並び替えます。並べ替え対象のテーブルの中身を考慮する必要がなくて便利です。
まとめ
DuckDB GSheets拡張機能の紹介と、これを使うとGoogleスプレッドシート上のデータを確認するのも簡単になるかも、という気付きの共有でした。
CREATE SECRET (TYPE gsheet)の動作は衝撃的で、DuckDB拡張機能の可能性を改めて感じています！
Discussion