😊

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

16 min read

はじめに

この記事は、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クエリを実行時に以下のエラーが表示されます。

The Google BigQuery Standard SQL database encountered an error while running this query.
Query execution failed: Access Denied: BigQuery BigQuery: Permission denied while getting Drive credentials.

  • 本投稿では、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_tablecompany_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値の扱いになります。
  • どうしてもマスターからデータを削除したい場合はtruefalseに変更してください。
  • また、直接スプレッドシートに値を入れるとGoogleフォームで認識されず上書きされます。

まとめ

さて、いかがでしたでしょうか?

今回の会社IPマスターのサンプルデータでは、結合しても全てNoになってしまいますが
正しいデータを利用すれば、会社経由からアクセスしたログなのか簡単に判断できるはずです。

今回は会社IPマスターをサンプルに解説しましたが
蓄積データに対して、後から特定のフィールド(もしくはフィールド内の一部の文字列)と
あるキーワードを結合させて、キーワードにマッチするデータをフィルタしたい場合には
この方法は意外と便利だと思います。

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

脚注
  1. label(for views) ↩︎

  2. label(for fields) ↩︎

  3. description(for fields) ↩︎

  4. primary_key ↩︎

  5. connection ↩︎

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

Discussion

ログインするとコメントできます