Connect Athena to Row Zero

Row Zero makes it easy to connect Amazon Athena to your spreadsheets with our built-in Athena connector. Follow the instructions below to connect to Athena, share your connection with others, and import data to a spreadsheet.

Create your Athena connection

Follow the instructions below to connect to Amazon Athena.

Step 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_RZ_ATHENA_RESULT_STAGING_BUCKET/.

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

Step 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 (732940336628) 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_RZ_ATHENA_RESULT_STAGING_BUCKET}"
       ],
       "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_RZ_ATHENA_RESULT_STAGING_BUCKET}/*"
       ],
       "Condition": {
           "StringEquals": {
               "aws:ResourceAccount": "{YOUR-AWS-ACCOUNT-ID}"
           }
       }
   }]
}

Here is a starting point for an Athena access policy, but it needs be updated to ensure it has access to run queries against your datasets. You should narrow the permissions in the example as appropriate.

{
   "Version": "2012-10-17",
   "Statement": [
       {
           "Sid": "BaseAthenaPermissions",
           "Effect": "Allow",
           "Action": [
               "athena:*"
           ],
           "Resource": [
               "*"
           ],
           "Condition": {
               "StringEquals": {
                   "aws:ResourceAccount": "{YOUR-AWS-ACCOUNT-ID}"
               }
           }
       },
       {
           "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": [
               "*"
           ]
       }
   ]
}

Step 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.'

Step 4: If needed, share your connection

If you would like other users to be able to use this role to query Athena from Row Zero, you can securely share the connection with them in Row Zero. This is appropriate if multiple users share a service account. To share your connection in Row Zero:

1. Navigate to your Connections page.
2. Find the Connection that you'd like to share, click '...' next to the name of the connection, and select 'Manage sharing'.
3. Enter the emails of the users or groups that should have access to this connection.

Write a query to import data from Athena

Now that your spreadsheet is connected to Athena, you can import data by writing queries directly from Row Zero.

1. From any Workbook, click 'Data', and then 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.

2. 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
   

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

4. The data imports directly into a connected table, a dynamic, condensed view that makes it easy to refresh and analyze your data. 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.

5. Once you have imported your Athena data, you can build out your connected spreadsheet, adding computed columns, pivot tables, charts, and more.