How do I escape reserved words used as column names on HIVE?

Question

When I am executing following query in HIVE, it is giving me current date instead of the column values from USER_INFO table.

SELECT CURRENT_DATE 
FROM   USER_INFO
LIMIT 1; 

How do I escape reserved words used as column names on HIVE?

Thanks & Regards, Kamlesh

Annabelle

Answer 1

Got it.

There are two ways

1 You can use apostrophe before and after the keyword as shown below.

SELECT CURRENT_DATE 
FROM   `USER_INFO`
LIMIT 1;

2 There is setting shown below which you can do if you are executing it with unix shell (i.e. with sh command)

hive.support.sql11.reserved.keywords=false

Hope this helps.

Thanks & Regards, Kamleshkumar Gujarathi

Annabelle

Down-voted until fixed. (1) The wrong element is quoted (2.1) This has nothing to do with shell. (2.2) This works only for some keywords, not all of them – Callboard

The escape character shown here is "backtick" or "accent grave" (code point 0060) not "apostrophe" – Nicholson

Answer 2

In order to query reserved words in columns (for example "users" or "current_date")

SELECT `CURRENT_DATE` 
FROM   USER_INFO
LIMIT 1; 

Notice the aposthrope type ` (As Wheezil points out this character is 'backtick' , but I want to point out that in international keyboards this character is relatively hard to type )

Self answer is ok but misleading due to wrong query, adding answer here for future reference.

Drucilladrucy

The escape character shown here is "backtick" or "accent grave" (code point 0060) not "apostrophe" – Nicholson

Answer 3

Facing the same issue with the keyword "application". Even backtick is not working.

select application from abc.def where transaction is not null and load_date = '20221115';

Error - Error while compiling statement: FAILED: SemanticException [Error 10004]: line 1:7 Invalid table alias or column reference 'application': (possible column names are: date, transaction, status, direction, message_type, smi, medium, medium_type, aircraft, flight, callsign, dep_icao, arr_icao, application_str, from_to, machine, msg_length, msg_text, attached_files, load_date)

Any other solution ?

Hamiltonian