LoginSignup
2
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

@jnitin株式会社BeeX

QuickSightからBigQueryを直接参照する

Last updated at Posted at 2023-03-07

2023/11/22追記

2023/11/20にQuickSightがGoogle BigQuery への接続をサポートするようになりました。
https://aws.amazon.com/jp/about-aws/whats-new/2023/11/amazon-quicksight-connectivity-google-bigquery/

そのため、本記事は良い意味で不要となりました。

こんな感じ

データセットでデータソース名（任意名）、プロジェクトID、データセットリージョンを入力します。
image.png

ブラウザの別ウインドウが立ち上がるのでGoogleアカウントを選びます。
従来のAthenaコネクタと比べて、Google サービスアカウントや、サービスアカウントの鍵が不要になっている！
image.png

画面が遷移するので、Google側での認可を許可します。これは楽ちん！
image.png

BigQueryがいとも簡単に参照できます。
image.png

SPICE利用前提でデータが読み込まれるようです。
image.png

とりあえず、新機能の報告は以上です。

以下は、元記事です。

はじめに

AWSのBIサービスであるQuickSightは、残念ながらデータソースとしてGoogleのBigQueryをサポートしていません。
以下がQuickSightで選択できるデータソースです(2023年2月4日現在)。
https://docs.aws.amazon.com/quicksight/latest/user/supported-data-sources.html
image.png

QuickSightがGoogleCloudのBigQueryやAzureのSynapse Analyticsなどをサポートしてないあたり、クラウドプラットフォーマーとしてのAWSのライバル意識を強く感じます。しかし各クラウドプラットフォーマーのデータ分析基盤関連サービスは、それぞれ得意領域が微妙に異なるため、ユーザサイドにおいてはマルチクラウドで組み合わせて使うことが最近は増えてきました。願わくばQuickSightにBigQueryへのコネクタを追加して欲しいものですが、無いものは仕方ないので、なんとかやってみることにしました。

AWSとBigQueryの連携というと、大きく２つのアプローチがあると思います。
まずはそれを整理していきます。

直接参照しないアプローチ

QuickSightからBigQueryに直接参照しないアプローチとしては、何らかの方法でBigQueryからS3にデータをコピーする方法が一般的です。

BigQueryへのクエリ対象のデータ量が多い場合などは、QuickSightに位置的に近いAWSサービス(S3,Redshift等)に置くことで、柔軟な分析も可能になり、クエリ速度も現実的なレベルで安定すると思います。
もちろん最初の転送は時間がかかりますが、リアルタイム性が必要でない場合はS3に置くほうがよいと思います。

  • BigQueryからS3への転送パターンは、大体以下のようなアプローチが選択肢になるかと思います。

  • AWS側ではS3にデータを置いた上で、以下の２パターンがよくあるデータパイプラインになると思います。

    • Glueクローラをかけカタログ化した上で、QuickSightからデータソースとしてAthenaを指定し参照する。
    • S3データをRedshiftに読み込ませ、QuickSightから参照する。

直接参照するアプローチ

ここからが本記事の本題です。
QuickSightからBigQueryに直接するアプローチです。しかし直接参照といっても、実際には間にLambdaが介在します。
以下で詳しく説明していきます。

本例における構成

image.png

※なお、本記事の後部にVPCを使わないパターンもあります。

本例で利用する機能

  • Lambda(AthenaGoogleBigQueryConnector)

    • AthenaGoogleBigQueryConnectorは、AthenaのFederated Queryがサポートするコネクタの１つで、OSSのため無償で利用ができます。
    • コネクタといっても実体はLambda(Java)です。クエリはLambdaの15分制限を受ける点に注意してください。
    • コードはgithubのリポジトリで確認することができます。
    • Federated Queryはデプロイ難易度が少し高くデバッグもしづらいため、AWSの中級者以上向けです。
      • デプロイ自体はLambdaアプリケーションのウィザードに従うことでCloudFormationがキックされるシンプルな手順になっていますが、入力するパラメータが若干、初見ユーザに厳しい感じが否めないです。

  • AWS Secrets Manager

    • GoogleCloudのサービスアカウントを保存しておくために利用します。
    • LambdaからSecretsを取得し、BigQueryにアクセスする形になります。

  • S3

    • クエリ実行時のデータ量がLambda関数の制限を超えた(spill)場合に利用される領域です。
      • 実際にどのようなケースで出力されるかは、私は確認していません。
        • Lambdaの実行時間15分間制限以内で発生したスピルデータが保存されると思われます。

  • VPCやNAT Gateway

    • この構成で最も謎なポイントがこの2つです。構成上、LambdaはVPC内に置かなくてもアクセス権限さえあればSecretsManagerやS3にはアクセスできるはずです。しかし数少ないAthenaGoogleBigQueryConnectorの事例がどれもVPC内Lambda+ NAT Gatewayの構成となっているため、とりあえず動かすためにLambda用のVPCと、インターネット接続のためのNAT Gateway、さらにVPCエンドポイントを作っておきます。
      • 後ほど、VPCを用いないパターンも試してみたいと思います。

  • Athena

    • AthenaからFederated Query(Lambda)を通してBigQueryにクエリをかけます。
      • 事前セットアップとして、Athena上ではカスタムコネクタ用にデータソースを追加する必要があります。

  • QuickSight

    • QuickSightからデータソースとしてAthenaを指定して、Lambda(AthenaGoogleBigQueryConnector)を呼び出します。
    • AthenaでBigQueryにクエリできるなら、QuickSightからもいけるんじゃね？という軽いノリでアクセスを試してみます。
      • 結果としては、できはするものの、IAMロールの設定にコツと注意ポイントが存在します。

注意事項

  • 何よりもまず、クエリが15分以内で完了しないとクエリ自体が失敗するという制約が重要です。

    • 可能な限り小さなテーブルを対象にして、where句で可能な限り対象を限定してください。
      • BigQueryの場合、述語プッシュダウン(predicate-pushdown)が効きます。AWS側で作成したクエリの述語部分、つまりwhere句で絞った範囲はBigQuery側でフィルタをかけてくれて、フィルタ済みのフィールドのみネットワーク越しでAWSに返してくれます。

  • ビューに関しては、フェデレートされたデータソースでビューを使用することはできません。

  • その他の制約は公式ページをご確認ください。

本例の検証環境

  • Quicksight
    • Enterprise Edition
      • StandardEditionでも問題ないと思われますが、Enterprise Editionで検証しています。
  • AthenaGoogleBigQueryConnector
    • version 2023.6.1

手順

構築手順を以下に記載します。

GoogleCloud

BigQuery

BigQueryには、QuickSightからクエリをかけたいテーブルがすでに存在しているものとします。
私はkaggleのデータを読み込ませています。
https://www.kaggle.com/datasets/gregorut/videogamesales?select=vgsales.csv

GoogleCloudにログインし、対象のテーブルのプロジェクト名を確認しておきます。

image.png

ServiceAccount

AthenaからBigqueryにアクセスするために、GoogleCloud側のクレデンシャルを作成していきます。
まずサービスアカウントを作成します。これはAWSでいうところのIAMロールのようなものです。

「IAMと管理」から「サービスアカウント」を選択し、サービスアカウントを作成します。
image.png

「サービスアカウント名」と「サービスアカウントID」を入力し、「作成して続行」を選びます。
　※ここでは私が以前glue用に作ったサービスアカウントのキャプチャを使用していますが、今回はGlueは使いません。
image.png

サービスアカウントに付与する権限を選択します。ロールに「BigQuery管理者」を選択し「完了」を押します。
image.png

サービスアカウントが作成されたら、対象のサービスアカウントを選択後、「キー」タブから「鍵を追加」→「新しい鍵を作成」を選びます。
image.png

キーのタイプは「JSON」を選択し、「作成」を押します。
image.png

するとブラウザからjsonファイルがダウンロードされますので、適切なフォルダに保管しておきます。
image.png

AWS VPC周り

AWSのマネジメントコンソールにログインし、データソースコネクタ用の VPC を作成します。
以下Federated Query用の案内ページに作成方法が書いてあります。
https://docs.aws.amazon.com/ja_jp/athena/latest/ug/athena-connectors-vpc-creation.html

特殊な部分はなくVPCとサブネットとSGを普通に作る内容となっているため、細かい手順は割愛しますが、ポイントだけ以下に説明していきます。

VPC

とにかく素直にVPCを作ります。私は既存のVPCを使うことにしました。プライベートサブネットと、パブリックサブネット構成となっているものです。

少し脱線します。
VPC内リソースへのアクセスが不要にもかかわらず、Athena Federated Queryの標準的な手順ではVPCを作る形になっているのはなぜかという疑問が湧きます。以下ページに、なにやらほのめかされています。
https://docs.aws.amazon.com/ja_jp/athena/latest/ug/athena-connectors-vpc-creation.html

一部の Athena データソースコネクタには VPC とセキュリティグループが必要です。

では今回使う[Amazon Athena Google BigQuery コネクタ]が「一部」の対象なのか、コネクタの案内ページを見てみます。
https://docs.aws.amazon.com/athena/latest/ug/connectors-bigquery.html

このコネクタを使用する前に、VPC とセキュリティ グループを設定します。

つまり今回のコネクタはVPCの作成対象のようです。
他のコネクタを見ていると、一般的にAWS外のサービスと接続するコネクタはVPC作成の指示が記載されているようです。
https://docs.aws.amazon.com/athena/latest/ug/connectors-prebuilt.html
VPNやDirectConnectなどで閉域アクセスさせるユースケースが多いからVPC前提なのかもしれません。
このへん、私は他のFederated Queryをまともに使ったことがないのでよくわかりません。

SG(Security Groups)

この時点で必要性がわからないのがもう１つ、セキュリティグループ(SG)です。Athenaのコネクタ作成案内ぺージではSGを作成する理由も、何にアタッチするSGなのかも書いてありません。書いてあるのはひとこと、
https://docs.aws.amazon.com/ja_jp/athena/latest/ug/athena-connectors-vpc-creation.html

必要なインバウンドルールとアウトバウンドルールを追加します。

という突き放した記述にとどまっています。結論としてこれは後に作成するLambdaのVPC用NICに付与するSGになっています。
しかしSGを設定するにあたり、VPC LambdaをキックするのはAthenaなのか、QuickSightなのか、プライベートIPを使ってくれるのか、など疑問が沸き起こります。
とりあえず私は個人検証環境VPCのプライベートサブネットにLambdaの足を出すつもりなので、あまりシビアには設計せずインバウンド、アウトバウンドともにフルオープンのSGを作成することにしました。

image.png

NAT Gateway

これもまた存在意義が謎なのですが、以下の公式資料「Amazon Athenaであっちこっちのデータを一括分析しよう」のP.14ではNAT Gatewayがパブリックサブネットに配置されています。

Lambdaをプライベートサブネットに配置していることから、Lambdaがインターネット接続をするためのNAT Gateway設置のようです。
接続先がインターネット越しのBigQueryなら、最初から非VPCのLambdaにすべきでは、という疑問がまた頭をもたげてきます。
しかし検証としてはまず動く状態に持っていくことが重要なので、素直に(会社経費に月額数千円のしかかることにちょっぴり心を痛めながら)パブリックサブネットにNAT Gatewayを立てて、プライベートサブネットのルートテーブルに経路を通しておきます。

image.png

VPC Endpoint

  • VPCエンドポイントを２種類作り、先程のVPCのサブネットにルートを設定していきます。

    • S3用(gateway型)
    • Secrets Manager用(Interface型)

  • S3用のVPCエンドポイントについては、せっかくVPC内にLambdaの足を出させるのだから、LambdaからS3へのアクセスも閉域接続で完結してほしいので作成します(実際に今回のLambda関数がS3アクセスにルートテーブルを参照するプログラムコードになっているかは不明なのですが)。Gateway型を作成しプライベートサブネットに紐づけるだけですので、手順は割愛します。公式の以下ページに記載されています。

  • SecretsManager用のInterface型VPCエンドポイントは費用もかかるので心理的に作りたくない気持ちがあるのですが、Athenaのデータソースコネクタの案内ページを見ると以下のように「作れ」と書いてあります。
    https://docs.aws.amazon.com/ja_jp/athena/latest/ug/connectors-prebuilt.html

AWS Secrets Manager で Athena 横串検索機能を使用するには、Secrets Manager に Amazon VPC プライベートエンドポイントを設定する必要があります。詳細については、「AWS Secrets Manager ユーザーガイド」の「Secrets Manager VPC プライベートエンドポイントを作成する」を参照してください。

「FederatedQuery」を和風に「横串検索」と翻訳する公式のセンスに頬が緩みかけますが、金食い虫のInterface型エンドポイントを作成する理由も書いてくれない冷たい態度は大変残念で、真顔に戻らざるを得ません。

  • 仕方ないので素直に作成し、プライベートサブネットのルートテーブルに紐づけます。Lambdaの足をマルチAZで出す場合は、VPCエンドポイントもマルチAZにしてください。一般的な手順は以下の通りです。

  • ここで少し悩ましいのが、SecretsManager用のVPCエンドポイントのセキュリティグループです。

    • Lambdaからシークレット情報へのアクセスなので、ソースはLambdaを配置するプライベートサブネットまたはVPCのCIDRを指定する程度でよいと思います。
    • タイプ、プロトコル、ポートは、SecretsManager APIの受け口になるので厳密には HTTPS/TCP/443 を指定すればよいと思いますが、個人的にVPC内でそこまで絞る理由もないと思うので、それぞれ「すべて」でもよいと思います。

AWS SecretsManager

GoogleCloudのサービスアカウントのクレデンシャルを登録していきます。

もしクレデンシャルが単純なID/パスワードの形式であったならSecretsManagerに登録する必然性もなかったとは思うのですが、GoogleCloudのクレデンシャルはjson形式なのでパラメータとして指定しづらいのでしょう、後に作成する「AthenaGoogleBigQueryConnector」の作成時パラメータでSecretsManagerのSecretsを指定する仕様になってしまっているので、素直に登録していきます。

Lambdaを配置するVPCと同じリージョンのSecrets Managerコンソールに行き、「新しいシークレットを保存する」を選択します。
image.png

シークレットのタイプに「その他のシークレットのタイプ」を選択し、「プレーンテキスト」タブを選択します。
エディットボックスの中に、先程GoogleCloudコンソールからダウンロードしたファイルの中身を、そのままjson形式で貼り付けます。
image.png

  • 普通に上記の手順に沿えば全く問題は起きないのですが、私は最初、過去に作成したGoogleCloud用の別のSecrets(base64エンコード)を流用したため、Athenaからつながらず以下のエラーでデバッグに半日を費やしました。ですので加工をせずそのままプレーンテキストでjsonを貼り付けてください。
    • Failed to invoke lambda function due to com.amazonaws.services.lambda.invoke.LambdaFunctionException: Response was null
    • Error reading credentials from stream, 'type' field not specified.

Secretsの名前は、わかりやすく区別がつきやすければなんでもよいです。私は「test/bigquery/athena」としました。
その他の設定はデフォルトです。
image.png

S3

スピル用バケットを作成します。
スピル用バケットとは、Lambda 関数の上限を超えたデータを保管するためのS3バケットです。

バケット名は何でもよいです。設定もデフォルトのままでOKだと思います。
QuickSight環境と同一のAWSアカウント、同一のリージョンだと設定が楽になると思います。

image.png

AthenaGoogleBigQueryConnectorのデプロイ

では準備が整ったので、コネクタのデプロイを行います。
AWSマネジメントコンソールからLambdaを開き、左メニューの「アプリケーション」より、「アプリケーションの作成」を選択します。

image.png

「サーバーレスアプリケーション」 -> 「パブリックアプリケーション」を選択します。
「カスタムIAMロールまたはリソースポリシーを作成するアプリを表示する」にチェックを入れます。
検索ボックスに「big」と入力すると、「AthenaGoogleBigQueryConnector」がリストアップされるので、コネクタ名をクリックします。

image.png

デプロイのセットアップ画面に遷移します。アプリケーションのバージョンや、githubのソースコードURLなども確認できます。
右下のほうにパラメータ入力画面が表示されます。

image.png

[アプリケーションの設定]パラメータ

以下に公式の説明がありますが、やや記載内容が古いようです。
https://docs.aws.amazon.com/ja_jp/athena/latest/ug/connectors-bigquery.html

以下は本記事執筆時点(2023/2/14時点)のパラメータです。コネクタのバージョンアップにより変化すると思われます。

  • アプリケーション名

    • デフォルト値：AthenaGoogleBigQueryConnector
    • 設定例：AthenaGoogleBigQueryConnector
    • 説明
      • Lambdaアプリケーションとして登録される名前です。既存のLambdaアプリケーションがあれば、それと区別しやすい名前をつけます。
        • なお、Lambdaの関数名は後続のパラメータで指定します。

  • SpillBucket

    • デフォルト値：(無し)
    • 設定例：your-bucket-name
    • 説明
      • Lambda 関数の上限を超えたデータを保管するためのS3バケット名を指定します。ARNやS3 URLではなくバケット名だけでOKです。事前に作成したバケットを指定しておきます。

  • BigQueryEndpoint（オプション）

    • デフォルト値：(無し)
    • 設定例：your-private-domain-name
    • 説明
      • BigQuery Private Endpointを指定します。AWSとGoogleCloudをインターネットVPN等で閉域接続していて本通信も閉域化したい場合に使います。2022年11月末に実装されたパラメータです。
      • GoogleCloudのPrivateServiceConnectのエンドポイントは、インターネット上のDNSからだとグローバルIPが複数返って来てしまいます。
        • このパラメータにドメイン名を入れるのであればAWSとGoogleCloudで名前解決の連携設定を行った上で、ドメイン名をプライベートIPに紐づける必要があると思います。私は試していません。プライベートIPを入れた場合、動くかどうかは未検証です。

  • ConcurrencyLimit

    • デフォルト値：10
    • 設定例：10
    • 説明
      • クエリ結果を並列取得するためのパラメータです。デフォルトで10並列になります。Lambdaがタイムアウトする場合などに、(GoogleCloudプロジェクト全体の上限値を気にしつつ)上げるとよさそうです。
      • コード上のコメントを見るとパーティショニングには対応していないようです。シンプルに行数などの値を元にして相対位置(オフセット)を計算して、１クエリの結果を複数のSQL文に分割して並列取得しているようです。
      • 上限値はBigQuery側で制限されるようです。どれが対応するか調べてもわかりませんでしたが「同時実行インタラクティブ クエリの最大数」だとするとデフォルト100、最大値も100で、上限緩和不可、クエリキューの使用により1000まで同時実行可能、と思われます。（知ってる方教えてください）

  • DisableSpillEncryption（オプション）

    • デフォルト値：false
    • 設定例：false
    • 説明
      • True に設定されている場合、スピルデータに対する暗号化を無効にします。スピル暗号化を無効にすると、スピル発生時にS3バケットへの出力パフォーマンスが向上します。
        • S3にスピルアウトするくらいデータが巨大で、ともかく早く処理させたい場合は、セキュリティのトレードオフ次第でTrueにするという手がありそうです。

  • GCPProjectID

    • デフォルト値：-
    • 設定例：your-google-cloud-project-id
    • 説明
      • コネクタの読み取り対象となるデータセットを含むプロジェクト IDを指定します。
      • 先程確認したGoogleCloudのプロジェクトIDを指定します。

  • LambdaFunctionName

    • デフォルト値：-
    • 設定例：athena-bigquery-func
    • 説明
      • 他のLambda関数と区別しやすい名前をつけます。

  • LambdaMemory

    • デフォルト値：3008
    • 設定例：3008
    • 説明
      • Lambda関数に割り当てるメモリ量(MB)です。
      • デフォルトの 3,008 MBというのは、おそらく2020年11月頃までのLambdaのメモリ上限です。
      • Lambdaは2020年12月頃から上限が10240 MBになりました。このjavaコード上でバリデーションがなければ3009以上指定できるでしょうし、このパラメータで指定できなかったとしても、関数生成後に手動でLambda関数の設定値を変えてもよいと思います。データが多すぎてS3にスピルアウトされるようであればメモリを増やすというのも１つの手だと思います。メモリ量に応じてLambdaの料金が変わってきますので注意してください。

  • LambdaTimeout

    • デフォルト値：900
    • 設定例：900
    • 説明
      • Lambda関数のタイムアウト時間(秒)です。
      • 900秒(15分)がデフォルトのため、最大値が指定されています。これは変更する必要はないと思います。

  • PermissionsBoundaryARN（オプション）

    • デフォルト値：-
    • 設定例：-
    • 説明
      • 作成された Lambda 関数の実行ロールの PermissionsBoundary として使用する IAM ポリシーのARNを指定します。
      • [AthenaGoogleBigQueryConnector]はLambdaのIAMロールを自動生成します。リソース名までかなり限定されたIAMポリシーが付与されるのですが、さらに独自にアクセス対象を絞りたい場合は、事前にバウンダリー用のIAMポリシーを作成しておき、ARNを指定します。
      • IAMロールをざっとみた感じ、個人的にはそれ以上絞る必要もないようにみえました。

  • SecretNamePrefix

    • デフォルト値：-
    • 設定例：test/bigquery/athena
    • 説明
      • SecretsManagerに保存されているGoogleCloud用のシークレット名を指定します。

  • SecurityGroupIds

    • デフォルト値：-
    • 設定例：sg-XXXXXXXXXXXXX
    • 説明
      • Lambda関数に付与するセキュリティグループIDを指定します。複数ある場合はカンマで区切ります。

  • SpillPrefix（オプション）

    • デフォルト値：athena-spill
    • 設定例：-
    • 説明
      • スピルバケット内のサブフォルダ名を指定します。ライフサイクルで数日あるいは数時間経過後のデータを削除するように推奨されています。

  • SubnetIds

    • デフォルト値：-
    • 設定例：subnet-AAAAAAAAAAAAAAAAAAA,subnet-BBBBBBBBBBBBBBBBBB
    • 説明
      • Lambdaを設置するVPCのサブネットを指定します。複数ある場合はカンマで区切ります。

私は以下のような設定パラメータを指定しました。
image.png

上記パラメータを任意な設定値で入力し、「このアプリがカスタムIAMロールを作成することを承認します。」にチェックをいれ、「デプロイ」をクリックします。

image.png

すると画面が遷移し、デプロイプロセスが始まります。「デプロイ」タブをクリックすると、現在のデプロイ状況がわかります。裏でCloudFormation(CFn)が動いており、「スタックイベントを表示」を押すことでCFnの詳細を見ることができます(割愛)。パラメータに問題がなければ数分以内でデプロイは完了します。
image.png

ステータスが「Create complete」となったら「概要」タブに戻ります。画面下部には作成されたリソースが表示されます。
　Lambda関数と、それに自動付与されたIAMロールが作成されていることがわかります。
青文字リンクをクリックするとLambdaやIAMロールなど画面に遷移しますが、設定の追加は必要ないので本手順としては次のAthenaの設定に移ります。
image.png

Athena

  • Athena側の設定は以下の２つを実施します。
    • Athenaエンジンバージョンの確認・変更
    • データソース（カスタムコネクタ）の追加

Athenaエンジンバージョンの確認・変更

Athena Federated Query は、Athena エンジンバージョン 2 以降でのみサポートされています。
Athenaエンジンは、通常意識することはありません。AthenaのWorkgroupに紐づいた設定になっています。

Amazon Athena の横串検索機能を使用するには、ワークグループを Athena エンジンバージョン 2 に設定します。

AthenaのWorkgroupはクエリに制限などをかけられる機能です。
デフォルトでは「primary」というworkgroupが存在しています。

workgroupのバージョンを確認するには、Athenaコンソールの左メニューから「ワークグループ」を選択し、Federated Queryを実行するworkgroupの「分析エンジン」列を確認してください。

image.png

デフォルトでエンジンバージョンが自動アップグレードされるようになっているため、本記事執筆時点ではVersion 2か、あるいは3になっているかと思います。もしバージョンが1である場合は、確認のうえ、アップグレードを行ってください。

データソース（カスタムコネクタ）の追加

  • 先程デプロイしたLambda関数をAthenaから参照する場合は、専用のカスタムコネクタを事前に作成し、ソースとしてLambdaを指定する必要があります。

Athenaのコンソール画面より、「データソース」から「データソースの作成」を選択します。
image.png

データソースは「カスタムまたは共有コネクタ」を選択します。

image.png

任意のデータソース名を指定し、Lambda関数には先程デプロイされた関数を指定します。

image.png

内容を確認しデータソースの作成をクリックします。

image.png

AthenaからBiqQueryへのクエリ実行

では AthenaGoogleBigQueryConnector を使ってクエリを投げてみたいと思います。
Athenaのクエリエディタに戻り、データソースに先程作成したカスタムコネクタのデータソース名を指定します。

カスタムコネクタを選択した時点で、裏ではLambdaが起動し、BigQueryにデータベース名リストアップ系クエリが発行されます。ここでLambdaがDB名を取得するまでの数秒間を待つことになります。

接続が成功すると「データベース」欄にBigQuery側のデータセット名が選択できるようになります。

Athenaデータソースのカスタムコネクタは、FROM句にてデータソース名をダブルクォーテーションで囲むことで指定できます。

"(Athenaデータソース名)".(Bigqueryのデータセット名).(Bigqueryのテーブル名)

Select系クエリ

今回の例では、以下のようなクエリになります。

SELECT * FROM "athena-bigquery-src".glue_connector_test.vgsales;

image.png

1600件程度の小さなテーブルの表示で約13秒かかりました。
何度か連続で同じクエリを実行してみましたが、11秒～14秒程度でしたので、Lambda用NICの起動オーバーヘッドは大きく影響しないのかもしれません。

CREATE VIEWクエリ

  • 今度は試しにAthena上でViewを作ってみます。
    • image.png
    • View作成はエラーで失敗しました。
      • line 1:50: extraneous input '"athena-bigquery-src"' expecting {, ',', 'FROM', 'WHERE', 'GROUP', 'ORDER', 'HAVING', 'LIMIT', 'LATERAL', 'WINDOW', 'UNION', 'EXCEPT', 'INTERSECT', 'SORT', 'CLUSTER', 'DISTRIBUTE'}
    • 通常、AthenaのView作成はデータソースに「AwsDataCatalog」を指定した場合は可能です。「AwsDataCatalog」の場合はGlueテーブルとしてViewが作成されますが、今回のようなカスタムコネクタの場合はデータソース側へのCreateが認められていないため、Viewを保持する領域がなくエラーになるものと思われます。
    • Viewと同様のことをしたい場合は、AthenaではなくQuickSight側でカスタムクエリ、またはフィルタでの対応になろうかと思います。

Bigqueryに接続できない場合

なお、BigQueryへの接続情報が間違っている場合などは、Athenaのデータソースとしてカスタムコネクタを指定した時点でエラーが発生します。
image.png

  • 私の場合のエラーは先に記載したように、SecretsManagerに登録した接続情報に間違ってbase64エンコードをかけていたことが原因ですが、以上のように画面上は詳細なエラーを吐いてくれません。
    • エラーが発生した場合は、Lambda関数が出力したCloudWatchLogsを見て、JavaのExceptionに出力されたエラー行番号からgithubのコードを追って推論を重ねる形になると思います。
    • 認証系の処理はawsリポジトリのコードではなく、googleリポジトリのコードを参照する必要があります。エラーを吐いているコードのパッケージがaws側なのかgoogle側なのか確認しつつデバッグをしてください。
    • 私はgoogle側のエラー文字列と、Googleのサービスアカウントのjsonの中身を比較してようやく問題に気づきました。 
{
  "type": "service_account",
  "project_id": "xxxxxxx-test01",
  "private_key_id": "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX",
  "private_key": "-----BEGIN PRIVATE KEY-----\nMIIEv(snip)Q=\n-----END PRIVATE KEY-----\n",
  "client_email": "bigquery-glue-test@xxxxxxx-test01.iam.gserviceaccount.com",
  "client_id": "xxxxxxxxxxxxxxxxxxxxx",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://oauth2.googleapis.com/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/bigquery-glue-test%40xxxxxxxx-test01.iam.gserviceaccount.com"
}

QuickSight

AthenaからBigQueryへのクエリが成功したので、今度はQuickSightからデータソースをAthenaに指定することで、QuickSightからBigqueryへの直接クエリができるか試してみます。

VPC Lambda利用における懸念

今回の構成ではQuickSightからVPCのLambdaを利用する形になります。
通常、QuickSightからVPC内のリソースにアクセスするには、QuickSightのEnterpriseEditionが必要になります。EnterpriseEditionでは「VPC 接続の管理」という機能で対象のVPC/サブネット/SGを指定することができます。

結論から先に申し上げると、FederatedQueryの利用を目的にした「VPC接続の管理」は不要です。
私はEnterpriseEditionを利用しており、StandardEdition利用における他の制約までは確認していませんが、「VPC接続の管理」の設定を削除しても、後述するようにQuickSightからLambdaを叩くことができました。

内部でどのような動きをしているかわかりませんが、挙動から考えるとFederatedQuery利用の場合、QuickSightとLambdaが直接TCP/IP通信を行っているわけではないようです。(SQLインターフェースとしてQuickSightがAthenaを使っているからかもしれません)

QuickSightが利用するIAMロール

QuickSightからAthena Federated Queryを利用するためには、もうひと手間が必要になります。
※このあたりの手順は、本記事執筆時点で公式にも情報が記載されていないように見えます。

  • QuickSightはデータソースにアクセスする際、デフォルトで複数のIAMロール（サービスリンクロール）を利用します。本記事執筆時点で、私の環境で確認できたQuickSightが利用するIAMロールは以下の3つです。

これらのIAMが使われているかどうかは、「QuickSightの管理」画面の「セキュリティとアクセス権限」から確認できます。「使用中のIAMロール」として「QuickSight で管理されるロール (デフォルト)」が表示されていれば、上記IAMロールが利用されています。

image.png

上記のような表示となっている場合、先に記載した３つのIAMロールが、データソース毎に自動的・選択的に使い分けられるようです。

IAMロールにFederatedQuery用の権限を追加する

FederatedQueryを使えるようにするにはIAMロール [aws-quicksight-s3-consumers-role-v0] に、権限を追加する必要があります。ただしその権限をIAMの管理画面から自由に追加してしまうと、QuickSight側で検知されてしまい以下のように怒られて先に進めなくなります。

image.png

そのためQuickSightの管理画面から設定を行っていきます。
「セキュリティとアクセス権限」から「管理」を選択します。

image.png

表示されるサービスから、「Amazon Athena」にチェックを入れます。すでにチェック済みの場合は一度チェックを外し、再度チェックを入れます。
image.png

チェックを入れると以下のようなダイアログがでますので、「次へ」をクリックします。

image.png

まずLambda関数の権限を追加します。Lambdaタブをクリックし、今回追加したLambda関数にチェックをいれます。
image.png

次にS3タブを選択し、先程作成したSpillデータ格納用のS3バケットにチェックをいれ、「完了」を押します。
image.png

  • スピル用S3バケットは忘れずにチェックしてください。
    • チェックしないと、データセット作成後、実際にQuickSightがデータをロードする段階（SPICE読み込み時など）で以下のようなエラーが発生します。S3バケットへの出力有無に関わらず、アクセス可否をチェックしているようです。
      • Access Denied (Service: Amazon S3; Status Code: 403
    • なおSPICE読み込み時に失敗すると、デフォルトではQuickSight管理者に「QuickSight データセットを更新できませんでした」という件名のエラーメールが送信されます。

最後に「保存」をクリックします。以下のようにオレンジ色の文字で留意事項が表示されます。今回はクロスアカウントを使っていないので、特に気にせず進めます。
image.png

  • 上記作業により、IAMロール[aws-quicksight-s3-consumers-role-v0]にアタッチされているポリシーのうち、Lambda用とS3用のポリシーに、それぞれLambdaの関数のinvoke権限と、S3バケットへのアクセス権限が追加されます。
    • AWSQuickSightLambdaPolicy
    • AWSQuickSightS3ConsumersPoicy

データセットの作成

では準備が整ったので、QuickSightからAthena FederatedQueryをソースに設定していきます。
「データセット」から「新しいデータセット」を選択します。

image.png

データセットが利用するデータソースはAthenaを選択します。Athenaのものであれば、既存のデータソースでもいいですし、新規でもどちらでもOKです。
image.png

次に「カタログ」のリストから、先程Athenaのセットアップで作成したAthenaカスタムコネクタのデータソース名を探して選択します。
すると裏でLambdaがキックされ、正常にいけば5秒くらいでBigQuery側のデータセット名が見えるようになります。
この例ではAthena「データベース」に、私のBigQuery上のデータセット「glue_connector_test」を選択します。
image.png

QuickSightで参照したいテーブル名を選択し、「選択」をクリックします。
image.png

あとはSPICE利用有無などを選びます。
FederatedQueryを利用する場合は、多くの場合距離的に遠い場所にアクセスを行う形になります。特に分析で利用する場合はSPICEで高速化を図ることでようやく実用に耐えられる操作感になると思います。

「Visualize」または「データの編集/プレビュー」で中身を見ることができます。
ここでは「Visualize」を選んでみます。
image.png

最近分析の初期アクセス時にこのようなダイアログが出ますが、右の「ページ分割されたレポート」を選択しない限りは、作成でもキャンセルでもどちらを選んでも「インタラクティブシート」が作成されます。
image.png

SPICE利用の場合は初回にSPICEの読み込み待ち時間が少し発生します。100%になるのを待ちましょう。
フィールドにBigQueryのカラムが確認できます。
image.png

適当にドラッグアンドドロップして円グラフを作成してみました。
image.png

これでQuickSightからBigQueryを直接参照するという一通りの目的は果たせました。

その他の検討事項

QuickSightへのデータロード速度

  • SPICE

    • SPICEの利用

      • 前述したようにQuickSightからBigQueryのデータを読み込む際は、よほど小さいテーブルでない限りSPICEの利用をおすすめします。
      • SPICE利用によりリアルタイム性は損なわれますが、QuickSightの分析操作が劇的に早くなります。
      • データ量に応じて購入するSPICE費用がかさみますが、恒常的にBigQueryのデータを参照するのであれば必要経費になると思います。
      • 注意点として、読み込みデータが大きすぎてLambda実行時間の15分を超えてSPICE読み込みが失敗する場合もあると思いますので、ロードが正常に完了するか事前に確認する必要があります。

    • SPICEのデータ更新

    • SPICE使用量の監視

      • 2022年11月からCloudWatchでSPICE使用量がモニタリングできるようになりました。
      • 詳細は以下に記載されています。
        • https://docs.aws.amazon.com/ja_jp/quicksight/latest/user/monitoring-quicksight.html#aggregate-spice-metrics
          • SPICECapacityLimitInMB：購入済み容量(MB)
          • SPICECapacityConsumedInMB：現在の消費量(MB)
        • パーセンテージでアラームを作るには、手順は省きますがMetric Mathを使って簡単な計算式を書いたメトリクスを作成すれば良いと思います。
          • 上記のメトリクスはSPICEを利用したタイミング(SPICE更新や容量変更等)にしかポイントされないため、[INSUFFICIENT_DATA]状態が長く続きます。アラーム状態のときに通知をするようにしてください。
          • Metric Mathでは空の式を作り、以下計算式例のように設定すると良いと思います。
            • (SPICECapacityConsumedInMB / SPICECapacityLimitInMB * 100)
            • 実計算式ではメトリクスをIDで参照しているので、以下例だと [m2/m1*100]になります。
          • image.png

  • [AthenaGoogleBigQueryConnector]のパラメータチューニング

    • Lambda関数の編集
      • コネクタデプロイ時に設定したパラメータは、デプロイ後でも対象のLambda関数側で変更することができます。
        • SPICEへのロードがデータ量によるタイムアウトで失敗する場合などもチューニングが必要になります。
      • 環境変数に指定されているパラメータで、データ参照速度をあげるチューニング要素として考えられるのは以下です。詳しくはデプロイ時の説明を参照してください。
        • DisableSpillEncryption：効果小？
        • ConcurrencyLimit：効果大？
      • image.png
      • 他にはLambdaの「基本設定」内の以下の項目でしょうか。(下図はデプロイ後のデフォルト値です)
        • メモリ：効果大？
        • エフェメラルストレージ：効果小？
      • image.png

VPCを使わないパターン

本記事ではコネクタのLambdaをVPC内に配置するパターンを検証してきました。
記事内ではVPC Lambdaである必然性が感じられないと繰り返し書いてきましたので、実際にVPCの外にデプロイした場合に動作するか試してみます。

image.png

  • 検証にあたり事前に念のため、非VPC版に不要なリソースを削除しました。
    • NAT Gatewayの削除
      • 念のためVPC内に設置したNAT Gatewayを削除して、既存のコネクタでQuickSightからアクセスできなくなることを確認しました。
    • VPC Endpoint(SecretsManager用)の削除
      • 私の場合、本例以外の他の用途で使いませんしコストがかかりますので、削除しました。

手順

  • BigQuery
  • AWS
    • AWS SecretsManagerへのクレデンシャル登録（実施済みのため省略）
    • [AthenaGoogleBigQueryConnector]のデプロイ
      • 以下直リンクから、またコネクタを作成します。
      • 今度はVPCを使わない形でテストしたいので、[SecurityGroupIds], [SubnetIds]は空を指定します。
        image.png
      • これでVPC設定がされていないLambda関数が出来上がります。
      • Lambda関数に付与された自動作成のIAMロールには、以下のポリシーが付与されています。
        • Lambda実行の基礎的なポリシー(AWSLambdaBasicExecutionRole)
        • VPCアクセス用ポリシー(AWSLambdaVPCAccessExecutionRole)
        • SecretsManagerアクセス用ポリシー(AthenaBigQueryConnectorRolePolicy0)
        • CloudWatchLogsグループ作成用ポリシー(AthenaBigQueryConnectorRolePolicy1)
        • CloudWatchLogs出力用ポリシー(AthenaBigQueryConnectorRolePolicy2)
        • S3バケットリスト表示/Athenaクエリ実行用ポリシー(AthenaBigQueryConnectorRolePolicy3)
        • スピル用S3バケット読み書き用ポリシー(AthenaBigQueryConnectorRolePolicy4)
        • VPCへのNIC作成・削除用ポリシー(AthenaBigQueryConnectorRolePolicy5)
      • 上記のとおり、SecretsManagerやスピル用バケットなどのアクセス権限が一通りついているため、VPC外からでも機能しそうです。
    • Athena
      • Athenaエンジンバージョンの確認・変更（実施済みのため省略）
      • データソース（カスタムコネクタ）の追加
      • ためしに新しいデータソースを指定しクエリを投げてみると、正常にBigQueryへのクエリが成功していることがわかります。
        　- image.png
      • 実行速度としては3回実施して 18秒、12秒、11秒という結果でした。試行回数が少ないためなんとも言えませんが、VPC版だと同データで11秒～14秒程度だったので、非VPCはほんのちょっと速いのかも？しれません。いずれにせよNAT GatewayやVPCエンドポイントが不要というのは大きいです。
        　　- image.png
    • QuickSight
      • IAMロールにFederatedQuery用の権限を追加する
        • VPCのときと同じように、追加したLambda関数へのアクセス権限をQuickSightコンソールから追加します。スピル用バケットは、今回はVPCと同じものを使っているため、追加は省略します。
          • image.png
      • データセットを作成する
        • VPCのときと同じように、BigQueryのテーブルが見えました！
        • なお、SPICE読み込みは今回のデータで40秒ほどで完了しました。
          • image.png

非VPC版の結論

以上の検証により、[AthenaGoogleBigQueryConnector]は、VPCの外にデプロイしても動くということがわかりました。

コネクタの削除

  • [AthenaGoogleBigQueryConnector]コネクタを削除するには、Lambdaのアプリケーションメニューから選択し削除を選んでください。
    image.png

リンクをクリックするとCloudFormationの該当スタックに遷移しますので、スタックごと削除してください。これによりLambda関数と、Lambda用のIAMロールが削除されます。(手順省略）
image.png

  • その他、本記事で作成したリソースが不要な場合は手動で削除してください。
    • VPC周り(SG,VPCエンドポイント,NAT Gateway等)
    • S3バケット
    • SecretsManagerのシークレット
    • Athenaデータソース
    • QuickSightのアクセス権限
    • QuickSightデータセット

おわりに

長い記事となってしまいました。

  • このあたりはWeb上に事例がほとんどなく、AWSのQuickSightからBigQueryを直接参照しようと考えている人は少ないのだろうと推測されます。

    • しかしQuickSightのSPICEを利用すれば、リアルタイム性は落ちるものの、クエリ速度の問題は改善できます。
      • 参照するBigQueryのテーブルサイズが、Lambda上限の15分で収まる程度のものであれば、本構成は十分実用的だと感じました。
    • 個人的には、15分制限のLambdaの影響を受けるコネクタのアーキテクチャそのものがナンセンスであり、QuickSightがBigQueryコネクタに対応すれば、このような複雑な設定をする必要もないため、AWSのサービス改善に期待します。(みんなでAWSサポートにどしどし改善要望を出しましょう。本コネクタはOSSですがApache-2.0 ライセンスなので商用利用の壁は低いはずです)

  • データ分析基盤は今後、データメッシュ寄りになっていく可能性もあります。

    • もちろんデータメッシュの普及には使いやすいアーキテクチャの登場が必要ですが、すでに世間的にはマルチクラウド化が進んでいるわけですから、データ分散化を前提とした本例のようなアーキテクチャの手段を用意しておくことは有効だと思います。

  • 本構成のバリエーションとして他に検証してみたいこと

    • AWSとGoogleCloudをインターネットVPNでつなぐ
      • コネクタのオプション[BigQueryEndpoint]を利用しVPN越しのクエリを投げる
    • BigQueryに大きめのテーブルを用意する
      • クエリ速度の変化を調べる
      • where句を使って述語プッシュダウンの効果を調べる
      • S3バケットへのスピルアウトを観測する
      • Lambda15分のタイムアウトを発生させてみる
      • チューニングを試してみる
        • ConcurrencyLimitを増やす
        • DisableSpillEncryptionをTrueにする
        • Lambdaのメモリを増やす

  • まとめ

    • [AthenaGoogleBigQueryConnector]コネクタ
      • BigQueryコネクタのデプロイには最低限、以下の事前準備が必要
        • GoogleCloudのServiceAccount作成
        • AWSのS3バケット作成
        • SecretsManagerへのクレデンシャル登録
      • QuickSightからクエリを実行するには以下の事前準備が必要
        • Athenaデータソースの作成
        • QuickSightのアクセス権限の追加
        • QuickSightデータセット作成
      • BigQueryコネクタでViewを作成することはできない
      • BigQueryコネクタはVPCの内外で正常に機能する

参考にさせて頂いたサイト

以上です。

2
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
2
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?