CODEWITHSUNDEEP
hadoophive
sweatwang
sweatwang

Reputation: 11

SemanticException Unable to fetch table

Has anyone got a quick fix for dropping such table please?

 hive>> drop table table_1;

Error: Error while compiling statement: FAILED: SemanticException Unable to fetch table table_1. null (state=42000,code=40000)

and None of these requests works: desc\select\

Upvotes: 1

Views: 2099

Answers (1)

QuangPH
QuangPH

Reputation: 136

I faced the same issue. The table was corrupted when I tried to drop and recreate it.

I wasn't able to find the actual root cause, but it was fixed by deleting the table's metadata (in MySQL) as mentioned here.

Run following commands in MySQL:

1. select DB_ID from DBS where NAME='your-database-name'
2. select tbl_id from TBLS where TBL_NAME='your-table-name' and DB_ID=<id from step 1>;
3. delete from PARTITION_KEY_VALS WHERE PART_ID in (select PART_ID from PARTITIONS where TBL_ID=<id from step 2>);
4. delete from PARTITION_PARAMS WHERE PART_ID in (select PART_ID from PARTITIONS where TBL_ID=<id from step 2>); 
5. delete from PARTITIONS where TBL_ID=<id from step 2>; 
6. delete from TBL_COL_PRIVS where TBL_ID=<id from step 2>;
7. delete from TBL_PRIVS where TBL_ID=<id from step 2>; 
8. delete from TBLS where TBL_ID=<id from step 2>; 
9. COMMIT;

other references:

Upvotes: 1

Related Questions