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

Quote identifiers in SQL emitted by SchemaTool for MySQL

    XMLWordPrintableJSON

Details

    Description

      Various SchemaTool options/tasks (e.g., "validate") generate and run SQL statements on the underlying database. Depending on the database identifiers in the SQL statements may be quoted (see https://github.com/apache/hive/blob/2dbfbeefc1a73d6a50f1c829658846fc827fc780/standalone-metastore/metastore-server/src/main/java/org/apache/hadoop/hive/metastore/tools/schematool/HiveSchemaHelper.java#L173).

      Currently, all identifiers are quoted when the database is Postgres and this tickets aims to do the same for MySQL/MariaDB.

      The main motivation behind this change is to avoid unexpected surprises and query failures when/if the database decides to turn some of the tables/columns we are using internally to reserved keywords.

      As a concrete example, the Percona fork of MySQL recently turned SEQUENCE_TABLE into a reserved keyword (https://docs.percona.com/percona-server/8.0/flexibility/sequence_table.html) and this comes in conflict with our internal metastore table.

      The installation scripts do not fail since in that case SEQUENCE_TABLE is quoted (https://github.com/apache/hive/blob/2dbfbeefc1a73d6a50f1c829658846fc827fc780/standalone-metastore/metastore-server/src/main/sql/mysql/hive-schema-4.0.0-beta-2.mysql.sql#L447) but validation queries emitted by the SchemaTool will fail (https://github.com/apache/hive/blob/2dbfbeefc1a73d6a50f1c829658846fc827fc780/standalone-metastore/metastore-server/src/main/java/org/apache/hadoop/hive/metastore/tools/schematool/SchemaToolTaskValidate.java#L117) if we don't use quoted identifiers.

      Attachments

        1. TestSchemaToolForMetastore-validateSequences-after.txt
          6 kB
          Stamatis Zampetakis
        2. TestMysql-upgrade-after.txt
          5 kB
          Stamatis Zampetakis
        3. TestMysql-upgrade-before.txt
          5 kB
          Stamatis Zampetakis
        4. TestSchemaToolForMetastore-validateSequences-before.txt
          5 kB
          Stamatis Zampetakis
        5. TestSchemaToolForMetastore-validateTables-after.txt
          5 kB
          Stamatis Zampetakis
        6. TestSchemaToolForMetastore-validateTables-before.txt
          5 kB
          Stamatis Zampetakis

        Issue Links

          Activity

            People

              zabetak Stamatis Zampetakis
              zabetak Stamatis Zampetakis
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: