Uploaded image for project: 'Hive'
  1. Hive
  2. HIVE-5264

SQL generated by MetaStoreDirectSql.java not compliant with Postgres.

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 0.12.0
    • Fix Version/s: 0.13.0
    • Component/s: Metastore
    • Labels:
      None
    • Environment:

      Ubuntu 12.04
      PostgreSQL 9.1.8

      Description

      Some operations against the Hive Metastore seem broken
      against Postgres.

      For example, when using HiveMetastoreClient.listPartitions()
      the Postgres logs show queries such as:

      2013-09-09 19:10:01 PDT STATEMENT: select PARTITIONS.PART_ID from
      PARTITIONS inner join TBLS on PARTITIONS.TBL_ID = TBLS.TBL_ID inner
      join DBS on TBLS.DB_ID = DBS.DB_ID where TBLS.TBL_NAME = $1 and
      DBS.NAME = $2 order by PART_NAME asc

      with a somewhat cryptic (but correct) error:

      ERROR: relation "partitions" does not exist at character 32

      Postgres identifiers are somewhat unusual. Unquoted identifiers are interpreted as lower case (there is no Postgres option to change this). Since the Metastore table schema uses upper case table names, the correct SQL requires escaped identifiers to those tables, i.e.,
      select "PARTITIONS"."PART_ID" from "PARTITIONS"...

      Hive sets metastore.try.direct.sql=true by default, so the above SQL is generated by hive/metastore/MetaStoreDirectSql.java, i.e., this is not a Datanucleus problem.

      When I set metastore.try.direct.sql=false, then the Metastore backed by Postgres works.

        Attachments

        1. HIVE-5264.patch
          23 kB
          Sergey Shelukhin
        2. HIVE-5264.03.patch
          23 kB
          Sergey Shelukhin
        3. HIVE-5264.02.patch
          23 kB
          Sergey Shelukhin
        4. HIVE-5264.01.patch
          23 kB
          Sergey Shelukhin
        5. D12993.2.patch
          21 kB
          Phabricator
        6. D12993.1.patch
          22 kB
          Phabricator

          Activity

            People

            • Assignee:
              sershe Sergey Shelukhin
              Reporter:
              alex.behm Alexander Behm
            • Votes:
              0 Vote for this issue
              Watchers:
              8 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: