Amazon Athena
User Guide  | API Reference

Using Athena with SQL Workbench

Follow these instructions as a general guideline for how to use Athena with SQL Workbench with the JDBC driver.

Before You Begin#

This tutorial assumes that:

  • You have downloaded and installed SQL Workbench for your operating system.
  • You have downloaded and installed the Athena JDBC driver and placed it in the SQL Workbench directory.
  • You have set up Athena according to Setting Up.

Configure SQL Workbench to use the Athena JDBC Driver#

  1. Open SQL Workbench.

  2. Configure an Athena JDBC driver by choosing File, Manage Drivers....

  3. For Name, type a name, such as "Athena JDBC Driver".

  4. For Library, type the path for the location to which you downloaded your driver. For example, on a Linux machine, this might look like: /usr/local/SqlWorkBench-121/AthenaJDBC41-1.0.0.jar.

  5. For Classname, enter the full class name: com.amazonaws.athena.jdbc.AthenaDriver.

  6. For Sample URL, enter the URL, replacing {REGION} with your desired region (for example, us-west-2). For more information about supported regions, see AWS Regions and Endpoints.

    jdbc:awsathena://athena.REGION.amazonaws.com:443

  7. Choose OK.

    _images/workbenchdriver.png
  8. Set up a connection by choosing File, Connect window.

  9. Create a new connection profile and call it "Athena".

  10. Under Driver, select the Athena driver (com.amazonaws.athena.jdbc.AthenaDriver).

  11. For URL, enter the connection string. For example, in us-west-2, this would be jdbc:awsathena://athena.us-west-2.amazonaws.com:443/.

  12. For Username and Password, enter your AWS access key and secret key, respectively.

  13. Under Extended Properties, enter a desired value for s3_staging_dir that is in the same region where you are working, and then choose OK. This setting is necessary to place the query results in Amazon S3 so you can download them locally using the JDBC driver. For more information, see Query Results. You can leave other JDBC driver options at their default values. For a list of options, see Configure the JDBC Driver Options.

  14. Choose OK.

    _images/connectwindow.png

Query Data with SQL Workbench#

In the Statement window, you can enter a series of queries on your data. You can also use a CREATE statement to add new tables. The JDBC driver uses the default database but you can also create databases and use them. In this case, to distinguish between tables in the default and custom databases, use the database identifier as a namespace prefix to your table name when writing your queries.

  1. Create a table in the default database, as in this example:

    CREATE EXTERNAL TABLE IF NOT EXISTS cloudfront_logs (
       `Date` DATE,
       Time STRING,
       Location STRING,
       Bytes INT,
       RequestIP STRING,
       Method STRING,
       Host STRING,
       Uri STRING,
       Status INT,
       Referrer STRING,
       os STRING,
       Browser STRING,
       BrowserVersion STRING
       ) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
       WITH SERDEPROPERTIES (
       "input.regex" = "^(?!#)([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+[^\(]+[\(]([^\;]+).*\%20([^\/]+)[\/](.*)$"
       ) LOCATION 's3://athena-examples/cloudfront/plaintext/';
    
  2. Choose Execute.

  3. Run a simple query such as SELECT DISTINCT os FROM cloudfront_logs, and view the results.

Explore Data#

For a more interactive way to view your Athena data, use the Database Explorer tab.

  1. On the Database Explorer tab, select the default schema (database).

  2. Select the cloudfront_logs table. This loads the Columns tab, which shows the table schema.

    _images/workbenchcolumns.png
  3. Open other tabs. Two other tabs are of immediate interest:

  • SQL Source shows an output similar to the following:

    _images/workbenchsqlsource.png
  • Data shows a list of rows returned from your table. It may take time to load the data.

    _images/workbenchdata.png