Scribd
Upload
48 views2 pages

Snowflake S3 External Stage Setup

The document outlines the steps to create a database and a customer table in SQL, define a file format for CSV files, and set up an internal and external stage for data integration with AWS S3. It includes commands for creating a storage integration object, listing stages, copying data from an external stage into the customer table, and selecting data from the customer table. The process is aimed at integrating customer data from a CSV file stored in S3 into a database for further use.

Uploaded by

maddypd18
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
48 views2 pages

Snowflake S3 External Stage Setup

The document outlines the steps to create a database and a customer table in SQL, define a file format for CSV files, and set up an internal and external stage for data integration with AWS S3. It includes commands for creating a storage integration object, listing stages, copying data from an external stage into the customer table, and selecting data from the customer table. The process is aimed at integrating customer data from a CSV file stored in S3 into a database for further use.

Uploaded by

maddypd18
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 2

1.

Create the Database


CREATE or replace DATABASE aws_integration;

2. create the table customer table


CREATE TABLE CUSTOMER
(
FName varchar(80),
LName varchar(80),
Email varchar(100),
Date_Of_Birth DATE,
City varchar(100),
Country varchar(100)
);

3. Define the file format for customers.csv file ( delimiter - pipe)


CREATE FILE FORMAT PIPE_DELIMITER
TYPE = CSV
FIELD_DELIMITER = '|'
FIELD_OPTIONALLY_ENCLOSED_BY = '"'
SKIP_HEADER = 1
DATE_FORMAT = 'YYYY-MM-DD';

show file formats;

4. Create an internal stage


CREATE STAGE CUSTOMER_STAGE FILE_FORMAT = PIPE_DELIMITER;

5.Create integration object for external stage

create or replace storage integration s3_integration


type = external_stage
storage_provider = s3
enabled = true
storage_aws_role_arn = 'arn:aws:iam::325530134896:role/cts9am'
storage_allowed_locations = ('s3://cts9am/csv/');

6. Describe the integration object to fetch external_id to use in AWS S3 configuration


DESC INTEGRATION s3_integration;
7. Create external stage
create or replace stage EXTERNAL_CSV_STAGE
URL = 's3://cts9am/csv/'
STORAGE_INTEGRATION = s3_integration
file_format = PIPE_DELIMITER;

8. List the stage


List @<stage_name>;
list @EXTERNAL_CSV_STAGE;

list @external_csv_stage;
remove @external_csv_stage;

9. Copy the data into customer table from external stage


copy into "AWS_INTEGRATION"."PUBLIC"."CUSTOMER" from
@EXTERNAL_CSV_STAGE/customers.csv on_error = CONTINUE;

10. Select the data from Customer table


select * from customer;

You might also like