| -- create the tables and load the data |
| Create external table lineitem (L_ORDERKEY INT, L_PARTKEY INT, L_SUPPKEY INT, L_LINENUMBER INT, L_QUANTITY DOUBLE, L_EXTENDEDPRICE DOUBLE, L_DISCOUNT DOUBLE, L_TAX DOUBLE, L_RETURNFLAG STRING, L_LINESTATUS STRING, L_SHIPDATE STRING, L_COMMITDATE STRING, L_RECEIPTDATE STRING, L_SHIPINSTRUCT STRING, L_SHIPMODE STRING, L_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/100/lineitem'; |
| 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/100/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/100/customer'; |
| create external table nation (N_NATIONKEY INT, N_NAME STRING, N_REGIONKEY INT, N_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/100/nation'; |
| |
| -- create the result table |
| create table q10_returned_item (c_custkey int, c_name string, revenue double, c_acctbal string, n_name string, c_address string, c_phone string, c_comment string); |
| |
| set mapred.min.split.size=536870912; |
| set hive.exec.reducers.bytes.per.reducer=1024000000; |
| |
| -- the query |
| insert overwrite table q10_returned_item |
| select |
| c_custkey, c_name, sum(l_extendedprice * (1 - l_discount)) as revenue, |
| c_acctbal, n_name, c_address, c_phone, c_comment |
| from |
| customer c join orders o |
| on |
| c.c_custkey = o.o_custkey and o.o_orderdate >= '1993-10-01' and o.o_orderdate < '1994-01-01' |
| join nation n |
| on |
| c.c_nationkey = n.n_nationkey |
| join lineitem l |
| on |
| l.l_orderkey = o.o_orderkey and l.l_returnflag = 'R' |
| group by c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment |
| order by revenue desc |
| limit 20; |
| |
| DROP TABLE lineitem; |
| DROP TABLE orders; |
| DROP TABLE customer; |
| DROP TABLE nation; |
| DROP TABLE q10_returned_item; |