Snowflake : How to load data from Amazon S3 to Snowflake table using Copy

With Snowflake COPY command you can load data from staged files on internal/external locations to an existing table or vice versa. The following are the steps to Copy CSV data from S3 to Snowflake table. Snowflake offers two types of COPY commands.

1. COPY INTO <location>: Copy the data from an existing table to locations (Amazon S3, Google Cloud Storage, or Microsoft Azure).
2. COPY INTO <table>: Copy the data from staged files to the existing table. The staged files can be at: ( cloud provided by Amazon, Google, or Microsoft)

Here we are explaining the second one,  Copy into table 

Step l: Create Snowflake Database, Schema and Table

create or replace database myfreshersdatabase;
use database myfreshersdatabase;
create or replace schema freshers_schema;
use schema freshers_schema;

create or replace temporary table freshers_table (
emp_id integer, j
emp_last_name string,
emp_first_name string,
emp_office string,
emp_email string);

create or replace warehouse mywarehouse with
warehouse_size= 'X-SMALL'
auto_suspend = 120
auto_resume = true
initially_suspended=true;

Step 2: Create File Format Objects

create or replace file format freshers_csvformat
type = 'CSV'
field_delimjter = '|'
skip_header = 1;
freshers_csvformat

show file formats;
show file formats in database myfreshersdatabase;
show file formats in schema myfreshersdatabase.freshers_schema;

Step 3: Create a Named Stage Object

create or replace stage my_csv_stage
file_format = freshers_csvformat
url = 's3://freshers-in-snowflale-training';

show stages; 
show stages in database myfreshersdatabase;
show stages in schema myfreshersdatabase.freshers_schema;

copy into freshers_table
from @my_csv_stage/freshers/raw_data/companyl.csv
on.error = 'skip_file' ;

When you have multiple files in the same bucket you can use the pattern matching to copy all the files

copy into freshers_table
from @my_csv_stage/freshers/raw_data/
pattern='.*company[L-5].csv'
on.error = 'skip_file';

Official Docs

Explore the Latest : Snowflake interview Questions