使用Mysql来存储Hive的Meta信息
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';