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

SQL generated by MetaStoreDirectSql.java not compliant with Postgres.

Log workAgile BoardRank to TopRank to BottomBulk Copy AttachmentsBulk Move AttachmentsVotersWatch issueWatchersCreate sub-taskConvert to sub-taskMoveLinkCloneLabelsUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

Details

    • Bug
    • Status: Resolved
    • Major
    • Resolution: Fixed
    • 0.12.0
    • 0.13.0
    • Metastore
    • None
    • 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. D12993.1.patch
          22 kB
          Phabricator
        2. D12993.2.patch
          21 kB
          Phabricator
        3. HIVE-5264.01.patch
          23 kB
          Sergey Shelukhin
        4. HIVE-5264.02.patch
          23 kB
          Sergey Shelukhin
        5. HIVE-5264.03.patch
          23 kB
          Sergey Shelukhin
        6. HIVE-5264.patch
          23 kB
          Sergey Shelukhin

        Activity

          This comment will be Viewable by All Users Viewable by All Users
          Cancel

          People

            sershe Sergey Shelukhin Assign to me
            alex.behm Alexander Behm
            Votes:
            0 Vote for this issue
            Watchers:
            8 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Slack

                Issue deployment