Reply
Explorer
Posts: 44
Registered: ‎07-24-2014

Impala table with column names as reserved words

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

Cloudera Employee
Posts: 14
Registered: ‎09-09-2013

Re: Impala table with column names as reserved words

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

Explorer
Posts: 44
Registered: ‎07-24-2014

Re: Impala table with column names as reserved words

That's wonderful. Thanks a lot Lenny.

Big
New Contributor
Posts: 1
Registered: ‎12-16-2018

Re: Impala table with column names as reserved words

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

 

Cloudera Employee
Posts: 416
Registered: ‎07-29-2015

Re: Impala table with column names as reserved words

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