This happens if you previously were on Hive0.12 and metastore database was created by autoCreateSchema as @Deepesh. So, to start with, first of all set datanucleus.autoCreateSchema to false. Contact Hortonworks Support (support.hortonworks.com) while doing in production and ensure you have backed up Hive Metastore Database before doing this. I have faced this issue many times in past while upgrade. And then, I resolve this by performing below steps. ###### Modify/correct table schemas and indexes
######Note: These are empty tables with wrong schema in Hive0.12.0 metastore schema created by autocreation. (Note: This is example of HDP2.2.8 and assumes MySQL as Metastore Datbase)
mysql -u hive -p
Enter Password:
mysql> use hive;
Database changed
DROP INDEX PCS_STATS_IDX ON PART_COL_STATS;
DROP TABLE TAB_COL_STATS;
DROP TABLE PART_COL_STATS;
#######Recreate these tables and index
-- Table structure for table `TAB_COL_STATS`
--
CREATE TABLE IF NOT EXISTS `TAB_COL_STATS` (
`CS_ID` bigint(20) NOT NULL,
`DB_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
`TABLE_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
`COLUMN_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
`COLUMN_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
`TBL_ID` bigint(20) NOT NULL,
`LONG_LOW_VALUE` bigint(20),
`LONG_HIGH_VALUE` bigint(20),
`DOUBLE_HIGH_VALUE` double(53,4),
`DOUBLE_LOW_VALUE` double(53,4),
`BIG_DECIMAL_LOW_VALUE` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin,
`BIG_DECIMAL_HIGH_VALUE` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin,
`NUM_NULLS` bigint(20) NOT NULL,
`NUM_DISTINCTS` bigint(20),
`AVG_COL_LEN` double(53,4),
`MAX_COL_LEN` bigint(20),
`NUM_TRUES` bigint(20),
`NUM_FALSES` bigint(20),
`LAST_ANALYZED` bigint(20) NOT NULL,
PRIMARY KEY (`CS_ID`),
CONSTRAINT `TAB_COL_STATS_FK` FOREIGN KEY (`TBL_ID`) REFERENCES `TBLS` (`TBL_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Table structure for table `PART_COL_STATS`
--
CREATE TABLE IF NOT EXISTS `PART_COL_STATS` (
`CS_ID` bigint(20) NOT NULL,
`DB_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
`TABLE_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
`PARTITION_NAME` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
`COLUMN_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
`COLUMN_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
`PART_ID` bigint(20) NOT NULL,
`LONG_LOW_VALUE` bigint(20),
`LONG_HIGH_VALUE` bigint(20),
`DOUBLE_HIGH_VALUE` double(53,4),
`DOUBLE_LOW_VALUE` double(53,4),
`BIG_DECIMAL_LOW_VALUE` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin,
`BIG_DECIMAL_HIGH_VALUE` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin,
`NUM_NULLS` bigint(20) NOT NULL,
`NUM_DISTINCTS` bigint(20),
`AVG_COL_LEN` double(53,4),
`MAX_COL_LEN` bigint(20),
`NUM_TRUES` bigint(20),
`NUM_FALSES` bigint(20),
`LAST_ANALYZED` bigint(20) NOT NULL,
PRIMARY KEY (`CS_ID`),
CONSTRAINT `PART_COL_STATS_FK` FOREIGN KEY (`PART_ID`) REFERENCES `PARTITIONS` (`PART_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE INDEX PCS_STATS_IDX ON PART_COL_STATS (DB_NAME,TABLE_NAME,COLUMN_NAME,PARTITION_NAME) USING BTREE;
######Now, Edit this file '/usr/hdp/2.2.8.0-3150/hive/scripts/metastore/upgrade/mysql/upgrade-0.12.0-to-0.13.0.mysql.sql' to correct path from relative path for following files otherwise metastore upgrade would fail as file paths will not resolve:
###replace '016-HIVE-6386.mysql.sql' with /usr/hdp/2.2.8.0-3150/hive/scripts/metastore/upgrade/mysql/016-HIVE-6386.mysql.sql
####replace '017-HIVE-6458.mysql.sql' with
/usr/hdp/2.2.8.0-3150/hive/scripts/metastore/upgrade/mysql/017-HIVE-6458.mysql.sql
replace '018-HIVE-6757.mysql.sql' with /usr/hdp/2.2.8.0-3150/hive/scripts/metastore/upgrade/mysql/018-HIVE-6757.mysql.sql
####replace
'hive-txn-schema-0.13.0.mysql.sql' with
/usr/hdp/2.2.8.0-3150/hive/scripts/metastore/upgrade/mysql/hive-txn-schema-0.13.0.mysql.sql
###On Hive Metastore Node:
cd /usr/hdp/2.2.8.0-3150/hive/scripts/metastore/upgrade/mysql/
vi /usr/hdp/2.2.8.0-3150/hive/scripts/metastore/upgrade/mysql/upgrade-0.12.0-to-0.13.0.mysql.sql
###Upgrade Hive Metastore Database
######
mysql -u hive -p
Enter Password:
mysql> use hive;
Database changed
mysql> source /usr/hdp/2.2.8.0-3150/hive/scripts/metastore/upgrade/mysql/upgrade-0.12.0-to-0.13.0.mysql.sql
..............
..............
..............
mysql>exit;
#######
/usr/hdp/2.2.8.0-3150/hive/bin/schematool -upgradeSchema -dbType mysql -userName hive -passWord gacluster -verbose
Metastore connection URL: jdbc:mysql://gpgbyhppn02.srv.gapac.com/hive
Metastore Connection Driver : com.mysql.jdbc.Driver
Metastore connection User: hive
Starting upgrade metastore schema from version 0.13.0 to 0.14.0
Upgrade script upgrade-0.13.0-to-0.14.0.mysql.sql
Looking for pre-0-upgrade-0.13.0-to-0.14.0.mysql.sql in /usr/hdp/2.2.8.0-3150/hive/scripts/metastore/upgrade/mysql
Connecting to jdbc:mysql://hive.srv.test.com/hive
Connected to: MySQL (version 5.1.66)
Driver: MySQL-AB JDBC Driver (version mysql-connector-java-5.1.17-SNAPSHOT ( Revision: ${bzr.revision-id} ))
Transaction isolation: TRANSACTION_READ_COMMITTED
0: jdbc:mysql://hive.srv.test.com/hiv> !autocommit on
Autocommit status: true
0: jdbc:mysql://hive.srv.test.com/hiv> SELECT 'Upgrading MetaStore schema from 0.13.0 to 0.14.0' AS ' '
+---------------------------------------------------+--+
| |
+---------------------------------------------------+--+
| Upgrading MetaStore schema from 0.13.0 to 0.14.0 |
+---------------------------------------------------+--+
1 row selected (0.015 seconds)
0: jdbc:mysql://hive.srv.test.com/hiv> CREATE TABLE IF NOT EXISTS `PART_COL_STATS` ( `CS_ID` bigint(20) NOT NULL, `DB_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, `TABLE_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, `PARTITION_NAME` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, `COLUMN_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, `COLUMN_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, `PART_ID` bigint(20) NOT NULL, `LONG_LOW_VALUE` bigint(20), `LONG_HIGH_VALUE` bigint(20), `DOUBLE_HIGH_VALUE` double(53,4), `DOUBLE_LOW_VALUE` double(53,4), `BIG_DECIMAL_LOW_VALUE` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin, `BIG_DECIMAL_HIGH_VALUE` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin, `NUM_NULLS` bigint(20) NOT NULL, `NUM_DISTINCTS` bigint(20), `AVG_COL_LEN` double(53,4), `MAX_COL_LEN` bigint(20), `NUM_TRUES` bigint(20), `NUM_FALSES` bigint(20), `LAST_ANALYZED` bigint(20) NOT NULL, PRIMARY KEY (`CS_ID`), CONSTRAINT `PART_COL_STATS_FK` FOREIGN KEY (`PART_ID`) REFERENCES `PARTITIONS` (`PART_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
No rows affected (0.002 seconds)
0: jdbc:mysql://hive.srv.test.com/hiv> CREATE INDEX PCS_STATS_IDX ON PART_COL_STATS (DB_NAME,TABLE_NAME,COLUMN_NAME,PARTITION_NAME) USING BTREE
No rows affected (0.298 seconds)
0: jdbc:mysql://hive.srv.test.com/hiv> UPDATE VERSION SET SCHEMA_VERSION='0.14.0', VERSION_COMMENT='Hive release version 0.14.0' where VER_ID=1
1 row affected (0.101 seconds)
0: jdbc:mysql://hive.srv.test.com/hiv> SELECT 'Finished upgrading MetaStore schema from 0.13.0 to 0.14.0' AS ' '
+------------------------------------------------------------+--+
| |
+------------------------------------------------------------+--+
| Finished upgrading MetaStore schema from 0.13.0 to 0.14.0 |
+------------------------------------------------------------+--+
1 row selected (0.002 seconds)
0: jdbc:mysql://hive.srv.test.com/hiv> !closeall
Closing: 0: jdbc:mysql://hive.srv.test.com/hive
beeline>
Completed upgrade-0.13.0-to-0.14.0.mysql.sql
schemaTool completed
######Now, Start Hive Service from Ambari UI##
... View more
