NEW: Check our new hosted service COZYROC Cloud ...
Amazon Athena Connection
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 task, 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']}}
For further information, please check here.
Template Fields define custom and/or compound data types to enable flexible modelling of entities' fields.
- 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:
Template Resources enable the reuse of fields and actions across multiple resources.
- [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:
Resources represent entities exposed by the service. The API endpoints are grouped by resources.
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
For further information check here.
- [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
For further information check here.
- [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)}}
For further information check here.
-
X-Amz-Target:
- [Delete] action
-
Endpoint URL address:
/.The following request parameters will be automatically processed:
-
X-Amz-Target:
AmazonAthena.DeleteNamedQuery
For further information check here.
-
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
For further information check here.
- [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
For further information check here.
- [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)}}
For further information check here.
-
X-Amz-Target:
- [Delete] action
-
Endpoint URL address:
/.The following request parameters will be automatically processed:
-
X-Amz-Target:
AmazonAthena.StopQueryExecution
For further information check here.
-
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
For further information check here.
-
X-Amz-Target:
- [Create] action
-
Endpoint URL address:
/.The following request parameters will be automatically processed:
-
X-Amz-Target:
AmazonAthena.CreateDataCatalog
For further information check here.
-
X-Amz-Target:
- [Update] action
-
Endpoint URL address:
/.The following request parameters will be automatically processed:
-
X-Amz-Target:
AmazonAthena.UpdateDataCatalog
For further information check here.
-
X-Amz-Target:
- [Delete] action
-
Endpoint URL address:
/.The following request parameters will be automatically processed:
-
X-Amz-Target:
AmazonAthena.DeleteDataCatalog
For further information check here.
-
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
For further information check here.
- [Create] action
-
Endpoint URL address:
/.The following request parameters will be automatically processed:
-
X-Amz-Target:
AmazonAthena.CreatePreparedStatement
For further information check here.
-
X-Amz-Target:
- [Update] action
-
Endpoint URL address:
/.The following request parameters will be automatically processed:
-
X-Amz-Target:
AmazonAthena.UpdatePreparedStatement
For further information check here.
-
X-Amz-Target:
- [Delete] action
-
Endpoint URL address:
/.The following request parameters will be automatically processed:
-
X-Amz-Target:
AmazonAthena.DeletePreparedStatement
For further information check here.
-
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
For further information check here.
-
X-Amz-Target:
- [Create] action
-
Endpoint URL address:
/.The following request parameters will be automatically processed:
-
X-Amz-Target:
AmazonAthena.CreateWorkGroup
For further information check here.
-
X-Amz-Target:
- [Update] action
-
Endpoint URL address:
/.The following request parameters will be automatically processed:
-
X-Amz-Target:
AmazonAthena.UpdateWorkGroup
For further information check here.
-
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 } }}
For further information check here.
-
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
For further information check here.
- [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
For further information check here.
- 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
For further information check here.
- [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
For further information check here.
- [Create] action
-
Endpoint URL address:
/.The following request parameters will be automatically processed:
-
X-Amz-Target:
AmazonAthena.TagResource
For further information check here.
-
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') } }}
For further information check here.
-
X-Amz-Target:
- ResourceARN
Template: ShortText.
- Tags
Field components:
- An array.
- Contains the following components: Key, Value.
What's New
2.1
- 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.