[NOT USE]DBeaver로 MySQL to PG

StartUP SQL에서 구성한 MySQL용 startdb 데이터베이스를 pg로 카피합니다.

아래의 내용이 먼저 진행되어 있어야 합니다.

•

2. 실습 환경 구성하기 

•

DOCKER PG16 설치와 PG_HINT_PLAN 

•

MySQL의 Information_schema를 읽어서 PG용 DDL을 생성합니다.

-- MySQL startdb에 접속해 실행합니다.
-- CREATE TABLE 만들기(From MySQL, To PostgreSQL)
-- StartUP SQL MySQL 용 데이터베이스에 접속해 아래 SQL을 실행합니다.
WITH W1 AS(
SELECT  T1.TABLE_NAME
        ,T2.COLUMN_NAME
        ,T2.IS_NULLABLE
        ,CASE WHEN T2.DATA_TYPE = 'datetime' THEN 'timestamp'
              WHEN T2.DATA_TYPE in ('int','bigint') THEN 'decimal'
              ELSE T2.DATA_TYPE END DATA_TYPE
        ,T2.CHARACTER_MAXIMUM_LENGTH
        ,T2.NUMERIC_PRECISION
        ,T2.NUMERIC_SCALE
        ,CASE WHEN T2.DATA_TYPE in ('date','datetime') THEN ''
              WHEN T2.DATA_TYPE = 'varchar' THEN CONCAT('(',T2.CHARACTER_MAXIMUM_LENGTH,')') 
              WHEN T2.DATA_TYPE in ('int','bigint','numeric','decimal') THEN CONCAT('(',T2.NUMERIC_PRECISION,',',T2.NUMERIC_SCALE,')')   
              END DT_LEN
        ,T2.ORDINAL_POSITION POS
        ,MAX(T2.ORDINAL_POSITION) OVER(PARTITION BY T1.TABLE_NAME) LAST_POS
FROM    INFORMATION_SCHEMA.TABLES T1
        INNER JOIN INFORMATION_SCHEMA.COLUMNS T2
                ON (T1.TABLE_CATALOG = T2.TABLE_CATALOG
                    AND T1.TABLE_SCHEMA = T2.TABLE_SCHEMA
                    AND T1.TABLE_NAME = T2.TABLE_NAME)
WHERE   T1.TABLE_SCHEMA = 'startdb'
AND     T1.TABLE_NAME NOT IN ('test_a','vw_itemfull','student','courseregistration')
)    
,W2 AS(
SELECT  CASE WHEN T1.POS = 1 THEN CONCAT('CREATE TABLE ',T1.TABLE_NAME,' (') ELSE '          ,' END CT
        ,CONCAT(LOWER(T1.COLUMN_NAME),'    ',T1.DATA_TYPE,'   ',T1.DT_LEN,'   ',CASE WHEN T1.IS_NULLABLE = 'YES' THEN ' NULL ' ELSE ' NOT NULL ' END) COL
        ,CASE WHEN T1.POS = T1.LAST_POS THEN ');' ELSE '' END FIN
        ,T1.TABLE_NAME ,T1.POS
FROM    W1 T1
ORDER BY T1.TABLE_NAME ,T1.POS
)
SELECT  CONCAT(T2.CT, T2.COL, T2.FIN) DDL_SQL
FROM    W2 T2
ORDER BY T2.TABLE_NAME ,T2.POS;
SQL
복사

•

위 결과로 얻은 SQL(아래 참고)을 PG의 startdb에 접속해 실행합니다.

-- pg startdb에 접속해 실행합니다.

CREATE TABLE basecd (basecddv    varchar   (40)    NOT NULL 
          ,basecd    varchar   (40)    NOT NULL 
          ,basecdnm    varchar   (100)    NULL 
          ,sortorder    decimal   (10,0)    NULL );
          
CREATE TABLE basecddv (basecddv    varchar   (40)    NOT NULL 
          ,basecddvnm    varchar   (100)    NULL );
          
CREATE TABLE basedt (basedt    date       NOT NULL 
          ,baseymd    varchar   (8)    NOT NULL 
          ,basedtseq    decimal   (10,0)    NOT NULL 
          ,basewkd    varchar   (10)    NOT NULL );
          
CREATE TABLE event (eventid    varchar   (40)    NOT NULL 
          ,eventnm    varchar   (100)    NOT NULL 
          ,eventstartdtm    timestamp       NOT NULL 
          ,eventenddtm    timestamp       NOT NULL );
          
CREATE TABLE evententry (eventid    varchar   (40)    NOT NULL 
          ,memberid    varchar   (40)    NOT NULL 
          ,entrydtm    timestamp       NOT NULL 
          ,entryresultcd    varchar   (40)    NOT NULL );
          
CREATE TABLE item (itemid    varchar   (40)    NOT NULL 
          ,itemnm    varchar   (100)    NULL 
          ,itemcat    varchar   (40)    NULL 
          ,itemsizecd    varchar   (40)    NULL 
          ,hotcoldcd    varchar   (40)    NULL 
          ,launchdt    date       NULL );
          
CREATE TABLE itemcat (itemcat    varchar   (40)    NOT NULL 
          ,itemcatnm    varchar   (100)    NULL );
          
CREATE TABLE itemprchist (itemid    varchar   (40)    NOT NULL 
          ,fromdt    date       NOT NULL 
          ,todt    date       NOT NULL 
          ,saleprc    decimal   (18,3)    NULL );
          
CREATE TABLE member (memberid    varchar   (40)    NOT NULL 
          ,nicknm    varchar   (100)    NULL 
          ,mobileno    varchar   (100)    NULL 
          ,email    varchar   (100)    NULL 
          ,joindtm    timestamp       NULL 
          ,membergd    varchar   (40)    NULL 
          ,memberst    varchar   (40)    NULL 
          ,leavedtm    timestamp       NULL );
          
CREATE TABLE ord (ordno    decimal   (20,0)    NOT NULL 
          ,orddtm    timestamp       NULL 
          ,preparecmpdtm    timestamp       NULL 
          ,pickupdtm    timestamp       NULL 
          ,memberid    varchar   (40)    NULL 
          ,shopid    varchar   (40)    NULL 
          ,ordst    varchar   (40)    NULL 
          ,ordamt    decimal   (18,3)    NULL 
          ,paytp    varchar   (40)    NULL );
          
CREATE TABLE orddet (ordno    decimal   (20,0)    NOT NULL 
          ,orddetno    decimal   (10,0)    NOT NULL 
          ,itemid    varchar   (40)    NULL 
          ,ordqty    decimal   (10,0)    NULL 
          ,saleprc    decimal   (18,3)    NULL );
          
CREATE TABLE shop (shopid    varchar   (40)    NOT NULL 
          ,shopnm    varchar   (100)    NULL 
          ,shopsize    decimal   (10,0)    NULL 
          ,shopopertp    varchar   (40)    NULL 
          ,tableqty    decimal   (10,0)    NULL 
          ,chairqty    decimal   (10,0)    NULL 
          ,opentime    varchar   (4)    NULL 
          ,closetime    varchar   (4)    NULL 
          ,shopst    varchar   (40)    NULL 
          ,shopstartymd    varchar   (8)    NULL 
          ,shopendymd    varchar   (8)    NULL );
SQL
복사

•

MySQL의 데이터를 Pg로 밀어 넣기

◦

DBeaver를 이용, 왼쪽의 MySQL 네비게이션을 열어서, startdb아래에 이관할 테이블들을 선택합니다. (아래 그림 참고)

◦

테이블을 선택후 마우스 오른쪽을 누르고 데이터내보내기 선택

◦

아래 화면(Export target)에서 데이터베이스:테이블을 선택후 다음을 클릭

◦

오른쪽 위에, Choose Target Catalog 의 폴더모양 버튼 선택

▪

만들어놓은 postgresql-startdb 컨넥션 밑에 startdb 아래 schemas 아래 public을 선택후 확인

▪

아래와 같이 existing이 나와야 합니다.

◦

그 다음부터는 ‘다음’  버튼으로 계속 넘어가고 마지막에 ‘진행’만 눌러주면 mysql로 구성한 startdb의 데이터를 pg에도 밀어 넣습니다.

•

MySQL의 PK 제약을 가져와서 pg에도 만들어줍니다.

-- MySQL에서 실행합니다.

SELECT  *
FROM    INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'information_schema';

WITH W1 AS(
SELECT  LOWER(T1.TABLE_NAME) TABLE_NAME
        ,GROUP_CONCAT(LOWER(T1.COLUMN_NAME) ORDER BY T1.ORDINAL_POSITION) COLS
FROM    information_schema.KEY_COLUMN_USAGE T1
WHERE   T1.TABLE_SCHEMA = 'startdb'
AND     T1.CONSTRAINT_NAME = 'PRIMARY'
AND     T1.TABLE_NAME NOT IN ('student','courseregistration')
GROUP BY LOWER(T1.TABLE_NAME)
)
SELECT  CONCAT('ALTER TABLE ',T1.TABLE_NAME, ' ADD CONSTRAINT ',T1.TABLE_NAME,'_pk PRIMARY KEY(',T1.COLS ,');') C_PK
FROM    W1 T1;
SQL
복사

-- PG에서 실행합니다.(PK생성)
ALTER TABLE basecd ADD CONSTRAINT basecd_pk PRIMARY KEY(basecddv,basecd);
ALTER TABLE basecddv ADD CONSTRAINT basecddv_pk PRIMARY KEY(basecddv);
ALTER TABLE basedt ADD CONSTRAINT basedt_pk PRIMARY KEY(basedt);
ALTER TABLE event ADD CONSTRAINT event_pk PRIMARY KEY(eventid);
ALTER TABLE evententry ADD CONSTRAINT evententry_pk PRIMARY KEY(eventid,memberid);
ALTER TABLE item ADD CONSTRAINT item_pk PRIMARY KEY(itemid);
ALTER TABLE itemcat ADD CONSTRAINT itemcat_pk PRIMARY KEY(itemcat);
ALTER TABLE itemprchist ADD CONSTRAINT itemprchist_pk PRIMARY KEY(itemid,fromdt);
ALTER TABLE member ADD CONSTRAINT member_pk PRIMARY KEY(memberid);
ALTER TABLE ord ADD CONSTRAINT ord_pk PRIMARY KEY(ordno);
ALTER TABLE orddet ADD CONSTRAINT orddet_pk PRIMARY KEY(ordno,orddetno);
ALTER TABLE shop ADD CONSTRAINT shop_pk PRIMARY KEY(shopid);
SQL
복사

•

MySQL의 FK 제약을 가져와서 pg에도 만들어줍니다.

-- MySQL에서 실행합니다.(FK 생성 SQL 만들기)
WITH W1 AS(
SELECT  LOWER(T1.CONSTRAINT_NAME) CONS_NAME
        ,LOWER(T1.TABLE_NAME) TABLE_NAME
        ,LOWER(T1.REFERENCED_TABLE_NAME) REF_TAB
        ,GROUP_CONCAT(LOWER(T1.COLUMN_NAME) ORDER BY T1.ORDINAL_POSITION) COLS
        ,GROUP_CONCAT(LOWER(T1.REFERENCED_COLUMN_NAME) ORDER BY T1.ORDINAL_POSITION) REF_COLS
FROM    information_schema.KEY_COLUMN_USAGE T1
WHERE   T1.TABLE_SCHEMA = 'startdb'
AND     T1.CONSTRAINT_NAME != 'PRIMARY'
AND     T1.TABLE_NAME NOT IN ('student','courseregistration')
GROUP BY LOWER(T1.TABLE_NAME)
        ,LOWER(T1.REFERENCED_TABLE_NAME)
        ,LOWER(T1.CONSTRAINT_NAME)
)
SELECT  CONCAT('ALTER TABLE ',T1.TABLE_NAME, ' ADD CONSTRAINT ',T1.CONS_NAME,' FOREIGN KEY(',T1.COLS ,') REFERENCES ',T1.REF_TAB,'(',T1.REF_COLS,');') C_FK
FROM    W1 T1;
SQL
복사

-- PG에서 실행합니다. FK 생성
ALTER TABLE basecd ADD CONSTRAINT basecd_fk01 FOREIGN KEY(basecddv) REFERENCES basecddv(basecddv);
ALTER TABLE evententry ADD CONSTRAINT evententry_fk01 FOREIGN KEY(eventid) REFERENCES event(eventid);
ALTER TABLE evententry ADD CONSTRAINT evententry_fk02 FOREIGN KEY(memberid) REFERENCES member(memberid);
ALTER TABLE item ADD CONSTRAINT item_fk01 FOREIGN KEY(itemcat) REFERENCES itemcat(itemcat);
ALTER TABLE itemprchist ADD CONSTRAINT itemprchist_fk01 FOREIGN KEY(itemid) REFERENCES item(itemid);
ALTER TABLE ord ADD CONSTRAINT ord_fk01 FOREIGN KEY(memberid) REFERENCES member(memberid);
ALTER TABLE ord ADD CONSTRAINT ord_fk02 FOREIGN KEY(shopid) REFERENCES shop(shopid);
ALTER TABLE orddet ADD CONSTRAINT orddetail_fk02 FOREIGN KEY(itemid) REFERENCES item(itemid);
ALTER TABLE orddet ADD CONSTRAINT orddetail_fk01 FOREIGN KEY(ordno) REFERENCES ord(ordno);
SQL
복사

•

이제 PG에 MySQL용 StartDB와 똑같은 데이터 셋이 만들어졌습니다. 연습으로 SQL 한번 실행해보죠. 잘 되네요.

select  t1.memberid ,t3.nicknm ,t1.sumordamt
from    (
        select  b.memberid
                ,sum(b.ordamt) sumordamt
        from    shop a
                inner join ord b
                    on (a.shopid = b.shopid)
        where   a.shopstartymd = '20180327'
        and     b.orddtm >= TO_DATE('20200101','YYYYMMDD')
        and     b.orddtm <  TO_DATE('20200201','YYYYMMDD')
        group by b.memberid
        ) t1
        inner join member t3
            on (t3.memberid = t1.memberid)
where   t3.membergd = 'PLAT'
order by t1.memberid;

memberid|nicknm   |sumordamt|
--------+---------+---------+
M0291   |Star5    |28500.000|
M0298   |Thunder5 |10500.000|
M1283   |Quantum25|12000.000|
M1297   |Swift25  |15500.000|
SQL
복사