>>obey TEST009(set_up);
>>prepare get_hv_schemas from
+>select schema_name from "_MD_".objects
+>where (schema_name = '_HV_HIVE_' or schema_name = '_HV_SCH_T009_')
+>       and object_type = 'PS';

--- SQL command prepared.
>>
>>prepare get_hb_schemas from
+>select schema_name from "_MD_".objects
+>where schema_name like '\_HB\_\_%' escape '\' and object_type = 'PS';

--- SQL command prepared.
>>
>>obey TEST009(create_db);
>>
>>-- The version of hive installed does not support special characters
>>-- TDB - when hive is upgraded to a new version, add tests where
>>--   the hive schema contains special characters
>>sh regrhive.ksh -v -f $REGRTSTDIR/TEST009_a.hive.sql &> $REGRRUNDIR/LOG009_a.hive.log;
>>sh regrhbase.ksh $REGRTSTDIR/TEST009_create_hbase_objects.hbase &> $REGRRUNDIR/LOG009_create_hbase_tables.log ;
>>
>>-- make sure external schemas exist in Trafodion
>>execute get_hv_schemas;

--- 0 row(s) selected.
>>execute get_hb_schemas;

--- 0 row(s) selected.
>>
>>obey TEST009(test_hive1);
>>-- create external tables for precreated tables
>>create external table customer for hive.hive.customer;

--- SQL operation complete.
>>create external table item for hive.hive.item;

--- SQL operation complete.
>>create external table promotion for hive.hive.promotion;

--- SQL operation complete.
>>
>>-- should see a schema called "_HV_HIVE_"
>>execute get_hv_schemas;

SCHEMA_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

_HV_HIVE_                                                                                                                                                                                                                                                       

--- 1 row(s) selected.
>>
>>-- Verify tables were created
>>showddl trafodion."_HV_HIVE_".customer;

CREATE EXTERNAL TABLE CUSTOMER
  FOR HIVE.HIVE.CUSTOMER
;

--- SQL operation complete.
>>showddl trafodion."_HV_HIVE_".item;

CREATE EXTERNAL TABLE ITEM
  FOR HIVE.HIVE.ITEM
;

--- SQL operation complete.
>>showddl trafodion."_HV_HIVE_".promotion;

CREATE EXTERNAL TABLE PROMOTION
  FOR HIVE.HIVE.PROMOTION
;

--- SQL operation complete.
>>
>>-- create external tables from hive schema sch_t009
>>create external table t009t1 for hive.sch_t009.t009t1;

--- SQL operation complete.
>>create external table t009t2 for hive.sch_t009.t009t2;

--- SQL operation complete.
>>
>>execute get_hv_schemas;

SCHEMA_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

_HV_HIVE_                                                                                                                                                                                                                                                       
_HV_SCH_T009_                                                                                                                                                                                                                                                   

--- 2 row(s) selected.
>>showddl trafodion."_HV_SCH_T009_".t009t1;

CREATE EXTERNAL TABLE T009T1
  FOR HIVE.SCH_T009.T009T1
;

--- SQL operation complete.
>>showddl trafodion."_HV_SCH_T009_".t009t2;

CREATE EXTERNAL TABLE T009T2
  FOR HIVE.SCH_T009.T009T2
;

--- SQL operation complete.
>>
>>select count(*) from hive.hive.customer;

(EXPR)              
--------------------

              100000

--- 1 row(s) selected.
>>select count(*) from hive.hive.item;

(EXPR)              
--------------------

               18000

--- 1 row(s) selected.
>>select count(*) from hive.hive.promotion;

(EXPR)              
--------------------

                 300

--- 1 row(s) selected.
>>
>>select * from hive.sch_t009.t009t1;

A            B            C          
-----------  -----------  -----------

         10           15           10
          9           26           10
          8           26           12
          7           24            4
          6            4           12
          5            8            5
          4            7            6
          3           18            9
          2            9            4
          1            9           12

--- 10 row(s) selected.
>>select * from hive.sch_t009.t009t2;

A            B            C          
-----------  -----------  -----------

         10           15           10
          9           26           10
          8           26           12
          7           24            4
          6            4           12
          5            8            5
          4            7            6
          3           18            9
          2            9            4
          1            9           12

--- 10 row(s) selected.
>>
>>-- fails since accesses to the external name are prohibited
>>select * from trafodion."_HV_SCH_T009_".t009t1;

*** ERROR[4258] Trying to access external table TRAFODION."_HV_SCH_T009_".T009T1 through its external name format. Please use the native table name.

*** ERROR[8822] The statement was not prepared.

>>
>>set schema "_HV_HIVE_";

--- SQL operation complete.
>>
>>-- all these creates should fail, they are not supported yet
>>create table hive_customer like hive.hive.customer;

*** ERROR[1118] Creating object TRAFODION."_HV_HIVE_".HIVE_CUSTOMER is not allowed in a reserved system schema.

--- SQL operation failed with errors.
>>create table newtable1 like hive.hive.customer;

*** ERROR[1118] Creating object TRAFODION."_HV_HIVE_".NEWTABLE1 is not allowed in a reserved system schema.

--- SQL operation failed with errors.
>>create table newtable2 like customer;

*** ERROR[1118] Creating object TRAFODION."_HV_HIVE_".NEWTABLE2 is not allowed in a reserved system schema.

--- SQL operation failed with errors.
>>create table newtable3 (a int);

*** ERROR[1118] Creating object TRAFODION."_HV_HIVE_".NEWTABLE3 is not allowed in a reserved system schema.

--- SQL operation failed with errors.
>>
>>-- test creates with a different default schema
>>create schema hive_t009;

--- SQL operation complete.
>>set schema hive_t009;

--- SQL operation complete.
>>
>>-- create like on hive tables/views
>>create table t009hivecust1 like hive.hive.customer;

--- SQL operation complete.
>>invoke t009hivecust1;

-- Definition of Trafodion table TRAFODION.HIVE_T009.T009HIVECUST1
-- Definition current  Wed Jul 12 21:37:45 2017

  (
    SYSKEY                           LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
  , C_CUSTOMER_SK                    INT DEFAULT NULL
  , C_CUSTOMER_ID                    VARCHAR(20 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL
  , C_CURRENT_CDEMO_SK               INT DEFAULT NULL
  , C_CURRENT_HDEMO_SK               INT DEFAULT NULL
  , C_CURRENT_ADDR_SK                INT DEFAULT NULL
  , C_FIRST_SHIPTO_DATE_SK           INT DEFAULT NULL
  , C_FIRST_SALES_DATE_SK            INT DEFAULT NULL
  , C_SALUTATION                     VARCHAR(20 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL
  , C_FIRST_NAME                     VARCHAR(20 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL
  , C_LAST_NAME                      VARCHAR(20 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL
  , C_PREFERRED_CUST_FLAG            VARCHAR(20 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL
  , C_BIRTH_DAY                      INT DEFAULT NULL
  , C_BIRTH_MONTH                    INT DEFAULT NULL
  , C_BIRTH_YEAR                     INT DEFAULT NULL
  , C_BIRTH_COUNTRY                  VARCHAR(20 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL
  , C_LOGIN                          VARCHAR(20 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL
  , C_EMAIL_ADDRESS                  VARCHAR(20 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL
  , C_LAST_REVIEW_DATE               VARCHAR(20 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL
  )

--- SQL operation complete.
>>create table newtable1 like hive.hive.customer;

--- SQL operation complete.
>>process hive statement 'drop view t009cust';

--- SQL operation complete.
>>process hive statement 'create view t009cust as select * from customer';

--- SQL operation complete.
>>create table t009hivecust2 like hive.hive.t009cust;

--- SQL operation complete.
>>invoke t009hivecust2;

-- Definition of Trafodion table TRAFODION.HIVE_T009.T009HIVECUST2
-- Definition current  Wed Jul 12 21:37:55 2017

  (
    SYSKEY                           LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
  , C_CUSTOMER_SK                    INT DEFAULT NULL
  , C_CUSTOMER_ID                    VARCHAR(20 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL
  , C_CURRENT_CDEMO_SK               INT DEFAULT NULL
  , C_CURRENT_HDEMO_SK               INT DEFAULT NULL
  , C_CURRENT_ADDR_SK                INT DEFAULT NULL
  , C_FIRST_SHIPTO_DATE_SK           INT DEFAULT NULL
  , C_FIRST_SALES_DATE_SK            INT DEFAULT NULL
  , C_SALUTATION                     VARCHAR(20 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL
  , C_FIRST_NAME                     VARCHAR(20 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL
  , C_LAST_NAME                      VARCHAR(20 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL
  , C_PREFERRED_CUST_FLAG            VARCHAR(20 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL
  , C_BIRTH_DAY                      INT DEFAULT NULL
  , C_BIRTH_MONTH                    INT DEFAULT NULL
  , C_BIRTH_YEAR                     INT DEFAULT NULL
  , C_BIRTH_COUNTRY                  VARCHAR(20 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL
  , C_LOGIN                          VARCHAR(20 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL
  , C_EMAIL_ADDRESS                  VARCHAR(20 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL
  , C_LAST_REVIEW_DATE               VARCHAR(20 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL
  )

--- SQL operation complete.
>>
>>-- these creates fail
>>create external table seabase.customer like hive.hive.customer;

*** ERROR[3242] This statement is not supported. Reason: 'like' clause cannot be specified when creating an external table.

*** ERROR[8822] The statement was not prepared.

>>create external table customer1 like hive.hive.customer;

*** ERROR[3242] This statement is not supported. Reason: 'like' clause cannot be specified when creating an external table.

*** ERROR[8822] The statement was not prepared.

>>create table t009t2 as select * from "_HV_SCH_T009_".t009t2;

*** ERROR[4258] Trying to access external table TRAFODION."_HV_SCH_T009_".T009T2 through its external name format. Please use the native table name.

*** ERROR[8822] The statement was not prepared.

>>
>>-- this create succeeds
>>create table t009t1 like "_HV_SCH_T009_".t009t1;

--- SQL operation complete.
>>drop table t009t1;

--- SQL operation complete.
>>create table t009t1 as select * from hive.sch_t009.t009t1;

--- 10 row(s) inserted.
>>
>>drop table t009t1;

--- SQL operation complete.
>>
>>drop external table "_HV_HIVE_".customer;

--- SQL operation complete.
>>drop external table item for hive.hive.item;

--- SQL operation complete.
>>
>>obey TEST009(test_hive2);
>>-- drop data from the hive table and recreate with 4 columns
>>-- this causes the external table to be invalid
>>
>>-- cleanup data from the old table, and create/load data with additional column
>>sh regrhadoop.ksh fs -rm   /user/trafodion/hive/exttables/t009t1/*;
>>sh regrhive.ksh -v -f $REGRTSTDIR/TEST009_b.hive.sql &> $REGRRUNDIR/LOG009_b.hive.log;
>>
>>-- should fail - column mismatch
>>select count(*) from hive.sch_t009.t009t1;

*** ERROR[8437] Mismatch detected between external table and underlying hive table definitions.

*** ERROR[8822] The statement was not prepared.

>>sh sqlci -i "TEST009(select_stmts)";
>>select * from hive.sch_t009.t009t1;

*** ERROR[8437] Mismatch detected between external table and underlying hive table definitions.

*** ERROR[8822] The statement was not prepared.

>>select * from hive.sch_t009.t009t2;

A            B            C          
-----------  -----------  -----------

         10           15           10
          9           26           10
          8           26           12
          7           24            4
          6            4           12
          5            8            5
          4            7            6
          3           18            9
          2            9            4
          1            9           12

--- 10 row(s) selected.
>>
>>exit;

End of MXCI Session

>>
>>-- drop and recreate external table
>>select catalog_name, schema_name, object_name from table(natablecacheentries('user','local')) order by 1,2,3;

CATALOG_NAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      SCHEMA_NAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

HIVE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              HIVE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              CUSTOMER                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
HIVE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              HIVE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              PROMOTION                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
HIVE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              SCH_T009                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          T009T2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
TRAFODION                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         HIVE_T009                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         T009HIVECUST1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
TRAFODION                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         HIVE_T009                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         T009HIVECUST2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
TRAFODION                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         _HV_HIVE_                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         PROMOTION                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
TRAFODION                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         _HV_SCH_T009_                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     T009T2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
TRAFODION                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         _MD_                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              OBJECTS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         

--- 8 row(s) selected.
>>drop external table t009t1 for hive.sch_t009.t009t1;

--- SQL operation complete.
>>select catalog_name, schema_name, object_name from table(natablecacheentries('user','local')) order by 1,2,3;

CATALOG_NAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      SCHEMA_NAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

HIVE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              HIVE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              CUSTOMER                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
HIVE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              HIVE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              PROMOTION                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
HIVE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              SCH_T009                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          T009T2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
TRAFODION                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         HIVE_T009                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         T009HIVECUST1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
TRAFODION                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         HIVE_T009                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         T009HIVECUST2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
TRAFODION                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         _HV_HIVE_                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         PROMOTION                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
TRAFODION                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         _HV_SCH_T009_                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     T009T2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
TRAFODION                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         _MD_                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              OBJECTS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         

--- 8 row(s) selected.
>>create external table t009t1 for hive.sch_t009.t009t1;

--- SQL operation complete.
>>get tables in schema "_HV_SCH_T009_";

Tables in Schema TRAFODION._HV_SCH_T009_
========================================

T009T1
T009T2

--- SQL operation complete.
>>invoke hive.sch_t009.t009t1;

-- Definition of hive table T009T1
-- Definition current  Wed Jul 12 21:39:07 2017

  (
    A                                INT
  , B                                INT
  , C                                INT
  , D                                INT
  )
  /* stored as textfile */

--- SQL operation complete.
>>showddl trafodion."_HV_SCH_T009_".t009t1;

CREATE EXTERNAL TABLE T009T1
  FOR HIVE.SCH_T009.T009T1
;

--- SQL operation complete.
>>
>>-- select should now succeed
>>select count(*) from hive.sch_t009.t009t1;

(EXPR)              
--------------------

                  10

--- 1 row(s) selected.
>>select * from hive.sch_t009.t009t1;

A            B            C            D          
-----------  -----------  -----------  -----------

         10           15           10         1973
          9           26           10         1966
          8           26           12         1938
          7           24            4         1985
          6            4           12         1925
          5            8            5         1956
          4            7            6         1983
          3           18            9         1979
          2            9            4         1966
          1            9           12         1936

--- 10 row(s) selected.
>>
>>obey TEST009(test_hbase);
>>select count(*) from hbase."_ROW_"."baseball";

(EXPR)              
--------------------

                   3

--- 1 row(s) selected.
>>select count(*) from hbase."_CELL_"."baseball";

(EXPR)              
--------------------

                  27

--- 1 row(s) selected.
>>
>>select left(row_id, 10) as row_id, left(column_display(column_details, ('teams:team_number', 'games:visitor_team', 'games:game_time')), 100) as cols from hbase."_ROW_"."baseball";

ROW_ID      COLS
----------  ----------------------------------------------------------------------------------------------------

1           games:game_time =>  2015-10-23 19:30:30 , games:visitor_team =>  2 , teams:team_number =>  1        
2           games:game_time =>  2015-10-24 19:30:30 , games:visitor_team =>  3 , teams:team_number =>  2        
3           games:game_time =>  2015-10-25 19:30:30 , games:visitor_team =>  4 , teams:team_number =>  3        

--- 3 row(s) selected.
>>
>>-- create like on native hbase tables
>>create table bblike1 like hbase."_ROW_"."baseball";

--- SQL operation complete.
>>invoke bblike1;

-- Definition of Trafodion table TRAFODION.HIVE_T009.BBLIKE1
-- Definition current  Wed Jul 12 21:39:20 2017

  (
    ROW_ID                           VARCHAR(100) CHARACTER SET ISO88591
      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , COLUMN_DETAILS                   VARCHAR(10000) CHARACTER SET ISO88591
      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  )
  PRIMARY KEY (ROW_ID ASC)

--- SQL operation complete.
>>create table bblike2 like hbase."_CELL_"."baseball";

--- SQL operation complete.
>>invoke bblike2;

-- Definition of Trafodion table TRAFODION.HIVE_T009.BBLIKE2
-- Definition current  Wed Jul 12 21:39:25 2017

  (
    ROW_ID                           VARCHAR(100) CHARACTER SET ISO88591
      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , COL_FAMILY                       VARCHAR(100) CHARACTER SET ISO88591
      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , COL_NAME                         VARCHAR(100) CHARACTER SET ISO88591
      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , COL_TIMESTAMP                    LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
  , COL_VALUE                        VARCHAR(1000) CHARACTER SET ISO88591
      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  )
  PRIMARY KEY (ROW_ID ASC)

--- SQL operation complete.
>>
>>-- error. Cannot create a non-hbase-mapped table
>>create external table "baseball" for hbase."_ROW_"."baseball";

*** ERROR[3242] This statement is not supported. Reason: Cannot create external table on a native HBase table without the MAP TO option.

--- SQL operation failed with errors.
>>
>>obey TEST009(test_ext);
>>-- test external table attributes
>>set schema trafodion.sch;

--- SQL operation complete.
>>drop external table if exists store_sales for hive.hive.store_sales;

--- SQL operation complete.
>>create external table store_sales 
+>  for hive.hive.store_sales;

--- SQL operation complete.
>>invoke hive.hive.store_sales;

-- Definition of hive table STORE_SALES
-- Definition current  Wed Jul 12 21:39:32 2017

  (
    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                REAL
  , SS_LIST_PRICE                    REAL
  , SS_SALES_PRICE                   REAL
  , SS_EXT_DISCOUNT_AMT              REAL
  , SS_EXT_SALES_PRICE               REAL
  , SS_EXT_WHOLESALE_COST            REAL
  , SS_EXT_LIST_PRICE                REAL
  , SS_EXT_TAX                       REAL
  , SS_COUPON_AMT                    REAL
  , SS_NET_PAID                      REAL
  , SS_NET_PAID_INC_TAX              REAL
  , SS_NET_PROFIT                    REAL
  )
  /* stored as textfile */

--- SQL operation complete.
>>
>>set schema hive.hive;

--- SQL operation complete.
>>prepare s from select * from store_sales where ss_item_sk = 1;

*** WARNING[6008] Statistics for column (SS_ITEM_SK) from table HIVE.HIVE.STORE_SALES were not available. As a result, the access path chosen might not be the best possible.

--- SQL command prepared.
>>explain options 'c' s;

------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME ............ ###
STATEMENT_NAME ........... S
PLAN_ID ................ ###
ROWS_OUT ............... ###
EST_TOTAL_COST ......... ###
STATEMENT ................ select * from store_sales where ss_item_sk = 1;


------------------------------------------------------------------ NODE LISTING
ROOT ======================================  SEQ_NO 2        ONLY CHILD 1
REQUESTS_IN ............ ###
ROWS_OUT ............... ###
EST_OPER_COST .......... ###
EST_TOTAL_COST ......... ###
DESCRIPTION
  max_card_est ......... ###
  fragment_id ............ 0
  parent_frag ............ (none)
  fragment_type .......... master
  statement_index ...... ###
  affinity_value ....... ###
  max_max_cardinality    ###
  total_overflow_size    ###
  xn_access_mode ......... read_only
  xn_autoabort_interval  ###
  auto_query_retry ....... enabled
  plan_version ......... ###
  embedded_arkcmp ........ used
  IS_SQLCI ............... ON
  LDAP_USERNAME
  HIVE_MAX_STRING_LENGTH  20
  SHOWCONTROL_SHOW_ALL ... OFF
  SCHEMA ................. HIVE.HIVE
  select_list ............ HIVE.STORE_SALES.SS_SOLD_DATE_SK,
                             HIVE.STORE_SALES.SS_SOLD_TIME_SK, %(1),
                             HIVE.STORE_SALES.SS_CUSTOMER_SK,
                             HIVE.STORE_SALES.SS_CDEMO_SK,
                             HIVE.STORE_SALES.SS_HDEMO_SK,
                             HIVE.STORE_SALES.SS_ADDR_SK,
                             HIVE.STORE_SALES.SS_STORE_SK,
                             HIVE.STORE_SALES.SS_PROMO_SK,
                             HIVE.STORE_SALES.SS_TICKET_NUMBER,
                             HIVE.STORE_SALES.SS_QUANTITY,
                             HIVE.STORE_SALES.SS_WHOLESALE_COST,
                             HIVE.STORE_SALES.SS_LIST_PRICE,
                             HIVE.STORE_SALES.SS_SALES_PRICE,
                             HIVE.STORE_SALES.SS_EXT_DISCOUNT_AMT,
                             HIVE.STORE_SALES.SS_EXT_SALES_PRICE,
                             HIVE.STORE_SALES.SS_EXT_WHOLESALE_COST,
                             HIVE.STORE_SALES.SS_EXT_LIST_PRICE,
                             HIVE.STORE_SALES.SS_EXT_TAX,
                             HIVE.STORE_SALES.SS_COUPON_AMT,
                             HIVE.STORE_SALES.SS_NET_PAID,
                             HIVE.STORE_SALES.SS_NET_PAID_INC_TAX,
                             HIVE.STORE_SALES.SS_NET_PROFIT
  input_variables ........ %(1)


HIVE_SCAN =================================  SEQ_NO 1        NO CHILDREN
TABLE_NAME ............... HIVE.HIVE.STORE_SALES
REQUESTS_IN ............ ###
ROWS_OUT ............... ###
EST_OPER_COST .......... ###
EST_TOTAL_COST ......... ###
DESCRIPTION
  max_card_est ......... ###
  fragment_id ............ 0
  parent_frag ............ (none)
  fragment_type .......... master
  scan_type .............. subset scan of table HIVE.HIVE.STORE_SALES
  object_type ............ Hive_Text
  scan_direction ......... forward
  lock_mode .............. not specified, defaulted to lock cursor
  access_mode ............ not specified, defaulted to read committed
  columns_retrieved ..... 23
  key_columns ............ HIVE.STORE_SALES.SS_SOLD_DATE_SK,
                             HIVE.STORE_SALES.SS_SOLD_TIME_SK,
                             HIVE.STORE_SALES.SS_ITEM_SK,
                             HIVE.STORE_SALES.SS_CUSTOMER_SK,
                             HIVE.STORE_SALES.SS_CDEMO_SK,
                             HIVE.STORE_SALES.SS_HDEMO_SK,
                             HIVE.STORE_SALES.SS_ADDR_SK,
                             HIVE.STORE_SALES.SS_STORE_SK,
                             HIVE.STORE_SALES.SS_PROMO_SK,
                             HIVE.STORE_SALES.SS_TICKET_NUMBER,
                             HIVE.STORE_SALES.SS_QUANTITY,
                             HIVE.STORE_SALES.SS_WHOLESALE_COST,
                             HIVE.STORE_SALES.SS_LIST_PRICE,
                             HIVE.STORE_SALES.SS_SALES_PRICE,
                             HIVE.STORE_SALES.SS_EXT_DISCOUNT_AMT,
                             HIVE.STORE_SALES.SS_EXT_SALES_PRICE,
                             HIVE.STORE_SALES.SS_EXT_WHOLESALE_COST,
                             HIVE.STORE_SALES.SS_EXT_LIST_PRICE,
                             HIVE.STORE_SALES.SS_EXT_TAX,
                             HIVE.STORE_SALES.SS_COUPON_AMT,
                             HIVE.STORE_SALES.SS_NET_PAID,
                             HIVE.STORE_SALES.SS_NET_PAID_INC_TAX,
                             HIVE.STORE_SALES.SS_NET_PROFIT
  executor_predicates .... (HIVE.STORE_SALES.SS_ITEM_SK = %(1))

--- SQL operation complete.
>>
>>-- join with nested join
>>control query shape nested_join(scan(path 'CUSTOMER'), 
+>      scan(path 'STORE_SALES'));

--- SQL operation complete.
>>prepare s from select * from customer, store_sales 
+>    where store_sales.ss_item_sk = customer.c_customer_sk;

*** WARNING[6008] Statistics for column (C_CUSTOMER_SK) from table HIVE.HIVE.CUSTOMER were not available. As a result, the access path chosen might not be the best possible.

*** WARNING[6008] Statistics for column (SS_ITEM_SK) from table HIVE.HIVE.STORE_SALES were not available. As a result, the access path chosen might not be the best possible.

--- SQL command prepared.
>>explain options 'fc' s;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

3    .    4    root                                                  1.00E+002
1    2    3    nested_join                                           1.00E+002
.    .    2    hive_scan                       STORE_SALES           1.00E+002
.    .    1    hive_scan                       CUSTOMER              1.00E+002

--- SQL operation complete.
>>explain options 'p' s;

------------------------------------------------------------------ PLAN SUMMARY
STATEMENT_NAME ........... S
STATEMENT ................ select *
                           from customer, store_sales
                           where store_sales.ss_item_sk =
                             customer.c_customer_sk;
MUST_MATCH ............... forced nested join(forced scan, forced scan)


------------------------------------------------------------------ NODE LISTING
ROOT ======================================  SEQ_NO 4        ONLY CHILD 3
DESCRIPTION
  fragment_id ............ 0
  parent_frag ............ (none)
  fragment_type .......... master
  xn_access_mode ......... read_only
  auto_query_retry ....... enabled
  embedded_arkcmp ........ used
  IS_SQLCI ............... ON
  LDAP_USERNAME
  HIVE_MAX_STRING_LENGTH  20
  SHOWCONTROL_SHOW_ALL ... OFF
  SCHEMA ................. HIVE.HIVE
  select_list ............ HIVE.CUSTOMER.C_CUSTOMER_SK,
                             HIVE.CUSTOMER.C_CUSTOMER_ID,
                             HIVE.CUSTOMER.C_CURRENT_CDEMO_SK,
                             HIVE.CUSTOMER.C_CURRENT_HDEMO_SK,
                             HIVE.CUSTOMER.C_CURRENT_ADDR_SK,
                             HIVE.CUSTOMER.C_FIRST_SHIPTO_DATE_SK,
                             HIVE.CUSTOMER.C_FIRST_SALES_DATE_SK,
                             HIVE.CUSTOMER.C_SALUTATION,
                             HIVE.CUSTOMER.C_FIRST_NAME,
                             HIVE.CUSTOMER.C_LAST_NAME,
                             HIVE.CUSTOMER.C_PREFERRED_CUST_FLAG,
                             HIVE.CUSTOMER.C_BIRTH_DAY,
                             HIVE.CUSTOMER.C_BIRTH_MONTH,
                             HIVE.CUSTOMER.C_BIRTH_YEAR,
                             HIVE.CUSTOMER.C_BIRTH_COUNTRY,
                             HIVE.CUSTOMER.C_LOGIN, HIVE.CUSTOMER.C_EMAIL_ADDRE
                             SS, HIVE.CUSTOMER.C_LAST_REVIEW_DATE,
                             HIVE.STORE_SALES.SS_SOLD_DATE_SK,
                             HIVE.STORE_SALES.SS_SOLD_TIME_SK,
                             HIVE.CUSTOMER.C_CUSTOMER_SK,
                             HIVE.STORE_SALES.SS_CUSTOMER_SK,
                             HIVE.STORE_SALES.SS_CDEMO_SK,
                             HIVE.STORE_SALES.SS_HDEMO_SK,
                             HIVE.STORE_SALES.SS_ADDR_SK,
                             HIVE.STORE_SALES.SS_STORE_SK,
                             HIVE.STORE_SALES.SS_PROMO_SK,
                             HIVE.STORE_SALES.SS_TICKET_NUMBER,
                             HIVE.STORE_SALES.SS_QUANTITY,
                             HIVE.STORE_SALES.SS_WHOLESALE_COST,
                             HIVE.STORE_SALES.SS_LIST_PRICE,
                             HIVE.STORE_SALES.SS_SALES_PRICE,
                             HIVE.STORE_SALES.SS_EXT_DISCOUNT_AMT,
                             HIVE.STORE_SALES.SS_EXT_SALES_PRICE,
                             HIVE.STORE_SALES.SS_EXT_WHOLESALE_COST,
                             HIVE.STORE_SALES.SS_EXT_LIST_PRICE,
                             HIVE.STORE_SALES.SS_EXT_TAX,
                             HIVE.STORE_SALES.SS_COUPON_AMT,
                             HIVE.STORE_SALES.SS_NET_PAID,
                             HIVE.STORE_SALES.SS_NET_PAID_INC_TAX,
                             HIVE.STORE_SALES.SS_NET_PROFIT


NESTED_JOIN ===============================  SEQ_NO 3        CHILDREN 1, 2
DESCRIPTION
  fragment_id ............ 0
  parent_frag ............ (none)
  fragment_type .......... master
  join_type .............. inner
  join_method ............ nested


HIVE_SCAN =================================  SEQ_NO 2        NO CHILDREN
TABLE_NAME ............... HIVE.HIVE.STORE_SALES
DESCRIPTION
  fragment_id ............ 0
  parent_frag ............ (none)
  fragment_type .......... master
  scan_type .............. subset scan of table HIVE.HIVE.STORE_SALES
  object_type ............ Hive_Text
  scan_direction ......... forward
  lock_mode .............. not specified, defaulted to lock cursor
  access_mode ............ not specified, defaulted to read committed
  columns_retrieved ..... 23
  key_columns ............ HIVE.STORE_SALES.SS_SOLD_DATE_SK,
                             HIVE.STORE_SALES.SS_SOLD_TIME_SK,
                             HIVE.STORE_SALES.SS_ITEM_SK,
                             HIVE.STORE_SALES.SS_CUSTOMER_SK,
                             HIVE.STORE_SALES.SS_CDEMO_SK,
                             HIVE.STORE_SALES.SS_HDEMO_SK,
                             HIVE.STORE_SALES.SS_ADDR_SK,
                             HIVE.STORE_SALES.SS_STORE_SK,
                             HIVE.STORE_SALES.SS_PROMO_SK,
                             HIVE.STORE_SALES.SS_TICKET_NUMBER,
                             HIVE.STORE_SALES.SS_QUANTITY,
                             HIVE.STORE_SALES.SS_WHOLESALE_COST,
                             HIVE.STORE_SALES.SS_LIST_PRICE,
                             HIVE.STORE_SALES.SS_SALES_PRICE,
                             HIVE.STORE_SALES.SS_EXT_DISCOUNT_AMT,
                             HIVE.STORE_SALES.SS_EXT_SALES_PRICE,
                             HIVE.STORE_SALES.SS_EXT_WHOLESALE_COST,
                             HIVE.STORE_SALES.SS_EXT_LIST_PRICE,
                             HIVE.STORE_SALES.SS_EXT_TAX,
                             HIVE.STORE_SALES.SS_COUPON_AMT,
                             HIVE.STORE_SALES.SS_NET_PAID,
                             HIVE.STORE_SALES.SS_NET_PAID_INC_TAX,
                             HIVE.STORE_SALES.SS_NET_PROFIT
  executor_predicates .... (HIVE.STORE_SALES.SS_ITEM_SK =
                             HIVE.CUSTOMER.C_CUSTOMER_SK)


HIVE_SCAN =================================  SEQ_NO 1        NO CHILDREN
TABLE_NAME ............... HIVE.HIVE.CUSTOMER
DESCRIPTION
  fragment_id ............ 0
  parent_frag ............ (none)
  fragment_type .......... master
  scan_type .............. subset scan of table HIVE.HIVE.CUSTOMER
  object_type ............ Hive_Text
  scan_direction ......... forward
  lock_mode .............. not specified, defaulted to lock cursor
  access_mode ............ not specified, defaulted to read committed
  columns_retrieved ..... 18
  key_columns ............ HIVE.CUSTOMER.C_CUSTOMER_SK,
                             HIVE.CUSTOMER.C_CUSTOMER_ID,
                             HIVE.CUSTOMER.C_CURRENT_CDEMO_SK,
                             HIVE.CUSTOMER.C_CURRENT_HDEMO_SK,
                             HIVE.CUSTOMER.C_CURRENT_ADDR_SK,
                             HIVE.CUSTOMER.C_FIRST_SHIPTO_DATE_SK,
                             HIVE.CUSTOMER.C_FIRST_SALES_DATE_SK,
                             HIVE.CUSTOMER.C_SALUTATION,
                             HIVE.CUSTOMER.C_FIRST_NAME,
                             HIVE.CUSTOMER.C_LAST_NAME,
                             HIVE.CUSTOMER.C_PREFERRED_CUST_FLAG,
                             HIVE.CUSTOMER.C_BIRTH_DAY,
                             HIVE.CUSTOMER.C_BIRTH_MONTH,
                             HIVE.CUSTOMER.C_BIRTH_YEAR,
                             HIVE.CUSTOMER.C_BIRTH_COUNTRY,
                             HIVE.CUSTOMER.C_LOGIN, HIVE.CUSTOMER.C_EMAIL_ADDRE
                             SS, HIVE.CUSTOMER.C_LAST_REVIEW_DATE
  executor_predicates .... HIVE.CUSTOMER.C_CUSTOMER_SK is not null

--- SQL operation complete.
>>
>>control query shape cut;

--- SQL operation complete.
>>set schema trafodion.sch;

--- SQL operation complete.
>>drop external table if exists date_dim for hive.hive.date_dim;

--- SQL operation complete.
>>cqd volatile_table_find_suitable_key 'SYSTEM';

--- SQL operation complete.
>>create external table date_dim 
+>  (d_date_sk int, d_date_id varchar(100 bytes) character set utf8, d_date date, 
+>   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 varchar(120 bytes) character set utf8, d_quarter_name varchar(200 bytes) character set utf8, d_holiday varchar(100 bytes) character set utf8,
+>   d_weekend varchar(100 bytes) character set utf8, d_following_holiday varchar(100 bytes) character set utf8, 
+>   d_first_dom int, d_last_dom int, d_same_day_ly int, d_same_day_lq int,
+>   d_current_day varchar(100 bytes) character set utf8, d_current_week varchar(111 bytes) character set utf8,
+>   d_current_month varchar(200 bytes) character set utf8, d_current_quarter varchar(100 bytes) character set utf8, 
+>   d_current_year varchar(100 bytes) character set utf8)
+>  for hive.hive.date_dim;

--- SQL operation complete.
>>invoke hive.hive.date_dim;

-- Definition of hive table DATE_DIM
-- Definition current  Wed Jul 12 21:39:40 2017

  (
    D_DATE_SK                        INT
  , D_DATE_ID                        VARCHAR(100 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT
  , D_DATE                           DATE
  , 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                       VARCHAR(120 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT
  , D_QUARTER_NAME                   VARCHAR(200 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT
  , D_HOLIDAY                        VARCHAR(100 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT
  , D_WEEKEND                        VARCHAR(100 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT
  , D_FOLLOWING_HOLIDAY              VARCHAR(100 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT
  , D_FIRST_DOM                      INT
  , D_LAST_DOM                       INT
  , D_SAME_DAY_LY                    INT
  , D_SAME_DAY_LQ                    INT
  , D_CURRENT_DAY                    VARCHAR(100 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT
  , D_CURRENT_WEEK                   VARCHAR(111 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT
  , D_CURRENT_MONTH                  VARCHAR(200 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT
  , D_CURRENT_QUARTER                VARCHAR(100 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT
  , D_CURRENT_YEAR                   VARCHAR(100 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT
  )
  /* stored as textfile */

--- SQL operation complete.
>>showddl hive.hive.date_dim;

/* Hive DDL */
CREATE 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
  )
  stored as textfile
;

REGISTER /*INTERNAL*/ HIVE TABLE HIVE.HIVE.DATE_DIM;
/* ObjectUID = 5537310018384739180 */

/* Trafodion DDL */

CREATE EXTERNAL TABLE DATE_DIM
  (
    D_DATE_SK                        INT DEFAULT NULL
  , D_DATE_ID                        VARCHAR(100 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL
  , D_DATE                           DATE DEFAULT NULL
  , D_MONTH_SEQ                      INT DEFAULT NULL
  , D_WEEK_SEQ                       INT DEFAULT NULL
  , D_QUARTER_SEQ                    INT DEFAULT NULL
  , D_YEAR                           INT DEFAULT NULL
  , D_DOW                            INT DEFAULT NULL
  , D_MOY                            INT DEFAULT NULL
  , D_DOM                            INT DEFAULT NULL
  , D_QOY                            INT DEFAULT NULL
  , D_FY_YEAR                        INT DEFAULT NULL
  , D_FY_QUARTER_SEQ                 INT DEFAULT NULL
  , D_FY_WEEK_SEQ                    INT DEFAULT NULL
  , D_DAY_NAME                       VARCHAR(120 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL
  , D_QUARTER_NAME                   VARCHAR(200 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL
  , D_HOLIDAY                        VARCHAR(100 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL
  , D_WEEKEND                        VARCHAR(100 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL
  , D_FOLLOWING_HOLIDAY              VARCHAR(100 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL
  , D_FIRST_DOM                      INT DEFAULT NULL
  , D_LAST_DOM                       INT DEFAULT NULL
  , D_SAME_DAY_LY                    INT DEFAULT NULL
  , D_SAME_DAY_LQ                    INT DEFAULT NULL
  , D_CURRENT_DAY                    VARCHAR(100 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL
  , D_CURRENT_WEEK                   VARCHAR(111 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL
  , D_CURRENT_MONTH                  VARCHAR(200 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL
  , D_CURRENT_QUARTER                VARCHAR(100 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL
  , D_CURRENT_YEAR                   VARCHAR(100 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL
  )
  FOR HIVE.HIVE.DATE_DIM
;

--- SQL operation complete.
>>prepare s from select * from hive.hive.date_dim where d_date = date '2016-01-27';

*** WARNING[6008] Statistics for column (D_DATE) from table HIVE.HIVE.DATE_DIM were not available. As a result, the access path chosen might not be the best possible.

--- SQL command prepared.
>>explain options 'c' s;

------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME ............ ###
STATEMENT_NAME ........... S
PLAN_ID ................ ###
ROWS_OUT ............... ###
EST_TOTAL_COST ......... ###
STATEMENT ................ select *
                           from hive.hive.date_dim
                           where d_date = date '2016-01-27';


------------------------------------------------------------------ NODE LISTING
ROOT ======================================  SEQ_NO 2        ONLY CHILD 1
REQUESTS_IN ............ ###
ROWS_OUT ............... ###
EST_OPER_COST .......... ###
EST_TOTAL_COST ......... ###
DESCRIPTION
  max_card_est ......... ###
  fragment_id ............ 0
  parent_frag ............ (none)
  fragment_type .......... master
  statement_index ...... ###
  affinity_value ....... ###
  max_max_cardinality    ###
  total_overflow_size    ###
  xn_access_mode ......... read_only
  xn_autoabort_interval  ###
  auto_query_retry ....... enabled
  plan_version ......... ###
  embedded_arkcmp ........ used
  IS_SQLCI ............... ON
  LDAP_USERNAME
  HIVE_MAX_STRING_LENGTH  20
  SHOWCONTROL_SHOW_ALL ... OFF
  SCHEMA ................. TRAFODION.SCH
  VOLATILE_TABLE_FIND_SUI  SYSTEM
  select_list ............ HIVE.DATE_DIM.D_DATE_SK, HIVE.DATE_DIM.D_DATE_ID,
                             %(2016-01-27), HIVE.DATE_DIM.D_MONTH_SEQ,
                             HIVE.DATE_DIM.D_WEEK_SEQ,
                             HIVE.DATE_DIM.D_QUARTER_SEQ, HIVE.DATE_DIM.D_YEAR,
                             HIVE.DATE_DIM.D_DOW, HIVE.DATE_DIM.D_MOY,
                             HIVE.DATE_DIM.D_DOM, HIVE.DATE_DIM.D_QOY,
                             HIVE.DATE_DIM.D_FY_YEAR,
                             HIVE.DATE_DIM.D_FY_QUARTER_SEQ,
                             HIVE.DATE_DIM.D_FY_WEEK_SEQ,
                             HIVE.DATE_DIM.D_DAY_NAME,
                             HIVE.DATE_DIM.D_QUARTER_NAME,
                             HIVE.DATE_DIM.D_HOLIDAY, HIVE.DATE_DIM.D_WEEKEND,
                             HIVE.DATE_DIM.D_FOLLOWING_HOLIDAY,
                             HIVE.DATE_DIM.D_FIRST_DOM,
                             HIVE.DATE_DIM.D_LAST_DOM,
                             HIVE.DATE_DIM.D_SAME_DAY_LY,
                             HIVE.DATE_DIM.D_SAME_DAY_LQ,
                             HIVE.DATE_DIM.D_CURRENT_DAY,
                             HIVE.DATE_DIM.D_CURRENT_WEEK,
                             HIVE.DATE_DIM.D_CURRENT_MONTH,
                             HIVE.DATE_DIM.D_CURRENT_QUARTER,
                             HIVE.DATE_DIM.D_CURRENT_YEAR
  input_variables ........ %(2016-01-27)


HIVE_SCAN =================================  SEQ_NO 1        NO CHILDREN
TABLE_NAME ............... HIVE.HIVE.DATE_DIM
REQUESTS_IN ............ ###
ROWS_OUT ............... ###
EST_OPER_COST .......... ###
EST_TOTAL_COST ......... ###
DESCRIPTION
  max_card_est ......... ###
  fragment_id ............ 0
  parent_frag ............ (none)
  fragment_type .......... master
  scan_type .............. subset scan of table HIVE.HIVE.DATE_DIM
  object_type ............ Hive_Text
  scan_direction ......... forward
  lock_mode .............. not specified, defaulted to lock cursor
  access_mode ............ not specified, defaulted to read committed
  columns_retrieved ..... 28
  executor_predicates .... (HIVE.DATE_DIM.D_DATE = %(2016-01-27))

--- SQL operation complete.
>>
>>drop external table if exists date_dim for hive.hive.date_dim;

--- SQL operation complete.
>>create external table date_dim 
+>  (d_date_sk int, d_date_id varchar(100 bytes) character set utf8, d_date date)
+>  for hive.hive.date_dim;

--- SQL operation complete.
>>invoke hive.hive.date_dim;

-- Definition of hive table DATE_DIM
-- Definition current  Wed Jul 12 21:39:56 2017

  (
    D_DATE_SK                        INT
  , D_DATE_ID                        VARCHAR(100 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT
  , D_DATE                           DATE
  , 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                       VARCHAR(20 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT
  , D_QUARTER_NAME                   VARCHAR(20 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT
  , D_HOLIDAY                        VARCHAR(20 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT
  , D_WEEKEND                        VARCHAR(20 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT
  , D_FOLLOWING_HOLIDAY              VARCHAR(20 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT
  , D_FIRST_DOM                      INT
  , D_LAST_DOM                       INT
  , D_SAME_DAY_LY                    INT
  , D_SAME_DAY_LQ                    INT
  , D_CURRENT_DAY                    VARCHAR(20 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT
  , D_CURRENT_WEEK                   VARCHAR(20 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT
  , D_CURRENT_MONTH                  VARCHAR(20 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT
  , D_CURRENT_QUARTER                VARCHAR(20 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT
  , D_CURRENT_YEAR                   VARCHAR(20 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT
  )
  /* stored as textfile */

--- SQL operation complete.
>>showddl hive.hive.date_dim;

/* Hive DDL */
CREATE 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
  )
  stored as textfile
;

REGISTER /*INTERNAL*/ HIVE TABLE HIVE.HIVE.DATE_DIM;
/* ObjectUID = 5537310018384739180 */

/* Trafodion DDL */

CREATE EXTERNAL TABLE DATE_DIM
  (
    D_DATE_SK                        INT DEFAULT NULL
  , D_DATE_ID                        VARCHAR(100 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL
  , D_DATE                           DATE DEFAULT NULL
  , D_MONTH_SEQ                      INT DEFAULT NULL
  , D_WEEK_SEQ                       INT DEFAULT NULL
  , D_QUARTER_SEQ                    INT DEFAULT NULL
  , D_YEAR                           INT DEFAULT NULL
  , D_DOW                            INT DEFAULT NULL
  , D_MOY                            INT DEFAULT NULL
  , D_DOM                            INT DEFAULT NULL
  , D_QOY                            INT DEFAULT NULL
  , D_FY_YEAR                        INT DEFAULT NULL
  , D_FY_QUARTER_SEQ                 INT DEFAULT NULL
  , D_FY_WEEK_SEQ                    INT DEFAULT NULL
  , D_DAY_NAME                       VARCHAR(20 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL
  , D_QUARTER_NAME                   VARCHAR(20 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL
  , D_HOLIDAY                        VARCHAR(20 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL
  , D_WEEKEND                        VARCHAR(20 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL
  , D_FOLLOWING_HOLIDAY              VARCHAR(20 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL
  , D_FIRST_DOM                      INT DEFAULT NULL
  , D_LAST_DOM                       INT DEFAULT NULL
  , D_SAME_DAY_LY                    INT DEFAULT NULL
  , D_SAME_DAY_LQ                    INT DEFAULT NULL
  , D_CURRENT_DAY                    VARCHAR(20 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL
  , D_CURRENT_WEEK                   VARCHAR(20 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL
  , D_CURRENT_MONTH                  VARCHAR(20 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL
  , D_CURRENT_QUARTER                VARCHAR(20 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL
  , D_CURRENT_YEAR                   VARCHAR(20 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL
  )
  FOR HIVE.HIVE.DATE_DIM
;

--- SQL operation complete.
>>prepare s from select * from hive.hive.date_dim where d_date = date '2016-01-27';

*** WARNING[6008] Statistics for column (D_DATE) from table HIVE.HIVE.DATE_DIM were not available. As a result, the access path chosen might not be the best possible.

--- SQL command prepared.
>>explain options 'p' s;

------------------------------------------------------------------ PLAN SUMMARY
STATEMENT_NAME ........... S
STATEMENT ................ select *
                           from hive.hive.date_dim
                           where d_date = date '2016-01-27';


------------------------------------------------------------------ NODE LISTING
ROOT ======================================  SEQ_NO 2        ONLY CHILD 1
DESCRIPTION
  fragment_id ............ 0
  parent_frag ............ (none)
  fragment_type .......... master
  xn_access_mode ......... read_only
  auto_query_retry ....... enabled
  embedded_arkcmp ........ used
  IS_SQLCI ............... ON
  LDAP_USERNAME
  HIVE_MAX_STRING_LENGTH  20
  SHOWCONTROL_SHOW_ALL ... OFF
  SCHEMA ................. TRAFODION.SCH
  VOLATILE_TABLE_FIND_SUI  SYSTEM
  select_list ............ HIVE.DATE_DIM.D_DATE_SK, HIVE.DATE_DIM.D_DATE_ID,
                             %(2016-01-27), HIVE.DATE_DIM.D_MONTH_SEQ,
                             HIVE.DATE_DIM.D_WEEK_SEQ,
                             HIVE.DATE_DIM.D_QUARTER_SEQ, HIVE.DATE_DIM.D_YEAR,
                             HIVE.DATE_DIM.D_DOW, HIVE.DATE_DIM.D_MOY,
                             HIVE.DATE_DIM.D_DOM, HIVE.DATE_DIM.D_QOY,
                             HIVE.DATE_DIM.D_FY_YEAR,
                             HIVE.DATE_DIM.D_FY_QUARTER_SEQ,
                             HIVE.DATE_DIM.D_FY_WEEK_SEQ,
                             HIVE.DATE_DIM.D_DAY_NAME,
                             HIVE.DATE_DIM.D_QUARTER_NAME,
                             HIVE.DATE_DIM.D_HOLIDAY, HIVE.DATE_DIM.D_WEEKEND,
                             HIVE.DATE_DIM.D_FOLLOWING_HOLIDAY,
                             HIVE.DATE_DIM.D_FIRST_DOM,
                             HIVE.DATE_DIM.D_LAST_DOM,
                             HIVE.DATE_DIM.D_SAME_DAY_LY,
                             HIVE.DATE_DIM.D_SAME_DAY_LQ,
                             HIVE.DATE_DIM.D_CURRENT_DAY,
                             HIVE.DATE_DIM.D_CURRENT_WEEK,
                             HIVE.DATE_DIM.D_CURRENT_MONTH,
                             HIVE.DATE_DIM.D_CURRENT_QUARTER,
                             HIVE.DATE_DIM.D_CURRENT_YEAR
  input_variables ........ %(2016-01-27)


HIVE_SCAN =================================  SEQ_NO 1        NO CHILDREN
TABLE_NAME ............... HIVE.HIVE.DATE_DIM
DESCRIPTION
  fragment_id ............ 0
  parent_frag ............ (none)
  fragment_type .......... master
  scan_type .............. subset scan of table HIVE.HIVE.DATE_DIM
  object_type ............ Hive_Text
  scan_direction ......... forward
  lock_mode .............. not specified, defaulted to lock cursor
  access_mode ............ not specified, defaulted to read committed
  columns_retrieved ..... 28
  executor_predicates .... (HIVE.DATE_DIM.D_DATE = %(2016-01-27))

--- SQL operation complete.
>>
>>
>>-- error cases
>>drop external table if exists date_dim for hive.hive.date_dim;

--- SQL operation complete.
>>
>>-- column d_date_skk doesn't exist in native hive table
>>create external table date_dim 
+>  (d_date_skk int)
+>  for hive.hive.date_dim;

*** ERROR[1009] Column D_DATE_SKK does not exist in the specified table.

--- SQL operation failed with errors.
>>
>>-- cannot have primary key on hive/text tables
>>drop external table if exists store_sales for hive.hive.store_sales;

--- SQL operation complete.
>>create external table store_sales 
+>  for hive.hive.store_sales primary key (ss_item_sk);

*** ERROR[3242] This statement is not supported. Reason: Cannot specify key attribute for external tables.

--- SQL operation failed with errors.
>>
>>-- column d_date_sk has incompatible type
>>create external table date_dim 
+>  (d_date_sk date)
+>  for hive.hive.date_dim;

--- SQL operation complete.
>>log;
