Connect to Amazon Athena

The following steps will help you setup connection to your Athena Instance. Please ensure that you have an Amazon IAM account, and an S3 bucket.

You can follow the video below for a walkthrough of the steps, or skip ahead to follow the instructions below.

Step-by-step Instructions

Step 1: Create a Holistics User in Amazon IAM Console

Add a user with 'Programmatic Access' in Amazon IAM Console.

Allow this user full access to Athena API: AmazonAthenaFullAccess.

Or a custom policy with full access to Athena, and List, Read, Write to the source S3 bucket.

Holistics requires below actions to be allowed for all operations working properly:

• athena:StartQueryExecution
• athena:GetQueryResults
• athena:StopQueryExecution
• athena:GetQueryExecution
• athena:ListDatabases
• s3:GetBucketLocation
• s3:PutObject
• s3:GetObject
• s3:DeleteObject
• s3:AbortMultipartUpload
• s3:ListBucket
• s3:ListBucketMultipartUploads
• s3:ListMultipartUploadParts
• glue:GetDatabase
• glue:GetDatabases
• glue:GetTable
• glue:GetTables
• glue:CreateTable
• glue:DeleteTable

Example policy JSON:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "Athena",
      "Effect": "Allow",
      "Action": [
        "athena:StartQueryExecution",
        "athena:ListDatabases",
        "athena:StopQueryExecution",
        "athena:GetQueryExecution",
        "athena:GetQueryResults"
      ],
      "Resource": [
        "arn:aws:athena:ap-southeast-1:*:workgroup/primary",
        "arn:aws:athena:ap-southeast-1:*:datacatalog/AwsDataCatalog"
      ]
    },
    {
      "Sid": "ListSourceS3Buckets",
      "Effect": "Allow",
      "Action": "s3:ListBucket",
      "Resource": "arn:aws:s3:::aws-athena-source-data-bucket"
    },
    {
      "Sid": "ReadSourceS3BucketData",
      "Effect": "Allow",
      "Action": "s3:GetObject",
      "Resource": "arn:aws:s3:::aws-athena-source-data-bucket/data/*"
    },
    {
      "Sid": "WriteResultS3Bucket",
      "Effect": "Allow",
      "Action": [
        "s3:PutObject",
        "s3:GetObject",
        "s3:ListBucketMultipartUploads",
        "s3:AbortMultipartUpload",
        "s3:ListBucket",
        "s3:DeleteObject",
        "s3:GetBucketLocation",
        "s3:ListMultipartUploadParts"
      ],
      "Resource": [
        "arn:aws:s3:::aws-athena-query-results-bucket",
        "arn:aws:s3:::aws-athena-query-results-bucket/*"
      ]
    },
    {
      "Sid": "Glue",
      "Effect": "Allow",
      "Action": [
        "glue:GetDatabase",
        "glue:GetDatabases",
        "glue:CreateTable",
        "glue:DeleteTable",
        "glue:GetTables",
        "glue:GetTable"
      ],
      "Resource": "*"
    }
  ]
}

Notes:

• aws-athena-source-data-bucket is the S3 bucket where Athena queries data from
• aws-athena-query-results-bucket is the S3 bucket that will be used to store Holistics query results

Warning

This is only the example policy.
While all the Actions in the example are required, please refer to AWS (Athena) documentations to securedly allow those Actions on the appropriate Resources only.

Step 2: Add AWS Athena Data Source

Create an Athena database using AWS Athena Console.

Visit (https://secure.holistics.io/data_sources) and add a new data source. Select AWS Athena data source type, then fill in the form:

• Region: AWS region hosting Athena database, source file S3 buckets, and query result S3 bucket.
• Access Key ID and Secret Access Key are from the Amazon account you created in Step 1.
• Result Bucket: all Athena query results are stored in this bucket. The current Amazon account should be able to access this bucket. Please see AWS Athena docs for more details.
• Storage Bucket URL: This is the bucket to store your imported data and files.

Step 3: Setup your Athena Instance for Querying

First, you need to create Athena table to be used as schema-on-read during query execution (Please refer to AWS Athena docs for more details). Remember to enable Non-select Queries.

Then you can build report, view results, and make charts.

Note: In AWS Athena Schema and Data source are the same thing (https://docs.aws.amazon.com/athena/latest/ug/create-database.html). If you don't specify the schema in your query, the query will be executed on the default schema.

FAQ

Does Holistics support connecting to AWS Athena using temporary credentials?

No. Holistics does not support the ability to connect to AWS Athena using temporary credentials

ATHENA_NAME_ERROR

Sometimes you'll receive this error with pattern like this: ATHENA_NAME_ERROR: HIVE_METASTORE_ERROR: com.facebook.presto.spi.PrestoException: Error: name expected at the position ... of ... but 'special character' is found.

There is a reason for that:

• Special characters other than underscore (_) are not supported for Table, Database and columns names.
• Although you may succeed in creating table, view, database, or column names that contain special characters other than underscore by enclosing them in backtick (`) characters, subsequent DDL or DML queries that reference them can fail.
• For more details please refer to the official docs of AWS Athena here