Cao Felix Cao Felix - 3 months ago 32
Java Question

How to use special character in column name using HIVE JDBC driver?

I have a java program that is using Hortonworks' JDBC driver to connect to their database in the VirtualBox. Everything works very well, but I have problem with the colon in the sql query.

query = new StringBuilder("SELECT ROW_NUMBER() OVER() AS "+rowid+", * FROM "+tableName).toString();

The rowid I am trying to set is ":rowid:". I add colon in the front and in the back to make the column name to be unique for the other applications. Right now the colon has to be there and I want to know if there is a way to do it with Hortonworks JDBC Driver for Apache Hive (v1.0.36) from the official site. I have tested it without the colon there and that will work.

With the colon, the error message:

JDBC driver version: HiveJDBC
java.sql.SQLException: [Simba]HiveJDBCDriver ERROR processing query/statement. Error Code: 40000, SQL state: TStatus(statusCode:ERROR_STATUS, infoMessages:[*org.apache.hive.service.cli.HiveSQLException:Error while compiling statement: FAILED: ParseException line 1:30 cannot recognize input near 'AS' ':' 'rowid' in selection target:17:16,,,,,,,,, org.apache.hive.service.cli.thrift.TCLIService$Processor$, org.apache.hive.service.cli.thrift.TCLIService$Processor$,,,, org.apache.thrift.server.TThreadPoolServer$,, java.util.concurrent.ThreadPoolExecutor$,, *org.apache.hadoop.hive.ql.parse.ParseException:line 1:30 cannot recognize input near 'AS' ':' 'rowid' in selection target:22:6,,,,,,,], sqlState:42000, errorCode:40000, errorMessage:Error while compiling statement: FAILED: ParseException line 1:30 cannot recognize input near 'AS' ':' 'rowid' in selection target), Query: SELECT ROW_NUMBER() OVER() AS :rowid:, * FROM input.
at com.simba.hiveserver2.hivecommon.api.HS2Client.executeStatementInternal(Unknown Source)
at com.simba.hiveserver2.hivecommon.api.HS2Client.executeStatement(Unknown Source)
at com.simba.hiveserver2.hivecommon.dataengine.HiveJDBCNativeQueryExecutor.executeQuery(Unknown Source)
at com.simba.hiveserver2.hivecommon.dataengine.HiveJDBCNativeQueryExecutor.(Unknown Source)
at com.simba.hiveserver2.hivecommon.dataengine.HiveJDBCDataEngine.prepare(Unknown Source)
at com.simba.hiveserver2.jdbc.common.SStatement.executeNoParams(Unknown Source)
at com.simba.hiveserver2.jdbc.common.SStatement.executeQuery(Unknown Source)

Does anyone have any idea? I tried to add ' and \" in the string, but they all give errors.


Special characters in column name should be enclosed by back-tick(`) character:

new StringBuilder("SELECT ROW_NUMBER() OVER() as `"+rowid+"`, * FROM "+tableName).toString();

Read Supporting Quoted Identifiers in Column Names for more details.