Athena

Create a connection between Faraday and Amazon Athena so that your data is always up to date to make predictions, and your predictions can seamlessly sync back to your lake.

In this tutorial, we'll show you how to:

  • Connect your Athena account to Faraday using a connection.

Let's dive in.

  1. You'll need a Faraday Enterprise account — talk to sales to get set up.

Prerequisites

You'll need the following details to create your connection to Athena:

  • AWS account ID requiredtextTwelve-digit AWS account ID that owns the Glue catalog Faraday should query.
  • AWS region requiredtextRegion of the Glue catalog and the S3 output location, e.g. us-east-1.
  • Database requiredtextGlue/Athena database Faraday should read from and write to.
  • S3 output location requiredtexts3://bucket/prefix/ where Faraday stages extracts, query results, and target tables. Grant the role in aws_customer_role_arn read and write on this prefix.
  • Customer AWS IAM role ARN optionaltextIAM role in your AWS account that Faraday assumes to run Athena. Create the Faraday connection first, trust aws_iam_role_arn on this role, then set this field.
  • Athena workgroup optionaltextAthena workgroup in your AWS account. Defaults to primary.
  • AWS IAM role ARN optionaltextAWS IAM role Faraday generated for this connection. Create a role in your AWS account that trusts this ARN, grant that role Athena, Glue, and S3, set aws_customer_role_arn, then force-update. Unique per Faraday Athena connection.

Granting access

First, you'll need Faraday access to your Athena account.

Amazon Athena is a serverless SQL engine over data in S3, catalogued in AWS Glue. Faraday can read Glue tables (datasets) and write full-replacement tables (targets).

Athena runs in your AWS account. You pay for query compute. Faraday generates a unique IAM role for the connection (aws_iam_role_arn) and assumes a role you create in your account (aws_customer_role_arn) to submit queries. Faraday does not run Athena in Faraday's account and does not use IAM user access keys.

Please allowlist these official Faraday IP addresses:

  • 34.86.175.54
  • 34.86.252.230
  • 34.145.239.81
  • 35.245.199.181
  • 52.22.91.248
  • 52.23.137.21
  • 52.204.223.208
  • 52.204.228.32
  • 52.204.230.227

If an S3 bucket policy or Glue/Lake Formation policy restricts by IP, add those addresses.

Setup process

  1. Create your Faraday connection using the API or UI with aws_account_id, aws_region, database, and s3_output_location. Faraday generates a unique IAM role and returns aws_iam_role_arn. You can set workgroup if you do not want the default primary workgroup in your account.

  2. Copy Faraday's IAM role ARN from the connection details.

  3. Create an IAM role in your AWS account that Faraday will assume. This is the role that actually calls Athena. Replace FARADAY_IAM_ROLE_ARN with the ARN from step 2.

    Trust policy:

    {
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Principal": { "AWS": "FARADAY_IAM_ROLE_ARN" },
      "Action": "sts:AssumeRole"
    }
  ]
}

    Permissions (attach to the same role). Replace the region, account ID, and Faraday staging bucket. Duplicate the YOUR-DATA-BUCKET statements for every bucket that backs Glue tables Faraday will read. If Lake Formation is enabled, the lakeformation:GetDataAccess action is required and you must also grant this role on the catalog, database, and tables. If objects are KMS-encrypted, replace YOUR-KEY-ID.

    {
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "AthenaGlueLakeFormation",
      "Effect": "Allow",
      "Action": [
        "athena:StartQueryExecution",
        "athena:GetQueryExecution",
        "athena:GetQueryResults",
        "athena:StopQueryExecution",
        "athena:GetWorkGroup",
        "athena:GetTableMetadata",
        "athena:ListTableMetadata",
        "glue:GetDatabase",
        "glue:GetTable",
        "glue:GetTables",
        "glue:GetPartitions",
        "glue:CreateTable",
        "glue:UpdateTable",
        "glue:DeleteTable",
        "glue:BatchCreatePartition",
        "lakeformation:GetDataAccess"
      ],
      "Resource": "*"
    },
    {
      "Sid": "OutputBucketLocation",
      "Effect": "Allow",
      "Action": ["s3:GetBucketLocation"],
      "Resource": "arn:aws:s3:::YOUR-BUCKET"
    },
    {
      "Sid": "OutputPrefixList",
      "Effect": "Allow",
      "Action": ["s3:ListBucket"],
      "Resource": "arn:aws:s3:::YOUR-BUCKET",
      "Condition": {
        "StringLike": {
          "s3:prefix": ["athena", "athena/*"]
        }
      }
    },
    {
      "Sid": "OutputPrefixObjects",
      "Effect": "Allow",
      "Action": [
        "s3:GetObject",
        "s3:GetObjectVersion",
        "s3:PutObject",
        "s3:DeleteObject",
        "s3:DeleteObjectVersion"
      ],
      "Resource": "arn:aws:s3:::YOUR-BUCKET/athena/*"
    },
    {
      "Sid": "ReadGlueTableData",
      "Effect": "Allow",
      "Action": ["s3:ListBucket", "s3:GetBucketLocation"],
      "Resource": "arn:aws:s3:::YOUR-DATA-BUCKET"
    },
    {
      "Sid": "ReadGlueTableObjects",
      "Effect": "Allow",
      "Action": ["s3:GetObject", "s3:GetObjectVersion"],
      "Resource": "arn:aws:s3:::YOUR-DATA-BUCKET/*"
    },
    {
      "Sid": "KmsForEncryptedBuckets",
      "Effect": "Allow",
      "Action": ["kms:Decrypt", "kms:GenerateDataKey", "kms:DescribeKey"],
      "Resource": "arn:aws:kms:us-east-1:123456789012:key/YOUR-KEY-ID"
    }
  ]
}

  4. Set aws_customer_role_arn on the Faraday connection to that role's ARN.

  5. Create or choose a Glue database Faraday should read from and write to. A Faraday-only database is simplest.

  6. Choose an S3 prefix for Faraday staging, for example s3://faraday-acme-pwiiprz162ez/athena/. Faraday unloads extracts here, writes query results under faraday-query-results/, and writes target tables under a per-target prefix. The role from step 3 must be able to list, read, write, and delete objects in this prefix. Put an unguessable string in the bucket or prefix name.

  7. Test the connection with Force Update. The connection will error until Faraday can assume your role.

Faraday suggests that you use an unguessable string somewhere in the path to your data. This avoids what is called the Confused deputy problem

For example, let's say you were using S3. Instead of naming an S3 bucket s3://faraday-acme/, name it s3://faraday-acme-pwiiprz162ez. This guarantees that malicious actors cannot guess the name and request that Faraday import data from it into their account. The same logic applies to any path that is used to locate data.

Targets

Targets always fully replace the destination table. Faraday writes CSV to a new prefix under your s3_output_location, then updates the Glue table in place so the table name never disappears. Append and upsert are not supported. Faraday then deletes previous target prefixes.

Supported types

Faraday maps Athena scalar types (booleans, integers, floats, decimals up to precision 18, strings, dates, timestamps, uuid, json). Arrays, maps, and rows/structs are exported as JSON text. Oversized decimals above precision 18 are rejected.

Connecting

API via cURL
Dashboard

Use a POST /connections request:

curl https://api.faraday.ai/connections --json '{
  "name": "Athena",
  "options": {
    "type": "athena",
    "aws_account_id": "...",
    "aws_region": "...",
    "database": "...",
    "s3_output_location": "..."
  }
}'
  1. Wait briefly while Faraday establishes your connection. It shouldn't take long.

Your new connection is now ready to use.