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
質問済み 1年前111ビュー
回答なし

ログインしていません。 ログイン 回答を投稿する。

優れた回答とは、質問に明確に答え、建設的なフィードバックを提供し、質問者の専門分野におけるスキルの向上を促すものです。

質問に答えるためのガイドライン

関連するコンテンツ