CODEWITHSUNDEEP
hiveamazon-emr
Taipan
Taipan

Reputation: 116

Hive "Show Tables" Fails with MetaException

Using Hive 2.3.7 on AWS EMR (5.33.1) I have created a database which shows correctly when calling show databases;. I then create a table which seems to work correctly (no exceptions). When I call describe <table>; It correctly returns the name and schema of the table. However when I run show tables; the following error is returned:

FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask.MetaException(message:Got exception: org.apache.hadoop.hive.metastore.api.MetaException 
Exception thrown when executing query : 
SELECT A0.TBL_NAME,A0.TBL_NAME AS NUCORDER0 FROM TBLS A0 LEFT OUTER JOIN DBS B0 ON 
A0.DB_ID = B0.DB_ID WHERE B0.`NAME` = ? AND LOWER(A0.TBL_NAME) LIKE '_%' ESCAPE '\' ORDER BY NUCORDER0)

If anyone can shed any light on this issue it would be really appreciated.

I have googled around and found nothing of any use.

EDIT: show tables in <schema>; returned the same result

EDIT 2: This issue was solved by updating the EMR to emr-6.4.0. I have no great insight into the issue beyond what is mentioned here.

Upvotes: 6

Views: 1131

Answers (4)

SivaSingh
SivaSingh

Reputation: 11

jdbc:{{mariadburi}}?sessionVariables=sql_mode='ANSI,NO_ENGINE_SUBSTITUTION,NO_BACKSLASH_ESCAPES'

In your hive-site.xml, for this key javax.jdo.option.ConnectionURL, set the above values.

We have various sqlmodes available. Need to set this sqlmodes,

sql_mode='ANSI,NO_ENGINE_SUBSTITUTION,NO_BACKSLASH_ESCAPES'

To consider the / as escape character from the query.

Upvotes: 0

Jason Jun
Jason Jun

Reputation: 11

Hive meta store is using datanucleus, https://www.datanucleus.org/, for all CRUD of metastore database. It's generating \\ to escape backslash itself, but Mariadb interprete \\ as string literal. So it needs to use \\ as escape character.

You can see sql_mode setting here, https://mariadb.com/kb/en/sql-mode/#sql_mode-values.

Get rid of NO_BACKSLASH_ESCAPE from the mode and it should be all right.

Upvotes: 1

Matt Andruff
Matt Andruff

Reputation: 5125

I think your metadata database has been corrupted/has bad data. I would take a backup. And then see if you can restore some previous backups. I would connect to the database directly and look at the those tables and see if anything looks out of the ordinary. If you find a bad table entry don't delete it. I'd try using "Delete table" commands (via hive) to remove it to keep integrity. If you have to you can delete entries in your database, you have a backup and could restore back the tables.

Upvotes: 1

phzz
phzz

Reputation: 161

Try providing the schema which you want to see the tables:

show tables in schema_name;

Upvotes: 0

Related Questions