SingleStoreDB MPSQL을 이용한 View 생성 배치 처리

이번 포스트는 기준 Table 의 컬럼 중 특정 Column 만 선택하여 View 를 배치로 생성하는 예제입니다.

요구 사항은 다음과 같습니다.
1) View 를 만들 Table 은 tlist 테이블에 tname, vname 으로 기술됨
2) PK 컬럼은 id 로 동일하다고 가정
3) 기준 테이블의 column 명은 'var'+ 시퀀스 의 형식이며 view 에서는 newcol 로 컬럼명 변경
4) View 에 포함될 컬럼은 use_yn = 'y' 로 확인 가능
5) 컬럼 변경 조건은 clist 테이블에 tname, orgcol, newcol, use_yn 으로 기술됨

위의 조건을 가진 Sample Schema 및 데이터 입력은 다음과 같습니다.

create table t1 (
 id bigint primary key,
 var1 int,
 var2 int,
 var3 int,
 var4 int,
 var5 int,
 var6 int,
 var7 int,
 var8 int,
 var9 int,
 var10 int 
);

create table t2 (
 id bigint primary key,
 var1 int,
 var2 int,
 var3 int,
 var4 int,
 var5 int,
 var6 int,
 var7 int,
 var8 int,
 var9 int,
 var10 int 
);

create table t3 (
 id bigint primary key,
 var1 int,
 var2 int,
 var3 int,
 var4 int,
 var5 int,
 var6 int,
 var7 int,
 var8 int,
 var9 int,
 var10 int 
);

create table tlist (
 tname varchar(100), 
 vname varchar(100)
);

insert into tlist  values 
('t1', 'v1'), ('t2', 'v2'), ('t3', 'v3');

create table clist (
 tname varchar(100),
 orgcol varchar(100),
 newcol varchar(100),
 use_yn varchar(1) 
);

delete from clist;
insert into clist values 
 ('t1', '1', 'val1', 'y'),
 ('t1', '2', 'val2', 'y'),
 ('t1', '3', 'val3', 'n'),
 ('t1', '4', 'val4', 'n'),
 ('t1', '5', 'val5', 'n'),
 ('t1', '6', 'val6', 'y'),
 ('t1', '7', 'val7', 'y'),
 ('t1', '8', 'val8', 'n'),
 ('t1', '9', 'val9', 'n'),
 ('t1', '10', 'val10', 'y'),
 ('t2', '1', 'val1', 'y'),
 ('t2', '2', 'val2', 'n'),
 ('t2', '3', 'val3', 'y'),
 ('t2', '4', 'val4', 'n'),
 ('t2', '5', 'val5', 'y'),
 ('t2', '6', 'val6', 'n'),
 ('t2', '7', 'val7', 'y'),
 ('t2', '8', 'val8', 'n'),
 ('t2', '9', 'val9', 'y'),
 ('t2', '10', 'val10', 'n'),
 ('t3', '1', 'val1', 'n'),
 ('t3', '2', 'val2', 'n'),
 ('t3', '3', 'val3', 'n'),
 ('t3', '4', 'val4', 'n'),
 ('t3', '5', 'val5', 'n'),
 ('t3', '6', 'val6', 'y'),
 ('t3', '7', 'val7', 'y'),
 ('t3', '8', 'val8', 'y'),
 ('t3', '9', 'val9', 'y'),
 ('t3', '10', 'val10', 'y')
;

create or replace function gen_rows(n bigint)
returns table as return
select row_number() over () as rn from table(create_array(n):>array(bigint));

insert into t1 select rn, rn, rn, rn, rn, rn, rn, rn, rn, rn, rn from gen_rows(10000);
insert into t2 select rn, rn, rn, rn, rn, rn, rn, rn, rn, rn, rn from gen_rows(10000);
insert into t3 select rn, rn, rn, rn, rn, rn, rn, rn, rn, rn, rn from gen_rows(10000);

t1, t2, t3 에서 각각 v1, v2, v3 의 view 를 만들되 use_yn 이 'y' 인 컬럼들만을 대상으로 orgcol 의 순서에 맞춰 newcol 이라는 column alias 로 변경하면 됩니다.

먼저 tname 과 vname 을 인수로 받아들여 create view 명령어를 수행하는 procedure 를 작성합니다. 다음 몇 가지 포인트를 참고하세요.

※ EXECUTE IMMEDIATE : Dynamic SQL 수행
1) 동일한 이름의 View 가 있을 경우 Drop
2) Column 명을 Alias 로 변경하는 SELECT SQL 을 수행 후 결과를 sel_list 변수에 저장
3) 이 때 concat, group_concat, concat_ws 등의 함수 활용
4) CREATE VIEW 명령어 수행

DELIMITER //
CREATE OR REPLACE PROCEDURE create_view(tname VARCHAR(100), vname VARCHAR(100)) AS
DECLARE
  stmt1 TEXT;
  stmt2 TEXT;
  stmt3 TEXT;
  sel_list TEXT;
BEGIN
  EXECUTE IMMEDIATE CONCAT('DROP VIEW IF EXISTS ', vname); 
  stmt1 = CONCAT('CREATE VIEW ', vname, ' AS SELECT id, ');
  sel_list = CONCAT("select group_concat(concat_ws(' as ', concat('var',orgcol), newcol) order by orgcol:>int) from clist where tname = '", tname, "' and use_yn = 'y'");
  stmt3 = CONCAT(" from ", tname); 
  EXECUTE IMMEDIATE sel_list into stmt2;
  EXECUTE IMMEDIATE CONCAT(stmt1, stmt2, stmt3);
END
//
DELIMITER ;

이제 tlist 를 읽어 각 row 별로 create_view 프로시져를 수행하는 cv_batch 프로시져를 작성합니다.

※ Query, Array Type 활용: SQL Query 및 해당 Query 수행 후 데이터 처리할 수 있는 Data Type

1) qry 를 query type 으로 명시한 후 SELECT tname, vname FROM tlist 지정
2) COLLECT 명령어를 수행하여 결과를 arr ARRAY 에 저장
3) FOR LOOP 반복문에서 각 record 별로 tname, vname 을 추출, create_view 호출 시 인수로 사용
4) ECHO SELECT 문으로 결과 메시지 출력

DELIMITER //
CREATE OR REPLACE PROCEDURE cv_batch() AS
DECLARE
  qry QUERY(tname VARCHAR(100), vname VARCHAR(100)) = SELECT tname, vname FROM tlist;
  arr ARRAY(RECORD(tname VARCHAR(100), vname VARCHAR(100)));
  _tname VARCHAR(100);
  _vname VARCHAR(100);
BEGIN
  arr = COLLECT(qry);
  FOR x in arr LOOP
    _tname = x.tname;
    _vname = x.vname;
    CALL create_view(_tname, _vname);
    ECHO SELECT CONCAT(_vname, " is created") as message;
  END LOOP;
END
//
DELIMITER ;

이제 실제 cv_batch() 프로시져를 수행하겠습니다.

singlestore> show tables;
+----------------+
| Tables_in_test |
+----------------+
| clist          |
| t1             |
| t2             |
| t3             |
| tlist          |
+----------------+
5 rows in set (0.01 sec)

singlestore> call cv_batch();
+---------------+
| message       |
+---------------+
| v2 is created |
+---------------+
1 row in set (0.01 sec)

+---------------+
| message       |
+---------------+
| v3 is created |
+---------------+
1 row in set (0.10 sec)

+---------------+
| message       |
+---------------+
| v1 is created |
+---------------+
1 row in set (0.11 sec)

Query OK, 0 rows affected (0.11 sec)

singlestore> show tables extended;
+----------------+------------+-------------+--------------+
| Tables_in_test | Table_type | distributed | Storage_type |
+----------------+------------+-------------+--------------+
| clist          | BASE TABLE |           1 | COLUMNSTORE  |
| t1             | BASE TABLE |           1 | COLUMNSTORE  |
| t2             | BASE TABLE |           1 | COLUMNSTORE  |
| t3             | BASE TABLE |           1 | COLUMNSTORE  |
| tlist          | BASE TABLE |           1 | COLUMNSTORE  |
| v1             | VIEW       |           0 | NULL         |
| v2             | VIEW       |           0 | NULL         |
| v3             | VIEW       |           0 | NULL         |
+----------------+------------+-------------+--------------+
8 rows in set (0.01 sec)

singlestore> select * from v1 limit 5;
+----+------+------+------+------+-------+
| id | val1 | val2 | val6 | val7 | val10 |
+----+------+------+------+------+-------+
| 21 |   21 |   21 |   21 |   21 |    21 |
| 25 |   25 |   25 |   25 |   25 |    25 |
| 31 |   31 |   31 |   31 |   31 |    31 |
| 39 |   39 |   39 |   39 |   39 |    39 |
| 42 |   42 |   42 |   42 |   42 |    42 |
+----+------+------+------+------+-------+
5 rows in set (0.29 sec)

singlestore> select * from v2 limit 5;
+----+------+------+------+------+------+
| id | val1 | val3 | val5 | val7 | val9 |
+----+------+------+------+------+------+
| 21 |   21 |   21 |   21 |   21 |   21 |
| 25 |   25 |   25 |   25 |   25 |   25 |
| 31 |   31 |   31 |   31 |   31 |   31 |
| 39 |   39 |   39 |   39 |   39 |   39 |
| 42 |   42 |   42 |   42 |   42 |   42 |
+----+------+------+------+------+------+
5 rows in set (0.21 sec)

singlestore> select * from v3 limit 5;
+----+------+------+------+------+-------+
| id | val6 | val7 | val8 | val9 | val10 |
+----+------+------+------+------+-------+
| 21 |   21 |   21 |   21 |   21 |    21 |
| 25 |   25 |   25 |   25 |   25 |    25 |
| 31 |   31 |   31 |   31 |   31 |    31 |
| 39 |   39 |   39 |   39 |   39 |    39 |
| 42 |   42 |   42 |   42 |   42 |    42 |
+----+------+------+------+------+-------+
5 rows in set (0.21 sec)

singlestore> show create view v3\G
*************************** 1. row ***************************
                View: v3
         Create View: CREATE DEFINER=`root`@`%` SCHEMA_BINDING=OFF VIEW `v3` AS SELECT `t3`.`id` AS `id`, `t3`.`var6` AS `val6`, `t3`.`var7` AS `val7`, `t3`.`var8` AS `val8`, `t3`.`var9` AS `val9`, `t3`.`var10` AS `val10` FROM `t3` as `t3`
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

굉장히 단순화된 예제입니다만 이를 활용해 현업에서 좀더 복잡한 요구사항을 처리할 수 있도록 길을 잡아주는 템플릿으로 유용할 것으로 생각되어 소개해 드렸습니다.

감사합니다.