Command line tool to access Amazon Athena

Question

I am looking for a command line tool to make queries to Amazon Athena.

It works with JDBC, using the driver com.amazonaws.athena.jdbc.AthenaDriver, but I haven't found any command line tool that works with it.

Answer 1

Expanding on previous answer from @MasonWinsauer. Requires bash and jq.

    #!/bin/bash

    # Athena queries are fundamentally Asynchronous.  So we have to :
    # 1) Make the query, and tell Athena where in s3 to put the results (tell it the same place as the UI uses).
    # 2) Wait for the query to finish
    # 3) Pull down the results and un-wacky-Jsonify them.


    # run the query, use jq to capture the QueryExecutionId, and then capture that into bash variable
    queryExecutionId=$(
        aws athena start-query-execution \
        --query-string "SELECT Count(*) AS numBooks FROM books" \
        --query-execution-context "Database=demo_books" \
        --result-configuration "OutputLocation"="s3://whatever_is_in_the_athena_UI_settings" \
        --region us-east-1 | jq -r ".QueryExecutionId"
    )

    echo "queryExecutionId was ${queryExecutionId}"


    # Wait for the query to finish running.
    # This will wait for up to 60 seconds (30 * 2)
    for i in $(seq 1 30); do

        queryState=$(
            aws athena get-query-execution --query-execution-id "${queryExecutionId}"  --region us-east-1 | jq -r ".QueryExecution.Status.State"
        );

        if [[ "${queryState}" == "SUCCEEDED" ]]; then
            break;
        fi;

        echo "  Awaiting queryExecutionId ${queryExecutionId} - state was ${queryState}"

        if [[ "${queryState}" == "FAILED" ]]; then
            # exit with "bad" error code
            exit 1;
        fi;

        sleep 2
    done


    # Get the results.
    aws athena get-query-results \
        --query-execution-id "${queryExecutionId}" \
        --region us-east-1 > numberOfBooks_wacky.json

    # Todo un-wacky the json with jq or something
    # cat numberOfBooks_wacky.json | jq -r ".ResultSet.Rows[] | .Data[0].VarCharValue"

Answer 2

As of version 1.11.89, the AWS command line tool supports Amazon Athena operations.

First, you will need to attach the AmazonAthenaFullAccess policy to the IAM role of the calling user.

Then, to get started querying, you will use the start-query-execution command as follows:

aws athena start-query-execution 
    --query-string "SELECT * FROM MyDb.MyTable" 
    --result-configuration "OutputLocation"="s3://MyBucket/logs" [Optional: EncryptionConfiguration]
    --region <region>

This will return a JSON object of the QueryExecutionId, which can be used to retrieve the query results using the following command:

aws athena get-query-results
    --query-execution-id <id>
    --region <region>

Which also returns a JSON object of the results and metadata.

More information can be found in the official AWS Documentation.

Hope this helps!