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 ) ) || ' ) ' 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 |