How to use question mark in a query using Athena JDBC driver?

0

Following is an example query that I'm trying to execute using Athena JDBC driver. It works fine using Athena query editor.

CREATE OR REPLACE VIEW default.simple_table_transformed AS SELECT REGEXP_REPLACE( so_simple, '(?i)(Hive)?(SerDe)' ) AS example from default.simple_table

However, it fails with following error when executed using JDBC driver. Following is from driver debug log, query after translation is identical to how we would have executed in the query editor and it would have worked.

Apr 21 16:42:04.312 TRACE 1 com.simba.athena.dsi.core.impl.DSIConnection.toNativeSQL("CREATE OR REPLACE VIEW default.simple_table_transformed AS SELECT REGEXP_REPLACE( so_simple, '(?i)(Hive)?(SerDe)' ) AS example from default.simple_table;"): +++++ enter +++++
Apr 21 16:42:04.312 TRACE 1 com.simba.athena.athena.dataengine.AJDataEngine.prepare(): +++++ enter +++++
Apr 21 16:42:04.312 DEBUG 1 com.simba.athena.athena.dataengine.AJDataEngine.prepare: Query before escape: CREATE OR REPLACE VIEW default.simple_table_transformed AS SELECT REGEXP_REPLACE( so_simple, '(?i)(Hive)?(SerDe)' ) AS example from default.simple_table;
Apr 21 16:42:04.314 DEBUG 1 com.simba.athena.athena.dataengine.AJDataEngine.prepare: Query after escape: CREATE OR REPLACE VIEW default.simple_table_transformed AS SELECT REGEXP_REPLACE( so_simple, '(?i)(Hive)?(SerDe)' ) AS example from default.simple_table
Apr 21 16:42:04.315 TRACE 1 com.simba.athena.athena.dataengine.AJQueryExecutor.AJQueryExecutor(): +++++ enter +++++
Apr 21 16:42:04.315 TRACE 1 com.simba.athena.athena.core.AJConnection.getClient(com.simba.athena.athena.api.AJClient@710b30ef): +++++ enter +++++
Apr 21 16:42:04.315 TRACE 1 com.simba.athena.athena.core.AJStatement.getQueryInfoHandler(): +++++ enter +++++
Apr 21 16:42:04.315 TRACE 1 com.simba.athena.athena.core.AJStatement.getQueryInfoHandler(): +++++ enter +++++
Apr 21 16:42:04.368 ERROR 1 com.simba.athena.exceptions.ExceptionConverter.toSQLException: [JDBC Driver]Operation unsupported for Statement Type CREATE_VIEW (Service: AmazonAthena; Status Code: 400; Error Code: InvalidRequestException; Request ID: 80cb6a85-400d-4a04-80aa-4bacaa382539; Proxy: null)
java.sql.SQLException: [JDBC Driver]Operation unsupported for Statement Type CREATE_VIEW (Service: AmazonAthena; Status Code: 400; Error Code: InvalidRequestException; Request ID: 80cb6a85-400d-4a04-80aa-4bacaa382539; Proxy: null)
  at com.simba.athena.amazonaws.http.AmazonHttpClient$RequestExecutor.handleErrorResponse(AmazonHttpClient.java:1879)
  at com.simba.athena.amazonaws.http.AmazonHttpClient$RequestExecutor.handleServiceErrorResponse(AmazonHttpClient.java:1418)
  at com.simba.athena.amazonaws.http.AmazonHttpClient$RequestExecutor.executeOneRequest(AmazonHttpClient.java:1387)
  at com.simba.athena.amazonaws.http.AmazonHttpClient$RequestExecutor.executeHelper(AmazonHttpClient.java:1157)
  at com.simba.athena.amazonaws.http.AmazonHttpClient$RequestExecutor.doExecute(AmazonHttpClient.java:814)
  at com.simba.athena.amazonaws.http.AmazonHttpClient$RequestExecutor.executeWithTimer(AmazonHttpClient.java:781)
  at com.simba.athena.amazonaws.http.AmazonHttpClient$RequestExecutor.execute(AmazonHttpClient.java:755)
  at com.simba.athena.amazonaws.http.AmazonHttpClient$RequestExecutor.access$500(AmazonHttpClient.java:715)
  at com.simba.athena.amazonaws.http.AmazonHttpClient$RequestExecutionBuilderImpl.execute(AmazonHttpClient.java:697)
  at com.simba.athena.amazonaws.http.AmazonHttpClient.execute(AmazonHttpClient.java:561)
  at com.simba.athena.amazonaws.http.AmazonHttpClient.execute(AmazonHttpClient.java:541)
  at com.simba.athena.amazonaws.services.athena.AmazonAthenaClient.doInvoke(AmazonAthenaClient.java:2570)
  at com.simba.athena.amazonaws.services.athena.AmazonAthenaClient.invoke(AmazonAthenaClient.java:2537)
  at com.simba.athena.amazonaws.services.athena.AmazonAthenaClient.invoke(AmazonAthenaClient.java:2526)
  at com.simba.athena.amazonaws.services.athena.AmazonAthenaClient.executeCreatePreparedStatement(AmazonAthenaClient.java:532)
  at com.simba.athena.amazonaws.services.athena.AmazonAthenaClient.createPreparedStatement(AmazonAthenaClient.java:499)
  at com.simba.athena.athena.api.AJClient.prepareStatement(Unknown Source)
  at com.simba.athena.athena.dataengine.AJQueryExecutor.<init>(Unknown Source)
  at com.simba.athena.athena.dataengine.AJDataEngine.prepare(Unknown Source)
  at com.simba.athena.athena.dataengine.AJDataEngine.prepare(Unknown Source)
  at com.simba.athena.jdbc.common.SStatement.executeNoParams(Unknown Source)
  at com.simba.athena.jdbc.common.BaseStatement.execute(Unknown Source)
  at org.sudarshan.CreateView.createView(CreateView.java:43)
  at org.sudarshan.CreateView.main(CreateView.java:33)
Caused by: com.simba.athena.amazonaws.services.athena.model.InvalidRequestException: Operation unsupported for Statement Type CREATE_VIEW (Service: AmazonAthena; Status Code: 400; Error Code: InvalidRequestException; Request ID: 80cb6a85-400d-4a04-80aa-4bacaa382539; Proxy: null)
  ... 24 more

Sample code

String sqlToExecute = "CREATE OR REPLACE VIEW default.simple_table_transformed AS SELECT REGEXP_REPLACE( so_simple, '(?i)(Hive)?(SerDe)' ) AS example from default.simple_table;";
Statement statement = connection.createStatement();
statement.execute(sqlToExecute);

What have I tried?

Tested with following but none of them seems to have helped.

  1. Using ?? in place of ?
  2. Escaping ? with the help of \ character
Su
asked a year ago108 views
No Answers

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions