| drop table IF EXISTS lineitem; |
| |
| 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/lineitem'; |
| |
| select * from (select sum(abs(L_QUANTITY)), SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)*(1+L_TAX)) |
| FROM lineitem WHERE L_SHIPDATE<='1998-09-02' GROUP BY L_RETURNFLAG) T; |
| |
| drop table lineitem; |
