Preparing the backend HMS database for upgrade
Learn how you can prevent upgrade failures when you have materialized views or MSSQL indexed views created on top of Hive backend schemas, such as SYS or INFORMATION_SCHEMA tables.
If you have created any materialized views or MSSQL indexed views on Hive SYS or INFORMATION_SCHEMA tables, your upgrade process can fail when the upgrade SQL statements are trying to drop these tables.
You must drop the materialized views before performing an upgrade and then recreate the views after the upgrade process is complete.
You must back up the Hive metastore (HMS) backend database before dropping the materialized views.
Start a Hive Beeline session and run the following query to identify
materialized views that are created on top of the SYS and INFORMATION_SCHEMA
SELECT DISTINCT d.DB_LOCATION_URI, d.NAME, t.TBL_NAME, t.TBL_TYPE, t.OWNER, t.VIEW_EXPANDED_TEXT FROM sys.TBLS t INNER JOIN sys.DBS d ON t.DB_ID = d.DB_ID INNER JOIN sys.MV_CREATION_METADATA mv ON mv.TBL_NAME = t.TBL_NAME INNER JOIN sys.MV_TABLES_USED tu ON mv.MV_CREATION_METADATA_ID = tu.MV_CREATION_METADATA_ID WHERE tu.TBL_ID IN (SELECT distinct t.TBL_ID FROM sys.MV_CREATION_METADATA mv INNER JOIN sys.MV_TABLES_USED tu ON mv.MV_CREATION_METADATA_ID = tu.MV_CREATION_METADATA_ID INNER JOIN sys.TBLS t ON tu.TBL_ID = t.TBL_ID INNER JOIN sys.DBS d ON t.DB_ID = d.DB_ID WHERE lower(d.NAME) IN ('sys', 'information_schema')) AND upper(t.TBL_TYPE) = 'MATERIALIZED_VIEW';
If the query returns any materialized views, drop each view using the DROP
DROP MATERIALIZED VIEW [db_name.]materialzed_view_name;
- Upgrade the cluster and recreate the views after the upgrade process is complete.