Prepare Hive tables for migration
To prepare the tables for migration, use Hive SRE Tool which is a Cloudera Lab tool that scans your Hive Metastore and HDFS to identify common upgrade problems that can cause the CDP upgrade to fail. The tool provides guidance for fixing those problems before migrating the tables to CDP. This guidance is provided through reports that the administrator must take action on. The tool does not take corrective action itself.
Cloudera strongly recommends running the Hive SRE Tool to aid in this pre-upgrade HMS healthcheck. If you do not run it, you must manually investigate your HMS for the following types of problems.
Please refer to Hive SRE Tooling for tool setup, tool execution & interpretation of output using Hive SRE Tool on Hive Metadata.
The general transition from Hive 1 and 2 to Hive 3 includes the following types of HMS operations. The Hive SRE Tool performs the equivalent types of checks in an automated fashion. Please review these from the Cloudera documentation site (CDH 5, CDH 6, HDP 2, HDP 3).
Note for section below:XXX stands for some value/count, this is a value of the number of affected paths we will get from the Hive-SRE tool output reports. For each type of condition listed in the table , there will be some number of paths/tables affected.
Tables that would be converted from a Managed Non-Acid table to an ACID transactional table require the files to match a certain pattern. This process will scan the potential directories of these tables for bad filename patterns. When located, it will indicate which tables/partitions have file naming conventions that would prevent a successful conversion to ACID.
The best and easiest way to correct these file names is to use HiveSQL to
rewrite the contents of the table/partition with a simple
This type of statement will replace the current bad filenames with valid file names by rewriting the contents in HiveSQL.
There are approximately XXX paths that may need remediation. The list of paths
can be found in the output of the Hive assessment report in the
Missing Directories cause the upgrade conversion process to fail. This inconsistency is an indicator that data was removed from the file system, but the Hive MetaStore was not updated to reflect that operation. An example of this is deleting a partition in HDFS, without dropping the partition in Hive.
There are XXX affected paths that need remediation. The list of
directories can be found in the output of the Hive assessment report, in the file
|Managed Shadow Tables||
In Hive 3, Managed tables are 'ACID' tables. Sharing a location between two 'ACID' tables will cause compaction issues and data issues. These need to be resolved before the upgrade.
There are XXX affected tables that need remediation. The list of paths
can be found in the output of the Hive assessment report, in the file
|Managed Table Migrations||
This process will list tables that will and 'could' be migrated to "Managed ACID" tables during the upgrade process.
Tables used directly by Spark or if data is managed by a separate process that interacts with the FileSystem, you may experience issues post-upgrade.
Recommended: Consider converting these tables to external tables.
There are XXX affected tables that may need remediation. The list of
tables can be found in the output of the Hive assessment report in the file
In the upgrade to Hive 3, ACID tables must be compacted prior to initiating the upgrade.
XXX tables were noted as requiring compaction. Because CDH does not support Hive ACID tables, this may be a leftover from the previous HDP to CDH migration that the customer implemented.
This should be investigated further. The affected table is
|Unknown SerDe Jars||
Will list tables using SerDe’s that are not standard to the platform. Review list of SerDes and verify they are still necessary and available for CDP.
There are approximately XXX tables configured with 3rd party SerDes.
|Remove transactional=false from Table Properties||
In CDH 5.x it is possible to create tables with the property
You must remove 'transactional'='false' from any tables you want to upgrade from CDH 5.x to CDP.
Alter the table as follows:
|Make Tables SparkSQL Compatible||
Non-Acid, managed tables in ORC or in a Hive Native (but non-ORC) format that are owned by the POSIX user hive will not be SparkSQL compatible after the upgrade unless you perform manual conversions.
If your table is a managed, non-ACID table, you can convert it to an external table using this procedure (recommended). After the upgrade, you can easily convert the external table to an ACID table, and then use the Hive Warehouse Connector to access the ACID table from Spark.
Take one of the following actions.
|Legacy Kudu Serde Report||Early versions of Hive/Impala tables using Kudu were built before Kudu became an Apache Project. Once it became an Apache Project, the base Kudu Storage Handler classname changed. This report locates and reports on tables using the legacy storage handler class.|
|Legacy Decimal Scale and Precision Check||When the DECIMAL data type was first introduced in Hive 1, it did NOT include a Scale or Precision element. This causes issues in later integration with Hive and Spark. We'll identify and suggest corrective action for tables where this condition exists.|
|Database / Table and Partition Counts||Use this to understand the scope of what is in the metastore.|
|Small Files, Table Volumes, Empty Datasets||Identify and fix these details to clean up unwanted datasets in the cluster which would speed up the Hive upgrade process.|
|Merge Independent Hive and Spark Catalogs||
In HDP 3.0 - 3.1.4, Spark and Hive use independent catalogs for accessing tables created using SparkSQL or Hive tables. A table created from Spark resides in the Spark catalog. A table created from Hive resides in the Hive catalog. Databases fall under the catalog namespace, similar to how tables belong to a database namespace. In HPD 3.1.5, Spark and Hive share a catalog in Hive metastore (HMS) instead of using separate catalogs.
The Apache Hive schematool in HDP 3.1.5 and CDP releases supports the mergeCatalog task.