반응형

Oracle to Mongo 시나리오 테스트

2020 8 10일 월요일

09:49

Oracle Sample schema Download : https://www.oracletutorial.com/getting-started/oracle-sample-database/

테이블 생성 시 에러 발생 발생 경우 generate 제거하고 그냥 Primary Key만 살려 두고 만들면 됨.

 

ERD

 

Table Info

 

 

Order 부분을 묶어서 하나의 Collection으로 만드는 예

 

다음 쿼리 결과를 하나의 Collection으로 묶을 예정

 

SELECT  o.customer_id , o.order_id , oi.item_id , o.status  , o.order_date ,product_name , quantity , p.list_price , o.salesman_id

FROM    orders  o ,

        order_items oi ,

        products p

WHERE   o.order_id = oi.order_id

AND     oi.product_id = p.product_id

ORDER BY o.customer_id , o.order_id , item_id  ;

1        60        1        Shipped        2017/06/30 금요일        G.Skill Trident X                   74        649.99    62

1        60        2        Shipped        2017/06/30 금요일        G.Skill Ripjaws V Series           138        704.99    62

1        60        3        Shipped        2017/06/30 금요일        G.Skill Ripjaws 4 Series           144        1073.99   62

1        60        4        Shipped        2017/06/30 금요일        Intel Xeon E5-2643 V2 (OEM/Tray)   125        2200      62

1        60        5        Shipped        2017/06/30 금요일        G.Skill Ripjaws V Series           114        677.99    62

1        60        6        Shipped        2017/06/30 금요일        Intel Xeon E5-2695 V4               79        2269.99   62

1        60        7        Shipped        2017/06/30 금요일        Asus X99-E-10G WS                  146        649       62

 

1        77        1        Shipped        2017/01/02 월요일        Kingston                            65        671.38    60

1        77        2        Shipped        2017/01/02 월요일        Seagate ST31000340NS – FFP         136        43.99     60

1        77        3        Shipped        2017/01/02 월요일        Intel Xeon E5-2660 V4              143        1388.89   60

1        77        4        Shipped        2017/01/02 월요일        Intel Xeon E5-2697 V4               99        2554.99   60

1        77        5        Shipped        2017/01/02 월요일        MSI X99A GODLIKE GAMING CARBON     148        549.59    60

 

1        94        1        Shipped        2017/10/27 금요일        Seagate ST2000DX002                 38        90.99     62

1        94        2        Shipped        2017/10/27 금요일        PNY VCQK5200-PB                    146        1449.98   62

1        94        3        Shipped        2017/10/27 금요일        Intel Xeon E5-2660 V4               86        1388.89   62

1        94        4        Shipped        2017/10/27 금요일        Intel Xeon E5-1650 V3              133        564.89    62

1        94        5        Shipped        2017/10/27 금요일        ATI FirePro R5000                  143        999.99    62

1        94        6        Shipped        2017/10/27 금요일        AMD 100-505989                     111        2699.99   62

1        94        7        Shipped        2017/10/27 금요일        ASRock EP2C612 WS                   37        358.49    62

1        94        8        Shipped        2017/10/27 금요일        PNY SSD7CS1311-120-RB               73        57.98     62

1        94        9        Shipped        2017/10/27 금요일        Gigabyte GV-N108TAORUSX W-11GD      33        824.98    62

예상 추출 데이터 형식

하나의 주문에 대해서 상세 내역을 배열로 넣을 예정

 

{ customer_id : 1 , order_id : 60 , status : "Shipped" , order_date : "2017/06/30 금요일" , sales_id : 62 , 

  order_detail : [ { product_name : "G.Skill Trident X"                    , quntity : 74        , unit_price : 649.99  } ,

                   { product_name : "G.Skill Ripjaws V Series"             , quntity : 138       , unit_price : 704.99  } ,

                   { product_name : "G.Skill Ripjaws 4 Series"             , quntity : 144       , unit_price : 1073.99 } ,

                   { product_name : "Intel Xeon E5-2643 V2 (OEM/Tray)"     , quntity : 125       , unit_price : 2200        } ,

                   { product_name : "G.Skill Ripjaws V Series"             , quntity : 114       , unit_price : 677.99  } ,

                   { product_name : "Intel Xeon E5-2695 V4"                , quntity : 79        , unit_price : 2269.99 } ,

                   { product_name : "Asus X99-E-10G WS"                    , quntity : 146       , unit_price : 649     } ] } ,

{ customer_id : 1 , order_id : 77 , status : "Shipped" , order_date : "2017/01/02 월요일" , sales_id : 60 ,

  order_detail : [ { product_name : "Kingston"                       , quntity : 65         , unit_price : 671.38  } ,

                   { product_name : "Seagate ST31000340NS - FFP"     , quntity : 136        , unit_price : 43.99   } ,

                   { product_name : "Intel Xeon E5-2660 V4"          , quntity : 143        , unit_price : 1388.89 } ,

                   { product_name : "Intel Xeon E5-2697 V4"          , quntity : 99         , unit_price : 2554.99        } ,

                   { product_name : "MSI X99A GODLIKE GAMING CARBON" , quntity : 148        , unit_price : 549.59  } ] }

 

Oracle 11g

 

JSON 함수를 지원하지 않기 때문에 PL/SQL로 데이터 추출 (Insert 구문으로 추출 예정)

데이터가 없는 부분은 Null 처리 ( mongo에서는 null 소문자로 써야 인식)

 

/tmp/order.sql

set timing off

set feed off

set serveroutput on

 

DECLARE

v_total_rn number := 1 ;

v_sub_rn number := 1 ;

v_item_sum number := 0 ;

v_db varchar2(100) := 'use ot' ;

v_collection varchar2(4000) := 'db.order.insert( ' ;

BEGIN

DBMS_OUTPUT.PUT_LINE( v_db ) ;

FOR v_main IN ( SELECT count(*) over () total_rn ,  o.order_id , ' { customer_id : ' || to_char(o.customer_id) || ' , order_id : ' || to_char(o.order_id) || ' , status : "' || nvl(o.status,'null')  || '" , order_date : "' || nvl(to_char(o.order_date),'null') || '" , sales_id : ' || NVL(to_char(o.salesman_id), 'null') || ' , order_detail : ' as str FROM   orders  o ) LOOP

v_sub_rn := 1 ;

v_item_sum := 0 ;

DBMS_OUTPUT.PUT_LINE (v_collection ||v_main.str || ' [ ' ) ;

FOR v_sub IN ( SELECT max(oi.item_id) over (partition by oi.order_id) rn , oi.quantity * oi.unit_price as price ,  '    ' || ' { product_name : "' || p.product_name || '" , quantity : ' || to_char(oi.quantity) || ' , unit_price : ' || to_char(oi.unit_price) || ' } ' as s_str FROM   order_items oi , products p WHERE  oi.order_id  =  v_main.order_id AND oi.product_id = p.product_id ) LOOP

 

v_item_sum := v_item_sum + v_sub.price ;

 

IF v_sub_rn = v_sub.rn THEN

DBMS_OUTPUT.PUT_LINE( v_sub.s_str || ' ]' ) ;

ELSE

DBMS_OUTPUT.PUT_LINE( v_sub.s_str || ' , ' ) ;

END IF ;

 

v_sub_rn := v_sub_rn + 1 ;

END LOOP ;

 

v_total_rn := v_total_rn + 1  ;

 

DBMS_OUTPUT.PUT_LINE ( ' , order_sum_price : ' || to_char(v_item_sum) || ' }) ' ) ;

END LOOP ;

END ;

/

sqlplus -s ot/ot < /tmp/order.sql > /tmp/order.json

use ot

 

db.order.insert(  { customer_id : 1 , order_id : 105 , status : "Pending" , order_date : "17-NOV-16" , sales_id : 54 , order_detail :  [

{ product_name : "G.Skill Ripjaws V Series" , quantity : 80 , unit_price : 645.99 }  ,

{ product_name : "Supermicro MBD-X10DAX" , quantity : 116 , unit_price : 443.72 }  ,

{ product_name : "MSI X99A GODLIKE GAMING" , quantity : 45 , unit_price : 399.99 }  ,

{ product_name : "Intel Xeon E5-1680 V3 (OEM/Tray)" , quantity : 67 , unit_price : 1751.99 }  ,

{ product_name : "Asus GTX780TI-3GD5" , quantity : 79 , unit_price : 899.99 }  ,

{ product_name : "EVGA 11G-P4-6598-KR" , quantity : 77 , unit_price : 809.99 }  ]

, order_sum_price : 372002.04 })

 

db.order.insert(  { customer_id : 2 , order_id : 44 , status : "Pending" , order_date : "20-FEB-17" , sales_id : 55 , order_detail :  [

{ product_name : "MSI X99A GODLIKE GAMING CARBON" , quantity : 37 , unit_price : 549.59 }  ,

{ product_name : "Intel Xeon E5-2667 V3 (OEM/Tray)" , quantity : 100 , unit_price : 2009.46 }  ,

{ product_name : "PNY VCQP4000-PB" , quantity : 148 , unit_price : 829.89 }  ,

{ product_name : "MSI GeForce GTX 1080 Ti GAMING X 11G" , quantity : 118 , unit_price : 759.99 }  ]

, order_sum_price : 433783.37 })

 

db.order.insert(  { customer_id : 3 , order_id : 101 , status : "Pending" , order_date : "03-JAN-17" , sales_id : 55 , order_detail :  [

{ product_name : "Corsair Vengeance LPX" , quantity : 47 , unit_price : 699.01 }  ,

{ product_name : "MSI X299 TOMAHAWK ARCTIC" , quantity : 36 , unit_price : 281.98 }  ,

{ product_name : "Samsung MZ-V6E250" , quantity : 134 , unit_price : 127.88 }  ,

{ product_name : "Kingston SA400S37/120G" , quantity : 52 , unit_price : 54.99 }  ,

{ product_name : "Intel Xeon E5-2687W V4" , quantity : 123 , unit_price : 2042.69 }  ,

{ product_name : "Corsair Dominator Platinum" , quantity : 138 , unit_price : 719.99 }  ,

{ product_name : "G.Skill Trident Z" , quantity : 134 , unit_price : 704.99 }  ]

, order_sum_price : 508078.3 })

 

db.order.insert(  { customer_id : 6 , order_id : 6 , status : "Shipped" , order_date : "09-APR-15" , saleman_id : null , order_detail :  [

{ product_name : "G.Skill Ripjaws V Series" , quantity : 119 , unit_price : 680.99 }  ,

{ product_name : "G.Skill Ripjaws V Series" , quantity : 41 , unit_price : 645.99 }  ,

{ product_name : "PNY SSD7CS1311-120-RB" , quantity : 145 , unit_price : 57.98 }  ,

{ product_name : "Western Digital WD2500AVVS" , quantity : 90 , unit_price : 15.55 }  ,

{ product_name : "Intel Xeon E5-2630 V4" , quantity : 67 , unit_price : 647.99 }  ,

{ product_name : "PNY VCQP5000-PB" , quantity : 74 , unit_price : 2015.11 }  ,

{ product_name : "PNY VCGGTX780T3XPB-OC" , quantity : 150 , unit_price : 749.99 }  ]

, order_sum_price : 422361.97 })

 

mongo --host mongo:9999 -u root -p root1122 --authenticationDatabase admin < /tmp/order.json

 

Oracle12c

제공해 주는 JSON 함수를 이용하여 추출 (12c R2부터 지원)

 

/tmp/order12.sql

set pagesize 0

set heading off

set timing off

set feed off

set line 32767

 

SELECT 'use ot' FROM dual

UNION ALL

SELECT  'db.order12.insert(' || JSON_OBJECT(

            'customer_id' value o.customer_id

            , 'order_id' value o.order_id

            , 'status' value o.status 

            , 'order_date' value o.order_date

            , 'sales_id' value o.salesman_id

            , 'order_detail' value ( SELECT  JSON_ARRAYAGG( JSON_OBJECT(

                                                          'product_name' value p.product_name

                                                         , 'quantity' value oi.quantity

                                                         , 'unit_price' value oi.unit_price ))

                                                   FROM   order_items oi , products p

                                                   WHERE  oi.order_id  =  o.order_id AND oi.product_id = p.product_id

                                   )
            , 'order_sum_price' value ( select sum(quantity*unit_price) from order_items oi where oi.order_id  =  o.order_id)

             ) || ' ) '

FROM    orders  o ;

sqlplus -s ot/ot < /tmp/order12.sql > /tmp/order12.json

use ot

 

db.order12.insert({"customer_id":1,"order_id":105,"status":"Pending","order_date":"2016-11-17T00:00:00","sales_id":54,"order_detail":[{"product_name":"Asus GTX780TI-3GD5","quantity":79,"unit_price":899.99},{"product_name":"EVGA 11G-P4-6598-KR","quantity":77,"unit_price":809.99},{"product_name":"G.Skill Ripjaws V Series","quantity":80,"unit_price":645.99},{"product_name":"Supermicro MBD-X10DAX","quantity":116,"unit_price":443.72},{"product_name":"MSI X99A GODLIKE GAMING","quantity":45,"unit_price":399.99},{"product_name":"Intel Xeon E5-1680 V3 (OEM/Tray)","quantity":67,"unit_price":1751.99}],"order_sum_price":372002.04} )

 

db.order12.insert({"customer_id":2,"order_id":44,"status":"Pending","order_date":"2017-02-20T00:00:00","sales_id":55,"order_detail":[{"product_name":"PNY VCQP4000-PB","quantity":148,"unit_price":829.89},{"product_name":"MSI GeForce GTX 1080 Ti GAMING X 11G","quantity":118,"unit_price":759.99},{"product_name":"MSI X99A GODLIKE GAMING CARBON","quantity":37,"unit_price":549.59},{"product_name":"Intel Xeon E5-2667 V3 (OEM/Tray)","quantity":100,"unit_price":2009.46}],"order_sum_price":433783.37} )

 

db.order12.insert({"customer_id":3,"order_id":101,"status":"Pending","order_date":"2017-01-03T00:00:00","sales_id":55,"order_detail":[{"product_name":"Corsair Dominator Platinum","quantity":138,"unit_price":719.99},{"product_name":"G.Skill Trident Z","quantity":134,"unit_price":704.99},{"product_name":"Corsair Vengeance LPX","quantity":47,"unit_price":699.01},{"product_name":"MSI X299 TOMAHAWK ARCTIC","quantity":36,"unit_price":281.98},{"product_name":"Samsung MZ-V6E250","quantity":134,"unit_price":127.88},{"product_name":"Kingston SA400S37/120G","quantity":52,"unit_price":54.99},{"product_name":"Intel Xeon E5-2687W V4","quantity":123,"unit_price":2042.69}],"order_sum_price":508078.3} )

 

db.order12.insert({"customer_id":6,"order_id":6,"status":"Shipped","order_date":"2015-04-09T00:00:00","sales_id":null,"order_detail":[{"product_name":"PNY VCGGTX780T3XPB-OC","quantity":150,"unit_price":749.99},{"product_name":"G.Skill Ripjaws V Series","quantity":119,"unit_price":680.99},{"product_name":"G.Skill Ripjaws V Series","quantity":41,"unit_price":645.99},{"product_name":"PNY SSD7CS1311-120-RB","quantity":145,"unit_price":57.98},{"product_name":"Western Digital WD2500AVVS","quantity":90,"unit_price":15.55},{"product_name":"Intel Xeon E5-2630 V4","quantity":67,"unit_price":647.99},{"product_name":"PNY VCQP5000-PB","quantity":74,"unit_price":2015.11}],"order_sum_price":422361.97} )

mongo --host mongo:9999 -u root -p root1122 --authenticationDatabase admin < /tmp/order12.json

반응형

+ Recent posts