Skip to main content

Athena

Amazon Athena is a serverless, interactive query service that lets users analyze data stored in Amazon S3 using standard SQL. Athena’s architecture removes the need for infrastructure management, making it efficient to query large datasets on demand for fast insights.

Prerequisites​

The following prerequisites must be met to create and test a successful connection.

  • The Athena server must be reachable from the iceDQ server.
  • Valid credentials to access Athena.
  • Add the athena:GetQueryResultsStream policy action to the IAM principals. Refer Amazon Athena Policies.

Connection Properties​

AWS Custom Credential Authentication​

Use one or more properties from the below table to create a valid connection. Properties marked with an asterisk (*) are required.

NameDescriptionExample Values
Connection Name *          Name that uniquely identifies the connection.Athena_Sales_Conn
Driver *Driver used to establish the connection. One driver is available by default.Athena Native JDBC
Custom JDBC URL *Standardized string used to define the connection details. Use this format supported by the driver: jdbc:awsathena://AwsRegion=[region];S3OutputLocation=[s3-location];jdbc:awsathena://AwsRegion=us-west-2;S3OutputLocation=s3://user-bucket/query-results/;
S3 Output Location *Amazon S3 path where query results are stored. Must be accessible by Athena.s3://user-bucket/query-results/
Region *AWS region where the Athena instance is hosted.us-west-2
Type *The connection type – either System Connection or User Connection. Refer to Connections for more details.System or User
Access Key *AWS access key ID used for authentication.AKIAIOSFODNN7EXAMPLE
Secret Key *AWS secret access key associated with the access key ID.wJalrXUtnFEMRfiCYEdzfbh
Session Token *Temporary token used in case of session-based authentication. Optional unless using temporary credentials.FQoGZXIvYEjU3mYrJz1tG2
note

The Access & Secret keys must be updated whenever the session token expires.

AWS EC2 Role Authentication​

Use one or more properties from the below table to create a valid connection. Properties marked with an asterisk (*) are required.

NameDescriptionExample Values
Connection Name *          Name that uniquely identifies the connection.Prod_Athena_Conn
Driver *Driver used to establish the connection. One driver is available by default.Athena Native JDBC
Custom JDBC URL *Standardized string used to define the connection details. Use this format supported by the driver: jdbc:awsathena://AwsRegion=[region];S3OutputLocation=[s3-location];jdbc:awsathena://AwsRegion=us-west-2;S3OutputLocation=s3://user-bucket/query-results/;
S3 Output Location *Amazon S3 path where query results are stored. Must be accessible by Athena.s3://user-bucket/query-results/
Region *AWS region where the Athena instance is hosted.us-west-2
Type *EC2 role authentication supports only System Connections. Refer to Connections for more details.System or User

AWS IAM Authentication​

Use one or more properties from the below table to create a valid connection. Properties marked with an asterisk (*) are required.

NameDescriptionExample Values
Connection Name *     Name that uniquely identifies the connection.Athena_Dev_Conn
Driver *Driver used to establish the connection. One driver is available by default.Athena Native JDBC
S3 Output Location *Amazon S3 path where query results are stored. Must be accessible by Athena.s3://user-bucket/query-results/
Region *AWS region where the Athena instance is hosted.us-west-2
Type *The connection type – either System Connection or User Connection. Refer to Connections for more details.System or User
Access Key *AWS access key ID used for authentication.AKIAIOSFODNN7EXAMPLE
Secret Key *AWS secret access key associated with the access key ID.wJalrXUtnFEMRfiCYEdzfbh

Custom Properties​

The following optional connection properties can be configured based on user requirements.

PropertyDefault ValuePossible ValuesDescription
ApplicationName(none)StringThe name of the application used by Athena.
AwsCredentialsProviderClass(none)Fully qualified class nameSpecifies the AwsCredentialsProvider implementation to authenticate with AWS.
AwsCredentialsProviderArguments(none)Comma-separated stringsArguments passed to the AwsCredentialsProvider constructor.
AwsRegion(none)String (AWS region)The AWS region where your Athena and AWS Glue instances reside.
BinaryColumnLength32767IntegerMax length for BINARY columns.
ComplexTypeColumnLength65535IntegerMax length for ARRAY, MAP, STRUCT columns.
ConnectionTest10 or 1Whether to verify connection using a “SELECT 1” query (1 = yes, 0 = no).
ConnectTimeout10Integer (seconds)Time to wait when establishing connection before timing out (0 = no timeout).
EndpointOverride(none)String (URL)Custom endpoint for Athena, if not using the default region endpoint.
LogLevelOFF (v3), or 0 (v2)OFF, ERROR, WARN, INFO, DEBUG, TRACELevel of logging detail in the connector.
LogPath(current working dir)String (file path)Path to folder where log files are saved.
MaxErrorRetry10IntegerMax retry attempts for failed requests (e.g., 5xx errors).
ProxyHost(none)StringHostname or IP of proxy server.
ProxyPort(none)IntegerPort for proxy server.
S3OutputLocation(required)String (S3 URI)S3 location (s3://...) to store query results.
S3OutputEncOption(none)e.g., AES256Encryption protocol for results stored in S3.
Web_identity_token(none)String (token)OAuth 2.0 or OpenID Connect token from identity provider.
Workgroup(none)StringThe Athena workgroup to use for the session.
CatalogAwsDataCatalogString (catalog name)Default Athena catalog for queries without explicit catalog reference.

Supported Datatypes​

The following data types are supported.

  • BOOLEAN
  • TINYINT
  • SMALLINT
  • INTEGER / INT
  • BIGINT
  • DOUBLE
  • FLOAT
  • DECIMAL
  • VARCHAR
  • STRING
  • DATE
  • TIMESTAMP
  • ARRAY
  • MAP
  • STRUCT

Unsupported Datatypes​

The following data types are not supported.

  • BINARY
  • VARBINARY
Last updated on