QuickSightからBigQueryを直接参照する

# 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/

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

- 使い方については以下ブログに記載されています。
    - https://aws.amazon.com/jp/blogs/news/unlock-the-power-of-unified-business-intelligence-with-google-cloud-bigquery-and-amazon-quicksight/
    - ブログでみるかぎり、なにやらEnterprise Edition にしなければいけなさそう。（私はStandardEditionを持っていないので、条件は未確認）
    - Googleのサービスアカウントではなく、ユーザの認証情報を利用するようです。

### こんな感じ

データセットでデータソース名（任意名）、プロジェクトID、データセットリージョンを入力します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/da5e1265-d9cb-4ef4-c923-7b46823646f1.png)

ブラウザの別ウインドウが立ち上がるのでGoogleアカウントを選びます。
従来のAthenaコネクタと比べて、Google サービスアカウントや、サービスアカウントの鍵が不要になっている！
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/3861c320-ca3f-b567-1e6f-97586c36b3a7.png)

画面が遷移するので、Google側での認可を許可します。これは楽ちん！
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/d7230735-81b8-ed9e-8487-cc9a6737674b.png)

BigQueryがいとも簡単に参照できます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/657df38c-0cd2-e979-c7c3-c9b807c56b2f.png)

SPICE利用前提でデータが読み込まれるようです。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/07fc8f96-845d-7f47-d83b-c8323f7f6413.png)

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

以下は、元記事です。

# はじめに

AWSのBIサービスである**QuickSightは、残念ながらデータソースとしてGoogleのBigQueryをサポートしていません。**
以下がQuickSightで選択できるデータソースです(2023年2月4日現在)。
https://docs.aws.amazon.com/quicksight/latest/user/supported-data-sources.html
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/874bdb38-7b56-3545-71c7-4c406dc683bd.png)

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

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

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

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

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

- BigQueryからS3への転送パターンは、大体以下のようなアプローチが選択肢になるかと思います。
  - CLIで頑張るアプローチ
     -  GoogleCloud側でGCSに吐き出し、そこからgsutilコマンドでS3に転送する方法です。
     -  以下のサイトなどが参考になると思います。
        - https://tech.rhythm-corp.com/transfer-bq-data-to-s3-and-run-in-athena/
        - https://techblog.zozo.com/entry/bigquery-to-s3
  - サービスで多少自動化するアプローチ
     - **GlueからBigQueryにアクセスして、S3に吐き出す方法**です。
       - こちらはGlueカスタムコネクタの「AWS Glue Connector for Google BigQuery」を利用します。
       - 以下のサイトなどが参考になると思います。
         - 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/
       - ただし、カスタムコネクタはあくまでGlueのコネクタとして動く１つのパーツに過ぎませんので、データパイプラインとして継続的にデータを取得するには別途、何らかのワークフローツールを使って全体を自動化する必要があります。
         - ワークフローツールは色々ありますがAWSに近いところでいうと「Glue Workflows」か「StepFunctions」になると思います。
           - ワークフローがGlueで完結するのであればGlue WorkFlowsでもよいのですが、QuickSightで可視化する場合は多くの場合SPICE領域の更新処理を入れる必要が出てくると思いますので、StepFunctionsがよいと思います。

- AWS側ではS3にデータを置いた上で、以下の２パターンがよくあるデータパイプラインになると思います。
   - Glueクローラをかけカタログ化した上で、QuickSightからデータソースとしてAthenaを指定し参照する。
   - S3データをRedshiftに読み込ませ、QuickSightから参照する。


## 直接参照するアプローチ

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



## 本例における構成

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/d2864914-4b6a-6dad-a6b6-4e00c114b402.png)

**※なお、本記事の後部に[VPCを使わないパターン](https://qiita.com/jnit/items/e16f4a945e1aeb3f532a#vpc%E3%82%92%E4%BD%BF%E3%82%8F%E3%81%AA%E3%81%84%E3%83%91%E3%82%BF%E3%83%BC%E3%83%B3)もあります。**

### 本例で利用する機能
 - **Lambda(AthenaGoogleBigQueryConnector)**
   - AthenaGoogleBigQueryConnectorは、AthenaのFederated Queryがサポートするコネクタの１つで、OSSのため無償で利用ができます。
   - コネクタといっても実体はLambda(Java)です。**クエリはLambdaの15分制限を受ける**点に注意してください。
   - コードはgithubのリポジトリで確認することができます。
     - https://github.com/awslabs/aws-athena-query-federation/tree/master/athena-google-bigquery
   - 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に返してくれます。
- ビューに関しては、フェデレートされたデータソースでビューを使用することはできません。

- その他の制約は公式ページをご確認ください。
  - https://docs.aws.amazon.com/ja_jp/athena/latest/ug/connect-to-a-data-source.html#connect-to-a-data-source-considerations

### 本例の検証環境
  - 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](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/90614b54-5109-db7b-2cd2-6ff3ee115acb.png)

### ServiceAccount

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

「IAMと管理」から「サービスアカウント」を選択し、サービスアカウントを作成します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/5b955c5a-55a1-0027-44bf-a921458ba205.png)

「サービスアカウント名」と「サービスアカウントID」を入力し、「作成して続行」を選びます。
　※ここでは私が以前glue用に作ったサービスアカウントのキャプチャを使用していますが、今回はGlueは使いません。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/056ebe2a-38bf-78ba-1794-f1aa57b1168e.png)


サービスアカウントに付与する権限を選択します。ロールに「BigQuery管理者」を選択し「完了」を押します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/4fd5f665-74a0-4636-b23c-1d7580c849fd.png)

サービスアカウントが作成されたら、対象のサービスアカウントを選択後、「キー」タブから「鍵を追加」→「新しい鍵を作成」を選びます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/87feb5ee-b486-8ad7-2f58-6f9cb35ae2f7.png)

キーのタイプは「JSON」を選択し、「作成」を押します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/3f99c747-bb85-279c-ced1-294a54e05423.png)

するとブラウザからjsonファイルがダウンロードされますので、適切なフォルダに保管しておきます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/104cc99f-43cd-33ae-0c10-05634c19f1dd.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を使うことにしました。プライベートサブネットと、パブリックサブネット構成となっているものです。

:::note info
少し脱線します。
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](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/c84c7e81-db69-177b-a2fc-139dfd22fccc.png)

### NAT Gateway

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

https://pages.awscloud.com/rs/112-TZM-766/images/20220728_20th_ISV_DiveDeepSeminar_Athena.pdf

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/3d397fe8-d06b-08d6-0217-136f335cd549.png)

### VPC Endpoint

- VPCエンドポイントを２種類作り、先程のVPCのサブネットにルートを設定していきます。
  - S3用(gateway型)
  - Secrets Manager用(Interface型)

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

- 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にしてください。一般的な手順は以下の通りです。
  - https://docs.aws.amazon.com/ja_jp/secretsmanager/latest/userguide/vpc-endpoint-overview.html#vpc-endpoint-create

- ここで少し悩ましいのが、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](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/9fc4d8ec-4b5a-7fba-db9a-5f3df8edcc8c.png)

シークレットのタイプに「その他のシークレットのタイプ」を選択し、「プレーンテキスト」タブを選択します。
エディットボックスの中に、先程GoogleCloudコンソールからダウンロードしたファイルの中身を、そのままjson形式で貼り付けます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/a88030b5-b400-e5d3-f23c-744a43198513.png)



:::note info
- 普通に上記の手順に沿えば全く問題は起きないのですが、私は最初、過去に作成した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](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/e9b4614e-cb24-5498-08fa-4d37c6bf31f5.png)

## S3

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

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/ceca9613-a9e7-c75e-540c-66c2e8c1226c.png)


## AthenaGoogleBigQueryConnectorのデプロイ

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/ded6a3a8-5da7-2e24-1742-17fce191c5a5.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/ee8422df-d3ce-4328-db41-345d34b0f235.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/7205729a-7855-47a8-d98d-cbc96c8924c6.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プロジェクト全体の上限値を気にしつつ)上げるとよさそうです。
    - コード上の[コメント](https://github.com/awslabs/aws-athena-query-federation/blob/8d952c40d1371273fa55f91f356344f5ead65244/athena-google-bigquery/src/main/java/com/amazonaws/athena/connectors/google/bigquery/BigQueryMetadataHandler.java#L170)を見るとパーティショニングには対応していないようです。シンプルに行数などの値を元にして相対位置(オフセット)を計算して、１クエリの結果を複数のSQL文に分割して並列取得しているようです。
    - 上限値は[BigQuery側](https://cloud.google.com/bigquery/quotas?hl=ja#api_requests)で制限されるようです。どれが対応するか調べてもわかりませんでしたが「同時実行インタラクティブ クエリの最大数」だとするとデフォルト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の料金](https://aws.amazon.com/jp/lambda/pricing/)が変わってきますので注意してください。

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

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

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

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

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

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

私は以下のような設定パラメータを指定しました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/0def1a7a-56e2-4646-3fa6-91444b72921f.png)


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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/a919d6b1-410d-5686-a928-cc49b6209d2b.png)

すると画面が遷移し、デプロイプロセスが始まります。「デプロイ」タブをクリックすると、現在のデプロイ状況がわかります。裏でCloudFormation(CFn)が動いており、「スタックイベントを表示」を押すことでCFnの詳細を見ることができます(割愛)。パラメータに問題がなければ数分以内でデプロイは完了します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/fcd331c8-53ec-5c33-b104-0eb9c6326824.png)

ステータスが「Create complete」となったら「概要」タブに戻ります。画面下部には作成されたリソースが表示されます。
　Lambda関数と、それに自動付与されたIAMロールが作成されていることがわかります。
青文字リンクをクリックするとLambdaやIAMロールなど画面に遷移しますが、設定の追加は必要ないので本手順としては次のAthenaの設定に移ります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/f3c9844b-3a70-a9da-6937-edea6eb09d51.png)


## Athena

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

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

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

https://docs.aws.amazon.com/ja_jp/athena/latest/ug/connect-data-source-serverless-app-repo.html

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

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

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/268944ff-0ccb-8e2f-4822-817ff8aaeea5.png)

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

- バージョンアップグレード方法は以下に記載されています。
  - https://docs.aws.amazon.com/ja_jp/athena/latest/ug/engine-versions-changing.html
- 各バージョンの詳細は以下に記載されています。
  - https://docs.aws.amazon.com/ja_jp/athena/latest/ug/engine-versions-reference.html

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

- 先程デプロイしたLambda関数をAthenaから参照する場合は、専用のカスタムコネクタを事前に作成し、ソースとしてLambdaを指定する必要があります。
  - デフォルトで用意されているGlue連動型のデータカタログ「AwsDataCatalog」ではFederatedQueryを利用できませんので、必ずこの手順が必要になります。
  - 公式の手順は以下です。
    - https://docs.aws.amazon.com/athena/latest/ug/connect-to-a-data-source-lambda.html

Athenaのコンソール画面より、「データソース」から「データソースの作成」を選択します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/08d134ce-cf1c-8e65-e926-43b33d2d6c9d.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/14d77404-29d9-2b5a-7238-fc32ed36d36d.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/b21d5322-e21d-29b8-0a39-a16db087e34d.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/fd9831c9-ef8e-d996-0484-fb718c6af8b1.png)

### AthenaからBiqQueryへのクエリ実行

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

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

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

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

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

#### Select系クエリ

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

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/e5e8c192-0603-d87c-faa0-738859718594.png)


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

#### CREATE VIEWクエリ

- 今度は試しにAthena上でViewを作ってみます。
  - ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/2e53eeda-75d0-ceb3-77d8-4a756364f900.png)
  - View作成はエラーで失敗しました。
    - line 1:50: extraneous input '"athena-bigquery-src"' expecting {<EOF>, ',', '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](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/f73a5f52-9608-1f62-c4c2-1650ce239e2e.png)

- 私の場合のエラーは先に記載したように、SecretsManagerに登録した接続情報に間違ってbase64エンコードをかけていたことが原因ですが、以上のように画面上は詳細なエラーを吐いてくれません。
  - エラーが発生した場合は、Lambda関数が出力したCloudWatchLogsを見て、JavaのExceptionに出力されたエラー行番号からgithubのコードを追って推論を重ねる形になると思います。
  - 認証系の処理はawsリポジトリのコードではなく、googleリポジトリのコードを参照する必要があります。エラーを吐いているコードのパッケージがaws側なのかgoogle側なのか確認しつつデバッグをしてください。
  - 私はgoogle側のエラー文字列と、Googleのサービスアカウントのjsonの中身を比較してようやく問題に気づきました。
    - https://github.com/googleapis/google-auth-library-java/blob/main/oauth2_http/java/com/google/auth/oauth2/ServiceAccountCredentials.java#L473
```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つです。
  - **[aws-quicksight-service-role-v0]**
     - データソースがRDS,Redshift,Timestreamなど、以下以外の全般的なAWSサービスへのアクセスに利用
  - **[aws-quicksight-s3-consumers-role-v0]**
     - データソースが Athena, S3, Federated Queryの場合に利用
     - https://docs.aws.amazon.com/ja_jp/quicksight/latest/user/troubleshoot-athena-insufficient-permissions.html
     - 環境によっては本ロールが存在せず、Athena等の権限が[aws-quicksight-service-role-v0]側に設定されている場合もあります。
  - **[aws-quicksight-secretsmanager-role-v0]**
     - 接続先DBのユーザ/パスワード情報をAWS SecretsManagerに保持する際に利用
     - 本記事執筆時点ではCLIでのみ初期設定可能
     - https://docs.aws.amazon.com/ja_jp/quicksight/latest/user/secrets-manager-integration.html


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


![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/6f110fbf-9aa0-dec3-d40e-82b6c2bfdd79.png)

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

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

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/378ffe31-eb51-c21f-8571-c7473646cd84.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/3030efa9-4ddd-92dd-e02b-31ca849dab2d.png)

表示されるサービスから、「Amazon Athena」にチェックを入れます。**すでにチェック済みの場合は一度チェックを外し、再度チェックを入れます。**
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/61af4503-1eee-f7c4-ff69-f789f16f23c5.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/39bcc111-c431-c402-0855-efd1201ec364.png)

まずLambda関数の権限を追加します。Lambdaタブをクリックし、今回追加したLambda関数にチェックをいれます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/75129aaa-ff09-04d6-ee68-220f1135cb43.png)

次にS3タブを選択し、先程作成したSpillデータ格納用のS3バケットにチェックをいれ、「完了」を押します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/36b76f5d-81fa-6adc-0b09-218333979c5d.png)

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

最後に「保存」をクリックします。以下のようにオレンジ色の文字で留意事項が表示されます。今回はクロスアカウントを使っていないので、特に気にせず進めます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/5b9a4b66-f585-53f0-b250-f8d41d8c0bed.png)

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

### データセットの作成

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/9d781ef9-427b-c5fc-eabe-04a355346536.png)

データセットが利用するデータソースはAthenaを選択します。Athenaのものであれば、既存のデータソースでもいいですし、新規でもどちらでもOKです。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/14e0fc78-32ce-6604-9d6a-ce7e2f490e2c.png)

- 以下は新規データソースの例です。
   - データソース名は識別名ですので、他と区別しやすい任意の名前を入れます。
   - Athenaワークグループは、前述したようにAthenaエンジンバージョンが2以降になっているグループを指定してください。
   - 「接続を検証」は押しても押さなくてもどちらでもよいです。Athenaの場合基本的に接続は成功するはずですが、失敗する場合はQuickSightが利用するIAMロール（前述）に、Athena workgroupのlist/Get権限が不足しているなどの問題が考えられます。その場合は以下のサイトを参照してください。IAMロールを作り直すという手もあるようです。
     - https://docs.aws.amazon.com/ja_jp/quicksight/latest/user/troubleshoot-connect-athena.html
     - https://docs.aws.amazon.com/ja_jp/quicksight/latest/user/troubleshoot-athena-workgroup.html
     - https://dev.classmethod.jp/articles/how-to-handle-quicksight-error-when-creating-athena-dataset/
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/26bfa797-148f-6df9-2b29-6f35e0e0a2dd.png)

次に「カタログ」のリストから、先程Athenaのセットアップで作成したAthenaカスタムコネクタのデータソース名を探して選択します。
すると裏でLambdaがキックされ、正常にいけば5秒くらいでBigQuery側のデータセット名が見えるようになります。
この例ではAthena「データベース」に、私のBigQuery上のデータセット「glue_connector_test」を選択します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/dfcdfa60-960d-8aaa-908a-b302380a0fac.png)

QuickSightで参照したいテーブル名を選択し、「選択」をクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/2fdb3ba4-2ce6-ed68-46a0-8c6d5f5c5df9.png)

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

「Visualize」または「データの編集/プレビュー」で中身を見ることができます。
ここでは「Visualize」を選んでみます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/9b07179e-b8c2-41cf-3302-419921194ba2.png)

最近分析の初期アクセス時にこのようなダイアログが出ますが、右の「ページ分割されたレポート」を選択しない限りは、作成でもキャンセルでもどちらを選んでも「インタラクティブシート」が作成されます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/e1e3c0eb-80e8-0fa5-585c-454f268efbcf.png)

SPICE利用の場合は初回にSPICEの読み込み待ち時間が少し発生します。100%になるのを待ちましょう。
フィールドにBigQueryのカラムが確認できます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/198bf1b5-3f62-713b-a362-a134dc3d62f0.png)

適当にドラッグアンドドロップして円グラフを作成してみました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/dc500c53-9be7-7212-3802-8fd84c2efd58.png)

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

# その他の検討事項

## QuickSightへのデータロード速度

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

  - SPICEのデータ更新
    - SPICEを利用する場合はデータセットの編集画面でデータ更新頻度を設定しておくと良いと思います。
    - 頻度は毎月、毎週、毎日が選択でき、EnterpriseEditionであれば更に毎時も利用できます。
       - https://docs.aws.amazon.com/ja_jp/quicksight/latest/user/refreshing-imported-data.html#schedule-data-refresh
    - また、EnterpriseEditionであれば、参照先テーブルに適合する日付型のカラムがあることが条件になりますが、増分更新も可能です。
  - SPICE使用量の監視
    - 2022年11月からCloudWatchでSPICE使用量がモニタリングできるようになりました。
      - https://aws.amazon.com/jp/about-aws/whats-new/2022/11/amazon-quicksight-monitoring-spice-consumption-sending-metrics-cloudwatch/
    - 詳細は以下に記載されています。
      - 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](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/fb937c1c-c675-a238-3602-9161634c5d3e.png)

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



## VPCを使わないパターン

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/fc23d0f5-e323-23de-4f58-09cac8035933.png)

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

### 手順

- BigQuery
  - Project名の確認（[実施済み](#bigquery)のため省略）
  - ServiceAccountの作成（[実施済み](#serviceaccount)のため省略）
- AWS
  - AWS SecretsManagerへのクレデンシャル登録（[実施済み](#aws-secretsmanager)のため省略）
  - [AthenaGoogleBigQueryConnector]のデプロイ
    - 以下直リンクから、またコネクタを作成します。
      - https://ap-northeast-1.console.aws.amazon.com/serverlessrepo/home?region=ap-northeast-1#/available-applications
    - 今度は**VPCを使わない形でテストしたいので、[SecurityGroupIds], [SubnetIds]は空を指定します。**
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/a0764909-8720-db8f-39c4-cbbca73c24b0.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エンジンバージョンの確認・変更（[実施済み](#athena%E3%82%A8%E3%83%B3%E3%82%B8%E3%83%B3%E3%83%90%E3%83%BC%E3%82%B8%E3%83%A7%E3%83%B3%E3%81%AE%E7%A2%BA%E8%AA%8D%E5%A4%89%E6%9B%B4)のため省略）
    - データソース（カスタムコネクタ）の追加
      - Athenaで[データソースを追加](#%E3%83%87%E3%83%BC%E3%82%BF%E3%82%BD%E3%83%BC%E3%82%B9%E3%82%AB%E3%82%B9%E3%82%BF%E3%83%A0%E3%82%B3%E3%83%8D%E3%82%AF%E3%82%BF%E3%81%AE%E8%BF%BD%E5%8A%A0)して、非VPC用に作成したLambda関数を指定してください。
      - ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/1ff3e62c-f538-3ac3-7211-ee1f1b5b0cb5.png)
    - ためしに新しいデータソースを指定しクエリを投げてみると、正常にBigQueryへのクエリが成功していることがわかります。
  　- ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/f0a6184e-d3be-365e-2ea6-44a7c0b47ab9.png)
    - 実行速度としては3回実施して 18秒、12秒、11秒という結果でした。試行回数が少ないためなんとも言えませんが、VPC版だと同データで11秒～14秒程度だったので、非VPCはほんのちょっと速いのかも？しれません。いずれにせよNAT GatewayやVPCエンドポイントが不要というのは大きいです。
　　- ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/e49c5b2c-47ae-13aa-1760-66eb1d72ad31.png)
  - QuickSight
    - IAMロールにFederatedQuery用の[権限を追加する](#iam%E3%83%AD%E3%83%BC%E3%83%AB%E3%81%ABfederatedquery%E7%94%A8%E3%81%AE%E6%A8%A9%E9%99%90%E3%82%92%E8%BF%BD%E5%8A%A0%E3%81%99%E3%82%8B)
      - VPCのときと同じように、追加したLambda関数へのアクセス権限をQuickSightコンソールから追加します。スピル用バケットは、今回はVPCと同じものを使っているため、追加は省略します。
        - ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/403a0f60-dcf2-dece-aa80-271a16f3a9fe.png)
    - [データセットを作成する](#%E3%83%87%E3%83%BC%E3%82%BF%E3%82%BB%E3%83%83%E3%83%88%E3%81%AE%E4%BD%9C%E6%88%90)
      - VPCのときと同じように、BigQueryのテーブルが見えました！
      - なお、SPICE読み込みは今回のデータで40秒ほどで完了しました。
        - ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/924a954f-5650-62f9-92b0-6dc8e3482fbe.png)

### 非VPC版の結論

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

## コネクタの削除

- [AthenaGoogleBigQueryConnector]コネクタを削除するには、Lambdaのアプリケーションメニューから選択し削除を選んでください。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/816a1aea-0320-049f-2525-14a387afe0f7.png)

リンクをクリックするとCloudFormationの該当スタックに遷移しますので、スタックごと削除してください。これによりLambda関数と、Lambda用のIAMロールが削除されます。(手順省略）
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/538058/e7c74c4a-6284-6526-61e0-d5e280bf9c2f.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の内外で正常に機能する


### 参考にさせて頂いたサイト
 - https://dev.classmethod.jp/articles/20220423-amazon-athena-connect-to-bigquery/
   - さすがのクラスメソッドさんです。こちらの記事がなかったら私はサッパリわからん状態でした。

以上です。