Athena jdbc example with localstack-pro

Ilia · June 6, 2023, 5:45pm

Hello, I try to run some example of using jdbc with dockerized localstack-pro.

This is my docker-compose config:

version: "3.8"

services:
  aws:
    image: localstack/localstack-pro
    container_name: mocked-aws
    ports:
      - "127.0.0.1:4566:4566"            # LocalStack Gateway
      - "127.0.0.1:4510-4559:4510-4559"  # external services port range
      - "127.0.0.1:53:53"                # DNS config (required for Pro)
      - "127.0.0.1:53:53/udp"            # DNS config (required for Pro)
      - "127.0.0.1:443:443"              # LocalStack HTTPS Gateway (required for Pro)
    networks:
      - hdl-network
    environment:
      - DEBUG=false
      - PERSISTENCE=false
      - LOCALSTACK_API_KEY=…
      - DOCKER_HOST=unix:///var/run/docker.sock
    volumes:
      - ./local_env/aws_local/data:/root/data
      - ./local_env/aws_local/init-aws.sh:/etc/localstack/init/ready.d/init-aws.sh
      - ./local_env/aws_local/volume:/var/lib/localstack
      - /var/run/docker.sock:/var/run/docker.sock

networks:
  hdl-network: {}

this is my jdbc url:

String url = "jdbc:awsathena://EndpointOverride=localhost:443;AwsRegion=us-east-1;User=doesntmatter;Password=doesntmatter;S3OutputLocation=s3://aws-athena-query-results-000000000000-us-east-1;";

While I am trying to get a connection:

Connection conn = DriverManager.getConnection(url)

I see such an error:

Exception in thread "main" java.sql.SQLException: [Simba][AthenaJDBC](100131) An error has been thrown from the AWS SDK client. Unable to execute HTTP request: Certificate for <localhost> doesn't match any of the subject alternative names: [*.amplifyapp.localhost.localstack.cloud, *.cloudfront.localhost.localstack.cloud, *.elb.localhost.localstack.cloud, *.execute-api.localhost.localstack.cloud, *.localhost.localstack.cloud, *.opensearch.localhost.localstack.cloud, *.s3-website.localhost.localstack.cloud, *.s3.localhost.localstack.cloud, *.scm.localhost.localstack.cloud, localhost.localstack.cloud] [Execution ID not available]
	at com.simba.athena.athena.api.AJClient.checkAndThrowException(Unknown Source)
	at com.simba.athena.athena.api.AJClient.executeQuery(Unknown Source)
	at com.simba.athena.athena.api.AJClient.checkAthenaConnection(Unknown Source)
	at com.simba.athena.athena.api.AJClient.<init>(Unknown Source)
	at com.simba.athena.athena.core.AJConnection.connect(Unknown Source)
	at com.simba.athena.jdbc.common.BaseConnectionFactory.doConnect(Unknown Source)
	at com.simba.athena.jdbc.common.AbstractDriver.connect(Unknown Source)
	at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:683)
	at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:253)
Caused by: com.simba.athena.support.exceptions.GeneralException: [Simba][AthenaJDBC](100131) An error has been thrown from the AWS SDK client. Unable to execute HTTP request: Certificate for <localhost> doesn't match any of the subject alternative names: [*.amplifyapp.localhost.localstack.cloud, *.cloudfront.localhost.localstack.cloud, *.elb.localhost.localstack.cloud, *.execute-api.localhost.localstack.cloud, *.localhost.localstack.cloud, *.opensearch.localhost.localstack.cloud, *.s3-website.localhost.localstack.cloud, *.s3.localhost.localstack.cloud, *.scm.localhost.localstack.cloud, localhost.localstack.cloud] [Execution ID not available]
	... 9 more
Caused by: com.simba.athena.amazonaws.SdkClientException: Unable to execute HTTP request: Certificate for <localhost> doesn't match any of the subject alternative names: [*.amplifyapp.localhost.localstack.cloud, *.cloudfront.localhost.localstack.cloud, *.elb.localhost.localstack.cloud, *.execute-api.localhost.localstack.cloud, *.localhost.localstack.cloud, *.opensearch.localhost.localstack.cloud, *.s3-website.localhost.localstack.cloud, *.s3.localhost.localstack.cloud, *.scm.localhost.localstack.cloud, localhost.localstack.cloud]
	at com.simba.athena.amazonaws.http.AmazonHttpClient$RequestExecutor.handleRetryableException(AmazonHttpClient.java:1219)
	at com.simba.athena.amazonaws.http.AmazonHttpClient$RequestExecutor.executeHelper(AmazonHttpClient.java:1165)
	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.executeStartQueryExecution(AmazonAthenaClient.java:2050)
	at com.simba.athena.amazonaws.services.athena.AmazonAthenaClient.startQueryExecution(AmazonAthenaClient.java:2019)
	at com.simba.athena.athena.api.AJClient.executeQuery(Unknown Source)
	at com.simba.athena.athena.api.AJClient.checkAthenaConnection(Unknown Source)
	at com.simba.athena.athena.api.AJClient.<init>(Unknown Source)
	at com.simba.athena.athena.core.AJConnection.connect(Unknown Source)
	at com.simba.athena.jdbc.common.BaseConnectionFactory.doConnect(Unknown Source)
	at com.simba.athena.jdbc.common.AbstractDriver.connect(Unknown Source)
	at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:683)
	at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:253)
	at com.dxfeed.hdl.rest.T.main(T.java:13)
Caused by: javax.net.ssl.SSLPeerUnverifiedException: Certificate for <localhost> doesn't match any of the subject alternative names: [*.amplifyapp.localhost.localstack.cloud, *.cloudfront.localhost.localstack.cloud, *.elb.localhost.localstack.cloud, *.execute-api.localhost.localstack.cloud, *.localhost.localstack.cloud, *.opensearch.localhost.localstack.cloud, *.s3-website.localhost.localstack.cloud, *.s3.localhost.localstack.cloud, *.scm.localhost.localstack.cloud, localhost.localstack.cloud]
	at org.apache.http.conn.ssl.SSLConnectionSocketFactory.verifyHostname(SSLConnectionSocketFactory.java:507)
	at org.apache.http.conn.ssl.SSLConnectionSocketFactory.createLayeredSocket(SSLConnectionSocketFactory.java:437)
	at org.apache.http.conn.ssl.SSLConnectionSocketFactory.connectSocket(SSLConnectionSocketFactory.java:384)
	at com.simba.athena.amazonaws.http.conn.ssl.SdkTLSSocketFactory.connectSocket(SdkTLSSocketFactory.java:142)
	at org.apache.http.impl.conn.DefaultHttpClientConnectionOperator.connect(DefaultHttpClientConnectionOperator.java:142)
	at org.apache.http.impl.conn.PoolingHttpClientConnectionManager.connect(PoolingHttpClientConnectionManager.java:376)
	at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:104)
	at java.base/java.lang.reflect.Method.invoke(Method.java:578)
	at com.simba.athena.amazonaws.http.conn.ClientConnectionManagerFactory$Handler.invoke(ClientConnectionManagerFactory.java:76)
	at com.simba.athena.amazonaws.http.conn.$Proxy13.connect(Unknown Source)
	at org.apache.http.impl.execchain.MainClientExec.establishRoute(MainClientExec.java:393)
	at org.apache.http.impl.execchain.MainClientExec.execute(MainClientExec.java:236)
	at org.apache.http.impl.execchain.ProtocolExec.execute(ProtocolExec.java:186)
	at org.apache.http.impl.client.InternalHttpClient.doExecute(InternalHttpClient.java:185)
	at org.apache.http.impl.client.CloseableHttpClient.execute(CloseableHttpClient.java:83)
	at org.apache.http.impl.client.CloseableHttpClient.execute(CloseableHttpClient.java:56)
	at com.simba.athena.amazonaws.http.apache.client.impl.SdkHttpClient.execute(SdkHttpClient.java:72)
	at com.simba.athena.amazonaws.http.AmazonHttpClient$RequestExecutor.executeOneRequest(AmazonHttpClient.java:1346)
	at com.simba.athena.amazonaws.http.AmazonHttpClient$RequestExecutor.executeHelper(AmazonHttpClient.java:1157)
	... 21 more

Marcel · June 7, 2023, 6:05am

Hi @Ilia,

Could you please try using for EndpointOverride localhost.localstack.cloud:4566 or 443, or localhost:4566? It might be helpful to set the SSL_Insecure=true.

Ilia · June 7, 2023, 7:20am

Thank you, I tried localhost.localstack.cloud:4566 and now I have another error:

Exception in thread "main" java.sql.SQLException: [Simba][AthenaJDBC](700110) Unexpected session error: Cannot invoke "com.simba.athena.amazonaws.services.athena.model.ResultReuseInformation.getReusedPreviousResult()" because the return value of "com.simba.athena.amazonaws.services.athena.model.QueryExecutionStatistics.getResultReuseInformation()" is null.
	at com.simba.athena.athena.api.AJClient.checkAthenaConnection(Unknown Source)
	at com.simba.athena.athena.api.AJClient.<init>(Unknown Source)
	at com.simba.athena.athena.core.AJConnection.connect(Unknown Source)
	at com.simba.athena.jdbc.common.BaseConnectionFactory.doConnect(Unknown Source)
	at com.simba.athena.jdbc.common.AbstractDriver.connect(Unknown Source)
	at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:683)
	at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:253)
Caused by: com.simba.athena.support.exceptions.GeneralException: [Simba][AthenaJDBC](700110) Unexpected session error: Cannot invoke "com.simba.athena.amazonaws.services.athena.model.ResultReuseInformation.getReusedPreviousResult()" because the return value of "com.simba.athena.amazonaws.services.athena.model.QueryExecutionStatistics.getResultReuseInformation()" is null.
	... 7 more

Marcel · June 7, 2023, 12:55pm

Would you be able to try using something similar? I was able to connect to LocalStack using the SQLWorkbench and the SimbaAthenaJDBC42-2.0.36.1000 (AthenaJDBC42-2.0.36.1000.jar).

String url = "jdbc:awsathena://EndpointOverride=localhost.localstack.cloud:4566;AwsRegion=us-east-1;User=example;Password=password123;s3://aws-athena-query-results-000000000000-us-east-1;"

If you have not yet run any Athena queries, LocalStack will download the required packages like Spark, Hive, Hadoop, and others. The download and unzip process can take a while.

You can also use something simple to start the downloads, like
awslocal athena start-query-execution --query-string "SELECT 1".

Ilia · June 7, 2023, 1:46pm

Yes, I use exactly the same url. There is not issues with running queries, I can run a query via CLI. There is an issue with establishing jdbc connection.

Unfortunately, I can not check it in MySQLWorkbench. But I can check the connection in DBeaver, and in DBeaver I have the same issue.

Ilia · June 7, 2023, 1:47pm

And I use the same version of the driver as you mentioned.

Marcel · June 7, 2023, 3:21pm

I’ve done a few more tests, and the latest version doesn’t seem to be working with the LocalStack at the moment.

Can you please download the older version of drivers and use for example 2.0.31.1000?

Ilia · June 7, 2023, 4:26pm

Thank you. So, I downgraded to the suggested version. The connection is established successfully (from java code, and from DBeaver). But now I am faced with another issue:

Exception in thread "main" java.sql.SQLException: [Simba][AthenaJDBC](100123) An error has occurred. Exception during column initialization .Details: Unable to execute HTTP request: Remote host terminated the handshake [Execution ID: c7399852].
	at com.simba.athena.athena.dataengine.AJStreamResultSet.<init>(Unknown Source)
	at com.simba.athena.athena.dataengine.AJQueryExecutor.execute(Unknown Source)
	at com.simba.athena.jdbc.common.SStatement.executeNoParams(Unknown Source)
	at com.simba.athena.jdbc.common.BaseStatement.executeQuery(Unknown Source)
Caused by: com.simba.athena.support.exceptions.GeneralException: [Simba][AthenaJDBC](100123) An error has occurred. Exception during column initialization .Details: Unable to execute HTTP request: Remote host terminated the handshake [Execution ID: c7399852].
	... 4 more

This is my sample code:

String url = "jdbc:awsathena://EndpointOverride=localhost.localstack.cloud:4566;AwsRegion=us-east-1;User=example;Password=password123;s3://aws-athena-query-results-000000000000-us-east-1;";

try (Connection conn = DriverManager.getConnection(url)) {
  ResultSet rs = conn.createStatement().executeQuery("select f1, f2 from hdl.tns limit 25");

  while (rs.next()) {
    System.out.println(rs.getString(1));
  }
}

Marcel · June 12, 2023, 9:32am

Hi @Ilia,

We have now implemented changes which, we hope, will resolve the issue you reported.
It will be available in the latest release with a build date of today, e.g. 2023-06-12.

Please update the Docker image when it is available and try again.
Thank you.

whummer · June 12, 2023, 3:02pm

Thanks @Ilia , we have also provided some more details in the Github issue you’ve created: bug: Remote host terminated the handshake · Issue #8483 · localstack/localstack · GitHub Please keep us posted on how it goes…

Ilia · June 13, 2023, 7:41am

Thank you very much. Everything works.

Topic		Replies	Views
Parameterized SQL query to Athena via AWS SDK Platform	1	427	June 16, 2023
Localstack athena queries stuck in RUNNING state Platform	3	202	July 10, 2024
Issue Connecting AWS SAM Lambda Handlers to LocalStack Services Platform localstack	2	283	April 11, 2024
Unable to connect to localstack container from another container in the same network Platform localstack , discussion-pages	1	1814	February 20, 2023
.NET Lambda on LocalStack throws "exception while calling lambda with unknown operation:" Platform localstack	2	29	September 17, 2024

Athena jdbc example with localstack-pro

Related topics