Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

creating materialized view getting below error?

Solved Go to solution
Highlighted

creating materialized view getting below error?

New Contributor

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)



1 ACCEPTED SOLUTION

Accepted Solutions

Re: creating materialized view getting below error?

Mentor

@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

5 REPLIES 5

Re: creating materialized view getting below error?

Mentor

@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?

New Contributor

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?

Mentor

@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?

Community Manager

The question above and the reply thread below were originally posted in the Community Help Track. On Sun Jun 16 16:03 UTC 2019, a member of the HCC moderation staff moved it to the Data Processing track. The Community Help Track is intended for questions about using the HCC site itself.

Re: creating materialized view getting below error?

New Contributor

hi @Geoffrey Shelton Okot,

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