bi

Ad-hoc querying on AWS: Connecting BI tools to Athena

Gregory Golberg mz , , , , , , , , , , ,

In a previous post, we discussed using Lambda, Glue and Athena to set up queries of events that are logged by our real-time bidding system. Here, we will build on that foundation, and show how to make this even friendlier to business users by connecting BI tools to this setup.

Luckily, Athena supports both JDBC and ODBC, and, thus, any BI tool that uses either of these connection methods can use Athena!

First, we need to create an IAM user. The the minimum policies required are:

  • AmazonAthenaFullAccess
  • Writing to a bucket for Athena query output (use an existing one or create a new one). For the sake of example, let’s call it s3://athena.out
  • Reading from our s3://logbucket which is where the logs are in

Now we’ll need the access and the secret keys for that user to use it with various tools. 

JDBC

JDBC driver (com.simba.athena.jdbc.Driver) can be downloaded here.

The JDBC URL is constructed as follows: 

jdbc:awsathena://User=<aws-access-key>;Password=<aws-secret-key>;S3OutputLocation=s3://athena.out;

Here’s a sample Java program that shows it in action:

import java.sql.Connection;
import java.sql.DriverManager;
 
public class Main {
  public static void main(String[] args) throws Throwable {
    Class.forName("com.simba.athena.jdbc.Driver");
    String accessKey = "...";
    String secretKey = "...";
    String bucket = "athena.out";
    String url = "jdbc:awsathena://AwsRegion=us-east-1;User=" + accessKey + ";Password=" + secretKey
        + ";S3OutputLocation=s3://" + bucket +”;";
    Connection connection = DriverManager.getConnection(url);
    System.out.println("Successfully connected to\n\t" + url);
  }
}

Example using JDBC: DbVisualizer

  1. If you haven’t already, download the JDBC driver to some folder.
  2. Open Driver Manager (Tools-Driver Manager)
  1. Press green + to create new driver
  1. Press the folder icon on the right …
  1. … and browse to the folder where you saved the JDBC driver and select it:
  1. Leave the URL Format field blank and pick com.simba.athena.jdbc.Driver for Driver Class:
  1. Close the Driver Manager, and let’s create a Connection:
  1. We’ll use “No Wizard” option. Pick Athena from the dropdown in the Driver (JDBC) field and enter the JDBC URL from above in the Database URL field:
  2. Press “Connect” and observe DbVisualizer read the metadata information from Athena (well, Glue, really), including tables and views.

ODBC (on OSX)

  1. Download run ODBC driver installer
  2. Create or edit /Library/ODBC/odbcinst.ini to add the following information:
    [ODBC Drivers]
    Simba Athena ODBC Driver=Installed
    [Simba Athena ODBC Driver]
    Driver = /Library/simba/athenaodbc/lib/libathenaodbc_sbu.dylib

    If the odbcinst.ini file already has entries, put new entries into the appropriate sections; e.g., if it was
    [ODBC Drivers]
    PostgreSQL Unicode = Installed
    [PostgreSQL Unicode]
    Description = PostgreSQL ODBC driver
    Driver = /usr/local/lib/psqlodbcw.so

    Then it becomes
     [ODBC Drivers]
     PostgreSQL Unicode = Installed
     Simba Athena ODBC Driver=Installed
     [PostgreSQL Unicode]
     Description = PostgreSQL ODBC driver
     Driver = /usr/local/lib/psqlodbcw.so
     [Simba Athena ODBC Driver]
     Driver = /Library/simba/athenaodbc/lib/libathenaodbc_sbu.dylib
  3. Create or edit, in a similar fashion, /Library/ODBC/odbc.ini to include the following information: 
    [AthenaDSN]
    Driver=/Library/simba/athenaodbc/lib/libathenaodbc_sbu.dylib
    AwsRegion=us-east-1
    S3OutputLocation=s3://athena.out
    AuthenticationType=IAM Credentials
    UID=AWS_ACCESS_KEY
    PWD=AWS_SECRET_KEY
  4. If you wish to test, download and run ODBC Manager. You should see that it successfully recognizes the DSN:

Example using ODBC: Excel

  1. Switch to Data tab, and under New Database Query select From Database:
  1. In the iODBC Data Source Chooser window, select AthenaDSN we configured above and hit OK. 
  1. Annoyingly, despite having configured it, you will be asked for credentials again. Enter the access and secret key.
  2. You should see a Microsoft Query window. 

Success!

Helpful links

Reporting: you’re doing it wrong

Gregory Golberg holding forth , , , , , , , , , , ,

I’ve often said that there are certain things average application programmers just do not get. And those are:

  • Calendars and timezones
  • Character encodings, Unicode, etc.
  • L10n & I18n in general
  • Relational databases
  • Networking (as in OSI)
  • Operating systems

And by “do not get” I do not mean “are not experts in”. I mean, they don’t know what they don’t know. Time and time again I see evidence of this. Recently I saw one that was so bad it was good — and that, I think, necessitates a meta-like amendment to this list, in the spirit of Dunning-Kruger. As in:

You are probably not the first person to have this problem. It is very likely that smarter (yes, snowflake) people already solved this problem in some tool or library that has endured for years, if not decades. USE IT!

Here is the incident, worthy of The Daily WTF.

There is a monthly report that business runs (it doesn’t really matter what kind of report — some numbers and dollars and stuff). How is the report being generated? (Leaving aside for now the question of why one would roll one own‘s reports, rather than using a ready-made BI/reporting solution. Using the brilliant algorithm that Donald Knuth forever regrets not including in his TAO series:

  1. Set current_time to the chosen start date, at midnight.
  2. Output the relevant data from day specified by current_time.
  3. If current_time is greater than the chosen end date, exit.
  4. Increment current_time by 86400 (because 86400 is a universal constant).

What could possibly go wrong?

Nothing. Except when you hit the “fall back” time (end of DST). Once you go past that date, the system will subtract an hour, and you end up at 11pm the previous day, not midnight of the day you wanted. And because you’re stupid, you have no idea why all your business users are waiting for those reports forever.