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
;
'Data > Data Engineering & Analystics' 카테고리의 다른 글
데이터 중심 애플리케이션 설계 : 1장 신뢰할 수 있고 확장 가능하며 유지보수하기 쉬운 애플리케이션 (0) | 2024.03.21 |
---|---|
[Snowflake] Snowflake-python 연결 (Snowflake를 Python으로 사용하는 방법. Snowflake Connector for Python) (0) | 2024.03.06 |
[Kafka] Kafka-python 설치 (0) | 2024.03.06 |
[Airflow] Airflow 설치하기(pip) (0) | 2024.03.06 |
[Airflow] 8. Airflow plugin 생성하기 (0) | 2024.03.06 |