Document toolboxDocument toolbox

[M] AWS Athena

Owned by Ben
Last updated: Mar 12, 2022 by Dean Nguyen
4 min read

AWS setup

The KADA Athena extractor requires a User with the following IAM policy OR a User that can assume a role with equivalent IAM policy.

The Athena integration is currently in beta. Some changes may occur in the near future. Please check with the KADA team prior to using this.

 

The IAM policy for the KADA Athena extractor requires the following:

  1. Permissions to execute queries against Athena INFORMATION_SCHEMA. In particular the following tables:

    1. information_schema.views

    2. information_schema.tables

    3. information_schema.columns

  2. Executing queries in Athena requires an s3 bucket to temporary store results.
    The IAM policy must also allow Read Write Listing access to objects to the bucket and the s3 Bucket policy will require the same permissions for the IAM User / Role.

  3. Permission to call the following Athena APIs

    1. list_databases

    2. list_table_metadata

    3. list_query_executions

    4. list_work_groups

    5. batch_get_query_executions

    6. start_query_execution

    7. get_query_execution

  4. The IAM policy will need permissions to access all Athena workgroups to be able to extract query logs data. Without access to the workgroups KADA can not track data usage.

    1. To limit access to workgroups See https://docs.aws.amazon.com/athena/latest/ug/workgroups-iam-policy.html

Athena reports usage at the workgroup. This means usage can not be attributed to the user that executed the query. In K Athena usage will be reported against each work group in the format “athena_workgroup_<name>”

 

Example Role Policy to allow Athena Access with least privileges for actions.

Note

  1. This policy has access to ALL Athena workgroups, due to the Resources: '*'

  2. ATHENA RESULTS BUCKET NAME needs to be changed to your bucket.

 

AWSTemplateFormatVersion: "2010-09-09" Description: 'AWS IAM Role - Athena and Cloudtrail Access to KADA' Resources: KadaAthenaRole: Type: "AWS::IAM::Role" Properties: RoleName: "KadaAthenaRole" MaxSessionDuration: 43200 Path: "/" KadaAthenaPolicy: Type: 'AWS::IAM::Policy' Properties: PolicyName: root PolicyDocument: Version: "2012-10-17" Statement: - Effect: Allow Action: - athena:BatchGetQueryExecution - athena:GetQueryExecution - athena:GetQueryResults - athena:GetQueryResultsStream - athena:ListQueryExecutions - athena:StartQueryExecution - athena:ListWorkGroups - athena:ListDataCatalogs - athena:ListDatabases - athena:ListTableMetadata Resource: '*' - Effect: Allow Action: - s3:GetBucketLocation - s3:GetObject - s3:ListBucket - s3:ListBucketMultipartUploads - s3:ListMultipartUploadParts - s3:AbortMultipartUpload - s3:PutObject - s3:PutBucketPublicAccessBlock - s3:DeleteObject Resource: - arn:aws:s3:::[ATHENA RESULTS BUCKET NAME] Roles: - !Ref KadaAthenaRole

Running the extractor

Setup a python environment Python 3.8+

KADA Support will provide the extractor on request.

Run the extractor from the kada-athena-extractor directory

To run you will need to the following inputs

  1. Onboard the Athena Source in K. Note the host name used in onboarding

  2. An AWS User access key and secret.

  3. Optionally if assuming a role. ARN of a role to assume.

  4. List of catalogs to extract from Athena. If not provided default is AwsDataCatalog

pipenv run python kada-athena-extractor.py -k <aws user key> -s <aws user seccret> -hn <k host name for athena> -b <s3 temp results location for athena> -ro <optional role to assume if needed> -c <comma seperated list of catalogs to extract, default is just AwsDataCatalog>

Full Populated Example

pipenv run python kada-athena-extractor.py -k mykey -s mysecret -hn athena -b s3://kada-athena-temp-results -ro arn:aws:iam::xxxxx:role/myrole -c AwsDataCatalog,CustomCatalog