2.3. Using Hive with PostgreSQL

To set up PostgreSQL for use with Hive:

On the Ambari Server host, stage the appropriate PostgreSQL connector for later deployment.
1. Install the connector.
  RHEL/CentOS/Oracle Linux
```
yum install postgresql-jdbc*
```
  SLES
```
zypper install -y postgresql-jdbc
```
2. Copy the connector .jar file to the Java share directory.
```
cp /usr/share/pgsql/postgresql-*.jdbc3.jar /usr/share/java/postgresql-jdbc.jar
```
3. Confirm that .jar is in the Java share directory.
```
ls /usr/share/java/postgresql-jdbc.jar
```
4. Change the access mode of the .jar file to 644.
```
chmod 644 /usr/share/java/postgresql-jdbc.jar
```
5. Execute the following command:
```
ambari-server setup --jdbc-db=postgres --jdbc-driver=/usr/share/java/postgresql-connector-java.jar
```
Create a user for Hive and grant it permissions.
- Using the PostgreSQL database admin utility:
```
echo "CREATE DATABASE $HIVEDATABASE;" | psql -U postgres
echo "CREATE USER $HIVEUSER WITH PASSWORD '$HIVEPASSWORD';" | psql -U postgres
echo "GRANT ALL PRIVILEGES ON DATABASE $HIVEDATABASE TO $HIVEUSER;" | psql -U postgres
```
- Where $HIVEUSER is the Hive user name, $HIVEPASSWORD is the Hive user password and $HIVEDATABASE is the Hive database name.
Load the Hive database schema.
- You must pre-load the Hive database schema into your PostgreSQL database using the schema script.
- When using HDP 2.1 Stack:
```
# psql -U $HIVEUSER -d $HIVEDATABASE
\connect $HIVEDATABASE;
\i hive-schema-0.13.0.postgres.sql;
```
  Find the hive-schema-0.13.0.postgres.sql file in the /var/lib/ambari-server/resources/stacks/HDP/2.1/services/HIVE/etc/ directory of the Ambari Server host after you have installed Ambari Server.
- When using HDP 2.0 Stack:
```
# sudo -u postgres psql
\connect $HIVEDATABASE;
\i hive-schema-0.12.0.postgres.sql;
```
  Find the hive-schema-0.12.0.postgres.sql file in the /var/lib/ambari-server/resources/stacks/HDP/2.0.6/services/HIVE/etc/ directory of the Ambari Server host after you have installed Ambari Server.
- When using HDP 1.3 Stack:
```
# sudo -u postgres psql
\connect $HIVEDATABASE;
\i hive-schema-0.10.0.postgres.sql;
```
  Find the hive-schema-0.10.0.postgres.sql file in the /var/lib/ambari-server/resources/stacks/HDP/1.3.2/services/HIVE/etc/ directory of the Ambari Server host after you have installed Ambari Server.

Legal notices