Snowflake – Data
Loading/Ingestion and
Extraction
Contents
31. Lab – Create an Integration Object to Connect to Snowflake with AWS S3:....................................3
31. Lab – Create an Integration Object to Connect to
Snowflake with AWS S3:
Snowflake – AWS Connetction:
Step 1: Create an IAM Role for the snowflake to access the S3 Buckets
Step 2: Create an S3 bucket and upload the file
Step 3: Creating an Intergration Object in Snowflake for authentication
Step 4: Create a file format object ( Using Sysadmin/Custom Role)
Step 5: Create a stage object refrencing the location from which the data needs to be
ingested. ( Using Sysadmin/Custom Role)
Step 6: Load the data into Snowflake Tables . ( Using Sysadmin/Custom Role)
To create an integration object, below is the query:
create or replace storage Integration aws_sf_data
TYPE= EXTERNAL_STAGE
STORAGE_PROVIDER = S3
ENABLED = TRUE
STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::690869813674:role/aws-snowflake-role'
STORAGE_ALLOWED_LOCATIONS = ('s3://nigam-snowflake-data/');
The value of “STORAGE_AWS_ROLE_ARN” one can
get from the “arn” of the role that you have
specifically created for the snowflake and the aws
communication.
This is the “S3 URI”of your s3 bucket. You can get by
taking the ARN of the bucket i.e. “arn:aws:s[Link]nigam-
snowflake-data” and then changing it to the following
“s3://nigam-snowflake-data/”. NOTE: don’t forget to
give the final “/”.
Then you can describe it :
Desc integration aws_sf_data;
property_typ
property e property_value
ENABLED Boolean TRUE
STORAGE_PROVIDER String S3
STORAGE_ALLOWED_LOCATIO
NS List s3://nigam-snowflake-data/
STORAGE_BLOCKED_LOCATION
S List
STORAGE_AWS_IAM_USER_AR
N String arn:aws:iam::786596211272:user/ibgc0000-s
STORAGE_AWS_ROLE_ARN String arn:aws:iam::690869813674:role/aws-snowflake-role
DZ02544_SFCRole=2_NCZNbuE6wmmxTMItQ2ENxC6U3v
STORAGE_AWS_EXTERNAL_ID String M=
COMMENT String
Now you have to take the value of “STORAGE_AWS_IAM_USER_ARN” and
“STORAGE_AWS_EXTERNAL_ID” and put it in the “trusted policy “of the particular IAM role
2
3
After clicking on editing , you will get you will be taken to a new page where you can go and edit the
Section highlighted.
"Version": "2012-10-17",
"Statement": [
"Effect": "Allow",
"Principal": { 1
"AWS": "arn:aws:iam::786596211272:user/ibgc0000-s"
},
"Action": "sts:AssumeRole",
"Condition": {
"StringEquals": { 2
"sts:ExternalId": "DZ02544_SFCRole=2_NCZNbuE6wmmxTMItQ2ENxC6U3vM="
AWS = STORAGE_AWS_IAM_USER_ARN
And
"sts:ExternalId": = STORAGE_AWS_EXTERNAL_ID
Lab 32: Ingest CSV from S3 to Snowflake
1) Grant “USAGE” for the “INTEGRATION” object i.e. AWS_SF_DATA to the role “ SYSADMIN”
2) Create the “file format” object i.e. csv_load_format
3) Create the “stage” object i.e. stg_lineitem_csv_dev
4) Load the data from s3 to the snowflake
create or replace file format csv_load_format
TYPE = 'CSV'
Compression = 'AUTO'
field_delimiter = ','
record_delimiter = '\n'
skip_header = 1
field_optionally_enclosed_by = '\042'
trim_space = FALSE
error_on_column_count_mismatch = true
escape = 'NONE'
escape_unenclosed_field = '\134'
date_format = 'AUTO'
timestamp_format = 'AUTO';
-- create a stage for the lineitem table--
create stage stg_lineitem_csv_dev
storage_integration = aws_sf_data
url = 's3://nigam-snowflake-data/ecom_dev/lineitem_csv/'
file_format= csv_load_format;
GRANT USAGE ON INTEGRATION AWS_SF_DATA TO ROLE sysadmin;
show grants on AWS_SF_DATA;
list @stg_lineitem_csv_dev;
--Loading data from s3 to snowflake
select count(*) from ECOMMERCE_DB.ECOM_DEV.LINEITEM;
copy into lineitem
from @stg_lineitem_csv_dev
file_format= csv_load_format
ON_ERROR = ABORT_STATEMENT;
