MySQL metabase configuration on hive

Hive Debug Information Display mode:

./hive-hiveconf Hive.root.logger=debug,console

Very useful.

By default, the hive metadata is saved in the embedded Derby database, allowing only one session connection and only for simple testing. In order to support multi-user multi-session, we need a separate meta-database, we use MySQL as a meta-database, the Hive has a good support for MySQL, configuring a separate metabase requires the following steps:

The first step: Install the MySQL server and MySQL client and start the MySQL service.
This step is omitted, see http://www.cnblogs.com/wuhou/archive/2008/09/28/1301071.html for details

Step Two: Install Hive
This step is omitted, see http://www.cnblogs.com/linjiqin/archive/2013/03/04/2942402.html for details

Step three: Establish the appropriate MySQL account for hive and give sufficient privileges to execute the command as follows:

[Email protected]:~$ mysql-uroot-pmysqlmysql> CREATE USER ' hive ' identified by ' MySQL ';mysql> GRANT all privileges On * * to ' hive ' @ '% ' with GRANT option;mysql> flush privileges;

By default, MySQL only allows log on locally, so you need to modify the configuration file to bind the address to comment out:

[Email protected]:~# sudo gedit/etc/mysql/my.cnf

Find the following:

# Instead of skip-networking The default is now-listen only on# localhost which are more compatible and are not less secu Re. #bind-address           = 127.0.0.1  <---comment out this line and you can log in remotely.

To restart the MySQL service:

sudo service MySQL restart

Fourth Step: Create a hive-specific metabase , and remember to use the "hive" account you just created to log in.

Mysql> exit; [Email protected]:~$ mysql-uhive-pmysqlmysql> CREATE DATABASE hive;

Fifth Step: Add the following configuration to the file "Hive-site.xml" in the Conf Directory of hive:

<?xml version= "1.0"? ><?xml-stylesheet type= "text/xsl" href= "configuration.xsl"?><configuration>    <property>        <name>hive.metastore.local</name>        <value>true</value>    </property>    <property>        <name>javax.jdo.option.ConnectionURL</name>        <value>jdbc:mysql://192.168.11.157:3306/hive?characterEncoding=UTF-8</value>    </property>    <property>        <name>javax.jdo.option.ConnectionDriverName</name>        <value> com.mysql.jdbc.driver</value>    </property>    <property>        <name> javax.jdo.option.connectionusername</name>        <value>hive</value>    </property>    <property>        <name>javax.jdo.option.ConnectionPassword</name>        <value> Mysql</value>    </property></configuration>

From the front we know that our "hive-site.xml" is a copy of "Hive-default.xml.template", there are many configuration parameters, but not what we all need, we know, hive The system will load two configuration files, one default profile "Hive-default.xml" and the other is the user-defined file "Hive-site.xml". When the value of the configuration parameter in "Hive-site.xml" is inconsistent with the "hive-default.xml" file, the user-defined will prevail. So we're going to remove all the parameters we don't need, leaving only the content shown above.
Note: In fact, modify the configuration file here if it is very troublesome under Linux, altogether we have set up the FTP, why not, so the master.hadoop above the configuration file downloaded, as required to modify, Such a file in Windows to operate is very convenient, and after the upload, the original can be overwritten.

Sixth step: Copy the MySQL JDBC driver package to the Lib directory of hive.
Version of the JDBC driver package: Mysql-connector-java-5.1.18-bin.jar

Seventh Step: Launch Hive Shell, execute "show tables;" command, if not an error, indicates that Hive based on the standalone metabase has been installed successfully.

Eighth step: Verify the Hive configuration is incorrect , enter Hive's shell to create a new table, you can see the corresponding metabase information in the MySQL hive database.
1) Set up a data table on hive
Hive> CREATE TABLE xp (ID int,name string) ROW FORMAT delimited fields TERMINATED by ' \ t ';
2) View metadata information from the MySQL database
SQL statement used:
Use hive; Using the Hive Database library
Show tables;//shows data tables in the Hive database
SELECT * FROM tbls;//view hive metadata Information
To this hive integrated MySQL as metadata has been completed.

Remote Mode installation
Remote mode installation is to configure the Metastore to the remote machine, you can configure multiple. The following configuration items need to be added in the Hive-site.xml file on the basis of standalone mode:

<property>    <name>hive.metastore.local</name>    <value>local</value></ property><property>    <name>hive.metastore.uris</name>    <value>uri1,uri2,... </value>//can configure multiple URIs    <DESCRIPTION>JDBC connect string for a JDBC metastore</description></ Property>

MySQL metabase configuration on hive