[Snowflake] data insert 하는 방법 (1) snowlight worksheet SQL

snowflake 공식 문서의 chapter6에서는 3가지 방법으로 snowflake에서 data를 loading하고 unloading하는 방법을 다룬다. data loading은 insert를 뜻하는데, 그 중 첫번째 방법을 사용해봤다. 

1. snowlight worksheet를 통해 SQL insert문 사용하기
2. classic console에서 snow wizard 사용하기
3. snowSQL에서 CLI (command-line interface) 사용하기

 

(1) row 하나씩 insert 하기 (INSERT INTO) : structured / semi-structured data

<structured data>

INSERT INTO, VALUE절을 통해 insert할 수 있다.

--CREATE TABLE
CREATE OR REPLACE TABLE TABLE1
(id integer, f_name string, l_name string, city string)
COMMENT = "Single-Row Insert for Structured Data using Explicitly Specified Values";

--INSERT DATA
INSERT INTO TABLE1 (id, f_name, l_name, city)
VALUES (1, 'Anthony', 'Robinson', 'Atlanta');

--INSERT DATA
INSERT INTO TABLE1 (id, f_name, l_name, city)
VALUES (2, 'Peggy', 'Mathison', 'Birmingham');

TABLE1을 조회해보면 아래와 같이 data가 insert된 것을 확인할 수 있다.

 

<semi-structured data> 

테이블 생성 시 VARIANT type으로 설정해줘야 하며, structured data 처럼  VALUES 절 사용할 수 없다. 아래와 같은 QUERY절을 사용해야 한다.

--CREATE TABLE
CREATE OR REPLACE TABLE TABLE2
(id integer, variant1 variant)
COMMENT = "Single-Row Insert for Semi-Structured JSON Data";

--INSERT DATA
INSERT INTO TABLE2 (id, variant1)
SELECT 1, parse_json(' {"f_name": "Anthony", "l_name": "Robinson", "city": "Atlanta"} ');

--INSERT DATA
INSERT INTO TABLE2 (id, variant1)
SELECT 2, parse_json(' {"f_name": "Peggy", "l_name": "Mathison", "city": "Birminham"} ');

 

(2) multiple row insert 하기

single row와 syntax가 크게 다르지 않다.

<structured data>

--CREATE TABLE
CREATE OR REPLACE TABLE TABLE3
(id integer, f_name string, l_name string, city string)
COMMENT = "Multi-Row Insert for Structured Data using Explicitly Specified Values";

--INSERT DATA
INSERT INTO TABLE3 (id, f_name, l_name, city)
VALUES (1, 'Anthony', 'Robinson', 'Atlanta'), (2, 'Peggy', 'Mathison', 'Birmingham');

<semi-structured data>

--CREATE TABLE
CREATE OR REPLACE TABLE TABLE11
(id integer, variant1 variant)
COMMENT = "Multi-Row Insert for Semi-Structured JSON Data";

--INSERT DATA
INSERT INTO TABLE11
 select parse_json(column1)
 from values
 ('{ "_id": "1",
 "name": { "first": "Anthony", "last": "Robinson" },
 "company": "Pascal",
 "email": "anthony@pascal.com",
 "phone": "+1 (999) 444-2222"}'),
 ('{ "id": "2",
 "name": { "first": "Peggy", "last": "Mathison" },
 "company": "Ada",
 "email": "Peggy@ada.com",
 "phone": "+1 (999) 555-3333"}');
SELECT * FROM TABLE11;

해당 예제에서 id와 _id의 입력된 순서를 보면 key는 내가 작성한 순서가 아닌 알파벳 순서로 입력이 된 것을 볼 수 있다.

 

(3) 다른 TABLE의 내용을 가져와서 새로운 TABLE에 INSERT

TABLE3의 일부를 TABLE4에 INSERT하기

--CREATE TABLE
CREATE OR REPLACE TABLE TABLE4
 (id integer, f_name string, l_name string, city string)
COMMENT = "Multi-row Insert for Structured Data using Query, All Columns Same";

--INSERT DATA
INSERT INTO TABLE4 (id, f_name, l_name, city)
 SELECT * FROM TABLE3 WHERE CONTAINS (city, 'Atlanta');

TABLE5의 일부 컬럼 값만 TABLE3의 값을 insert 하려고 하는 경우 반드시 TABLE3의 어떤 컬럼에서 값을 가져오는지도 명시해줘야 한다. 

--CREATE TABLE
CREATE OR REPLACE TABLE TABLE5
 (id integer, f_name string, l_name string, city string)
COMMENT = "Multi-row Insert for Structured Data using Query, Fewer Columns";

--INSERT --> ERROR
INSERT INTO TABLE5 (id, f_name, l_name)
 SELECT * FROM TABLE3 WHERE CONTAINS (city, 'Atlanta');
 
 --INSERT --> CORRECT
 INSERT INTO TABLE5 (id, f_name, l_name)
 SELECT id, f_name, l_name FROM TABLE3 WHERE CONTAINS (city, 'Atlanta');

multi row insert하기

--CREATE TABLE
CREATE OR REPLACE TABLE TABLE6
 (id integer, first_name string, last_name string, city_name string)
COMMENT = "Table to be used as part of next demo";

--INSERT DATA
INSERT INTO TABLE6 (id, first_name, last_name, city_name) VALUES
 (1, 'Anthony', 'Robinson', 'Atlanta'),
 (2, 'Peggy', 'Mathison', 'Birmingham');

 

(4) CTE(common table expression)를 이용해서 insert하기

* CTE를 사용하는 이유 : 코드 가독성, 유지보수를 용이하게 하기 위해

<WITH>

--CREATE TABLE
CREATE OR REPLACE TABLE TABLE7
 (id integer, f_name string, l_name string, city string)
COMMENT = "Multi-row Insert for Structured Data using CTE";

--INSERT DATA (WITH)
INSERT INTO TABLE7 (id, f_name, l_name, city)
 WITH CTE AS
 (SELECT id, first_name as f_name, last_name as l_name,
city_name as city FROM TABLE6);

<JOIN>

TABLE8, TABLE9를 JOIN한 DATA를 TABLE10에 INSERT

<structured data>

-- CREATE TABLE
CREATE OR REPLACE TABLE TABLE8
 (id integer, f_name string, l_name string, zip_code string)
COMMENT = "Table to be used as part of next demo";

--INSERT DATA
INSERT INTO TABLE8 (id, f_name, l_name, zip_code)
VALUES (1, 'Anthony', 'Robinson', '30301'), (2, 'Peggy', 'Mathison', '35005');

-- CREATE TABLE
CREATE OR REPLACE TABLE TABLE9
(id integer, zip_code string, city string, state string)
COMMENT = "Table to be used as part of next demo";

--INSERT DATA
INSERT INTO TABLE9 (id, zip_code, city, state) VALUES
 (1, '30301', 'Atlanta', 'Georgia'),
 (2, '35005', 'Birmingham', 'Alabama');

--CREATE TABLE
CREATE OR REPLACE TABLE TABLE10
 (id integer, f_name string, l_name string, city string,
 state string, zip_code string)
COMMENT = "Multi-row inserts from two tables using an Inner JOIN on zip_code";

--INSERT DATA (JOIN)
INSERT INTO TABLE10 (id, f_name, l_name, city, state, zip_code)
SELECT a.id, a.f_name, a.l_name, b.city, b.state, a.zip_code
FROM TABLE8 a
 INNER JOIN TABLE9 b o

<semi-structured data>

CREATE
OR REPLACE TABLE TABLE11
 (variant1 variant)
COMMENT = "Multi-row Insert for Semi-structured JSON Data";

INSERT INTO TABLE11
 select parse_json(column1)
 from values
 ('{ "_id": "1",
 "name": { "first": "Anthony", "last": "Robinson" },
 "company": "Pascal",
 "email": "anthony@pascal.com",
 "phone": "+1 (999) 444-2222"}'),
 ('{ "id": "2",
 "name": { "first": "Peggy", "last": "Mathison" },
 "company": "Ada",
 "email": "Peggy@ada.com",
 "phone": "+1 (999) 555-3333"}');
SELECT * FROM TABLE11;

 

(5) multi table에 insert하기

INSERT ALL을 이용해 TABLE12를 TABLE13, TABLE14 두개의 TABLE에 한번에 INSERT

--CREATE TABLE
CREATE OR REPLACE TABLE TABLE12
(id integer, first_name string, last_name string, city_name string)
COMMENT = "Source Table to be used as part of next demo for Unconditional Table
Inserts";

--INSERT DATA
INSERT INTO TABLE12 (id, first_name, last_name, city_name) VALUES
(1, 'Anthony', 'Robinson', 'Atlanta'), (2, 'Peggy', 'Mathison', 'Birmingham');

--CREATE TABLE
CREATE OR REPLACE TABLE TABLE13
(id integer, f_name string, l_name string, city string)
COMMENT = "Unconditional Table Insert - Destination Table 1 for unconditional
 multi-table insert";
 
--CREATE TABLE
CREATE OR REPLACE TABLE TABLE14
(id integer, f_name string, l_name string, city string)
COMMENT = "Unconditional Table Insert - Destination Table 2 for unconditional
 multi-table insert";
 
 --INSERT DATA (MULTITABLE INSERT)
 INSERT ALL
 INTO TABLE13
 INTO TABLE13 (id, f_name, l_name, city)
 VALUES (id, last_name, first_name, default)
 INTO TABLE14 (id, f_name, l_name, city)
 INTO TABLE14 VALUES (id, city_name, last_name, first_name)
SELECT id, first_name, last_name, city_name FROM TABLE12;

TABLE13의 1,2 row는 전체를 INSERT 했고, 3,4 row에는 CITY 값을 default로 입력했기 때문에 null값이 생성됨을 확인할 수 있다.  TABLE14에는 1,2 row에는 각 컬럼별 INSERT를 하고, 3,4 row에는 VALUE의 순서를 변경해서 INSERT 했기 때문에 1,2와 3,4번째 row의 값이  다른 것을 확인할 수 있다.

TABLE12

TABLE13 / TABLE14

(6) ARRAY TYPE INSERT

array type는 semi-constructed type이므로 table생성시 variant로 설정한다. syntax는 다음과 같다.

ARRAY_INSERT (<array>, <position>, <new element> )

--CREATE TABLE
CREATE OR REPLACE TABLE TABLE18
 (Array variant)
COMMENT = "Insert Array";

--INSERT DATA
INSERT INTO TABLE18
SELECT ARRAY_INSERT(array_construct(0, 1, 2, 3), 4, 4);

INSERT INTO TABLE18
SELECT ARRAY_INSERT(array_construct(0, 1, 2, 3), 7, 4);

두번째 INSERT에서는 7번째 위치에 4를 입력했다. 4~6번째에는 값이 없으므로 undefined가 insert됨을 확인 할 수 있다.

 

(7) OBJECT TYPE INSERT

object type은 semi-constructed type이므로 table생성시 variant로 설정한다. 

OBJECT_INSERT (<object>, <key>, <value>)

CREATE OR REPLACE TABLE TABLE19
 (Object variant)
COMMENT = "Insert Object";

INSERT INTO TABLE19
 SELECT OBJECT_INSERT(OBJECT_CONSTRUCT('a', 1, 'b', 2, 'c', 3), 'd', 4);

INSERT INTO TABLE19 SELECT
 OBJECT_INSERT(object_construct('a', 1, 'b', 2, 'c', 3), 'd', ' ');
INSERT INTO TABLE19 SELECT
 OBJECT_INSERT(object_construct('a', 1, 'b', 2, 'c', 3), 'd', 'null');
INSERT INTO TABLE19 SELECT
 OBJECT_INSERT(object_construct('a', 1, 'b', 2, 'c', 3), 'd', null);
INSERT INTO TABLE19 SELECT
 OBJECT_INSERT(object_construct('a', 1, 'b', 2, 'c', 3), null, 'd');

OBJECT type에서는 key와 value중 하나라도 값이 null이면 insert되지 않는다.

 

이번 예제에서 생성한 테이블과 작성했던 comment를 조회해보자

SHOW TABLES LIKE '%TABLE%';