AWS Athena ODI JDBC connection

Question

Has anyone tried connecting AWS Athena from Oracle Data Integrator.

I have been trying this since long but am not able to find the appropriate JDBC connection string.

Steps I have followed from https://docs.aws.amazon.com/athena/latest/ug/connect-with-jdbc.html#jdbc-url-format

Downloaded AthenaJDBC42_2.0.7.jar driver from AWS
Copied the same into the userlib directory of ODI
Created new technology in ODI
Trying to add Data server. Not able to form JDBC url.

JDBC string Sample format (which isn't working):

jdbc:awsathena://AwsRegion=[Region];User=[AccessKey];Password=[SecretKey];S3OutputLocation=[Output];

Please can anyone help? Thanks.

Red Boy Red Boy · Accepted Answer · 2019-05-01T06:48:49

This is sorter version of JDBC I implemented for Athena. This was just POC and we want to go with AWS SDK rather then jdbc though less important here.

package com.poc.aws.athena;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class AthenaJDBC {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Connection connection = null;
        Class.forName("com.simba.athena.jdbc.Driver");
        connection = DriverManager.getConnection("jdbc:awsathena://AwsRegion=us-east-1;User=EXAMPLEKEY;"
                + "Password=EXAMPLESECRETKYE;S3OutputLocation=s3://example-bucket-name-us-east-1;");

        Statement statement = connection.createStatement();
        ResultSet queryResults = statement.executeQuery(ExampleConstants.ATHENA_SAMPLE_QUERY);
        System.out.println(queryResults.next());

    }
}

The only important point here related to url.

jdbc:awsathena://AwsRegion=us-east-1;User=EXAMPLEKEY;" + "Password=EXAMPLESECRETKYE;S3OutputLocation=s3://example-bucket-name-us-east-1;.

us-east-1 must be replaced with your actual region like us-west-1 etc
EXAMPLEKEY must be replaced with your AWS Access key that has Athena access.
EXAMPLESECRETKEY must be replaced with your AWS Secret key that has Athena access.
example-bucket-name-us-east-1 must be replaced with your S3 bucket that above keys has write access too.

There other keys simba driver support but less important here.

I hope this helps.

AWS Athena ODI JDBC connection

2 Answers