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

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

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

BigQueryのデータソース 

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

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

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

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

一般的な作成方法 

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

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

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

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

課題 

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

今回紹介する作成方法 

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 

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

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

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

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

落ち穂拾い 

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

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

まとめ 

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

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

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

以下、発表から除外 

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

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