SemanticException "in insert schema specification is not found among regular columns" is Received when Loading Data into Hive Using ODI 12c Knowledge Module "LKM SQL to SQL"

(Doc ID 2319936.1)

Last updated on NOVEMBER 09, 2017

Applies to:

Oracle Data Integrator - Version 12.2.1.2.6 and later
Information in this document applies to any platform.

Symptoms

The following error is received when using Oracle Data Integrator (ODI) and loading data into Apache Hive using a regular SQL Loading Knowledge Module (LKM) such as "LKM SQL to SQL" (built-in):

Caused By: weblogic.jdbc.hivebase.ddc: [FMWGEN][Hive JDBC Driver][Hive]Error while compiling statement: FAILED:
SemanticException 2:6 'MY_UPPER_CASE_COL' in insert schema specification is not found among regular columns of my_hive_schema.C_0MY_HIVE_STAGING_TABLE nor dynamic partition columns..
Error encountered near token 'MY_UPPER_CASE_COL'
  at weblogic.jdbc.hivebase.ddcp.b(Unknown Source)
  at weblogic.jdbc.hivebase.ddcp.a(Unknown Source)
  ...

  == or ==

Caused By: org.apache.hive.service.cli.HiveSQLException: Error while compiling statement: FAILED:
SemanticException 2:6 'MY_UPPER_CASE_COL' in insert schema specification is not found among regular columns of my_hive_schema.C_0MY_HIVE_STAGING_TABLE nor dynamic partition columns..
Error encountered near token 'MY_UPPER_CASE_COL'
  at org.apache.hive.jdbc.Utils.verifySuccess(Utils.java:262)
  at org.apache.hive.jdbc.Utils.verifySuccessWithInfo(Utils.java:248)
  at org.apache.hive.jdbc.HiveStatement.runAsyncOnServer(HiveStatement.java:297)
  at org.apache.hive.jdbc.HiveStatement.execute(HiveStatement.java:238)
  at org.apache.hive.jdbc.HivePreparedStatement.execute(HivePreparedStatement.java:98)
  at oracle.odi.runtime.agent.execution.sql.SQLCommand.execute(SQLCommand.java:203)
  at ...

Note the source Oracle table name and columns are all upper case.

However, Hive enforces lower case table and column names.

So even if the C$ staging table is created to use upper case letters for its name and columns, Hive will automatically change these to all lower case.

Steps to reproduce the behavior:

  1. Have an Oracle table with name and columns in upper case.
  2. Have a Mapping that loads data from above Oracle table into Hive, using the built-in "LKM SQL to SQL".
  3. Run the Mapping and observe the error message.

Changes

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms