| DROP TABLE IF EXISTS orders; |
| DROP TABLE IF EXISTS customer; |
| DROP TABLE IF EXISTS u10_join; |
| |
| -- create the tables and load the data |
| create external table orders (O_ORDERKEY INT, O_CUSTKEY INT, O_ORDERSTATUS STRING, O_TOTALPRICE DOUBLE, O_ORDERDATE STRING, O_ORDERPRIORITY STRING, O_CLERK STRING, O_SHIPPRIORITY INT, O_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/orders'; |
| create external table customer (C_CUSTKEY INT, C_NAME STRING, C_ADDRESS STRING, C_NATIONKEY INT, C_PHONE STRING, C_ACCTBAL DOUBLE, C_MKTSEGMENT STRING, C_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/customer'; |
| |
| create table u10_join(O_ORDERSTATUS STRING); |
| |
| insert overwrite table u10_join |
| select O_TOTALPRICE |
| from orders join customer |
| on orders.O_CUSTKEY=customer.C_CUSTKEY |
| order by O_TOTALPRICE; |