Execute multiple queries (Amazon Athena, Amazon SNS)
This sample project demonstrates how to run Athena queries in succession and then in parallel, handle errors and then send an Amazon SNS notification based on whether the queries succeed or fail. Deploying this sample project will create an Amazon Step Functions state machine, Amazon Athena queries, and an Amazon SNS topic.
In this project, Step Functions uses a state machine to run Athena queries synchronously. After the query results are returned, enter parallel state with two Athena queries executing in parallel. It then waits for the job to succeed or fail, and it sends an Amazon SNS topic with a message about whether the job succeeded or failed.
Create the State Machine and Provision Resources
Open the Amazon Athena console at https://console.amazonaws.cn/athena/
. -
In the left navigation pane, choose Workflows.
-
In the Execute multiple queries tile, choose Get started.
-
In the Get started dialog box, choose Deploy a sample project, and then choose Continue.
-
You're redirected to the Review workflow page of the Step Functions console. Review the Amazon States Language definition automatically generated for the sample project.
The state machine Workflow definition and Visual Workflow are displayed.
-
Choose Next.
The Deploy and run page is displayed, listing the resources that will be created. This sample project creates the following resources:
-
Amazon Athena queries
-
Lambda function
-
An Amazon S3 bucket
-
An Amazon SNS topic
-
A Amazon Glue database
-
-
Choose Deploy and run.
Note
It can take up to 10 minutes for these resources and related IAM permissions to be created. While the Deploy and run page is displayed, you can open the Stack ID link to see which resources are being provisioned.
Start a New Execution
-
On the New execution page, enter an execution name (optional), and then choose Start Execution.
(Optional) To identify your execution, you can specify a name for it in the Name box. By default, Step Functions automatically generates a unique execution name.
Note
Step Functions allows you to create names for state machines, executions, activities, and labels that contain non-ASCII characters. These non-ASCII names don't work with Amazon CloudWatch. To ensure that you can track CloudWatch metrics, choose a name that uses only ASCII characters.
-
Optionally, you can go to the newly created state machine on the Step Functions Dashboard, and then choose New execution.
-
When an execution is complete, you can select states on the Visual workflow and browse the Input and Output under Step details.
Example State Machine Code
The state machine in this sample project integrates with Amazon Athena and Amazon SNS by passing parameters directly to those resources.
Browse through this example state machine to see how Step Functions controls Amazon Athena and Amazon SNS by
connecting to the Amazon Resource Name (ARN) in the Resource field, and by passing
Parameters to the service API.
For more information about how Amazon Step Functions can control other Amazon services, see Using Amazon Step Functions with other services.
{
"Comment": "An example of using Athena to execute queries in sequence and parallel, with error handling and notifications.",
"StartAt": "Generate Example Data",
"States": {
"Generate Example Data": {
"Type": "Task",
"Resource": "arn:aws-cn:states:::lambda:invoke",
"OutputPath": "$.Payload",
"Parameters": {
"FunctionName": "<ATHENA_FUNCTION_NAME>"
},
"Next": "Load Data to Database"
},
"Load Data to Database": {
"Type": "Task",
"Resource": "arn:aws-cn:states:::athena:startQueryExecution.sync",
"Parameters": {
"QueryString": "<ATHENA_QUERYSTRING>",
"WorkGroup": "<ATHENA_WORKGROUP>"
},
"Catch": [
{
"ErrorEquals": [
"States.ALL"
],
"Next": "Send query results"
}
],
"Next": "Map"
},
"Map": {
"Type": "Parallel",
"ResultSelector": {
"Query1Result.$": "$[0].ResultSet.Rows",
"Query2Result.$": "$[1].ResultSet.Rows"
},
"Catch": [
{
"ErrorEquals": [
"States.ALL"
],
"Next": "Send query results"
}
],
"Branches": [
{
"StartAt": "Start Athena query 1",
"States": {
"Start Athena query 1": {
"Type": "Task",
"Resource": "arn:aws-cn:states:::athena:startQueryExecution.sync",
"Parameters": {
"QueryString": "<ATHENA_QUERYSTRING>",
"WorkGroup": "<ATHENA_WORKGROUP>"
},
"Next": "Get Athena query 1 results"
},
"Get Athena query 1 results": {
"Type": "Task",
"Resource": "arn:aws-cn:states:::athena:getQueryResults",
"Parameters": {
"QueryExecutionId.$": "$.QueryExecution.QueryExecutionId"
},
"End": true
}
}
},
{
"StartAt": "Start Athena query 2",
"States": {
"Start Athena query 2": {
"Type": "Task",
"Resource": "arn:aws-cn:states:::athena:startQueryExecution.sync",
"Parameters": {
"QueryString": "<ATHENA_QUERYSTRING>",
"WorkGroup": "<ATHENA_WORKGROUP>"
},
"Next": "Get Athena query 2 results"
},
"Get Athena query 2 results": {
"Type": "Task",
"Resource": "arn:aws-cn:states:::athena:getQueryResults",
"Parameters": {
"QueryExecutionId.$": "$.QueryExecution.QueryExecutionId"
},
"End": true
}
}
}
],
"Next": "Send query results"
},
"Send query results": {
"Type": "Task",
"Resource": "arn:aws-cn:states:::sns:publish",
"Parameters": {
"Message.$": "$",
"TopicArn": "<SNS_TOPIC_ARN>"
},
"End": true
}
}
}IAM Examples
This example Amazon Identity and Access Management (IAM) policy generated by the sample project includes the least privilege necessary to execute the state machine and related resources. We recommend that you include only those permissions that are necessary in your IAM policies.
AthenaStartQueryExecution
{
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Action": [
"athena:startQueryExecution",
"athena:stopQueryExecution",
"athena:getQueryExecution",
"athena:getDataCatalog"
],
"Resource": [
"arn:aws-cn:athena:us-west-2:123456789012:workgroup/stepfunctions-athena-sample-project-workgroup-ztuvu9yuix",
"arn:aws-cn:athena:us-west-2:123456789012:datacatalog/*"
]
},
{
"Effect": "Allow",
"Action": [
"s3:GetBucketLocation",
"s3:GetObject",
"s3:ListBucket",
"s3:ListBucketMultipartUploads",
"s3:ListMultipartUploadParts",
"s3:AbortMultipartUpload",
"s3:CreateBucket",
"s3:PutObject"
],
"Resource": [
"arn:aws-cn:s3:::*"
]
},
{
"Effect": "Allow",
"Action": [
"glue:CreateDatabase",
"glue:GetDatabase",
"glue:GetDatabases",
"glue:UpdateDatabase",
"glue:DeleteDatabase",
"glue:CreateTable",
"glue:UpdateTable",
"glue:GetTable",
"glue:GetTables",
"glue:DeleteTable",
"glue:BatchDeleteTable",
"glue:BatchCreatePartition",
"glue:CreatePartition",
"glue:UpdatePartition",
"glue:GetPartition",
"glue:GetPartitions",
"glue:BatchGetPartition",
"glue:DeletePartition",
"glue:BatchDeletePartition"
],
"Resource": [
"arn:aws-cn:glue:us-west-2:123456789012:catalog",
"arn:aws-cn:glue:us-west-2:123456789012:database/*",
"arn:aws-cn:glue:us-west-2:123456789012:table/*",
"arn:aws-cn:glue:us-west-2:123456789012:userDefinedFunction/*"
]
},
{
"Effect": "Allow",
"Action": [
"lakeformation:GetDataAccess"
],
"Resource": [
"*"
]
}
]
}
AthenaGetQueryResults
{
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Action": [
"athena:getQueryResults"
],
"Resource": [
"arn:aws-cn:us-west-2:123456789012:workgroup/*"
]
},
{
"Effect": "Allow",
"Action": [
"s3:GetObject"
],
"Resource": [
"arn:aws-cn:s3:::*"
]
}
]
}SNSPublish
{
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Action": [
"sns:Publish"
],
"Resource": [
"arn:aws-cn:sns:us-west-2:123456789012:StepFunctionsSample-AthenaMultipleQueriese1ec229b-5cbe-4754-a8a8-078474bac878-SNSTopic-9AID0HEJT7TH"
]
}
]
}LambdaInvokeFunction
{
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Action": [
"lambda:InvokeFunction"
],
"Resource": [
"arn:aws-cn:lambda:us-west-2:123456789012:function:StepFunctionsSample-Athen-LambdaForStringGeneratio-GQFQjN7mE9gl:*"
]
},
{
"Effect": "Allow",
"Action": [
"lambda:InvokeFunction"
],
"Resource": [
"arn:aws-cn:lambda:us-west-2:123456789012:function:StepFunctionsSample-Athen-LambdaForStringGeneratio-GQFQjN7mE9gl"
]
}
]
}For information about how to configure IAM when using Step Functions with other Amazon services, see IAM Policies for integrated services.