http://git-wip-us.apache.org/repos/asf/trafodion/blob/ff339843/core/sql/regress/hive/EXPECTED030 ---------------------------------------------------------------------- diff --git a/core/sql/regress/hive/EXPECTED030 b/core/sql/regress/hive/EXPECTED030 deleted file mode 100644 index ad53d98..0000000 --- a/core/sql/regress/hive/EXPECTED030 +++ /dev/null @@ -1,3101 +0,0 @@ ->>obey TEST030(setup); ->>-------------------------------------------------------------------------- ->> ->>set schema hive.hive; - ---- SQL operation complete. ->>cqd HIVE_MAX_STRING_LENGTH_IN_BYTES '20' ; - ---- SQL operation complete. ->>cqd mode_seahive 'ON'; - ---- SQL operation complete. ->>cqd traf_enable_orc_format 'ON'; - ---- SQL operation complete. ->>cqd HIST_ROWCOUNT_REQUIRING_STATS '50000'; - ---- SQL operation complete. ->>cqd hive_use_ext_table_attrs 'ON'; - ---- SQL operation complete. ->>cqd hist_missing_stats_warning_level '0'; - ---- SQL operation complete. ->>cqd ORC_NJS_PROBES_THRESHOLD '1000000'; - ---- SQL operation complete. ->>cqd HIVE_MIN_NUM_ESPS_PER_DATANODE '0'; - ---- SQL operation complete. ->> ->>prepare explainIt from -+> select substring(cast(SEQ_NUM+100 as char(3)),2,2) s, -+> substring(operator,1,16) operator, -+> cast(LEFT_CHILD_SEQ_NUM as char(2)) lc, -+> cast(RIGHT_CHILD_SEQ_NUM as char(2)) rc, -+> substring -+> (substring(substring(tname from (1+locate('.',tname))),1,case locate(')',tname) when 0 then 0 else locate(')',substring(tname from (1+locate('.',tname))))-1 end), -+> (locate('.',substring(tname from (1+locate('.',tname)))))+1, -+> 10 -+> ) tab_name -+> from table (explain(NULL,'XX')) -+> order by 1 desc; - ---- SQL command prepared. ->> ->>obey TEST030(tests); ->>-------------------------------------------------------------------------- ->>-- ORC file metadata info ->>invoke hive.hive.store_orc; - --- Definition of hive table STORE_ORC -<<<<<<< HEAD --- Definition current Wed Nov 29 02:19:58 2017 -======= --- Definition current Sun Jun 11 11:09:19 2017 ->>>>>>> 1830668... Enhanced checktests changes. Jenkins usage not yet enabled. - - ( - S_STORE_SK INT - , S_STORE_ID VARCHAR(20 BYTES) CHARACTER SET UTF8 - COLLATE DEFAULT - , S_REC_START_DATE DATE - , S_REC_END_DATE DATE - , S_CLOSED_DATE_SK INT - , S_STORE_NAME VARCHAR(20 BYTES) CHARACTER SET UTF8 - COLLATE DEFAULT - , S_NUMBER_EMPLOYEES INT - , S_FLOOR_SPACE INT - , S_HOURS VARCHAR(20 BYTES) CHARACTER SET UTF8 - COLLATE DEFAULT - , S_MANAGER VARCHAR(20 BYTES) CHARACTER SET UTF8 - COLLATE DEFAULT - , S_MARKET_ID INT - , S_GEOGRAPHY_CLASS VARCHAR(20 BYTES) CHARACTER SET UTF8 - COLLATE DEFAULT - , S_MARKET_DESC VARCHAR(20 BYTES) CHARACTER SET UTF8 - COLLATE DEFAULT - , S_MARKET_MANAGER VARCHAR(20 BYTES) CHARACTER SET UTF8 - COLLATE DEFAULT - , S_DIVISION_ID INT - , S_DIVISION_NAME VARCHAR(20 BYTES) CHARACTER SET UTF8 - COLLATE DEFAULT - , S_COMPANY_ID INT - , S_COMPANY_NAME VARCHAR(20 BYTES) CHARACTER SET UTF8 - COLLATE DEFAULT - , S_STREET_NUMBER VARCHAR(20 BYTES) CHARACTER SET UTF8 - COLLATE DEFAULT - , S_STREET_NAME VARCHAR(20 BYTES) CHARACTER SET UTF8 - COLLATE DEFAULT - , S_STREET_TYPE VARCHAR(20 BYTES) CHARACTER SET UTF8 - COLLATE DEFAULT - , S_SUITE_NUMBER VARCHAR(20 BYTES) CHARACTER SET UTF8 - COLLATE DEFAULT - , S_CITY VARCHAR(20 BYTES) CHARACTER SET UTF8 - COLLATE DEFAULT - , S_COUNTY VARCHAR(20 BYTES) CHARACTER SET UTF8 - COLLATE DEFAULT - , S_STATE VARCHAR(20 BYTES) CHARACTER SET UTF8 - COLLATE DEFAULT - , S_ZIP VARCHAR(20 BYTES) CHARACTER SET UTF8 - COLLATE DEFAULT - , S_COUNTRY VARCHAR(20 BYTES) CHARACTER SET UTF8 - COLLATE DEFAULT - , S_GMT_OFFSET REAL - , S_TAX_PRECENTAGE REAL - ) - /* stored as orc */ - ---- SQL operation complete. ->> ->>-- select one row from ORC table ->>select [first 1] * from hive.hive.store_orc; - -S_STORE_SK S_STORE_ID S_REC_START_DATE S_REC_END_DATE S_CLOSED_DATE_SK S_STORE_NAME S_NUMBER_EMPLOYEES S_FLOOR_SPACE S_HOURS S_MANAGER S_MARKET_ID S_GEOGRAPHY_CLASS S_MARKET_DESC S_MARKET_MANAGER S_DIVISION_ID S_DIVISION_NAME S_COMPANY_ID S_COMPANY_NAME S_STREET_NUMBER S_STREET_NAME S_STREET_TYPE S_SUITE_NUMBER S_CITY S_COUNTY S_STATE S_ZIP S_COUNTRY S_GMT_OFFSET S_TAX_PRECENTAGE ------------ -------------------- ---------------- -------------- ---------------- -------------------- ------------------ ------------- -------------------- -------------------- ----------- -------------------- -------------------- -------------------- ------------- -------------------- ------------ -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------- ---------------- - - 1 AAAAAAAABAAAAAAA 1997-03-13 ? 2451189 ought 245 5250760 8AM-4PM William Ward 2 Unknown Enough high areas st Charles Bartley 1 Unknown 1 Unknown 767 Spring Wy Suite 250 Midway Williamson County TN 31904 United States -5.0000000E+000 2.9999999E-002 - ---- 1 row(s) selected. ->> ->>-- select all rows from ORC table ->>select * from hive.hive.store_orc order by s_store_sk; - -S_STORE_SK S_STORE_ID S_REC_START_DATE S_REC_END_DATE S_CLOSED_DATE_SK S_STORE_NAME S_NUMBER_EMPLOYEES S_FLOOR_SPACE S_HOURS S_MANAGER S_MARKET_ID S_GEOGRAPHY_CLASS S_MARKET_DESC S_MARKET_MANAGER S_DIVISION_ID S_DIVISION_NAME S_COMPANY_ID S_COMPANY_NAME S_STREET_NUMBER S_STREET_NAME S_STREET_TYPE S_SUITE_NUMBER S_CITY S_COUNTY S_STATE S_ZIP S_COUNTRY S_GMT_OFFSET S_TAX_PRECENTAGE ------------ -------------------- ---------------- -------------- ---------------- -------------------- ------------------ ------------- -------------------- -------------------- ----------- -------------------- -------------------- -------------------- ------------- -------------------- ------------ -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------- ---------------- - - 1 AAAAAAAABAAAAAAA 1997-03-13 ? 2451189 ought 245 5250760 8AM-4PM William Ward 2 Unknown Enough high areas st Charles Bartley 1 Unknown 1 Unknown 767 Spring Wy Suite 250 Midway Williamson County TN 31904 United States -5.0000000E+000 2.9999999E-002 - 2 AAAAAAAACAAAAAAA 1997-03-13 2000-03-12 ? able 236 5285950 8AM-4PM Scott Smith 8 Unknown Parliamentary candid David Lamontagne 1 Unknown 1 Unknown 255 Sycamore Dr. Suite 410 Midway Williamson County TN 31904 United States -5.0000000E+000 2.9999999E-002 - 3 AAAAAAAACAAAAAAA 2000-03-13 ? ? able 236 7557959 8AM-4PM Scott Smith 7 Unknown Impossible, true arm David Lamontagne 1 Unknown 1 Unknown 877 Park Laurel Road Suite T Midway Williamson County TN 31904 United States -5.0000000E+000 2.9999999E-002 - 4 AAAAAAAAEAAAAAAA 1997-03-13 1999-03-13 2451044 ese 218 9341467 8AM-4PM Edwin Adams 4 Unknown Events would achieve Thomas Pollack 1 Unknown 1 Unknown 27 Lake Ln Suite 260 Midway Williamson County TN 31904 United States -5.0000000E+000 2.9999999E-002 - 5 AAAAAAAAEAAAAAAA 1999-03-14 2001-03-12 2450910 anti 288 9078805 8AM-4PM Edwin Adams 8 Unknown Events would achieve Thomas Pollack 1 Unknown 1 Unknown 27 Lee 6th Court Suite 80 Fairview Williamson County TN 35709 United States -5.0000000E+000 2.9999999E-002 - 6 AAAAAAAAEAAAAAAA 2001-03-13 ? ? cally 229 9026222 8AM-4PM Edwin Adams 10 Unknown Events would achieve Thomas Pollack 1 Unknown 1 Unknown 220 6th Lane Suite 140 Midway Williamson County TN 31904 United States -5.0000000E+000 2.9999999E-002 - 7 AAAAAAAAHAAAAAAA 1997-03-13 ? ? ation 297 8954883 8AM-4PM David Thomas 9 Unknown Architects coul Thomas Benton 1 Unknown 1 Unknown 811 Lee Circle Suite T Midway Williamson County TN 31904 United States -5.0000000E+000 9.9999997E-003 - 8 AAAAAAAAIAAAAAAA 1997-03-13 2000-03-12 ? eing 278 6995995 8AM-4PM Brett Yates 2 Unknown Various bars make mo Dean Morrison 1 Unknown 1 Unknown 226 12th Lane Suite D Fairview Williamson County TN 35709 United States -5.0000000E+000 7.9999998E-002 - 9 AAAAAAAAIAAAAAAA 2000-03-13 ? ? eing 271 6995995 8AM-4PM Brett Yates 2 Unknown Formal, psychologica Dean Morrison 1 Unknown 1 Unknown 226 Hill Boulevard Suite 190 Midway Williamson County TN 31904 United States -5.0000000E+000 7.9999998E-002 - 10 AAAAAAAAKAAAAAAA 1997-03-13 1999-03-13 ? bar 294 9294113 8AM-4PM Raymond Jacobs 8 Unknown Little expectations Michael Wilson 1 Unknown 1 Unknown 175 4th Court Suite C Midway Williamson County TN 31904 United States -5.0000000E+000 5.9999998E-002 - 11 AAAAAAAAKAAAAAAA 1999-03-14 2001-03-12 ? ought 294 9294113 8AM-4PM Raymond Jacobs 6 Unknown Mysterious employe Michael Wilson 1 Unknown 1 Unknown 175 Park Green Court Suite 160 Midway Williamson County TN 31904 United States -5.0000000E+000 1.0999999E-001 - 12 AAAAAAAAKAAAAAAA 2001-03-13 ? ? ought 294 5219562 8AM-12AM Robert Thompson 6 Unknown Events develop i Dustin Kelly 1 Unknown 1 Unknown 337 College Boulevard Suite 100 Fairview Williamson County TN 31904 United States -5.0000000E+000 9.9999997E-003 - ---- 12 row(s) selected. ->> ->>-- select of few columns with WHERE predicate ->>select s_store_sk, left(s_store_id, 20) from hive.hive.store_orc where s_store_sk < 7; - -S_STORE_SK (EXPR) ------------ -------------------- - - 1 AAAAAAAABAAAAAAA - 2 AAAAAAAACAAAAAAA - 3 AAAAAAAACAAAAAAA - 4 AAAAAAAAEAAAAAAA - 5 AAAAAAAAEAAAAAAA - 6 AAAAAAAAEAAAAAAA - ---- 6 row(s) selected. ->> ->>-- select count of rows ->>select count(*) from hive.hive.store_orc; - -(EXPR) --------------------- - - 12 - ---- 1 row(s) selected. ->> ->>-- explain of join between 2 ORC tables ->>prepare XX from select x.s_suite_number, y.s_street_name -+> from hive.hive.store_orc x, hive.hive.store_orc y -+> where x.s_store_sk = y.s_store_sk; - ---- SQL command prepared. ->>execute explainIt; - -S OPERATOR LC RC TAB_NAME --- ---------------- -- -- ---------- - -04 ROOT 3 ? -03 HYBRID_HASH_JOIN 2 1 -02 ORC_SCAN ? ? STORE_ORC -01 ORC_SCAN ? ? STORE_ORC - ---- 4 row(s) selected. ->> ->>-- execute of join between 2 ORC tables ->>execute XX; - -S_SUITE_NUMBER S_STREET_NAME --------------------- -------------------- - -Suite 250 Spring -Suite 410 Sycamore -Suite T Park Laurel -Suite 260 Lake -Suite 80 Lee 6th -Suite 140 6th -Suite T Lee -Suite D 12th -Suite 190 Hill -Suite C 4th -Suite 160 Park Green -Suite 100 College - ---- 12 row(s) selected. ->> ->>-- explain of join between hive(hdfs) and ORC tables ->>prepare XX from select x.s_suite_number, y.s_street_name -+> from hive.hive.store x, hive.hive.store_orc y -+> where x.s_store_sk = y.s_store_sk; - ---- SQL command prepared. ->>execute explainIt; - -S OPERATOR LC RC TAB_NAME --- ---------------- -- -- ---------- - -04 ROOT 3 ? -03 HYBRID_HASH_JOIN 2 1 -02 HIVE_SCAN ? ? STORE -01 ORC_SCAN ? ? STORE_ORC - ---- 4 row(s) selected. ->> ->>-- execute of join between hive(hdfs) and ORC tables ->>execute XX; - -S_SUITE_NUMBER S_STREET_NAME --------------------- -------------------- - -Suite 250 Spring -Suite 410 Sycamore -Suite T Park Laurel -Suite 260 Lake -Suite 80 Lee 6th -Suite 140 6th -Suite T Lee -Suite D 12th -Suite 190 Hill -Suite C 4th -Suite 160 Park Green -Suite 100 College - ---- 12 row(s) selected. ->> ->>-- column list pushdown test ->>cqd orc_columns_pushdown 'OFF'; - ---- SQL operation complete. ->>showplan option 'tr' select s_store_sk from store_orc; -MASTER Executor fragment -======================== - -<<<<<<< HEAD -Fragment ID: 0, Length: 58688 -======= -Fragment ID: 0, Length: 17144 ->>>>>>> 1830668... Enhanced checktests changes. Jenkins usage not yet enabled. - -Contents of EX_ROOT [2]: ------------------------- - -For ComTdb : -Class Version = 1, Class Size = 576 -InitialQueueSizeDown = 4, InitialQueueSizeUp = 4 -queueResizeLimit = 9, queueResizeFactor = 4 -queueSizeDown = 0, queueSizeUp = 0, numBuffers = 0, bufferSize = 0 -estimatedRowUsed = 0.000000, estimatedRowsAccessed = 0.000000, expressionMode = 0 -Flag = 0x129 - -For ComTdbRoot : -FirstNRows = -1, baseTablenamePosition = -1 -queryType_ = 2, planVersion_ = 2600 -rtFlags1_ = 0x20300a00 -rtFlags2_ = 0 -rtFlags3_ = 0 -rtFlags4_ = 0x2000 -rtFlags5_ = 0 -queryType_ = 2 -inputVarsSize_ = 0 -querySimilarityInfo()->siList()->numEntries() = 1 -<<<<<<< HEAD -explain_plan_size = 3088 -======= -explain_plan_size = 3184 ->>>>>>> 1830668... Enhanced checktests changes. Jenkins usage not yet enabled. - -# of Expressions = 4 - -Expression: inputExpr_ is NULL -Expression: outputExpr_ is not NULL -Expression: pkeyExpr_ is NULL -Expression: predExpr_ is NULL -Contents of EX_EXT_STORAGE_SCAN [1]: ------------------------------------- - -For ComTdb : -Class Version = 1, Class Size = 464 -InitialQueueSizeDown = 4, InitialQueueSizeUp = 4 -queueResizeLimit = 9, queueResizeFactor = 4 -queueSizeDown = 2048, queueSizeUp = 2048, numBuffers = 4, bufferSize = 41032 -estimatedRowUsed = 100.000000, estimatedRowsAccessed = 100.000000, expressionMode = 0 -Flag = 0x9 -criDescDown_->noTuples() = 2, criDescUp_->noTuples() = 3 - -For ComTdbExtStorageScan : -tableName_ = HIVE.HIVE.STORE_ORC -type_ = ORC -hostName_ = localhost, port_ = 36000 -recordDelimiter_ = 10, columnDelimiter_ = 1 -hdfsBufSize_ = 703, rangeTailIOSize_ = 20970, hdfsSqlMaxRecLen_ = 8 -tuppIndex_ = 2, workAtpIndex_ = 3 -asciiTuppIndex_ = 4, asciiRowLen_ = 516 -moveExprColsTuppIndex_ = 2, moveExprColsRowLength_ = 560 -convertSkipListSize_ = 33, convertSkipList_ = 3 -outputRowLength_ = 16 -Flag = 0xc - -Number of ranges to scan: 1 -Number of esps to scan: 1 - - Esp# Range# StripeOffset Length FileName -====== ====== ============ ============ ============================== - - 0 0 3 2853 store_orc/000000_0 - -Summary of bytes read per ESP (2853 = 100 percent): - -ESP 0 reads 2853 bytes ( 100 percent of avg) - -Number of columns to retrieve: 29 -ColNumber: 1, ColName: S_STORE_SK -ColNumber: 2, ColName: S_STORE_ID -ColNumber: 3, ColName: S_REC_START_DATE -ColNumber: 4, ColName: S_REC_END_DATE -ColNumber: 5, ColName: S_CLOSED_DATE_SK -ColNumber: 6, ColName: S_STORE_NAME -ColNumber: 7, ColName: S_NUMBER_EMPLOYEES -ColNumber: 8, ColName: S_FLOOR_SPACE -ColNumber: 9, ColName: S_HOURS -ColNumber: 10, ColName: S_MANAGER -ColNumber: 11, ColName: S_MARKET_ID -ColNumber: 12, ColName: S_GEOGRAPHY_CLASS -ColNumber: 13, ColName: S_MARKET_DESC -ColNumber: 14, ColName: S_MARKET_MANAGER -ColNumber: 15, ColName: S_DIVISION_ID -ColNumber: 16, ColName: S_DIVISION_NAME -ColNumber: 17, ColName: S_COMPANY_ID -ColNumber: 18, ColName: S_COMPANY_NAME -ColNumber: 19, ColName: S_STREET_NUMBER -ColNumber: 20, ColName: S_STREET_NAME -ColNumber: 21, ColName: S_STREET_TYPE -ColNumber: 22, ColName: S_SUITE_NUMBER -ColNumber: 23, ColName: S_CITY -ColNumber: 24, ColName: S_COUNTY -ColNumber: 25, ColName: S_STATE -ColNumber: 26, ColName: S_ZIP -ColNumber: 27, ColName: S_COUNTRY -ColNumber: 28, ColName: S_GMT_OFFSET -ColNumber: 29, ColName: S_TAX_PRECENTAGE -<<<<<<< HEAD -hdfsRootDir: hdfs://localhost:36000/user/trafodion/hive/tpcds/store_orc -modTSforDir_ = 1511634123, numOfPartCols_ = 0 -======= -hdfsRootDir: hdfs://localhost:24200/user/trafodion/hive/tpcds/store_orc -modTSforDir_ = 1496437232, numOfPartCols_ = 0 ->>>>>>> 1830668... Enhanced checktests changes. Jenkins usage not yet enabled. - -# of Expressions = 6 - -Expression: selectExpr_ is NULL -Expression: moveExpr_ is not NULL -Expression: convertExpr_ is NULL -Expression: moveColsConvertExpr_ is not NULL -Expression: partElimExpr_ is NULL -Expression: extOperExpr_ is NULL - ---- SQL operation complete. ->>select s_store_sk from store_orc; - -S_STORE_SK ------------ - - 1 - 2 - 3 - 4 - 5 - 6 - 7 - 8 - 9 - 10 - 11 - 12 - ---- 12 row(s) selected. ->>cqd orc_columns_pushdown 'ON'; - ---- SQL operation complete. ->>showplan option 'tr' select s_store_sk from store_orc; -MASTER Executor fragment -======================== - -Fragment ID: 0, Length: 14664 - -Contents of EX_ROOT [2]: ------------------------- - -For ComTdb : -Class Version = 1, Class Size = 576 -InitialQueueSizeDown = 4, InitialQueueSizeUp = 4 -queueResizeLimit = 9, queueResizeFactor = 4 -queueSizeDown = 0, queueSizeUp = 0, numBuffers = 0, bufferSize = 0 -estimatedRowUsed = 0.000000, estimatedRowsAccessed = 0.000000, expressionMode = 0 -Flag = 0x129 - -For ComTdbRoot : -FirstNRows = -1, baseTablenamePosition = -1 -queryType_ = 2, planVersion_ = 2600 -rtFlags1_ = 0x20300a00 -rtFlags2_ = 0 -rtFlags3_ = 0 -rtFlags4_ = 0x2000 -rtFlags5_ = 0 -queryType_ = 2 -inputVarsSize_ = 0 -querySimilarityInfo()->siList()->numEntries() = 1 -<<<<<<< HEAD -explain_plan_size = 3080 -======= -explain_plan_size = 3176 ->>>>>>> 1830668... Enhanced checktests changes. Jenkins usage not yet enabled. - -# of Expressions = 4 - -Expression: inputExpr_ is NULL -Expression: outputExpr_ is not NULL -Expression: pkeyExpr_ is NULL -Expression: predExpr_ is NULL -Contents of EX_EXT_STORAGE_SCAN [1]: ------------------------------------- - -For ComTdb : -Class Version = 1, Class Size = 464 -InitialQueueSizeDown = 4, InitialQueueSizeUp = 4 -queueResizeLimit = 9, queueResizeFactor = 4 -queueSizeDown = 2048, queueSizeUp = 2048, numBuffers = 4, bufferSize = 41032 -estimatedRowUsed = 100.000000, estimatedRowsAccessed = 100.000000, expressionMode = 0 -Flag = 0x9 -criDescDown_->noTuples() = 2, criDescUp_->noTuples() = 3 - -For ComTdbExtStorageScan : -tableName_ = HIVE.HIVE.STORE_ORC -type_ = ORC -hostName_ = localhost, port_ = 36000 -recordDelimiter_ = 10, columnDelimiter_ = 1 -hdfsBufSize_ = 115, rangeTailIOSize_ = 20970, hdfsSqlMaxRecLen_ = 8 -tuppIndex_ = 2, workAtpIndex_ = 3 -asciiTuppIndex_ = 4, asciiRowLen_ = 8 -moveExprColsTuppIndex_ = 2, moveExprColsRowLength_ = 16 -convertSkipListSize_ = 33, convertSkipList_ = 3 -outputRowLength_ = 16 -Flag = 0xc - -Number of ranges to scan: 1 -Number of esps to scan: 1 - - Esp# Range# StripeOffset Length FileName -====== ====== ============ ============ ============================== - - 0 0 3 2853 store_orc/000000_0 - -Summary of bytes read per ESP (2853 = 100 percent): - -ESP 0 reads 2853 bytes ( 100 percent of avg) - -Number of columns to retrieve: 1 -ColNumber: 1, ColName: S_STORE_SK -<<<<<<< HEAD -hdfsRootDir: hdfs://localhost:36000/user/trafodion/hive/tpcds/store_orc -modTSforDir_ = 1511634123, numOfPartCols_ = 0 -======= -hdfsRootDir: hdfs://localhost:24200/user/trafodion/hive/tpcds/store_orc -modTSforDir_ = 1496437232, numOfPartCols_ = 0 ->>>>>>> 1830668... Enhanced checktests changes. Jenkins usage not yet enabled. - -# of Expressions = 6 - -Expression: selectExpr_ is NULL -Expression: moveExpr_ is not NULL -Expression: convertExpr_ is NULL -Expression: moveColsConvertExpr_ is not NULL -Expression: partElimExpr_ is NULL -Expression: extOperExpr_ is NULL - ---- SQL operation complete. ->>select s_store_sk from store_orc; - -S_STORE_SK ------------ - - 1 - 2 - 3 - 4 - 5 - 6 - 7 - 8 - 9 - 10 - 11 - 12 - ---- 12 row(s) selected. ->> ->>-- predicate pushdown to ORC layer ->>cqd orc_pred_pushdown 'OFF'; - ---- SQL operation complete. ->>select s_store_sk from store_orc where s_store_sk = 3; - -S_STORE_SK ------------ - - 3 - ---- 1 row(s) selected. ->>select s_store_sk from store_orc where s_store_sk < 2; - -S_STORE_SK ------------ - - 1 - ---- 1 row(s) selected. ->>select s_store_sk from store_orc where s_store_sk >= 11; - -S_STORE_SK ------------ - - 11 - 12 - ---- 2 row(s) selected. ->>select s_store_sk from store_orc where s_store_sk >= 4 and s_store_sk < 6; - -S_STORE_SK ------------ - - 4 - 5 - ---- 2 row(s) selected. ->>select s_store_sk from store_orc where s_store_sk in (7,8,9); - -S_STORE_SK ------------ - - 7 - 8 - 9 - ---- 3 row(s) selected. ->>showplan option 'tr' select s_store_sk from store_orc where s_store_sk = 3; -MASTER Executor fragment -======================== - -Fragment ID: 0, Length: 14840 - -Contents of EX_ROOT [2]: ------------------------- - -For ComTdb : -Class Version = 1, Class Size = 576 -InitialQueueSizeDown = 4, InitialQueueSizeUp = 4 -queueResizeLimit = 9, queueResizeFactor = 4 -queueSizeDown = 0, queueSizeUp = 0, numBuffers = 0, bufferSize = 0 -estimatedRowUsed = 0.000000, estimatedRowsAccessed = 0.000000, expressionMode = 0 -Flag = 0x129 - -For ComTdbRoot : -FirstNRows = -1, baseTablenamePosition = -1 -queryType_ = 2, planVersion_ = 2600 -rtFlags1_ = 0x20300a00 -rtFlags2_ = 0 -rtFlags3_ = 0 -rtFlags4_ = 0x2000 -rtFlags5_ = 0 -queryType_ = 2 -inputVarsSize_ = 0 -querySimilarityInfo()->siList()->numEntries() = 1 -<<<<<<< HEAD -explain_plan_size = 3176 -======= -explain_plan_size = 3272 ->>>>>>> 1830668... Enhanced checktests changes. Jenkins usage not yet enabled. - -# of Expressions = 4 - -Expression: inputExpr_ is NULL -Expression: outputExpr_ is not NULL -Expression: pkeyExpr_ is NULL -Expression: predExpr_ is NULL -Contents of EX_EXT_STORAGE_SCAN [1]: ------------------------------------- - -For ComTdb : -Class Version = 1, Class Size = 464 -InitialQueueSizeDown = 4, InitialQueueSizeUp = 4 -queueResizeLimit = 9, queueResizeFactor = 4 -queueSizeDown = 2048, queueSizeUp = 2048, numBuffers = 4, bufferSize = 32840 -estimatedRowUsed = 100.000000, estimatedRowsAccessed = 100.000000, expressionMode = 0 -Flag = 0x9 -criDescDown_->noTuples() = 3, criDescUp_->noTuples() = 4 - -For ComTdbExtStorageScan : -tableName_ = HIVE.HIVE.STORE_ORC -type_ = ORC -hostName_ = localhost, port_ = 36000 -recordDelimiter_ = 10, columnDelimiter_ = 1 -hdfsBufSize_ = 115, rangeTailIOSize_ = 20970, hdfsSqlMaxRecLen_ = 16 -tuppIndex_ = 3, workAtpIndex_ = 3 -asciiTuppIndex_ = 4, asciiRowLen_ = 8 -moveExprColsTuppIndex_ = 2, moveExprColsRowLength_ = 8 -convertSkipListSize_ = 33, convertSkipList_ = 2 -outputRowLength_ = 8 -Flag = 0xc - -Number of ranges to scan: 1 -Number of esps to scan: 1 - - Esp# Range# StripeOffset Length FileName -====== ====== ============ ============ ============================== - - 0 0 3 2853 store_orc/000000_0 - -Summary of bytes read per ESP (2853 = 100 percent): - -ESP 0 reads 2853 bytes ( 100 percent of avg) - -Number of columns to retrieve: 1 -ColNumber: 1, ColName: S_STORE_SK -<<<<<<< HEAD -hdfsRootDir: hdfs://localhost:36000/user/trafodion/hive/tpcds/store_orc -modTSforDir_ = 1511634123, numOfPartCols_ = 0 -======= -hdfsRootDir: hdfs://localhost:24200/user/trafodion/hive/tpcds/store_orc -modTSforDir_ = 1496437232, numOfPartCols_ = 0 ->>>>>>> 1830668... Enhanced checktests changes. Jenkins usage not yet enabled. - -# of Expressions = 6 - -Expression: selectExpr_ is not NULL -Expression: moveExpr_ is NULL -Expression: convertExpr_ is not NULL -Expression: moveColsConvertExpr_ is NULL -Expression: partElimExpr_ is NULL -Expression: extOperExpr_ is NULL - ---- SQL operation complete. ->> ->>cqd orc_pred_pushdown 'ON'; - ---- SQL operation complete. ->>select s_store_sk from store_orc where s_store_sk = 3; - -S_STORE_SK ------------ - - 3 - ---- 1 row(s) selected. ->>select s_store_sk from store_orc where s_store_sk < 2; - -S_STORE_SK ------------ - - 1 - ---- 1 row(s) selected. ->>select s_store_sk from store_orc where s_store_sk >= 11; - -S_STORE_SK ------------ - - 11 - 12 - ---- 2 row(s) selected. ->>select s_store_sk from store_orc where s_store_sk >= 4 and s_store_sk < 6; - -S_STORE_SK ------------ - - 4 - 5 - ---- 2 row(s) selected. ->>select s_store_sk from store_orc where s_store_sk in (7,8,9); - -S_STORE_SK ------------ - - 7 - 8 - 9 - ---- 3 row(s) selected. ->>showplan option 'tr' select s_store_sk from store_orc where s_store_sk = 3; -MASTER Executor fragment -======================== - -<<<<<<< HEAD -Fragment ID: 0, Length: 19144 -======= -Fragment ID: 0, Length: 19984 ->>>>>>> 1830668... Enhanced checktests changes. Jenkins usage not yet enabled. - -Contents of EX_ROOT [2]: ------------------------- - -For ComTdb : -Class Version = 1, Class Size = 576 -InitialQueueSizeDown = 4, InitialQueueSizeUp = 4 -queueResizeLimit = 9, queueResizeFactor = 4 -queueSizeDown = 0, queueSizeUp = 0, numBuffers = 0, bufferSize = 0 -estimatedRowUsed = 0.000000, estimatedRowsAccessed = 0.000000, expressionMode = 0 -Flag = 0x129 - -For ComTdbRoot : -FirstNRows = -1, baseTablenamePosition = -1 -queryType_ = 2, planVersion_ = 2600 -rtFlags1_ = 0x20300a00 -rtFlags2_ = 0 -rtFlags3_ = 0 -rtFlags4_ = 0x2000 -rtFlags5_ = 0 -queryType_ = 2 -inputVarsSize_ = 0 -querySimilarityInfo()->siList()->numEntries() = 1 -<<<<<<< HEAD -explain_plan_size = 3264 -======= -explain_plan_size = 3368 ->>>>>>> 1830668... Enhanced checktests changes. Jenkins usage not yet enabled. - -# of Expressions = 4 - -Expression: inputExpr_ is NULL -Expression: outputExpr_ is not NULL -Expression: pkeyExpr_ is NULL -Expression: predExpr_ is NULL -Contents of EX_EXT_STORAGE_SCAN [1]: ------------------------------------- - -For ComTdb : -Class Version = 1, Class Size = 464 -InitialQueueSizeDown = 4, InitialQueueSizeUp = 4 -queueResizeLimit = 9, queueResizeFactor = 4 -queueSizeDown = 2048, queueSizeUp = 2048, numBuffers = 4, bufferSize = 32840 -estimatedRowUsed = 100.000000, estimatedRowsAccessed = 100.000000, expressionMode = 0 -Flag = 0x9 -criDescDown_->noTuples() = 3, criDescUp_->noTuples() = 4 - -For ComTdbExtStorageScan : -tableName_ = HIVE.HIVE.STORE_ORC -type_ = ORC -hostName_ = localhost, port_ = 36000 -recordDelimiter_ = 10, columnDelimiter_ = 1 -hdfsBufSize_ = 115, rangeTailIOSize_ = 20970, hdfsSqlMaxRecLen_ = 16 -tuppIndex_ = 3, workAtpIndex_ = 3 -asciiTuppIndex_ = 4, asciiRowLen_ = 8 -moveExprColsTuppIndex_ = 2, moveExprColsRowLength_ = 8 -convertSkipListSize_ = 33, convertSkipList_ = 2 -outputRowLength_ = 8 -Flag = 0xc - -Number of ranges to scan: 1 -Number of esps to scan: 1 - - Esp# Range# StripeOffset Length FileName -====== ====== ============ ============ ============================== - - 0 0 3 2853 store_orc/000000_0 - -Summary of bytes read per ESP (2853 = 100 percent): - -ESP 0 reads 2853 bytes ( 100 percent of avg) - -Number of columns to retrieve: 1 -ColNumber: 1, ColName: S_STORE_SK -<<<<<<< HEAD -hdfsRootDir: hdfs://localhost:36000/user/trafodion/hive/tpcds/store_orc -modTSforDir_ = 1511634123, numOfPartCols_ = 0 -======= -hdfsRootDir: hdfs://localhost:24200/user/trafodion/hive/tpcds/store_orc -modTSforDir_ = 1496437232, numOfPartCols_ = 0 ->>>>>>> 1830668... Enhanced checktests changes. Jenkins usage not yet enabled. - -Number of PPI entries: 3 -PPI: #1 - type: STARTAND(1) -PPI: #2 - type: EQUALS(5) - operAttrIndex: 0 - colName_: s_store_sk -PPI: #3 - type: END(4) -Num Of extAllColInfoList entries: 33 - -# of Expressions = 6 - -Expression: selectExpr_ is not NULL -Expression: moveExpr_ is NULL -Expression: convertExpr_ is not NULL -Expression: moveColsConvertExpr_ is NULL -Expression: partElimExpr_ is NULL -Expression: extOperExpr_ is not NULL - ---- SQL operation complete. ->> ->>-- local join predicate is not pushed down ->>explain select * from store_sales_orc where ss_sold_date_sk = ss_item_sk ; - ------------------------------------------------------------------- PLAN SUMMARY -MODULE_NAME .............. DYNAMICALLY COMPILED -STATEMENT_NAME ........... NOT NAMED -<<<<<<< HEAD -PLAN_ID .................. 212378682018129421 -======= -PLAN_ID .................. 212363939384659273 ->>>>>>> 1830668... Enhanced checktests changes. Jenkins usage not yet enabled. -ROWS_OUT ......... 1,440,202 -EST_TOTAL_COST ......... 191.55 -STATEMENT ................ select * - from store_sales_orc - where ss_sold_date_sk = ss_item_sk; - - ------------------------------------------------------------------- NODE LISTING -ROOT ====================================== SEQ_NO 3 ONLY CHILD 2 -REQUESTS_IN .............. 1 -ROWS_OUT ......... 1,440,202 -EST_OPER_COST ............ 0 -EST_TOTAL_COST ......... 191.55 -DESCRIPTION - est_memory_per_node .... 10240.00(Limit), 0.00(BMOs), 0.00(nBMOs) MB - max_card_est ........... 2.8804e+06 - fragment_id ............ 0 - parent_frag ............ (none) - fragment_type .......... master - record_length ........ 132 - statement_index ........ 0 - affinity_value ......... 0 - max_max_cardinal 1,440,202 - esp_2_node_map ......... (-1(4 times)) - xn_access_mode ......... read_only - xn_autoabort_interval 0 - auto_query_retry ....... enabled - plan_version ....... 2,600 - embedded_arkcmp ........ used - IS_SQLCI ............... ON - LDAP_USERNAME .......... NOT AVAILABLE -<<<<<<< HEAD - HBASE_FILTER_PREDS ..... 2 - TRAF_INDEX_CREATE_OPT ON - TRAF_USE_REGION_XN ..... ON -======= - MODE_SEABASE ........... ON - SEABASE_VOLATILE_TABLES ON - HBASE_ASYNC_DROP_TABLE OFF - HBASE_SERIALIZATION .... ON - HBASE_SMALL_SCANNER .... SYSTEM - HBASE_FILTER_PREDS ..... 2 - TRAF_ALIGNED_ROW_FORMAT ON - TRAF_INDEX_CREATE_OPT ON ->>>>>>> 1830668... Enhanced checktests changes. Jenkins usage not yet enabled. - SCHEMA ................. HIVE.HIVE - HIVE_MAX_STRING_LENGTH 20 - MODE_SEAHIVE ........... ON - TRAF_ENABLE_ORC_FORMAT ON - HIST_ROWCOUNT_REQU 50,000 - HIVE_USE_EXT_TABLE_ATTR ON - HIST_MISSING_STATS_WARN 0 - ORC_NJS_PROBES_T 1,000,000 - HIVE_MIN_NUM_ESPS_PER_D 0 - ORC_COLUMNS_PUSHDOWN ... ON - ORC_PRED_PUSHDOWN ...... ON - GENERATE_EXPLAIN ....... ON - select_list ............ HIVE.STORE_SALES_ORC.SS_SOLD_DATE_SK, - HIVE.STORE_SALES_ORC.SS_SOLD_TIME_SK, - HIVE.STORE_SALES_ORC.SS_SOLD_DATE_SK, - HIVE.STORE_SALES_ORC.SS_CUSTOMER_SK, - HIVE.STORE_SALES_ORC.SS_CDEMO_SK, - HIVE.STORE_SALES_ORC.SS_HDEMO_SK, - HIVE.STORE_SALES_ORC.SS_ADDR_SK, - HIVE.STORE_SALES_ORC.SS_STORE_SK, - HIVE.STORE_SALES_ORC.SS_PROMO_SK, - HIVE.STORE_SALES_ORC.SS_TICKET_NUMBER, - HIVE.STORE_SALES_ORC.SS_QUANTITY, - HIVE.STORE_SALES_ORC.SS_WHOLESALE_COST, - HIVE.STORE_SALES_ORC.SS_LIST_PRICE, - HIVE.STORE_SALES_ORC.SS_SALES_PRICE, - HIVE.STORE_SALES_ORC.SS_EXT_DISCOUNT_AMT, - HIVE.STORE_SALES_ORC.SS_EXT_SALES_PRICE, - HIVE.STORE_SALES_ORC.SS_EXT_WHOLESALE_COST, - HIVE.STORE_SALES_ORC.SS_EXT_LIST_PRICE, - HIVE.STORE_SALES_ORC.SS_EXT_TAX, - HIVE.STORE_SALES_ORC.SS_COUPON_AMT, - HIVE.STORE_SALES_ORC.SS_NET_PAID, - HIVE.STORE_SALES_ORC.SS_NET_PAID_INC_TAX, - HIVE.STORE_SALES_ORC.SS_NET_PROFIT - - -ESP_EXCHANGE ============================== SEQ_NO 2 ONLY CHILD 1 -REQUESTS_IN .............. 1 -ROWS_OUT ......... 1,440,202 -EST_OPER_COST ............ 0.01 -EST_TOTAL_COST ......... 191.55 -DESCRIPTION - max_card_est ........... 2.8804e+06 - fragment_id ............ 2 - parent_frag ............ 0 - fragment_type .......... esp - est_memory_per_node .... 153.262 KB - record_length ........ 132 - buffer_size ....... 30,388 - parent_processes ....... 1 - child_processes ........ 4 - child_partitioning_func hash2 partitioned 4 ways on (randomNum) - - -ORC_SCAN ================================== SEQ_NO 1 NO CHILDREN -TABLE_NAME ............... HIVE.HIVE.STORE_SALES_ORC -REQUESTS_IN .............. 1 -ROWS_OUT ......... 1,440,202 -EST_OPER_COST .......... 191.55 -EST_TOTAL_COST ......... 191.55 -DESCRIPTION - max_card_est ........... 2.8804e+06 - fragment_id ............ 2 - parent_frag ............ 0 - fragment_type .......... esp - record_length ........ 132 - scan_type .............. full scan of table HIVE.HIVE.STORE_SALES_ORC - object_type ............ Hive_Orc - scan_direction ......... forward - lock_mode .............. not specified, defaulted to lock cursor - access_mode ............ not specified, defaulted to read committed - columns_retrieved ..... 23 - probes ................. 1 - rows_accessed .......... 2.8804e+06 - executor_predicates .... (HIVE.STORE_SALES_ORC.SS_ITEM_SK = - HIVE.STORE_SALES_ORC.SS_SOLD_DATE_SK) - ---- SQL operation complete. ->> ->>-- Boolean constant predicate is not pushed down ->>explain select * from store_sales_orc where 1=2; - ------------------------------------------------------------------- PLAN SUMMARY -MODULE_NAME .............. DYNAMICALLY COMPILED -STATEMENT_NAME ........... NOT NAMED -<<<<<<< HEAD -PLAN_ID .................. 212378682018429456 -======= -PLAN_ID .................. 212363939386438156 ->>>>>>> 1830668... Enhanced checktests changes. Jenkins usage not yet enabled. -ROWS_OUT ................. 1 -EST_TOTAL_COST ........... 0 -STATEMENT ................ select * from store_sales_orc where 1=2; - - ------------------------------------------------------------------- NODE LISTING -ROOT ====================================== SEQ_NO 2 ONLY CHILD 1 -REQUESTS_IN .............. 1 -ROWS_OUT ................. 1 -EST_OPER_COST ............ 0 -EST_TOTAL_COST ........... 0 -DESCRIPTION - est_memory_per_node .... 10240.00(Limit), 0.00(BMOs), 0.00(nBMOs) MB - max_card_est ........... 0 - fragment_id ............ 0 - parent_frag ............ (none) - fragment_type .......... master - record_length ........ 138 - statement_index ........ 0 - affinity_value ......... 0 - max_max_cardinality .... 1 - xn_access_mode ......... read_only - xn_autoabort_interval 0 - auto_query_retry ....... enabled - plan_version ....... 2,600 - embedded_arkcmp ........ used - IS_SQLCI ............... ON - LDAP_USERNAME .......... NOT AVAILABLE -<<<<<<< HEAD - HBASE_FILTER_PREDS ..... 2 - TRAF_INDEX_CREATE_OPT ON - TRAF_USE_REGION_XN ..... ON -======= - MODE_SEABASE ........... ON - SEABASE_VOLATILE_TABLES ON - HBASE_ASYNC_DROP_TABLE OFF - HBASE_SERIALIZATION .... ON - HBASE_SMALL_SCANNER .... SYSTEM - HBASE_FILTER_PREDS ..... 2 - TRAF_ALIGNED_ROW_FORMAT ON - TRAF_INDEX_CREATE_OPT ON ->>>>>>> 1830668... Enhanced checktests changes. Jenkins usage not yet enabled. - SCHEMA ................. HIVE.HIVE - HIVE_MAX_STRING_LENGTH 20 - MODE_SEAHIVE ........... ON - TRAF_ENABLE_ORC_FORMAT ON - HIST_ROWCOUNT_REQU 50,000 - HIVE_USE_EXT_TABLE_ATTR ON - HIST_MISSING_STATS_WARN 0 - ORC_NJS_PROBES_T 1,000,000 - HIVE_MIN_NUM_ESPS_PER_D 0 - ORC_COLUMNS_PUSHDOWN ... ON - ORC_PRED_PUSHDOWN ...... ON - GENERATE_EXPLAIN ....... ON - select_list ............ HIVE.STORE_SALES_ORC.SS_SOLD_DATE_SK, - HIVE.STORE_SALES_ORC.SS_SOLD_TIME_SK, - HIVE.STORE_SALES_ORC.SS_ITEM_SK, - HIVE.STORE_SALES_ORC.SS_CUSTOMER_SK, - HIVE.STORE_SALES_ORC.SS_CDEMO_SK, - HIVE.STORE_SALES_ORC.SS_HDEMO_SK, - HIVE.STORE_SALES_ORC.SS_ADDR_SK, - HIVE.STORE_SALES_ORC.SS_STORE_SK, - HIVE.STORE_SALES_ORC.SS_PROMO_SK, - HIVE.STORE_SALES_ORC.SS_TICKET_NUMBER, - HIVE.STORE_SALES_ORC.SS_QUANTITY, - HIVE.STORE_SALES_ORC.SS_WHOLESALE_COST, - HIVE.STORE_SALES_ORC.SS_LIST_PRICE, - HIVE.STORE_SALES_ORC.SS_SALES_PRICE, - HIVE.STORE_SALES_ORC.SS_EXT_DISCOUNT_AMT, - HIVE.STORE_SALES_ORC.SS_EXT_SALES_PRICE, - HIVE.STORE_SALES_ORC.SS_EXT_WHOLESALE_COST, - HIVE.STORE_SALES_ORC.SS_EXT_LIST_PRICE, - HIVE.STORE_SALES_ORC.SS_EXT_TAX, - HIVE.STORE_SALES_ORC.SS_COUPON_AMT, - HIVE.STORE_SALES_ORC.SS_NET_PAID, - HIVE.STORE_SALES_ORC.SS_NET_PAID_INC_TAX, - HIVE.STORE_SALES_ORC.SS_NET_PROFIT - - -ORC_SCAN ================================== SEQ_NO 1 NO CHILDREN -TABLE_NAME ............... HIVE.HIVE.STORE_SALES_ORC -REQUESTS_IN .............. 1 -ROWS_OUT ................. 1 -EST_OPER_COST ............ 0 -EST_TOTAL_COST ........... 0 -DESCRIPTION - max_card_est ........... 0 - fragment_id ............ 0 - parent_frag ............ (none) - fragment_type .......... master - record_length ........ 138 - scan_type .............. full scan of table HIVE.HIVE.STORE_SALES_ORC - object_type ............ Hive_Orc - scan_direction ......... forward - lock_mode .............. not specified, defaulted to lock cursor - access_mode ............ not specified, defaulted to read committed - columns_retrieved ..... 23 - probes ................. 1 - part_elim_compiled ..... 0. - ---- SQL operation complete. ->> ->>-- aggregate pushdown to ORC layer ->>cqd orc_aggr_pushdown 'ON'; - ---- SQL operation complete. ->>explain options 'f' select count(*) from store_orc; - -LC RC OP OPERATOR OPT DESCRIPTION CARD ----- ---- ---- -------------------- -------- -------------------- --------- - -1 . 2 root 1.00E+000 -. . 1 orc_aggr 1.00E+000 - ---- SQL operation complete. ->>select count(*) from store_orc; - -(EXPR) --------------------- - - 12 - ---- 1 row(s) selected. ->>select count(*), count(*) from store_orc; - -(EXPR) (EXPR) --------------------- -------------------- - - 12 12 - ---- 1 row(s) selected. ->>select min(s_store_sk) from store_orc; - -(EXPR) ------------ - - 1 - ---- 1 row(s) selected. ->>select max(s_store_sk) from store_orc; - -(EXPR) ------------ - - 12 - ---- 1 row(s) selected. ->>select sum(s_store_sk) from store_orc; - -(EXPR) --------------------- - - 78 - ---- 1 row(s) selected. ->>select count(*), min(s_store_sk), max(s_store_sk), sum(s_store_sk) -+> from store_orc; - -(EXPR) (EXPR) (EXPR) (EXPR) --------------------- ----------- ----------- -------------------- - - 12 1 12 78 - ---- 1 row(s) selected. ->>explain options 'f' select count(*), min(s_store_sk), max(s_store_sk), -+> sum(s_store_sk) from store_orc; - -LC RC OP OPERATOR OPT DESCRIPTION CARD ----- ---- ---- -------------------- -------- -------------------- --------- - -1 . 2 root 1.00E+000 -. . 1 orc_aggr 1.00E+000 - ---- SQL operation complete. ->> ->>explain options 'f' -+> select count(*) from hive.hive.store_orc union all -+> select count(*) from hive.hive.store_orc; - -LC RC OP OPERATOR OPT DESCRIPTION CARD ----- ---- ---- -------------------- -------- -------------------- --------- - -3 . 4 root 2.00E+000 -1 2 3 merge_union 2.00E+000 -. . 2 orc_aggr 1.00E+000 -. . 1 orc_aggr 1.00E+000 - ---- SQL operation complete. ->>select count(*) from hive.hive.store_orc union all -+> select count(*) from hive.hive.store_orc; - -(EXPR) --------------------- - - 12 - 12 - ---- 2 row(s) selected. ->> ->>explain options 'f' -+> select min(s_store_sk) from hive.hive.store_orc union all -+> select min(s_store_sk) from hive.hive.store_orc; - -LC RC OP OPERATOR OPT DESCRIPTION CARD ----- ---- ---- -------------------- -------- -------------------- --------- - -3 . 4 root 2.00E+000 -1 2 3 merge_union 2.00E+000 -. . 2 orc_aggr 1.00E+000 -. . 1 orc_aggr 1.00E+000 - ---- SQL operation complete. ->>select min(s_store_sk) from hive.hive.store_orc union all -+> select min(s_store_sk) from hive.hive.store_orc; - -(EXPR) ------------ - - 1 - 1 - ---- 2 row(s) selected. ->> ->>explain options 'f' select count(s_store_sk) from store_orc; - -LC RC OP OPERATOR OPT DESCRIPTION CARD ----- ---- ---- -------------------- -------- -------------------- --------- - -2 . 3 root 1.00E+000 -1 . 2 sort_scalar_aggr 1.00E+000 -. . 1 orc_scan STORE_ORC 1.00E+002 - ---- SQL operation complete. ->>select count(s_store_sk) from store_orc; - -(EXPR) --------------------- - - 12 - ---- 1 row(s) selected. ->> ->>explain options 'f' select count(*) from store_orc -+> having sum(s_store_sk) = 78; - -LC RC OP OPERATOR OPT DESCRIPTION CARD ----- ---- ---- -------------------- -------- -------------------- --------- - -1 . 2 root 1.00E+000 -. . 1 orc_aggr 1.00E+000 - ---- SQL operation complete. ->>select count(*) from store_orc having sum(s_store_sk) = 78; - -(EXPR) --------------------- - - 12 - ---- 1 row(s) selected. ->> ->>select count(*) from store_orc having sum(s_store_sk) = 77; - ---- 0 row(s) selected. ->> ->>cqd orc_aggr_pushdown 'OFF'; - ---- SQL operation complete. ->>explain options 'f' select count(*), min(s_store_sk), max(s_store_sk), -+> sum(s_store_sk) from store_orc; - -LC RC OP OPERATOR OPT DESCRIPTION CARD ----- ---- ---- -------------------- -------- -------------------- --------- - -2 . 3 root 1.00E+000 -1 . 2 sort_scalar_aggr 1.00E+000 -. . 1 orc_scan STORE_ORC 1.00E+002 - ---- SQL operation complete. ->>select count(*), min(s_store_sk), max(s_store_sk), sum(s_store_sk) -+> from store_orc; - -(EXPR) (EXPR) (EXPR) (EXPR) --------------------- ----------- ----------- -------------------- - - 12 1 12 78 - ---- 1 row(s) selected. ->> ->>-- test query cache ->>prepare xx from select cd_gender from customer_demographics_orc where cd_gender = 'male' ; - ---- SQL command prepared. ->>select num_hits, num_params from table(querycacheentries('user', 'local')) -+>where substring(text, 1, 16) = 'select cd_gender' order by 1,2; - -NUM_HITS NUM_PARAMS ----------- ---------- - - 0 1 - ---- 1 row(s) selected. ->> ->>prepare xx from select cd_gender from customer_demographics_orc where cd_gender = 'female' ; - ---- SQL command prepared. ->>select num_hits, num_params from table(querycacheentries('user', 'local')) -+>where substring(text, 1, 16) = 'select cd_gender' order by 1,2; - -NUM_HITS NUM_PARAMS ----------- ---------- - - 1 1 - ---- 1 row(s) selected. ->> ->> ->>-- test external table attributes ->>set schema trafodion.sch; - ---- SQL operation complete. ->>drop external table if exists store_sales_orc for hive.hive.store_sales_orc; - ---- SQL operation complete. ->>create external table store_sales_orc -+> for hive.hive.store_sales_orc; - ---- SQL operation complete. ->>invoke hive.hive.store_sales_orc; - --- Definition of hive table STORE_SALES_ORC -<<<<<<< HEAD --- Definition current Wed Nov 29 02:20:31 2017 -======= --- Definition current Sun Jun 11 11:10:08 2017 ->>>>>>> 1830668... Enhanced checktests changes. Jenkins usage not yet enabled. - - ( - SS_SOLD_DATE_SK INT - , SS_SOLD_TIME_SK INT - , SS_ITEM_SK INT - , SS_CUSTOMER_SK INT - , SS_CDEMO_SK INT - , SS_HDEMO_SK INT - , SS_ADDR_SK INT - , SS_STORE_SK INT - , SS_PROMO_SK INT - , SS_TICKET_NUMBER INT - , SS_QUANTITY INT - , SS_WHOLESALE_COST REAL - , SS_LIST_PRICE REAL - , SS_SALES_PRICE REAL - , SS_EXT_DISCOUNT_AMT REAL - , SS_EXT_SALES_PRICE REAL - , SS_EXT_WHOLESALE_COST REAL - , SS_EXT_LIST_PRICE REAL - , SS_EXT_TAX REAL - , SS_COUPON_AMT REAL - , SS_NET_PAID REAL - , SS_NET_PAID_INC_TAX REAL - , SS_NET_PROFIT REAL - ) - /* stored as orc */ - ---- SQL operation complete. ->> ->>set schema hive.hive; - ---- SQL operation complete. ->>prepare s from select * from store_sales_orc where ss_item_sk = 1; - ---- SQL command prepared. ->>explain s; - ------------------------------------------------------------------- PLAN SUMMARY -MODULE_NAME .............. DYNAMICALLY COMPILED -STATEMENT_NAME ........... S -<<<<<<< HEAD -PLAN_ID .................. 212378682032138196 -======= -PLAN_ID .................. 212363939411863426 ->>>>>>> 1830668... Enhanced checktests changes. Jenkins usage not yet enabled. -ROWS_OUT ............. 1,698 -EST_TOTAL_COST ......... 191.52 -STATEMENT ................ select * from store_sales_orc where ss_item_sk = 1; - - ------------------------------------------------------------------- NODE LISTING -ROOT ====================================== SEQ_NO 3 ONLY CHILD 2 -REQUESTS_IN .............. 1 -ROWS_OUT ............. 1,698 -EST_OPER_COST ............ 0 -EST_TOTAL_COST ......... 191.52 -DESCRIPTION - est_memory_per_node .... 10240.00(Limit), 0.00(BMOs), 0.00(nBMOs) MB - max_card_est ........... 2.73638e+06 - fragment_id ............ 0 - parent_frag ............ (none) - fragment_type .......... master - record_length ........ 138 - statement_index ........ 0 - affinity_value ......... 0 - max_max_cardinality 1,698 - esp_2_node_map ......... (-1(4 times)) - xn_access_mode ......... read_only - xn_autoabort_interval 0 - auto_query_retry ....... enabled - plan_version ....... 2,600 - embedded_arkcmp ........ used - IS_SQLCI ............... ON - LDAP_USERNAME .......... NOT AVAILABLE -<<<<<<< HEAD - HBASE_FILTER_PREDS ..... 2 - TRAF_INDEX_CREATE_OPT ON - TRAF_USE_REGION_XN ..... ON -======= - MODE_SEABASE ........... ON - SEABASE_VOLATILE_TABLES ON - HBASE_ASYNC_DROP_TABLE OFF - HBASE_SERIALIZATION .... ON - HBASE_SMALL_SCANNER .... SYSTEM - HBASE_FILTER_PREDS ..... 2 - TRAF_ALIGNED_ROW_FORMAT ON - TRAF_INDEX_CREATE_OPT ON ->>>>>>> 1830668... Enhanced checktests changes. Jenkins usage not yet enabled. - HIVE_MAX_STRING_LENGTH 20 - MODE_SEAHIVE ........... ON - TRAF_ENABLE_ORC_FORMAT ON - HIST_ROWCOUNT_REQU 50,000 - HIVE_USE_EXT_TABLE_ATTR ON - HIST_MISSING_STATS_WARN 0 - ORC_NJS_PROBES_T 1,000,000 - HIVE_MIN_NUM_ESPS_PER_D 0 - ORC_COLUMNS_PUSHDOWN ... ON - ORC_PRED_PUSHDOWN ...... ON - ORC_AGGR_PUSHDOWN ...... OFF - SCHEMA ................. HIVE.HIVE - select_list ............ HIVE.STORE_SALES_ORC.SS_SOLD_DATE_SK, - HIVE.STORE_SALES_ORC.SS_SOLD_TIME_SK, %(1), - HIVE.STORE_SALES_ORC.SS_CUSTOMER_SK, - HIVE.STORE_SALES_ORC.SS_CDEMO_SK, - HIVE.STORE_SALES_ORC.SS_HDEMO_SK, - HIVE.STORE_SALES_ORC.SS_ADDR_SK, - HIVE.STORE_SALES_ORC.SS_STORE_SK, - HIVE.STORE_SALES_ORC.SS_PROMO_SK, - HIVE.STORE_SALES_ORC.SS_TICKET_NUMBER, - HIVE.STORE_SALES_ORC.SS_QUANTITY, - HIVE.STORE_SALES_ORC.SS_WHOLESALE_COST, - HIVE.STORE_SALES_ORC.SS_LIST_PRICE, - HIVE.STORE_SALES_ORC.SS_SALES_PRICE, - HIVE.STORE_SALES_ORC.SS_EXT_DISCOUNT_AMT, - HIVE.STORE_SALES_ORC.SS_EXT_SALES_PRICE, - HIVE.STORE_SALES_ORC.SS_EXT_WHOLESALE_COST, - HIVE.STORE_SALES_ORC.SS_EXT_LIST_PRICE, - HIVE.STORE_SALES_ORC.SS_EXT_TAX, - HIVE.STORE_SALES_ORC.SS_COUPON_AMT, - HIVE.STORE_SALES_ORC.SS_NET_PAID, - HIVE.STORE_SALES_ORC.SS_NET_PAID_INC_TAX, - HIVE.STORE_SALES_ORC.SS_NET_PROFIT - input_variables ........ %(1) - - -ESP_EXCHANGE ============================== SEQ_NO 2 ONLY CHILD 1 -REQUESTS_IN .............. 1 -ROWS_OUT ............. 1,698 -EST_OPER_COST ............ 0.01 -EST_TOTAL_COST ......... 191.52 -DESCRIPTION - max_card_est ........... 2.73638e+06 - fragment_id ............ 2 - parent_frag ............ 0 - fragment_type .......... esp - est_memory_per_node .... 29.297 KB - record_length ........ 132 - buffer_size ........ 5,000 - parent_processes ....... 1 - child_processes ........ 4 - child_partitioning_func hash2 partitioned 4 ways on (randomNum) - - -ORC_SCAN ================================== SEQ_NO 1 NO CHILDREN -TABLE_NAME ............... HIVE.HIVE.STORE_SALES_ORC -REQUESTS_IN .............. 1 -ROWS_OUT ............. 1,698 -EST_OPER_COST .......... 191.52 -EST_TOTAL_COST ......... 191.52 -DESCRIPTION - max_card_est ........... 2.73638e+06 - fragment_id ............ 2 - parent_frag ............ 0 - fragment_type .......... esp - record_length ........ 132 - scan_type .............. full scan of table HIVE.HIVE.STORE_SALES_ORC - object_type ............ Hive_Orc - scan_direction ......... forward - lock_mode .............. not specified, defaulted to lock cursor - access_mode ............ not specified, defaulted to read committed - columns_retrieved ..... 23 - probes ................. 1 - rows_accessed .......... 2.8804e+06 - orc_pred_pushdown ...... yes - orc_search_arguments ... and( HIVE.STORE_SALES_ORC.SS_ITEM_SK = %(1) ) - executor_predicates .... (HIVE.STORE_SALES_ORC.SS_ITEM_SK = %(1)) - ---- SQL operation complete. ->> ->>-- join with nested join ->>cqd orc_njs 'on'; - ---- SQL operation complete. ->>control query shape nested_join(scan(path 'CUSTOMER_ORC'), -+> scan(path 'STORE_SALES_ORC')); - ---- SQL operation complete. ->>prepare s from select * from customer_orc, store_sales_orc -+> where store_sales_orc.ss_item_sk = customer_orc.c_customer_sk; - ---- SQL command prepared. ->>explain options 'f' s; - -LC RC OP OPERATOR OPT DESCRIPTION CARD ----- ---- ---- -------------------- -------- -------------------- --------- - -3 . 4 root 2.88E+006 -1 2 3 nested_join 2.88E+006 -. . 2 orc_scan STORE_SALES_ORC 2.88E+001 -. . 1 orc_scan CUSTOMER_ORC 1.00E+005 - ---- SQL operation complete. ->>explain s; - ------------------------------------------------------------------- PLAN SUMMARY -MODULE_NAME .............. DYNAMICALLY COMPILED -STATEMENT_NAME ........... S -<<<<<<< HEAD -PLAN_ID .................. 212378682032886873 -======= -PLAN_ID .................. 212363939416859647 ->>>>>>> 1830668... Enhanced checktests changes. Jenkins usage not yet enabled. -ROWS_OUT ......... 2,880,404 -EST_TOTAL_COST 23,225,420 -STATEMENT ................ select * - from customer_orc, store_sales_orc - where store_sales_orc.ss_item_sk = - customer_orc.c_customer_sk; -MUST_MATCH ............... forced nested join(forced scan, forced scan) - - ------------------------------------------------------------------- NODE LISTING -ROOT ====================================== SEQ_NO 4 ONLY CHILD 3 -REQUESTS_IN .............. 1 -ROWS_OUT ......... 2,880,404 -EST_OPER_COST ............ 0 -EST_TOTAL_COST 23,225,420 -DESCRIPTION - est_memory_per_node .... 10240.00(Limit), 0.00(BMOs), 0.00(nBMOs) MB - max_card_est ........... 2.8804e+06 - fragment_id ............ 0 - parent_frag ............ (none) - fragment_type .......... master - record_length ........ 402 - statement_index ........ 0 - affinity_value ......... 0 - max_max_cardinal 2,880,404 - xn_access_mode ......... read_only - xn_autoabort_interval 0 - auto_query_retry ....... enabled - plan_version ....... 2,600 - embedded_arkcmp ........ used - IS_SQLCI ............... ON - LDAP_USERNAME .......... NOT AVAILABLE -<<<<<<< HEAD - HBASE_FILTER_PREDS ..... 2 - TRAF_INDEX_CREATE_OPT ON - TRAF_USE_REGION_XN ..... ON -======= - MODE_SEABASE ........... ON - SEABASE_VOLATILE_TABLES ON - HBASE_ASYNC_DROP_TABLE OFF - HBASE_SERIALIZATION .... ON - HBASE_SMALL_SCANNER .... SYSTEM - HBASE_FILTER_PREDS ..... 2 - TRAF_ALIGNED_ROW_FORMAT ON - TRAF_INDEX_CREATE_OPT ON ->>>>>>> 1830668... Enhanced checktests changes. Jenkins usage not yet enabled. - HIVE_MAX_STRING_LENGTH 20 - MODE_SEAHIVE ........... ON - TRAF_ENABLE_ORC_FORMAT ON - HIST_ROWCOUNT_REQU 50,000 - HIVE_USE_EXT_TABLE_ATTR ON - HIST_MISSING_STATS_WARN 0 - ORC_NJS_PROBES_T 1,000,000 - HIVE_MIN_NUM_ESPS_PER_D 0 - ORC_COLUMNS_PUSHDOWN ... ON - ORC_PRED_PUSHDOWN ...... ON - ORC_AGGR_PUSHDOWN ...... OFF - SCHEMA ................. HIVE.HIVE - ORC_NJS ................ ON - select_list ............ HIVE.CUSTOMER_ORC.C_CUSTOMER_SK, - HIVE.CUSTOMER_ORC.C_CUSTOMER_ID, - HIVE.CUSTOMER_ORC.C_CURRENT_CDEMO_SK, - HIVE.CUSTOMER_ORC.C_CURRENT_HDEMO_SK, - HIVE.CUSTOMER_ORC.C_CURRENT_ADDR_SK, - HIVE.CUSTOMER_ORC.C_FIRST_SHIPTO_DATE_SK, - HIVE.CUSTOMER_ORC.C_FIRST_SALES_DATE_SK, - HIVE.CUSTOMER_ORC.C_SALUTATION, - HIVE.CUSTOMER_ORC.C_FIRST_NAME, - HIVE.CUSTOMER_ORC.C_LAST_NAME, - HIVE.CUSTOMER_ORC.C_PREFERRED_CUST_FLAG, - HIVE.CUSTOMER_ORC.C_BIRTH_DAY, - HIVE.CUSTOMER_ORC.C_BIRTH_MONTH, - HIVE.CUSTOMER_ORC.C_BIRTH_YEAR, - HIVE.CUSTOMER_ORC.C_BIRTH_COUNTRY, - HIVE.CUSTOMER_ORC.C_LOGIN, - HIVE.CUSTOMER_ORC.C_EMAIL_ADDRESS, - HIVE.CUSTOMER_ORC.C_LAST_REVIEW_DATE, - HIVE.STORE_SALES_ORC.SS_SOLD_DATE_SK, - HIVE.STORE_SALES_ORC.SS_SOLD_TIME_SK, - HIVE.CUSTOMER_ORC.C_CUSTOMER_SK, - HIVE.STORE_SALES_ORC.SS_CUSTOMER_SK, - HIVE.STORE_SALES_ORC.SS_CDEMO_SK, - HIVE.STORE_SALES_ORC.SS_HDEMO_SK, - HIVE.STORE_SALES_ORC.SS_ADDR_SK, - HIVE.STORE_SALES_ORC.SS_STORE_SK, - HIVE.STORE_SALES_ORC.SS_PROMO_SK, - HIVE.STORE_SALES_ORC.SS_TICKET_NUMBER, - HIVE.STORE_SALES_ORC.SS_QUANTITY, - HIVE.STORE_SALES_ORC.SS_WHOLESALE_COST, - HIVE.STORE_SALES_ORC.SS_LIST_PRICE, - HIVE.STORE_SALES_ORC.SS_SALES_PRICE, - HIVE.STORE_SALES_ORC.SS_EXT_DISCOUNT_AMT, - HIVE.STORE_SALES_ORC.SS_EXT_SALES_PRICE, - HIVE.STORE_SALES_ORC.SS_EXT_WHOLESALE_COST, - HIVE.STORE_SALES_ORC.SS_EXT_LIST_PRICE, - HIVE.STORE_SALES_ORC.SS_EXT_TAX, - HIVE.STORE_SALES_ORC.SS_COUPON_AMT, - HIVE.STORE_SALES_ORC.SS_NET_PAID, - HIVE.STORE_SALES_ORC.SS_NET_PAID_INC_TAX, - HIVE.STORE_SALES_ORC.SS_NET_PROFIT - - -NESTED_JOIN =============================== SEQ_NO 3 CHILDREN 1, 2 -REQUESTS_IN .............. 1 -ROWS_OUT ......... 2,880,404 -EST_OPER_COST ............ 0.5 -EST_TOTAL_COST 23,225,420 -DESCRIPTION - max_card_est ........... 2.8804e+06 - fragment_id ............ 0 - parent_frag ............ (none) - fragment_type .......... master - record_length ........ 402 - join_type .............. inner - join_method ............ nested - - -ORC_SCAN ================================== SEQ_NO 2 NO CHILDREN -TABLE_NAME ............... HIVE.HIVE.STORE_SALES_ORC -REQUESTS_IN ........ 100,000 -ROWS/REQUEST ............ 28.8 -EST_OPER_COST ... 23,225,410 -EST_TOTAL_COST 23,225,410 -DESCRIPTION - max_card_est ........... 2.8804e+06 - fragment_id ............ 0 - parent_frag ............ (none) - fragment_type .......... master - record_length ........ 132 - scan_type .............. full scan of table HIVE.HIVE.STORE_SALES_ORC - object_type ............ Hive_Orc - scan_direction ......... forward - lock_mode .............. not specified, defaulted to lock cursor - access_mode ............ not specified, defaulted to read committed - columns_retrieved ..... 23 - probes ........... 100,000 - successful_probes 100,000 - unique_probes ...... 5,000 - duplicated_succ_pr 95,000 - rows_accessed .......... 2.8804e+06 - orc_pred_pushdown ...... yes - orc_search_arguments ... and( HIVE.STORE_SALES_ORC.SS_ITEM_SK = - HIVE.CUSTOMER_ORC.C_CUSTOMER_SK ) - executor_predicates .... (HIVE.STORE_SALES_ORC.SS_ITEM_SK = - HIVE.CUSTOMER_ORC.C_CUSTOMER_SK) - - -ORC_SCAN ================================== SEQ_NO 1 NO CHILDREN -TABLE_NAME ............... HIVE.HIVE.CUSTOMER_ORC -REQUESTS_IN .............. 1 -ROWS_OUT ........... 100,000 -EST_OPER_COST ............ 7.71 -EST_TOTAL_COST ........... 7.71 -DESCRIPTION - max_card_est ..... 100,000 - fragment_id ............ 0 - parent_frag ............ (none) - fragment_type .......... master - record_length ........ 270 - scan_type .............. full scan of table HIVE.HIVE.CUSTOMER_ORC - object_type ............ Hive_Orc - scan_direction ......... forward - lock_mode .............. not specified, defaulted to lock cursor - access_mode ............ not specified, defaulted to read committed - columns_retrieved ..... 18 - probes ................. 1 - rows_accessed .... 100,000 - orc_pred_pushdown ...... yes - orc_search_arguments ... and( not( HIVE.CUSTOMER_ORC.C_CUSTOMER_SK is null ) - ) - executor_predicates .... HIVE.CUSTOMER_ORC.C_CUSTOMER_SK is not null - ---- SQL operation complete. ->>cqd orc_njs reset; - ---- SQL operation complete. ->> ->>-- join with parallel nested join ->> ->>control query shape off; - ---- SQL operation complete. ->>cqd HIVE_USE_EXT_TABLE_ATTRS 'off'; - ---- SQL operation complete. ->>cqd ncm_orc_costing 'on'; - ---- SQL operation complete. ->>cqd orc_njs 'on'; - ---- SQL operation complete. ->>cqd parallel_num_esps '4'; - ---- SQL operation complete. ->> ->>prepare s from -+>select [last 0] ss_net_profit from -+>date_dim_orc dim, store_sales_sorted_orc ss -+>where -+>dim.d_date_sk = ss.ss_sold_date_sk -+>and d_year in (2001) and d_dom = 30 -- produce 12 rows -+>; - ---- SQL command prepared. ->> ->>explain options 'f' s; - -LC RC OP OPERATOR OPT DESCRIPTION CARD ----- ---- ---- -------------------- -------- -------------------- --------- - -6 . 7 root 1.00E+000 -5 . 6 firstn 1.00E+000 -4 . 5 esp_exchange 1:4(hash2) 2.88E+006 -3 2 4 hybrid_hash_join 2.88E+006 -. . 3 orc_scan STORE_SALES_SORTED_O 2.88E+006 -1 . 2 esp_exchange 4(rep-b):4(hash2) 2.00E+000 -. . 1 orc_scan DATE_DIM_ORC 2.00E+000 - ---- SQL operation complete. ->> ->>cqd HIVE_USE_EXT_TABLE_ATTRS reset; - ---- SQL operation complete. ->>cqd ncm_orc_costing reset; - ---- SQL operation complete. ->>cqd orc_njs reset; - ---- SQL operation complete. ->>cqd parallel_num_esps reset; - ---- SQL operation complete. ->> ->> ->>-- more external table tests ->> ->>control query shape cut; - ---- SQL operation complete. ->>set schema trafodion.sch; - ---- SQL operation complete. ->>drop external table if exists date_dim_orc for hive.hive.date_dim_orc; - ---- SQL operation complete. ->>cqd volatile_table_find_suitable_key 'SYSTEM'; - ---- SQL operation complete. ->>create external table date_dim_orc -+> (d_date_sk int, d_date_id varchar(100 bytes) character set utf8, d_date date, -+> d_month_seq int, d_week_seq int, d_quarter_seq int, d_year int, d_dow int, -+> d_moy int, d_dom int, d_qoy int, d_fy_year int, d_fy_quarter_seq int, -+> d_fy_week_seq int, -+> d_day_name varchar(120 bytes) character set utf8, d_quarter_name varchar(200 bytes) character set utf8, d_holiday varchar(100 bytes) character set utf8, -+> d_weekend varchar(100 bytes) character set utf8, d_following_holiday varchar(100 bytes) character set utf8, -+> d_first_dom int, d_last_dom int, d_same_day_ly int, d_same_day_lq int, -+> d_current_day varchar(100 bytes) character set utf8, d_current_week varchar(111 bytes) character set utf8, -+> d_current_month varchar(200 bytes) character set utf8, d_current_quarter varchar(100 bytes) character set utf8, -+> d_current_year varchar(100 bytes) character set utf8) -+> for hive.hive.date_dim_orc; - ---- SQL operation complete. ->>invoke hive.hive.date_dim_orc; - --- Definition of hive table DATE_DIM_ORC -<<<<<<< HEAD --- Definition current Wed Nov 29 02:20:37 2017 -======= --- Definition current Sun Jun 11 11:10:29 2017 ->>>>>>> 1830668... Enhanced checktests changes. Jenkins usage not yet enabled. - - ( - D_DATE_SK INT - , D_DATE_ID VARCHAR(100 BYTES) CHARACTER SET UTF8 - COLLATE DEFAULT - , D_DATE DATE - , D_MONTH_SEQ INT - , D_WEEK_SEQ INT - , D_QUARTER_SEQ INT - , D_YEAR INT - , D_DOW INT - , D_MOY INT - , D_DOM INT - , D_QOY INT - , D_FY_YEAR INT - , D_FY_QUARTER_SEQ INT - , D_FY_WEEK_SEQ INT - , D_DAY_NAME VARCHAR(120 BYTES) CHARACTER SET UTF8 - COLLATE DEFAULT - , D_QUARTER_NAME VARCHAR(200 BYTES) CHARACTER SET UTF8 - COLLATE DEFAULT - , D_HOLIDAY VARCHAR(100 BYTES) CHARACTER SET UTF8 - COLLATE DEFAULT - , D_WEEKEND VARCHAR(100 BYTES) CHARACTER SET UTF8 - COLLATE DEFAULT - , D_FOLLOWING_HOLIDAY VARCHAR(100 BYTES) CHARACTER SET UTF8 - COLLATE DEFAULT - , D_FIRST_DOM INT - , D_LAST_DOM INT - , D_SAME_DAY_LY INT - , D_SAME_DAY_LQ INT - , D_CURRENT_DAY VARCHAR(100 BYTES) CHARACTER SET UTF8 - COLLATE DEFAULT - , D_CURRENT_WEEK VARCHAR(111 BYTES) CHARACTER SET UTF8 - COLLATE DEFAULT - , D_CURRENT_MONTH VARCHAR(200 BYTES) CHARACTER SET UTF8 - COLLATE DEFAULT - , D_CURRENT_QUARTER VARCHAR(100 BYTES) CHARACTER SET UTF8 - COLLATE DEFAULT - , D_CURRENT_YEAR VARCHAR(100 BYTES) CHARACTER SET UTF8 - COLLATE DEFAULT - ) - /* stored as orc */ - ---- SQL operation complete. ->>showddl hive.hive.date_dim_orc; - -/* Hive DDL */ -CREATE TABLE DEFAULT.DATE_DIM_ORC - ( - D_DATE_SK int - , D_DATE_ID string - , D_DATE date - , D_MONTH_SEQ int - , D_WEEK_SEQ int - , D_QUARTER_SEQ int - , D_YEAR int - , D_DOW int - , D_MOY int - , D_DOM int - , D_QOY int - , D_FY_YEAR int - , D_FY_QUARTER_SEQ int - , D_FY_WEEK_SEQ int - , D_DAY_NAME string - , D_QUARTER_NAME string - , D_HOLIDAY string - , D_WEEKEND string - , D_FOLLOWING_HOLIDAY string - , D_FIRST_DOM int - , D_LAST_DOM int - , D_SAME_DAY_LY int - , D_SAME_DAY_LQ int - , D_CURRENT_DAY string - , D_CURRENT_WEEK string - , D_CURRENT_MONTH string - , D_CURRENT_QUARTER string - , D_CURRENT_YEAR string - ) - stored as orc -; - -<<<<<<< HEAD -======= -REGISTER /*INTERNAL*/ HIVE TABLE HIVE.HIVE.DATE_DIM_ORC; -/* ObjectUID = 3064268403396931736 */ - ->>>>>>> 1830668... Enhanced checktests changes. Jenkins usage not yet enabled. -/* Trafodion DDL */ - -REGISTER /*INTERNAL*/ HIVE TABLE HIVE.HIVE.DATE_DIM_ORC; -/* ObjectUID = 8954428504972159626 */ - -CREATE EXTERNAL TABLE DATE_DIM_ORC - ( - D_DATE_SK INT DEFAULT NULL - , D_DATE_ID VARCHAR(100 BYTES) CHARACTER SET UTF8 - COLLATE DEFAULT DEFAULT NULL - , D_DATE DATE DEFAULT NULL - , D_MONTH_SEQ INT DEFAULT NULL - , D_WEEK_SEQ INT DEFAULT NULL - , D_QUARTER_SEQ INT DEFAULT NULL - , D_YEAR INT DEFAULT NULL - , D_DOW INT DEFAULT NULL - , D_MOY INT DEFAULT NULL - , D_DOM INT DEFAULT NULL - , D_QOY INT DEFAULT NULL - , D_FY_YEAR INT DEFAULT NULL - , D_FY_QUARTER_SEQ INT DEFAULT NULL - , D_FY_WEEK_SEQ INT DEFAULT NULL - , D_DAY_NAME VARCHAR(120 BYTES) CHARACTER SET UTF8 - COLLATE DEFAULT DEFAULT NULL - , D_QUARTER_NAME VARCHAR(200 BYTES) CHARACTER SET UTF8 - COLLATE DEFAULT DEFAULT NULL - , D_HOLIDAY VARCHAR(100 BYTES) CHARACTER SET UTF8 - COLLATE DEFAULT DEFAULT NULL - , D_WEEKEND VARCHAR(100 BYTES) CHARACTER SET UTF8 - COLLATE DEFAULT DEFAULT NULL - , D_FOLLOWING_HOLIDAY VARCHAR(100 BYTES) CHARACTER SET UTF8 - COLLATE DEFAULT DEFAULT NULL - , D_FIRST_DOM INT DEFAULT NULL - , D_LAST_DOM INT DEFAULT NULL - , D_SAME_DAY_LY INT DEFAULT NULL - , D_SAME_DAY_LQ INT DEFAULT NULL - , D_CURRENT_DAY VARCHAR(100 BYTES) CHARACTER SET UTF8 - COLLATE DEFAULT DEFAULT NULL - , D_CURRENT_WEEK VARCHAR(111 BYTES) CHARACTER SET UTF8 - COLLATE DEFAULT DEFAULT NULL - , D_CURRENT_MONTH VARCHAR(200 BYTES) CHARACTER SET UTF8 - COLLATE DEFAULT DEFAULT NULL - , D_CURRENT_QUARTER VARCHAR(100 BYTES) CHARACTER SET UTF8 - COLLATE DEFAULT DEFAULT NULL - , D_CURRENT_YEAR VARCHAR(100 BYTES) CHARACTER SET UTF8 - COLLATE DEFAULT DEFAULT NULL - ) - FOR HIVE.HIVE.DATE_DIM_ORC -; - ---- SQL operation complete. ->>prepare s from select * from hive.hive.date_dim_orc where d_date = date '2016-01-27'; - ---- SQL command prepared. ->>explain s; - ------------------------------------------------------------------- PLAN SUMMARY -MODULE_NAME .............. DYNAMICALLY COMPILED -STATEMENT_NAME ........... S -<<<<<<< HEAD -PLAN_ID .................. 212378682040442348 -======= -PLAN_ID .................. 212363939435356144 ->>>>>>> 1830668... Enhanced checktests changes. Jenkins usage not yet enabled. -ROWS_OUT ............... 271 -EST_TOTAL_COST ........... 0.66 -STATEMENT ................ select * - from hive.hive.date_dim_orc - where d_date = date '2016-01-27'; - - ------------------------------------------------------------------- NODE LISTING -ROOT ====================================== SEQ_NO 2 ONLY CHILD 1 -REQUESTS_IN .............. 1 -ROWS_OUT ............... 271 -EST_OPER_COST ............ 0 -EST_TOTAL_COST ........... 0.66 -DESCRIPTION - est_memory_per_node .... 10240.00(Limit), 0.00(BMOs), 0.00(nBMOs) MB - max_card_est ...... 69,397.5 - fragment_id ............ 0 - parent_frag ............ (none) - fragment_type .......... master - record_length ...... 1,477 - statement_index ........ 0 - affinity_value ......... 0 - max_max_cardinality 271 - xn_access_mode ......... read_only - xn_autoabort_interval 0 - auto_query_retry ....... enabled - plan_version ....... 2,600 - embedded_arkcmp ........ used - IS_SQLCI ............... ON - LDAP_USERNAME .......... NOT AVAILABLE -<<<<<<< HEAD - HBASE_FILTER_PREDS ..... 2 - TRAF_INDEX_CREATE_OPT ON - TRAF_USE_REGION_XN ..... ON -======= - MODE_SEABASE ........... ON - SEABASE_VOLATILE_TABLES ON - HBASE_ASYNC_DROP_TABLE OFF - HBASE_SERIALIZATION .... ON - HBASE_SMALL_SCANNER .... SYSTEM - HBASE_FILTER_PREDS ..... 2 - TRAF_ALIGNED_ROW_FORMAT ON - TRAF_INDEX_CREATE_OPT ON ->>>>>>> 1830668... Enhanced checktests changes. Jenkins usage not yet enabled. - HIVE_MAX_STRING_LENGTH 20 - MODE_SEAHIVE ........... ON - TRAF_ENABLE_ORC_FORMAT ON - HIST_ROWCOUNT_REQU 50,000 - HIST_MISSING_STATS_WARN 0 - ORC_NJS_PROBES_T 1,000,000 - HIVE_MIN_NUM_ESPS_PER_D 0 - ORC_COLUMNS_PUSHDOWN ... ON - ORC_PRED_PUSHDOWN ...... ON - ORC_AGGR_PUSHDOWN ...... OFF - SCHEMA ................. TRAFODION.SCH - VOLATILE_TABLE_FIND_SUI SYSTEM - select_list ............ HIVE.DATE_DIM_ORC.D_DATE_SK, - HIVE.DATE_DIM_ORC.D_DATE_ID, %(2016-01-27), - HIVE.DATE_DIM_ORC.D_MONTH_SEQ, - HIVE.DATE_DIM_ORC.D_WEEK_SEQ, - HIVE.DATE_DIM_ORC.D_QUARTER_SEQ, - HIVE.DATE_DIM_ORC.D_YEAR, HIVE.DATE_DIM_ORC.D_DOW, - HIVE.DATE_DIM_ORC.D_MOY, HIVE.DATE_DIM_ORC.D_DOM, - HIVE.DATE_DIM_ORC.D_QOY, - HIVE.DATE_DIM_ORC.D_FY_YEAR, - HIVE.DATE_DIM_ORC.D_FY_QUARTER_SEQ, - HIVE.DATE_DIM_ORC.D_FY_WEEK_SEQ, - HIVE.DATE_DIM_ORC.D_DAY_NAME, - HIVE.DATE_DIM_ORC.D_QUARTER_NAME, - HIVE.DATE_DIM_ORC.D_HOLIDAY, - HIVE.DATE_DIM_ORC.D_WEEKEND, - HIVE.DATE_DIM_ORC.D_FOLLOWING_HOLIDAY, - HIVE.DATE_DIM_ORC.D_FIRST_DOM, - HIVE.DATE_DIM_ORC.D_LAST_DOM, - HIVE.DATE_DIM_ORC.D_SAME_DAY_LY, - HIVE.DATE_DIM_ORC.D_SAME_DAY_LQ, - HIVE.DATE_DIM_ORC.D_CURRENT_DAY, - HIVE.DATE_DIM_ORC.D_CURRENT_WEEK, - HIVE.DATE_DIM_ORC.D_CURRENT_MONTH, - HIVE.DATE_DIM_ORC.D_CURRENT_QUARTER, - HIVE.DATE_DIM_ORC.D_CURRENT_YEAR - input_variables ........ %(2016-01-27) - - -ORC_SCAN ================================== SEQ_NO 1 NO CHILDREN -TABLE_NAME ............... HIVE.HIVE.DATE_DIM_ORC -REQUESTS_IN .............. 1 -ROWS_OUT ............... 271 -EST_OPER_COST ............ 0.66 -EST_TOTAL_COST ........... 0.66 -DESCRIPTION - max_card_est ...... 69,397.5 - fragment_id ............ 0 - parent_frag ............ (none) - fragment_type .......... master - record_length ...... 1,471 - scan_type .............. full scan of table HIVE.HIVE.DATE_DIM_ORC - object_type ............ Hive_Orc - scan_direction ......... forward - lock_mode .............. not specified, defaulted to lock cursor - access_mode ............ not specified, defaulted to read committed - columns_retrieved ..... 28 - probes ................. 1 - rows_accessed ..... 73,049 - orc_pred_pushdown ...... yes - orc_search_arguments ... and( HIVE.DATE_DIM_ORC.D_DATE = %(2016-01-27) ) - executor_predicates .... (HIVE.DATE_DIM_ORC.D_DATE = %(2016-01-27)) - ---- SQL operation complete. ->> ->>drop external table if exists date_dim_orc for hive.hive.date_dim_orc; - ---- SQL operation complete. ->>create external table date_dim_orc -+> (d_date_sk int, d_date_id varchar(100 bytes) character set utf8, d_date date) -+> for hive.hive.date_dim_orc; - ---- SQL operation complete. ->>invoke hive.hive.date_dim_orc; - --- Definition of hive table DATE_DIM_ORC -<<<<<<< HEAD --- Definition current Wed Nov 29 02:20:44 2017 -======= --- Definition current Sun Jun 11 11:10:50 2017 ->>>>>>> 1830668... Enhanced checktests changes. Jenkins usage not yet enabled. - - ( - D_DATE_SK INT - , D_DATE_ID VARCHAR(100 BYTES) CHARACTER SET UTF8 - COLLATE DEFAULT - , D_DATE DATE - , D_MONTH_SEQ INT - , D_WEEK_SEQ INT - , D_QUARTER_SEQ INT - , D_YEAR INT - , D_DOW INT - , D_MOY INT - , D_DOM INT - , D_QOY INT - , D_FY_YEAR INT - , D_FY_QUARTER_SEQ INT - , D_FY_WEEK_SEQ INT - , D_DAY_NAME VARCHAR(20 BYTES) CHARACTER SET UTF8 - COLLATE DEFAULT - , D_QUARTER_NAME VARCHAR(20 BYTES) CHARACTER SET UTF8 - COLLATE DEFAULT - , D_HOLIDAY VARCHAR(20 BYTES) CHARACTER SET UTF8 - COLLATE DEFAULT - , D_WEEKEND VARCHAR(20 BYTES) CHARACTER SET UTF8 - COLLATE DEFAULT - , D_FOLLOWING_HOLIDAY VARCHAR(20 BYTES) CHARACTER SET UTF8 - COLLATE DEFAULT - , D_FIRST_DOM INT - , D_LAST_DOM INT - , D_SAME_DAY_LY INT - , D_SAME_DAY_LQ INT - , D_CURRENT_DAY VARCHAR(20 BYTES) CHARACTER SET UTF8 - COLLATE DEFAULT - , D_CURRENT_WEEK VARCHAR(20 BYTES) CHARACTER SET UTF8 - COLLATE DEFAULT - , D_CURRENT_MONTH VARCHAR(20 BYTES) CHARACTER SET UTF8 - COLLATE DEFAULT - , D_CURRENT_QUARTER VARCHAR(20 BYTES) CHARACTER SET UTF8 - COLLATE DEFAULT - , D_CURRENT_YEAR VARCHAR(20 BYTES) CHARACTER SET UTF8 - COLLATE DEFAULT - ) - /* stored as orc */ - ---- SQL operation complete. ->>showddl hive.hive.date_dim_orc; - -/* Hive DDL */ -CREATE TABLE DEFAULT.DATE_DIM_ORC - ( - D_DATE_SK int - , D_DATE_ID string - , D_DATE date - , D_MONTH_SEQ int - , D_WEEK_SEQ int - , D_QUARTER_SEQ int - , D_YEAR int - , D_DOW int - , D_MOY int - , D_DOM int - , D_QOY int - , D_FY_YEAR int - , D_FY_QUARTER_SEQ int - , D_FY_WEEK_SEQ int - , D_DAY_NAME string - , D_QUARTER_NAME string - , D_HOLIDAY string - , D_WEEKEND string - , D_FOLLOWING_HOLIDAY string - , D_FIRST_DOM int - , D_LAST_DOM int - , D_SAME_DAY_LY int - , D_SAME_DAY_LQ int - , D_CURRENT_DAY string - , D_CURRENT_WEEK string - , D_CURRENT_MONTH string - , D_CURRENT_QUARTER string - , D_CURRENT_YEAR string - ) - stored as orc -; - -<<<<<<< HEAD -======= -REGISTER /*INTERNAL*/ HIVE TABLE HIVE.HIVE.DATE_DIM_ORC; -/* ObjectUID = 3064268403396931736 */ - ->>>>>>> 1830668... Enhanced checktests changes. Jenkins usage not yet enabled. -/* Trafodion DDL */ - -REGISTER /*INTERNAL*/ HIVE TABLE HIVE.HIVE.DATE_DIM_ORC; -/* ObjectUID = 8954428504972159626 */ - -CREATE EXTERNAL TABLE DATE_DIM_ORC - ( - D_DATE_SK INT DEFAULT NULL - , D_DATE_ID VARCHAR(100 BYTES) CHARACTER SET UTF8 - COLLATE DEFAULT DEFAULT NULL - , D_DATE DATE DEFAULT NULL - , D_MONTH_SEQ INT DEFAULT NULL - , D_WEEK_SEQ INT DEFAULT NULL - , D_QUARTER_SEQ INT DEFAULT NULL - , D_YEAR INT DEFAULT NULL - , D_DOW INT DEFAULT NULL - , D_MOY INT DEFAULT NULL - , D_DOM INT DEFAULT NULL - , D_QOY INT DEFAULT NULL - , D_FY_YEAR INT DEFAULT NULL - , D_FY_QUARTER_SEQ INT DEFAULT NULL - , D_FY_WEEK_SEQ INT DEFAULT NULL - , D_DAY_NAME VARCHAR(20 BYTES) CHARACTER SET UTF8 - COLLATE DEFAULT DEFAULT NULL - , D_QUARTER_NAME VARCHAR(20 BYTES) CHARACTER SET UTF8 - COLLATE DEFAULT DEFAULT NULL - , D_HOLIDAY VARCHAR(20 BYTES) CHARACTER SET UTF8 - COLLATE DEFAULT DEFAULT NULL - , D_WEEKEND VARCHAR(20 BYTES) CHARACTER SET UTF8 - COLLATE DEFAULT DEFAULT NULL - , D_FOLLOWING_HOLIDAY VARCHAR(20 BYTES) CHARACTER SET UTF8 - COLLATE DEFAULT DEFAULT NULL - , D_FIRST_DOM INT DEFAULT NULL - , D_LAST_DOM INT DEFAULT NULL - , D_SAME_DAY_LY INT DEFAULT NULL - , D_SAME_DAY_LQ INT DEFAULT NULL - , D_CURRENT_DAY VARCHAR(20 BYTES) CHARACTER SET UTF8 - COLLATE DEFAULT DEFAULT NULL - , D_CURRENT_WEEK VARCHAR(20 BYTES) CHARACTER SET UTF8 - COLLATE DEFAULT DEFAULT NULL - , D_CURRENT_MONTH VARCHAR(20 BYTES) CHARACTER SET UTF8 - COLLATE DEFAULT DEFAULT NULL - , D_CURRENT_QUARTER VARCHAR(20 BYTES) CHARACTER SET UTF8 - COLLATE DEFAULT DEFAULT NULL - , D_CURRENT_YEAR VARCHAR(20 BYTES) CHARACTER SET UTF8 - COLLATE DEFAULT DEFAULT NULL - ) - FOR HIVE.HIVE.DATE_DIM_ORC -; - ---- SQL operation complete. ->>prepare s from select * from hive.hive.date_dim_orc where d_date = date '2016-01-27'; - ---- SQL command prepared. ->>explain s; - ------------------------------------------------------------------- PLAN SUMMARY -MODULE_NAME .............. DYNAMICALLY COMPILED -STATEMENT_NAME ........... S -<<<<<<< HEAD -PLAN_ID .................. 212378682040442348 -======= -PLAN_ID .................. 212363939456056239 ->>>>>>> 1830668... Enhanced checktests changes. Jenkins usage not yet enabled. -ROWS_OUT ............... 271 -EST_TOTAL_COST ........... 0.66 -STATEMENT ................ select * - from hive.hive.date_dim_orc - where d_date = date '2016-01-27'; - - ------------------------------------------------------------------- NODE LISTING -ROOT ====================================== SEQ_NO 2 ONLY CHILD 1 -REQUESTS_IN .............. 1 -ROWS_OUT ............... 271 -EST_OPER_COST ............ 0 -EST_TOTAL_COST ........... 0.66 -DESCRIPTION - est_memory_per_node .... 10240.00(Limit), 0.00(BMOs), 0.00(nBMOs) MB - max_card_est ...... 69,397.5 - fragment_id ............ 0 - parent_frag ............ (none) - fragment_type .......... master - record_length ...... 1,477 - statement_index ........ 0 - affinity_value ......... 0 - max_max_cardinality 271 - xn_access_mode ......... read_only - xn_autoabort_interval 0 - auto_query_retry ....... enabled - plan_version ....... 2,600 - embedded_arkcmp ........ used - IS_SQLCI ............... ON - LDAP_USERNAME .......... NOT AVAILABLE -<<<<<<< HEAD - HBASE_FILTER_PREDS ..... 2 - TRAF_INDEX_CREATE_OPT ON - TRAF_USE_REGION_XN ..... ON -======= - MODE_SEABASE ........... ON - SEABASE_VOLATILE_TABLES ON - HBASE_ASYNC_DROP_TABLE OFF - HBASE_SERIALIZATION .... ON - HBASE_SMALL_SCANNER .... SYSTEM - HBASE_FILTER_PREDS ..... 2 - TRAF_ALIGNED_ROW_FORMAT ON - TRAF_INDEX_CREATE_OPT ON ->>>>>>> 1830668... Enhanced checktests changes. Jenkins usage not yet enabled. - HIVE_MAX_STRING_LENGTH 20 - MODE_SEAHIVE ........... ON - TRAF_ENABLE_ORC_FORMAT ON - HIST_ROWCOUNT_REQU 50,000 - HIST_MISSING_STATS_WARN 0 - ORC_NJS_PROBES_T 1,000,000 - HIVE_MIN_NUM_ESPS_PER_D 0 - ORC_COLUMNS_PUSHDOWN ... ON - ORC_PRED_PUSHDOWN ...... ON - ORC_AGGR_PUSHDOWN ...... OFF - SCHEMA ................. TRAFODION.SCH - VOLATILE_TABLE_F
<TRUNCATED>
