概要
複数キーワードであいまい検索を行いたいが、SQLを書き換えるのは面倒なのでGoogleスプレッドシートをWHERE句のパラメータ代わりに使用する。
下記"temp.daikusyohin"テーブルから"釘"と"ナット"を含む文字を検索したい場合、
| syohin_code | syohin_name |
|---|---|
| 1 | 丸釘 |
| 2 | スクリュー釘 |
| 3 | ナベ小ねじ |
| 4 | 皿小ねじ |
| 5 | 六角ナット |
| 6 | 六角ボルト |
通常は下記SQLのように条件指定するが、"釘","ナット"の部分を自由に変更したい場合、毎回SQLを書き換えるのはしんどい＋非エンジニアはSQL書けないので、スプレッドシートに釘、ナットと書けば自動で抽出されるような仕組みを考える。
#standardSQL
SELECT syohin_code, syohin_name from temp.daikusyohin
WHERE
syohin_name like '%釘%' OR syohin_name like '%ナット%'
ORDER BY 1
手順
1.GCP設定
2.スプレッドシート作成
3.外部テーブル作成
4.SQL実行
1.GCP設定
1.1 Google ドライブ API を有効にする。
1.2 Google ドライブで認証する。
gcloud auth login --enable-gdrive-access
2.スプレッドシート作成
下記のような条件を入力するスプレッドシートを作成
3.外部テーブル作成
bqコマンドを実行して外部テーブルを作成
bq mk --external_table_definition=syohin_name:string@GOOGLE_SHEETS=[2.で作成したスプレッドシートのURL] temp.syohinjoken
4.SQL実行
#standardSQL
SELECT
A.syohin_code
,A.syohin_name
FROM
temp.daikusyohin A
INNER JOIN
temp.syohinjoken B
ON
A.syohin_name LIKE CONCAT('%',B.syohin_name,'%')
ORDER BY
1
釘とナットのみが表示される事を確認
スプレッドシートの値を変更し、
同じSQLを実行
今度は"ナベ"と"六角"を含むデータのみ抽出された事を確認
まとめ
上記SQLをビュー化し、BIツールからアクセスすれば、ユーザーはSQLを意識する事なく、スプレッドシートの値を変更する事で自由に条件を設定する事が可能。
BigQueryの外部テーブル機能は使える！