BigQuery経由で使うGoogleスプレッドシートのスキーマ管理

2022-07-21 Looker User Meetup Online #8 の発表資料です。
https://looker-japan-user-group.connpass.com/event/253923/
https://developer.feedforce.jp/entry/2022/07/21/210000

Transcript

1. BigQuery経由で使う Googleスプレッドシート のスキーマ管理 2022-07-21 Looker User Meetup Online #8 株式会社フィードフォース 増田貴士
2. 自己紹介 • 増田貴士（@masutaka） • https://masutaka.net/ • 株式会社フィードフォースのボッチデータ整備人 ◦ ウェブ広告やShopify関連で、LookML開発やAdmin権限での運用に携わっている • 過去に組み込みソフトウェア開発や、ウェブのバックエ ンド＆インフラの開発や運用の経験もあり
3. 今回の概要 • BigQueryからGoogleスプレッドシートを参照できるよ • 制約はあるけど、ビジネスユーザーが簡単にデータを更 新できるのはメリットだよ • フィードフォースで使っている、Googleスプレッドシー トのゆるふわスキーマ管理方法を紹介するよ ◦ ※ スキーマとはカラム（列）の名前や型の定義のことです
4. BigQueryのデータソース
5. BigQueryのデータソース 種別 メカニズム データソース/データ ベース 形式 BigQueryストレージ 外部データソース 外部テーブル Bigtable Cloud Storage (GCS) CSV, JSON など Googleドライブ カンマ区切り値（CSV） JSON（改行区切り） Avro スプレッドシート 連携クエリ Cloud Spanner Cloud SQL 外部データソースの概要 | BigQuery | Google Cloud
6. データソースを意識せずにクエリ実行可能 BigQuery BigQuery ストレージ Google スプレッドシート 外部データソー ス クエリ実行 参照のみ 参照/更新
7. BigQuery経由で使った場合の比較 BigQueryストレージ Googleスプレッドシート ビジネスユー ザーがデータを 更新可能 難しい 簡単 ビジネスユー ザーがスキーマ を変更可能 難しい 例えば列を挿入すると、クエ リエラーになる 扱えるデータ量 気にしなくて良い 上限1,000万セルだが、実際 は数万行でクエリタイムアウ トなどが発生し始める Google ドライブに保管可能なファイル - Google ドライブ ヘルプ
8. 外部データソースにGoogleスプレッドシートを使う場合の、 一般的な作成方法と、今回紹介する作成方法を比較していき ます。
9. 一般的な作成方法
10. BigQueryコンソールから作成する ドライブデータをクエリする | BigQuery | Google Cloud
11. Googleスプレッドシートに閲覧権限を与える（続く）
12. 与えるユーザーは当該ConnectionのService Account Email
13. SQLは通常のBigQueryと変わらない SELECT * FROM `<project_name>.<dataset_name>.<table_name>`
14. 課題
15. 課題 • スキーマの変更履歴をGitに記録できない ◦ なぜ変更したのか？をあとから追跡しづらい • スキーマを破壊的に変更しづらい ◦ 一度作ったテーブルのカラムは変更や削除が出来ない。追加なら出来る ◦ 例えばあるカラムを削除したくなった時は、変更したいテーブルと同じスキーマのテーブル を作りつつ、削除したいカラムは含めないようにする ◦ その後、LookMLからのテーブル参照を切り替える
16. 今回紹介する作成方法
17. bq CLIを使う 1. データセットの作成 ◦ $ bq mk <dataset_name> ◦ 例: $ bq mk masterdata 2. 外部テーブルの作成 ◦ $ bq mk --external_table_definition=./define.json <dataset_name>.<table_name> ◦ 例: $ bq mk --external_table_definition=./define.json masterdata.gsheet
18. ポイント • bq CLIを使う ◦ ローカルにCloud SDKをインストールすれば使える ▪ Cloud SDK のインストール | Google Cloud ◦ macOSなら $ brew install --cask google-cloud-sdk でのインストールが楽かも • スキーマ定義をdefine.jsonで管理する ◦ 例: https://gist.github.com/masutaka/142ca20b802401d12012fe952f2ea1f3
19. LookMLと同じリポジトリで管理する bigquery/ ├ dataset_name/ │├ README.md │├ define.json │└ schema.json └ … explores/ layers/ …
20. 課題を解決できた • スキーマの変更をGitで記録できない件 ◦ LookMLと同じリポジトリで管理することが出来た ◦ スキーマ変更に伴うLookMLの変更を、1つのPull requestで表現できるようになった • スキーマを破壊的に変更しづらい件 ◦ define.jsonから同じスキーマのテーブルを量産出来るので、簡単になった（次ページ参照）
21. スキーマの破壊的な変更例 1. スプレッドシートの複製を作る 2. ↑を参照する、ゆるふわなバージョンを付けたデータセットとテーブルを新 たに作る。例: masterdata_v2.gsheet 3. LookML側でテーブル参照を切り替える 4. 良さそうなら、スプレッドシートの参照を切り替えてデプロイする 5. 古いテーブルはデータセットごと削除する ◦ 例: $ bq rm -r masterdata
22. 落ち穂拾い
23. ベストプラクティス • 1行目ヘッダ、2行目以降データのシンプルな構造を保つ ◦ 人間ではなく、機械にやさしい構造を意識する ◦ とにかく軽く、シンプルさを保つ。個人的には、出来るだけ色も付けない • IMPORTRANGEのような、他シートを参照する関数はで きるだけ使わない ◦ 使いすぎるとクエリタイムアウトの確率が上がる。特に多段IMPORTRANGE
24. 雑多なこと • クエリエラーが発生するほどデータ量が多くなったり、 シート数が複数の時は、ETLツールに任せている ◦ Googleスプレッドシート x N → Funnel.io → BigQuery • BigQueryは一番左のシートしか参照できない
25. まとめ
26. まとめ • BigQuery経由でGoogleスプレッドシートを使う場合の、 ゆるふわスキーマ管理方法を紹介しました • 2年くらいこの方法で運用できています • 他の方法もあれば知りたいです🙏
27. 宣伝など • 割とニッチなLookerのブログ記事を書いています🔥 ◦ Looker カテゴリーの記事一覧 - Feedforce Developer Blog • ソフトウェア開発の経験を生かしたLookML開発を続けて いますが、ずっとボッチなので一度誰かと意見交換など してみたいです🙏 ◦ 特にLookMLのレイヤー化の話は試行錯誤中で興味あります
28. ご清聴ありがとうございました。
29. 以下、発表から除外
30. LookML側でそのまま扱いづらいデータは、SQLのビューを 間に挟むと良い（次ページ）
31. SQLのビューを間に挟む例 例えば、全カラムnullのレコードが急に出てくることがある。primary_keyがnullになり得るので避けた いところ。LookMLの派生テーブルでも解決出来るが、BigQueryの責務にしたほうがLookMLをシンプル に保てると思う。 # SQLのビューとして、例えばこんなSQLのファイルを用意する $ cat all_data_view.sql SELECT DISTINCT * FROM `project_name.masterdata.gsheet` WHERE id IS NOT NULL $ bq mk --use_legacy_sql=false --view "$(cat all_data_view.sql)" masterdata.all_data_view # LookML → SQLのビュー → 外部テーブル 人間が作るテーブルでありがちな、ピボットされたテーブルのピボット解除もSQLのビューでやると良い 気がする。