| >> |
| >>set schema hive.hive; |
| |
| --- SQL operation complete. |
| >> |
| >>set terminal_charset utf8; |
| >> |
| >>cqd AUTO_QUERY_RETRY_WARNINGS 'ON'; |
| |
| --- SQL operation complete. |
| >>cqd HIVE_MAX_STRING_LENGTH_IN_BYTES '25' ; |
| |
| --- SQL operation complete. |
| >>cqd mode_seahive 'ON'; |
| |
| --- SQL operation complete. |
| >>cqd HIST_ROWCOUNT_REQUIRING_STATS '50000'; |
| |
| --- SQL operation complete. |
| >> |
| >>------------------------------------------------------------ |
| >>-- Testing query plan invalidation in the compiler, but |
| >>-- not the executor. Perform DML/DDL operations on a |
| >>-- table and try re-executing the old plan as well as |
| >>-- getting a query cache hit and updating the changed |
| >>-- Hive and HDFS metadata |
| >>------------------------------------------------------------ |
| >>prepare s1 from |
| +> select c_preferred_cust_flag, |
| +> count(*) |
| +> from customer_ddl |
| +> group by 1 |
| +> order by 1 |
| +> ; |
| |
| --- SQL command prepared. |
| >>execute s1; |
| |
| --- 0 row(s) selected. |
| >>-- expect 0 rows |
| >> |
| >>prepare s1part from |
| +> -- selecting part col not supported right now |
| +> select --c_preferred_cust_flag, |
| +> count(*) |
| +> from customer_bp |
| +> --group by 1 |
| +> --order by 1 |
| +> ; |
| |
| --- SQL command prepared. |
| >>execute s1part; |
| |
| (EXPR) |
| -------------------- |
| |
| 0 |
| |
| --- 1 row(s) selected. |
| >>-- expect 0 rows |
| >> |
| >>-- insert some data and add one more partition |
| >>sh regrhive.ksh -v -f $REGRTSTDIR/TEST005_b.hive.sql; |
| >> |
| >>-- query cache hit, no validation at all |
| >> select c_preferred_cust_flag, |
| +> count(*) |
| +> from customer_ddl |
| +> group by 1 |
| +> order by 1 |
| +> ; |
| |
| *** 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 |
| |
| C_PREFERRED_CUST_FLAG (EXPR) |
| ------------------------- -------------------- |
| |
| 685 |
| N 9789 |
| Y 9525 |
| |
| --- 3 row(s) selected. |
| >> |
| >>-- vary query to avoid query cache hit |
| >>prepare s2 from |
| +> select c_preferred_cust_flag, |
| +> count(c_customer_sk) |
| +> from customer_ddl |
| +> group by 1 |
| +> order by 1 |
| +> ; |
| |
| --- SQL command prepared. |
| >> |
| >>prepare s2part from |
| +> -- selecting part col not supported right now |
| +> select --c_preferred_cust_flag, |
| +> count(*) |
| +> from customer_bp |
| +> --group by 1 |
| +> --order by 1 |
| +> ; |
| |
| --- SQL command prepared. |
| >>execute s1; |
| |
| *** 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 |
| |
| C_PREFERRED_CUST_FLAG (EXPR) |
| ------------------------- -------------------- |
| |
| 685 |
| N 9789 |
| Y 9525 |
| |
| --- 3 row(s) selected. |
| >>-- because we don't invalidate in the executor, |
| >>-- this should still return 0 rows |
| >> |
| >>execute s2; |
| |
| C_PREFERRED_CUST_FLAG (EXPR) |
| ------------------------- -------------------- |
| |
| 685 |
| N 9789 |
| Y 9525 |
| |
| --- 3 row(s) selected. |
| >>-- should get an NATable cache |
| >>-- hit, we should notice the change in the table |
| >>-- and return the correct result |
| >> |
| >>execute s1part; |
| |
| (EXPR) |
| -------------------- |
| |
| 0 |
| |
| --- 1 row(s) selected. |
| >>-- because we don't invalidate in the executor, |
| >>-- this should still return 0 rows |
| >> |
| >>execute s2part; |
| |
| (EXPR) |
| -------------------- |
| |
| 0 |
| |
| --- 1 row(s) selected. |
| >>-- although this should get an NATable cache |
| >>-- hit, we should notice the change in the table |
| >>-- and return the correct result |
| >> |
| >>insert into customer_temp |
| +>select * from customer |
| +>where c_customer_sk between 20000 and 39999; |
| |
| *** 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. |
| |
| --- 20000 row(s) inserted. |
| >> |
| >>select * from newtable; |
| |
| --- 0 row(s) selected. |
| >>-- no rows, but should know the new table |
| >>insert into newtable values ('abc'); |
| |
| --- 1 row(s) inserted. |
| >>select * from newtable; |
| |
| *** 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 |
| |
| A |
| ------------------------- |
| |
| abc |
| |
| --- 1 row(s) selected. |
| >>-- expect to see the row, but only because query cache is off |
| >> |
| >>insert into hiveregr5.newtable2 values ('xyz'); |
| |
| --- 1 row(s) inserted. |
| >>select * from hiveregr5.newtable2; |
| |
| A |
| ------------------------- |
| |
| xyz |
| |
| --- 1 row(s) selected. |
| >> |
| >>-- add a second partition to customer_bp |
| >>sh regrhive.ksh -v -f $REGRTSTDIR/TEST005_c.hive.sql; |
| >>-- add more files to customer_ddl |
| >>sh regrhadoop.ksh dfs -cp /user/trafodion/hive/exttables/customer_temp/* /user/trafodion/hive/exttables/customer_ddl; |
| >> |
| >>-- no query cache hit, but NATable cache hit |
| >>prepare s3 from |
| +> select count(*) |
| +> from customer_ddl |
| +> ; |
| |
| --- SQL command prepared. |
| >> |
| >>-- no query cache hit, but NATable cache hit |
| >>prepare s3part from |
| +> -- selecting part col not supported right now |
| +> select --c_preferred_cust_flag, |
| +> count(c_customer_id) |
| +> from customer_bp |
| +> --group by 1 |
| +> --order by 1 |
| +> ; |
| |
| --- SQL command prepared. |
| >>execute s1; |
| |
| *** 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 |
| |
| C_PREFERRED_CUST_FLAG (EXPR) |
| ------------------------- -------------------- |
| |
| 1384 |
| N 19631 |
| Y 18984 |
| |
| --- 3 row(s) selected. |
| >>-- s1 should still return 0 rows - for now |
| >>execute s2; |
| |
| *** 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 |
| |
| C_PREFERRED_CUST_FLAG (EXPR) |
| ------------------------- -------------------- |
| |
| 1384 |
| N 19631 |
| Y 18984 |
| |
| --- 3 row(s) selected. |
| >>execute s3; |
| |
| (EXPR) |
| -------------------- |
| |
| 39999 |
| |
| --- 1 row(s) selected. |
| >>execute s1part; |
| |
| (EXPR) |
| -------------------- |
| |
| 0 |
| |
| --- 1 row(s) selected. |
| >>-- s1 should still return 0 rows - for now |
| >>execute s2part; |
| |
| (EXPR) |
| -------------------- |
| |
| 0 |
| |
| --- 1 row(s) selected. |
| >>execute s3part; |
| |
| (EXPR) |
| -------------------- |
| |
| 0 |
| |
| --- 1 row(s) selected. |
| >> |
| >>select a,b from newtable; |
| |
| --- 0 row(s) selected. |
| >>-- should return 0 rows |
| >> |
| >>insert into newtable values (1, 'def'); |
| |
| --- 1 row(s) inserted. |
| >>select a,b from newtable; |
| |
| *** 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 |
| |
| A B |
| ----------- ------------------------- |
| |
| 1 def |
| |
| --- 1 row(s) selected. |
| >> |
| >>-- overwrite the table with auto-generated partitions |
| >>sh regrhive.ksh -v -f $REGRTSTDIR/TEST005_d.hive.sql; |
| >> |
| >>prepare s4 from |
| +> select c_preferred_cust_flag, |
| +> count(*) |
| +> from customer_ddl |
| +> group by 1 |
| +> order by 1 |
| +> ; |
| |
| --- SQL command prepared. |
| >>prepare s4part from |
| +> -- selecting part col not supported right now |
| +> select --c_preferred_cust_flag, |
| +> count(*) |
| +> from customer_bp |
| +> --group by 1 |
| +> --order by 1 |
| +> ; |
| |
| --- SQL command prepared. |
| >>execute s2; |
| |
| C_PREFERRED_CUST_FLAG (EXPR) |
| ------------------------- -------------------- |
| |
| 1384 |
| N 19631 |
| Y 18984 |
| |
| --- 3 row(s) selected. |
| >>execute s4; |
| |
| C_PREFERRED_CUST_FLAG (EXPR) |
| ------------------------- -------------------- |
| |
| 1384 |
| N 19631 |
| Y 18984 |
| |
| --- 3 row(s) selected. |
| >>execute s2part; |
| |
| (EXPR) |
| -------------------- |
| |
| 0 |
| |
| --- 1 row(s) selected. |
| >>execute s4part; |
| |
| (EXPR) |
| -------------------- |
| |
| 0 |
| |
| --- 1 row(s) selected. |
| >>select count(*) from tbl_utf8; |
| |
| (EXPR) |
| -------------------- |
| |
| 75 |
| |
| --- 1 row(s) selected. |
| >>select * from tbl_utf8 where id between 8 and 12; |
| |
| ID CHAPTER ENGLISH TRANSLATOR |
| ----------- ------------------------- ------------------------- ------------------------- |
| |
| 8 Shin fukatoku 心不可 The Mind Cannot Be Got (Bielefeldt) |
| 9 Kobutsushin 古佛心 The Old Buddha Mind (Bielefeldt) |
| 10 Daigo 大悟 Great Awakening (Bielefeldt) |
| 11 Zazen gi 坐禪儀 Principles of Zazen (Bielefeldt) |
| 12 Zazen shin 坐禪箴 Lancet of Zazen (Bielefeldt) |
| |
| --- 5 row(s) selected. |
| >>select * from tbl_utf8 where chapter like '%三%'; |
| |
| ID CHAPTER ENGLISH TRANSLATOR |
| ----------- ------------------------- ------------------------- ------------------------- |
| |
| 13 Kaiin zanmai 海印三昧 The Ocean Seal Samadhi (Bielefeldt & Radich) |
| 41 Sangai yuishin 三界唯 The Three Realms Are Only (Weinstein) |
| 66 Zanmai ō zanmai 三昧 The King of Samadhis Sama (Bielefeldt) |
| 69 Jishō zanmai 自證三 The Samadhi of Self Verif (Bielefeldt) |
| |
| --- 4 row(s) selected. |
| >>select * from tbl_utf8 where chapter like '%海印_昧%'; |
| |
| ID CHAPTER ENGLISH TRANSLATOR |
| ----------- ------------------------- ------------------------- ------------------------- |
| |
| 13 Kaiin zanmai 海印三昧 The Ocean Seal Samadhi (Bielefeldt & Radich) |
| |
| --- 1 row(s) selected. |
| >> |
| >>insert into tbl_utf8_temp |
| +>select * from tbl_utf8; |
| |
| --- 75 row(s) inserted. |
| >> |
| >>select count(*) from tbl_utf8_temp; |
| |
| (EXPR) |
| -------------------- |
| |
| 75 |
| |
| --- 1 row(s) selected. |
| >>select * from tbl_utf8_temp where id between 8 and 12; |
| |
| ID CHAPTER ENGLISH TRANSLATOR |
| ----------- ------------------------- ------------------------- ------------------------- |
| |
| 8 Shin fukatoku 心不可 The Mind Cannot Be Got (Bielefeldt) |
| 9 Kobutsushin 古佛心 The Old Buddha Mind (Bielefeldt) |
| 10 Daigo 大悟 Great Awakening (Bielefeldt) |
| 11 Zazen gi 坐禪儀 Principles of Zazen (Bielefeldt) |
| 12 Zazen shin 坐禪箴 Lancet of Zazen (Bielefeldt) |
| |
| --- 5 row(s) selected. |
| >>select * from tbl_utf8_temp where chapter like '%海印_昧%'; |
| |
| ID CHAPTER ENGLISH TRANSLATOR |
| ----------- ------------------------- ------------------------- ------------------------- |
| |
| 13 Kaiin zanmai 海印三昧 The Ocean Seal Samadhi (Bielefeldt & Radich) |
| |
| --- 1 row(s) selected. |
| >> |
| >>select count(*) from tbl_utf8p; |
| |
| (EXPR) |
| -------------------- |
| |
| 0 |
| |
| --- 1 row(s) selected. |
| >>select * from tbl_utf8p where id between 8 and 12; |
| |
| --- 0 row(s) selected. |
| >>select * from tbl_utf8p where chapter like '%海印_昧%'; |
| |
| --- 0 row(s) selected. |
| >> |
| >>select * from tbl_type; |
| |
| TINT SM I BIG STR F D T DT VC D10 D18 D181 D30 |
| ---- ------ ----------- -------------------- ------------------------- --------------- ------------------------- -------------------------- ---------- ---------------------------------------- -------------------- ------------ -------------------- ------------------------------- |
| |
| 101 202 203 204 two hundred 2.0000000E+002 2.00000000000000000E+002 2022-02-02 22:22:22.222222 2022-02-02 varchar 1234567890 123456.11 12345 11111111111111111111111111111 |
| |
| --- 1 row(s) selected. |
| >>insert into tbl_type_temp select * from tbl_type; |
| |
| --- 1 row(s) inserted. |
| >>select * from tbl_type_temp; |
| |
| TINT SM I BIG STR F D T DT VC D10 D18 D181 D30 |
| ---- ------ ----------- -------------------- ------------------------- --------------- ------------------------- -------------------------- ---------- ---------------------------------------- -------------------- ------------ -------------------- ------------------------------- |
| |
| 101 202 203 204 two hundred 2.0000000E+002 2.00000000000000000E+002 2022-02-02 22:22:22.222222 2022-02-02 varchar 1234567890 123456.11 12345 11111111111111111111111111111 |
| |
| --- 1 row(s) selected. |
| >> |
| >> |
| >>cqd HIVE_FILE_CHARSET 'GBK'; |
| |
| --- SQL operation complete. |
| >>select c1, CONVERTTOHEX(c2) from tbl_gbk; |
| |
| C1 (EXPR) |
| ----------- -------------------------------------------------- |
| |
| 3 EC8B90EC978E |
| 5 EC8B90EC978E |
| 2 EC8B90EC978E |
| 4 EC8B90EC978E |
| 6 EC8B90EC978E |
| 7 EC8B90EC978E |
| 8 EC8B90EC978E |
| 3 ECBB93EB9F8FECAB97EB9B91 |
| 2 ECBB93EB9F8FECAB97EB9B91 |
| 6 ECBB93EB9F8FECAB97EB9B91 |
| 19 ECBB93EB9F8FECAB97EB9B91 |
| 8 ECBB93EB9F8FECAB97EB9B91 |
| |
| --- 12 row(s) selected. |
| >>cqd HIVE_FILE_CHARSET reset; |
| |
| --- SQL operation complete. |
| >> |
| >>cqd HIVE_SCAN_SPECIAL_MODE '1'; |
| |
| --- SQL operation complete. |
| >>select * from tbl_dos; |
| |
| C1 C2 C3 C4 |
| ----------- ----------- ----------- ------------------------- |
| |
| 0 39478 8147 2008-07-17 |
| 1 21944 8327 2005-05-12 |
| 2 32730 9999 2000-11-05 |
| 3 19653 5727 2005-06-24 |
| 4 67794 6012 2008-07-01 |
| 5 93265 5823 2012-06-26 |
| 6 28219 909 2009-04-26 |
| 7 23967 8290 2006-02-21 |
| 8 24265 8663 2006-10-06 |
| 9 70273 3363 2001-03-17 |
| |
| --- 10 row(s) selected. |
| >>cqd HIVE_SCAN_SPECIAL_MODE reset; |
| |
| --- SQL operation complete. |
| >>drop table if exists trafodion.seabase.tbl_dos_num; |
| |
| --- SQL operation complete. |
| >>create table trafodion.seabase.tbl_dos_num (c1 int, c2 int); |
| |
| --- SQL operation complete. |
| >>load with NO OUTPUT into trafodion.seabase.tbl_dos_num select * from tbl_dos_num; |
| |
| *** ERROR[8413] The string argument contains characters that cannot be converted. Source data(in hex): 33393437380d |
| |
| --- 0 row(s) loaded. |
| >>cqd HIVE_SCAN_SPECIAL_MODE '1'; |
| |
| --- SQL operation complete. |
| >>load with no output into trafodion.seabase.tbl_dos_num select * from tbl_dos_num; |
| |
| --- 10 row(s) loaded. |
| >>select * from trafodion.seabase.tbl_dos_num; |
| |
| C1 C2 |
| ----------- ----------- |
| |
| 0 39478 |
| 1 21944 |
| 2 32730 |
| 3 19653 |
| 4 67794 |
| 5 93265 |
| 6 ? |
| 7 ? |
| 8 ? |
| 9 17 |
| |
| --- 10 row(s) selected. |
| >>cqd HIVE_SCAN_SPECIAL_MODE reset; |
| |
| --- SQL operation complete. |
| >>select * from tbl_bad; |
| |
| *** ERROR[8413] The string argument contains characters that cannot be converted. Source data(in hex): 61 |
| |
| --- 0 row(s) selected. |
| >>cqd HIVE_SCAN_SPECIAL_MODE '2'; |
| |
| --- SQL operation complete. |
| >>select * from tbl_bad; |
| |
| C1 C2 C3 C4 C5 C6 C7 C8 |
| ----------- -------------------- ------------------------- --------------- ------ -------------------------- ------------------------- ---- |
| |
| ? ? c ? ? ? ? ? |
| ? ? c ? ? 2017-01-01 10:10:10.000000 1.01000000000000000E+000 1 |
| ? ? ? ? ? ? ? |
| 1 1 averylongstring -1.0000000E+000 0 2017-01-01 10:10:10.000000 1.00010000000000000E+002 1 |
| 2 2 good 1.1000000E+000 2 2017-01-01 10:10:10.000000 2.00000000000000000E+002 100 |
| 3 3 good 1.0000000E+000 2 2017-01-01 10:10:10.000000 2.10000000000000000E+002 10 |
| ? 4294967295 good 3.3999999E+038 ? 2017-01-01 10:10:10.000000 1.69999999999999968E+308 10 |
| 0 9999999999 bad ? ? ? ? ? |
| |
| --- 8 row(s) selected. |
| >>cqd HIVE_SCAN_SPECIAL_MODE reset; |
| |
| --- SQL operation complete. |
| >>drop table if exists trafodion.seabase.traf_tbl_bad; |
| |
| --- SQL operation complete. |
| >>create table trafodion.seabase.traf_tbl_bad ( |
| +>c1 int, |
| +>c2 largeint, |
| +>c3 varchar(25), |
| +>c4 real, |
| +>c5 smallint, |
| +>c6 timestamp(6), |
| +>c7 float(54), |
| +>c8 smallint |
| +>); |
| |
| --- SQL operation complete. |
| >>load with no output into trafodion.seabase.traf_tbl_bad select * from tbl_bad; |
| |
| *** ERROR[8413] The string argument contains characters that cannot be converted. Source data(in hex): 61 |
| |
| --- 0 row(s) loaded. |
| >>select count(*) from trafodion.seabase.traf_tbl_bad; |
| |
| (EXPR) |
| -------------------- |
| |
| 0 |
| |
| --- 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 |
| 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 |
| 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 |
| |
| --- 3 row(s) loaded. |
| >>select count(*) from trafodion.seabase.traf_tbl_bad; |
| |
| (EXPR) |
| -------------------- |
| |
| 3 |
| |
| --- 1 row(s) selected. |
| >>delete from trafodion.seabase.traf_tbl_bad ; |
| |
| --- 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 |
| 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 |
| 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 |
| |
| --- 3 row(s) loaded. |
| >>select count(*) from trafodion.seabase.traf_tbl_bad; |
| |
| (EXPR) |
| -------------------- |
| |
| 3 |
| |
| --- 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 |
| 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 |
| 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 |
| |
| --- 3 row(s) loaded. |
| >>select count(*) from trafodion.seabase.traf_tbl_bad; |
| |
| (EXPR) |
| -------------------- |
| |
| 6 |
| |
| --- 1 row(s) selected. |
| >>delete from trafodion.seabase.traf_tbl_bad ; |
| |
| --- 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 |
| |
| *** ERROR[8113] The maximum number of error rows is exceeded. |
| |
| --- 0 row(s) loaded. |
| >>select count(*) from trafodion.seabase.traf_tbl_bad; |
| |
| (EXPR) |
| -------------------- |
| |
| 0 |
| |
| --- 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 |
| |
| *** ERROR[8113] The maximum number of error rows is exceeded. |
| |
| --- 0 row(s) loaded. |
| >>select count(*) from trafodion.seabase.traf_tbl_bad; |
| |
| (EXPR) |
| -------------------- |
| |
| 0 |
| |
| --- 1 row(s) selected. |
| >>insert into trafodion.seabase.traf_tbl_bad select * from tbl_bad; |
| |
| *** ERROR[8413] The string argument contains characters that cannot be converted. Source data(in hex): 61 |
| |
| --- 0 row(s) inserted. |
| >>upsert into trafodion.seabase.traf_tbl_bad select * from tbl_bad; |
| |
| *** ERROR[8413] The string argument contains characters that cannot be converted. Source data(in hex): 61 |
| |
| --- 0 row(s) inserted. |
| >>cqd HIVE_SCAN_SPECIAL_MODE '2'; |
| |
| --- SQL operation complete. |
| >>load with no output into trafodion.seabase.traf_tbl_bad select * from tbl_bad; |
| |
| --- 8 row(s) loaded. |
| >>insert into trafodion.seabase.traf_tbl_bad select * from tbl_bad; |
| |
| --- 8 row(s) inserted. |
| >>upsert into trafodion.seabase.traf_tbl_bad select * from tbl_bad; |
| |
| --- 8 row(s) inserted. |
| >>cqd HIVE_SCAN_SPECIAL_MODE reset; |
| |
| --- SQL operation complete. |
| >>drop table if exists trafodion.seabase.traf_tbl_bad; |
| |
| --- SQL operation complete. |
| >>create table trafodion.seabase.traf_tbl_bad ( |
| +>c1 int not null, |
| +>c2 largeint not null, |
| +>c3 varchar(25), |
| +>c4 real, |
| +>c5 smallint, |
| +>c6 timestamp(6) not null, |
| +>c7 float(54) not null, |
| +>c8 smallint |
| +>); |
| |
| --- SQL operation complete. |
| >>cqd HIVE_SCAN_SPECIAL_MODE '2'; |
| |
| --- SQL operation complete. |
| >>insert into trafodion.seabase.traf_tbl_bad select * from tbl_bad; |
| |
| *** ERROR[8421] NULL cannot be assigned to a NOT NULL column. |
| |
| --- 0 row(s) inserted. |
| >>cqd HIVE_SCAN_SPECIAL_MODE reset; |
| |
| --- SQL operation complete. |
| >> |
| >>-- tests for hive timestamp mismatch check |
| >>cqd auto_query_retry_warnings 'ON'; |
| |
| --- SQL operation complete. |
| >> |
| >>process hive statement 'drop table thive'; |
| |
| --- SQL operation complete. |
| >>process hive statement 'create table thive(a int)'; |
| |
| --- SQL operation complete. |
| >> |
| >>select a from hive.hive.thive; |
| |
| --- 0 row(s) selected. |
| >> |
| >>sh echo "insert into thive values (1);" > TEST005_junk; |
| >>sh regrhive.ksh -f TEST005_junk; |
| >> |
| >>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 |
| |
| A |
| ----------- |
| |
| 1 |
| |
| --- 1 row(s) selected. |
| >>insert into hive.hive.thive values (2); |
| |
| --- 1 row(s) inserted. |
| >>select a from hive.hive.thive; |
| |
| A |
| ----------- |
| |
| 1 |
| 2 |
| |
| --- 2 row(s) selected. |
| >> |
| >>process hive statement 'drop table thive'; |
| |
| --- SQL operation complete. |
| >>process hive statement 'create table thive(a int, b int)'; |
| |
| --- SQL operation complete. |
| >> |
| >>sh echo "insert into thive values (1,2);" > TEST005_junk; |
| >>sh regrhive.ksh -f TEST005_junk; |
| >> |
| >>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 |
| ----------- |
| |
| 1 |
| |
| --- 1 row(s) selected. |
| >> |
| >>select * from hive.hive.thive; |
| |
| A B |
| ----------- ----------- |
| |
| 1 2 |
| |
| --- 1 row(s) selected. |
| >> |
| >>-- truncate of hive data |
| >>--cqd query_cache '0'; |
| >>truncate 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; |
| |
| A B |
| ----------- ----------- |
| |
| 10 20 |
| |
| --- 1 row(s) selected. |
| >>truncate hive.hive.thive; |
| |
| --- SQL operation complete. |
| >>truncate 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'; |
| |
| --- SQL operation complete. |
| >>process hive statement 'create table t005part(a int) partitioned by (b int, c int)'; |
| |
| --- SQL operation complete. |
| >> |
| >>sh echo "insert into t005part partition (b=10,c=11) values (5);" > TEST005_junk; |
| >>sh regrhive.ksh -f TEST005_junk; |
| >>sh echo "select * from t005part;" > TEST005_junk; |
| >>sh regrhive.ksh -f TEST005_junk | tee -a LOG005; |
| t005part.a t005part.b t005part.c |
| 5 10 11 |
| >> |
| >>truncate hive.hive.t005part; |
| |
| --- 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'); |
| |
| --- 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'); |
| |
| --- 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; |
| >>sh echo "insert into t005part partition (b=10,c=12) values (6);" > TEST005_junk; |
| >>sh regrhive.ksh -f TEST005_junk; |
| >>sh echo "select * from t005part;" > TEST005_junk; |
| >>sh regrhive.ksh -f TEST005_junk | tee -a LOG005; |
| t005part.a t005part.b t005part.c |
| 5 10 11 |
| 6 10 12 |
| >> |
| >>truncate hive.hive.t005part partition ('b=10','c=11'); |
| |
| --- 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 |
| 6 10 12 |
| >> |
| >>truncate 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 |
| >> |
| >>-- 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. |
| |
| *** ERROR[8035] Truncation of hive table failed. Reason: specified partition does not exist |
| |
| --- 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[8822] The statement was not prepared. |
| |
| >> |
| >>-- 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 |
| |
| ( |
| A SMALLINT |
| ) |
| /* stored as textfile */ |
| |
| --- SQL operation complete. |
| >>showddl hive.hive.thive_insert_smallint; |
| |
| /* Hive DDL */ |
| CREATE TABLE THIVE_INSERT_SMALLINT |
| ( |
| A smallint |
| ) |
| stored as textfile |
| ; |
| |
| /* Trafodion DDL */ |
| |
| --- SQL operation complete. |
| >> |
| >>truncate hive.hive.thive_insert_smallint; |
| |
| --- SQL operation complete. |
| >>cqd hive_insert_error_mode '0'; |
| |
| --- SQL operation complete. |
| >>insert into hive.hive.thive_insert_smallint select * from |
| +> (values (10), (11111111), (21), (22222222)); |
| |
| --- 4 row(s) inserted. |
| >>select * from hive.hive.thive_insert_smallint; |
| |
| A |
| ------ |
| |
| 10 |
| |
| *** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:VARCHAR(REC_BYTE_V_ASCII,8 BYTES,ISO88591) Source Value:11111111 to Target Type:SMALLINT SIGNED(REC_BIN16_SIGNED). |
| |
| --- 1 row(s) selected. |
| >> |
| >>truncate hive.hive.thive_insert_smallint; |
| |
| --- SQL operation complete. |
| >>cqd hive_insert_error_mode '1'; |
| |
| --- SQL operation complete. |
| >>insert into hive.hive.thive_insert_smallint select * from |
| +> (values (10), (11111111), (21), (22222222)); |
| |
| *** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:INTEGER SIGNED(MBIN32S) Source Value:11111111 Target Type:LARGEINT(IBIN64S) Max Target Value:32767. Instruction:RANGE_HIGH_S32S64 Operation:RANGE_HIGH. |
| |
| --- 0 row(s) inserted. |
| >>select * from hive.hive.thive_insert_smallint; |
| |
| --- 0 row(s) selected. |
| >> |
| >>truncate hive.hive.thive_insert_smallint; |
| |
| --- SQL operation complete. |
| >>cqd hive_insert_error_mode '2'; |
| |
| --- SQL operation complete. |
| >>insert into hive.hive.thive_insert_smallint select * from |
| +> (values (10), (11111111), (21), (22222222)); |
| |
| --- 2 row(s) inserted. |
| >>select * from hive.hive.thive_insert_smallint; |
| |
| A |
| ------ |
| |
| 10 |
| 21 |
| |
| --- 2 row(s) selected. |
| >> |
| >>truncate hive.hive.thive_insert_smallint; |
| |
| --- SQL operation complete. |
| >>cqd hive_insert_error_mode '3'; |
| |
| --- SQL operation complete. |
| >>insert into hive.hive.thive_insert_smallint select * from |
| +> (values (10), (11111111), (21), (22222222)); |
| |
| --- 4 row(s) inserted. |
| >>select * from hive.hive.thive_insert_smallint; |
| |
| A |
| ------ |
| |
| 10 |
| ? |
| 21 |
| ? |
| |
| --- 4 row(s) selected. |
| >> |
| >>cqd hive_max_string_length_in_bytes '2'; |
| |
| --- 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 |
| |
| ( |
| A VARCHAR(1 CHAR) CHARACTER SET UTF8 COLLATE |
| DEFAULT |
| , B VARCHAR(2 BYTES) CHARACTER SET UTF8 |
| COLLATE DEFAULT |
| ) |
| /* stored as textfile */ |
| |
| --- SQL operation complete. |
| >>showddl hive.hive.thive_insert_varchar; |
| |
| /* Hive DDL */ |
| CREATE TABLE THIVE_INSERT_VARCHAR |
| ( |
| A varchar(1) |
| , B string |
| ) |
| stored as textfile |
| ; |
| |
| /* Trafodion DDL */ |
| |
| --- SQL operation complete. |
| >>cqd hive_insert_error_mode '1'; |
| |
| --- SQL operation complete. |
| >>truncate hive.hive.thive_insert_varchar; |
| |
| --- SQL operation complete. |
| >>insert into hive.hive.thive_insert_varchar values ('abcddcba','efghijkl'); |
| |
| --- 1 row(s) inserted. |
| >> |
| >>cqd hive_max_string_length_in_bytes '20'; |
| |
| --- SQL operation complete. |
| >>select * from hive.hive.thive_insert_varchar; |
| |
| A B |
| ---- -------------------- |
| |
| abcd efghijkl |
| |
| --- 1 row(s) selected. |
| >> |
| >> |
| >>log; |
