| >>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 sqlci -i"$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 |
| ----------- ----------- ----------- |
| |
| 1 9 12 |
| 2 9 4 |
| 3 18 9 |
| 4 7 6 |
| 5 8 5 |
| 6 4 12 |
| 7 24 4 |
| 8 26 12 |
| 9 26 10 |
| 10 15 10 |
| |
| --- 10 row(s) selected. |
| >>select * from hive.sch_t009.t009t2; |
| |
| A B C |
| ----------- ----------- ----------- |
| |
| 1 9 12 |
| 2 9 4 |
| 3 18 9 |
| 4 7 6 |
| 5 8 5 |
| 6 4 12 |
| 7 24 4 |
| 8 26 12 |
| 9 26 10 |
| 10 15 10 |
| |
| --- 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 Thu May 24 14:49:49 2018 |
| |
| ( |
| 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 Thu May 24 14:50:05 2018 |
| |
| ( |
| 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 sqlci -i"$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 |
| ----------- ----------- ----------- |
| |
| 1 9 12 |
| 2 9 4 |
| 3 18 9 |
| 4 7 6 |
| 5 8 5 |
| 6 4 12 |
| 7 24 4 |
| 8 26 12 |
| 9 26 10 |
| 10 15 10 |
| |
| --- 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 |
| |
| ======================= |
| 2 row(s) returned |
| |
| --- SQL operation complete. |
| >>invoke hive.sch_t009.t009t1; |
| |
| -- Definition of hive table HIVE.SCH_T009.T009T1 |
| -- Definition current Thu May 24 14:51:34 2018 |
| |
| ( |
| 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 Thu May 24 14:51:53 2018 |
| |
| ( |
| 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 Thu May 24 14:52:01 2018 |
| |
| ( |
| 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 HIVE.HIVE.STORE_SALES |
| -- Definition current Thu May 24 14:52:08 2018 |
| |
| ( |
| 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 |
| est_memory_per_node ### |
| max_card_est ......... ### |
| fragment_id ............ 0 |
| parent_frag ............ (none) |
| fragment_type .......... master |
| affinity_value ....... ### |
| max_max_cardinality ### |
| xn_access_mode ......... read_only |
| xn_autoabort_interval ### |
| auto_query_retry ....... enabled |
| plan_version ......... ### |
| embedded_arkcmp ........ used |
| IS_SQLCI ............... ON |
| LDAP_USERNAME |
| SEABASE_VOLATILE_TABLES ON |
| HBASE_ASYNC_DROP_TABLE OFF |
| HBASE_SERIALIZATION .... ON |
| HBASE_SMALL_SCANNER .... SYSTEM |
| HBASE_FILTER_PREDS ..... 2 |
| TRAF_ALIGNED_ROW_FORMAT ON |
| TRAF_INDEX_CREATE_OPT ON |
| HIVE_MAX_STRING_LENGTH 20 |
| SHOWCONTROL_SHOW_ALL ... OFF |
| SCHEMA ................. HIVE.HIVE |
| ObjectUIDs ........... ### |
| 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 |
| SEABASE_VOLATILE_TABLES ON |
| HBASE_ASYNC_DROP_TABLE OFF |
| HBASE_SERIALIZATION .... ON |
| HBASE_SMALL_SCANNER .... SYSTEM |
| HBASE_FILTER_PREDS ..... 2 |
| TRAF_ALIGNED_ROW_FORMAT ON |
| TRAF_INDEX_CREATE_OPT ON |
| 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 HIVE.HIVE.DATE_DIM |
| -- Definition current Thu May 24 14:52:14 2018 |
| |
| ( |
| 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 EXTERNAL TABLE HIVE.HIVE.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 |
| ; |
| |
| /* Trafodion DDL */ |
| |
| REGISTER /*INTERNAL*/ HIVE TABLE HIVE.HIVE.DATE_DIM; |
| /* ObjectUID = 4592144177079350537 */ |
| |
| 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'; |
| |
| --- 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 |
| est_memory_per_node ### |
| max_card_est ......... ### |
| fragment_id ............ 0 |
| parent_frag ............ (none) |
| fragment_type .......... master |
| affinity_value ....... ### |
| max_max_cardinality ### |
| xn_access_mode ......... read_only |
| xn_autoabort_interval ### |
| auto_query_retry ....... enabled |
| plan_version ......... ### |
| embedded_arkcmp ........ used |
| IS_SQLCI ............... ON |
| LDAP_USERNAME |
| SEABASE_VOLATILE_TABLES ON |
| HBASE_ASYNC_DROP_TABLE OFF |
| HBASE_SERIALIZATION .... ON |
| HBASE_SMALL_SCANNER .... SYSTEM |
| HBASE_FILTER_PREDS ..... 2 |
| TRAF_ALIGNED_ROW_FORMAT ON |
| TRAF_INDEX_CREATE_OPT ON |
| HIVE_MAX_STRING_LENGTH 20 |
| SHOWCONTROL_SHOW_ALL ... OFF |
| SCHEMA ................. TRAFODION.SCH |
| VOLATILE_TABLE_FIND_SUI SYSTEM |
| ObjectUIDs ........... ### |
| 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 HIVE.HIVE.DATE_DIM |
| -- Definition current Thu May 24 14:52:24 2018 |
| |
| ( |
| 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 EXTERNAL TABLE HIVE.HIVE.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 |
| ; |
| |
| /* Trafodion DDL */ |
| |
| REGISTER /*INTERNAL*/ HIVE TABLE HIVE.HIVE.DATE_DIM; |
| /* ObjectUID = 4592144177079350537 */ |
| |
| 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 |
| SEABASE_VOLATILE_TABLES ON |
| HBASE_ASYNC_DROP_TABLE OFF |
| HBASE_SERIALIZATION .... ON |
| HBASE_SMALL_SCANNER .... SYSTEM |
| HBASE_FILTER_PREDS ..... 2 |
| TRAF_ALIGNED_ROW_FORMAT ON |
| TRAF_INDEX_CREATE_OPT ON |
| 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; |
