Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Hive Insert statement with or without select case sensitive

Highlighted

Hive Insert statement with or without select case sensitive

Rising Star

I create a table and tried to insert the data but got the below exception due to case sensitivity.

INSERT INTO TABLE X(A,B,C) select A,B,C FROM Y;

FAILED: SemanticException 1:27 '[A, B, C]' in insert schema specification are not found among regular columns of default.X nor dynamic partition columns.. Error encountered near token 'C'

Whereas the below successful:

INSERT INTO TABLE X(a,b,c) select A,B,C FROM Y;

After some investigation, I found Hive metastore store table info in lowercase and is case-sensitive.

Is there any way I can make sure JDBC drive can handle this scenario? We are using a tool to push data from RDBMS system to Hive and it is generating CREATE & INSERT statements in uppercase. I am working with the tool SME's as well if they can enfore a lowercase schema during code generation.

2 REPLIES 2

Re: Hive Insert statement with or without select case sensitive

Expert Contributor

@Shashant Panwar, i had similar issue and tried to fix it in Hive Code.

If source & destination tables have identical columns in same order, it not required to mention source & destination columns, else better to mention destination columns in lowercase in insert statement

Re: Hive Insert statement with or without select case sensitive

New Contributor

I'm confirm that. Destination table column names must be writted in lowercase , otherwise insert statement does'nt work