Zenn
😊

【Looker】Googleフォームからデータ登録することでデータ民主化を加速してみた

2021/12/19に公開
GCP
Google
BigQuery
Google Sheets
Looker
tech

はじめに

この記事は、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

実施手順

  1. Googleフォームの作成
  2. BigQueryのテーブル作成
  3. Googleフォームからデータ登録
  4. BigQueryクエリエディタで参照
  5. スプレッドシートの閲覧アクセス権追加
  6. Lookerでviewの作成
  7. LookerでExploreの作成
  8. 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ファイルをベースに以下の内容を追記します。
company_ip_master.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を作成します。
log_table.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ファイルに以下の設定を追記します。

log_table_with_company_ip_master.model.lkml
# 事前作成済みのデータベース接続名を指定します。
+ 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マスターをサンプルに解説しましたが
蓄積データに対して、後から特定のフィールド(もしくはフィールド内の一部の文字列)と
あるキーワードを結合させて、キーワードにマッチするデータをフィルタしたい場合には
この方法は意外と便利だと思います。

皆様にとっても何かの役に立つと幸いです^^
では、良いお年を！！

脚注

  1. label(for views) ↩︎

  2. label(for fields) ↩︎

  3. description(for fields) ↩︎

  4. primary_key ↩︎

  5. connection ↩︎

  6. LookMLでの結合の使用 ↩︎

Discussion