How we can load the data from AWS S3 into snowflake and unload data from snowflake to s3

            Load the data from AWS S3 into Snowflake and unload data from snowflake to s3 bucket

purpose of the article: how we can load the data from aws s3 into snowflake.

Intend Audience: useful for the people who working with snowflake and aws .

 CSV  File:

A comma-separated values (CSV) file is a delimited text file that uses a comma to separate values. Each line of the file is a data record. Each record consists of one or more fields, separated by commas. Alternative delimiter-separated files are often given a .csv extension despite the use of a non-comma field separator.

AWS S3 bucket:

Amazon simple storage service (Amazon s3) is an object storage service. Amazon S3 has a simple web services interface that you can use to store and retrieve any amount of data, at any time, from anywhere on the web.

IAM User:

An AWS Identity and Access Management (IAM) user is an entity that you create in AWS to represent the person or application that uses it to interact with AWS. A user in AWS consists of a name and credentials. An IAM user with administrator permissions is not the same thing as the AWS account root user.

 Snowflake:

Snowflake’s Data Cloud is powered by an advanced data platform provided as Software-as-a-Service (SaaS). Snowflake enables data storage, processing, and analytic solutions that are faster, easier to use, and far more flexible than traditional offerings.

data loading from AWS S3 bucket source file to snowflake

·     Login to the AWS Management console

·     Select the AWS S3 service

·     Create the S3 bucket and folders in it as shown below

 

           

            create a IAM User for Access key and secreate  key.

Step -1 : Name the user with credentials type - Access key - Programmatic access

 Step-2:Attach existing policies directly with AmazonS3FullAccess policy name.

Step 3: Adding tags is optional

Step 4: create user as shown below

 Step 5: download the Access key ID and Secret access key

Create the stage :

and then click next . here we can select the Amazon s3 as  the file location.

 

give the s3 file location and aws acess key id and secret key id.

click finish.

the stage will be created.

then next go to work sheet write the copy into command for load data from s3 into snow flake.

-- load sample data

COPY INTO empdata1

FROM  s3://snowfalke-data-load/emptable.csv

credentials=(aws_key_id='AKIAVACJ5IJCERLX4NAW' aws_secret_key='rZpVB4kmf7iKN8uxh3dxdsV1QiJbDfhMtC7QTBZk ')

FILE_FORMAT = (TYPE = csv SKIP_HEADER = 1 FIELD_OPTIONALLY_ENCLOSED_BY = '"');

in the copy into command  i have gve the s3 file location and aws acess key and aws  secret key.and give the table in which table the data will be loaded.

As shown in above the data will be loaded sucessfully to snowflake.

Unload the  data from snowflake to s3 bucket

first i have create a table to unload data from snowflake to s3.so i have create a customer table for that

and then i have insert the data in the customers table.

i have create a table in the snowflake  and then i have create bucket in aws for load the data into snowflake to s3 bucket.

and also i have give permissions to s3 .

i have folder to s3 bucket.floder name is out1 i have given.

and then go to snowflake worksheet run script as shown in below fig.

    

-- unload sample data

COPY INTO s3://snowflake-data-unload/out1/

FROM  customers

credentials=(aws_key_id='AKIAVACJ5IJCERLX4NAW' aws_secret_key='rZpVB4kmf7iKN8uxh3dxdsV1QiJbDfhMtC7QTBZk ')

FILE_FORMAT = (TYPE = csv SKIP_HEADER = 1 FIELD_OPTIONALLY_ENCLOSED_BY = '"');

here the script sucessfully run .then go to s3 bucket check the data will be loaded are not.

here as shown in above the data will be sucessfully loaded to s3 bucket.