dWELXFgH

1CREATE DATABASE IF NOT EXISTS trainingdemo;
2
3USE trainingdemo;
4
5CREATE TABLE orders (
6  order_id INT,
7  order_date STRING,
8  order_customer_id INT,
9  order_status STRING
10) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
11
12CREATE TABLE order_items (
13  order_item_id INT,
14  order_item_order_id INT,
15  order_item_product_id INT,
16  order_item_quantity INT,
17  order_item_subtotal FLOAT,
18  order_item_product_price FLOAT
19) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
20
21LOAD DATA LOCAL INPATH '/data/retail_db/orders' INTO TABLE orders;
22LOAD DATA LOCAL INPATH '/data/retail_db/order_items' INTO TABLE order_items;
23
24SELECT * FROM orders LIMIT 10;
25SELECT * FROM order_items LIMIT 10;
26
27CREATE TABLE daily_revenue (
28  order_date STRING,
29  revenue FLOAT
30) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
31
32INSERT INTO daily_revenue
33SELECT order_date, sum(order_item_subtotal) revenue 
34FROM orders JOIN order_items
35ON order_id = order_item_order_id
36GROUP BY order_date;