Return-Path: X-Original-To: apmail-asterixdb-commits-archive@minotaur.apache.org Delivered-To: apmail-asterixdb-commits-archive@minotaur.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 2545B17BDC for ; Fri, 24 Apr 2015 18:46:46 +0000 (UTC) Received: (qmail 25608 invoked by uid 500); 24 Apr 2015 18:46:46 -0000 Delivered-To: apmail-asterixdb-commits-archive@asterixdb.apache.org Received: (qmail 25576 invoked by uid 500); 24 Apr 2015 18:46:46 -0000 Mailing-List: contact commits-help@asterixdb.incubator.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@asterixdb.incubator.apache.org Delivered-To: mailing list commits@asterixdb.incubator.apache.org Received: (qmail 25563 invoked by uid 99); 24 Apr 2015 18:46:46 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 24 Apr 2015 18:46:45 +0000 X-ASF-Spam-Status: No, hits=-0.0 required=5.0 tests=SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: domain of root@apache.org designates 54.191.145.13 as permitted sender) Received: from [54.191.145.13] (HELO mx1-us-west.apache.org) (54.191.145.13) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 24 Apr 2015 18:46:39 +0000 Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by mx1-us-west.apache.org (ASF Mail Server at mx1-us-west.apache.org) with SMTP id EE97625FD7 for ; Fri, 24 Apr 2015 18:45:29 +0000 (UTC) Received: (qmail 19049 invoked by uid 99); 24 Apr 2015 18:45:29 -0000 Received: from git1-us-west.apache.org (HELO git1-us-west.apache.org) (140.211.11.23) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 24 Apr 2015 18:45:29 +0000 Received: by git1-us-west.apache.org (ASF Mail Server at git1-us-west.apache.org, from userid 33) id C5732E091A; Fri, 24 Apr 2015 18:45:29 +0000 (UTC) Content-Type: text/plain; charset="us-ascii" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit From: imaxon@apache.org To: commits@asterixdb.incubator.apache.org Date: Fri, 24 Apr 2015 18:46:14 -0000 Message-Id: <6d96643f4de8458e863ee29870ef92f1@git.apache.org> In-Reply-To: <0a743a5c99b842bf8de294f7df7a3d2b@git.apache.org> References: <0a743a5c99b842bf8de294f7df7a3d2b@git.apache.org> X-Mailer: ASF-Git Admin Mailer Subject: [47/85] [abbrv] [partial] incubator-asterixdb-hyracks git commit: Move Pregelix and Hivesterix codebase to new repositories: 1. Move Pregelix codebase to https://github.com/pregelix/pregelix; 2. Move Hivesterix codebase to https://code.google.com/p/hives X-Virus-Checked: Checked by ClamAV on apache.org http://git-wip-us.apache.org/repos/asf/incubator-asterixdb-hyracks/blob/ffc967fd/hivesterix/hivesterix-dist/resource/hivesterix/tpch_sample/q1_pricing_summary_report.hive ---------------------------------------------------------------------- diff --git a/hivesterix/hivesterix-dist/resource/hivesterix/tpch_sample/q1_pricing_summary_report.hive b/hivesterix/hivesterix-dist/resource/hivesterix/tpch_sample/q1_pricing_summary_report.hive deleted file mode 100644 index 1785b48..0000000 --- a/hivesterix/hivesterix-dist/resource/hivesterix/tpch_sample/q1_pricing_summary_report.hive +++ /dev/null @@ -1,19 +0,0 @@ --- 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/100/lineitem'; - --- create the target table -CREATE TABLE q1_pricing_summary_report ( L_RETURNFLAG STRING, L_LINESTATUS STRING, SUM_QTY DOUBLE, SUM_BASE_PRICE DOUBLE, SUM_DISC_PRICE DOUBLE, SUM_CHARGE DOUBLE, AVE_QTY DOUBLE, AVE_PRICE DOUBLE, AVE_DISC DOUBLE, COUNT_ORDER INT); - --- the query -INSERT OVERWRITE TABLE q1_pricing_summary_report -SELECT - L_RETURNFLAG, L_LINESTATUS, SUM(L_QUANTITY), SUM(L_EXTENDEDPRICE), SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)), SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)*(1+L_TAX)), AVG(L_QUANTITY), AVG(L_EXTENDEDPRICE), AVG(L_DISCOUNT), COUNT(1) -FROM - lineitem -WHERE - L_SHIPDATE<='1998-09-02' -GROUP BY L_RETURNFLAG, L_LINESTATUS -ORDER BY L_RETURNFLAG, L_LINESTATUS; - -DROP TABLE lineitem; -DROP TABLE q1_pricing_summary_report; http://git-wip-us.apache.org/repos/asf/incubator-asterixdb-hyracks/blob/ffc967fd/hivesterix/hivesterix-dist/resource/hivesterix/tpch_sample/q3_shipping_priority.hive ---------------------------------------------------------------------- diff --git a/hivesterix/hivesterix-dist/resource/hivesterix/tpch_sample/q3_shipping_priority.hive b/hivesterix/hivesterix-dist/resource/hivesterix/tpch_sample/q3_shipping_priority.hive deleted file mode 100644 index 1dc68a2..0000000 --- a/hivesterix/hivesterix-dist/resource/hivesterix/tpch_sample/q3_shipping_priority.hive +++ /dev/null @@ -1,27 +0,0 @@ --- 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/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 customer (C_CUSTKEY INT, C_NAME STRING, C_ADDRESS STRING, C_NATIONKEY INT, C_PHONE STRING, C_ACCTBAL DOUBLE, C_MKTSEGMENT STRING, C_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/100/customer'; - --- create the target table -create table q3_shipping_priority (l_orderkey int, revenue double, o_orderdate string, o_shippriority int); - --- the query -Insert overwrite table q3_shipping_priority -select - l_orderkey, sum(l_extendedprice*(1-l_discount)) as revenue, o_orderdate, o_shippriority -from - customer c join orders o - on c.c_mktsegment = 'BUILDING' and c.c_custkey = o.o_custkey - join lineitem l - on l.l_orderkey = o.o_orderkey -where - o_orderdate < '1995-03-15' and l_shipdate > '1995-03-15' -group by l_orderkey, o_orderdate, o_shippriority -order by revenue desc, o_orderdate -limit 10; - -DROP TABLE orders; -DROP TABLE lineitem; -DROP TABLE customer; -DROP TABLE q3_shipping_priority; http://git-wip-us.apache.org/repos/asf/incubator-asterixdb-hyracks/blob/ffc967fd/hivesterix/hivesterix-dist/resource/hivesterix/tpch_sample/q5_local_supplier_volume.hive ---------------------------------------------------------------------- diff --git a/hivesterix/hivesterix-dist/resource/hivesterix/tpch_sample/q5_local_supplier_volume.hive b/hivesterix/hivesterix-dist/resource/hivesterix/tpch_sample/q5_local_supplier_volume.hive deleted file mode 100644 index be91a25..0000000 --- a/hivesterix/hivesterix-dist/resource/hivesterix/tpch_sample/q5_local_supplier_volume.hive +++ /dev/null @@ -1,39 +0,0 @@ --- create tables and load data -create external table customer (C_CUSTKEY INT, C_NAME STRING, C_ADDRESS STRING, C_NATIONKEY INT, C_PHONE STRING, C_ACCTBAL DOUBLE, C_MKTSEGMENT STRING, C_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/100/customer'; -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 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 external table region (R_REGIONKEY INT, R_NAME STRING, R_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/100/region'; - --- create the target table -create table q5_local_supplier_volume (N_NAME STRING, REVENUE DOUBLE); - --- the query -insert overwrite table q5_local_supplier_volume -select - n_name, sum(l_extendedprice * (1 - l_discount)) as revenue -from - customer c join - ( select n_name, l_extendedprice, l_discount, s_nationkey, o_custkey from orders o join - ( select n_name, l_extendedprice, l_discount, l_orderkey, s_nationkey from lineitem l join - ( select n_name, s_suppkey, s_nationkey from supplier s join - ( select n_name, n_nationkey - from nation n join region r - on n.n_regionkey = r.r_regionkey and r.r_name = 'ASIA' - ) n1 on s.s_nationkey = n1.n_nationkey - ) s1 on l.l_suppkey = s1.s_suppkey - ) l1 on l1.l_orderkey = o.o_orderkey and o.o_orderdate >= '1994-01-01' - and o.o_orderdate < '1995-01-01' -) o1 -on c.c_nationkey = o1.s_nationkey and c.c_custkey = o1.o_custkey -group by n_name -order by revenue desc; - -DROP TABLE customer; -DROP TABLE orders; -DROP TABLE lineitem; -DROP TABLE supplier; -DROP TABLE nation; -DROP TABLE region; -DROP TABLE q5_local_supplier_volume; http://git-wip-us.apache.org/repos/asf/incubator-asterixdb-hyracks/blob/ffc967fd/hivesterix/hivesterix-dist/resource/hivesterix/tpch_sample/q9_product_type_profit.hive ---------------------------------------------------------------------- diff --git a/hivesterix/hivesterix-dist/resource/hivesterix/tpch_sample/q9_product_type_profit.hive b/hivesterix/hivesterix-dist/resource/hivesterix/tpch_sample/q9_product_type_profit.hive deleted file mode 100644 index c95a92b..0000000 --- a/hivesterix/hivesterix-dist/resource/hivesterix/tpch_sample/q9_product_type_profit.hive +++ /dev/null @@ -1,47 +0,0 @@ --- 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; http://git-wip-us.apache.org/repos/asf/incubator-asterixdb-hyracks/blob/ffc967fd/hivesterix/hivesterix-dist/resource/tpch/q10_returned_item.hive ---------------------------------------------------------------------- diff --git a/hivesterix/hivesterix-dist/resource/tpch/q10_returned_item.hive b/hivesterix/hivesterix-dist/resource/tpch/q10_returned_item.hive deleted file mode 100644 index b6535cb..0000000 --- a/hivesterix/hivesterix-dist/resource/tpch/q10_returned_item.hive +++ /dev/null @@ -1,37 +0,0 @@ -DROP TABLE lineitem; -DROP TABLE orders; -DROP TABLE customer; -DROP TABLE nation; -DROP TABLE q10_returned_item; - --- create the tables and load the 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 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/10/orders'; -create external table customer (C_CUSTKEY INT, C_NAME STRING, C_ADDRESS STRING, C_NATIONKEY INT, C_PHONE STRING, C_ACCTBAL DOUBLE, C_MKTSEGMENT STRING, C_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/customer'; -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 the result table -create table q10_returned_item (c_custkey int, c_name string, revenue double, c_acctbal string, n_name string, c_address string, c_phone string, c_comment string); - -set mapred.min.split.size=536870912; -set hive.exec.reducers.bytes.per.reducer=1024000000; - --- the query -insert overwrite table q10_returned_item -select - c_custkey, c_name, sum(l_extendedprice * (1 - l_discount)) as revenue, - c_acctbal, n_name, c_address, c_phone, c_comment -from - customer c join orders o - on - c.c_custkey = o.o_custkey and o.o_orderdate >= '1993-10-01' and o.o_orderdate < '1994-01-01' - join nation n - on - c.c_nationkey = n.n_nationkey - join lineitem l - on - l.l_orderkey = o.o_orderkey and l.l_returnflag = 'R' -group by c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment -order by revenue desc -limit 20; - http://git-wip-us.apache.org/repos/asf/incubator-asterixdb-hyracks/blob/ffc967fd/hivesterix/hivesterix-dist/resource/tpch/q11_important_stock.hive ---------------------------------------------------------------------- diff --git a/hivesterix/hivesterix-dist/resource/tpch/q11_important_stock.hive b/hivesterix/hivesterix-dist/resource/tpch/q11_important_stock.hive deleted file mode 100644 index bfa3743..0000000 --- a/hivesterix/hivesterix-dist/resource/tpch/q11_important_stock.hive +++ /dev/null @@ -1,47 +0,0 @@ -DROP TABLE partsupp; -DROP TABLE supplier; -DROP TABLE nation; -DROP TABLE q11_important_stock; -DROP TABLE q11_part_tmp; -DROP TABLE q11_sum_tmp; - --- create tables and load data -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 q11_important_stock(ps_partkey INT, value DOUBLE); -create table q11_part_tmp(ps_partkey int, part_value double); -create table q11_sum_tmp(total_value double); - --- the query -insert overwrite table q11_part_tmp -select - ps_partkey, sum(ps_supplycost * ps_availqty) as part_value -from - nation n join supplier s - on - s.s_nationkey = n.n_nationkey and n.n_name = 'GERMANY' - join partsupp ps - on - ps.ps_suppkey = s.s_suppkey -group by ps_partkey; - -insert overwrite table q11_sum_tmp -select - sum(part_value) as total_value -from - q11_part_tmp; - -insert overwrite table q11_important_stock -select - ps_partkey, part_value as value -from - ( - select ps_partkey, part_value, total_value - from q11_part_tmp join q11_sum_tmp - ) a -where part_value > total_value * 0.0001 -order by value desc; - http://git-wip-us.apache.org/repos/asf/incubator-asterixdb-hyracks/blob/ffc967fd/hivesterix/hivesterix-dist/resource/tpch/q12_shipping.hive ---------------------------------------------------------------------- diff --git a/hivesterix/hivesterix-dist/resource/tpch/q12_shipping.hive b/hivesterix/hivesterix-dist/resource/tpch/q12_shipping.hive deleted file mode 100644 index 0ae896c..0000000 --- a/hivesterix/hivesterix-dist/resource/tpch/q12_shipping.hive +++ /dev/null @@ -1,42 +0,0 @@ -DROP TABLE lineitem; -DROP TABLE orders; -DROP TABLE q12_shipping; - --- create the tables and load the 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 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/10/orders'; - --- create the result table -create table q12_shipping(l_shipmode string, high_line_count double, low_line_count double); - -set mapred.min.split.size=536870912; -set hive.exec.reducers.bytes.per.reducer=1225000000; - --- the query -insert overwrite table q12_shipping -select - l_shipmode, - sum(case - when o_orderpriority ='1-URGENT' - or o_orderpriority ='2-HIGH' - then 1 - else 0 -end - ) as high_line_count, - sum(case - when o_orderpriority <> '1-URGENT' - and o_orderpriority <> '2-HIGH' - then 1 - else 0 -end - ) as low_line_count -from - orders o join lineitem l - on - o.o_orderkey = l.l_orderkey and l.l_commitdate < l.l_receiptdate -and l.l_shipdate < l.l_commitdate and l.l_receiptdate >= '1994-01-01' -and l.l_receiptdate < '1995-01-01' -where - l.l_shipmode = 'MAIL' or l.l_shipmode = 'SHIP' -group by l_shipmode -order by l_shipmode; http://git-wip-us.apache.org/repos/asf/incubator-asterixdb-hyracks/blob/ffc967fd/hivesterix/hivesterix-dist/resource/tpch/q13_customer_distribution.hive ---------------------------------------------------------------------- diff --git a/hivesterix/hivesterix-dist/resource/tpch/q13_customer_distribution.hive b/hivesterix/hivesterix-dist/resource/tpch/q13_customer_distribution.hive deleted file mode 100644 index dd3674d..0000000 --- a/hivesterix/hivesterix-dist/resource/tpch/q13_customer_distribution.hive +++ /dev/null @@ -1,27 +0,0 @@ -DROP TABLE customer; -DROP TABLE orders; -DROP TABLE q13_customer_distribution; - --- create the tables and load the data -create external table customer (C_CUSTKEY INT, C_NAME STRING, C_ADDRESS STRING, C_NATIONKEY INT, C_PHONE STRING, C_ACCTBAL DOUBLE, C_MKTSEGMENT STRING, C_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/customer'; -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/10/orders'; - --- create the result table -create table q13_customer_distribution (c_count int, custdist int); - --- the query -insert overwrite table q13_customer_distribution -select - c_count, count(1) as custdist -from - (select - c_custkey, count(o_orderkey) as c_count - from - customer c left outer join orders o - on - c.c_custkey = o.o_custkey and not o.o_comment like '%special%requests%' - group by c_custkey - ) c_orders -group by c_count -order by custdist desc, c_count desc; - http://git-wip-us.apache.org/repos/asf/incubator-asterixdb-hyracks/blob/ffc967fd/hivesterix/hivesterix-dist/resource/tpch/q14_promotion_effect.hive ---------------------------------------------------------------------- diff --git a/hivesterix/hivesterix-dist/resource/tpch/q14_promotion_effect.hive b/hivesterix/hivesterix-dist/resource/tpch/q14_promotion_effect.hive deleted file mode 100644 index a7ea773..0000000 --- a/hivesterix/hivesterix-dist/resource/tpch/q14_promotion_effect.hive +++ /dev/null @@ -1,28 +0,0 @@ -DROP TABLE lineitem; -DROP TABLE part; -DROP TABLE q14_promotion_effect; - --- create the tables and load the 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 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 the result table -create table q14_promotion_effect(promo_revenue double); - -set mapred.min.split.size=536870912; -set hive.exec.reducers.bytes.per.reducer=1040000000; - --- the query -insert overwrite table q14_promotion_effect -select - 100.00 * sum(case - when p_type like 'PROMO%' - then l_extendedprice*(1-l_discount) - else 0.0 - end - ) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue -from - part p join lineitem l - on - l.l_partkey = p.p_partkey and l.l_shipdate >= '1995-09-01' and l.l_shipdate < '1995-10-01'; - http://git-wip-us.apache.org/repos/asf/incubator-asterixdb-hyracks/blob/ffc967fd/hivesterix/hivesterix-dist/resource/tpch/q15_top_supplier.hive ---------------------------------------------------------------------- diff --git a/hivesterix/hivesterix-dist/resource/tpch/q15_top_supplier.hive b/hivesterix/hivesterix-dist/resource/tpch/q15_top_supplier.hive deleted file mode 100644 index b38ba2c..0000000 --- a/hivesterix/hivesterix-dist/resource/tpch/q15_top_supplier.hive +++ /dev/null @@ -1,45 +0,0 @@ -DROP TABLE lineitem; -DROP TABLE supplier; -DROP TABLE revenue; -DROP TABLE max_revenue; -DROP TABLE q15_top_supplier; - --- create the tables and load the 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 result tables -create table revenue(supplier_no int, total_revenue double); -create table max_revenue(max_revenue double); -create table q15_top_supplier(s_suppkey int, s_name string, s_address string, s_phone string, total_revenue double); - - -set mapred.min.split.size=536870912; - --- the query -insert overwrite table revenue -select - l_suppkey as supplier_no, sum(l_extendedprice * (1 - l_discount)) as total_revenue -from - lineitem -where - l_shipdate >= '1996-01-01' and l_shipdate < '1996-04-01' -group by l_suppkey; - -insert overwrite table max_revenue -select - max(total_revenue) -from - revenue; - -insert overwrite table q15_top_supplier -select - s_suppkey, s_name, s_address, s_phone, total_revenue -from supplier s join revenue r - on - s.s_suppkey = r.supplier_no - join max_revenue m - on - r.total_revenue = m.max_revenue -order by s_suppkey; - http://git-wip-us.apache.org/repos/asf/incubator-asterixdb-hyracks/blob/ffc967fd/hivesterix/hivesterix-dist/resource/tpch/q16_parts_supplier_relationship.hive ---------------------------------------------------------------------- diff --git a/hivesterix/hivesterix-dist/resource/tpch/q16_parts_supplier_relationship.hive b/hivesterix/hivesterix-dist/resource/tpch/q16_parts_supplier_relationship.hive deleted file mode 100644 index 495a5ea..0000000 --- a/hivesterix/hivesterix-dist/resource/tpch/q16_parts_supplier_relationship.hive +++ /dev/null @@ -1,53 +0,0 @@ -DROP TABLE partsupp; -DROP TABLE part; -DROP TABLE supplier; -DROP TABLE q16_parts_supplier_relationship; -DROP TABLE q16_tmp; -DROP TABLE supplier_tmp; - --- 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 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 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 the result table -create table q16_parts_supplier_relationship(p_brand string, p_type string, p_size int, supplier_cnt int); -create table q16_tmp(p_brand string, p_type string, p_size int, ps_suppkey int); -create table supplier_tmp(s_suppkey int); - --- the query -insert overwrite table supplier_tmp -select - s_suppkey -from - supplier -where - not s_comment like '%Customer%Complaints%'; - -insert overwrite table q16_tmp -select - p_brand, p_type, p_size, ps_suppkey -from - partsupp ps join part p - on - p.p_partkey = ps.ps_partkey and p.p_brand <> 'Brand#45' - and not p.p_type like 'MEDIUM POLISHED%' - join supplier_tmp s - on - ps.ps_suppkey = s.s_suppkey; - -insert overwrite table q16_parts_supplier_relationship -select - p_brand, p_type, p_size, count(distinct ps_suppkey) as supplier_cnt -from - (select - * - from - q16_tmp - where p_size = 49 or p_size = 14 or p_size = 23 or - p_size = 45 or p_size = 19 or p_size = 3 or - p_size = 36 or p_size = 9 -) q16_all -group by p_brand, p_type, p_size -order by supplier_cnt desc, p_brand, p_type, p_size; - http://git-wip-us.apache.org/repos/asf/incubator-asterixdb-hyracks/blob/ffc967fd/hivesterix/hivesterix-dist/resource/tpch/q17_small_quantity_order_revenue.hive ---------------------------------------------------------------------- diff --git a/hivesterix/hivesterix-dist/resource/tpch/q17_small_quantity_order_revenue.hive b/hivesterix/hivesterix-dist/resource/tpch/q17_small_quantity_order_revenue.hive deleted file mode 100644 index 448b8f3..0000000 --- a/hivesterix/hivesterix-dist/resource/tpch/q17_small_quantity_order_revenue.hive +++ /dev/null @@ -1,38 +0,0 @@ -DROP TABLE lineitem; -DROP TABLE part; -DROP TABLE q17_small_quantity_order_revenue; -DROP TABLE lineitem_tmp; - --- create the tables and load the 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 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 the result table -create table q17_small_quantity_order_revenue (avg_yearly double); -create table lineitem_tmp (t_partkey int, t_avg_quantity double); - --- the query -insert overwrite table lineitem_tmp -select - l_partkey as t_partkey, 0.2 * avg(l_quantity) as t_avg_quantity -from - lineitem -group by l_partkey; - -insert overwrite table q17_small_quantity_order_revenue -select - sum(l_extendedprice) / 7.0 as avg_yearly -from - (select l_quantity, l_extendedprice, t_avg_quantity from - lineitem_tmp t join - (select - l_quantity, l_partkey, l_extendedprice - from - part p join lineitem l - on - p.p_partkey = l.l_partkey - and p.p_brand = 'Brand#23' - and p.p_container = 'MED BOX' - ) l1 on l1.l_partkey = t.t_partkey - ) a -where l_quantity < t_avg_quantity; http://git-wip-us.apache.org/repos/asf/incubator-asterixdb-hyracks/blob/ffc967fd/hivesterix/hivesterix-dist/resource/tpch/q18_large_volume_customer.hive ---------------------------------------------------------------------- diff --git a/hivesterix/hivesterix-dist/resource/tpch/q18_large_volume_customer.hive b/hivesterix/hivesterix-dist/resource/tpch/q18_large_volume_customer.hive deleted file mode 100644 index 04081ad..0000000 --- a/hivesterix/hivesterix-dist/resource/tpch/q18_large_volume_customer.hive +++ /dev/null @@ -1,43 +0,0 @@ -DROP TABLE lineitem; -DROP TABLE orders; -DROP TABLE customer; -DROP TABLE q18_tmp; -DROP TABLE q18_large_volume_customer; - --- create the tables and load the 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 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/10/orders'; -create external table customer (C_CUSTKEY INT, C_NAME STRING, C_ADDRESS STRING, C_NATIONKEY INT, C_PHONE STRING, C_ACCTBAL DOUBLE, C_MKTSEGMENT STRING, C_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/customer'; - --- create the result tables -create table q18_tmp(l_orderkey int, t_sum_quantity double); -create table q18_large_volume_customer(c_name string, c_custkey int, o_orderkey int, o_orderdate string, o_totalprice double, sum_quantity double); - -set mapred.min.split.size=268435456; -set hive.exec.reducers.bytes.per.reducer=1164000000; - --- the query -insert overwrite table q18_tmp -select - l_orderkey, sum(l_quantity) as t_sum_quantity -from - lineitem -group by l_orderkey; - -insert overwrite table q18_large_volume_customer -select - c_name,c_custkey,o_orderkey,o_orderdate,o_totalprice,sum(l_quantity) -from - customer c join orders o - on - c.c_custkey = o.o_custkey - join q18_tmp t - on - o.o_orderkey = t.l_orderkey and t.t_sum_quantity > 300 - join lineitem l - on - o.o_orderkey = l.l_orderkey -group by c_name,c_custkey,o_orderkey,o_orderdate,o_totalprice -order by o_totalprice desc,o_orderdate -limit 100; - http://git-wip-us.apache.org/repos/asf/incubator-asterixdb-hyracks/blob/ffc967fd/hivesterix/hivesterix-dist/resource/tpch/q19_discounted_revenue.hive ---------------------------------------------------------------------- diff --git a/hivesterix/hivesterix-dist/resource/tpch/q19_discounted_revenue.hive b/hivesterix/hivesterix-dist/resource/tpch/q19_discounted_revenue.hive deleted file mode 100644 index 1e821ca..0000000 --- a/hivesterix/hivesterix-dist/resource/tpch/q19_discounted_revenue.hive +++ /dev/null @@ -1,49 +0,0 @@ -DROP TABLE lineitem; -DROP TABLE part; -DROP TABLE q19_discounted_revenue; - --- create the tables and load the 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 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 the result table -create table q19_discounted_revenue(revenue double); - -set mapred.min.split.size=268435456; -set hive.exec.reducers.bytes.per.reducer=1040000000; - --- the query -insert overwrite table q19_discounted_revenue -select - sum(l_extendedprice * (1 - l_discount) ) as revenue -from - lineitem l join part p - on - p.p_partkey = l.l_partkey -where - ( - p_brand = 'Brand#12' - and p_container REGEXP 'SM CASE||SM BOX||SM PACK||SM PKG' - and l_quantity >= 1 and l_quantity <= 11 - and p_size >= 1 and p_size <= 5 - and l_shipmode REGEXP 'AIR||AIR REG' - and l_shipinstruct = 'DELIVER IN PERSON' - ) - or - ( - p_brand = 'Brand#23' - and p_container REGEXP 'MED BAG||MED BOX||MED PKG||MED PACK' - and l_quantity >= 10 and l_quantity <= 20 - and p_size >= 1 and p_size <= 10 - and l_shipmode REGEXP 'AIR||AIR REG' - and l_shipinstruct = 'DELIVER IN PERSON' - ) - or - ( - p_brand = 'Brand#34' - and p_container REGEXP 'LG CASE||LG BOX||LG PACK||LG PKG' - and l_quantity >= 20 and l_quantity <= 30 - and p_size >= 1 and p_size <= 15 - and l_shipmode REGEXP 'AIR||AIR REG' - and l_shipinstruct = 'DELIVER IN PERSON' - ); http://git-wip-us.apache.org/repos/asf/incubator-asterixdb-hyracks/blob/ffc967fd/hivesterix/hivesterix-dist/resource/tpch/q1_pricing_summary_report.hive ---------------------------------------------------------------------- diff --git a/hivesterix/hivesterix-dist/resource/tpch/q1_pricing_summary_report.hive b/hivesterix/hivesterix-dist/resource/tpch/q1_pricing_summary_report.hive deleted file mode 100644 index 94b2913..0000000 --- a/hivesterix/hivesterix-dist/resource/tpch/q1_pricing_summary_report.hive +++ /dev/null @@ -1,21 +0,0 @@ -DROP TABLE lineitem; -DROP TABLE q1_pricing_summary_report; - --- 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 the target table -CREATE TABLE q1_pricing_summary_report ( L_RETURNFLAG STRING, L_LINESTATUS STRING, SUM_QTY DOUBLE, SUM_BASE_PRICE DOUBLE, SUM_DISC_PRICE DOUBLE, SUM_CHARGE DOUBLE, AVE_QTY DOUBLE, AVE_PRICE DOUBLE, AVE_DISC DOUBLE, COUNT_ORDER INT); - -set mapred.min.split.size=536870912; - --- the query --- INSERT OVERWRITE TABLE q1_pricing_summary_report -SELECT - L_RETURNFLAG, L_LINESTATUS, SUM(L_QUANTITY), SUM(L_EXTENDEDPRICE), SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)), SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)*(1+L_TAX)), AVG(L_QUANTITY), AVG(L_EXTENDEDPRICE), AVG(L_DISCOUNT), COUNT(1) -FROM - lineitem -WHERE - L_SHIPDATE<='1998-09-02' -GROUP BY L_RETURNFLAG, L_LINESTATUS -ORDER BY L_RETURNFLAG, L_LINESTATUS; http://git-wip-us.apache.org/repos/asf/incubator-asterixdb-hyracks/blob/ffc967fd/hivesterix/hivesterix-dist/resource/tpch/q20_potential_part_promotion.hive ---------------------------------------------------------------------- diff --git a/hivesterix/hivesterix-dist/resource/tpch/q20_potential_part_promotion.hive b/hivesterix/hivesterix-dist/resource/tpch/q20_potential_part_promotion.hive deleted file mode 100644 index 40ae423..0000000 --- a/hivesterix/hivesterix-dist/resource/tpch/q20_potential_part_promotion.hive +++ /dev/null @@ -1,77 +0,0 @@ -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; - - http://git-wip-us.apache.org/repos/asf/incubator-asterixdb-hyracks/blob/ffc967fd/hivesterix/hivesterix-dist/resource/tpch/q21_suppliers_who_kept_orders_waiting.hive ---------------------------------------------------------------------- diff --git a/hivesterix/hivesterix-dist/resource/tpch/q21_suppliers_who_kept_orders_waiting.hive b/hivesterix/hivesterix-dist/resource/tpch/q21_suppliers_who_kept_orders_waiting.hive deleted file mode 100644 index 0418540..0000000 --- a/hivesterix/hivesterix-dist/resource/tpch/q21_suppliers_who_kept_orders_waiting.hive +++ /dev/null @@ -1,74 +0,0 @@ -DROP TABLE orders; -DROP TABLE lineitem; -DROP TABLE supplier; -DROP TABLE nation; -DROP TABLE q21_tmp1; -DROP TABLE q21_tmp2; -DROP TABLE q21_suppliers_who_kept_orders_waiting; - --- 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 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/10/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/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 target tables -create table q21_tmp1(l_orderkey int, count_suppkey int, max_suppkey int); -create table q21_tmp2(l_orderkey int, count_suppkey int, max_suppkey int); -create table q21_suppliers_who_kept_orders_waiting(s_name string, numwait int); - --- the query -insert overwrite table q21_tmp1 -select - l_orderkey, count(distinct l_suppkey), max(l_suppkey) as max_suppkey -from - lineitem -group by l_orderkey; - -insert overwrite table q21_tmp2 -select - l_orderkey, count(distinct l_suppkey), max(l_suppkey) as max_suppkey -from - lineitem -where - l_receiptdate > l_commitdate -group by l_orderkey; - -insert overwrite table q21_suppliers_who_kept_orders_waiting -select - s_name, count(1) as numwait -from - (select s_name from -(select s_name, t2.l_orderkey, l_suppkey, count_suppkey, max_suppkey - from q21_tmp2 t2 right outer join - (select s_name, l_orderkey, l_suppkey from - (select s_name, t1.l_orderkey, l_suppkey, count_suppkey, max_suppkey - from - q21_tmp1 t1 join - (select s_name, l_orderkey, l_suppkey - from - orders o join - (select s_name, l_orderkey, l_suppkey - from - nation n join supplier s - on - s.s_nationkey = n.n_nationkey - and n.n_name = 'SAUDI ARABIA' - join lineitem l - on - s.s_suppkey = l.l_suppkey - where - l.l_receiptdate > l.l_commitdate - ) l1 on o.o_orderkey = l1.l_orderkey and o.o_orderstatus = 'F' - ) l2 on l2.l_orderkey = t1.l_orderkey - ) a - where - (count_suppkey > 1) or ((count_suppkey=1) and (l_suppkey <> max_suppkey)) - ) l3 on l3.l_orderkey = t2.l_orderkey - ) b - where - (count_suppkey is null) or ((count_suppkey=1) and (l_suppkey = max_suppkey)) - )c -group by s_name -order by numwait desc, s_name -limit 100; http://git-wip-us.apache.org/repos/asf/incubator-asterixdb-hyracks/blob/ffc967fd/hivesterix/hivesterix-dist/resource/tpch/q22_global_sales_opportunity.hive ---------------------------------------------------------------------- diff --git a/hivesterix/hivesterix-dist/resource/tpch/q22_global_sales_opportunity.hive b/hivesterix/hivesterix-dist/resource/tpch/q22_global_sales_opportunity.hive deleted file mode 100644 index 379cfc9..0000000 --- a/hivesterix/hivesterix-dist/resource/tpch/q22_global_sales_opportunity.hive +++ /dev/null @@ -1,70 +0,0 @@ -DROP TABLE customer; -DROP TABLE orders; -DROP TABLE q22_customer_tmp; -DROP TABLE q22_customer_tmp1; -DROP TABLE q22_orders_tmp; -DROP TABLE q22_global_sales_opportunity; - --- create tables and load data -create external table customer (C_CUSTKEY INT, C_NAME STRING, C_ADDRESS STRING, C_NATIONKEY INT, C_PHONE STRING, C_ACCTBAL DOUBLE, C_MKTSEGMENT STRING, C_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/customer'; -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/10/orders'; - --- create target tables -create table q22_customer_tmp(c_acctbal double, c_custkey int, cntrycode string); -create table q22_customer_tmp1(avg_acctbal double); -create table q22_orders_tmp(o_custkey int); -create table q22_global_sales_opportunity(cntrycode string, numcust int, totacctbal double); - --- the query -insert overwrite table q22_customer_tmp -select - c_acctbal, c_custkey, substr(c_phone, 1, 2) as cntrycode -from - customer -where - substr(c_phone, 1, 2) = '13' or - substr(c_phone, 1, 2) = '31' or - substr(c_phone, 1, 2) = '23' or - substr(c_phone, 1, 2) = '29' or - substr(c_phone, 1, 2) = '30' or - substr(c_phone, 1, 2) = '18' or - substr(c_phone, 1, 2) = '17'; - -insert overwrite table q22_customer_tmp1 -select - avg(c_acctbal) -from - q22_customer_tmp -where - c_acctbal > 0.00; - -insert overwrite table q22_orders_tmp -select - o_custkey -from - orders -group by - o_custkey; - -insert overwrite table q22_global_sales_opportunity -select - cntrycode, count(1) as numcust, sum(c_acctbal) as totacctbal -from -( - select cntrycode, c_acctbal, avg_acctbal from - q22_customer_tmp1 ct1 join - ( - select cntrycode, c_acctbal from - q22_orders_tmp ot - right outer join q22_customer_tmp ct - on - ct.c_custkey = ot.o_custkey - where - o_custkey is null - ) ct2 -) a -where - c_acctbal > avg_acctbal -group by cntrycode -order by cntrycode; - http://git-wip-us.apache.org/repos/asf/incubator-asterixdb-hyracks/blob/ffc967fd/hivesterix/hivesterix-dist/resource/tpch/q2_copy.hive ---------------------------------------------------------------------- diff --git a/hivesterix/hivesterix-dist/resource/tpch/q2_copy.hive b/hivesterix/hivesterix-dist/resource/tpch/q2_copy.hive deleted file mode 100644 index 647d500..0000000 --- a/hivesterix/hivesterix-dist/resource/tpch/q2_copy.hive +++ /dev/null @@ -1,46 +0,0 @@ -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 - http://git-wip-us.apache.org/repos/asf/incubator-asterixdb-hyracks/blob/ffc967fd/hivesterix/hivesterix-dist/resource/tpch/q2_minimum_cost_supplier.hive ---------------------------------------------------------------------- diff --git a/hivesterix/hivesterix-dist/resource/tpch/q2_minimum_cost_supplier.hive b/hivesterix/hivesterix-dist/resource/tpch/q2_minimum_cost_supplier.hive deleted file mode 100644 index 7a68ee2..0000000 --- a/hivesterix/hivesterix-dist/resource/tpch/q2_minimum_cost_supplier.hive +++ /dev/null @@ -1,56 +0,0 @@ -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 and p.p_type like '%BRASS' ; - -insert overwrite table q2_minimum_cost_supplier_tmp2 -select - p_partkey, min(ps_supplycost) -from - q2_minimum_cost_supplier_tmp1 -group by p_partkey; - -insert overwrite table q2_minimum_cost_supplier -select - t1.s_acctbal, t1.s_name, t1.n_name, t1.p_partkey, t1.p_mfgr, t1.s_address, t1.s_phone, t1.s_comment -from - q2_minimum_cost_supplier_tmp1 t1 join q2_minimum_cost_supplier_tmp2 t2 -on - t1.p_partkey = t2.p_partkey and t1.ps_supplycost=t2.ps_min_supplycost -order by s_acctbal desc, n_name, s_name, p_partkey -limit 100; - http://git-wip-us.apache.org/repos/asf/incubator-asterixdb-hyracks/blob/ffc967fd/hivesterix/hivesterix-dist/resource/tpch/q3_shipping_priority.hive ---------------------------------------------------------------------- diff --git a/hivesterix/hivesterix-dist/resource/tpch/q3_shipping_priority.hive b/hivesterix/hivesterix-dist/resource/tpch/q3_shipping_priority.hive deleted file mode 100644 index 888775e..0000000 --- a/hivesterix/hivesterix-dist/resource/tpch/q3_shipping_priority.hive +++ /dev/null @@ -1,30 +0,0 @@ -DROP TABLE orders; -DROP TABLE lineitem; -DROP TABLE customer; -DROP TABLE q3_shipping_priority; - --- 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 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/10/orders'; -create external table customer (C_CUSTKEY INT, C_NAME STRING, C_ADDRESS STRING, C_NATIONKEY INT, C_PHONE STRING, C_ACCTBAL DOUBLE, C_MKTSEGMENT STRING, C_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/customer'; - --- create the target table -create table q3_shipping_priority (l_orderkey int, revenue double, o_orderdate string, o_shippriority int); - -set mapred.min.split.size=536870912; -set hive.exec.reducers.bytes.per.reducer=1024000000; - --- the query -Insert overwrite table q3_shipping_priority -select - l_orderkey, sum(l_extendedprice*(1-l_discount)) as revenue, o_orderdate, o_shippriority -from - customer c join orders o - on c.c_mktsegment = 'BUILDING' and c.c_custkey = o.o_custkey - join lineitem l - on l.l_orderkey = o.o_orderkey -where - o_orderdate < '1995-03-15' and l_shipdate > '1995-03-15' -group by l_orderkey, o_orderdate, o_shippriority -order by revenue desc, o_orderdate -limit 10; http://git-wip-us.apache.org/repos/asf/incubator-asterixdb-hyracks/blob/ffc967fd/hivesterix/hivesterix-dist/resource/tpch/q4_order_priority.hive ---------------------------------------------------------------------- diff --git a/hivesterix/hivesterix-dist/resource/tpch/q4_order_priority.hive b/hivesterix/hivesterix-dist/resource/tpch/q4_order_priority.hive deleted file mode 100644 index 18c8d9d..0000000 --- a/hivesterix/hivesterix-dist/resource/tpch/q4_order_priority.hive +++ /dev/null @@ -1,30 +0,0 @@ -DROP TABLE orders; -DROP TABLE lineitem; -DROP TABLE q4_order_priority_tmp; -DROP TABLE q4_order_priority; - --- create tables and load data -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/10/orders'; -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 the target table -CREATE TABLE q4_order_priority_tmp (O_ORDERKEY INT); -CREATE TABLE q4_order_priority (O_ORDERPRIORITY STRING, ORDER_COUNT INT); - -set mapred.min.split.size=536870912; --- the query -INSERT OVERWRITE TABLE q4_order_priority_tmp -select - DISTINCT l_orderkey -from - lineitem -where - l_commitdate < l_receiptdate; -INSERT OVERWRITE TABLE q4_order_priority -select o_orderpriority, count(1) as order_count -from - orders o join q4_order_priority_tmp t - on -o.o_orderkey = t.o_orderkey and o.o_orderdate >= '1993-07-01' and o.o_orderdate < '1993-10-01' -group by o_orderpriority -order by o_orderpriority; http://git-wip-us.apache.org/repos/asf/incubator-asterixdb-hyracks/blob/ffc967fd/hivesterix/hivesterix-dist/resource/tpch/q5_local_supplier_volume.hive ---------------------------------------------------------------------- diff --git a/hivesterix/hivesterix-dist/resource/tpch/q5_local_supplier_volume.hive b/hivesterix/hivesterix-dist/resource/tpch/q5_local_supplier_volume.hive deleted file mode 100644 index f5b10d8..0000000 --- a/hivesterix/hivesterix-dist/resource/tpch/q5_local_supplier_volume.hive +++ /dev/null @@ -1,42 +0,0 @@ -DROP TABLE customer; -DROP TABLE orders; -DROP TABLE lineitem; -DROP TABLE supplier; -DROP TABLE nation; -DROP TABLE region; -DROP TABLE q5_local_supplier_volume; - --- create tables and load data -create external table customer (C_CUSTKEY INT, C_NAME STRING, C_ADDRESS STRING, C_NATIONKEY INT, C_PHONE STRING, C_ACCTBAL DOUBLE, C_MKTSEGMENT STRING, C_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/customer'; -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 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/10/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/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 region (R_REGIONKEY INT, R_NAME STRING, R_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/region'; - --- create the target table -create table q5_local_supplier_volume (N_NAME STRING, REVENUE DOUBLE); - -set mapred.min.split.size=536870912; - --- the query -insert overwrite table q5_local_supplier_volume -select - n_name, sum(l_extendedprice * (1 - l_discount)) as revenue -from - customer c join - ( select n_name, l_extendedprice, l_discount, s_nationkey, o_custkey from orders o join - ( select n_name, l_extendedprice, l_discount, l_orderkey, s_nationkey from lineitem l join - ( select n_name, s_suppkey, s_nationkey from supplier s join - ( select n_name, n_nationkey - from nation n join region r - on n.n_regionkey = r.r_regionkey and r.r_name = 'ASIA' - ) n1 on s.s_nationkey = n1.n_nationkey - ) s1 on l.l_suppkey = s1.s_suppkey - ) l1 on l1.l_orderkey = o.o_orderkey and o.o_orderdate >= '1994-01-01' - and o.o_orderdate < '1995-01-01' -) o1 -on c.c_nationkey = o1.s_nationkey and c.c_custkey = o1.o_custkey -group by n_name -order by revenue desc; - http://git-wip-us.apache.org/repos/asf/incubator-asterixdb-hyracks/blob/ffc967fd/hivesterix/hivesterix-dist/resource/tpch/q6_forecast_revenue_change.hive ---------------------------------------------------------------------- diff --git a/hivesterix/hivesterix-dist/resource/tpch/q6_forecast_revenue_change.hive b/hivesterix/hivesterix-dist/resource/tpch/q6_forecast_revenue_change.hive deleted file mode 100644 index 72900c7..0000000 --- a/hivesterix/hivesterix-dist/resource/tpch/q6_forecast_revenue_change.hive +++ /dev/null @@ -1,21 +0,0 @@ -DROP TABLE lineitem; -DROP TABLE q6_forecast_revenue_change; - --- 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 the target table -create table q6_forecast_revenue_change (revenue double); - --- the query -insert overwrite table q6_forecast_revenue_change -select - sum(l_extendedprice*l_discount) as revenue -from - lineitem -where - l_shipdate >= '1994-01-01' - and l_shipdate < '1995-01-01' - and l_discount >= 0.05 and l_discount <= 0.07 - and l_quantity < 24; - http://git-wip-us.apache.org/repos/asf/incubator-asterixdb-hyracks/blob/ffc967fd/hivesterix/hivesterix-dist/resource/tpch/q7_volume_shipping.hive ---------------------------------------------------------------------- diff --git a/hivesterix/hivesterix-dist/resource/tpch/q7_volume_shipping.hive b/hivesterix/hivesterix-dist/resource/tpch/q7_volume_shipping.hive deleted file mode 100644 index da6eab2..0000000 --- a/hivesterix/hivesterix-dist/resource/tpch/q7_volume_shipping.hive +++ /dev/null @@ -1,71 +0,0 @@ -DROP TABLE customer; -DROP TABLE orders; -DROP TABLE lineitem; -DROP TABLE supplier; -DROP TABLE nation; -DROP TABLE q7_volume_shipping; -DROP TABLE q7_volume_shipping_tmp; - --- create tables and load data -create external table customer (C_CUSTKEY INT, C_NAME STRING, C_ADDRESS STRING, C_NATIONKEY INT, C_PHONE STRING, C_ACCTBAL DOUBLE, C_MKTSEGMENT STRING, C_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/customer'; -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 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/10/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/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 the target table -create table q7_volume_shipping (supp_nation string, cust_nation string, l_year int, revenue double); -create table q7_volume_shipping_tmp(supp_nation string, cust_nation string, s_nationkey int, c_nationkey int); - -set mapred.min.split.size=536870912; -set hive.exec.reducers.bytes.per.reducer=1225000000; - --- the query -insert overwrite table q7_volume_shipping_tmp -select - * -from - ( - select - n1.n_name as supp_nation, n2.n_name as cust_nation, n1.n_nationkey as s_nationkey, - n2.n_nationkey as c_nationkey -from - nation n1 join nation n2 - on - n1.n_name = 'FRANCE' and n2.n_name = 'GERMANY' - UNION ALL -select - n1.n_name as supp_nation, n2.n_name as cust_nation, n1.n_nationkey as s_nationkey, - n2.n_nationkey as c_nationkey -from - nation n1 join nation n2 - on - n2.n_name = 'FRANCE' and n1.n_name = 'GERMANY' -) a; - -insert overwrite table q7_volume_shipping -select - supp_nation, cust_nation, l_year, sum(volume) as revenue -from - ( - select - supp_nation, cust_nation, year(l_shipdate) as l_year, - l_extendedprice * (1 - l_discount) as volume - from - q7_volume_shipping_tmp t join - (select l_shipdate, l_extendedprice, l_discount, c_nationkey, s_nationkey - from supplier s join - (select l_shipdate, l_extendedprice, l_discount, l_suppkey, c_nationkey - from customer c join - (select l_shipdate, l_extendedprice, l_discount, l_suppkey, o_custkey - from orders o join lineitem l - on - o.o_orderkey = l.l_orderkey and l.l_shipdate >= '1995-01-01' - and l.l_shipdate <= '1996-12-31' - ) l1 on c.c_custkey = l1.o_custkey - ) l2 on s.s_suppkey = l2.l_suppkey - ) l3 on l3.c_nationkey = t.c_nationkey and l3.s_nationkey = t.s_nationkey - ) shipping -group by supp_nation, cust_nation, l_year -order by supp_nation, cust_nation, l_year; - http://git-wip-us.apache.org/repos/asf/incubator-asterixdb-hyracks/blob/ffc967fd/hivesterix/hivesterix-dist/resource/tpch/q8_national_market_share.hive ---------------------------------------------------------------------- diff --git a/hivesterix/hivesterix-dist/resource/tpch/q8_national_market_share.hive b/hivesterix/hivesterix-dist/resource/tpch/q8_national_market_share.hive deleted file mode 100644 index ae2abec..0000000 --- a/hivesterix/hivesterix-dist/resource/tpch/q8_national_market_share.hive +++ /dev/null @@ -1,56 +0,0 @@ -DROP TABLE customer; -DROP TABLE orders; -DROP TABLE lineitem; -DROP TABLE supplier; -DROP TABLE nation; -DROP TABLE region; -DROP TABLE part; -DROP TABLE q8_national_market_share; - --- 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 customer (C_CUSTKEY INT, C_NAME STRING, C_ADDRESS STRING, C_NATIONKEY INT, C_PHONE STRING, C_ACCTBAL DOUBLE, C_MKTSEGMENT STRING, C_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/customer'; -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 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/10/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/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 region (R_REGIONKEY INT, R_NAME STRING, R_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/10/region'; - --- create the result table -create table q8_national_market_share(o_year string, mkt_share double); - --- the query -insert overwrite table q8_national_market_share -select - o_year, sum(case when nation = 'BRAZIL' then volume else 0.0 end) / sum(volume) as mkt_share -from - ( -select - year(o_orderdate) as o_year, l_extendedprice * (1-l_discount) as volume, - n2.n_name as nation - from - nation n2 join - (select o_orderdate, l_discount, l_extendedprice, s_nationkey - from supplier s join - (select o_orderdate, l_discount, l_extendedprice, l_suppkey - from part p join - (select o_orderdate, l_partkey, l_discount, l_extendedprice, l_suppkey - from lineitem l join - (select o_orderdate, o_orderkey - from orders o join - (select c.c_custkey - from customer c join - (select n1.n_nationkey - from nation n1 join region r - on n1.n_regionkey = r.r_regionkey and r.r_name = 'AMERICA' - ) n11 on c.c_nationkey = n11.n_nationkey - ) c1 on c1.c_custkey = o.o_custkey - ) o1 on l.l_orderkey = o1.o_orderkey and o1.o_orderdate >= '1995-01-01' - and o1.o_orderdate < '1996-12-31' - ) l1 on p.p_partkey = l1.l_partkey and p.p_type = 'ECONOMY ANODIZED STEEL' - ) p1 on s.s_suppkey = p1.l_suppkey - ) s1 on s1.s_nationkey = n2.n_nationkey - ) all_nation -group by o_year -order by o_year; - http://git-wip-us.apache.org/repos/asf/incubator-asterixdb-hyracks/blob/ffc967fd/hivesterix/hivesterix-dist/resource/tpch/q9_product_type_profit.hive ---------------------------------------------------------------------- diff --git a/hivesterix/hivesterix-dist/resource/tpch/q9_product_type_profit.hive b/hivesterix/hivesterix-dist/resource/tpch/q9_product_type_profit.hive deleted file mode 100644 index bc8ba3f..0000000 --- a/hivesterix/hivesterix-dist/resource/tpch/q9_product_type_profit.hive +++ /dev/null @@ -1,51 +0,0 @@ -DROP TABLE part; -DROP TABLE lineitem; -DROP TABLE supplier; -DROP TABLE orders; -DROP TABLE partsupp; -DROP TABLE nation; -DROP TABLE q9_product_type_profit; - --- 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 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 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/10/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/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 the result table -create table q9_product_type_profit (nation string, o_year string, sum_profit double); - -set mapred.min.split.size=536870912; -set hive.exec.reducers.bytes.per.reducer=1024000000; - --- 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; - http://git-wip-us.apache.org/repos/asf/incubator-asterixdb-hyracks/blob/ffc967fd/hivesterix/hivesterix-dist/resource/tpch100/q10_returned_item.hive ---------------------------------------------------------------------- diff --git a/hivesterix/hivesterix-dist/resource/tpch100/q10_returned_item.hive b/hivesterix/hivesterix-dist/resource/tpch100/q10_returned_item.hive deleted file mode 100644 index 1e4e3c6..0000000 --- a/hivesterix/hivesterix-dist/resource/tpch100/q10_returned_item.hive +++ /dev/null @@ -1,36 +0,0 @@ --- create the tables and load the 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/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 customer (C_CUSTKEY INT, C_NAME STRING, C_ADDRESS STRING, C_NATIONKEY INT, C_PHONE STRING, C_ACCTBAL DOUBLE, C_MKTSEGMENT STRING, C_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/100/customer'; -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 q10_returned_item (c_custkey int, c_name string, revenue double, c_acctbal string, n_name string, c_address string, c_phone string, c_comment string); - -set mapred.min.split.size=536870912; -set hive.exec.reducers.bytes.per.reducer=1024000000; - --- the query -insert overwrite table q10_returned_item -select - c_custkey, c_name, sum(l_extendedprice * (1 - l_discount)) as revenue, - c_acctbal, n_name, c_address, c_phone, c_comment -from - customer c join orders o - on - c.c_custkey = o.o_custkey and o.o_orderdate >= '1993-10-01' and o.o_orderdate < '1994-01-01' - join nation n - on - c.c_nationkey = n.n_nationkey - join lineitem l - on - l.l_orderkey = o.o_orderkey and l.l_returnflag = 'R' -group by c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment -order by revenue desc -limit 20; - -DROP TABLE lineitem; -DROP TABLE orders; -DROP TABLE customer; -DROP TABLE nation; -DROP TABLE q10_returned_item; http://git-wip-us.apache.org/repos/asf/incubator-asterixdb-hyracks/blob/ffc967fd/hivesterix/hivesterix-dist/resource/tpch100/q11_important_stock.hive ---------------------------------------------------------------------- diff --git a/hivesterix/hivesterix-dist/resource/tpch100/q11_important_stock.hive b/hivesterix/hivesterix-dist/resource/tpch100/q11_important_stock.hive deleted file mode 100644 index 271b614..0000000 --- a/hivesterix/hivesterix-dist/resource/tpch100/q11_important_stock.hive +++ /dev/null @@ -1,46 +0,0 @@ --- create tables and load data -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 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 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 the target table -create table q11_important_stock(ps_partkey INT, value DOUBLE); -create table q11_part_tmp(ps_partkey int, part_value double); -create table q11_sum_tmp(total_value double); - --- the query -insert overwrite table q11_part_tmp -select - ps_partkey, sum(ps_supplycost * ps_availqty) as part_value -from - nation n join supplier s - on - s.s_nationkey = n.n_nationkey and n.n_name = 'GERMANY' - join partsupp ps - on - ps.ps_suppkey = s.s_suppkey -group by ps_partkey; - -insert overwrite table q11_sum_tmp -select - sum(part_value) as total_value -from - q11_part_tmp; - -insert overwrite table q11_important_stock -select - ps_partkey, part_value as value -from - ( - select ps_partkey, part_value, total_value - from q11_part_tmp join q11_sum_tmp - ) a -where part_value > total_value * 0.0001 -order by value desc; - -DROP TABLE partsupp; -DROP TABLE supplier; -DROP TABLE nation; -DROP TABLE q11_important_stock; -DROP TABLE q11_part_tmp; -DROP TABLE q11_sum_tmp; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/incubator-asterixdb-hyracks/blob/ffc967fd/hivesterix/hivesterix-dist/resource/tpch100/q12_shipping.hive ---------------------------------------------------------------------- diff --git a/hivesterix/hivesterix-dist/resource/tpch100/q12_shipping.hive b/hivesterix/hivesterix-dist/resource/tpch100/q12_shipping.hive deleted file mode 100644 index cd5c8aa..0000000 --- a/hivesterix/hivesterix-dist/resource/tpch100/q12_shipping.hive +++ /dev/null @@ -1,42 +0,0 @@ --- create the tables and load the 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/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 the result table -create table q12_shipping(l_shipmode string, high_line_count double, low_line_count double); - -set mapred.min.split.size=536870912; -set hive.exec.reducers.bytes.per.reducer=1225000000; - --- the query -insert overwrite table q12_shipping -select - l_shipmode, - sum(case - when o_orderpriority ='1-URGENT' - or o_orderpriority ='2-HIGH' - then 1 - else 0 -end - ) as high_line_count, - sum(case - when o_orderpriority <> '1-URGENT' - and o_orderpriority <> '2-HIGH' - then 1 - else 0 -end - ) as low_line_count -from - orders o join lineitem l - on - o.o_orderkey = l.l_orderkey and l.l_commitdate < l.l_receiptdate -and l.l_shipdate < l.l_commitdate and l.l_receiptdate >= '1994-01-01' -and l.l_receiptdate < '1995-01-01' -where - l.l_shipmode = 'MAIL' or l.l_shipmode = 'SHIP' -group by l_shipmode -order by l_shipmode; - -DROP TABLE lineitem; -DROP TABLE orders; -DROP TABLE q12_shipping; http://git-wip-us.apache.org/repos/asf/incubator-asterixdb-hyracks/blob/ffc967fd/hivesterix/hivesterix-dist/resource/tpch100/q13_customer_distribution.hive ---------------------------------------------------------------------- diff --git a/hivesterix/hivesterix-dist/resource/tpch100/q13_customer_distribution.hive b/hivesterix/hivesterix-dist/resource/tpch100/q13_customer_distribution.hive deleted file mode 100644 index dc7f832..0000000 --- a/hivesterix/hivesterix-dist/resource/tpch100/q13_customer_distribution.hive +++ /dev/null @@ -1,26 +0,0 @@ --- create the tables and load the data -create external table customer (C_CUSTKEY INT, C_NAME STRING, C_ADDRESS STRING, C_NATIONKEY INT, C_PHONE STRING, C_ACCTBAL DOUBLE, C_MKTSEGMENT STRING, C_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/100/customer'; -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 the result table -create table q13_customer_distribution (c_count int, custdist int); - --- the query -insert overwrite table q13_customer_distribution -select - c_count, count(1) as custdist -from - (select - c_custkey, count(o_orderkey) as c_count - from - customer c left outer join orders o - on - c.c_custkey = o.o_custkey and not o.o_comment like '%special%requests%' - group by c_custkey - ) c_orders -group by c_count -order by custdist desc, c_count desc; - -DROP TABLE customer; -DROP TABLE orders; -DROP TABLE q13_customer_distribution; http://git-wip-us.apache.org/repos/asf/incubator-asterixdb-hyracks/blob/ffc967fd/hivesterix/hivesterix-dist/resource/tpch100/q14_promotion_effect.hive ---------------------------------------------------------------------- diff --git a/hivesterix/hivesterix-dist/resource/tpch100/q14_promotion_effect.hive b/hivesterix/hivesterix-dist/resource/tpch100/q14_promotion_effect.hive deleted file mode 100644 index ca6d6b2..0000000 --- a/hivesterix/hivesterix-dist/resource/tpch100/q14_promotion_effect.hive +++ /dev/null @@ -1,27 +0,0 @@ --- create the tables and load the 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/100/lineitem'; -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 the result table -create table q14_promotion_effect(promo_revenue double); - -set mapred.min.split.size=536870912; -set hive.exec.reducers.bytes.per.reducer=1040000000; - --- the query -insert overwrite table q14_promotion_effect -select - 100.00 * sum(case - when p_type like 'PROMO%' - then l_extendedprice*(1-l_discount) - else 0.0 - end - ) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue -from - part p join lineitem l - on - l.l_partkey = p.p_partkey and l.l_shipdate >= '1995-09-01' and l.l_shipdate < '1995-10-01'; - -DROP TABLE lineitem; -DROP TABLE part; -DROP TABLE q14_promotion_effect; http://git-wip-us.apache.org/repos/asf/incubator-asterixdb-hyracks/blob/ffc967fd/hivesterix/hivesterix-dist/resource/tpch100/q15_top_supplier.hive ---------------------------------------------------------------------- diff --git a/hivesterix/hivesterix-dist/resource/tpch100/q15_top_supplier.hive b/hivesterix/hivesterix-dist/resource/tpch100/q15_top_supplier.hive deleted file mode 100644 index d3d73c3..0000000 --- a/hivesterix/hivesterix-dist/resource/tpch100/q15_top_supplier.hive +++ /dev/null @@ -1,44 +0,0 @@ --- create the tables and load the 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/100/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/100/supplier'; - --- create result tables -create table revenue(supplier_no int, total_revenue double); -create table max_revenue(max_revenue double); -create table q15_top_supplier(s_suppkey int, s_name string, s_address string, s_phone string, total_revenue double); - - -set mapred.min.split.size=536870912; - --- the query -insert overwrite table revenue -select - l_suppkey as supplier_no, sum(l_extendedprice * (1 - l_discount)) as total_revenue -from - lineitem -where - l_shipdate >= '1996-01-01' and l_shipdate < '1996-04-01' -group by l_suppkey; - -insert overwrite table max_revenue -select - max(total_revenue) -from - revenue; - -insert overwrite table q15_top_supplier -select - s_suppkey, s_name, s_address, s_phone, total_revenue -from supplier s join revenue r - on - s.s_suppkey = r.supplier_no - join max_revenue m - on - r.total_revenue = m.max_revenue -order by s_suppkey; - -DROP TABLE lineitem; -DROP TABLE supplier; -DROP TABLE revenue; -DROP TABLE max_revenue; -DROP TABLE q15_top_supplier;