Connect Your Spreadsheet to Athena

How to Connect Athena to Row Zero

There are three steps to connect Athena to Row Zero:

1. Create an AWS bucket for Athena to write results which Row Zero will read.

You need to create a bucket in the same account and region that your Athena is running in. This bucket must take the naming pattern s3://YOUR_COMPANY-rowzero-datastage-REGION/. Region follows the AWS region naming convention (like us-west-2).

Only temporary data goes into this bucket, so we recommend creating a lifecycle policy to delete all data from this bucket after 1 day.

2. Create an AWS role that can execute queries against Athena tables (and write results to the bucket above).

Create a role that Row Zero will use to execute Athena queries on your behalf and read the results. The role must have a trust policy that allows the Row Zero account () to use the role, using the connection specific External ID that is provided when you go to create an Athena connection (see step 3 below).

This role must have permissions to use Athena and permissions to read the results. You can choose to use predefined permissions to Athena/S3 or attach inline policies.

Here's an example inline policy for reading results:

{
    "Version": "2012-10-17",
    "Statement": [{
        "Effect": "Allow",
        "Action": [
            "s3:Get*",
            "s3:List*",
            "s3:Describe*",
            "s3:PutLifecycleConfiguration",
            "s3:PutBucketVersioning"
        ],
        "Resource": [
            "arn:aws:s3:::{YOUR_COMPANY}-rowzero-datastage-*"
        ],
        "Condition": {
            "StringEquals": {
                "aws:ResourceAccount": "{YOUR_AWS_ACCOUNT_ID}"
            }
        }
    },
    {
        "Effect": "Allow",
        "Action": [
            "s3:Get*",
            "s3:List*",
            "s3:Describe*",
            "s3:AbortMultipartUpload",
            "s3:PutObject",
            "s3:DeleteObject"
        ],
        "Resource": [
            "arn:aws:s3:::{YOUR_COMPANY}-rowzero-datastage-*/*"
        ],
        "Condition": {
            "StringEquals": {
                "aws:ResourceAccount": "{YOUR-AWS-ACCOUNT-ID}"
            }
        }
    }]
}

Here is an example policy for Athena access (but this likely needs to be updated to ensure it has access to run queries against your datasets).

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "BaseAthenaPermissions",
            "Effect": "Allow",
            "Action": [
                "athena:*"
            ],
            "Resource": [
                "*"
            ]
        },
        {
            "Sid": "BaseGluePermissions",
            "Effect": "Allow",
            "Action": [
                "glue:GetCatalog",
                "glue:GetCatalogs",
                "glue:GetDatabase",
                "glue:GetDatabases",
                "glue:GetTable",
                "glue:GetTables",
                "glue:GetPartition",
                "glue:GetPartitions",
                "glue:BatchGetPartition",
                "glue:StartColumnStatisticsTaskRun",
                "glue:GetColumnStatisticsTaskRun",
                "glue:GetColumnStatisticsTaskRuns",
                "glue:GetCatalogImportStatus"
            ],
            "Resource": [
                "*"
            ]
        }
    ]
}

3. Create the connection in Row Zero that will use the role created above.

To connect a new connection in Row Zero, open up a new workbook, click the Data button in the top right, and click 'Add connection':

Select Athena as your connection type and fill in the connection details corresponding to the steps above. Note that the External ID you see here is what you should use in step 2 above.

Once all the information is entered, hit 'Test connection' to ensure the information is correct. If the test returns green, you're good to go and can click 'Create connection.'

Write a query to import data from Athena

Now that Athena is connected to your spreadsheet, click the "+" sign next to your Athena connection to insert a connected table.This adds a data table to your spreadsheet that is connected to Athena and opens a query editor. Select the 'Data Source' and 'Database' from the two drop downs and then write a SQL query. The easiest query to write is a 'select *' statement, which pulls in the entire table. For example:

select * from table_name

Click 'Run' to execute the query and import the queried Athena data into your spreadsheet.

The data imports directly into a connected table in the spreadsheet, which provides a dynamic, condensed view of the data and makes it easy to filter, sort, format, pivot, chart, etc. You can also add calculated columns and reference the data in formula functions throughout your workbook.Double-click on the connected table to re-run your query for the latest data or schedule auto-refresh and everything built on connected data automatically updates and stays in sync.Here is more info on how to build connected spreadsheets.