Snowflake Snowpipe: Automated data loading from S3 Bucket
Snowpipe is the feature in snowflake data warehouse where we can automate the data ingestion from S3 bucket to snowflake table.
I have come accross the snowflake in 2019 and since then staretd using and liking it very much. In this blog I will be demostrating the implementation of snowpipe.
In snowflake, we have copy command by using which we can load the data from any file from S3 bucket. Although we can not directly access the S3 bucket, we need to create an stage on top of the S3 bucket.
Stage creation:
create stage snowpipedemo
url = ‘s3://some-demo-bucket/snowpipe/’ credentials=(aws_key_id=’1a2b3c’ aws_secret_key=’4x5y6z’);
Copy command:
copy into customer
from @STAGEFORLOADUNLOAD
file_format = (type= csv field_delimiter = ‘|’, skip_header = 1);
Snowpipe is used to automate the execution of Copy command whenever there is a file arrieved in S3 bucket.
So syntex of snowpipe is as below
Get Anupam Kushwah’s stories in your inbox
Join Medium for free to get updates from this writer.
create or replace pipe loadcustomer_data
auto_ingest = true as
copy into customer
from @STAGEFORLOADUNLOAD
file_format = (type= csv field_delimiter = ‘|’, skip_header = 1);
Once we have this snowpipe ready we get one ARN for this. ARN is AWS resource number. which is a unique number to the resource in AWS universe.
Setting in S3 bucket:
Once we created a snowpipe, now its time to setup S3 bucket to send the signal to snowpipe to start loading of data.
- Go to S3 bucket properties
2. Look for the Events property
3. Configure the ARN in this event as below
That’s all you need to do. You can test it by uploading any csv file in to the bucket. you will see data will load automatically in few seconds in table.
