move hivesterix codebase into hyracks fullstack
git-svn-id: https://hyracks.googlecode.com/svn/branches/fullstack_staging@2420 123451ca-8445-de46-9d55-352943316053
diff --git a/hivesterix/resource/tpch/q20_potential_part_promotion.hive b/hivesterix/resource/tpch/q20_potential_part_promotion.hive
new file mode 100644
index 0000000..40ae423
--- /dev/null
+++ b/hivesterix/resource/tpch/q20_potential_part_promotion.hive
@@ -0,0 +1,77 @@
+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;
+
+