Preventing SparkSQL incompatibility

As the Hive data engineer, if you did not convert your managed, non-ACID tables to external tables before the upgrade, the upgrade process converts the hive-owned files to ACID v2 files. These files are not compatible with native SparkSQL.

Consider using either one of the following options:
  • Convert ACID tables to external tables after the CDP upgrade.
  • Use the Hive Warehouse Connector.
Follow these steps to create a new external table using Hive 3 and migrate the data from the managed table to the new table.
  1. Run the SHOW CREATE TABLE statement on the original table to get the full definition of the table.
    SHOW CREATE TABLE <tablename>;
  2. Rename the managed table to *_old.
  3. Migrate data from *_old to <new> external table using the original name in the historical, or the default, location (/warehouse/tablespace/external/hive/<?>.db/<tablename>).
    CREATE EXTERNAL TABLE new_t AS SELECT * FROM old_t;