Access AWS athena through JPA spring boot

Question

I am trying to use AWS athena using spring boot jpa datasource . I tried setting up datasource with given properties.

    spring.datasource.driver-class-name=com.amazonaws.athena.jdbc.AthenaDriver
    spring.datasource.url=jdbc:awsathena://athena.us-east-1.amazonaws.com:443/default
    spring.datasource.username=*****
    spring.datasource.password=***
    spring.datasource.tomcat.connectionProperties=s3_staging_dir=*****

I am getting below exception

    Caused by:org.springframework.beans.BeanInstantiationException:    Failed to instantiate [org.springframework.orm.jpa.JpaVendorAdapter]: Factory method 'jpaVendorAdapter' threw exception; nested exception is java.lang.IllegalArgumentException: URL must start with 'jdbc'
at org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:189) ~[spring-beans-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at org.springframework.beans.factory.support.ConstructorResolver.instantiateUsingFactoryMethod(Constructo`enter code here`rResolver.java:588) ~[spring-beans-4.3.7.RELEASE.jar:4.3.7.RELEASE]
... 51 common frames omitted
    Caused by: java.lang.IllegalArgumentException: URL must start with 'jdbc'
at org.springframework.util.Assert.isTrue(Assert.java:92) ~[spring-core-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at org.springframework.boot.jdbc.DatabaseDriver.fromJdbcUrl(DatabaseDriver.java:268) ~[spring-boot-1.5.2.RELEASE.jar:1.5.2.RELEASE]
at org.springframework.boot.autoconfigure.orm.jpa.DatabaseLookup.getDatabase(DatabaseLookup.java:73) ~[spring-boot-autoconfigure-1.5.2.RELEASE.jar:1.5.2.RELEASE]
at org.springframework.boot.autoconfigure.orm.jpa.JpaProperties.determineDatabase(JpaProperties.java:139) ~[spring-boot-autoconfigure-1.5.2.RELEASE.jar:1.5.2.RELEASE]
at org.springframework.boot.autoconfigure.orm.jpa.JpaBaseConfiguration.jpaVendorAdapter(JpaBaseConfiguration.java:105) ~[spring-boot-autoconfigure-1.5.2.RELEASE.jar:1.5.2.RELEASE]
at org.springframework.boot.autoconfigure.orm.jpa.HibernateJpaAutoConfiguration$$EnhancerBySpringCGLIB$$720f8624.CGLIB$jpaVendorAdapter$4(<generated>) ~[spring-boot-autoconfigure-1.5.2.RELEASE.jar:1.5.2.RELEASE]
at org.springframework.boot.autoconfigure.orm.jpa.HibernateJpaAutoConfiguration$$EnhancerBySpringCGLIB$$720f8624$$FastClassBySpringCGLIB$$9766cf.invoke(<generated>) ~[spring-boot-autoconfigure-1.5.2.RELEASE.jar:1.5.2.RELEASE]
at org.springframework.cglib.proxy.MethodProxy.invokeSuper(MethodProxy.java:228) ~[spring-core-4.3.7.RELEASE.jar:4.3.7.RELEASE]

afaik athena jdbc driver is eligible for using sql clients only. at times I tried to implement a `javax.sql.DataSource` that using athena jdbc driver but it doesn't implement and thus not supporting prepared statements. — px5x2, May 12 '18 at 13:37
Have you tried configuring a vanilla JdbcTemplate against the Athena JDBC url? — Kevin Hooke, Mar 05 '19 at 22:10
I was trying to do the same thing. JPA doesn't work but using JdbcTemplate works fine. — JabariP, Mar 06 '19 at 21:59
Have you tried using double quotes or single quotes around the URL string? — Abdullah Khawer, Aug 21 '19 at 08:27

score 2 · Answer 1 · answered Apr 03 '20 at 15:06

As a response to answers provided here (i cannot comment because of reputation) and also because there is not working example online, i will provide how i have managed to configure spring boot(2.2.5) data JPA to use Athena JDBC driver.

Athena jdbc connection url has format
jdbc:awsathena://athena.[Region].amazonaws.com:443;User= [AccessKey];Password=[SecretKey];S3OutputLocation=[Output];

My applications.properties file
spring.datasource.url=specifiedformat
spring.datasource.driver-class-name=com.simba.athena.jdbc42.Driver
spring.datasource.hikari.connection-test-query=SELECT 1
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQLDialect
spring.jpa.hibernate.ddl-auto=validate

JDBC driver for Athena can be found here
https://docs.aws.amazon.com/athena/latest/ug/connect-with-jdbc.html
Or if you wish to use maven repository
https://mvnrepository.com/artifact/com.syncron.amazonaws/simba-athena-jdbc-driver/2.0.2

I used MySQLDialect because its similar to ANSI SQL used by Athena (there is currently not existing dialect for Athena). Of course because of MySQL dialect and the fact that Athena doesn't support prepared statements (only those with ? placeholder actually, you can use String.format() to bypass this, ref: AWS Athena JDBC PreparedStatement), you will not be able to perform all of the spring boot data jpa default implemented operations, but you can bypass this by using @Query annotations inside your repository classes and String.format() to bypass unsupported prepared statements.

Spring boot + JPA + Hibernate + Athena JDBC driver works fine for me (I am currently only reading data), only problem is that you have to test each of your queries if its supported in this configuration (as they can throw exceptions for unsupported operations).

could you please post some examples of how do you use `@Query` annotations inside your repository classes and `String.format()` to bypass unsupported prepared statements? — Cosmin, May 05 '21 at 15:31

Ken Chan · Answer 2 · 2019-08-23T13:07:45.040

By default, Spring boot will automatically detect which Hibernate Dialect to use based on the Datasource 's metadata retrieved from the JDBC driver.

This error is due to Athena JDBC driver fails to get the related metadata. Not sure why it fails but you can by-pass this detection and explicitly declare which Hibernate Dialect to use by yourself by declaring a JpaVendorAdapter bean :

@Bean
public JpaVendorAdapter jpaVendorAdapter(JpaProperties properties) {
    AbstractJpaVendorAdapter adapter = new HibernateJpaVendorAdapter();
    adapter.setShowSql(properties.isShowSql());
    adapter.setGenerateDdl(properties.isGenerateDdl());
    return adapter;
}

^{I keep all the default behaviour but just disable that auto-detecting dialect}

And define the Dialect in application.properties:

spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.XXXXXX

But the point is that I am doubtful if Athena can be used with Hibernate as I cannot find there is an existing Dialect for Athena. So what I suggest are:

Try the Dialect from another database which its SQL syntax is similar to Athena. It is not surprise that it will not work for some cases.
Implement the Dialect for Athena by yourself.

score 0 · Answer 3 · answered Aug 22 '19 at 16:39

AWS Athena documentation states to make sure port 444 is open to outbound traffic. Per the connection documentation here https://docs.aws.amazon.com/athena/latest/ug/connect-with-jdbc.html

Not sure if this is the right solution, so I dug in a bit since this question is so old.

Access AWS athena through JPA spring boot

3 Answers3