Hi All,
Created a materialized view with partitions and
tblproperties('transactional'='true') in Hive Version 4.0 running on Hadoop
3.1. View gets created properly and I can see one file in each partition.
When we tried to query the view though no rows are getting returned.In describe formatted, we see that numFiles and numRows are 0. Tried gathering statistics, MSCK repair table etc and manually dropped and re-added partition. Still the materialized view returns 0 rows. Not quite sure if I am missing something or if this is a bug. Have attached the output of describe and hive-server2 logs that got generated when the materialized view was created. Log shows below error message while gathering statistics: MetaException(message:Cannot change stats state for a transactional table without providing the transactional write state for verification (new write ID 0, valid write IDs default.test_fact_mv:0:9223372036854775807::; Can you please give me some pointers to get the above materialized view working? Thanks, Karthik
0: jdbc:hive2://myserver:2181/> describe formatted test_fact_mv;
+----------------------------------+----------------------------------------------------+----------------------------------------------------+
| col_name | data_type
| comment |
+----------------------------------+----------------------------------------------------+----------------------------------------------------+
| # col_name | data_type
| comment |
| dim1 | string
| |
| dim2 | string
| |
| dim3 | string
| |
| dim4 | string
| |
| dim5 | string
| |
| dim6 | string
| |
| dim7 | string
| |
| dim8 | string
| |
| dim9 | string
| |
| dim10 | string
| |
| dim11 | string
| |
| dim12 | string
| |
| dim13 | string
| |
| dim14 | string
| |
| dim15 | string
| |
| dim16 | string
| |
| dim17 | string
| |
| dim18 | string
| |
| dim19 | string
| |
| amt1 | decimal(20,3)
| |
| amt2 | decimal(20,3)
| |
| amt3 | decimal(20,3)
| |
| dim20 | string
| |
| my_test_dim11 | string
| |
| my_test_veh_id | string
| |
| mtr_dim_dim9 | string
| |
| mtr_dim_t_f_id | string
| |
| mtr_dim_b_c | string
| |
| mtr_dim_b_r | string
| |
| mtr_dim_p_m_g | string
| |
| mtr_dim_dim16 | string
| |
| mtr_dim_f_c_l | string
| |
| dim4_lvl2_id | string
| |
| dim4_lvl3_id | string
| |
| dim4_lvl4_id | string
| |
| dim4_lvl5_id | string
| |
| dim4_lvl6_id | string
| |
| dim4_tree_dim4 | string
| |
| dim7_f_lvl2_id | string
| |
| dim7_f_lvl3_id | string
| |
| dim7_f_lvl4_id | string
| |
| dim7_f_lvl5_id | string
| |
| dim7_f_lvl6_id | string
| |
| dim7_f_tree_dim7 | string
| |
| | NULL
| NULL |
| # Partition Information | NULL
| NULL |
| # col_name | data_type
| comment |
| fiscal_year | string
| |
| accounting_period | string
| |
| | NULL
| NULL |
| # Detailed Table Information | NULL
| NULL |
| Database: | default
| NULL |
| OwnerType: | USER
| NULL |
| Owner: | oshiveuser
| NULL |
| CreateTime: | Wed Sep 05 05:56:20 PDT 2018
| NULL |
| LastAccessTime: | UNKNOWN
| NULL |
| Retention: | 0
| NULL |
| Location: |
hdfs://myserver:9000/user/hive/warehouse_tmp/test_fact_mv | NULL
|
| Table Type: | MATERIALIZED_VIEW
| NULL |
| Table Parameters: | NULL
| NULL |
| | COLUMN_STATS_ACCURATE
| {\"BASIC_STATS\":\"true\"} |
| | bucketing_version
| 2 |
| | numFiles
| 0 |
| | numPartitions
| 84 |
| | numRows
| 0 |
| | rawDataSize
| 0 |
| | totalSize
| 0 |
| | transactional
| true |
| | transactional_properties
| default |
| | transient_lastDdlTime
| 1536152180 |
| | NULL
| NULL |
| # Storage Information | NULL
| NULL |
| SerDe Library: | org.apache.hadoop.hive.ql.io.orc.OrcSerde
| NULL |
| InputFormat: |
org.apache.hadoop.hive.ql.io.orc.OrcInputFormat | NULL
|
| OutputFormat: |
org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat | NULL
|
| Compressed: | No
| NULL |
| Num Buckets: | -1
| NULL |
| Bucket Columns: | []
| NULL |
| Sort Columns: | []
| NULL |
| | NULL
| NULL |
| # Materialized View Information | NULL
| NULL |
Original Query: select f.dim1, f.dim2, f.FISCAL_YEAR,
f.accounting_PERIOD, f.dim3, f.dim4, f.dim5, f.dim6, f.dim7, f.dim8, f.dim9,
f.dim10, f.dim11, f.dim12, f.dim13, f.dim14, f.dim15, f.dim16, f.dim17,
f.dim18, f.dim19, f.amt1, f.amt2, f.amt3, f.dim20, mdim.my_test_dim11
,mdim.my_test_veh_id , mdim.dim9 as mtr_dim_dim9,mdim.f_t_f_id as
mtr_dim_t_f_id, mdim.dim17 as mtr_dim_b_c,mdim.dim15 as
mtr_dim_b_r,mdim.f_p_m_g as mtr_dim_p_m_g, mdim.dim16 as
mtr_dim_dim16,mdim.f_c_l as mtr_dim_f_c_l, accnt1.dim4_lvl2_id ,
accnt1.dim4_lvl3_id , accnt1.dim4_lvl4_id , accnt1.dim4_lvl5_id ,
accnt1.dim4_lvl6_id ,accnt1.dim4_id as dim4_tree_dim4, dept_tree.dim7_f_lvl2_id
, dept_tree.dim7_f_lvl3_id, dept_tree.dim7_f_lvl4_id ,
dept_tree.dim7_f_lvl5_id, dept_tree.dim7_f_lvl6_id ,dept_tree.dim7_f_id as
dim7_f_tree_dim7 from my_fact1 f join dim4_tree accnt1 on accnt1.dim4_id =
f.dim4 join p_f_mtr_DIM_orc mdim on mdim.dim9=f.dim9 join p_f_d_dim_tree
dept_tree on dept_tree.dim7_f_id=f.dim7
Expanded Query: SELECT `dim1`, `dim2`, `dim3`, `dim4`,
`dim5`, `dim6`, `dim7`, `dim8`, `dim9`, `dim10`, `dim11`, `dim12`, `dim13`,
`dim14`, `dim15`, `dim16`, `dim17`, `dim18`, `dim19`, `amt1`, `amt2`, `amt3`,
`dim20`, `my_test_dim11`, `my_test_veh_id`, `mtr_dim_dim9`, `mtr_dim_t_f_id`,
`mtr_dim_b_c`, `mtr_dim_b_r`, `mtr_dim_p_m_g`, `mtr_dim_dim16`,
`mtr_dim_f_c_l`, `dim4_lvl2_id`, `dim4_lvl3_id`, `dim4_lvl4_id`,
`dim4_lvl5_id`, `dim4_lvl6_id`, `dim4_tree_dim4`, `dim7_f_lvl2_id`,
`dim7_f_lvl3_id`, `dim7_f_lvl4_id`, `dim7_f_lvl5_id`, `dim7_f_lvl6_id`,
`dim7_f_tree_dim7`, `fiscal_year`, `accounting_period` FROM (select `f`.`dim1`,
`f`.`dim2`, `f`.`fiscal_year`, `f`.`accounting_period`, `f`.`dim3`, `f`.`dim4`,
`f`.`dim5`, `f`.`dim6`, `f`.`dim7`, `f`.`dim8`, `f`.`dim9`, `f`.`dim10`,
`f`.`dim11`, `f`.`dim12`, `f`.`dim13`, `f`.`dim14`, `f`.`dim15`, `f`.`dim16`,
`f`.`dim17`, `f`.`dim18`, `f`.`dim19`, `f`.`amt1`, `f`.`amt2`, `f`.`amt3`,
`f`.`dim20`, `mdim`.`my_test_dim11` ,`mdim`.`my_test_veh_id` , `mdim`.`dim9`
as `mtr_dim_dim9`,`mdim`.`f_t_f_id` as `mtr_dim_t_f_id`, `mdim`.`dim17` as
`mtr_dim_b_c`,`mdim`.`dim15` as `mtr_dim_b_r`,`mdim`.`f_p_m_g` as
`mtr_dim_p_m_g`, `mdim`.`dim16` as `mtr_dim_dim16`,`mdim`.`f_c_l` as
`mtr_dim_f_c_l`, `accnt1`.`dim4_lvl2_id` , `accnt1`.`dim4_lvl3_id` ,
`accnt1`.`dim4_lvl4_id` , `accnt1`.`dim4_lvl5_id` , `accnt1`.`dim4_lvl6_id`
,`accnt1`.`dim4_id` as `dim4_tree_dim4`, `dept_tree`.`dim7_f_lvl2_id` ,
`dept_tree`.`dim7_f_lvl3_id`, `dept_tree`.`dim7_f_lvl4_id` ,
`dept_tree`.`dim7_f_lvl5_id`, `dept_tree`.`dim7_f_lvl6_id`
,`dept_tree`.`dim7_f_id` as `dim7_f_tree_dim7` from `default`.`my_fact1` `f`
join `default`.`dim4_tree` `accnt1` on `accnt1`.`dim4_id` = `f`.`dim4` join
`default`.`p_f_mtr_DIM_orc` `mdim` on `mdim`.`dim9`=`f`.`dim9` join
`default`.`p_f_d_dim_tree` `dept_tree` on `dept_tree`.`dim7_f_id`=`f`.`dim7`)
`default.test_fact_mv`
+----------------------------------+----------------------------------------------------+----------------------------------------------------+
| col_name | data_type
| comment |
+----------------------------------+----------------------------------------------------+----------------------------------------------------+
| Rewrite Enabled: | Yes
| NULL |
| Outdated for Rewriting: | Unknown
| NULL |
+----------------------------------+----------------------------------------------------+----------------------------------------------------+
