Return-Path: X-Original-To: archive-asf-public-internal@cust-asf2.ponee.io Delivered-To: archive-asf-public-internal@cust-asf2.ponee.io Received: from cust-asf.ponee.io (cust-asf.ponee.io [163.172.22.183]) by cust-asf2.ponee.io (Postfix) with ESMTP id 46FD7200C41 for ; Thu, 16 Feb 2017 21:53:09 +0100 (CET) Received: by cust-asf.ponee.io (Postfix) id 4439F160B52; Thu, 16 Feb 2017 20:53:09 +0000 (UTC) Delivered-To: archive-asf-public@cust-asf.ponee.io Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by cust-asf.ponee.io (Postfix) with SMTP id D61BD160B75 for ; Thu, 16 Feb 2017 21:53:06 +0100 (CET) Received: (qmail 46588 invoked by uid 500); 16 Feb 2017 20:53:06 -0000 Mailing-List: contact commits-help@asterixdb.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@asterixdb.apache.org Delivered-To: mailing list commits@asterixdb.apache.org Received: (qmail 46493 invoked by uid 99); 16 Feb 2017 20:53:06 -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; Thu, 16 Feb 2017 20:53:06 +0000 Received: by git1-us-west.apache.org (ASF Mail Server at git1-us-west.apache.org, from userid 33) id C0E49E006B; Thu, 16 Feb 2017 20:53:05 +0000 (UTC) Content-Type: text/plain; charset="us-ascii" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit From: buyingyi@apache.org To: commits@asterixdb.apache.org Date: Thu, 16 Feb 2017 20:53:09 -0000 Message-Id: <0402fa9588e8459e938a77f8f3d18a99@git.apache.org> In-Reply-To: <370fd1a4da014b3799c9165c24708900@git.apache.org> References: <370fd1a4da014b3799c9165c24708900@git.apache.org> X-Mailer: ASF-Git Admin Mailer Subject: [5/6] asterixdb git commit: Add cluster performance testing scripts. archived-at: Thu, 16 Feb 2017 20:53:09 -0000 http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/setup/create.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/setup/create.sqlpp b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/setup/create.sqlpp new file mode 100644 index 0000000..2aa67c6 --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/setup/create.sqlpp @@ -0,0 +1,125 @@ +/* + * Copyright by The Regents of the University of California + * Licensed under the Apache License, Version 2.0 (the "License"); + * you may not use this file except in compliance with the License. + * you may obtain a copy of the License from + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ + +drop dataverse tpch if exists; +create dataverse tpch; +use tpch; + +create type LineItemType as { + l_orderkey: int64, + l_partkey: int64, + l_suppkey: int64, + l_linenumber: int64, + l_quantity: int32, + 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 +} + +create type OrderType as { + o_orderkey: int64, + o_custkey: int64, + o_orderstatus: string, + o_totalprice: double, + o_orderdate: string, + o_orderpriority: string, + o_clerk: string, + o_shippriority: int32, + o_comment: string +} + +create type CustomerType as { + c_custkey: int64, + c_name: string, + c_address: string, + c_nationkey: int32, + c_phone: string, + c_acctbal: double, + c_mktsegment: string, + c_comment: string +} + +create type PartType as { + p_partkey: int64, + p_name: string, + p_mfgr: string, + p_brand: string, + p_type: string, + p_size: int32, + p_container: string, + p_retailprice: double, + p_comment: string +} + +create type PartSuppType as { + ps_partkey: int64, + ps_suppkey: int64, + ps_availqty: int32, + ps_supplycost: double, + ps_comment: string +} + +create type SupplierType as { + s_suppkey: int64, + s_name: string, + s_address: string, + s_nationkey: int32, + s_phone: string, + s_acctbal: double, + s_comment: string +} + +create type NationType as { + n_nationkey: int32, + n_name: string, + n_regionkey: int32, + n_comment: string +} + +create type RegionType as { + r_regionkey: int32, + r_name: string, + r_comment: string +} + +create dataset LineItem(LineItemType) primary key l_orderkey, l_linenumber; +create dataset Orders(OrderType) primary key o_orderkey; +create dataset Customer(CustomerType) primary key c_custkey; +create dataset Part(PartType) primary key p_partkey; +create dataset Partsupp(PartSuppType) primary key ps_partkey, ps_suppkey; +create dataset Supplier(SupplierType) primary key s_suppkey; +create dataset Region(RegionType) primary key r_regionkey; +create dataset Nation(NationType) primary key n_nationkey; + +create index nation_fk_region on Nation(n_regionkey); +create index supplier_fk_nation on Supplier (s_nationkey); +create index partsupp_fk_part on Partsupp (ps_partkey); +create index partsupp_fk_supplier on Partsupp (ps_suppkey); +create index customer_fk_nation on Customer (c_nationkey); +create index orders_fk_customer on Orders (o_custkey); +create index lineitem_fk_orders on LineItem (l_orderkey); +create index lineitem_fk_part on LineItem (l_partkey); +create index lineitem_fk_supplier on LineItem (l_suppkey); +create index orders_orderdateIx on Orders (o_orderdate); +create index lineitem_shipdateIx on LineItem (l_shipdate); +create index lineitem_receiptdateIx on LineItem (l_receiptdate); \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/setup/setup.yml ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/setup/setup.yml b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/setup/setup.yml new file mode 100644 index 0000000..9c21581 --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/setup/setup.yml @@ -0,0 +1,27 @@ +# ------------------------------------------------------------ +# Licensed to the Apache Software Foundation (ASF) under one +# or more contributor license agreements. See the NOTICE file +# distributed with this work for additional information +# regarding copyright ownership. The ASF licenses this file +# to you under the Apache License, Version 2.0 (the +# "License"); you may not use this file except in compliance +# with the License. You may obtain a copy of the License at +# +# http://www.apache.org/licenses/LICENSE-2.0 +# +# Unless required by applicable law or agreed to in writing, +# software distributed under the License is distributed on an +# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +# KIND, either express or implied. See the License for the +# specific language governing permissions and limitations +# under the License. +# ------------------------------------------------------------ + +- hosts: [localhost,] + vars: + runquery: ../../../ansible/runquery.yml + ddl: ../benchmarks/tpch/setup/create.sqlpp + tasks: + - name: Create all datasets + shell: "ansible-playbook -i {{ inventory }} {{ runquery }} --extra-vars=\"query_file={{ ddl }} report=false\"" + http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/teardown/drop.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/teardown/drop.sqlpp b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/teardown/drop.sqlpp new file mode 100644 index 0000000..45d2efa --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/teardown/drop.sqlpp @@ -0,0 +1,16 @@ +/* + * Copyright by The Regents of the University of California + * Licensed under the Apache License, Version 2.0 (the "License"); + * you may not use this file except in compliance with the License. + * you may obtain a copy of the License from + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ + +drop dataverse tpch if exists; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/teardown/teardown.yml ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/teardown/teardown.yml b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/teardown/teardown.yml new file mode 100644 index 0000000..10e6643 --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/teardown/teardown.yml @@ -0,0 +1,27 @@ +# ------------------------------------------------------------ +# Licensed to the Apache Software Foundation (ASF) under one +# or more contributor license agreements. See the NOTICE file +# distributed with this work for additional information +# regarding copyright ownership. The ASF licenses this file +# to you under the Apache License, Version 2.0 (the +# "License"); you may not use this file except in compliance +# with the License. You may obtain a copy of the License at +# +# http://www.apache.org/licenses/LICENSE-2.0 +# +# Unless required by applicable law or agreed to in writing, +# software distributed under the License is distributed on an +# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +# KIND, either express or implied. See the License for the +# specific language governing permissions and limitations +# under the License. +# ------------------------------------------------------------ + +- hosts: [localhost,] + vars: + runquery: ../../../ansible/runquery.yml + ddl: ../benchmarks/tpch/teardown/drop.sqlpp + tasks: + - name: Drop all datasets + shell: "ansible-playbook -i {{ inventory }} {{ runquery }} --extra-vars=\"query_file={{ ddl }} report=false\"" + http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/bin/runall.sh ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmark/src/main/resources/bin/runall.sh b/asterixdb/asterix-benchmark/src/main/resources/bin/runall.sh new file mode 100755 index 0000000..1cd0f0b --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/bin/runall.sh @@ -0,0 +1,79 @@ +#!/bin/bash +# ------------------------------------------------------------ +# Licensed to the Apache Software Foundation (ASF) under one +# or more contributor license agreements. See the NOTICE file +# distributed with this work for additional information +# regarding copyright ownership. The ASF licenses this file +# to you under the Apache License, Version 2.0 (the +# "License"); you may not use this file except in compliance +# with the License. You may obtain a copy of the License at +# +# http://www.apache.org/licenses/LICENSE-2.0 +# +# Unless required by applicable law or agreed to in writing, +# software distributed under the License is distributed on an +# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +# KIND, either express or implied. See the License for the +# specific language governing permissions and limitations +# under the License. +# ------------------------------------------------------------ + +# Gets the absolute path so that the script can work no matter where it is invoked. +pushd `dirname $0` > /dev/null +SCRIPT_PATH=`pwd -P` +popd > /dev/null + +# Gets the perf. distribution path. +DIST_PATH=`dirname "${SCRIPT_PATH}"` + +# Gets the benchmark path. +BENCHMARK_PATH=$DIST_PATH/benchmarks + +# Gets the inventory file path. +INVENTORY=$1 + +# Checks the existence of the inventory file. +if [ ! -f "$INVENTORY" ]; +then + echo "The inventory file \"$INVENTORY\" does not exist." + exit 1 +fi + +ANSIBLE_PATH=$DIST_PATH/ansible + +# Enumerates all benchmarks and run each of them. +for benchmark in $BENCHMARK_PATH/*; do + # Sets up paths for scripts. + gen=$benchmark/gen + setup=$benchmark/setup + load=$benchmark/load + queries=$benchmark/queries + teardown=$benchmark/teardown + + # Creates datasets that are used for testing. + ansible-playbook -i $INVENTORY --extra-vars="inventory=${INVENTORY}" $setup/setup.yml + + # Generates data distributedly on all machines + ansible-playbook -i $INVENTORY $gen/gen.yml + + # Loads generated data into those created datasets. + ansible-playbook -i $INVENTORY --extra-vars="inventory=${INVENTORY}" $load/load.yml + + # Runs queries for heating the system. + for query in $queries/*.sqlpp; do + ansible-playbook -i $INVENTORY --extra-vars="query_file=${query} report=false" \ + $ANSIBLE_PATH/runquery.yml + done + + # Runs all queries three times and collect numbers. + for number in 1 2 3 + do + for query in $queries/*.sqlpp; do + ansible-playbook -i $INVENTORY --extra-vars="query_file=${query} report=true metric=\"AsterixDB\"" \ + $ANSIBLE_PATH/runquery.yml + done + done + + # Removes all loaded datasets. + ansible-playbook -i $INVENTORY --extra-vars="inventory=${INVENTORY}" $teardown/teardown.yml +done http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/conf/benchmark_setting.yml ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmark/src/main/resources/conf/benchmark_setting.yml b/asterixdb/asterix-benchmark/src/main/resources/conf/benchmark_setting.yml new file mode 100644 index 0000000..49d169f --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/conf/benchmark_setting.yml @@ -0,0 +1,33 @@ +# ------------------------------------------------------------ +# Licensed to the Apache Software Foundation (ASF) under one +# or more contributor license agreements. See the NOTICE file +# distributed with this work for additional information +# regarding copyright ownership. The ASF licenses this file +# to you under the Apache License, Version 2.0 (the +# "License"); you may not use this file except in compliance +# with the License. You may obtain a copy of the License at +# +# http://www.apache.org/licenses/LICENSE-2.0 +# +# Unless required by applicable law or agreed to in writing, +# software distributed under the License is distributed on an +# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +# KIND, either express or implied. See the License for the +# specific language governing permissions and limitations +# under the License. +# ------------------------------------------------------------ + +# scale = (dataset size)/(aggregated RAM) +scale: 1.0 + +# performance testing directory. +perfdir: "perf" + +# service endpoint +service_endpoint: "query/service" + +# service port +service_port: 19002 + +# The url for reporting results. +result_url: http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q01_pricing_summary_report_nt.3.query.aql ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q01_pricing_summary_report_nt.3.query.aql b/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q01_pricing_summary_report_nt.3.query.aql deleted file mode 100644 index 18e9348..0000000 --- a/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q01_pricing_summary_report_nt.3.query.aql +++ /dev/null @@ -1,40 +0,0 @@ -/* - * Licensed to the Apache Software Foundation (ASF) under one - * or more contributor license agreements. See the NOTICE file - * distributed with this work for additional information - * regarding copyright ownership. The ASF licenses this file - * to you under the Apache License, Version 2.0 (the - * "License"); you may not use this file except in compliance - * with the License. You may obtain a copy of the License at - * - * http://www.apache.org/licenses/LICENSE-2.0 - * - * Unless required by applicable law or agreed to in writing, - * software distributed under the License is distributed on an - * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY - * KIND, either express or implied. See the License for the - * specific language governing permissions and limitations - * under the License. - */ -use dataverse tpch; -set import-private-functions 'true'; - -for $l in dataset('LineItem') -where $l.l_shipdate <= '1998-09-02' -/*+ hash*/ -group by $l_returnflag := $l.l_returnflag, - $l_linestatus := $l.l_linestatus - with $l -order by $l_returnflag, $l_linestatus -return { - "l_returnflag": $l_returnflag, - "l_linestatus": $l_linestatus, - "sum_qty": sum(for $i in $l return $i.l_quantity), - "sum_base_price": sum(for $i in $l return $i.l_extendedprice), - "sum_disc_price": sum(for $i in $l return $i.l_extendedprice * (1 - $i.l_discount)), - "sum_charge": sum(for $i in $l return $i.l_extendedprice * (1 - $i.l_discount) * (1 + $i.l_tax)), - "ave_qty": avg(for $i in $l return $i.l_quantity), - "ave_price": avg(for $i in $l return $i.l_extendedprice), - "ave_disc": avg(for $i in $l return $i.l_discount), - "count_order": count($l) -} http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q02_minimum_cost_supplier.3.query.aql ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q02_minimum_cost_supplier.3.query.aql b/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q02_minimum_cost_supplier.3.query.aql deleted file mode 100644 index a42a0af..0000000 --- a/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q02_minimum_cost_supplier.3.query.aql +++ /dev/null @@ -1,135 +0,0 @@ -/* - * Licensed to the Apache Software Foundation (ASF) under one - * or more contributor license agreements. See the NOTICE file - * distributed with this work for additional information - * regarding copyright ownership. The ASF licenses this file - * to you under the Apache License, Version 2.0 (the - * "License"); you may not use this file except in compliance - * with the License. You may obtain a copy of the License at - * - * http://www.apache.org/licenses/LICENSE-2.0 - * - * Unless required by applicable law or agreed to in writing, - * software distributed under the License is distributed on an - * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY - * KIND, either express or implied. See the License for the - * specific language governing permissions and limitations - * under the License. - */ -use dataverse tpch; - -declare function tmp1() { - for $p in dataset('Part') - for $pssrn in ( - for $ps in dataset('Partsupp') - for $srn in ( - for $s in dataset('Supplier') - for $rn in ( - for $r in dataset('Region') - for $n in dataset('Nation') - where $n.n_regionkey = $r.r_regionkey and $r.r_name = 'EUROPE' - return { - "n_nationkey": $n.n_nationkey, - "n_name": $n.n_name - } - ) - where $s.s_nationkey = $rn.n_nationkey - return { - "s_suppkey": $s.s_suppkey, - "n_name": $rn.n_name, - "s_name": $s.s_name, - "s_acctbal": $s.s_acctbal, - "s_address": $s.s_address, - "s_phone": $s.s_phone, - "s_comment": $s.s_comment - } - ) - where $srn.s_suppkey = $ps.ps_suppkey - return { - "n_name": $srn.n_name, - "p_partkey": $ps.ps_partkey, - "ps_supplycost": $ps.ps_supplycost, - "s_name": $srn.s_name, - "s_acctbal": $srn.s_acctbal, - "s_address": $srn.s_address, - "s_phone": $srn.s_phone, - "s_comment": $srn.s_comment - } - ) - where $p.p_partkey = $pssrn.p_partkey and like($p.p_type, '%BRASS') and $p.p_size = 15 - return { - "s_acctbal": $pssrn.s_acctbal, - "s_name": $pssrn.s_name, - "n_name": $pssrn.n_name, - "p_partkey": $p.p_partkey, - "ps_supplycost": $pssrn.ps_supplycost, - "p_mfgr": $p.p_mfgr, - "s_address": $pssrn.s_address, - "s_phone": $pssrn.s_phone, - "s_comment": $pssrn.s_comment - } -} - -declare function tmp2(){ - for $p in dataset('Part') - for $pssrn in ( - for $ps in dataset('Partsupp') - for $srn in ( - for $s in dataset('Supplier') - for $rn in ( - for $r in dataset('Region') - for $n in dataset('Nation') - where $n.n_regionkey = $r.r_regionkey and $r.r_name = 'EUROPE' - return { - "n_nationkey": $n.n_nationkey, - "n_name": $n.n_name - } - ) - where $s.s_nationkey = $rn.n_nationkey - return { - "s_suppkey": $s.s_suppkey, - "n_name": $rn.n_name, - "s_name": $s.s_name, - "s_acctbal": $s.s_acctbal, - "s_address": $s.s_address, - "s_phone": $s.s_phone, - "s_comment": $s.s_comment - } - ) - where $srn.s_suppkey = $ps.ps_suppkey - return { - "n_name": $srn.n_name, - "p_partkey": $ps.ps_partkey, - "ps_supplycost": $ps.ps_supplycost, - "s_name": $srn.s_name, - "s_acctbal": $srn.s_acctbal, - "s_address": $srn.s_address, - "s_phone": $srn.s_phone, - "s_comment": $srn.s_comment - } - ) - where $p.p_partkey = $pssrn.p_partkey and like($p.p_type, '%BRASS') and $p.p_size = 15 - /*+ hash*/ - group by $p_partkey := $pssrn.p_partkey with $pssrn - return { - "p_partkey": $p_partkey, - "ps_min_supplycost": min(for $i in $pssrn return $i.ps_supplycost) - } -} - -for $t2 in tmp2() -for $t1 in tmp1() -where $t1.p_partkey = $t2.p_partkey and $t1.ps_supplycost = $t2.ps_min_supplycost -order by $t1.s_acctbal desc, $t1.n_name, $t1.s_name, $t1.p_partkey -limit 100 -return -{ - "s_acctbal": $t1.s_acctbal, - "s_name": $t1.s_name, - "n_name": $t1.n_name, - "p_partkey": $t1.p_partkey, - "p_mfgr": $t1.p_mfgr, - "s_address": $t1.s_address, - "s_phone": $t1.s_phone, - "s_comment": $t1.s_comment -} http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q03_shipping_priority_nt.3.query.aql ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q03_shipping_priority_nt.3.query.aql b/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q03_shipping_priority_nt.3.query.aql deleted file mode 100644 index 8780b3a..0000000 --- a/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q03_shipping_priority_nt.3.query.aql +++ /dev/null @@ -1,44 +0,0 @@ -/* - * Licensed to the Apache Software Foundation (ASF) under one - * or more contributor license agreements. See the NOTICE file - * distributed with this work for additional information - * regarding copyright ownership. The ASF licenses this file - * to you under the Apache License, Version 2.0 (the - * "License"); you may not use this file except in compliance - * with the License. You may obtain a copy of the License at - * - * http://www.apache.org/licenses/LICENSE-2.0 - * - * Unless required by applicable law or agreed to in writing, - * software distributed under the License is distributed on an - * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY - * KIND, either express or implied. See the License for the - * specific language governing permissions and limitations - * under the License. - */ -use dataverse tpch; - -for $c in dataset('Customer') -for $o in dataset('Orders') -where - $c.c_mktsegment = 'BUILDING' and $c.c_custkey = $o.o_custkey -for $l in dataset('LineItem') -where - $l.l_orderkey = $o.o_orderkey and - $o.o_orderdate < '1995-03-15' and $l.l_shipdate > '1995-03-15' -/*+ hash*/ -group by $l_orderkey := $l.l_orderkey, $o_orderdate := $o.o_orderdate, $o_shippriority := $o.o_shippriority - with $l -let $revenue := sum ( - for $i in $l - return - $i.l_extendedprice * (1 - $i.l_discount) -) -order by $revenue desc, $o_orderdate -limit 10 -return { - "l_orderkey": $l_orderkey, - "revenue": $revenue, - "o_orderdate": $o_orderdate, - "o_shippriority": $o_shippriority -} http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q04_order_priority.3.query.aql ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q04_order_priority.3.query.aql b/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q04_order_priority.3.query.aql deleted file mode 100644 index 2459659..0000000 --- a/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q04_order_priority.3.query.aql +++ /dev/null @@ -1,39 +0,0 @@ -/* - * Licensed to the Apache Software Foundation (ASF) under one - * or more contributor license agreements. See the NOTICE file - * distributed with this work for additional information - * regarding copyright ownership. The ASF licenses this file - * to you under the Apache License, Version 2.0 (the - * "License"); you may not use this file except in compliance - * with the License. You may obtain a copy of the License at - * - * http://www.apache.org/licenses/LICENSE-2.0 - * - * Unless required by applicable law or agreed to in writing, - * software distributed under the License is distributed on an - * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY - * KIND, either express or implied. See the License for the - * specific language governing permissions and limitations - * under the License. - */ -use dataverse tpch; - -declare function tmp() -{ - for $l in dataset('LineItem') - where $l.l_commitdate < $l.l_receiptdate - distinct by $l.l_orderkey - return { "o_orderkey": $l.l_orderkey } -} - -for $o in dataset('Orders') -for $t in tmp() -where $o.o_orderkey = $t.o_orderkey and - $o.o_orderdate >= '1993-07-01' and $o.o_orderdate < '1993-10-01' -group by $o_orderpriority := $o.o_orderpriority with $o -order by $o_orderpriority -return { - "order_priority": $o_orderpriority, - "count": count($o) -} - http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q05_local_supplier_volume.3.query.aql ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q05_local_supplier_volume.3.query.aql b/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q05_local_supplier_volume.3.query.aql deleted file mode 100644 index 07d1c88..0000000 --- a/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q05_local_supplier_volume.3.query.aql +++ /dev/null @@ -1,75 +0,0 @@ -/* - * Licensed to the Apache Software Foundation (ASF) under one - * or more contributor license agreements. See the NOTICE file - * distributed with this work for additional information - * regarding copyright ownership. The ASF licenses this file - * to you under the Apache License, Version 2.0 (the - * "License"); you may not use this file except in compliance - * with the License. You may obtain a copy of the License at - * - * http://www.apache.org/licenses/LICENSE-2.0 - * - * Unless required by applicable law or agreed to in writing, - * software distributed under the License is distributed on an - * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY - * KIND, either express or implied. See the License for the - * specific language governing permissions and limitations - * under the License. - */ -use dataverse tpch; - -for $c in dataset('Customer') -for $o1 in ( - for $o in dataset('Orders') - for $l1 in ( - for $l in dataset('LineItem') - for $s1 in ( - for $s in dataset('Supplier') - for $n1 in ( - for $n in dataset('Nation') - for $r in dataset('Region') - where $n.n_regionkey = $r.r_regionkey - and $r.r_name = 'ASIA' - return { - "n_name": $n.n_name, - "n_nationkey": $n.n_nationkey - } - ) - where $s.s_nationkey = $n1.n_nationkey - return { - "n_name": $n1.n_name, - "s_suppkey": $s.s_suppkey, - "s_nationkey": $s.s_nationkey - } - ) - where $l.l_suppkey = $s1.s_suppkey - return { - "n_name": $s1.n_name, - "l_extendedprice": $l.l_extendedprice, - "l_discount": $l.l_discount, - "l_orderkey": $l.l_orderkey, - "s_nationkey": $s1.s_nationkey - } - ) - where $l1.l_orderkey = $o.o_orderkey and $o.o_orderdate >= '1994-01-01' and $o.o_orderdate < '1995-01-01' - return { - "n_name": $l1.n_name, - "l_extendedprice": $l1.l_extendedprice, - "l_discount": $l1.l_discount, - "s_nationkey": $l1.s_nationkey, - "o_custkey": $o.o_custkey - } -) -where $c.c_nationkey = $o1.s_nationkey and $c.c_custkey = $o1.o_custkey -/*+ hash*/ -group by $n_name := $o1.n_name with $o1 -let $revenue := sum ( - for $i in $o1 - return - $i.l_extendedprice * (1 - $i.l_discount) -) -order by $revenue desc -return { - "n_name": $n_name, - "revenue": $revenue -} http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q06_forecast_revenue_change.3.query.aql ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q06_forecast_revenue_change.3.query.aql b/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q06_forecast_revenue_change.3.query.aql deleted file mode 100644 index 1fb3e55..0000000 --- a/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q06_forecast_revenue_change.3.query.aql +++ /dev/null @@ -1,31 +0,0 @@ -/* - * Licensed to the Apache Software Foundation (ASF) under one - * or more contributor license agreements. See the NOTICE file - * distributed with this work for additional information - * regarding copyright ownership. The ASF licenses this file - * to you under the Apache License, Version 2.0 (the - * "License"); you may not use this file except in compliance - * with the License. You may obtain a copy of the License at - * - * http://www.apache.org/licenses/LICENSE-2.0 - * - * Unless required by applicable law or agreed to in writing, - * software distributed under the License is distributed on an - * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY - * KIND, either express or implied. See the License for the - * specific language governing permissions and limitations - * under the License. - */ -use dataverse tpch; - -let $revenue := sum( - for $l in dataset('LineItem') - where $l.l_shipdate >= '1994-01-01' - and $l.l_shipdate < '1995-01-01' - and $l.l_discount >= 0.05 and $l.l_discount <= 0.07 - and $l.l_quantity < 24 - return $l.l_extendedprice * $l.l_discount -) -return { - "revenue": $revenue -} http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q07_volume_shipping.3.query.aql ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q07_volume_shipping.3.query.aql b/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q07_volume_shipping.3.query.aql deleted file mode 100644 index a118a10..0000000 --- a/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q07_volume_shipping.3.query.aql +++ /dev/null @@ -1,81 +0,0 @@ -/* - * Licensed to the Apache Software Foundation (ASF) under one - * or more contributor license agreements. See the NOTICE file - * distributed with this work for additional information - * regarding copyright ownership. The ASF licenses this file - * to you under the Apache License, Version 2.0 (the - * "License"); you may not use this file except in compliance - * with the License. You may obtain a copy of the License at - * - * http://www.apache.org/licenses/LICENSE-2.0 - * - * Unless required by applicable law or agreed to in writing, - * software distributed under the License is distributed on an - * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY - * KIND, either express or implied. See the License for the - * specific language governing permissions and limitations - * under the License. - */ -use dataverse tpch; - -declare function q7_volume_shipping_tmp() { - for $n1 in dataset('Nation') - for $n2 in dataset('Nation') - where ($n1.n_name='FRANCE' and $n2.n_name='GERMANY') or ($n1.n_name='GERMANY' and $n2.n_name='FRANCE') - return { - "supp_nation": $n1.n_name, - "cust_nation": $n2.n_name, - "s_nationkey": $n1.n_nationkey, - "c_nationkey": $n2.n_nationkey - } -} - -for $locs in ( - for $loc in ( - for $lo in ( - for $l in dataset('LineItem') - for $o in dataset('Orders') - where $o.o_orderkey = $l.l_orderkey and $l.l_shipdate >= '1995-01-01' - and $l.l_shipdate <= '1996-12-31' - return { - "l_shipdate": $l.l_shipdate, - "l_extendedprice": $l.l_extendedprice, - "l_discount": $l.l_discount, - "l_suppkey": $l.l_suppkey, - "o_custkey": $o.o_custkey - } - ) - for $c in dataset('Customer') - where $c.c_custkey = $lo.o_custkey - return { - "l_shipdate": $lo.l_shipdate, - "l_extendedprice": $lo.l_extendedprice, - "l_discount": $lo.l_discount, - "l_suppkey": $lo.l_suppkey, - "c_nationkey": $c.c_nationkey - } - ) - for $s in dataset('Supplier') - where $s.s_suppkey = $loc.l_suppkey - return { - "l_shipdate": $loc.l_shipdate, - "l_extendedprice": $loc.l_extendedprice, - "l_discount": $loc.l_discount, - "c_nationkey": $loc.c_nationkey, - "s_nationkey": $s.s_nationkey - } -) -for $t in q7_volume_shipping_tmp() -where $locs.c_nationkey = $t.c_nationkey - and $locs.s_nationkey = $t.s_nationkey -let $l_year0 := get-year($locs.l_shipdate) -group by $supp_nation := $t.supp_nation, $cust_nation := $t.cust_nation, $l_year := $l_year0 -with $locs -let $revenue := sum(for $i in $locs return $i.l_extendedprice * (1 - $i.l_discount)) -order by $supp_nation, $cust_nation, $l_year -return { - "supp_nation": $supp_nation, - "cust_nation": $cust_nation, - "l_year": $l_year, - "revenue": $revenue -} http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q08_national_market_share.3.query.aql ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q08_national_market_share.3.query.aql b/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q08_national_market_share.3.query.aql deleted file mode 100644 index cea12dd..0000000 --- a/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q08_national_market_share.3.query.aql +++ /dev/null @@ -1,91 +0,0 @@ -/* - * Licensed to the Apache Software Foundation (ASF) under one - * or more contributor license agreements. See the NOTICE file - * distributed with this work for additional information - * regarding copyright ownership. The ASF licenses this file - * to you under the Apache License, Version 2.0 (the - * "License"); you may not use this file except in compliance - * with the License. You may obtain a copy of the License at - * - * http://www.apache.org/licenses/LICENSE-2.0 - * - * Unless required by applicable law or agreed to in writing, - * software distributed under the License is distributed on an - * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY - * KIND, either express or implied. See the License for the - * specific language governing permissions and limitations - * under the License. - */ -use dataverse tpch; - -for $t in ( - for $slnrcop in ( - for $s in dataset("Supplier") - for $lnrcop in ( - for $lnrco in ( - for $l in dataset('LineItem') - for $nrco in ( - for $o in dataset('Orders') - for $nrc in ( - for $c in dataset('Customer') - for $nr in ( - for $n1 in dataset('Nation') - for $r1 in dataset('Region') - where $n1.n_regionkey = $r1.r_regionkey and $r1.r_name = 'AMERICA' - return { "n_nationkey": $n1.n_nationkey } - ) - where $c.c_nationkey = $nr.n_nationkey - return { "c_custkey": $c.c_custkey } - ) - where $nrc.c_custkey = $o.o_custkey - return { - "o_orderdate" : $o.o_orderdate, - "o_orderkey": $o.o_orderkey - } - ) - where $l.l_orderkey = $nrco.o_orderkey - and $nrco.o_orderdate >= '1995-01-01' - and $nrco.o_orderdate <= '1996-12-31' - return { - "o_orderdate": $nrco.o_orderdate, - "l_partkey": $l.l_partkey, - "l_discount": $l.l_discount, - "l_extendedprice": $l.l_extendedprice, - "l_suppkey": $l.l_suppkey - } - ) - for $p in dataset("Part") - where $p.p_partkey = $lnrco.l_partkey and $p.p_type = 'ECONOMY ANODIZED STEEL' - return { - "o_orderdate": $lnrco.o_orderdate, - "l_discount": $lnrco.l_discount, - "l_extendedprice": $lnrco.l_extendedprice, - "l_suppkey": $lnrco.l_suppkey - } - ) - where $s.s_suppkey = $lnrcop.l_suppkey - return { - "o_orderdate": $lnrcop.o_orderdate, - "l_discount": $lnrcop.l_discount, - "l_extendedprice": $lnrcop.l_extendedprice, - "l_suppkey": $lnrcop.l_suppkey, - "s_nationkey": $s.s_nationkey - } - ) - for $n2 in dataset('Nation') - where $slnrcop.s_nationkey = $n2.n_nationkey - let $o_year := get-year($slnrcop.o_orderdate) - return { - "year": $o_year, - "revenue": $slnrcop.l_extendedprice *(1-$slnrcop.l_discount), - "s_name": $n2.n_name - } -) -group by $year := $t.year with $t -order by $year -return { - "year": $year, - "mkt_share": sum(for $i in $t return switch-case($i.s_name='BRAZIL', true, $i.revenue, false, 0.0))/ - sum(for $i in $t return $i.revenue) -} - http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q09_product_type_profit_nt.3.query.aql ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q09_product_type_profit_nt.3.query.aql b/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q09_product_type_profit_nt.3.query.aql deleted file mode 100644 index 8bee998..0000000 --- a/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q09_product_type_profit_nt.3.query.aql +++ /dev/null @@ -1,85 +0,0 @@ -/* - * Licensed to the Apache Software Foundation (ASF) under one - * or more contributor license agreements. See the NOTICE file - * distributed with this work for additional information - * regarding copyright ownership. The ASF licenses this file - * to you under the Apache License, Version 2.0 (the - * "License"); you may not use this file except in compliance - * with the License. You may obtain a copy of the License at - * - * http://www.apache.org/licenses/LICENSE-2.0 - * - * Unless required by applicable law or agreed to in writing, - * software distributed under the License is distributed on an - * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY - * KIND, either express or implied. See the License for the - * specific language governing permissions and limitations - * under the License. - */ -use dataverse tpch; - -for $profit in ( - for $o in dataset('Orders') - for $l3 in ( - for $p in dataset('Part') - for $l2 in ( - for $ps in dataset('Partsupp') - for $l1 in ( - for $s1 in ( - for $s in dataset('Supplier') - for $n in dataset('Nation') - where $n.n_nationkey = $s.s_nationkey - return { - "s_suppkey": $s.s_suppkey, - "n_name": $n.n_name - } - ) - for $l in dataset('LineItem') - where $s1.s_suppkey = $l.l_suppkey - return { - "l_suppkey": $l.l_suppkey, - "l_extendedprice": $l.l_extendedprice, - "l_discount": $l.l_discount, - "l_quantity": $l.l_quantity, - "l_partkey": $l.l_partkey, - "l_orderkey": $l.l_orderkey, - "n_name": $s1.n_name - } - ) - where $ps.ps_suppkey = $l1.l_suppkey and $ps.ps_partkey = $l1.l_partkey - return { - "l_extendedprice": $l1.l_extendedprice, - "l_discount": $l1.l_discount, - "l_quantity": $l1.l_quantity, - "l_partkey": $l1.l_partkey, - "l_orderkey": $l1.l_orderkey, - "n_name": $l1.n_name, - "ps_supplycost": $ps.ps_supplycost - } - ) - where contains($p.p_name, 'green') and $p.p_partkey = $l2.l_partkey - return { - "l_extendedprice": $l2.l_extendedprice, - "l_discount": $l2.l_discount, - "l_quantity": $l2.l_quantity, - "l_orderkey": $l2.l_orderkey, - "n_name": $l2.n_name, - "ps_supplycost": $l2.ps_supplycost - } - ) - where $o.o_orderkey = $l3.l_orderkey - let $amount := $l3.l_extendedprice * (1 - $l3.l_discount) - $l3.ps_supplycost * $l3.l_quantity - let $o_year := get-year($o.o_orderdate) - return { - "nation": $l3.n_name, - "o_year": $o_year, - "amount": $amount - } -) -group by $nation := $profit.nation, $o_year := $profit.o_year with $profit -order by $nation, $o_year desc -return { - "nation": $nation, - "o_year": $o_year, - "sum_profit": sum( for $pr in $profit return $pr.amount ) -} http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q10_returned_item.3.query.aql ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q10_returned_item.3.query.aql b/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q10_returned_item.3.query.aql deleted file mode 100644 index ede220f..0000000 --- a/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q10_returned_item.3.query.aql +++ /dev/null @@ -1,73 +0,0 @@ -/* - * Licensed to the Apache Software Foundation (ASF) under one - * or more contributor license agreements. See the NOTICE file - * distributed with this work for additional information - * regarding copyright ownership. The ASF licenses this file - * to you under the Apache License, Version 2.0 (the - * "License"); you may not use this file except in compliance - * with the License. You may obtain a copy of the License at - * - * http://www.apache.org/licenses/LICENSE-2.0 - * - * Unless required by applicable law or agreed to in writing, - * software distributed under the License is distributed on an - * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY - * KIND, either express or implied. See the License for the - * specific language governing permissions and limitations - * under the License. - */ -use dataverse tpch; - -for $locn in ( - for $l in dataset('LineItem') - for $ocn in ( - for $o in dataset('Orders') - for $c in dataset('Customer') - where $c.c_custkey = $o.o_custkey and $o.o_orderdate >= '1993-10-01' - and $o.o_orderdate < '1994-01-01' - for $n in dataset('Nation') - where $c.c_nationkey = $n.n_nationkey - return { - "c_custkey": $c.c_custkey, - "c_name": $c.c_name, - "c_acctbal": $c.c_acctbal, - "n_name": $n.n_name, - "c_address": $c.c_address, - "c_phone": $c.c_phone, - "c_comment": $c.c_comment, - "o_orderkey": $o.o_orderkey - } - ) - where $l.l_orderkey = $ocn.o_orderkey and $l.l_returnflag = 'R' - return { - "c_custkey": $ocn.c_custkey, - "c_name": $ocn.c_name, - "c_acctbal": $ocn.c_acctbal, - "n_name": $ocn.n_name, - "c_address": $ocn.c_address, - "c_phone": $ocn.c_phone, - "c_comment": $ocn.c_comment, - "l_extendedprice": $l.l_extendedprice, - "l_discount": $l.l_discount - } -) -group by $c_custkey:=$locn.c_custkey, - $c_name:=$locn.c_name, - $c_acctbal:=$locn.c_acctbal, $c_phone:=$locn.c_phone, - $n_name:=$locn.n_name, $c_address:=$locn.c_address, $c_comment:=$locn.c_comment - with $locn -let $revenue := sum(for $i in $locn return $i.l_extendedprice * (1 - $i.l_discount)) -order by $revenue desc -limit 20 -return { - "c_custkey": $c_custkey, - "c_name": $c_name, - "revenue": $revenue, - "c_acctbal": $c_acctbal, - "n_name": $n_name, - "c_address": $c_address, - "c_phone": $c_phone, - "c_comment": $c_comment -} - - http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q11_important_stock.3.query.aql ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q11_important_stock.3.query.aql b/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q11_important_stock.3.query.aql deleted file mode 100644 index d880e03..0000000 --- a/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q11_important_stock.3.query.aql +++ /dev/null @@ -1,54 +0,0 @@ -/* - * Licensed to the Apache Software Foundation (ASF) under one - * or more contributor license agreements. See the NOTICE file - * distributed with this work for additional information - * regarding copyright ownership. The ASF licenses this file - * to you under the Apache License, Version 2.0 (the - * "License"); you may not use this file except in compliance - * with the License. You may obtain a copy of the License at - * - * http://www.apache.org/licenses/LICENSE-2.0 - * - * Unless required by applicable law or agreed to in writing, - * software distributed under the License is distributed on an - * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY - * KIND, either express or implied. See the License for the - * specific language governing permissions and limitations - * under the License. - */ -use dataverse tpch; - -let $sum := sum ( - for $ps in dataset('Partsupp') - for $sn in ( - for $s in dataset('Supplier') - for $n in dataset('Nation') - where $s.s_nationkey = $n.n_nationkey - and $n.n_name = 'GERMANY' - return { "s_suppkey": $s.s_suppkey } - ) - where $ps.ps_suppkey = $sn.s_suppkey - return $ps.ps_supplycost * $ps.ps_availqty -) -for $t1 in ( - for $ps in dataset('Partsupp') - for $sn in ( - for $s in dataset('Supplier') - for $n in dataset('Nation') - where $s.s_nationkey = $n.n_nationkey - and $n.n_name = 'GERMANY' - return { "s_suppkey": $s.s_suppkey } - ) - where $ps.ps_suppkey = $sn.s_suppkey - group by $ps_partkey := $ps.ps_partkey with $ps - return { - "ps_partkey": $ps_partkey, - "part_value": sum(for $i in $ps return $i.ps_supplycost * $i.ps_availqty) - } -) -where $t1.part_value > $sum * 0.0001 -order by $t1.part_value desc -return { - "partkey": $t1.ps_partkey, - "part_value": $t1.part_value -} http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q12_shipping.3.query.aql ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q12_shipping.3.query.aql b/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q12_shipping.3.query.aql deleted file mode 100644 index 35d1a99..0000000 --- a/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q12_shipping.3.query.aql +++ /dev/null @@ -1,45 +0,0 @@ -/* - * Licensed to the Apache Software Foundation (ASF) under one - * or more contributor license agreements. See the NOTICE file - * distributed with this work for additional information - * regarding copyright ownership. The ASF licenses this file - * to you under the Apache License, Version 2.0 (the - * "License"); you may not use this file except in compliance - * with the License. You may obtain a copy of the License at - * - * http://www.apache.org/licenses/LICENSE-2.0 - * - * Unless required by applicable law or agreed to in writing, - * software distributed under the License is distributed on an - * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY - * KIND, either express or implied. See the License for the - * specific language governing permissions and limitations - * under the License. - */ -use dataverse tpch; - -for $l in dataset('LineItem') -for $o in dataset('Orders') -where $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' - and ($l.l_shipmode = 'MAIL' or $l.l_shipmode = 'SHIP') -group by $l_shipmode := $l.l_shipmode with $o -order by $l_shipmode -return { - "l_shipmode": $l_shipmode, - "high_line_count": sum( - for $i in $o - return - switch-case($i.o_orderpriority ='1-URGENT' or $i.o_orderpriority ='2-HIGH', - true, 1, false, 0) - ), - "low_line_count": sum( - for $i in $o - return switch-case($i.o_orderpriority ='1-URGENT' or $i.o_orderpriority ='2-HIGH', - true, 0, false, 1) - ) -} - http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q13_customer_distribution.3.query.aql ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q13_customer_distribution.3.query.aql b/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q13_customer_distribution.3.query.aql deleted file mode 100644 index 2a490d9..0000000 --- a/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q13_customer_distribution.3.query.aql +++ /dev/null @@ -1,47 +0,0 @@ -/* - * Licensed to the Apache Software Foundation (ASF) under one - * or more contributor license agreements. See the NOTICE file - * distributed with this work for additional information - * regarding copyright ownership. The ASF licenses this file - * to you under the Apache License, Version 2.0 (the - * "License"); you may not use this file except in compliance - * with the License. You may obtain a copy of the License at - * - * http://www.apache.org/licenses/LICENSE-2.0 - * - * Unless required by applicable law or agreed to in writing, - * software distributed under the License is distributed on an - * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY - * KIND, either express or implied. See the License for the - * specific language governing permissions and limitations - * under the License. - */ -use dataverse tpch; -set import-private-functions 'true'; - -for $gco in ( - for $co in ( - for $c in dataset('Customer') - return { - "c_custkey": $c.c_custkey, - "o_orderkey_count": count( - for $o in dataset('Orders') - where $c.c_custkey = $o.o_custkey and not(like($o.o_comment,'%special%requests%')) - return $o.o_orderkey - ) - } - ) - group by $c_custkey := $co.c_custkey with $co - return { - "c_custkey": $c_custkey, - "c_count": sum(for $i in $co return $i.o_orderkey_count) - } -) -group by $c_count := $gco.c_count with $gco -let $custdist := count($gco) -order by $custdist desc, $c_count desc -return { - "c_count": $c_count, - "custdist": $custdist -} - http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q14_promotion_effect.3.query.aql ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q14_promotion_effect.3.query.aql b/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q14_promotion_effect.3.query.aql deleted file mode 100644 index 6603c8e..0000000 --- a/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q14_promotion_effect.3.query.aql +++ /dev/null @@ -1,38 +0,0 @@ -/* - * Licensed to the Apache Software Foundation (ASF) under one - * or more contributor license agreements. See the NOTICE file - * distributed with this work for additional information - * regarding copyright ownership. The ASF licenses this file - * to you under the Apache License, Version 2.0 (the - * "License"); you may not use this file except in compliance - * with the License. You may obtain a copy of the License at - * - * http://www.apache.org/licenses/LICENSE-2.0 - * - * Unless required by applicable law or agreed to in writing, - * software distributed under the License is distributed on an - * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY - * KIND, either express or implied. See the License for the - * specific language governing permissions and limitations - * under the License. - */ -use dataverse tpch; - -for $l in dataset('LineItem') -for $p in dataset('Part') -where $l.l_partkey = $p.p_partkey - and $l.l_shipdate >= '1995-09-01' - and $l.l_shipdate < '1995-10-01' -let $lp := { - "l_extendedprice": $l.l_extendedprice, - "l_discount": $l.l_discount, - "p_type": $p.p_type - } -group by $t:=1 with $lp -return 100.00 * sum( - for $i in $lp - return switch-case(like($i.p_type, 'PROMO%'), - true, $i.l_extendedprice*(1-$i.l_discount), - false, 0.0) - ) / sum(for $i in $lp return $i.l_extendedprice * (1 - $i.l_discount) -) http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q15_top_supplier.3.query.aql ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q15_top_supplier.3.query.aql b/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q15_top_supplier.3.query.aql deleted file mode 100644 index 107bd4a..0000000 --- a/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q15_top_supplier.3.query.aql +++ /dev/null @@ -1,45 +0,0 @@ -/* - * Licensed to the Apache Software Foundation (ASF) under one - * or more contributor license agreements. See the NOTICE file - * distributed with this work for additional information - * regarding copyright ownership. The ASF licenses this file - * to you under the Apache License, Version 2.0 (the - * "License"); you may not use this file except in compliance - * with the License. You may obtain a copy of the License at - * - * http://www.apache.org/licenses/LICENSE-2.0 - * - * Unless required by applicable law or agreed to in writing, - * software distributed under the License is distributed on an - * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY - * KIND, either express or implied. See the License for the - * specific language governing permissions and limitations - * under the License. - */ -use dataverse tpch; - -declare function revenue() { - for $l in dataset('LineItem') - where $l.l_shipdate >= '1996-01-01' and $l.l_shipdate < '1996-04-01' - group by $l_suppkey := $l.l_suppkey with $l - return { - "supplier_no": $l_suppkey, - "total_revenue": sum(for $i in $l return $i.l_extendedprice * (1 - $i.l_discount)) - } -} - -let $m := max( - for $r2 in revenue() - return $r2.total_revenue -) - -for $s in dataset('Supplier') -for $r in revenue() -where $s.s_suppkey = $r.supplier_no and $r.total_revenue<$m+0.000000001 and $r.total_revenue>$m-0.000000001 -return { - "s_suppkey": $s.s_suppkey, - "s_name": $s.s_name, - "s_address": $s.s_address, - "s_phone": $s.s_phone, - "total_revenue": $r.total_revenue -} http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q16_parts_supplier_relationship.3.query.aql ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q16_parts_supplier_relationship.3.query.aql b/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q16_parts_supplier_relationship.3.query.aql deleted file mode 100644 index 8f969fc..0000000 --- a/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q16_parts_supplier_relationship.3.query.aql +++ /dev/null @@ -1,66 +0,0 @@ -/* - * Licensed to the Apache Software Foundation (ASF) under one - * or more contributor license agreements. See the NOTICE file - * distributed with this work for additional information - * regarding copyright ownership. The ASF licenses this file - * to you under the Apache License, Version 2.0 (the - * "License"); you may not use this file except in compliance - * with the License. You may obtain a copy of the License at - * - * http://www.apache.org/licenses/LICENSE-2.0 - * - * Unless required by applicable law or agreed to in writing, - * software distributed under the License is distributed on an - * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY - * KIND, either express or implied. See the License for the - * specific language governing permissions and limitations - * under the License. - */ -use dataverse tpch; - -declare function tmp(){ - for $psp in ( - for $ps in dataset('Partsupp') - for $p in dataset('Part') - where $p.p_partkey = $ps.ps_partkey and $p.p_brand != 'Brand#45' - and not(like($p.p_type, 'MEDIUM POLISHED%')) - return { - "p_brand": $p.p_brand, - "p_type": $p.p_type, - "p_size": $p.p_size, - "ps_suppkey": $ps.ps_suppkey - } - ) - for $s in dataset('Supplier') - where $psp.ps_suppkey = $s.s_suppkey and not(like($s.s_comment, '%Customer%Complaints%')) - return { - "p_brand": $psp.p_brand, - "p_type": $psp.p_type, - "p_size": $psp.p_size, - "ps_suppkey": $psp.ps_suppkey - } -} - -for $t2 in ( - for $t in tmp() - where $t.p_size = 49 or $t.p_size = 14 or $t.p_size = 23 - or $t.p_size = 45 or $t.p_size = 19 or $t.p_size = 3 - or $t.p_size = 36 or $t.p_size = 9 - group by $p_brand1:= $t.p_brand, $p_type1 := $t.p_type, - $p_size1:= $t.p_size, $ps_suppkey1:=$t.ps_suppkey with $t - return { - "p_brand": $p_brand1, - "p_type": $p_type1, - "p_size": $p_size1, - "ps_suppkey": $ps_suppkey1 - } -) -group by $p_brand := $t2.p_brand, $p_type := $t2.p_type, $p_size := $t2.p_size with $t2 -let $supplier_cnt := count(for $i in $t2 return $i.ps_suppkey) -order by $supplier_cnt desc, $p_brand, $p_type, $p_size -return { - "p_brand": $p_brand, - "p_type": $p_type, - "p_size": $p_size, - "supplier_cnt": $supplier_cnt -} http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q17_small_quantity_order_revenue.3.query.aql ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q17_small_quantity_order_revenue.3.query.aql b/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q17_small_quantity_order_revenue.3.query.aql deleted file mode 100644 index ef9a3e9..0000000 --- a/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q17_small_quantity_order_revenue.3.query.aql +++ /dev/null @@ -1,41 +0,0 @@ -/* - * Licensed to the Apache Software Foundation (ASF) under one - * or more contributor license agreements. See the NOTICE file - * distributed with this work for additional information - * regarding copyright ownership. The ASF licenses this file - * to you under the Apache License, Version 2.0 (the - * "License"); you may not use this file except in compliance - * with the License. You may obtain a copy of the License at - * - * http://www.apache.org/licenses/LICENSE-2.0 - * - * Unless required by applicable law or agreed to in writing, - * software distributed under the License is distributed on an - * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY - * KIND, either express or implied. See the License for the - * specific language governing permissions and limitations - * under the License. - */ -use dataverse tpch; - -declare function tmp(){ - for $l in dataset('LineItem') - group by $l_partkey := $l.l_partkey with $l - return { - "t_partkey": $l_partkey, - "t_avg_quantity": 0.2 * avg(for $i in $l return $i.l_quantity) - } -} - -sum( - for $l in dataset('LineItem') - for $p in dataset('Part') - where $p.p_partkey = $l.l_partkey - and $p.p_brand = 'Brand#23' - and $p.p_container = 'MED BOX' - for $t in tmp() - where $l.l_partkey = $t.t_partkey - and $l.l_quantity < $t.t_avg_quantity - return $l.l_extendedprice -)/7.0 - http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q18_large_volume_customer.3.query.aql ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q18_large_volume_customer.3.query.aql b/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q18_large_volume_customer.3.query.aql deleted file mode 100644 index 1c46983..0000000 --- a/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q18_large_volume_customer.3.query.aql +++ /dev/null @@ -1,47 +0,0 @@ -/* - * Licensed to the Apache Software Foundation (ASF) under one - * or more contributor license agreements. See the NOTICE file - * distributed with this work for additional information - * regarding copyright ownership. The ASF licenses this file - * to you under the Apache License, Version 2.0 (the - * "License"); you may not use this file except in compliance - * with the License. You may obtain a copy of the License at - * - * http://www.apache.org/licenses/LICENSE-2.0 - * - * Unless required by applicable law or agreed to in writing, - * software distributed under the License is distributed on an - * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY - * KIND, either express or implied. See the License for the - * specific language governing permissions and limitations - * under the License. - */ -use dataverse tpch; - -for $c in dataset('Customer') -for $o in dataset('Orders') -where $c.c_custkey = $o.o_custkey -for $t in ( - for $l in dataset('LineItem') - group by $l_orderkey := $l.l_orderkey with $l - return { - "l_orderkey": $l_orderkey, - "t_sum_quantity": sum(for $i in $l return $i.l_quantity) - } -) -where $o.o_orderkey = $t.l_orderkey and $t.t_sum_quantity > 300 -for $l in dataset('LineItem') -where $l.l_orderkey = $o.o_orderkey -group by $c_name := $c.c_name, $c_custkey := $c.c_custkey, $o_orderkey := $o.o_orderkey, - $o_orderdate := $o.o_orderdate, $o_totalprice := $o.o_totalprice with $l -order by $o_totalprice desc, $o_orderdate -limit 100 -return { - "c_name": $c_name, - "c_custkey": $c_custkey, - "o_orderkey": $o_orderkey, - "o_orderdate": $o_orderdate, - "o_totalprice": $o_totalprice, - "sum_quantity": sum(for $j in $l return $j.l_quantity) -} - http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q19_discounted_revenue.3.query.aql ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q19_discounted_revenue.3.query.aql b/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q19_discounted_revenue.3.query.aql deleted file mode 100644 index cdb6245..0000000 --- a/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q19_discounted_revenue.3.query.aql +++ /dev/null @@ -1,51 +0,0 @@ -/* - * Licensed to the Apache Software Foundation (ASF) under one - * or more contributor license agreements. See the NOTICE file - * distributed with this work for additional information - * regarding copyright ownership. The ASF licenses this file - * to you under the Apache License, Version 2.0 (the - * "License"); you may not use this file except in compliance - * with the License. You may obtain a copy of the License at - * - * http://www.apache.org/licenses/LICENSE-2.0 - * - * Unless required by applicable law or agreed to in writing, - * software distributed under the License is distributed on an - * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY - * KIND, either express or implied. See the License for the - * specific language governing permissions and limitations - * under the License. - */ -use dataverse tpch; - -set import-private-functions 'true'; - -sum( - for $l in dataset('LineItem') - for $p in dataset('Part') - where $p.p_partkey = $l.l_partkey - and ( ( - $p.p_brand = 'Brand#12' - and reg-exp($p.p_container,'SM CASE|SM BOX|SM PACK|SM PKG') - and $l.l_quantity >= 1 and $l.l_quantity <= 11 - and $p.p_size >= 1 and $p.p_size <= 5 - and ($l.l_shipmode = 'AIR' or $l.l_shipmode = 'AIR REG') - and $l.l_shipinstruct = 'DELIVER IN PERSON' - ) or ( - $p.p_brand = 'Brand#23' - and reg-exp($p.p_container, 'MED BAG|MED BOX|MED PKG|MED PACK') - and $l.l_quantity >= 10 and $l.l_quantity <= 20 - and $p.p_size >= 1 and $p.p_size <= 10 - and ($l.l_shipmode = 'AIR' or $l.l_shipmode = 'AIR REG') - and $l.l_shipinstruct = 'DELIVER IN PERSON' - ) or ( - $p.p_brand = 'Brand#34' - and reg-exp($p.p_container, 'LG CASE|LG BOX|LG PACK|LG PKG') - and $l.l_quantity >= 20 and $l.l_quantity <= 30 - and $p.p_size >= 1 and $p.p_size <= 15 - and ($l.l_shipmode = 'AIR' or $l.l_shipmode = 'AIR REG') - and $l.l_shipinstruct = 'DELIVER IN PERSON' - ) - ) - return $l.l_extendedprice * (1 - $l.l_discount) -) http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q20_potential_part_promotion.3.query.aql ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q20_potential_part_promotion.3.query.aql b/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q20_potential_part_promotion.3.query.aql deleted file mode 100644 index c531195..0000000 --- a/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q20_potential_part_promotion.3.query.aql +++ /dev/null @@ -1,68 +0,0 @@ -/* - * Licensed to the Apache Software Foundation (ASF) under one - * or more contributor license agreements. See the NOTICE file - * distributed with this work for additional information - * regarding copyright ownership. The ASF licenses this file - * to you under the Apache License, Version 2.0 (the - * "License"); you may not use this file except in compliance - * with the License. You may obtain a copy of the License at - * - * http://www.apache.org/licenses/LICENSE-2.0 - * - * Unless required by applicable law or agreed to in writing, - * software distributed under the License is distributed on an - * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY - * KIND, either express or implied. See the License for the - * specific language governing permissions and limitations - * under the License. - */ -use dataverse tpch; - -for $t3 in ( - for $t2 in ( - for $l in dataset('LineItem') - where $l.l_shipdate >= '1994-01-01' and $l.l_shipdate < '1995-01-01' - group by $l_partkey:=$l.l_partkey, $l_suppkey:=$l.l_suppkey with $l - return { - "l_partkey": $l_partkey, - "l_suppkey": $l_suppkey, - "sum_quantity": 0.5 * sum(for $i in $l return $i.l_quantity) - } - ) - for $pst1 in ( - for $ps in dataset('Partsupp') - for $t1 in ( - for $p in dataset('Part') - where like($p.p_name, 'forest%') - distinct by $p.p_partkey - return { "p_partkey": $p.p_partkey } - ) - where $ps.ps_partkey = $t1.p_partkey - return { - "ps_suppkey": $ps.ps_suppkey, - "ps_partkey": $ps.ps_partkey, - "ps_availqty": $ps.ps_availqty - } - ) - where $pst1.ps_partkey = $t2.l_partkey and $pst1.ps_suppkey = $t2.l_suppkey - and $pst1.ps_availqty > $t2.sum_quantity - distinct by $pst1.ps_suppkey - return { "ps_suppkey": $pst1.ps_suppkey } -) -for $t4 in ( - for $n in dataset('Nation') - for $s in dataset('Supplier') - where $s.s_nationkey = $n.n_nationkey and $n.n_name = 'CANADA' - return { - "s_name": $s.s_name, - "s_address": $s.s_address, - "s_suppkey": $s.s_suppkey - } -) -where $t3.ps_suppkey = $t4.s_suppkey -order by $t4.s_name -return { - "s_name": $t4.s_name, - "s_address": $t4.s_address -} - http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q21_suppliers_who_kept_orders_waiting.3.query.aql ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q21_suppliers_who_kept_orders_waiting.3.query.aql b/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q21_suppliers_who_kept_orders_waiting.3.query.aql deleted file mode 100644 index 0d01a34..0000000 --- a/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q21_suppliers_who_kept_orders_waiting.3.query.aql +++ /dev/null @@ -1,114 +0,0 @@ -/* - * Licensed to the Apache Software Foundation (ASF) under one - * or more contributor license agreements. See the NOTICE file - * distributed with this work for additional information - * regarding copyright ownership. The ASF licenses this file - * to you under the Apache License, Version 2.0 (the - * "License"); you may not use this file except in compliance - * with the License. You may obtain a copy of the License at - * - * http://www.apache.org/licenses/LICENSE-2.0 - * - * Unless required by applicable law or agreed to in writing, - * software distributed under the License is distributed on an - * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY - * KIND, either express or implied. See the License for the - * specific language governing permissions and limitations - * under the License. - */ -use dataverse tpch; - -declare function tmp1() { - for $l2 in ( - for $l in dataset('LineItem') - group by $l_orderkey1 := $l.l_orderkey, $l_suppkey1 := $l.l_suppkey with $l - return { - "l_orderkey": $l_orderkey1, - "l_suppkey": $l_suppkey1 - } - ) - group by $l_orderkey := $l2.l_orderkey with $l2 - return { - "l_orderkey": $l_orderkey, - "count_suppkey": count(for $i in $l2 return $i.l_suppkey), - "max_suppkey": max(for $i in $l2 return $i.l_suppkey) - } -} - -declare function tmp2() { - for $l2 in ( - for $l in dataset('LineItem') - group by $l_orderkey1 := $l.l_orderkey, $l_suppkey1 := $l.l_suppkey with $l - return { - "l_orderkey": $l_orderkey1, - "l_suppkey": $l_suppkey1, - "count": count(for $i in $l return $i.l_suppkey) - } - ) - for $l3 in ( - for $l in dataset('LineItem') - where $l.l_receiptdate <= $l.l_commitdate - group by $l_orderkey1 := $l.l_orderkey, $l_suppkey1 := $l.l_suppkey with $l - return { - "l_orderkey": $l_orderkey1, - "l_suppkey": $l_suppkey1, - "count": count(for $i in $l return $i.l_suppkey) - } - ) - where $l2.l_orderkey = $l3.l_orderkey - and $l2.l_suppkey = $l3.l_suppkey - and $l2.count = $l3.count - group by $l_orderkey := $l2.l_orderkey with $l2 - return { - "l_orderkey": $l_orderkey, - "count_suppkey": count(for $i in $l2 return $i.l_suppkey), - "max_suppkey": max(for $i in $l2 return $i.l_suppkey) - } -} - -for $t4 in ( - for $t3 in ( - for $l in dataset('LineItem') - for $ns in ( - for $n in dataset('Nation') - for $s in dataset('Supplier') - where $s.s_nationkey = $n.n_nationkey - and $n.n_name = 'SAUDI ARABIA' - return { - "s_name": $s.s_name, - "s_suppkey": $s.s_suppkey - } - ) - where $ns.s_suppkey = $l.l_suppkey and $l.l_receiptdate > $l.l_commitdate - for $o in dataset('Orders') - where $o.o_orderkey = $l.l_orderkey - and $o.o_orderstatus = 'F' - for $t1 in tmp1() - where $l.l_orderkey = $t1.l_orderkey - and $t1.count_suppkey > 1 - return { - "s_name": $ns.s_name, - "l_orderkey": $t1.l_orderkey, - "l_suppkey": $l.l_suppkey, - "t1_count_suppkey": $t1.count_suppkey - } - ) - for $t2 in tmp2() - where $t3.l_orderkey = $t2.l_orderkey - and $t2.count_suppkey = $t3.t1_count_suppkey - 1 - return { - "s_name": $t3.s_name, - "l_suppkey": $t3.l_suppkey, - "l_orderkey": $t2.l_orderkey, - "count_suppkey": $t2.count_suppkey, - "max_suppkey": $t2.max_suppkey - } -) -group by $s_name := $t4.s_name with $t4 -let $numwait := count($t4) -order by $numwait desc, $s_name -limit 100 -return { - "s_name": $s_name, - "numwait": $numwait -} http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q21_suppliers_who_kept_orders_waiting.3.query_2.aql ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q21_suppliers_who_kept_orders_waiting.3.query_2.aql b/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q21_suppliers_who_kept_orders_waiting.3.query_2.aql deleted file mode 100644 index 6dc5494..0000000 --- a/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q21_suppliers_who_kept_orders_waiting.3.query_2.aql +++ /dev/null @@ -1,49 +0,0 @@ -/* - * Licensed to the Apache Software Foundation (ASF) under one - * or more contributor license agreements. See the NOTICE file - * distributed with this work for additional information - * regarding copyright ownership. The ASF licenses this file - * to you under the Apache License, Version 2.0 (the - * "License"); you may not use this file except in compliance - * with the License. You may obtain a copy of the License at - * - * http://www.apache.org/licenses/LICENSE-2.0 - * - * Unless required by applicable law or agreed to in writing, - * software distributed under the License is distributed on an - * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY - * KIND, either express or implied. See the License for the - * specific language governing permissions and limitations - * under the License. - */ -use dataverse tpch; - -for $x in ( - for $n in dataset Nation - where $n.n_name = 'SAUDI ARABIA' - for $s in dataset Supplier - where $s.s_nationkey = $n.n_nationkey - for $l1 in dataset LineItem - where $s.s_suppkey = $l1.l_suppkey - and $l1.l_receiptdate > $l1.l_commitdate - for $o in dataset Orders - where $o.o_orderstatus = 'F' - and $o.o_orderkey = $l1.l_orderkey - and (some $l2 in dataset LineItem - satisfies $l1.l_orderkey = $l2.l_orderkey - and $l1.l_suppkey != $l2.l_suppkey) - and (every $l3 in dataset LineItem - satisfies $l1.l_orderkey != $l3.l_orderkey - or $l1.l_suppkey = $l3.l_suppkey - or $l3.l_receiptdate <= $l3.l_commitdate) - return { - "s_name" : $s.s_name - } -) -group by $s_name := $x.s_name with $x -let $numwait := count($x) -order by $numwait desc -return { - "s_name": $s_name, - "numwait": $numwait -} http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q22_global_sales_opportunity.3.query.aql ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q22_global_sales_opportunity.3.query.aql b/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q22_global_sales_opportunity.3.query.aql deleted file mode 100644 index a12fb8c..0000000 --- a/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/queries/q22_global_sales_opportunity.3.query.aql +++ /dev/null @@ -1,51 +0,0 @@ -/* - * Licensed to the Apache Software Foundation (ASF) under one - * or more contributor license agreements. See the NOTICE file - * distributed with this work for additional information - * regarding copyright ownership. The ASF licenses this file - * to you under the Apache License, Version 2.0 (the - * "License"); you may not use this file except in compliance - * with the License. You may obtain a copy of the License at - * - * http://www.apache.org/licenses/LICENSE-2.0 - * - * Unless required by applicable law or agreed to in writing, - * software distributed under the License is distributed on an - * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY - * KIND, either express or implied. See the License for the - * specific language governing permissions and limitations - * under the License. - */ -use dataverse tpch; - -declare function q22_customer_tmp() { - for $c in dataset('Customer') - let $phone_substr := substring($c.c_phone, 1, 2) - where $phone_substr = '13' - or $phone_substr = '31' - or $phone_substr = '23' - or $phone_substr = '29' - or $phone_substr = '30' - or $phone_substr = '18' - or $phone_substr = '17' - return { - "c_acctbal": $c.c_acctbal, - "c_custkey": $c.c_custkey, - "cntrycode": $phone_substr - } -} - -let $avg := avg( - for $c in q22_customer_tmp() - where $c.c_acctbal > 0.00 - return $c.c_acctbal -) -for $ct in q22_customer_tmp() -where $ct.c_acctbal > $avg -group by $cntrycode := $ct.cntrycode with $ct -order by $cntrycode -return { - "cntrycode": $cntrycode, - "numcust": count($ct), - "totacctbal": sum(for $i in $ct return $i.c_acctbal) -} http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/results/q01_pricing_summary_report_nt.3.query.adm ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/results/q01_pricing_summary_report_nt.3.query.adm b/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/results/q01_pricing_summary_report_nt.3.query.adm deleted file mode 100644 index bd79360..0000000 --- a/asterixdb/asterix-benchmarks/src/main/resources/tpc-h/results/q01_pricing_summary_report_nt.3.query.adm +++ /dev/null @@ -1,4 +0,0 @@ -{ "l_returnflag": "A", "l_linestatus": "F", "sum_qty": 37734107, "sum_base_price": 5.658655440072989E10d, "sum_disc_price": 5.3758257134870056E10d, "sum_charge": 5.5909065222827835E10d, "ave_qty": 25.522005853257337, "ave_price": 38273.129734621594, "ave_disc": 0.04998529583842826, "count_order": 1478493i64 } -{ "l_returnflag": "N", "l_linestatus": "F", "sum_qty": 991417, "sum_base_price": 1.4875047103800046E9d, "sum_disc_price": 1.4130821680540953E9d, "sum_charge": 1.469649223194375E9d, "ave_qty": 25.516471920522985, "ave_price": 38284.46776084842, "ave_disc": 0.050093426674214014, "count_order": 38854i64 } -{ "l_returnflag": "N", "l_linestatus": "O", "sum_qty": 74476040, "sum_base_price": 1.1170172969774083E11d, "sum_disc_price": 1.0611823030760648E11d, "sum_charge": 1.1036704387249731E11d, "ave_qty": 25.50222676958499, "ave_price": 38249.117988908554, "ave_disc": 0.049996586053579505, "count_order": 2920374i64 } -{ "l_returnflag": "R", "l_linestatus": "F", "sum_qty": 37719753, "sum_base_price": 5.656804138089871E10d, "sum_disc_price": 5.374129268460493E10d, "sum_charge": 5.588961911983354E10d, "ave_qty": 25.50579361269077, "ave_price": 38250.854626098786, "ave_disc": 0.0500094058301571, "count_order": 1478870i64 } \ No newline at end of file