Configuring a Remote PostgreSQL Database for the Hive Metastore
Before you can run the Hive metastore with a remote PostgreSQL database, you must configure a JDBC driver to the remote PostgreSQL database, set up the initial database schema, and configure the PostgreSQL user account for the Hive user.
After installing PostgreSQL, perform the following steps to configure Hive Metastore on PostgreSQL.
Installing and Configuring PostgreSQL for the Hive Metastore
- Download the PostgreSQL JDBC driver.
Refer to the official PostgreSQL JDBC Driver website to download the JDBC driver and get information about the latest updates. Determine the appropriate database version and get the released drivers and JAR file.
- Run the following commands using
sudo
:- Move the JAR into the Java
share
directory:sudo mv <postgresql-jdbc.jar> /usr/share/java/postgresql-jdbc.jar
- Change the access mode of the JAR file to
644:
sudo chmod 644 /usr/share/java/postgresql-jdbc.jar
- Create symbolic link to the
/usr/lib/hive/lib/ directory
, for example:sudo ln -s /usr/share/java/postgresql-jdbc.jar /opt/mapr/hive/hive-<version>/lib/postgresql-jdbc.jar
- Move the JAR into the Java
- Create the Metastore database and user
accounts:
$ sudo -u postgres psql postgres=# CREATE USER hiveuser WITH PASSWORD 'mypassword'; postgres=# CREATE DATABASE metastore;
To verify the connection from the Metastore service host, run the following command:psql -h myhost -U hiveuser -d metastore metastore=#
- Configure the Metastore service to communicate with the PostgreSQL database by setting
the necessary properties (shown below) in the
/opt/mapr/hive//hive-<version>/conf/hive-site.xml
file. Suppose a PostgreSQL database running on hostmyhost
under the user accounthive
with the passwordmypassword
, set the following configuration properties in thehive-site.xml
file:<property> <name>javax.jdo.option.ConnectionURL</name> <value>jdbc:postgresql://myhost/metastore</value> </property> <property> <name>javax.jdo.option.ConnectionDriverName</name> <value>org.postgresql.Driver</value> </property> <property> <name>javax.jdo.option.ConnectionUserName</name> <value>hiveuser</value> </property> <property> <name>javax.jdo.option.ConnectionPassword</name> <value>mypassword</value> </property> <property> <name>hive.metastore.uris</name> <value>thrift://<n.n.n.n>:9083</value> <description>IP address (or fully-qualified domain name) and port of the metastore host</description> </property>
NOTEThough you can use the samehive-site.xml
properties on all the hosts (client, metastore, HiveServer),hive.metastore.uris
is the only property that must be configured on all of the hosts; the other properties are only needed on the Metastore host. - Run
schemaTool
to create the initial DB structure:/opt/mapr/hive/hive-<version>/bin/schematool -dbType postgres -initSchema