Repository: asterixdb Updated Branches: refs/heads/master dc77522bf -> 30ad75634
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/30ad7563/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/queries/q3.sql ---------------------------------------------------------------------- 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 http://git-wip-us.apache.org/repos/asf/asterixdb/blob/30ad7563/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/queries/q4.sql ---------------------------------------------------------------------- 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 http://git-wip-us.apache.org/repos/asf/asterixdb/blob/30ad7563/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/queries/q6.sql ---------------------------------------------------------------------- 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 http://git-wip-us.apache.org/repos/asf/asterixdb/blob/30ad7563/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/queries/q7.sql ---------------------------------------------------------------------- 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 http://git-wip-us.apache.org/repos/asf/asterixdb/blob/30ad7563/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/run_sparksql.sh ---------------------------------------------------------------------- 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 http://git-wip-us.apache.org/repos/asf/asterixdb/blob/30ad7563/asterixdb/asterix-benchmark/src/main/resources/others/SparkSQL/start_aws_SparkSQL.sh ---------------------------------------------------------------------- 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 http://git-wip-us.apache.org/repos/asf/asterixdb/blob/30ad7563/asterixdb/asterix-server/src/main/opt/aws/bin/terminate.sh ---------------------------------------------------------------------- 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 @@ SCRIPT_PATH=`pwd -P` 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