Amazon Athena から Snowflake に直接クエリ：フェデレーテッドクエリ

Amazon Athenaはデータコネクタを利用することでAWS以外の様々なサービスにライブデータアクセス（フェデレーテッドクエリ）できる。

https://docs.aws.amazon.com/ja_jp/athena/latest/ug/connectors-prebuilt.html

Snowflake コネクタを利用してAthenaからSnowflakeへのライブデータアクセスしてみた


# 構成概要
- Athenaデータコネクタはlambda関数で実現される
- コネクタはAWS Serverless Application Registoryから検索して簡単にデプロイできる
- Snowflakeへの接続はLambdaコネクタからNAT経由で接続（Business Critical Edition以上ならPrivateLink接続もあるがここでは省略）
- Snowflake接続の認証情報（ID・Password）はAWS Secrets Managerで管理し、Lambdaのコネクタから呼ぶ
- 処理した結果はLambdaのコネクタ経由で指定したS3バケットに出力する
- Secrets ManagerとS3への接続経路はVPC EndpointからNAT経由
- 構成イメージは以下
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/112537/d0a306ed-984f-2ab2-90d8-7ae5969e254d.png)

AtheanコンソールからSnowflakeにクエリ実行したイメージ
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/112537/2e802db0-a3af-30be-646b-d25f61afff9c.png)


# セットアップ

### 1. SnowflakeのID・PasswordをAWS Secrets Managerに登録

AWS Secret ManagerでSnowflakeのログインIDとパスワードを登録しておく
- シークレットタイプは「その他のシークレットのタイプ」
- キーはそれぞれ username、password
- 値はSnowflakeのログイン情報

![スクリーンショット 2022-12-12 21.57.38.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/112537/d2ee2ee4-a029-6ffb-5cad-8b34ba263ef0.png)

### 2.AWS Serverless Application RegistoryからAthenaコネクタを検索してデプロイ

Athena snowflakeで検索すると出てくる「AnthenaSnowflakeConnector」を選択
![スクリーンショット 2022-12-12 21.55.38.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/112537/1d7f44ca-77cd-dc08-8c84-1b32c73a26e0.png)

以下詳細パラメータ設定
![スクリーンショット 2022-12-12 22.23.02.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/112537/78b2b3b7-34f9-a379-6ddc-d2bbc04f2858.png)

- **SecretNamePrecix** : SecretsManagerで登録した「シークレットの名前」
- **SpillBucket**：処理結果を出力するS3バケット名（s3://の部分は不要）
- **DefaultConnectionString**：Snowflakeへの接続情報
    - フォーマットは`snowflake://jdbc:snowflake://{snowflake_instance_url}/?warehouse={warehousename}&db={databasename}&schema={schemaname}&${secretname}`
    - {snowflake_instance_url} は`XXXXXXX.snowflakecomputing.com`のようなURI
    - {secretname}はSecretsManagerで登録した「シークレットの名前」
    - 例：`snowflake://jdbc:snowflake://glb37380.us-east-1.snowflakecomputing.com/?warehouse=COMPUTE_WH&db=SNOWFLAKE_SAMPLE_DATA&schema=TPCH_SF1&${snowflake-athena}`
- **LambdaFunctionName**：追加するLambda関数名（Snowflakeコネクタの実態）
- **SecurityGroupIds**：Lambda関数に紐付けるSG。AWS SecretsManager・S3とSnowflakeへ接続するOutbound経路が必要
- **SubnetIds**：Lambda関数に紐付けるサブネット。Lambda関数自体にはPublic IPがなくNAT経由の接続となるためプライベートサブネットにする。

デプロイが完了するとLambdaコンソールからもアプリケーションおよび関数に追加される
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/112537/0609f352-7fe5-5e92-1cd6-a30b2bc95f53.png)



### 3. Snowflakeコネクタ(Lambda関数)のIAM ROLE修正
- IAM ROLEのポリシーに`SecretManagerReadWrite`を追加する
- 編集対象のIAM ROLEは追加したLambda関数の設定から確認できる

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/112537/b369b96f-4900-39f5-32cc-932884ff2940.png)


### 4. Snowflakeコネクタ(Lambda関数)の環境編集修正
- 環境変数にキー`athenasnowflake_connection_string`として値にdefaultキーと同じものを追加
- Athenaのデータカタログ接続に必要
   
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/112537/60a5e9a5-194e-4e91-767c-3c272ea11a53.png)

 - この指定がない場合に、後でAthena利用時に以下のエラーが出る
        - `Failed to invoke lambda function due to com.amazonaws.services.lambda.invoke.
LambdaFunctionException: Catalog is not supported in multiplexer. 
After registering the catalog in Athena, must set 'athenasnowflake_connection_string' environment variable in Lambda. 
See JDBC connector README for further details.
`

### 5. Athenaでデータソースを追加
- Athenaコンソールの「データソース」から「データソースを作成」し、下記のようにsnowflakeを選択。Lambda関数に先ほど作成したSnowflakeコネクタの関数を指定する

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/112537/4b4fe058-42cd-fbc4-7151-b1d9e1f1049c.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/112537/4a7f38d1-7bbc-7235-bdab-16d8b2aacace.png)


# クエリ実行

あとはAthenaコンソールからクエリ実行するのみ
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/112537/2e802db0-a3af-30be-646b-d25f61afff9c.png)


参考）

https://aws.amazon.com/jp/blogs/big-data/query-snowflake-using-athena-federated-query-and-join-with-data-in-your-amazon-s3-data-lake/

# 最後に

- Athenaデータコネクタを利用すると、AWS以外のサービスへも事前データコピーなくライブデータアクセスができる
- クエリパフォーマンス面を考えると、Amazon AppFlowやAWS Glueなどを利用してS3へ集約した方が良いが、ちょっとした外部データへのアクセス要件などには有益
- 注意点としてはLambdaが仲介するため、Lambda固有の制約（実行時間最大15分など）も考慮が必要