You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@impala.apache.org by ta...@apache.org on 2016/10/25 20:16:05 UTC
[18/33] incubator-impala git commit: IMPALA-3739: Enable stress tests
on Kudu
IMPALA-3739: Enable stress tests on Kudu
This commit modifies the stress test framework to run TPC-H and TPC-DS
workloads against Kudu. The follwing changes are included in this
commit:
1. Created template files with DDL and DML statements for loading TPC-H and
TPC-DS data in Kudu
2. Created a script (load-tpc-kudu.py) to load data in Kudu. The
script is invoked by the stress test runner to load test data in an
existing Impala/Kudu cluster (both local and CM-managed clusters are
supported).
3. Created SQL files with TPC-DS queries to be executed in Kudu. SQL
files with TPC-H queries for Kudu were added in a previous patch.
4. Modified the stress test runner to take additional parameters
specific to Kudu (e.g. kudu master addr)
The stress test runner for Kudu was tested on EC2 clusters for both TPC-H
and TPC-DS workloads.
Missing functionality:
* No CRUD operations in the existing TPC-H/TPC-DS workloads for Kudu.
* Not all supported TPC-DS queries are included. Currently, only the
TPC-DS queries from the testdata/workloads/tpcds/queries directory
were modified to run against Kudu.
Change-Id: I3c9fc3dae24b761f031ee8e014bd611a49029d34
Reviewed-on: http://gerrit.cloudera.org:8080/4327
Reviewed-by: Dimitris Tsirogiannis <dt...@cloudera.com>
Tested-by: Internal Jenkins
Project: http://git-wip-us.apache.org/repos/asf/incubator-impala/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-impala/commit/8a49ceaa
Tree: http://git-wip-us.apache.org/repos/asf/incubator-impala/tree/8a49ceaa
Diff: http://git-wip-us.apache.org/repos/asf/incubator-impala/diff/8a49ceaa
Branch: refs/heads/hadoop-next
Commit: 8a49ceaae532163f17836b1050b639329424ee5c
Parents: 041fa6d
Author: Dimitris Tsirogiannis <dt...@cloudera.com>
Authored: Wed Aug 24 10:20:48 2016 -0700
Committer: Internal Jenkins <cl...@gerrit.cloudera.org>
Committed: Fri Oct 21 11:01:37 2016 +0000
----------------------------------------------------------------------
testdata/bin/load-tpc-kudu.py | 119 +++
.../functional/functional_schema_template.sql | 13 +
testdata/datasets/tpcds/tpcds_kudu_template.sql | 945 +++++++++++++++++++
testdata/datasets/tpch/tpch_kudu_template.sql | 198 ++++
.../workloads/tpcds/queries/tpcds-kudu-q19.test | 39 +
.../workloads/tpcds/queries/tpcds-kudu-q27.test | 36 +
.../workloads/tpcds/queries/tpcds-kudu-q3.test | 32 +
.../workloads/tpcds/queries/tpcds-kudu-q34.test | 47 +
.../workloads/tpcds/queries/tpcds-kudu-q42.test | 29 +
.../workloads/tpcds/queries/tpcds-kudu-q43.test | 37 +
.../workloads/tpcds/queries/tpcds-kudu-q46.test | 81 ++
.../workloads/tpcds/queries/tpcds-kudu-q47.test | 53 ++
.../workloads/tpcds/queries/tpcds-kudu-q52.test | 28 +
.../workloads/tpcds/queries/tpcds-kudu-q53.test | 38 +
.../workloads/tpcds/queries/tpcds-kudu-q55.test | 25 +
.../workloads/tpcds/queries/tpcds-kudu-q59.test | 84 ++
.../workloads/tpcds/queries/tpcds-kudu-q6.test | 28 +
.../workloads/tpcds/queries/tpcds-kudu-q61.test | 43 +
.../workloads/tpcds/queries/tpcds-kudu-q63.test | 38 +
.../workloads/tpcds/queries/tpcds-kudu-q65.test | 63 ++
.../workloads/tpcds/queries/tpcds-kudu-q68.test | 62 ++
.../workloads/tpcds/queries/tpcds-kudu-q7.test | 32 +
.../workloads/tpcds/queries/tpcds-kudu-q73.test | 51 +
.../workloads/tpcds/queries/tpcds-kudu-q79.test | 61 ++
.../workloads/tpcds/queries/tpcds-kudu-q8.test | 71 ++
.../workloads/tpcds/queries/tpcds-kudu-q88.test | 93 ++
.../workloads/tpcds/queries/tpcds-kudu-q89.test | 33 +
.../workloads/tpcds/queries/tpcds-kudu-q96.test | 17 +
.../workloads/tpcds/queries/tpcds-kudu-q98.test | 34 +
tests/comparison/db_connection.py | 5 +
tests/stress/concurrent_select.py | 30 +-
31 files changed, 2459 insertions(+), 6 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/8a49ceaa/testdata/bin/load-tpc-kudu.py
----------------------------------------------------------------------
diff --git a/testdata/bin/load-tpc-kudu.py b/testdata/bin/load-tpc-kudu.py
new file mode 100755
index 0000000..01de79e
--- /dev/null
+++ b/testdata/bin/load-tpc-kudu.py
@@ -0,0 +1,119 @@
+#!/usr/bin/env impala-python
+#
+# 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.
+
+# Script to load TPC-[H|DS] data in a Kudu cluster.
+#
+# Kudu tables are created in the specified 'target-db' using the existing HDFS tables
+# from 'source-db'.
+
+import logging
+import os
+import sqlparse
+import sys
+
+LOG = logging.getLogger(os.path.splitext(os.path.basename(__file__))[0])
+
+source_db = None
+target_db = None
+kudu_master = None
+verbose = False
+buckets = None
+workload = None
+
+tpch_tables = ['customer', 'lineitem', 'nation', 'orders', 'part', 'partsupp', 'region',
+ 'revenue', 'supplier']
+
+tpcds_tables = ['call_center', 'catalog_page', 'catalog_returns', 'catalog_sales',
+ 'customer', 'customer_address', 'customer_demographics', 'date_dim',
+ 'household_demographics', 'income_band', 'inventory', 'item', 'promotion',
+ 'reason', 'ship_mode', 'store', 'store_returns', 'store_sales', 'time_dim',
+ 'warehouse', 'web_page', 'web_returns', 'web_sales', 'web_site']
+
+def clean_data():
+ """Drop the specified 'target_db' and all its tables"""
+ with cluster.impala.cursor() as impala:
+ tbls_to_clean = tpch_tables if workload.lower() == 'tpch' else tpcds_tables
+ # TODO: Replace with DROP DATABASE CASCADE when it is supported for Kudu tables
+ for table_name in tbls_to_clean:
+ impala.execute("drop table if exists {}.{}".format(target_db, table_name))
+ impala.drop_db_if_exists(target_db)
+
+def load_data():
+ sql_params = {
+ "source_db_name": source_db,
+ "target_db_name": target_db,
+ "kudu_master": kudu_master,
+ "buckets": buckets}
+
+ sql_file_path = get_test_file_path(workload)
+ with open(sql_file_path, "r") as test:
+ queries = sqlparse.split(test.read())
+
+ with cluster.impala.cursor() as impala:
+ impala.create_db_if_not_exists(target_db)
+ impala.execute("USE %s" % target_db)
+ for query in queries:
+ query = sqlparse.format(query.rstrip(';'), strip_comments=True)
+ query_str = query.format(**sql_params)
+ if (len(query_str)) == 0: continue
+ if verbose: print query_str
+ impala.execute(query_str)
+
+def get_test_file_path(workload):
+ if "IMPALA_HOME" not in os.environ:
+ raise Exception("IMPALA_HOME must be set")
+ sql_file_path = os.path.join(os.environ["IMPALA_HOME"], "testdata", "datasets",
+ workload, "%s_kudu_template.sql" % (workload))
+ return sql_file_path
+
+if __name__ == "__main__":
+ from argparse import ArgumentDefaultsHelpFormatter, ArgumentParser
+ import tests.comparison.cli_options as cli_options
+
+ parser = ArgumentParser(formatter_class=ArgumentDefaultsHelpFormatter)
+ cli_options.add_logging_options(parser)
+ cli_options.add_cluster_options(parser)
+ parser.add_argument("-s", "--source-db", required=True,
+ help="Source DB to load data from.")
+ parser.add_argument("-t", "--target-db", required=True,
+ help="Target DB to load data to.")
+ parser.add_argument("-w", "--workload", choices=['tpch', 'tpcds'],
+ required=True)
+ parser.add_argument("--kudu_master", required=True,
+ help="Address or host name of Kudu master")
+ # TODO: Automatically set #buckets as a function of cluster nodes and/or
+ # scale
+ parser.add_argument("-b", "--buckets", default="9",
+ help="Number of buckets to partition Kudu tables (only for hash-based).")
+ parser.add_argument("-v", "--verbose", action='store_true',
+ help="Print the executed statements.")
+ parser.add_argument("--clean", action='store_true',
+ help="Drop all tables in the speficied target database.")
+ args = parser.parse_args()
+
+ cli_options.configure_logging(args.log_level, debug_log_file=args.debug_log_file)
+ cluster = cli_options.create_cluster(args)
+ source_db = args.source_db
+ target_db = args.target_db
+ buckets = args.buckets
+ kudu_master = args.kudu_master
+ workload = args.workload
+ verbose = args.verbose
+ if args.clean: clean_data()
+ load_data()
http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/8a49ceaa/testdata/datasets/functional/functional_schema_template.sql
----------------------------------------------------------------------
diff --git a/testdata/datasets/functional/functional_schema_template.sql b/testdata/datasets/functional/functional_schema_template.sql
index 76e1427..7b929b7 100644
--- a/testdata/datasets/functional/functional_schema_template.sql
+++ b/testdata/datasets/functional/functional_schema_template.sql
@@ -77,6 +77,7 @@ LOAD DATA LOCAL INPATH '{impala_home}/testdata/target/AllTypes/101001.txt' OVERW
LOAD DATA LOCAL INPATH '{impala_home}/testdata/target/AllTypes/101101.txt' OVERWRITE INTO TABLE {db_name}{db_suffix}.{table_name} PARTITION(year=2010, month=11);
LOAD DATA LOCAL INPATH '{impala_home}/testdata/target/AllTypes/101201.txt' OVERWRITE INTO TABLE {db_name}{db_suffix}.{table_name} PARTITION(year=2010, month=12);
---- CREATE_KUDU
+DROP TABLE IF EXISTS {db_name}{db_suffix}.{table_name};
CREATE TABLE {db_name}{db_suffix}.{table_name} (
id INT PRIMARY KEY,
bool_col BOOLEAN,
@@ -154,6 +155,7 @@ LOAD DATA LOCAL INPATH '{impala_home}/testdata/target/AllTypesSmall/090201.txt'
LOAD DATA LOCAL INPATH '{impala_home}/testdata/target/AllTypesSmall/090301.txt' OVERWRITE INTO TABLE {db_name}{db_suffix}.{table_name} PARTITION(year=2009, month=3);
LOAD DATA LOCAL INPATH '{impala_home}/testdata/target/AllTypesSmall/090401.txt' OVERWRITE INTO TABLE {db_name}{db_suffix}.{table_name} PARTITION(year=2009, month=4);
---- CREATE_KUDU
+DROP TABLE IF EXISTS {db_name}{db_suffix}.{table_name};
CREATE TABLE {db_name}{db_suffix}.{table_name} (
id INT PRIMARY KEY,
bool_col BOOLEAN,
@@ -212,6 +214,7 @@ LOAD DATA LOCAL INPATH '{impala_home}/testdata/target/AllTypesTiny/090201.txt' O
LOAD DATA LOCAL INPATH '{impala_home}/testdata/target/AllTypesTiny/090301.txt' OVERWRITE INTO TABLE {db_name}{db_suffix}.{table_name} PARTITION(year=2009, month=3);
LOAD DATA LOCAL INPATH '{impala_home}/testdata/target/AllTypesTiny/090401.txt' OVERWRITE INTO TABLE {db_name}{db_suffix}.{table_name} PARTITION(year=2009, month=4);
---- CREATE_KUDU
+DROP TABLE IF EXISTS {db_name}{db_suffix}.{table_name};
CREATE TABLE {db_name}{db_suffix}.{table_name} (
id INT PRIMARY KEY,
bool_col BOOLEAN,
@@ -625,6 +628,7 @@ LOAD DATA LOCAL INPATH '{impala_home}/testdata/target/AllTypesAggNoNulls/100108.
LOAD DATA LOCAL INPATH '{impala_home}/testdata/target/AllTypesAggNoNulls/100109.txt' OVERWRITE INTO TABLE {db_name}{db_suffix}.{table_name} PARTITION(year=2010, month=1, day=9);
LOAD DATA LOCAL INPATH '{impala_home}/testdata/target/AllTypesAggNoNulls/100110.txt' OVERWRITE INTO TABLE {db_name}{db_suffix}.{table_name} PARTITION(year=2010, month=1, day=10);
---- CREATE_KUDU
+DROP TABLE IF EXISTS {db_name}{db_suffix}.{table_name};
CREATE TABLE {db_name}{db_suffix}.{table_name} (
id INT PRIMARY KEY,
bool_col BOOLEAN,
@@ -756,6 +760,7 @@ zip int
---- ROW_FORMAT
delimited fields terminated by ',' escaped by '\\'
---- CREATE_KUDU
+DROP TABLE IF EXISTS {db_name}{db_suffix}.{table_name};
create table {db_name}{db_suffix}.{table_name} (
id bigint primary key,
name string,
@@ -778,6 +783,7 @@ INSERT OVERWRITE TABLE {db_name}{db_suffix}.{table_name} SELECT * FROM {db_name}
---- LOAD
LOAD DATA LOCAL INPATH '{impala_home}/testdata/DimTbl/data.csv' OVERWRITE INTO TABLE {db_name}{db_suffix}.{table_name};
---- CREATE_KUDU
+DROP TABLE IF EXISTS {db_name}{db_suffix}.{table_name};
create table {db_name}{db_suffix}.{table_name} (
id bigint primary key,
name string,
@@ -801,6 +807,7 @@ INSERT OVERWRITE TABLE {db_name}{db_suffix}.{table_name} SELECT * FROM {db_name}
---- LOAD
LOAD DATA LOCAL INPATH '{impala_home}/testdata/JoinTbl/data.csv' OVERWRITE INTO TABLE {db_name}{db_suffix}.{table_name};
---- CREATE_KUDU
+DROP TABLE IF EXISTS {db_name}{db_suffix}.{table_name};
create table {db_name}{db_suffix}.{table_name} (
test_id bigint,
test_name string,
@@ -1142,6 +1149,7 @@ f2 int
---- COLUMNS
field string
---- CREATE_KUDU
+DROP TABLE IF EXISTS {db_name}{db_suffix}.{table_name};
CREATE TABLE {db_name}{db_suffix}.{table_name} (
field STRING PRIMARY KEY,
f2 INT
@@ -1248,6 +1256,7 @@ INSERT OVERWRITE TABLE {db_name}{db_suffix}.{table_name} SELECT * FROM {db_name}
---- LOAD
LOAD DATA LOCAL INPATH '{impala_home}/testdata/TinyTable/data.csv' OVERWRITE INTO TABLE {db_name}{db_suffix}.{table_name};
---- CREATE_KUDU
+DROP TABLE IF EXISTS {db_name}{db_suffix}.{table_name};
create table {db_name}{db_suffix}.{table_name} (
a string primary key,
b string
@@ -1267,6 +1276,7 @@ INSERT OVERWRITE TABLE {db_name}{db_suffix}.{table_name} SELECT * FROM {db_name}
---- LOAD
LOAD DATA LOCAL INPATH '{impala_home}/testdata/TinyIntTable/data.csv' OVERWRITE INTO TABLE {db_name}{db_suffix}.{table_name};
---- CREATE_KUDU
+DROP TABLE IF EXISTS {db_name}{db_suffix}.{table_name};
create table {db_name}{db_suffix}.{table_name} (
int_col int primary key
)
@@ -1292,6 +1302,7 @@ INSERT OVERWRITE TABLE {db_name}{db_suffix}.{table_name} select * from functiona
LOAD DATA LOCAL INPATH '{impala_home}/testdata/NullTable/data.csv'
OVERWRITE INTO TABLE {db_name}{db_suffix}.{table_name};
---- CREATE_KUDU
+DROP TABLE IF EXISTS {db_name}{db_suffix}.{table_name};
create table {db_name}{db_suffix}.{table_name} (
a string primary key, b string, c string, d int, e double, f string, g string
)
@@ -1317,6 +1328,7 @@ INSERT OVERWRITE TABLE {db_name}{db_suffix}.{table_name} select * from functiona
LOAD DATA LOCAL INPATH '{impala_home}/testdata/NullTable/data.csv'
OVERWRITE INTO TABLE {db_name}{db_suffix}.{table_name};
---- CREATE_KUDU
+DROP TABLE IF EXISTS {db_name}{db_suffix}.{table_name};
create table {db_name}{db_suffix}.{table_name} (
a string primary key, b string, c string, d int, e double, f string, g string
)
@@ -1391,6 +1403,7 @@ INSERT OVERWRITE TABLE {db_name}{db_suffix}.{table_name} SELECT * FROM {db_name}
---- LOAD
LOAD DATA LOCAL INPATH '{impala_home}/testdata/ImpalaDemoDataset/DEC_00_SF3_P077_with_ann_noheader.csv' OVERWRITE INTO TABLE {db_name}{db_suffix}.{table_name};
---- CREATE_KUDU
+DROP TABLE IF EXISTS {db_name}{db_suffix}.{table_name};
create table {db_name}{db_suffix}.{table_name} (
id string,
zip string,
http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/8a49ceaa/testdata/datasets/tpcds/tpcds_kudu_template.sql
----------------------------------------------------------------------
diff --git a/testdata/datasets/tpcds/tpcds_kudu_template.sql b/testdata/datasets/tpcds/tpcds_kudu_template.sql
new file mode 100644
index 0000000..dd65fef
--- /dev/null
+++ b/testdata/datasets/tpcds/tpcds_kudu_template.sql
@@ -0,0 +1,945 @@
+---- Template SQL statements to create and load TPCDS tables in KUDU.
+---- TODO: Change to the new syntax for CREATE TABLE statements (IMPALA-3719)
+---- TODO: Use range partitioning for some tables
+---- TODO: Fix the primary key column order
+---- TODO: Use different number of buckets for fact and dimension tables
+
+---- STORE_SALES
+CREATE TABLE IF NOT EXISTS {target_db_name}.store_sales (
+ ss_ticket_number BIGINT,
+ ss_item_sk BIGINT,
+ ss_sold_date_sk BIGINT,
+ ss_sold_time_sk BIGINT,
+ ss_customer_sk BIGINT,
+ ss_cdemo_sk BIGINT,
+ ss_hdemo_sk BIGINT,
+ ss_addr_sk BIGINT,
+ ss_store_sk BIGINT,
+ ss_promo_sk BIGINT,
+ ss_quantity BIGINT,
+ ss_wholesale_cost DOUBLE,
+ ss_list_price DOUBLE,
+ ss_sales_price DOUBLE,
+ ss_ext_discount_amt DOUBLE,
+ ss_ext_sales_price DOUBLE,
+ ss_ext_wholesale_cost DOUBLE,
+ ss_ext_list_price DOUBLE,
+ ss_ext_tax DOUBLE,
+ ss_coupon_amt DOUBLE,
+ ss_net_paid DOUBLE,
+ ss_net_paid_inc_tax DOUBLE,
+ ss_net_profit DOUBLE
+)
+DISTRIBUTE BY HASH (ss_ticket_number,ss_item_sk) INTO {buckets} BUCKETS
+TBLPROPERTIES(
+'storage_handler' = 'com.cloudera.kudu.hive.KuduStorageHandler',
+'kudu.table_name' = '{target_db_name}_store_sales',
+'kudu.master_addresses' = '{kudu_master}:7051',
+'kudu.key_columns' = 'ss_ticket_number, ss_item_sk'
+);
+
+INSERT INTO {target_db_name}.store_sales
+SELECT
+ ss_ticket_number,
+ ss_item_sk,
+ ss_sold_date_sk,
+ ss_sold_time_sk,
+ ss_customer_sk,
+ ss_cdemo_sk,
+ ss_hdemo_sk,
+ ss_addr_sk,
+ ss_store_sk,
+ ss_promo_sk,
+ ss_quantity,
+ ss_wholesale_cost,
+ ss_list_price,
+ ss_sales_price,
+ ss_ext_discount_amt,
+ ss_ext_sales_price,
+ ss_ext_wholesale_cost,
+ ss_ext_list_price,
+ ss_ext_tax,
+ ss_coupon_amt,
+ ss_net_paid,
+ ss_net_paid_inc_tax,ss_net_profit
+FROM {source_db_name}.store_sales;
+
+---- WEB_SALES
+CREATE TABLE IF NOT EXISTS {target_db_name}.web_sales (
+ ws_order_number BIGINT,
+ ws_item_sk BIGINT,
+ ws_sold_date_sk BIGINT,
+ ws_sold_time_sk BIGINT,
+ ws_ship_date_sk BIGINT,
+ ws_bill_customer_sk BIGINT,
+ ws_bill_cdemo_sk BIGINT,
+ ws_bill_hdemo_sk BIGINT,
+ ws_bill_addr_sk BIGINT,
+ ws_ship_customer_sk BIGINT,
+ ws_ship_cdemo_sk BIGINT,
+ ws_ship_hdemo_sk BIGINT,
+ ws_ship_addr_sk BIGINT,
+ ws_web_page_sk BIGINT,
+ ws_web_site_sk BIGINT,
+ ws_ship_mode_sk BIGINT,
+ ws_warehouse_sk BIGINT,
+ ws_promo_sk BIGINT,
+ ws_quantity BIGINT,
+ ws_wholesale_cost DOUBLE,
+ ws_list_price DOUBLE,
+ ws_sales_price DOUBLE,
+ ws_ext_discount_amt DOUBLE,
+ ws_ext_sales_price DOUBLE,
+ ws_ext_wholesale_cost DOUBLE,
+ ws_ext_list_price DOUBLE,
+ ws_ext_tax DOUBLE,
+ ws_coupon_amt DOUBLE,
+ ws_ext_ship_cost DOUBLE,
+ ws_net_paid DOUBLE,
+ ws_net_paid_inc_tax DOUBLE,
+ ws_net_paid_inc_ship DOUBLE,
+ ws_net_paid_inc_ship_tax DOUBLE,
+ ws_net_profit DOUBLE
+)
+DISTRIBUTE BY HASH (ws_order_number,ws_item_sk) INTO {buckets} BUCKETS
+TBLPROPERTIES(
+'storage_handler' = 'com.cloudera.kudu.hive.KuduStorageHandler',
+'kudu.table_name' = '{target_db_name}_web_sales',
+'kudu.master_addresses' = '{kudu_master}:7051',
+'kudu.key_columns' = 'ws_order_number, ws_item_sk'
+);
+
+INSERT INTO {target_db_name}.web_sales
+SELECT
+ ws_order_number,
+ ws_item_sk,
+ ws_sold_date_sk,
+ ws_sold_time_sk,
+ ws_ship_date_sk,
+ ws_bill_customer_sk,
+ ws_bill_cdemo_sk,
+ ws_bill_hdemo_sk,
+ ws_bill_addr_sk,
+ ws_ship_customer_sk,
+ ws_ship_cdemo_sk,
+ ws_ship_hdemo_sk,
+ ws_ship_addr_sk,
+ ws_web_page_sk,
+ ws_web_site_sk,
+ ws_ship_mode_sk,
+ ws_warehouse_sk,
+ ws_promo_sk,
+ ws_quantity,
+ ws_wholesale_cost,
+ ws_list_price,
+ ws_sales_price,
+ ws_ext_discount_amt,
+ ws_ext_sales_price,
+ ws_ext_wholesale_cost,
+ ws_ext_list_price,
+ ws_ext_tax,
+ ws_coupon_amt,
+ ws_ext_ship_cost,
+ ws_net_paid,
+ ws_net_paid_inc_tax,
+ ws_net_paid_inc_ship,
+ ws_net_paid_inc_ship_tax,
+ ws_net_profit
+FROM {source_db_name}.web_sales;
+
+---- CATALOG_SALES
+CREATE TABLE IF NOT EXISTS {target_db_name}.catalog_sales (
+ cs_order_number BIGINT,
+ cs_item_sk BIGINT,
+ cs_sold_date_sk BIGINT,
+ cs_sold_time_sk BIGINT,
+ cs_ship_date_sk BIGINT,
+ cs_bill_customer_sk BIGINT,
+ cs_bill_cdemo_sk BIGINT,
+ cs_bill_hdemo_sk BIGINT,
+ cs_bill_addr_sk BIGINT,
+ cs_ship_customer_sk BIGINT,
+ cs_ship_cdemo_sk BIGINT,
+ cs_ship_hdemo_sk BIGINT,
+ cs_ship_addr_sk BIGINT,
+ cs_call_center_sk BIGINT,
+ cs_catalog_page_sk BIGINT,
+ cs_ship_mode_sk BIGINT,
+ cs_warehouse_sk BIGINT,
+ cs_promo_sk BIGINT,
+ cs_quantity BIGINT,
+ cs_wholesale_cost DOUBLE,
+ cs_list_price DOUBLE,
+ cs_sales_price DOUBLE,
+ cs_ext_discount_amt DOUBLE,
+ cs_ext_sales_price DOUBLE,
+ cs_ext_wholesale_cost DOUBLE,
+ cs_ext_list_price DOUBLE,
+ cs_ext_tax DOUBLE,
+ cs_coupon_amt DOUBLE,
+ cs_ext_ship_cost DOUBLE,
+ cs_net_paid DOUBLE,
+ cs_net_paid_inc_tax DOUBLE,
+ cs_net_paid_inc_ship DOUBLE,
+ cs_net_paid_inc_ship_tax DOUBLE,
+ cs_net_profit DOUBLE
+)
+DISTRIBUTE BY HASH (cs_order_number,cs_item_sk) INTO {buckets} BUCKETS
+TBLPROPERTIES(
+'storage_handler' = 'com.cloudera.kudu.hive.KuduStorageHandler',
+'kudu.table_name' = '{target_db_name}_catalog_sales',
+'kudu.master_addresses' = '{kudu_master}:7051',
+'kudu.key_columns' = 'cs_order_number, cs_item_sk'
+);
+
+INSERT INTO {target_db_name}.catalog_sales
+SELECT
+ cs_order_number,
+ cs_item_sk,
+ cs_sold_date_sk,
+ cs_sold_time_sk,
+ cs_ship_date_sk,
+ cs_bill_customer_sk,
+ cs_bill_cdemo_sk,
+ cs_bill_hdemo_sk,
+ cs_bill_addr_sk,
+ cs_ship_customer_sk,
+ cs_ship_cdemo_sk,
+ cs_ship_hdemo_sk,
+ cs_ship_addr_sk,
+ cs_call_center_sk,
+ cs_catalog_page_sk,
+ cs_ship_mode_sk,
+ cs_warehouse_sk,
+ cs_promo_sk,
+ cs_quantity,
+ cs_wholesale_cost,
+ cs_list_price,
+ cs_sales_price,
+ cs_ext_discount_amt,
+ cs_ext_sales_price,
+ cs_ext_wholesale_cost,
+ cs_ext_list_price,
+ cs_ext_tax,
+ cs_coupon_amt,
+ cs_ext_ship_cost,
+ cs_net_paid,
+ cs_net_paid_inc_tax,
+ cs_net_paid_inc_ship,
+ cs_net_paid_inc_ship_tax,
+ cs_net_profit
+FROM {source_db_name}.catalog_sales;
+
+---- STORE_RETURNS
+CREATE TABLE IF NOT EXISTS {target_db_name}.store_returns (
+ sr_ticket_number BIGINT,
+ sr_item_sk BIGINT,
+ sr_returned_date_sk BIGINT,
+ sr_return_time_sk BIGINT,
+ sr_customer_sk BIGINT,
+ sr_cdemo_sk BIGINT,
+ sr_hdemo_sk BIGINT,
+ sr_addr_sk BIGINT,
+ sr_store_sk BIGINT,
+ sr_reason_sk BIGINT,
+ sr_return_quantity BIGINT,
+ sr_return_amt DOUBLE,
+ sr_return_tax DOUBLE,
+ sr_return_amt_inc_tax DOUBLE,
+ sr_fee DOUBLE,
+ sr_return_ship_cost DOUBLE,
+ sr_refunded_cash DOUBLE,
+ sr_reversed_charge DOUBLE,
+ sr_store_credit DOUBLE,
+ sr_net_loss DOUBLE
+)
+DISTRIBUTE BY HASH (sr_ticket_number,sr_item_sk) INTO {buckets} BUCKETS
+TBLPROPERTIES (
+'kudu.master_addresses'='{kudu_master}:7051',
+'kudu.key_columns'='sr_ticket_number, sr_item_sk',
+'kudu.table_name'='{target_db_name}_store_returns',
+'storage_handler'='com.cloudera.kudu.hive.KuduStorageHandler');
+
+INSERT INTO {target_db_name}.store_returns
+SELECT
+ sr_ticket_number,
+ sr_item_sk,
+ sr_returned_date_sk,
+ sr_return_time_sk,
+ sr_customer_sk,
+ sr_cdemo_sk,
+ sr_hdemo_sk,
+ sr_addr_sk,
+ sr_store_sk,
+ sr_reason_sk,
+ sr_return_quantity,
+ sr_return_amt,
+ sr_return_tax,
+ sr_return_amt_inc_tax,
+ sr_fee,
+ sr_return_ship_cost,
+ sr_refunded_cash,
+ sr_reversed_charge,
+ sr_store_credit,
+ sr_net_loss
+FROM {source_db_name}.store_returns;
+
+---- WEB_RETURNS
+CREATE TABLE IF NOT EXISTS {target_db_name}.web_returns (
+ wr_order_number BIGINT,
+ wr_item_sk BIGINT,
+ wr_returned_date_sk BIGINT,
+ wr_returned_time_sk BIGINT,
+ wr_refunded_customer_sk BIGINT,
+ wr_refunded_cdemo_sk BIGINT,
+ wr_refunded_hdemo_sk BIGINT,
+ wr_refunded_addr_sk BIGINT,
+ wr_returning_customer_sk BIGINT,
+ wr_returning_cdemo_sk BIGINT,
+ wr_returning_hdemo_sk BIGINT,
+ wr_returning_addr_sk BIGINT,
+ wr_web_page_sk BIGINT,
+ wr_reason_sk BIGINT,
+ wr_return_quantity BIGINT,
+ wr_return_amt DOUBLE,
+ wr_return_tax DOUBLE,
+ wr_return_amt_inc_tax DOUBLE,
+ wr_fee DOUBLE,
+ wr_return_ship_cost DOUBLE,
+ wr_refunded_cash DOUBLE,
+ wr_reversed_charge DOUBLE,
+ wr_account_credit DOUBLE,
+ wr_net_loss DOUBLE
+)
+DISTRIBUTE BY HASH (wr_order_number,wr_item_sk) INTO {buckets} BUCKETS
+TBLPROPERTIES (
+'kudu.master_addresses'='{kudu_master}:7051',
+'kudu.key_columns'='wr_order_number, wr_item_sk',
+'kudu.table_name'='{target_db_name}_web_returns',
+'storage_handler'='com.cloudera.kudu.hive.KuduStorageHandler');
+
+INSERT INTO {target_db_name}.web_returns
+SELECT
+ wr_order_number,
+ wr_item_sk,
+ wr_returned_date_sk,
+ wr_returned_time_sk,
+ wr_refunded_customer_sk,
+ wr_refunded_cdemo_sk,
+ wr_refunded_hdemo_sk,
+ wr_refunded_addr_sk,
+ wr_returning_customer_sk,
+ wr_returning_cdemo_sk,
+ wr_returning_hdemo_sk,
+ wr_returning_addr_sk,
+ wr_web_page_sk,
+ wr_reason_sk,
+ wr_return_quantity,
+ wr_return_amt,
+ wr_return_tax,
+ wr_return_amt_inc_tax,
+ wr_fee,
+ wr_return_ship_cost,
+ wr_refunded_cash,
+ wr_reversed_charge,
+ wr_account_credit,
+ wr_net_loss
+FROM {source_db_name}.web_returns;
+
+---- CATALOG_RETURNS
+CREATE TABLE IF NOT EXISTS {target_db_name}.catalog_returns (
+ cr_order_number BIGINT,
+ cr_item_sk BIGINT,
+ cr_returned_date_sk BIGINT,
+ cr_returned_time_sk BIGINT,
+ cr_refunded_customer_sk BIGINT,
+ cr_refunded_cdemo_sk BIGINT,
+ cr_refunded_hdemo_sk BIGINT,
+ cr_refunded_addr_sk BIGINT,
+ cr_returning_customer_sk BIGINT,
+ cr_returning_cdemo_sk BIGINT,
+ cr_returning_hdemo_sk BIGINT,
+ cr_returning_addr_sk BIGINT,
+ cr_call_center_sk BIGINT,
+ cr_catalog_page_sk BIGINT,
+ cr_ship_mode_sk BIGINT,
+ cr_warehouse_sk BIGINT,
+ cr_reason_sk BIGINT,
+ cr_return_quantity BIGINT,
+ cr_return_amount DOUBLE,
+ cr_return_tax DOUBLE,
+ cr_return_amt_inc_tax DOUBLE,
+ cr_fee DOUBLE,
+ cr_return_ship_cost DOUBLE,
+ cr_refunded_cash DOUBLE,
+ cr_reversed_charge DOUBLE,
+ cr_store_credit DOUBLE,
+ cr_net_loss DOUBLE
+)
+DISTRIBUTE BY HASH (cr_order_number,cr_item_sk) INTO {buckets} BUCKETS
+TBLPROPERTIES (
+'kudu.master_addresses'='{kudu_master}:7051',
+'kudu.key_columns'='cr_order_number, cr_item_sk',
+'kudu.table_name'='{target_db_name}_catalog_returns',
+'storage_handler'='com.cloudera.kudu.hive.KuduStorageHandler');
+
+INSERT INTO {target_db_name}.catalog_returns
+SELECT
+ cr_order_number,
+ cr_item_sk,
+ cr_returned_date_sk,
+ cr_returned_time_sk,
+ cr_refunded_customer_sk,
+ cr_refunded_cdemo_sk,
+ cr_refunded_hdemo_sk,
+ cr_refunded_addr_sk,
+ cr_returning_customer_sk,
+ cr_returning_cdemo_sk,
+ cr_returning_hdemo_sk,
+ cr_returning_addr_sk,
+ cr_call_center_sk,
+ cr_catalog_page_sk,
+ cr_ship_mode_sk,
+ cr_warehouse_sk,
+ cr_reason_sk,
+ cr_return_quantity,
+ cr_return_amount,
+ cr_return_tax,
+ cr_return_amt_inc_tax,
+ cr_fee,
+ cr_return_ship_cost,
+ cr_refunded_cash,
+ cr_reversed_charge,
+ cr_store_credit,
+ cr_net_loss
+FROM {source_db_name}.catalog_returns;
+
+---- INVENTORY
+CREATE TABLE IF NOT EXISTS {target_db_name}.inventory (
+ inv_date_sk BIGINT,
+ inv_item_sk BIGINT,
+ inv_warehouse_sk BIGINT,
+ inv_quantity_on_hand BIGINT
+)
+DISTRIBUTE BY HASH (inv_item_sk,inv_date_sk,inv_warehouse_sk) INTO {buckets} BUCKETS
+TBLPROPERTIES (
+'kudu.master_addresses'='{kudu_master}:7051',
+'kudu.key_columns'='inv_date_sk,inv_item_sk,inv_warehouse_sk',
+'kudu.table_name'='{target_db_name}_inventory',
+'storage_handler'='com.cloudera.kudu.hive.KuduStorageHandler');
+
+INSERT INTO {target_db_name}.inventory SELECT * FROM {source_db_name}.inventory;
+
+---- CUSTOMER
+
+CREATE TABLE {target_db_name}.customer (
+ c_customer_sk BIGINT,
+ c_customer_id STRING,
+ c_current_cdemo_sk BIGINT,
+ c_current_hdemo_sk BIGINT,
+ c_current_addr_sk BIGINT,
+ c_first_shipto_date_sk BIGINT,
+ c_first_sales_date_sk BIGINT,
+ c_salutation STRING,
+ c_first_name STRING,
+ c_last_name STRING,
+ c_preferred_cust_flag STRING,
+ c_birth_day INT,
+ c_birth_month INT,
+ c_birth_year INT,
+ c_birth_country STRING,
+ c_login STRING,
+ c_email_address STRING,
+ c_last_review_date BIGINT
+)
+DISTRIBUTE BY HASH (c_customer_sk) INTO {buckets} BUCKETS
+TBLPROPERTIES (
+'kudu.master_addresses'='{kudu_master}:7051',
+'kudu.key_columns'='c_customer_sk',
+'kudu.table_name'='{target_db_name}_customer',
+'storage_handler'='com.cloudera.kudu.hive.KuduStorageHandler');
+
+INSERT INTO {target_db_name}.customer SELECT * FROM {source_db_name}.customer;
+
+---- CUSTOMER_ADDRESS
+CREATE TABLE IF NOT EXISTS {target_db_name}.customer_address (
+ ca_address_sk BIGINT,
+ ca_address_id STRING,
+ ca_street_number STRING,
+ ca_street_name STRING,
+ ca_street_type STRING,
+ ca_suite_number STRING,
+ ca_city STRING,
+ ca_county STRING,
+ ca_state STRING,
+ ca_zip STRING,
+ ca_country STRING,
+ ca_gmt_offset DOUBLE,
+ ca_location_type STRING
+)
+DISTRIBUTE BY HASH (ca_address_sk) INTO {buckets} BUCKETS
+TBLPROPERTIES (
+'kudu.master_addresses'='{kudu_master}:7051',
+'kudu.key_columns'='ca_address_sk',
+'kudu.table_name'='{target_db_name}_customer_address',
+'storage_handler'='com.cloudera.kudu.hive.KuduStorageHandler');
+
+INSERT INTO {target_db_name}.customer_address
+SELECT * FROM {source_db_name}.customer_address;
+
+---- CUSTOMER_DEMOGRAPHICS
+CREATE TABLE IF NOT EXISTS {target_db_name}.customer_demographics (
+ cd_demo_sk BIGINT,
+ cd_gender STRING,
+ cd_marital_status STRING,
+ cd_education_status STRING,
+ cd_purchase_estimate BIGINT,
+ cd_credit_rating STRING,
+ cd_dep_count BIGINT,
+ cd_dep_employed_count BIGINT,
+ cd_dep_college_count BIGINT
+)
+DISTRIBUTE BY HASH (cd_demo_sk) INTO {buckets} BUCKETS
+TBLPROPERTIES (
+'kudu.master_addresses'='{kudu_master}:7051',
+'kudu.key_columns'='cd_demo_sk',
+'kudu.table_name'='{target_db_name}_customer_demographics',
+'storage_handler'='com.cloudera.kudu.hive.KuduStorageHandler');
+
+INSERT INTO {target_db_name}.customer_demographics
+SELECT * FROM {source_db_name}.customer_demographics;
+
+---- DATE_DIM
+CREATE TABLE IF NOT EXISTS {target_db_name}.date_dim (
+ d_date_sk BIGINT,
+ d_date_id STRING,
+ d_date STRING,
+ d_month_seq BIGINT,
+ d_week_seq BIGINT,
+ d_quarter_seq BIGINT,
+ d_year BIGINT,
+ d_dow BIGINT,
+ d_moy BIGINT,
+ d_dom BIGINT,
+ d_qoy BIGINT,
+ d_fy_year BIGINT,
+ d_fy_quarter_seq BIGINT,
+ d_fy_week_seq BIGINT,
+ d_day_name STRING,
+ d_quarter_name STRING,
+ d_holiday STRING,
+ d_weekend STRING,
+ d_following_holiday STRING,
+ d_first_dom BIGINT,
+ d_last_dom BIGINT,
+ d_same_day_ly BIGINT,
+ d_same_day_lq BIGINT,
+ d_current_day STRING,
+ d_current_week STRING,
+ d_current_month STRING,
+ d_current_quarter STRING,
+ d_current_year STRING
+)
+DISTRIBUTE BY HASH (d_date_sk) INTO {buckets} BUCKETS
+TBLPROPERTIES (
+'kudu.master_addresses'='{kudu_master}:7051',
+'kudu.key_columns'='d_date_sk',
+'kudu.table_name'='{target_db_name}_date_dim',
+'storage_handler'='com.cloudera.kudu.hive.KuduStorageHandler');
+
+INSERT INTO {target_db_name}.date_dim SELECT * FROM {source_db_name}.date_dim;
+
+---- HOUSEHOLD_DEMOGRAPHICS
+CREATE TABLE IF NOT EXISTS {target_db_name}.household_demographics (
+ hd_demo_sk BIGINT,
+ hd_income_band_sk BIGINT,
+ hd_buy_potential STRING,
+ hd_dep_count BIGINT,
+ hd_vehicle_count BIGINT
+)
+DISTRIBUTE BY HASH (hd_demo_sk) INTO {buckets} BUCKETS
+TBLPROPERTIES (
+'kudu.master_addresses'='{kudu_master}:7051',
+'kudu.key_columns'='hd_demo_sk',
+'kudu.table_name'='{target_db_name}_household_demographics',
+'storage_handler'='com.cloudera.kudu.hive.KuduStorageHandler');
+
+INSERT INTO {target_db_name}.household_demographics
+SELECT * FROM {source_db_name}.household_demographics;
+
+---- ITEM
+CREATE TABLE IF NOT EXISTS {target_db_name}.item (
+ i_item_sk BIGINT,
+ i_item_id STRING,
+ i_rec_start_date STRING,
+ i_rec_end_date STRING,
+ i_item_desc STRING,
+ i_current_price DOUBLE,
+ i_wholesale_cost DOUBLE,
+ i_brand_id BIGINT,
+ i_brand STRING,
+ i_class_id BIGINT,
+ i_class STRING,
+ i_category_id BIGINT,
+ i_category STRING,
+ i_manufact_id BIGINT,
+ i_manufact STRING,
+ i_size STRING,
+ i_formulation STRING,
+ i_color STRING,
+ i_units STRING,
+ i_container STRING,
+ i_manager_id BIGINT,
+ i_product_name STRING
+)
+DISTRIBUTE BY HASH (i_item_sk) INTO {buckets} BUCKETS
+TBLPROPERTIES (
+'kudu.master_addresses'='{kudu_master}:7051',
+'kudu.key_columns'='i_item_sk',
+'kudu.table_name'='{target_db_name}_item',
+'storage_handler'='com.cloudera.kudu.hive.KuduStorageHandler');
+
+INSERT INTO {target_db_name}.item SELECT * FROM {source_db_name}.item;
+
+---- PROMOTION
+CREATE TABLE IF NOT EXISTS {target_db_name}.promotion (
+ p_promo_sk BIGINT,
+ p_item_sk BIGINT,
+ p_start_date_sk BIGINT,
+ p_end_date_sk BIGINT,
+ p_promo_id STRING,
+ p_cost DOUBLE,
+ p_response_target BIGINT,
+ p_promo_name STRING,
+ p_channel_dmail STRING,
+ p_channel_email STRING,
+ p_channel_catalog STRING,
+ p_channel_tv STRING,
+ p_channel_radio STRING,
+ p_channel_press STRING,
+ p_channel_event STRING,
+ p_channel_demo STRING,
+ p_channel_details STRING,
+ p_purpose STRING,
+ p_discount_active STRING
+)
+DISTRIBUTE BY HASH (p_promo_sk) INTO {buckets} BUCKETS
+TBLPROPERTIES (
+'kudu.master_addresses'='{kudu_master}:7051',
+'kudu.key_columns'='p_promo_sk',
+'kudu.table_name'='{target_db_name}_promotion',
+'storage_handler'='com.cloudera.kudu.hive.KuduStorageHandler');
+
+INSERT INTO {target_db_name}.promotion
+SELECT
+ p_promo_sk,
+ p_item_sk,
+ p_start_date_sk,
+ p_end_date_sk,
+ p_promo_id,
+ p_cost,
+ p_response_target,
+ p_promo_name,
+ p_channel_dmail,
+ p_channel_email,
+ p_channel_catalog,
+ p_channel_tv,
+ p_channel_radio,
+ p_channel_press,
+ p_channel_event,
+ p_channel_demo,
+ p_channel_details,
+ p_purpose,
+ p_discount_active
+FROM {source_db_name}.promotion;
+
+---- STORE
+CREATE TABLE IF NOT EXISTS {target_db_name}.store (
+ s_store_sk BIGINT,
+ s_store_id STRING,
+ s_rec_start_date STRING,
+ s_rec_end_date STRING,
+ s_closed_date_sk BIGINT,
+ s_store_name STRING,
+ s_number_employees BIGINT,
+ s_floor_space BIGINT,
+ s_hours STRING,
+ s_manager STRING,
+ s_market_id BIGINT,
+ s_geography_class STRING,
+ s_market_desc STRING,
+ s_market_manager STRING,
+ s_division_id BIGINT,
+ s_division_name STRING,
+ s_company_id BIGINT,
+ s_company_name STRING,
+ s_street_number STRING,
+ s_street_name STRING,
+ s_street_type STRING,
+ s_suite_number STRING,
+ s_city STRING,
+ s_county STRING,
+ s_state STRING,
+ s_zip STRING,
+ s_country STRING,
+ s_gmt_offset DOUBLE,
+ s_tax_precentage DOUBLE
+)
+DISTRIBUTE BY HASH (s_store_sk) INTO {buckets} BUCKETS
+TBLPROPERTIES (
+'kudu.master_addresses'='{kudu_master}:7051',
+'kudu.key_columns'='s_store_sk',
+'kudu.table_name'='{target_db_name}_store',
+'storage_handler'='com.cloudera.kudu.hive.KuduStorageHandler');
+
+INSERT INTO {target_db_name}.store SELECT * FROM {source_db_name}.store;
+
+---- TIME_DIM
+CREATE TABLE IF NOT EXISTS {target_db_name}.time_dim (
+ t_time_sk BIGINT,
+ t_time_id STRING,
+ t_time BIGINT,
+ t_hour BIGINT,
+ t_minute BIGINT,
+ t_second BIGINT,
+ t_am_pm STRING,
+ t_shift STRING,
+ t_sub_shift STRING,
+ t_meal_time STRING
+)
+DISTRIBUTE BY HASH (t_time_sk) INTO {buckets} BUCKETS
+TBLPROPERTIES (
+'kudu.master_addresses'='{kudu_master}:7051',
+'kudu.key_columns'='t_time_sk',
+'kudu.table_name'='{target_db_name}_time_dim',
+'storage_handler'='com.cloudera.kudu.hive.KuduStorageHandler');
+
+INSERT INTO {target_db_name}.time_dim SELECT * FROM {source_db_name}.time_dim;
+
+---- CALL_CENTER
+CREATE TABLE IF NOT EXISTS {target_db_name}.call_center (
+ cc_call_center_sk BIGINT,
+ cc_call_center_id STRING,
+ cc_rec_start_date STRING,
+ cc_rec_end_date STRING,
+ cc_closed_date_sk BIGINT,
+ cc_open_date_sk BIGINT,
+ cc_name STRING,
+ cc_class STRING,
+ cc_employees BIGINT,
+ cc_sq_ft BIGINT,
+ cc_hours STRING,
+ cc_manager STRING,
+ cc_mkt_id BIGINT,
+ cc_mkt_class STRING,
+ cc_mkt_desc STRING,
+ cc_market_manager STRING,
+ cc_division BIGINT,
+ cc_division_name STRING,
+ cc_company BIGINT,
+ cc_company_name STRING,
+ cc_street_number STRING,
+ cc_street_name STRING,
+ cc_street_type STRING,
+ cc_suite_number STRING,
+ cc_city STRING,
+ cc_county STRING,
+ cc_state STRING,
+ cc_zip STRING,
+ cc_country STRING,
+ cc_gmt_offset DOUBLE,
+ cc_tax_percentage DOUBLE
+)
+DISTRIBUTE BY HASH (cc_call_center_sk) INTO {buckets} BUCKETS
+TBLPROPERTIES (
+'kudu.master_addresses'='{kudu_master}:7051',
+'kudu.key_columns'='cc_call_center_sk',
+'kudu.table_name'='{target_db_name}_call_center',
+'storage_handler'='com.cloudera.kudu.hive.KuduStorageHandler');
+
+INSERT INTO {target_db_name}.call_center SELECT * FROM {source_db_name}.call_center;
+
+---- CATALOG_PAGE
+CREATE TABLE IF NOT EXISTS {target_db_name}.catalog_page (
+ cp_catalog_page_sk BIGINT,
+ cp_catalog_page_id STRING,
+ cp_start_date_sk BIGINT,
+ cp_end_date_sk BIGINT,
+ cp_department STRING,
+ cp_catalog_number BIGINT,
+ cp_catalog_page_number BIGINT,
+ cp_description STRING,
+ cp_type STRING
+)
+DISTRIBUTE BY HASH (cp_catalog_page_sk) INTO {buckets} BUCKETS
+TBLPROPERTIES (
+'kudu.master_addresses'='{kudu_master}:7051',
+'kudu.key_columns'='cp_catalog_page_sk',
+'kudu.table_name'='{target_db_name}_catalog_page',
+'storage_handler'='com.cloudera.kudu.hive.KuduStorageHandler');
+
+INSERT INTO {target_db_name}.catalog_page SELECT * FROM {source_db_name}.catalog_page;
+
+---- INCOME_BANDS
+CREATE TABLE IF NOT EXISTS {target_db_name}.income_band (
+ ib_income_band_sk BIGINT,
+ ib_lower_bound BIGINT,
+ ib_upper_bound BIGINT
+)
+DISTRIBUTE BY HASH (ib_income_band_sk) INTO {buckets} BUCKETS
+TBLPROPERTIES (
+'kudu.master_addresses'='{kudu_master}:7051',
+'kudu.key_columns'='ib_income_band_sk',
+'kudu.table_name'='{target_db_name}_income_band',
+'storage_handler'='com.cloudera.kudu.hive.KuduStorageHandler');
+
+INSERT INTO {target_db_name}.income_band SELECT * FROM {source_db_name}.income_band;
+
+---- REASON
+CREATE TABLE IF NOT EXISTS {target_db_name}.reason (
+ r_reason_sk BIGINT,
+ r_reason_id STRING,
+ r_reason_desc STRING
+)
+DISTRIBUTE BY HASH (r_reason_sk) INTO {buckets} BUCKETS
+TBLPROPERTIES (
+'kudu.master_addresses'='{kudu_master}:7051',
+'kudu.key_columns'='r_reason_sk',
+'kudu.table_name'='{target_db_name}_reason',
+'storage_handler'='com.cloudera.kudu.hive.KuduStorageHandler');
+
+INSERT INTO {target_db_name}.reason SELECT * FROM {source_db_name}.reason;
+
+---- SHIP_MODE
+CREATE TABLE IF NOT EXISTS {target_db_name}.ship_mode (
+ sm_ship_mode_sk BIGINT,
+ sm_ship_mode_id STRING,
+ sm_type STRING,
+ sm_code STRING,
+ sm_carrier STRING,
+ sm_contract STRING
+)
+DISTRIBUTE BY HASH (sm_ship_mode_sk) INTO {buckets} BUCKETS
+TBLPROPERTIES (
+'kudu.master_addresses'='{kudu_master}:7051',
+'kudu.key_columns'='sm_ship_mode_sk',
+'kudu.table_name'='{target_db_name}_ship_mode',
+'storage_handler'='com.cloudera.kudu.hive.KuduStorageHandler');
+
+INSERT INTO {target_db_name}.ship_mode SELECT * FROM {source_db_name}.ship_mode;
+
+---- WAREHOUSE
+CREATE TABLE IF NOT EXISTS {target_db_name}.warehouse (
+ w_warehouse_sk BIGINT,
+ w_warehouse_id STRING,
+ w_warehouse_name STRING,
+ w_warehouse_sq_ft BIGINT,
+ w_street_number STRING,
+ w_street_name STRING,
+ w_street_type STRING,
+ w_suite_number STRING,
+ w_city STRING,
+ w_county STRING,
+ w_state STRING,
+ w_zip STRING,
+ w_country STRING,
+ w_gmt_offset DOUBLE
+)
+DISTRIBUTE BY HASH (w_warehouse_sk) INTO {buckets} BUCKETS
+TBLPROPERTIES (
+'kudu.master_addresses'='{kudu_master}:7051',
+'kudu.key_columns'='w_warehouse_sk',
+'kudu.table_name'='{target_db_name}_warehouse',
+'storage_handler'='com.cloudera.kudu.hive.KuduStorageHandler');
+
+INSERT INTO {target_db_name}.warehouse SELECT * FROM {source_db_name}.warehouse;
+
+---- WEB_PAGE
+CREATE TABLE IF NOT EXISTS {target_db_name}.web_page (
+ wp_web_page_sk BIGINT,
+ wp_web_page_id STRING,
+ wp_rec_start_date STRING,
+ wp_rec_end_date STRING,
+ wp_creation_date_sk BIGINT,
+ wp_access_date_sk BIGINT,
+ wp_autogen_flag STRING,
+ wp_customer_sk BIGINT,
+ wp_url STRING,
+ wp_type STRING,
+ wp_char_count BIGINT,
+ wp_link_count BIGINT,
+ wp_image_count BIGINT,
+ wp_max_ad_count BIGINT
+)
+DISTRIBUTE BY HASH (wp_web_page_sk) INTO {buckets} BUCKETS
+TBLPROPERTIES (
+'kudu.master_addresses'='{kudu_master}:7051',
+'kudu.key_columns'='wp_web_page_sk',
+'kudu.table_name'='{target_db_name}_web_page',
+'storage_handler'='com.cloudera.kudu.hive.KuduStorageHandler');
+
+INSERT INTO {target_db_name}.web_page SELECT * FROM {source_db_name}.web_page;
+
+---- WEB_SITE
+CREATE TABLE IF NOT EXISTS {target_db_name}.web_site (
+ web_site_sk BIGINT,
+ web_site_id STRING,
+ web_rec_start_date STRING,
+ web_rec_end_date STRING,
+ web_name STRING,
+ web_open_date_sk BIGINT,
+ web_close_date_sk BIGINT,
+ web_class STRING,
+ web_manager STRING,
+ web_mkt_id BIGINT,
+ web_mkt_class STRING,
+ web_mkt_desc STRING,
+ web_market_manager STRING,
+ web_company_id BIGINT,
+ web_company_name STRING,
+ web_street_number STRING,
+ web_street_name STRING,
+ web_street_type STRING,
+ web_suite_number STRING,
+ web_city STRING,
+ web_county STRING,
+ web_state STRING,
+ web_zip STRING,
+ web_country STRING,
+ web_gmt_offset DOUBLE,
+ web_tax_percentage DOUBLE
+)
+DISTRIBUTE BY HASH (web_site_sk) INTO {buckets} BUCKETS
+TBLPROPERTIES (
+'kudu.master_addresses'='{kudu_master}:7051',
+'kudu.key_columns'='web_site_sk',
+'kudu.table_name'='{target_db_name}_web_site',
+'storage_handler'='com.cloudera.kudu.hive.KuduStorageHandler');
+
+INSERT INTO {target_db_name}.web_site SELECT * FROM {source_db_name}.web_site;
+
+---- COMPUTE STATS
+compute stats {target_db_name}.call_center;
+compute stats {target_db_name}.catalog_page;
+compute stats {target_db_name}.catalog_returns;
+compute stats {target_db_name}.catalog_sales;
+compute stats {target_db_name}.customer;
+compute stats {target_db_name}.customer_address;
+compute stats {target_db_name}.customer_demographics;
+compute stats {target_db_name}.date_dim;
+compute stats {target_db_name}.household_demographics;
+compute stats {target_db_name}.income_band;
+compute stats {target_db_name}.inventory;
+compute stats {target_db_name}.item;
+compute stats {target_db_name}.reason;
+compute stats {target_db_name}.ship_mode;
+compute stats {target_db_name}.store;
+compute stats {target_db_name}.store_returns;
+compute stats {target_db_name}.store_sales;
+compute stats {target_db_name}.time_dim;
+compute stats {target_db_name}.warehouse;
+compute stats {target_db_name}.web_page;
+compute stats {target_db_name}.web_returns;
+compute stats {target_db_name}.web_sales;
+compute stats {target_db_name}.web_site;
+compute stats {target_db_name}.promotion;
http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/8a49ceaa/testdata/datasets/tpch/tpch_kudu_template.sql
----------------------------------------------------------------------
diff --git a/testdata/datasets/tpch/tpch_kudu_template.sql b/testdata/datasets/tpch/tpch_kudu_template.sql
new file mode 100644
index 0000000..5abde0b
--- /dev/null
+++ b/testdata/datasets/tpch/tpch_kudu_template.sql
@@ -0,0 +1,198 @@
+---- Template SQL statements to create and load TPCH tables in KUDU.
+---- TODO: Change to the new syntax for CREATE TABLE statements (IMPALA-3719)
+---- TODO: Fix the primary key column order
+---- TODO: Remove the CREATE_KUDU sections from tpch_schema_template.sql and use
+---- this file instead for loading TPC-H data in Kudu.
+
+--- LINEITEM
+CREATE TABLE IF NOT EXISTS {target_db_name}.lineitem (
+ L_ORDERKEY BIGINT,
+ L_LINENUMBER BIGINT,
+ L_PARTKEY BIGINT,
+ L_SUPPKEY BIGINT,
+ L_QUANTITY DOUBLE,
+ L_EXTENDEDPRICE DOUBLE,
+ L_DISCOUNT DOUBLE,
+ L_TAX DOUBLE,
+ L_RETURNFLAG STRING,
+ L_LINESTATUS STRING,
+ L_SHIPDATE STRING,
+ L_COMMITDATE STRING,
+ L_RECEIPTDATE STRING,
+ L_SHIPINSTRUCT STRING,
+ L_SHIPMODE STRING,
+ L_COMMENT STRING
+)
+distribute by hash (l_orderkey) into {buckets} buckets
+tblproperties(
+ 'storage_handler' = 'com.cloudera.kudu.hive.KuduStorageHandler',
+ 'kudu.master_addresses' = '{kudu_master}:7051',
+ 'kudu.table_name' = '{target_db_name}_lineitem',
+ 'kudu.key_columns' = 'l_orderkey, l_linenumber'
+);
+
+INSERT INTO TABLE {target_db_name}.lineitem
+SELECT
+ L_ORDERKEY,
+ L_LINENUMBER,
+ L_PARTKEY,
+ L_SUPPKEY,
+ L_QUANTITY,
+ L_EXTENDEDPRICE,
+ L_DISCOUNT,
+ L_TAX,
+ L_RETURNFLAG,
+ L_LINESTATUS,
+ L_SHIPDATE,
+ L_COMMITDATE,
+ L_RECEIPTDATE,
+ L_SHIPINSTRUCT,
+ L_SHIPMODE,
+ L_COMMENT
+FROM {source_db_name}.lineitem;
+
+---- PART
+CREATE TABLE IF NOT EXISTS {target_db_name}.part (
+ P_PARTKEY BIGINT,
+ P_NAME STRING,
+ P_MFGR STRING,
+ P_BRAND STRING,
+ P_TYPE STRING,
+ P_SIZE BIGINT,
+ P_CONTAINER STRING,
+ P_RETAILPRICE DOUBLE,
+ P_COMMENT STRING
+)
+distribute by hash (p_partkey) into {buckets} buckets
+tblproperties(
+ 'storage_handler' = 'com.cloudera.kudu.hive.KuduStorageHandler',
+ 'kudu.master_addresses' = '{kudu_master}:7051',
+ 'kudu.table_name' = '{target_db_name}_part',
+ 'kudu.key_columns' = 'p_partkey'
+);
+
+INSERT INTO TABLE {target_db_name}.part SELECT * FROM {source_db_name}.part;
+
+---- PARTSUPP
+CREATE TABLE IF NOT EXISTS {target_db_name}.partsupp (
+ PS_PARTKEY BIGINT,
+ PS_SUPPKEY BIGINT,
+ PS_AVAILQTY BIGINT,
+ PS_SUPPLYCOST DOUBLE,
+ PS_COMMENT STRING
+)
+distribute by hash (ps_partkey, ps_suppkey) into {buckets} buckets
+tblproperties(
+ 'storage_handler' = 'com.cloudera.kudu.hive.KuduStorageHandler',
+ 'kudu.master_addresses' = '{kudu_master}:7051',
+ 'kudu.table_name' = '{target_db_name}_partsupp',
+ 'kudu.key_columns' = 'ps_partkey, ps_suppkey'
+);
+
+INSERT INTO TABLE {target_db_name}.partsupp SELECT * FROM {source_db_name}.partsupp;
+
+---- SUPPLIER
+CREATE TABLE IF NOT EXISTS {target_db_name}.supplier (
+ S_SUPPKEY BIGINT,
+ S_NAME STRING,
+ S_ADDRESS STRING,
+ S_NATIONKEY BIGINT,
+ S_PHONE STRING,
+ S_ACCTBAL DOUBLE,
+ S_COMMENT STRING
+)
+distribute by hash (s_suppkey) into {buckets} buckets
+tblproperties(
+ 'storage_handler' = 'com.cloudera.kudu.hive.KuduStorageHandler',
+ 'kudu.master_addresses' = '{kudu_master}:7051',
+ 'kudu.table_name' = '{target_db_name}_supplier',
+ 'kudu.key_columns' = 's_suppkey'
+);
+
+INSERT INTO TABLE {target_db_name}.supplier SELECT * FROM {source_db_name}.supplier;
+
+---- NATION
+CREATE TABLE IF NOT EXISTS {target_db_name}.nation (
+ N_NATIONKEY BIGINT,
+ N_NAME STRING,
+ N_REGIONKEY BIGINT,
+ N_COMMENT STRING
+)
+distribute by hash (n_nationkey) into {buckets} buckets
+tblproperties(
+ 'storage_handler' = 'com.cloudera.kudu.hive.KuduStorageHandler',
+ 'kudu.master_addresses' = '{kudu_master}:7051',
+ 'kudu.table_name' = '{target_db_name}_nation',
+ 'kudu.key_columns' = 'n_nationkey'
+);
+
+INSERT INTO TABLE {target_db_name}.nation SELECT * FROM {source_db_name}.nation;
+
+---- REGION
+CREATE TABLE IF NOT EXISTS {target_db_name}.region (
+ R_REGIONKEY BIGINT,
+ R_NAME STRING,
+ R_COMMENT STRING
+)
+distribute by hash (r_regionkey) into {buckets} buckets
+tblproperties(
+ 'storage_handler' = 'com.cloudera.kudu.hive.KuduStorageHandler',
+ 'kudu.master_addresses' = '{kudu_master}:7051',
+ 'kudu.table_name' = '{target_db_name}_region',
+ 'kudu.key_columns' = 'r_regionkey'
+);
+
+INSERT INTO TABLE {target_db_name}.region SELECT * FROM {source_db_name}.region;
+
+---- ORDERS
+CREATE TABLE IF NOT EXISTS {target_db_name}.orders (
+ O_ORDERKEY BIGINT,
+ O_CUSTKEY BIGINT,
+ O_ORDERSTATUS STRING,
+ O_TOTALPRICE DOUBLE,
+ O_ORDERDATE STRING,
+ O_ORDERPRIORITY STRING,
+ O_CLERK STRING,
+ O_SHIPPRIORITY BIGINT,
+ O_COMMENT STRING
+)
+distribute by hash (o_orderkey) into {buckets} buckets
+tblproperties(
+ 'storage_handler' = 'com.cloudera.kudu.hive.KuduStorageHandler',
+ 'kudu.master_addresses' = '{kudu_master}:7051',
+ 'kudu.table_name' = '{target_db_name}_orders',
+ 'kudu.key_columns' = 'o_orderkey'
+);
+
+INSERT INTO TABLE {target_db_name}.orders SELECT * FROM {source_db_name}.orders;
+
+---- CUSTOMER
+CREATE TABLE IF NOT EXISTS {target_db_name}.customer (
+ C_CUSTKEY BIGINT,
+ C_NAME STRING,
+ C_ADDRESS STRING,
+ C_NATIONKEY BIGINT,
+ C_PHONE STRING,
+ C_ACCTBAL DOUBLE,
+ C_MKTSEGMENT STRING,
+ C_COMMENT STRING
+)
+distribute by hash (c_custkey) into {buckets} buckets
+tblproperties(
+ 'storage_handler' = 'com.cloudera.kudu.hive.KuduStorageHandler',
+ 'kudu.master_addresses' = '{kudu_master}:7051',
+ 'kudu.table_name' = '{target_db_name}_customer',
+ 'kudu.key_columns' = 'c_custkey'
+);
+
+INSERT INTO TABLE {target_db_name}.customer SELECT * FROM {source_db_name}.customer;
+
+---- COMPUTE STATS
+compute stats {target_db_name}.customer;
+compute stats {target_db_name}.lineitem;
+compute stats {target_db_name}.nation;
+compute stats {target_db_name}.orders;
+compute stats {target_db_name}.part;
+compute stats {target_db_name}.partsupp;
+compute stats {target_db_name}.region;
+compute stats {target_db_name}.supplier;
http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/8a49ceaa/testdata/workloads/tpcds/queries/tpcds-kudu-q19.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-kudu-q19.test b/testdata/workloads/tpcds/queries/tpcds-kudu-q19.test
new file mode 100644
index 0000000..46f358e
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-kudu-q19.test
@@ -0,0 +1,39 @@
+====
+---- QUERY: TPCDS-Q19
+select
+ i_brand_id brand_id,
+ i_brand brand,
+ i_manufact_id,
+ i_manufact,
+ round(sum(ss_ext_sales_price), 2) ext_price
+from
+ date_dim,
+ store_sales,
+ item,
+ customer,
+ customer_address,
+ store
+where
+ d_date_sk = ss_sold_date_sk
+ and ss_item_sk = i_item_sk
+ and i_manager_id = 7
+ and d_moy = 11
+ and d_year = 1999
+ and ss_customer_sk = c_customer_sk
+ and c_current_addr_sk = ca_address_sk
+ and substr(ca_zip, 1, 5) <> substr(s_zip, 1, 5)
+ and ss_store_sk = s_store_sk
+ and ss_sold_date_sk between 2451484 and 2451513
+group by
+ i_brand,
+ i_brand_id,
+ i_manufact_id,
+ i_manufact
+order by
+ ext_price desc,
+ i_brand,
+ i_brand_id,
+ i_manufact_id,
+ i_manufact
+limit 100;
+====
http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/8a49ceaa/testdata/workloads/tpcds/queries/tpcds-kudu-q27.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-kudu-q27.test b/testdata/workloads/tpcds/queries/tpcds-kudu-q27.test
new file mode 100644
index 0000000..f2723c8
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-kudu-q27.test
@@ -0,0 +1,36 @@
+====
+---- QUERY: TPCDS-Q27
+select
+ i_item_id,
+ s_state,
+ -- grouping(s_state) g_state,
+ round(avg(ss_quantity), 2) agg1,
+ round(avg(ss_list_price), 2) agg2,
+ round(avg(ss_coupon_amt), 2) agg3,
+ round(avg(ss_sales_price), 2) agg4
+from
+ store_sales,
+ customer_demographics,
+ date_dim,
+ store,
+ item
+where
+ ss_sold_date_sk = d_date_sk
+ and ss_item_sk = i_item_sk
+ and ss_store_sk = s_store_sk
+ and ss_cdemo_sk = cd_demo_sk
+ and cd_gender = 'F'
+ and cd_marital_status = 'W'
+ and cd_education_status = 'Primary'
+ and d_year = 1998
+ and s_state in ('WI', 'CA', 'TX', 'FL', 'WA', 'TN')
+ and ss_sold_date_sk between 2450815 and 2451179 -- partition key filter
+group by
+ -- rollup (i_item_id, s_state)
+ i_item_id,
+ s_state
+order by
+ i_item_id,
+ s_state
+limit 100;
+====
http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/8a49ceaa/testdata/workloads/tpcds/queries/tpcds-kudu-q3.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-kudu-q3.test b/testdata/workloads/tpcds/queries/tpcds-kudu-q3.test
new file mode 100644
index 0000000..ba1dd67
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-kudu-q3.test
@@ -0,0 +1,32 @@
+====
+---- QUERY: TPCDS-Q3
+select
+ dt.d_year,
+ item.i_brand_id brand_id,
+ item.i_brand brand,
+ round(sum(ss_ext_sales_price), 2) sum_agg
+from
+ date_dim dt,
+ store_sales,
+ item
+where
+ dt.d_date_sk = store_sales.ss_sold_date_sk
+ and store_sales.ss_item_sk = item.i_item_sk
+ and item.i_manufact_id = 436
+ and dt.d_moy = 12
+ -- partition key filters
+ and (ss_sold_date_sk between 2451149 and 2451179
+ or ss_sold_date_sk between 2451514 and 2451544
+ or ss_sold_date_sk between 2451880 and 2451910
+ or ss_sold_date_sk between 2452245 and 2452275
+ or ss_sold_date_sk between 2452610 and 2452640)
+group by
+ dt.d_year,
+ item.i_brand,
+ item.i_brand_id
+order by
+ dt.d_year,
+ sum_agg desc,
+ brand_id
+limit 100;
+====
http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/8a49ceaa/testdata/workloads/tpcds/queries/tpcds-kudu-q34.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-kudu-q34.test b/testdata/workloads/tpcds/queries/tpcds-kudu-q34.test
new file mode 100644
index 0000000..2e6e033
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-kudu-q34.test
@@ -0,0 +1,47 @@
+====
+---- QUERY: TPCDS-Q34
+select
+ c_last_name,
+ c_first_name,
+ c_salutation,
+ c_preferred_cust_flag,
+ ss_ticket_number,
+ cnt
+from
+ (select
+ ss_ticket_number,
+ ss_customer_sk,
+ count(*) cnt
+ from
+ store_sales,
+ date_dim,
+ store,
+ household_demographics
+ where
+ store_sales.ss_sold_date_sk = date_dim.d_date_sk
+ and store_sales.ss_store_sk = store.s_store_sk
+ and store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk
+ and (date_dim.d_dom between 1 and 3
+ or date_dim.d_dom between 25 and 28)
+ and (household_demographics.hd_buy_potential = '>10000'
+ or household_demographics.hd_buy_potential = 'unknown')
+ and household_demographics.hd_vehicle_count > 0
+ and (case when household_demographics.hd_vehicle_count > 0 then household_demographics.hd_dep_count / household_demographics.hd_vehicle_count else null end) > 1.2
+ and date_dim.d_year in (1998, 1998 + 1, 1998 + 2)
+ and store.s_county in ('Saginaw County', 'Sumner County', 'Appanoose County', 'Daviess County', 'Fairfield County', 'Raleigh County', 'Ziebach County', 'Williamson County')
+ and ss_sold_date_sk between 2450816 and 2451910 -- partition key filter
+ group by
+ ss_ticket_number,
+ ss_customer_sk
+ ) dn,
+ customer
+where
+ ss_customer_sk = c_customer_sk
+ and cnt between 15 and 20
+order by
+ c_last_name,
+ c_first_name,
+ c_salutation,
+ c_preferred_cust_flag desc
+limit 100000;
+====
http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/8a49ceaa/testdata/workloads/tpcds/queries/tpcds-kudu-q42.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-kudu-q42.test b/testdata/workloads/tpcds/queries/tpcds-kudu-q42.test
new file mode 100644
index 0000000..6d173e3
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-kudu-q42.test
@@ -0,0 +1,29 @@
+====
+---- QUERY: TPCDS-Q42
+select
+ dt.d_year,
+ item.i_category_id,
+ item.i_category,
+ round(sum(ss_ext_sales_price), 2)
+from
+ date_dim dt,
+ store_sales,
+ item
+where
+ dt.d_date_sk = store_sales.ss_sold_date_sk
+ and store_sales.ss_item_sk = item.i_item_sk
+ and item.i_manager_id = 1
+ and dt.d_moy = 12
+ and dt.d_year = 1998
+ and ss_sold_date_sk between 2451149 and 2451179 -- partition key filter
+group by
+ dt.d_year,
+ item.i_category_id,
+ item.i_category
+order by
+ round(sum(ss_ext_sales_price), 2) desc,
+ dt.d_year,
+ item.i_category_id,
+ item.i_category
+limit 100;
+====
http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/8a49ceaa/testdata/workloads/tpcds/queries/tpcds-kudu-q43.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-kudu-q43.test b/testdata/workloads/tpcds/queries/tpcds-kudu-q43.test
new file mode 100644
index 0000000..5c7ccda
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-kudu-q43.test
@@ -0,0 +1,37 @@
+====
+---- QUERY: TPCDS-Q43
+select
+ s_store_name,
+ s_store_id,
+ round(sum(case when (d_day_name = 'Sunday') then ss_sales_price else null end), 2) sun_sales,
+ round(sum(case when (d_day_name = 'Monday') then ss_sales_price else null end), 2) mon_sales,
+ round(sum(case when (d_day_name = 'Tuesday') then ss_sales_price else null end), 2) tue_sales,
+ round(sum(case when (d_day_name = 'Wednesday') then ss_sales_price else null end), 2) wed_sales,
+ round(sum(case when (d_day_name = 'Thursday') then ss_sales_price else null end), 2) thu_sales,
+ round(sum(case when (d_day_name = 'Friday') then ss_sales_price else null end), 2) fri_sales,
+ round(sum(case when (d_day_name = 'Saturday') then ss_sales_price else null end), 2) sat_sales
+from
+ date_dim,
+ store_sales,
+ store
+where
+ d_date_sk = ss_sold_date_sk
+ and s_store_sk = ss_store_sk
+ and s_gmt_offset = -5
+ and d_year = 1998
+ and ss_sold_date_sk between 2450816 and 2451179 -- partition key filter
+group by
+ s_store_name,
+ s_store_id
+order by
+ s_store_name,
+ s_store_id,
+ sun_sales,
+ mon_sales,
+ tue_sales,
+ wed_sales,
+ thu_sales,
+ fri_sales,
+ sat_sales
+limit 100;
+====
http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/8a49ceaa/testdata/workloads/tpcds/queries/tpcds-kudu-q46.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-kudu-q46.test b/testdata/workloads/tpcds/queries/tpcds-kudu-q46.test
new file mode 100644
index 0000000..dffecc3
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-kudu-q46.test
@@ -0,0 +1,81 @@
+====
+---- QUERY: TPCDS-Q46
+select
+ c_last_name,
+ c_first_name,
+ ca_city,
+ bought_city,
+ ss_ticket_number,
+ amt,
+ profit
+from
+ (select
+ ss_ticket_number,
+ ss_customer_sk,
+ ca_city bought_city,
+ round(sum(ss_coupon_amt), 2) amt,
+ round(sum(ss_net_profit), 2) profit
+ from
+ store_sales,
+ date_dim,
+ store,
+ household_demographics,
+ customer_address
+ where
+ store_sales.ss_sold_date_sk = date_dim.d_date_sk
+ and store_sales.ss_store_sk = store.s_store_sk
+ and store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk
+ and store_sales.ss_addr_sk = customer_address.ca_address_sk
+ and (household_demographics.hd_dep_count = 5
+ or household_demographics.hd_vehicle_count = 3)
+ and date_dim.d_dow in (6, 0)
+ and date_dim.d_year in (1999, 1999 + 1, 1999 + 2)
+ and store.s_city in ('Midway', 'Concord', 'Spring Hill', 'Brownsville', 'Greenville')
+ -- partition key filter
+ and ss_sold_date_sk in (2451181, 2451182, 2451188, 2451189, 2451195, 2451196, 2451202, 2451203, 2451209, 2451210, 2451216, 2451217,
+ 2451223, 2451224, 2451230, 2451231, 2451237, 2451238, 2451244, 2451245, 2451251, 2451252, 2451258, 2451259,
+ 2451265, 2451266, 2451272, 2451273, 2451279, 2451280, 2451286, 2451287, 2451293, 2451294, 2451300, 2451301,
+ 2451307, 2451308, 2451314, 2451315, 2451321, 2451322, 2451328, 2451329, 2451335, 2451336, 2451342, 2451343,
+ 2451349, 2451350, 2451356, 2451357, 2451363, 2451364, 2451370, 2451371, 2451377, 2451378, 2451384, 2451385,
+ 2451391, 2451392, 2451398, 2451399, 2451405, 2451406, 2451412, 2451413, 2451419, 2451420, 2451426, 2451427,
+ 2451433, 2451434, 2451440, 2451441, 2451447, 2451448, 2451454, 2451455, 2451461, 2451462, 2451468, 2451469,
+ 2451475, 2451476, 2451482, 2451483, 2451489, 2451490, 2451496, 2451497, 2451503, 2451504, 2451510, 2451511,
+ 2451517, 2451518, 2451524, 2451525, 2451531, 2451532, 2451538, 2451539, 2451545, 2451546, 2451552, 2451553,
+ 2451559, 2451560, 2451566, 2451567, 2451573, 2451574, 2451580, 2451581, 2451587, 2451588, 2451594, 2451595,
+ 2451601, 2451602, 2451608, 2451609, 2451615, 2451616, 2451622, 2451623, 2451629, 2451630, 2451636, 2451637,
+ 2451643, 2451644, 2451650, 2451651, 2451657, 2451658, 2451664, 2451665, 2451671, 2451672, 2451678, 2451679,
+ 2451685, 2451686, 2451692, 2451693, 2451699, 2451700, 2451706, 2451707, 2451713, 2451714, 2451720, 2451721,
+ 2451727, 2451728, 2451734, 2451735, 2451741, 2451742, 2451748, 2451749, 2451755, 2451756, 2451762, 2451763,
+ 2451769, 2451770, 2451776, 2451777, 2451783, 2451784, 2451790, 2451791, 2451797, 2451798, 2451804, 2451805,
+ 2451811, 2451812, 2451818, 2451819, 2451825, 2451826, 2451832, 2451833, 2451839, 2451840, 2451846, 2451847,
+ 2451853, 2451854, 2451860, 2451861, 2451867, 2451868, 2451874, 2451875, 2451881, 2451882, 2451888, 2451889,
+ 2451895, 2451896, 2451902, 2451903, 2451909, 2451910, 2451916, 2451917, 2451923, 2451924, 2451930, 2451931,
+ 2451937, 2451938, 2451944, 2451945, 2451951, 2451952, 2451958, 2451959, 2451965, 2451966, 2451972, 2451973,
+ 2451979, 2451980, 2451986, 2451987, 2451993, 2451994, 2452000, 2452001, 2452007, 2452008, 2452014, 2452015,
+ 2452021, 2452022, 2452028, 2452029, 2452035, 2452036, 2452042, 2452043, 2452049, 2452050, 2452056, 2452057,
+ 2452063, 2452064, 2452070, 2452071, 2452077, 2452078, 2452084, 2452085, 2452091, 2452092, 2452098, 2452099,
+ 2452105, 2452106, 2452112, 2452113, 2452119, 2452120, 2452126, 2452127, 2452133, 2452134, 2452140, 2452141,
+ 2452147, 2452148, 2452154, 2452155, 2452161, 2452162, 2452168, 2452169, 2452175, 2452176, 2452182, 2452183,
+ 2452189, 2452190, 2452196, 2452197, 2452203, 2452204, 2452210, 2452211, 2452217, 2452218, 2452224, 2452225,
+ 2452231, 2452232, 2452238, 2452239, 2452245, 2452246, 2452252, 2452253, 2452259, 2452260, 2452266, 2452267,
+ 2452273, 2452274)
+ group by
+ ss_ticket_number,
+ ss_customer_sk,
+ ss_addr_sk,
+ ca_city
+ ) dn,
+ customer,
+ customer_address current_addr
+where
+ ss_customer_sk = c_customer_sk
+ and customer.c_current_addr_sk = current_addr.ca_address_sk
+ and current_addr.ca_city <> bought_city
+order by
+ c_last_name,
+ c_first_name,
+ ca_city,
+ bought_city,
+ ss_ticket_number
+limit 100;
+====
http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/8a49ceaa/testdata/workloads/tpcds/queries/tpcds-kudu-q47.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-kudu-q47.test b/testdata/workloads/tpcds/queries/tpcds-kudu-q47.test
new file mode 100644
index 0000000..51305e9
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-kudu-q47.test
@@ -0,0 +1,53 @@
+====
+---- QUERY: TPCDS-Q47
+with v1 as (
+ select i_category, i_brand,
+ s_store_name, s_company_name,
+ d_year, d_moy,
+ sum(ss_sales_price) sum_sales,
+ avg(sum(ss_sales_price)) over
+ (partition by i_category, i_brand,
+ s_store_name, s_company_name, d_year)
+ avg_monthly_sales,
+ rank() over
+ (partition by i_category, i_brand,
+ s_store_name, s_company_name
+ order by d_year, d_moy) rn
+ from item, store_sales, date_dim, store
+ where ss_item_sk = i_item_sk and
+ ss_sold_date_sk = d_date_sk and
+ ss_store_sk = s_store_sk and
+ (
+ d_year = 2000 or
+ ( d_year = 2000-1 and d_moy =12) or
+ ( d_year = 2000+1 and d_moy =1)
+ )
+ group by i_category, i_brand,
+ s_store_name, s_company_name,
+ d_year, d_moy),
+ v2 as(
+ select v1.i_category, v1.i_brand
+ ,v1.d_year
+ ,round(v1.avg_monthly_sales, 2) avg_monthly_sales
+ ,round(v1.sum_sales, 2) sum_sales, round(v1_lag.sum_sales, 2) psum
+ ,round(v1_lead.sum_sales, 2) nsum
+ from v1, v1 v1_lag, v1 v1_lead
+ where v1.i_category = v1_lag.i_category and
+ v1.i_category = v1_lead.i_category and
+ v1.i_brand = v1_lag.i_brand and
+ v1.i_brand = v1_lead.i_brand and
+ v1.s_store_name = v1_lag.s_store_name and
+ v1.s_store_name = v1_lead.s_store_name and
+ v1.s_company_name = v1_lag.s_company_name and
+ v1.s_company_name = v1_lead.s_company_name and
+ v1.rn = v1_lag.rn + 1 and
+ v1.rn = v1_lead.rn - 1)
+ select * from ( select *
+ from v2
+ where d_year = 2000 and
+ avg_monthly_sales > 0 and
+ case when avg_monthly_sales > 0 then abs(sum_sales - avg_monthly_sales) / avg_monthly_sales else null end > 0.1
+ order by sum_sales - avg_monthly_sales, d_year
+ limit 100
+) as v3;
+====
http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/8a49ceaa/testdata/workloads/tpcds/queries/tpcds-kudu-q52.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-kudu-q52.test b/testdata/workloads/tpcds/queries/tpcds-kudu-q52.test
new file mode 100644
index 0000000..c3a252a
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-kudu-q52.test
@@ -0,0 +1,28 @@
+====
+---- QUERY: TPCDS-Q52
+select
+ dt.d_year,
+ item.i_brand_id brand_id,
+ item.i_brand brand,
+ round(sum(ss_ext_sales_price), 2) ext_price
+from
+ date_dim dt,
+ store_sales,
+ item
+where
+ dt.d_date_sk = store_sales.ss_sold_date_sk
+ and store_sales.ss_item_sk = item.i_item_sk
+ and item.i_manager_id = 1
+ and dt.d_moy = 12
+ and dt.d_year = 1998
+ and ss_sold_date_sk between 2451149 and 2451179 -- added for partition pruning
+group by
+ dt.d_year,
+ item.i_brand,
+ item.i_brand_id
+order by
+ dt.d_year,
+ ext_price desc,
+ brand_id
+limit 100;
+====
http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/8a49ceaa/testdata/workloads/tpcds/queries/tpcds-kudu-q53.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-kudu-q53.test b/testdata/workloads/tpcds/queries/tpcds-kudu-q53.test
new file mode 100644
index 0000000..a3ac8e7
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-kudu-q53.test
@@ -0,0 +1,38 @@
+====
+---- QUERY: TPCDS-Q53
+select
+ *
+from
+ (select
+ i_manufact_id,
+ round(sum(ss_sales_price), 2) sum_sales
+ -- avg(sum(ss_sales_price)) over (partition by i_manufact_id) avg_quarterly_sales
+ from
+ item,
+ store_sales,
+ date_dim,
+ store
+ where
+ ss_item_sk = i_item_sk
+ and ss_sold_date_sk = d_date_sk
+ and ss_store_sk = s_store_sk
+ and d_month_seq in (1212, 1212 + 1, 1212 + 2, 1212 + 3, 1212 + 4, 1212 + 5, 1212 + 6, 1212 + 7, 1212 + 8, 1212 + 9, 1212 + 10, 1212 + 11)
+ and ((i_category in ('Books', 'Children', 'Electronics')
+ and i_class in ('personal', 'portable', 'reference', 'self-help')
+ and i_brand in ('scholaramalgamalg #14', 'scholaramalgamalg #7', 'exportiunivamalg #9', 'scholaramalgamalg #9'))
+ or (i_category in ('Women', 'Music', 'Men')
+ and i_class in ('accessories', 'classical', 'fragrances', 'pants')
+ and i_brand in ('amalgimporto #1', 'edu packscholar #1', 'exportiimporto #1', 'importoamalg #1')))
+ and ss_sold_date_sk between 2451911 and 2452275 -- partition key filter
+ group by
+ i_manufact_id,
+ d_qoy
+ ) tmp1
+-- where
+-- case when avg_quarterly_sales > 0 then abs (sum_sales - avg_quarterly_sales) / avg_quarterly_sales else null end > 0.1
+order by
+ -- avg_quarterly_sales,
+ sum_sales,
+ i_manufact_id
+limit 100;
+====
http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/8a49ceaa/testdata/workloads/tpcds/queries/tpcds-kudu-q55.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-kudu-q55.test b/testdata/workloads/tpcds/queries/tpcds-kudu-q55.test
new file mode 100644
index 0000000..15223c5
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-kudu-q55.test
@@ -0,0 +1,25 @@
+====
+---- QUERY: TPCDS-Q55
+select
+ i_brand_id brand_id,
+ i_brand brand,
+ round(sum(ss_ext_sales_price), 2) ext_price
+from
+ date_dim,
+ store_sales,
+ item
+where
+ d_date_sk = ss_sold_date_sk
+ and ss_item_sk = i_item_sk
+ and i_manager_id = 36
+ and d_moy = 12
+ and d_year = 2001
+ and ss_sold_date_sk between 2452245 and 2452275 -- partition key filter
+group by
+ i_brand,
+ i_brand_id
+order by
+ ext_price desc,
+ i_brand_id
+limit 100;
+====
http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/8a49ceaa/testdata/workloads/tpcds/queries/tpcds-kudu-q59.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-kudu-q59.test b/testdata/workloads/tpcds/queries/tpcds-kudu-q59.test
new file mode 100644
index 0000000..e4e3afb
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-kudu-q59.test
@@ -0,0 +1,84 @@
+====
+---- QUERY: TPCDS-Q59
+with
+ wss as
+ (select
+ d_week_seq,
+ ss_store_sk,
+ round(sum(case when (d_day_name = 'Sunday') then ss_sales_price else null end), 2) sun_sales,
+ round(sum(case when (d_day_name = 'Monday') then ss_sales_price else null end), 2) mon_sales,
+ round(sum(case when (d_day_name = 'Tuesday') then ss_sales_price else null end), 2) tue_sales,
+ round(sum(case when (d_day_name = 'Wednesday') then ss_sales_price else null end), 2) wed_sales,
+ round(sum(case when (d_day_name = 'Thursday') then ss_sales_price else null end), 2) thu_sales,
+ round(sum(case when (d_day_name = 'Friday') then ss_sales_price else null end), 2) fri_sales,
+ round(sum(case when (d_day_name = 'Saturday') then ss_sales_price else null end), 2) sat_sales
+ from
+ store_sales,
+ date_dim
+ where
+ d_date_sk = ss_sold_date_sk
+ group by
+ d_week_seq,
+ ss_store_sk
+ )
+select
+ s_store_name1,
+ s_store_id1,
+ d_week_seq1,
+ sun_sales1 / sun_sales2,
+ mon_sales1 / mon_sales2,
+ tue_sales1 / tue_sales2,
+ wed_sales1 / wed_sales2,
+ thu_sales1 / thu_sales2,
+ fri_sales1 / fri_sales2,
+ sat_sales1 / sat_sales2
+from
+ (select
+ s_store_name s_store_name1,
+ wss.d_week_seq d_week_seq1,
+ s_store_id s_store_id1,
+ sun_sales sun_sales1,
+ mon_sales mon_sales1,
+ tue_sales tue_sales1,
+ wed_sales wed_sales1,
+ thu_sales thu_sales1,
+ fri_sales fri_sales1,
+ sat_sales sat_sales1
+ from
+ wss,
+ store,
+ date_dim d
+ where
+ d.d_week_seq = wss.d_week_seq
+ and ss_store_sk = s_store_sk
+ and d_month_seq between 1185 and 1185 + 11
+ ) y,
+ (select
+ s_store_name s_store_name2,
+ wss.d_week_seq d_week_seq2,
+ s_store_id s_store_id2,
+ sun_sales sun_sales2,
+ mon_sales mon_sales2,
+ tue_sales tue_sales2,
+ wed_sales wed_sales2,
+ thu_sales thu_sales2,
+ fri_sales fri_sales2,
+ sat_sales sat_sales2
+ from
+ wss,
+ store,
+ date_dim d
+ where
+ d.d_week_seq = wss.d_week_seq
+ and ss_store_sk = s_store_sk
+ and d_month_seq between 1185 + 12 and 1185 + 23
+ ) x
+where
+ s_store_id1 = s_store_id2
+ and d_week_seq1 = d_week_seq2 - 52
+order by
+ s_store_name1,
+ s_store_id1,
+ d_week_seq1
+limit 100;
+====
http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/8a49ceaa/testdata/workloads/tpcds/queries/tpcds-kudu-q6.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-kudu-q6.test b/testdata/workloads/tpcds/queries/tpcds-kudu-q6.test
new file mode 100644
index 0000000..82e3f66
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-kudu-q6.test
@@ -0,0 +1,28 @@
+====
+---- QUERY: TPCDS-Q6
+select * from (
+ select a.ca_state state, count(*) cnt
+ from customer_address a
+ ,customer c
+ ,store_sales s
+ ,date_dim d
+ ,item i
+ where
+ a.ca_address_sk = c.c_current_addr_sk
+ and c.c_customer_sk = s.ss_customer_sk
+ and s.ss_sold_date_sk = d.d_date_sk
+ and s.ss_item_sk = i.i_item_sk
+ and d.d_month_seq =
+ (select distinct (d_month_seq)
+ from date_dim
+ where d_year = 1999
+ and d_moy = 1
+ limit 1)
+ and i.i_current_price > 1.2 *
+ (select round(avg(j.i_current_price), 2)
+ from item j
+ where j.i_category = i.i_category)
+ group by a.ca_state
+ having count(*) >= 10
+ order by cnt limit 100) as t;
+====
http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/8a49ceaa/testdata/workloads/tpcds/queries/tpcds-kudu-q61.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-kudu-q61.test b/testdata/workloads/tpcds/queries/tpcds-kudu-q61.test
new file mode 100644
index 0000000..c31e167
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-kudu-q61.test
@@ -0,0 +1,43 @@
+====
+---- QUERY: TPCDS-Q61
+select promotions,total,cast(promotions as DOUBLE)/cast(total as DOUBLE)*100
+from
+ (select round(sum(ss_ext_sales_price), 2) promotions
+ from store_sales
+ ,store
+ ,promotion
+ ,date_dim
+ ,customer
+ ,customer_address
+ ,item
+ where ss_sold_date_sk = d_date_sk
+ and ss_store_sk = s_store_sk
+ and ss_promo_sk = p_promo_sk
+ and ss_customer_sk= c_customer_sk
+ and ca_address_sk = c_current_addr_sk
+ and ss_item_sk = i_item_sk
+ and ca_gmt_offset = -5
+ and i_category = 'Books'
+ and (p_channel_dmail = 'Y' or p_channel_email = 'Y' or p_channel_tv = 'Y')
+ and s_gmt_offset = -5
+ and d_year = 2000
+ and d_moy = 11) promotional_sales,
+ (select round(sum(ss_ext_sales_price), 2) total
+ from store_sales
+ ,store
+ ,date_dim
+ ,customer
+ ,customer_address
+ ,item
+ where ss_sold_date_sk = d_date_sk
+ and ss_store_sk = s_store_sk
+ and ss_customer_sk= c_customer_sk
+ and ca_address_sk = c_current_addr_sk
+ and ss_item_sk = i_item_sk
+ and ca_gmt_offset = -5
+ and i_category = 'Books'
+ and s_gmt_offset = -5
+ and d_year = 2000
+ and d_moy = 11) all_sales
+order by promotions, total;
+====
http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/8a49ceaa/testdata/workloads/tpcds/queries/tpcds-kudu-q63.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-kudu-q63.test b/testdata/workloads/tpcds/queries/tpcds-kudu-q63.test
new file mode 100644
index 0000000..387db25
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-kudu-q63.test
@@ -0,0 +1,38 @@
+====
+---- QUERY: TPCDS-Q63
+select
+ *
+from
+ (select
+ i_manager_id,
+ round(sum(ss_sales_price), 2) sum_sales,
+ round(avg(sum(ss_sales_price)) over (partition by i_manager_id), 2) avg_monthly_sales
+ from
+ item,
+ store_sales,
+ date_dim,
+ store
+ where
+ ss_item_sk = i_item_sk
+ and ss_sold_date_sk = d_date_sk
+ and ss_store_sk = s_store_sk
+ and d_month_seq in (1212, 1212 + 1, 1212 + 2, 1212 + 3, 1212 + 4, 1212 + 5, 1212 + 6, 1212 + 7, 1212 + 8, 1212 + 9, 1212 + 10, 1212 + 11)
+ and ((i_category in ('Books', 'Children', 'Electronics')
+ and i_class in ('personal', 'portable', 'refernece', 'self-help')
+ and i_brand in ('scholaramalgamalg #14', 'scholaramalgamalg #7', 'exportiunivamalg #9', 'scholaramalgamalg #9'))
+ or (i_category in ('Women', 'Music', 'Men')
+ and i_class in ('accessories', 'classical', 'fragrances', 'pants')
+ and i_brand in ('amalgimporto #1', 'edu packscholar #1', 'exportiimporto #1', 'importoamalg #1')))
+ and ss_sold_date_sk between 2451911 and 2452275 -- partition key filter
+ group by
+ i_manager_id,
+ d_moy
+ ) tmp1
+where
+case when avg_monthly_sales > 0 then abs (sum_sales - avg_monthly_sales) / avg_monthly_sales else null end > 0.1
+order by
+ i_manager_id,
+ avg_monthly_sales,
+ sum_sales
+limit 100;
+====
http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/8a49ceaa/testdata/workloads/tpcds/queries/tpcds-kudu-q65.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-kudu-q65.test b/testdata/workloads/tpcds/queries/tpcds-kudu-q65.test
new file mode 100644
index 0000000..e1d3af0
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-kudu-q65.test
@@ -0,0 +1,63 @@
+====
+---- QUERY: TPCDS-Q65
+select
+ s_store_name,
+ i_item_desc,
+ sc.revenue,
+ i_current_price,
+ i_wholesale_cost,
+ i_brand
+from
+ store,
+ item,
+ (select
+ ss_store_sk,
+ round(avg(revenue), 2) as ave
+ from
+ (select
+ ss_store_sk,
+ ss_item_sk,
+ round(sum(ss_sales_price), 2) as revenue
+ from
+ store_sales,
+ date_dim
+ where
+ ss_sold_date_sk = d_date_sk
+ and d_month_seq between 1212 and 1212 + 11
+ and ss_sold_date_sk between 2451911 and 2452275 -- partition key filter
+ group by
+ ss_store_sk,
+ ss_item_sk
+ ) sa
+ group by
+ ss_store_sk
+ ) sb,
+ (select
+ ss_store_sk,
+ ss_item_sk,
+ round(sum(ss_sales_price), 2) as revenue
+ from
+ store_sales,
+ date_dim
+ where
+ ss_sold_date_sk = d_date_sk
+ and d_month_seq between 1212 and 1212 + 11
+ and ss_sold_date_sk between 2451911 and 2452275 -- partition key filter
+ group by
+ ss_store_sk,
+ ss_item_sk
+ ) sc
+where
+ sb.ss_store_sk = sc.ss_store_sk
+ and sc.revenue <= 0.1 * sb.ave
+ and s_store_sk = sc.ss_store_sk
+ and i_item_sk = sc.ss_item_sk
+order by
+ s_store_name,
+ i_item_desc,
+ sc.revenue, -- for consistent ordering of results
+ i_current_price, -- for consistent ordering of results
+ i_wholesale_cost, -- for consistent ordering of results
+ i_brand -- for consistent ordering of results
+limit 100;
+====
http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/8a49ceaa/testdata/workloads/tpcds/queries/tpcds-kudu-q68.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-kudu-q68.test b/testdata/workloads/tpcds/queries/tpcds-kudu-q68.test
new file mode 100644
index 0000000..2e0f994
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-kudu-q68.test
@@ -0,0 +1,62 @@
+====
+---- QUERY: TPCDS-Q68
+select
+ c_last_name,
+ c_first_name,
+ ca_city,
+ bought_city,
+ ss_ticket_number,
+ extended_price,
+ extended_tax,
+ list_price
+from
+ (select
+ ss_ticket_number,
+ ss_customer_sk,
+ ca_city bought_city,
+ round(sum(ss_ext_sales_price), 2) extended_price,
+ round(sum(ss_ext_list_price), 2) list_price,
+ round(sum(ss_ext_tax), 2) extended_tax
+ from
+ store_sales,
+ date_dim,
+ store,
+ household_demographics,
+ customer_address
+ where
+ store_sales.ss_sold_date_sk = date_dim.d_date_sk
+ and store_sales.ss_store_sk = store.s_store_sk
+ and store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk
+ and store_sales.ss_addr_sk = customer_address.ca_address_sk
+ -- and date_dim.d_dom between 1 and 2
+ and (household_demographics.hd_dep_count = 5
+ or household_demographics.hd_vehicle_count = 3)
+ -- and date_dim.d_year in (1999, 1999 + 1, 1999 + 2)
+ and store.s_city in ('Midway', 'Fairview')
+ -- partition key filter
+ -- and ss_sold_date_sk in (2451180, 2451181, 2451211, 2451212, 2451239, 2451240, 2451270, 2451271, 2451300, 2451301, 2451331,
+ -- 2451332, 2451361, 2451362, 2451392, 2451393, 2451423, 2451424, 2451453, 2451454, 2451484, 2451485,
+ -- 2451514, 2451515, 2451545, 2451546, 2451576, 2451577, 2451605, 2451606, 2451636, 2451637, 2451666,
+ -- 2451667, 2451697, 2451698, 2451727, 2451728, 2451758, 2451759, 2451789, 2451790, 2451819, 2451820,
+ -- 2451850, 2451851, 2451880, 2451881, 2451911, 2451912, 2451942, 2451943, 2451970, 2451971, 2452001,
+ -- 2452002, 2452031, 2452032, 2452062, 2452063, 2452092, 2452093, 2452123, 2452124, 2452154, 2452155,
+ -- 2452184, 2452185, 2452215, 2452216, 2452245, 2452246)
+ and ss_sold_date_sk between 2451180 and 2451269 -- partition key filter (3 months)
+ and d_date between '1999-01-01' and '1999-03-31'
+ group by
+ ss_ticket_number,
+ ss_customer_sk,
+ ss_addr_sk,
+ ca_city
+ ) dn,
+ customer,
+ customer_address current_addr
+where
+ ss_customer_sk = c_customer_sk
+ and customer.c_current_addr_sk = current_addr.ca_address_sk
+ and current_addr.ca_city <> bought_city
+order by
+ c_last_name,
+ ss_ticket_number
+limit 100;
+====
http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/8a49ceaa/testdata/workloads/tpcds/queries/tpcds-kudu-q7.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-kudu-q7.test b/testdata/workloads/tpcds/queries/tpcds-kudu-q7.test
new file mode 100644
index 0000000..0130d4c
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-kudu-q7.test
@@ -0,0 +1,32 @@
+====
+---- QUERY: TPCDS-Q7
+select
+ i_item_id,
+ round(avg(ss_quantity), 2) agg1,
+ round(avg(ss_list_price), 2) agg2,
+ round(avg(ss_coupon_amt), 2) agg3,
+ round(avg(ss_sales_price), 1) agg4
+from
+ store_sales,
+ customer_demographics,
+ date_dim,
+ item,
+ promotion
+where
+ ss_sold_date_sk = d_date_sk
+ and ss_item_sk = i_item_sk
+ and ss_cdemo_sk = cd_demo_sk
+ and ss_promo_sk = p_promo_sk
+ and cd_gender = 'F'
+ and cd_marital_status = 'W'
+ and cd_education_status = 'Primary'
+ and (p_channel_email = 'N'
+ or p_channel_event = 'N')
+ and d_year = 1998
+ and ss_sold_date_sk between 2450815 and 2451179 -- partition key filter
+group by
+ i_item_id
+order by
+ i_item_id
+limit 100;
+====
http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/8a49ceaa/testdata/workloads/tpcds/queries/tpcds-kudu-q73.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-kudu-q73.test b/testdata/workloads/tpcds/queries/tpcds-kudu-q73.test
new file mode 100644
index 0000000..66e2a8f
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-kudu-q73.test
@@ -0,0 +1,51 @@
+====
+---- QUERY: TPCDS-Q73
+select
+ c_last_name,
+ c_first_name,
+ c_salutation,
+ c_preferred_cust_flag,
+ ss_ticket_number,
+ cnt
+from
+ (select
+ ss_ticket_number,
+ ss_customer_sk,
+ count(*) cnt
+ from
+ store_sales,
+ date_dim,
+ store,
+ household_demographics
+ where
+ store_sales.ss_sold_date_sk = date_dim.d_date_sk
+ and store_sales.ss_store_sk = store.s_store_sk
+ and store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk
+ -- and date_dim.d_dom between 1 and 2
+ and (household_demographics.hd_buy_potential = '>10000'
+ or household_demographics.hd_buy_potential = 'unknown')
+ and household_demographics.hd_vehicle_count > 0
+ and case when household_demographics.hd_vehicle_count > 0 then household_demographics.hd_dep_count / household_demographics.hd_vehicle_count else null end > 1
+ -- and date_dim.d_year in (1998, 1998 + 1, 1998 + 2)
+ and store.s_county in ('Saginaw County', 'Sumner County', 'Appanoose County', 'Daviess County')
+ -- partition key filter
+ -- and ss_sold_date_sk in (2450816, 2450846, 2450847, 2450874, 2450875, 2450905, 2450906, 2450935, 2450936, 2450966, 2450967,
+ -- 2450996, 2450997, 2451027, 2451028, 2451058, 2451059, 2451088, 2451089, 2451119, 2451120, 2451149,
+ -- 2451150, 2451180, 2451181, 2451211, 2451212, 2451239, 2451240, 2451270, 2451271, 2451300, 2451301,
+ -- 2451331, 2451332, 2451361, 2451362, 2451392, 2451393, 2451423, 2451424, 2451453, 2451454, 2451484,
+ -- 2451485, 2451514, 2451515, 2451545, 2451546, 2451576, 2451577, 2451605, 2451606, 2451636, 2451637,
+ -- 2451666, 2451667, 2451697, 2451698, 2451727, 2451728, 2451758, 2451759, 2451789, 2451790, 2451819,
+ -- 2451820, 2451850, 2451851, 2451880, 2451881)
+ and ss_sold_date_sk between 2451180 and 2451269 -- partition key filter (3 months)
+ group by
+ ss_ticket_number,
+ ss_customer_sk
+ ) dj,
+ customer
+where
+ ss_customer_sk = c_customer_sk
+ and cnt between 1 and 5
+order by
+ cnt desc
+limit 1000;
+====
http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/8a49ceaa/testdata/workloads/tpcds/queries/tpcds-kudu-q79.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/tpcds/queries/tpcds-kudu-q79.test b/testdata/workloads/tpcds/queries/tpcds-kudu-q79.test
new file mode 100644
index 0000000..fcaca50
--- /dev/null
+++ b/testdata/workloads/tpcds/queries/tpcds-kudu-q79.test
@@ -0,0 +1,61 @@
+====
+---- QUERY: TPCDS-Q79
+select
+ c_last_name,
+ c_first_name,
+ substr(s_city, 1, 30),
+ ss_ticket_number,
+ amt,
+ profit
+from
+ (select
+ ss_ticket_number,
+ ss_customer_sk,
+ store.s_city,
+ round(sum(ss_coupon_amt), 2) amt,
+ round(sum(ss_net_profit), 2) profit
+ from
+ store_sales,
+ date_dim,
+ store,
+ household_demographics
+ where
+ store_sales.ss_sold_date_sk = date_dim.d_date_sk
+ and store_sales.ss_store_sk = store.s_store_sk
+ and store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk
+ and (household_demographics.hd_dep_count = 8
+ or household_demographics.hd_vehicle_count > 0)
+ -- and date_dim.d_dow = 1
+ -- and date_dim.d_year in (1998, 1998 + 1, 1998 + 2)
+ and store.s_number_employees between 200 and 295
+ -- partition key filter
+ -- and ss_sold_date_sk in (2450819, 2450826, 2450833, 2450840, 2450847, 2450854, 2450861, 2450868, 2450875, 2450882, 2450889,
+ -- 2450896, 2450903, 2450910, 2450917, 2450924, 2450931, 2450938, 2450945, 2450952, 2450959, 2450966, 2450973, 2450980, 2450987,
+ -- 2450994, 2451001, 2451008, 2451015, 2451022, 2451029, 2451036, 2451043, 2451050, 2451057, 2451064, 2451071, 2451078, 2451085,
+ -- 2451092, 2451099, 2451106, 2451113, 2451120, 2451127, 2451134, 2451141, 2451148, 2451155, 2451162, 2451169, 2451176, 2451183,
+ -- 2451190, 2451197, 2451204, 2451211, 2451218, 2451225, 2451232, 2451239, 2451246, 2451253, 2451260, 2451267, 2451274, 2451281,
+ -- 2451288, 2451295, 2451302, 2451309, 2451316, 2451323, 2451330, 2451337, 2451344, 2451351, 2451358, 2451365, 2451372, 2451379,
+ -- 2451386, 2451393, 2451400, 2451407, 2451414, 2451421, 2451428, 2451435, 2451442, 2451449, 2451456, 2451463, 2451470, 2451477,
+ -- 2451484, 2451491, 2451498, 2451505, 2451512, 2451519, 2451526, 2451533, 2451540, 2451547, 2451554, 2451561, 2451568, 2451575,
+ -- 2451582, 2451589, 2451596, 2451603, 2451610, 2451617, 2451624, 2451631, 2451638, 2451645, 2451652, 2451659, 2451666, 2451673,
+ -- 2451680, 2451687, 2451694, 2451701, 2451708, 2451715, 2451722, 2451729, 2451736, 2451743, 2451750, 2451757, 2451764, 2451771,
+ -- 2451778, 2451785, 2451792, 2451799, 2451806, 2451813, 2451820, 2451827, 2451834, 2451841, 2451848, 2451855, 2451862, 2451869,
+ -- 2451876, 2451883, 2451890, 2451897, 2451904)
+ and d_date between '1999-01-01' and '1999-03-31'
+ and ss_sold_date_sk between 2451180 and 2451269 -- partition key filter
+ group by
+ ss_ticket_number,
+ ss_customer_sk,
+ ss_addr_sk,
+ store.s_city
+ ) ms,
+ customer
+where
+ ss_customer_sk = c_customer_sk
+order by
+ c_last_name,
+ c_first_name,
+ substr(s_city, 1, 30),
+ profit
+limit 100;
+====