Working with Athena Connection

✅ Enterprise: AWS, Azure, GCP, and On-Premises

✅ Marketplace: AWS, Azure, and GCP

✅ SaaS: AWS, Azure, and GCP

✅ Single Node Installation (SNI)

Overview

Kyvos supports Amazon Athena Service to run interactive ad hoc SQL queries against data on Amazon S3, without having to manage any infrastructure or cluster. Athena is an interactive query service that makes it easy to analyze data directly in Amazon S3 using standard SQL. Athena scales automatically, running queries in parallel, so results are fast, even with large datasets and complex queries.

Note

From Kyvos 2024.10 onwards, Athena connection for no-Spark is supported.
You can create multiple Athena connections for raw data querying. The connections are available on the Semantic model designer page, where you can select the connection to be used for a particular semantic model.
See the Working with non-materialized or raw data semantic models section for more details.

On this page:

1 Overview
2 Prerequisites
3 Creating an Athena connection
4 Post step after creating Athena connection
5 Cross-Account Athena Access Setup

Prerequisites

The permissions required to run Athena queries include the following:

Amazon S3 locations where the underlying data to query is stored.
Metadata and resources that you store in the AWS Glue Data Catalog, such as databases and tables
Athena API actions

Creating an Athena connection

To set up or view the AthenaSQL connection, perform the following steps.

From the Toolbox, click Setup, then Connections.
From the Actions menu ( ⋮ ) click Add Connection.
Enter a Name or select it from the Connection list.
After you finish configuring the settings using the table shown below, click the Test button from the top left to validate the connection settings.
If the connection is valid, click the Save button.

Post step after creating Athena connection

Note

These following permissions must be attached as without these permissions, Athena will not process semantic model on EKS or K8s cluster.
If an Athena connection is with Shared Query Engine, no permission is needed.

Following set of permissions for Athena must be added on AWS>EKS/K8s cluster> node group IAM role.


{
 "Version": "2012-10-17",
 "Statement": [
 {
 "Sid": "Statement1",
 "Effect": "Allow",
 "Action": [
 "athena:StartQueryExecution",
 "athena:GetQueryResultsStream",
 "athena:StopQueryExecution",
 "athena:GetQueryExecution",
 "athena:GetQueryResults",
 "athena:GetDataCatalog",
 "athena:ListDataCatalogs",
 "athena:ListWorkGroups",
 "athena:GetDatabase",
 "glue:GetDatabase",
 "glue:GetDatabases",
 "glue:GetTable",
 "glue:GetPartitions",
 "glue:GetTables",
 "glue:GetUserDefinedFunctions",
 "glue:GetSchema",
 "athena:ListDatabases",
 "athena:ListTableMetadata",
 "athena:GetTableMetadata",
 "athena:GetNamedQuery",
 "athena:ListQueryExecutions",
 "athena:ListNamedQueries",
 "athena:CreateNamedQuery",
 "athena:GetWorkGroup",
 "s3:GetBucketLocation",
 "s3:GetObject",
 "s3:ListBucket"
 ],
 "Resource": [
 "*"
 ]
 }
 ]
}

Cross-Account Athena Access Setup

From Kyvos 2026.5 onwards, support is available for the IAM – Role ARN authentication type. This enables cross-accounts data connectivity with multiple Athena accounts.

Architecture Overview

Account A: AWS account where Kyvos is deployed
Account B: AWS account where Amazon Athena resides

Use this configuration guide when Kyvos is running in Account A to achieve the following workflow:

Execute Athena queries in Account B.
Store Athena query results in an Amazon S3 bucket located back in:
- Account A or
- Account B (refer to section Enabling Data Export is User Account below for details).

To configure cross-account Athena access, perform the following steps:

Account B Setup

In the AWS account hosting Athena (Account B), create a new IAM role with the following configuration:
- Role Name: For this example we're using the name AthenaDevCrossAccRole
- Service: Custom trust policy
Configure Trust Relationship, replacing<ACCOUNT A EC2 Role ARN> with the IAM role ARN associated with the Kyvos EC2 instance in Account A:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Principal": {
        "AWS": "<ACCOUNT A EC2 Role ARN>"
      },
      "Action": "sts:AssumeRole"
    }
  ]
}

Add an inline policy to the IAM role created in the previous step. Use the JSON below, replacing the following placeholder before applying the policy:
- <Account A Output Directory Bucket>

{
	"Version": "2012-10-17",
	"Statement": [
		{
			"Effect": "Allow",
			"Action": [
				"athena:*"
			],
			"Resource": "*"
		},
		{
			"Effect": "Allow",
			"Action": [
				"glue:GetDatabase",
				"glue:GetTable",
				"glue:GetPartitions",
				"glue:GetDatabases",
				"glue:GetTables"
			],
			"Resource": "*"
		},
		{
			"Effect": "Allow",
			"Action": [
				"s3:GetObject",
				"s3:ListBucket",
				"s3:PutObject",
				"s3:GetBucketLocation"
			],
			"Resource": [
				"arn:aws:s3:::*",
				"arn:aws:s3:::*/*"
			]
		},
		{
			"Effect": "Allow",
			"Action": [
				"s3:GetBucketLocation",
				"s3:ListBucket"
			],
			"Resource": "arn:aws:s3:::<Account A Output Directory Bucket>"
		},
		{
			"Effect": "Allow",
			"Action": [
				"s3:PutObject",
				"s3:GetObject",
				"s3:AbortMultipartUpload"
			],
			"Resource": "arn:aws:s3:::<Account A Output Directory Bucket>/*"
		},
		{
			"Effect": "Allow",
			"Action": [
				"s3:ListBucketMultipartUploads"
			],
			"Resource": "arn:aws:s3:::<Account A Output Directory Bucket>"
		}
	]
}

Account A Setup

In Account A, Update your EC2 IAM Role. Create and attach the following inline policy replacing <Account B role ARN created in previous step>:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": ["sts:AssumeRole"],
      "Resource": "<Account B role ARN created in previous step>"
    }
  ]
}

In case using Account A bucket as output directory, configure Output S3 Bucket Policy with the details below. Replace the following placeholders before applying the policy:
- <Account B role ARN created in previous step>
- <Account A Output Directory Bucket>

Note

To use an Account B S3 bucket as the output directory, follow the steps outlined in the section Enabling Data Export is User Account below.

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "AllowAthenaBucketAccess",
      "Effect": "Allow",
      "Principal": {
        "AWS": "<Account B role ARN created in previous step>"
      },
      "Action": [
        "s3:ListBucket",
        "s3:GetBucketLocation",
        "s3:ListBucketMultipartUploads"
      ],
      "Resource": "arn:aws:s3:::<Account A Output Directory Bucket>"
    },
    {
      "Sid": "AllowAthenaObjectAccess",
      "Effect": "Allow",
      "Principal": {
        "AWS": "<Account B role ARN created in previous step>"
      },
      "Action": [
        "s3:PutObject",
        "s3:GetObject",
        "s3:AbortMultipartUpload"
      ],
      "Resource": "arn:aws:s3:::<Account A Output Directory Bucket>/*"
    }
  ]
}

After completing the configuration, create an Athena connection in Kyvos using the IAM – Role ARN authentication type. Run a test query to verify cross-account Athena access is working correctly.

Enabling Athena Data Export in User Account

To enable and support Athena data export in a user’s account, perform the following steps:

Add the below property at the Athena connection level. Ensure that the export folder is created inside the customer bucket.

kyvos.connection.data.export.dir = s3://<CUSTOMER_BUCKET>/user2/engine_work2/

Update the Customer Account B bucket policy. Grant access to the Kyvos Account A EC2 IAM role by adding the following bucket policy:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Principal": {
                "AWS": "<ACCOUNT_A_EC2_ROLE_ARN>"
            },
            "Action": "s3:ListBucket",
            "Resource": "arn:aws:s3:::<CUSTOMER_BUCKET>"
        },
        {
            "Effect": "Allow",
            "Principal": {
                "AWS": "<ACCOUNT_A_EC2_ROLE_ARN>"
            },
            "Action": [
                "s3:GetObject",
                "s3:PutObject",
                "s3:DeleteObject"
            ],
            "Resource": "arn:aws:s3:::<CUSTOMER_BUCKET>/*"
        }
    ]
}

Update the Account A EC2 IAM role policy. Add the following permissions to the Account A EC2 IAM role for accessing the customer bucket:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "s3:ListBucket",
                "s3:GetBucketLocation"
            ],
            "Resource": "arn:aws:s3:::<CUSTOMER_BUCKET>"
        },
        {
            "Effect": "Allow",
            "Action": [
                "s3:GetObject",
                "s3:PutObject",
                "s3:DeleteObject"
            ],
            "Resource": "arn:aws:s3:::<CUSTOMER_BUCKET>/*"
        }
    ]
}

Note

For optimal performance and to prevent connection validation issues, users are recommended to create a new workbook in the development account. This can be done by running the following command on a Kyvos cluster node:

aws athena create-work-group \
--name pauldev(name of the cross account athena work-group) \
--configuration '{
"ResultConfiguration": {
"OutputLocation": "s3://<Account A Output Directory Bucket>/athena_query_output/"
},
"EnforceWorkGroupConfiguration": true,
"PublishCloudWatchMetricsEnabled": false
}'