Return-Path: X-Original-To: apmail-hawq-commits-archive@minotaur.apache.org Delivered-To: apmail-hawq-commits-archive@minotaur.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id D5B2517E93 for ; Sat, 19 Sep 2015 00:36:09 +0000 (UTC) Received: (qmail 6884 invoked by uid 500); 19 Sep 2015 00:36:09 -0000 Delivered-To: apmail-hawq-commits-archive@hawq.apache.org Received: (qmail 6834 invoked by uid 500); 19 Sep 2015 00:36:09 -0000 Mailing-List: contact commits-help@hawq.incubator.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@hawq.incubator.apache.org Delivered-To: mailing list commits@hawq.incubator.apache.org Received: (qmail 6824 invoked by uid 99); 19 Sep 2015 00:36:09 -0000 Received: from Unknown (HELO spamd1-us-west.apache.org) (209.188.14.142) by apache.org (qpsmtpd/0.29) with ESMTP; Sat, 19 Sep 2015 00:36:09 +0000 Received: from localhost (localhost [127.0.0.1]) by spamd1-us-west.apache.org (ASF Mail Server at spamd1-us-west.apache.org) with ESMTP id 2FBCFF41B0 for ; Sat, 19 Sep 2015 00:36:09 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd1-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: 1.791 X-Spam-Level: * X-Spam-Status: No, score=1.791 tagged_above=-999 required=6.31 tests=[KAM_ASCII_DIVIDERS=0.8, KAM_LAZY_DOMAIN_SECURITY=1, T_RP_MATCHES_RCVD=-0.01, URIBL_BLOCKED=0.001] autolearn=disabled Received: from mx1-us-west.apache.org ([10.40.0.8]) by localhost (spamd1-us-west.apache.org [10.40.0.7]) (amavisd-new, port 10024) with ESMTP id rRGxJQ994UBc for ; Sat, 19 Sep 2015 00:35:54 +0000 (UTC) Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by mx1-us-west.apache.org (ASF Mail Server at mx1-us-west.apache.org) with SMTP id DF80C20F1E for ; Sat, 19 Sep 2015 00:35:45 +0000 (UTC) Received: (qmail 3671 invoked by uid 99); 19 Sep 2015 00:35:45 -0000 Received: from git1-us-west.apache.org (HELO git1-us-west.apache.org) (140.211.11.23) by apache.org (qpsmtpd/0.29) with ESMTP; Sat, 19 Sep 2015 00:35:45 +0000 Received: by git1-us-west.apache.org (ASF Mail Server at git1-us-west.apache.org, from userid 33) id BDA93E0942; Sat, 19 Sep 2015 00:35:45 +0000 (UTC) Content-Type: text/plain; charset="us-ascii" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit From: rvs@apache.org To: commits@hawq.incubator.apache.org Date: Sat, 19 Sep 2015 00:36:20 -0000 Message-Id: <84a8aa59990844af95db3c1767a3c4cf@git.apache.org> In-Reply-To: <1419874d5448421596d056c57b3abfc4@git.apache.org> References: <1419874d5448421596d056c57b3abfc4@git.apache.org> X-Mailer: ASF-Git Admin Mailer Subject: [37/51] [partial] incubator-hawq git commit: SGA import http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/8b26974c/contrib/hawq-hadoop/hawq-mapreduce-tool/generate_load_tpch.pl ---------------------------------------------------------------------- diff --git a/contrib/hawq-hadoop/hawq-mapreduce-tool/generate_load_tpch.pl b/contrib/hawq-hadoop/hawq-mapreduce-tool/generate_load_tpch.pl new file mode 100755 index 0000000..fa3b5a3 --- /dev/null +++ b/contrib/hawq-hadoop/hawq-mapreduce-tool/generate_load_tpch.pl @@ -0,0 +1,573 @@ +#!/usr/bin/env perl + +#################################################### +#Author : Bharath +#Comment : Takes the scale factor, number of primary segments, master port number and database name as input and load the tpch data in parallel by streaming the output of dbgen using external tables. Expects the dbgen program and dists.dss file to be present in the $GPHOME/bin directory on all the segment hosts. +#Usage : sample usage > perl load_tpch.pl -scale -num -port -db +################################################### + +#use strict; +use warnings; +use Getopt::Long; + +# Arguments to the program +my ($scale, $num_primary_segs, $port, $dbname, $table, $orientation, $haspartition, $dbversion, $compress_flag, $compress_type, $compress_level); + +GetOptions( + 'scale=s' => \$scale, + 'num=i' => \$num_primary_segs, + 'port=i' => \$port, + 'db=s' => \$dbname, + 'table=s' => \$table, + 'orient=s' => \$orientation, + 'type=s' => \$compress_type, + 'level=s' => \$compress_level, + 'partition=s' => \$haspartition, + 'dbversion=s' => \$dbversion, + 'compress=s' =>\$compress_flag + ); + +my $ddl_dml_file = "/tmp/load_tpch_".`date "+%Y%m%d%H%M"`; +chomp($ddl_dml_file); + +unless (defined($scale) && defined($num_primary_segs) && defined($port) && defined($dbname) && defined($haspartition) && defined($dbversion) && defined($compress_flag) && defined($orientation)) +{ + print "\nUsage: $0 -scale -num -port -db -table -orient -partition -dbverions -compress -type -level \n\n"; + print "The program expects database to be present. Also, it expects dbgen program and dists.dss file to be present in GPHOME/bin directory. \n\n"; + exit; +} + +open(OUT,">$ddl_dml_file") or die("Unable to open file $ddl_dml_file\n"); + +print "\nWriting the necessary ddl and dml statements to file: $ddl_dml_file\n"; + +# Define table name + +if ($dbversion eq "hawq") +{ + $table = "ao"; +} +elsif ($dbversion eq "gpdb") +{ + $table = "heap"; +} +$name_suffix=""; +$sql_suffix=""; +$ori=$orientation; +if ($table eq "ao") +{ + if ($compress_flag eq "true") + { + $type=$compress_type; + $level=$compress_level; + $name_suffix="_".$table."_".$ori."_".$type."_level".$level; + $sql_suffix = "WITH (appendonly=true, orientation=$ori, compresstype=$type, compresslevel=$level)"; + } + else + { + $name_suffix="_".$table."_".$ori; + $sql_suffix = "WITH (appendonly=true, orientation=$ori)"; + } +} +else +{ + $name_suffix="_".$table; +} + +$compress_sql = ""; +if($compress_type eq "true") +{ + $compress_sql = ", compresstype=$compress_type, compresslevel=$compress_level" +} + +$nation = "nation".$name_suffix; +$region = "region".$name_suffix; +$part = "part".$name_suffix; +$supplier = "supplier".$name_suffix; +$partsupp = "partsupp".$name_suffix; +$customer = "customer".$name_suffix; +$orders = "orders".$name_suffix; +$lineitem = "lineitem".$name_suffix; + +$e_nation = "e_nation".$name_suffix; +$e_region = "e_region".$name_suffix; +$e_part = "e_part".$name_suffix; +$e_supplier = "e_supplier".$name_suffix; +$e_partsupp = "e_partsupp".$name_suffix; +$e_customer = "e_customer".$name_suffix; +$e_orders = "e_orders".$name_suffix; +$e_lineitem = "e_lineitem".$name_suffix; + +# Drop table statments + +print OUT "\\timing on\n"; +print OUT "drop external web table if exists $e_nation;\n"; +print OUT "drop external web table if exists $e_customer;\n"; +print OUT "drop external web table if exists $e_region;\n"; +print OUT "drop external web table if exists $e_part;\n"; +print OUT "drop external web table if exists $e_supplier;\n"; +print OUT "drop external web table if exists $e_partsupp;\n"; +print OUT "drop external web table if exists $e_orders;\n"; +print OUT "drop external web table if exists $e_lineitem;\n\n"; +print OUT "drop table if exists $nation cascade;\n"; +print OUT "drop table if exists $region cascade;\n"; +print OUT "drop table if exists $part cascade;\n"; +print OUT "drop table if exists $supplier cascade;\n"; +print OUT "drop table if exists $partsupp cascade;\n"; +print OUT "drop table if exists $customer cascade;\n"; +print OUT "drop table if exists $orders cascade;\n"; +print OUT "drop table if exists $lineitem cascade;\n\n"; + +# Create table statements + +print OUT "CREATE TABLE $nation ( N_NATIONKEY INTEGER NOT NULL, + N_NAME CHAR(25) NOT NULL, + N_REGIONKEY INTEGER NOT NULL, + N_COMMENT VARCHAR(152)) $sql_suffix;\n"; + +print OUT "CREATE TABLE $region ( R_REGIONKEY INTEGER NOT NULL, + R_NAME CHAR(25) NOT NULL, + R_COMMENT VARCHAR(152)) $sql_suffix;\n"; + +print OUT "CREATE TABLE $part ( P_PARTKEY INTEGER NOT NULL, + P_NAME VARCHAR(55) NOT NULL, + P_MFGR CHAR(25) NOT NULL, + P_BRAND CHAR(10) NOT NULL, + P_TYPE VARCHAR(25) NOT NULL, + P_SIZE INTEGER NOT NULL, + P_CONTAINER CHAR(10) NOT NULL, + P_RETAILPRICE DECIMAL(15,2) NOT NULL, + P_COMMENT VARCHAR(23) NOT NULL ) $sql_suffix;\n"; + +print OUT "CREATE TABLE $supplier ( S_SUPPKEY INTEGER NOT NULL, + S_NAME CHAR(25) NOT NULL, + S_ADDRESS VARCHAR(40) NOT NULL, + S_NATIONKEY INTEGER NOT NULL, + S_PHONE CHAR(15) NOT NULL, + S_ACCTBAL DECIMAL(15,2) NOT NULL, + S_COMMENT VARCHAR(101) NOT NULL) $sql_suffix;\n"; + +print OUT "CREATE TABLE $partsupp ( PS_PARTKEY INTEGER NOT NULL, + PS_SUPPKEY INTEGER NOT NULL, + PS_AVAILQTY INTEGER NOT NULL, + PS_SUPPLYCOST DECIMAL(15,2) NOT NULL, + PS_COMMENT VARCHAR(199) NOT NULL ) $sql_suffix;\n"; + +print OUT "CREATE TABLE $customer ( C_CUSTKEY INTEGER NOT NULL, + C_NAME VARCHAR(25) NOT NULL, + C_ADDRESS VARCHAR(40) NOT NULL, + C_NATIONKEY INTEGER NOT NULL, + C_PHONE CHAR(15) NOT NULL, + C_ACCTBAL DECIMAL(15,2) NOT NULL, + C_MKTSEGMENT CHAR(10) NOT NULL, + C_COMMENT VARCHAR(117) NOT NULL) $sql_suffix;\n"; + +print OUT "CREATE TABLE $orders ( O_ORDERKEY INT8 NOT NULL, + O_CUSTKEY INTEGER NOT NULL, + O_ORDERSTATUS CHAR(1) NOT NULL, + O_TOTALPRICE DECIMAL(15,2) NOT NULL, + O_ORDERDATE DATE NOT NULL, + O_ORDERPRIORITY CHAR(15) NOT NULL, + O_CLERK CHAR(15) NOT NULL, + O_SHIPPRIORITY INTEGER NOT NULL, + O_COMMENT VARCHAR(79) NOT NULL) $sql_suffix"; + + +if ($haspartition eq "true") +{ +print OUT "PARTITION BY RANGE(o_orderdate) + ( + PARTITION p1_1 START ('1992-01-01'::date) END ('1992-01-21'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_1', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_2 START ('1992-01-21'::date) END ('1992-02-10'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_2', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_3 START ('1992-02-10'::date) END ('1992-03-01'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_3', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_4 START ('1992-03-01'::date) END ('1992-03-21'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_4', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_5 START ('1992-03-21'::date) END ('1992-04-10'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_5', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_6 START ('1992-04-10'::date) END ('1992-04-30'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_6', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_7 START ('1992-04-30'::date) END ('1992-05-20'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_7', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_8 START ('1992-05-20'::date) END ('1992-06-09'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_8', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_9 START ('1992-06-09'::date) END ('1992-06-29'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_9', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_10 START ('1992-06-29'::date) END ('1992-07-19'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_10', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_11 START ('1992-07-19'::date) END ('1992-08-08'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_11', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_12 START ('1992-08-08'::date) END ('1992-08-28'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_12', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_13 START ('1992-08-28'::date) END ('1992-09-17'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_13', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_14 START ('1992-09-17'::date) END ('1992-10-07'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_14', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_15 START ('1992-10-07'::date) END ('1992-10-27'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_15', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_16 START ('1992-10-27'::date) END ('1992-11-16'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_16', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_17 START ('1992-11-16'::date) END ('1992-12-06'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_17', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_18 START ('1992-12-06'::date) END ('1992-12-26'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_18', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_19 START ('1992-12-26'::date) END ('1993-01-15'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_19', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_20 START ('1993-01-15'::date) END ('1993-02-04'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_20', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_21 START ('1993-02-04'::date) END ('1993-02-24'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_21', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_22 START ('1993-02-24'::date) END ('1993-03-16'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_22', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_23 START ('1993-03-16'::date) END ('1993-04-05'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_23', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_24 START ('1993-04-05'::date) END ('1993-04-25'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_24', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_25 START ('1993-04-25'::date) END ('1993-05-15'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_25', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_26 START ('1993-05-15'::date) END ('1993-06-04'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_26', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_27 START ('1993-06-04'::date) END ('1993-06-24'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_27', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_28 START ('1993-06-24'::date) END ('1993-07-14'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_28', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_29 START ('1993-07-14'::date) END ('1993-08-03'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_29', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_30 START ('1993-08-03'::date) END ('1993-08-23'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_30', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_31 START ('1993-08-23'::date) END ('1993-09-12'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_31', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_32 START ('1993-09-12'::date) END ('1993-10-02'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_32', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_33 START ('1993-10-02'::date) END ('1993-10-22'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_33', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_34 START ('1993-10-22'::date) END ('1993-11-11'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_34', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_35 START ('1993-11-11'::date) END ('1993-12-01'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_35', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_36 START ('1993-12-01'::date) END ('1993-12-21'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_36', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_37 START ('1993-12-21'::date) END ('1994-01-10'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_37', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_38 START ('1994-01-10'::date) END ('1994-01-30'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_38', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_39 START ('1994-01-30'::date) END ('1994-02-19'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_39', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_40 START ('1994-02-19'::date) END ('1994-03-11'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_40', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_41 START ('1994-03-11'::date) END ('1994-03-31'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_41', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_42 START ('1994-03-31'::date) END ('1994-04-20'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_42', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_43 START ('1994-04-20'::date) END ('1994-05-10'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_43', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_44 START ('1994-05-10'::date) END ('1994-05-30'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_44', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_45 START ('1994-05-30'::date) END ('1994-06-19'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_45', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_46 START ('1994-06-19'::date) END ('1994-07-09'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_46', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_47 START ('1994-07-09'::date) END ('1994-07-29'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_47', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_48 START ('1994-07-29'::date) END ('1994-08-18'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_48', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_49 START ('1994-08-18'::date) END ('1994-09-07'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_49', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_50 START ('1994-09-07'::date) END ('1994-09-27'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_50', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_51 START ('1994-09-27'::date) END ('1994-10-17'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_51', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_52 START ('1994-10-17'::date) END ('1994-11-06'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_52', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_53 START ('1994-11-06'::date) END ('1994-11-26'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_53', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_54 START ('1994-11-26'::date) END ('1994-12-16'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_54', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_55 START ('1994-12-16'::date) END ('1995-01-05'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_55', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_56 START ('1995-01-05'::date) END ('1995-01-25'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_56', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_57 START ('1995-01-25'::date) END ('1995-02-14'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_57', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_58 START ('1995-02-14'::date) END ('1995-03-06'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_58', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_59 START ('1995-03-06'::date) END ('1995-03-26'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_59', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_60 START ('1995-03-26'::date) END ('1995-04-15'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_60', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_61 START ('1995-04-15'::date) END ('1995-05-05'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_61', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_62 START ('1995-05-05'::date) END ('1995-05-25'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_62', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_63 START ('1995-05-25'::date) END ('1995-06-14'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_63', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_64 START ('1995-06-14'::date) END ('1995-07-04'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_64', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_65 START ('1995-07-04'::date) END ('1995-07-24'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_65', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_66 START ('1995-07-24'::date) END ('1995-08-13'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_66', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_67 START ('1995-08-13'::date) END ('1995-09-02'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_67', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_68 START ('1995-09-02'::date) END ('1995-09-22'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_68', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_69 START ('1995-09-22'::date) END ('1995-10-12'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_69', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_70 START ('1995-10-12'::date) END ('1995-11-01'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_70', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_71 START ('1995-11-01'::date) END ('1995-11-21'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_71', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_72 START ('1995-11-21'::date) END ('1995-12-11'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_72', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_73 START ('1995-12-11'::date) END ('1995-12-31'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_73', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_74 START ('1995-12-31'::date) END ('1996-01-20'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_74', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_75 START ('1996-01-20'::date) END ('1996-02-09'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_75', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_76 START ('1996-02-09'::date) END ('1996-02-29'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_76', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_77 START ('1996-02-29'::date) END ('1996-03-20'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_77', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_78 START ('1996-03-20'::date) END ('1996-04-09'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_78', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_79 START ('1996-04-09'::date) END ('1996-04-29'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_79', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_80 START ('1996-04-29'::date) END ('1996-05-19'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_80', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_81 START ('1996-05-19'::date) END ('1996-06-08'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_81', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_82 START ('1996-06-08'::date) END ('1996-06-28'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_82', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_83 START ('1996-06-28'::date) END ('1996-07-18'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_83', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_84 START ('1996-07-18'::date) END ('1996-08-07'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_84', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_85 START ('1996-08-07'::date) END ('1996-08-27'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_85', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_86 START ('1996-08-27'::date) END ('1996-09-16'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_86', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_87 START ('1996-09-16'::date) END ('1996-10-06'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_87', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_88 START ('1996-10-06'::date) END ('1996-10-26'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_88', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_89 START ('1996-10-26'::date) END ('1996-11-15'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_89', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_90 START ('1996-11-15'::date) END ('1996-12-05'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_90', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_91 START ('1996-12-05'::date) END ('1996-12-25'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_91', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_92 START ('1996-12-25'::date) END ('1997-01-14'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_92', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_93 START ('1997-01-14'::date) END ('1997-02-03'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_93', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_94 START ('1997-02-03'::date) END ('1997-02-23'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_94', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_95 START ('1997-02-23'::date) END ('1997-03-15'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_95', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_96 START ('1997-03-15'::date) END ('1997-04-04'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_96', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_97 START ('1997-04-04'::date) END ('1997-04-24'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_97', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_98 START ('1997-04-24'::date) END ('1997-05-14'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_98', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_99 START ('1997-05-14'::date) END ('1997-06-03'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_99', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_100 START ('1997-06-03'::date) END ('1997-06-23'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_100', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_101 START ('1997-06-23'::date) END ('1997-07-13'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_101', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_102 START ('1997-07-13'::date) END ('1997-08-02'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_102', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_103 START ('1997-08-02'::date) END ('1997-08-22'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_103', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_104 START ('1997-08-22'::date) END ('1997-09-11'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_104', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_105 START ('1997-09-11'::date) END ('1997-10-01'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_105', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_106 START ('1997-10-01'::date) END ('1997-10-21'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_106', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_107 START ('1997-10-21'::date) END ('1997-11-10'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_107', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_108 START ('1997-11-10'::date) END ('1997-11-30'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_108', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_109 START ('1997-11-30'::date) END ('1997-12-20'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_109', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_110 START ('1997-12-20'::date) END ('1998-01-09'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_110', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_111 START ('1998-01-09'::date) END ('1998-01-29'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_111', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_112 START ('1998-01-29'::date) END ('1998-02-18'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_112', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_113 START ('1998-02-18'::date) END ('1998-03-10'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_113', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_114 START ('1998-03-10'::date) END ('1998-03-30'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_114', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_115 START ('1998-03-30'::date) END ('1998-04-19'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_115', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_116 START ('1998-04-19'::date) END ('1998-05-09'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_116', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_117 START ('1998-05-09'::date) END ('1998-05-29'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_117', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_118 START ('1998-05-29'::date) END ('1998-06-18'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_118', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_119 START ('1998-06-18'::date) END ('1998-07-08'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_119', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_120 START ('1998-07-08'::date) END ('1998-07-28'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_120', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_121 START ('1998-07-28'::date) END ('1998-08-17'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_121', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_122 START ('1998-08-17'::date) END ('1998-09-06'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_122', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_123 START ('1998-09-06'::date) END ('1998-09-26'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_123', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_124 START ('1998-09-26'::date) END ('1998-10-16'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_124', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_125 START ('1998-10-16'::date) END ('1998-11-05'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_125', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_126 START ('1998-11-05'::date) END ('1998-11-25'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_126', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_127 START ('1998-11-25'::date) END ('1998-12-15'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_127', orientation=$orientation , appendonly=true$compress_sql ), + PARTITION p1_128 START ('1998-12-15'::date) END ('1998-12-31'::date) EVERY ('20 days'::interval) WITH (tablename='${orders}_1_prt_p1_128', orientation=$orientation , appendonly=true$compress_sql ) + )"; +} +print OUT ";\n\n"; + + + + +print OUT "CREATE TABLE $lineitem ( L_ORDERKEY INT8 NOT NULL, + L_PARTKEY INTEGER NOT NULL, + L_SUPPKEY INTEGER NOT NULL, + L_LINENUMBER INTEGER NOT NULL, + L_QUANTITY DECIMAL(15,2) NOT NULL, + L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL, + L_DISCOUNT DECIMAL(15,2) NOT NULL, + L_TAX DECIMAL(15,2) NOT NULL, + L_RETURNFLAG CHAR(1) NOT NULL, + L_LINESTATUS CHAR(1) NOT NULL, + L_SHIPDATE DATE NOT NULL, + L_COMMITDATE DATE NOT NULL, + L_RECEIPTDATE DATE NOT NULL, + L_SHIPINSTRUCT CHAR(25) NOT NULL, + L_SHIPMODE CHAR(10) NOT NULL, + L_COMMENT VARCHAR(44) NOT NULL) $sql_suffix"; +if ($haspartition eq "true") +{ +print OUT "PARTITION BY RANGE(l_shipdate)( + PARTITION p1_1 START ('1992-01-01'::date) END ('1992-01-21'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_1', orientation=$orientation, appendonly=true$compress_sql), + PARTITION p1_2 START ('1992-01-21'::date) END ('1992-02-10'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_2', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_3 START ('1992-02-10'::date) END ('1992-03-01'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_3', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_4 START ('1992-03-01'::date) END ('1992-03-21'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_4', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_5 START ('1992-03-21'::date) END ('1992-04-10'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_5', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_6 START ('1992-04-10'::date) END ('1992-04-30'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_6', orientation=$orientation, appendonly=true$compress_sql), + PARTITION p1_7 START ('1992-04-30'::date) END ('1992-05-20'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_7', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_8 START ('1992-05-20'::date) END ('1992-06-09'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_8', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_9 START ('1992-06-09'::date) END ('1992-06-29'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_9', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_10 START ('1992-06-29'::date) END ('1992-07-19'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_10', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_11 START ('1992-07-19'::date) END ('1992-08-08'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_11', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_12 START ('1992-08-08'::date) END ('1992-08-28'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_12', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_13 START ('1992-08-28'::date) END ('1992-09-17'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_13', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_14 START ('1992-09-17'::date) END ('1992-10-07'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_14', orientation=$orientation, appendonly=true$compress_sql), + PARTITION p1_15 START ('1992-10-07'::date) END ('1992-10-27'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_15', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_16 START ('1992-10-27'::date) END ('1992-11-16'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_16', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_17 START ('1992-11-16'::date) END ('1992-12-06'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_17', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_18 START ('1992-12-06'::date) END ('1992-12-26'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_18', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_19 START ('1992-12-26'::date) END ('1993-01-15'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_19', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_20 START ('1993-01-15'::date) END ('1993-02-04'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_20', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_21 START ('1993-02-04'::date) END ('1993-02-24'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_21', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_22 START ('1993-02-24'::date) END ('1993-03-16'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_22', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_23 START ('1993-03-16'::date) END ('1993-04-05'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_23', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_24 START ('1993-04-05'::date) END ('1993-04-25'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_24', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_25 START ('1993-04-25'::date) END ('1993-05-15'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_25', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_26 START ('1993-05-15'::date) END ('1993-06-04'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_26', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_27 START ('1993-06-04'::date) END ('1993-06-24'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_27', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_28 START ('1993-06-24'::date) END ('1993-07-14'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_28', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_29 START ('1993-07-14'::date) END ('1993-08-03'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_29', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_30 START ('1993-08-03'::date) END ('1993-08-23'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_30', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_31 START ('1993-08-23'::date) END ('1993-09-12'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_31', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_32 START ('1993-09-12'::date) END ('1993-10-02'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_32', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_33 START ('1993-10-02'::date) END ('1993-10-22'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_33', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_34 START ('1993-10-22'::date) END ('1993-11-11'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_34', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_35 START ('1993-11-11'::date) END ('1993-12-01'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_35', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_36 START ('1993-12-01'::date) END ('1993-12-21'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_36', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_37 START ('1993-12-21'::date) END ('1994-01-10'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_37', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_38 START ('1994-01-10'::date) END ('1994-01-30'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_38', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_39 START ('1994-01-30'::date) END ('1994-02-19'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_39', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_40 START ('1994-02-19'::date) END ('1994-03-11'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_40', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_41 START ('1994-03-11'::date) END ('1994-03-31'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_41', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_42 START ('1994-03-31'::date) END ('1994-04-20'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_42', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_43 START ('1994-04-20'::date) END ('1994-05-10'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_43', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_44 START ('1994-05-10'::date) END ('1994-05-30'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_44', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_45 START ('1994-05-30'::date) END ('1994-06-19'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_45', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_46 START ('1994-06-19'::date) END ('1994-07-09'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_46', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_47 START ('1994-07-09'::date) END ('1994-07-29'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_47', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_48 START ('1994-07-29'::date) END ('1994-08-18'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_48', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_49 START ('1994-08-18'::date) END ('1994-09-07'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_49', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_50 START ('1994-09-07'::date) END ('1994-09-27'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_50', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_51 START ('1994-09-27'::date) END ('1994-10-17'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_51', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_52 START ('1994-10-17'::date) END ('1994-11-06'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_52', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_53 START ('1994-11-06'::date) END ('1994-11-26'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_53', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_54 START ('1994-11-26'::date) END ('1994-12-16'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_54', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_55 START ('1994-12-16'::date) END ('1995-01-05'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_55', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_56 START ('1995-01-05'::date) END ('1995-01-25'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_56', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_57 START ('1995-01-25'::date) END ('1995-02-14'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_57', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_58 START ('1995-02-14'::date) END ('1995-03-06'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_58', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_59 START ('1995-03-06'::date) END ('1995-03-26'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_59', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_60 START ('1995-03-26'::date) END ('1995-04-15'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_60', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_61 START ('1995-04-15'::date) END ('1995-05-05'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_61', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_62 START ('1995-05-05'::date) END ('1995-05-25'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_62', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_63 START ('1995-05-25'::date) END ('1995-06-14'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_63', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_64 START ('1995-06-14'::date) END ('1995-07-04'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_64', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_65 START ('1995-07-04'::date) END ('1995-07-24'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_65', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_66 START ('1995-07-24'::date) END ('1995-08-13'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_66', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_67 START ('1995-08-13'::date) END ('1995-09-02'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_67', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_68 START ('1995-09-02'::date) END ('1995-09-22'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_68', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_69 START ('1995-09-22'::date) END ('1995-10-12'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_69', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_70 START ('1995-10-12'::date) END ('1995-11-01'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_70', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_71 START ('1995-11-01'::date) END ('1995-11-21'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_71', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_72 START ('1995-11-21'::date) END ('1995-12-11'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_72', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_73 START ('1995-12-11'::date) END ('1995-12-31'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_73', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_74 START ('1995-12-31'::date) END ('1996-01-20'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_74', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_75 START ('1996-01-20'::date) END ('1996-02-09'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_75', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_76 START ('1996-02-09'::date) END ('1996-02-29'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_76', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_77 START ('1996-02-29'::date) END ('1996-03-20'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_77', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_78 START ('1996-03-20'::date) END ('1996-04-09'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_78', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_79 START ('1996-04-09'::date) END ('1996-04-29'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_79', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_80 START ('1996-04-29'::date) END ('1996-05-19'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_80', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_81 START ('1996-05-19'::date) END ('1996-06-08'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_81', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_82 START ('1996-06-08'::date) END ('1996-06-28'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_82', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_83 START ('1996-06-28'::date) END ('1996-07-18'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_83', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_84 START ('1996-07-18'::date) END ('1996-08-07'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_84', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_85 START ('1996-08-07'::date) END ('1996-08-27'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_85', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_86 START ('1996-08-27'::date) END ('1996-09-16'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_86', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_87 START ('1996-09-16'::date) END ('1996-10-06'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_87', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_88 START ('1996-10-06'::date) END ('1996-10-26'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_88', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_89 START ('1996-10-26'::date) END ('1996-11-15'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_89', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_90 START ('1996-11-15'::date) END ('1996-12-05'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_90', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_91 START ('1996-12-05'::date) END ('1996-12-25'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_91', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_92 START ('1996-12-25'::date) END ('1997-01-14'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_92', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_93 START ('1997-01-14'::date) END ('1997-02-03'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_93', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_94 START ('1997-02-03'::date) END ('1997-02-23'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_94', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_95 START ('1997-02-23'::date) END ('1997-03-15'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_95', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_96 START ('1997-03-15'::date) END ('1997-04-04'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_96', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_97 START ('1997-04-04'::date) END ('1997-04-24'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_97', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_98 START ('1997-04-24'::date) END ('1997-05-14'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_98', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_99 START ('1997-05-14'::date) END ('1997-06-03'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_99', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_100 START ('1997-06-03'::date) END ('1997-06-23'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_100', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_101 START ('1997-06-23'::date) END ('1997-07-13'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_101', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_102 START ('1997-07-13'::date) END ('1997-08-02'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_102', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_103 START ('1997-08-02'::date) END ('1997-08-22'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_103', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_104 START ('1997-08-22'::date) END ('1997-09-11'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_104', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_105 START ('1997-09-11'::date) END ('1997-10-01'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_105', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_106 START ('1997-10-01'::date) END ('1997-10-21'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_106', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_107 START ('1997-10-21'::date) END ('1997-11-10'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_107', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_108 START ('1997-11-10'::date) END ('1997-11-30'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_108', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_109 START ('1997-11-30'::date) END ('1997-12-20'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_109', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_110 START ('1997-12-20'::date) END ('1998-01-09'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_110', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_111 START ('1998-01-09'::date) END ('1998-01-29'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_111', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_112 START ('1998-01-29'::date) END ('1998-02-18'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_112', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_113 START ('1998-02-18'::date) END ('1998-03-10'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_113', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_114 START ('1998-03-10'::date) END ('1998-03-30'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_114', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_115 START ('1998-03-30'::date) END ('1998-04-19'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_115', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_116 START ('1998-04-19'::date) END ('1998-05-09'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_116', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_117 START ('1998-05-09'::date) END ('1998-05-29'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_117', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_118 START ('1998-05-29'::date) END ('1998-06-18'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_118', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_119 START ('1998-06-18'::date) END ('1998-07-08'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_119', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_120 START ('1998-07-08'::date) END ('1998-07-28'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_120', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_121 START ('1998-07-28'::date) END ('1998-08-17'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_121', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_122 START ('1998-08-17'::date) END ('1998-09-06'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_122', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_123 START ('1998-09-06'::date) END ('1998-09-26'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_123', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_124 START ('1998-09-26'::date) END ('1998-10-16'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_124', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_125 START ('1998-10-16'::date) END ('1998-11-05'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_125', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_126 START ('1998-11-05'::date) END ('1998-11-25'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_126', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_127 START ('1998-11-25'::date) END ('1998-12-15'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_127', orientation=$orientation, appendonly=true$compress_sql ), + PARTITION p1_128 START ('1998-12-15'::date) END ('1998-12-31'::date) EVERY ('20 days'::interval) WITH (tablename='${lineitem}_1_prt_p1_128', orientation=$orientation, appendonly=true$compress_sql) + )"; +} +print OUT ";\n\n"; + +print OUT "create external web table $e_nation (N_NATIONKEY INTEGER , + N_NAME CHAR(25) , + N_REGIONKEY INTEGER , + N_COMMENT VARCHAR(152)) + execute 'bash -c \"\$GPHOME/bin/dbgen -b \$GPHOME/bin/dists.dss -T n -s $scale\"' on 1 format 'text' (delimiter '|');\n"; + +print OUT "CREATE external web TABLE $e_region ( R_REGIONKEY INTEGER , + R_NAME CHAR(25) , + R_COMMENT VARCHAR(152)) + execute 'bash -c \"\$GPHOME/bin/dbgen -b \$GPHOME/bin/dists.dss -T r -s $scale\"' + on 1 format 'text' (delimiter '|');\n"; + +print OUT "CREATE external web TABLE $e_part ( P_PARTKEY INTEGER , + P_NAME VARCHAR(55) , + P_MFGR CHAR(25) , + P_BRAND CHAR(10) , + P_TYPE VARCHAR(25) , + P_SIZE INTEGER , + P_CONTAINER CHAR(10) , + P_RETAILPRICE DECIMAL(15,2) , + P_COMMENT VARCHAR(23) ) + execute 'bash -c \"\$GPHOME/bin/dbgen -b \$GPHOME/bin/dists.dss -T P -s $scale -N $num_primary_segs -n \$((GP_SEGMENT_ID + 1))\"' + on $num_primary_segs format 'text' (delimiter '|');\n"; + +print OUT "CREATE external web TABLE $e_supplier ( S_SUPPKEY INTEGER , + S_NAME CHAR(25) , + S_ADDRESS VARCHAR(40) , + S_NATIONKEY INTEGER , + S_PHONE CHAR(15) , + S_ACCTBAL DECIMAL(15,2) , + S_COMMENT VARCHAR(101) ) + execute 'bash -c \"\$GPHOME/bin/dbgen -b \$GPHOME/bin/dists.dss -T s -s $scale -N $num_primary_segs -n \$((GP_SEGMENT_ID + 1))\"' + on $num_primary_segs format 'text' (delimiter '|');\n"; + +print OUT "CREATE external web TABLE $e_partsupp ( PS_PARTKEY INTEGER , + PS_SUPPKEY INTEGER , + PS_AVAILQTY INTEGER , + PS_SUPPLYCOST DECIMAL(15,2) , + PS_COMMENT VARCHAR(199) ) + execute 'bash -c \"\$GPHOME/bin/dbgen -b \$GPHOME/bin/dists.dss -T S -s $scale -N $num_primary_segs -n \$((GP_SEGMENT_ID + 1))\"' + on $num_primary_segs format 'text' (delimiter '|');\n"; + +print OUT "CREATE external web TABLE $e_customer ( C_CUSTKEY INTEGER , + C_NAME VARCHAR(25) , + C_ADDRESS VARCHAR(40) , + C_NATIONKEY INTEGER , + C_PHONE CHAR(15) , + C_ACCTBAL DECIMAL(15,2) , + C_MKTSEGMENT CHAR(10) , + C_COMMENT VARCHAR(117) ) + execute 'bash -c \"\$GPHOME/bin/dbgen -b \$GPHOME/bin/dists.dss -T c -s $scale -N $num_primary_segs -n \$((GP_SEGMENT_ID + 1))\"' + on $num_primary_segs format 'text' (delimiter '|');\n"; + +print OUT "CREATE external web TABLE $e_orders ( O_ORDERKEY INT8 , + O_CUSTKEY INTEGER , + O_ORDERSTATUS CHAR(1) , + O_TOTALPRICE DECIMAL(15,2) , + O_ORDERDATE DATE , + O_ORDERPRIORITY CHAR(15) , + O_CLERK CHAR(15) , + O_SHIPPRIORITY INTEGER , + O_COMMENT VARCHAR(79) ) + execute 'bash -c \"\$GPHOME/bin/dbgen -b \$GPHOME/bin/dists.dss -T O -s $scale -N $num_primary_segs -n \$((GP_SEGMENT_ID + 1))\"' + on $num_primary_segs format 'text' (delimiter '|');\n"; + +print OUT "CREATE EXTERNAL WEB TABLE $e_lineitem ( L_ORDERKEY INT8 , + L_PARTKEY INTEGER , + L_SUPPKEY INTEGER , + L_LINENUMBER INTEGER , + L_QUANTITY DECIMAL(15,2) , + L_EXTENDEDPRICE DECIMAL(15,2) , + L_DISCOUNT DECIMAL(15,2) , + L_TAX DECIMAL(15,2) , + L_RETURNFLAG CHAR(1) , + L_LINESTATUS CHAR(1) , + L_SHIPDATE DATE , + L_COMMITDATE DATE , + L_RECEIPTDATE DATE , + L_SHIPINSTRUCT CHAR(25) , + L_SHIPMODE CHAR(10) , + L_COMMENT VARCHAR(44) ) + EXECUTE 'bash -c \"\$GPHOME/bin/dbgen -b \$GPHOME/bin/dists.dss -T L -s $scale -N $num_primary_segs -n \$((GP_SEGMENT_ID + 1))\"' + on $num_primary_segs format 'text' (delimiter '|');\n\n"; + +# Now statements to load the data + +print OUT "insert into $nation select * from $e_nation;\n"; +print OUT "insert into $region select * from $e_region;\n"; +print OUT "insert into $part select * from $e_part;\n"; +print OUT "insert into $supplier select * from $e_supplier;\n"; +print OUT "insert into $partsupp select * from $e_partsupp;\n"; +print OUT "insert into $customer select * from $e_customer;\n"; +print OUT "insert into $orders select * from $e_orders;\n"; +print OUT "insert into $lineitem select * from $e_lineitem;\n"; +print OUT "select current_timestamp;\n"; +#print OUT "VACUUM ANALYZE;\n"; +#print OUT "select current_timestamp;\n"; +print OUT "\\timing off"; + +close(OUT); + +print "Creating tables, loading data and running VACUUM ANALYZE\n"; + +my $tmp_outfile = $ddl_dml_file."_out"; + +system("psql -p $port -d $dbname -a -f $ddl_dml_file > $tmp_outfile 2>&1"); + +print "Done.\n"; http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/8b26974c/contrib/hawq-hadoop/hawq-mapreduce-tool/generate_mr_report.py ---------------------------------------------------------------------- diff --git a/contrib/hawq-hadoop/hawq-mapreduce-tool/generate_mr_report.py b/contrib/hawq-hadoop/hawq-mapreduce-tool/generate_mr_report.py new file mode 100644 index 0000000..e693aef --- /dev/null +++ b/contrib/hawq-hadoop/hawq-mapreduce-tool/generate_mr_report.py @@ -0,0 +1,50 @@ +#!/usr/bin/env python + +############################################################################ +# A script to generate pulse readable report. + +infile = file('inputformat.logs') +outfile = file('inputformat.report','w') + +testsuitename = '' +outline = '' +success = False + +line = infile.readline() +while line: + if line.find('Executing test case: ')!=-1: + if len(outline) != 0: + if success: + outline = outline + "|Test Status|PASS" + else: + outline = outline + "|Test Status|FAILED" + outfile.write(outline+'\n') + + outline="Test Suite Name|" + testsuitename + "|Test Case Name|" + success = False + startIndex = line.find('Executing test case: ') + len('Executing test case: ') + endIndex=len(line) + testcasename = line[startIndex:endIndex-1] + outline = outline + testcasename + "|Test Detail|" + testcasename + " (0.00 ms)" + + elif line.find('Executing test suite: ')!=-1: + startIndex = line.find('Executing test suite: ') + len('Executing test suite: ') + endIndex = len(line) + testsuitename = line[startIndex:endIndex-1] + + elif line.find('Successfully finish test case: ')!=-1: + success = True + + line = infile.readline() + +if len(outline) != 0: + if success: + outline = outline + "|Test Status|PASS" + else: + outline = outline + "|Test Status|FAILED" + outfile.write(outline+'\n') + +outfile.flush() +infile.close() +outfile.close() + http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/8b26974c/contrib/hawq-hadoop/hawq-mapreduce-tool/lib/parquet-column-1.1.0.jar ---------------------------------------------------------------------- diff --git a/contrib/hawq-hadoop/hawq-mapreduce-tool/lib/parquet-column-1.1.0.jar b/contrib/hawq-hadoop/hawq-mapreduce-tool/lib/parquet-column-1.1.0.jar new file mode 100644 index 0000000..698c93b Binary files /dev/null and b/contrib/hawq-hadoop/hawq-mapreduce-tool/lib/parquet-column-1.1.0.jar differ http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/8b26974c/contrib/hawq-hadoop/hawq-mapreduce-tool/lib/parquet-common-1.1.0.jar ---------------------------------------------------------------------- diff --git a/contrib/hawq-hadoop/hawq-mapreduce-tool/lib/parquet-common-1.1.0.jar b/contrib/hawq-hadoop/hawq-mapreduce-tool/lib/parquet-common-1.1.0.jar new file mode 100644 index 0000000..5a45060 Binary files /dev/null and b/contrib/hawq-hadoop/hawq-mapreduce-tool/lib/parquet-common-1.1.0.jar differ http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/8b26974c/contrib/hawq-hadoop/hawq-mapreduce-tool/lib/parquet-encoding-1.1.0.jar ---------------------------------------------------------------------- diff --git a/contrib/hawq-hadoop/hawq-mapreduce-tool/lib/parquet-encoding-1.1.0.jar b/contrib/hawq-hadoop/hawq-mapreduce-tool/lib/parquet-encoding-1.1.0.jar new file mode 100644 index 0000000..a5fc5b3 Binary files /dev/null and b/contrib/hawq-hadoop/hawq-mapreduce-tool/lib/parquet-encoding-1.1.0.jar differ http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/8b26974c/contrib/hawq-hadoop/hawq-mapreduce-tool/lib/parquet-format-1.0.0.jar ---------------------------------------------------------------------- diff --git a/contrib/hawq-hadoop/hawq-mapreduce-tool/lib/parquet-format-1.0.0.jar b/contrib/hawq-hadoop/hawq-mapreduce-tool/lib/parquet-format-1.0.0.jar new file mode 100644 index 0000000..4c52579 Binary files /dev/null and b/contrib/hawq-hadoop/hawq-mapreduce-tool/lib/parquet-format-1.0.0.jar differ http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/8b26974c/contrib/hawq-hadoop/hawq-mapreduce-tool/lib/parquet-hadoop-1.1.0.jar ---------------------------------------------------------------------- diff --git a/contrib/hawq-hadoop/hawq-mapreduce-tool/lib/parquet-hadoop-1.1.0.jar b/contrib/hawq-hadoop/hawq-mapreduce-tool/lib/parquet-hadoop-1.1.0.jar new file mode 100644 index 0000000..6385792 Binary files /dev/null and b/contrib/hawq-hadoop/hawq-mapreduce-tool/lib/parquet-hadoop-1.1.0.jar differ http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/8b26974c/contrib/hawq-hadoop/hawq-mapreduce-tool/lib/postgresql-9.2-1003-jdbc4.jar ---------------------------------------------------------------------- diff --git a/contrib/hawq-hadoop/hawq-mapreduce-tool/lib/postgresql-9.2-1003-jdbc4.jar b/contrib/hawq-hadoop/hawq-mapreduce-tool/lib/postgresql-9.2-1003-jdbc4.jar new file mode 100644 index 0000000..34b2b93 Binary files /dev/null and b/contrib/hawq-hadoop/hawq-mapreduce-tool/lib/postgresql-9.2-1003-jdbc4.jar differ http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/8b26974c/contrib/hawq-hadoop/hawq-mapreduce-tool/lib/snakeyaml-1.12.jar ---------------------------------------------------------------------- diff --git a/contrib/hawq-hadoop/hawq-mapreduce-tool/lib/snakeyaml-1.12.jar b/contrib/hawq-hadoop/hawq-mapreduce-tool/lib/snakeyaml-1.12.jar new file mode 100644 index 0000000..fd314d3 Binary files /dev/null and b/contrib/hawq-hadoop/hawq-mapreduce-tool/lib/snakeyaml-1.12.jar differ http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/8b26974c/contrib/hawq-hadoop/hawq-mapreduce-tool/lib/snappy-java-1.1.0.jar ---------------------------------------------------------------------- diff --git a/contrib/hawq-hadoop/hawq-mapreduce-tool/lib/snappy-java-1.1.0.jar b/contrib/hawq-hadoop/hawq-mapreduce-tool/lib/snappy-java-1.1.0.jar new file mode 100644 index 0000000..f7660c4 Binary files /dev/null and b/contrib/hawq-hadoop/hawq-mapreduce-tool/lib/snappy-java-1.1.0.jar differ http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/8b26974c/contrib/hawq-hadoop/hawq-mapreduce-tool/pom.xml ---------------------------------------------------------------------- diff --git a/contrib/hawq-hadoop/hawq-mapreduce-tool/pom.xml b/contrib/hawq-hadoop/hawq-mapreduce-tool/pom.xml new file mode 100644 index 0000000..5375bbf --- /dev/null +++ b/contrib/hawq-hadoop/hawq-mapreduce-tool/pom.xml @@ -0,0 +1,50 @@ + + + + hawq-hadoop + com.pivotal.hawq + 1.1.0 + ../pom.xml + + 4.0.0 + + hawq-mapreduce-tool + jar + + + + ${project.groupId} + hawq-mapreduce-ao + ${project.version} + + + ${project.groupId} + hawq-mapreduce-parquet + ${project.version} + + + + + + + com.atlassian.maven.plugins + maven-clover2-plugin + + ../lib/clover.license + + + + org.apache.maven.plugins + maven-surefire-plugin + 2.16 + + + **/HAWQInputFormatUnitTest*.java + + + + + + \ No newline at end of file http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/8b26974c/contrib/hawq-hadoop/hawq-mapreduce-tool/run-all-feature-tests-locally.sh ---------------------------------------------------------------------- diff --git a/contrib/hawq-hadoop/hawq-mapreduce-tool/run-all-feature-tests-locally.sh b/contrib/hawq-hadoop/hawq-mapreduce-tool/run-all-feature-tests-locally.sh new file mode 100755 index 0000000..f807805 --- /dev/null +++ b/contrib/hawq-hadoop/hawq-mapreduce-tool/run-all-feature-tests-locally.sh @@ -0,0 +1,66 @@ +#!/bin/sh +## ====================================================================== + +##----------------------------------------------------------------------- +## NOTICE!! +## Before using this script to run feature tests, you should +## 0. source greenplum_path.sh +## 1. start HDFS and HAWQ, and create a "gptest" database +## 2. if you HAWQ are not running at localhost:5432, specify PG_BASE_ADDRESS and PG_BASE_PORT +## 3. make sure HADOOP_HOME points to right place and hadoop commands have been added to PATH +## 4. mvn clean package to generate jar files needed +## +## This script must be run in hawq-mapreduce-tool folder! +##----------------------------------------------------------------------- + +## +## List of default test cases to run. This can be overwritten with +## the FEATURE_TEST_LIST environment variable. +## + +FEATURE_TEST_LIST=${FEATURE_TEST_LIST:=com.pivotal.hawq.mapreduce.ft.HAWQInputFormatFeatureTest_AO_Compression \ + com.pivotal.hawq.mapreduce.ft.HAWQInputFormatFeatureTest_AO_Misc \ + com.pivotal.hawq.mapreduce.ft.HAWQInputFormatFeatureTest_AO_Options \ + com.pivotal.hawq.mapreduce.ft.HAWQInputFormatFeatureTest_AO_Types \ + com.pivotal.hawq.mapreduce.ft.HAWQInputFormatFeatureTest_Parquet_Compression \ + com.pivotal.hawq.mapreduce.ft.HAWQInputFormatFeatureTest_Parquet_Misc \ + com.pivotal.hawq.mapreduce.ft.HAWQInputFormatFeatureTest_Parquet_Options \ + com.pivotal.hawq.mapreduce.ft.HAWQInputFormatFeatureTest_Parquet_Types \ + com.pivotal.hawq.mapreduce.ft.HAWQInputFormatFeatureTest_TPCH \ + com.pivotal.hawq.mapreduce.ft.HAWQInputFormatFeatureTest_Misc } + +## +## Bring in hadoop config environment. +## + +source $HADOOP_HOME/libexec/hadoop-config.sh + + +## +## Clean up any previous tmp working directory and copy contents to +## remote systems. +## + +LIBS=(`find .. -name "*.jar" | grep -v javadoc.jar`) +CLASSPATH=$(IFS=:; echo "${LIBS[*]}"):$CLASSPATH + +for test in ${FEATURE_TEST_LIST}; do + command="java -cp $CLASSPATH org.junit.runner.JUnitCore ${test}" + + echo "" + echo "======================================================================" + echo "Timestamp ..... : $(date)" + echo "Running test .. : ${test}" + echo "command ....... : ${command}" + echo "----------------------------------------------------------------------" + + ${command} | tee -a inputformat.logs 2>&1 + + echo "======================================================================" + echo "" +done + +## +## Generate reports parsed by pulse. +## +python generate_mr_report.py http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/8b26974c/contrib/hawq-hadoop/hawq-mapreduce-tool/run-performance-tests-locally.sh ---------------------------------------------------------------------- diff --git a/contrib/hawq-hadoop/hawq-mapreduce-tool/run-performance-tests-locally.sh b/contrib/hawq-hadoop/hawq-mapreduce-tool/run-performance-tests-locally.sh new file mode 100755 index 0000000..e690a90 --- /dev/null +++ b/contrib/hawq-hadoop/hawq-mapreduce-tool/run-performance-tests-locally.sh @@ -0,0 +1,48 @@ +#!/bin/sh +## ====================================================================== + +##----------------------------------------------------------------------- +## NOTICE!! +## Before using this script to run performance tests, you should +## 0. source greenplum_path.sh +## 1. start HDFS and HAWQ, and create a "gptest" database +## 2. if you HAWQ are not running at localhost:5432, specify PG_BASE_ADDRESS and PG_BASE_PORT +## 3. make sure HADOOP_HOME points to right place and hadoop commands have been added to PATH +## 4. mvn clean package to generate jar files needed +## +## This script must be run in hawq-mapreduce-tool folder! +##----------------------------------------------------------------------- + +##----------------------------------------------------------------------- +## Usage: +## 1. load and query +## ./run-performance-tests-locally ao|parquet scale is_partition tableName [columns] +## +## 2. query-only +## ./run-performance-tests-locally --query-only tableName [columns] +## +## Example: +## ./run-performance-tests-locally ao 0.1 false lineitem_ao_row l_orderkey,l_partkey,l_comment +## ./run-performance-tests-locally --query-only lineitem_ao_row +##----------------------------------------------------------------------- + +## +## Bring in hadoop config environment. +## + +source $HADOOP_HOME/libexec/hadoop-config.sh + + +## +## Clean up any previous tmp working directory and copy contents to +## remote systems. +## + +LIBS=(`find .. -name "*.jar" | grep -v javadoc.jar`) +CLASSPATH=$(IFS=:; echo "${LIBS[*]}"):$CLASSPATH + +COMMAND="java -cp $CLASSPATH com.pivotal.hawq.mapreduce.pt.HAWQInputFormatPerformanceTest_TPCH" + +echo "command: $COMMAND $@" +echo "--------------------" +$COMMAND $@ http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/8b26974c/contrib/hawq-hadoop/hawq-mapreduce-tool/run-tests.sh ---------------------------------------------------------------------- diff --git a/contrib/hawq-hadoop/hawq-mapreduce-tool/run-tests.sh b/contrib/hawq-hadoop/hawq-mapreduce-tool/run-tests.sh new file mode 100755 index 0000000..32cae91 --- /dev/null +++ b/contrib/hawq-hadoop/hawq-mapreduce-tool/run-tests.sh @@ -0,0 +1,101 @@ +#!/bin/sh +## ====================================================================== + +##----------------------------------------------------------------------- +## NOTICE!! +## Before using this script to run feature tests, you should +## 0. source greenplum_path.sh +## 1. start HDFS and HAWQ, and create a "gptest" database +## 2. if you HAWQ are not running at localhost:5432, specify PG_BASE_ADDRESS and PG_BASE_PORT +## 3. make sure HADOOP_HOME points to right place and hadoop commands have been added to PATH +## 4. mvn clean package to generate jar files needed +## +## This script must be run in hawq-mapreduce-tool folder! +##----------------------------------------------------------------------- + +## +## List of default test cases to run. This can be overwritten with +## the FEATURE_TEST_LIST environment variable. +## + +FEATURE_TEST_LIST=${FEATURE_TEST_LIST:=com.pivotal.hawq.mapreduce.ft.HAWQInputFormatFeatureTest_AO_Compression \ + com.pivotal.hawq.mapreduce.ft.HAWQInputFormatFeatureTest_AO_Misc \ + com.pivotal.hawq.mapreduce.ft.HAWQInputFormatFeatureTest_AO_Options \ + com.pivotal.hawq.mapreduce.ft.HAWQInputFormatFeatureTest_AO_Types \ + com.pivotal.hawq.mapreduce.ft.HAWQInputFormatFeatureTest_Parquet_Compression \ + com.pivotal.hawq.mapreduce.ft.HAWQInputFormatFeatureTest_Parquet_Misc \ + com.pivotal.hawq.mapreduce.ft.HAWQInputFormatFeatureTest_Parquet_Options \ + com.pivotal.hawq.mapreduce.ft.HAWQInputFormatFeatureTest_Parquet_Types \ + com.pivotal.hawq.mapreduce.ft.HAWQInputFormatFeatureTest_TPCH } + +## +## Default location for test working tmp directory. +## + +HAWQ_MAPREDUCE_TOOL_TMPDIR=${HAWQ_MAPREDUCE_TOOL_TMPDIR:=/tmp/hawq-mapreduce-tool} + +## +## Bring in hadoop config environment. +## + +export HADOOP_HOME=${HADOOP_HOME:=/usr/lib/gphd/hadoop/} +echo "HADOOP_HOME is set to ${HADOOP_HOME}" + +source /usr/lib/gphd/hadoop/libexec/hadoop-config.sh + +## +## Cleanup previous tmp working directory +## + +rm -rf ${HAWQ_MAPREDUCE_TOOL_TMPDIR} +mkdir -p ${HAWQ_MAPREDUCE_TOOL_TMPDIR} + +## +## Populate tmp working directory +## + +cp ../../demo_mapreduce/* ${HAWQ_MAPREDUCE_TOOL_TMPDIR} + +## +## Clean up any previous tmp working directory and copy contents to +## remote systems. +## + +for host in smdw sdw1 sdw2; do + ssh ${host} rm -rf ${HAWQ_MAPREDUCE_TOOL_TMPDIR} + scp -r ${HAWQ_MAPREDUCE_TOOL_TMPDIR} ${host}:${HAWQ_MAPREDUCE_TOOL_TMPDIR} +done + +## +## Setup CLASSPATH +## + +# +# After source hadoop-config.sh, our CLASSPATH contains an old version of snappy-java +# which doesn't work on centos 5. To handle this, HAWQ_MAPREDUCE_TOOL_TMPDIR includes +# an updated version (snappy-java-1.1.0.jar). But we should put in front of the CLASSPATH +# to make it work. +# +CLASSPATH=${HAWQ_MAPREDUCE_TOOL_TMPDIR}/*:/usr/lib/gphd/hadoop-yarn/*:$CLASSPATH + +rm inputformat.logs +for test in ${FEATURE_TEST_LIST}; do + command="java -cp $CLASSPATH org.junit.runner.JUnitCore ${test}" + + echo "" + echo "======================================================================" + echo "Timestamp ..... : $(date)" + echo "Running test .. : ${test}" + echo "command ....... : ${command}" + echo "----------------------------------------------------------------------" + + ${command} | tee -a inputformat.logs 2>&1 + + echo "======================================================================" + echo "" +done + +## +## Generate reports parsed by pulse. +## +python generate_mr_report.py