Connecting to files in S3 buckets via Amazon Athena

Follow

Jay Dastur

July 18, 2017 14:29

 

Introduction

 

Amazon Athena is an interactive query service that makes it easy to analyze data directly in Amazon Simple Storage Service (Amazon S3) using standard SQL. Amazon provides a JDBC driver for Athena which can be leveraged via the Sisense JDBC wrapper framework. 

 

On Sisense Version 7.2 and above we can use the Generic JDBC connector and the Athena AWS Driver directly. 

 

Instructions - Web ECM on Sisense Version 7.2 and Above

This is all based on the Generic JDBC connector instructions in the official documentation:

1. Download the Athena AWS JDBC Driver here

 

2. Create a folder called Athena in ..\ProgramData\Sisense\DataConnectors\jdbcdrivers\ 

 

3. Place the .jar file in the Athena folder

 

4. Restart the Sisense.JVMConnectorsContainer service

 

5. Edit your Elasticube in Sisense web, add data using the Generic JDBC connector

 

6. Use the following settings:

• Connection String: <your connection string>
• JDBC JARs Folder: C:\ProgramData\Sisense\DataConnectors\jdbcdrivers\Athena
• Driver's Class Name: com.simba.athena.jdbc.Driver
• User Name: <User name of the database>
• Password: <Password for that user>

 

7. Then click Next to move on to table selection

 

Instructions - Desktop ECM and Versions of Sisense prior to 7.2

 

1. Download the  JDBCConnector and follow the instructions to install it.

 

2. The connector shows up as a Data Source in the Elasticube Manager as JDBC Driver

 

When creating the connection enter the URL in the following format.

 

jdbc:awsathena://athena.<region>.amazonaws.com:443;s3_staging_dir=s3://<s3 folder>

 

e.g. jdbc:awsathena://athena.us-west-2.amazonaws.com:443;s3_staging_dir=s3://athena-query-results

 

When using encryption also append the following property to the URL

 

&query_results_encryption_option=<encryption type>

 

4. User =  <AWS access key> 

 

5. Password = <AWS secret key>

 

 

 

0

• Official comment

  

  Katie Garrison January 17, 2019 16:50 (Edited May 28, 2019 15:02)

  Just tested on Sisense Version 7.2.1 and 7.3 - if you are using Sisense Web (ECM 2.0), you do not necessarily need to use the additional driver Jay wrote to connect to Athena AWS. Instead follow the instructions below. This is all based on the Generic JDBC connector instructions in the official documentation https://documentation.sisense.com/latest/managing-data/connectors/jdbc.htm:

  1. Download the Athena AWS JDBC Driver here: https://docs.aws.amazon.com/athena/latest/ug/connect-with-jdbc.html. In my test I used AthenaJDBC42-2.0.6.jar
  2. Create a folder called Athena in ..\ProgramData\Sisense\DataConnectors\jdbcdrivers\ 
  3. Place the .jar file in the Athena folder
  4. Restart the Sisense.JVMConnectorsContainer service
  5. Edit your Elasticube in Sisense web, add data using the Generic JDBC connector
  6. Use the following settings:
     Connection String: <your connection string>
     JDBC JARs Folder: C:\ProgramData\Sisense\DataConnectors\jdbcdrivers\Athena
     Driver's Class Name: com.simba.athena.jdbc.Driver
     User Name: <User name of the database>
     Password: <Password for that user>
     
  7. Then click Next to move on to table selection

  Comment actions Permalink
• 

  David Peterson July 24, 2018 13:12 (Edited July 24, 2018 13:26)

  Does Sisense need the Java 8 or Java 7 jar file from Amazon?  Java does not seem to be installed on the Sisense box so difficult to say.  This page:  https://support.sisense.com/hc/en-us/community/posts/115000994027-Connecting-to-Data-using-a-JDBC-Driver?  references Athena driver 1.1 which is no longer available from AWS. I guess that was Java 6 maybe?  Only 2.0.2 for Java 7 and Java 8 jars are available.

  0

  Comment actions Permalink
• 

  Jay Dastur July 24, 2018 14:23

  Sisense is packaged with a JRE 8   C:\Program Files\Sisense\Infra\jre

  So it should be backwards compatible with Java7

  0

  Comment actions Permalink
• 

  David Peterson September 28, 2018 18:21 (Edited September 28, 2018 18:46)

  Thanks. I've been able to import tables into Sisense elasticube.  However, I just created a view in Athena but it does not appear in the Sisense JDBC import selector.  Just the tables in the list.

  0

  Comment actions Permalink
• 

  David Peterson October 01, 2018 15:55

  Using SQL workbenchJ I can certainly see the view using:

   

  show views in "database_name";

  0

  Comment actions Permalink
• 

  Jay Dastur October 01, 2018 16:05

  Hi David

  Are you using the same credentials ? The driver should load all object types. What is the name of the view ?

  There is an option to add a config file where you can manually specify any SQL. Can you open a support tickets and we can work via that channel

   

  0

  Comment actions Permalink
• 

  David Peterson October 01, 2018 17:25 (Edited October 01, 2018 18:38)

  Yes using the same credentials.  I was adding tables and expected to add the view at the same time, but it was missing.  The name is normal ascii characters with only the underscore as a special character 

  0

  Comment actions Permalink

Please sign in to leave a comment.