http://git-wip-us.apache.org/repos/asf/trafodion/blob/6f490daf/core/sql/regress/hive/EXPECTED005 ---------------------------------------------------------------------- diff --git a/core/sql/regress/hive/EXPECTED005 b/core/sql/regress/hive/EXPECTED005 index 6521790..a0e32d9 100644 --- a/core/sql/regress/hive/EXPECTED005 +++ b/core/sql/regress/hive/EXPECTED005 @@ -72,7 +72,7 @@ *** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry. -*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1521763081810, failedModTS = 1521763182817, failedLoc = hdfs://localhost:24200/user/trafodion/hive/exttables/customer_ddl +*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1528120446084, failedModTS = 1528120587673, failedLoc = hdfs://localhost:36000/user/trafodion/hive/exttables/customer_ddl C_PREFERRED_CUST_FLAG (EXPR) ------------------------- -------------------- @@ -108,7 +108,7 @@ Y 9525 *** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry. -*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1521763081810, failedModTS = 1521763182817, failedLoc = hdfs://localhost:24200/user/trafodion/hive/exttables/customer_ddl +*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1528120446084, failedModTS = 1528120587673, failedLoc = hdfs://localhost:36000/user/trafodion/hive/exttables/customer_ddl C_PREFERRED_CUST_FLAG (EXPR) ------------------------- -------------------- @@ -177,7 +177,7 @@ Y 9525 *** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry. -*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1521763188079, failedModTS = 1521763198786, failedLoc = hdfs://localhost:24200/user/hive/warehouse/newtable +*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1528120591381, failedModTS = 1528120599768, failedLoc = hdfs://localhost:36000/user/hive/warehouse/newtable A ------------------------- @@ -227,7 +227,7 @@ xyz *** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry. -*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1521763182817, failedModTS = 1521763227396, failedLoc = hdfs://localhost:24200/user/trafodion/hive/exttables/customer_ddl +*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1528120587673, failedModTS = 1528120619405, failedLoc = hdfs://localhost:36000/user/trafodion/hive/exttables/customer_ddl C_PREFERRED_CUST_FLAG (EXPR) ------------------------- -------------------- @@ -242,7 +242,7 @@ Y 18984 *** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry. -*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1521763182817, failedModTS = 1521763227396, failedLoc = hdfs://localhost:24200/user/trafodion/hive/exttables/customer_ddl +*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1528120587673, failedModTS = 1528120619405, failedLoc = hdfs://localhost:36000/user/trafodion/hive/exttables/customer_ddl C_PREFERRED_CUST_FLAG (EXPR) ------------------------- -------------------- @@ -298,7 +298,7 @@ Y 18984 *** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry. -*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1521763223181, failedModTS = 1521763236557, failedLoc = hdfs://localhost:24200/user/hive/warehouse/newtable +*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1528120616839, failedModTS = 1528120628168, failedLoc = hdfs://localhost:36000/user/hive/warehouse/newtable A B ----------- ------------------------- @@ -617,18 +617,18 @@ C1 C2 C3 C4 C --- 1 row(s) selected. >>load with continue on error into trafodion.seabase.traf_tbl_bad select * >>from tbl_bad; Task: LOAD Status: Started Object: TRAFODION.SEABASE.TRAF_TBL_BAD -Task: CLEANUP Status: Started Time: 2018-03-23 00:02:16.72820 -Task: CLEANUP Status: Ended Time: 2018-03-23 00:02:16.87954 -Task: CLEANUP Status: Ended Elapsed Time: 00:00:00.015 -Task: LOADING DATA Status: Started Time: 2018-03-23 00:02:16.88001 +Task: CLEANUP Status: Started Time: 2018-06-04 13:58:23.141041 +Task: CLEANUP Status: Ended Time: 2018-06-04 13:58:23.156785 +Task: CLEANUP Status: Ended Elapsed Time: 00:00:00.016 +Task: LOADING DATA Status: Started Time: 2018-06-04 13:58:23.156828 Rows Processed: 8 Error Rows: 5 -Task: LOADING DATA Status: Ended Time: 2018-03-23 00:02:16.505744 -Task: LOADING DATA Status: Ended Elapsed Time: 00:00:00.418 -Task: COMPLETION Status: Started Time: 2018-03-23 00:02:16.505804 +Task: LOADING DATA Status: Ended Time: 2018-06-04 13:58:23.329872 +Task: LOADING DATA Status: Ended Elapsed Time: 00:00:00.173 +Task: COMPLETION Status: Started Time: 2018-06-04 13:58:23.329912 Rows Loaded: 3 -Task: COMPLETION Status: Ended Time: 2018-03-23 00:02:17.132726 -Task: COMPLETION Status: Ended Elapsed Time: 00:00:00.627 +Task: COMPLETION Status: Ended Time: 2018-06-04 13:58:23.718479 +Task: COMPLETION Status: Ended Elapsed Time: 00:00:00.389 --- 3 row(s) loaded. >>select count(*) from trafodion.seabase.traf_tbl_bad; @@ -644,19 +644,19 @@ Task: COMPLETION Status: Ended Elapsed Time: 00:00:00.627 --- 3 row(s) deleted. >>load with log error rows into trafodion.seabase.traf_tbl_bad select * from >>tbl_bad; Task: LOAD Status: Started Object: TRAFODION.SEABASE.TRAF_TBL_BAD -Task: CLEANUP Status: Started Time: 2018-03-23 00:02:18.222742 -Task: CLEANUP Status: Ended Time: 2018-03-23 00:02:18.238737 -Task: CLEANUP Status: Ended Elapsed Time: 00:00:00.016 - Logging Location: /user/trafodion/bulkload/logs/ERR_TRAFODION.SEABASE.TRAF_TBL_BAD_20180323_000218 -Task: LOADING DATA Status: Started Time: 2018-03-23 00:02:18.238844 +Task: CLEANUP Status: Started Time: 2018-06-04 13:58:24.769397 +Task: CLEANUP Status: Ended Time: 2018-06-04 13:58:24.778567 +Task: CLEANUP Status: Ended Elapsed Time: 00:00:00.009 + Logging Location: /user/trafodion/bulkload/logs/ERR_TRAFODION.SEABASE.TRAF_TBL_BAD_20180604_135824 +Task: LOADING DATA Status: Started Time: 2018-06-04 13:58:24.778637 Rows Processed: 8 Error Rows: 5 -Task: LOADING DATA Status: Ended Time: 2018-03-23 00:02:18.652144 -Task: LOADING DATA Status: Ended Elapsed Time: 00:00:00.413 -Task: COMPLETION Status: Started Time: 2018-03-23 00:02:18.652182 +Task: LOADING DATA Status: Ended Time: 2018-06-04 13:58:24.985221 +Task: LOADING DATA Status: Ended Elapsed Time: 00:00:00.207 +Task: COMPLETION Status: Started Time: 2018-06-04 13:58:24.985272 Rows Loaded: 3 -Task: COMPLETION Status: Ended Time: 2018-03-23 00:02:19.268334 -Task: COMPLETION Status: Ended Elapsed Time: 00:00:00.616 +Task: COMPLETION Status: Ended Time: 2018-06-04 13:58:25.579791 +Task: COMPLETION Status: Ended Elapsed Time: 00:00:00.595 --- 3 row(s) loaded. >>select count(*) from trafodion.seabase.traf_tbl_bad; @@ -669,19 +669,19 @@ Task: COMPLETION Status: Ended Elapsed Time: 00:00:00.616 --- 1 row(s) selected. >>load with log error rows to '/user/trafodion/bulkload/logs/TEST005' into >>trafodion.seabase.traf_tbl_bad select * from tbl_bad; Task: LOAD Status: Started Object: TRAFODION.SEABASE.TRAF_TBL_BAD -Task: CLEANUP Status: Started Time: 2018-03-23 00:02:20.268608 -Task: CLEANUP Status: Ended Time: 2018-03-23 00:02:20.278195 -Task: CLEANUP Status: Ended Elapsed Time: 00:00:00.010 - Logging Location: /user/trafodion/bulkload/logs/TEST005/ERR_TRAFODION.SEABASE.TRAF_TBL_BAD_20180323_000220 -Task: LOADING DATA Status: Started Time: 2018-03-23 00:02:20.278249 +Task: CLEANUP Status: Started Time: 2018-06-04 13:58:26.571248 +Task: CLEANUP Status: Ended Time: 2018-06-04 13:58:26.592217 +Task: CLEANUP Status: Ended Elapsed Time: 00:00:00.021 + Logging Location: /user/trafodion/bulkload/logs/TEST005/ERR_TRAFODION.SEABASE.TRAF_TBL_BAD_20180604_135826 +Task: LOADING DATA Status: Started Time: 2018-06-04 13:58:26.592275 Rows Processed: 8 Error Rows: 5 -Task: LOADING DATA Status: Ended Time: 2018-03-23 00:02:20.743348 -Task: LOADING DATA Status: Ended Elapsed Time: 00:00:00.465 -Task: COMPLETION Status: Started Time: 2018-03-23 00:02:20.743404 +Task: LOADING DATA Status: Ended Time: 2018-06-04 13:58:26.781747 +Task: LOADING DATA Status: Ended Elapsed Time: 00:00:00.189 +Task: COMPLETION Status: Started Time: 2018-06-04 13:58:26.781792 Rows Loaded: 3 -Task: COMPLETION Status: Ended Time: 2018-03-23 00:02:21.350084 -Task: COMPLETION Status: Ended Elapsed Time: 00:00:00.607 +Task: COMPLETION Status: Ended Time: 2018-06-04 13:58:27.137355 +Task: COMPLETION Status: Ended Elapsed Time: 00:00:00.356 --- 3 row(s) loaded. >>select count(*) from trafodion.seabase.traf_tbl_bad; @@ -697,10 +697,10 @@ Task: COMPLETION Status: Ended Elapsed Time: 00:00:00.607 --- 6 row(s) deleted. >>load with stop after 3 error rows into trafodion.seabase.traf_tbl_bad select >>* from tbl_bad; Task: LOAD Status: Started Object: TRAFODION.SEABASE.TRAF_TBL_BAD -Task: CLEANUP Status: Started Time: 2018-03-23 00:02:22.398357 -Task: CLEANUP Status: Ended Time: 2018-03-23 00:02:22.409109 -Task: CLEANUP Status: Ended Elapsed Time: 00:00:00.011 -Task: LOADING DATA Status: Started Time: 2018-03-23 00:02:22.409145 +Task: CLEANUP Status: Started Time: 2018-06-04 13:58:28.171192 +Task: CLEANUP Status: Ended Time: 2018-06-04 13:58:28.186727 +Task: CLEANUP Status: Ended Elapsed Time: 00:00:00.016 +Task: LOADING DATA Status: Started Time: 2018-06-04 13:58:28.186761 *** ERROR[8113] The maximum number of error rows is exceeded. @@ -715,11 +715,11 @@ Task: LOADING DATA Status: Started Time: 2018-03-23 00:02:22.409145 --- 1 row(s) selected. >>load with log error rows, stop after 3 error rows into >>trafodion.seabase.traf_tbl_bad select * from tbl_bad; Task: LOAD Status: Started Object: TRAFODION.SEABASE.TRAF_TBL_BAD -Task: CLEANUP Status: Started Time: 2018-03-23 00:02:23.773372 -Task: CLEANUP Status: Ended Time: 2018-03-23 00:02:23.788799 -Task: CLEANUP Status: Ended Elapsed Time: 00:00:00.015 - Logging Location: /user/trafodion/bulkload/logs/ERR_TRAFODION.SEABASE.TRAF_TBL_BAD_20180323_000223 -Task: LOADING DATA Status: Started Time: 2018-03-23 00:02:23.788861 +Task: CLEANUP Status: Started Time: 2018-06-04 13:58:29.310459 +Task: CLEANUP Status: Ended Time: 2018-06-04 13:58:29.320142 +Task: CLEANUP Status: Ended Elapsed Time: 00:00:00.010 + Logging Location: /user/trafodion/bulkload/logs/ERR_TRAFODION.SEABASE.TRAF_TBL_BAD_20180604_135829 +Task: LOADING DATA Status: Started Time: 2018-06-04 13:58:29.320178 *** ERROR[8113] The maximum number of error rows is exceeded. @@ -789,10 +789,10 @@ Task: LOADING DATA Status: Started Time: 2018-03-23 00:02:23.788861 --- SQL operation complete. >> ->>process hive statement 'drop table thive'; +>>drop table if exists hive.hive.thive; --- SQL operation complete. ->>process hive statement 'create table thive(a int)'; +>>create table hive.hive.thive(a int); --- SQL operation complete. >> @@ -800,14 +800,15 @@ Task: LOADING DATA Status: Started Time: 2018-03-23 00:02:23.788861 --- 0 row(s) selected. >> ->>sh echo "insert into thive values (1);" > TEST005_junk; ->>sh regrhive.ksh -f TEST005_junk; +>>process hive statement 'insert into thive values (1)'; + +--- SQL operation complete. >> >>select a from hive.hive.thive; *** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry. -*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1521763369777, failedModTS = 1521763389102, failedLoc = hdfs://localhost:24200/user/hive/warehouse/thive +*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1528120736679, failedModTS = 1528120744575, failedLoc = hdfs://localhost:36000/user/hive/warehouse/thive A ----------- @@ -828,22 +829,19 @@ A --- 2 row(s) selected. >> ->>process hive statement 'drop table thive'; +>>drop table hive.hive.thive; --- SQL operation complete. ->>process hive statement 'create table thive(a int, b int)'; +>>create table hive.hive.thive(a int, b int); --- SQL operation complete. >> ->>sh echo "insert into thive values (1,2);" > TEST005_junk; ->>sh regrhive.ksh -f TEST005_junk; +>>process hive statement 'insert into thive values (1,2)'; + +--- SQL operation complete. >> >>select a from hive.hive.thive; -*** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry. - -*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1521763391527, failedModTS = 1521763411540, failedLoc = hdfs://localhost:24200/user/hive/warehouse/thive - A ----------- @@ -860,48 +858,45 @@ A B --- 1 row(s) selected. >> ->>-- truncate of hive data ->>--cqd query_cache '0'; ->>truncate hive.hive.thive; +>>-- truncate of regular hive table +>>truncate table hive.hive.thive; --- SQL operation complete. >>select * from hive.hive.thive; -*** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry. - -*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1521763411540, failedModTS = 1521763418274, failedLoc = hdfs://localhost:24200/user/hive/warehouse/thive - --- 0 row(s) selected. >>insert into hive.hive.thive values (10, 20); --- 1 row(s) inserted. >>select * from hive.hive.thive; +*** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry. + +*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1528120760959, failedModTS = 1528120763842, failedLoc = hdfs://localhost:36000/user/hive/warehouse/thive + A B ----------- ----------- 10 20 --- 1 row(s) selected. ->>truncate hive.hive.thive; +>>truncate table hive.hive.thive; --- SQL operation complete. ->>truncate hive.hive.thive; +>>truncate table hive.hive.thive; --- SQL operation complete. >>select * from hive.hive.thive; -*** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry. - -*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1521763422992, failedModTS = 1521763425613, failedLoc = hdfs://localhost:24200/user/hive/warehouse/thive - --- 0 row(s) selected. >> >>-- truncate of partitioned hive table ->>process hive statement 'drop table t005part'; +>>-- When partitioned hive tables are supported, then inserts and selects below +>>-- can be executed directly from traf instead of through hive shell. +>>drop table if exists hive.hive.t005part; --- SQL operation complete. ->>process hive statement 'create table t005part(a int) partitioned by (b int, c int)'; +>>create table hive.hive.t005part(a int) partitioned by (b int, c int); --- SQL operation complete. >> @@ -912,7 +907,7 @@ A B t005part.a t005part.b t005part.c 5 10 11 >> ->>truncate hive.hive.t005part; +>>truncate table hive.hive.t005part; --- SQL operation complete. >>sh echo "select * from t005part;" > TEST005_junk; @@ -921,17 +916,16 @@ t005part.a t005part.b t005part.c >> >>sh echo "insert into t005part partition (b=10,c=11) values (5);" > >>TEST005_junk; >>sh regrhive.ksh -f TEST005_junk; ->>truncate hive.hive.t005part partition ('b=10'); +>>truncate table hive.hive.t005part partition (b=10); --- SQL operation complete. >>sh echo "select * from t005part;" > TEST005_junk; >>sh regrhive.ksh -f TEST005_junk | tee -a LOG005; t005part.a t005part.b t005part.c >> ->> >>sh echo "insert into t005part partition (b=10,c=11) values (5);" > >>TEST005_junk; >>sh regrhive.ksh -f TEST005_junk; ->>truncate hive.hive.t005part partition ('b=10','c=11'); +>>truncate table hive.hive.t005part partition (b=10,c=11); --- SQL operation complete. >>sh echo "select * from t005part;" > TEST005_junk; @@ -948,7 +942,7 @@ t005part.a t005part.b t005part.c 5 10 11 6 10 12 >> ->>truncate hive.hive.t005part partition ('b=10','c=11'); +>>truncate table hive.hive.t005part partition (b=10,c=11); --- SQL operation complete. >>sh echo "select * from t005part;" > TEST005_junk; @@ -956,7 +950,7 @@ t005part.a t005part.b t005part.c t005part.a t005part.b t005part.c 6 10 12 >> ->>truncate hive.hive.t005part partition ('b=10'); +>>truncate table hive.hive.t005part partition (b=10); --- SQL operation complete. >>sh echo "select * from t005part;" > TEST005_junk; @@ -964,18 +958,16 @@ t005part.a t005part.b t005part.c t005part.a t005part.b t005part.c >> >>-- should return error ->>truncate hive.hive.t005part partition ('b=12'); - -*** ERROR[8442] Unable to access HDFS interface. Call to ExpLOBInterfaceEmptyDirectory returned error LOB_DIR_NAME_ERROR(535). Error detail 0. +>>truncate table hive.hive.t005part partition (b=10,c=13); -*** ERROR[8035] Truncation of hive table failed. Reason: specified partition does not exist +*** ERROR[1214] Error encountered when executing HiveQL statement "truncate table `default`.T005PART partition (b=10,c=13)". Details: org.apache.hive.service.cli.HiveSQLException: Error while compiling statement: FAILED: SemanticException [Error 10006]: Partition not found {b=10, c=13} --- SQL operation failed with errors. >> >>-- should return error >>purgedata hive.hive.thive; -*** ERROR[3242] This statement is not supported. Reason: Purgedata is not allowed for hive tables. Use Truncate command. +*** ERROR[3242] This statement is not supported. Reason: Purgedata is not allowed for Hive tables. Use 'Truncate Table' command. *** ERROR[8822] The statement was not prepared. @@ -983,8 +975,8 @@ t005part.a t005part.b t005part.c >>-- tests for hive insert error modes >>invoke hive.hive.thive_insert_smallint; --- Definition of hive table THIVE_INSERT_SMALLINT --- Definition current Fri Mar 23 00:07:13 2018 +-- Definition of hive table HIVE.HIVE.THIVE_INSERT_SMALLINT +-- Definition current Mon Jun 4 14:02:21 2018 ( A SMALLINT @@ -995,7 +987,7 @@ t005part.a t005part.b t005part.c >>showddl hive.hive.thive_insert_smallint; /* Hive DDL */ -CREATE TABLE THIVE_INSERT_SMALLINT +CREATE TABLE HIVE.HIVE.THIVE_INSERT_SMALLINT ( A smallint ) @@ -1004,9 +996,12 @@ CREATE TABLE THIVE_INSERT_SMALLINT /* Trafodion DDL */ +REGISTER /*INTERNAL*/ HIVE TABLE HIVE.HIVE.THIVE_INSERT_SMALLINT; +/* ObjectUID = 4678276860950888823 */ + --- SQL operation complete. >> ->>truncate hive.hive.thive_insert_smallint; +>>truncate table hive.hive.thive_insert_smallint; --- SQL operation complete. >>cqd hive_insert_error_mode '0'; @@ -1027,7 +1022,7 @@ A --- 1 row(s) selected. >> ->>truncate hive.hive.thive_insert_smallint; +>>truncate table hive.hive.thive_insert_smallint; --- SQL operation complete. >>cqd hive_insert_error_mode '1'; @@ -1043,7 +1038,7 @@ A --- 0 row(s) selected. >> ->>truncate hive.hive.thive_insert_smallint; +>>truncate table hive.hive.thive_insert_smallint; --- SQL operation complete. >>cqd hive_insert_error_mode '2'; @@ -1063,7 +1058,7 @@ A --- 2 row(s) selected. >> ->>truncate hive.hive.thive_insert_smallint; +>>truncate table hive.hive.thive_insert_smallint; --- SQL operation complete. >>cqd hive_insert_error_mode '3'; @@ -1090,8 +1085,8 @@ A --- SQL operation complete. >>invoke hive.hive.thive_insert_varchar; --- Definition of hive table THIVE_INSERT_VARCHAR --- Definition current Fri Mar 23 00:07:23 2018 +-- Definition of hive table HIVE.HIVE.THIVE_INSERT_VARCHAR +-- Definition current Mon Jun 4 14:02:40 2018 ( A VARCHAR(1 CHAR) CHARACTER SET UTF8 COLLATE @@ -1105,7 +1100,7 @@ A >>showddl hive.hive.thive_insert_varchar; /* Hive DDL */ -CREATE TABLE THIVE_INSERT_VARCHAR +CREATE TABLE HIVE.HIVE.THIVE_INSERT_VARCHAR ( A varchar(1) , B string @@ -1115,11 +1110,14 @@ CREATE TABLE THIVE_INSERT_VARCHAR /* Trafodion DDL */ +REGISTER /*INTERNAL*/ HIVE TABLE HIVE.HIVE.THIVE_INSERT_VARCHAR; +/* ObjectUID = 4678276860950889215 */ + --- SQL operation complete. >>cqd hive_insert_error_mode '1'; --- SQL operation complete. ->>truncate hive.hive.thive_insert_varchar; +>>truncate table hive.hive.thive_insert_varchar; --- SQL operation complete. >>insert into hive.hive.thive_insert_varchar values ('abcddcba','efghijkl'); @@ -1138,5 +1136,4 @@ abcd efghijkl --- 1 row(s) selected. >> ->> >>log;
http://git-wip-us.apache.org/repos/asf/trafodion/blob/6f490daf/core/sql/regress/hive/EXPECTED007 ---------------------------------------------------------------------- diff --git a/core/sql/regress/hive/EXPECTED007 b/core/sql/regress/hive/EXPECTED007 index 2b0fd34..4674f01 100644 --- a/core/sql/regress/hive/EXPECTED007 +++ b/core/sql/regress/hive/EXPECTED007 @@ -1,3 +1,4 @@ +>> >>obey TEST007(setup_traf); >>set schema hive.hive; @@ -39,7 +40,7 @@ >>showddl hive.hive.thive1; /* Hive DDL */ -CREATE TABLE THIVE1 +CREATE TABLE HIVE.HIVE.THIVE1 ( A int , B int @@ -50,7 +51,7 @@ CREATE TABLE THIVE1 /* Trafodion DDL */ REGISTER /*INTERNAL*/ HIVE TABLE HIVE.HIVE.THIVE1; -/* ObjectUID = 7080927501909560103 */ +/* ObjectUID = 8969081248987438491 */ --- SQL operation complete. >> @@ -82,7 +83,7 @@ REGISTER /*INTERNAL*/ HIVE TABLE HIVE.HIVE.THIVE1; >>invoke trafodion.sch007.vhivehbase; -- Definition of Trafodion view TRAFODION.SCH007.VHIVEHBASE --- Definition current Wed Oct 18 21:01:16 2017 +-- Definition current Sat Jun 2 18:21:30 2018 ( AA INT DEFAULT NULL @@ -251,7 +252,7 @@ TRAFODION.SCH007.VHIVEHBASE >>showddl hive.hive.thive2; /* Hive DDL */ -CREATE TABLE THIVE2 +CREATE TABLE HIVE.HIVE.THIVE2 ( A int , B int @@ -262,7 +263,7 @@ CREATE TABLE THIVE2 /* Trafodion DDL */ REGISTER /*INTERNAL*/ HIVE TABLE HIVE.HIVE.THIVE2; -/* ObjectUID = 1928809434068290686 */ +/* ObjectUID = 8969081248987438052 */ --- SQL operation complete. >>showddl trafodion.sch007.vhive3; @@ -320,13 +321,12 @@ TRAFODION.SCH007.VHIVEHBASE *** ERROR[8822] The statement was not prepared. >> ->>-- traf view must be in traf cat/sch ->>create view vhive5 as select * from thive1; - -*** ERROR[3242] This statement is not supported. Reason: DDL views can only be created or dropped in trafodion schema. +>>create view if not exists vhive5 as select * from thive1; -*** ERROR[8822] The statement was not prepared. +--- SQL operation complete. +>>drop view vhive5; +--- SQL operation complete. >> >>-- if underlying hive table is dropped, an error is returned >>prepare s from select * from trafodion.sch007.vhive1; @@ -346,14 +346,14 @@ A B --- SQL operation complete. >>execute s; -*** ERROR[4263] Object HIVE.HIVE.THIVE1 has invalid state and cannot be accessed. It is registered in trafodion metadata but the correponding hive object does not exist. Use 'cleanup hive table/view' command to cleanup this object. +*** ERROR[4263] Object HIVE.HIVE.THIVE1 has invalid state and cannot be accessed. It is registered in trafodion metadata but the correponding hive object does not exist. Use 'cleanup table/view' command to cleanup this object. *** ERROR[8822] The statement was not prepared. --- 0 row(s) selected. >>select * from trafodion.sch007.vhive1; -*** ERROR[4263] Object HIVE.HIVE.THIVE1 has invalid state and cannot be accessed. It is registered in trafodion metadata but the correponding hive object does not exist. Use 'cleanup hive table/view' command to cleanup this object. +*** ERROR[4263] Object HIVE.HIVE.THIVE1 has invalid state and cannot be accessed. It is registered in trafodion metadata but the correponding hive object does not exist. Use 'cleanup table/view' command to cleanup this object. *** ERROR[8822] The statement was not prepared. @@ -364,46 +364,47 @@ A B --- SQL operation failed with errors. >>select * from hive.hive.thive1; -*** ERROR[4263] Object HIVE.HIVE.THIVE1 has invalid state and cannot be accessed. It is registered in trafodion metadata but the correponding hive object does not exist. Use 'cleanup hive table/view' command to cleanup this object. +*** ERROR[4263] Object HIVE.HIVE.THIVE1 has invalid state and cannot be accessed. It is registered in trafodion metadata but the correponding hive object does not exist. Use 'cleanup table/view' command to cleanup this object. *** ERROR[8822] The statement was not prepared. ->>cleanup hive table hive.hive.thive1; +>>cleanup table hive.hive.thive1; --- SQL operation complete. >>select * from hive.hive.thive1; -*** ERROR[1388] Object HIVE.HIVE.THIVE1 does not exist in hive metadata. +*** ERROR[1388] Object HIVE.HIVE.THIVE1 does not exist in Hive Metadata. *** ERROR[8822] The statement was not prepared. >> >> >>obey TEST007(setup_hive); ->>process hive statement 'create database hivesch007'; +>>create database hive.hivesch007; --- SQL operation complete. >> ->>process hive statement 'create table hivesch007.thive1 (a int)'; +>>create table hive.hivesch007.thive1 (a int); --- SQL operation complete. >> ->>sh echo "insert into hivesch007.thive1 values (1);" > TEST007_junk; ->>sh regrhive.ksh -f TEST007_junk; +>>insert into hive.hivesch007.thive1 values (1); + +--- 1 row(s) inserted. >> ->>process hive statement 'create view hivesch007.vhive1 as select * from hivesch007.thive1 where thive1.a > 0'; +>>create view hive.hivesch007.vhive1 as select * from hivesch007.thive1 where thive1.a > 0; --- SQL operation complete. >> ->>process hive statement 'create view hivesch007.vhive11 as select * from hivesch007.vhive1 where vhive1.a > 0'; +>>create view hive.hivesch007.vhive11 as select * from hivesch007.vhive1 where vhive1.a > 0; --- SQL operation complete. >> ->>process hive statement 'create table thive007 (a int)'; +>>create table hive.hive.thive007 (a int); --- SQL operation complete. >> ->>process hive statement 'create view vhive007 as select * from thive007 where a > 0'; +>>create view hive.hive.vhive007 as select * from thive007 where a > 0; --- SQL operation complete. >> @@ -414,8 +415,8 @@ A B >>obey TEST007(tests_hive); >>invoke hive.hivesch007.vhive1; --- Definition of native Hive view VHIVE1 --- Definition current Wed Oct 18 21:03:26 2017 +-- Definition of native Hive view HIVE.HIVESCH007.VHIVE1 +-- Definition current Sat Jun 2 18:23:20 2018 ( A INT @@ -428,9 +429,12 @@ Original native Hive view text: select * from hivesch007.thive1 where thive1.a > 0 Expanded native Hive view text: -CREATE VIEW vhive1 AS +CREATE VIEW hive.hivesch007.vhive1 AS select thive1.a from hivesch007.thive1 where thive1.a > 0 ; +REGISTER /*INTERNAL*/ HIVE VIEW HIVE.HIVESCH007.VHIVE1; +/* ObjectUID = 8947126200801021695 */ + --- SQL operation complete. >> >>prepare s from select * from hive.hivesch007.vhive1; @@ -456,8 +460,8 @@ A >> >>invoke hive.hivesch007.vhive11; --- Definition of native Hive view VHIVE11 --- Definition current Wed Oct 18 21:03:30 2017 +-- Definition of native Hive view HIVE.HIVESCH007.VHIVE11 +-- Definition current Sat Jun 2 18:23:25 2018 ( A INT @@ -470,9 +474,12 @@ Original native Hive view text: select * from hivesch007.vhive1 where vhive1.a > 0 Expanded native Hive view text: -CREATE VIEW vhive11 AS +CREATE VIEW hive.hivesch007.vhive11 AS select vhive1.a from hivesch007.vhive1 where vhive1.a > 0 ; +REGISTER /*INTERNAL*/ HIVE VIEW HIVE.HIVESCH007.VHIVE11; +/* ObjectUID = 8947126200801021864 */ + --- SQL operation complete. >> >>prepare s from select * from hive.hivesch007.vhive11; @@ -653,6 +660,7 @@ TRAFODION.SCH007.VTRAFONHIVE >>unregister hive view hive.hivesch007.vhive11 cascade; --- SQL operation complete. +>> >>get tables in view hive.hivesch007.vhive11; --- SQL operation complete. @@ -669,11 +677,11 @@ Original native Hive view text: select * from hivesch007.vhive1 where vhive1.a > 0 Expanded native Hive view text: -CREATE VIEW vhive11 AS +CREATE VIEW hive.hivesch007.vhive11 AS select vhive1.a from hivesch007.vhive1 where vhive1.a > 0 ; REGISTER HIVE VIEW HIVE.HIVESCH007.VHIVE11; -/* ObjectUID = 6918234965366828184 */ +/* ObjectUID = 8947126200801027583 */ --- SQL operation complete. >>get tables in view hive.hivesch007.vhive11; @@ -727,7 +735,7 @@ Original native Hive view text: select vh1.a x, h1.a y from hivesch0071.vh1, hivesch0071.h1 Expanded native Hive view text: -CREATE VIEW vh11 AS +CREATE VIEW hive.hivesch0071.vh11 AS select vh1.a x, h1.a y from hivesch0071.vh1, hivesch0071.h1 ; --- SQL operation complete. @@ -822,7 +830,7 @@ Original native Hive view text: select vh1.a x, h1.a y from hivesch0071.vh1, hivesch0071.h1 Expanded native Hive view text: -CREATE VIEW vh11 AS +CREATE VIEW hive.hivesch0071.vh11 AS select vh1.a x, h1.a y from hivesch0071.vh1, hivesch0071.h1 ; --- SQL operation complete. @@ -854,7 +862,7 @@ CREATE VIEW vh11 AS >>showddl schema hive.hivesch0078; /* Hive DDL */ -create database hivesch0078; +CREATE SCHEMA HIVE.HIVESCH0078; --- SQL operation complete. @@ -864,9 +872,9 @@ create database hivesch0078; >>showddl schema hive.hivesch0078; /* Hive DDL */ -create database hivesch0078; +CREATE SCHEMA HIVE.HIVESCH0078; -REGISTER /*INTERNAL*/ HIVE SCHEMA hive.hivesch0078; +REGISTER /*INTERNAL*/ HIVE SCHEMA HIVE.HIVESCH0078; --- SQL operation complete. >>get hive registered schemas in catalog trafodion, match '%hivesch0078%'; @@ -883,7 +891,7 @@ hive.hivesch0078 >>showddl schema hive.hivesch0078; /* Hive DDL */ -create database hivesch0078; +CREATE SCHEMA HIVE.HIVESCH0078; --- SQL operation complete. @@ -924,7 +932,7 @@ create database hivesch0078; >> >>prepare s from select * from hive.hivesch007.vhive11; -*** ERROR[4263] Object HIVE.HIVESCH007.THIVE1 has invalid state and cannot be accessed. It is registered in trafodion metadata but the correponding hive object does not exist. Use 'cleanup hive table/view' command to cleanup this object. +*** ERROR[4263] Object HIVE.HIVESCH007.THIVE1 has invalid state and cannot be accessed. It is registered in trafodion metadata but the correponding hive object does not exist. Use 'cleanup table/view' command to cleanup this object. *** ERROR[8822] The statement was not prepared. @@ -950,7 +958,7 @@ create database hivesch0078; >> >>execute s; -*** ERROR[4263] Object HIVE.HIVESCH007.THIVE1 has invalid state and cannot be accessed. It is registered in trafodion metadata but the correponding hive object does not exist. Use 'cleanup hive table/view' command to cleanup this object. +*** ERROR[4263] Object HIVE.HIVESCH007.THIVE1 has invalid state and cannot be accessed. It is registered in trafodion metadata but the correponding hive object does not exist. Use 'cleanup table/view' command to cleanup this object. *** ERROR[8822] The statement was not prepared. @@ -985,7 +993,7 @@ create database hivesch0078; >>showddl thive9; /* Hive DDL */ -CREATE TABLE THIVE9 +CREATE TABLE HIVE.HIVESCH007.THIVE9 ( A int ) @@ -1019,11 +1027,11 @@ S------ HIVE.HIVESCH007.THIVE9 >>showstats for table hive.hivesch007.thive9 on every column; Histogram data for Table HIVE.HIVESCH007.THIVE9 -Table ID: 6918234965366838662 +Table ID: 8947126200801036437 Hist ID # Ints Rowcount UEC Colname(s) ========== ====== =========== =========== =========================== - 332052322 6 6 6 A +1235981905 6 6 6 A --- SQL operation complete. @@ -1033,7 +1041,7 @@ Table ID: 6918234965366838662 >>showddl hive.hivesch007.thive9; /* Hive DDL */ -CREATE TABLE THIVE9 +CREATE TABLE HIVE.HIVESCH007.THIVE9 ( A int ) @@ -1054,7 +1062,7 @@ CREATE EXTERNAL TABLE THIVE9 >>showddl thive9; /* Hive DDL */ -CREATE TABLE THIVE9 +CREATE TABLE HIVE.HIVESCH007.THIVE9 ( A int ) @@ -1064,7 +1072,7 @@ CREATE TABLE THIVE9 /* Trafodion DDL */ REGISTER HIVE TABLE HIVE.HIVESCH007.THIVE9; -/* ObjectUID = 6918234965366838662 */ +/* ObjectUID = 8947126200801036437 */ CREATE EXTERNAL TABLE THIVE9 FOR HIVE.HIVESCH007.THIVE9 @@ -1074,11 +1082,11 @@ CREATE EXTERNAL TABLE THIVE9 >>showstats for table hive.hivesch007.thive9 on every column; Histogram data for Table HIVE.HIVESCH007.THIVE9 -Table ID: 6918234965366838662 +Table ID: 8947126200801036437 Hist ID # Ints Rowcount UEC Colname(s) ========== ====== =========== =========== =========================== - 332052322 6 6 6 A +1235981905 6 6 6 A --- SQL operation complete. @@ -1088,7 +1096,7 @@ Table ID: 6918234965366838662 >>showddl thive9; /* Hive DDL */ -CREATE TABLE THIVE9 +CREATE TABLE HIVE.HIVESCH007.THIVE9 ( A int ) @@ -1105,11 +1113,11 @@ CREATE EXTERNAL TABLE THIVE9 >>showstats for table hive.hivesch007.thive9 on every column; Histogram data for Table HIVE.HIVESCH007.THIVE9 -Table ID: 6918234965366838662 +Table ID: 8947126200801036437 Hist ID # Ints Rowcount UEC Colname(s) ========== ====== =========== =========== =========================== - 332052322 6 6 6 A +1235981905 6 6 6 A --- SQL operation complete. @@ -1123,7 +1131,7 @@ Table ID: 6918234965366838662 >>showddl thive9; /* Hive DDL */ -CREATE TABLE THIVE9 +CREATE TABLE HIVE.HIVESCH007.THIVE9 ( A int ) @@ -1148,4 +1156,208 @@ No Histograms exist for the requested columns or groups >> >> >> +>>obey TEST007(clean_up_traf); +>>drop view trafodion.sch007.vhive1 cascade; + +*** ERROR[1389] Object TRAFODION.SCH007.VHIVE1 does not exist in Trafodion. + +*** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry. + +*** WARNING[1389] Object TRAFODION.SCH007.VHIVE1 does not exist in Trafodion. + +--- SQL operation failed with errors. +>>drop view trafodion.sch007.vhive11 cascade; + +--- SQL operation complete. +>>drop view trafodion.sch007.vhive2 cascade; + +*** ERROR[1389] Object TRAFODION.SCH007.VHIVE2 does not exist in Trafodion. + +*** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry. + +*** WARNING[1389] Object TRAFODION.SCH007.VHIVE2 does not exist in Trafodion. + +--- SQL operation failed with errors. +>>drop view trafodion.sch007.vhive3 cascade; + +*** ERROR[1389] Object TRAFODION.SCH007.VHIVE3 does not exist in Trafodion. + +*** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry. + +*** WARNING[1389] Object TRAFODION.SCH007.VHIVE3 does not exist in Trafodion. + +--- SQL operation failed with errors. +>>drop view trafodion.sch007.vhive4 cascade; + +*** ERROR[1389] Object TRAFODION.SCH007.VHIVE4 does not exist in Trafodion. + +*** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry. + +*** WARNING[1389] Object TRAFODION.SCH007.VHIVE4 does not exist in Trafodion. + +--- SQL operation failed with errors. +>>drop view trafodion.sch007.vhive5 cascade; + +*** ERROR[1389] Object TRAFODION.SCH007.VHIVE5 does not exist in Trafodion. + +*** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry. + +*** WARNING[1389] Object TRAFODION.SCH007.VHIVE5 does not exist in Trafodion. + +--- SQL operation failed with errors. +>>drop view trafodion.sch007.vhive6 cascade; + +*** ERROR[1389] Object TRAFODION.SCH007.VHIVE6 does not exist in Trafodion. + +*** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry. + +*** WARNING[1389] Object TRAFODION.SCH007.VHIVE6 does not exist in Trafodion. + +--- SQL operation failed with errors. +>>drop external table thive1 for hive.hive.thive1 cascade; + +*** ERROR[1389] Object TRAFODION."_HV_HIVE_".THIVE1 does not exist in Trafodion. + +*** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry. + +*** WARNING[1389] Object TRAFODION."_HV_HIVE_".THIVE1 does not exist in Trafodion. + +--- SQL operation failed with errors. +>>cleanup table trafodion."_HV_HIVE_".thive1; + +*** WARNING[4251] Object UID could not be retrieved from metadata based on the provided object name. Metadata cleanup will not be performed but the underlying hbase object will be removed, if it exists. + +--- SQL operation complete. +>>drop external table thive2 for hive.hive.thive2 cascade; + +*** ERROR[1389] Object TRAFODION."_HV_HIVE_".THIVE2 does not exist in Trafodion. + +*** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry. + +*** WARNING[1389] Object TRAFODION."_HV_HIVE_".THIVE2 does not exist in Trafodion. + +--- SQL operation failed with errors. +>>process hive statement 'drop table thive1'; + +--- SQL operation complete. +>>process hive statement 'drop table thive2'; + +--- SQL operation complete. +>>drop external table thive3 for hive.hive.thive3; + +*** ERROR[1389] Object TRAFODION."_HV_HIVE_".THIVE3 does not exist in Trafodion. + +*** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry. + +*** WARNING[1389] Object TRAFODION."_HV_HIVE_".THIVE3 does not exist in Trafodion. + +--- SQL operation failed with errors. +>>process hive statement 'drop table thive3'; + +--- SQL operation complete. +>>drop table if exists trafodion.sch007.thbase1 cascade; + +--- SQL operation complete. +>> +>>obey TEST007(clean_up_hive); +>> +>>set schema hive.hive; + +--- SQL operation complete. +>>unregister hive table hive.hive.thive1 cleanup; + +*** WARNING[1389] Object THIVE1 does not exist in Trafodion. + +--- SQL operation complete. +>> +>>unregister hive view hive.hivesch007.vhive1 cascade cleanup; + +*** ERROR[4263] Object HIVE.HIVESCH007.THIVE1 has invalid state and cannot be accessed. It is registered in trafodion metadata but the correponding hive object does not exist. Use 'cleanup table/view' command to cleanup this object. + +--- SQL operation failed with errors. +>>unregister hive view hive.hivesch007.vhive11 cascade cleanup; + +*** ERROR[4263] Object HIVE.HIVESCH007.THIVE1 has invalid state and cannot be accessed. It is registered in trafodion metadata but the correponding hive object does not exist. Use 'cleanup table/view' command to cleanup this object. + +--- SQL operation failed with errors. +>> +>>drop table hivesch007.thive1; + +*** ERROR[1388] Table hivesch007.thive1 does not exist in Hive Metadata. + +--- SQL operation failed with errors. +>>drop view hivesch007.vhive11; + +--- SQL operation complete. +>>drop view hivesch007.vhive1; + +--- SQL operation complete. +>>drop database hivesch007 cascade; + +--- SQL operation complete. +>>drop table hive.hive.thive007; + +--- SQL operation complete. +>>drop view vhive007; + +--- SQL operation complete. +>> +>>cleanup view hive.hivesch007.vhive11; + +*** ERROR[1389] Object VHIVE11 does not exist in Trafodion. + +*** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry. + +*** WARNING[1389] Object VHIVE11 does not exist in Trafodion. + +--- SQL operation failed with errors. +>> +>>drop view trafodion.sch007.vtrafonhive; + +--- SQL operation complete. +>> +>>drop schema trafodion.sch007 cascade; + +--- SQL operation complete. +>> +>>drop table hivesch0071.h1; + +--- SQL operation complete. +>>drop view hivesch0071.vh1; + +--- SQL operation complete. +>>drop view hivesch0071.vh11; + +--- SQL operation complete. +>> +>>drop table hivesch007.thive9; + +*** ERROR[1388] Table hivesch007.thive9 does not exist in Hive Metadata. + +--- SQL operation failed with errors. +>>drop view trafodion.sch007.vhive9; + +*** ERROR[1389] Object TRAFODION.SCH007.VHIVE9 does not exist in Trafodion. + +*** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry. + +*** WARNING[1389] Object TRAFODION.SCH007.VHIVE9 does not exist in Trafodion. + +--- SQL operation failed with errors. +>>drop external table thive9 for hive.hivesch007.thive9; + +*** ERROR[1389] Object TRAFODION."_HV_HIVESCH007_".THIVE9 does not exist in Trafodion. + +*** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry. + +*** WARNING[1389] Object TRAFODION."_HV_HIVESCH007_".THIVE9 does not exist in Trafodion. + +--- SQL operation failed with errors. +>> +>>drop database hivesch0071 cascade; + +--- SQL operation complete. +>> +>> +>> >>log; http://git-wip-us.apache.org/repos/asf/trafodion/blob/6f490daf/core/sql/regress/hive/EXPECTED008 ---------------------------------------------------------------------- diff --git a/core/sql/regress/hive/EXPECTED008 b/core/sql/regress/hive/EXPECTED008 new file mode 100644 index 0000000..e1c034b --- /dev/null +++ b/core/sql/regress/hive/EXPECTED008 @@ -0,0 +1,1105 @@ +>>cqd traf_ddl_on_hive_objects 'ON'; + +--- SQL operation complete. +>> +>>showddl schema hive.sch008; + +*** ERROR[1003] Schema HIVE.SCH008 does not exist. + +--- SQL operation failed with errors. +>>create database hive.sch008; + +--- SQL operation complete. +>>create schema if not exists hive.sch008; + +--- SQL operation complete. +>>showddl schema hive.sch008; + +/* Hive DDL */ +CREATE SCHEMA HIVE.SCH008; + +REGISTER /*INTERNAL*/ HIVE SCHEMA HIVE.SCH008; + +--- SQL operation complete. +>> +>>drop table if exists hive.sch008.t00801; + +--- SQL operation complete. +>>create table hive.sch008.t00801 (a int, b int); + +--- SQL operation complete. +>>invoke hive.sch008.t00801; + +-- Definition of hive table HIVE.SCH008.T00801 +-- Definition current Sat Jun 2 18:28:46 2018 + + ( + A INT + , B INT + ) + /* stored as textfile */ + +--- SQL operation complete. +>>showddl hive.sch008.t00801; + +/* Hive DDL */ +CREATE TABLE HIVE.SCH008.T00801 + ( + A int + , B int + ) + stored as textfile +; + +/* Trafodion DDL */ + +REGISTER /*INTERNAL*/ HIVE TABLE HIVE.SCH008.T00801; +/* ObjectUID = 1402470925633631767 */ + +--- SQL operation complete. +>>drop table hive.sch008.t00801; + +--- SQL operation complete. +>> +>>set schema hive.sch008; + +--- SQL operation complete. +>>create table t00802 (a int, b int); + +--- SQL operation complete. +>>invoke t00802; + +-- Definition of hive table HIVE.SCH008.T00802 +-- Definition current Sat Jun 2 18:28:57 2018 + + ( + A INT + , B INT + ) + /* stored as textfile */ + +--- SQL operation complete. +>> +>>insert into t00802 values (1,2); + +--- 1 row(s) inserted. +>>select * from t00802; + +A B +----------- ----------- + + 1 2 + +--- 1 row(s) selected. +>> +>>create table t00803 as select * from sch008.t00802; + +--- SQL operation complete. +>>invoke t00803; + +-- Definition of hive table HIVE.SCH008.T00803 +-- Definition current Sat Jun 2 18:29:08 2018 + + ( + A INT + , B INT + ) + /* stored as textfile */ + +--- SQL operation complete. +>>select * from t00803; + +A B +----------- ----------- + + 1 2 + +--- 1 row(s) selected. +>> +>>-- describe will show datatype 132 for b +>>prepare s from insert into t00802 values (?, ?); + +--- SQL command prepared. +>>describe s; +---Describing the INPUT entries--- +[1] NAME "", HEADING "" + TYPE 4, FS 132, LEN 4, OCTLEN 4, CHARSET 0 + PREC 0, LPREC 0, SCALE 0, DTCODE 0, NULL 1, MODE 1, IDX 1, ORDPOS 0 + CAT "", SCH "", TABLE "" +[2] NAME "", HEADING "" + TYPE 4, FS 132, LEN 4, OCTLEN 4, CHARSET 0 + PREC 0, LPREC 0, SCALE 0, DTCODE 0, NULL 1, MODE 1, IDX 2, ORDPOS 0 + CAT "", SCH "", TABLE "" +>> +>>-- alter will do QI and reload table definition +>>alter table t00802 change b b bigint; + +--- SQL operation complete. +>> +>>select * from t00802; + +A B +----------- -------------------- + + 1 2 + +--- 1 row(s) selected. +>> +>>-- describe will show datatype 134 for b +>>prepare s from insert into t00802 values (?, ?); + +--- SQL command prepared. +>>describe s; +---Describing the INPUT entries--- +[1] NAME "", HEADING "" + TYPE 4, FS 132, LEN 4, OCTLEN 4, CHARSET 0 + PREC 0, LPREC 0, SCALE 0, DTCODE 0, NULL 1, MODE 1, IDX 1, ORDPOS 0 + CAT "", SCH "", TABLE "" +[2] NAME "", HEADING "" + TYPE -402, FS 134, LEN 8, OCTLEN 8, CHARSET 0 + PREC 0, LPREC 0, SCALE 0, DTCODE 0, NULL 1, MODE 1, IDX 2, ORDPOS 0 + CAT "", SCH "", TABLE "" +>> +>>invoke t00802; + +-- Definition of hive table HIVE.SCH008.T00802 +-- Definition current Sat Jun 2 18:29:15 2018 + + ( + A INT + , B LARGEINT + ) + /* stored as textfile */ + +--- SQL operation complete. +>>showddl t00802; + +/* Hive DDL */ +CREATE TABLE HIVE.SCH008.T00802 + ( + A int + , B bigint + ) + stored as textfile +; + +/* Trafodion DDL */ + +REGISTER /*INTERNAL*/ HIVE TABLE HIVE.SCH008.T00802; +/* ObjectUID = 1402470925633632981 */ + +--- SQL operation complete. +>> +>>-- create in hive default schema +>>drop table if exists hive.`default`.t00804; + +--- SQL operation complete. +>>create table hive.`default`.`t00804` (`a` int); + +--- SQL operation complete. +>>showddl hive.hive.t00804; + +/* Hive DDL */ +CREATE TABLE HIVE.HIVE.T00804 + ( + A int + ) + stored as textfile +; + +/* Trafodion DDL */ + +REGISTER /*INTERNAL*/ HIVE TABLE HIVE.HIVE.T00804; +/* ObjectUID = 1402470925633635322 */ + +--- SQL operation complete. +>>insert into hive.hive.t00804 values (11); + +--- 1 row(s) inserted. +>> +>>create view if not exists hive.sch008.v00802 as select * from sch008.t00802; + +--- SQL operation complete. +>>showddl hive.sch008.v00802; + +Original native Hive view text: +select * from sch008.t00802 + +Expanded native Hive view text: +CREATE VIEW hive.sch008.v00802 AS + select t00802.a, t00802.b from sch008.t00802 ; + +REGISTER /*INTERNAL*/ HIVE VIEW HIVE.SCH008.V00802; +/* ObjectUID = 1402470925633635814 */ + +--- SQL operation complete. +>>select * from v00802; + +A B +----------- -------------------- + + 1 2 + +--- 1 row(s) selected. +>>alter view hive.sch008.v00802 as select * from sch008.t00802; + +--- SQL operation complete. +>>showddl hive.sch008.v00802; + +Original native Hive view text: +select * from sch008.t00802 + +Expanded native Hive view text: +CREATE VIEW hive.sch008.v00802 AS + select t00802.a, t00802.b from sch008.t00802 ; + +REGISTER /*INTERNAL*/ HIVE VIEW HIVE.SCH008.V00802; +/* ObjectUID = 1402470925633635814 */ + +--- SQL operation complete. +>> +>>-- view in a different schema than table +>>create view hive.sch008.v00803 as select * from `default`.t00804; + +--- SQL operation complete. +>>showddl hive.sch008.v00803; + +Original native Hive view text: +select * from default.t00804 + +Expanded native Hive view text: +CREATE VIEW hive.sch008.v00803 AS + select t00804.a from hive.t00804 ; + +REGISTER /*INTERNAL*/ HIVE VIEW HIVE.SCH008.V00803; +/* ObjectUID = 1402470925633636550 */ + +--- SQL operation complete. +>>prepare s from select * from v00803; + +--- SQL command prepared. +>> +>>get objects in schema hive.sch008; + +Objects in Schema HIVE.SCH008 +============================= + +t00802 +t00803 +v00802 +v00803 + +--- SQL operation complete. +>> +>>drop view if exists hive.sch008.v00802; + +--- SQL operation complete. +>>invoke hive.sch008.v00802; + +*** ERROR[1388] Object HIVE.SCH008.V00802 does not exist in Hive Metadata. + +*** ERROR[8822] The statement was not prepared. + +>> +>>get objects in schema hive.sch008; + +Objects in Schema HIVE.SCH008 +============================= + +t00802 +t00803 +v00803 + +--- SQL operation complete. +>> +>>-- delimited names using hive ` as delimiter. +>>create table hive.`sch008`.`t00804` (`a` int); + +--- SQL operation complete. +>>showddl hive.sch008.t00804; + +/* Hive DDL */ +CREATE TABLE HIVE.SCH008.T00804 + ( + A int + ) + stored as textfile +; + +/* Trafodion DDL */ + +REGISTER /*INTERNAL*/ HIVE TABLE HIVE.SCH008.T00804; +/* ObjectUID = 1402470925633637409 */ + +--- SQL operation complete. +>>create table hive.`sch008`.`t00804like` like `sch008`.`t00804`; + +--- SQL operation complete. +>>create external table hive.`sch008`.`t00804like2` like `sch008`.`t00804`; + +--- SQL operation complete. +>>showddl hive.sch008.t00804like; + +/* Hive DDL */ +CREATE TABLE HIVE.SCH008.T00804LIKE + ( + A int + ) + stored as textfile +; + +/* Trafodion DDL */ + +REGISTER /*INTERNAL*/ HIVE TABLE HIVE.SCH008.T00804LIKE; +/* ObjectUID = 1402470925633637693 */ + +--- SQL operation complete. +>>showddl hive.sch008.t00804like2; + +/* Hive DDL */ +CREATE EXTERNAL TABLE HIVE.SCH008.T00804LIKE2 + ( + A int + ) + stored as textfile +; + +/* Trafodion DDL */ + +REGISTER /*INTERNAL*/ HIVE TABLE HIVE.SCH008.T00804LIKE2; +/* ObjectUID = 1402470925633637856 */ + +--- SQL operation complete. +>>drop table if exists hive.sch008.`t00805like`; + +--- SQL operation complete. +>>drop table if exists hive.sch008.`t00805like2`; + +--- SQL operation complete. +>> +>>-- delimited names using traf " as delimiter. +>>create table hive."sch008"."t00805" (`a` int); + +--- SQL operation complete. +>>showddl hive.sch008.t00805; + +/* Hive DDL */ +CREATE TABLE HIVE.SCH008.T00805 + ( + A int + ) + stored as textfile +; + +/* Trafodion DDL */ + +REGISTER /*INTERNAL*/ HIVE TABLE HIVE.SCH008.T00805; +/* ObjectUID = 1402470925633638566 */ + +--- SQL operation complete. +>> +>>-- create external hive table LIKE a traf table +>>drop table if exists hive.sch008.columns; + +--- SQL operation complete. +>>create external hive table hive.sch008.columns like trafodion."_MD_".columns ++> with hive options 'stored as sequencefile'; + +--- SQL operation complete. +>>create external hive table if not exists hive.sch008.columns like trafodion."_MD_".columns ++> with hive options 'stored as sequencefile'; + +--- SQL operation complete. +>>showddl hive.sch008.columns; + +/* Hive DDL */ +CREATE EXTERNAL TABLE HIVE.SCH008.COLUMNS + ( + OBJECT_UID bigint + , COLUMN_NAME varchar(256) + , COLUMN_NUMBER int + , COLUMN_CLASS char(2) + , FS_DATA_TYPE int + , SQL_DATA_TYPE char(32) + , COLUMN_SIZE int + , COLUMN_PRECISION int + , COLUMN_SCALE int + , DATETIME_START_FIELD int + , DATETIME_END_FIELD int + , IS_UPSHIFTED char(2) + , COLUMN_FLAGS int + , NULLABLE int + , CHARACTER_SET char(40) + , DEFAULT_CLASS int + , DEFAULT_VALUE varchar(1024) + , COLUMN_HEADING varchar(256) + , HBASE_COL_FAMILY varchar(40) + , HBASE_COL_QUALIFIER varchar(40) + , DIRECTION char(2) + , IS_OPTIONAL char(2) + , FLAGS bigint + ) + stored as sequencefile +; + +/* Trafodion DDL */ + +REGISTER /*INTERNAL*/ HIVE TABLE HIVE.SCH008.COLUMNS; +/* ObjectUID = 1402470925633639085 */ + +--- SQL operation complete. +>>-- create managed hive table LIKE traf table +>>drop table if exists hive.sch008.columns; + +--- SQL operation complete. +>>create hive table hive.sch008.columns like trafodion."_MD_".columns ++> with hive options 'stored as sequencefile'; + +--- SQL operation complete. +>>showddl hive.sch008.columns; + +/* Hive DDL */ +CREATE TABLE HIVE.SCH008.COLUMNS + ( + OBJECT_UID bigint + , COLUMN_NAME varchar(256) + , COLUMN_NUMBER int + , COLUMN_CLASS char(2) + , FS_DATA_TYPE int + , SQL_DATA_TYPE char(32) + , COLUMN_SIZE int + , COLUMN_PRECISION int + , COLUMN_SCALE int + , DATETIME_START_FIELD int + , DATETIME_END_FIELD int + , IS_UPSHIFTED char(2) + , COLUMN_FLAGS int + , NULLABLE int + , CHARACTER_SET char(40) + , DEFAULT_CLASS int + , DEFAULT_VALUE varchar(1024) + , COLUMN_HEADING varchar(256) + , HBASE_COL_FAMILY varchar(40) + , HBASE_COL_QUALIFIER varchar(40) + , DIRECTION char(2) + , IS_OPTIONAL char(2) + , FLAGS bigint + ) + stored as sequencefile +; + +/* Trafodion DDL */ + +REGISTER /*INTERNAL*/ HIVE TABLE HIVE.SCH008.COLUMNS; +/* ObjectUID = 1402470925633639718 */ + +--- SQL operation complete. +>> +>>-- create and drop hive table and external table +>>drop table if exists t00806; + +--- SQL operation complete. +>>create table t00806 (a int, b string); + +--- SQL operation complete. +>>showddl t00806; + +/* Hive DDL */ +CREATE TABLE HIVE.SCH008.T00806 + ( + A int + , B string + ) + stored as textfile +; + +/* Trafodion DDL */ + +REGISTER /*INTERNAL*/ HIVE TABLE HIVE.SCH008.T00806; +/* ObjectUID = 1402470925633640194 */ + +--- SQL operation complete. +>>create external table t00806 for t00806; + +--- SQL operation complete. +>>showddl t00806; + +/* Hive DDL */ +CREATE TABLE HIVE.SCH008.T00806 + ( + A int + , B string + ) + stored as textfile +; + +/* Trafodion DDL */ + +REGISTER /*INTERNAL*/ HIVE TABLE HIVE.SCH008.T00806; +/* ObjectUID = 1402470925633640194 */ + +CREATE EXTERNAL TABLE T00806 + FOR HIVE.SCH008.T00806 +; + +--- SQL operation complete. +>>drop table if exists t00806; + +--- SQL operation complete. +>>showddl t00806; + +*** ERROR[1388] Object HIVE.SCH008.T00806 does not exist in Hive Metadata. + +*** ERROR[8822] The statement was not prepared. + +>>select object_uid from trafodion."_MD_".objects where object_name = 'T00806'; + +--- 0 row(s) selected. +>> +>>-- cleanup hive table +>>drop table if exists t00806; + +--- SQL operation complete. +>>create table t00806 (a int, b string); + +--- SQL operation complete. +>>showddl t00806; + +/* Hive DDL */ +CREATE TABLE HIVE.SCH008.T00806 + ( + A int + , B string + ) + stored as textfile +; + +/* Trafodion DDL */ + +REGISTER /*INTERNAL*/ HIVE TABLE HIVE.SCH008.T00806; +/* ObjectUID = 1402470925633643445 */ + +--- SQL operation complete. +>>create external table t00806 for t00806; + +--- SQL operation complete. +>>showddl t00806; + +/* Hive DDL */ +CREATE TABLE HIVE.SCH008.T00806 + ( + A int + , B string + ) + stored as textfile +; + +/* Trafodion DDL */ + +REGISTER /*INTERNAL*/ HIVE TABLE HIVE.SCH008.T00806; +/* ObjectUID = 1402470925633643445 */ + +CREATE EXTERNAL TABLE T00806 + FOR HIVE.SCH008.T00806 +; + +--- SQL operation complete. +>>cleanup table t00806; + +--- SQL operation complete. +>>showddl t00806; + +*** ERROR[1388] Object HIVE.SCH008.T00806 does not exist in Hive Metadata. + +*** ERROR[8822] The statement was not prepared. + +>>select trim(schema_name) || '.' || trim(object_name) from trafodion."_MD_".objects where object_name = 'T00806'; + +--- 0 row(s) selected. +>>drop table if exists t00806; + +--- SQL operation complete. +>>create table t00806 (a int, b string); + +--- SQL operation complete. +>>create external table t00806 for t00806; + +--- SQL operation complete. +>>process hive ddl 'drop table sch008.t00806'; + +--- SQL operation complete. +>>showddl t00806; + +*** ERROR[4262] Object HIVE.SCH008.T00806 has invalid state and cannot be accessed. It has an external table but the correponding hive table does not exist. Use 'cleanup table' command to cleanup this table. + +*** ERROR[8822] The statement was not prepared. + +>>select trim(schema_name) || '.' || trim(object_name) from trafodion."_MD_".objects where object_name = 'T00806'; + +(EXPR) +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + +SCH008.T00806 +_HV_SCH008_.T00806 + +--- 2 row(s) selected. +>>cleanup table t00806; + +--- SQL operation complete. +>>showddl t00806; + +*** ERROR[1388] Object HIVE.SCH008.T00806 does not exist in Hive Metadata. + +*** ERROR[8822] The statement was not prepared. + +>>select trim(schema_name) || '.' || trim(object_name) from trafodion."_MD_".objects where object_name = 'T00806'; + +--- 0 row(s) selected. +>> +>>-- TRUNCATE TABLE tests +>>-- hive managed table +>>create table if not exists t00807 (a int, b int); + +--- SQL operation complete. +>>showddl t00807; + +/* Hive DDL */ +CREATE TABLE HIVE.SCH008.T00807 + ( + A int + , B int + ) + stored as textfile +; + +/* Trafodion DDL */ + +REGISTER /*INTERNAL*/ HIVE TABLE HIVE.SCH008.T00807; +/* ObjectUID = 1402470925633648613 */ + +--- SQL operation complete. +>>insert into t00807 values (1,2), (3,4); + +--- 2 row(s) inserted. +>>select * from t00807; + +A B +----------- ----------- + + 1 2 + 3 4 + +--- 2 row(s) selected. +>>truncate table t00807; + +--- SQL operation complete. +>>select * from t00807; + +--- 0 row(s) selected. +>>drop table t00807; + +--- SQL operation complete. +>>-- next truncate should return error +>>truncate table t00807; + +*** ERROR[1388] Table sch008.t00807 does not exist in Hive Metadata. + +--- SQL operation failed with errors. +>>truncate table if exists t00807; + +--- SQL operation complete. +>> +>>-- hive external table +>>drop table t00807; + +*** ERROR[1388] Table sch008.t00807 does not exist in Hive Metadata. + +--- SQL operation failed with errors. +>>create external table if not exists t00807 (a int, b int); + +--- SQL operation complete. +>>showddl t00807; + +/* Hive DDL */ +CREATE EXTERNAL TABLE HIVE.SCH008.T00807 + ( + A int + , B int + ) + stored as textfile +; + +/* Trafodion DDL */ + +REGISTER /*INTERNAL*/ HIVE TABLE HIVE.SCH008.T00807; +/* ObjectUID = 1402470925633649823 */ + +--- SQL operation complete. +>>insert into t00807 values (1,2), (3,4); + +--- 2 row(s) inserted. +>>select * from t00807; + +A B +----------- ----------- + + 1 2 + 3 4 + +--- 2 row(s) selected. +>>truncate table t00807; + +--- SQL operation complete. +>>select * from t00807; + +--- 0 row(s) selected. +>>drop table t00807; + +--- SQL operation complete. +>> +>>-- hive partitioned external table. +>>-- hive partitioned table insert/select not yet supported from traf interface. +>>-- When supported, remove regrhive.ksh and do the insert/select from traf. +>>-- Also enabled select from t00807. +>>drop table t00807; + +*** ERROR[1388] Table sch008.t00807 does not exist in Hive Metadata. + +--- SQL operation failed with errors. +>>create external table if not exists t00807 (a int) partitioned by (b int); + +--- SQL operation complete. +>> +>>-- TBD: insert into sch008.t00807 values (1,2); +>>sh echo "insert into sch008.t00807 partition(b=2) values (1);" > TEST008_junk; +>>sh regrhive.ksh -f TEST008_junk; +>> +>>-- TBD: select * from t00807; +>>sh echo "select * from sch008.t00807;" > TEST008_junk; +>>sh regrhive.ksh -f TEST008_junk | tee -a LOG008; +t00807.a t00807.b +1 2 +>> +>>truncate table t00807; + +--- SQL operation complete. +>> +>>-- TBD: select * from t00807; +>>sh echo "select * from sch008.t00807;" > TEST008_junk; +>>sh regrhive.ksh -f TEST008_junk | tee -a LOG008; +t00807.a t00807.b +>> +>>drop table t00807; + +--- SQL operation complete. +>> +>> +>>-- explain on hive DDL +>>explain options 'f' drop table hive.hive.tnone; + +LC RC OP OPERATOR OPT DESCRIPTION CARD +---- ---- ---- -------------------- -------- -------------------- --------- + +1 . 2 root 1.00E+000 +. . 1 hive_ddl 1.00E+000 + +--- SQL operation complete. +>>explain options 'f' create table hive.hive.tnone (a bigint); + +LC RC OP OPERATOR OPT DESCRIPTION CARD +---- ---- ---- -------------------- -------- -------------------- --------- + +1 . 2 root 1.00E+000 +. . 1 hive_ddl 1.00E+000 + +--- SQL operation complete. +>> +>>-- cannot access hive objects if not DB__HIVEROLE. +>>-- should return error, SQL_USER4 not authorized. +>>log; +>>process hive ddl 'drop table tnotexists'; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>drop table hive.hive.tnotexists; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>> +>>exit; + +End of MXCI Session + +>> +>>-- grant/revoke to access hive objects +>>showddl role DB__HIVEROLE; + +CREATE ROLE "DB__HIVEROLE"; + +--- SQL operation complete. +>>grant role DB__HIVEROLE to SQL_USER4; + +--- SQL operation complete. +>>showddl role DB__HIVEROLE; + +CREATE ROLE "DB__HIVEROLE"; + +--- SQL operation complete. +>> +>>-- should not return unquthorized error. +>>log; +>>process hive ddl 'drop table tnotexists'; + +--- SQL operation complete. +>>drop table hive.hive.tnotexists; + +*** ERROR[1388] Table tnotexists does not exist in Hive Metadata. + +--- SQL operation failed with errors. +>> +>>exit; + +End of MXCI Session + +>> +>>revoke role DB__HIVEROLE from SQL_USER4; + +--- SQL operation complete. +>>showddl role DB__HIVEROLE; + +CREATE ROLE "DB__HIVEROLE"; + +--- SQL operation complete. +>> +>>-- should return error +>>log; +>>process hive ddl 'drop table tnotexists'; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>>drop table hive.hive.tnotexists; + +*** ERROR[1017] You are not authorized to perform this operation. + +--- SQL operation failed with errors. +>> +>>exit; + +End of MXCI Session + +>> +>>-- next alter should be passed on to hive. will return error from hive. +>>alter schema hive.sch008 abc; + +*** ERROR[1214] Error encountered when executing HiveQL statement "alter schema SCH008 abc". Details: org.apache.hive.service.cli.HiveSQLException: Error while compiling statement: FAILED: ParseException line 1:13 cannot recognize input near 'SCH008' 'abc' '<EOF>' in alter database statement + +--- SQL operation failed with errors. +>>alter database hive.sch008 abc; + +*** ERROR[1214] Error encountered when executing HiveQL statement "alter database SCH008 abc". Details: org.apache.hive.service.cli.HiveSQLException: Error while compiling statement: FAILED: ParseException line 1:15 cannot recognize input near 'SCH008' 'abc' '<EOF>' in alter database statement + +--- SQL operation failed with errors. +>> +>>-- error cases +>>set catalog hive; + +--- SQL operation complete. +>>drop table if exists sch008.temptab; + +--- SQL operation complete. +>> +>>-- next drop should return error but hive doesn't return error +>>-- on a nonexistent table +>>process hive ddl 'drop table sch008.temptab'; + +--- SQL operation complete. +>> +>>drop table if exists sch008.temptab; + +--- SQL operation complete. +>> +>>create table sch008.temptab (a int); + +--- SQL operation complete. +>> +>>-- next create should return error that table exists +>>create table sch008.temptab (a int); + +*** ERROR[1387] Table sch008.temptab already exists in Hive Metadata. + +--- SQL operation failed with errors. +>>create table if not exists sch008.temptab (a int); + +--- SQL operation complete. +>> +>>set schema hive.sch008; + +--- SQL operation complete. +>> +>>-- create table error. largeint is not a valid type. +>>create table temptab2(a largeint); + +*** ERROR[1214] Error encountered when executing HiveQL statement "create table SCH008.TEMPTAB2(a largeint)". Details: org.apache.hive.service.cli.HiveSQLException: Error while compiling statement: FAILED: ParseException line 1:31 cannot recognize input near 'largeint' ')' '<EOF>' in column type + +--- SQL operation failed with errors. +>> +>>-- return error: schema not empty +>>drop schema sch008; + +*** ERROR[1214] Error encountered when executing HiveQL statement "drop schema SCH008". Details: java.sql.SQLException: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. InvalidOperationException(message:Database sch008 is not empty. One or more tables exist.) + +--- SQL operation failed with errors. +>> +>>-- use of backquote should return an error for non-hive ddl +>>create table trafodion.sch.`temp` (a int); + +*** ERROR[15001] A syntax error occurred at or before: +create table trafodion.sch.`temp` (a int); + ^ (35 characters from start of SQL statement) + +*** ERROR[8822] The statement was not prepared. + +>>create external table trafodion.sch.`temp` (a int); + +*** ERROR[15001] A syntax error occurred at or before: +create external table trafodion.sch.`temp` (a int); + ^ (44 characters from start of SQL statement) + +*** ERROR[8822] The statement was not prepared. + +>>create table hive.hive.`temp` for hive.hive.`temp`; + +*** ERROR[1214] Error encountered when executing HiveQL statement "create table `default`.temp for hive.hive.`temp`". Details: org.apache.hive.service.cli.HiveSQLException: Error while compiling statement: FAILED: ParseException line 1:28 cannot recognize input near 'for' 'hive' '.' in create table statement + +--- SQL operation failed with errors. +>>create external table hive.hive.`temp` for hive.hive.`temp`; + +*** ERROR[1214] Error encountered when executing HiveQL statement "create external table `default`.temp for hive.hive.`temp`". Details: org.apache.hive.service.cli.HiveSQLException: Error while compiling statement: FAILED: ParseException line 1:37 cannot recognize input near 'for' 'hive' '.' in create table statement + +--- SQL operation failed with errors. +>>create table hive.hive.`temp` like hive.hive.`temp`; + +*** ERROR[1214] Error encountered when executing HiveQL statement "create table `default`.temp like hive.hive.`temp`". Details: org.apache.hive.service.cli.HiveSQLException: Error while compiling statement: FAILED: ParseException line 1:42 missing EOF at '.' near 'hive' + +--- SQL operation failed with errors. +>>select * from `dual`; + +*** ERROR[15001] A syntax error occurred at or before: +select * from `dual`; + ^ (20 characters from start of SQL statement) + +*** ERROR[8822] The statement was not prepared. + +>> +>>-- cannot use keyword database to create/drop traf schemas +>>create database trafodion.temp; + +*** ERROR[15001] A syntax error occurred at or before: +create database trafodion.temp; + ^ (31 characters from start of SQL statement) + +*** ERROR[8822] The statement was not prepared. + +>>drop database trafodion.temp; + +*** ERROR[15001] A syntax error occurred at or before: +drop database trafodion.temp; + ^ (29 characters from start of SQL statement) + +*** ERROR[8822] The statement was not prepared. + +>>alter database trafodion.temp; + +*** ERROR[15001] A syntax error occurred at or before: +alter database trafodion.temp; + ^ (30 characters from start of SQL statement) + +*** ERROR[8822] The statement was not prepared. + +>> +>>-- return error: unsupported operation from 'process hive ddl' stmt +>>process hive ddl 'grnt abc'; + +*** ERROR[3242] This statement is not supported. Reason: Specified DDL operation cannot be executed directly by hive. + +--- SQL operation failed with errors. +>> +>>-- return error: ctas on hive cannot use traf in the SELECT query +>>create table hive.hive.temptab2 as select * from trafodion.sch.temp; + +*** ERROR[1214] Error encountered when executing HiveQL statement "create table `default`.TEMPTAB2 as select * from trafodion.sch.temp". Details: org.apache.hive.service.cli.HiveSQLException: Error while compiling statement: FAILED: ParseException line 1:62 cannot recognize input near '.' 'temp' '<EOF>' in table source + +--- SQL operation failed with errors. +>> +>>-- cannot use 'with hive options' with non-hive tables +>>create table trafodion.seabase.temp like trafodion."_MD_".objects ++> with hive options 'abc'; + +*** ERROR[3242] This statement is not supported. Reason: Hive options cannot be specified for this table. + +--- SQL operation failed with errors. +>> +>>-- target table must be a hive table +>>create external hive table trafodion.seabase.temp like trafodion."_MD_".objects; + +*** ERROR[3242] This statement is not supported. Reason: LIKE target table must be a hive table. + +*** ERROR[8822] The statement was not prepared. + +>> +>>-- source table must be a trafodion table +>>create external hive table hive.hive.temp like hive.hive.temp2 ++> with hive options 'abc'; + +*** ERROR[3242] This statement is not supported. Reason: LIKE source table must be a trafodion table. + +*** ERROR[8822] The statement was not prepared. + +>> +>>-- ctas tgt cannot be an external hive table +>>create external hive table hive.hive.temp as select * from trafodion."_MD_".objects; + +*** ERROR[3242] This statement is not supported. Reason: 'create hive table ... as ...' construct is not allowed. + +*** ERROR[8822] The statement was not prepared. + +>> +>>-- like option must be specified +>>create external hive table hive.hive.temp (a int); + +*** ERROR[3242] This statement is not supported. Reason: LIKE clause must be specified to create this Hive table. + +*** ERROR[8822] The statement was not prepared. + +>> +>>drop database hive.sch008 cascade; + +--- SQL operation complete. +>>drop schema if exists hive.sch008 cascade; + +--- SQL operation complete. +>>showddl schema hive.sch008; + +*** ERROR[1003] Schema HIVE.SCH008 does not exist. + +--- SQL operation failed with errors. +>> +>>drop table hive.`default`.t00804; + +--- SQL operation complete. +>>drop table hive.sch008.temptab; + +*** ERROR[1388] Table sch008.temptab does not exist in Hive Metadata. + +--- SQL operation failed with errors. +>> +>>revoke role DB__HIVEROLE from SQL_USER4; + +*** ERROR[1018] Grant of role or privilege DB__HIVEROLE from DB__ROOT to SQL_USER4 not found, revoke request ignored. + +--- SQL operation failed with errors. +>> +>>log; http://git-wip-us.apache.org/repos/asf/trafodion/blob/6f490daf/core/sql/regress/hive/EXPECTED009 ---------------------------------------------------------------------- diff --git a/core/sql/regress/hive/EXPECTED009 b/core/sql/regress/hive/EXPECTED009 index f89d8ef..fad737c 100644 --- a/core/sql/regress/hive/EXPECTED009 +++ b/core/sql/regress/hive/EXPECTED009 @@ -17,7 +17,7 @@ >>-- 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 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 @@ -135,16 +135,16 @@ CREATE EXTERNAL TABLE 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 + 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; @@ -152,16 +152,16 @@ A B C 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 + 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. >> @@ -214,7 +214,7 @@ A B C >>invoke t009hivecust1; -- Definition of Trafodion table TRAFODION.HIVE_T009.T009HIVECUST1 --- Definition current Tue Apr 10 12:50:07 2018 +-- Definition current Thu May 24 14:49:49 2018 ( SYSKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE @@ -263,7 +263,7 @@ A B C >>invoke t009hivecust2; -- Definition of Trafodion table TRAFODION.HIVE_T009.T009HIVECUST2 --- Definition current Tue Apr 10 12:50:20 2018 +-- Definition current Thu May 24 14:50:05 2018 ( SYSKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE @@ -346,7 +346,7 @@ A B C >> >>-- 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; +>>sh sqlci -i"$REGRTSTDIR/TEST009_b.hive.sql" &> $REGRRUNDIR/LOG009_b.hive.log; >> >>-- should fail - column mismatch >>select count(*) from hive.sch_t009.t009t1; @@ -367,16 +367,16 @@ A B C 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 + 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. >> @@ -433,8 +433,8 @@ T009T2 --- SQL operation complete. >>invoke hive.sch_t009.t009t1; --- Definition of hive table T009T1 --- Definition current Tue Apr 10 12:51:54 2018 +-- Definition of hive table HIVE.SCH_T009.T009T1 +-- Definition current Thu May 24 14:51:34 2018 ( A INT @@ -516,7 +516,7 @@ ROW_ID COLS >>invoke bblike1; -- Definition of Trafodion table TRAFODION.HIVE_T009.BBLIKE1 --- Definition current Tue Apr 10 12:52:10 2018 +-- Definition current Thu May 24 14:51:53 2018 ( ROW_ID VARCHAR(100) CHARACTER SET ISO88591 @@ -533,7 +533,7 @@ ROW_ID COLS >>invoke bblike2; -- Definition of Trafodion table TRAFODION.HIVE_T009.BBLIKE2 --- Definition current Tue Apr 10 12:52:16 2018 +-- Definition current Thu May 24 14:52:01 2018 ( ROW_ID VARCHAR(100) CHARACTER SET ISO88591 @@ -571,8 +571,8 @@ ROW_ID COLS --- SQL operation complete. >>invoke hive.hive.store_sales; --- Definition of hive table STORE_SALES --- Definition current Tue Apr 10 12:52:22 2018 +-- Definition of hive table HIVE.HIVE.STORE_SALES +-- Definition current Thu May 24 14:52:08 2018 ( SS_SOLD_DATE_SK INT @@ -654,6 +654,7 @@ DESCRIPTION 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, @@ -931,8 +932,8 @@ DESCRIPTION --- SQL operation complete. >>invoke hive.hive.date_dim; --- Definition of hive table DATE_DIM --- Definition current Tue Apr 10 12:52:29 2018 +-- Definition of hive table HIVE.HIVE.DATE_DIM +-- Definition current Thu May 24 14:52:14 2018 ( D_DATE_SK INT @@ -981,7 +982,7 @@ DESCRIPTION >>showddl hive.hive.date_dim; /* Hive DDL */ -CREATE TABLE DATE_DIM +CREATE EXTERNAL TABLE HIVE.HIVE.DATE_DIM ( D_DATE_SK int , D_DATE_ID string @@ -1018,7 +1019,7 @@ CREATE TABLE DATE_DIM /* Trafodion DDL */ REGISTER /*INTERNAL*/ HIVE TABLE HIVE.HIVE.DATE_DIM; -/* ObjectUID = 4145713645956211204 */ +/* ObjectUID = 4592144177079350537 */ CREATE EXTERNAL TABLE DATE_DIM ( @@ -1068,8 +1069,6 @@ CREATE EXTERNAL TABLE 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; @@ -1117,6 +1116,7 @@ DESCRIPTION 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, @@ -1173,8 +1173,8 @@ DESCRIPTION --- SQL operation complete. >>invoke hive.hive.date_dim; --- Definition of hive table DATE_DIM --- Definition current Tue Apr 10 12:52:50 2018 +-- Definition of hive table HIVE.HIVE.DATE_DIM +-- Definition current Thu May 24 14:52:24 2018 ( D_DATE_SK INT @@ -1223,7 +1223,7 @@ DESCRIPTION >>showddl hive.hive.date_dim; /* Hive DDL */ -CREATE TABLE DATE_DIM +CREATE EXTERNAL TABLE HIVE.HIVE.DATE_DIM ( D_DATE_SK int , D_DATE_ID string @@ -1260,7 +1260,7 @@ CREATE TABLE DATE_DIM /* Trafodion DDL */ REGISTER /*INTERNAL*/ HIVE TABLE HIVE.HIVE.DATE_DIM; -/* ObjectUID = 4145713645956211204 */ +/* ObjectUID = 4592144177079350537 */ CREATE EXTERNAL TABLE DATE_DIM ( @@ -1310,6 +1310,8 @@ CREATE EXTERNAL TABLE 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;
