Snowflake Ninja

Working With External Tables in Snowflake

Working With External Tables in Snowflake

Complete Guide on Snowflake External Table

You can watch the complete hands on video tutorial

Find complete SQL Script Here Ch-11

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
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79


drop stage s3_customer_csv;
create stage s3_customer_csv 
url = 's3://my-s3-data-lake/customer/csv/' 
comment = 'this customer parquet data';
        
desc stage s3_customer_csv;
    
-- lets list the stage to see all the files and path 
list @s3_customer_csv;
    
-- create a csv file format
create file format csv_ff 
    type = 'csv' 
    compression = 'auto' 
    field_delimiter = ',' 
    record_delimiter = '\n' 
    skip_header = 0 
    field_optionally_enclosed_by = '\042' 
    null_if = ('\\N');
        
        
-- select external stage using $ notation
select t.$1, t.$2, t.$3,t.$4, t.$5, t.$6 , t.$7, t.$8 
from 
@s3_customer_csv (file_format => 'csv_ff') t; 
        
-- what if you give one. extra colum in your $ notation
select t.$1, t.$2, t.$3,t.$4, t.$5, t.$6 , t.$7, t.$8, t.$9 
from 
@s3_customer_csv (file_format => 'csv_ff') t; 

     
create or replace external TABLE customer_csv_et (
        CUST_KEY varchar AS (value:c1::varchar),
        NAME varchar AS (value:c2::varchar),
        ADDRESS varchar AS (value:c3::varchar),
        NATION_KEY varchar AS (value:c4::varchar),
        PHONE varchar AS (value:c5::varchar),
        ACCOUNT_BALANCE varchar AS (value:c6::varchar),
        MARKET_SEGMENT varchar AS (value:c7::varchar),
        COMMENT varchar AS (value:c8::varchar)
    )
with location=@s3_customer_csv
auto_refresh = false
file_format = (format_name = csv_ff)
;

-- lets see the extra value column and it is available in json format.
select * from customer_csv_et;


create or replace external TABLE customer_csv_et_dummy (
    )
with location=@s3_customer_csv
auto_refresh = false
file_format = (format_name = csv_ff);

-- select the table
select * from customer_csv_et;

-- fetch the value column and metadata column
select value, metadata$filename from customer_csv_et;

-- select clause
select value, metadata$filename from customer_csv_et where metadata$filename ='customer/csv/customer_003.csv';

-- if you are not sure about the column names etc, then you can simply add a dummy column and check all values
create or replace external TABLE customer_csv_et_dummy (
      col1 varchar AS (value:c1::varchar)
    )
    with location=@s3_customer_csv
    auto_refresh = false
    file_format = (format_name = csv_ff);

    select * from customer_csv_et_dummy;