スプレッドシート->BigQuery->データポータルでSQLでのデータ加工処理が必要なレポート作成を試してみる
- 社内やチームのデータをスプレッドシートで管理してる。
- スプレッドシートのデータをデータポータルで可視化したい。
- けど、スプレッドシートの内容そのままだと可視化しにくいから間に集計/加工処理を挟みたい...。
というモチベーションから、
- スプレッドシートのデータをBigQueryで読み込む
- ビューを作成
- データポータルからビューを参照
- レポートを作成！
を試してみたので、紹介します。
やってみる
今回は次のような人ごとの資格取得日を記載したスプレッドシートがあることを想定します。
- 1列目に名前
- 2から6列目に日付
- 空のセルもあり
このシートをデータポータルで可視化したいんですが、データが横持ちで扱いづらいという課題があります。そこで、BigQueryでこのシートを読み込み、縦持ちに変換したビューを作成し、データポータルから参照しレポートを作成します。
BigQuery
テーブル作成
BigQueryでスプレッドシートを読み込むテーブルを作成します。
まずはBigQueryのコンソールからデータセットを作成します。
まずはテーブルを格納するデータセットを作成します。データセットIDは適当に設定します。データのロケーションや、テーブルの有効期限、暗号化はデフォルトのまま利用します。
データセットが作成されたら対象のデータセットを選択します。
データセットの中にテーブルを作成します。
テーブルの作成元はドライブを選択します。
ドライブのURIには、対象のスプレッドシートのURIを入力します。
ファイル形式はGoogleスプレッドシートを選択し、シートの範囲には対象のシートのヘッダーを除いた値の範囲を選択します。範囲はシート名!左上のセル:右下のセルで指定します。
今回の範囲は、資格取得日時!A2:F7になります。
送信先にはどこになんて名前のテーブルを作成するかを設定します。
続いて、シートの内容に応じたスキーマを設定します。 今回はGUIでスキーマを設定しますが、JSON形式のスキーマを入力することも可能です。
名前(name)と5種類の資格取得日時(a~e)の6つの項目についてスキーマを定義します。 今回は詳細オプションを設定せず、そのままテーブルを作成します。
テーブル作成の際にスプレッドシートへのアクセス許可を求められる場合があります。その場合は、内容を確認の上、許可する必要があります。
ビュー作成
続いて、先ほど作成したテーブルを参照するビューを作成します。
クエリエディタからビュー用のデータを取得するクエリを実行します。クエリは、横持ちを縦持ち資格取得者名、資格名、取得日時に変換し、取得日時がNULLのデータは取り除くという内容です。
データ量が少ないので、実行はすぐ完了します。
クエリ実行後、ビューを保存からクエリをビューとして保存します。
送信先として、ビューをどこに保存するか設定します。
これでBigQuery側での準備は完了です。
注意点として、スプレッドシートなどの外部データソースの場合にはクエリはキャッシュされず、都度実行されるなどいくつか制限事項があります。詳細についてはドキュメントをご確認ください。
データポータル
次にデータポータルに場所を移して、データソースを作成します。
データソース作成
データの各項目はとくに編集せず、そのまま利用します。
右上のレポートを作成からレポート作成に移ります。
レポート作成
設定したデータソースを使ってレポートを作成するため、レポートに追加を選びます。
レポート画面になりました。デフォルトで名前とそれぞれのレコード数が表形式で表示されています。
全員での総取得数も表示してみます。
グラフを追加からスコアカードを選択します。
指標を資格取得数に設定します。
資格取得数が表示されました。
続いて、元々表示されていた名前ごとの資格取得者数を資格取得履歴に変更してみます。表を選択した上で右側の設定ペインで各ディメンションを設定します。
並び替え方法も設定します。
次のように資格取得履歴が表示されました。
続いて、積み上げ棒グラフで週ごとの資格取得数を表示してみます。
各ディメンションや指標、並び替え方法を設定します。
ディメンションに指定した年、週はcertified_dateを週表示に変換したものです。元々のデータのタイプと集計したい粒度を選択するだけで変換した値を利用できます。
こんな感じで週ごとの資格取得数を表示できました。表示できる週数(棒の数)は限られているので、表示したい期間を限定したい場合には設定ペインの日付範囲から設定可能です。
全体的に大きさや位置を整えて、レポートの完成です。
BigQueryのクエリ実行履歴
レポートを表示したり、更新するとBigQueryでクエリが実行されます。コンソールのクエリ履歴から次のようにどんなクエリが実行されたか確認できます。
クエリを選択すると、クエリの内容と課金されるバイト数などの情報も確認可能です。
さいごに
スプレッドシートをデータポータルで可視化する際にBigQueryを挟むことでSQLでの加工クエリを実行することができました。データポータルは無料で利用でき、BigQueryも同様にある程度までは無料で使えるため、ちょっとした可視化にはもってこいの組み合わせです。とはいえ、データ量が多かったり、クエリ実行回数が多い場合にはBigQueryの費用が発生し、場合によっては高額にもなりえます。予算アラートを設定するなど注意は必要です。費用には気をつけつつ、積極的に活用していきたいですね。
![[レポート]AWSでデータメッシュアーキテクチャを構築する#ANT336 #reinvent](https://devio2023-media.developers.io/wp-content/uploads/2022/11/eyecatch_reinvent2022_session-report.png)
