Administrator
Administrator

Loading Data Into Snowflake

Loading Data Into Snowflake

Summary

How to load data into snowflake

Loading Data Into Snowflake

Snowflake has many ways to load data, be it CSV or other file formats (json or parquet etc). How to load CSV data into snowflake is a common question across the data developer community.

Since snowflake is a cloud native data platform, it does not have any connector which allows a data developer to load data and that’s why we all must know different approaches to load data.

This episode is a comprehensive & practical guide with hands-on excercise to learn how to load data into snowflake using classic WebUI.

Once you complete this video, you will be able to answer following questions

  1. Limitation of Snowflake Classic WebUI while loading data file.
  2. What is the role of different type of data files and role of file delimiters (comma, pipe, tab)?
  3. What is the role of different file formats while loading data into snowflake?
  4. What are different copy command options while loading data into snowflake?
  5. What are different file format command options while loading data into snowflake? 6.How to debug data loading failure in snowflake?

SQL Script Used In Video

Following are the SQL script used in this video.

Table & File Format Along with Copy Command

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

create or replace transient table customer_csv (
	customer_pk number(38,0),
	salutation varchar(10),
	first_name varchar(20),
	last_name varchar(30),
	gender varchar(1),
	marital_status varchar(1),
	day_of_birth date,
	birth_country varchar(60),
	email_address varchar(50),
	city_name varchar(60),
	zip_code varchar(10),
	country_name varchar(20),
	gmt_timezone_offset number(10,2),
	preferred_cust_flag boolean,
	registration_time timestamp_ltz(9)
);

create or replace file format customer_csv_ff 
    type = 'csv' 
    compression = 'none' 
    field_delimiter = ','
    skip_header = 1 ;

-- field optionally enclosed
create or replace file format customer_csv_ff2
    type = 'csv' 
    compression = 'none' 
    field_delimiter = ','
    field_optionally_enclosed_by = '\042'
    skip_header = 1 ;

File Format for PSV & TSV Delimited Data File

1
2
3
4
5
6
7
8
9
10
11
12
13
14

create or replace file format customer_tsv_ff 
    type = 'csv' 
    compression = 'none' 
    field_delimiter = '\t'
    field_optionally_enclosed_by = '\042'
    skip_header = 1 ;

create or replace file format customer_tsv_ff 
    type = 'csv' 
    compression = 'none' 
    field_delimiter = '|'
    field_optionally_enclosed_by = '\042'
    skip_header = 1 ;

Copy Into Table Parameters

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

-- Option-1
-- On Error => Skip The file

COPY INTO "TTIPS"."CH01"."CUSTOMER_CSV" 
    FROM @/ui1664948747495 
    FILE_FORMAT = '"TTIPS"."CH01"."CUSTOMER_CSV_FF"' 
    ON_ERROR = 'SKIP_FILE' 
    PURGE = TRUE;
  
-- Option-2
-- On Error => Abort Statement

COPY INTO "TTIPS"."CH01"."CUSTOMER_CSV" 
    FROM @/ui1664948747495 
    FILE_FORMAT = '"TTIPS"."CH01"."CUSTOMER_CSV_FF"' 
    ON_ERROR = 'ABORT_STATEMENT' 
    PURGE = TRUE;

-- Option-3
-- On Error => Skip upto 10 error records
COPY INTO "TTIPS"."CH01"."CUSTOMER_CSV" 
    FROM @/ui1664948747495 
    FILE_FORMAT = '"TTIPS"."CH01"."CUSTOMER_CSV_FF"' 
    ON_ERROR = 'SKIP_FILE_10' 
    PURGE = TRUE;
    
-- Option-4
-- On Error => Continue even if there is an error
COPY INTO "TTIPS"."CH01"."CUSTOMER_CSV" 
    FROM @/ui1664948747495 
    FILE_FORMAT = '"TTIPS"."CH01"."CUSTOMER_CSV_FF"' 
    ON_ERROR = 'CONTINUE' 
    PURGE = TRUE;