Use MySQL for the Hive Metastore
The metadata for Hive tables and partitions are stored in the Hive Metastore. By default, the Hive Metastore stores all Hive metadata in an embedded Apache Derby database in the HPE Ezmeral Data Fabric file system. Derby only allows one connection at a time; if you want multiple concurrent Hive sessions, you can use MySQL for the Hive Metastore.
Prerequisites
- Verify that MySQL (version 5.6.17 or later) is installed on the machine that will
host the Hive metastore, and also verify that you can connect to the MySQL server
from the Hive machine. You can run the Hive metastore on any machine that is
accessible from Hive. You can test this with the following
command:
mysql -h <hostname> -u <user>
- The database administrator must create a database for the Hive metastore data, and
the username specified in
javax.jdo.option.ConnectionUserName
must have permissions to access it. The database can be specified using theConnectionURL
parameter. The tables and schemas are created automatically when the metastore is first started.
TIP
In MapR 6.1.0 and earlier releases, the following steps can be used
interchangeably for MariaDB.About this task
IMPORTANT
For MySQL 8, set the
javax.jdo.option.ConnectionDriverName
property to
com.mysql.cj.jdbc.Driver
. The com.mysql.jdbc.Driver
is deprecated. The new driver class is com.mysql.cj.jdbc.Driver
.
However, the driver is automatically registered via the Service Provider Interface, so
manual loading of the driver class is generally unnecessary. Procedure
-
Update the
hive-site.xml
in the Hive configuration directory (/opt/mapr/hive/hive-<version>/conf
) with the following contents:<configuration> <property> <name>javax.jdo.option.ConnectionURL</name> <value>jdbc:mysql://localhost:3306/hive?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>root</value> <description>username to use against metastore database</description> </property> <property> <name>javax.jdo.option.ConnectionPassword</name> <value><fill in with password></value> <description>password to use against metastore database</description> </property> <property> <name>hive.metastore.uris</name> <value>thrift://localhost:9083</value> </property> </configuration>
-
Run the
schematool
command as an initialization step./opt/mapr/hive/hive-<version>/bin/schematool -dbType mysql -initSchema
-
To connect to an existing MySQL metastore, make sure
the
ConnectionURL
parameter and theThrift URIs
parameters inhive-site.xml
point to the metastore's host and port. -
To set a specific port for Thrift URIs, add the
command
export METASTORE_PORT=<port>
into the filehive-env.sh
(ifhive-env.sh
does not exist, create it in the Hive configuration directory). Example:export METASTORE_PORT=9083
-
Start the Hive Metastore service using one of the following commands:
If you want the Hive Metastore to be managed by Warden, the maprcli, and the Control System:
maprcli node services -name hivemeta -action start -nodes <space delimited list of nodes>
If you want the Hive Metastore to be managed with standard hive commands:/opt/mapr/hive/hive-<version>/bin/hive --service metastore --start
You can use also use
nohup hive --service metastore
to run the Metastore in the background.WARNINGIf you have not configured a MySQL Metastore, do not run the Hive shell from an NFS mount location. If you try to do this, Hive will fail. The same problem will occur if you use thehive-site.xml
file to configure the Metastore on an NFS mount location. Avoid both of these configurations.