Add cluster performance testing scripts. - Wait SSH instead of sleep in the AWS installation script; - Remove Java code and generate config files in Ansible; - TPC-H is one benchmark, and more benchmarks could be added into the framework.
Change-Id: Ie17f20dc87694a865aebc561d8eded064c66f5bb Reviewed-on: https://asterix-gerrit.ics.uci.edu/1504 Sonar-Qube: Jenkins <[email protected]> Tested-by: Jenkins <[email protected]> Reviewed-by: Till Westmann <[email protected]> Project: http://git-wip-us.apache.org/repos/asf/asterixdb/repo Commit: http://git-wip-us.apache.org/repos/asf/asterixdb/commit/73715d87 Tree: http://git-wip-us.apache.org/repos/asf/asterixdb/tree/73715d87 Diff: http://git-wip-us.apache.org/repos/asf/asterixdb/diff/73715d87 Branch: refs/heads/master Commit: 73715d87802b6da9916aa094b06d66bb40b47ced Parents: 2898a01 Author: Yingyi Bu <[email protected]> Authored: Wed Feb 15 11:16:52 2017 -0800 Committer: Yingyi Bu <[email protected]> Committed: Thu Feb 16 12:49:25 2017 -0800 ---------------------------------------------------------------------- asterixdb/asterix-benchmark/pom.xml | 51 + .../src/main/assembly/binary-assembly.xml | 46 + .../src/main/resources/ansible/runquery.yml | 61 + .../resources/benchmarks/tpch/gen/compile.yml | 47 + .../main/resources/benchmarks/tpch/gen/gen.yml | 45 + .../resources/benchmarks/tpch/gen/gendata.yml | 36 + .../resources/benchmarks/tpch/gen/genscript.yml | 36 + .../resources/benchmarks/tpch/gen/settings.yml | 45 + .../benchmarks/tpch/load/gendml_centralized.yml | 41 + .../benchmarks/tpch/load/gendml_partitioned.yml | 54 + .../resources/benchmarks/tpch/load/load.yml | 35 + .../resources/benchmarks/tpch/load/loadgen.yml | 41 + .../resources/benchmarks/tpch/load/settings.yml | 37 + .../benchmarks/tpch/load/template.sqlpp | 18 + .../resources/benchmarks/tpch/queries/q1.sqlpp | 37 + .../resources/benchmarks/tpch/queries/q10.sqlpp | 42 + .../resources/benchmarks/tpch/queries/q11.sqlpp | 47 + .../resources/benchmarks/tpch/queries/q12.sqlpp | 32 + .../resources/benchmarks/tpch/queries/q13.sqlpp | 40 + .../resources/benchmarks/tpch/queries/q14.sqlpp | 28 + .../resources/benchmarks/tpch/queries/q15.sqlpp | 39 + .../resources/benchmarks/tpch/queries/q16.sqlpp | 46 + .../resources/benchmarks/tpch/queries/q17.sqlpp | 36 + .../benchmarks/tpch/queries/q18.sqlpp.dis | 41 + .../resources/benchmarks/tpch/queries/q19.sqlpp | 59 + .../benchmarks/tpch/queries/q2.sqlpp.dis | 51 + .../benchmarks/tpch/queries/q20.sqlpp.dis | 64 + .../benchmarks/tpch/queries/q21.sqlpp.dis | 71 + .../benchmarks/tpch/queries/q22.sqlpp.dis | 43 + .../resources/benchmarks/tpch/queries/q3.sqlpp | 31 + .../resources/benchmarks/tpch/queries/q4.sqlpp | 36 + .../benchmarks/tpch/queries/q5.sqlpp.dis | 45 + .../resources/benchmarks/tpch/queries/q6.sqlpp | 29 + .../benchmarks/tpch/queries/q7.sqlpp.dis | 58 + .../benchmarks/tpch/queries/q8.sqlpp.dis | 67 + .../benchmarks/tpch/queries/q9.sqlpp.dis | 47 + .../benchmarks/tpch/setup/create.sqlpp | 125 + .../resources/benchmarks/tpch/setup/setup.yml | 27 + .../benchmarks/tpch/teardown/drop.sqlpp | 16 + .../benchmarks/tpch/teardown/teardown.yml | 27 + .../src/main/resources/bin/runall.sh | 79 + .../main/resources/conf/benchmark_setting.yml | 33 + .../q01_pricing_summary_report_nt.3.query.aql | 40 - .../q02_minimum_cost_supplier.3.query.aql | 135 - .../q03_shipping_priority_nt.3.query.aql | 44 - .../queries/q04_order_priority.3.query.aql | 39 - .../q05_local_supplier_volume.3.query.aql | 75 - .../q06_forecast_revenue_change.3.query.aql | 31 - .../queries/q07_volume_shipping.3.query.aql | 81 - .../q08_national_market_share.3.query.aql | 91 - .../q09_product_type_profit_nt.3.query.aql | 85 - .../tpc-h/queries/q10_returned_item.3.query.aql | 73 - .../queries/q11_important_stock.3.query.aql | 54 - .../tpc-h/queries/q12_shipping.3.query.aql | 45 - .../q13_customer_distribution.3.query.aql | 47 - .../queries/q14_promotion_effect.3.query.aql | 38 - .../tpc-h/queries/q15_top_supplier.3.query.aql | 45 - .../q16_parts_supplier_relationship.3.query.aql | 66 - ...q17_small_quantity_order_revenue.3.query.aql | 41 - .../q18_large_volume_customer.3.query.aql | 47 - .../queries/q19_discounted_revenue.3.query.aql | 51 - .../q20_potential_part_promotion.3.query.aql | 68 - ...uppliers_who_kept_orders_waiting.3.query.aql | 114 - ...pliers_who_kept_orders_waiting.3.query_2.aql | 49 - .../q22_global_sales_opportunity.3.query.aql | 51 - .../q01_pricing_summary_report_nt.3.query.adm | 4 - .../q02_minimum_cost_supplier.3.query.adm | 100 - .../q03_shipping_priority_nt.3.query.adm | 10 - .../results/q04_order_priority.3.query.adm | 5 - .../q06_forecast_revenue_change.3.query.adm | 1 - .../results/q07_volume_shipping.3.query.adm | 4 - .../tpc-h/results/q10_returned_item.3.query.adm | 20 - .../results/q11_important_stock.3.query.adm | 1048 - .../tpc-h/results/q12_shipping.3.query.adm | 2 - .../q13_customer_distribution.3.query.adm | 42 - .../tpc-h/results/q15_top_supplier.3.query.adm | 1 - .../q16_parts_supplier_relationship.3.query.adm | 18314 ----------------- ...q17_small_quantity_order_revenue.3.query.adm | 1 - .../q20_potential_part_promotion.3.query.adm | 186 - asterixdb/asterix-server/pom.xml | 4 - .../java/org/apache/asterixdb/aws/AwsNode.java | 44 - .../apache/asterixdb/aws/ConfigGenerator.java | 136 - .../asterix-server/src/main/opt/aws/README | 1 + .../src/main/opt/aws/ansible/aws_start.yml | 68 +- .../src/main/opt/aws/ansible/instance_start.yml | 14 +- .../src/main/opt/aws/bin/start.sh | 6 - .../src/main/opt/aws/conf/aws_settings.yml | 8 +- .../src/main/opt/aws/conf/instance_settings.yml | 2 +- asterixdb/pom.xml | 1 + 89 files changed, 1974 insertions(+), 21347 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/pom.xml ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmark/pom.xml b/asterixdb/asterix-benchmark/pom.xml new file mode 100644 index 0000000..2feaeb9 --- /dev/null +++ b/asterixdb/asterix-benchmark/pom.xml @@ -0,0 +1,51 @@ +<!-- + ~ 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. + --> +<project xmlns="http://maven.apache.org/POM/4.0.0"; xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"; xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd";> + <modelVersion>4.0.0</modelVersion> + <parent> + <artifactId>apache-asterixdb</artifactId> + <groupId>org.apache.asterix</groupId> + <version>0.9.1-SNAPSHOT</version> + </parent> + <artifactId>asterix-benchmark</artifactId> + + <build> + <plugins> + <plugin> + <artifactId>maven-assembly-plugin</artifactId> + <version>2.6</version> + <executions> + <execution> + <configuration> + <attach>true</attach> + <descriptors> + <descriptor>${project.basedir}/src/main/assembly/binary-assembly.xml</descriptor> + </descriptors> + </configuration> + <phase>package</phase> + <goals> + <goal>single</goal> + </goals> + </execution> + </executions> + </plugin> + </plugins> + </build> + +</project> http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/assembly/binary-assembly.xml ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmark/src/main/assembly/binary-assembly.xml b/asterixdb/asterix-benchmark/src/main/assembly/binary-assembly.xml new file mode 100644 index 0000000..387d1d1 --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/assembly/binary-assembly.xml @@ -0,0 +1,46 @@ +<!-- + ! 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. + !--> +<assembly xmlns="http://maven.apache.org/plugins/maven-assembly-plugin/assembly/1.1.3"; + xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"; + xsi:schemaLocation="http://maven.apache.org/plugins/maven-assembly-plugin/assembly/1.1.3 http://maven.apache.org/xsd/assembly-1.1.3.xsd";> + <id>binary-assembly</id> + <formats> + <format>zip</format> + <format>dir</format> + </formats> + <includeBaseDirectory>false</includeBaseDirectory> + <fileSets> + <fileSet> + <directory>src/main/resources</directory> + <outputDirectory>.</outputDirectory> + <excludes> + <exclude>**/*.sh</exclude> + </excludes> + </fileSet> + <fileSet> + <directory>src/main/resources</directory> + <outputDirectory>.</outputDirectory> + <includes> + <include>**/*.sh</include> + </includes> + <filtered>true</filtered> + <fileMode>0755</fileMode> + </fileSet> + </fileSets> +</assembly> http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/ansible/runquery.yml ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmark/src/main/resources/ansible/runquery.yml b/asterixdb/asterix-benchmark/src/main/resources/ansible/runquery.yml new file mode 100644 index 0000000..ef3c2a5 --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/ansible/runquery.yml @@ -0,0 +1,61 @@ +# ------------------------------------------------------------ +# 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. +# ------------------------------------------------------------ + +# This script requires extra variables if the caller wants to report numbers: +# -- result_url +# -- metric + +- hosts: [localhost,] + vars: + service: "http://{{ groups['cc'][0] }}:{{ service_port }}/{{ service_endpoint }}" + temp_file: "/tmp/query_var.yml" + + tasks: + - include_vars: ../conf/benchmark_setting.yml + + - name: Execute query {{ query_file }} + uri: + url: "{{ service }}" + method: POST + body: "{{ lookup('file', query_file ) }}" + timeout: 3600 + register: response + + - name: Extract execution time + shell: printf "exec_time{{':'}} {{ response.json.metrics.elapsedTime | regex_replace('m*s', '') | int }}\n" > "{{ temp_file }}" + when: report + + - include_vars: "{{ temp_file }}" + when: report + + - name: Report execution time + uri: + url: "{{ result_url }}" + method: POST + body: "{\"group\": \"{{ query_file | basename }}\", \"metric\": \"{{ metric }}\", \"value\": {{ exec_time }} }" + body_format: json + timeout: 3600 + status_code: 201 + when: report + + - name: Delete the temporary file + file: + path: "{{ temp_file }}" + state: absent + when: report \ 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/gen/compile.yml ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/gen/compile.yml b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/gen/compile.yml new file mode 100644 index 0000000..a0aa8d3 --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/gen/compile.yml @@ -0,0 +1,47 @@ +# ------------------------------------------------------------ +# 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. +# ------------------------------------------------------------ + +- include_vars: settings.yml + +- name: Install gcc + shell: sudo yum install -y gcc + +- name: Clean up data generator + file: + path: "{{ binary_dir }}" + state: absent + +- name: Clean up leftover file + file: + path: "{{ generatorzip }}" + state: absent + +- name: Download TPC-H data generator + get_url: + url: "{{ tpch_url }}" + dest: "{{ home_dir }}" + force: yes + +- name: Unzip data generator + shell: unzip "{{ generatorzip }}" + +- name: Compile dbgen + command: make + args: + chdir: "{{ binary_dir }}" http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/gen/gen.yml ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/gen/gen.yml b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/gen/gen.yml new file mode 100644 index 0000000..536111e --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/gen/gen.yml @@ -0,0 +1,45 @@ +# ------------------------------------------------------------ +# 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. +# ------------------------------------------------------------ + + +# Compiles data generation source code. +- hosts: ncs + tasks: + - name: Compile binary + include: compile.yml + +# Generates a script for each host. +- hosts: [localhost,] + vars: + partitions: "{{ groups['ncs'] | length }}" + tasks: + - include_vars: ../../../conf/benchmark_setting.yml + + - name: Generate host-dependent script + shell: "ansible-playbook -i {{ node.1}}, genscript.yml \ + --extra-vars=\"partition={{ node.0 }} partitions={{ partitions }} sf={{ partitions|float * scale }} ansible_ssh_user=ec2-user\"" + with_indexed_items: "{{ groups['ncs'] }}" + loop_control: + loop_var: node + +# Generates data and copy them to the data directory. +- hosts: ncs + tasks: + - name: Generate data + include: gendata.yml http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/gen/gendata.yml ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/gen/gendata.yml b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/gen/gendata.yml new file mode 100644 index 0000000..389b973 --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/gen/gendata.yml @@ -0,0 +1,36 @@ +# ------------------------------------------------------------ +# 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. +# ------------------------------------------------------------ + +- include_vars: settings.yml + +- name: Generate data + command: "sh {{ localgencmd }}" + args: + chdir: "{{ binary_dir }}" + +- name: Ensure data directory exits + file: + path: "{{ data_dir }}" + state: directory + +- name: Cleanup data directory + shell: "rm -rf {{ data_dir }}/*" + +- name: Move generated files + shell: "mv {{ binary_dir }}/*.tbl* {{ data_dir }}/" http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/gen/genscript.yml ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/gen/genscript.yml b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/gen/genscript.yml new file mode 100644 index 0000000..30cf6e7 --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/gen/genscript.yml @@ -0,0 +1,36 @@ +# ------------------------------------------------------------ +# 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: all + tasks: + - include_vars: settings.yml + + - name: Ensure data directory exits + file: + path: "{{ data_dir }}" + state: directory + + - name: Generate host-dependent data generation script + shell: echo "./{{ generator }} -s {{ sf }} -S {{ partition | int + 1 }} -C {{ partitions }}" > "{{ localgen }}" + + - name: Change the permission for data generation script + file: + path: "{{ localgen }}" + owner: "{{ user }}" + mode: 0755 http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/gen/settings.yml ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/gen/settings.yml b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/gen/settings.yml new file mode 100644 index 0000000..11ba53c --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/gen/settings.yml @@ -0,0 +1,45 @@ +# ------------------------------------------------------------ +# 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. +# ------------------------------------------------------------ + +# Data generator executable name. +generator: dbgen + +# The zip contains all files for the generator. +generatorzip: "{{ generator }}.zip" + +# The user name for data generation. +user: ec2-user + +# The home directory. +home_dir: "/home/{{ user }}" + +# The location for generated data. +data_dir: "{{ home_dir }}/tpch" + +# The location for data generator binary. +binary_dir: "{{ home_dir }}/{{ generator }}" + +# The location for each local generator script on each machine. +localgen: "{{ binary_dir }}/gen.sh" + +# The local data generation command. +localgencmd: "gen.sh" + +# The url for the TPC-H benchmark. +tpch_url: <to be filed> \ 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/load/gendml_centralized.yml ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/load/gendml_centralized.yml b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/load/gendml_centralized.yml new file mode 100644 index 0000000..ef12ec8 --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/load/gendml_centralized.yml @@ -0,0 +1,41 @@ +# ------------------------------------------------------------ +# 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. +# ------------------------------------------------------------ + +# Generates a loading statement for a given dataset with a single file. +# This script assumes there is an input variable: {{ dataset }}. + +- include_vars: settings.yml +- include_vars: ../gen/settings.yml + +- name: Set target DML file name + set_fact: + target_file: "{{ dml_dir }}/load{{ dataset }}.sqlpp" + +- name: Set file path for dataset "{{ dataset }}" + set_fact: + file_path: "1://{{ data_dir }}/{{ dataset|lower }}.tbl" + +- name: Create DML for dataset {{ dataset }} + shell: cp template.sqlpp "{{ target_file }}" + +- name: Set dataset name to "{{ dataset }}" + shell: find -P "{{ target_file }}" | xargs perl -pi -e 's|<DATASET>|{{ dataset }}|g' + +- name: Set file paths for dataset "{{ dataset }}" + shell: find -P "{{ target_file }}" | xargs perl -pi -e 's|<FILES>|{{ file_path }}|g' http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/load/gendml_partitioned.yml ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/load/gendml_partitioned.yml b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/load/gendml_partitioned.yml new file mode 100644 index 0000000..118a7c9 --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/load/gendml_partitioned.yml @@ -0,0 +1,54 @@ +# ------------------------------------------------------------ +# 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. +# ------------------------------------------------------------ + +# Generates a loading statement for a given dataset with partitioned files. +# This script assumes there is an input variable: {{ dataset }}. + +- include_vars: settings.yml +- include_vars: ../gen/settings.yml + +- name: Create path file + shell: printf "" > "{{ path_file }}" + +- name: Generate partition-dependent file path + shell: echo "{{ inner_item.0 + 1 }}://{{ data_dir }}/{{ dataset|lower }}.tbl.{{ inner_item.0 + 1 }}" >> "{{ path_file }}" + with_indexed_items: "{{ groups['ncs'] }}" + loop_control: + loop_var: inner_item + +- name: Put together file paths + set_fact: + contents: "{{ lookup('file', path_file ) }}" + +- name: Concatenate file paths + set_fact: + paths: "{{ contents.split('\n')|join(',') }}" + +- name: Set target DML file name + set_fact: + target_file: "{{ dml_dir }}/load{{ dataset }}.sqlpp" + +- name: Create DML for dataset {{ dataset }} + shell: cp template.sqlpp "{{ target_file }}" + +- name: Set dataset name to "{{ dataset }}" + shell: find -P "{{ target_file }}" | xargs perl -pi -e 's|<DATASET>|{{ dataset }}|g' + +- name: Set file paths for dataset "{{ dataset }}" + shell: find -P "{{ target_file }}" | xargs perl -pi -e 's|<FILES>|{{ paths }}|g' http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/load/load.yml ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/load/load.yml b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/load/load.yml new file mode 100644 index 0000000..ff22a7d --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/load/load.yml @@ -0,0 +1,35 @@ +# ------------------------------------------------------------ +# 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 + tasks: + - include_vars: settings.yml + - include_vars: ../gen/settings.yml + + - name: Generate loading DML statements + include: loadgen.yml + + - name: Load all datasets + shell: "ansible-playbook -i {{ inventory }} {{ runquery }} --extra-vars=\"query_file={{ dml }} report=false\"" + with_fileglob: + - "{{ dml_dir }}/*" + loop_control: + loop_var: dml http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/load/loadgen.yml ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/load/loadgen.yml b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/load/loadgen.yml new file mode 100644 index 0000000..468b7f2 --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/load/loadgen.yml @@ -0,0 +1,41 @@ +# ------------------------------------------------------------ +# 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. +# ------------------------------------------------------------ + +- name: Clear DML directory + file: + path: "{{ dml_dir }}" + state: absent + +- name: Ensure DML directory exits + file: + path: "{{ dml_dir }}" + state: directory + +- name: Generate DML for datasets with partitioned files + include: gendml_partitioned.yml dataset="{{ outer_item }}" + with_items: "{{ partitioned_datasets }}" + loop_control: + loop_var: outer_item + +- name: Generate DML for datasets with a single file + include: gendml_centralized.yml dataset="{{ outer_item }}" + with_items: "{{ centralized_datasets }}" + loop_control: + loop_var: outer_item + http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/load/settings.yml ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/load/settings.yml b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/load/settings.yml new file mode 100644 index 0000000..17a3638 --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/load/settings.yml @@ -0,0 +1,37 @@ +# ------------------------------------------------------------ +# 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. +# ------------------------------------------------------------ + +# Datasets with partitioned files. +partitioned_datasets: ["LineItem", "Orders", "Customer", "Part", "Partsupp", "Supplier"] + +# Datasets with a single file. +centralized_datasets: ["Region", "Nation"] + +# Temp directory. +temp_dir: "/tmp/asterixdb" + +# Temp file to store all data paths. +path_file: "{{ temp_dir }}/paths" + +# Temp directory for generated DDLs. +dml_dir: "{{ temp_dir }}/dmls" + +# The prefix for ddl files. +ddl_prefix: "create" + http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/load/template.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/load/template.sqlpp b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/load/template.sqlpp new file mode 100644 index 0000000..1b5d867 --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/load/template.sqlpp @@ -0,0 +1,18 @@ +/* + * 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. + */ + +use tpch; + +load dataset <DATASET> using localfs ((`path`=`<FILES>`),(`format`=`delimited-text`),(`delimiter`=`|`)); http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q1.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q1.sqlpp b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q1.sqlpp new file mode 100644 index 0000000..2695bf6 --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q1.sqlpp @@ -0,0 +1,37 @@ +/* + * 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 tpch; + +SELECT l.l_returnflag, + l.l_linestatus, + sum(l.l_quantity) AS sum_qty, + sum(l.l_extendedprice) AS sum_base_price, + sum(l.l_extendedprice * (1 - l.l_discount)) AS sum_disc_price, + sum(l.l_extendedprice * (1 - l.l_discount) * (1 + l.l_tax)) AS sum_charge, + avg(l.l_quantity) AS ave_qty, + avg(l.l_extendedprice) AS ave_price, + avg(l.l_discount) AS ave_disc, + count(l) AS count_order +FROM LineItem AS l +WHERE l.l_shipdate /*+ skip-index */ <= '1998-09-02' +/* +hash */ +GROUP BY l.l_returnflag, l.l_linestatus +ORDER BY l.l_returnflag, l.l_linestatus +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q10.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q10.sqlpp b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q10.sqlpp new file mode 100644 index 0000000..a07ad2e --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q10.sqlpp @@ -0,0 +1,42 @@ +/* + * 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 tpch; + +SELECT c_custkey, c_name, + sum(locn.l_extendedprice * (1 - locn.l_discount)) AS revenue, + c_acctbal, n_name, c_address, c_phone, c_comment +FROM ( + SELECT ocn.c_custkey, ocn.c_name, ocn.c_acctbal, ocn.n_name, ocn.c_address, ocn.c_phone, ocn.c_comment, l.l_extendedprice, + l.l_discount + FROM LineItem AS l, + ( + SELECT c.c_custkey, c.c_name, c.c_acctbal, n.n_name, c.c_address, c.c_phone, c.c_comment, o.o_orderkey + FROM Orders as o, + Customer as c, + Nation as n + WHERE c.c_custkey = o.o_custkey AND o.o_orderdate >= '1993-10-01' + AND o.o_orderdate < '1994-01-01' AND c.c_nationkey = n.n_nationkey + ) AS ocn + WHERE l.l_orderkey = ocn.o_orderkey and l.l_returnflag = 'R' + ) AS locn +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/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q11.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q11.sqlpp b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q11.sqlpp new file mode 100644 index 0000000..a6c0308 --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q11.sqlpp @@ -0,0 +1,47 @@ +/* + * 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 tpch; + +WITH sum AS ( + SELECT VALUE SUM(ps.ps_supplycost * ps.ps_availqty) + FROM Partsupp AS ps, + ( + SELECT s.s_suppkey + FROM Supplier as s, + Nation as n + WHERE s.s_nationkey = n.n_nationkey AND n.n_name = 'GERMANY' + ) AS sn + WHERE ps.ps_suppkey = sn.s_suppkey +)[0] + + +SELECT ps_partkey, SUM(ps.ps_supplycost * ps.ps_availqty) AS part_value +FROM Partsupp ps, + ( + SELECT s.s_suppkey + FROM Supplier AS s, + Nation as n + WHERE s.s_nationkey = n.n_nationkey and n.n_name = 'GERMANY' + ) sn +WHERE ps.ps_suppkey = sn.s_suppkey +GROUP BY ps.ps_partkey +HAVING part_value > sum * 0.0001000 +ORDER BY part_value DESC +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q12.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q12.sqlpp b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q12.sqlpp new file mode 100644 index 0000000..f289c79 --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q12.sqlpp @@ -0,0 +1,32 @@ +/* + * 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 tpch; + +SELECT l.l_shipmode, + sum(CASE WHEN o.o_orderpriority = '1-URGENT' or o.o_orderpriority = '2-HIGH' THEN 1 ELSE 0 END) high_line_count, + sum(CASE WHEN o.o_orderpriority = '1-URGENT' or o.o_orderpriority = '2-HIGH' THEN 0 ELSE 1 END) low_line_count +FROM LineItem l, + Orders o +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.l_shipmode +ORDER BY l.l_shipmode +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q13.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q13.sqlpp b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q13.sqlpp new file mode 100644 index 0000000..fd6c657 --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q13.sqlpp @@ -0,0 +1,40 @@ +/* + * 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 tpch; + +SELECT c_count, COUNT(gco) AS custdist +FROM ( + SELECT c_custkey, SUM(o_orderkey_count) AS c_count + FROM ( + SELECT c.c_custkey, + COLL_COUNT( + ( + select element o.o_orderkey + from Orders o + where c.c_custkey = o.o_custkey and o.o_comment NOT LIKE '%special%requests%' + ) + ) AS o_orderkey_count + from Customer c + ) co + GROUP BY c_custkey +) gco +GROUP BY c_count +ORDER BY custdist DESC,c_count DESC +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q14.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q14.sqlpp b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q14.sqlpp new file mode 100644 index 0000000..f7b1214 --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q14.sqlpp @@ -0,0 +1,28 @@ +/* + * 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 tpch; + +SELECT 100.0 * + SUM( CASE WHEN p.p_type LIKE 'PROMO%' THEN l.l_extendedprice * (1 - l.l_discount) ELSE 0.0 END ) + / SUM(l.l_extendedprice * (1 - l.l_discount)) +FROM LineItem l, + Part p +WHERE 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/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q15.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q15.sqlpp b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q15.sqlpp new file mode 100644 index 0000000..7955a0b --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q15.sqlpp @@ -0,0 +1,39 @@ +/* + * 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 tpch; + +WITH revenue AS ( + SELECT l_suppkey AS supplier_no, + sum(l.l_extendedprice * (1 - l.l_discount)) AS total_revenue + FROM LineItem l + WHERE l.l_shipdate >= '1996-01-01' and l.l_shipdate < '1996-04-01' + GROUP BY l.l_suppkey l_suppkey +), +m AS ( + SELECT ELEMENT max(r2.total_revenue) + FROM revenue r2 +)[0] + +SELECT s.s_suppkey, s.s_name, s.s_address, s.s_phone, r.total_revenue +FROM Supplier s, + revenue r +WHERE s.s_suppkey = r.supplier_no AND r.total_revenue < m + 0.000000001 + AND r.total_revenue > m - 0.000000001 +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q16.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q16.sqlpp b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q16.sqlpp new file mode 100644 index 0000000..266b2c3 --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q16.sqlpp @@ -0,0 +1,46 @@ +/* + * 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 tpch; + +WITH tmp AS +( + SELECT psp.p_brand, psp.p_type, psp.p_size, psp.ps_suppkey + FROM ( + SELECT p.p_brand, p.p_type, p.p_size, ps.ps_suppkey + FROM Partsupp ps, + Part p + WHERE p.p_partkey = ps.ps_partkey AND p.p_brand != 'Brand#45' AND + p.p_type NOT LIKE 'MEDIUM POLISHED%' + ) AS psp, + Supplier s + WHERE psp.ps_suppkey = s.s_suppkey AND s.s_comment NOT LIKE '%Customer%Complaints%' +) + +SELECT p_brand, p_type, p_size, count(ps_suppkey) supplier_cnt +FROM ( + SELECT p_brand, p_type, p_size, ps_suppkey + FROM 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 + GROUP BY p_brand, p_type, p_size, ps_suppkey +) AS t2 +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/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q17.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q17.sqlpp b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q17.sqlpp new file mode 100644 index 0000000..46b79b5 --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q17.sqlpp @@ -0,0 +1,36 @@ +/* + * 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 tpch; + + +WITH tmp AS +( + SELECT l_partkey t_partkey, 0.2 * avg(l_quantity) t_avg_quantity + FROM LineItem + GROUP BY l_partkey +) + +SELECT SUM(l.l_extendedprice) / 7.0 +FROM tmp t, + LineItem l, + Part p +WHERE p.p_partkey = l.l_partkey AND p.p_container = 'MED BOX' AND p.p_brand = 'Brand#23' + AND l.l_partkey = t.t_partkey AND l.l_quantity < t.t_avg_quantity +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q18.sqlpp.dis ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q18.sqlpp.dis b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q18.sqlpp.dis new file mode 100644 index 0000000..5c4f9cb --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q18.sqlpp.dis @@ -0,0 +1,41 @@ +/* + * 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. + */ + +// Error: premature file / JVM crash + +USE tpch; + +WITH tmp AS +( + SELECT l_orderkey, SUM(l_quantity) t_sum_quantity + FROM LineItem + GROUP BY l_orderkey +) + +SELECT c.c_name, c.c_custkey, o.o_orderkey, o.o_orderdate, o.o_totalprice, + SUM(l.l_quantity) sum_quantity +FROM Customer c +JOIN Orders o ON c.c_custkey = o.o_custkey +JOIN tmp t ON o.o_orderkey = t.l_orderkey +JOIN LineItem l ON t.l_orderkey = l.l_orderkey +WHERE t.t_sum_quantity > 30 +GROUP BY c.c_name, c.c_custkey, o.o_orderkey, o.o_orderdate, o.o_totalprice +ORDER BY o.o_totalprice DESC, o.o_orderdate +LIMIT 100 +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q19.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q19.sqlpp b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q19.sqlpp new file mode 100644 index 0000000..3eb3571 --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q19.sqlpp @@ -0,0 +1,59 @@ +/* + * 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 tpch; + +WITH tmp AS +( +SELECT + l_partkey AS lpkey, + l_quantity AS quantity, + l_extendedprice AS extndprice, + l_discount AS discount +FROM LineItem +WHERE (l_shipmode = 'AIR' or l_shipmode = 'AIR REG') +AND l_shipinstruct = 'DELIVER IN PERSON' +) + +SELECT SUM(l.extndprice * (1 - l.discount)) +FROM tmp l +JOIN Part p +ON p.p_partkey = l.lpkey +WHERE + ( + p.p_brand = 'Brand#12' + AND regexp_contains(p.p_container, 'SM CASE|SM BOX|SM PACK|SM PKG') + AND l.quantity >= 1 and l.quantity <= 11 + AND p.p_size >= 1 and p.p_size <= 5 + ) + OR + ( + p.p_brand = 'Brand#23' + AND regexp_contains(p.p_container, 'MED BAG|MED BOX|MED PKG|MED PACK') + AND l.quantity >= 10 and l.quantity <= 20 + AND p.p_size >= 1 and p.p_size <= 10 + ) + OR + ( + p.p_brand = 'Brand#34' + AND regexp_contains(p.p_container, 'LG CASE|LG BOX|LG PACK|LG PKG') + AND l.quantity >= 20 and l.quantity <= 30 + AND p.p_size >= 1 and p.p_size <= 15 + ) +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q2.sqlpp.dis ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q2.sqlpp.dis b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q2.sqlpp.dis new file mode 100644 index 0000000..ec11738 --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q2.sqlpp.dis @@ -0,0 +1,51 @@ +/* + * 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. + */ + +// Error: budget exceeds + +USE tpch; + +WITH q2_minimum_cost_supplier_tmp1 AS +( + 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_type LIKE '%BRASS' AND p.p_size = 15 +), +q2_minimum_cost_supplier_tmp2 AS +( + SELECT p.p_partkey, min(p.ps_supplycost) AS ps_min_supplycost + FROM + q2_minimum_cost_supplier_tmp1 p + GROUP BY p.p_partkey +) + +SELECT t1.s_acctbal, t1.s_name, t1.n_name, t1.p_partkey, t1.p_mfgr AS 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 t1.s_acctbal DESC, t1.n_name, t1.s_name, t1.p_partkey +LIMIT 100; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q20.sqlpp.dis ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q20.sqlpp.dis b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q20.sqlpp.dis new file mode 100644 index 0000000..45c4740 --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q20.sqlpp.dis @@ -0,0 +1,64 @@ +/* + * 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. + */ + +// Error: budget exceeds + +USE tpch; + +WITH q20_tmp1 AS +( + SELECT DISTINCT p_partkey + FROM Part + WHERE p_name LIKE 'forest%' +) +, +q20_tmp2 AS +( + SELECT l_partkey, l_suppkey, 0.5 * sum(l_quantity) AS sum_quantity + FROM LineItem + WHERE l_shipdate >= '1994-01-01' and l_shipdate < '1995-01-01' + GROUP BY l_partkey l_partkey, l_suppkey l_suppkey +) +, +q20_tmp3 AS +( + SELECT ps_suppkey, ps_availqty, t2.sum_quantity + FROM Partsupp + JOIN q20_tmp1 t1 + ON ps_partkey = t1.p_partkey + JOIN q20_tmp2 t2 + ON ps_partkey = t2.l_partkey and ps_suppkey = t2.l_suppkey +) +, +q20_tmp4 AS +( + SELECT ps_suppkey + FROM q20_tmp3 + WHERE ps_availqty > sum_quantity + GROUP BY ps_suppkey +) + +SELECT s.s_name, s.s_address +FROM Supplier s +JOIN Nation n +ON s.s_nationkey = n.n_nationkey +JOIN q20_tmp4 t4 +ON s.s_suppkey = t4.ps_suppkey +WHERE n.n_name = 'CANADA' +ORDER BY s.s_name; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q21.sqlpp.dis ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q21.sqlpp.dis b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q21.sqlpp.dis new file mode 100644 index 0000000..768ad47 --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q21.sqlpp.dis @@ -0,0 +1,71 @@ +/* + * 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. + */ + +// Error: budget exceeds + +USE tpch; + +WITH tmp1 AS +( + SELECT l_orderkey, + COUNT(l_suppkey) AS count_suppkey, + MAX(l_suppkey) AS max_suppkey + FROM ( + SELECT l_orderkey, l_suppkey + from LineItem l + GROUP BY l_orderkey, l_suppkey + ) AS l2 + GROUP BY l_orderkey +), +tmp2 AS +( + SELECT l2.l_orderkey, + COUNT(l_suppkey) AS count_suppkey, + MAX(l_suppkey) AS max_suppkey + FROM ( + SELECT l_orderkey, l_suppkey + FROM LineItem l + WHERE l_receiptdate > l_commitdate + GROUP BY l_orderkey, l_suppkey + ) AS l2 + GROUP BY l_orderkey +) + +SELECT t4.s_name, COUNT(*) AS numwait +FROM ( + SELECT t3.s_name, l_suppkey, t2.l_orderkey, count_suppkey, max_suppkey + FROM ( + SELECT ns.s_name, t1.l_orderkey, t1.l_suppkey + FROM LineItem l, + ( + SELECT s.s_name, s.s_suppkey + FROM Nation n, Supplier s + WHERE s.s_nationkey = n.n_nationkey AND n.n_name='SAUDI ARABIA' + ) AS ns, + Orders o, + tmp1 AS t1 + WHERE ns.s_suppkey = l.l_suppkey AND l.l_receiptdate > l.l_commitdate + AND o.o_orderkey = t1.l_orderkey AND l.l_orderkey = t1.l_orderkey + AND o.o_orderstatus = 'F' + ) AS t3 + JOIN tmp2 AS t2 ON count_suppkey >= 0 AND t3.l_orderkey = t2.l_orderkey +) AS t4 +GROUP BY t4.s_name +ORDER BY numwait DESC, t4.s_name +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q22.sqlpp.dis ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q22.sqlpp.dis b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q22.sqlpp.dis new file mode 100644 index 0000000..196caae --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q22.sqlpp.dis @@ -0,0 +1,43 @@ +/* + * 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. + */ + +// Error: budget exceeds + +USE tpch; + +WITH q22_customer_tmp AS +( + SELECT c_acctbal, c_custkey, cntrycode + FROM Customer + LET cntrycode = substring(c_phone,1,2) + WHERE cntrycode = '13' OR cntrycode = '31' OR cntrycode = '23' OR cntrycode = '29' OR cntrycode = '30' OR cntrycode = '18' OR cntrycode = '17' +), +avg AS ( + SELECT ELEMENT AVG(c_acctbal) + FROM Customer + LET cntrycode = substring(c_phone,1,2) + WHERE c_acctbal > 0.0 AND (cntrycode = '13' OR cntrycode = '31' OR cntrycode = '23' OR cntrycode = '29' OR cntrycode = '30' OR cntrycode = '18' OR cntrycode = '17') +)[0] + +SELECT cntrycode, count(ct) AS numcust, SUM(c_acctbal) AS totacctbal +FROM q22_customer_tmp AS ct +WHERE ct.c_acctbal > avg AND EXISTS ( SELECT * FROM Orders as o WHERE ct.c_custkey = o.o_custkey ) +GROUP BY cntrycode +ORDER BY cntrycode +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q3.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q3.sqlpp b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q3.sqlpp new file mode 100644 index 0000000..82e5ef8 --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q3.sqlpp @@ -0,0 +1,31 @@ +/* + * 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 tpch; + +SELECT l.l_orderkey, sum(l.l_extendedprice * (1 - l.l_discount)) AS revenue, o.o_orderdate, o.o_shippriority +FROM Customer AS c, + Orders AS o, + LineItem AS l +where c.c_mktsegment = 'BUILDING' AND c.c_custkey = o.o_custkey AND l.l_orderkey = o.o_orderkey + AND o.o_orderdate < '1995-03-15' AND l.l_shipdate > '1995-03-15' +GROUP BY l.l_orderkey, o.o_orderdate, o.o_shippriority +ORDER BY revenue DESC,o_orderdate +LIMIT 10 +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q4.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q4.sqlpp b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q4.sqlpp new file mode 100644 index 0000000..d01b502 --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q4.sqlpp @@ -0,0 +1,36 @@ +/* + * 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 tpch; + +WITH tmp AS +( + SELECT l.l_orderkey AS o_orderkey + FROM LineItem AS l + WHERE l.l_commitdate < l.l_receiptdate +) + +SELECT o.o_orderpriority, count(o) AS count +FROM Orders AS o +JOIN tmp AS t +ON o.o_orderkey = t.o_orderkey +WHERE o.o_orderdate >= '1993-07-01' AND o.o_orderdate < '1993-10-01' +GROUP BY o.o_orderpriority +ORDER BY o_orderpriority +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q5.sqlpp.dis ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q5.sqlpp.dis b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q5.sqlpp.dis new file mode 100644 index 0000000..3b9e60d --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q5.sqlpp.dis @@ -0,0 +1,45 @@ +/* + * 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. + */ + +// Error: budget exceeds + +USE tpch; + +SELECT n_name, sum(o1.l_extendedprice * (1 - o1.l_discount)) AS revenue +FROM + Customer c JOIN + ( SELECT l1.n_name, l1.l_extendedprice, l1.l_discount, l1.s_nationkey, o.o_custkey + FROM Orders o JOIN + ( SELECT s1.n_name, l.l_extendedprice, l.l_discount, l.l_orderkey, s1.s_nationkey + FROM LineItem l JOIN + ( SELECT n1.n_name, s.s_suppkey, s.s_nationkey + FROM Supplier s JOIN + ( SELECT n.n_name, n.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 o1.n_name AS n_name +ORDER BY revenue DESC; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q6.sqlpp ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q6.sqlpp b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q6.sqlpp new file mode 100644 index 0000000..2124bf2 --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q6.sqlpp @@ -0,0 +1,29 @@ +/* + * 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 tpch; + +SELECT SUM(l.l_extendedprice * l.l_discount) +FROM LineItem AS l +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 +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q7.sqlpp.dis ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q7.sqlpp.dis b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q7.sqlpp.dis new file mode 100644 index 0000000..bc6a7a6 --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q7.sqlpp.dis @@ -0,0 +1,58 @@ +/* + * 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. + */ + +// Error: sporadically dead node. + +USE tpch; + +WITH q7_volume_shipping_tmp AS +( + 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 as n1, + Nation as n2 + WHERE (n1.n_name='FRANCE' AND n2.n_name='GERMANY') OR (n1.n_name='GERMANY' AND n2.n_name='FRANCE') +) + +SELECT supp_nation, cust_nation, l_year, sum(volume) AS revenue +FROM + ( + SELECT t.supp_nation, t.cust_nation, GET_YEAR(l3.l_shipdate) AS l_year, + l3.l_extendedprice * (1 - l3.l_discount) AS volume + FROM ( + SELECT l2.l_shipdate, l2.l_extendedprice, l2.l_discount, l2.c_nationkey, s.s_nationkey + FROM Supplier s JOIN + ( + SELECT l1.l_shipdate, l1.l_extendedprice, l1.l_discount, l1.l_suppkey, c.c_nationkey + FROM Customer c JOIN + ( + SELECT l.l_shipdate, l.l_extendedprice, l.l_discount, l.l_suppkey, o.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 + JOIN q7_volume_shipping_tmp t + ON t.c_nationkey = l3.c_nationkey AND t.s_nationkey = l3.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/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q8.sqlpp.dis ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q8.sqlpp.dis b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q8.sqlpp.dis new file mode 100644 index 0000000..60b399a --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q8.sqlpp.dis @@ -0,0 +1,67 @@ +/* + * 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. + */ + +// Error: budget exceeds + +USE tpch; + +SELECT year, + SUM( CASE t.s_name = 'BRAZIL' WHEN true THEN t.revenue ELSE 0.0 END ) / SUM(t.revenue) AS mkt_share +FROM ( + SELECT o_year AS year, + slnrcop.l_extendedprice * (1 - slnrcop.l_discount) AS revenue, + n2.n_name AS s_name + FROM ( + SELECT lnrcop.o_orderdate, lnrcop.l_discount, lnrcop.l_extendedprice, lnrcop.l_suppkey, s.s_nationkey + FROM Supplier s, + ( + SELECT lnrco.o_orderdate, lnrco.l_discount, lnrco.l_extendedprice, lnrco.l_suppkey + FROM ( + SELECT nrco.o_orderdate, l.l_partkey, l.l_discount, l.l_extendedprice, l.l_suppkey + FROM LineItem l, + ( + SELECT o.o_orderdate, o.o_orderkey + FROM Orders o, + ( + SELECT c.c_custkey + FROM Customer c, + ( + SELECT n.n_nationkey + FROM Nation n, + Region r + WHERE n.n_regionkey = r.r_regionkey AND r.r_name = 'AMERICA' + ) AS nr + WHERE c.c_nationkey = nr.n_nationkey + ) AS nrc + WHERE nrc.c_custkey = o.o_custkey + ) AS nrco + WHERE l.l_orderkey = nrco.o_orderkey and nrco.o_orderdate >= '1995-01-01' and nrco.o_orderdate < '1996-12-31' + ) AS lnrco, + Part p + WHERE p.p_partkey = lnrco.l_partkey and p.p_type = 'ECONOMY ANODIZED STEEL' + ) AS lnrcop + WHERE s.s_suppkey = lnrcop.l_suppkey + ) AS slnrcop, + Nation n2 + LET o_year = `get-year`(slnrcop.o_orderdate) + WHERE slnrcop.s_nationkey = n2.n_nationkey + ) as t +GROUP BY year +ORDER BY year +; http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q9.sqlpp.dis ---------------------------------------------------------------------- diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q9.sqlpp.dis b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q9.sqlpp.dis new file mode 100644 index 0000000..80a37f4 --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q9.sqlpp.dis @@ -0,0 +1,47 @@ +/* + * 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. + */ + +// Error: no space left + +USE tpch; + +SELECT nation, o_year, SUM(amount) AS sum_profit +FROM + ( + SELECT l3.n_name AS nation, + GET_YEAR(o.o_orderdate) AS o_year, + l3.l_extendedprice * (1 - l3.l_discount) - l3.ps_supplycost * l3.l_quantity AS amount + FROM + Orders o JOIN + ( + SELECT l2.l_extendedprice, l2.l_discount, l2.l_quantity, l2.l_orderkey, l2.n_name,l2. ps_supplycost + FROM Part p JOIN + (SELECT l1.l_extendedprice, l1.l_discount, l1.l_quantity, l1.l_partkey, l1.l_orderkey, l1.n_name, ps.ps_supplycost + FROM Partsupp ps join + (SELECT l.l_suppkey, l.l_extendedprice, l.l_discount, l.l_quantity, l.l_partkey, l.l_orderkey, s1.n_name + FROM + (SELECT s.s_suppkey, n.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 contains(p.p_name,'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;
