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

Slide 1

Slide 1 text

Slide 2

Slide 2 text

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

Slide 3

Slide 3 text

Slide 4

Slide 4 text

Slide 5

Slide 5 text

Slide 6

Slide 6 text

Slide 7

Slide 7 text

Slide 8

Slide 8 text

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

Slide 9

Slide 9 text

Slide 10

Slide 10 text

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

Slide 11

Slide 11 text

Slide 12

Slide 12 text

Slide 13

Slide 13 text

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

Slide 14

Slide 14 text

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

Slide 15

Slide 15 text

Slide 16

Slide 16 text

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

Slide 17

Slide 17 text

Slide 18

Slide 18 text

Slide 19

Slide 19 text

Slide 20

Slide 20 text

Slide 21

Slide 21 text

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

Slide 22

Slide 22 text