Open8
DuckDBでGoogle Sheetsのデータをクエリしてみる
FKeisuke公開されているスプレッドシートへのクエリは特に難しくなさそう
参考: https://www.arecadata.com/sql-for-google-sheets-with-duckdb/
SELECT *
FROM read_csv_auto('https://docs.google.com/spreadsheets/export?format=csv&id=1GuEPkwjdICgJ31Ji3iUoarirZNDbPxQj_kf7fd4h4Ro', normalize_names=True);
おそらくexport?format=csvの部分がミソ
これを行った上でread_csvで読み込む
FKeisuke非公開なスプレッドシートへのクエリは一手間加える必要がありそう
参考となるのは（いつ追加された機能かは不明だが）HTTPヘッダーをSecret Managerに保管できる機能
FKeisukeGoogle Cloudアカウント側での準備
- サービスアカウントを作成し、認証情報のjsonファイルを出力しておく
- 一旦人間のアドホックな分析ではなく、定期的なETL(ELT)ワークフロー内での実行を想定したためサービスアカウントを利用
- APIライブラリから「Google Sheets API」を有効化しておく
- 参照したいスプレッドシート側でサービスアカウントのメールアドレスに対して共有設定を行っておく
FKeisuke実行
Python(rye)で実行するものとする
依存
"duckdb>=1.1.1",
"google-auth>=2.35.0",
"google-auth-oauthlib>=1.2.1",
"google-auth-httplib2>=0.2.0",
"google-api-python-client>=2.147.0",
スクリプト
import sys
from argparse import ArgumentParser
from pathlib import Path
from typing import NamedTuple
import duckdb
from google.auth.transport.requests import Request
from google.oauth2.service_account import Credentials
ScriptArgs = NamedTuple("ScriptArgs", [("secret_json_path", Path), ("sheet_id", str)])
def valid_path(path_str: str) -> Path:
if (p := Path(path_str).resolve()) and p.exists() and p.is_file():
return p
raise ValueError(f"`{path_str}` is invalid")
def parse_args(argv: list[str]) -> ScriptArgs:
parser = ArgumentParser()
parser.add_argument("--secret-json-path", type=valid_path, required=True)
parser.add_argument("--sheet-id", type=str, required=True)
args = parser.parse_args(argv)
return ScriptArgs(**vars(args))
SCOPES = [
"https://www.googleapis.com/auth/spreadsheets.readonly",
"https://www.googleapis.com/auth/drive.readonly",
]
def get_access_token(secret_json_path: Path) -> str | None:
try:
# サービスアカウントの認証情報を読み込む
credentials = Credentials.from_service_account_file(
secret_json_path, scopes=SCOPES
)
# 認証情報が有効でない場合は更新
if not credentials.valid:
credentials.refresh(Request())
# アクセストークンを取得
access_token = credentials.token
print("Succeeded to get access token")
return access_token
except Exception as e:
print(e)
return None
def add_secret(access_token: str) -> bool:
try:
duckdb.sql(f"""
CREATE OR REPLACE SECRET http (
TYPE HTTP,
EXTRA_HTTP_HEADERS MAP {{
'Authorization': 'Bearer {access_token}'
}}
);
""")
print("Succeeded to add secret")
return True
except Exception as e:
print(e)
return False
def query_spreadsheet(sheet_id: str) -> duckdb.DuckDBPyRelation:
try:
url = f"https://docs.google.com/spreadsheets/export?format=csv&id={sheet_id}"
res = duckdb.sql(f"""
SELECT
*
FROM read_csv_auto('{url}');
""")
return res
except Exception as e:
print(e)
return None
def main(args: ScriptArgs) -> int:
if not (access_token := get_access_token(args.secret_json_path)):
print("Failed to get access token")
return 1
if not (_ := add_secret(access_token)):
print("Failed to add secret")
return 1
if not (result := query_spreadsheet(args.sheet_id)):
print("Failed to fetch spread sheet data")
return 1
print(result)
return 0
if __name__ == "__main__":
args = parse_args(sys.argv[1:])
sys.exit(main(args))
実行結果
- ※
--secret-json-pathはサービスアカウント作成時に出力した認証情報 - ※
--sheet-idは参照したいスプレッドシートのID。URLhttps://docs.google.com/spreadsheets/d/{{ SPREAD_SHEET_ID }}/edit?gid=9999999999#gid=9999999999のSPREAD_SHEET_ID部分
$ rye run python -m src.test_duckdb_gsheets.main --secret-json-path=./secret/hoge.json --sheet-id=fuga
Succeeded to get access token
Succeeded to add secret
https://docs.google.com/spreadsheets/export?format=csv&id=fuga
┌──────────┬────────────────┬────────────────┬──────────┬──────────┬─────────┬──────────┬───┬──────────────────┬──────────────────┬──────────────────────┬──────────────────────┬──────────────────────┬──────────────────────┐
│ 資料区分 │ 都道府県コード │ 警察署等コード │ 本票番号 │ 事故内容 │ 死者数 │ 負傷者数 │ … │ 曜日(発生年月日) │ 祝日(発生年月日) │ 認知機能検査経過日… │ 認知機能検査経過日… │ 運転練習の方法（当… │ 運転練習の方法（当… │
│ int64 │ int64 │ varchar │ varchar │ int64 │ varchar │ varchar │ │ int64 │ int64 │ varchar │ varchar │ int64 │ int64 │
├──────────┼────────────────┼────────────────┼──────────┼──────────┼─────────┼──────────┼───┼──────────────────┼──────────────────┼──────────────────────┼──────────────────────┼──────────────────────┼──────────────────────┤
│ 1 │ 10 │ 059 │ 0001 │ 2 │ 000 │ 001 │ … │ 7 │ 3 │ 9999 │ 0000 │ 5 │ 0 │
│ 1 │ 10 │ 059 │ 0002 │ 2 │ 000 │ 001 │ … │ 6 │ 3 │ 9999 │ 0000 │ 1 │ 0 │
│ 1 │ 10 │ 059 │ 0003 │ 2 │ 000 │ 001 │ … │ 2 │ 3 │ 9999 │ 9999 │ 1 │ 1 │
│ 1 │ 10 │ 059 │ 0004 │ 2 │ 000 │ 001 │ … │ 7 │ 3 │ 9999 │ 9999 │ 1 │ 9 │
│ 1 │ 10 │ 101 │ 0001 │ 2 │ 000 │ 001 │ … │ 7 │ 2 │ 9999 │ 9999 │ 1 │ 1 │
│ 1 │ 10 │ 101 │ 0002 │ 2 │ 000 │ 001 │ … │ 6 │ 3 │ 9999 │ 0000 │ 9 │ 0 │
│ 1 │ 10 │ 101 │ 0003 │ 2 │ 000 │ 001 │ … │ 4 │ 3 │ 9999 │ 9999 │ 1 │ 9 │
│ 1 │ 10 │ 101 │ 0004 │ 2 │ 000 │ 001 │ … │ 6 │ 3 │ 9999 │ 0000 │ 9 │ 0 │
│ 1 │ 10 │ 101 │ 0005 │ 2 │ 000 │ 001 │ … │ 6 │ 3 │ 9999 │ 9999 │ 1 │ 9 │
│ 1 │ 10 │ 101 │ 0006 │ 2 │ 000 │ 001 │ … │ 5 │ 3 │ 9999 │ 9999 │ 9 │ 9 │
│ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │
│ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │
│ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │
│ 1 │ 10 │ 103 │ 0005 │ 2 │ 000 │ 001 │ … │ 7 │ 3 │ 9999 │ 9999 │ 1 │ 1 │
│ 1 │ 10 │ 103 │ 0006 │ 2 │ 000 │ 001 │ … │ 6 │ 3 │ 9999 │ 9999 │ 9 │ 1 │
│ 1 │ 10 │ 103 │ 0007 │ 2 │ 000 │ 001 │ … │ 4 │ 3 │ 9999 │ 9999 │ 9 │ 1 │
│ 1 │ 10 │ 103 │ 0008 │ 2 │ 000 │ 001 │ … │ 2 │ 1 │ 9999 │ 9999 │ 9 │ 1 │
│ 1 │ 10 │ 103 │ 0009 │ 2 │ 000 │ 001 │ … │ 3 │ 3 │ 9999 │ 0000 │ 9 │ 0 │
│ 1 │ 10 │ 103 │ 0010 │ 2 │ 000 │ 001 │ … │ 4 │ 3 │ 9999 │ 9999 │ 1 │ 1 │
│ 1 │ 10 │ 103 │ 0011 │ 2 │ 000 │ 002 │ … │ 3 │ 2 │ 9999 │ 9999 │ 1 │ 9 │
│ 1 │ 10 │ 103 │ 0012 │ 2 │ 000 │ 001 │ … │ 4 │ 3 │ 0189 │ 9999 │ 9 │ 1 │
│ 1 │ 10 │ 103 │ 0013 │ 2 │ 000 │ 001 │ … │ 2 │ 3 │ 9999 │ 9999 │ 9 │ 1 │
│ 1 │ 10 │ 103 │ 0014 │ 2 │ 000 │ 001 │ … │ 1 │ 2 │ 9999 │ 9999 │ 1 │ 1 │
├──────────┴────────────────┴────────────────┴──────────┴──────────┴─────────┴──────────┴───┴──────────────────┴──────────────────┴──────────────────────┴──────────────────────┴──────────────────────┴──────────────────────┤
│ 100 rows (20 shown) 68 columns (13 shown) │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
DuckDB CLIからの実行
Google Cloudがよくわからん状態なので間違えてる部分があるかもしれない…
雑に試すとしたらこちらでアクセストークンを払い出して
pythonでなくduckdbのcliから実行することもできた
$ duckdb
v1.1.1 af39bd0dcf
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
D CREATE OR REPLACE SECRET http (
TYPE HTTP,
EXTRA_HTTP_HEADERS MAP {
'Authorization': 'Bearer {{ ACCESS_TOKEN }}'
}
);
┌─────────┐
│ Success │
│ boolean │
├─────────┤
│ true │
└─────────┘
※ {{ ACCESS_TOKEN }}部分を取得したトークンに変更してSecretを作成
D SELECT *
FROM read_csv('https://docs.google.com/spreadsheets/export?format=csv&id={{ SPREAD_SHEET_ID }}');
┌──────────┬────────────────┬────────────────┬──────────┬──────────┬─────────┬──────────┬───┬──────────────────┬──────────────────┬──────────────────────┬──────────────────────┬──────────────────────┬──────────────────────┐
│ 資料区分 │ 都道府県コード │ 警察署等コード │ 本票番号 │ 事故内容 │ 死者数 │ 負傷者数 │ … │ 曜日(発生年月日) │ 祝日(発生年月日) │ 認知機能検査経過日… │ 認知機能検査経過日… │ 運転練習の方法（当… │ 運転練習の方法（当… │
│ int64 │ int64 │ varchar │ varchar │ int64 │ varchar │ varchar │ │ int64 │ int64 │ varchar │ varchar │ int64 │ int64 │
├──────────┼────────────────┼────────────────┼──────────┼──────────┼─────────┼──────────┼───┼──────────────────┼──────────────────┼──────────────────────┼──────────────────────┼──────────────────────┼──────────────────────┤
│ 1 │ 10 │ 059 │ 0001 │ 2 │ 000 │ 001 │ … │ 7 │ 3 │ 9999 │ 0000 │ 5 │ 0 │
│ 1 │ 10 │ 059 │ 0002 │ 2 │ 000 │ 001 │ … │ 6 │ 3 │ 9999 │ 0000 │ 1 │ 0 │
│ 1 │ 10 │ 059 │ 0003 │ 2 │ 000 │ 001 │ … │ 2 │ 3 │ 9999 │ 9999 │ 1 │ 1 │
│ 1 │ 10 │ 059 │ 0004 │ 2 │ 000 │ 001 │ … │ 7 │ 3 │ 9999 │ 9999 │ 1 │ 9 │
│ 1 │ 10 │ 101 │ 0001 │ 2 │ 000 │ 001 │ … │ 7 │ 2 │ 9999 │ 9999 │ 1 │ 1 │
│ 1 │ 10 │ 101 │ 0002 │ 2 │ 000 │ 001 │ … │ 6 │ 3 │ 9999 │ 0000 │ 9 │ 0 │
│ 1 │ 10 │ 101 │ 0003 │ 2 │ 000 │ 001 │ … │ 4 │ 3 │ 9999 │ 9999 │ 1 │ 9 │
│ 1 │ 10 │ 101 │ 0004 │ 2 │ 000 │ 001 │ … │ 6 │ 3 │ 9999 │ 0000 │ 9 │ 0 │
│ 1 │ 10 │ 101 │ 0005 │ 2 │ 000 │ 001 │ … │ 6 │ 3 │ 9999 │ 9999 │ 1 │ 9 │
│ 1 │ 10 │ 101 │ 0006 │ 2 │ 000 │ 001 │ … │ 5 │ 3 │ 9999 │ 9999 │ 9 │ 9 │
│ 1 │ 10 │ 101 │ 0007 │ 2 │ 000 │ 001 │ … │ 5 │ 3 │ 9999 │ 9999 │ 9 │ 1 │
│ 1 │ 10 │ 101 │ 0008 │ 2 │ 000 │ 001 │ … │ 3 │ 3 │ 9999 │ 9999 │ 9 │ 1 │
│ 1 │ 10 │ 101 │ 0009 │ 2 │ 000 │ 001 │ … │ 5 │ 3 │ 9999 │ 0000 │ 9 │ 0 │
│ 1 │ 10 │ 101 │ 0010 │ 2 │ 000 │ 001 │ … │ 7 │ 3 │ 9999 │ 9999 │ 5 │ 1 │
│ 1 │ 10 │ 101 │ 0011 │ 2 │ 000 │ 001 │ … │ 2 │ 3 │ 9999 │ 0000 │ 9 │ 0 │
│ 1 │ 10 │ 101 │ 0012 │ 2 │ 000 │ 002 │ … │ 1 │ 3 │ 9999 │ 9999 │ 9 │ 1 │
│ 1 │ 10 │ 101 │ 0013 │ 2 │ 000 │ 001 │ … │ 1 │ 2 │ 9999 │ 9999 │ 9 │ 9 │
│ 1 │ 10 │ 101 │ 0014 │ 2 │ 000 │ 001 │ … │ 3 │ 3 │ 9999 │ 9999 │ 5 │ 9 │
│ 1 │ 10 │ 101 │ 0015 │ 2 │ 000 │ 001 │ … │ 5 │ 3 │ 9999 │ 9999 │ 9 │ 9 │
│ 1 │ 10 │ 101 │ 0016 │ 2 │ 000 │ 001 │ … │ 2 │ 3 │ 9999 │ 9999 │ 9 │ 1 │
│ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │
│ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │
│ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │
│ 1 │ 10 │ 102 │ 0037 │ 2 │ 000 │ 003 │ … │ 4 │ 3 │ 9999 │ 9999 │ 1 │ 1 │
│ 1 │ 10 │ 102 │ 0038 │ 2 │ 000 │ 001 │ … │ 4 │ 3 │ 9999 │ 9999 │ 1 │ 9 │
│ 1 │ 10 │ 102 │ 0039 │ 2 │ 000 │ 001 │ … │ 5 │ 3 │ 9999 │ 0000 │ 1 │ 0 │
│ 1 │ 10 │ 102 │ 0040 │ 2 │ 000 │ 001 │ … │ 2 │ 3 │ 0797 │ 9999 │ 9 │ 1 │
│ 1 │ 10 │ 102 │ 0041 │ 2 │ 000 │ 001 │ … │ 3 │ 3 │ 9999 │ 9999 │ 9 │ 1 │
│ 1 │ 10 │ 102 │ 0042 │ 2 │ 000 │ 001 │ … │ 6 │ 3 │ 9999 │ 9999 │ 9 │ 5 │
│ 1 │ 10 │ 103 │ 0001 │ 2 │ 000 │ 001 │ … │ 3 │ 3 │ 9999 │ 9999 │ 9 │ 9 │
│ 1 │ 10 │ 103 │ 0002 │ 2 │ 000 │ 001 │ … │ 1 │ 3 │ 9999 │ 0000 │ 9 │ 0 │
│ 1 │ 10 │ 103 │ 0003 │ 2 │ 000 │ 001 │ … │ 2 │ 3 │ 9999 │ 9999 │ 1 │ 1 │
│ 1 │ 10 │ 103 │ 0004 │ 2 │ 000 │ 001 │ … │ 2 │ 3 │ 9999 │ 9999 │ 1 │ 9 │
│ 1 │ 10 │ 103 │ 0005 │ 2 │ 000 │ 001 │ … │ 7 │ 3 │ 9999 │ 9999 │ 1 │ 1 │
│ 1 │ 10 │ 103 │ 0006 │ 2 │ 000 │ 001 │ … │ 6 │ 3 │ 9999 │ 9999 │ 9 │ 1 │
│ 1 │ 10 │ 103 │ 0007 │ 2 │ 000 │ 001 │ … │ 4 │ 3 │ 9999 │ 9999 │ 9 │ 1 │
│ 1 │ 10 │ 103 │ 0008 │ 2 │ 000 │ 001 │ … │ 2 │ 1 │ 9999 │ 9999 │ 9 │ 1 │
│ 1 │ 10 │ 103 │ 0009 │ 2 │ 000 │ 001 │ … │ 3 │ 3 │ 9999 │ 0000 │ 9 │ 0 │
│ 1 │ 10 │ 103 │ 0010 │ 2 │ 000 │ 001 │ … │ 4 │ 3 │ 9999 │ 9999 │ 1 │ 1 │
│ 1 │ 10 │ 103 │ 0011 │ 2 │ 000 │ 002 │ … │ 3 │ 2 │ 9999 │ 9999 │ 1 │ 9 │
│ 1 │ 10 │ 103 │ 0012 │ 2 │ 000 │ 001 │ … │ 4 │ 3 │ 0189 │ 9999 │ 9 │ 1 │
│ 1 │ 10 │ 103 │ 0013 │ 2 │ 000 │ 001 │ … │ 2 │ 3 │ 9999 │ 9999 │ 9 │ 1 │
│ 1 │ 10 │ 103 │ 0014 │ 2 │ 000 │ 001 │ … │ 1 │ 2 │ 9999 │ 9999 │ 1 │ 1 │
├──────────┴────────────────┴────────────────┴──────────┴──────────┴─────────┴──────────┴───┴──────────────────┴──────────────────┴──────────────────────┴──────────────────────┴──────────────────────┴──────────────────────┤
│ 100 rows (40 shown) 68 columns (13 shown) │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
※ {{ SPREAD_SHEET_ID }}部分は参照したいスプレッドシートのIDに変更してクエリを実行
FKeisukeextensionを開発中とのこと
トークンとsheet idを指定して読み込むという感じに見受けられるのでAuthorizationヘッダーをSecrets Managerに登録して読み込む方法と原理的には似た感じか
FKeisuke書き込みもできるようになるかも