Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

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

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

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

Yasuhiro Murata

March 18, 2022
Tweet

More Decks by Yasuhiro Murata

Other Decks in Technology

Transcript

  1. Copyright ©2022 by Future Corporation 自己紹介 村田 靖拓 (むらた やすひろ)

    • 新卒でFutureへ入社 • 文系文学部のド文系出身からITの世界へ • Technology Innovation Group, DX Unit所属 • UI/UXからビジネス、システムのインフラまで丸っと担当 • クラウドを活用したIoT案件が最近のお仕事 @famipapamart
  2. Copyright ©2022 by Future Corporation 今回お話ししたいこと n BigQueryを軸としたIoTデータ蓄積・活用基盤の設計と実装 – 全体アーキ設計における、サービス選定

    – 全体アーキ設計における、At least onceとの付き合い方 – BigQueryのテーブル設計 ü テーブル分割 ü カラム構造 – JSON関数との戯れ
  3. Copyright ©2022 by Future Corporation 本日のお話しの背景 n ビルのIoTデータをひとつのデータ基盤に集約したい、という要望 があった。 システム

    A システム B システム C データ基盤 連携元システムには データ取得用のAPIが 用意されていた 温湿度、空調、人感 点検、インシデント
  4. 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がなかった)
  5. 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以前の重複を許容
  6. Copyright ©2022 by Future Corporation BigQueryのテーブル設計 – テーブル分割編 n テーブルは”データ種別ごと”に分割した。

    – DWHとしてのテーブル設計について社内で議論。 – データ構造の変更はデータ種別ごとに行われるケースが多いこと、デー タ取得もデータ種別ごとに行われることから、上記方針を採択。
  7. Copyright ©2022 by Future Corporation BigQueryのテーブル設計 – テーブル分割編 n 各連携元システムでは、データ種別ごとにAPIが分割されていたた

    め、APIごとにテーブルを分割する形となった。 システム A システム B システム C 点検情報 空室情報 温湿度 インシデント 防災情報 点検情報API インシデントAPI 空室情報API 温湿度API 防災情報API APIも日々成長を続けてお り、APIの状況に合わせて 各テーブルの取り扱いを柔 軟に変更できる形とした。
  8. 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クエリ時にカラム指定によるス キャンデータ量削減を見込めないが、 柔軟性を優先しこちらの方式を採択
  9. 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一括方式の採択を後押し
  10. Copyright ©2022 by Future Corporation まとめ n BigQueryの機能のおかげで、クイックスタートに適したデータ活 用基盤を簡単に構築することが可能。 n

    JSON関数を利用することで、データレイアウトがまだふわっとし ている段階からでもデータ分析を実施することができる。 n (ただし、本方式はBQのコスト最適化アプローチから外れるため、 用法・容量を守って正しく使うべし)