BigQuery・Glue・S3・QuickSightの連携

今回は、FirebaseAnalyticsからBigQueryにエクスポートしたデータをQuickSight上で、表形式で閲覧したいという要件を実現するための過程を記事にしました。

以前書いたこの記事の続きみたいな感じです。

なぜ、BigQueryのデータをわざわざQuickSightで見ようとするかというと、BigQuery上のデータとRDSのDBデータをガッチャンこして見たいという要件があり、少し回りくどく見えるような構成になっています。

要件を実現するための構成はこれ以外にも案はあると思いますが、考えた結果、これが最適と考えた末なので、何かいい案をお持ちの方がいれば優しく教えて欲しいです。（ここには出てこない隠れた要件も色々あるので最適な構成を考えるのはかなり難しかったです。）

BigQuery・Glue・S3の連携について

それぞれのツールについての概要の公式記事は以下です。

• BigQuery
  • https://cloud.google.com/bigquery/docs/introduction?hl=ja
• Glue
  • https://docs.aws.amazon.com/ja_jp/glue/latest/dg/what-is-glue.html
• S3
  • https://docs.aws.amazon.com/ja_jp/AmazonS3/latest/userguide/Welcome.html

完成形（簡単にざっくりやりたいこと）

QuickSight上で以下のような表示を目指したい。

• 赤枠以外のところがRDSに保存されているDBデータ
• 赤枠のところがBigQueryに保存されているデータ
• それら２つを結合してQuickSight上で一覧表示したい
  • ソートしたりフィルターかけたりCSV出力したりして分析したい

BigQuery・Glue・S3の連携をどのようにしていくか

処理の大枠の流れ

以下のような流れが処理の大枠です。

• GoogleAnalyticsで取得しているデータをBigQueryに定期的にエクスポートしてBigQuery上に永続化しておく
• BigQuery上でその実行時点までの全件取得クエリを一度実行しテーブルを作成
• BigQuery上で1日前のデータが入ったテーブルを作成して日々データを追加していくクエリの定期実行をスケジューリングする(22:30に実行)
• AWS上でGlueを使いコネクター経由でBigQuery上のテーブルデータをその実行時点までの全件取得して取り込んでCSV形式に変換してS3へ吐き出す定期実行のジョブを作成し一度実行
• AWS上でGlueを使いコネクター経由で1日前に追加されたBigQuery上のテーブルデータを取り込んでCSV形式に変換してS3へ吐き出す定期実行のジョブを作成し1日1回実行するようにスケジューリング(23:00に実行)
• S3へ置いたCSVデータとQuickSightを連携させる
• QuickSight上で連携したCSVデータをとRDSのDBデータとを結合させ一覧表示

大枠の図解

設定方法

• 公式

https://aws.amazon.com/jp/blogs/news/migrating-data-from-google-bigquery-to-amazon-s3-using-aws-glue-custom-connectors/

• クラスメソッドさんの記事を参考にしてます（めちゃめちゃわかりやすかったです）

https://dev.classmethod.jp/articles/aws-glue-connector-for-google-bigquery-bigquery-to-s3/

設定の流れ

AWS Secrets Manager に gcp の json キーを base64 に変換したものを登録

base64エンコードする

$ base64 gcp-credentials.json

key名: credentials

シークレット1あたり0.40USD/月

https://aws.amazon.com/jp/secrets-manager/pricing/

• シークレットの名前を登録
• シークレットの自動ローテーションは設定しない

S3 バケットの作成

バケットの作り方は割愛しますが、適切な命名でバケットを作成してください。

IAMロールとポリシーの作成

stg/prod環境の共通設定としてロールとポリシーを作りました。

• ポリシー
  • 信頼関係の設定

GlueがAWSの各種必要なサービス(s3,secret manager)を呼び出すことを許可するための信頼ポリシーを作成（これでGlueに紐付けされる）

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Principal": {
        "Service": "glue.amazonaws.com"
      },
      "Action": "sts:AssumeRole"
    }
  ]
}

• ポリシー
  • glueが行う予定のアクションを許可するためポリシーを作成

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "VisualEditor0",
      "Effect": "Allow",
      "Action": "glue:StartJobRun",
      "Resource": "*"
    },
    {
      "Sid": "VisualEditor1",
      "Effect": "Allow",
      "Action": [
        "s3:PutObject",
        "s3:GetObject",
        "secretsmanager:GetResourcePolicy",
        "secretsmanager:GetSecretValue",
        "secretsmanager:DescribeSecret",
        "s3:ListBucket",
        "s3:GetBucketAcl",
        "s3:DeleteObject",
        "s3:GetBucketLocation",
        "secretsmanager:ListSecretVersionIds"
      ],
      "Resource": [
        "${stgのsecret_manegerのarn}",
        "${prodのsecret_manegerのarn}",
        "arn:aws:s3:::${stgのバケット名}",
        "arn:aws:s3:::${stgのバケット名}/*",
        "arn:aws:s3:::${prodのバケット名}",
        "arn:aws:s3:::${prodのバケット名}/*"
      ]
    }
  ]
}

• ロール

以下のポリシーを紐付けてGlueが各種必要なサービスにアクセスするためのロールが完成する

• 先ほど作ったポリシー
  • AmazonEC2ContainerRegistryReadOnly
  • AWSGlueServiceRole
• gcpのIAMでBigQueryの適切な権限を追加しておく
• コネクターをサブスクライブし、Launch
• Google BigQuery Connector for AWS Glue

https://aws.amazon.com/marketplace/pp/prodview-sqnd4gn5fykx6

• サブスクライブ
  • マーケットプレースの「AWS Glue Connector for Google BigQuery」にアクセスし、「Continue to Subscribe」を選択します。
• コネクター登録
  • サブスクライブが完了したら、「Continue to Configuretaion」を選択
• 使用するバージョンを選んだら「Launch Software」を選択

• 中央の「Usage Instractions」を選択し、ダイアログ内のリンクをクリックすることでAWS Glue Stduioのコネクター追加画面に遷移

• コネクター名と、先程作成したシークレットを設定し作成

• credentialsを選択

Glue stadio でコネクタを登録しジョブを作成

GlueからBigQueryにアクセスするためのコネクターを設定（これによりAWS Glue Studioに紐付けられる）

Glue Job を作成

• AWS Glue Studioのジョブ画面のconectorsのcreate job from a blank graphでジョブを作成する

• ソースにはAWS Glue Connector for Google BigQueryを選択

• ターゲットにはS3を選択

• 事前に作成していたS3バケットを選択
  • 形式はCSV形式

• 「Connection」には、先程作成したコネクター名を設定し、「Connection options」には以下のようにデータを登録

"parentProject": "${プロジェクト名}

"table": "${テーブル名}",

"connectionName": "${コネクター名}

• job detailで先ほど作成したロールを紐付け
• jobのスクリプトを編集
  • job生成時に実行内容のscriptが指定した言語で自動生成されるが自分でそれを編集することも可能（編集するとvisual画面が消えるが特に問題なし）

ジョブスクリプトのロックを解除すると、ビジュアルモードからスクリプトオンリーモードに変換されます。この操作は元に戻すことができません。ビジュアルモードのジョブのコピーを保持するには、Glue Studioのジョブページでジョブのクローンを作成します。

• スクリプト(python)
  • 今回は前日のBigQuery上のデータを取得したいためtarget_dateをscriptに追加した

import datetime
import sys
from awsglue.transforms import *
from awsglue.utils import getResolvedOptions
from pyspark.context import SparkContext
from awsglue.context import GlueContext
from awsglue.job import Job

args = getResolvedOptions(sys.argv, ["JOB_NAME"])
sc = SparkContext()
glueContext = GlueContext(sc)
spark = glueContext.spark_session
job = Job(glueContext)
job.init(args["JOB_NAME"], args)
# 対象のデータは昨日のものを指定
target_date = datetime.datetime.now() - datetime.timedelta(days=1)
# Script generated for node Google BigQuery Connector 0.22.0 for AWS Glue 3.0
GoogleBigQueryConnector0220forAWSGlue30_node1 = (
    glueContext.create_dynamic_frame.from_options(
        connection_type="marketplace.spark",
        connection_options={
            "parentProject": "${BigQuery上のプロジェクト名}",
            "table": "${BigQuery上のテーブル名}",
            "connectionName": "${コネクター名}",
        },
        transformation_ctx="GoogleBigQueryConnector0220forAWSGlue30_node1",
    )
)

# Script generated for node S3 bucket
S3bucket_node3 = glueContext.write_dynamic_frame.from_options(
    frame=GoogleBigQueryConnector0220forAWSGlue30_node1,
    connection_type="s3",
    format="csv",
    connection_options={
        "path": "s3://${データを吐き出す先のS3バケット名}/",
        "partitionKeys": [],
    },
    transformation_ctx="S3bucket_node3",
)

job.commit()

スケジュールの設定

• ジョブの実行スケジュールを作成

BigQueryの定期実行

BigQuery で新たにデータセットを作成

ロケーションはアナリティクスのデータに合わせて US を指定（ロケーションが違うとうまく動かない）

定期実行用のサービスアカウントを作成

• テーブル作成や更新等の権限を付与する必要がある為別でサービスアカウントを用意する
• ロール作成し、その後をサービスアカウントを作成するときに紐づける
• ロールを作成
• 「IAMと管理」から「ロール」へ

最終的に付与した権限は以下の通り

bigquery.datasets.update

bigquery.jobs.create

bigquery.readsessions.create

bigquery.readsessions.getData

bigquery.tables.create

bigquery.tables.get

bigquery.tables.getData

bigquery.tables.update

bigquery.tables.updateData

bigquery.transfers.get

bigquery.transfers.update

• サービスアカウント作成
  • 先ほどのロールを紐付け

定期実行用のクエリを作成

これまでのデータを全取得したテーブルを生成

• クエリの新規作成
  • まずは今日までのデータ全てをクエリで取得
• 取得した結果をデータセットに保存

定期実行用のクエリをセット

https://cloud.google.com/bigquery/docs/scheduling-queries?hl=ja

• 該当データがない場合は空のテーブルが作成される

スケジュールの設定

• スケジュールの設定

「スケジュール」→「スケジュールの新規作成」

• 繰り返しの頻度: 毎日
• 設定した時間に開始を選択して開始日時を設定（以降はその時間に毎日実行される）
• 宛先のテーブルID設定
• サービスアカウントは作成したものを指定

Glueを走らせてS3へデータが保存されるか試してみる

• RUNでジョブを実行

QuickSightについて

QuickSightのアカウント作成（初期セットアップ）

• S3へのチェックが入っていないが、今回はS3に吐き出されたCSV形式のデータにアクセスする必要があるので最初からチェックを入れておいても良い
• 入れていないと、自動で作られるロールにS3のバケットのアクセス権限が付与されていない状態なので追加で付与してあげないとS3にアクセスできないので注意。

セキュリティーグループの作成

VPC外リソースのQuickSightがプライベートサブネットにいるRDSに接続できる理屈

こちらのクラスメソッドさんの記事が非常に参考になります。

https://dev.classmethod.jp/articles/aurora-quicksight/

QuickSight用ENIセキュリティグループの作成

• インバウンド/アウトバウンドのソースにはrdsにアタッチしているセキュリティーグループを選択

Auroraクラスターセキュリティグループルールの追加

• 既存のRDSにアタッチされているセキュリティーグループのインバウンドルールに先程作ったセキュリティーグループのIDを追加する

VPC接続管理

• QuickSightコンソール>Account>QuickSightの管理>VPC接続管理
  • RDSと同じVPCにあるPrivateSubnetを選択する
• サブネットが3つあるのでどれか一つを選択(AZの1aを選択)
• 先程作成したQuickSight用SGを選択する

QuickSightのアカウント生成時に自動作成されたIAMにS3バケットへのアクセス権限を追加する

• 自動追加されているロール名
  • aws-quicksight-service-role-v0

QuickSightの単位

分析

• ダッシュボード

データセット

• データソース(RDSのデータやS3のCSVデータなど)を元にして作成される概念

データソース

• データソースはRDBのデータであったりGlueでS3に吐き出したデータなど

データセット作成

• データソース(RDSのデータやS3のCSVデータなど)を元にして作成される概念
• QuickSightのトップ画>右上「新しい分析」>「新しいデータセット」> データソース一覧からソースを選択して作成
• 新たにデータソースもセットで作成する場合はデータセット作成時にどの新規データソースから作成するのか選択してからデータセットを作成する
• 既存のデータソースを選択してから作成する場合は既存のデータソースリストから選択して作成する

• RDSをデータソースとして作る場合
  • 接続のためのコネクタとして先程作成したQuickSight用のセキュリティーグループを選択

  して、データベース名、ユーザー名、ホスト名、パスワードなどを入力して繋ぎ込む

  • その後、どのテーブルのデータを表示するか選択する

• S3をデータソースとして作る場合
  • マニフェストファイルという設定ファイルをアップロードすると指定のバケットのデータにアクセス可能となる
  • Glueで取り込んだS3上のCSVデータが格納されているS3バケットを指定する

{
  "fileLocations": [
    {
      "URIPrefixes": [
        "s3://${S3のバケット名}/"
      ]
    }
  ],
  "globalUploadSettings": {
    "format": "CSV",
    "delimiter": ",",
    "textqualifier": "'",
    "containsHeader": "true"
  }
}

• データセット作成時にデータソースもセットで作成するとデータソース一覧にデータソースが追加されている

データセットやデータソースの追加方法

• データソースは最初に元になるDBのテーブルを１個だけ選択して作成（後から結合したいテーブルがあれば結合していく）今回結合するときはすべてleft outer joinで結合した。

• データを追加（ダッシュボード上）

• データセットを追加してデータセット同士の結合、データソースとの結合も可能

その他QuickSight上での基本操作

• 結合も簡単に可能
• カラムを選択して一覧化
• csvでエクスポート可能

ダッシュボードでデータをみる

作ったデータセットやデータソースをダッシュボード上で読み込んであげればあとは煮るなり焼くなり可視化は可能になる

いかがでしたでしょうか？？何かのお役に立てば幸いです。