HIVE-11107 : Support for Performance regression test suite with TPCDS (Hari Subramaniyan, reviewed by Ashutosh Chauhan)
Project: http://git-wip-us.apache.org/repos/asf/hive/repo Commit: http://git-wip-us.apache.org/repos/asf/hive/commit/09b6f9a3 Tree: http://git-wip-us.apache.org/repos/asf/hive/tree/09b6f9a3 Diff: http://git-wip-us.apache.org/repos/asf/hive/diff/09b6f9a3 Branch: refs/heads/master Commit: 09b6f9a36b7c96ef91e70f6364c034b3ac89a2f6 Parents: e091bc2 Author: Hari Subramaniyan <harisan...@apache.org> Authored: Tue Dec 15 14:15:00 2015 -0800 Committer: Hari Subramaniyan <harisan...@apache.org> Committed: Tue Dec 15 14:15:00 2015 -0800 ---------------------------------------------------------------------- data/conf/perf-reg/hive-site.xml | 285 +++++ data/conf/perf-reg/tez-site.xml | 6 + .../metastore_export/csv/TABLE_PARAMS.txt | 102 ++ .../metastore_export/csv/TAB_COL_STATS.txt | 259 ++++ data/scripts/q_perf_test_init.sql | 616 +++++++++ itests/qtest/pom.xml | 20 + .../org/apache/hadoop/hive/ql/QTestUtil.java | 163 +++ .../upgrade/derby/022-HIVE-11107.derby.sql | 2 + .../upgrade/derby/hive-schema-2.1.0.derby.sql | 4 +- .../test/queries/clientpositive/perf/query12.q | 1 + .../test/queries/clientpositive/perf/query13.q | 54 + .../test/queries/clientpositive/perf/query15.q | 1 + .../test/queries/clientpositive/perf/query17.q | 1 + .../test/queries/clientpositive/perf/query18.q | 1 + .../test/queries/clientpositive/perf/query19.q | 1 + .../test/queries/clientpositive/perf/query20.q | 1 + .../test/queries/clientpositive/perf/query21.q | 26 + .../test/queries/clientpositive/perf/query22.q | 1 + .../test/queries/clientpositive/perf/query25.q | 1 + .../test/queries/clientpositive/perf/query26.q | 1 + .../test/queries/clientpositive/perf/query27.q | 1 + .../test/queries/clientpositive/perf/query28.q | 52 + .../test/queries/clientpositive/perf/query29.q | 1 + .../test/queries/clientpositive/perf/query3.q | 1 + .../test/queries/clientpositive/perf/query31.q | 2 + .../test/queries/clientpositive/perf/query32.q | 19 + .../test/queries/clientpositive/perf/query34.q | 2 + .../test/queries/clientpositive/perf/query39.q | 2 + .../test/queries/clientpositive/perf/query40.q | 1 + .../test/queries/clientpositive/perf/query42.q | 1 + .../test/queries/clientpositive/perf/query43.q | 1 + .../test/queries/clientpositive/perf/query45.q | 1 + .../test/queries/clientpositive/perf/query46.q | 1 + .../test/queries/clientpositive/perf/query48.q | 2 + .../test/queries/clientpositive/perf/query50.q | 58 + .../test/queries/clientpositive/perf/query51.q | 42 + .../test/queries/clientpositive/perf/query52.q | 1 + .../test/queries/clientpositive/perf/query54.q | 1 + .../test/queries/clientpositive/perf/query55.q | 1 + .../test/queries/clientpositive/perf/query58.q | 47 + .../test/queries/clientpositive/perf/query64.q | 2 + .../test/queries/clientpositive/perf/query65.q | 38 + .../test/queries/clientpositive/perf/query66.q | 219 ++++ .../test/queries/clientpositive/perf/query67.q | 44 + .../test/queries/clientpositive/perf/query68.q | 1 + .../test/queries/clientpositive/perf/query7.q | 1 + .../test/queries/clientpositive/perf/query70.q | 1 + .../test/queries/clientpositive/perf/query71.q | 2 + .../test/queries/clientpositive/perf/query72.q | 1 + .../test/queries/clientpositive/perf/query73.q | 2 + .../test/queries/clientpositive/perf/query75.q | 1 + .../test/queries/clientpositive/perf/query76.q | 1 + .../test/queries/clientpositive/perf/query79.q | 1 + .../test/queries/clientpositive/perf/query80.q | 1 + .../test/queries/clientpositive/perf/query82.q | 1 + .../test/queries/clientpositive/perf/query84.q | 1 + .../test/queries/clientpositive/perf/query85.q | 1 + .../test/queries/clientpositive/perf/query87.q | 1 + .../test/queries/clientpositive/perf/query88.q | 92 ++ .../test/queries/clientpositive/perf/query89.q | 27 + .../test/queries/clientpositive/perf/query90.q | 2 + .../test/queries/clientpositive/perf/query91.q | 2 + .../test/queries/clientpositive/perf/query92.q | 1 + .../test/queries/clientpositive/perf/query93.q | 1 + .../test/queries/clientpositive/perf/query94.q | 1 + .../test/queries/clientpositive/perf/query95.q | 1 + .../test/queries/clientpositive/perf/query96.q | 1 + .../test/queries/clientpositive/perf/query97.q | 1 + .../test/queries/clientpositive/perf/query98.q | 2 + .../results/clientpositive/perf/query12.q.out | 129 ++ .../results/clientpositive/perf/query13.q.out | 300 +++++ .../results/clientpositive/perf/query15.q.out | 153 +++ .../results/clientpositive/perf/query17.q.out | 264 ++++ .../results/clientpositive/perf/query18.q.out | 236 ++++ .../results/clientpositive/perf/query19.q.out | 207 +++ .../results/clientpositive/perf/query20.q.out | 138 ++ .../results/clientpositive/perf/query21.q.out | 201 +++ .../results/clientpositive/perf/query22.q.out | 148 +++ .../results/clientpositive/perf/query25.q.out | 264 ++++ .../results/clientpositive/perf/query26.q.out | 174 +++ .../results/clientpositive/perf/query27.q.out | 178 +++ .../results/clientpositive/perf/query28.q.out | 357 ++++++ .../results/clientpositive/perf/query29.q.out | 264 ++++ .../results/clientpositive/perf/query3.q.out | 119 ++ .../results/clientpositive/perf/query31.q.out | 641 ++++++++++ .../results/clientpositive/perf/query32.q.out | 205 +++ .../results/clientpositive/perf/query34.q.out | 177 +++ .../results/clientpositive/perf/query39.q.out | 313 +++++ .../results/clientpositive/perf/query40.q.out | 177 +++ .../results/clientpositive/perf/query42.q.out | 117 ++ .../results/clientpositive/perf/query43.q.out | 118 ++ .../results/clientpositive/perf/query45.q.out | 210 +++ .../results/clientpositive/perf/query46.q.out | 241 ++++ .../results/clientpositive/perf/query48.q.out | 160 +++ .../results/clientpositive/perf/query50.q.out | 287 +++++ .../results/clientpositive/perf/query51.q.out | 283 +++++ .../results/clientpositive/perf/query52.q.out | 118 ++ .../results/clientpositive/perf/query54.q.out | 314 +++++ .../results/clientpositive/perf/query55.q.out | 118 ++ .../results/clientpositive/perf/query58.q.out | 602 +++++++++ .../results/clientpositive/perf/query64.q.out | 1192 ++++++++++++++++++ .../results/clientpositive/perf/query65.q.out | 315 +++++ .../results/clientpositive/perf/query66.q.out | 796 ++++++++++++ .../results/clientpositive/perf/query67.q.out | 249 ++++ .../results/clientpositive/perf/query68.q.out | 241 ++++ .../results/clientpositive/perf/query7.q.out | 174 +++ .../results/clientpositive/perf/query70.q.out | 268 ++++ .../results/clientpositive/perf/query71.q.out | 242 ++++ .../results/clientpositive/perf/query72.q.out | 352 ++++++ .../results/clientpositive/perf/query73.q.out | 177 +++ .../results/clientpositive/perf/query75.q.out | 719 +++++++++++ .../results/clientpositive/perf/query76.q.out | 284 +++++ .../results/clientpositive/perf/query79.q.out | 181 +++ .../results/clientpositive/perf/query80.q.out | 602 +++++++++ .../results/clientpositive/perf/query82.q.out | 127 ++ .../results/clientpositive/perf/query84.q.out | 171 +++ .../results/clientpositive/perf/query85.q.out | 275 ++++ .../results/clientpositive/perf/query87.q.out | 334 +++++ .../results/clientpositive/perf/query88.q.out | 1157 +++++++++++++++++ .../results/clientpositive/perf/query89.q.out | 224 ++++ .../results/clientpositive/perf/query90.q.out | 260 ++++ .../results/clientpositive/perf/query91.q.out | 232 ++++ .../results/clientpositive/perf/query92.q.out | 160 +++ .../results/clientpositive/perf/query93.q.out | 117 ++ .../results/clientpositive/perf/query94.q.out | 228 ++++ .../results/clientpositive/perf/query95.q.out | 275 ++++ .../results/clientpositive/perf/query96.q.out | 135 ++ .../results/clientpositive/perf/query97.q.out | 163 +++ .../results/clientpositive/perf/query98.q.out | 135 ++ ql/src/test/templates/TestPerfCliDriver.vm | 175 +++ 130 files changed, 19403 insertions(+), 3 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/hive/blob/09b6f9a3/data/conf/perf-reg/hive-site.xml ---------------------------------------------------------------------- diff --git a/data/conf/perf-reg/hive-site.xml b/data/conf/perf-reg/hive-site.xml new file mode 100644 index 0000000..77bbe91 --- /dev/null +++ b/data/conf/perf-reg/hive-site.xml @@ -0,0 +1,285 @@ +<?xml version="1.0"?> +<?xml-stylesheet type="text/xsl" href="configuration.xsl"?> +<!-- + 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. +--> + +<configuration> + +<property> + <name>hive.in.test</name> + <value>true</value> + <description>Internal marker for test. Used for masking env-dependent values</description> +</property> + +<!-- Hive Configuration can either be stored in this file or in the hadoop configuration files --> +<!-- that are implied by Hadoop setup variables. --> +<!-- Aside from Hadoop setup variables - this file is provided as a convenience so that Hive --> +<!-- users do not have to edit hadoop configuration files (that may be managed as a centralized --> +<!-- resource). --> + +<!-- Hive Execution Parameters --> +<property> + <name>hadoop.tmp.dir</name> + <value>${test.tmp.dir}/hadoop-tmp</value> + <description>A base for other temporary directories.</description> +</property> + +<property> + <name>hive.tez.container.size</name> + <value>128</value> + <description></description> +</property> + +<property> + <name>hive.merge.tezfiles</name> + <value>false</value> + <description>Merge small files at the end of a Tez DAG</description> +</property> + +<property> + <name>hive.tez.input.format</name> + <value>org.apache.hadoop.hive.ql.io.HiveInputFormat</value> + <description>The default input format for tez. Tez groups splits in the AM.</description> +</property> + +<property> + <name>hive.exec.scratchdir</name> + <value>${test.tmp.dir}/scratchdir</value> + <description>Scratch space for Hive jobs</description> +</property> + +<property> + <name>datanucleus.autoCreateSchema</name> + <value>true</value> +</property> + +<property> + <name>datanucleus.fixedDatastore</name> + <value>false</value> +</property> + +<property> + <name>hive.metastore.schema.verification</name> + <value>false</value> +</property> + +<property> + <name>hive.exec.local.scratchdir</name> + <value>${test.tmp.dir}/localscratchdir/</value> + <description>Local scratch space for Hive jobs</description> +</property> + +<property> + <name>javax.jdo.option.ConnectionURL</name> + <value>jdbc:derby:;databaseName=${test.tmp.dir}/junit_metastore_db;create=true</value> +</property> + +<property> + <name>javax.jdo.option.ConnectionDriverName</name> + <value>org.apache.derby.jdbc.EmbeddedDriver</value> +</property> + +<property> + <name>javax.jdo.option.ConnectionUserName</name> + <value>APP</value> +</property> + +<property> + <name>javax.jdo.option.ConnectionPassword</name> + <value>mine</value> +</property> + +<property> + <!-- this should eventually be deprecated since the metastore should supply this --> + <name>hive.metastore.warehouse.dir</name> + <value>${test.warehouse.dir}</value> + <description></description> +</property> + +<property> + <name>hive.metastore.metadb.dir</name> + <value>file://${test.tmp.dir}/metadb/</value> + <description> + Required by metastore server or if the uris argument below is not supplied + </description> +</property> + +<property> + <name>test.log.dir</name> + <value>${test.tmp.dir}/log/</value> + <description></description> +</property> + +<property> + <name>test.data.files</name> + <value>${hive.root}/data/files</value> + <description></description> +</property> + +<property> + <name>test.data.scripts</name> + <value>${hive.root}/data/scripts</value> + <description></description> +</property> + +<property> + <name>hive.jar.path</name> + <value>${maven.local.repository}/org/apache/hive/hive-exec/${hive.version}/hive-exec-${hive.version}.jar</value> + <description></description> +</property> + +<property> + <name>hive.metastore.rawstore.impl</name> + <value>org.apache.hadoop.hive.metastore.ObjectStore</value> + <description>Name of the class that implements org.apache.hadoop.hive.metastore.rawstore interface. This class is used to store and retrieval of raw metadata objects such as table, database</description> +</property> + +<property> + <name>hive.querylog.location</name> + <value>${test.tmp.dir}/tmp</value> + <description>Location of the structured hive logs</description> +</property> + +<property> + <name>hive.exec.pre.hooks</name> + <value>org.apache.hadoop.hive.ql.hooks.PreExecutePrinter, org.apache.hadoop.hive.ql.hooks.EnforceReadOnlyTables</value> + <description>Pre Execute Hook for Tests</description> +</property> + +<property> + <name>hive.exec.post.hooks</name> + <value>org.apache.hadoop.hive.ql.hooks.PostExecutePrinter</value> + <description>Post Execute Hook for Tests</description> +</property> + +<property> + <name>hive.support.concurrency</name> + <value>false</value> + <description>Whether hive supports concurrency or not. A zookeeper instance must be up and running for the default hive lock manager to support read-write locks.</description> +</property> + +<property> + <name>fs.pfile.impl</name> + <value>org.apache.hadoop.fs.ProxyLocalFileSystem</value> + <description>A proxy for local file system used for cross file system testing</description> +</property> + +<property> + <name>hive.exec.mode.local.auto</name> + <value>false</value> + <description> + Let hive determine whether to run in local mode automatically + Disabling this for tests so that minimr is not affected + </description> +</property> + +<property> + <name>hive.auto.convert.join</name> + <value>false</value> + <description>Whether Hive enable the optimization about converting common join into mapjoin based on the input file size</description> +</property> + +<property> + <name>hive.ignore.mapjoin.hint</name> + <value>true</value> + <description>Whether Hive ignores the mapjoin hint</description> +</property> + +<property> + <name>io.sort.mb</name> + <value>10</value> +</property> + +<property> + <name>hive.input.format</name> + <value>org.apache.hadoop.hive.ql.io.CombineHiveInputFormat</value> + <description>The default input format, if it is not specified, the system assigns it. It is set to HiveInputFormat for hadoop versions 17, 18 and 19, whereas it is set to CombineHiveInputFormat for hadoop 20. The user can always overwrite it - if there is a bug in CombineHiveInputFormat, it can always be manually set to HiveInputFormat. </description> +</property> + +<property> + <name>hive.default.rcfile.serde</name> + <value>org.apache.hadoop.hive.serde2.columnar.ColumnarSerDe</value> + <description>The default SerDe hive will use for the rcfile format</description> +</property> + +<property> + <name>hive.stats.dbclass</name> + <value>fs</value> + <description>The default storatge that stores temporary hive statistics. Currently, fs type is supported</description> +</property> + +<property> + <name>hive.execution.engine</name> + <value>tez</value> + <description>Whether to use MR or Tez</description> +</property> + +<property> + <name>tez.am.node-blacklisting.enabled</name> + <value>false</value> +</property> + +<property> + <name>hive.prewarm.enabled</name> + <value>true</value> + <description> + Enables container prewarm for tez (hadoop 2 only) + </description> +</property> + +<property> + <name>hive.prewarm.numcontainers</name> + <value>3</value> + <description> + Controls the number of containers to prewarm for tez (hadoop 2 only) + </description> +</property> + +<property> + <name>hive.in.tez.test</name> + <value>true</value> + <description> + Indicates that we are in tez testing mode. + </description> +</property> + +<property> + <name>hive.tez.java.opts</name> + <value> -Dlog4j.configurationFile=tez-container-log4j2.properties -Dtez.container.log.level=INFO -Dtez.container.root.logger=CLA </value> +</property> + +<property> + <name>tez.am.launch.cmd-opts</name> + <value> -Dlog4j.configurationFile=tez-container-log4j2.properties -Dtez.container.log.level=INFO -Dtez.container.root.logger=CLA </value> +</property> + +<property> + <name>hive.metastore.fastpath</name> + <value>true</value> +</property> + +<property> + <name>hive.metastore.rawstore.impl</name> + <value>org.apache.hadoop.hive.metastore.ObjectStore</value> +</property> + +<property> + <name>hive.orc.splits.ms.footer.cache.enabled</name> + <value>true</value> +</property> + +</configuration> http://git-wip-us.apache.org/repos/asf/hive/blob/09b6f9a3/data/conf/perf-reg/tez-site.xml ---------------------------------------------------------------------- diff --git a/data/conf/perf-reg/tez-site.xml b/data/conf/perf-reg/tez-site.xml new file mode 100644 index 0000000..940f390 --- /dev/null +++ b/data/conf/perf-reg/tez-site.xml @@ -0,0 +1,6 @@ +<configuration> + <property> + <name>tez.am.dag.scheduler.class</name> + <value>org.apache.tez.dag.app.dag.impl.DAGSchedulerNaturalOrderControlled</value> + </property> +</configuration> http://git-wip-us.apache.org/repos/asf/hive/blob/09b6f9a3/data/files/tpcds-perf/metastore_export/csv/TABLE_PARAMS.txt ---------------------------------------------------------------------- diff --git a/data/files/tpcds-perf/metastore_export/csv/TABLE_PARAMS.txt b/data/files/tpcds-perf/metastore_export/csv/TABLE_PARAMS.txt new file mode 100644 index 0000000..9aa385a --- /dev/null +++ b/data/files/tpcds-perf/metastore_export/csv/TABLE_PARAMS.txt @@ -0,0 +1,102 @@ +_store_,COLUMN_STATS_ACCURATE,true +_store_,numFiles,1 +_store_,numRows,1704 +_store_,rawDataSize,3256276 +_store_,totalSize,101707 +_store_,transient_lastDdlTime,143456_customer_demographics_ +_call_center_,COLUMN_STATS_ACCURATE,true +_call_center_,numFiles,1 +_call_center_,numRows,60 +_call_center_,rawDataSize,122700 +_call_center_,totalSize,10347 +_call_center_,transient_lastDdlTime,1434561922 +_catalog_page_,COLUMN_STATS_ACCURATE,true +_catalog_page_,numFiles,1 +_catalog_page_,numRows,46000 +_catalog_page_,rawDataSize,21198808 +_catalog_page_,totalSize,1576662 +_catalog_page_,transient_lastDdlTime,1434561925 +_customer_,COLUMN_STATS_ACCURATE,true +_customer_,numFiles,538 +_customer_,numRows,80000000 +_customer_,rawDataSize,68801615852 +_customer_,totalSize,3143935054 +_customer_,transient_lastDdlTime,1434561966 +_customer_address_,COLUMN_STATS_ACCURATE,true +_customer_address_,numFiles,274 +_customer_address_,numRows,40000000 +_customer_address_,rawDataSize,40595195284 +_customer_address_,totalSize,530195843 +_customer_address_,transient_lastDdlTime,1434561994 +_customer_demographics_,COLUMN_STATS_ACCURATE,true +_customer_demographics_,numFiles,8 +_customer_demographics_,numRows,_time_dim_800 +_customer_demographics_,rawDataSize,717_income_band_59 +_customer_demographics_,totalSize,323062 +_customer_demographics_,transient_lastDdlTime,1434562071 +_date_dim_,COLUMN_STATS_ACCURATE,true +_date_dim_,numFiles,1 +_date_dim_,numRows,73049 +_date_dim_,rawDataSize,81741831 +_date_dim_,totalSize,362925 +_date_dim_,transient_lastDdlTime,1434562075 +_household_demographics_,COLUMN_STATS_ACCURATE,true +_household_demographics_,numFiles,1 +_household_demographics_,numRows,7200 +_household_demographics_,rawDataSize,770400 +_household_demographics_,totalSize,901 +_household_demographics_,transient_lastDdlTime,1434562078 +_income_band_,COLUMN_STATS_ACCURATE,true +_income_band_,numFiles,1 +_income_band_,numRows,20 +_income_band_,rawDataSize,240 +_income_band_,totalSize,399 +_income_band_,transient_lastDdlTime,1434562081 +_item_,COLUMN_STATS_ACCURATE,true +_item_,numFiles,9 +_item_,numRows,462000 +_item_,rawDataSize,663560457 +_item_,totalSize,29760748 +_item_,transient_lastDdlTime,1434562091 +_promotion_,COLUMN_STATS_ACCURATE,true +_promotion_,numFiles,1 +_promotion_,numRows,2300 +_promotion_,rawDataSize,2713420 +_promotion_,totalSize,63964 +_promotion_,transient_lastDdlTime,1434562093 +_reason_,COLUMN_STATS_ACCURATE,true +_reason_,numFiles,1 +_reason_,numRows,72 +_reason_,rawDataSize,14400 +_reason_,totalSize,1024 +_reason_,transient_lastDdlTime,1434562095 +_ship_mode_,COLUMN_STATS_ACCURATE,true +_ship_mode_,numFiles,0 +_ship_mode_,numRows,0 +_ship_mode_,rawDataSize,0 +_ship_mode_,totalSize,0 +_ship_mode_,transient_lastDdlTime,1434562097 +_time_dim_,COLUMN_STATS_ACCURATE,true +_time_dim_,numFiles,1 +_time_dim_,numRows,86400 +_time_dim_,rawDataSize,40694400 +_time_dim_,totalSize,133902 +_time_dim_,transient_lastDdlTime,1434562099 +_warehouse_,COLUMN_STATS_ACCURATE,true +_warehouse_,numFiles,1 +_warehouse_,numRows,27 +_warehouse_,rawDataSize,27802 +_warehouse_,totalSize,2971 +_warehouse_,transient_lastDdlTime,1434562102 +_web_page_,COLUMN_STATS_ACCURATE,true +_web_page_,numFiles,1 +_web_page_,numRows,4602 +_web_page_,rawDataSize,2696178 +_web_page_,totalSize,50572 +_web_page_,transient_lastDdlTime,1434562104 +_web_site_,COLUMN_STATS_ACCURATE,true +_web_site_,numFiles,1 +_web_site_,numRows,84 +_web_site_,rawDataSize,155408 +_web_site_,totalSize,11271 +_web_site_,transient_lastDdlTime,1434562107 http://git-wip-us.apache.org/repos/asf/hive/blob/09b6f9a3/data/files/tpcds-perf/metastore_export/csv/TAB_COL_STATS.txt ---------------------------------------------------------------------- diff --git a/data/files/tpcds-perf/metastore_export/csv/TAB_COL_STATS.txt b/data/files/tpcds-perf/metastore_export/csv/TAB_COL_STATS.txt new file mode 100644 index 0000000..a1e54fd --- /dev/null +++ b/data/files/tpcds-perf/metastore_export/csv/TAB_COL_STATS.txt @@ -0,0 +1,259 @@ +default,store,s_store_sk,int,1,1704,,,,,1507,0,,,,,1434571564,6196,_store_ +default,store,s_store_id,string,,,,,,,822,0,16,16,,,1434571564,6197,_store_ +default,store,s_rec_start_date,string,,,,,,,5,0,9.94131455399061,10,,,1434571564,6198,_store_ +default,store,s_rec_end_date,string,,,,,,,4,0,5,10,,,1434571564,6199,_store_ +default,store,s_closed_date_sk,int,2450820,2451314,,,,,214,1211,,,,,1434571564,6200,_store_ +default,store,s_store_name,string,,,,,,,11,0,3.91138497652582,5,,,1434571564,6201,_store_ +default,store,s_number_employees,int,200,300,,,,,63,13,,,,,1434571564,6202,_store_ +default,store,s_floor_space,int,5000201,9997773,,,,,1323,12,,,,,1434571564,6203,_store_ +default,store,s_hours,string,,,,,,,4,0,7.07746478873239,8,,,1434571564,6204,_store_ +default,store,s_manager,string,,,,,,,1213,0,12.6584507042254,21,,,1434571564,6205,_store_ +default,store,s_market_id,int,1,10,,,,,11,15,,,,,1434571564,6206,_store_ +default,store,s_geography_class,string,,,,,,,2,0,6.94659624413146,7,,,1434571564,6207,_store_ +default,store,s_market_desc,string,,,,,,,1382,0,57.7194835680751,100,,,1434571564,6208,_store_ +default,store,s_market_manager,string,,,,,,,896,0,12.781103286385,20,,,1434571564,6209,_store_ +default,store,s_division_id,int,1,1,,,,,1,10,,,,,1434571564,6210,_store_ +default,store,s_division_name,string,,,,,,,2,0,6.95070422535211,7,,,1434571564,6211,_store_ +default,store,s_company_id,int,1,1,,,,,1,13,,,,,1434571564,6212,_store_ +default,store,s_company_name,string,,,,,,,2,0,6.95070422535211,7,,,1434571564,6213,_store_ +default,store,s_street_number,string,,,,,,,633,0,2.87793427230047,4,,,1434571564,6214,_store_ +default,store,s_street_name,string,,,,,,,1020,0,8.58509389671362,19,,,1434571564,6215,_store_ +default,store,s_street_type,string,,,,,,,23,0,4.19307511737089,9,,,1434571564,6216,_store_ +default,store,s_suite_number,string,,,,,,,86,0,7.83978873239437,9,,,1434571564,6217,_store_ +default,store,s_city,string,,,,,,,244,0,8.53462441314554,15,,,1434571564,6218,_store_ +default,store,s_county,string,,,,,,,122,0,13.9237089201878,22,,,1434571564,6219,_store_ +default,store,s_state,string,,,,,,,49,0,1.98591549295775,2,,,1434571564,6220,_store_ +default,store,s_zip,string,,,,,,,936,0,4.96478873239437,5,,,1434571564,6221,_store_ +default,store,s_country,string,,,,,,,2,0,12.931338028169,13,,,1434571564,6222,_store_ +default,store,s_gmt_offset,float,,,-9,-5,,,5,9,,,,,1434571564,6223,_store_ +default,store,s_tax_precentage,float,,,0,0.109999999403954,,,15,12,,,,,1434571564,6224,_store_ +default,call_center,cc_call_center_sk,int,1,60,,,,,37,0,,,,,1434571573,6225,_call_center_ +default,call_center,cc_call_center_id,string,,,,,,,30,0,16,16,,,1434571573,6226,_call_center_ +default,call_center,cc_rec_start_date,string,,,,,,,4,0,10,10,,,1434571573,6227,_call_center_ +default,call_center,cc_rec_end_date,string,,,,,,,4,0,5,10,,,1434571573,6228,_call_center_ +default,call_center,cc_closed_date_sk,int,,,,,,,1,60,,,,,1434571573,6229,_call_center_ +default,call_center,cc_open_date_sk,int,2450794,2451146,,,,,21,0,,,,,1434571573,6230,_call_center_ +default,call_center,cc_name,string,,,,,,,29,0,13.85,19,,,1434571573,6231,_call_center_ +default,call_center,cc_class,string,,,,,,,3,0,5.43333333333333,6,,,1434571573,6232,_call_center_ +default,call_center,cc_employees,int,5412266,1963174023,,,,,39,0,,,,,1434571573,6233,_call_center_ +default,call_center,cc_sq_ft,int,-2108783316,2044891959,,,,,56,0,,,,,1434571573,6234,_call_center_ +default,call_center,cc_hours,string,,,,,,,3,0,7.1,8,,,1434571573,6235,_call_center_ +default,call_center,cc_manager,string,,,,,,,47,0,12.2333333333333,17,,,1434571573,6236,_call_center_ +default,call_center,cc_mkt_id,int,1,6,,,,,5,0,,,,,1434571573,6237,_call_center_ +default,call_center,cc_mkt_class,string,,,,,,,43,0,34.7,50,,,1434571573,6238,_call_center_ +default,call_center,cc_mkt_desc,string,,,,,,,41,0,61.8666666666667,100,,,1434571573,6239,_call_center_ +default,call_center,cc_market_manager,string,,,,,,,45,0,12.3833333333333,17,,,1434571573,6240,_call_center_ +default,call_center,cc_division,int,1,6,,,,,5,0,,,,,1434571573,6241,_call_center_ +default,call_center,cc_division_name,string,,,,,,,7,0,3.96666666666667,5,,,1434571573,6242,_call_center_ +default,call_center,cc_company,int,1,6,,,,,5,0,,,,,1434571573,6243,_call_center_ +default,call_center,cc_company_name,string,,,,,,,7,0,3.8,5,,,1434571573,6244,_call_center_ +default,call_center,cc_street_number,string,,,,,,,31,0,2.9,3,,,1434571573,6245,_call_center_ +default,call_center,cc_street_name,string,,,,,,,28,0,8.16666666666667,16,,,1434571573,6246,_call_center_ +default,call_center,cc_street_type,string,,,,,,,14,0,4.28333333333333,9,,,1434571573,6247,_call_center_ +default,call_center,cc_suite_number,string,,,,,,,26,0,7.63333333333333,9,,,1434571573,6248,_call_center_ +default,call_center,cc_city,string,,,,,,,33,0,9.01666666666667,15,,,1434571573,6249,_call_center_ +default,call_center,cc_county,string,,,,,,,31,0,14.1166666666667,21,,,1434571573,6250,_call_center_ +default,call_center,cc_state,string,,,,,,,18,0,2,2,,,1434571573,6251,_call_center_ +default,call_center,cc_zip,string,,,,,,,20,0,5,5,,,1434571573,6252,_call_center_ +default,call_center,cc_country,string,,,,,,,1,0,13,13,,,1434571573,6253,_call_center_ +default,call_center,cc_gmt_offset,float,,,-8,-5,,,3,0,,,,,1434571573,6254,_call_center_ +default,call_center,cc_tax_percentage,float,,,0,0.119999997317791,,,17,0,,,,,1434571573,6255,_call_center_ +default,catalog_page,cp_catalog_page_sk,int,1,46000,,,,,62562,0,,,,,1434571586,6256,_catalog_page_ +default,catalog_page,cp_catalog_page_id,string,,,,,,,38846,0,16,16,,,1434571586,6257,_catalog_page_ +default,catalog_page,cp_start_date_sk,int,2450815,2453005,,,,,112,444,,,,,1434571586,6258,_catalog_page_ +default,catalog_page,cp_end_date_sk,int,2450844,2453186,,,,,86,446,,,,,1434571586,6259,_catalog_page_ +default,catalog_page,cp_department,string,,,,,,,2,0,9.90826086956522,10,,,1434571586,6260,_catalog_page_ +default,catalog_page,cp_catalog_number,int,1,109,,,,,66,465,,,,,1434571586,6261,_catalog_page_ +default,catalog_page,cp_catalog_page_number,int,1,425,,,,,224,443,,,,,1434571586,6262,_catalog_page_ +default,catalog_page,cp_description,string,,,,,,,48242,0,73.8601956521739,99,,,1434571586,6263,_catalog_page_ +default,catalog_page,cp_type,string,,,,,,,5,0,7.59682608695652,9,,,1434571586,6264,_catalog_page_ +default,customer,c_customer_sk,int,1,80000000,,,,,72955234,0,,,,,1434571680,6265,_customer_ +default,customer,c_customer_id,string,,,,,,,66900244,0,16,16,,,1434571680,6266,_customer_ +default,customer,c_current_cdemo_sk,int,1,_time_dim_800,,,,,1835839,2798616,,,,,1434571680,6267,_customer_ +default,customer,c_current_hdemo_sk,int,1,7200,,,,,9299,2799006,,,,,1434571680,6268,_customer_ +default,customer,c_current_addr_sk,int,1,40000000,,,,,45300013,0,,,,,1434571680,6269,_customer_ +default,customer,c_first_shipto_date_sk,int,2449028,2452678,,,,,3585,2800717,,,,,1434571680,6270,_customer_ +default,customer,c_first_sales_date_sk,int,2448998,2452648,,,,,3585,2799514,,,,,1434571680,6271,_customer_ +default,customer,c_salutation,string,,,,,,,9,0,3.1285808875,4,,,1434571680,6272,_customer_ +default,customer,c_first_name,string,,,,,,,5529,0,5.6321494125,11,,,1434571680,6273,_customer_ +default,customer,c_last_name,string,,,,,,,7820,0,5.9160140875,13,,,1434571680,6274,_customer_ +default,customer,c_preferred_cust_flag,string,,,,,,,3,0,0.96499295,1,,,1434571680,6275,_customer_ +default,customer,c_birth_day,int,1,31,,,,,23,2799325,,,,,1434571680,6276,_customer_ +default,customer,c_birth_month,int,1,12,,,,,13,2800192,,,,,1434571680,6277,_customer_ +default,customer,c_birth_year,int,1924,1992,,,,,49,2798667,,,,,1434571680,6278,_customer_ +default,customer,c_birth_country,string,,,,,,,214,0,8.3879500875,20,,,1434571680,6279,_customer_ +default,customer,c_login,string,,,,,,,2,0,0,0,,,1434571680,6280,_customer_ +default,customer,c_email_address,string,,,,,,,112512351,0,26.5065306,48,,,1434571680,6281,_customer_ +default,customer,c_last_review_date,string,,,,,,,224,0,6.7549565125,7,,,1434571680,6282,_customer_ +default,customer_address,ca_address_sk,int,1,40000000,,,,,53871098,0,,,,,1434571715,6283,_customer_address_ +default,customer_address,ca_address_id,string,,,,,,,47305616,0,16,16,,,1434571715,6284,_customer_address_ +default,customer_address,ca_street_number,string,,,,,,,822,0,2.806232675,4,,,1434571715,6285,_customer_address_ +default,customer_address,ca_street_name,string,,,,,,,7488,0,8.44949205,21,,,1434571715,6286,_customer_address_ +default,customer_address,ca_street_type,string,,,,,,,23,0,4.073537425,9,,,1434571715,6287,_customer_address_ +default,customer_address,ca_suite_number,string,,,,,,,86,0,7.653062625,9,,,1434571715,6288,_customer_address_ +default,customer_address,ca_city,string,,,,,,,896,0,8.684372925,20,,,1434571715,6289,_customer_address_ +default,customer_address,ca_county,string,,,,,,,1716,0,13.543959525,28,,,1434571715,6290,_customer_address_ +default,customer_address,ca_state,string,,,,,,,51,0,1.9400121,2,,,1434571715,6291,_customer_address_ +default,customer_address,ca_zip,string,,,,,,,10141,0,4.849962,5,,,1434571715,6292,_customer_address_ +default,customer_address,ca_country,string,,,,,,,2,0,12.610234325,13,,,1434571715,6293,_customer_address_ +default,customer_address,ca_gmt_offset,float,,,-10,-5,,,5,1201179,,,,,1434571715,6294,_customer_address_ +default,customer_address,ca_location_type,string,,,,,,,4,0,8.729921525,13,,,1434571715,6295,_customer_address_ +default,customer_demographics,cd_demo_sk,int,1,_time_dim_800,,,,,1835839,0,,,,,1434571729,6296,_customer_demographics_ +default,customer_demographics,cd_gender,string,,,,,,,2,0,1,1,,,1434571729,6297,_customer_demographics_ +default,customer_demographics,cd_marital_status,string,,,,,,,7,0,1,1,,,1434571729,6298,_customer_demographics_ +default,customer_demographics,cd_education_status,string,,,,,,,9,0,9.57142857142857,15,,,1434571729,6299,_customer_demographics_ +default,customer_demographics,cd_purchase_estimate,int,500,10000,,,,,24,0,,,,,1434571729,6300,_customer_demographics_ +default,customer_demographics,cd_credit_rating,string,,,,,,,5,0,7,9,,,1434571729,6301,_customer_demographics_ +default,customer_demographics,cd_dep_count,int,0,6,,,,,5,0,,,,,1434571729,6302,_customer_demographics_ +default,customer_demographics,cd_dep_employed_count,int,0,6,,,,,5,0,,,,,1434571729,6303,_customer_demographics_ +default,customer_demographics,cd_dep_college_count,int,0,6,,,,,5,0,,,,,1434571729,6304,_customer_demographics_ +default,date_dim,d_date_sk,int,2415022,2488070,,,,,65332,0,,,,,1434571736,6305,_date_dim_ +default,date_dim,d_date_id,string,,,,,,,109875,0,16,16,,,1434571736,6306,_date_dim_ +default,date_dim,d_date,string,,,,,,,92393,0,10,10,,,1434571736,6307,_date_dim_ +default,date_dim,d_month_seq,int,0,2400,,,,,2764,0,,,,,1434571736,6308,_date_dim_ +default,date_dim,d_week_seq,int,1,10436,,,,,13152,0,,,,,1434571736,6309,_date_dim_ +default,date_dim,d_quarter_seq,int,1,801,,,,,429,0,,,,,1434571736,6310,_date_dim_ +default,date_dim,d_year,int,1900,2100,,,,,112,0,,,,,1434571736,6311,_date_dim_ +default,date_dim,d_dow,int,0,6,,,,,5,0,,,,,1434571736,6312,_date_dim_ +default,date_dim,d_moy,int,1,12,,,,,13,0,,,,,1434571736,6313,_date_dim_ +default,date_dim,d_dom,int,1,31,,,,,23,0,,,,,1434571736,6314,_date_dim_ +default,date_dim,d_qoy,int,1,4,,,,,5,0,,,,,1434571736,6315,_date_dim_ +default,date_dim,d_fy_year,int,1900,2100,,,,,112,0,,,,,1434571736,6316,_date_dim_ +default,date_dim,d_fy_quarter_seq,int,1,801,,,,,429,0,,,,,1434571736,6317,_date_dim_ +default,date_dim,d_fy_week_seq,int,1,10436,,,,,13152,0,,,,,1434571736,6318,_date_dim_ +default,date_dim,d_day_name,string,,,,,,,9,0,7.14286300976057,9,,,1434571736,6319,_date_dim_ +default,date_dim,d_quarter_name,string,,,,,,,721,0,6,6,,,1434571736,6320,_date_dim_ +default,date_dim,d_holiday,string,,,,,,,2,0,1,1,,,1434571736,6321,_date_dim_ +default,date_dim,d_weekend,string,,,,,,,2,0,1,1,,,1434571736,6322,_date_dim_ +default,date_dim,d_following_holiday,string,,,,,,,2,0,1,1,,,1434571736,6323,_date_dim_ +default,date_dim,d_first_dom,int,2415021,2488070,,,,,2226,0,,,,,1434571736,6324,_date_dim_ +default,date_dim,d_last_dom,int,2415020,2488372,,,,,2535,0,,,,,1434571736,6325,_date_dim_ +default,date_dim,d_same_day_ly,int,2414657,2487705,,,,,65332,0,,,,,1434571736,6326,_date_dim_ +default,date_dim,d_same_day_lq,int,2414930,2487978,,,,,65332,0,,,,,1434571736,6327,_date_dim_ +default,date_dim,d_current_day,string,,,,,,,1,0,1,1,,,1434571736,6328,_date_dim_ +default,date_dim,d_current_week,string,,,,,,,1,0,1,1,,,1434571736,6329,_date_dim_ +default,date_dim,d_current_month,string,,,,,,,2,0,1,1,,,1434571736,6330,_date_dim_ +default,date_dim,d_current_quarter,string,,,,,,,2,0,1,1,,,1434571736,6331,_date_dim_ +default,date_dim,d_current_year,string,,,,,,,2,0,1,1,,,1434571736,6332,_date_dim_ +default,household_demographics,hd_demo_sk,int,1,7200,,,,,9299,0,,,,,1434571741,6333,_household_demographics_ +default,household_demographics,hd_income_band_sk,int,1,20,,,,,18,0,,,,,1434571741,6334,_household_demographics_ +default,household_demographics,hd_buy_potential,string,,,,,,,5,0,7.5,10,,,1434571741,6335,_household_demographics_ +default,household_demographics,hd_dep_count,int,0,9,,,,,11,0,,,,,1434571741,6336,_household_demographics_ +default,household_demographics,hd_vehicle_count,int,-1,4,,,,,6,0,,,,,1434571741,6337,_household_demographics_ +default,income_band,ib_income_band_sk,int,1,20,,,,,18,0,,,,,1434571745,6338,_income_band_ +default,income_band,ib_lower_bound,int,0,190001,,,,,21,0,,,,,1434571745,6339,_income_band_ +default,income_band,ib_upper_bound,int,10000,200000,,,,,18,0,,,,,1434571745,6340,_income_band_ +default,item,i_item_sk,int,1,462000,,,,,439501,0,,,,,1434571764,6341,_item_ +default,item,i_item_id,string,,,,,,,310774,0,16,16,,,1434571764,6342,_item_ +default,item,i_rec_start_date,string,,,,,,,5,0,9.97480519480519,10,,,1434571764,6343,_item_ +default,item,i_rec_end_date,string,,,,,,,5,0,5,10,,,1434571764,6344,_item_ +default,item,i_item_desc,string,,,,,,,338901,0,100.203757575758,200,,,1434571764,6345,_item_ +default,item,i_current_price,float,,,0.0900000035762787,99.9899978637695,,,12060,1167,,,,,1434571764,6346,_item_ +default,item,i_wholesale_cost,float,,,0.0199999995529652,89.7399978637695,,,7820,1120,,,,,1434571764,6347,_item_ +default,item,i_brand_id,int,1001001,10016017,,,,,633,1139,,,,,1434571764,6348,_item_ +default,item,i_brand,string,,,,,,,633,0,16.1186558441558,22,,,1434571764,6349,_item_ +default,item,i_class_id,int,1,16,,,,,15,1117,,,,,1434571764,6350,_item_ +default,item,i_class,string,,,,,,,102,0,7.76902164502164,15,,,1434571764,6351,_item_ +default,item,i_category_id,int,1,10,,,,,11,1141,,,,,1434571764,6352,_item_ +default,item,i_category,string,,,,,,,10,0,5.88895454545454,11,,,1434571764,6353,_item_ +default,item,i_manufact_id,int,1,1000,,,,,691,1152,,,,,1434571764,6354,_item_ +default,item,i_manufact,string,,,,,,,1267,0,11.2630519480519,15,,,1434571764,6355,_item_ +default,item,i_size,string,,,,,,,9,0,4.32115800865801,11,,,1434571764,6356,_item_ +default,item,i_formulation,string,,,,,,,310774,0,19.9492207792208,20,,,1434571764,6357,_item_ +default,item,i_color,string,,,,,,,66,0,5.36743939393939,10,,,1434571764,6358,_item_ +default,item,i_units,string,,,,,,,18,0,4.17825541125541,7,,,1434571764,6359,_item_ +default,item,i_container,string,,,,,,,2,0,6.9825,7,,,1434571764,6360,_item_ +default,item,i_manager_id,int,1,100,,,,,63,1124,,,,,1434571764,6361,_item_ +default,item,i_product_name,string,,,,,,,522658,0,22.8322532467532,30,,,1434571764,6362,_item_ +default,promotion,p_promo_sk,int,1,2300,,,,,2764,0,,,,,1434571768,6363,_promotion_ +default,promotion,p_promo_id,string,,,,,,,2427,0,16,16,,,1434571768,6364,_promotion_ +default,promotion,p_start_date_sk,int,2450096,2450915,,,,,822,30,,,,,1434571768,6365,_promotion_ +default,promotion,p_end_date_sk,int,2450102,2450970,,,,,1066,26,,,,,1434571768,6366,_promotion_ +default,promotion,p_item_sk,int,614,461932,,,,,2132,28,,,,,1434571768,6367,_promotion_ +default,promotion,p_cost,float,,,1000,1000,,,1,28,,,,,1434571768,6368,_promotion_ +default,promotion,p_response_target,int,1,1,,,,,1,33,,,,,1434571768,6369,_promotion_ +default,promotion,p_promo_name,string,,,,,,,11,0,3.94695652173913,5,,,1434571768,6370,_promotion_ +default,promotion,p_channel_dmail,string,,,,,,,3,0,0.989565217391304,1,,,1434571768,6371,_promotion_ +default,promotion,p_channel_email,string,,,,,,,2,0,0.988260869565217,1,,,1434571768,6372,_promotion_ +default,promotion,p_channel_catalog,string,,,,,,,2,0,0.989565217391304,1,,,1434571768,6373,_promotion_ +default,promotion,p_channel_tv,string,,,,,,,2,0,0.988260869565217,1,,,1434571768,6374,_promotion_ +default,promotion,p_channel_radio,string,,,,,,,2,0,0.987826086956522,1,,,1434571768,6375,_promotion_ +default,promotion,p_channel_press,string,,,,,,,2,0,0.988695652173913,1,,,1434571768,6376,_promotion_ +default,promotion,p_channel_event,string,,,,,,,2,0,0.989565217391304,1,,,1434571768,6377,_promotion_ +default,promotion,p_channel_demo,string,,,,,,,2,0,0.988260869565217,1,,,1434571768,6378,_promotion_ +default,promotion,p_channel_details,string,,,,,,,2041,0,39.2652173913044,60,,,1434571768,6379,_promotion_ +default,promotion,p_purpose,string,,,,,,,2,0,6.92391304347826,7,,,1434571768,6380,_promotion_ +default,promotion,p_discount_active,string,,,,,,,2,0,0.986521739130435,1,,,1434571768,6381,_promotion_ +default,reason,r_reason_sk,int,1,72,,,,,47,0,,,,,1434571773,6382,_reason_ +default,reason,r_reason_id,string,,,,,,,61,0,16,16,,,1434571773,6383,_reason_ +default,reason,r_reason_desc,string,,,,,,,112,0,12.6527777777778,43,,,1434571773,6384,_reason_ +default,ship_mode,sm_ship_mode_sk,int,,,,,,,0,0,,,,,1434571776,6385,_ship_mode_ +default,ship_mode,sm_ship_mode_id,string,,,,,,,0,0,0,0,,,1434571776,6386,_ship_mode_ +default,ship_mode,sm_type,string,,,,,,,0,0,0,0,,,1434571776,6387,_ship_mode_ +default,ship_mode,sm_code,string,,,,,,,0,0,0,0,,,1434571776,6388,_ship_mode_ +default,ship_mode,sm_carrier,string,,,,,,,0,0,0,0,,,1434571776,6389,_ship_mode_ +default,ship_mode,sm_contract,string,,,,,,,0,0,0,0,,,1434571776,6390,_ship_mode_ +default,time_dim,t_time_sk,int,0,86399,,,,,125124,0,,,,,1434571782,6391,_time_dim_ +default,time_dim,t_time_id,string,,,,,,,71245,0,16,16,,,1434571782,6392,_time_dim_ +default,time_dim,t_time,int,0,86399,,,,,125124,0,,,,,1434571782,6393,_time_dim_ +default,time_dim,t_hour,int,0,23,,,,,19,0,,,,,1434571782,6394,_time_dim_ +default,time_dim,t_minute,int,0,59,,,,,37,0,,,,,1434571782,6395,_time_dim_ +default,time_dim,t_second,int,0,59,,,,,37,0,,,,,1434571782,6396,_time_dim_ +default,time_dim,t_am_pm,string,,,,,,,2,0,2,2,,,1434571782,6397,_time_dim_ +default,time_dim,t_shift,string,,,,,,,3,0,5.33333333333333,6,,,1434571782,6398,_time_dim_ +default,time_dim,t_sub_shift,string,,,,,,,5,0,6.91666666666667,9,,,1434571782,6399,_time_dim_ +default,time_dim,t_meal_time,string,,,,,,,4,0,2.875,9,,,1434571782,6400,_time_dim_ +default,warehouse,w_warehouse_sk,int,1,27,,,,,22,0,,,,,1434571790,6401,_warehouse_ +default,warehouse,w_warehouse_id,string,,,,,,,22,0,16,16,,,1434571790,6402,_warehouse_ +default,warehouse,w_warehouse_name,string,,,,,,,21,0,15.5555555555556,20,,,1434571790,6403,_warehouse_ +default,warehouse,w_warehouse_sq_ft,int,73065,977787,,,,,37,1,,,,,1434571790,6404,_warehouse_ +default,warehouse,w_street_number,string,,,,,,,28,0,2.77777777777778,3,,,1434571790,6405,_warehouse_ +default,warehouse,w_street_name,string,,,,,,,23,0,8.40740740740741,15,,,1434571790,6406,_warehouse_ +default,warehouse,w_street_type,string,,,,,,,18,0,3.92592592592593,9,,,1434571790,6407,_warehouse_ +default,warehouse,w_suite_number,string,,,,,,,21,0,7.44444444444444,9,,,1434571790,6408,_warehouse_ +default,warehouse,w_city,string,,,,,,,19,0,9.14814814814815,15,,,1434571790,6409,_warehouse_ +default,warehouse,w_county,string,,,,,,,15,0,14.4074074074074,17,,,1434571790,6410,_warehouse_ +default,warehouse,w_state,string,,,,,,,15,0,2,2,,,1434571790,6411,_warehouse_ +default,warehouse,w_zip,string,,,,,,,17,0,5,5,,,1434571790,6412,_warehouse_ +default,warehouse,w_country,string,,,,,,,1,0,13,13,,,1434571790,6413,_warehouse_ +default,warehouse,w_gmt_offset,float,,,-8,-5,,,3,1,,,,,1434571790,6414,_warehouse_ +default,web_page,wp_web_page_sk,int,1,4602,,,,,5529,0,,,,,1434571794,6415,_web_page_ +default,web_page,wp_web_page_id,string,,,,,,,2647,0,16,16,,,1434571794,6416,_web_page_ +default,web_page,wp_rec_start_date,string,,,,,,,4,0,9.88700564971751,10,,,1434571794,6417,_web_page_ +default,web_page,wp_rec_end_date,string,,,,,,,4,0,5,10,,,1434571794,6418,_web_page_ +default,web_page,wp_creation_date_sk,int,2450492,2450815,,,,,234,64,,,,,1434571794,6419,_web_page_ +default,web_page,wp_access_date_sk,int,2452548,2452648,,,,,63,58,,,,,1434571794,6420,_web_page_ +default,web_page,wp_autogen_flag,string,,,,,,,3,0,0.984789222077358,1,,,1434571794,6421,_web_page_ +default,web_page,wp_customer_sk,int,33025,79895491,,,,,1382,3263,,,,,1434571794,6422,_web_page_ +default,web_page,wp_url,string,,,,,,,2,0,17.8005215123859,18,,,1434571794,6423,_web_page_ +default,web_page,wp_type,string,,,,,,,6,0,6.30704041720991,9,,,1434571794,6424,_web_page_ +default,web_page,wp_char_count,int,303,8523,,,,,3585,65,,,,,1434571794,6425,_web_page_ +default,web_page,wp_link_count,int,2,25,,,,,16,50,,,,,1434571794,6426,_web_page_ +default,web_page,wp_image_count,int,1,7,,,,,6,48,,,,,1434571794,6427,_web_page_ +default,web_page,wp_max_ad_count,int,0,4,,,,,5,52,,,,,1434571794,6428,_web_page_ +default,web_site,web_site_sk,int,1,84,,,,,49,0,,,,,1434572787,6429,_web_site_ +default,web_site,web_site_id,string,,,,,,,53,0,16,16,,,1434572787,6430,_web_site_ +default,web_site,web_rec_start_date,string,,,,,,,5,0,9.76190476190476,10,,,1434572787,6431,_web_site_ +default,web_site,web_rec_end_date,string,,,,,,,4,0,5,10,,,1434572787,6432,_web_site_ +default,web_site,web_name,string,,,,,,,18,0,6.14285714285714,7,,,1434572787,6433,_web_site_ +default,web_site,web_open_date_sk,int,2450118,2450807,,,,,41,1,,,,,1434572787,6434,_web_site_ +default,web_site,web_close_date_sk,int,2440993,2446218,,,,,29,15,,,,,1434572787,6435,_web_site_ +default,web_site,web_class,string,,,,,,,2,0,6.91666666666667,7,,,1434572787,6436,_web_site_ +default,web_site,web_manager,string,,,,,,,61,0,12.4642857142857,19,,,1434572787,6437,_web_site_ +default,web_site,web_mkt_id,int,1,6,,,,,5,1,,,,,1434572787,6438,_web_site_ +default,web_site,web_mkt_class,string,,,,,,,79,0,34.7142857142857,49,,,1434572787,6439,_web_site_ +default,web_site,web_mkt_desc,string,,,,,,,61,0,64.6190476190476,98,,,1434572787,6440,_web_site_ +default,web_site,web_market_manager,string,,,,,,,58,0,12.7738095238095,18,,,1434572787,6441,_web_site_ +default,web_site,web_company_id,int,1,6,,,,,5,0,,,,,1434572787,6442,_web_site_ +default,web_site,web_company_name,string,,,,,,,7,0,3.79761904761905,5,,,1434572787,6443,_web_site_ +default,web_site,web_street_number,string,,,,,,,51,0,2.89285714285714,3,,,1434572787,6444,_web_site_ +default,web_site,web_street_name,string,,,,,,,102,0,8.94047619047619,16,,,1434572787,6445,_web_site_ +default,web_site,web_street_type,string,,,,,,,23,0,3.8452380952381,9,,,1434572787,6446,_web_site_ +default,web_site,web_suite_number,string,,,,,,,58,0,7.90476190476191,9,,,1434572787,6447,_web_site_ +default,web_site,web_city,string,,,,,,,37,0,8.73809523809524,15,,,1434572787,6448,_web_site_ +default,web_site,web_county,string,,,,,,,69,0,13.8333333333333,22,,,1434572787,6449,_web_site_ +default,web_site,web_state,string,,,,,,,33,0,2,2,,,1434572787,6450,_web_site_ +default,web_site,web_zip,string,,,,,,,56,0,5,5,,,1434572787,6451,_web_site_ +default,web_site,web_country,string,,,,,,,2,0,12.8452380952381,13,,,1434572787,6452,_web_site_ +default,web_site,web_gmt_offset,float,,,-8,-5,,,3,1,,,,,1434572787,6453,_web_site_ +default,web_site,web_tax_percentage,float,,,0,0.119999997317791,,,17,1,,,,,1434572787,6454,_web_site_ http://git-wip-us.apache.org/repos/asf/hive/blob/09b6f9a3/data/scripts/q_perf_test_init.sql ---------------------------------------------------------------------- diff --git a/data/scripts/q_perf_test_init.sql b/data/scripts/q_perf_test_init.sql new file mode 100644 index 0000000..d27215b --- /dev/null +++ b/data/scripts/q_perf_test_init.sql @@ -0,0 +1,616 @@ +drop table if exists call_center; +create external table call_center +( + cc_call_center_sk int, + cc_call_center_id string, + cc_rec_start_date string, + cc_rec_end_date string, + cc_closed_date_sk int, + cc_open_date_sk int, + cc_name string, + cc_class string, + cc_employees int, + cc_sq_ft int, + cc_hours string, + cc_manager string, + cc_mkt_id int, + cc_mkt_class string, + cc_mkt_desc string, + cc_market_manager string, + cc_division int, + cc_division_name string, + cc_company int, + 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 decimal(5,2), + cc_tax_percentage decimal(5,2) +) +row format delimited fields terminated by '\t' +STORED AS ORC tblproperties ("orc.compress"="ZLIB"); + +drop table if exists catalog_page; +create external table catalog_page +( + cp_catalog_page_sk int, + cp_catalog_page_id string, + cp_start_date_sk int, + cp_end_date_sk int, + cp_department string, + cp_catalog_number int, + cp_catalog_page_number int, + cp_description string, + cp_type string +) +row format delimited fields terminated by '\t' +STORED AS ORC tblproperties ("orc.compress"="ZLIB"); + + +drop table if exists catalog_returns; +create external table catalog_returns +( + cr_returned_date_sk int, + cr_returned_time_sk int, + cr_item_sk int, + cr_refunded_customer_sk int, + cr_refunded_cdemo_sk int, + cr_refunded_hdemo_sk int, + cr_refunded_addr_sk int, + cr_returning_customer_sk int, + cr_returning_cdemo_sk int, + cr_returning_hdemo_sk int, + cr_returning_addr_sk int, + cr_call_center_sk int, + cr_catalog_page_sk int, + cr_ship_mode_sk int, + cr_warehouse_sk int, + cr_reason_sk int, + cr_order_number int, + cr_return_quantity int, + cr_return_amount decimal(7,2), + cr_return_tax decimal(7,2), + cr_return_amt_inc_tax decimal(7,2), + cr_fee decimal(7,2), + cr_return_ship_cost decimal(7,2), + cr_refunded_cash decimal(7,2), + cr_reversed_charge decimal(7,2), + cr_store_credit decimal(7,2), + cr_net_loss decimal(7,2) +) +row format delimited fields terminated by '\t' +STORED AS ORC tblproperties ("orc.compress"="ZLIB"); + + +drop table if exists catalog_sales; +create external table catalog_sales +( + cs_sold_date_sk int, + cs_sold_time_sk int, + cs_ship_date_sk int, + cs_bill_customer_sk int, + cs_bill_cdemo_sk int, + cs_bill_hdemo_sk int, + cs_bill_addr_sk int, + cs_ship_customer_sk int, + cs_ship_cdemo_sk int, + cs_ship_hdemo_sk int, + cs_ship_addr_sk int, + cs_call_center_sk int, + cs_catalog_page_sk int, + cs_ship_mode_sk int, + cs_warehouse_sk int, + cs_item_sk int, + cs_promo_sk int, + cs_order_number int, + cs_quantity int, + cs_wholesale_cost decimal(7,2), + cs_list_price decimal(7,2), + cs_sales_price decimal(7,2), + cs_ext_discount_amt decimal(7,2), + cs_ext_sales_price decimal(7,2), + cs_ext_wholesale_cost decimal(7,2), + cs_ext_list_price decimal(7,2), + cs_ext_tax decimal(7,2), + cs_coupon_amt decimal(7,2), + cs_ext_ship_cost decimal(7,2), + cs_net_paid decimal(7,2), + cs_net_paid_inc_tax decimal(7,2), + cs_net_paid_inc_ship decimal(7,2), + cs_net_paid_inc_ship_tax decimal(7,2), + cs_net_profit decimal(7,2) +) +row format delimited fields terminated by '\t' +STORED AS ORC tblproperties ("orc.compress"="ZLIB"); + + +drop table if exists customer; +create external table customer +( + c_customer_sk int, + c_customer_id string, + c_current_cdemo_sk int, + c_current_hdemo_sk int, + c_current_addr_sk int, + c_first_shipto_date_sk int, + c_first_sales_date_sk int, + 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 string +) +row format delimited fields terminated by '\t' +STORED AS ORC tblproperties ("orc.compress"="ZLIB"); + + +drop table if exists customer_address; +create external table customer_address +( + ca_address_sk int, + 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 decimal(5,2), + ca_location_type string +) +row format delimited fields terminated by '\t' +STORED AS ORC tblproperties ("orc.compress"="ZLIB"); + + +drop table if exists customer_demographics; +create external table customer_demographics +( + cd_demo_sk int, + cd_gender string, + cd_marital_status string, + cd_education_status string, + cd_purchase_estimate int, + cd_credit_rating string, + cd_dep_count int, + cd_dep_employed_count int, + cd_dep_college_count int +) +row format delimited fields terminated by '\t' +STORED AS ORC tblproperties ("orc.compress"="ZLIB"); + + +drop table if exists date_dim; +create external table date_dim +( + d_date_sk int, + d_date_id string, + d_date string, + d_month_seq int, + d_week_seq int, + d_quarter_seq int, + d_year int, + d_dow int, + d_moy int, + d_dom int, + d_qoy int, + d_fy_year int, + d_fy_quarter_seq int, + d_fy_week_seq int, + d_day_name string, + d_quarter_name string, + d_holiday string, + d_weekend string, + d_following_holiday string, + d_first_dom int, + d_last_dom int, + d_same_day_ly int, + d_same_day_lq int, + d_current_day string, + d_current_week string, + d_current_month string, + d_current_quarter string, + d_current_year string +) +row format delimited fields terminated by '\t' +STORED AS ORC tblproperties ("orc.compress"="ZLIB"); + + +drop table if exists household_demographics; +create external table household_demographics +( + hd_demo_sk int, + hd_income_band_sk int, + hd_buy_potential string, + hd_dep_count int, + hd_vehicle_count int +) +row format delimited fields terminated by '\t' +STORED AS ORC tblproperties ("orc.compress"="ZLIB"); + + +drop table if exists income_band; +create external table income_band +( + ib_income_band_sk int, + ib_lower_bound int, + ib_upper_bound int +) +row format delimited fields terminated by '\t' +STORED AS ORC tblproperties ("orc.compress"="ZLIB"); + + +drop table if exists inventory; +create external table inventory +( + inv_date_sk int, + inv_item_sk int, + inv_warehouse_sk int, + inv_quantity_on_hand int +) +row format delimited fields terminated by '\t' +STORED AS ORC tblproperties ("orc.compress"="ZLIB"); + + +drop table if exists item; +create external table item +( + i_item_sk int, + i_item_id string, + i_rec_start_date string, + i_rec_end_date string, + i_item_desc string, + i_current_price decimal(7,2), + i_wholesale_cost decimal(7,2), + i_brand_id int, + i_brand string, + i_class_id int, + i_class string, + i_category_id int, + i_category string, + i_manufact_id int, + i_manufact string, + i_size string, + i_formulation string, + i_color string, + i_units string, + i_container string, + i_manager_id int, + i_product_name string +) +row format delimited fields terminated by '\t' +STORED AS ORC tblproperties ("orc.compress"="ZLIB"); + + +drop table if exists promotion; +create external table promotion +( + p_promo_sk int, + p_promo_id string, + p_start_date_sk int, + p_end_date_sk int, + p_item_sk int, + p_cost decimal(15,2), + p_response_target int, + 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 +) +row format delimited fields terminated by '\t' +STORED AS ORC tblproperties ("orc.compress"="ZLIB"); + + +drop table if exists reason; +create external table reason +( + r_reason_sk int, + r_reason_id string, + r_reason_desc string +) +row format delimited fields terminated by '\t' +STORED AS ORC tblproperties ("orc.compress"="ZLIB"); + + +drop table if exists ship_mode; +create external table ship_mode +( + sm_ship_mode_sk int, + sm_ship_mode_id string, + sm_type string, + sm_code string, + sm_carrier string, + sm_contract string +) +row format delimited fields terminated by '\t' +STORED AS ORC tblproperties ("orc.compress"="ZLIB"); + + +drop table if exists store; +create external table store +( + s_store_sk int, + s_store_id string, + s_rec_start_date string, + s_rec_end_date string, + s_closed_date_sk int, + s_store_name string, + s_number_employees int, + s_floor_space int, + s_hours string, + s_manager string, + s_market_id int, + s_geography_class string, + s_market_desc string, + s_market_manager string, + s_division_id int, + s_division_name string, + s_company_id int, + 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 decimal(5,2), + s_tax_precentage decimal(5,2) +) +row format delimited fields terminated by '\t' +STORED AS ORC tblproperties ("orc.compress"="ZLIB"); + + +drop table if exists store_returns; +create external table store_returns +( + sr_returned_date_sk int, + sr_return_time_sk int, + sr_item_sk int, + sr_customer_sk int, + sr_cdemo_sk int, + sr_hdemo_sk int, + sr_addr_sk int, + sr_store_sk int, + sr_reason_sk int, + sr_ticket_number int, + sr_return_quantity int, + sr_return_amt decimal(7,2), + sr_return_tax decimal(7,2), + sr_return_amt_inc_tax decimal(7,2), + sr_fee decimal(7,2), + sr_return_ship_cost decimal(7,2), + sr_refunded_cash decimal(7,2), + sr_reversed_charge decimal(7,2), + sr_store_credit decimal(7,2), + sr_net_loss decimal(7,2) +) +row format delimited fields terminated by '\t' +STORED AS ORC tblproperties ("orc.compress"="ZLIB"); + + +drop table if exists store_sales; +create external table store_sales +( + ss_sold_date_sk int, + ss_sold_time_sk int, + ss_item_sk int, + ss_customer_sk int, + ss_cdemo_sk int, + ss_hdemo_sk int, + ss_addr_sk int, + ss_store_sk int, + ss_promo_sk int, + ss_ticket_number int, + ss_quantity int, + ss_wholesale_cost decimal(7,2), + ss_list_price decimal(7,2), + ss_sales_price decimal(7,2), + ss_ext_discount_amt decimal(7,2), + ss_ext_sales_price decimal(7,2), + ss_ext_wholesale_cost decimal(7,2), + ss_ext_list_price decimal(7,2), + ss_ext_tax decimal(7,2), + ss_coupon_amt decimal(7,2), + ss_net_paid decimal(7,2), + ss_net_paid_inc_tax decimal(7,2), + ss_net_profit decimal(7,2) +) +row format delimited fields terminated by '\t' +STORED AS ORC tblproperties ("orc.compress"="ZLIB"); + + +drop table if exists time_dim; +create external table time_dim +( + t_time_sk int, + t_time_id string, + t_time int, + t_hour int, + t_minute int, + t_second int, + t_am_pm string, + t_shift string, + t_sub_shift string, + t_meal_time string +) +row format delimited fields terminated by '\t' +STORED AS ORC tblproperties ("orc.compress"="ZLIB"); + + +drop table if exists warehouse; +create external table warehouse +( + w_warehouse_sk int, + w_warehouse_id string, + w_warehouse_name string, + w_warehouse_sq_ft int, + 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 decimal(5,2) +) +row format delimited fields terminated by '\t' +STORED AS ORC tblproperties ("orc.compress"="ZLIB"); + + +drop table if exists web_page; +create external table web_page +( + wp_web_page_sk int, + wp_web_page_id string, + wp_rec_start_date string, + wp_rec_end_date string, + wp_creation_date_sk int, + wp_access_date_sk int, + wp_autogen_flag string, + wp_customer_sk int, + wp_url string, + wp_type string, + wp_char_count int, + wp_link_count int, + wp_image_count int, + wp_max_ad_count int +) +row format delimited fields terminated by '\t' +STORED AS ORC tblproperties ("orc.compress"="ZLIB"); + + +drop table if exists web_returns; +create external table web_returns +( + wr_returned_date_sk int, + wr_returned_time_sk int, + wr_item_sk int, + wr_refunded_customer_sk int, + wr_refunded_cdemo_sk int, + wr_refunded_hdemo_sk int, + wr_refunded_addr_sk int, + wr_returning_customer_sk int, + wr_returning_cdemo_sk int, + wr_returning_hdemo_sk int, + wr_returning_addr_sk int, + wr_web_page_sk int, + wr_reason_sk int, + wr_order_number int, + wr_return_quantity int, + wr_return_amt decimal(7,2), + wr_return_tax decimal(7,2), + wr_return_amt_inc_tax decimal(7,2), + wr_fee decimal(7,2), + wr_return_ship_cost decimal(7,2), + wr_refunded_cash decimal(7,2), + wr_reversed_charge decimal(7,2), + wr_account_credit decimal(7,2), + wr_net_loss decimal(7,2) +) +row format delimited fields terminated by '\t' +STORED AS ORC tblproperties ("orc.compress"="ZLIB"); + + +drop table if exists web_sales; +create external table web_sales +( + ws_sold_date_sk int, + ws_sold_time_sk int, + ws_ship_date_sk int, + ws_item_sk int, + ws_bill_customer_sk int, + ws_bill_cdemo_sk int, + ws_bill_hdemo_sk int, + ws_bill_addr_sk int, + ws_ship_customer_sk int, + ws_ship_cdemo_sk int, + ws_ship_hdemo_sk int, + ws_ship_addr_sk int, + ws_web_page_sk int, + ws_web_site_sk int, + ws_ship_mode_sk int, + ws_warehouse_sk int, + ws_promo_sk int, + ws_order_number int, + ws_quantity int, + ws_wholesale_cost decimal(7,2), + ws_list_price decimal(7,2), + ws_sales_price decimal(7,2), + ws_ext_discount_amt decimal(7,2), + ws_ext_sales_price decimal(7,2), + ws_ext_wholesale_cost decimal(7,2), + ws_ext_list_price decimal(7,2), + ws_ext_tax decimal(7,2), + ws_coupon_amt decimal(7,2), + ws_ext_ship_cost decimal(7,2), + ws_net_paid decimal(7,2), + ws_net_paid_inc_tax decimal(7,2), + ws_net_paid_inc_ship decimal(7,2), + ws_net_paid_inc_ship_tax decimal(7,2), + ws_net_profit decimal(7,2) +) +row format delimited fields terminated by '\t' +STORED AS ORC tblproperties ("orc.compress"="ZLIB"); + + +drop table if exists web_site; +create external table web_site +( + web_site_sk int, + web_site_id string, + web_rec_start_date string, + web_rec_end_date string, + web_name string, + web_open_date_sk int, + web_close_date_sk int, + web_class string, + web_manager string, + web_mkt_id int, + web_mkt_class string, + web_mkt_desc string, + web_market_manager string, + web_company_id int, + 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 decimal(5,2), + web_tax_percentage decimal(5,2) +) +row format delimited fields terminated by '\t' +STORED AS ORC tblproperties ("orc.compress"="ZLIB"); + + http://git-wip-us.apache.org/repos/asf/hive/blob/09b6f9a3/itests/qtest/pom.xml ---------------------------------------------------------------------- diff --git a/itests/qtest/pom.xml b/itests/qtest/pom.xml index ec2fc85..292e562 100644 --- a/itests/qtest/pom.xml +++ b/itests/qtest/pom.xml @@ -396,6 +396,7 @@ <taskdef name="qtestgen" classname="org.apache.hadoop.hive.ant.QTestGenTask" classpath="${test.classpath}" /> <mkdir dir="${project.build.directory}/qfile-results/clientpositive/" /> + <mkdir dir="${project.build.directory}/qfile-results/clientpositive/perf" /> <mkdir dir="${project.build.directory}/qfile-results/clientnegative/" /> <mkdir dir="${project.build.directory}/qfile-results/clientcompare"/> <mkdir dir="${project.build.directory}/qfile-results/positive/" /> @@ -441,6 +442,25 @@ initScript="${initScript}" cleanupScript="q_test_cleanup.sql"/> + <!-- PerfCli --> + <qtestgen hiveRootDirectory="${basedir}/${hive.path.to.root}/" + outputDirectory="${project.build.directory}/generated-test-sources/java/org/apache/hadoop/hive/cli/" + templatePath="${basedir}/${hive.path.to.root}/ql/src/test/templates/" template="TestPerfCliDriver.vm" + queryDirectory="${basedir}/${hive.path.to.root}/ql/src/test/queries/clientpositive/perf" + queryFile="${qfile}" + excludeQueryFile="${minimr.query.files},${minitez.query.files},${encrypted.query.files}" + queryFileRegex="${qfile_regex}" + clusterMode="tez" + runDisabled="${run_disabled}" + hiveConfDir="${basedir}/${hive.path.to.root}/data/conf/perf-reg" + resultsDirectory="${basedir}/${hive.path.to.root}/ql/src/test/results/clientpositive/perf" className="TestPerfCliDriver" + logFile="${project.build.directory}/testperfclidrivergen.log" + logDirectory="${project.build.directory}/qfile-results/clientpositive" + hadoopVersion="${hadoop.version}" + initScript="q_perf_test_init.sql" + cleanupScript="q_perf_test_cleanup.sql" + useHBaseMetastore="false"/> + <!-- Negative Cli --> <qtestgen hiveRootDirectory="${basedir}/${hive.path.to.root}/" outputDirectory="${project.build.directory}/generated-test-sources/java/org/apache/hadoop/hive/cli/" http://git-wip-us.apache.org/repos/asf/hive/blob/09b6f9a3/itests/util/src/main/java/org/apache/hadoop/hive/ql/QTestUtil.java ---------------------------------------------------------------------- diff --git a/itests/util/src/main/java/org/apache/hadoop/hive/ql/QTestUtil.java b/itests/util/src/main/java/org/apache/hadoop/hive/ql/QTestUtil.java index 928a071..fa16020 100644 --- a/itests/util/src/main/java/org/apache/hadoop/hive/ql/QTestUtil.java +++ b/itests/util/src/main/java/org/apache/hadoop/hive/ql/QTestUtil.java @@ -39,15 +39,24 @@ import java.io.PrintStream; import java.io.Serializable; import java.io.StringWriter; import java.net.URL; +import java.sql.Connection; +import java.sql.DriverManager; +import java.sql.PreparedStatement; +import java.sql.ResultSet; import java.sql.SQLException; +import java.sql.Statement; import java.util.ArrayList; import java.util.Arrays; import java.util.Collection; +import java.util.Comparator; import java.util.Deque; import java.util.HashSet; import java.util.LinkedList; import java.util.List; +import java.util.Map; +import java.util.Properties; import java.util.Set; +import java.util.SortedMap; import java.util.TreeMap; import java.util.concurrent.TimeUnit; import java.util.regex.Matcher; @@ -1991,4 +2000,158 @@ public class QTestUtil { Assert.fail("Unexpected exception " + org.apache.hadoop.util.StringUtils.stringifyException(e)); } } + + public static void setupMetaStoreTableColumnStatsFor30TBTPCDSWorkload(HiveConf conf) { + Connection conn = null; + ArrayList<Statement> statements = new ArrayList<Statement>(); // list of Statements, PreparedStatements + + try { + Properties props = new Properties(); // connection properties + props.put("user", conf.get("javax.jdo.option.ConnectionUserName")); + props.put("password", conf.get("javax.jdo.option.ConnectionPassword")); + conn = DriverManager.getConnection(conf.get("javax.jdo.option.ConnectionURL"), props); + ResultSet rs = null; + Statement s = conn.createStatement(); + + if (LOG.isDebugEnabled()) { + LOG.debug("Connected to metastore database "); + } + + String mdbPath = "../../data/files/tpcds-perf/metastore_export/"; + + // Setup the table column stats + BufferedReader br = new BufferedReader(new FileReader(new File("../../metastore/scripts/upgrade/derby/022-HIVE-11107.derby.sql"))); + String command; + + s.execute("DROP TABLE APP.TABLE_PARAMS"); + s.execute("DROP TABLE APP.TAB_COL_STATS"); + // Create the column stats table + while ((command = br.readLine()) != null) { + if (!command.endsWith(";")) { + continue; + } + if (LOG.isDebugEnabled()) { + LOG.debug("Going to run command : " + command); + } + try { + PreparedStatement psCommand = conn.prepareStatement(command.substring(0, command.length()-1)); + statements.add(psCommand); + psCommand.execute(); + if (LOG.isDebugEnabled()) { + LOG.debug("successfully completed " + command); + } + } catch (SQLException e) { + LOG.info("Got SQL Exception " + e.getMessage()); + } + } + br.close(); + + File tabColStatsCsv = new File(mdbPath+"csv/TAB_COL_STATS.txt"); + File tabParamsCsv = new File(mdbPath+"csv/TABLE_PARAMS.txt"); + + // Set up the foreign key constraints properly in the TAB_COL_STATS data + String tmpBaseDir = System.getProperty("test.tmp.dir"); + File tmpFileLoc1 = new File(tmpBaseDir+"/TAB_COL_STATS.txt"); + File tmpFileLoc2 = new File(tmpBaseDir+"/TABLE_PARAMS.txt"); + FileUtils.copyFile(tabColStatsCsv, tmpFileLoc1); + FileUtils.copyFile(tabParamsCsv, tmpFileLoc2); + + class MyComp implements Comparator<String> { + @Override + public int compare(String str1, String str2) { + if (str2.length() != str1.length()) { + return str2.length() - str1.length(); + } + return str1.compareTo(str2); + } + } + + SortedMap<String, Integer> tableNameToID = new TreeMap<String, Integer>(new MyComp()); + + rs = s.executeQuery("SELECT * FROM APP.TBLS"); + while(rs.next()) { + String tblName = rs.getString("TBL_NAME"); + Integer tblId = rs.getInt("TBL_ID"); + tableNameToID.put(tblName, tblId); + + if (LOG.isDebugEnabled()) { + LOG.debug("Resultset : " + tblName + " | " + tblId); + } + } + for (Map.Entry<String, Integer> entry : tableNameToID.entrySet()) { + String toReplace = "_" + entry.getKey() + "_" ; + String replacementString = ""+entry.getValue(); + try { + String content1 = FileUtils.readFileToString(tmpFileLoc1, "UTF-8"); + content1 = content1.replaceAll(toReplace, replacementString); + FileUtils.writeStringToFile(tmpFileLoc1, content1, "UTF-8"); + String content2 = FileUtils.readFileToString(tmpFileLoc2, "UTF-8"); + content2 = content2.replaceAll(toReplace, replacementString); + FileUtils.writeStringToFile(tmpFileLoc2, content2, "UTF-8"); + } catch (IOException e) { + LOG.info("Generating file failed", e); + } + } + + // Load the column stats and table params with 30 TB scale + String importStatement1 = "CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE_LOBS_FROM_EXTFILE(null, '" + "TAB_COL_STATS" + + "', '" + tmpFileLoc1.getAbsolutePath() + + "', ',', null, 'UTF-8', 1)"; + String importStatement2 = "CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE_LOBS_FROM_EXTFILE(null, '" + "TABLE_PARAMS" + + "', '" + tmpFileLoc2.getAbsolutePath() + + "', ',', null, 'UTF-8', 1)"; + try { + PreparedStatement psImport1 = conn.prepareStatement(importStatement1); + if (LOG.isDebugEnabled()) { + LOG.debug("Going to execute : " + importStatement1); + } + statements.add(psImport1); + psImport1.execute(); + if (LOG.isDebugEnabled()) { + LOG.debug("successfully completed " + importStatement1); + } + PreparedStatement psImport2 = conn.prepareStatement(importStatement2); + if (LOG.isDebugEnabled()) { + LOG.debug("Going to execute : " + importStatement2); + } + statements.add(psImport2); + psImport2.execute(); + if (LOG.isDebugEnabled()) { + LOG.debug("successfully completed " + importStatement2); + } + } catch (SQLException e) { + LOG.info("Got SQL Exception " + e.getMessage()); + } + } catch (FileNotFoundException e1) { + LOG.info("Got File not found Exception " + e1.getMessage()); + } catch (IOException e1) { + LOG.info("Got IOException " + e1.getMessage()); + } catch (SQLException e1) { + LOG.info("Got SQLException " + e1.getMessage()); + } finally { + // Statements and PreparedStatements + int i = 0; + while (!statements.isEmpty()) { + // PreparedStatement extend Statement + Statement st = (Statement)statements.remove(i); + try { + if (st != null) { + st.close(); + st = null; + } + } catch (SQLException sqle) { + } + } + + //Connection + try { + if (conn != null) { + conn.close(); + conn = null; + } + } catch (SQLException sqle) { + } + } + } + } http://git-wip-us.apache.org/repos/asf/hive/blob/09b6f9a3/metastore/scripts/upgrade/derby/022-HIVE-11107.derby.sql ---------------------------------------------------------------------- diff --git a/metastore/scripts/upgrade/derby/022-HIVE-11107.derby.sql b/metastore/scripts/upgrade/derby/022-HIVE-11107.derby.sql new file mode 100644 index 0000000..ef08acb --- /dev/null +++ b/metastore/scripts/upgrade/derby/022-HIVE-11107.derby.sql @@ -0,0 +1,2 @@ +CREATE TABLE "APP"."TAB_COL_STATS"("DB_NAME" VARCHAR(128) NOT NULL,"TABLE_NAME" VARCHAR(128) NOT NULL, "COLUMN_NAME" VARCHAR(1000) NOT NULL, "COLUMN_TYPE" VARCHAR(128) NOT NULL, "LONG_LOW_VALUE" BIGINT, "LONG_HIGH_VALUE" BIGINT, "DOUBLE_LOW_VALUE" DOUBLE, "DOUBLE_HIGH_VALUE" DOUBLE, "BIG_DECIMAL_LOW_VALUE" VARCHAR(4000),"BIG_DECIMAL_HIGH_VALUE" VARCHAR(4000),"NUM_DISTINCTS" BIGINT, "NUM_NULLS" BIGINT NOT NULL, "AVG_COL_LEN" DOUBLE, "MAX_COL_LEN" BIGINT, "NUM_TRUES" BIGINT, "NUM_FALSES" BIGINT, "LAST_ANALYZED" BIGINT, "CS_ID" BIGINT NOT NULL, "TBL_ID" BIGINT NOT NULL); +CREATE TABLE "APP"."TABLE_PARAMS" ("TBL_ID" BIGINT NOT NULL, "PARAM_KEY" VARCHAR(256) NOT NULL, "PARAM_VALUE" VARCHAR(4000)); http://git-wip-us.apache.org/repos/asf/hive/blob/09b6f9a3/metastore/scripts/upgrade/derby/hive-schema-2.1.0.derby.sql ---------------------------------------------------------------------- diff --git a/metastore/scripts/upgrade/derby/hive-schema-2.1.0.derby.sql b/metastore/scripts/upgrade/derby/hive-schema-2.1.0.derby.sql index abc6bf4..f08de64 100644 --- a/metastore/scripts/upgrade/derby/hive-schema-2.1.0.derby.sql +++ b/metastore/scripts/upgrade/derby/hive-schema-2.1.0.derby.sql @@ -70,7 +70,7 @@ CREATE TABLE "APP"."SDS" ("SD_ID" BIGINT NOT NULL, "INPUT_FORMAT" VARCHAR(4000), CREATE TABLE "APP"."SEQUENCE_TABLE" ("SEQUENCE_NAME" VARCHAR(256) NOT NULL, "NEXT_VAL" BIGINT NOT NULL); -CREATE TABLE "APP"."TABLE_PARAMS" ("TBL_ID" BIGINT NOT NULL, "PARAM_KEY" VARCHAR(256) NOT NULL, "PARAM_VALUE" VARCHAR(4000)); +RUN '022-HIVE-11107.derby.sql'; CREATE TABLE "APP"."BUCKETING_COLS" ("SD_ID" BIGINT NOT NULL, "BUCKET_COL_NAME" VARCHAR(256), "INTEGER_IDX" INTEGER NOT NULL); @@ -94,8 +94,6 @@ CREATE TABLE "APP"."MASTER_KEYS" ("KEY_ID" INTEGER NOT NULL generated always as CREATE TABLE "APP"."DELEGATION_TOKENS" ( "TOKEN_IDENT" VARCHAR(767) NOT NULL, "TOKEN" VARCHAR(767)); -CREATE TABLE "APP"."TAB_COL_STATS"("DB_NAME" VARCHAR(128) NOT NULL,"TABLE_NAME" VARCHAR(128) NOT NULL, "COLUMN_NAME" VARCHAR(1000) NOT NULL, "COLUMN_TYPE" VARCHAR(128) NOT NULL, "LONG_LOW_VALUE" BIGINT, "LONG_HIGH_VALUE" BIGINT, "DOUBLE_LOW_VALUE" DOUBLE, "DOUBLE_HIGH_VALUE" DOUBLE, "BIG_DECIMAL_LOW_VALUE" VARCHAR(4000), "BIG_DECIMAL_HIGH_VALUE" VARCHAR(4000),"NUM_DISTINCTS" BIGINT, "NUM_NULLS" BIGINT NOT NULL, "AVG_COL_LEN" DOUBLE, "MAX_COL_LEN" BIGINT, "NUM_TRUES" BIGINT, "NUM_FALSES" BIGINT, "LAST_ANALYZED" BIGINT, "CS_ID" BIGINT NOT NULL, "TBL_ID" BIGINT NOT NULL); - CREATE TABLE "APP"."PART_COL_STATS"("DB_NAME" VARCHAR(128) NOT NULL,"TABLE_NAME" VARCHAR(128) NOT NULL, "PARTITION_NAME" VARCHAR(767) NOT NULL, "COLUMN_NAME" VARCHAR(1000) NOT NULL, "COLUMN_TYPE" VARCHAR(128) NOT NULL, "LONG_LOW_VALUE" BIGINT, "LONG_HIGH_VALUE" BIGINT, "DOUBLE_LOW_VALUE" DOUBLE, "DOUBLE_HIGH_VALUE" DOUBLE, "BIG_DECIMAL_LOW_VALUE" VARCHAR(4000), "BIG_DECIMAL_HIGH_VALUE" VARCHAR(4000),"NUM_DISTINCTS" BIGINT, "NUM_NULLS" BIGINT NOT NULL, "AVG_COL_LEN" DOUBLE, "MAX_COL_LEN" BIGINT, "NUM_TRUES" BIGINT, "NUM_FALSES" BIGINT, "LAST_ANALYZED" BIGINT, "CS_ID" BIGINT NOT NULL, "PART_ID" BIGINT NOT NULL); CREATE TABLE "APP"."VERSION" ("VER_ID" BIGINT NOT NULL, "SCHEMA_VERSION" VARCHAR(127) NOT NULL, "VERSION_COMMENT" VARCHAR(255)); http://git-wip-us.apache.org/repos/asf/hive/blob/09b6f9a3/ql/src/test/queries/clientpositive/perf/query12.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/perf/query12.q b/ql/src/test/queries/clientpositive/perf/query12.q new file mode 100644 index 0000000..89dd2de4 --- /dev/null +++ b/ql/src/test/queries/clientpositive/perf/query12.q @@ -0,0 +1 @@ +explain select i_item_desc ,i_category ,i_class ,i_current_price ,i_item_id ,sum(ws_ext_sales_price) as itemrevenue ,sum(ws_ext_sales_price)*100/sum(sum(ws_ext_sales_price)) over (partition by i_class) as revenueratio from web_sales ,item ,date_dim where web_sales.ws_item_sk = item.i_item_sk and item.i_category in ('Jewelry', 'Sports', 'Books') and web_sales.ws_sold_date_sk = date_dim.d_date_sk and date_dim.d_date between '2001-01-12' and '2001-02-11' group by i_item_id ,i_item_desc ,i_category ,i_class ,i_current_price order by i_category ,i_class ,i_item_id ,i_item_desc ,revenueratio limit 100; http://git-wip-us.apache.org/repos/asf/hive/blob/09b6f9a3/ql/src/test/queries/clientpositive/perf/query13.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/perf/query13.q b/ql/src/test/queries/clientpositive/perf/query13.q new file mode 100644 index 0000000..6f43d47 --- /dev/null +++ b/ql/src/test/queries/clientpositive/perf/query13.q @@ -0,0 +1,54 @@ +set hive.mapred.mode=nonstrict; +EXPLAIN SELECT Avg(ss_quantity) , + Avg(ss_ext_sales_price) , + Avg(ss_ext_wholesale_cost) , + Sum(ss_ext_wholesale_cost) +FROM store_sales , + store , + customer_demographics , + household_demographics , + customer_address , + date_dim +WHERE store.s_store_sk = store_sales.ss_store_sk +AND store_sales.ss_sold_date_sk = date_dim.d_date_sk +AND date_dim.d_year = 2001 +AND (( + store_sales.ss_hdemo_sk=household_demographics.hd_demo_sk + AND customer_demographics.cd_demo_sk = store_sales.ss_cdemo_sk + AND customer_demographics.cd_marital_status = 'M' + AND customer_demographics.cd_education_status = '4 yr Degree' + AND store_sales.ss_sales_price BETWEEN 100.00 AND 150.00 + AND household_demographics.hd_dep_count = 3 ) + OR ( + store_sales.ss_hdemo_sk=household_demographics.hd_demo_sk + AND customer_demographics.cd_demo_sk = store_sales.ss_cdemo_sk + AND customer_demographics.cd_marital_status = 'D' + AND customer_demographics.cd_education_status = 'Primary' + AND store_sales.ss_sales_price BETWEEN 50.00 AND 100.00 + AND household_demographics.hd_dep_count = 1 ) + OR ( + store_sales.ss_hdemo_sk=household_demographics.hd_demo_sk + AND customer_demographics.cd_demo_sk = ss_cdemo_sk + AND customer_demographics.cd_marital_status = 'U' + AND customer_demographics.cd_education_status = 'Advanced Degree' + AND store_sales.ss_sales_price BETWEEN 150.00 AND 200.00 + AND household_demographics.hd_dep_count = 1 )) +AND (( + store_sales.ss_addr_sk = customer_address.ca_address_sk + AND customer_address.ca_country = 'United States' + AND customer_address.ca_state IN ('KY', + 'GA', + 'NM') + AND store_sales.ss_net_profit BETWEEN 100 AND 200 ) + OR ( + store_sales.ss_addr_sk = customer_address.ca_address_sk + AND customer_address.ca_country = 'United States' + AND customer_address.ca_state IN ('MT', + 'OR', + 'IN') + AND store_sales.ss_net_profit BETWEEN 150 AND 300 ) + OR ( + store_sales.ss_addr_sk = customer_address.ca_address_sk + AND customer_address.ca_country = 'United States' + AND customer_address.ca_state IN ('WI', 'MO', 'WV') + AND store_sales.ss_net_profit BETWEEN 50 AND 250 )) ; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/hive/blob/09b6f9a3/ql/src/test/queries/clientpositive/perf/query15.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/perf/query15.q b/ql/src/test/queries/clientpositive/perf/query15.q new file mode 100644 index 0000000..9f75410 --- /dev/null +++ b/ql/src/test/queries/clientpositive/perf/query15.q @@ -0,0 +1 @@ +explain select ca_zip ,sum(cs_sales_price) from catalog_sales ,customer ,customer_address ,date_dim where catalog_sales.cs_bill_customer_sk = customer.c_customer_sk and customer.c_current_addr_sk = customer_address.ca_address_sk and ( substr(ca_zip,1,5) in ('85669', '86197','88274','83405','86475', '85392', '85460', '80348', '81792') or customer_address.ca_state in ('CA','WA','GA') or catalog_sales.cs_sales_price > 500) and catalog_sales.cs_sold_date_sk = date_dim.d_date_sk and date_dim.d_qoy = 2 and date_dim.d_year = 2000 group by ca_zip order by ca_zip limit 100; http://git-wip-us.apache.org/repos/asf/hive/blob/09b6f9a3/ql/src/test/queries/clientpositive/perf/query17.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/perf/query17.q b/ql/src/test/queries/clientpositive/perf/query17.q new file mode 100644 index 0000000..bb8a33b --- /dev/null +++ b/ql/src/test/queries/clientpositive/perf/query17.q @@ -0,0 +1 @@ +explain select i_item_id ,i_item_desc ,s_state ,count(ss_quantity) as store_sales_quantitycount ,avg(ss_quantity) as store_sales_quantityave ,stddev_samp(ss_quantity) as store_sales_quantitystdev ,stddev_samp(ss_quantity)/avg(ss_quantity) as store_sales_quantitycov ,count(sr_return_quantity) as_store_returns_quantitycount ,avg(sr_return_quantity) as_store_returns_quantityave ,stddev_samp(sr_return_quantity) as_store_returns_quantitystdev ,stddev_samp(sr_return_quantity)/avg(sr_return_quantity) as store_returns_quantitycov ,count(cs_quantity) as catalog_sales_quantitycount ,avg(cs_quantity) as catalog_sales_quantityave ,stddev_samp(cs_quantity)/avg(cs_quantity) as catalog_sales_quantitystdev ,stddev_samp(cs_quantity)/avg(cs_quantity) as catalog_sales_quantitycov from store_sales ,store_returns ,catalog_sales ,date_dim d1 ,date_dim d2 ,date_dim d3 ,store ,item where d1.d_quarter_name = '2000Q1' and d1.d_date_sk = store_sales.ss_sold_date_sk and item.i_item_sk = store_sales.ss_item_sk and store.s_store_sk = store_sales.ss_store_sk and store_sales.ss_customer_sk = store_returns.sr_customer_sk and store_sales.ss_item_sk = store_returns.sr_item_sk and store_sales.ss_ticket_number = store_returns.sr_ticket_number and store_returns.sr_returned_date_sk = d2.d_date_sk and d2.d_quarter_name in ('2000Q1','2000Q2','2000Q3') and store_returns.sr_customer_sk = catalog_sales.cs_bill_customer_sk and store_returns.sr_item_sk = catalog_sales.cs_item_sk and catalog_sales.cs_sold_date_sk = d3.d_date_sk and d3.d_quarter_name in ('2000Q1','2000Q2','2000Q3') group by i_item_id ,i_item_desc ,s_state order by i_item_id ,i_item_desc ,s_state limit 100;