Authenticating to Amazon Athena
To authorize Amazon Athena requests, provide the credentials for an administrator account or for an IAM user with custom permissions: Set AccessKey to the access key Id. Set SecretKey to the secret access key.
Note: Though you can connect as the AWS account administrator, it is recommended to use IAM user credentials to access AWS services.
Obtaining the Access Key
To obtain the credentials for an IAM user, follow the steps below:
- Sign into the IAM console.
- In the navigation pane, select Users.
- To create or manage the access keys for a user, select the user and then select the Security Credentials tab.
To obtain the credentials for your AWS root account, follow the steps below:
- Sign into the AWS Management console with the credentials for your root account.
- Select your account name or number and select My Security Credentials in the menu that is displayed.
- Click Continue to Security Credentials and expand the Access Keys section to manage or create root account access keys.
Authenticating from an EC2 Instance
If you are using the Lyftrondata Provider for Amazon Athena Lyftrondata from an EC2 Instance and have an IAM Role assigned to the instance, you can use the IAM Role to authenticate. To do so, set UseEC2Roles to true and leave AccessKey and SecretKey empty. The Lyftrondata Provider for Amazon Athena will automatically obtain your IAM Role credentials and authenticate with them.
Authenticating as an AWS Role
In many situations it may be preferable to use an IAM role for authentication instead of the direct security credentials of an AWS root user. An AWS role may be used instead by specifying the RoleARN. This will cause the Lyftrondata Provider for Amazon Athena to attempt to retrieve credentials for the specified role. If you are connecting to AWS (instead of already being connected such as on an EC2 instance), you must additionally specify the AccessKey and SecretKey of an IAM user to assume the role for. Roles may not be used when specifying the AccessKey and SecretKey of an AWS root user.
Authenticating with SSO
For users and roles that require SSO Authentication, specify the RoleARN and PrincipalArn connection properties. You must also specify the SSOProperties which is specific for each Identity Provider and leave AccessKey and SecretKey empty. This will cause the Lyftrondata Provider for Amazon Athena 2019 to submit the SSO credentials in a request to retrieve temporary authentication credentials. Note that the duration of the temporary credentials may be controlled via the TemporaryTokenDuration (default 3600 seconds).
Authenticating with MFA
For users and roles that require Multi-factor Authentication, specify the MFASerialNumber and MFAToken connection properties. This will cause the Lyftrondata Provider for Amazon Athena to submit the MFA credentials in a request to retrieve temporary authentication credentials. Note that the duration of the temporary credentials may be controlled via the TemporaryTokenDuration (default 3600 seconds).
Connecting to Amazon Athena
In addition to the AccessKey and SecretKey properties, specify Database, S3StagingDirectory and Region. Set Region to the region where your Amazon Athena data is hosted. Set S3StagingDirectory to a folder in S3 where you would like to store the results of queries.
If Database is not set in the connection, the provider connects to the default database set in Amazon Athena.
Customizing Amazon Athena Behavior
Follow the steps below to configure the provider defaults for querying Amazon Athena.
Cleaning Query Results
Amazon Athena stores the results of every query you execute in CSV files in S3StagingDirectory; these can quickly rack up a lot of space in Amazon S3. You can use CleanQueryResults, enabled by default, to clean these files for every query executed.
Note that this behavior will add a minor performance hit when you disconnect the last connection in a process.
Using Athena's Query Caching
You configure QueryCachingLevel to modify the usage of the query results stored in S3StagingDirectory; note that you have to keep the connection open to benefit from this feature. This is especially helpful when executing a certain query multiple times. This means Amazon Athena will not scan the same data again and simply use the results from the previous execution. These results are cleaned in the amount of seconds specified in QueryTolerance.
Note that failing to properly disconnect the connection when QueryCachingLevel is set to Cloud may lead to a large amount of saved queries in Athena. For most use cases setting QueryCachingLevel to Local should be enough.
Using QueryPassthrough
Amazon Athena supports a set of queries that are not specified in the regular SQL-92 standard; to execute these queries simply set QueryPassthrough to true. This will pass the query directly to Amazon Athena without parsing it internally.
Encrypting Query Results to S3
Set EncryptionKey and EncryptionType if you would like to encrypt the result set stored in S3 after query execution.
To encrypt results stored in S3, follow the steps below:
- Navigate to Amazon Athena Console.
- Click Settings.
- Enable the Encrypt Query Results option.
- Select the Encryption type and set EncryptionType when you connect.
- If you selected SSE-KMS or CSE-KMS, set both EncryptionType and EncryptionKey: select an encryption key in the menu or click Create KMS Key.
Fine Tuning Performance
You can use the PageSize property to optimize use of your provisioned throughput, based on the size of your items and Amazon Athena's 1000MB page size. Set this property to the number of items to return.
Generally, a smaller page size reduces spikes in throughput that cause throttling. A smaller page size also inserts pauses between requests. This interval evens out the distribution of requests and allows more requests to be successful by avoiding throttling.
Connecting Through a Firewall or Proxy
HTTP Proxies
To connect through the Windows system proxy, you do not need to set any additional connection properties. To connect to other proxies, set ProxyAutoDetect to false and in addition set the following.
To authenticate to an HTTP proxy, set ProxyAuthScheme, ProxyUser, and ProxyPassword, in addition to ProxyServer and ProxyPort.
Other Proxies
Set the following properties: Set FirewallType, FirewallServer, and FirewallPort. To tunnel the connection, set FirewallType to TUNNEL. To authenticate specify FirewallUser and FirewallPassword. To authenticate to a SOCKS proxy, additionally set FirewallType to SOCKS5.
Troubleshooting the Connection
To show provider activity from query execution to HTTP calls, use Logfile and Verbosity. The examples of common connection errors below show how to use these properties to get more context. Contact the support team for help tracing the source of an error or circumventing a performance issue.
- Authentication errors: Typically, recording a Logfile at Verbosity 4 is necessary to get full details on an authentication error.
- Queries time out: A server that takes too long to respond will exceed the provider's client-side timeout. Often, setting the Timeout property to a higher value will avoid a connection error. Another option is to disable the timeout by setting the property to 0. Setting Verbosity to 2 will show where the time is being spent.