blob: 647d5006b1fb4845923316d80ef5269415e967fa [file] [log] [blame]
DROP TABLE part;
DROP TABLE supplier;
DROP TABLE partsupp;
DROP TABLE nation;
DROP TABLE region;
DROP TABLE q2_minimum_cost_supplier;
DROP TABLE q2_minimum_cost_supplier_tmp1;
DROP TABLE q2_minimum_cost_supplier_tmp2;
-- 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/10/part';
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 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 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 region (R_REGIONKEY INT, R_NAME STRING, R_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/region';
-- create result tables
create table q2_minimum_cost_supplier_tmp1 (s_acctbal double, s_name string, n_name string, p_partkey int, ps_supplycost double, p_mfgr string, s_address string, s_phone string, s_comment string);
create table q2_minimum_cost_supplier_tmp2 (p_partkey int, ps_min_supplycost double);
create table q2_minimum_cost_supplier (s_acctbal double, s_name string, n_name string, p_partkey int, p_mfgr string, s_address string, s_phone string, s_comment string);
-- the query
insert overwrite table q2_minimum_cost_supplier_tmp1
select
s.s_acctbal, s.s_name, n.n_name, p.p_partkey, ps.ps_supplycost, p.p_mfgr, s.s_address, s.s_phone, s.s_comment
from
nation n join region r
on
n.n_regionkey = r.r_regionkey and r.r_name = 'EUROPE'
join supplier s
on
s.s_nationkey = n.n_nationkey
join partsupp ps
on
s.s_suppkey = ps.ps_suppkey
join part p
on
p.p_partkey = ps.ps_partkey and p.p_size = 15 ;
-- explain insert overwrite table q2_minimum_cost_supplier_tmp2
-- select
-- p_partkey, min(ps_supplycost)
-- from
-- q2_minimum_cost_supplier_tmp1
-- group by p_partkey