blob: 40ae42395a76a3f851f36c0a4db5710e750fa562 [file] [log] [blame]
DROP TABLE partsupp;
DROP TABLE lineitem;
DROP TABLE supplier;
DROP TABLE nation;
DROP TABLE q20_tmp1;
DROP TABLE q20_tmp2;
DROP TABLE q20_tmp3;
DROP TABLE q20_tmp4;
DROP TABLE q20_potential_part_promotion;
-- create tables and load 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/10/lineitem';
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/10/supplier';
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/10/nation';
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/10/partsupp';
-- create the target table
create table q20_tmp1(p_partkey int);
create table q20_tmp2(l_partkey int, l_suppkey int, sum_quantity double);
create table q20_tmp3(ps_suppkey int, ps_availqty int, sum_quantity double);
create table q20_tmp4(ps_suppkey int);
create table q20_potential_part_promotion(s_name string, s_address string);
set mapred.min.split.size=536870912;
-- the query
insert overwrite table q20_tmp1
select distinct p_partkey
from
part
where
p_name like 'forest%';
insert overwrite table q20_tmp2
select
l_partkey, l_suppkey, 0.5 * sum(l_quantity)
from
lineitem
where
l_shipdate >= '1994-01-01'
and l_shipdate < '1995-01-01'
group by l_partkey, l_suppkey;
insert overwrite table q20_tmp3
select
ps_suppkey, ps_availqty, sum_quantity
from
partsupp ps join q20_tmp1 t1
on
ps.ps_partkey = t1.p_partkey
join q20_tmp2 t2
on
ps.ps_partkey = t2.l_partkey and ps.ps_suppkey = t2.l_suppkey;
insert overwrite table q20_tmp4
select
ps_suppkey
from
q20_tmp3
where
ps_availqty > sum_quantity
group by ps_suppkey;
insert overwrite table q20_potential_part_promotion
select
s_name, s_address
from
supplier s join nation n
on
s.s_nationkey = n.n_nationkey
and n.n_name = 'CANADA'
join q20_tmp4 t4
on
s.s_suppkey = t4.ps_suppkey
order by s_name;