[HIVE-20872] Creating information_schema and sys schema via schematool fails with parser error

• Type: Bug
• Status: Resolved
• Priority: Critical
• Resolution: Duplicate
• Affects Version/s: 3.1.0, 3.1.1
• Component/s: Hive, HiveServer2, Metastore, SQL

Environment

Apache Hive (version 3.1.1)
Hive JDBC (version 3.1.1)

metastore on derby embedded, derby server, postgres server

Apache Hadoop (version 2.9.1)

• Assignee: Unassigned
• Reporter: Carsten Steckel
• Created: 06/Nov/18 15:55
• Updated: 12/Dec/18 13:44
• Resolved: 12/Dec/18 13:44

Description

it took quite some time to figure out how to install the "information_schema" and "sys" schemas (thanks to https://issues.apache.org/jira/browse/HIVE-16941) into a hive 3.1.0/3.1.1 on hdfs/hadoop 2.9.1 and I am still unsure if it is the proper way of doing it.

when I execute:

 

hive@hive-server ~> schematool -metaDbType derby -dbType hive -initSchema -url jdbc:hive2://localhost:10000/default -driver org.apache.hive.jdbc.HiveDriver"

 I receive an error (from --verbose log):

 

[...]
Error: Error while compiling statement: FAILED: SemanticException org.apache.hadoop.hive.ql.metadata.InvalidTableException: Table not found _dummy_table (state=42000,code=40000)
org.apache.hadoop.hive.metastore.HiveMetaException: Schema initialization FAILED! Metastore state would be inconsistent !!
[...]

  

It seems to be the last statement during setup of the sys-schema causes the issue. When executing it manually:

 

 

0: jdbc:hive2://localhost:10000> CREATE OR REPLACE VIEW `VERSION` AS SELECT 1 AS `VER_ID`, '3.1.0' AS `SCHEMA_VERSION`, 'Hive release version 3.1.0' AS `VERSION_COMMENT`;
Error: Error while compiling statement: FAILED: SemanticException org.apache.hadoop.hive.ql.metadata.InvalidTableException: Table not found _dummy_table (state=42000,code=40000)

 

I have tried to switch the metastore_db from derby embedded to derby server to postgresql and made sure the changed metadatabases each worked, but setting up the information_schema and sys schemas always delivers the same error.

Executing only the select part without the create view works:

 

0: jdbc:hive2://localhost:10000> SELECT 1 AS `VER_ID`, '3.1.0' AS `SCHEMA_VERSION`, 'Hive release version 3.1.0' AS `VERSION_COMMENT`;
+---------+-----------------+-----------------------------+
| ver_id  | schema_version  |       version_comment       |
+---------+-----------------+-----------------------------+
| 1       | 3.1.0           | Hive release version 3.1.0  |
+---------+-----------------+-----------------------------+
1 row selected (0.595 seconds)

It seems to be related to: HIVE-19444

 

Issue Links

• duplicates: HIVE-20010 Fix create view over literals