Home | About | Sematext search-lucene.com search-hadoop.com
 Search Hadoop and all its subprojects:

Switch to Threaded View
Hive, mail # user - Using PostgreSQL as Hive metastore

Copy link to this message
Re: Using PostgreSQL as Hive metastore
Jov 2011-05-17, 06:02
this is caused by HIVE auto create some meta tables when found them has not

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

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.



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 <[EMAIL PROTECTED]>

> 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