Overview

You can use this Snap to retrieve data from Amazon Athena by executing SQL queries on the specified data stored in Amazon S3. The Snap supports executing SELECT statements and fetching results based on provided filters and conditions.

• Read-type Snap
• Works in Ultra Tasks

Prerequisites

None.

Known issues

• ARRAY, STRUCT, and MAP data types are not supported in SELECT queries. If used, it may result in incorrect values in the output documents.
• This Snap can fail with a connection error if the account is configured with an IAM role, and, Role ARN.
• This Snap uses the Athena JDBC driver to retrieve data. When using a Cross-account IAM role, the JDBC connection expires based on the role's duration. Large datasets requiring more time than this duration are not supported.
• CSE_KMS encryption is not supported because of an issue in the Athena JDBC driver.

  Workaround: Use the Athena Query Snap with a SELECT query.

• The second output view of the Snap cannot connect directly to the second input view of Athena Bulk Load Snap because Athena does not provide complete table metadata for all data types.
• This Snap can fail on a table created with ORC files in LZO compression because of an Athena service defect.
• This Snap displays an incorrect value of 1970-01-01T00:00:00Z for the lastAccessTime field in the table metadata returned for the second output view. This value does not reflect the actual last access time of the table.

Limitations

• This Snap does not support time travel features. If used, the Snap can produce incorrect output values or errors. For example, appending $snapshots to the Table property can result in a Table not found error when the second output view is enabled.

  Workaround: Use the Athena Query Snap for time travel queries.

Snap views

[
  {
    "dataCatalog": "AwsDataCatalog",
    "database": "mydatabase",
    "table": "athena_123",
    "createTime": "2025-03-10T22:57:54Z",
    "columns": {
      "msg": "string",
      "co_decimal": "double",
      "co_varchar": "varchar(10)",
      "co_array": "array<string>",
      "id": "int",
      "co_binary": "binary",
      "co_float": "float",
      "co_date": "date"
    },
    "partitionKeys": {
      "week": "int",
      "month": "int"
    },
    "parameters": {
      "EXTERNAL": "TRUE",
      "serde.serialization.lib": "org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSer..."
    }
  }
]

Snap settings

Field / Field set	Type	Description
Label	String	Required. Specify a unique name for the Snap. Modify this to be more appropriate, especially if more than one of the same Snaps is in the pipeline. Default value: Athena Select Example: Athena Select data
Database	String/Expression/ Suggestion	Required. Specify the name of Athena database, that is a logical grouping of Athena tables. Default value: N/A Example: mydatabase
Table	String/Expression/ Suggestion	Required. Specify the name of the table in the Athena database from which to retrieve data using the SELECT query. Additionally, the Snap supports retrieving data from Iceberg tables and views. Important: Athena Select Snap does not support time travel features. If used, the Snap can produce incorrect output values or errors. For example, appending $snapshots to the Table property can result in a Table not found error when the second output view is enabled. Workaround: Use the Athena Query Snap for time travel queries. Default value: N/A Example: Sales_db
Iceberg table metadata	Dropdown list	Choose one of the following options to query the Iceberg table metadata: None: Does not get the metadata of the iceberg table. $files: Gets the table's current data files. $manifests: Gets the table's current file manifests. $history: Gets the table's history. $partitions: Gets the table's current partitions. $snapshots: Gets the table's snapshots. $refs: Gets the table's references. Default value: None Example: $refs Note: When you select any of the above options other than None, the Snap displays the content of the associated metadata table at the first output view and does not retrieve the content of the Iceberg data table. If you need to retrieve the content of the data table, choose None. When you choose None, the following fields are displayed:
Output fields	Appears when you select None for the Iceberg table metadata. Use this field set to define the column names in the SELECT query. Tip: You can leave this field set empty to select all columns in the table.
Column	String/Expression/ Suggestion	Appears when you select None for the Iceberg table metadata. Specify the name of the column to include in the query result. Default value: N/A Example: employeeid
Where clause	String/Expression/ Suggestion	Appears when you select None for the Iceberg table metadata. Specify the SQL WHERE clause to filter the records returned by the query. Default value: N/A Example: salary > 50000
Group by	Appears when you select None for the Iceberg table metadata. Use this field set to define the GROUP BY clause.
Column	String/Expression/ Suggestion	Appears when you select None for the Iceberg table metadata. Specify the column name for the GROUP BY clause. Default value: N/A Example: Column1
Order by	Use this field set to define the ORDER BY clause.
Column	String/Expression/ Suggestion	Appears when you select None for the Iceberg table metadata. Specify the list of columns for ORDER BY clause. Default value: N/A Example: Column2
Limit offset	Integer/Expression	Appears when you select None for the Iceberg table metadata. Specify the number of rows to skip before starting to return rows in the query result. Default value: N/A Example: 1000
Limit rows	Integer/Expression	Specify the maximum number of rows to return from the query result. Default value: N/A Example: 100
Additional connection parameters	Appears when you select None for the Iceberg table metadata. Use this field set to define the connection parameters in addition to the ones provided in Amazon Athena Account.
Parameter name	String/Expression	Appears when you select None for the Iceberg table metadata. Specify the name of the parameter. Default value: N/A Example: Region
Value	String/Expression	Appears when you select None for the Iceberg table metadata. Specify the value of the parameter. Default value: N/A Example: us-west-2
Client settings	Use this field set to define the client settings.
Maximum retries	Integer/Expression	Required. Specify the maximum number of retry attempts. Default value: 3 Example: 100
Fetch size	Integer/Expression	Required. Specify the number of rows to fetch per batch when retrieving the query result. Tip: Higher value can improve performance but may use more memory. Default value: 10000 Example: 50000
Snap execution	Dropdown list	Choose one of the three modes in which the Snap executes. Available options are: Validate & Execute: Performs limited execution of the Snap and generates a data preview during pipeline validation. Subsequently, performs full execution of the Snap (unlimited records) during pipeline runtime. Execute only: Performs full execution of the Snap during pipeline execution without generating preview data. Disabled: Disables the Snap and all Snaps that are downstream from it. Default value: Validate & Execute Default value: Execute

Examples

• Query and load data in S3 using Athena
• Create and query an external table in Athena