Snowflake MLSI コマンド備忘録
1. Source側：MLSI作成
USE ROLE ACCOUNTADMIN;
CREATE STORAGE INTEGRATION <MLSI_NAME>
TYPE = EXTERNAL_STAGE
STORAGE_LOCATIONS =
(
(
NAME = 'PRIMARY_S3'
STORAGE_PROVIDER = 'S3'
STORAGE_BASE_URL = 's3://<PRIMARY_BUCKET>'
STORAGE_AWS_ROLE_ARN =
'arn:aws:iam::<AWS_ACCOUNT_ID>:role/<IAM_ROLE_NAME>'
STORAGE_AWS_EXTERNAL_ID = '<EXTERNAL_ID>'
ENCRYPTION = ( TYPE = 'AWS_SSE_S3' )
),
(
NAME = 'SECONDARY_S3'
STORAGE_PROVIDER = 'S3'
STORAGE_BASE_URL = 's3://<SECONDARY_BUCKET>'
STORAGE_AWS_ROLE_ARN =
'arn:aws:iam::<AWS_ACCOUNT_ID>:role/<IAM_ROLE_NAME>'
STORAGE_AWS_EXTERNAL_ID = '<EXTERNAL_ID>'
ENCRYPTION = ( TYPE = 'AWS_SSE_S3' )
)
)
ENABLED = TRUE
STORAGE_ALLOWED_LOCATIONS = ('*')
ACTIVE = 'PRIMARY_S3';
2. Source側：Snowflake IAM情報取得
DESC STORAGE INTEGRATION <MLSI_NAME>;
控える値：
STORAGE_AWS_IAM_USER_ARN
STORAGE_AWS_EXTERNAL_ID
AWS IAM RoleのTrust Policy設定で使用する。
3. Failover GroupにStorage Integrationを追加
既存のReplication対象を残したうえで INTEGRATIONS を追加する。
ALTER FAILOVER GROUP <FAILOVER_GROUP_NAME> SET
OBJECT_TYPES = DATABASES, INTEGRATIONS
ALLOWED_DATABASES = <DATABASE_NAME>
ALLOWED_INTEGRATION_TYPES = STORAGE INTEGRATIONS;
ROLESもReplication中の場合：
ALTER FAILOVER GROUP <FAILOVER_GROUP_NAME> SET
OBJECT_TYPES = DATABASES, ROLES, INTEGRATIONS
ALLOWED_DATABASES = <DATABASE_NAME>
ALLOWED_INTEGRATION_TYPES = STORAGE INTEGRATIONS;
事前確認：
SHOW FAILOVER GROUPS;
OBJECT_TYPESは追加ではなく再指定になるため、既存対象を落とさないよう注意。
4. Target側：Refresh
ALTER FAILOVER GROUP <FAILOVER_GROUP_NAME> REFRESH;
5. Target側：ACTIVE Location変更
ALTER STORAGE INTEGRATION <MLSI_NAME>
SET ACTIVE = 'SECONDARY_S3';
確認：
DESC STORAGE INTEGRATION <MLSI_NAME>;
確認観点：
ACTIVE = SECONDARY_S3
6. Target側：Snowflake IAM情報取得
DESC STORAGE INTEGRATION <MLSI_NAME>;
控える値：
STORAGE_AWS_IAM_USER_ARN
AWS IAM RoleのTrust Policyには、Source / Target双方のSnowflake IAM User ARNを許可する。
7. External Stage作成
CREATE STAGE <DATABASE_NAME>.<SCHEMA_NAME>.<STAGE_NAME>
RELATIVE_URL = '/<RELATIVE_PATH>/'
STORAGE_INTEGRATION = <MLSI_NAME>;
Storage Location直下を使う場合：
CREATE STAGE <DATABASE_NAME>.<SCHEMA_NAME>.<STAGE_NAME>
RELATIVE_URL = ''
STORAGE_INTEGRATION = <MLSI_NAME>;
Primary / Secondary S3では同じ相対パス構成にする。
8. Stage接続確認
LIST @<DATABASE_NAME>.<SCHEMA_NAME>.<STAGE_NAME>;
9. Failover
Target側：
ALTER FAILOVER GROUP <FAILOVER_GROUP_NAME> PRIMARY;
ACTIVE LocationはTarget側で事前に SECONDARY_S3 に設定済みのため、Failover時の変更は不要。
10. Failback
元Source側で最新状態を取得：
ALTER FAILOVER GROUP <FAILOVER_GROUP_NAME> REFRESH;
Refresh完了後：
ALTER FAILOVER GROUP <FAILOVER_GROUP_NAME> PRIMARY;