Connect Amazon Athena

An Amazon Athena source reads tables from Athena into the Lake. Its form has three steps, General, Connection and Review: sign in with an IAM user key, choose the region and the output location, and check the datetime settings before you save.

Add an Athena source

1. Open Lake and click Add datasource in the header, or open the New menu in the toolbar above the tree.
2. Choose Amazon Athena in the picker. The form opens on the General step. Supported connectors shows how the picker is grouped.
3. On General, enter Name and Code, and choose a Folder if you want one, then click Next. The step is the same for every source; see Add a data source.
4. On Connection, fill in the AWS credentials and Query results fields described below. A Test connection card sits at the foot of the step; the test is optional.
5. On Review, check the summary and the Source timezone: row, then click Add.

Once the source is saved, import its tables.

Sign in with an AWS key

The AWS credentials card takes the IAM user key the source signs in with:

• AWS Access Key ID: the key's ID; the placeholder reads AKIA….
• AWS Secret Access Key: the secret access key that belongs to the ID.

The card notes: "An IAM user key with Athena, Glue Data Catalog and S3 access to the data and the output location."

After you save, the form never shows the secret again: the field keeps a stored value with Replace beside it. If your administrator turned on re-entering connector secrets and you change AWS Access Key ID, the form asks for the secret access key again. Stored secrets and the test card behave as on the other connector forms; Connect a SQL database describes both.

Choose the region and the output location

The Query results card sets where the queries run:

• Region is required. Search the picker by region code or city (Search region code or city…) and pick an option such as us-east-1 · US East (N. Virginia). Before you pick, the field reads Select the AWS region; a search with no match reads No matching region.
• Output location is required. Enter an S3 location such as s3://my-bucket/athena-results/. The field notes: "Athena writes query results here. A trailing / is added if missing."

If a saved source uses a region that is not in the picker's list, the form keeps it as the selected option, so an edit never drops it.

The form names each required field you leave empty:

• AWS access key ID is required
• AWS secret access key is required
• Region is required
• Output location is required

An output location that is not an S3 location is refused with Use an S3 location such as s3://my-bucket/athena-results/. The address must start with s3://, name a bucket and contain no spaces.

Check the review before you save

The Review step shows the summary of the new source: Name, Access key ID, Region and Output location. A field you left empty reads as -; the secret access key is never shown.

The Source timezone: row starts collapsed. A new Athena source starts with the timezone set to UTC and keeps datetime values as written, so the row reads UTC (Athena always runs in UTC). Click Change to open the Source datetime settings and pick another timezone or change how datetime values are read. Timezones and datetime values explains both settings and the rules that always apply.

The submit button reads Add for a new source, Save in edit mode and Duplicate for a copy.

What a load runs

When a load runs, DataMind runs a query in Athena and loads the rows the query returns. The query reads the columns you synced for the table.

If the table has a WHERE clause for sync, the clause is applied inside that query, so Athena filters the rows on the table's raw column values before they are read out. Set the clause on the table's Conditions step; Configure a table describes it.

A load can stop with one of these messages:

• Invalid query pattern detected: the query the load would run ends with a semicolon or contains INFORMATION_SCHEMA, and either one is refused. Rework the query without them.
• Table not found: the load cannot find the table it was set up for, and the message continues with the table's database, schema and table name. Check that the table still exists in the source, run Sync Schema on the datasource, and run the load again.

See Run, stop and troubleshoot loads for the Sync Log and for Sync Schema, and Lake troubleshooting for the messages you may see while you connect.

Questions, answered

Which AWS key does the connection need?

An IAM user key with Athena, Glue Data Catalog and S3 access to the data and the output location. Enter its AWS Access Key ID and AWS Secret Access Key on the Connection step.

What does the Output location do?

Athena writes query results there. Enter an S3 location such as s3://my-bucket/athena-results/; a trailing / is added if it is missing.

Why was my Output location rejected?

The field accepts an S3 location only: it must start with s3://, name a bucket and contain no spaces. Anything else shows Use an S3 location such as s3://my-bucket/athena-results/.

The saved source uses a region that is not in the picker. Is it kept?

Yes. When a saved region is not among the listed AWS regions, the form keeps it as the selected option, so an edit never drops it.

Why does the review show UTC (Athena always runs in UTC)?

Athena runs its queries in UTC and a new source keeps datetime values as written, so the Source timezone: row shows that. Click Change to pick another timezone or change how values are read; see Timezones and datetime values.

What runs when I load an Athena table?

A query in Athena: DataMind runs it, reads the columns you synced, and loads the rows the query returns.

Where does the WHERE clause for sync apply for Athena?

Inside the query itself, on the table's raw column values, so Athena filters the rows before they are read out. Set the clause on the table's Conditions step (Configure a table).

Why did a load fail with Invalid query pattern detected?

The query the load would run ends with a semicolon or contains INFORMATION_SCHEMA, and either one is refused. Rework the query without them and run the load again.

What does Table not found mean?

The load cannot find the table it was set up for; the message continues with the table's database, schema and table name. Check that the table still exists in the source, run Sync Schema on the datasource, and run the load again.

What opens when I edit a saved Athena source?

The form opens on the Connection step, Name and Code are locked, and the submit button reads Save. Duplicate starts a copy with Name and Code empty.