Athena

Steps to prepare and connect to Athena

Lightup account setup

Lightup needs an IAM user account than can access the Athena data you want to include in the datasource. The following procedure helps you create the account and a policy to enable read access, and attach the policy to the new account.

  1. Create a new IAM user and an IAM access key for it, and leave Provide user access to the AWS Management Console unchecked. IMPORTANT: Anyone who connects to this Athena datasource will need two things from the access key: the Access Key ID and the Secret Access Key— you should save a copy of them so you can provide this information as needed.

  2. At Step 2 - Set permissions, select Attach policies directly, then Create policy.

  1. For Policy editor, select JSON.
  1. Use the following template to create the new policy:

❗️

Please replace these template values with your own data:

  • Replace 000000000000 with your own lightup user ID.
  • Replace any incorrect mention of us-west-2 with the correct AWS region.
  • Replace lightup-athena-staging with your actual S3 bucket.
  • Replace AwsDataCatalog with your own data catalog name.
{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "VisualEditor0",
            "Effect": "Allow",
            "Action": [
                "athena:GetTableMetadata",
                "athena:StartQueryExecution",
                "athena:GetQueryResultsStream",
                "athena:GetQueryResults",
                "athena:GetDatabase",
                "athena:GetDataCatalog",
                "athena:GetNamedQuery",
                "athena:ListTagsForResource",
                "athena:ListQueryExecutions",
                "athena:ListNamedQueries",
                "athena:GetWorkgroup",
                "athena:StopQueryExecution",
                "athena:GetQueryExecution",
                "athena:BatchGetNamedQuery",
                "athena:ListTableMetadata",
                "athena:BatchGetQueryExecution",
                "glue:GetDatabase*",
                "glue:GetDatabases",
                "glue:GetTable",
                "glue:GetTables",
                "glue:GetPartition",
                "glue:GetPartitions",
                "glue:BatchGetPartition"
            ],
            "Resource": [
                "arn:aws:glue:us-west-2:000000000000:catalog",
                "arn:aws:glue:us-west-2:000000000000:database/*",
                "arn:aws:glue:us-west-2:000000000000:table/*",
                "arn:aws:athena:us-west-2:000000000000:workgroup/primary",
                "arn:aws:athena:us-west-2:000000000000:dataCatalog/AwsDataCatalog"
            ]
        },
        {
            "Sid": "VisualEditor1",
            "Effect": "Allow",
            "Action": [
                "athena:ListDataCatalogs",
                "athena:ListWorkGroups",
                "athena:ListDatabases"
            ],
            "Resource": "*"
        },
        {
            "Effect": "Allow",
            "Action": [
                "s3:ListBucket",
                "s3:GetBucketLocation"
            ],
            "Resource": [
                "arn:aws:s3:::lightup-athena-staging"
            ]
        },
        {
            "Effect": "Allow",
            "Action": [
                "s3:PutObject",
                "s3:GetObject",
                "s3:DeleteObject"
            ],
            "Resource": [
                "arn:aws:s3:::lightup-athena-staging/*"
            ]
        }
    ]
}
  1. Name the policy athena-read-only, then select Create policy.
  2. Back on the Create User page, refresh the policy list.
  3. Select the athena-read-only policy, and then select Next.
  4. Finish the Create User dialog.

📘

Workgroup support

Lightup supports Athena workgroups, which can help you manage costs and performance. A workgroup must be in place before you connect to the Athena datasource in Lightup— you can't add a workgroup to an existing datasource.

Connect to an Athena datasource

  1. In the left pane, open a workspace menu and select Datasources.
  2. In the main page select Create Datasource +.
  1. Enter a Datasource Name, then for Connector Type select Athena.
  2. Under Configure connector, provide the following inputs:
  • Region - Specify the AWS Region where your data is hosted, e.g. "us-west-2". Read more about Athena Regions.
  • Access Key ID - Part of the access key for the lightup user. If you didn't set up the lightup user account and don't know its Access Key ID, ask your system administrator for this information.
  • Secret Access Key - Part of the access key for the lightup user. If you didn't set up the lightup user account and don't know its Secret Access Key, ask your system administrator for this information.
  • Staging Directory - Enter the S3 bucket for your Athena database.
  • Workgroup - If needed, enter a specific Athena workgroup to connect to.
  1. After entering the required settings and any optional settings that apply, below the Configure connector section select Test Connection.
  2. After a successful connection test, select Save.
  3. Your new datasource appears in the list of available datasources. By default, these are listed in alphabetical order, so you might have to scroll or change the sort order to see your new datasource.

Advanced/Schema scan frequency

You can adjust how often scans run for a datasource.

  • In section 3 - Advanced, select a value for Schema scan frequency: Hourly, Daily, or Weekly.

Query Governance

Athena datasources support the Query History, Scheduling, Enable data storage, and Maximum backfill duration, and Maximum distinct values settings. For steps, see Set query governance settings for a datasource.

Date/time data types

These Athena date/time data types are supported:

  • DATE
  • TIMESTAMP

Object types

These Athena object types are supported:

  • Tables

Partitions

Athena datasources support partitions, multi-partitions, and partition time zones.