Saturday, September 24 2022

STORAGE INTEGRATION

STORAGE INTEGRATION is a Snowflake object that stores a generated identity and access management entity for external cloud storage (Amazon S3, Google Cloud Storage, or Microsoft Azure). Cloud administrators can grant permissions on the storage locations to the generated entity. By using storage integration users need not provide credentials as open text while creating external stages or when loading data from external cloud storage.

STORAGE INTEGRATION is a Snowflake object that stores a generated identity and access management entity for external cloud storage (Amazon S3, Google Cloud Storage, or Microsoft Azure). Cloud administrators can grant permissions on the storage locations to the generated entity. By using storage integration users need not provide credentials as open text while creating external stages or when loading data from external cloud storage.

Storage integration can be configured to support any public cloud storage regardless of the cloud provider hosted on the snowflake account.

 

How to Create Storage Integration

 The Syntax for creating storage integration is.

CREATE [ OR REPLACE ] STORAGE INTEGRATION [IF NOT EXISTS]
  <name>
  TYPE = EXTERNAL_STAGE
  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 is:

For AWS S3
cloudProviderParams  = STORAGE_PROVIDER = 'S3'
                                        STORAGE_AWS_ROLE_ARN = '<iam_role>'
                                         [ STORAGE_AWS_OBJECT_ACL = 'bucket-owner-full-control' ]

For Google Cloud Storage
cloudProviderParams = STORAGE_PROVIDER = 'GCS'

For Microsoft Azure 
cloudProviderParams = STORAGE_PROVIDER = 'AZURE'
                                       AZURE_TENANT_ID = '<tenant_id>'

 

Examples

AWS S3

create storage integration s3_int
  type = external_stage
  storage_provider = 'S3'
  storage_aws_role_arn = 'arn:aws:iam::001234567890:role/myrole'
  enabled = true
  storage_allowed_locations = ('s3://mybucket1/path1/', 's3://mybucket2/path2/');

Google Cloud Storage

create storage integration gcs_int
   type = external_stage
   storage_provider = 'GCS'
   enabled = true
    storage_allowed_locations = ('gcs://mybucket1/path1/', 'gcs://mybucket2/path2/');

Microsoft Azure

create storage integration azure_int
  type = external_stage
  storage_provider = 'AZURE'
  enabled = true
  azure_tenant_id = '<tenant_id>'
  storage_allowed_locations = ('azure://myaccount.blob.core.windows.net/mycontainer/path1/', 'azure://myaccount.blob.co

 

Monday, March 14 2022

Snowflake :- Real Time Data Ingestion

Monday, December 27 2021

Snowflake :- Load Parquet File in table

Tuesday, December 21 2021

Snowflake :- Load Json File in table

Wednesday, November 24 2021

Snowflake :- Create Tables

How to create a warehouse in snowflake.

Snowflake Create External Tables

Tuesday, November 23 2021

JSON DATA PARSING IN SNOWFLAKE

What is JSON? JSON (JavaScript Object Notation) is a lightweight data-interchange format. It is easy for humans to read and write. It is easy for machines to parse and generate. While it is based on a subset of the JavaScript Programming Language, Standard ECMA-262 3rd Edition - December 1999, it  […]

Continue reading

- page 1 of 2