Table meta export from MapR hive to Postgres DB (Database)

To Install the PostgreSQL JDBC driver, On the Hive Metastore server host, install libpostgresql-jdbc-java and symbolically link the file to the /usr/lib/hive/lib/ directory. For example:

$ sudo apt-get install libpostgresql-jdbc-java
$ ln -s /usr/share/java/postgresql-jdbc4.jar /opt/mapr/hive//hive-<version>/lib/postgresql-jdbc.jar

Create the Metastore database and user accounts.

$ sudo -u postgres psql
postgres=# CREATE USER hiveuser WITH PASSWORD 'mypassword';
postgres=# CREATE DATABASE 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.

<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>

Run schemaTool to create the initial DB structure.

/opt/mapr/hive/hive-<version>/bin/schematool -dbType postgres -initSchema