http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/6dd81240/core/sql/regress/hive/EXPECTED040 ---------------------------------------------------------------------- diff --git a/core/sql/regress/hive/EXPECTED040 b/core/sql/regress/hive/EXPECTED040 new file mode 100644 index 0000000..971c8f8 --- /dev/null +++ b/core/sql/regress/hive/EXPECTED040 @@ -0,0 +1,2527 @@ +>>obey TEST040(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_parquet_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 TEST040(tests); +>>-------------------------------------------------------------------------- +>>-- PARQUET file metadata info +>>--invoke hive.hive.store_parquet; +>>-- +>>-- select one row from PARQUET table +>>select [first 1] * from hive.hive.store_parquet; + +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 00:00:00.000000 ? 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 PARQUET table +>>select * from hive.hive.store_parquet 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 00:00:00.000000 ? 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 00:00:00.000000 2000-03-12 00:00:00.000000 ? 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 00:00:00.000000 ? ? 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 00:00:00.000000 1999-03-13 00:00:00.000000 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 00:00:00.000000 2001-03-12 00:00:00.000000 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 00:00:00.000000 ? ? 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 00:00:00.000000 ? ? 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 00:00:00.000000 2000-03-12 00:00:00.000000 ? 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 00:00:00.000000 ? ? 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 00:00:00.000000 1999-03-13 00:00:00.000000 ? 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 00:00:00.000000 2001-03-12 00:00:00.000000 ? 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 00:00:00.000000 ? ? 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_parquet 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_parquet; + +(EXPR) +-------------------- + + 12 + +--- 1 row(s) selected. +>> +>>-- explain of join between 2 PARQUET tables +>>prepare XX from select x.s_suite_number, y.s_street_name ++> from hive.hive.store_parquet x, hive.hive.store_parquet 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 PARQUET_SCAN ? ? STORE_PARQ +01 PARQUET_SCAN ? ? STORE_PARQ + +--- 4 row(s) selected. +>> +>>-- execute of join between 2 PARQUET 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 PARQUET tables +>>control query shape join(scan(path 'STORE'), scan(path 'STORE_PARQUET')); + +--- SQL operation complete. +>>prepare XX from select x.s_suite_number, y.s_street_name ++> from hive.hive.store x, hive.hive.store_parquet y ++> where x.s_store_sk = y.s_store_sk; + +--- SQL command prepared. +>>control query shape cut; + +--- SQL operation complete. +>>execute explainIt; + +S OPERATOR LC RC TAB_NAME +-- ---------------- -- -- ---------- + +04 ROOT 3 ? +03 HYBRID_HASH_JOIN 2 1 +02 HIVE_SCAN ? ? STORE +01 PARQUET_SCAN ? ? STORE_PARQ + +--- 4 row(s) selected. +>> +>>-- execute of join between hive(hdfs) and PARQUET 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 parquet_columns_pushdown 'OFF'; + +--- SQL operation complete. +>>showplan option 'tr' select s_store_sk from store_parquet; +MASTER Executor fragment +======================== + +Fragment ID: 0, Length: 58712 + +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 +explain_plan_size = 3112 + +# 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_PARQUET +type_ = PARQUET +hostName_ = localhost, port_ = 36000 +recordDelimiter_ = 10, columnDelimiter_ = 124 +hdfsBufSize_ = 735, rangeTailIOSize_ = 20984, hdfsSqlMaxRecLen_ = 8 +tuppIndex_ = 2, workAtpIndex_ = 3 +asciiTuppIndex_ = 4, asciiRowLen_ = 532 +moveExprColsTuppIndex_ = 2, moveExprColsRowLength_ = 576 +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 4 3681 store_parquet/000000_0 + +Summary of bytes read per ESP (3681 = 100 percent): + +ESP 0 reads 3681 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 +hdfsRootDir: hdfs://localhost:36000/user/trafodion/hive/tpcds/store_parquet +modTSforDir_ = 1511634536, numOfPartCols_ = 0 + +# 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_parquet; + +S_STORE_SK +----------- + + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10 + 11 + 12 + +--- 12 row(s) selected. +>>cqd parquet_columns_pushdown 'ON'; + +--- SQL operation complete. +>>showplan option 'tr' select s_store_sk from store_parquet; +MASTER Executor fragment +======================== + +Fragment ID: 0, Length: 14672 + +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 +explain_plan_size = 3104 + +# 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_PARQUET +type_ = PARQUET +hostName_ = localhost, port_ = 36000 +recordDelimiter_ = 10, columnDelimiter_ = 124 +hdfsBufSize_ = 115, rangeTailIOSize_ = 20984, 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 4 3681 store_parquet/000000_0 + +Summary of bytes read per ESP (3681 = 100 percent): + +ESP 0 reads 3681 bytes ( 100 percent of avg) + +Number of columns to retrieve: 1 +ColNumber: 1, ColName: S_STORE_SK +hdfsRootDir: hdfs://localhost:36000/user/trafodion/hive/tpcds/store_parquet +modTSforDir_ = 1511634536, numOfPartCols_ = 0 + +# 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_parquet; + +S_STORE_SK +----------- + + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10 + 11 + 12 + +--- 12 row(s) selected. +>> +>>-- predicate pushdown to PARQUET layer +>>cqd parquet_pred_pushdown 'OFF'; + +--- SQL operation complete. +>>select s_store_sk from store_parquet where s_store_sk = 3; + +S_STORE_SK +----------- + + 3 + +--- 1 row(s) selected. +>>select s_store_sk from store_parquet where s_store_sk < 2; + +S_STORE_SK +----------- + + 1 + +--- 1 row(s) selected. +>>select s_store_sk from store_parquet where s_store_sk >= 11; + +S_STORE_SK +----------- + + 11 + 12 + +--- 2 row(s) selected. +>>select s_store_sk from store_parquet 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_parquet 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_parquet where s_store_sk = 3; +MASTER Executor fragment +======================== + +Fragment ID: 0, Length: 14848 + +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 +explain_plan_size = 3208 + +# 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_PARQUET +type_ = PARQUET +hostName_ = localhost, port_ = 36000 +recordDelimiter_ = 10, columnDelimiter_ = 124 +hdfsBufSize_ = 115, rangeTailIOSize_ = 20984, 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 4 3681 store_parquet/000000_0 + +Summary of bytes read per ESP (3681 = 100 percent): + +ESP 0 reads 3681 bytes ( 100 percent of avg) + +Number of columns to retrieve: 1 +ColNumber: 1, ColName: S_STORE_SK +hdfsRootDir: hdfs://localhost:36000/user/trafodion/hive/tpcds/store_parquet +modTSforDir_ = 1511634536, numOfPartCols_ = 0 + +# 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 parquet_pred_pushdown 'ON'; + +--- SQL operation complete. +>>select s_store_sk from store_parquet where s_store_sk = 3; + +S_STORE_SK +----------- + + 3 + +--- 1 row(s) selected. +>>select s_store_sk from store_parquet where s_store_sk < 2; + +S_STORE_SK +----------- + + 1 + +--- 1 row(s) selected. +>>select s_store_sk from store_parquet where s_store_sk >= 11; + +S_STORE_SK +----------- + + 11 + 12 + +--- 2 row(s) selected. +>>select s_store_sk from store_parquet 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_parquet 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_parquet where s_store_sk = 3; +MASTER Executor fragment +======================== + +Fragment ID: 0, Length: 17104 + +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 +explain_plan_size = 3240 + +# 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_PARQUET +type_ = PARQUET +hostName_ = localhost, port_ = 36000 +recordDelimiter_ = 10, columnDelimiter_ = 124 +hdfsBufSize_ = 115, rangeTailIOSize_ = 20984, 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 4 3681 store_parquet/000000_0 + +Summary of bytes read per ESP (3681 = 100 percent): + +ESP 0 reads 3681 bytes ( 100 percent of avg) + +Number of columns to retrieve: 1 +ColNumber: 1, ColName: S_STORE_SK +hdfsRootDir: hdfs://localhost:36000/user/trafodion/hive/tpcds/store_parquet +modTSforDir_ = 1511634536, numOfPartCols_ = 0 + +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 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_parquet where ss_sold_date_sk = ss_item_sk ; + +------------------------------------------------------------------ PLAN SUMMARY +MODULE_NAME .............. DYNAMICALLY COMPILED +STATEMENT_NAME ........... NOT NAMED +PLAN_ID .................. 212378573691398125 +ROWS_OUT ......... 1,440,202 +EST_TOTAL_COST ......... 234.75 +STATEMENT ................ select * + from store_sales_parquet + 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 ......... 234.75 +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 + total_overflow_size .... 0.00 KB + 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 + HBASE_FILTER_PREDS ..... 2 + TRAF_INDEX_CREATE_OPT ON + TRAF_USE_REGION_XN ..... ON + SCHEMA ................. HIVE.HIVE + HIVE_MAX_STRING_LENGTH 20 + MODE_SEAHIVE ........... ON + TRAF_ENABLE_PARQUET_FOR 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 + PARQUET_COLUMNS_PUSHDOW ON + PARQUET_PRED_PUSHDOWN ON + GENERATE_EXPLAIN ....... ON + select_list ............ HIVE.STORE_SALES_PARQUET.SS_SOLD_DATE_SK, + HIVE.STORE_SALES_PARQUET.SS_SOLD_TIME_SK, + HIVE.STORE_SALES_PARQUET.SS_SOLD_DATE_SK, + HIVE.STORE_SALES_PARQUET.SS_CUSTOMER_SK, + HIVE.STORE_SALES_PARQUET.SS_CDEMO_SK, + HIVE.STORE_SALES_PARQUET.SS_HDEMO_SK, + HIVE.STORE_SALES_PARQUET.SS_ADDR_SK, + HIVE.STORE_SALES_PARQUET.SS_STORE_SK, + HIVE.STORE_SALES_PARQUET.SS_PROMO_SK, + HIVE.STORE_SALES_PARQUET.SS_TICKET_NUMBER, + HIVE.STORE_SALES_PARQUET.SS_QUANTITY, + HIVE.STORE_SALES_PARQUET.SS_WHOLESALE_COST, + HIVE.STORE_SALES_PARQUET.SS_LIST_PRICE, + HIVE.STORE_SALES_PARQUET.SS_SALES_PRICE, + HIVE.STORE_SALES_PARQUET.SS_EXT_DISCOUNT_AMT, + HIVE.STORE_SALES_PARQUET.SS_EXT_SALES_PRICE, + HIVE.STORE_SALES_PARQUET.SS_EXT_WHOLESALE_COST, + HIVE.STORE_SALES_PARQUET.SS_EXT_LIST_PRICE, + HIVE.STORE_SALES_PARQUET.SS_EXT_TAX, + HIVE.STORE_SALES_PARQUET.SS_COUPON_AMT, + HIVE.STORE_SALES_PARQUET.SS_NET_PAID, + HIVE.STORE_SALES_PARQUET.SS_NET_PAID_INC_TAX, + HIVE.STORE_SALES_PARQUET.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 ......... 234.75 +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) + + +PARQUET_SCAN ============================== SEQ_NO 1 NO CHILDREN +TABLE_NAME ............... HIVE.HIVE.STORE_SALES_PARQUET +REQUESTS_IN .............. 1 +ROWS_OUT ......... 1,440,202 +EST_OPER_COST .......... 234.75 +EST_TOTAL_COST ......... 234.75 +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_PARQUET + object_type ............ Hive_Parquet + 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_PARQUET.SS_ITEM_SK = + HIVE.STORE_SALES_PARQUET.SS_SOLD_DATE_SK) + +--- SQL operation complete. +>> +>>-- Boolean constant predicate is not pushed down +>>explain select * from store_sales_parquet where 1=2; + +------------------------------------------------------------------ PLAN SUMMARY +MODULE_NAME .............. DYNAMICALLY COMPILED +STATEMENT_NAME ........... NOT NAMED +PLAN_ID .................. 212378573691753629 +ROWS_OUT ................. 1 +EST_TOTAL_COST ........... 0 +STATEMENT ................ select * from store_sales_parquet 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 + total_overflow_size .... 0.00 KB + 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 + HBASE_FILTER_PREDS ..... 2 + TRAF_INDEX_CREATE_OPT ON + TRAF_USE_REGION_XN ..... ON + SCHEMA ................. HIVE.HIVE + HIVE_MAX_STRING_LENGTH 20 + MODE_SEAHIVE ........... ON + TRAF_ENABLE_PARQUET_FOR 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 + PARQUET_COLUMNS_PUSHDOW ON + PARQUET_PRED_PUSHDOWN ON + GENERATE_EXPLAIN ....... ON + select_list ............ HIVE.STORE_SALES_PARQUET.SS_SOLD_DATE_SK, + HIVE.STORE_SALES_PARQUET.SS_SOLD_TIME_SK, + HIVE.STORE_SALES_PARQUET.SS_ITEM_SK, + HIVE.STORE_SALES_PARQUET.SS_CUSTOMER_SK, + HIVE.STORE_SALES_PARQUET.SS_CDEMO_SK, + HIVE.STORE_SALES_PARQUET.SS_HDEMO_SK, + HIVE.STORE_SALES_PARQUET.SS_ADDR_SK, + HIVE.STORE_SALES_PARQUET.SS_STORE_SK, + HIVE.STORE_SALES_PARQUET.SS_PROMO_SK, + HIVE.STORE_SALES_PARQUET.SS_TICKET_NUMBER, + HIVE.STORE_SALES_PARQUET.SS_QUANTITY, + HIVE.STORE_SALES_PARQUET.SS_WHOLESALE_COST, + HIVE.STORE_SALES_PARQUET.SS_LIST_PRICE, + HIVE.STORE_SALES_PARQUET.SS_SALES_PRICE, + HIVE.STORE_SALES_PARQUET.SS_EXT_DISCOUNT_AMT, + HIVE.STORE_SALES_PARQUET.SS_EXT_SALES_PRICE, + HIVE.STORE_SALES_PARQUET.SS_EXT_WHOLESALE_COST, + HIVE.STORE_SALES_PARQUET.SS_EXT_LIST_PRICE, + HIVE.STORE_SALES_PARQUET.SS_EXT_TAX, + HIVE.STORE_SALES_PARQUET.SS_COUPON_AMT, + HIVE.STORE_SALES_PARQUET.SS_NET_PAID, + HIVE.STORE_SALES_PARQUET.SS_NET_PAID_INC_TAX, + HIVE.STORE_SALES_PARQUET.SS_NET_PROFIT + + +PARQUET_SCAN ============================== SEQ_NO 1 NO CHILDREN +TABLE_NAME ............... HIVE.HIVE.STORE_SALES_PARQUET +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_PARQUET + object_type ............ Hive_Parquet + 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 PARQUET layer +>>cqd parquet_aggr_pushdown 'ON'; + +--- SQL operation complete. +>>explain options 'f' select count(*) from store_parquet; + +LC RC OP OPERATOR OPT DESCRIPTION CARD +---- ---- ---- -------------------- -------- -------------------- --------- + +1 . 2 root 1.00E+000 +. . 1 parquet_aggr 1.00E+000 + +--- SQL operation complete. +>>select count(*) from store_parquet; + +(EXPR) +-------------------- + + 12 + +--- 1 row(s) selected. +>>select count(*), count(*) from store_parquet; + +(EXPR) (EXPR) +-------------------- -------------------- + + 12 12 + +--- 1 row(s) selected. +>>select min(s_store_sk) from store_parquet; + +(EXPR) +----------- + + 1 + +--- 1 row(s) selected. +>>select max(s_store_sk) from store_parquet; + +(EXPR) +----------- + + 12 + +--- 1 row(s) selected. +>>select sum(s_store_sk) from store_parquet; + +(EXPR) +-------------------- + + 78 + +--- 1 row(s) selected. +>>select count(*), min(s_store_sk), max(s_store_sk), sum(s_store_sk) ++> from store_parquet; + +(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_parquet; + +LC RC OP OPERATOR OPT DESCRIPTION CARD +---- ---- ---- -------------------- -------- -------------------- --------- + +2 . 3 root 1.00E+000 +1 . 2 sort_scalar_aggr 1.00E+000 +. . 1 parquet_scan STORE_PARQUET 1.00E+002 + +--- SQL operation complete. +>> +>>explain options 'f' ++> select count(*) from hive.hive.store_parquet union all ++> select count(*) from hive.hive.store_parquet; + +LC RC OP OPERATOR OPT DESCRIPTION CARD +---- ---- ---- -------------------- -------- -------------------- --------- + +3 . 4 root 2.00E+000 +1 2 3 merge_union 2.00E+000 +. . 2 parquet_aggr 1.00E+000 +. . 1 parquet_aggr 1.00E+000 + +--- SQL operation complete. +>>select count(*) from hive.hive.store_parquet union all ++> select count(*) from hive.hive.store_parquet; + +(EXPR) +-------------------- + + 12 + 12 + +--- 2 row(s) selected. +>> +>>explain options 'f' ++> select min(s_store_sk) from hive.hive.store_parquet union all ++> select min(s_store_sk) from hive.hive.store_parquet; + +LC RC OP OPERATOR OPT DESCRIPTION CARD +---- ---- ---- -------------------- -------- -------------------- --------- + +3 . 4 root 2.00E+000 +1 2 3 merge_union 2.00E+000 +. . 2 parquet_aggr 1.00E+000 +. . 1 parquet_aggr 1.00E+000 + +--- SQL operation complete. +>>select min(s_store_sk) from hive.hive.store_parquet union all ++> select min(s_store_sk) from hive.hive.store_parquet; + +(EXPR) +----------- + + 1 + 1 + +--- 2 row(s) selected. +>> +>>explain options 'f' select count(s_store_sk) from store_parquet; + +LC RC OP OPERATOR OPT DESCRIPTION CARD +---- ---- ---- -------------------- -------- -------------------- --------- + +1 . 2 root 1.00E+000 +. . 1 parquet_aggr 1.00E+000 + +--- SQL operation complete. +>>select count(s_store_sk) from store_parquet; + +(EXPR) +-------------------- + + 12 + +--- 1 row(s) selected. +>> +>>explain options 'f' select count(*) from store_parquet ++> having sum(s_store_sk) = 78; + +LC RC OP OPERATOR OPT DESCRIPTION CARD +---- ---- ---- -------------------- -------- -------------------- --------- + +2 . 3 root 1.00E+000 +1 . 2 sort_scalar_aggr 1.00E+000 +. . 1 parquet_scan STORE_PARQUET 1.00E+002 + +--- SQL operation complete. +>>select count(*) from store_parquet having sum(s_store_sk) = 78; + +(EXPR) +-------------------- + + 12 + +--- 1 row(s) selected. +>> +>>select count(*) from store_parquet having sum(s_store_sk) = 77; + +--- 0 row(s) selected. +>> +>>cqd parquet_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_parquet; + +LC RC OP OPERATOR OPT DESCRIPTION CARD +---- ---- ---- -------------------- -------- -------------------- --------- + +2 . 3 root 1.00E+000 +1 . 2 sort_scalar_aggr 1.00E+000 +. . 1 parquet_scan STORE_PARQUET 1.00E+002 + +--- SQL operation complete. +>>select count(*), min(s_store_sk), max(s_store_sk), sum(s_store_sk) ++> from store_parquet; + +(EXPR) (EXPR) (EXPR) (EXPR) +-------------------- ----------- ----------- -------------------- + + 12 1 12 78 + +--- 1 row(s) selected. +>> +>>-- test query cache +>>prepare xx from select cd_gender from customer_demographics_parquet 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_parquet 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_parquet for hive.hive.store_sales_parquet; + +--- SQL operation complete. +>>create external table store_sales_parquet ++> for hive.hive.store_sales_parquet; + +--- SQL operation complete. +>>invoke hive.hive.store_sales_parquet; + +-- Definition of hive table STORE_SALES_PARQUET +-- Definition current Mon Nov 27 20:15:06 2017 + + ( + SS_SOLD_DATE_SK INT + , SS_SOLD_TIME_SK INT + , SS_ITEM_SK INT + , SS_CUSTOMER_SK INT + , SS_CDEMO_SK INT + , SS_HDEMO_SK INT + , SS_ADDR_SK INT + , SS_STORE_SK INT + , SS_PROMO_SK INT + , SS_TICKET_NUMBER INT + , SS_QUANTITY INT + , SS_WHOLESALE_COST REAL + , SS_LIST_PRICE REAL + , SS_SALES_PRICE REAL + , SS_EXT_DISCOUNT_AMT REAL + , SS_EXT_SALES_PRICE REAL + , SS_EXT_WHOLESALE_COST REAL + , SS_EXT_LIST_PRICE REAL + , SS_EXT_TAX REAL + , SS_COUPON_AMT REAL + , SS_NET_PAID REAL + , SS_NET_PAID_INC_TAX REAL + , SS_NET_PROFIT REAL + ) + /* stored as parquet */ + +--- SQL operation complete. +>> +>>set schema hive.hive; + +--- SQL operation complete. +>>prepare s from select * from store_sales_parquet where ss_item_sk = 1; + +--- SQL command prepared. +>>explain s; + +------------------------------------------------------------------ PLAN SUMMARY +MODULE_NAME .............. DYNAMICALLY COMPILED +STATEMENT_NAME ........... S +PLAN_ID .................. 212378573706907158 +ROWS_OUT ............. 1,698 +EST_TOTAL_COST ......... 234.71 +STATEMENT ................ select * + from store_sales_parquet + 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 ......... 234.71 +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 + total_overflow_size .... 0.00 KB + 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 + HBASE_FILTER_PREDS ..... 2 + TRAF_INDEX_CREATE_OPT ON + TRAF_USE_REGION_XN ..... ON + HIVE_MAX_STRING_LENGTH 20 + MODE_SEAHIVE ........... ON + TRAF_ENABLE_PARQUET_FOR 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 + PARQUET_COLUMNS_PUSHDOW ON + PARQUET_PRED_PUSHDOWN ON + PARQUET_AGGR_PUSHDOWN OFF + SCHEMA ................. HIVE.HIVE + select_list ............ HIVE.STORE_SALES_PARQUET.SS_SOLD_DATE_SK, + HIVE.STORE_SALES_PARQUET.SS_SOLD_TIME_SK, %(1), + HIVE.STORE_SALES_PARQUET.SS_CUSTOMER_SK, + HIVE.STORE_SALES_PARQUET.SS_CDEMO_SK, + HIVE.STORE_SALES_PARQUET.SS_HDEMO_SK, + HIVE.STORE_SALES_PARQUET.SS_ADDR_SK, + HIVE.STORE_SALES_PARQUET.SS_STORE_SK, + HIVE.STORE_SALES_PARQUET.SS_PROMO_SK, + HIVE.STORE_SALES_PARQUET.SS_TICKET_NUMBER, + HIVE.STORE_SALES_PARQUET.SS_QUANTITY, + HIVE.STORE_SALES_PARQUET.SS_WHOLESALE_COST, + HIVE.STORE_SALES_PARQUET.SS_LIST_PRICE, + HIVE.STORE_SALES_PARQUET.SS_SALES_PRICE, + HIVE.STORE_SALES_PARQUET.SS_EXT_DISCOUNT_AMT, + HIVE.STORE_SALES_PARQUET.SS_EXT_SALES_PRICE, + HIVE.STORE_SALES_PARQUET.SS_EXT_WHOLESALE_COST, + HIVE.STORE_SALES_PARQUET.SS_EXT_LIST_PRICE, + HIVE.STORE_SALES_PARQUET.SS_EXT_TAX, + HIVE.STORE_SALES_PARQUET.SS_COUPON_AMT, + HIVE.STORE_SALES_PARQUET.SS_NET_PAID, + HIVE.STORE_SALES_PARQUET.SS_NET_PAID_INC_TAX, + HIVE.STORE_SALES_PARQUET.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 ......... 234.71 +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) + + +PARQUET_SCAN ============================== SEQ_NO 1 NO CHILDREN +TABLE_NAME ............... HIVE.HIVE.STORE_SALES_PARQUET +REQUESTS_IN .............. 1 +ROWS_OUT ............. 1,698 +EST_OPER_COST .......... 234.71 +EST_TOTAL_COST ......... 234.71 +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_PARQUET + object_type ............ Hive_Parquet + 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 + parquet_pred_pushdown yes + parquet_search_argument and( HIVE.STORE_SALES_PARQUET.SS_ITEM_SK = %(1) ) + +--- SQL operation complete. +>> +>>-- join with nested join +>>?ignore +>>cqd orc_njs 'on'; +>>control query shape nested_join(scan(path 'CUSTOMER_PARQUET'), ++> scan(path 'STORE_SALES_PARQUET')); +>>prepare s from select * from customer_parquet, store_sales_parquet ++> where store_sales_parquet.ss_item_sk = customer_parquet.c_customer_sk; +>>explain options 'f' s; +>>explain s; +>>cqd orc_njs reset; +>>?ignore +>> +>>-- 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_parquet dim, store_sales_sorted_parquet 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 parquet_scan STORE_SALES_SORTED_P 2.88E+006 +1 . 2 esp_exchange 4(rep-b):4(hash2) 2.00E+000 +. . 1 parquet_scan DATE_DIM_PARQUET 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_parquet for hive.hive.date_dim_parquet; + +--- SQL operation complete. +>>cqd volatile_table_find_suitable_key 'SYSTEM'; + +--- SQL operation complete. +>>create external table date_dim_parquet ++> (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_parquet; + +--- SQL operation complete. +>>invoke hive.hive.date_dim_parquet; + +-- Definition of hive table DATE_DIM_PARQUET +-- Definition current Mon Nov 27 20:15:11 2017 + + ( + D_DATE_SK INT + , D_DATE_ID VARCHAR(100 BYTES) CHARACTER SET UTF8 + COLLATE DEFAULT + , D_DATE DATE + , D_MONTH_SEQ INT + , D_WEEK_SEQ INT + , D_QUARTER_SEQ INT + , D_YEAR INT + , D_DOW INT + , D_MOY INT + , D_DOM INT + , D_QOY INT + , D_FY_YEAR INT + , D_FY_QUARTER_SEQ INT + , D_FY_WEEK_SEQ INT + , D_DAY_NAME VARCHAR(120 BYTES) CHARACTER SET UTF8 + COLLATE DEFAULT + , D_QUARTER_NAME VARCHAR(200 BYTES) CHARACTER SET UTF8 + COLLATE DEFAULT + , D_HOLIDAY VARCHAR(100 BYTES) CHARACTER SET UTF8 + COLLATE DEFAULT + , D_WEEKEND VARCHAR(100 BYTES) CHARACTER SET UTF8 + COLLATE DEFAULT + , D_FOLLOWING_HOLIDAY VARCHAR(100 BYTES) CHARACTER SET UTF8 + COLLATE DEFAULT + , D_FIRST_DOM INT + , D_LAST_DOM INT + , D_SAME_DAY_LY INT + , D_SAME_DAY_LQ INT + , D_CURRENT_DAY VARCHAR(100 BYTES) CHARACTER SET UTF8 + COLLATE DEFAULT + , D_CURRENT_WEEK VARCHAR(111 BYTES) CHARACTER SET UTF8 + COLLATE DEFAULT + , D_CURRENT_MONTH VARCHAR(200 BYTES) CHARACTER SET UTF8 + COLLATE DEFAULT + , D_CURRENT_QUARTER VARCHAR(100 BYTES) CHARACTER SET UTF8 + COLLATE DEFAULT + , D_CURRENT_YEAR VARCHAR(100 BYTES) CHARACTER SET UTF8 + COLLATE DEFAULT + ) + /* stored as parquet */ + +--- SQL operation complete. +>>showddl hive.hive.date_dim_parquet; + +/* Hive DDL */ +CREATE TABLE DEFAULT.DATE_DIM_PARQUET + ( + D_DATE_SK int + , D_DATE_ID string + , D_DATE timestamp + , D_MONTH_SEQ int + , D_WEEK_SEQ int + , D_QUARTER_SEQ int + , D_YEAR int + , D_DOW int + , D_MOY int + , D_DOM int + , D_QOY int + , D_FY_YEAR int + , D_FY_QUARTER_SEQ int + , D_FY_WEEK_SEQ int + , D_DAY_NAME string + , D_QUARTER_NAME string + , D_HOLIDAY string + , D_WEEKEND string + , D_FOLLOWING_HOLIDAY string + , D_FIRST_DOM int + , D_LAST_DOM int + , D_SAME_DAY_LY int + , D_SAME_DAY_LQ int + , D_CURRENT_DAY string + , D_CURRENT_WEEK string + , D_CURRENT_MONTH string + , D_CURRENT_QUARTER string + , D_CURRENT_YEAR string + ) + stored as parquet +; + +/* Trafodion DDL */ + +REGISTER /*INTERNAL*/ HIVE TABLE HIVE.HIVE.DATE_DIM_PARQUET; +/* ObjectUID = 1842681638780263725 */ + +CREATE EXTERNAL TABLE DATE_DIM_PARQUET + ( + 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_PARQUET +; + +--- SQL operation complete. +>>prepare s from select * from hive.hive.date_dim_parquet where d_date = date '2016-01-27'; + +--- SQL command prepared. +>>explain s; + +------------------------------------------------------------------ PLAN SUMMARY +MODULE_NAME .............. DYNAMICALLY COMPILED +STATEMENT_NAME ........... S +PLAN_ID .................. 212378573717690486 +ROWS_OUT ............... 271 +EST_TOTAL_COST ......... 244.13 +STATEMENT ................ select * + from hive.hive.date_dim_parquet + 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 ......... 244.13 +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 + total_overflow_size .... 0.00 KB + 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 + HBASE_FILTER_PREDS ..... 2 + TRAF_INDEX_CREATE_OPT ON + TRAF_USE_REGION_XN ..... ON + HIVE_MAX_STRING_LENGTH 20 + MODE_SEAHIVE ........... ON + TRAF_ENABLE_PARQUET_FOR 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 + PARQUET_COLUMNS_PUSHDOW ON + PARQUET_PRED_PUSHDOWN ON + PARQUET_AGGR_PUSHDOWN OFF + SCHEMA ................. TRAFODION.SCH + VOLATILE_TABLE_FIND_SUI SYSTEM + select_list ............ HIVE.DATE_DIM_PARQUET.D_DATE_SK, + HIVE.DATE_DIM_PARQUET.D_DATE_ID, %(2016-01-27), + HIVE.DATE_DIM_PARQUET.D_MONTH_SEQ, + HIVE.DATE_DIM_PARQUET.D_WEEK_SEQ, + HIVE.DATE_DIM_PARQUET.D_QUARTER_SEQ, + HIVE.DATE_DIM_PARQUET.D_YEAR, + HIVE.DATE_DIM_PARQUET.D_DOW, + HIVE.DATE_DIM_PARQUET.D_MOY, + HIVE.DATE_DIM_PARQUET.D_DOM, + HIVE.DATE_DIM_PARQUET.D_QOY, + HIVE.DATE_DIM_PARQUET.D_FY_YEAR, + HIVE.DATE_DIM_PARQUET.D_FY_QUARTER_SEQ, + HIVE.DATE_DIM_PARQUET.D_FY_WEEK_SEQ, + HIVE.DATE_DIM_PARQUET.D_DAY_NAME, + HIVE.DATE_DIM_PARQUET.D_QUARTER_NAME, + HIVE.DATE_DIM_PARQUET.D_HOLIDAY, + HIVE.DATE_DIM_PARQUET.D_WEEKEND, + HIVE.DATE_DIM_PARQUET.D_FOLLOWING_HOLIDAY, + HIVE.DATE_DIM_PARQUET.D_FIRST_DOM, + HIVE.DATE_DIM_PARQUET.D_LAST_DOM, + HIVE.DATE_DIM_PARQUET.D_SAME_DAY_LY, + HIVE.DATE_DIM_PARQUET.D_SAME_DAY_LQ, + HIVE.DATE_DIM_PARQUET.D_CURRENT_DAY, + HIVE.DATE_DIM_PARQUET.D_CURRENT_WEEK, + HIVE.DATE_DIM_PARQUET.D_CURRENT_MONTH, + HIVE.DATE_DIM_PARQUET.D_CURRENT_QUARTER, + HIVE.DATE_DIM_PARQUET.D_CURRENT_YEAR + input_variables ........ %(2016-01-27) + + +PARQUET_SCAN ============================== SEQ_NO 1 NO CHILDREN +TABLE_NAME ............... HIVE.HIVE.DATE_DIM_PARQUET +REQUESTS_IN .............. 1 +ROWS_OUT ............... 271 +EST_OPER_COST .......... 244.13 +EST_TOTAL_COST ......... 244.13 +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_PARQUET + object_type ............ Hive_Parquet + 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 + parquet_pred_pushdown yes + parquet_search_argument and( HIVE.DATE_DIM_PARQUET.D_DATE = %(2016-01-27) ) + +--- SQL operation complete. +>> +>>drop external table if exists date_dim_parquet for hive.hive.date_dim_parquet; + +--- SQL operation complete. +>>create external table date_dim_parquet ++> (d_date_sk int, d_date_id varchar(100 bytes) character set utf8, d_date date) ++> for hive.hive.date_dim_parquet; + +--- SQL operation complete. +>>invoke hive.hive.date_dim_parquet; + +-- Definition of hive table DATE_DIM_PARQUET +-- Definition current Mon Nov 27 20:15:28 2017 + + ( + D_DATE_SK INT + , D_DATE_ID VARCHAR(100 BYTES) CHARACTER SET UTF8 + COLLATE DEFAULT + , D_DATE DATE + , D_MONTH_SEQ INT + , D_WEEK_SEQ INT + , D_QUARTER_SEQ INT + , D_YEAR INT + , D_DOW INT + , D_MOY INT + , D_DOM INT + , D_QOY INT + , D_FY_YEAR INT + , D_FY_QUARTER_SEQ INT + , D_FY_WEEK_SEQ INT + , D_DAY_NAME VARCHAR(20 BYTES) CHARACTER SET UTF8 + COLLATE DEFAULT + , D_QUARTER_NAME VARCHAR(20 BYTES) CHARACTER SET UTF8 + COLLATE DEFAULT + , D_HOLIDAY VARCHAR(20 BYTES) CHARACTER SET UTF8 + COLLATE DEFAULT + , D_WEEKEND VARCHAR(20 BYTES) CHARACTER SET UTF8 + COLLATE DEFAULT + , D_FOLLOWING_HOLIDAY VARCHAR(20 BYTES) CHARACTER SET UTF8 + COLLATE DEFAULT + , D_FIRST_DOM INT + , D_LAST_DOM INT + , D_SAME_DAY_LY INT + , D_SAME_DAY_LQ INT + , D_CURRENT_DAY VARCHAR(20 BYTES) CHARACTER SET UTF8 + COLLATE DEFAULT + , D_CURRENT_WEEK VARCHAR(20 BYTES) CHARACTER SET UTF8 + COLLATE DEFAULT + , D_CURRENT_MONTH VARCHAR(20 BYTES) CHARACTER SET UTF8 + COLLATE DEFAULT + , D_CURRENT_QUARTER VARCHAR(20 BYTES) CHARACTER SET UTF8 + COLLATE DEFAULT + , D_CURRENT_YEAR VARCHAR(20 BYTES) CHARACTER SET UTF8 + COLLATE DEFAULT + ) + /* stored as parquet */ + +--- SQL operation complete. +>>showddl hive.hive.date_dim_parquet; + +/* Hive DDL */ +CREATE TABLE DEFAULT.DATE_DIM_PARQUET + ( + D_DATE_SK int + , D_DATE_ID string + , D_DATE timestamp + , D_MONTH_SEQ int + , D_WEEK_SEQ int + , D_QUARTER_SEQ int + , D_YEAR int + , D_DOW int + , D_MOY int + , D_DOM int + , D_QOY int + , D_FY_YEAR int + , D_FY_QUARTER_SEQ int + , D_FY_WEEK_SEQ int + , D_DAY_NAME string + , D_QUARTER_NAME string + , D_HOLIDAY string + , D_WEEKEND string + , D_FOLLOWING_HOLIDAY string + , D_FIRST_DOM int + , D_LAST_DOM int + , D_SAME_DAY_LY int + , D_SAME_DAY_LQ int + , D_CURRENT_DAY string + , D_CURRENT_WEEK string + , D_CURRENT_MONTH string + , D_CURRENT_QUARTER string + , D_CURRENT_YEAR string + ) + stored as parquet +; + +/* Trafodion DDL */ + +REGISTER /*INTERNAL*/ HIVE TABLE HIVE.HIVE.DATE_DIM_PARQUET; +/* ObjectUID = 1842681638780263725 */ + +CREATE EXTERNAL TABLE DATE_DIM_PARQUET + ( + 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_PARQUET +; + +--- SQL operation complete. +>>prepare s from select * from hive.hive.date_dim_parquet where d_date = date '2016-01-27'; + +--- SQL command prepared. +>>explain s; + +------------------------------------------------------------------ PLAN SUMMARY +MODULE_NAME .............. DYNAMICALLY COMPILED +STATEMENT_NAME ........... S +PLAN_ID .................. 212378573717690486 +ROWS_OUT ............... 271 +EST_TOTAL_COST ......... 244.13 +STATEMENT ................ select * + from hive.hive.date_dim_parquet + 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 ......... 244.13 +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 + total_overflow_size .... 0.00 KB + 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 + HBASE_FILTER_PREDS ..... 2 + TRAF_INDEX_CREATE_OPT ON + TRAF_USE_REGION_XN ..... ON + HIVE_MAX_STRING_LENGTH 20 + MODE_SEAHIVE ........... ON + TRAF_ENABLE_PARQUET_FOR 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 + PARQUET_COLUMNS_PUSHDOW ON + PARQUET_PRED_PUSHDOWN ON + PARQUET_AGGR_PUSHDOWN OFF + SCHEMA ................. TRAFODION.SCH + VOLATILE_TABLE_FIND_SUI SYSTEM + select_list ............ HIVE.DATE_DIM_PARQUET.D_DATE_SK, + HIVE.DATE_DIM_PARQUET.D_DATE_ID, %(2016-01-27), + HIVE.DATE_DIM_PARQUET.D_MONTH_SEQ, + HIVE.DATE_DIM_PARQUET.D_WEEK_SEQ, + HIVE.DATE_DIM_PARQUET.D_QUARTER_SEQ, + HIVE.DATE_DIM_PARQUET.D_YEAR, + HIVE.DATE_DIM_PARQUET.D_DOW, + HIVE.DATE_DIM_PARQUET.D_MOY, + HIVE.DATE_DIM_PARQUET.D_DOM, + HIVE.DATE_DIM_PARQUET.D_QOY, + HIVE.DATE_DIM_PARQUET.D_FY_YEAR, + HIVE.DATE_DIM_PARQUET.D_FY_QUARTER_SEQ, + HIVE.DATE_DIM_PARQUET.D_FY_WEEK_SEQ, + HIVE.DATE_DIM_PARQUET.D_DAY_NAME, + HIVE.DATE_DIM_PARQUET.D_QUARTER_NAME, + HIVE.DATE_DIM_PARQUET.D_HOLIDAY, + HIVE.DATE_DIM_PARQUET.D_WEEKEND, + HIVE.DATE_DIM_PARQUET.D_FOLLOWING_HOLIDAY, + HIVE.DATE_DIM_PARQUET.D_FIRST_DOM, + HIVE.DATE_DIM_PARQUET.D_LAST_DOM, + HIVE.DATE_DIM_PARQUET.D_SAME_DAY_LY, + HIVE.DATE_DIM_PARQUET.D_SAME_DAY_LQ, + HIVE.DATE_DIM_PARQUET.D_CURRENT_DAY, + HIVE.DATE_DIM_PARQUET.D_CURRENT_WEEK, + HIVE.DATE_DIM_PARQUET.D_CURRENT_MONTH, + HIVE.DATE_DIM_PARQUET.D_CURRENT_QUARTER, + HIVE.DATE_DIM_PARQUET.D_CURRENT_YEAR + input_variables ........ %(2016-01-27) + + +PARQUET_SCAN ============================== SEQ_NO 1 NO CHILDREN +TABLE_NAME ............... HIVE.HIVE.DATE_DIM_PARQUET +REQUESTS_IN .............. 1 +ROWS_OUT ............... 271 +EST_OPER_COST .......... 244.13 +EST_TOTAL_COST ......... 244.13 +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_PARQUET + object_type ............ Hive_Parquet + 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 + parquet_pred_pushdown yes + parquet_search_argument and( HIVE.DATE_DIM_PARQUET.D_DATE = %(2016-01-27) ) + +--- SQL operation complete. +>> +>> +>>-- error cases +>>drop external table if exists date_dim_parquet for hive.hive.date_dim_parquet; + +--- SQL operation complete. +>> +>>-- column d_date_skk doesn't exist in native hive table +>>create external table date_dim_parquet ++> (d_date_skk int) ++> for hive.hive.date_dim_parquet; + +*** ERROR[1009] Column D_DATE_SKK does not exist in the specified table. + +--- SQL operation failed with errors. +>> +>>-- del/update not supported on parquet or hive +>>prepare s from delete from hive.hive.store2_sales_parquet; + +*** ERROR[4223] Update/Delete on PARQUET table is not supported in this software version. + +*** ERROR[8822] The statement was not prepared. + +>>prepare s from update hive.hive.store2_sales_parquet set ss_ext_tax = 1; + +*** ERROR[4223] Update/Delete on PARQUET table is not supported in this software version. + +*** ERROR[8822] The statement was not prepared. + +>>prepare s from delete from hive.hive.store_sales; + +*** ERROR[4223] Update/Delete on Hive table is not supported in this software version. + +*** ERROR[8822] The statement was not prepared. + +>>prepare s from update hive.hive.store_sales set ss_ext_tax = 1; + +*** ERROR[4223] Update/Delete on Hive table is not supported in this software version. + +*** ERROR[8822] The statement was not prepared. + +>> +>>-- upsert/insert cannot specify column list and must provide all column values. +>>prepare s from upsert into hive.hive.store2_sales_parquet values (1); + +*** ERROR[4023] The degree of each row value constructor (1) must equal the degree of the target table column list (23). + +*** ERROR[8822] The statement was not prepared. + +>>prepare s from upsert using load into hive.hive.store2_sales_parquet values (1); + +*** ERROR[4023] The degree of each row value constructor (1) must equal the degree of the target table column list (23). + +*** ERROR[8822] The statement was not prepared. + +>>prepare s from insert into hive.hive.store2_sales_parquet values (1); + +*** ERROR[4023] The degree of each row value constructor (1) must equal the degree of the target table column list (23). + +*** ERROR[8822] The statement was not prepared. + +>>prepare s from update hive.hive.store2_sales_parquet set ss_net_paid = 1; + +*** ERROR[4223] Update/Delete on PARQUET table is not supported in this software version. + +*** ERROR[8822] The statement was not prepared. + +>>prepare s from upsert into hive.hive.store2_sales_parquet(ss_sold_date_sk) values (1); + +*** ERROR[4223] Target column list specification for insert/upsert into a Hive table is not supported in this software version. + +*** ERROR[8822] The statement was not prepared. + +>>prepare s from insert into hive.hive.store2_sales_parquet(ss_sold_date_sk) values (1); + +*** ERROR[4223] Target column list specification for insert/upsert into a Hive table is not supported in this software version. + +*** ERROR[8822] The statement was not prepared. + +>>prepare s from upsert into hive.hive.store_sales(ss_sold_date_sk) values (1); + +*** ERROR[4223] Target column list specification for insert/upsert into a Hive table is not supported in this software version. + +*** ERROR[8822] The statement was not prepared. + +>>prepare s from insert into hive.hive.store_sales(ss_sold_date_sk) values (1); + +*** ERROR[4223] Target column list specification for insert/upsert into a Hive table is not supported in this software version. + +*** ERROR[8822] The statement was not prepared. + +>>prepare s from upsert into hive.hive.store_sales values (1); + +*** ERROR[4023] The degree of each row value constructor (1) must equal the degree of the target table column list (23). + +*** ERROR[8822] The statement was not prepared. + +>>prepare s from insert into hive.hive.store_sales values (1); + +*** ERROR[4023] The degree of each row value constructor (1) must equal the degree of the target table column list (23). + +*** ERROR[8822] The statement was not prepared. + +>> +>> +>>-- test min-max optimization +>> +>>update statistics for table hive.hive.date_dim on every column sample; + +--- SQL operation complete. +>>update statistics for table hive.hive.time_dim on every column sample; + +--- SQL operation complete. +>> +>>cqd PARQUET_PRED_PUSHDOWN 'ON'; + +--- SQL operation complete. +>>cqd GEN_HSHJ_MIN_MAX_OPT 'on'; + +--- SQL operation complete. +>>cqd parallel_num_esps '4'; + +--- SQL operation complete. +>>cqd nested_joins 'off'; + +--- SQL operation complete. +>> +>>prepare xx from select count(*) from ++>hive.hive.store2_sales_parquet, -- sorted on ss_sold_date_sk ++>hive.hive.date_dim, ++>hive.hive.time_dim ++> where ss_sold_date_sk = d_date_sk and ++> ss_sold_date_sk = t_time_sk and d_year = 2001 and t_hour = 10 ; + +--- SQL command prepared. +>> +>>-- display the push-down predicates, which should include the +>>-- min/max expressions from dimension table date_dim and time_dim. +>>select cast(tokenstr('parquet_search_arguments', description, 'executor_predicates') ++> as char(400)) ++> from table (explain(NULL,'XX')) ++>where position('parquet_search_arguments' in description) > 0 ; + +(EXPR) +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + +and( and( and( and( and( not( HIVE.STORE2_SALES_PARQUET.SS_SOLD_DATE_SK is null ) not( HIVE.STORE2_SALES_PARQUET.SS_SOLD_DATE_SK <\:_sys_MinVal0 ) ) HIVE.STORE2_SALES_PARQUET.SS_SOLD_DATE_SK <= \:_sys_MaxVal0 ) not( HIVE.STORE2_SALES_PARQUET.SS_SOLD_DATE_SK <\:_sys_MinVal1 ) ) HIVE.STORE2_SALES_PARQUET.SS_SOLD_DATE_SK <= \:_sys_MaxVal1 ) ) + +--- 1 row(s) selected. +>> +>>execute xx; + +(EXPR) +-------------------- + + 0 + +--- 1 row(s) selected. +>> +>>cqd parallel_num_esps reset; + +--- SQL operation complete. +>>cqd nested_joins reset; + +--- SQL operation complete. +>> +>>-- test NJ into a sorted PARQUET table +>> +>>cqd parquet_pred_pushdown 'ON'; + +--- SQL operation complete. +>>cqd orc_njs 'ON'; + +--- SQL operation complete. +>>cqd parallel_num_esps '4'; + +--- SQL operation complete. +>>cqd HIVE_USE_EXT_TABLE_ATTRS 'off'; + +--- SQL operation complete. +>> +>>prepare xx from select count(*) from hive.hive.customer_parquet, hive.hive.store_sales_sorted_parquet ++> where ss_sold_date_sk = c_customer_sk ++> and c_first_sales_date_sk = 4; + +--- SQL command prepared. +>> +>>explain xx; + +------------------------------------------------------------------ PLAN SUMMARY +MODULE_NAME .............. DYNAMICALLY COMPILED +STATEMENT_NAME ........... XX +PLAN_ID .................. 212378573801436592 +ROWS_OUT ................. 1 +EST_TOTAL_COST .......... 23.94 +STATEMENT ................ select count(*) + from hive.hive.customer_parquet, + hive.hive.store_sales_sorted_parquet + where ss_sold_date_sk = c_customer_sk and + c_first_sales_date_sk = 4; + + +------------------------------------------------------------------ NODE LISTING +ROOT ====================================== SEQ_NO 8 ONLY CHILD 7 +REQUESTS_IN .............. 1 +ROWS_OUT ................. 1 +EST_OPER_COST ............ 0 +EST_TOTAL_COST .......... 23.94 +DESCRIPTION + est_memory_per_node .... 10240.00(Limit), 0.13(BMOs), 0.00(nBMOs) MB + max_card_est ........... 1 + fragment_id ............ 0 + parent_frag ............ (none) + fragment_type .......... master + record_length .......... 8 + statement_index ........ 0 + affinity_value ......... 0 + max_max_cardinal 2,880,404 + total_overflow_size .... 0.00 KB + esp_2_node_map ......... (-1(4 times)) + esp_3_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 + HBASE_FILTER_PREDS ..... 2 + TRAF_INDEX_CREATE_OPT ON + TRAF_USE_REGION_XN ..... ON + HIVE_MAX_STRING_LENGTH 20 + MODE_SEAHIVE ........... ON + TRAF_ENABLE_PARQUET_FOR 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 + PARQUET_COLUMNS_PUSHDOW ON + PARQUET_AGGR_PUSHDOWN OFF + SCHEMA ................. TRAFODION.SCH + VOLATILE_TABLE_FIND_SUI SYSTEM + GEN_HSHJ_MIN_MAX_OPT ... ON + PARQUET_PRED_PUSHDOWN ON + ORC_NJS ................ ON + PARALLEL_NUM_ESPS ...... 4 + HIVE_USE_EXT_TABLE_ATTR OFF + select_list ............ cast(sum(count(1 ))) + input_variables ........ %(4), execution_count + + +SORT_PARTIAL_AGGR_ROOT ==================== SEQ_NO 7 ONLY CHILD 6 +REQUESTS_IN .............. 1 +ROWS_OUT ................. 1 +EST_OPER_COST ............ 0.01 +EST_TOTAL_COST .......... 23.94 +DESCRIPTION + max_card_est ........... 1 + fragment_id ............ 0 + parent_frag ............ (none) + fragment_type .......... master + record_length .......... 8 + aggregates ............. sum(count(1 )) + + +ESP_EXCHANGE ============================== SEQ_NO 6 ONLY CHILD 5 +REQUESTS_IN .............. 1 +ROWS_OUT ................. 1 +EST_OPER_COST ............ 0.01 +EST_TOTAL_COST .......... 23.94 +DESCRIPTION + max_card_est ........... 1 + fragment_id ............ 2 + parent_frag ............ 0 + fragment_type .......... esp + est_memory_per_node .... 29.297 KB + record_length .......... 8 + buffer_size ........ 5,000 + parent_processes ....... 1 + child_processes ........ 4 + child_partitioning_func hash2 partitioned 4 ways on (randomNum) + + +SORT_PARTIAL_AGGR_LEAF ==================== SEQ_NO 5 ONLY CHILD 4 +REQUESTS_IN .............. 1 +ROWS_OUT ................. 1 +EST_OPER_COST ............ 0.01 +EST_TOTAL_COST .......... 23.94 +DESCRIPTION + max_card_est ........... 1 + fragment_id ............ 2 + parent_frag ............ 0 + fragment_type .......... esp + record_length .......... 8 + aggregates ............. count(1 ) + + +HYBRID_HASH_JOIN ========================== SEQ_NO 4 CHILDREN 3, 2 +REQUESTS_IN .............. 1 +ROWS_OUT ......... 2,880,404 +EST_OPER_COST ............ 0.02 +EST_TOTAL_COST .......... 23.94 +DESCRIPTION + memory_quota_per_instan 800 MB + max_card_est ........... 2.8804e+06 + fragment_id ............ 2 + parent_frag ............ 0 + fragment_type .......... esp + est_memory_per_instance 66.631 KB + record_length .......... 0 + join_type .............. inner + join_method ............ hash + parallel_join_type ..... 2 + min_max_cols ........... HIVE.CUSTOMER_PARQUET.C_CUSTOMER_SK + min_max_expr ........... \:_sys_MinVal0, \:_sys_MaxVal0 + hash_join_predicates ... (HIVE.STORE_SALES_SORTED_PARQUET.SS_SOLD_DATE_SK = + HIVE.CUSTOMER_PARQUET.C_CUSTOMER_SK) + + +PARQUET_SCAN ============================== SEQ_NO 3 NO CHILDREN +TABLE_NAME ............... HIVE.HIVE.STORE_SALES_SORTED_PARQUET +REQUESTS_IN .............. 1 +ROWS_OUT ......... 2,880,404 +EST_OPER_COST ........... 19.89 +EST_TOTAL_COST .......... 19.89 +DESCRIPTION + max_car
<TRUNCATED>
