Snowflake と Glue Data Catalog をカタログ統合し自動リフレッシュの動作を確認する

CREATE TABLE `<your_database>`.`employees`(
	ID INT,
	Name STRING,
	LastReviewDate STRING
)
LOCATION
  's3://<your_bucket>/tables/iceberg/employees/'
TBLPROPERTIES (
  'table_type'='ICEBERG',
  'format'='parquet',
  'write_compression'='snappy'
);

INSERT INTO <your_database>.employees
VALUES (10 , 'taukami', '2025-01-01');

SELECT * FROM <your_database>.employees;

CREATE OR REPLACE CATALOG INTEGRATION
  my_catalog_integration
  CATALOG_SOURCE = GLUE
  TABLE_FORMAT = ICEBERG
  GLUE_AWS_ROLE_ARN = '<IAM Role ARN>'  -- Glue Data Catalog を取得する際の IAM ロール
  GLUE_CATALOG_ID = '<AWS Account ID>'  -- Glue Data Catalog を管理する AWS アカウント ID
  GLUE_REGION = 'ap-northeast-1'  -- Glue Data Catalog の AWS リージョン
  CATALOG_NAMESPACE = '<glue_data_catalog_database>'  -- Glue Data Catalog のデータベース名
  ENABLED = TRUE -- カタログ統合をIcebergテーブルで使用できるか
  REFRESH_INTERVAL_SECONDS = 30 -- カタログの自動更新間隔 デフォルト: 30秒
  COMMENT = 'glue data catalog integration'  -- コメント
;

DESCRIBE CATALOG INTEGRATION my_catalog_integration;

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "AllowGlueCatalogTableAccess",
            "Effect": "Allow",
            "Action": [
                "glue:GetTable",
                "glue:GetTables"
            ],
            "Resource": [
                "arn:aws:glue:*:<AWS Account ID>:table/*/*",
                "arn:aws:glue:*:<AWS Account ID>:catalog",
                "arn:aws:glue:*:<AWS Account ID>:database/<glue_data_catalog_database>"
            ]
        }
    ]
}

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Principal": {
                "AWS": "<信頼関係に指定する IAM ユーザー ARN (1)>"
            },
            "Action": "sts:AssumeRole",
            "Condition": {
                "StringEquals": {
                    "sts:ExternalId": "<外部 ID (2)>"
                }
            }
        }
    ]
}

CREATE OR REPLACE EXTERNAL VOLUME iceberg_external_volume
   STORAGE_LOCATIONS =
      (
         (
            NAME = 'my-s3'
            STORAGE_PROVIDER = 'S3'
            STORAGE_BASE_URL = 's3://<your_bucket>'
            STORAGE_AWS_ROLE_ARN = '<IAM Role ARN>'
            STORAGE_AWS_EXTERNAL_ID = '<External Id>' -- 省略した場合は自動生成されます
         )
      )
      ALLOW_WRITES = false
;

DESC EXTERNAL VOLUME iceberg_external_volume;

{
    "NAME": "my-s3",
    "STORAGE_PROVIDER": "S3",
    "STORAGE_BASE_URL": "s3://<your_bucket>",
    "STORAGE_ALLOWED_LOCATIONS": ["s3://<your_bucket>/*"],
    "STORAGE_REGION": "ap-northeast-1",
    "STORAGE_AWS_ROLE_ARN": "<My IAM Role ARN>",
    "STORAGE_AWS_IAM_USER_ARN": "<Snowflake IAM User ARN>",
    "STORAGE_AWS_EXTERNAL_ID": "<External ID>",
    "ENCRYPTION_TYPE": "NONE",
    "ENCRYPTION_KMS_KEY_ID": ""
}

SELECT SYSTEM$VERIFY_EXTERNAL_VOLUME('iceberg_external_volume');

CREATE ICEBERG TABLE sample_db.sample_schema.myGlueTable -- Snowflake から参照する際のテーブル名
  EXTERNAL_VOLUME='iceberg_external_volume'  -- 使用する外部ボリューム
  CATALOG='my_catalog_integration'  -- 使用するカタログ（Glue Data Catalog とのカタログ統合）
  CATALOG_TABLE_NAME='employees';  -- Glue Data Catalog 上のテーブル名

ALTER ICEBERG TABLE myGlueTable SET AUTO_REFRESH = TRUE;
-- ALTER ICEBERG TABLE myGlueTable SET AUTO_REFRESH = FALSE;

ALTER ICEBERG TABLE myGlueTable REFRESH;

ALTER TABLE <your_database>.employees ADD COLUMNS (additional_col string);

CREATE TABLE `<your_database>`.`employees2`(
	ID INT,
	Name STRING,
	LastReviewDate STRING
)
LOCATION
  's3://<your_bucket>/tables/iceberg/employees2/'
TBLPROPERTIES (
  'table_type'='ICEBERG',
  'format'='parquet',
  'write_compression'='snappy'
);