I am trying to query a table by selecting only those cases that meet a certain condition. It is a query called from Beeline, within a R script. The problem is the field that must meet this condition has a reserved word as a column name: 'table'.
Whenever I run this in an AWS-EMR cluster:
SELECT ... FROM ... WHERE `table` = 'something' AND year = 2018
I get the following error:
bash: table: command not found
Connecting to jdbc:hive2://localhost:10000
Connected to: Apache Hive (version 2.3.2-amzn-0)
Driver: Hive JDBC (version 2.3.2-amzn-0)
Transaction isolation: TRANSACTION_REPEATABLE_READ
. . . . . . . . . . . . . . . . Error: Error while compiling statement: FAILED: ParseException line 1:242 cannot recognize input near '=' ''something'' 'and' in expression specification (state=42000,code=40000)
Closing: 0: jdbc:hive2://localhost:10000
ExitValue: 1
Both blocks marked in bold style should be fixed. Notice that the keyword 'table' is already escaped with backticks.
I have searched the web, but still cannot find a proper solution. Any help would be appreciated.
As @SamsonScharfrichter pointed out in the comments:
Short-term workaround: try escaping back-ticks i.e. \`table\` or maybe \\`table\\`
This worked for my problem.