【Looker】Googleフォームからデータ登録することでデータ民主化を加速してみた
はじめに
この記事は、Looker Advent Calendar 2021の19日目の記事です。
あっという間に1年が終わろうとしてますが、Lookerライフをエンジョイしてましたか?
Lookerと言えば、やはり データ民主化 ですよね。
今回は、非エンジニアであるビジネスユーザにSQLを使わせずに
分析に利用するデータを登録する方法を考えてみました。
今回のやりたきこと
普段、セキュリティのログ分析にLookerを利用しています。
Lookerが参照しているデータベースには GoogleのBigQuery を採用しています。
ですが、ビジネスユーザに直接BigQueryでSQLを書かせてしまっては
Lookerを利用している価値が半減してしまいます。
そこでGoogleフォームを利用して、入力したデータをSpreadsheetに出力させて
そのSpreadsheetのシートをBigQueryの外部テーブルとして参照させてみました。
構成図
上図の構成を取ることでビジネスユーザはSQLを書くことなく
簡単なマスターデータの登録と分析や可視化をWebブラウザのみで完結できています。
今回はサンプルとして、オフィスで利用しているグローバルIPアドレスを管理する
会社IPマスター(company_ip_master) をGoogleフォームから登録し
ログテーブル(log_table) をLookerで結合して分析できるようにしています。
【参考】
・ Live Spreadsheets in Databases
・ 回答をスプレッドシートに集計
実行環境
Product | version |
---|---|
Google Workspace | Business Standard |
BigQuery | 2021年11月24日時点 |
Looker | 21.18.21 |
【補足】
・ Lookerのデータベース接続にはGoogle BigQuery Standard SQLを利用しています。
・ LookerからBigQueryへの接続は事前設定済みの環境を利用しています。
・ BigQueryのログテーブル(log_table)は事前作成済みの環境を利用しています。
スキーマ情報
- ログテーブル(log_table)のスキーマ情報は以下の通りになります。
フィールド名 | タイプ | モード |
---|---|---|
action | STRING | REQUIRED |
actor_type | STRING | NULLABLE |
actor_user_email | STRING | NULLABLE |
actor_user_id | STRING | NULLABLE |
actor_user_name | STRING | NULLABLE |
actor_user_team | STRING | NULLABLE |
context_ip_address | STRING | NULLABLE |
context_location_domain | STRING | NULLABLE |
context_location_id | STRING | NULLABLE |
context_location_name | STRING | NULLABLE |
context_location_type | STRING | NULLABLE |
context_session_id | STRING | NULLABLE |
context_ua | STRING | NULLABLE |
date_create | TIMESTAMP | REQUIRED |
entity_file_filetype | STRING | NULLABLE |
entity_file_id | STRING | NULLABLE |
entity_file_name | STRING | NULLABLE |
entity_file_title | STRING | NULLABLE |
entity_type | STRING | NULLABLE |
id | STRING | REQUIRED |
実施手順
- Googleフォームの作成
- BigQueryのテーブル作成
- Googleフォームからデータ登録
- BigQueryクエリエディタで参照
- スプレッドシートの閲覧アクセス権追加
- Lookerでviewの作成
- LookerでExploreの作成
- LookerでExploreの参照
1. Googleフォームの作成
- WebブラウザでGoogleアプリから Forms をクリックします。
- [新しいフォームを作成]で[空白]を選択します。
新しいフォームを作成
- 以下の内容でフォームを作成します。
項目 | 値 |
---|---|
フォームのタイトル | 会社IPマスター登録 |
フォームの説明 | 会社で利用しているグローバルIPアドレスを登録するためのフォーム |
フォームのタイトルと説明
- 以下の内容で1つ目の質問を作成します。
項目 | 値 |
---|---|
質問 | ipaddress |
形式 | 記述式 |
説明 | IPアドレス (x.x.x.xの形式)を入力します。 |
必須 | オン |
質問1の設定
- 以下の内容で2つ目の質問を作成します。
項目 | 値 |
---|---|
質問 | office_ip |
形式 | チェックボックス |
説明 | trueをチェックします。 |
選択肢1 | true |
必須 | オン |
質問2の設定
- 以上で質問の作成は完了で、次は回答をスプレッドシートに出力するように設定します。
- [回答]タブをクリックし、 スプレッドシート のアイコンをクリックします。
回答のスプレッドシート出力
- [新しいスプレッドシートを作成]を選択し、 company_ip_master と入力します。
- 下部の[作成]をクリックします。
新しいスプレッドシートを作成
- 1行目にタイムスタンプ(回答時刻)、質問1、質問2が記載された空のシートが作成されます。
- データ登録すると回答が2行目以降に順に追記されていきます。
スプレッドシートの内容
2. BigQueryのテーブル作成
- WebブラウザでGCPコンソールにログインします。
- [BigQuery] > [SQLワークスペース]を開きます。
- テーブルを作成 をクリックします。(データセットがない場合は合わせて作成します)
- 下記内容でソースを設定します。
【ソースの設定】
項目 | 値 |
---|---|
テーブルの作成元 | ドライブ |
ドライブのURIを選択 | [スプレッドシートのURI] |
ファイル形式 | Googleスプレッドシート |
シート範囲 | [空欄のまま] |
※ シート範囲はデフォルト設定の場合、最初のシートのみ参照します。
ソースの設定
- 下記内容で送信先を設定します。
【送信先の設定】
項目 | 値 |
---|---|
プロジェクト | [テーブルが存在するBigQueryプロジェクト名] |
データセットID | [テーブルが存在するデータセットID] |
テーブル名 | company_ip_master |
テーブルタイプ | 外部テーブル |
送信先の設定
- 下記内容でスキーマの手動設定を行います。
【スキーマの設定】
フィールド名 | タイプ | モード |
---|---|---|
time_stamp | TIMESTAMP | NULLABLE |
ipaddress | STRING | NULLABLE |
office_ip | BOOLEAN | NULLABLE |
スキーマの設定
- 下記内容で詳細オプションを設定します。
【詳細オプションの設定】
項目 | 値 |
---|---|
スキップするヘッダー行 | 1 |
詳細オプションの設定
- 下部の[テーブルを作成]をクリックして、テーブルを作成します。
- 以下のようにテーブルが作成されます。
スキーマの内容
- [詳細]タブの横に[プレビュー]タブがないのが、外部テーブルの特徴だと思います。
外部データ設定の内容
3. Googleフォームからデータ登録
- Googleフォームの右上部の[プレビュー]ボタンを押します。
プレビューボタン
- サンプルとして適当にIPアドレス (今回は、1.1.1.1) を入力し、trueにチェックを入れます。
- 下部の[送信]ボタンを押して、登録します。
データ登録
- スプレッドシートに回答として2行目のデータが登録されていることを確認します。
登録の内容
4. BigQueryクエリエディタで参照
- 再度、BigQueryの[SQLワークスペース]を開き、[クエリ]をクリックします。
クエリボタン
- クエリエディタで
company_ip_master
テーブルに対して、以下のクエリを実行します。
SELECT
*
FROM
`<BigQueryプロジェクト名>.<データセット名>.company_ip_master`
- 以下のような結果が得られるはずです。
クエリ結果
5. スプレッドシートの閲覧アクセス権追加
- スプレッドシートにはBigQueryを介してLookerからアクセスされます。
- BigQuery接続時のサービスアカウントでLookerが閲覧できる必要があります。
Share the spreadsheet with the service account you use to connect to Looker or make the spreadsheet available to all via link access (view only).
- 権限不足しているとLookerでSQLクエリを実行時に以下のエラーが表示されます。
-
本投稿では、Cloud IAMでのBigQuery接続用のサービスアカウント作成やデータベース接続の設定方法は説明しません。Looker公式ドキュメントを参考にしてみてください。
-
スプレッドシートの[共有]ボタンをクリックします。
共有ボタン
- BigQuery接続用サービスアカウントに閲覧権限を付与して共有します。
共有内容
【補足】
・ 上記のlooker-user@
のサービスアカウントはサンプルになります。
6. Lookerでviewの作成
- ここからはLookerを使い、以下のER図のような分析環境を構築します。
ER図
- まずは、各テーブルを元にLookerのViewを作成します。
- Lookerにログインし、[Develop] > [Manage LookML Projects]をクリックします。
- [New LookML Project]をクリックして、新規に空のLookMLプロジェクトを作成します。
- [+]ボタンから[Create View From Table]を選択します。
- [company_ip_master]を選択し、[Create Views]をクリックします。
- 自動生成されたViewファイルをベースに以下の内容を追記します。
view: company_ip_master {
+ label: "会社IPマスターテーブル"
sql_table_name: `<BigQueryプロジェクト名>.<データセット名>.company_ip_master`
;;
dimension: ipaddress {
+ label: "IPアドレス"
+ description: "オフィスLANからアクセスする時に利用しているグローバルIPアドレス"
type: string
+ primary_key: yes
sql: ${TABLE}.ipaddress ;;
}
dimension: office_ip {
+ label: "会社用IPアドレス有無"
+ description: "会社で利用している場合はyes、違う場合はno"
type: yesno
sql: ${TABLE}.office_ip ;;
}
dimension_group: time_stamp {
+ label: "登録日時"
+ description: "会社マスター用テーブルに登録した日時"
type: time
timeframes: [
raw,
time,
date,
week,
month,
quarter,
year
]
sql: ${TABLE}.time_stamp ;;
}
measure: count {
type: count
+ label: "件数"
drill_fields: []
}
}
【補足】
※1 label[1]を使用して、Viewに論理名(表示名)を追加しています。
※2 label[2]を使用して、フィールドに論理名(表示名)を追加しています。
※3 description[3]を使用して、フィールドの説明文を追加しています。
※4 主キーとするフィールドにprimary_key[4]を設定しています。
- [Save Changes]をクリックし、Viewファイルの変更を保存します。
- ログテーブルも同じようにテーブルからViewを作成します。
view: log_table {
+ label: "ログテーブル"
sql_table_name: `<BigQueryプロジェクト名>.<データセット名>.log_table`
;;
dimension: id {
+ primary_key: yes
type: string
+ label: "エントリのID"
sql: ${TABLE}.id ;;
}
dimension: action {
type: string
+ label: "アクション名"
sql: ${TABLE}.action ;;
}
dimension: actor_type {
type: string
+ label: "操作対象の種別"
sql: ${TABLE}.actor_type ;;
}
dimension: actor_user_email {
type: string
+ label: "操作ユーザのメールアドレス"
sql: ${TABLE}.actor_user_email ;;
}
dimension: actor_user_id {
type: string
+ label: "操作ユーザID"
sql: ${TABLE}.actor_user_id ;;
}
dimension: actor_user_name {
type: string
+ label: "操作ユーザ名"
sql: ${TABLE}.actor_user_name ;;
}
dimension: actor_user_team {
type: string
+ label: "操作ユーザのチームID"
sql: ${TABLE}.actor_user_team ;;
}
dimension: context_ip_address {
type: string
+ label: "送信元IPアドレス"
sql: ${TABLE}.context_ip_address ;;
}
dimension: context_location_domain {
type: string
+ label: "ワークスペースのドメイン名"
sql: ${TABLE}.context_location_domain ;;
}
dimension: context_location_id {
type: string
+ label: "ワークスペースのID"
sql: ${TABLE}.context_location_id ;;
}
dimension: context_location_name {
type: string
+ label: "ワークスペース名"
sql: ${TABLE}.context_location_name ;;
}
dimension: context_location_type {
type: string
+ label: "ローケーションの種別"
sql: ${TABLE}.context_location_type ;;
}
dimension: context_session_id {
type: number
+ label: "セッションID"
sql: ${TABLE}.context_session_id ;;
}
dimension: context_ua {
type: string
+ label: "ユーザエージェント名"
sql: ${TABLE}.context_ua ;;
}
dimension_group: date_create {
type: time
+ label: "イベントの発生日時"
timeframes: [
raw,
time,
hour,
date,
week,
month,
quarter,
year
]
sql: ${TABLE}.date_create ;;
}
dimension: entity_file_filetype {
type: string
+ label: "ファイルの種別"
sql: ${TABLE}.entity_file_filetype ;;
}
dimension: entity_file_id {
type: string
+ label: "ファイルの識別子"
sql: ${TABLE}.entity_file_id ;;
}
dimension: entity_file_name {
type: string
+ label: "ファイル名"
sql: ${TABLE}.entity_file_name ;;
}
dimension: entity_file_title {
type: string
+ label: "ファイルのタイトル"
sql: ${TABLE}.entity_file_title ;;
}
dimension: entity_type {
type: string
+ label: "エンティティの種別"
sql: ${TABLE}.entity_type ;;
}
measure: count {
type: count
+ label: "件数"
drill_fields: [id, entity_file_name, context_location_name, actor_user_name]
}
}
- [Save Changes]をクリックし、Viewファイルの変更を保存します。
7. LookerでExploreの作成
- log_tableにcompany_ip_masterを結合するExlporeをModelファイルで設定します。
- [+]ボタンから[Create Model]を選択します。
-
ファイル名を[log_table_with_company_ip_master]とします。
-
Modelファイルに以下の設定を追記します。
# 事前作成済みのデータベース接続名を指定します。
+ connection: "test_bq_connection"
include: "/views/*.view.lkml"
# 結合の設定
+ explore: log_table {
+ label: "ログテーブル(会社IPマスタ結合)"
+ join: company_ip_master {
+ relationship: many_to_one
+ type: left_outer
+ sql_on: ${log_table.context_ip_address} = ${company_ip_master.ipaddress}
+ ;;
+ }
+ }
【補足】
※1 connection[5]には事前作成済みのデータベース接続名を指定します。
※2 exlopreセクションでは2つのViewを結合するための設定[6]をしています。
- [Save Changes]をクリックしてModelファイルの変更を保存します。
8. LookerでExploreの参照
- [Explore] > [ログテーブル(会社IPマスタ結合)]を開きます。
- ログテーブルの[送信元IPアドレス]をフィールド選択します。(❶)
- 会社IPマスターテーブルの[会社IPアドレス有無(Yes/No)]をフィールド選択します。(❷)
- 画面右上の[実行]ボタンを押します。
- [データ(結果)]に上記のような結合結果が出力されればOKです。(❸)
注意点
- スプレッドシートを編集するとBigQueryがデータを認識できなくなることがあります。
- 具体的には、セルの値を
Del
キーで削除するとBigQueryではNull値の扱いになります。 - どうしてもマスターからデータを削除したい場合は
true
をfalse
に変更してください。 - また、直接スプレッドシートに値を入れるとGoogleフォームで認識されず上書きされます。
まとめ
さて、いかがでしたでしょうか?
今回の会社IPマスターのサンプルデータでは、結合しても全てNoになってしまいますが
正しいデータを利用すれば、会社経由からアクセスしたログなのか簡単に判断できるはずです。
今回は会社IPマスターをサンプルに解説しましたが
蓄積データに対して、後から特定のフィールド(もしくはフィールド内の一部の文字列)と
あるキーワードを結合させて、キーワードにマッチするデータをフィルタしたい場合には
この方法は意外と便利だと思います。
皆様にとっても何かの役に立つと幸いです^^
では、良いお年を!!
Discussion