Friday, December 12, 2014

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

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.
Or
2. Disable hive.metastore.try.direct.sql in hive-site.xml of the Hive metastore and restart the service.


1 comment:

  1. Such a very useful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. ขายตรง

    ReplyDelete

Popular Posts