ch05 01-columnmismatch
create or replace transient table col_mismatch_tbl (
bad_good_desc varchar,
run_cycle varchar,
col1 varchar,
col2 varchar,
col3 varchar,
col4 varchar
col5 varchar
);
put
file:////tmp/sample01.csv
@`/ch05/customer/csv
auto_compress=false
parralel=1;
list @~/ch05/customer/csv;
coonect snow sql
create or replace file format std_csv_ff
type = 'csv'
compression = 'none'
field_delimiter = ','
field_optionally_enclosed_by = '\042'
skip_header = 1 ;
select * from col_mismatch_tbl;
copy into col_mismatch_tbl
from @~/ch05/customer/csv/sample01.csv
file_format=std_csv_ff
on_error="abort_statement"
pattern='.*[.]csv';
select * from col_mismatch_tbl;
copy into col_mismatch_tbl
from @~/ch05/customer/csv/sample01.csv
file_format=std_csv_ff
on_error="continue"
pattern='.*[.]csv';
-------------------
put
file:////tmp/sample02.csv
@`/ch05/customer/csv
auto_compress=false
parralel=1;
list @~/ch05/customer/csv;
crate or replace file format col_mismatch_csv_ff
type = 'csv'
compression = 'none'
field_delimiter = ','
field_optionally_enclosed_by = '\042'
error_on_column_count_mismatch=false
skip_header = 1 ;
copy into col_mismatch_tbl
from @~/ch05/customer/csv/sample02.csv
file_format=colmismatch_csv_ff
on_error="abort_statement"
pattern='.*[.]csv';
_______________________________________________________
ch05 02-large-file
use warehouse ;
use role sysadmin;
use schema ttips.cho5;
remove @~/ch05/customer/csv/500k;
list @~/ch05/customer/csv/500k;
put
file ://///tmp/
@~/ch05/customer/csv/500k
parrlel=50;
connect to snowsql
put
create or replace transient table customer_3mil (
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_gzip_01
type = 'csv'
compression = 'gzip'
field_delimiter = ','
filed_optionaly_enclosed_by='\042'
skip_header = 1 ;
copy into customer_3m
from @~/ch05/customer/csv/500k
file_format=customer_csv_gzip_01
on_error="continue"
pattern='.*[.]csv';
select count(*) from customer_3m;
select * from customer_3m limit 10;
_______________________________________________
use warehouse xs_wh_02;
create or replace transient table customer_3mil_lesscol (
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),
);
create or replace file format customer_csv_gzip_02
type = 'csv'
compression = 'gzip'
field_delimiter = ','
filed_optionaly_enclosed_by='\042'
error_on_column_count_mismtch=true
skip_header = 1 ;
copy into customer_3mil_lesscol
from @~/ch05/customer/csv/500k
file_format=customer_csv_gzip_02
on_error="continue"
pattern='.*[.]csv';
select count(*) from customer_3mil_lesscol;
select * from customer_3m limit 10;
_____________________________________________________
use warehouse xs_wh_03;
create or replace transient table customer_3mil_lesscol (
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),
);
create or replace file format customer_csv_gzip_03
type = 'csv'
compression = 'gzip'
field_delimiter = ','
filed_optionaly_enclosed_by='\042'
error_on_column_count_mismtch=false
skip_header = 1 ;
copy into customer_3mil_lesscol
from @~/ch05/customer/csv/500k
file_format=customer_csv_gzip_03
on_error="continue"
pattern='.*[.]csv';
select count(*) from customer_3mil_lesscol;
select * from customer_3m limit 10;
________________________________________________
ch-06
snowsql cli
put
create or replace transient table user_email(
id number,
first_name varchar(100),
last_name varchar(100),
email varchar(100),
gender varchar(1),
about_me varchar(500)
);
*****************
create or replace file format csv_double_q_ff
type = 'csv'
compression = 'none'
field_delimiter = ','
record_delimiter = '\n'
skip_header = 1
field_optionally_enclosed_by = '\042'
trim_space = false
error_on_column_count_mismatch = true;
**********************
-- another file format where field_optionally_enclosed_by parameter
-- will take single quote (\047)
create or replace file format csv_single_q_ff
type = 'csv'
compression = 'none'
field_delimiter = ','
record_delimiter = '\n'
skip_header = 1
field_optionally_enclosed_by = '\047'
trim_space = false
error_on_column_count_mismatch = true;
"C:\tmp\01_sample_user_email.csv"
put
file:///tmp/*.csv
@~/cho6/csv/
auto_ompress=false;
list @~/cho6/csv/;
-- lets load our 1st sample file. this has 1k records and all of them should get
processed.
copy into user_email
from @~/ch06/csv/01_sample_user_email.csv
file_format = csv_double_q_ff
on_error = 'continue';
-- review the loaded data
select * from user_email;
-- Truncating before loading new file
truncate table user_email;
-- loading next file where about_me field is enclosed with double quotes.
copy into user_email
from @~/ch06/csv/02_sample_email_double_quotes.csv
file_format = csv_double_q_ff
on_error = 'continue';
-- review the loaded data
select * from user_email;
truncate table user_email;
-- loading next file where about_me field is enclosed with single quotes.
copy into user_email
from @~/ch06/csv/03_sample_email_single_quote.csv
file_format = csv_single_q_ff
on_error = 'continue';
-- review the loaded data
select * from user_email;
truncate table user_email;
truncate table user_email;
-- run a select command
select * from user_email;
-- lets see if we have the sample file #06 available in our user stage or not.
list @~/ch06/csv/06_sample_email_regex.csv;
-- file format
create or replace file format csv_double_q_ff
type = 'csv'
compression = 'none'
field_delimiter = ','
record_delimiter = '\n'
skip_header = 1
field_optionally_enclosed_by = '\042'
escape = '\134'
skip_blank_lines = true
trim_space = true;
copy into user_email
from
(
select $1, $2, $3, $4, $5, regexp_replace($6,'\\"|\'','')
from @~/ch06/csv/06_sample_email_regex.csv t
)
file_format = csv_double_q_ff
force = true
on_error = 'continue'
;
-- run a select command
select * from user_email;
