Slide 1

Slide 1 text

BigQuery経由で使う Googleスプレッドシート のスキーマ管理 2022-07-21 Looker User Meetup Online #8 株式会社フィードフォース 増田貴士

Slide 2

Slide 2 text

自己紹介 ● 増田貴士(@masutaka) ● https://masutaka.net/ ● 株式会社フィードフォースのボッチデータ整備人 ○ ウェブ広告やShopify関連で、LookML開発やAdmin権限での運用に携わっている ● 過去に組み込みソフトウェア開発や、ウェブのバックエ ンド&インフラの開発や運用の経験もあり

Slide 3

Slide 3 text

今回の概要 ● BigQueryからGoogleスプレッドシートを参照できるよ ● 制約はあるけど、ビジネスユーザーが簡単にデータを更 新できるのはメリットだよ ● フィードフォースで使っている、Googleスプレッドシー トのゆるふわスキーマ管理方法を紹介するよ ○ ※ スキーマとはカラム(列)の名前や型の定義のことです

Slide 4

Slide 4 text

BigQueryのデータソース

Slide 5

Slide 5 text

BigQueryのデータソース 種別 メカニズム データソース/データ ベース 形式 BigQueryストレージ 外部データソース 外部テーブル Bigtable Cloud Storage (GCS) CSV, JSON など Googleドライブ カンマ区切り値(CSV) JSON(改行区切り) Avro スプレッドシート 連携クエリ Cloud Spanner Cloud SQL 外部データソースの概要 | BigQuery | Google Cloud

Slide 6

Slide 6 text

データソースを意識せずにクエリ実行可能 BigQuery BigQuery ストレージ Google スプレッドシート 外部データソー ス クエリ実行 参照のみ 参照/更新

Slide 7

Slide 7 text

BigQuery経由で使った場合の比較 BigQueryストレージ Googleスプレッドシート ビジネスユー ザーがデータを 更新可能 難しい 簡単 ビジネスユー ザーがスキーマ を変更可能 難しい 例えば列を挿入すると、クエ リエラーになる 扱えるデータ量 気にしなくて良い 上限1,000万セルだが、実際 は数万行でクエリタイムアウ トなどが発生し始める Google ドライブに保管可能なファイル - Google ドライブ ヘルプ

Slide 8

Slide 8 text

外部データソースにGoogleスプレッドシートを使う場合の、 一般的な作成方法と、今回紹介する作成方法を比較していき ます。

Slide 9

Slide 9 text

一般的な作成方法

Slide 10

Slide 10 text

BigQueryコンソールから作成する ドライブデータをクエリする | BigQuery | Google Cloud

Slide 11

Slide 11 text

Googleスプレッドシートに閲覧権限を与える(続く)

Slide 12

Slide 12 text

与えるユーザーは当該ConnectionのService Account Email

Slide 13

Slide 13 text

SQLは通常のBigQueryと変わらない SELECT * FROM `..`

Slide 14

Slide 14 text

課題

Slide 15

Slide 15 text

課題 ● スキーマの変更履歴をGitに記録できない ○ なぜ変更したのか?をあとから追跡しづらい ● スキーマを破壊的に変更しづらい ○ 一度作ったテーブルのカラムは変更や削除が出来ない。追加なら出来る ○ 例えばあるカラムを削除したくなった時は、変更したいテーブルと同じスキーマのテーブル を作りつつ、削除したいカラムは含めないようにする ○ その後、LookMLからのテーブル参照を切り替える

Slide 16

Slide 16 text

今回紹介する作成方法

Slide 17

Slide 17 text

bq CLIを使う 1. データセットの作成 ○ $ bq mk ○ 例: $ bq mk masterdata 2. 外部テーブルの作成 ○ $ bq mk --external_table_definition=./define.json . ○ 例: $ bq mk --external_table_definition=./define.json masterdata.gsheet

Slide 18

Slide 18 text

ポイント ● bq CLIを使う ○ ローカルにCloud SDKをインストールすれば使える ■ Cloud SDK のインストール | Google Cloud ○ macOSなら $ brew install --cask google-cloud-sdk でのインストールが楽かも ● スキーマ定義をdefine.jsonで管理する ○ 例: https://gist.github.com/masutaka/142ca20b802401d12012fe952f2ea1f3

Slide 19

Slide 19 text

LookMLと同じリポジトリで管理する bigquery/ ├ dataset_name/ │├ README.md │├ define.json │└ schema.json └ … explores/ layers/ …

Slide 20

Slide 20 text

課題を解決できた ● スキーマの変更をGitで記録できない件 ○ LookMLと同じリポジトリで管理することが出来た ○ スキーマ変更に伴うLookMLの変更を、1つのPull requestで表現できるようになった ● スキーマを破壊的に変更しづらい件 ○ define.jsonから同じスキーマのテーブルを量産出来るので、簡単になった(次ページ参照)

Slide 21

Slide 21 text

スキーマの破壊的な変更例 1. スプレッドシートの複製を作る 2. ↑を参照する、ゆるふわなバージョンを付けたデータセットとテーブルを新 たに作る。例: masterdata_v2.gsheet 3. LookML側でテーブル参照を切り替える 4. 良さそうなら、スプレッドシートの参照を切り替えてデプロイする 5. 古いテーブルはデータセットごと削除する ○ 例: $ bq rm -r masterdata

Slide 22

Slide 22 text

落ち穂拾い

Slide 23

Slide 23 text

ベストプラクティス ● 1行目ヘッダ、2行目以降データのシンプルな構造を保つ ○ 人間ではなく、機械にやさしい構造を意識する ○ とにかく軽く、シンプルさを保つ。個人的には、出来るだけ色も付けない ● IMPORTRANGEのような、他シートを参照する関数はで きるだけ使わない ○ 使いすぎるとクエリタイムアウトの確率が上がる。特に多段IMPORTRANGE

Slide 24

Slide 24 text

雑多なこと ● クエリエラーが発生するほどデータ量が多くなったり、 シート数が複数の時は、ETLツールに任せている ○ Googleスプレッドシート x N → Funnel.io → BigQuery ● BigQueryは一番左のシートしか参照できない

Slide 25

Slide 25 text

まとめ

Slide 26

Slide 26 text

まとめ ● BigQuery経由でGoogleスプレッドシートを使う場合の、 ゆるふわスキーマ管理方法を紹介しました ● 2年くらいこの方法で運用できています ● 他の方法もあれば知りたいです🙏

Slide 27

Slide 27 text

宣伝など ● 割とニッチなLookerのブログ記事を書いています🔥 ○ Looker カテゴリーの記事一覧 - Feedforce Developer Blog ● ソフトウェア開発の経験を生かしたLookML開発を続けて いますが、ずっとボッチなので一度誰かと意見交換など してみたいです🙏 ○ 特にLookMLのレイヤー化の話は試行錯誤中で興味あります

Slide 28

Slide 28 text

ご清聴ありがとうございました。

Slide 29

Slide 29 text

以下、発表から除外

Slide 30

Slide 30 text

LookML側でそのまま扱いづらいデータは、SQLのビューを 間に挟むと良い(次ページ)

Slide 31

Slide 31 text

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のビューでやると良い 気がする。