#!/bin/sh
# @@@ START COPYRIGHT @@@
#
# 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.
#
# @@@ END COPYRIGHT @@@
#
##############################################################################

# This script installs a TPC-DS environment, to be used for Trafodion regression
# tests.

# invoke it with -h or --help to get help

# Before calling this script, the following environment variables need to be set up,
# or they need to be passed in through command line parameters:

# MY_LOCAL_SW_DIST (optional): Location for tar/zip files for this install
# MY_TPCDS_DATA_DIR          : Directory where to unpack the TPC-DS data generator
# MY_LOG_FILE                : Log file for error/tracing information
# MY_HDFS_CMD                : Command to be used for HDFS shell (e.g. "hdfs" or "swhdfs")
# MY_HIVE_CMD                : Command to be used for Hive (e.g. "hive" or "swhive")

TPCDS_MIRROR_URL=http://www.tpc.org/tpcds/dsgen/dsgen-download-files.asp?download_key=NaN
TPCDS_ZIP=tpcds_kit.zip

function usage {

cat <<EOF

Usage:

$0 [ options... ]

Options:

   --unpackDir=<dir1>   Directory where to unpack the zip files
   --dataDir=<dir2>     Directory where to store generated data files
   --logFile=<file2>    Log file for diagnostic messages
   --hdfsCmd=<cmd1>     HDFS command
   --hiveCmd=<cmd2>     Hive command
   --force              Force re-install
   --check              Check for show stoppers only and exit
   -h | --help          Display this help information

Set environment variable MY_LOCAL_SW_DIST to the directory of a local
copy of the TPC-DS zip file, if possible.

EOF
}

FORCE=""
CHECKONLY=""

while [ $# -gt 0 ];
do
  case "$1" in
    --unpackDir=*)
                     MY_TPCDS_UNPACK_DIR=${1#--unpackDir=}
                     ;;

    --dataDir=*)
                     MY_TPCDS_DATA_DIR=${1#--dataDir=}
                     ;;

    --logFile=*)
                     MY_LOG_FILE=${1#--logFile=}
                     ;;

    --hdfsCmd=*)
                     MY_HDFS_CMD=${1#--hdfsCmd=}
                     ;;

    --hiveCmd=*)
                     MY_HIVE_CMD=${1#--hiveCmd=}
                     ;;

    --force)
                     FORCE="true"
                     ;;

    --check)
                     CHECKONLY="true"
                     ;;

    -h)
                     usage
                     exit 1
                     ;;

    --help)
                     usage
                     exit 1
                     ;;

    **)
                     usage
                     echo "Unexpected argument: $1"
                     exit 100
                     ;;
  esac
  shift
done

#####################################################
# Check the last thing done by this script
# (or most recently added item)
if [[ "$FORCE" != "true" ]]
then
  $MY_HIVE_CMD -e 'describe ship_mode;' >/dev/null 2>&1
  if (( $? == 0 ))
  then
      #check if the files exist in hdfs 
      $MY_HDFS_CMD dfs  -ls /user/trafodion/hive/tpcds/ship_mode >/dev/null 2>&1
      if (( $? == 0 ))
	  then
	  echo "Hive table ship_mode found. Skipping TPC-DS set-up."
      fi
    exit 0
  fi
fi

#####################################################

if [ ! -f $MY_LOCAL_SW_DIST/${TPCDS_ZIP} ]; then
  # Right now there is no URL to download this tool automatically
  # Please download the tpcds_kit.zip file from"
  # "http://www.tpc.org/tpcds/dsgen-download-request.asp"
  # "and place it into a directory pointed to by the MY_LOCAL_SW_DIST"
  # "environment variable. Then retry installing. Sorry, this is due"
  # "to the TPC wanting your email address."
  echo "The testware tpcds_kit.zip does not exist and will not be installed"
  echo "This testware is needed to run developer HIVE regression tests"
  exit 99
fi

if [ -n "$CHECKONLY" ]; then
  echo "Requisite files exist, script should succeed if called."
  exit 0
fi

echo "Downloading TPC-DS setup tools..."

if [ -z "$MY_TPCDS_UNPACK_DIR" ]; then
  MY_TPCDS_UNPACK_DIR=.
  cd -P .
else
  mkdir -p $MY_TPCDS_UNPACK_DIR
  cd $MY_TPCDS_UNPACK_DIR
fi

if [ -f $MY_LOCAL_SW_DIST/${TPCDS_ZIP} ]; then
  cp $MY_LOCAL_SW_DIST/${TPCDS_ZIP} .
else
  # Right now there is no URL to download this tool automatically
  # curl --output ${TPCDS_ZIP} ${TPCDS_MIRROR_URL}
  exit 99
fi

  echo "Unzipping TPC-DS kit ${TPCDS_ZIP} into ${PWD} and making the tools..."
  unzip ${TPCDS_ZIP} >/dev/null

  if [ -d tools ]; then
    # older version of tpcds_kit
    cd tools
  else
    # more recent version
    cd TPCDSVersion*/tools
  fi

  echo "Making objects for TPC-DS database generator..." | tee -a ${MY_LOG_FILE}
  make clean >>${MY_LOG_FILE} 2>&1 
  make >>${MY_LOG_FILE} 2>&1 

  echo "Generating the data..." | tee -a ${MY_LOG_FILE}
  mkdir -p $MY_TPCDS_DATA_DIR
  SCALE=1
  FORCE=Y

  export PATH=.:$PATH

  ./dsdgen -force $FORCE -dir $MY_TPCDS_DATA_DIR -scale $SCALE -table store_sales >>${MY_LOG_FILE} 2>&1
  ./dsdgen -force $FORCE -dir $MY_TPCDS_DATA_DIR -scale $SCALE -table date_dim    >>${MY_LOG_FILE} 2>&1
  ./dsdgen -force $FORCE -dir $MY_TPCDS_DATA_DIR -scale $SCALE -table time_dim    >>${MY_LOG_FILE} 2>&1
  ./dsdgen -force $FORCE -dir $MY_TPCDS_DATA_DIR -scale $SCALE -table item        >>${MY_LOG_FILE} 2>&1
  ./dsdgen -force $FORCE -dir $MY_TPCDS_DATA_DIR -scale $SCALE -table customer    >>${MY_LOG_FILE} 2>&1
  ./dsdgen -force $FORCE -dir $MY_TPCDS_DATA_DIR -scale $SCALE -table customer_demographics  >>${MY_LOG_FILE} 2>&1
  ./dsdgen -force $FORCE -dir $MY_TPCDS_DATA_DIR -scale $SCALE -table household_demographics >>${MY_LOG_FILE} 2>&1
  ./dsdgen -force $FORCE -dir $MY_TPCDS_DATA_DIR -scale $SCALE -table customer_address       >>${MY_LOG_FILE} 2>&1
  ./dsdgen -force $FORCE -dir $MY_TPCDS_DATA_DIR -scale $SCALE -table store       >>${MY_LOG_FILE} 2>&1
  ./dsdgen -force $FORCE -dir $MY_TPCDS_DATA_DIR -scale $SCALE -table promotion   >>${MY_LOG_FILE} 2>&1

  ./dsdgen -force $FORCE -dir $MY_TPCDS_DATA_DIR -scale $SCALE -table catalog_sales >>${MY_LOG_FILE} 2>&1
  ./dsdgen -force $FORCE -dir $MY_TPCDS_DATA_DIR -scale $SCALE -table web_sales   >>${MY_LOG_FILE} 2>&1
  ./dsdgen -force $FORCE -dir $MY_TPCDS_DATA_DIR -scale $SCALE -table inventory   >>${MY_LOG_FILE} 2>&1
  ./dsdgen -force $FORCE -dir $MY_TPCDS_DATA_DIR -scale $SCALE -table call_center >>${MY_LOG_FILE} 2>&1
  ./dsdgen -force $FORCE -dir $MY_TPCDS_DATA_DIR -scale $SCALE -table catalog_page >>${MY_LOG_FILE} 2>&1
  ./dsdgen -force $FORCE -dir $MY_TPCDS_DATA_DIR -scale $SCALE -table web_site    >>${MY_LOG_FILE} 2>&1
  ./dsdgen -force $FORCE -dir $MY_TPCDS_DATA_DIR -scale $SCALE -table web_page    >>${MY_LOG_FILE} 2>&1
  ./dsdgen -force $FORCE -dir $MY_TPCDS_DATA_DIR -scale $SCALE -table warehouse   >>${MY_LOG_FILE} 2>&1
  ./dsdgen -force $FORCE -dir $MY_TPCDS_DATA_DIR -scale $SCALE -table income_band >>${MY_LOG_FILE} 2>&1
  ./dsdgen -force $FORCE -dir $MY_TPCDS_DATA_DIR -scale $SCALE -table reason      >>${MY_LOG_FILE} 2>&1
  ./dsdgen -force $FORCE -dir $MY_TPCDS_DATA_DIR -scale $SCALE -table ship_mode   >>${MY_LOG_FILE} 2>&1

  $MY_HDFS_CMD dfs -mkdir -p /user/trafodion/hive/tpcds                        >>${MY_LOG_FILE} 2>&1
  $MY_HDFS_CMD dfs -mkdir -p /user/trafodion/hive/tpcds/date_dim               >>${MY_LOG_FILE} 2>&1
  $MY_HDFS_CMD dfs -mkdir -p /user/trafodion/hive/tpcds/time_dim               >>${MY_LOG_FILE} 2>&1
  $MY_HDFS_CMD dfs -mkdir -p /user/trafodion/hive/tpcds/item                   >>${MY_LOG_FILE} 2>&1
  $MY_HDFS_CMD dfs -mkdir -p /user/trafodion/hive/tpcds/customer               >>${MY_LOG_FILE} 2>&1
  $MY_HDFS_CMD dfs -mkdir -p /user/trafodion/hive/tpcds/customer_demographics  >>${MY_LOG_FILE} 2>&1
  $MY_HDFS_CMD dfs -mkdir -p /user/trafodion/hive/tpcds/household_demographics >>${MY_LOG_FILE} 2>&1
  $MY_HDFS_CMD dfs -mkdir -p /user/trafodion/hive/tpcds/customer_address       >>${MY_LOG_FILE} 2>&1
  $MY_HDFS_CMD dfs -mkdir -p /user/trafodion/hive/tpcds/store                  >>${MY_LOG_FILE} 2>&1
  $MY_HDFS_CMD dfs -mkdir -p /user/trafodion/hive/tpcds/promotion              >>${MY_LOG_FILE} 2>&1
  $MY_HDFS_CMD dfs -mkdir -p /user/trafodion/hive/tpcds/store_sales            >>${MY_LOG_FILE} 2>&1
  $MY_HDFS_CMD dfs -mkdir -p /user/trafodion/hive/tpcds/ship_mode              >>${MY_LOG_FILE} 2>&1
  $MY_HDFS_CMD dfs -mkdir -p /user/trafodion/hive/tpcds/reason                 >>${MY_LOG_FILE} 2>&1
  $MY_HDFS_CMD dfs -mkdir -p /user/trafodion/hive/tpcds/income_band            >>${MY_LOG_FILE} 2>&1
  $MY_HDFS_CMD dfs -mkdir -p /user/trafodion/hive/tpcds/warehouse              >>${MY_LOG_FILE} 2>&1
  $MY_HDFS_CMD dfs -mkdir -p /user/trafodion/hive/tpcds/web_page               >>${MY_LOG_FILE} 2>&1
  $MY_HDFS_CMD dfs -mkdir -p /user/trafodion/hive/tpcds/web_site               >>${MY_LOG_FILE} 2>&1
  $MY_HDFS_CMD dfs -mkdir -p /user/trafodion/hive/tpcds/catalog_page           >>${MY_LOG_FILE} 2>&1
  $MY_HDFS_CMD dfs -mkdir -p /user/trafodion/hive/tpcds/call_center            >>${MY_LOG_FILE} 2>&1
  $MY_HDFS_CMD dfs -mkdir -p /user/trafodion/hive/tpcds/inventory              >>${MY_LOG_FILE} 2>&1
  $MY_HDFS_CMD dfs -mkdir -p /user/trafodion/hive/tpcds/web_returns            >>${MY_LOG_FILE} 2>&1
  $MY_HDFS_CMD dfs -mkdir -p /user/trafodion/hive/tpcds/web_sales              >>${MY_LOG_FILE} 2>&1
  $MY_HDFS_CMD dfs -mkdir -p /user/trafodion/hive/tpcds/catalog_returns        >>${MY_LOG_FILE} 2>&1
  $MY_HDFS_CMD dfs -mkdir -p /user/trafodion/hive/tpcds/catalog_sales          >>${MY_LOG_FILE} 2>&1
  $MY_HDFS_CMD dfs -mkdir -p /user/trafodion/hive/tpcds/store_returns          >>${MY_LOG_FILE} 2>&1

  cd $MY_TPCDS_DATA_DIR

  which iconv >>${MY_LOG_FILE} 2>&1
  if (( $? != 0 ))
  then
    echo "iconv utility not available. The data will be in ISO-8859-1 format."
  else
    echo "Converting the data into UTF-8 format ..."
    for t in date_dim time_dim item customer customer_demographics household_demographics customer_address store promotion store_sales store_returns catalog_sales catalog_returns web_sales web_returns inventory call_center catalog_page web_site web_page warehouse income_band reason reason
      do
        iconv -f ISO-8859-1 -t UTF-8 -o ${t}.utf8.dat ${t}.dat >>${MY_LOG_FILE} 2>&1
        mv ${t}.utf8.dat ${t}.dat
      done
  fi

  echo "Copying generated data to HDFS..."
  for t in date_dim time_dim item customer customer_demographics household_demographics customer_address store promotion store_sales store_returns catalog_sales catalog_returns web_sales web_returns inventory call_center catalog_page web_site web_page warehouse income_band reason reason
    do
      $MY_HDFS_CMD dfs -put ${t}.dat /user/trafodion/hive/tpcds/${t}        >>${MY_LOG_FILE} 2>&1
    done

  $MY_HDFS_CMD dfs -ls -R /user/trafodion/hive/tpcds/*/*.dat                >>${MY_LOG_FILE} 2>&1

  echo "Creating tables in Hive..." | tee -a ${MY_LOG_FILE}
  $MY_HIVE_CMD <<EOF >>${MY_LOG_FILE} 2>&1
-- store_sales star only

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         float,
    ss_list_price             float,
    ss_sales_price            float,
    ss_ext_discount_amt       float,
    ss_ext_sales_price        float,
    ss_ext_wholesale_cost     float,
    ss_ext_list_price         float,
    ss_ext_tax                float,
    ss_coupon_amt             float,
    ss_net_paid               float,
    ss_net_paid_inc_tax       float,
    ss_net_profit             float                  
)
row format delimited fields terminated by '|' 
location '/user/trafodion/hive/tpcds/store_sales';

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 '|' 
location '/user/trafodion/hive/tpcds/customer_demographics';

create external table date_dim
(
    d_date_sk                 int,
    d_date_id                 string,
    d_date                    timestamp,
    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 '|' 
location '/user/trafodion/hive/tpcds/date_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 '|' 
location '/user/trafodion/hive/tpcds/time_dim';

create external table item
(
    i_item_sk                 int,
    i_item_id                 string,
    i_rec_start_date          timestamp,
    i_rec_end_date            timestamp,
    i_item_desc               string,
    i_current_price           float,
    i_wholesale_cost          float,
    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 '|' 
location '/user/trafodion/hive/tpcds/item';

create external table store
(
    s_store_sk                int,
    s_store_id                string,
    s_rec_start_date          timestamp,
    s_rec_end_date            timestamp,
    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              float,
    s_tax_precentage          float                  
)
row format delimited fields terminated by '|' 
location '/user/trafodion/hive/tpcds/store';

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 '|' 
location '/user/trafodion/hive/tpcds/customer';

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                    float,
    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 '|' 
location '/user/trafodion/hive/tpcds/promotion';

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 '|' 
location '/user/trafodion/hive/tpcds/household_demographics';

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             float,
    ca_location_type          string
)
row format delimited fields terminated by '|' 
location '/user/trafodion/hive/tpcds/customer_address';

create table store_orc stored as orc as select * from store;

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               float,
    sr_return_tax               float,
    sr_return_amt_inc_tax       float,
    sr_fee                      float,
    sr_return_ship_cost         float,
    sr_refunded_cash            float,
    sr_reversed_charge          float,
    sr_store_credit             float,
    sr_net_loss                 float
)
row format delimited fields terminated by '|'
location '/user/trafodion/hive/tpcds/store_returns';


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       float,
    cs_list_price           float,
    cs_sales_price          float,
    cs_ext_discount_amt     float,
    cs_ext_sales_price      float,
    cs_ext_wholesale_cost   float,
    cs_ext_list_price       float,
    cs_ext_tax              float,
    cs_coupon_amt           float,
    cs_ext_ship_cost        float,
    cs_net_paid             float,
    cs_net_paid_inc_tax     float,
    cs_net_paid_inc_ship    float,
    cs_net_paid_inc_ship_tax    float,
    cs_net_profit           float
)
row format delimited fields terminated by '|'
location '/user/trafodion/hive/tpcds/catalog_sales';


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            float,
    cr_return_tax               float,
    cr_return_amt_inc_tax       float,
    cr_fee                      float,
    cr_return_ship_cost         float,
    cr_refunded_cash            float,
    cr_reversed_charge          float,
    cr_store_credit             float,
    cr_net_loss                 float
)
row format delimited fields terminated by '|'
location '/user/trafodion/hive/tpcds/catalog_returns';

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           float,
    ws_list_price               float,
    ws_sales_price              float,
    ws_ext_discount_amt         float,
    ws_ext_sales_price          float,
    ws_ext_wholesale_cost       float,
    ws_ext_list_price           float,
    ws_ext_tax                  float,
    ws_coupon_amt               float,
    ws_ext_ship_cost            float,
    ws_net_paid                 float,
    ws_net_paid_inc_tax         float,
    ws_net_paid_inc_ship        float,
    ws_net_paid_inc_ship_tax    float,
    ws_net_profit               float
)
row format delimited fields terminated by '|'
location '/user/trafodion/hive/tpcds/web_sales';

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               float,
    wr_return_tax               float,
    wr_return_amt_inc_tax       float,
    wr_fee                      float,
    wr_return_ship_cost         float,
    wr_refunded_cash            float,
    wr_reversed_charge          float,
    wr_account_credit           float,
    wr_net_loss                 float
)
row format delimited fields terminated by '|'
location '/user/trafodion/hive/tpcds/web_returns';

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 '|'
location '/user/trafodion/hive/tpcds/inventory';

create external table call_center
(
    cc_call_center_sk       int,
    cc_call_center_id       string,
    cc_rec_start_date       timestamp,
    cc_rec_end_date         timestamp,
    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           float,
    cc_tax_percentage       float
)
row format delimited fields terminated by '|'
location '/user/trafodion/hive/tpcds/call_center';

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 '|'
location '/user/trafodion/hive/tpcds/catalog_page';

create external table web_site
(
    web_site_sk             int ,
    web_site_id             string ,
    web_rec_start_date      timestamp,
    web_rec_end_date        timestamp,
    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          float,
    web_tax_percentage      float
)
row format delimited fields terminated by '|'
location '/user/trafodion/hive/tpcds/web_site';

create external table Web_page
(
    wp_web_page_sk          int,
    wp_web_page_id          string,
    wp_rec_start_date       timestamp,
    wp_rec_end_date         timestamp,
    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 '|'
location '/user/trafodion/hive/tpcds/web_page';

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            float
)
row format delimited fields terminated by '|'
location '/user/trafodion/hive/tpcds/warehouse';

create external table income_band
(
    ib_income_band_sk       int,
    ib_lower_bound          int,
    ib_upper_bound          int
)
row format delimited fields terminated by '|'
location '/user/trafodion/hive/tpcds/income_band';

create external table reason
(
    r_reason_sk             int,
    r_reason_id             string,
    r_reason_desc           string
)
row format delimited fields terminated by '|'
location '/user/trafodion/hive/tpcds/reason';

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 '|'
location '/user/trafodion/hive/tpcds/ship_mode';

quit;

EOF

