Fri. Dec 4th, 2020

Quick post to go through external tables in snowflake and what could be the benefit of using them.

Quick reminder :

When queried, an external table reads data from a set of one or more files in a specified external stage and outputs the data in a single VARIANT column.

Additional columns can be defined, with each column definition consisting of a name, data type, and optionally whether the column requires a value (NOT NULL) or has any referential integrity constraints (primary key, foreign key, etc.). See the usage notes for more information.

--create test database
create or replace database test;
use database test;

--create file format
create or replace file format mycsv
type = 'csv'
field_delimiter = ','
skip_header = 1;

--create external stage 
create or replace stage mystage url = 's3://vfleaXXXect/'
credentials=(aws_key_id='XXXX' aws_secret_key='XXXXX')
file_format = mycsv;

--create external table without columns , it will be automatically VARIANT
create or replace external table sample_ext 
with location = @mystage file_format = mycsv;

--query that table
select * from sample_ext;
--the schema is one column type variant
describe table sample_ext;

--query the variant column
select value:c1 , value:c2 from sample_ext;


--create external table with columns
create or replace external table sample_ext 
(Year                       INT as  (value:c1::int),
Industry_aggregation_NZSIOC varchar as  (value:c2::varchar),
Industry_code_NZSIOC        varchar as  (value:c3::varchar),
Industry_name_NZSIOC        varchar as  (value:c4::varchar),
Units                       varchar as  (value:c4::varchar),
Variable_code               varchar as  (value:c5::varchar),
Variable_name               varchar as  (value:c6::varchar),
Variable_category           varchar as  (value:c7::varchar),
Industry_code_ANZSIC06      varchar as  (value:c8::varchar) )
with location = @mystage
file_format = mycsv;

--query that table, you see the table has VALUE and all the other columnar format
select * from sample_ext;


By Amine Hallam

Author & blogger on the latest data analytics technologies: Hadoop, Snowflake, Cloud ( AWS ), ETL .. etc

Leave a Reply

Your email address will not be published. Required fields are marked *