| -- create the tables and load the data |
| create external table part (P_PARTKEY INT, P_NAME STRING, P_MFGR STRING, P_BRAND STRING, P_TYPE STRING, P_SIZE INT, P_CONTAINER STRING, P_RETAILPRICE DOUBLE, P_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/100/part'; |
| 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 supplier (S_SUPPKEY INT, S_NAME STRING, S_ADDRESS STRING, S_NATIONKEY INT, S_PHONE STRING, S_ACCTBAL DOUBLE, S_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/100/supplier'; |
| create external table partsupp (PS_PARTKEY INT, PS_SUPPKEY INT, PS_AVAILQTY INT, PS_SUPPLYCOST DOUBLE, PS_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION'/tpch/100/partsupp'; |
| 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 q9_product_type_profit (nation string, o_year string, sum_profit double); |
| |
| -- the query |
| insert overwrite table q9_product_type_profit |
| select |
| nation, o_year, sum(amount) as sum_profit |
| from |
| ( |
| select |
| n_name as nation, year(o_orderdate) as o_year, |
| l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount |
| from |
| orders o join |
| (select l_extendedprice, l_discount, l_quantity, l_orderkey, n_name, ps_supplycost |
| from part p join |
| (select l_extendedprice, l_discount, l_quantity, l_partkey, l_orderkey, |
| n_name, ps_supplycost |
| from partsupp ps join |
| (select l_suppkey, l_extendedprice, l_discount, l_quantity, l_partkey, |
| l_orderkey, n_name |
| from |
| (select s_suppkey, n_name |
| from nation n join supplier s on n.n_nationkey = s.s_nationkey |
| ) s1 join lineitem l on s1.s_suppkey = l.l_suppkey |
| ) l1 on ps.ps_suppkey = l1.l_suppkey and ps.ps_partkey = l1.l_partkey |
| ) l2 on p.p_name like '%green%' and p.p_partkey = l2.l_partkey |
| ) l3 on o.o_orderkey = l3.l_orderkey |
| )profit |
| group by nation, o_year |
| order by nation, o_year desc; |
| |
| DROP TABLE part; |
| DROP TABLE lineitem; |
| DROP TABLE supplier; |
| DROP TABLE orders; |
| DROP TABLE partsupp; |
| DROP TABLE nation; |
| DROP TABLE q9_product_type_profit; |