Hive "Show Tables" Fails with MetaException

Question

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.

Inwrap

I have the same problem. Anyone? – Avril

is this production data or just your data or can't loose data? – Ita

This was luckily a proof of concept that had no production data. – Inwrap

Answer 1

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.

Ita

Thank you. I think this is ultimately the correct issue. The solution (as mentioned in the EDIT 2 seemed to be update the Hive / EMR version. – Inwrap

Answer 2

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.

Jeffcott

how do I get rid of NO_BACKSLASH_ESCAPE in EMR (hive-site.xml)? Is there any property for that? – Egidius

Answer 3

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

show tables in schema_name;

Loon

Thanks for your help! Sadly the same result was returned with this command. – Inwrap

Answer 4

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.

Hezekiah

Can you edit to clarify your meaning? The last part, in particular, is confusing to me. – Gilder