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
    JSON関数と共に歩む
    BigQueryを使った超汎化型データ活用基盤
    Future Tech Night #21 - 3/17(Thu) 19:00 ~ 20:00
    Yasuhiro Murata

    View full-size slide

  2. Copyright ©2022 by Future Corporation
    自己紹介
    村田 靖拓 (むらた やすひろ)
    ● 新卒でFutureへ入社
    ● 文系文学部のド文系出身からITの世界へ
    ● Technology Innovation Group, DX Unit所属
    ● UI/UXからビジネス、システムのインフラまで丸っと担当
    ● クラウドを活用したIoT案件が最近のお仕事
    @famipapamart

    View full-size slide

  3. Copyright ©2022 by Future Corporation
    今回お話ししたいこと

    View full-size slide

  4. Copyright ©2022 by Future Corporation
    今回お話ししたいこと
    n BigQueryを軸としたIoTデータ蓄積・活用基盤の設計と実装
    – 全体アーキ設計における、サービス選定
    – 全体アーキ設計における、At least onceとの付き合い方
    – BigQueryのテーブル設計
    ü テーブル分割
    ü カラム構造
    – JSON関数との戯れ

    View full-size slide

  5. Copyright ©2022 by Future Corporation
    まずは少しばかり背景を

    View full-size slide

  6. Copyright ©2022 by Future Corporation
    本日のお話しの背景
    n ビルのIoTデータをひとつのデータ基盤に集約したい、という要望
    があった。
    システム A
    システム B
    システム C
    データ基盤
    連携元システムには
    データ取得用のAPIが
    用意されていた
    温湿度、空調、人感
    点検、インシデント

    View full-size slide

  7. Copyright ©2022 by Future Corporation
    全体アーキ設計における
    サービス選定

    View full-size slide

  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がなかった)

    View full-size slide

  9. Copyright ©2022 by Future Corporation
    全体アーキ設計における
    At least onceとの付き合い方

    View full-size slide

  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以前の重複を許容

    View full-size slide

  11. Copyright ©2022 by Future Corporation
    BigQueryのテーブル設計

    View full-size slide

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

    View full-size slide

  13. Copyright ©2022 by Future Corporation
    BigQueryのテーブル設計 – テーブル分割編
    n 各連携元システムでは、データ種別ごとにAPIが分割されていたた
    め、APIごとにテーブルを分割する形となった。
    システム A
    システム B
    システム C
    点検情報
    空室情報
    温湿度
    インシデント
    防災情報
    点検情報API
    インシデントAPI
    空室情報API
    温湿度API
    防災情報API
    APIも日々成長を続けてお
    り、APIの状況に合わせて
    各テーブルの取り扱いを柔
    軟に変更できる形とした。

    View full-size slide

  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クエリ時にカラム指定によるス
    キャンデータ量削減を見込めないが、
    柔軟性を優先しこちらの方式を採択

    View full-size slide

  15. Copyright ©2022 by Future Corporation
    JSON関数との戯れ

    View full-size slide

  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一括方式の採択を後押し

    View full-size slide

  17. Copyright ©2022 by Future Corporation
    JSON関数との戯れ
    n 分析クエリでは、“JSON_EXTRACT_SCALAR”を活用。
    – JSONオブジェクト内でデータは入れ子状態になりうるが、アクセス対象
    の値は”スカラー値”であった。

    View full-size slide

  18. Copyright ©2022 by Future Corporation
    JSON関数との戯れ
    n 以下のようなイメージで、クエリを構築。
    例えば
    {“position”:”roomA”,”sensor”:{“temp”:”25”}}
    Where句での絞り込みにも使用可能

    View full-size slide

  19. Copyright ©2022 by Future Corporation
    JSON関数との戯れ
    n 最新のBigQueryドキュメントでは、新しいJSON関数の利用が推奨
    されている。
    レガシー 推奨
    JSON_EXTRACT JSON_QUERY
    JSON_EXTRACT_SCALAR JSON_VALUE

    View full-size slide

  20. Copyright ©2022 by Future Corporation
    まとめ

    View full-size slide

  21. Copyright ©2022 by Future Corporation
    まとめ
    n BigQueryの機能のおかげで、クイックスタートに適したデータ活
    用基盤を簡単に構築することが可能。
    n JSON関数を利用することで、データレイアウトがまだふわっとし
    ている段階からでもデータ分析を実施することができる。
    n (ただし、本方式はBQのコスト最適化アプローチから外れるため、
    用法・容量を守って正しく使うべし)

    View full-size slide

  22. Copyright ©2022 by Future Corporation
    Fin.

    View full-size slide