Using Hive with PostgreSQL

« Prev

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. Confirm that .jar is in the Java share directory.
  ls /usr/share/java/postgresql-jdbc.jar
3. Change the access mode of the.jar file to 644.
  chmod 644 /usr/share/java/postgresql-jdbc.jar
4. Execute the following command:
  ambari-server setup --jdbc-db=postgres --jdbc-driver=/usr/share/java/postgresql-jdbc.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.
- For a HDP 2.2 or later Stacks:
  Important
  Ambari sets up the Hive Metastore database schema automatically.
  You do not need to pre-load the Hive Metastore database schema into your PostgreSQL database for a HDP 2.2 Stack.
- For HDP 2.1 Stack:
  You must pre-load the Hive database schema into your PostgreSQL database using the schema script, as follows:
  # 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.
- For HDP 2.0 Stack:
  You must pre-load the Hive database schema into your PostgreSQL database using the schema script, as follows:
  # 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.

​Using Hive with PostgreSQL

Using Hive with PostgreSQL