Cao Felix Cao Felix - 1 month ago 22
Java Question

How does Hive JDBC driver handle colon?

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 01.00.36.1046
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.operation.Operation:toSQLException:Operation.java:335, org.apache.hive.service.cli.operation.SQLOperation:prepare:SQLOperation.java:148, org.apache.hive.service.cli.operation.SQLOperation:runInternal:SQLOperation.java:226, org.apache.hive.service.cli.operation.Operation:run:Operation.java:276, org.apache.hive.service.cli.session.HiveSessionImpl:executeStatementInternal:HiveSessionImpl.java:468, org.apache.hive.service.cli.session.HiveSessionImpl:executeStatementAsync:HiveSessionImpl.java:456, org.apache.hive.service.cli.CLIService:executeStatementAsync:CLIService.java:298, org.apache.hive.service.cli.thrift.ThriftCLIService:ExecuteStatement:ThriftCLIService.java:506, org.apache.hive.service.cli.thrift.TCLIService$Processor$ExecuteStatement:getResult:TCLIService.java:1317, org.apache.hive.service.cli.thrift.TCLIService$Processor$ExecuteStatement:getResult:TCLIService.java:1302, org.apache.thrift.ProcessFunction:process:ProcessFunction.java:39, org.apache.thrift.TBaseProcessor:process:TBaseProcessor.java:39, org.apache.hive.service.auth.TSetIpAddressProcessor:process:TSetIpAddressProcessor.java:56, org.apache.thrift.server.TThreadPoolServer$WorkerProcess:run:TThreadPoolServer.java:286, java.util.concurrent.ThreadPoolExecutor:runWorker:ThreadPoolExecutor.java:1145, java.util.concurrent.ThreadPoolExecutor$Worker:run:ThreadPoolExecutor.java:615, java.lang.Thread:run:Thread.java:745, *org.apache.hadoop.hive.ql.parse.ParseException:line 1:30 cannot recognize input near 'AS' ':' 'rowid' in selection target:22:6, org.apache.hadoop.hive.ql.parse.ParseDriver:parse:ParseDriver.java:205, org.apache.hadoop.hive.ql.parse.ParseDriver:parse:ParseDriver.java:166, org.apache.hadoop.hive.ql.Driver:compile:Driver.java:437, org.apache.hadoop.hive.ql.Driver:compile:Driver.java:320, org.apache.hadoop.hive.ql.Driver:compileInternal:Driver.java:1219, org.apache.hadoop.hive.ql.Driver:compileAndRespond:Driver.java:1213, org.apache.hive.service.cli.operation.SQLOperation:prepare:SQLOperation.java:146], 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.

Answer

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.