You are viewing a plain text version of this content. The canonical link for it is here.

Posted to user@hive.apache.org by Mike <na...@yahoo.com> on 2011/05/16 17:54:21 UTC

Using PostgreSQL as Hive metastore

Hi all,

   I am trying to use PostgreSQL as the local metastore in Hive. I have the  postgresql-8.4-701.jdbc4.jar in the path. I use hive-0.7 in my system.

 I have the following properties added to hive-site.xml.

  
 <property>

   <name>hive.metastore.local</name>

   <value>true</value>

 </property>

 <property>

   <name>javax.jdo.option.ConnectionURL</name>

   <value>jdbc:postgresql://localhost:5432/hivedb</value>

 </property>

  <property>

     <name>javax.jdo.option.ConnectionDriverName</name>

     <value>org.postgresql.Driver</value>

   </property>

   <property>

     <name>javax.jdo.option.ConnectionUserName</name>

     <value>******</value>

   </property>

   <property>

     <name>javax.jdo.option.ConnectionPassword</name>

     <value>******</value>

   </property>

 I created a table using Hive Cli, and it went good. But when I try to

 DROP the table the hive cli hangs and is not responding. 

I found a similar thread here : http://search-hadoop.com/m/VYmqW1ejsiA&subj=Re+Can+t+drop+table , but I didnot quite get the solution. Can anyone help me on this? 

 Thanks,

 Mike

Re: Using PostgreSQL as Hive metastore

Posted by 김영우 <wa...@gmail.com>.

Hi Mike,

It seems like there are some problems on creating metastore schema. So,
create a schema for hive metastore first and then try it again.
There is a DDL script for Pg,
http://svn.apache.org/repos/asf/hive/trunk/metastore/scripts/upgrade/postgres/hive-schema-0.7.0.postgres.sql


- Youngwoo


2011/5/17 Mike <na...@yahoo.com>

> Hi all,
>
>   I am trying to use PostgreSQL as the local metastore in Hive. I have the
> postgresql-8.4-701.jdbc4.jar in the path. I use hive-0.7 in my system.
>
>
> I have the following properties added to hive-site.xml.
>
>
> <property>
>   <name>hive.metastore.local</name>
>   <value>true</value>
> </property>
> <property>
>   <name>javax.jdo.option.ConnectionURL</name>
>   <value>jdbc:postgresql://localhost:5432/hivedb</value>
>  </property>
>  <property>
>     <name>javax.jdo.option.ConnectionDriverName</name>
>     <value>org.postgresql.Driver</value>
>   </property>
>   <property>
>     <name>javax.jdo.option.ConnectionUserName</name>
>     <value>******</value>
>   </property>
>   <property>
>     <name>javax.jdo.option.ConnectionPassword</name>
>     <value>******</value>
>   </property>
>
> I created a table using Hive Cli, and it went good. But when I try to
> DROP the table the hive cli hangs and is not responding.
>
>
> I found a similar thread here :
> http://search-hadoop.com/m/VYmqW1ejsiA&subj=Re+Can+t+drop+table , but I
> didnot quite get the solution.
>
> Can anyone help me on this?
>
>
> Thanks,
> Mike

Re: Using PostgreSQL as Hive metastore

Posted by Jov <zh...@gmail.com>.

this is caused by HIVE auto create some meta tables when found them has not
created.

when you create table without partition or index,it will not touch the
partition or index meta table,all goes well.but when you drop the table ,it
will query the partition or index meta table,but find it has not created,so
hive will create them,this ddl need some locks.this cause pgsql dead lock.

there' three methods to solve it:
1. connect to pg as super user, select * from pg_stat_activity,find the
process id of backend which status is "<IDLE> in transaction",so the
partition or index meta table will create sucess.then re run the drop talbe
stmt.

2.run some non-ddl stmt in hive:
for example,after create table t,run:

show partitions t;
show index on t;

these stmt will create the meta table but not cause dead lock.

3.see:

http://www.datanucleus.org/products/accessplatform_2_0/rdbms/schematool.html

and use hive jdo file in src source_root/metastore/src/model/package.jdo as
input file,to init the metastore db in pg.


good luck!

2011/5/16 Mike <na...@yahoo.com>

> Hi all,
>
>   I am trying to use PostgreSQL as the local metastore in Hive. I have the
> postgresql-8.4-701.jdbc4.jar in the path. I use hive-0.7 in my system.
>
>
> I have the following properties added to hive-site.xml.
>
>
> <property>
>   <name>hive.metastore.local</name>
>   <value>true</value>
> </property>
> <property>
>   <name>javax.jdo.option.ConnectionURL</name>
>   <value>jdbc:postgresql://localhost:5432/hivedb</value>
>  </property>
>  <property>
>     <name>javax.jdo.option.ConnectionDriverName</name>
>     <value>org.postgresql.Driver</value>
>   </property>
>   <property>
>     <name>javax.jdo.option.ConnectionUserName</name>
>     <value>******</value>
>   </property>
>   <property>
>     <name>javax.jdo.option.ConnectionPassword</name>
>     <value>******</value>
>   </property>
>
> I created a table using Hive Cli, and it went good. But when I try to
> DROP the table the hive cli hangs and is not responding.
>
>
> I found a similar thread here :
> http://search-hadoop.com/m/VYmqW1ejsiA&subj=Re+Can+t+drop+table , but I
> didnot quite get the solution.
>
> Can anyone help me on this?
>
>
> Thanks,
> Mike