question Re: creating materialized view getting below error? in Support Questions

creating materialized view getting below error?

wamshikreshna — Sat, 15 Jun 2019 21:01:59 GMT

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)

Re: creating materialized view getting below error?

Shelton — Sat, 15 Jun 2019 21:16:21 GMT

@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

Re: creating materialized view getting below error?

wamshikreshna — Sat, 15 Jun 2019 23:20:54 GMT

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?

Re: creating materialized view getting below error?

Shelton — Sun, 16 Jun 2019 00:52:10 GMT

@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

Re: creating materialized view getting below error?

wamshikreshna — Mon, 17 Jun 2019 15:52:49 GMT

hi @Geoffrey Shelton Okot,

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

Re: creating materialized view getting below error?

anvanna — Tue, 03 Dec 2019 03:37:57 GMT

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