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
tables:
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
statement.
DROP MATERIALIZED VIEW
[db_name.]materialzed_view_name;
Upgrade the cluster and recreate the views after the upgrade process is
complete.
