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.

Impala table with column names as reserved words

Highlighted

Impala table with column names as reserved words

Explorer

Hello Friends,

It seems Impala does not support column names being a reserved word, is it correct? we have table with column name DATE if we select date or select distinct date query it says syntax error.Encountered: DATE
Expected: ALL, CASE, CAST, DISTINCT, FALSE, IF, INTERVAL, NOT, NULL, STRAIGHT_JOIN, TRUE, IDENTIFIER. Hive does not show up any problem. Is there any plan to fix this issue in Impala or it is built like that intentionally if yes why is it so? Is not it a problem if people created a table in Hive and then retrieving data from Impala. I am cautious that having column names same as reserved word is not good practice but this is not completly avoidable as well

 

Regards

Sree

4 REPLIES 4

Re: Impala table with column names as reserved words

Cloudera Employee
Hi Sree,
Impala allows keywords to be used as identifiers (table/db/column/etc
names), but you must escape them using backticks. For example, notice
keywords are being used below:

SELECT `date` FROM `database`.`table`;

Hope that helps.

Thanks,
Lenni

Re: Impala table with column names as reserved words

Explorer

That's wonderful. Thanks a lot Lenny.

Re: Impala table with column names as reserved words

New Contributor

Hi,

 

it does not work and gives me following error

 

SELECT DISTINCT `date` FROM mydb.syscal;
Query: SELECT DISTINCT `date` FROM mydb.syscal
Query submitted at: 2018-12-16 11:54:55 (Coordinator: https://host:port)
ERROR: AnalysisException: Unsupported type 'DATE' in '`date`'.

 

Any other options? 

 

Thanks & Regards,

Kamleshkumar Gujarathi

Bigdata Consultant

 

Re: Impala table with column names as reserved words

Master Collaborator

Hi @Big

 

I checked on our latest build and it works for me - see below. Are you sure that you're not trying to query a table with a DATE type column?

 

[localhost:21000] default> create table foo2 (`date` int);
Query: create table foo2 (`date` int)
+-------------------------+
| summary                 |
+-------------------------+
| Table has been created. |
+-------------------------+
Fetched 1 row(s) in 1.19s
[localhost:21000] default> select distinct `date` from foo2;
Query: select distinct `date` from foo2
Fetched 0 row(s) in 0.12s
Don't have an account?
Coming from Hortonworks? Activate your account here