Re: Can't drop table

2014-06-12 Thread Keith Wiley

On Jun 11, 2014, at 13:15 , Edward Capriolo wrote:

 There is a lengthy discussion, but it's unclear how to actually drop the 
 darn table.  In fact, that page
 
 Dumb work around:
  
 You could go directly to mysql and delete the table...
 
 Or make a serde with that name just so you will not get the class not found 
 error :)

Okay, thanks.  Still a pretty weird bug all in all, considering that the 
conclusion of that bug report was to not fix the bug.


Keith Wiley kwi...@keithwiley.com keithwiley.commusic.keithwiley.com

Luminous beings are we, not this crude matter.
   --  Yoda




Re: Can't drop table

2014-06-11 Thread Keith Wiley
That's beside the point.  The question is, why can't I drop the table?  There 
is no excuse for not dropping a table just because some serde can't be found.  
It shouldn't operate that way at all.

Thanks.

On Jun 10, 2014, at 23:33 , Nitin Pawar wrote:

 if you have added a table with a serde definition 
 
 then just keep the jar in local filesystem and then in hive console do 
 add jar full path to jar;
 
 this should make your serde available to table and you should be able to drop 
 the table then.
 
 I just tried above steps and it works for my json based tables. 
 
 
 On Wed, Jun 11, 2014 at 2:24 AM, Keith Wiley kwi...@keithwiley.com wrote:
 I tried to create a table that would use a csv serde.  There were various 
 problems with it, primarily in that I couldn't figure out how to specify the 
 path to the serde (whether I indicate the serde location either locally or on 
 HDFS) there were subsequent errors about not finding the serde, even though 
 the paths were correct).
 
 So I decided to drop the table since it wasn't working...but the serde error 
 that is causing these problems to begin with prevents me from dropping the 
 table due to a serde-doesn't-exist error.  Yeah, duh, that's the problem in 
 the first place.
 
 This is an acknowledged problem, as indicated here:
 https://issues.apache.org/jira/browse/HIVE-3392
 ...but I don't understand from that page how to get around the problem.  
 There is a lengthy discussion, but it's unclear how to actually drop the darn 
 table.  In fact, that page suggests the problem is officially classified as 
 won't be fixed.
 
 Note, this problem also prevents me from renaming the table via alter.
 
 I don't know what to do at this point.  Any ideas?
 
 
 Keith Wiley kwi...@keithwiley.com keithwiley.com
 music.keithwiley.com
 
 The easy confidence with which I know another man's religion is folly teaches
 me to suspect that my own is also.
--  Mark Twain
 
 
 
 
 
 -- 
 Nitin Pawar



Keith Wiley kwi...@keithwiley.com keithwiley.commusic.keithwiley.com

It's a fine line between meticulous and obsessive-compulsive and a slippery
rope between obsessive-compulsive and debilitatingly slow.
   --  Keith Wiley




Re: Can't drop table

2014-06-11 Thread Edward Capriolo
There is a lengthy discussion, but it's unclear how to actually drop the
darn table.  In fact, that page

Dumb work around:

You could go directly to mysql and delete the table...

Or make a serde with that name just so you will not get the class not found
error :)


On Wed, Jun 11, 2014 at 9:59 AM, Keith Wiley kwi...@keithwiley.com wrote:

 That's beside the point.  The question is, why can't I drop the table?
  There is no excuse for not dropping a table just because some serde can't
 be found.  It shouldn't operate that way at all.

 Thanks.

 On Jun 10, 2014, at 23:33 , Nitin Pawar wrote:

  if you have added a table with a serde definition
 
  then just keep the jar in local filesystem and then in hive console do
  add jar full path to jar;
 
  this should make your serde available to table and you should be able to
 drop the table then.
 
  I just tried above steps and it works for my json based tables.
 
 
  On Wed, Jun 11, 2014 at 2:24 AM, Keith Wiley kwi...@keithwiley.com
 wrote:
  I tried to create a table that would use a csv serde.  There were
 various problems with it, primarily in that I couldn't figure out how to
 specify the path to the serde (whether I indicate the serde location either
 locally or on HDFS) there were subsequent errors about not finding the
 serde, even though the paths were correct).
 
  So I decided to drop the table since it wasn't working...but the serde
 error that is causing these problems to begin with prevents me from
 dropping the table due to a serde-doesn't-exist error.  Yeah, duh, that's
 the problem in the first place.
 
  This is an acknowledged problem, as indicated here:
  https://issues.apache.org/jira/browse/HIVE-3392
  ...but I don't understand from that page how to get around the problem.
  There is a lengthy discussion, but it's unclear how to actually drop the
 darn table.  In fact, that page suggests the problem is officially
 classified as won't be fixed.
 
  Note, this problem also prevents me from renaming the table via alter.
 
  I don't know what to do at this point.  Any ideas?
 
 
 
  Keith Wiley kwi...@keithwiley.com keithwiley.com
 music.keithwiley.com
 
  The easy confidence with which I know another man's religion is folly
 teaches
  me to suspect that my own is also.
 --  Mark Twain
 
 
 
 
 
 
  --
  Nitin Pawar



 
 Keith Wiley kwi...@keithwiley.com keithwiley.com
 music.keithwiley.com

 It's a fine line between meticulous and obsessive-compulsive and a
 slippery
 rope between obsessive-compulsive and debilitatingly slow.
--  Keith Wiley

 




Re: Can't drop table

2011-04-28 Thread wd
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 w...@wdicc.com

 hi,

 I've setup a single node hadoop and hive. And can create table in hive, but
 can't drop table, hive cli will hang there, nothing more infos.

 hive-0.6.0-bin
 hadoop-0.20.2
 jre1.6.0_23
 postgresql-9.0-801.jdbc4.jar (have tried postgresql-8.4-701.jdbc4.jar)
 pgsql 9.0.2

 How to find what's wrong happed? thx.

CREATE TABLE IDXS (
  INDEX_ID bigint NOT NULL,
  CREATE_TIME int NOT NULL,
  DEFERRED_REBUILD bit(1) NOT NULL,
  INDEX_HANDLER_CLASS varchar(256) DEFAULT NULL,
  INDEX_NAME varchar(128) DEFAULT NULL,
  INDEX_TBL_ID bigint DEFAULT NULL,
  LAST_ACCESS_TIME int NOT NULL,
  ORIG_TBL_ID bigint DEFAULT NULL,
  SD_ID bigint DEFAULT NULL,
  PRIMARY KEY (INDEX_ID),
  CONSTRAINT UNIQUEINDEX UNIQUE (INDEX_NAME,ORIG_TBL_ID),
  CONSTRAINT IDXS_FK1 FOREIGN KEY (SD_ID) REFERENCES SDS (SD_ID),
  CONSTRAINT IDXS_FK2 FOREIGN KEY (INDEX_TBL_ID) REFERENCES TBLS (TBL_ID),
  CONSTRAINT IDXS_FK3 FOREIGN KEY (ORIG_TBL_ID) REFERENCES TBLS (TBL_ID)
) ;

create index IDXS_FK1 on IDXS using btree (SD_ID);
create index IDXS_FK2 on IDXS using btree (INDEX_TBL_ID);
create index IDXS_FK3 on IDXS using btree (ORIG_TBL_ID);


CREATE TABLE INDEX_PARAMS (
  INDEX_ID bigint NOT NULL,
  PARAM_KEY varchar(256) NOT NULL,
  PARAM_VALUE varchar(767) DEFAULT NULL,
  PRIMARY KEY (INDEX_ID,PARAM_KEY),
  CONSTRAINT INDEX_PARAMS_FK1 FOREIGN KEY (INDEX_ID) REFERENCES IDXS (INDEX_ID)
);


CREATE TABLE ROLES (
  ROLE_ID bigint NOT NULL,
  CREATE_TIME int NOT NULL,
  OWNER_NAME varchar(128) DEFAULT NULL,
  ROLE_NAME varchar(128) DEFAULT NULL,
  PRIMARY KEY (ROLE_ID),
  CONSTRAINT ROLEENTITYINDEX UNIQUE (ROLE_NAME)
) ;


CREATE TABLE ROLE_MAP (
  ROLE_GRANT_ID bigint NOT NULL,
  ADD_TIME int NOT NULL,
  GRANT_OPTION smallint NOT NULL,
  GRANTOR varchar(128) DEFAULT NULL,
  GRANTOR_TYPE varchar(128) DEFAULT NULL,
  PRINCIPAL_NAME varchar(128) DEFAULT NULL,
  PRINCIPAL_TYPE varchar(128) DEFAULT NULL,
  ROLE_ID bigint DEFAULT NULL,
  PRIMARY KEY (ROLE_GRANT_ID),
  CONSTRAINT USERROLEMAPINDEX UNIQUE (PRINCIPAL_NAME,ROLE_ID,GRANTOR,GRANTOR_TYPE),
  CONSTRAINT ROLE_MAP_FK1 FOREIGN KEY (ROLE_ID) REFERENCES ROLES (ROLE_ID)
) ;

CREATE TABLE GLOBAL_PRIVS (
  USER_GRANT_ID bigint NOT NULL,
  CREATE_TIME int NOT NULL,
  GRANT_OPTION smallint NOT NULL,
  GRANTOR varchar(128) DEFAULT NULL,
  GRANTOR_TYPE varchar(128) DEFAULT NULL,
  PRINCIPAL_NAME varchar(128) DEFAULT NULL,
  PRINCIPAL_TYPE varchar(128) DEFAULT NULL,
  USER_PRIV varchar(128) DEFAULT NULL,
  PRIMARY KEY (USER_GRANT_ID),
  CONSTRAINT GLOBALPRIVILEGEINDEX UNIQUE (PRINCIPAL_NAME,PRINCIPAL_TYPE,USER_PRIV,GRANTOR,GRANTOR_TYPE)
) ;

CREATE TABLE DB_PRIVS (
  DB_GRANT_ID bigint NOT NULL,
  CREATE_TIME int NOT NULL,
  DB_ID bigint DEFAULT NULL,
  GRANT_OPTION smallint NOT NULL,
  GRANTOR varchar(128) DEFAULT NULL,
  GRANTOR_TYPE varchar(128) DEFAULT NULL,
  PRINCIPAL_NAME varchar(128) DEFAULT NULL,
  PRINCIPAL_TYPE varchar(128) DEFAULT NULL,
  DB_PRIV varchar(128) DEFAULT NULL,
  PRIMARY KEY (DB_GRANT_ID),
  CONSTRAINT DBPRIVILEGEINDEX UNIQUE (DB_ID,PRINCIPAL_NAME,PRINCIPAL_TYPE,DB_PRIV,GRANTOR,GRANTOR_TYPE),
  CONSTRAINT DB_PRIVS_FK1 FOREIGN KEY (DB_ID) REFERENCES DBS (DB_ID)
) ;

CREATE TABLE TBL_PRIVS (
  TBL_GRANT_ID bigint NOT NULL,
  CREATE_TIME int NOT NULL,
  GRANT_OPTION smallint NOT NULL,
  GRANTOR varchar(128) DEFAULT NULL,
  GRANTOR_TYPE varchar(128) DEFAULT NULL,
  PRINCIPAL_NAME varchar(128) DEFAULT NULL,
  PRINCIPAL_TYPE varchar(128) DEFAULT NULL,
  TBL_PRIV varchar(128) DEFAULT NULL,
  TBL_ID bigint DEFAULT NULL,
  PRIMARY KEY (TBL_GRANT_ID),
  CONSTRAINT TBL_PRIVS_FK1 FOREIGN KEY (TBL_ID) REFERENCES TBLS (TBL_ID)
) ;
  
create index TABLEPRIVILEGEINDEX on TBL_PRIVS using btree (TBL_ID,PRINCIPAL_NAME,PRINCIPAL_TYPE,TBL_PRIV,GRANTOR,GRANTOR_TYPE);

CREATE TABLE TBL_COL_PRIVS (
  TBL_COLUMN_GRANT_ID bigint NOT NULL,
  COLUMN_NAME varchar(128) DEFAULT NULL,
  CREATE_TIME int NOT NULL,
  GRANT_OPTION smallint NOT NULL,
  GRANTOR varchar(128) DEFAULT NULL,
  GRANTOR_TYPE varchar(128) DEFAULT NULL,
  PRINCIPAL_NAME varchar(128) DEFAULT NULL,
  PRINCIPAL_TYPE varchar(128) DEFAULT NULL,
  TBL_COL_PRIV varchar(128) DEFAULT NULL,

Re: Can't drop table

2011-01-06 Thread wd
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 c...@cloudera.com

 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

 On Thu, Jan 6, 2011 at 1:29 AM, wd w...@wdicc.com wrote:

 hi,

 I've setup a single node hadoop and hive. And can create table in hive,
 but can't drop table, hive cli will hang there, nothing more infos.

 hive-0.6.0-bin
 hadoop-0.20.2
 jre1.6.0_23
 postgresql-9.0-801.jdbc4.jar (have tried postgresql-8.4-701.jdbc4.jar)
 pgsql 9.0.2

 How to find what's wrong happed? thx.