[Hive-user] Can't drop table

The best first step is enable logging to the console and then
try the operation again through the CLI:

hive -hiveconf hive.root.logger=INFO,console

or if you want even more logging info try

hive -hiveconf hive.root.logger=DEBUG,console

Thanks.

Carl

11/01/06 18:20:14 INFO metastore.HiveMetaStore: 0: get_table : db=default
tbl=t1
11/01/06 18:20:14 INFO metastore.HiveMetaStore: 0: drop_table : db=default
tbl=t1
11/01/06 18:20:14 INFO metastore.HiveMetaStore: 0: get_table : db=default
tbl=t1
11/01/06 18:20:14 DEBUG metastore.ObjectStore: Executing listMPartitions

Will hung at here.

I've tried derby metadata, it worked.


2011/1/6 Carl Steinbach <carl@cloudera.com>

I've tried in postgresql-8.1.22-1.el5_5.1, and tried hive-0.5-bin，the
problem still there...
Also tried postgresql-8.4-702.jdbc4.jar, anyone else have this problem ?

2011/1/6 wd <wd@wdicc.com>

Oh, WTF
It worked now, But I've done nothing!

在 2011年1月17日 下午3:14，wd <wd@wdicc.com>写道：

Finally I found, when use hive-0.5.0-bin, 'drop table' will hung at first
time, after Ctrl-c kill the client, and run hive again, it can successfully
drop the table. When use hive-0.6.0-bin, it will always hung there.

2011/1/6 wd <wd@wdicc.com>

You are the first person I have heard of using postgres. I commend you
for not succumbing to the social pressure and just installing mysql.
However I would advice succumbing to the social pressure and using
either derby or mysql.

The reason I say this is because jpox "has support" for a number of
data stores (M$ SQL server) however, people have run into issues with
them. Databases other then derby and mysql 'should work' but are
generally untested.

Edward

Hi all,

We use postgres as a metastore for Hive and haven't come across any
problems. The postgres driver jar is: postgresql-8.4.701-jdbc.jar. We use
the version of hive that comes out of Cloudera's CDH3b2, which I believe is
some variant of Hive 0.5.0.

Java: HotSpot 1.6.0_20
OS: Ubuntu Lucid (10.04)
Postgres: 8.4.4
Postgres JDBC driver: postgresql-8.4.701-jdbc.jar
Hadoop: 0.20.2+320 (Cloudera CDH3b2)
Hive: 0.5.0+20 (Cloudera CDH3b2)

Thanks,

Jamie

Hi All,

I'm using PostgreSQL for Hive metastore in production. As far as I know
MySQL has limitations on constraints in unicode environments. I remember
Hive could not create metastore schema automatically with MySQL, Innodb and
UTF-8 encoding environment. so I switched PostgreSQL for metastore.

Actually, it works with PostgreSQL. but first time when one run hive cli
with PostgreSQL, there are problems on creating metastore schema because JDO
does not work well with PostgreSQL. I created the metastore schema manually.
after that, everything works fine.

Regards,

- Youngwoo

2011/1/17 Edward Capriolo <edlinuxguru@gmail.com>

We have a similar problem with not being able to drop tables, using Hive 0.6 and Hadoop 20.0 along with Postgres. Can you share with us the schema you created? We are wondering if the schema that was automatically created in our case is somehow incomplete.

Thanks,
Tali



Date: Mon, 17 Jan 2011 22:46:44 +0900
Subject: Re: Can't drop table
From: warwithin@gmail.com
To: user@hive.apache.org
CC: hive-user@hadoop.apache.org

Hi All,


I'm using PostgreSQL for Hive metastore in production. As far as I know MySQL has limitations on constraints in unicode environments. I remember Hive could not create metastore schema automatically with MySQL, Innodb and UTF-8 encoding environment. so I switched PostgreSQL for metastore.


Actually, it works with PostgreSQL. but first time when one run hive cli with PostgreSQL, there are problems on creating metastore schema because JDO does not work well with PostgreSQL. I created the metastore schema manually. after that, everything works fine.


Regards,


- Youngwoo


2011/1/17 Edward Capriolo <edlinuxguru@gmail.com>

You are the first person I have heard of using postgres. I commend you
for not succumbing to the social pressure and just installing mysql.
However I would advice succumbing to the social pressure and using
either derby or mysql.

The reason I say this is because jpox "has support" for a number of
data stores (M$ SQL server) however, people have run into issues with
them. Databases other then derby and mysql 'should work' but are
generally untested.

Edward

Seems I found the reason.

I'm try to upgrade my hive-0.5.0 to hive-0.7 today, and executed the sql in
upgrade directory, migrate conf to new hive. Then found that can't drop
table again.

When drop a table, I found there is a connection from hive try to do
something on "IDXS" table in postgress, and hung at this sql.

16385 | hive_metadb | 8259 | 16386 | hive | ALTER TABLE "IDXS" ADD
CONSTRAINT "IDXS_FK1" FOREIGN KEY ("SD_ID") REFERENCES "SDS" (
"SD_ID") INITIALLY DEFERRED | t | 2011-04-28 13:54:16.384769+08 |
2011-04-28 13:54:16.404204+08 | 2011-04-28 13:54:01.784839+08 | 12
7.0.0.1 | 37438

But there is no table named "IDXS" exists. So I think this problem is cased
by these missing tables. Then I migrate a upgrade sql file from mysql, and
upgrade my postgress, the problem solved.

The attachment is the upgrade SQLs to run.

2011/1/6 wd <wd@wdicc.com>