[ https://issues.apache.org/jira/browse/HIVE-19292?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Work on HIVE-19292 started by Jesus Camacho Rodriguez. ------------------------------------------------------ > More than one materialized view in DB affecting query rewrite > ------------------------------------------------------------- > > Key: HIVE-19292 > URL: https://issues.apache.org/jira/browse/HIVE-19292 > Project: Hive > Issue Type: Bug > Components: Hive > Affects Versions: 3.0.0 > Reporter: Aswathy Chellammal Sreekumar > Assignee: Jesus Camacho Rodriguez > Priority: Critical > > When there are more than one materialized view query rewrite fails to pick > the materialized view, which it picks otherwise > {noformat} > 1: jdbc:hive2://<host-name>> show materialized views; > INFO : Compiling > command(queryId=hive_20180424204708_e39107e4-ae65-4e3e-a73f-19e0519b515c): > show materialized views > INFO : Semantic Analysis Completed > INFO : Returning Hive schema: > Schema(fieldSchemas:[FieldSchema(name:tab_name, type:string, comment:from > deserializer)], properties:null) > INFO : Completed compiling > command(queryId=hive_20180424204708_e39107e4-ae65-4e3e-a73f-19e0519b515c); > Time taken: 0.021 seconds > INFO : Executing > command(queryId=hive_20180424204708_e39107e4-ae65-4e3e-a73f-19e0519b515c): > show materialized views > INFO : Starting task [Stage-0:DDL] in serial mode > INFO : Completed executing > command(queryId=hive_20180424204708_e39107e4-ae65-4e3e-a73f-19e0519b515c); > Time taken: 0.174 seconds > INFO : OK > +----------------------+ > | tab_name | > +----------------------+ > | cmv_mat_view | > | mv_agg | > | source_table_001_mv | > +----------------------+ > 3 rows selected (0.3 seconds) > 1: jdbc:hive2://<host-name>> drop materialized view cmv_mat_view; > INFO : Compiling > command(queryId=hive_20180424204724_5d4f3aaf-ed22-4828-a1a8-d8fe9f6bd9af): > drop materialized view cmv_mat_view > INFO : Semantic Analysis Completed > INFO : Returning Hive schema: Schema(fieldSchemas:null, properties:null) > INFO : Completed compiling > command(queryId=hive_20180424204724_5d4f3aaf-ed22-4828-a1a8-d8fe9f6bd9af); > Time taken: 0.029 seconds > INFO : Executing > command(queryId=hive_20180424204724_5d4f3aaf-ed22-4828-a1a8-d8fe9f6bd9af): > drop materialized view cmv_mat_view > INFO : Starting task [Stage-0:DDL] in serial mode > INFO : Completed executing > command(queryId=hive_20180424204724_5d4f3aaf-ed22-4828-a1a8-d8fe9f6bd9af); > Time taken: 0.312 seconds > INFO : OK > No rows affected (0.369 seconds) > 1: jdbc:hive2://<host-name>> explain > . . . . . . . . . . . . . . . . . . . . . . .> select > . . . . . . . . . . . . . . . . . . . . . . .> SUM(A.DOWN_VOLUME) AS > DOWNLOAD_VOLUME_BYTES, > . . . . . . . . . . . . . . . . . . . . . . .> FLOOR(A.MY_DATE to > hour),A.MY_ID2,A.ENVIRONMENT > . . . . . . . . . . . . . . . . . . . . . . .> FROM source_table_001 AS A > . . . . . . . . . . . . . . . . . . . . . . .> group by > A.MY_ID,A.MY_ID2,A.ENVIRONMENT,FLOOR(A.MY_DATE to hour); > INFO : Compiling > command(queryId=hive_20180424204736_76958a4d-0f08-4e22-93c6-67e3a1493b92): > explain > select > SUM(A.DOWN_VOLUME) AS DOWNLOAD_VOLUME_BYTES, > FLOOR(A.MY_DATE to hour),A.MY_ID2,A.ENVIRONMENT > FROM source_table_001 AS A > group by A.MY_ID,A.MY_ID2,A.ENVIRONMENT,FLOOR(A.MY_DATE to hour) > INFO : Semantic Analysis Completed > INFO : Returning Hive schema: Schema(fieldSchemas:[FieldSchema(name:Explain, > type:string, comment:null)], properties:null) > INFO : Completed compiling > command(queryId=hive_20180424204736_76958a4d-0f08-4e22-93c6-67e3a1493b92); > Time taken: 0.374 seconds > INFO : Executing > command(queryId=hive_20180424204736_76958a4d-0f08-4e22-93c6-67e3a1493b92): > explain > select > SUM(A.DOWN_VOLUME) AS DOWNLOAD_VOLUME_BYTES, > FLOOR(A.MY_DATE to hour),A.MY_ID2,A.ENVIRONMENT > FROM source_table_001 AS A > group by A.MY_ID,A.MY_ID2,A.ENVIRONMENT,FLOOR(A.MY_DATE to hour) > INFO : Starting task [Stage-3:EXPLAIN] in serial mode > INFO : Completed executing > command(queryId=hive_20180424204736_76958a4d-0f08-4e22-93c6-67e3a1493b92); > Time taken: 0.006 seconds > INFO : OK > +----------------------------------------------------+ > | Explain | > +----------------------------------------------------+ > | Plan optimized by CBO. | > | | > | Vertex dependency in root stage | > | Reducer 2 <- Map 1 (SIMPLE_EDGE) | > | | > | Stage-0 | > | Fetch Operator | > | limit:-1 | > | Stage-1 | > | Reducer 2 vectorized, llap | > | File Output Operator [FS_13] | > | Select Operator [SEL_12] (rows=1 width=143) | > | Output:["_col0","_col1","_col2","_col3"] | > | Group By Operator [GBY_11] (rows=1 width=151) | > | > Output:["_col0","_col1","_col2","_col3","_col4"],aggregations:["sum(VALUE._col0)"],keys:KEY._col0, > KEY._col1, KEY._col2, KEY._col3 | > | <-Map 1 [SIMPLE_EDGE] vectorized, llap | > | SHUFFLE [RS_10] | > | PartitionCols:_col0, _col1, _col2, _col3 | > | Group By Operator [GBY_9] (rows=1 width=151) | > | > Output:["_col0","_col1","_col2","_col3","_col4"],aggregations:["sum(_col4)"],keys:_col0, > _col1, _col2, _col3 | > | Select Operator [SEL_8] (rows=1 width=151) | > | Output:["_col0","_col1","_col2","_col3","_col4"] | > | TableScan [TS_0] (rows=1 width=151) | > | default@source_table_001,a, ACID > table,Tbl:COMPLETE,Col:COMPLETE,Output:["my_id","my_id2","environment","my_date","down_volume"] > | > | | > +----------------------------------------------------+ > 25 rows selected (0.41 seconds) > 1: jdbc:hive2://<host-name>> drop materialized view mv_agg; > INFO : Compiling > command(queryId=hive_20180424204752_149cc1fd-b21e-4b35-955a-67e019a34e98): > drop materialized view mv_agg > INFO : Semantic Analysis Completed > INFO : Returning Hive schema: Schema(fieldSchemas:null, properties:null) > INFO : Completed compiling > command(queryId=hive_20180424204752_149cc1fd-b21e-4b35-955a-67e019a34e98); > Time taken: 0.036 seconds > INFO : Executing > command(queryId=hive_20180424204752_149cc1fd-b21e-4b35-955a-67e019a34e98): > drop materialized view mv_agg > INFO : Starting task [Stage-0:DDL] in serial mode > INFO : Completed executing > command(queryId=hive_20180424204752_149cc1fd-b21e-4b35-955a-67e019a34e98); > Time taken: 0.234 seconds > INFO : OK > No rows affected (0.297 seconds) > 1: jdbc:hive2://<host-name>> explain > . . . . . . . . . . . . . . . . . . . . . . .> select > . . . . . . . . . . . . . . . . . . . . . . .> SUM(A.DOWN_VOLUME) AS > DOWNLOAD_VOLUME_BYTES, > . . . . . . . . . . . . . . . . . . . . . . .> FLOOR(A.MY_DATE to > hour),A.MY_ID2,A.ENVIRONMENT > . . . . . . . . . . . . . . . . . . . . . . .> FROM source_table_001 AS A > . . . . . . . . . . . . . . . . . . . . . . .> group by > A.MY_ID,A.MY_ID2,A.ENVIRONMENT,FLOOR(A.MY_DATE to hour); > INFO : Compiling > command(queryId=hive_20180424204759_4bdef3d8-0f06-49fb-876e-fab8100359b6): > explain > select > SUM(A.DOWN_VOLUME) AS DOWNLOAD_VOLUME_BYTES, > FLOOR(A.MY_DATE to hour),A.MY_ID2,A.ENVIRONMENT > FROM source_table_001 AS A > group by A.MY_ID,A.MY_ID2,A.ENVIRONMENT,FLOOR(A.MY_DATE to hour) > INFO : Semantic Analysis Completed > INFO : Returning Hive schema: Schema(fieldSchemas:[FieldSchema(name:Explain, > type:string, comment:null)], properties:null) > INFO : Completed compiling > command(queryId=hive_20180424204759_4bdef3d8-0f06-49fb-876e-fab8100359b6); > Time taken: 0.369 seconds > INFO : Executing > command(queryId=hive_20180424204759_4bdef3d8-0f06-49fb-876e-fab8100359b6): > explain > select > SUM(A.DOWN_VOLUME) AS DOWNLOAD_VOLUME_BYTES, > FLOOR(A.MY_DATE to hour),A.MY_ID2,A.ENVIRONMENT > FROM source_table_001 AS A > group by A.MY_ID,A.MY_ID2,A.ENVIRONMENT,FLOOR(A.MY_DATE to hour) > INFO : Starting task [Stage-3:EXPLAIN] in serial mode > INFO : Completed executing > command(queryId=hive_20180424204759_4bdef3d8-0f06-49fb-876e-fab8100359b6); > Time taken: 0.006 seconds > INFO : OK > +----------------------------------------------------+ > | Explain | > +----------------------------------------------------+ > | Plan optimized by CBO. | > | | > | Vertex dependency in root stage | > | Reducer 2 <- Map 1 (SIMPLE_EDGE) | > | | > | Stage-0 | > | Fetch Operator | > | limit:-1 | > | Stage-1 | > | Reducer 2 vectorized, llap | > | File Output Operator [FS_13] | > | Select Operator [SEL_12] (rows=1 width=143) | > | Output:["_col0","_col1","_col2","_col3"] | > | Group By Operator [GBY_11] (rows=1 width=151) | > | > Output:["_col0","_col1","_col2","_col3","_col4"],aggregations:["sum(VALUE._col0)"],keys:KEY._col0, > KEY._col1, KEY._col2, KEY._col3 | > | <-Map 1 [SIMPLE_EDGE] vectorized, llap | > | SHUFFLE [RS_10] | > | PartitionCols:_col0, _col1, _col2, _col3 | > | Group By Operator [GBY_9] (rows=1 width=151) | > | > Output:["_col0","_col1","_col2","_col3","_col4"],aggregations:["sum(_col0)"],keys:_col1, > _col2, _col3, _col4 | > | Select Operator [SEL_8] (rows=1 width=151) | > | Output:["_col0","_col1","_col2","_col3","_col4"] | > | TableScan [TS_0] (rows=1 width=151) | > | > default@source_table_001_mv,default.source_table_001_mv,Tbl:COMPLETE,Col:COMPLETE,Output:["down_volume_sum","my_id","my_id2","environment","my_date"] > | > | | > +----------------------------------------------------+ > 25 rows selected (0.403 seconds) > 1: jdbc:hive2://<host-name>> show materialized views; > INFO : Compiling > command(queryId=hive_20180424204948_16bcffca-f716-49b0-94f5-d088231dba5a): > show materialized views > INFO : Semantic Analysis Completed > INFO : Returning Hive schema: > Schema(fieldSchemas:[FieldSchema(name:tab_name, type:string, comment:from > deserializer)], properties:null) > INFO : Completed compiling > command(queryId=hive_20180424204948_16bcffca-f716-49b0-94f5-d088231dba5a); > Time taken: 0.013 seconds > INFO : Executing > command(queryId=hive_20180424204948_16bcffca-f716-49b0-94f5-d088231dba5a): > show materialized views > INFO : Starting task [Stage-0:DDL] in serial mode > INFO : Completed executing > command(queryId=hive_20180424204948_16bcffca-f716-49b0-94f5-d088231dba5a); > Time taken: 0.147 seconds > INFO : OK > +----------------------+ > | tab_name | > +----------------------+ > | source_table_001_mv | > +----------------------+ > 1 row selected (0.219 seconds) > {noformat} -- This message was sent by Atlassian JIRA (v7.6.3#76005)