[Snowflake] Prac 4 - Stage

CHAN LIM·2023년 12월 29일
snowflakesnowsqlstage
0

Snowflake

목록 보기
7/19

0. Intro

목표 : Local에서 Snowflake로 Data를 전송한다.

1. Named Stage를 생성하여 Local에서 Stage로 데이터를 Put 한다.
2. Stage에서 DB Table로 데이터를 적재한다.
3. SnowSQL Log 확인 | SnowSight QueryHistory 확인

1. Named Stage 생성 및 Data Put

1.1 Named Stage 생성

$ ./snowsql -a <Snowflake 개인 URL> -u <개인 ID>

다음 명령어로 snowSQL에 접속한다.

> USE ROLE SYSADMIN;
> USE WAREHOUSE COMPUTE_WH;

역할과 웨어하우스를 설정한다.

> CREATE OR REPLACE DATABASE PRAC1;

데이터베이스를 생성한다.

  • 데이터베이스 생성 시,
    바로 생성한 데이터베이스와 그 안의 PUBLIC 스키마를 사용한다.

> CREATE OR REPLACE STAGE LOTOSN
  file_format = (type = 'CSV' field_delimiter = ',' skip_header = 1);

Named Stage 생성

  • stage 설정에 file_format이 존재한다.

    • TYPE = { CSV | JSON | AVRO | ORC | PARQUET | XML }

1.2 Put Data

> PUT file:///home/chan01/TestZone/start/Prac1.csv @LOTOSN;
> LIST @LOTOSN;

Stage에 Data Put 및 스테이징된 데이터 파일 보기

  • @ 문자는 자체가 명명된 스테이지를 식별한다는 점에 유의

2. Stage To Table

2.1 Table 생성

> CREATE OR REPLACE TABLE STTOSN
                                 (ID int,
                                 NAME varchar(32)
                                 );

적재할 테이블 생성

  • 실습할 데이터가 ID : NAME 형식이므로 맞춰서 생성

2.2 Stage To Table

// COPY INTO 시, Stage에 데이터 남아있음.
> COPY INTO LOSTSN FROM @LOTOSN;

// COPY INTO 시, Stage 데이터 삭제.
> COPY INTO LOSTSN FROM @LOTOSN PURGE = TRUE;

> SELECT * FROM <TABLE>

2.3 Prac 종료 및 정리

> LIST @LOTOSN;

> REMOVE @LOTOSN;

> TRUNCATE STTOSN;

> SELECT * FROM STTOSN;

> DROP TABLE STTOSN;

> DROP DATABASE PRAC1;

3. log 확인 및 QueryHistory 확인

3.1 log 확인

$ cat ~/.snowsql/log

log

2023-12-29 00:44:17,884 (7593/MainThread) snowflake.connector.connection INFO     connection:1171 - This connection is in OCSP Fail Open Mode. TLS Certificates would be checked for validity and revocation status. Any other Certificate Revocation related exceptions or OCSP Responder failures would be disregarded in favor of connectivity.
2023-12-29 00:44:18,201 (7593/Thread-1) snowflake.connector.cursor INFO         cursor:1028 - query: [SHOW /* snowsql */ DATABASES]
2023-12-29 00:44:18,203 (7593/Thread-2) snowflake.connector.cursor INFO         cursor:1028 - query: [SHOW /* snowsql */ WAREHOUSES]
2023-12-29 00:44:18,207 (7593/Thread-3) snowflake.connector.cursor INFO         cursor:1028 - query: [SHOW /* snowsql */ ROLES]
2023-12-29 00:44:18,211 (7593/Thread-4) snowflake.connector.cursor INFO         cursor:1028 - query: [SHOW /* snowsql */ USERS]
2023-12-29 00:44:18,317 (7593/Thread-1) snowflake.connector.cursor INFO         cursor:1041 - query execution done
2023-12-29 00:44:18,346 (7593/Thread-1) snowflake.connector.cursor INFO         cursor:1205 - Number of results in first chunk: 2
2023-12-29 00:44:18,394 (7593/Thread-2) snowflake.connector.cursor INFO         cursor:1041 - query execution done
2023-12-29 00:44:18,396 (7593/Thread-2) snowflake.connector.cursor INFO         cursor:1205 - Number of results in first chunk: 1
2023-12-29 00:48:00,178 (7593/MainThread) snowflake.connector.cursor INFO         cursor:1205 - Number of results in first chunk: 1
2023-12-29 00:48:00,184 (7593/Thread-49) snowflake.connector.cursor INFO         cursor:1028 - query: [SHOW /* snowsql */ DATABASES]
2023-12-29 00:48:00,189 (7593/Thread-50) snowflake.connector.cursor INFO         cursor:1028 - query: [SHOW /* snowsql */ WAREHOUSES]
2023-12-29 00:48:00,193 (7593/Thread-51) snowflake.connector.cursor INFO         cursor:1028 - query: [SHOW /* snowsql */ ROLES]
2023-12-29 00:48:00,196 (7593/Thread-52) snowflake.connector.cursor INFO         cursor:1028 - query: [SHOW /* snowsql */ USERS]
2023-12-29 00:48:00,282 (7593/Thread-49) snowflake.connector.cursor INFO         cursor:1041 - query execution done
2023-12-29 01:06:02,556 (7593/MainThread) snowflake.connector.cursor INFO         cursor:1205 - Number of results in first chunk: 1
2023-12-29 01:14:19,660 (7593/MainThread) snowflake.connector.cursor INFO         cursor:1028 - query: [CREATE OR REPLACE TABLE STTOSN;]
2023-12-29 01:14:19,843 (7593/MainThread) snowflake.connector.cursor INFO         cursor:1041 - query execution done
2023-12-29 01:14:19,846 (7593/MainThread) __main__ ERROR      eventloop:813  - error with sql: 'CREATE OR REPLACE TABLE STTOSN;'
Traceback (most recent call last):
  File "eventloop.py", line 757, in run_cli
  File "snowflake/cli/sqlexecute.py", line 491, in run
  File "snowflake/cli/sqlexecute.py", line 574, in execute_normal_sql
  File "snowflake/connector/cursor.py", line 1132, in execute
  File "snowflake/connector/errors.py", line 290, in errorhandler_wrapper
  File "snowflake/connector/errors.py", line 345, in hand_to_other_handler
  File "snowflake/connector/errors.py", line 221, in default_errorhandler
snowflake.connector.errors.ProgrammingError: 001003 (42000): 01b14d4a-0000-5691-0000-36c50001b246: SQL compilation error:
~
~
~
snowflake.connector.cursor INFO         cursor:1205 - Number of results in first chunk: 1
2023-12-29 01:20:18,740 (7593/MainThread) snowflake.connector.cursor INFO         cursor:1028 - query: [REMOVE @LOTOSN;]
2023-12-29 01:20:18,884 (7593/MainThread) snowflake.connector.cursor INFO         cursor:1041 - query execution done
2023-12-29 01:20:18,885 (7593/MainThread) snowflake.connector.cursor INFO         cursor:1205 - Number of results in first chunk: 1
2023-12-29 01:21:01,258 (7593/MainThread) snowflake.connector.cursor INFO         cursor:1028 - query: [TRUNCATE STTOSN;]
2023-12-29 01:21:01,472 (7593/MainThread) snowflake.connector.cursor INFO         cursor:1041 - query execution done
2023-12-29 01:21:01,474 (7593/MainThread) snowflake.connector.cursor INFO         cursor:1205 - Number of results in first chunk: 1
2023-12-29 01:21:06,333 (7593/MainThread) snowflake.connector.cursor INFO         cursor:1028 - query: [SELECT * FROM STTOSN;]
2023-12-29 01:21:06,406 (7593/MainThread) snowflake.connector.cursor INFO         cursor:1041 - query execution done
2023-12-29 01:21:06,408 (7593/MainThread) snowflake.connector.cursor INFO         cursor:1205 - Number of results in first chunk: 0
2023-12-29 01:21:16,538 (7593/MainThread) snowflake.connector.cursor INFO         cursor:1028 - query: [DROP TABLE STTOSN;]
2023-12-29 01:21:16,625 (7593/MainThread) snowflake.connector.cursor INFO         cursor:1041 - query execution done
2023-12-29 01:21:16,626 (7593/MainThread) snowflake.connector.cursor INFO         cursor:1205 - Number of results in first chunk: 1
2023-12-29 01:21:16,632 (7593/Thread-427) snowflake.connector.cursor INFO         cursor:1028 - query: [SHOW /* snowsql */ DATABASES]
2023-12-29 01:21:16,635 (7593/Thread-428) snowflake.connector.cursor INFO         cursor:1028 - query: [SHOW /* snowsql */ WAREHOUSES]
2023-12-29 01:21:16,640 (7593/Thread-429) snowflake.connector.cursor INFO         cursor:1028 - query: [SHOW /* snowsql */ ROLES]
2023-12-29 01:21:16,644 (7593/Thread-430) snowflake.connector.cursor INFO         cursor:1028 - query: [SHOW /* snowsql */ USERS]
2023-12-29 01:21:16,723 (7593/Thread-427) snowflake.connector.cursor INFO         cursor:1041 - query execution done
2023-12-29 01:21:16,724 (7593/Thread-427) snowflake.connector.cursor INFO         cursor:1205 - Number of results in first chunk: 3
2023-12-29 01:21:16,725 (7593/Thread-427) snowflake.connector.cursor INFO         cursor:1028 - query: [SHOW /* snowsql */ SCHEMAS IN DATABASE PRAC1]
2023-12-29 01:21:16,829 (7593/Thread-430) snowflake.connector.cursor INFO         cursor:1041 - query execution done
2023-12-29 01:21:16,831 (7593/Thread-430) snowflake.connector.cursor INFO         cursor:1205 - Number of results in first chunk: 2
2023-12-29 01:21:16,834 (7593/Thread-429) snowflake.connector.cursor INFO         cursor:1041 - query execution done
2023-12-29 01:21:16,840 (7593/Thread-429) snowflake.connector.cursor INFO         cursor:1205 - Number of results in first chunk: 8
2023-12-29 01:21:16,836 (7593/Thread-427) snowflake.connector.cursor INFO         cursor:1041 - query execution done
2023-12-29 01:21:16,841 (7593/Thread-427) snowflake.connector.cursor INFO         cursor:1205 - Number of results in first chunk: 2
2023-12-29 01:21:16,842 (7593/Thread-427) snowflake.connector.cursor INFO         cursor:1028 - query: [SHOW /* snowsql */ TABLES IN SCHEMA PRAC1.PUBLIC]
2023-12-29 01:21:16,838 (7593/Thread-428) snowflake.connector.cursor INFO         cursor:1041 - query execution done
2023-12-29 01:21:16,844 (7593/Thread-428) snowflake.connector.cursor INFO         cursor:1205 - Number of results in first chunk: 1
2023-12-29 01:21:16,896 (7593/Thread-427) snowflake.connector.cursor INFO         cursor:1041 - query execution done
2023-12-29 01:21:16,897 (7593/Thread-427) snowflake.connector.cursor INFO         cursor:1205 - Number of results in first chunk: 0
2023-12-29 01:21:16,898 (7593/Thread-427) snowflake.connector.cursor INFO         cursor:1028 - query: [SHOW /* snowsql */ VIEWS IN SCHEMA PRAC1.PUBLIC]
2023-12-29 01:21:16,954 (7593/Thread-427) snowflake.connector.cursor INFO         cursor:1041 - query execution done
2023-12-29 01:21:16,955 (7593/Thread-427) snowflake.connector.cursor INFO         cursor:1205 - Number of results in first chunk: 0
2023-12-29 01:21:16,957 (7593/Thread-427) snowflake.connector.cursor INFO         cursor:1028 - query: [SHOW /* snowsql */ USER FUNCTIONS IN SCHEMA PRAC1.PUBLIC]
2023-12-29 01:21:17,012 (7593/Thread-427) snowflake.connector.cursor INFO         cursor:1041 - query execution done
2023-12-29 01:21:17,014 (7593/Thread-427) snowflake.connector.cursor INFO         cursor:1205 - Number of results in first chunk: 0
2023-12-29 01:21:17,015 (7593/Thread-427) snowflake.connector.cursor INFO         cursor:1028 - query: [SHOW /* snowsql */ STAGES IN SCHEMA PRAC1.PUBLIC]
2023-12-29 01:21:17,075 (7593/Thread-427) snowflake.connector.cursor INFO         cursor:1041 - query execution done
2023-12-29 01:21:17,076 (7593/Thread-427) snowflake.connector.cursor INFO         cursor:1205 - Number of results in first chunk: 1
2023-12-29 01:21:17,077 (7593/Thread-427) snowflake.connector.cursor INFO         cursor:1028 - query: [SHOW /* snowsql */ COLUMNS IN SCHEMA PRAC1.PUBLIC]
2023-12-29 01:21:17,133 (7593/Thread-427) snowflake.connector.cursor INFO         cursor:1041 - query execution done
2023-12-29 01:21:17,134 (7593/Thread-427) snowflake.connector.cursor INFO         cursor:1205 - Number of results in first chunk: 0
2023-12-29 01:21:25,293 (7593/MainThread) snowflake.connector.cursor INFO         cursor:1028 - query: [DROP DATABASE PRAC1;]
2023-12-29 01:21:25,392 (7593/MainThread) snowflake.connector.cursor INFO         cursor:1041 - query execution done
2023-12-29 01:21:25,393 (7593/MainThread) snowflake.connector.cursor INFO         cursor:1205 - Number of results in first chunk: 1
2023-12-29 01:21:25,400 (7593/Thread-444) snowflake.connector.cursor INFO         cursor:1028 - query: [SHOW /* snowsql */ DATABASES]
2023-12-29 01:21:25,404 (7593/Thread-445) snowflake.connector.cursor INFO         cursor:1028 - query: [SHOW /* snowsql */ WAREHOUSES]
2023-12-29 01:21:25,407 (7593/Thread-446) snowflake.connector.cursor INFO         cursor:1028 - query: [SHOW /* snowsql */ ROLES]
2023-12-29 01:21:25,410 (7593/Thread-447) snowflake.connector.cursor INFO         cursor:1028 - query: [SHOW /* snowsql */ USERS]
2023-12-29 01:21:25,474 (7593/Thread-446) snowflake.connector.cursor INFO         cursor:1041 - query execution done
2023-12-29 01:21:25,476 (7593/Thread-446) snowflake.connector.cursor INFO         cursor:1205 - Number of results in first chunk: 8
2023-12-29 01:21:25,481 (7593/Thread-445) snowflake.connector.cursor INFO         cursor:1041 - query execution done
2023-12-29 01:21:25,483 (7593/Thread-444) snowflake.connector.cursor INFO         cursor:1041 - query execution done
2023-12-29 01:21:25,485 (7593/Thread-444) snowflake.connector.cursor INFO         cursor:1205 - Number of results in first chunk: 2
2023-12-29 01:21:25,485 (7593/Thread-444) snowflake.connector.cursor INFO         cursor:1028 - query: [SHOW /* snowsql */ SCHEMAS IN DATABASE PRAC1]
2023-12-29 01:21:25,489 (7593/Thread-447) snowflake.connector.cursor INFO         cursor:1041 - query execution done
2023-12-29 01:21:25,491 (7593/Thread-447) snowflake.connector.cursor INFO         cursor:1205 - Number of results in first chunk: 2
2023-12-29 01:21:25,484 (7593/Thread-445) snowflake.connector.cursor INFO         cursor:1205 - Number of results in first chunk: 1
2023-12-29 01:21:25,557 (7593/Thread-444) snowflake.connector.cursor INFO         cursor:1041 - query execution done
2023-12-29 01:21:25,559 (7593/Thread-444) snowflake.connector.cursor INFO         cursor:1028 - query: [SHOW /* snowsql */ TABLES IN SCHEMA PRAC1.PUBLIC]
2023-12-29 01:21:25,614 (7593/Thread-444) snowflake.connector.cursor INFO         cursor:1041 - query execution done
2023-12-29 01:21:25,617 (7593/Thread-444) snowflake.connector.cursor INFO         cursor:1028 - query: [SHOW /* snowsql */ VIEWS IN SCHEMA PRAC1.PUBLIC]
2023-12-29 01:21:25,678 (7593/Thread-444) snowflake.connector.cursor INFO         cursor:1041 - query execution done
2023-12-29 01:21:25,681 (7593/Thread-444) snowflake.connector.cursor INFO         cursor:1028 - query: [SHOW /* snowsql */ USER FUNCTIONS IN SCHEMA PRAC1.PUBLIC]
2023-12-29 01:21:25,736 (7593/Thread-444) snowflake.connector.cursor INFO         cursor:1041 - query execution done
2023-12-29 01:21:25,738 (7593/Thread-444) snowflake.connector.cursor INFO         cursor:1028 - query: [SHOW /* snowsql */ STAGES IN SCHEMA PRAC1.PUBLIC]
2023-12-29 01:21:25,797 (7593/Thread-444) snowflake.connector.cursor INFO         cursor:1041 - query execution done
2023-12-29 01:21:25,800 (7593/Thread-444) snowflake.connector.cursor INFO         cursor:1028 - query: [SHOW /* snowsql */ COLUMNS IN SCHEMA PRAC1.PUBLIC]
2023-12-29 01:21:25,865 (7593/Thread-444) snowflake.connector.cursor INFO         cursor:1041 - query execution done
2023-12-29 01:23:41,483 (7593/MainThread) snowflake.connector.cursor INFO         cursor:1028 - query: [quit;]
2023-12-29 01:23:41,572 (7593/MainThread) snowflake.connector.cursor INFO         cursor:1041 - query execution done
2023-12-29 01:23:41,575 (7593/MainThread) __main__ ERROR      eventloop:813  - error with sql: 'quit;'
Traceback (most recent call last):
  File "eventloop.py", line 757, in run_cli
  File "snowflake/cli/sqlexecute.py", line 491, in run
  File "snowflake/cli/sqlexecute.py", line 574, in execute_normal_sql
  File "snowflake/connector/cursor.py", line 1132, in execute
  File "snowflake/connector/errors.py", line 290, in errorhandler_wrapper
  File "snowflake/connector/errors.py", line 345, in hand_to_other_handler
  File "snowflake/connector/errors.py", line 221, in default_errorhandler
snowflake.connector.errors.ProgrammingError: 001003 (42000): 01b14d53-0000-5691-0000-36c50001b2c6: SQL compilation error:
syntax error line 1 at position 0 unexpected 'quit'.
2023-12-29 01:23:47,656 (7593/MainThread) snowflake.cli.sqlexecute INFO     sqlexecute:395  - rollback
2023-12-29 01:23:47,656 (7593/MainThread) snowflake.connector.cursor INFO         cursor:1028 - query: [ROLLBACK]
2023-12-29 01:23:47,726 (7593/MainThread) snowflake.connector.cursor INFO         cursor:1041 - query execution done
2023-12-29 01:23:47,727 (7593/MainThread) snowflake.connector.cursor INFO         cursor:1205 - Number of results in first chunk: 1
2023-12-29 01:23:47,729 (7593/MainThread) snowflake.connector.connection INFO     connection:718  - closed
2023-12-29 01:23:47,764 (7593/MainThread) snowflake.connector.connection INFO     connection:724  - No async queries seem to be running, deleting session

3.2 QueryHistory

$ cat .snowsql/history

history

# 2023-12-29 09:48:00.020218
+USE ROLE SYSADMIN;
# 2023-12-29 09:48:17.114744
+USE WAREHOUSE COMPUTE_WH;
# 2023-12-29 09:51:21.872786
+CREATE OR REPLACE DATABASE PRAC1;
# 2023-12-29 09:56:00.848084
+CREATE OR REPLACE STAGE LOTOSN
+file_format = (type = 'CSV' field_delimiter = ',' skip_header = 1);
# 2023-12-29 10:05:44.266951
+PUT file:///home/chan01/TestZone/start/Prac1.csv @LOTOSN;
# 2023-12-29 10:06:02.446232
+LIST @LOTOSN;
# 2023-12-29 10:14:19.656538
+CREATE OR REPLACE TABLE STTOSN;
# 2023-12-29 10:14:51.521931
+CREATE OR REPLACE TABLE STTOSN
+(ID int,
+NAME varchar(32)
+);
# 2023-12-29 10:17:42.254603
+COPY INTO STTOSN FROM @LOTOSN;
# 2023-12-29 10:18:34.799037
+SELECT * FROM STTOSN;
# 2023-12-29 10:20:12.275897
+LIST @LOTOSN;
# 2023-12-29 10:20:18.738535
+REMOVE @LOTOSN;
# 2023-12-29 10:21:01.256302
+TRUNCATE STTOSN;
# 2023-12-29 10:21:06.330875
+SELECT * FROM STTOSN;
# 2023-12-29 10:21:16.535432
+DROP TABLE STTOSN;
# 2023-12-29 10:21:25.290777
+DROP DATABASE PRAC1;
# 2023-12-29 10:23:41.480506
+quit;
# 2023-12-29 10:23:47.654088
+!quit;

또는 SnowSight에서,

profile
CHAN LIM
클라우드, 데이터, DevOps 엔지니어 지향 || 글보단 사진 지향
이전 포스트

[Snowflake] Prac 3 - QueryHistory

다음 포스트

[Snowflake] Prac 5 - UDF, 저장 프로시저

0개의 댓글