S3 を外部ステージとした時に INFER_SCHEMA でスキーマを検出できない #SnowflakeDB

モダンデータスタック(MDS)

#Snowflake

#Amazon S3

安原朋紀

2024.02.25

はじめに

Snowflake では、ステージに配置された対応するフォーマットのデータファイルから、スキーマ情報を自動的に検出する INFER_SCHEMA という機能が提供されています。
外部ステージとして S3 を指定し、S3 上のデータファイルに対してスキーマ検出を行うことも可能なのですが、この際、結果が返ってこない（スキーマ検出ができない）場面があったので、確認したポイントを記事としました。

確認する観点

先に結論ですが S3 上のファイルに対してスキーマ検出がうまくいかない場合は、以下の観点で各種設定が正しいかをご確認ください。

データファイルのパス
ファイルフォーマット
IAM ロールに付与されたポリシーの権限
S3 の暗号化キー

事前準備

検証用に、以下の手順で外部ステージを作成しました。

S3 バケット

暗号化方式
Amazon S3 では、バケットのデフォルト暗号化方式として、SSE-S3（Amazon S3 マネージドキーを使用したサーバー側の暗号化）により、オブジェクトが暗号化されます。ここでもデフォルトの暗号化方式でバケットを作成しました。

IAM ロール

以下の通り IAM ロールを作成しました。

IAM ポリシー

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "s3:GetObject",
                "s3:GetObjectVersion"
            ],
            "Resource": [
                "arn:aws:s3:::<バケット名>/*"
            ]
        },
        {
            "Effect": "Allow",
            "Action": [
                "s3:ListBucket",
                "s3:GetBucketLocation"
            ],
            "Resource": [
                "arn:aws:s3:::<バケット名>"
            ],
            "Condition": {
                "StringLike": {
                    "s3:prefix": [
                        "*"
                    ]
                }
            }
        }
    ]
}

信頼関係

※snowflake_user_arn,snowflake_external_id は後述する手順で取得し、後ほど置き換えます。

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "",
            "Effect": "Allow",
            "Principal": {
                "AWS": "<snowflake_user_arn>"
            },
            "Action": "sts:AssumeRole",
            "Condition": {
                "StringEquals": {
                    "sts:ExternalId": "<snowflake_external_id>"
                }
            }
        }
    ]
}

外部ステージの作成

Snowflake 側で以下の手順で外部ステージとファイルフォーマットもあわせて作成します。

--ストレージ統合の作成
CREATE OR REPLACE STORAGE INTEGRATION test_integration
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = 'S3'
  ENABLED = TRUE
  STORAGE_AWS_ROLE_ARN = 'Snowflake用IAMロールのArn'
  STORAGE_ALLOWED_LOCATIONS = ('s3://<バケット名>/');

--snowflake_user_arn,snowflake_external_id の確認
DESC INTEGRATION test_integration;
  
--外部ステージを作成
CREATE OR REPLACE STAGE test_s3_stage
  STORAGE_INTEGRATION = test_integration
  URL = 's3://<バケット名>/';

--ファイルフォーマットを作成
CREATE OR REPLACE FILE FORMAT my_parquet_format
  TYPE = PARQUET;

データファイルの配置

以下のURLで Parquet ファイル（iris.parquet）を配置しました。

s3://<バケット名>/iris_sse-s3/iris.parquet

検証

はじめに、通常通り INFER_SCHEMA によるスキーマ検出を試してみます。上記の手順でアップロードしたデータファイルに対するスキーマ検出を行います。

SELECT *
  FROM TABLE(
    INFER_SCHEMA(
      LOCATION=>'@test_s3_stage/iris_sse-s3/iris.parquet'
      ,FILE_FORMAT=>'my_parquet_format'
      )
    );

この場合、問題なくスキーマを検出できました。

データファイルのパス

パスの指定が誤っている場合、スキーマ検出がうまくいきません。
以下のように存在しないパスでスキーマ検出を行います。

SELECT *
  FROM TABLE(
    INFER_SCHEMA(
      LOCATION=>'@test_s3_stage/wrongpath/iris.parquet'
      , FILE_FORMAT=>'my_parquet_format'
      )
    );

出力は下図のようになり、結果が生成されません。

基本的な部分ですが、パスが正しく設定されているか確認します。

ファイルフォーマット

指定のデータファイルに適合しないファイルフォーマットを指定してみます。

--ファイルフォーマットを作成
CREATE OR REPLACE FILE FORMAT my_json_format
  TYPE = JSON;
--スキーマ検出
SELECT *
  FROM TABLE(
    INFER_SCHEMA(
      LOCATION=>'@test_s3_stage/iris_sse-s3/iris.parquet'
      , FILE_FORMAT=>'my_json_format'
      )
    );

この場合、使用したファイルフォーマットごとにエラー内容は異なりますが、メッセージとしてエラーの原因が推測しやすいので、ファイルフォーマットを再度ご確認ください。

IAM ロールに付与されたポリシーの権限

ドキュメントでは、Snowflake からファイルにアクセスするために、S3バケットおよびフォルダーに対する以下の権限が必要と記載があります。

s3:GetBucketLocation
s3:GetObject
s3:GetObjectVersion
s3:ListBucket

オプション1：Amazon S3にアクセスするためのSnowflakeストレージ統合の構成 | Snowflake Documentation

外部ステージのファイルに対してスキーマ検出を行う際も基本的に同様の考え方になります。ここでは、s3:GetObject ,s3:ListBucket がない場合スキーマ検出できず、それぞれ以下のような結果となりました。

IAM ロールにs3:GetObjectの権限がない場合

IAM ロールにs3:ListBucketの権限がない場合

AWS 側と連携した設定が必要な部分なので、スキーマ検出が動作しない際は、必要な権限が付与されていることをご確認ください。

S3 の暗号化キー

S3 に別のオブジェクトをアップロードします。
また、この際にデフォルトの暗号化方式から変更し、別途作成しておいた KMS キーを指定します。
ここでは以下の設定で、s3://<バケット名>/iris_sse-kms/iris.parquet としてファイルを追加しました。

アップロードしたファイルに対してスキーマ検出を行います。

SELECT *
  FROM TABLE(
    INFER_SCHEMA(
      LOCATION=>'@test_s3_stage/iris_sse-kms'
      , FILE_FORMAT=>'my_parquet_format'
      )
 );

この場合、下図の出力となり、スキーマの検出ができません。

その他の権限は満たしているので、list コマンドによるデータファイルの一覧は可能です。

list @test_s3_stage/iris_sse-kms;

この状態ですとスキーマの検出ができない原因の特定が難しいので、データファイルに対して COPY コマンドを実行してみます。
すると下図の通り、エラー内容が変わります。

--データロード用のテーブルを作成
CREATE OR REPLACE TABLE parquet_test(
    v VARIANT  
);
--COPYコマンドでロードを行う
COPY INTO parquet_test
	from @test_s3_stage/iris_sse-kms
	FILE_FORMAT = my_parquet_format;

エラーメッセージ

このエラーメッセージについては、コミュニティにも記事がありました。ファイルを KMS で暗号化している場合、この表示となるようです。

記事にもあるように、対象のキーに関するキーポリシーを変更し、ストレージ統合作成時に指定した IAM ロールに対するkms:Decrypt操作を許可します。

{
    "Id": "key-consolepolicy-3",
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "Enable IAM User Permissions",
            "Effect": "Allow",
            "Principal": {
                "AWS": "arn:aws:iam::XXXX:root"
            },
            "Action": "kms:*",
            "Resource": "*"
        },
        {
            "Sid": "Allow use of the key",
            "Effect": "Allow",
            "Principal": {
                "AWS": "Snowflake用IAMロールのArn"
            },
            "Action": [
                "kms:Decrypt"
            ],
            "Resource": "*"
        }
    ]
}

変更を保存後、再度スキーマ検出を試すと、意図した結果が返ってきました。

ここではキーポリシーを変更しましたが、対象のロールの IAM ポリシーで、このキーの復号に必要なアクションを設定した場合でも動作すると考えられます。

さいごに

INFER_SCHEMA で S3 上のファイルに対してスキーマ検出できない際にチェックする観点をいくつかあげてみました。原因が明確となるエラーメッセージが返ってこない場合があるので、AWS 側の設定も含めた調査が必要になる場合があります。
同ファイルに対して、内部ステージに配置した場合はスキーマ検出できるのか、スキーマ検出以外の操作（クエリやコピーコマンドの実行）ができるのか、など問題を切り分けつつ調査する必要があるので、その際にこちらの内容が参考になれば幸いです。