728x90
반응형
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 |
반응형