본문 바로가기
Data/Data Engineering & Analystics

[Snowflake] S3 Load Data

by DenverAlmighty 2024. 3. 6.

Snowflake 연동

Load Data

-- create date format
create or replace file format lightening_csv
  type = csv
  field_delimiter = ','
  skip_header = 0
  null_if = ('NULL', 'null')
  empty_field_as_null = true;

  
-- WEB UI로
-- CREATE STAGE my_s3_stage
--   STORAGE_INTEGRATION = s3_int
--   URL = 's3://iron-heritage/'
--   FILE_FORMAT = lightening_csv;

-- create table 
CREATE TABLE test_table
  USING TEMPLATE (
    SELECT ARRAY_AGG(OBJECT_CONSTRUCT(*))
    -- WITHIN GROUP (ORDER BY order_id)
      FROM TABLE(
        INFER_SCHEMA(
          LOCATION=>'@IRON_HERITAGE_STAGE',
          FILE_FORMAT=>'lightening_csv'
        )
      ));

-- copy data from stag .log file
COPY INTO test_table
  FROM @IRON_HERITAGE_STAGE
  pattern='.*log.*[.]log';

table

select * from test_table;
DESC TABLE test_table;

-- rename column 
ALTER TABLE test_table RENAME COLUMN 
"c3" TO "name"
;

-- count by name
select "name", count(*) as "cnt"
from test_table
group by "name"
order by "cnt" desc
;