creating materialized view getting below error?

[#1]

Hi team,

Iam creating materialized view for a table ,for that getting below error.can any one help on this?

create materialized view car1_mv as select * from cars;

Error: Error while compiling statement: FAILED: SemanticException org.apache.hadoop.hive.ql.parse.SemanticException: Automatic rewriting for materialized view cannot be enabled if the materialized view uses non-transactional tables (state=42000,code=40000)

[#2 · Accepted solution]

@choppadandi vamshi krishna

You can only create a materialized view on transactional tables. where changes in the base table will be logged and there is a refresh mechanism to update the materialized view whenever the view is queried

Please, can you check whether the base table is transactional? Below are steps to help you determine that. The assumption below is your table cars is in the default database.

# hive -e "describe extended <Database>.<tablename>;" | grep "transactional=true"

If you get an output with the string that you grep for, then the table is transactional

Example:

 #hive -e "describe extended default.cars;" | grep "transactional=true"

Else Alter the flat table to make it transactional.

ALTER TABLE cars SET TBLPROPERTIES ('transactional'='true');  

Then try creating the materialized view again it should succeed

Please revert

[#3]

hi @Geoffrey Shelton Okot,

Thanks for the reply,

As per my understanding ,if we enable materialized views the table must have orc storage format and transnational,

my doubt is when we create different storage format other than orc like (avro,text etc) did we apply materialized views if it works?

[#4]

@choppadandi vamshi krishna

With Hive 3.0 you have hive and druid storage options too and the orc is the most common. I haven't tested and can't confirm whether it's possible to create an MV over Avro and refresh it a regular interval would work

You can also use the rebuild option to refresh the MV when scripting run the rebuild which will overwrite the previous mv before querying it so you have an updated

ALTER MATERIALIZED VIEW mv REBUILD;  

You also have the Druid storage org .apache.hadoop.hive.druid.DruidStorageHandler or you can rebuild an MV like every 5 minutes but you should take into account that every rebuild will take longer than the previous due to the addition of data in the source table.

HTH

[#5]

Do you know my hive mv not incremental rebuild with druid storage handler?

[#6]

hi @Geoffrey Shelton Okot,

iam able create to materialized view for hive managed table ,but not external table ?