Hive metastore errors out "Direct SQL failed, falling back to ORM"

Document created by Hao Zhu Employee on Feb 17, 2016
Version 1Show Document
  • View in full screen mode

Author: Hao Zhu

Original Publication Date: December 11, 2014

 

Env:

Hive 0.12

Symptom:

Hive metastore keeps printing error message:

ERROR metastore.ObjectStore (ObjectStore.java:handleDirectSqlError(1742)) - Direct SQL failed, falling back to ORM

If Hive metastore is using PostgreSQL as backend database, below error message may appear in Metastore log:

org.postgresql.util.PSQLException: ERROR: relation "partitions" does not exist

Root Cause:

Hive metastore direct SQL is enabled by hive.metastore.try.direct.sql , and it was added in Hive 0.12 with HIVE-4051. It controls:Whether the Hive metastore should try to use direct SQL queries instead of the DataNucleus for certain read paths. This can improve metastore performance when fetching many partitions or column statistics by orders of magnitude; however, it is not guaranteed to work on all RDBMS-es and all versions. In case of SQL failures, the metastore will fall back to the DataNucleus, so it's safe even if SQL doesn't work for all queries on your datastore. If all SQL queries fail (for example, your metastore is backed by MongoDB), you might want to disable this to save the try-and-fall-back cost.

 

So if you are using PostgreSQL as the back-end database, you will trigger HIVE-5264 .This is because when Hive metastore creates metadata tables in PostgreSQL, the table names are uppercase, for example, "PARTITIONS".However when selecting from the tables, Hive metastore does not use double quotes, for example:

select ... from PARTITIONS

PostgreSQL will treat the table name lower case if it is not double quoted.The correct one should be :

select ... from "PARTITIONS"

That is why PostgreSQL can not find the tables.

Solution:

1. Upgrade Hive to 0.13 or above version.Or2. Disable hive.metastore.try.direct.sql in hive-site.xml of the Hive metastore and restart the service.

Attachments

    Outcomes