Amazon Athena

Amazon Athena is an interactive query warehouse service that makes it easy to analyze data using standard SQL.

Sprinkle Data integrates with Amazon Athena’s warehouse which is serverless. It runs on standard SQL and is built on presto. Athena provides interactive performance even for large data sets, and also at a much faster rate.

Let’s dive deep on the configuration part of Amazon Athena data warehouse in Sprinkle

Admin -> Drivers

The driver’s tab routes the user to Storage, Warehouse, and Compute tabs.

Create new warehouse -> Amazon Athena

On clicking the Create new warehouse, the screen displays a number of data warehousing options, Amazon Athena is selected in this case.

Athena Setup

If you are setting up Athena in your AWS account for the first time, you can follow the following steps

1. open Athena service on the console for the region of your interest : For example https://ap-south-1.console.aws.amazon.com/athena/home?region=ap-south-1.
2. Setup ‘Query result location’ for storing query outputs in athena. For example s3://query-results-bucket/folder/ (Don’t forget to give / (slash) at the end of path, it wont recognize the path otherwise) Note down this path further.
3. Create database using ‘create database <dbname>’ query in Query editor.

Sprinkle Driver configuration

Athena warehouse configuration on sprinkle requires following parameters to be configured.

1. Athena JDBC url : Configure url as _'jdbc:awsathena://athena.<region>.amazonaws.com:443/' _by replacing the region in which athena is setup on aws account.
2. Provide Access key and secret key for accessing athena. The Access key should have AmazonS3FullAccess and AmazonAthenaFullAccess permission/policies enabled. Detailed documentation available at https://docs.aws.amazon.com/general/latest/gr/aws-sec-cred-types.html#access-keys-and-secret-access-keys for creating keys.
3. For providing access with limited permissions refer DB level access.
4. S3 output location : This is the path of the Amazon S3 location where you want to store query results, prefixed by s3://; This is the same path given on your athena setup.
5. Schema : The name of the database to use in sprinkle, when a schema is not explicitly specified in a query.

Database Level Access in Athena

Steps to be followed

1. Create new database in Athena
2. Create IAM Policy
3. Create IAM User
4. Create Access key
5. Update Sprinkle driver

Create Athena Database

1. Login to AWS console and goto Athena.

2. Run the create database command in the query editor by updating db name.

   _CREATE DATABASE <DB_NAME> _

Create IAM Policy

1. Login to AWS console, goto IAM and select Policy from left pane.
2. Click on Creare policy and goto JSON tab.
3. Paste the following policy in the editor with updating region, database name, account number and S3 bucket name.

Access Policy

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "VisualEditor0",
            "Effect": "Allow",
            "Action": [
                "glue:GetDatabase",
                "glue:GetDatabases",
                "glue:GetPartition",
                "glue:CreateTable",
                "glue:GetTables",
                "glue:GetPartitions",
                "glue:CreateDatabase",
                "glue:UpdateTable",
                "glue:DeleteTable",
                "glue:CreatePartition",
                "glue:DeletePartition",
                "glue:UpdatePartition",
                "glue:GetTable",
                "athena:StartQueryExecution",
                "athena:GetQueryExecution",
                "athena:GetQueryResults"
            ],
            "Resource": [
              "arn:aws:glue:<REGION>:<ACCOUNT_NO>:catalog",
           "arn:aws:glue:<REGION>:<ACCOUNT_NO>:database/<DB_NAME>",
           "arn:aws:glue:<REGION>:<ACCOUNT_NO>table/<DB_NAME>/*",
           "arn:aws:athena:<REGION>:<ACCOUNT_NO>:workgroup/primary"
            ]
        },
         {
             "Effect": "Allow",
             "Action": [
                 "s3:PutObject",
                 "s3:GetObject",
                 "s3:ListBucketMultipartUploads",
                 "s3:ListBucket",
                 "s3:DeleteObject",
                 "s3:GetBucketLocation",
                 "s3:ListMultipartUploadParts"
             ],
             "Resource": [
                 "arn:aws:s3:::<BUCKET_NAME>/*",
                 "arn:aws:s3:::<BUCKET_NAME>"
             ]
         }
    ]
}

Create IAM user

1. Login to AWS console, goto IAM and select User from the left pane.
2. Click on Add User, provide User name and select access type as Programmatic Access.
3. Attach the policy created for Athena access from Attach existing policy directly.

Create Access key

1. Goto the IAM users and select the newly created user.
2. Goto Security Credentials tab and click on create access key.
3. This will create the access key. Download the csv file and click on show secret to copy the secret.