2.3. Using Hive with PostgreSQL

To set up PostgreSQL for use with Hive:

  1. 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
  2. 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.

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


loading table of contents...