JSON関数と共に歩む、BigQueryを使った超汎化型データ活用基盤

Future Tech Night #21 Google Cloud: データエンジニア＋MLOps編 の登壇資料です。

https://future.connpass.com/event/240577/

Transcript

1. Copyright ©2022 by Future Corporation JSON関数と共に歩む BigQueryを使った超汎化型データ活用基盤 Future Tech Night #21 - 3/17(Thu) 19:00 ~ 20:00 Yasuhiro Murata
2. Copyright ©2022 by Future Corporation 自己紹介 村田 靖拓 （むらた やすひろ） • 新卒でFutureへ入社 • 文系文学部のド文系出身からITの世界へ • Technology Innovation Group, DX Unit所属 • UI/UXからビジネス、システムのインフラまで丸っと担当 • クラウドを活用したIoT案件が最近のお仕事 @famipapamart
3. Copyright ©2022 by Future Corporation 今回お話ししたいこと
4. Copyright ©2022 by Future Corporation 今回お話ししたいこと n BigQueryを軸としたIoTデータ蓄積・活用基盤の設計と実装 – 全体アーキ設計における、サービス選定 – 全体アーキ設計における、At least onceとの付き合い方 – BigQueryのテーブル設計 ü テーブル分割 ü カラム構造 – JSON関数との戯れ
5. Copyright ©2022 by Future Corporation まずは少しばかり背景を
6. Copyright ©2022 by Future Corporation 本日のお話しの背景 n ビルのIoTデータをひとつのデータ基盤に集約したい、という要望 があった。 システム A システム B システム C データ基盤 連携元システムには データ取得用のAPIが 用意されていた 温湿度、空調、人感 点検、インシデント
7. Copyright ©2022 by Future Corporation 全体アーキ設計における サービス選定
8. Copyright ©2022 by Future Corporation 全体アーキ設計における、サービス選定 n 主に以下のサービス群を組み合わせる設計とした。 連携元システム Cloud Run BigQuery Pub/Sub Cloud Scheduler 連携元システムには データ取得用のAPIが 用意されていた Cloud RunにてAPIからのデータ取 得、BQ格納前の加工処理を実施 Pub/Sub Cloud Run Pub/SubはPush型で動作 （当時はまだAlways on CPUがなかった）
9. Copyright ©2022 by Future Corporation 全体アーキ設計における At least onceとの付き合い方
10. Copyright ©2022 by Future Corporation 全体アーキ設計における、At least onceとの付き合い方 n BigQueryまでは”重複を許容する”方針で設計した。 連携元システム Cloud Run BigQuery Pub/Sub Cloud Scheduler Pub/Sub Cloud Run Pub/SubはAt least onceなため、 メッセージが複数回送信されうる BQへの書き込みメッセージが重 複するケースが散見された SELECT DISTINCTを利用する方針で、 BQ以前の重複を許容
11. Copyright ©2022 by Future Corporation BigQueryのテーブル設計
12. Copyright ©2022 by Future Corporation BigQueryのテーブル設計 – テーブル分割編 n テーブルは”データ種別ごと”に分割した。 – DWHとしてのテーブル設計について社内で議論。 – データ構造の変更はデータ種別ごとに行われるケースが多いこと、デー タ取得もデータ種別ごとに行われることから、上記方針を採択。
13. Copyright ©2022 by Future Corporation BigQueryのテーブル設計 – テーブル分割編 n 各連携元システムでは、データ種別ごとにAPIが分割されていたた め、APIごとにテーブルを分割する形となった。 システム A システム B システム C 点検情報 空室情報 温湿度 インシデント 防災情報 点検情報API インシデントAPI 空室情報API 温湿度API 防災情報API APIも日々成長を続けてお り、APIの状況に合わせて 各テーブルの取り扱いを柔 軟に変更できる形とした。
14. Copyright ©2022 by Future Corporation BigQueryのテーブル設計 – カラム構造編 n 各APIは成長段階にあったためレスポンスレイアウトの頻繁な変更 が想定され、カラム構造は柔軟な形が求められた。 JSON一括 キーごとに分割 time data 2021-09-13 10:00 {“場所”：”会議室A”, ”湿度":"57.5”, ”温度":"24"} 2021-09-13 10:00 {“場所”：”会議室B”, ”湿度":"56.0”, ”温度":"25"} 2021-09-13 10:03 {“場所”：”会議室A”, ”湿度":"56.5”, ”温度":"25"} time 場所 湿度 温度 2021-09-13 10:00 会議室A 57.5 24 2021-09-13 10:00 会議室B 56.0 25 2021-09-13 10:03 会議室A 56.5 25 BQクエリ時にカラム指定によるス キャンデータ量削減を見込めないが、 柔軟性を優先しこちらの方式を採択
15. Copyright ©2022 by Future Corporation JSON関数との戯れ
16. Copyright ©2022 by Future Corporation JSON関数との戯れ n BigQueryにはJSON関数があり、1カラムに格納されたJSON文字列 の特定キーの値にアクセスが可能。 time data 2021-09-13 10:00 {“場所”：”会議室A”, ”湿度":"57.5”, ”温度":"24"} 2021-09-13 10:00 {“場所”：”会議室B”, ”湿度":"56.0”, ”温度":"25"} 2021-09-13 10:03 {“場所”：”会議室A”, ”湿度":"56.5”, ”温度":"25"} 例えば 『会議室Aの湿度のみを取得する』 といったクエリが記述可能。 この機能の存在が JSON一括方式の採択を後押し
17. Copyright ©2022 by Future Corporation JSON関数との戯れ n 分析クエリでは、“JSON_EXTRACT_SCALAR”を活用。 – JSONオブジェクト内でデータは入れ子状態になりうるが、アクセス対象 の値は”スカラー値”であった。
18. Copyright ©2022 by Future Corporation JSON関数との戯れ n 以下のようなイメージで、クエリを構築。 例えば {“position”:”roomA”,”sensor”:{“temp”:”25”}} Where句での絞り込みにも使用可能
19. Copyright ©2022 by Future Corporation JSON関数との戯れ n 最新のBigQueryドキュメントでは、新しいJSON関数の利用が推奨 されている。 レガシー 推奨 JSON_EXTRACT JSON_QUERY JSON_EXTRACT_SCALAR JSON_VALUE
20. Copyright ©2022 by Future Corporation まとめ
21. Copyright ©2022 by Future Corporation まとめ n BigQueryの機能のおかげで、クイックスタートに適したデータ活 用基盤を簡単に構築することが可能。 n JSON関数を利用することで、データレイアウトがまだふわっとし ている段階からでもデータ分析を実施することができる。 n （ただし、本方式はBQのコスト最適化アプローチから外れるため、 用法・容量を守って正しく使うべし）
22. Copyright ©2022 by Future Corporation Fin.