■ はじめに
長い間、よくわからなかったSnowflakeの接続問題について やっと原因が理解できたので、メモする。
目次
【１】トラブル概要 【２】エラー内容 【３】原因 １）確認方法 【４】解決案 【５】別ケースでの同じエラー
【１】トラブル概要
Snowflakeから、Integration（ストレージ統合）を使って ステージ経由で、AWS S3上のファイルにアクセス(※)しようとした際に 以下「エラー内容」が表示された。 ※ 以下「SQL例」の「[3] 接続テスト用として、SELECT」を参照
SQL例
-- [1] INTEGRATION の作成 CREATE STORAGE INTEGRATION demo_integ TYPE = EXTERNAL_STAGE STORAGE_PROVIDER = S3 ENABLED = TRUE STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::xxxx:role/your_role' STORAGE_ALLOWED_LOCATIONS = ('s3://your-s3-bucket/test/') ; -- [2] ステージ作成（ここまでOK） CREATE OR REPLACE TEMPORARY STAGE demo_stage URL = 's3://your-s3-bucket/test/demo/' STORAGE_INTEGRATION = demo_integ FILE_FORMAT = (TYPE =PARQUET COMPRESSION = SNAPPY) ; -- [3] 接続テスト用として、SELECT (★ここでエラー★) SELECT * FROM @demo_stage LIMIT 10;
【２】エラー内容
SQL execution error: Error assuming AWS_ROLE. Please verify the role and externalId are configured correctly in your AWS policy.
和訳
SQL 実行エラー: エラー assuming AWS_ROLE（※） あなたのAWSポリシー内で、 そのロールおよび外部ID(externalId)が正しく設定されているか を確認してください。 cf. assume = 引き受ける cf. verify = 確認する、検証する ※ AssumeRole については、以下のサイトなどを参照
https://dev.classmethod.jp/articles/iam-role-and-assumerole/
https://oji-cloud.net/2019/08/16/post-2701/
=> どこをどうやって確認するのかは、後述「確認方法」を参照のこと
【３】原因
AWS Role 側で、作成したINTEGRATION の Trust Relationship（信頼関係）が設定されていなかったため。
１）確認方法
* 以下を一読してみるのもいいかも。
https://docs.snowflake.com/ja/user-guide/data-load-s3-config-storage-integration.html
Step１: STORAGE_AWS_EXTERNAL_ID を確認する
-- Step1 -- 「DESC INTEGRATION 【ストレージ統合名】」で -- STORAGE_AWS_EXTERNAL_ID（信頼関係を確立するために必要な外部 ID） -- を確認する DESC INTEGRATION demo_integ; <出力結果・一部抜粋> | STORAGE_AWS_IAM_USER_ARN | String | arn:aws:iam::123456789001:user/abc1-b-self1234 | STORAGE_AWS_ROLE_ARN | String | arn:aws:iam::xxxx:role/your_role | STORAGE_AWS_EXTERNAL_ID | String | MYACCOUNT_SFCRole=2_a123456/xxxxxxxxxxx=
Step２: STORAGE_AWS_EXTERNAL_ID を確認する
[1] AWS Managementコンソールにログイン [2] [IAM]-[Roles]を選択していき、Textboxに指定されているロールを入力 => 今回の場合「arn:aws:iam::xxxx:role/your_role」 [3] 指定しているロールのリンク押下し、[Trust Relationship]タブを選択 => そこで Step１で確認した「STORAGE_AWS_EXTERNAL_ID」が含まれているかどうかを確認
Trust Relationshipの例
{ "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Principal": { "AWS": "arn:aws:iam::123456789001:user/abc1-b-self1234" <= snowflake_user_arn }, "Action": "sts:AssumeRole", "Condition": { "StringEquals": { "sts:ExternalId": "ACCT_SFCRole=2_zzzzzzzzz" <= ★snowflake_external_id「MYACCOUNT_SFCRole=2_a123456/xxxxxxxxxxx=」がない } } } ] }
【４】解決案
* 作成したIntegration（ストレージ統合）の STORAGE_AWS_EXTERNAL_ID を追記する => 開発環境などで汎用的にしたい場合は、 「"MYACCOUNT_SFCRole=*」など「*」を使って指定するのもありかも。
Trust Relationshipの修正例
{ "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Principal": { "AWS": "arn:aws:iam::123456789001:user/abc1-b-self1234" }, "Action": "sts:AssumeRole", "Condition": { "StringEquals": { "sts:ExternalId": [ "ACCT_SFCRole=2_zzzzzzzzz", "MYACCOUNT_SFCRole=2_a123456/xxxxxxxxxxx=" <= ★ここを追加★ ] } } } ] }
注意点
Integration（ストレージ統合）を再作成すると STORAGE_AWS_EXTERNAL_IDが変更されるので、一度設定した際は、 DROP & CREATE や CREATE OR REPLACE 等で再作成しないように注意すること => 変更する場合は、ALTER で... (以下の関連記事を参照のこと)
Snowflake ～ ストレージ統合 ～
https://dk521123.hatenablog.com/entry/2022/06/29/221037
【５】別ケースでの同じエラー
* 同じエラーになったが、別の原因。。。
エラーが発生したTrust Relationship例
{ "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Principal": { "AWS": "arn:aws:iam::123456789001:user/abc1-b-self1234" }, "Action": "sts:AssumeRole", "Condition": { "StringEquals": { "sts:ExternalId": "MYACCOUNT_SFCRole=*" } } } ] }
原因
* 以下の部分。 => やりたかったのは、「*」で汎用的にしたかったが それに対して、「StringEquals」を指定していた、、、
抜粋
"Condition": { "StringEquals": { "sts:ExternalId": "MYACCOUNT_SFCRole=*" } }
解決案
案１：正確に指定する
"Condition": { "StringEquals": { "sts:ExternalId": "MYACCOUNT_SFCRole=2_a123456/xxxxxxxxxxx=" } }
案２：「StringLike」を使う
"Condition": { "StringLike": { "sts:ExternalId": "MYACCOUNT_SFCRole=*" } }
関連記事
Snowflake ～ 基礎知識編 ～
https://dk521123.hatenablog.com/entry/2021/11/02/130111
Snowflake ～ 入門編 / Hello world ～
https://dk521123.hatenablog.com/entry/2021/11/22/212520
Snowflake ～ 基本編 / ステージ ～
https://dk521123.hatenablog.com/entry/2022/09/01/220643
Snowflake ～ ストレージ統合 ～
https://dk521123.hatenablog.com/entry/2022/06/29/221037
エラー「Failure using stage area. ... AccessDenied」時の対応
https://dk521123.hatenablog.com/entry/2022/10/27/195547
