Overview
Configuration is provided for establishing connections with Amazon Athena service. The configuration is used in the REST Connection Manager.
Setup
Press 
icon to get more information about the connection parameters.
Obtain data
Use REST Source component to get data from the service resource.
Insert data
Use REST Destination component to insert data into service resource.
Quick Start
In this guide, we will show you step-by-step how to create a connection to Amazon Athena REST API using COZYROC's REST Connection Manager.
Step 1. Run "Visual Studio" under "Administrator account". Right click on Connection managers Tab and Select New Connection. In Add SSIS Connection Manager scroll down and select Connection managers for REST Connection and click Add.
Step 2. From drop-down menu Configuration select Amazon Athena.
Step 3. Set [Region] to the desired Region in the Server host parameter. Then provide access key and access secret in the grid below for the Access Key and Access Secret parameters
Step 4. Click "Test Connection" to test your connection.
Congratulations! You have now established a connection to your Amazon Athena instance.
In this guide, we will show how to read data from the Amazon Athena service resource using the COZYROC REST Source component.
Step 1. Select an existing "Data Flow task" or create a new "Data Flow Task".
Step 2. Open "SSIS Toolbox" and drag and drop COZYROC "REST Source" component.
Step 3. Double click on the "REST Source" component and select Amazon Athena connection from the combo. Select from available Amazon Athena resources listed in the second combo below.
Optionally you can include extra parameters in the grid below if needed.
Step 4. Click on the "Columns tab" and select Amazon Athena resource columns you want to read.
Step 5. Click Preview to make sure all necessary resource columns are included.
Bravo! You have now learned how to read data from Amazon Athena service.
In this guide, we will show how to write data to the Amazon Athena service resource using the COZYROC REST Destination component.
Step 1. Select an existing "Data Flow task" or create a new "Data Flow Task".
Step 2. Open the "SSIS Toolbox" and drag and drop COZYROC "REST Destination" component.
Step 3. Double click on the "REST Destination" component and select Amazon Athena connection from the combo. Also, select resource action and Amazon Athena resource you want to work on.
Step 4. Click on the "Mapping" tab and map the input columns for the process you are configuring.
If you want to provide input for other resource composite objects, change the input above and map the columns.
Done. You have now learned how to write data into the Amazon Athena resource.
Configuration
Base URL address: https://athena.[REGION].amazonaws.com.
- Amazon AWS Signature v4
-
The authentication uses a parameters-based authentication.
The authentication has the following user-defined parameters:
- Access Key: Required. Specify access key.
- Secret Key: Required. Specify secret key.
The following request parameters will be automatically processed during the authentication process:
-
Authorization:
{{=aws4s.authorizationHeader (request, connection)}} -
Host:
{{=request.signedRequest.headers['Host']}} -
X-Amz-Content-Sha256:
{{=request.signedRequest.headers['X-Amz-Content-Sha256']}} -
X-Amz-Date:
{{=request.signedRequest.headers['X-Amz-Date']}}
Documentation: https://docs.aws.amazon.com/general/latest/gr/signature-version-4.html.
Based on resource template Base.
- [Read] action
-
Endpoint URL address:
/.
The result is extracted from:{{=connection.execute({ url: '/', method: 'POST', headers: { 'X-Amz-Target': 'AmazonAthena.BatchGetNamedQuery' }, body: { content: { NamedQueryIds: response.NamedQueryIds }, contentType: 'application/x-amz-json-1.1' } }).NamedQueries }}.The action has the following user-defined parameters:
- WorkGroup: Specify workgroup name. Optional.
The following request parameters will be automatically processed:
-
X-Amz-Target:
AmazonAthena.ListNamedQueries
Documentation: https://docs.aws.amazon.com/athena/latest/APIReference/API_ListNamedQueries.html.
- [Read single] action
-
Endpoint URL address:
/.
The result is extracted from:{{=[response.NamedQuery]}}.The action has the following user-defined parameters:
- NamedQueryId: Required. Specify named query identifier.
The following request parameters will be automatically processed:
-
X-Amz-Target:
AmazonAthena.GetNamedQuery
Documentation: https://docs.aws.amazon.com/athena/latest/APIReference/API_GetNamedQuery.html .
- [Create] action
-
Endpoint URL address:
/.The following request parameters will be automatically processed:
-
X-Amz-Target:
AmazonAthena.CreateNamedQuery -
application/x-amz-json-1.1:
{{=_.extend({ ClientRequestToken: Math.random() }, item)}}
Documentation: https://docs.aws.amazon.com/athena/latest/APIReference/API_CreateNamedQuery.html#API_CreateNamedQuery_RequestSyntax.
-
X-Amz-Target:
- [Delete] action
-
Endpoint URL address:
/.The following request parameters will be automatically processed:
-
X-Amz-Target:
AmazonAthena.DeleteNamedQuery
Documentation: https://docs.aws.amazon.com/athena/latest/APIReference/API_DeleteNamedQuery.html.
-
X-Amz-Target:
- NamedQueryId
Template: ShortText.
- Database
Template: ShortText.
- Description
Template: ShortText.
- Name
Template: ShortText.
- QueryString
Template: ShortText.
- WorkGroup
Template: ShortText.
Based on resource template Base.
- [Read] action
-
Endpoint URL address:
/.
The result is extracted from:{{=connection.execute({ url: '/', method: 'POST', headers: { 'X-Amz-Target': 'AmazonAthena.BatchGetQueryExecution' }, body: { content: { QueryExecutionIds: response.QueryExecutionIds }, contentType: 'application/x-amz-json-1.1' } }).QueryExecutions }}.The action has the following user-defined parameters:
- WorkGroup: Specify workgroup name. Optional.
The following request parameters will be automatically processed:
-
X-Amz-Target:
AmazonAthena.ListQueryExecutions
Documentation: https://docs.aws.amazon.com/athena/latest/APIReference/API_ListQueryExecutions.html.
- [Read single] action
-
Endpoint URL address:
/.
The result is extracted from:{{=[response.QueryExecution]}}.The action has the following user-defined parameters:
- QueryExecutionId: Required. Specify query execution identifier.
The following request parameters will be automatically processed:
-
X-Amz-Target:
AmazonAthena.GetQueryExecution
Documentation: https://docs.aws.amazon.com/athena/latest/APIReference/API_GetQueryExecution.html.
- [Create] action
-
Endpoint URL address:
/.The following request parameters will be automatically processed:
-
X-Amz-Target:
AmazonAthena.StartQueryExecution -
application/x-amz-json-1.1:
{{=_.extend({ ClientRequestToken: Math.random(), QueryString: item.Query }, item)}}
Documentation: https://docs.aws.amazon.com/athena/latest/APIReference/API_StartQueryExecution.html.
-
X-Amz-Target:
- [Delete] action
-
Endpoint URL address:
/.The following request parameters will be automatically processed:
-
X-Amz-Target:
AmazonAthena.StopQueryExecution
Documentation: https://docs.aws.amazon.com/athena/latest/APIReference/API_StopQueryExecution.html.
-
X-Amz-Target:
- EngineVersion
Template: engineVersionT.
- Query
Template: ShortText.
- QueryExecutionContext
Template: queryExecutionContextT.
- QueryExecutionId
Template: ShortText.
- ResultConfiguration
Template: resultConfingurationT.
- StatementType
Template: ShortText.
- Statistics
Field components:
-
Uses template:
ShortText. - Contains the following components: DataManifestLocation, DataScannedInBytes, EngineExecutionTimeInMillis, QueryPlanningTimeInMillis, QueryQueueTimeInMillis, ServiceProcessingTimeInMillis, TotalExecutionTimeInMillis.
-
Uses template:
- Status
Field components:
-
Uses template:
UnixTime. - Contains the following components: CompletionDateTime, State, StateChangeReason, SubmissionDateTime.
-
Uses template:
- WorkGroup
Template: ShortText.
Based on resource template Base.
- [Read] action
-
The result is extracted from:{{=Athena.getQueryResult(parameters)}}.
The result is paginated.The action has the following user-defined parameters:
- QueryExecutionId: Required. Specify the query execution identifier.
- Timeout: Specify timeout in seconds to wait for query result. Optional.
- [External]
-
The external fields URL address:
/. The external fields list is extracted from:{{=response.ResultSet.ResultSetMetadata.ColumnInfo}}.- {{=external.Name}}
Data type: {{=Athena.getDataType(external.Type)}} ( length 255 )
Based on resource template Base.
- [Read] action
-
Endpoint URL address:
/.
The result is extracted from:{{=response.DataCatalogsSummary}}.The following request parameters will be automatically processed:
-
X-Amz-Target:
AmazonAthena.ListDataCatalogs
Documentation: https://docs.aws.amazon.com/athena/latest/APIReference/API_ListDataCatalogs.html.
-
X-Amz-Target:
- [Create] action
-
Endpoint URL address:
/.The following request parameters will be automatically processed:
-
X-Amz-Target:
AmazonAthena.CreateDataCatalog
Documentation: https://docs.aws.amazon.com/athena/latest/APIReference/API_CreateDataCatalog.html.
-
X-Amz-Target:
- [Update] action
-
Endpoint URL address:
/.The following request parameters will be automatically processed:
-
X-Amz-Target:
AmazonAthena.UpdateDataCatalog
Documentation: https://docs.aws.amazon.com/athena/latest/APIReference/API_UpdateDataCatalog.html.
-
X-Amz-Target:
- [Delete] action
-
Endpoint URL address:
/.The following request parameters will be automatically processed:
-
X-Amz-Target:
AmazonAthena.DeleteDataCatalog
Documentation: https://docs.aws.amazon.com/athena/latest/APIReference/API_DeleteDataCatalog.html#API_DeleteDataCatalog_RequestSyntax.
-
X-Amz-Target:
- Name
Template: ShortText. The gathered value is processed with
{{=item.Name || item.CatalogName}}expression.- Type
Template: ShortText.
Based on resource template Base.
- [Read] action
-
Endpoint URL address:
/.
The result is extracted from:{{=response.PreparedStatements}}.The action has the following user-defined parameters:
- WorkGroup: Required. Specify the workgroup name.
The following request parameters will be automatically processed:
-
X-Amz-Target:
AmazonAthena.ListPreparedStatements
Documentation: https://docs.aws.amazon.com/athena/latest/APIReference/API_ListPreparedStatements.html.
- [Create] action
-
Endpoint URL address:
/.The following request parameters will be automatically processed:
-
X-Amz-Target:
AmazonAthena.CreatePreparedStatement
Documentation: https://docs.aws.amazon.com/athena/latest/APIReference/API_CreatePreparedStatement.html.
-
X-Amz-Target:
- [Update] action
-
Endpoint URL address:
/.The following request parameters will be automatically processed:
-
X-Amz-Target:
AmazonAthena.UpdatePreparedStatement
Documentation: https://docs.aws.amazon.com/athena/latest/APIReference/API_UpdatePreparedStatement.html.
-
X-Amz-Target:
- [Delete] action
-
Endpoint URL address:
/.The following request parameters will be automatically processed:
-
X-Amz-Target:
AmazonAthena.DeletePreparedStatement
Documentation: https://docs.aws.amazon.com/athena/latest/APIReference/API_DeletePreparedStatement.html.
-
X-Amz-Target:
- StatementName
A key field. Template: ShortText.
- LastModifiedTime
Template: UnixTime. A read-only field.
Based on resource template Base.
- [Read] action
-
Endpoint URL address:
/.
The result is extracted from:{{=response.WorkGroups}}.The following request parameters will be automatically processed:
-
X-Amz-Target:
AmazonAthena.ListWorkGroups
Documentation: https://docs.aws.amazon.com/athena/latest/APIReference/API_ListWorkGroups.html.
-
X-Amz-Target:
- [Create] action
-
Endpoint URL address:
/.The following request parameters will be automatically processed:
-
X-Amz-Target:
AmazonAthena.CreateWorkGroup
Documentation: https://docs.aws.amazon.com/athena/latest/APIReference/API_CreateWorkGroup.html.
-
X-Amz-Target:
- [Update] action
-
Endpoint URL address:
/.The following request parameters will be automatically processed:
-
X-Amz-Target:
AmazonAthena.UpdateWorkGroup
Documentation: https://docs.aws.amazon.com/athena/latest/APIReference/API_UpdateWorkGroup.html.
-
X-Amz-Target:
- [Delete] action
-
Endpoint URL address:
/.The following request parameters will be automatically processed:
-
X-Amz-Target:
AmazonAthena.DeleteWorkGroup -
application/x-amz-json-1.1:
{{={ WorkGroup: item.Name } }}
Documentation: https://docs.aws.amazon.com/athena/latest/APIReference/API_DeleteWorkGroup.html.
-
X-Amz-Target:
- CreationTime
Template: UnixTime. A read-only field.
- Description
Template: ShortText.
- EngineVersion
Template: engineVersionT.
- Name
Template: ShortText.
- State
Template: ShortText.
- Tags
Field components:
- An array.
- Contains the following components: Key, Value.
- Configuration
The value is gathered from
/address. The gathered value is processed with{{=response.WorkGroup.Configuration}}expression. Field components:-
Uses template:
engineVersionT. - Contains the following components: BytesScannedCutoffPerQuery, EnforceWorkGroupConfiguration, EngineVersion, PublishCloudWatchMetricsEnabled, RequesterPaysEnabled, ResultConfiguration, ResultConfigurationUpdates.
-
Uses template:
Based on resource template Base.
- [Read] action
-
Endpoint URL address:
/.
The result is extracted from:{{=response.DatabaseList}}.The action has the following user-defined parameters:
- CatalogName: Required. Specify the catalog name.
The following request parameters will be automatically processed:
-
X-Amz-Target:
AmazonAthena.ListDatabases
Documentation: https://docs.aws.amazon.com/athena/latest/APIReference/API_ListDatabases.html.
- [Read single] action
-
Endpoint URL address:
/.
The result is extracted from:{{=[response.Database]}}.The action has the following user-defined parameters:
- CatalogName: Required. Specify the catalog name.
- DatabaseName: Required. Specify the database name.
The following request parameters will be automatically processed:
-
X-Amz-Target:
AmazonAthena.GetDatabase
Documentation: https://docs.aws.amazon.com/athena/latest/APIReference/API_GetDatabase.html.
- Description
Template: ShortText.
- Name
Template: ShortText.
- Parameters
Field components:
-
Uses template:
ShortText. - Contains the following components: string.
-
Uses template:
Based on resource template Base.
- [Read] action
-
Endpoint URL address:
/.
The result is extracted from:{{=response.TableMetadataList}}.The action has the following user-defined parameters:
- CatalogName: Required. Specify the catalog name.
- DatabaseName: Required. Specify the database name.
- Expression: Provide a regex filter that pattern-matches table names. Optional.
The following request parameters will be automatically processed:
-
X-Amz-Target:
AmazonAthena.ListTableMetadata
Documentation: https://docs.aws.amazon.com/athena/latest/APIReference/API_ListTableMetadata.html.
- [Read single] action
-
Endpoint URL address:
/.
The result is extracted from:{{=[response.TableMetadata]}}.The action has the following user-defined parameters:
- CatalogName: Required. Specify the catalog name.
- DatabaseName: Required. Specify the database name.
- TableName: Required. Specify the table name.
The following request parameters will be automatically processed:
-
X-Amz-Target:
AmazonAthena.GetTableMetadata
- Columns
Field components:
- An array.
- Contains the following components: Comment, Name, Type.
- CreateTime
Template: UnixTime. A read-only field.
- LastAccessTime
Template: UnixTime. A read-only field.
- Name
Template: ShortText.
- Parameters
The gathered value is processed with
{{=_.map(value, function(v, k) { return { name: k, value: v }; })}}expression. Field components:-
Uses template:
ShortText. - Contains the following components: name, value.
-
Uses template:
- PartitionKeys
Field components:
- An array.
- Contains the following components: Comment, Name, Type.
- TableType
Template: ShortText.
Based on resource template Base.
- [Read] action
-
Endpoint URL address:
/.
The result is extracted from:{{=response.EngineVersions}}.The following request parameters will be automatically processed:
-
X-Amz-Target:
AmazonAthena.ListEngineVersions
-
X-Amz-Target:
- EffectiveEngineVersion
Template: ShortText.
- SelectedEngineVersion
Template: ShortText.
Based on resource template Base.
- [Read] action
-
Endpoint URL address:
/.
The result is extracted from:{{=[response]}}.The action has the following user-defined parameters:
- ResourceARN: Required. Specify the ResourceARN.
The following request parameters will be automatically processed:
-
X-Amz-Target:
AmazonAthena.ListTagsForResource
Documentation: https://docs.aws.amazon.com/athena/latest/APIReference/API_ListTagsForResource.html.
- [Create] action
-
Endpoint URL address:
/.The following request parameters will be automatically processed:
-
X-Amz-Target:
AmazonAthena.TagResource
Documentation: https://docs.aws.amazon.com/athena/latest/APIReference/API_TagResource.html.
-
X-Amz-Target:
- [Delete] action
-
Endpoint URL address:
/.The following request parameters will be automatically processed:
-
X-Amz-Target:
AmazonAthena.UntagResource -
application/x-amz-json-1.1:
{{={ ResourceARN: item.ResourceARN, TagKeys: _.pluck(item.Tags, 'Key') } }}
Documentation: https://docs.aws.amazon.com/athena/latest/APIReference/API_UntagResource.html.
-
X-Amz-Target:
- ResourceARN
Template: ShortText.
- Tags
Field components:
- An array.
- Contains the following components: Key, Value.
- [Read] action
-
The action uses POST method.
The result is paginated.The following request parameters will be automatically processed:
-
application/x-amz-json-1.1:
{{=_.extend({ MaxResults: parameters.batchSize, NextToken: parameters.iterator }, parameters)}}
-
application/x-amz-json-1.1:
- [Create] action
-
The action uses POST method.The following request parameters will be automatically processed:
-
X-Amz-Target:
{{=parameters.get('X-Amz-Target')}} -
application/x-amz-json-1.1:
{{=item}} -
_includeUserParameters:
{{=parameters}}
-
X-Amz-Target:
- [Update] action
-
The action uses POST method.The following request parameters will be automatically processed:
-
X-Amz-Target:
{{=parameters.get('X-Amz-Target')}} -
application/x-amz-json-1.1:
{{=item}} -
_includeUserParameters:
{{=parameters}}
-
X-Amz-Target:
- [Delete] action
-
The action uses POST method.The following request parameters will be automatically processed:
-
X-Amz-Target:
{{=parameters.get('X-Amz-Target')}} -
application/x-amz-json-1.1:
{{=item}} -
_includeUserParameters:
{{=parameters}}
-
X-Amz-Target:
- ShortText
Data type: DT_WSTR ( length 255 )
- LongText
Data type: DT_WSTR ( length 1000 )
- DateTime
Data type: DT_DBTIMESTAMP
- UnixTime
Data type: DT_DBTIMESTAMP The gathered value is processed with
{{=value && new Date(value * 1000).toUTCString()}}expression.- engineVersionT
Field components:
-
Uses template:
ShortText. - Contains the following components: EffectiveEngineVersion, SelectedEngineVersion.
-
Uses template:
- queryExecutionContextT
Field components:
-
Uses template:
ShortText. - Contains the following components: Catalog, Database.
-
Uses template:
- resultConfingurationT
Field components:
-
Uses template:
ShortText. - Contains the following components: EncryptionConfiguration, OutputLocation.
-
Uses template:
What's New
- New: Introduced connection.
Related documentation
COZYROC SSIS+ Components Suite is free for testing in your development environment.
A licensed version can be deployed on-premises, on Azure-SSIS IR and on COZYROC Cloud.