Xikui Wang has submitted this change and it was merged. Change subject: [TEST] Add performance test for AsterixDB ......................................................................
[TEST] Add performance test for AsterixDB 1. Fix the issue that external var in ansible is truncked by space. 2. Add performance test comparison code for SparkSQL using small instances. Change-Id: Ic9ff8efa0be71bde67190ba6f9fbd647c7799084 Reviewed-on: https://asterix-gerrit.ics.uci.edu/1879 Sonar-Qube: Jenkins <jenk...@fulliautomatix.ics.uci.edu> Tested-by: Jenkins <jenk...@fulliautomatix.ics.uci.edu> Reviewed-by: Yingyi Bu <buyin...@gmail.com> --- M asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/gen/gen.yml M asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/gen/genscript.yml M asterixdb/asterix-benchmark/src/main/resources/bin/runall.sh A asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/ansible/create_aws_cluster.yml A asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/ansible/execute_queries.yml A asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/ansible/install_hdfs.yml A asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/ansible/install_spark.yml A asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/ansible/instance_init.yml A asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/ansible/load_tpch.yml A asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/ansible/prepare_queries.yml A asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/ansible/spark_sql_settings.yml A asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/ansible/start_hdfs.yml A asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/ansible/start_spark.yml A asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/backup_queries/q18.sql A asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/backup_queries/q2.sql A asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/backup_queries/q20.sql A asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/backup_queries/q21.sql A asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/backup_queries/q22.sql A asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/backup_queries/q5.sql A asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/backup_queries/q7_variant.sql A asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/backup_queries/q8.sql A asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/backup_queries/q9.sql A asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/conf/core-site-template.xml A asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/conf/execute-query.tmpl A asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/conf/hdfs-site-template.xml A asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/queries/q1.sql A asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/queries/q10.sql A asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/queries/q11.sql A asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/queries/q12.sql A asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/queries/q13.sql A asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/queries/q14.sql A asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/queries/q15.sql A asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/queries/q16.sql A asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/queries/q17.sql A asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/queries/q19.sql A asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/queries/q3.sql A asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/queries/q4.sql A asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/queries/q6.sql A asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/queries/q7.sql A asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/run_sparksql.sh A asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/start_aws_SparkSQL.sh M asterixdb/asterix-server/src/main/opt/aws/bin/terminate.sh 42 files changed, 1,849 insertions(+), 9 deletions(-) Approvals: Yingyi Bu: Looks good to me, approved Anon. E. Moose #1000171: Jenkins: Verified; No violations found Objections: Jenkins: 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 index 536111e..849a6cd 100644 --- a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/gen/gen.yml +++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/gen/gen.yml @@ -26,14 +26,14 @@ # Generates a script for each host. - hosts: [localhost,] + vars_files: + - ../../../conf/benchmark_setting.yml vars: partitions: "{{ groups['ncs'] | length }}" tasks: - - include_vars: ../../../conf/benchmark_setting.yml - - - name: Generate host-dependent script + - name: Generate host-dependent script with sf shell: "ansible-playbook -i {{ node.1}}, genscript.yml \ - --extra-vars=\"partition={{ node.0 }} partitions={{ partitions }} sf={{ partitions|float * scale }} ansible_ssh_user=ec2-user\"" + --extra-vars=\"partition={{ node.0 }} partitions={{ partitions }} sf={{ scale }} ansible_ssh_user=ec2-user\"" with_indexed_items: "{{ groups['ncs'] }}" loop_control: loop_var: node 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 index 30cf6e7..c814f4c 100644 --- a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/gen/genscript.yml +++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/gen/genscript.yml @@ -18,6 +18,8 @@ # ------------------------------------------------------------ - hosts: all + vars: + scale_factor: "{{ partitions|float * (ansible_memtotal_mb/1000)|int * sf|float }}" tasks: - include_vars: settings.yml @@ -26,8 +28,8 @@ 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: Generate host-dependent data generation script with scale_factor {{ scale_factor }} + shell: echo "./{{ generator }} -s {{ scale_factor }} -S {{ partition | int + 1 }} -C {{ partitions }}" > "{{ localgen }}" - name: Change the permission for data generation script file: diff --git a/asterixdb/asterix-benchmark/src/main/resources/bin/runall.sh b/asterixdb/asterix-benchmark/src/main/resources/bin/runall.sh index a086a34..4b8872a 100755 --- a/asterixdb/asterix-benchmark/src/main/resources/bin/runall.sh +++ b/asterixdb/asterix-benchmark/src/main/resources/bin/runall.sh @@ -72,7 +72,7 @@ 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=${SYSTEM_NAME}" \ + ansible-playbook -i $INVENTORY --extra-vars="query_file=${query} report=true metric='${SYSTEM_NAME}'" \ $ANSIBLE_PATH/runquery.yml done done diff --git a/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/ansible/create_aws_cluster.yml b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/ansible/create_aws_cluster.yml new file mode 100644 index 0000000..4529e8b --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/ansible/create_aws_cluster.yml @@ -0,0 +1,74 @@ +# ------------------------------------------------------------ +# 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: Create AWS cluster + hosts: localhost + gather_facts: false + vars: + inventory: "{{ playbook_dir }}/../conf/inventory" + private_ip: "{{ playbook_dir }}/../conf/private_ip.yml" + spark_instance_name: "spark_test" + tasks: + - include_vars: "{{ aws_setting }}" + + - name: Clean inventory file + file: + path: "{{ inventory }}" + state: absent + + - name: Clean private ip file + file: + path: "{{ private_ip }}" + state: absent + + - name: Launch EC2 instances + ec2: + key_name: "{{ keypair }}" + instance_type: "{{ instance_type }}" + image: "{{ image }}" + count: "{{ count }}" + wait: True + region: "{{ region }}" + instance_tags: + Name: "{{ spark_instance_name }}" + aws_access_key: "{{ access_key_id }}" + aws_secret_key: "{{ secret_access_key }}" + register: + ec2 + + - name: Set name node + set_fact: + master: "{{ ec2.instances[0] }}" + + - name: Populate inventory file for name node + shell: printf "[cc]\n{{ master.public_dns_name }}\n" >> "{{ inventory }}"; printf "cc_ip{{ ":" }} {{ master.private_ip }}\n" >> "{{ private_ip }}"; + + - name: Populate section head for slave nodes + shell: printf "\n[ncs]\n" >> "{{ inventory }}"; printf "\nnc_ip{{ ":" }}" >> "{{ private_ip }}"; + + - name: Populate inventory file for slave nodes + shell: printf "{{ node.1.public_dns_name }}\n" >> "{{ inventory }}"; printf " {{ node.1.private_ip }}" >> "{{ private_ip }}" + when: node.0 != 0 + with_indexed_items: "{{ ec2.instances }}" + loop_control: + loop_var: node + + - name: Setup username + shell: printf "\n[all:vars]\nansible_ssh_user={{ user }}\n" >> "{{ inventory }}" \ No newline at end of file diff --git a/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/ansible/execute_queries.yml b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/ansible/execute_queries.yml new file mode 100644 index 0000000..d304322 --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/ansible/execute_queries.yml @@ -0,0 +1,53 @@ +# ------------------------------------------------------------ +# 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: cc + tasks: + - include_vars: spark_sql_settings.yml + - include_vars: "{{ playbook_dir }}/../conf/private_ip.yml" + - name: Execute query with Spark + shell: bash {{ spark_base }}/bin/spark-shell --executor-memory {{ spark_memory }}m --master spark://{{ cc_ip }}:7077 -i main.scala > ~/sparkLog.txt 2>&1 + async: 5400 + poll: 100 + - name: Fetch the result file from cc + fetch: + src: "{{ result_file }}" + dest: "{{ local_result }}" + flat: yes + +- hosts: [localhost,] + tasks: + - include_vars: spark_sql_settings.yml + - include_vars: ../../../conf/benchmark_setting.yml + - stat: + path: "{{ local_result }}" + register: p + - name: Report result to REST + shell: <{{ local_result }} xargs -I % curl -XPOST -m 120 -d % {{ result_url }} + when: p.stat.exists + - name: Find out what the results are + slurp: + src: "{{ local_result }}" + register: res + - debug: + msg: "{{ res['content'] | b64decode }}" + - name: Remove result + file: + path: "{{ local_result }}" + state: absent \ No newline at end of file diff --git a/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/ansible/install_hdfs.yml b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/ansible/install_hdfs.yml new file mode 100644 index 0000000..267ab2a --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/ansible/install_hdfs.yml @@ -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. +# ------------------------------------------------------------ + +- name: Install HDFS + hosts: all + tasks: + - include_vars: spark_sql_settings.yml + - name: Download Hadoop + get_url: + url: http://apache.mirrors.hoobly.com/hadoop/common/hadoop-2.8.0/hadoop-2.8.0.tar.gz + dest: "{{ home_dir }}/hadoop.tar.gz" + - name: Unzip Hadoop + unarchive: + src: "{{ home_dir }}/hadoop.tar.gz" + dest: "{{ home_dir }}" + remote_src: yes + - name: Remove install package + file: + state: absent + path: "{{ home_dir }}/hadoop.tar.gz" + +- name: Add slaves to name node + hosts: cc + tasks: + - include_vars: spark_sql_settings.yml + - include_vars: "{{ playbook_dir }}/../conf/private_ip.yml" + - file: + path: '{{ hadoop_base }}/etc/hadoop/slaves' + state: absent + - shell: 'echo {{ item }} >> {{ hadoop_base }}/etc/hadoop/slaves' + with_items: + "{{ nc_ip.split(' ') }}" + +- name: Populate configuration file + hosts: all + tasks: + - include_vars: spark_sql_settings.yml + - include_vars: "{{ playbook_dir }}/../conf/private_ip.yml" + - name: Populate core-site-template.xml + template: + src: ../conf/core-site-template.xml + dest: "{{ hadoop_base }}/etc/hadoop/core-site.xml" + - name: Populate hdfs-site-template.xml + template: + src: ../conf/hdfs-site-template.xml + dest: "{{ hadoop_base }}/etc/hadoop/hdfs-site.xml" + - name: Add memory constraint on all nodes + shell: echo "export HADOOP_HEAPSIZE={{ hdfs_memory }}" >> {{ hadoop_base }}/etc/hadoop/hadoop-env.sh \ No newline at end of file diff --git a/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/ansible/install_spark.yml b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/ansible/install_spark.yml new file mode 100644 index 0000000..4230793 --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/ansible/install_spark.yml @@ -0,0 +1,44 @@ +# ------------------------------------------------------------ +# 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: Install Sparks + hosts: all + tasks: + - include_vars: spark_sql_settings.yml + - name: Clean old installation if exsits + file: + path: "{{ spark_base }}" + state: absent + - name: Download Spark + get_url: + url: https://d3kbcqa49mib13.cloudfront.net/spark-2.1.1-bin-hadoop2.7.tgz + dest: "{{ home_dir }}/spark.tgz" + - name: Unzip Spark + unarchive: + src: "{{ home_dir }}/spark.tgz" + dest: "{{ home_dir }}" + remote_src: yes + - name: Make configuration file + shell: "cp {{ spark_base }}/conf/spark-env.sh.template {{ spark_base }}/conf/spark-env.sh" + - name: Add memory option + shell: echo "SPARK_WORKER_MEMORY={{ spark_memory }}m" >> {{ spark_base }}/conf/spark-env.sh + - name: Remove install package + file: + state: absent + path: "{{ home_dir }}/spark.tgz" \ No newline at end of file diff --git a/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/ansible/instance_init.yml b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/ansible/instance_init.yml new file mode 100644 index 0000000..b4ec428 --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/ansible/instance_init.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: [localhost,] + tasks: + - name: Wait for SSH to come up at the cluster controller + wait_for: host="{{ item }}" port=22 search_regex=OpenSSH delay=0 timeout=300 state=started + with_items: "{{ groups['cc'] }}" + - name: Wait for SSH to come up at node controllers + wait_for: host="{{ item }}" port=22 search_regex=OpenSSH delay=0 timeout=300 state=started + with_items: "{{ groups['ncs'] }}" + +- hosts: all + tasks: + - name: Download JRE + shell: "wget -q --tries=5 --no-cookies --no-check-certificate --header \ + \"Cookie: gpw_e24=http%3A%2F%2Fwww.oracle.com%2F; oraclelicense=accept-securebackup-cookie\" \ + \"http://download.oracle.com/otn-pub/java/jdk/8u131-b11/d54c1d3a095b4ff2b6607d096fa80163/jre-8u131-linux-x64.rpm\""; + - name: Install JRE + shell: sudo yum -y localinstall jre-8u131-linux-x64.rpm diff --git a/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/ansible/load_tpch.yml b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/ansible/load_tpch.yml new file mode 100644 index 0000000..ea635f4 --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/ansible/load_tpch.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. +# ------------------------------------------------------------ + +- hosts: cc + tasks: + - include_vars: spark_sql_settings.yml + - include_vars: ../../../benchmarks/tpch/gen/settings.yml + - name: Create TPCH data dir on HDFS + shell: 'bash {{ hadoop_base }}/bin/hdfs dfs -mkdir -p {{ data_dir }}' + +- hosts: ncs + tasks: + - include_vars: spark_sql_settings.yml + - include_vars: "{{ playbook_dir }}/../conf/private_ip.yml" + - include_vars: ../../../benchmarks/tpch/gen/settings.yml + - name: Put data on to HDFS + shell: for i in `ls -S {{ data_dir }}/`; do {{ hadoop_base }}/bin/hdfs dfs -put -f {{ data_dir }}/$i hdfs://{{ cc_ip }}:9000/{{ data_dir }}; rm {{ data_dir }}/$i; done \ No newline at end of file diff --git a/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/ansible/prepare_queries.yml b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/ansible/prepare_queries.yml new file mode 100644 index 0000000..3535c9e --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/ansible/prepare_queries.yml @@ -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. +# ------------------------------------------------------------ + +- hosts: cc + tasks: + - include_vars: spark_sql_settings.yml + - include_vars: ../../../benchmarks/tpch/gen/settings.yml + - name: Sync queries directory + synchronize: + src: ../{{ query_files_root }} + dest: /home/{{ user }}/ + - name: Ship script template + template: + src: ../conf/execute-query.tmpl + dest: /home/{{ user }}/main.scala \ No newline at end of file diff --git a/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/ansible/spark_sql_settings.yml b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/ansible/spark_sql_settings.yml new file mode 100644 index 0000000..1ff3f67 --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/ansible/spark_sql_settings.yml @@ -0,0 +1,44 @@ +# ------------------------------------------------------------ +# 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. +# ------------------------------------------------------------ + +user: ec2-user + +home_dir: "/home/{{ user }}" + +hadoop_base: "{{ home_dir }}/hadoop-2.8.0" + +spark_base: "{{ home_dir }}/spark-2.1.1-bin-hadoop2.7" + +hdfs_memory: "{{ (ansible_memtotal_mb * 0.25)|int|abs }}" + +spark_memory: "{{ (ansible_memtotal_mb * 0.5)|int|abs }}" + +test_round: 3 + +query_files_root: "queries" + +dfs_replication: 1 + +result_file: "{{home_dir}}/result.txt" + +generator: dbgen + +binary_dir: "{{ home_dir }}/{{ generator }}" + +local_result: "/tmp/sparkSQL_Result.txt" diff --git a/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/ansible/start_hdfs.yml b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/ansible/start_hdfs.yml new file mode 100644 index 0000000..40497d7 --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/ansible/start_hdfs.yml @@ -0,0 +1,80 @@ +# ------------------------------------------------------------ +# 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: Prepare password-less on master + vars: + + hosts: cc + tasks: + - include_vars: spark_sql_settings.yml + - include_vars: "{{ playbook_dir }}/../conf/private_ip.yml" + - name: Generate key on master + user: + name: "{{ user }}" + generate_ssh_key: yes + ssh_key_bits: 2048 + ssh_key_file: .ssh/ec2_rsa + - name: Make passwordless to current host + shell: "cat ~/.ssh/ec2_rsa.pub >> ~/.ssh/authorized_keys" + - name: Change permissions on master + file: + path: ~/.ssh/authorized_keys + mode: 0600 + - name: Download key from master + fetch: + src: ~/.ssh/ec2_rsa.pub + dest: ../conf/master.key + flat: yes + - file: + path: ~/.ssh/config + state: absent + - name: Bypass host check on master + shell: printf "Host *.amazonaws.com 0.0.0.0 {{ cc_ip }}" >> ~/.ssh/config + - name: Add hosts to list + shell: printf " {{ item }}" >> ~/.ssh/config + with_items: "{{ nc_ip.split(' ') }}" + - shell: printf "\n IdentityFile /{{ home_dir }}/.ssh/ec2_rsa\n" >> ~/.ssh/config + - shell: printf "\n StrictHostKeyChecking no\n" >> ~/.ssh/config + - file: + path: ~/.ssh/config + mode: 0600 + + +- name: Prepare password-less on slaves + hosts: ncs + tasks: + - name: Ship master key to slaves + copy: + src: ../conf/master.key + dest: "/tmp/master.key" + mode: 0600 + - name: Add master key to authorized_keys list + shell: "cat /tmp/master.key >> ~/.ssh/authorized_keys" + - name: Change permissions on ncs + file: + path: ~/.ssh/authorized_keys + mode: 0600 + +- name: Start HDFS + hosts: cc + tasks: + - name: Format HDFS + shell: 'bash {{ hadoop_base }}/bin/hdfs namenode -format -force' + - name: Start name node + shell: 'bash {{ hadoop_base }}/sbin/start-dfs.sh' \ No newline at end of file diff --git a/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/ansible/start_spark.yml b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/ansible/start_spark.yml new file mode 100644 index 0000000..8f0c57e --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/ansible/start_spark.yml @@ -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. +# ------------------------------------------------------------ + +- hosts: cc + tasks: + - include_vars: spark_sql_settings.yml + - include_vars: "{{ playbook_dir }}/../conf/private_ip.yml" + - shell: 'echo {{ item }} >> {{ spark_base }}/conf/slaves' + with_items: "{{ nc_ip.split(' ') }}" + + - name: Start Sparks + shell: "bash {{ spark_base }}/sbin/start-all.sh" \ No newline at end of file diff --git a/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/backup_queries/q18.sql b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/backup_queries/q18.sql new file mode 100644 index 0000000..e46a9d4 --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/backup_queries/q18.sql @@ -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. +-- ------------------------------------------------------------ + +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 \ No newline at end of file diff --git a/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/backup_queries/q2.sql b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/backup_queries/q2.sql new file mode 100644 index 0000000..7592e65 --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/backup_queries/q2.sql @@ -0,0 +1,57 @@ +-- ------------------------------------------------------------ +-- 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. +-- ------------------------------------------------------------ + +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.S_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 \ No newline at end of file diff --git a/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/backup_queries/q20.sql b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/backup_queries/q20.sql new file mode 100644 index 0000000..e45fea1 --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/backup_queries/q20.sql @@ -0,0 +1,52 @@ +-- ------------------------------------------------------------ +-- 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. +-- ------------------------------------------------------------ + +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_SUPPKEY), + Q20_TMP3 AS + (SELECT PS_SUPPKEY, + PS_AVAILQTY, + T2.SUM_QUANTITY + FROM PARTSUPP + JOIN Q20_TMP1 T1 ON S_PARTKEY = T1.P_PARTKEY + JOIN Q20_TMP2 T2 ON S_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 \ No newline at end of file diff --git a/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/backup_queries/q21.sql b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/backup_queries/q21.sql new file mode 100644 index 0000000..61b80df --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/backup_queries/q21.sql @@ -0,0 +1,74 @@ +-- ------------------------------------------------------------ +-- 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. +-- ------------------------------------------------------------ + +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, + T3.L_SUPPKEY, + T2.L_ORDERKEY, + COUNT_SUPPKEY, + MAX_SUPPKEY + FROM + (SELECT NS.S_NAME, + T1.L_ORDERKEY, + L.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 \ No newline at end of file diff --git a/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/backup_queries/q22.sql b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/backup_queries/q22.sql new file mode 100644 index 0000000..d859047 --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/backup_queries/q22.sql @@ -0,0 +1,55 @@ +-- ------------------------------------------------------------ +-- 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. +-- ------------------------------------------------------------ + +WITH q22_customer_tmp AS + (SELECT C_ACCTBAL, + C_CUSTKEY, + SUBSTRING(C_PHONE,1,2) AS CNTRYCODE + FROM CUSTOMER + WHERE SUBSTRING(C_PHONE,1,2) = "13" + OR SUBSTRING(C_PHONE,1,2) = "31" + OR SUBSTRING(C_PHONE,1,2) = "23" + OR SUBSTRING(C_PHONE,1,2) = "29" + OR SUBSTRING(C_PHONE,1,2) = "30" + OR SUBSTRING(C_PHONE,1,2) = "18" + OR SUBSTRING(C_PHONE,1,2) = "17"), + AVG AS + (SELECT AVG(C_ACCTBAL) + FROM CUSTOMER + WHERE C_ACCTBAL > 0.0 + AND (SUBSTRING(C_PHONE,1,2) = "13" + OR SUBSTRING(C_PHONE,1,2) = "31" + OR SUBSTRING(C_PHONE,1,2) = "23" + OR SUBSTRING(C_PHONE,1,2) = "29" + OR SUBSTRING(C_PHONE,1,2) = "30" + OR SUBSTRING(C_PHONE,1,2) = "18" + OR SUBSTRING(C_PHONE,1,2) = "17")) +SELECT CNTRYCODE, + COUNT(*) AS NUMCUST, + SUM(C_ACCTBAL) AS TOTACCTBAL +FROM Q22_CUSTOMER_TMP AS CT +WHERE CT.C_ACCTBAL > + (SELECT * + FROM AVG) + AND EXISTS + (SELECT * + FROM ORDERS AS O + WHERE CT.C_CUSTKEY = O.O_CUSTKEY) +GROUP BY CNTRYCODE +ORDER BY CNTRYCODE \ No newline at end of file diff --git a/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/backup_queries/q5.sql b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/backup_queries/q5.sql new file mode 100644 index 0000000..7fc70be --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/backup_queries/q5.sql @@ -0,0 +1,52 @@ +-- ------------------------------------------------------------ +-- 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. +-- ------------------------------------------------------------ + +SELECT O1.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 +ORDER BY REVENUE DESC \ No newline at end of file diff --git a/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/backup_queries/q7_variant.sql b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/backup_queries/q7_variant.sql new file mode 100644 index 0000000..e87213f --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/backup_queries/q7_variant.sql @@ -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. +-- ------------------------------------------------------------ + +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, + YEAR(L3.L_SHIPDATE) AS L_YEAR, + L3.L_EXTENDEDPRICE * (1 - L3.L_DISCOUNT) AS VOLUME + FROM q7_volume_shipping_tmp T + JOIN + (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 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 \ No newline at end of file diff --git a/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/backup_queries/q8.sql b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/backup_queries/q8.sql new file mode 100644 index 0000000..10e6b44 --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/backup_queries/q8.sql @@ -0,0 +1,48 @@ +-- ------------------------------------------------------------ +-- 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. +-- ------------------------------------------------------------ + +SELECT O_YEAR, + SUM(CASE WHEN T.S_NAME = "BRAZIL" THEN T.REVENUE ELSE 0.0 END) / SUM(T.REVENUE) AS MKT_SHARE FROM + (SELECT YEAR(SLNRCOP.O_ORDERDATE) AS O_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 + WHERE SLNRCOP.S_NATIONKEY = N2.N_NATIONKEY) AS T +GROUP BY O_YEAR +ORDER BY O_YEAR \ No newline at end of file diff --git a/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/backup_queries/q9.sql b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/backup_queries/q9.sql new file mode 100644 index 0000000..db73e62 --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/backup_queries/q9.sql @@ -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. +-- ------------------------------------------------------------ + +SELECT NATION, + O_YEAR, + SUM(AMOUNT) AS SUM_PROFIT +FROM + (SELECT L3.N_NAME AS NATION, + 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.S_PARTKEY = L1.L_PARTKEY) L2 ON P.P_NAME LIKE "%GREEN%" + AND P.P_PARTKEY = L2.L_PARTKEY) L3 ON O.O_ORDERKEY = L3.L_ORDERKEY) PROFIT +GROUP BY NATION, + O_YEAR +ORDER BY NATION, + O_YEAR DESC \ No newline at end of file diff --git a/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/conf/core-site-template.xml b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/conf/core-site-template.xml new file mode 100644 index 0000000..ce00991 --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/conf/core-site-template.xml @@ -0,0 +1,23 @@ +<?xml version="1.0" encoding="UTF-8"?> +<?xml-stylesheet type="text/xsl" href="configuration.xsl"?> +<!-- + 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 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. See accompanying LICENSE file. +--> + +<!-- Put site-specific property overrides in this file. --> +<configuration> + <property> + <name>fs.defaultFS</name> + <value>hdfs://{{ groups['cc'][0] }}:9000</value> + </property> +</configuration> diff --git a/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/conf/execute-query.tmpl b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/conf/execute-query.tmpl new file mode 100644 index 0000000..cf5bc36 --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/conf/execute-query.tmpl @@ -0,0 +1,101 @@ +/* + * 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. + */ + +import org.apache.spark.sql.Encoders +import scala.io.Source +import java.io._ +import org.apache.http.impl.client.DefaultHttpClient + +val hdfs_host = "{{ groups['cc'][0] }}:9000" +val hdfs_data_root = "hdfs://" + hdfs_host + "{{ data_dir }}/" +val queries_root = "{{ query_files_root }}/" +val round = {{ test_round }} +val metric = "{{ metric }}" + +// Table Initialization +case class NATION(N_NATIONKEY: Int, N_NAME: String, N_REGIONKEY: Int, N_COMMENT: String) +case class REGION(R_REGIONKEY: Int, R_NAME: String, R_COMMENT: String) +case class PART(P_PARTKEY: Int, P_NAME: String, P_MFGR: String, P_BRAND: String, P_TYPE: String, P_SIZE: Int, P_CONTAINER: String, P_RETAILPRICE: Float, P_COMMENT: String) +case class SUPPLIER (S_SUPPKEY: Int, S_NAME: String, S_ADDRESS: String, S_NATIONKEY: Int, S_PHONE: String, S_ACCTBAL: Float, S_COMMENT: String) +case class PARTSUPP ( S_PARTKEY: Int, PS_SUPPKEY: Int, PS_AVAILQTY: Int, PS_SUPPLYCOST: Float, PS_COMMENT: String) +case class CUSTOMER (C_CUSTKEY: Int, C_NAME : String, C_ADDRESS : String, C_NATIONKEY: Int, C_PHONE : String, C_ACCTBAL: Float , C_MKTSEGMENT : String , C_COMMENT : String) +case class ORDERS (O_ORDERKEY: Int, O_CUSTKEY: Int, O_ORDERSTATUS : String, O_TOTALPRICE: Float, O_ORDERDATE: java.sql.Timestamp, + O_ORDERPRIORITY: String, O_CLERK : String, O_SHIPPRIORITY: Int, O_COMMENT: String) +case class LINEITEM (L_ORDERKEY: Int, L_PARTKEY: Int, L_SUPPKEY: Int, L_LINENUMBER: Int, L_QUANTITY: Float, + L_EXTENDEDPRICE: Float, L_DISCOUNT: Float, L_TAX: Float, L_RETURNFLAG: String, L_LINESTATUS : String, + L_SHIPDATE: java.sql.Timestamp, L_COMMITDATE: java.sql.Timestamp, L_RECEIPTDATE: java.sql.Timestamp, L_SHIPINSTRUCT: String, + L_SHIPMODE: String, L_COMMENT: String) + + +val nation = spark.read.option("delimiter","|").schema(Encoders.product[NATION].schema).csv(hdfs_data_root + "nation.*") +val region = spark.read.option("delimiter","|").schema(Encoders.product[REGION].schema).csv(hdfs_data_root + "region.*") +val part = spark.read.option("delimiter","|").schema(Encoders.product[PART].schema).csv(hdfs_data_root + "part.*") +val supp = spark.read.option("delimiter","|").schema(Encoders.product[SUPPLIER].schema).csv(hdfs_data_root + "supplier.*") +val part_supp = spark.read.option("delimiter","|").schema(Encoders.product[PARTSUPP].schema).csv(hdfs_data_root + "partsupp.*") +val customer = spark.read.option("delimiter","|").schema(Encoders.product[CUSTOMER].schema).csv(hdfs_data_root + "customer.*") +val orders = spark.read.option("delimiter","|").schema(Encoders.product[ORDERS].schema).csv(hdfs_data_root + "orders.*") +val lineitem = spark.read.option("delimiter","|").schema(Encoders.product[LINEITEM].schema).csv(hdfs_data_root + "lineitem.*") + + +nation.createOrReplaceTempView("NATION") +region.createOrReplaceTempView("REGION") +part.createOrReplaceTempView("PART") +supp.createOrReplaceTempView("SUPPLIER") +part_supp.createOrReplaceTempView("PARTSUPP") +customer.createOrReplaceTempView("CUSTOMER") +orders.createOrReplaceTempView("ORDERS") +lineitem.createOrReplaceTempView("LINEITEM") + +spark.sqlContext.cacheTable("NATION") +spark.sqlContext.cacheTable("REGION") +spark.sqlContext.cacheTable("PART") +spark.sqlContext.cacheTable("SUPPLIER") +spark.sqlContext.cacheTable("PARTSUPP") +spark.sqlContext.cacheTable("CUSTOMER") +spark.sqlContext.cacheTable("ORDERS") +spark.sqlContext.cacheTable("LINEITEM") + +// Execute Query +val queries_dir = new File(queries_root) +val etime = collection.mutable.Map[String, Float]() +for (i <- 0 to round) { + for (query_file <- queries_dir.listFiles()) { + print("Processing Query "+ query_file) + val file_name = query_file.getName() + val queries = Source.fromFile(query_file) + val t0 = System.nanoTime() + var query = "" + queries.getLines.foreach { line => query += (line + "\n")} + spark.sql(query).count() + val t1 = System.nanoTime() + val elapsed = (t1 - t0) / 1000000000.0f + if (i > 0) { + if (!etime.contains(file_name)) { + etime(file_name) = 0 + } + etime(file_name) += elapsed + } + } +} + +// Write result +val writer = new PrintWriter(new File("{{ result_file }}")) +for ((k, v) <- etime) writer.print("'{\"group\": \""+ k.split('.')(0) + ".sqlpp\", \"metric\": \"" + metric + "\", \"value\": "+ v / round +"}'\n") +writer.close() +System.exit(0) \ No newline at end of file diff --git a/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/conf/hdfs-site-template.xml b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/conf/hdfs-site-template.xml new file mode 100644 index 0000000..0cbfdef --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/conf/hdfs-site-template.xml @@ -0,0 +1,24 @@ +<?xml version="1.0" encoding="UTF-8"?> +<?xml-stylesheet type="text/xsl" href="configuration.xsl"?> +<!-- + 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 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. See accompanying LICENSE file. +--> + +<!-- Put site-specific property overrides in this file. --> + +<configuration> + <property> + <name>dfs.replication</name> + <value>{{ dfs_replication }}</value> + </property> +</configuration> diff --git a/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/queries/q1.sql b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/queries/q1.sql new file mode 100644 index 0000000..aadbb55 --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/queries/q1.sql @@ -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. +-- ------------------------------------------------------------ + +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(*) AS count_order +FROM LINEITEM AS l +WHERE l.L_SHIPDATE <= "1998-09-02" +GROUP BY l.L_RETURNFLAG, + l.L_LINESTATUS +ORDER BY l.L_RETURNFLAG, + l.L_LINESTATUS \ No newline at end of file diff --git a/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/queries/q10.sql b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/queries/q10.sql new file mode 100644 index 0000000..c322b34 --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/queries/q10.sql @@ -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. +-- ------------------------------------------------------------ + +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 \ No newline at end of file diff --git a/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/queries/q11.sql b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/queries/q11.sql new file mode 100644 index 0000000..f43db61 --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/queries/q11.sql @@ -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. +-- ------------------------------------------------------------ + +WITH s1 AS + (SELECT 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) +SELECT S_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.S_PARTKEY HAVING PART_VALUE > + (SELECT * + FROM s1) * 0.0001000 +ORDER BY PART_VALUE DESC \ No newline at end of file diff --git a/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/queries/q12.sql b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/queries/q12.sql new file mode 100644 index 0000000..04d7e0c --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/queries/q12.sql @@ -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. +-- ------------------------------------------------------------ + +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 \ No newline at end of file diff --git a/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/queries/q13.sql b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/queries/q13.sql new file mode 100644 index 0000000..4590598 --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/queries/q13.sql @@ -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. +-- ------------------------------------------------------------ + +SELECT C_COUNT, COUNT(*) AS CUSTDIST +FROM ( + SELECT C_CUSTKEY, SUM(O_ORDERKEY_COUNT) AS C_COUNT + FROM ( + SELECT C.C_CUSTKEY, COUNT(O.O_ORDERKEY) AS O_ORDERKEY_COUNT + FROM (CUSTOMER C LEFT OUTER JOIN ORDERS O) + WHERE C.C_CUSTKEY = O.O_CUSTKEY AND O.O_COMMENT NOT LIKE "%SPECIAL%REQUESTS%" + GROUP BY C.C_CUSTKEY + ) CO + GROUP BY C_CUSTKEY +) GCO +GROUP BY C_COUNT +ORDER BY CUSTDIST DESC,C_COUNT DESC \ No newline at end of file diff --git a/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/queries/q14.sql b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/queries/q14.sql new file mode 100644 index 0000000..60a5cf4 --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/queries/q14.sql @@ -0,0 +1,25 @@ +-- ------------------------------------------------------------ +-- 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. +-- ------------------------------------------------------------ + +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" \ No newline at end of file diff --git a/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/queries/q15.sql b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/queries/q15.sql new file mode 100644 index 0000000..3a2de09 --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/queries/q15.sql @@ -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. +-- ------------------------------------------------------------ + +WITH REVENUE AS + (SELECT L.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), + m AS + (SELECT MAX(R2.TOTAL_REVENUE) + FROM REVENUE R2) +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 < + (SELECT * + FROM m) + 0.000000001 + AND R.TOTAL_REVENUE > + (SELECT * + FROM m) - 0.000000001 \ No newline at end of file diff --git a/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/queries/q16.sql b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/queries/q16.sql new file mode 100644 index 0000000..aea9188 --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/queries/q16.sql @@ -0,0 +1,66 @@ +-- ------------------------------------------------------------ +-- 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. +-- ------------------------------------------------------------ + +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.S_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 \ No newline at end of file diff --git a/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/queries/q17.sql b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/queries/q17.sql new file mode 100644 index 0000000..c3894fe --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/queries/q17.sql @@ -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. +-- ------------------------------------------------------------ + +WITH tmp AS + (SELECT L_PARTKEY T_PARTKEY, + 0.2 * AVG(L_QUANTITY) T_AVG_QUANTITY + FROM LINEITEM + GROUP BY L_PARTKEY) +SELECT * +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 \ No newline at end of file diff --git a/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/queries/q19.sql b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/queries/q19.sql new file mode 100644 index 0000000..5cacbdb --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/queries/q19.sql @@ -0,0 +1,49 @@ +-- ------------------------------------------------------------ +-- 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. +-- ------------------------------------------------------------ + +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 P.P_CONTAINER REGEXP "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 P.P_CONTAINER REGEXP "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 P.P_CONTAINER REGEXP "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) \ No newline at end of file diff --git a/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/queries/q3.sql b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/queries/q3.sql new file mode 100644 index 0000000..aadbb55 --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/queries/q3.sql @@ -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. +-- ------------------------------------------------------------ + +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(*) AS count_order +FROM LINEITEM AS l +WHERE l.L_SHIPDATE <= "1998-09-02" +GROUP BY l.L_RETURNFLAG, + l.L_LINESTATUS +ORDER BY l.L_RETURNFLAG, + l.L_LINESTATUS \ No newline at end of file diff --git a/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/queries/q4.sql b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/queries/q4.sql new file mode 100644 index 0000000..1af2b2c --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/queries/q4.sql @@ -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. +-- ------------------------------------------------------------ + +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(*) 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 \ No newline at end of file diff --git a/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/queries/q6.sql b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/queries/q6.sql new file mode 100644 index 0000000..d1dfb05 --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/queries/q6.sql @@ -0,0 +1,26 @@ +-- ------------------------------------------------------------ +-- 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. +-- ------------------------------------------------------------ + +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 \ No newline at end of file diff --git a/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/queries/q7.sql b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/queries/q7.sql new file mode 100644 index 0000000..dcf4e63 --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/queries/q7.sql @@ -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. +-- ------------------------------------------------------------ + +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, + 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 \ No newline at end of file diff --git a/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/run_sparksql.sh b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/run_sparksql.sh new file mode 100755 index 0000000..9b14f67 --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/run_sparksql.sh @@ -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. +# ------------------------------------------------------------ + +#!/bin/bash + +pushd `dirname $0` > /dev/null +SCRIPT_PATH=`pwd -P` +popd > /dev/null +export ANSIBLE_HOST_KEY_CHECKING=false +export ANSIBLE_SSH_RETRIES=3 + +if [[ "$1" = /* ]]; then + INVENTORY=$1 +else + INVENTORY=$SCRIPT_PATH/$1 +fi + +SYSTEM_NAME=$2 + +if [ -z "$SYSTEM_NAME" ]; +then + SYSTEM_NAME="SparkSQL" +fi + +# Checks the existence of the inventory file. +if [ ! -f "$INVENTORY" ]; +then + echo "The inventory file \"$INVENTORY\" does not exist." + exit 1 +fi +# Load data +ansible-playbook -i $INVENTORY $SCRIPT_PATH/../../benchmarks/tpch/gen/gen.yml +# Configure HDFS +ansible-playbook -i $INVENTORY $SCRIPT_PATH/ansible/install_hdfs.yml +ansible-playbook -i $INVENTORY $SCRIPT_PATH/ansible/start_hdfs.yml +# Configure Spark +ansible-playbook -i $INVENTORY $SCRIPT_PATH/ansible/install_spark.yml +ansible-playbook -i $INVENTORY $SCRIPT_PATH/ansible/start_spark.yml +ansible-playbook -i $INVENTORY $SCRIPT_PATH/ansible/load_tpch.yml +# Execute queries +ansible-playbook -i $INVENTORY --extra-vars="metric='${SYSTEM_NAME}'" $SCRIPT_PATH/ansible/prepare_queries.yml +ansible-playbook -i $INVENTORY $SCRIPT_PATH/ansible/execute_queries.yml \ No newline at end of file diff --git a/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/start_aws_SparkSQL.sh b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/start_aws_SparkSQL.sh new file mode 100644 index 0000000..3ffabfd --- /dev/null +++ b/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/start_aws_SparkSQL.sh @@ -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. +# ------------------------------------------------------------ + +#!bin/sh +pushd `dirname $0` > /dev/null +SCRIPT_PATH=`pwd -P` +popd > /dev/null + +export ANSIBLE_HOST_KEY_CHECKING=false +if [[ "$1" = /* ]]; then + AWS_SETTING=$1 +else + AWS_SETTING=`pwd`/$1 +fi + +INVENTORY=$SCRIPT_PATH/conf/inventory + +ansible-playbook -i "localhost," --extra-vars="aws_setting=${AWS_SETTING}" $SCRIPT_PATH/ansible/create_aws_cluster.yml +ansible-playbook -i $INVENTORY $SCRIPT_PATH/ansible/instance_init.yml diff --git a/asterixdb/asterix-server/src/main/opt/aws/bin/terminate.sh b/asterixdb/asterix-server/src/main/opt/aws/bin/terminate.sh index 58ff965..e663208 100755 --- a/asterixdb/asterix-server/src/main/opt/aws/bin/terminate.sh +++ b/asterixdb/asterix-server/src/main/opt/aws/bin/terminate.sh @@ -24,5 +24,13 @@ popd > /dev/null AWS_PATH=`dirname "${SCRIPT_PATH}"` -# Terminates an AWS cluster. -ansible-playbook -i "localhost," $AWS_PATH/yaml/aws_terminate.yml +# Terminates an AWS cluster + +if [ -z "$1" ] +then + # without name parameter. + ansible-playbook -i "localhost," $AWS_PATH/yaml/aws_terminate.yml +else + # with name parameter + ansible-playbook -i "localhost," --extra-vars="tag=${1}" $AWS_PATH/yaml/aws_terminate.yml +fi \ No newline at end of file -- To view, visit https://asterix-gerrit.ics.uci.edu/1879 To unsubscribe, visit https://asterix-gerrit.ics.uci.edu/settings Gerrit-MessageType: merged Gerrit-Change-Id: Ic9ff8efa0be71bde67190ba6f9fbd647c7799084 Gerrit-PatchSet: 57 Gerrit-Project: asterixdb Gerrit-Branch: master Gerrit-Owner: Xikui Wang <xkk...@gmail.com> Gerrit-Reviewer: Anon. E. Moose #1000171 Gerrit-Reviewer: Jenkins <jenk...@fulliautomatix.ics.uci.edu> Gerrit-Reviewer: Xikui Wang <xkk...@gmail.com> Gerrit-Reviewer: Yingyi Bu <buyin...@gmail.com>
