大叔经验分享（29）cdh5使用已存在的metastore数据库部署hive

cdh5.16.1使用的hive版本是hive-1.1.0+cdh5.16.1+1431，详见：https://www.cloudera.com/documentation/enterprise/release-notes/topics/cdh_vd_cdh_package_tarball_516.html
如果想将直接使用之前已有的hive metastore的数据库，比如hive1.2，要做如下操作：

1）修改元数据的version

mysql> update VERSION set schema_version = '1.1.0';

修改version之后部署hive还会报错：

Table(s) [ [metastore_db_properties] ] are missing from the metastore database schema.

这个表metastore_db_properties在apache hive里是没有的，需要到cdh hive里找

# grep -i metastore_db_properties /opt/cloudera/parcels/CDH-5.16.1-1.cdh5.16.1.p0.3/lib/hive/scripts/metastore/upgrade/mysql/*
/opt/cloudera/parcels/CDH-5.16.1-1.cdh5.16.1.p0.3/lib/hive/scripts/metastore/upgrade/mysql/041-HIVE-16556.mysql.sql:-- Table structure for table METASTORE_DB_PROPERTIES
/opt/cloudera/parcels/CDH-5.16.1-1.cdh5.16.1.p0.3/lib/hive/scripts/metastore/upgrade/mysql/041-HIVE-16556.mysql.sql:CREATE TABLE IF NOT EXISTS `METASTORE_DB_PROPERTIES` (

2）建表metastore_db_properties

mysql> CREATE TABLE IF NOT EXISTS `METASTORE_DB_PROPERTIES` (
`PROPERTY_KEY` varchar(255) NOT NULL,
`PROPERTY_VALUE` varchar(1000) NOT NULL,
`DESCRIPTION` varchar(1000),
PRIMARY KEY(`PROPERTY_KEY`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

部署还有报错：

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'A0.SCHEMA_VERSION_V2' in 'field list'

查找

# grep -i SCHEMA_VERSION_V2 /opt/cloudera/parcels/CDH-5.16.1-1.cdh5.16.1.p0.3/lib/hive/scripts/metastore/upgrade/mysql/*

3）修改表结构version

mysql> ALTER TABLE VERSION ADD COLUMN SCHEMA_VERSION_V2 VARCHAR(255);
mysql> UPDATE VERSION SET SCHEMA_VERSION='1.1.0', VERSION_COMMENT='Hive release version 1.1.0', SCHEMA_VERSION_V2='1.1.0-cdh5.16.1' where VER_ID=1;

还有异常

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'A0.OWNER_TYPE' in 'field list'

查找

# grep -i OWNER_TYPE /opt/cloudera/parcels/CDH-5.16.1-1.cdh5.16.1.p0.3/lib/hive/scripts/metastore/upgrade/mysql/*

4）修改表结构

ALTER TABLE `DBS` ADD `OWNER_TYPE` varchar(10);
ALTER TABLE `TBLS` ADD COLUMN `OWNER_TYPE` VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL;

然后cdh的hive可以部署成功，直接使用之前的metastore数据库，并且hive功能正常；