hiveのmetastoreにPostgreSQLを使う

MySQLの情報は多いですがPostgreSQLはあまり見当たらなかったので書いておきます。
バージョンは2012/10/26時点での最新なので適宜入れ替えて下さい。

1. $HIVE_HOME/libにpostgresのJDBCドライバーを入れる

   $ wget http://jdbc.postgresql.org/download/http://jdbc.postgresql.org/download/postgresql-9.2-1000.jdbc4.jar
   $ mv postgresql-9.2-1000.jdbc4.jar $HIVE_HOME/lib/
   

2. hive-conf.xmlの設定

     <property>
       <name>javax.jdo.option.ConnectionDriverName</name>
       <value>org.postgresql.Driver</value>
       <description>DBの接続ドライバを指定</description>
     </property>
   
     <property>
       <name>javax.jdo.option.ConnectionURL</name>
       <value>jdbc:postgresql://localhost:5432/hive</value>
       <description>JDBCの接続URLを指定</description>
     </property>
   
     <property>
       <name>javax.jdo.option.ConnectionUserName</name>
       <value>hive</value>
       <description>DBの接続ユーザを指定</description>
     </property>
   
     <property>
       <name>javax.jdo.option.ConnectionPassword</name>
       <value>hive</value>
       <description>DBの接続パスワードを指定</description>
     </property>
   
   

3. postgresにDB、ユーザーを追加する

   $ createuser -P hive
   Enter password for new role: hive
   Enter it again: hive
   
   $ createdb -O hive hive
   

4. hiveを起動しshow tablesを実行

   $ hive
   > show tables; --ここでmetastoreに必要な諸々がDBに追加される
   

5. 完了

PostgreeSQL 9.1以上の場合

show tablesで下記のエラーが出ます

hive> show tables;
FAILED: Error in metadata: MetaException(message:Got exception: java.lang.reflect.UndeclaredThrowableException null)
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask

原因

詳細なエラーを確認します

$ hive -hiveconf hive.root.logger=DEBUG,console
> show tables

スタックトレースの途中にこんなエラーが出ていると思います

Caused by: javax.jdo.JDODataStoreException: Error executing JDOQL query "SELECT "THIS"."TBL_NAME" AS NUCORDER0 FROM "TBLS" "THIS" LEFT OUTER JOIN "DBS" "THIS_DATABASE_NAME" ON "THIS"."DB_ID" = "THIS_DATABASE_NAME"."DB_ID" WHERE "THIS_DATABASE_NAME"."NAME" = ? AND (LOWER("THIS"."TBL_NAME") LIKE ? ESCAPE '\\' ) ORDER BY NUCORDER0 " : ERROR: invalid escape string
  Hint: Escape string must be empty or one character..

これはPostgreSQL 9.1から文字列中のエスケープの扱いが変更されたことが原因です

参考: PostgreSQL: Documentation: 9.1: Lexical Structure

対応

1. $PGDATA/postgresql.confに以下の設定を追加

   standard_conforming_strings = off
   

2. postgresの設定を再読み込み

   $ pg_ctl reload
   

3. 完了