Hive, mail # user - Re: Problem with using Postgres as hive meta store DB.

Problem with using Postgres as hive meta store DB.

Ehsan Haq 2013-01-23, 08:46

Hi,
   I am new to hive, and I am trying to use postgresql9.1 as a meta data
store for hive. I read and followed the tutorial posted by cloudera "
https://ccp.cloudera.com/display/CDH4DOC/Hive+Installation#HiveInstallation-InstallingHive".
However I am unable to use it. Below are further details.

* Using hive-0.8.1 and hive-0.9.0
* Postgresql 9.1 with JDBC postgresql-9.1-903.jdbc3.jar
* I didn't find any postgres script in the bundle, thats why
autoCreateSchema can't work (I guess). I found another script for creating
hive meta data in postgres and ran the script to create the schemas.
* Did the configurations in the conf file and copied the JDBC driver file
in lib folder, followed the steps as mentioned in "
https://ccp.cloudera.com/display/CDH4DOC/Hive+Installation#HiveInstallation-ConfiguringaremotePostgreSQLdatabaseasHiveMetastore
"

However after logging in and running "show tables;" I get the following
exception
hive> show tables;
FAILED: Error in metadata: MetaException(message:Got exception: 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..)
FAILED: Execution Error, return code 1 from
org.apache.hadoop.hive.ql.exec.DDLTask

The detailed exception in the log file is. The last two exceptions suggest
that it was unable to find the drive. Is there any particular JDBC postgres
driver to work with hive???

2013-01-23 08:37:46,907 ERROR exec.Task (SessionState.java:printError(400))
- FAILED: Error in metadata: javax.jdo.JDOFatalInternalException: Error
creating transactional connection factory
NestedThrowables:
java.lang.reflect.InvocationTargetException
org.apache.hadoop.hive.ql.metadata.HiveException:
javax.jdo.JDOFatalInternalException: Error creating transactional
connection factory
NestedThrowables:
java.lang.reflect.InvocationTargetException
   at org.apache.hadoop.hive.ql.metadata.Hive.getDatabase(Hive.java:1080)
   at
org.apache.hadoop.hive.ql.metadata.Hive.databaseExists(Hive.java:1065)
   at org.apache.hadoop.hive.ql.exec.DDLTask.showTables(DDLTask.java:1992)
   at org.apache.hadoop.hive.ql.exec.DDLTask.execute(DDLTask.java:323)
   at org.apache.hadoop.hive.ql.exec.Task.executeTask(Task.java:134)
   at
org.apache.hadoop.hive.ql.exec.TaskRunner.runSequential(TaskRunner.java:57)
   at org.apache.hadoop.hive.ql.Driver.launchTask(Driver.java:1326)
   at org.apache.hadoop.hive.ql.Driver.execute(Driver.java:1118)
   at org.apache.hadoop.hive.ql.Driver.run(Driver.java:951)
   at
org.apache.hadoop.hive.cli.CliDriver.processLocalCmd(CliDriver.java:258)
   at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:215)
   at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:406)
   at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:689)
   at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:557)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
   at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
   at java.lang.reflect.Method.invoke(Method.java:601)
   at org.apache.hadoop.util.RunJar.main(RunJar.java:156)
Caused by: javax.jdo.JDOFatalInternalException: Error creating
transactional connection factory
NestedThrowables:
java.lang.reflect.InvocationTargetException
   at
org.datanucleus.jdo.NucleusJDOHelper.getJDOExceptionForNucleusException(NucleusJDOHelper.java:425)
   at
org.datanucleus.jdo.JDOPersistenceManagerFactory.freezeConfiguration(JDOPersistenceManagerFactory.java:601)
   at
org.datanucleus.jdo.JDOPersistenceManagerFactory.createPersistenceManagerFactory(JDOPersistenceManagerFactory.java:286)
   at
org.datanucleus.jdo.JDOPersistenceManagerFactory.getPersistenceManagerFactory(JDOPersistenceManagerFactory.java:182)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
   at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
   at java.lang.reflect.Method.invoke(Method.java:601)
   at javax.jdo.JDOHelper$16.run(JDOHelper.java:1958)
   at java.security.AccessController.doPrivileged(Native Method)
   at javax.jdo.JDOHelper.invoke(JDOHelper.java:1953)
   at
javax.jdo.JDOHelper.invokeGetPersistenceManagerFactoryOnImplementation(JDOHelper.java:1159)
   at javax.jdo.JDOHelper.getPersistenceManagerFactory(JDOHelper.java:803)
   at javax.jdo.JDOHelper.getPersistenceManagerFactory(JDOHelper.java:698)
   at
org.apache.hadoop.hive.metastore.ObjectStore.getPMF(ObjectStore.java:246)
   at
org.apache.hadoop.hive.metastore.ObjectStore.getPersistenceManager(ObjectStore.java:275)
   at
org.apache.hadoop.hive.metastore.ObjectStore.initialize(ObjectStore.java:208)
   at
org.apache.hadoop.hive.metastore.ObjectStore.setConf(ObjectStore.java:183)
   at
org.apache.hadoop.util.ReflectionUtils.setConf(ReflectionUtils.java:62)
   at
org.apache.hadoop.util.ReflectionUtils.newInstance(ReflectionUtils.java:117)
   at
org.apache.hadoop.hive.metastore.RetryingRawStore.<init>(RetryingRawStore.java:62)
   at
org.apache.hadoop.hive.metastore.RetryingRawStore.getProxy(RetryingRawStore.java:71)
   at
org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.newRawStore(HiveMetaStore.java:346)
   at
org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.getMS(HiveMetaStore.java:333)
   at
org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.createDefaultDB(HiveMetaStore.java:371)
   at
org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.init(HiveMetaStore.java:278)
   at
org.apache.hadoop.hive.metastore.Hiv

Re: Problem with using Postgres as hive meta store DB.

Ehsan Haq 2013-01-23, 14:09

Hmm. I have ran it on 0.9.0, will try it on 0.8.1. However i also found an
upgrade script for mysql from 0.8.0 to 0.9.0, and it didn't had anything
just two select static text lines. So I thought it would be same for
postgres, also the postgres schema that I am using was for version 0.7.0
which I found in some old version as the latest releases don't have a
postgres script in them.

/Ehsan

On Wed, Jan 23, 2013 at 3:00 PM, Dean Wampler <
[EMAIL PROTECTED]> wrote:

> Are you able to use Hive v0.8.1 and v0.9.0 interchangeably? Since the
> metadata schema changed between versions, I wouldn't expect 0.8.1 to work
> if you're schema is set up for 0.9.0.
>
> dean
>
>
> On Wed, Jan 23, 2013 at 6:35 AM, Ehsan Haq <[EMAIL PROTECTED]> wrote:
>
>> Thanks, that solved the problem :-)
>>
>> /Ehsan
>>
>>
>> On Wed, Jan 23, 2013 at 10:00 AM, wd <[EMAIL PROTECTED]> wrote:
>>
>>>
>>> On Wed, Jan 23, 2013 at 4:46 PM, Ehsan Haq <[EMAIL PROTECTED]> wrote:
>>>
>>>> ERROR: invalid escape string
>>>> Hint: Escape string must be empty or one character..)
>>>>
>>>
>>>
>>> You can set standard_conforming_strings = off in postgresql.conf to
>>> avoid this.
>>>
>>>
>>
>>
>> --
>> *Muhammad Ehsan ul Haque*
>> Klarna AB
>> Norra Stationsgatan 61
>> SE-113 43 Stockholm
>>
>> Tel: +46 (0)8- 120 120 00
>> Fax: +46 (0)8- 120 120 99
>> Web: www.klarna.com
>>
>
>
>
> --
> *Dean Wampler, Ph.D.*
> thinkbiganalytics.com
> +1-312-339-1330
>
>
--
*Muhammad Ehsan ul Haque*
Klarna AB
Norra Stationsgatan 61
SE-113 43 Stockholm

Tel: +46 (0)8- 120 120 00
Fax: +46 (0)8- 120 120 99
Web: www.klarna.com

Re: Problem with using Postgres as hive meta store DB.

Ehsan Haq 2013-01-23, 17:34

Well I have tested with version 0.8.1 as well and the same postgresql
schema that is working with 0.9.0 is also working with 0.8.1.

/Ehsan

On Wed, Jan 23, 2013 at 3:09 PM, Ehsan Haq <[EMAIL PROTECTED]> wrote:

> Hmm. I have ran it on 0.9.0, will try it on 0.8.1. However i also found an
> upgrade script for mysql from 0.8.0 to 0.9.0, and it didn't had anything
> just two select static text lines. So I thought it would be same for
> postgres, also the postgres schema that I am using was for version 0.7.0
> which I found in some old version as the latest releases don't have a
> postgres script in them.
>
> /Ehsan
>
>
> On Wed, Jan 23, 2013 at 3:00 PM, Dean Wampler <
> [EMAIL PROTECTED]> wrote:
>
>> Are you able to use Hive v0.8.1 and v0.9.0 interchangeably? Since the
>> metadata schema changed between versions, I wouldn't expect 0.8.1 to work
>> if you're schema is set up for 0.9.0.
>>
>> dean
>>
>>
>> On Wed, Jan 23, 2013 at 6:35 AM, Ehsan Haq <[EMAIL PROTECTED]> wrote:
>>
>>> Thanks, that solved the problem :-)
>>>
>>> /Ehsan
>>>
>>>
>>> On Wed, Jan 23, 2013 at 10:00 AM, wd <[EMAIL PROTECTED]> wrote:
>>>
>>>>
>>>> On Wed, Jan 23, 2013 at 4:46 PM, Ehsan Haq <[EMAIL PROTECTED]>wrote:
>>>>
>>>>> ERROR: invalid escape string
>>>>> Hint: Escape string must be empty or one character..)
>>>>>
>>>>
>>>>
>>>> You can set standard_conforming_strings = off in postgresql.conf to
>>>> avoid this.
>>>>
>>>>
>>>
>>>
>>> --
>>> *Muhammad Ehsan ul Haque*
>>> Klarna AB
>>> Norra Stationsgatan 61
>>> SE-113 43 Stockholm
>>>
>>> Tel: +46 (0)8- 120 120 00
>>> Fax: +46 (0)8- 120 120 99
>>> Web: www.klarna.com
>>>
>>
>>
>>
>> --
>> *Dean Wampler, Ph.D.*
>> thinkbiganalytics.com
>> +1-312-339-1330
>>
>>
>
>
> --
> *Muhammad Ehsan ul Haque*
> Klarna AB
> Norra Stationsgatan 61
> SE-113 43 Stockholm
>
> Tel: +46 (0)8- 120 120 00
> Fax: +46 (0)8- 120 120 99
> Web: www.klarna.com
>

--
*Muhammad Ehsan ul Haque*
Klarna AB
Norra Stationsgatan 61
SE-113 43 Stockholm

Tel: +46 (0)8- 120 120 00
Fax: +46 (0)8- 120 120 99
Web: www.klarna.com