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
posta un anno fa111 visualizzazioni
Nessuna risposta

Accesso non effettuato. Accedi per postare una risposta.

Una buona risposta soddisfa chiaramente la domanda, fornisce un feedback costruttivo e incoraggia la crescita professionale del richiedente.

Linee guida per rispondere alle domande