You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Ehsan Haq <eh...@klarna.com> on 2013/01/23 09:46:52 UTC
Problem with using Postgres as hive meta store DB.
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.HiveMetaStore$HMSHandler.<init>(HiveMetaStore.java:248)
at
org.apache.hadoop.hive.metastore.HiveMetaStoreClient.<init>(HiveMetaStoreClient.java:114)
at
org.apache.hadoop.hive.ql.metadata.Hive.createMetaStoreClient(Hive.java:2092)
at org.apache.hadoop.hive.ql.metadata.Hive.getMSC(Hive.java:2102)
at org.apache.hadoop.hive.ql.metadata.Hive.getDatabase(Hive.java:1076)
... 18 more
Caused by: java.lang.reflect.InvocationTargetException
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at
sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
at
sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
at
org.datanucleus.plugin.NonManagedPluginRegistry.createExecutableExtension(NonManagedPluginRegistry.java:588)
at
org.datanucleus.plugin.PluginManager.createExecutableExtension(PluginManager.java:324)
at
org.datanucleus.store.AbstractStoreManager.registerConnectionFactory(AbstractStoreManager.java:215)
at
org.datanucleus.store.AbstractStoreManager.<init>(AbstractStoreManager.java:190)
at
org.datanucleus.store.mapped.MappedStoreManager.<init>(MappedStoreManager.java:137)
at
org.datanucleus.store.rdbms.RDBMSStoreManager.<init>(RDBMSStoreManager.java:253)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at
sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
at
sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
at
org.datanucleus.plugin.NonManagedPluginRegistry.createExecutableExtension(NonManagedPluginRegistry.java:588)
at
org.datanucleus.plugin.PluginManager.createExecutableExtension(PluginManager.java:300)
at
org.datanucleus.ObjectManagerFactoryImpl.initialiseStoreManager(ObjectManagerFactoryImpl.java:161)
at
org.datanucleus.jdo.JDOPersistenceManagerFactory.freezeConfiguration(JDOPersistenceManagerFactory.java:583)
... 47 more
Caused by: org.datanucleus.exceptions.NucleusException: Attempt to invoke
the "DBCP" plugin to create a ConnectionPool gave an error : The specified
datastore driver ("org.postgresql.Driver") was not found in the CLASSPATH.
Please check your CLASSPATH specification, and the name of the driver.
at
org.datanucleus.store.rdbms.ConnectionFactoryImpl.initDataSourceTx(ConnectionFactoryImpl.java:165)
at
org.datanucleus.store.rdbms.ConnectionFactoryImpl.<init>(ConnectionFactoryImpl.java:84)
... 65 more
Caused by:
org.datanucleus.store.rdbms.datasource.DatastoreDriverNotFoundException:
The specified datastore driver ("org.postgresql.Driver") was not found in
the CLASSPATH. Please check your CLASSPATH specification, and the name of
the driver.
at
org.datanucleus.store.rdbms.datasource.dbcp.DBCPDataSourceFactory.makePooledDataSource(DBCPDataSourceFactory.java:80)
at
org.datanucleus.store.rdbms.ConnectionFactoryImpl.initDataSourceTx(ConnectionFactoryImpl.java:144)
... 66 more
I will appreciate any response about that.
Thanks
/Ehsan
Re: Problem with using Postgres as hive meta store DB.
Posted by Ehsan Haq <eh...@klarna.com>.
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 <eh...@klarna.com> 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 <
> dean.wampler@thinkbiganalytics.com> 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 <eh...@klarna.com> wrote:
>>
>>> Thanks, that solved the problem :-)
>>>
>>> /Ehsan
>>>
>>>
>>> On Wed, Jan 23, 2013 at 10:00 AM, wd <wd...@wdicc.com> wrote:
>>>
>>>>
>>>> On Wed, Jan 23, 2013 at 4:46 PM, Ehsan Haq <eh...@klarna.com>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
Re: Problem with using Postgres as hive meta store DB.
Posted by Ehsan Haq <eh...@klarna.com>.
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 <
dean.wampler@thinkbiganalytics.com> 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 <eh...@klarna.com> wrote:
>
>> Thanks, that solved the problem :-)
>>
>> /Ehsan
>>
>>
>> On Wed, Jan 23, 2013 at 10:00 AM, wd <wd...@wdicc.com> wrote:
>>
>>>
>>> On Wed, Jan 23, 2013 at 4:46 PM, Ehsan Haq <eh...@klarna.com> 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.
Posted by Dean Wampler <de...@thinkbiganalytics.com>.
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 <eh...@klarna.com> wrote:
> Thanks, that solved the problem :-)
>
> /Ehsan
>
>
> On Wed, Jan 23, 2013 at 10:00 AM, wd <wd...@wdicc.com> wrote:
>
>>
>> On Wed, Jan 23, 2013 at 4:46 PM, Ehsan Haq <eh...@klarna.com> 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
Re: Problem with using Postgres as hive meta store DB.
Posted by Ehsan Haq <eh...@klarna.com>.
Thanks, that solved the problem :-)
/Ehsan
On Wed, Jan 23, 2013 at 10:00 AM, wd <wd...@wdicc.com> wrote:
>
> On Wed, Jan 23, 2013 at 4:46 PM, Ehsan Haq <eh...@klarna.com> 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
Re: Problem with using Postgres as hive meta store DB.
Posted by wd <wd...@wdicc.com>.
On Wed, Jan 23, 2013 at 4:46 PM, Ehsan Haq <eh...@klarna.com> 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.