Snowflake에서 테이블 생성 및 데이터를 채우는 다양한 방법

이번 게시물에서는 Snowflake에서 테이블을 만들고 COPY 명령어를 사용하지 않고 테이블에 데이터를 채우는 다양한 방법에 대해 알아보겠습니다.

방법 1: TABLE(GENERATOR(ROWCOUNT))

이 테이블 함수는 쿼리 런타임 중에 지정한 수 만큼의 행을 테이블에 생성합니다. 행을 생성할 때 Random(), SEQ8() 함수 등을 함께 사용하여 컬럼 값을 램덤하게 생성 할 수 있습니다.

아래 SQL 코드는 3억건의 행을 램덤 값으로 생성합니다.

CREATE OR REPLACE TABLE INVOICE (
 INVC_NO BIGINT,
 INVC_CD VARCHAR,
 AMT INTEGER
);

INSERT INTO INVOICE
SELECT
 (SEQ8())::BIGINT AS INVC_NO
 ,RANDSTR(2, RANDOM())::VARCHAR INVC_CD
 ,UNIFORM(1,999999,RANDOM(10003))::INTEGER AMT
FROM TABLE(GENERATOR(ROWCOUNT => 300000000));

방법 2: TABLE(SPLIT_TO_TABLE)

이 테이블 함수는 텍스트 데이터의 포지션 또는 구분자 기반으로 분할해야하는 시나리오에서 유용하게 사용 할 수 있습니다.

아래 SQL 코드는 “|” 구분자를 기반으로 데이터를 분할하여 테이블에 데이터를 채웁니다.

CREATE OR REPLACE TABLE CUSTOMERS
AS
SELECT 
 $1[0]::STRING CUSTOMER_NAME,
 $1[1]::STRING OCCUPATION,
 REPLACE($1[2], ',')::STRING SALARY,
 $1[3]::STRING BIRTH_PLACE
FROM (
 SELECT SPLIT(X.VALUE, '|') 
    FROM TABLE(SPLIT_TO_TABLE('김철수|의사|134,500|서울
                              공영구|야구선수|364,098|부산
                              이영희|변호사|110,120|인천', '\n')) x
 );

방법 3: MULTIPLE INSERT

아래 SQL 코드는 multi-rows 데이터를 정의하여 테이블을 생성합니다.

CREATE OR REPLACE TABLE CUSTOMERS(CUSTOMER_NAME STRING, OCCUPATION STRING(30), SALARY STRING, BIRTH_PLACE STRING)
AS 
SELECT * FROM VALUES
('김철수','의사','134,500','서울'),
('공영구','야구선수','364,098','부산'),
('이영희','변호사','110,120','인천');

방법 4: CREATE TABLE FROM ANOTHER TABLE

아래 SQL 코드는 변수를 설정하여 다른 테이블에서 조건에 맞는 데이터를 조회하여 테이블을 생성합니다.

SET SALARY_VARIABLE = '120000';

SELECT $SALARY_VARIABLE;

CREATE OR REPLACE TABLE CUSTOMERS_BKP AS
(SELECT * FROM CUSTOMERS WHERE SALARY >= $SALARY_VARIABLE);

방법 5: ZERO-COPY CLONE

아래 SQL 코드는 Snowflake의 Zero-copy clone 기능을 사용하여 원본 테이블의 물리적 데이터 복제 없이 메타데이터만 복제하여 동일한 데이터를 보유한 테이블을 빠르게 생성합니다.

CREATE TABLE INVOICE_CLONE CLONE INVOICE

방법 6: EXTERNAL FUNCTION CALL

아래 SQL 코드는 Snowflake의 API GW Integration 및 External Function 기능을 사용하여 외부 데이터를 Snowflake 테이블에 적재 할 수 있습니다.

자세한 사용 방법은 External Function 문서를 읽어 보시길 바랍니다.

CREATE OR REPLACE TABLE HTTP_DATA ( DATA VARCHAR, URL VARCHAR, PAYLOAD VARIANT );

INSERT INTO HTTP_DATA
SELECT 
 $1 DATA, 
 $2 URL, 
 EXT_UDF_FETCHHTTPDATA(URL) PAYLOAD
FROM (VALUES ('stations', 'https://gbfs.citibikenyc.com/gbfs/en/station_information.json'));

방법 7: CROSS JOIN

Cross Join을 사용하여 단숨에 수 억만건의 데이터를 테이블에 생성할 수 있습니다. 예를 들어, 한 테이블에 100개의 행이 있다고 가정하면, 아래 SQL 코드 예제와 같이 동일 테이블을 여러 번 cross-join 하는 방식으로 (100 x 100 x 100 x 30), 단 1분에 3천만 행을 생성 할 수 있습니다.

CREATE TABLE BIG_TABLE AS
SELECT
 RANDOM() AS Customer_ID,
 UUID_STRING() AS Customer_Name,
 UUID_STRING() AS Customer_Email,
 UUID_STRING() AS Customer_City,
 UUID_STRING() AS Customer_State
FROM INVOICE A 
      CROSS JOIN INVOICE B 
      CROSS JOIN INVOICE C 
      CROSS JOIN (SELECT TOP 30 * FROM INVOICE) D;