Hive configuration MySQL Metastore

Hive configuration MySQL Metastore
In addition to saving real data in hive, there are additional data to describe the library, tables, and data, called hive metadata. Where are these metadata stored?
If you do not modify the configuration hive defaults to using the built-in Derby database store metadata.
Derby is a Java-based file database developed by Apache.
You can examine the directory where the command was executed before, and you will find that it produces a metastore.db file, which is the database file that Derby generates to hold the metadata.

The Derby database is used only for testing, and there are many limitations when it comes to real use.
The most obvious problem is that concurrency cannot be supported.
After testing, it can be found that the use of hive in the same directory can not be enabled at the same time, different directories may open hive at the same time but will each produce metastore.db files resulting in data cannot be accessed together.
So in a real production environment we would not use the default Derby database to save hive metadata.

Hive currently supports Derby and MySQL to store meta data.

Configure hive to use MySQL to save Meta data information:
Delete/user/hive in HDFs
Hadoop fs-rmr/user/hive
Copy Hive/conf/hive-default.xml.template to Hive-site.xml
CP Hive-default.xml.template Hive-site.xml
Configuring in <configuration>
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://hadoop01:3306/hive?createDatabaseIfNotExist=true</value>
&LT;DESCRIPTION&GT;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>root</value>
<description>username to use against Metastore database</description>
</property>

<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>root</value>
<description>password to use against Metastore database</description>
</property>

!! Create the Hive metabase manually, note that this library must be latin1, or there will be a strange problem! So it is recommended to create manually! And can not have any hive operation before creating the library, otherwise the Automatically created library table will use the MySQL default character set, still error!
!! Another way is to modify the MySQL configuration file, so that the MySQL default encoding set is Latin1, so that hive automatically created by the metabase is latin1, but this modification will affect the entire MySQL database, if there are other libraries in MySQL, this method is not good.
Create DATABASE hive character Set latin1;

Copy the MySQL connection jar package to the $hive_home/lib directory

If there is an issue with no permissions, in MySQL authorization (performed on the machine where MySQL is installed)
Mysql-uroot-p
# (Execute the following statement * *: All tables under all libraries%: Any IP address or host can be connected)
GRANT all privileges on * * to ' root ' @ '% ' identified by ' root ' with GRANT OPTION;
FLUSH privileges;

Then go to the Hive command line and try to create the library table to find no problems.

The test found that there was no problem opening multiple connections.

Connect to MySQL and find a hive library. Metadata with Hive is saved. dbs-The metadata information of the database, tbls-the table information. COLUMNS_V2 table field information, sds-table corresponding to the HDFs directory

Hive configuration MySQL Metastore