Categories:

User & Security DDL (Third-Party Service Integrations)

ALTER STORAGE INTEGRATION

Modifies the properties for an existing storage integration.

Note

This command is currently only supported for access to AWS S3 or Google Cloud Storage. Support for Microsoft Azure is planned.

See also:

CREATE STORAGE INTEGRATION , DROP INTEGRATION , SHOW INTEGRATIONS

In this Topic:

Syntax

ALTER [ STORAGE ] INTEGRATION [ IF EXISTS ] <name> SET
  cloudProviderParams
  [ ENABLED = { TRUE | FALSE } ]
  [ STORAGE_ALLOWED_LOCATIONS = ('<cloud>://<bucket>/<path>/', '<cloud>://<bucket>/<path>/') ]
  [ STORAGE_BLOCKED_LOCATIONS = ('<cloud>://<bucket>/<path>/', '<cloud>://<bucket>/<path>/') ]
  [ COMMENT = '<string_literal>' ]

Where:

cloudProviderParams (for AWS S3 bucket)

cloudProviderParams ::=
  STORAGE_AWS_ROLE_ARN = '<iam_role>'

Parameters

name

Identifier for the integration to alter. If the identifier contains spaces or special characters, the entire string must be enclosed in double quotes. Identifiers enclosed in double quotes are also case-sensitive.

SET ...

Specifies one (or more) properties/parameters to set for the table (separated by blank spaces, commas, or new lines):

ENABLED = TRUE | FALSE

Specifies whether this storage integration is available for usage in stages.

  • TRUE allows users to create new stages that reference this integration. Existing stages that reference this integration function normally.

  • FALSE prevents users from creating new stages that reference this integration. Existing stages that reference this integration cannot access the storage location in the stage definition.

STORAGE_ALLOWED_LOCATIONS = ('cloud_specific_url')

Explicitly limits external stages that use the integration to reference one or more storage locations (i.e. S3 buckets or GCS buckets). Supports a comma-separated list of URLs for existing buckets and, optionally, paths used to store data files for loading/unloading. Alternatively supports the * wildcard, meaning “allow access to all buckets and/or paths”.

AWS S3

STORAGE_ALLOWED_LOCATIONS = ('s3://bucket/path/', 's3://bucket/path/')

  • bucket is the name of an S3 bucket that stores your data files (e.g. mybucket).

  • path is an optional path (or directory) in the bucket that further limits access to data files.

Google Cloud Storage

STORAGE_ALLOWED_LOCATIONS = ('gcs://bucket/path/', 'gcs://bucket/path/')

  • bucket is the name of a GCS bucket that stores your data files (e.g. mybucket).

  • path is an optional path (or directory) in the bucket that further limits access to data files.

STORAGE_BLOCKED_LOCATIONS = ('cloud_specific_url')

Explicitly prohibits external stages that use the integration from referencing one or more storage locations (i.e. S3 buckets or GCS buckets). Supports a comma-separated list of URLs for existing storage locations and, optionally, paths used to store data files for loading/unloading. Commonly used when STORAGE_ALLOWED_LOCATIONS is set to the * wildcard, allowing access to all buckets in your account except for blocked storage locations and, optionally, paths.

AWS S3

STORAGE_BLOCKED_LOCATIONS = ('s3://bucket/path/', 's3://bucket/path/')

  • bucket is the name of an S3 bucket that stores your data files (e.g. mybucket).

  • path is an optional path (or directory) in the bucket that further limits access to data files.

Google Cloud Storage

STORAGE_BLOCKED_LOCATIONS = ('gcs://bucket/path/', 'gcs://bucket/path/')

  • bucket is the name of a GCS bucket that stores your data files (e.g. mybucket).

  • path is an optional path (or directory) in the bucket that further limits access to data files.

COMMENT = 'string_literal'

String (literal) that specifies a comment for the integration.

Cloud Provider Parameters (cloudProviderParams)

AWS S3

STORAGE_AWS_ROLE_ARN = iam_role

Specifies the Amazon Resource Name (ARN) of the AWS identity and access management (IAM) role that grants privileges on the S3 bucket containing your data files. For more information, see Configuring Secure Access to AWS S3.

Examples

The following example initiates operation of a suspended integration:

ALTER STORAGE INTEGRATION myint SET ENABLED = TRUE;