반응형

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

테이블 생성 VARCHAR2 --> VARCHAR, NUMBER --> DOUBLE, to_date --> STR_TO_DATE('07-JUN-16','%d-%b-%y')

 

ERD



 

Table Info

 

 

MySQL 5.7 이상에서 데이터를 추출

JSON 함수를 이용하여 데이터 추출

JSON 함수에 지정해준 순서대로 추출되지는 않음 --;

오라클(value) 다르게 콤마(,) 구분

칼럼헤더가 output 파일에 들어가지 않게 -sN 옵션 사용

 

/tmp/myorder.sql
SELECT 'use ot' ;
SELECT CONCAT( 'db.myorder.insert(' , JSON_OBJECT(
            'customer_id' ,o.customer_id
            , 'order_id' , o.order_id
            , 'status' , o.status 
            , 'order_date' , o.order_date
            , 'sales_id' , o.salesman_id
            , 'order_detail' , ( SELECT  JSON_ARRAYAGG( JSON_OBJECT(
                                                          'product_name' , p.product_name
                                                         , 'quantity' , oi.quantity
                                                         , 'unit_price' , 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' , ( select sum(quantity*unit_price) from order_items oi where oi.order_id  =  o.order_id)
             ) , ' )' )                         
FROM    orders  o  ;
/engine/mysql-5.7/bin/mysql -h 127.0.0.1 -P 3357 -u root -p'root1122' -D ot -sN < /tmp/myorder.sql > /tmp/myorder.json
use ot


db.myorder.insert({"status": "Pending", "order_id": 1, "sales_id": 56, "order_date": "2017-10-15", "customer_id": 4, "order_detail": [{"quantity": 116, "unit_price": 469.99, "product_name": "Asus Z10PE-D16 WS"}, {"quantity": 77, "unit_price": 686.99, "product_name": "G.Skill Ripjaws V Series"}, {"quantity": 52, "unit_price": 525.99, "product_name": "Supermicro H8DG6-F"}, {"quantity": 131, "unit_price": 645.99, "product_name": "G.Skill Ripjaws V Series"}, {"quantity": 95, "unit_price": 640.99, "product_name": "G.Skill Ripjaws V Series"}, {"quantity": 41, "unit_price": 645.2, "product_name": "G.Skill Ripjaws V Series"}, {"quantity": 129, "unit_price": 383.98, "product_name": "Asus X99-DELUXE II"}, {"quantity": 139, "unit_price": 1099.99, "product_name": "Corsair Vengeance LPX"}, {"quantity": 147, "unit_price": 525.99, "product_name": "Supermicro H8DG6-F"}, {"quantity": 105, "unit_price": 1844.89, "product_name": "Intel Xeon E5-2683 V4 (OEM/Tray)"}, {"quantity": 103, "unit_price": 309.85, "product_name": "Asus PRIME X299-A"}, {"quantity": 121, "unit_price": 1999.89, "product_name": "HP C2J95AT"}], "order_sum_price": 1054107.74} )


db.myorder.insert({"status": "Shipped", "order_id": 2, "sales_id": null, "order_date": "2015-04-26", "customer_id": 4, "order_detail": [{"quantity": 111, "unit_price": 298.98, "product_name": "Asus MAXIMUS IX CODE"}, {"quantity": 121, "unit_price": 1431.99, "product_name": "G.Skill Trident Z"}, {"quantity": 39, "unit_price": 1055.99, "product_name": "G.Skill Ripjaws 4 Series"}, {"quantity": 49, "unit_price": 799, "product_name": "Crucial"}, {"quantity": 30, "unit_price": 1908.73, "product_name": "Intel Xeon E5-2690 V3"}, {"quantity": 95, "unit_price": 949.89, "product_name": "PNY VCQK4200-PB"}, {"quantity": 100, "unit_price": 686.99, "product_name": "G.Skill Ripjaws V Series"}, {"quantity": 97, "unit_price": 419.99, "product_name": "Supermicro X11SSL-CF"}, {"quantity": 75, "unit_price": 620.95, "product_name": "Intel Core i7-4790K"}], "order_sum_price": 590302.91} )


db.myorder.insert({"status": "Shipped", "order_id": 3, "sales_id": null, "order_date": "2017-04-26", "customer_id": 5, "order_detail": [{"quantity": 111, "unit_price": 66.89, "product_name": "Seagate ST2000DM006"}, {"quantity": 65, "unit_price": 16.99, "product_name": "Western Digital WD2500AAJS"}, {"quantity": 63, "unit_price": 479.99, "product_name": "Supermicro X9SRH-7TF"}, {"quantity": 46, "unit_price": 525.99, "product_name": "Supermicro H8DG6-F"}, {"quantity": 112, "unit_price": 644, "product_name": "Kingston"}, {"quantity": 81, "unit_price": 1299.99, "product_name": "Corsair Vengeance LPX"}, {"quantity": 73, "unit_price": 768.99, "product_name": "G.Skill Trident Z"}, {"quantity": 127, "unit_price": 948.99, "product_name": "Supermicro X10SDV-8C-TLN4F"}], "order_sum_price": 417049.24} )
mongo --host mongo:9999 -u root -p root1122 --authenticationDatabase admin < /tmp/order.json

 

반응형

+ Recent posts