Hive默认是采用Derby来存储其Meta信息的,如下:

<property>

  <name>javax.jdo.option.ConnectionURL</name>

  <value>jdbc:derby://zw-hadoop-master:1527/metastore_db;create=true</value>

  <description>JDBC connect string for a JDBC metastore</description>

</property>


<property>

  <name>javax.jdo.option.ConnectionDriverName</name>

  <value>org.apache.derby.jdbc.ClientDriver</value>

  <description>Driver class name for a JDBC metastore</description>

</property>

我们可以修改一下配置,让Mysql来存储其Meta信息

首先,在Mysql服务器上建立相应的库,并赋权限

create database hivedb;

grant all privileges on hivedb.* to hiveuser@'%' identified by 'hiveuser';

然后,把mysql-connector-java-5.1.12.jar拷贝到/opt/sohuhadoop/hive/lib下

再修改hive-default.xml配置

<property>

  <name>javax.jdo.option.ConnectionURL</name>

  <value>jdbc:mysql://192.168.x.x:3306/hivedb?createDatabaseIfNotExist=true</value>

  <description>JDBC connect string for a JDBC metastore</description>

</property>

<property>

  <name>javax.jdo.option.ConnectionDriverName</name>

  <value>com.mysql.jdbc.Driver</value>

  <description>Driver class name for a JDBC metastore</description>

</property>

<property>

  <name>javax.jdo.option.ConnectionUserName</name>

  <value>hiveuser</value>

  <description>username to use against metastore database</description>

</property>

<property>

  <name>javax.jdo.option.ConnectionPassword</name>

  <value>hiveuser</value>

  <description>password to use against metastore database</description>

</property>

修改后,进入hive后,可能会报错:

FAILED: Error in metadata: javax.jdo.JDOException: Couldnt obtain a new sequence (unique id) : Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT'

因为,READ-COMMITTED需要把bin-log以mixed方式来记录,用以下命令来修改:

set global binlog_format='MIXED';