Start an Athena query and send a results notification
This sample project demonstrates how to use Step Functions and Amazon Athena to start an Athena query and send a notification with query results using Standard workflows.
In this project, Step Functions uses Lambda functions and an AWS Glue crawler to generate a set of example data. It then performs a query using the Athena service integration and returns the results using an SNS topic.
For more information about Athena and Step Functions service integrations, see the following:
Note
This sample project may incur charges.
For new AWS users, a free usage tier is available. On this tier, services are free below
a certain level of usage. For more information about AWS costs and the Free Tier, see Athena Pricing
Step 1: Create the state machine and provision resources
-
Open the Step Functions console
and choose Create state machine. -
Type
Start an Athena query
in the search box, and then choose Start an Athena query from the search results that are returned. -
Choose Next to continue.
-
Step Functions lists the AWS services used in the sample project you selected. It also shows a workflow graph for the sample project. Deploy this project to your AWS account or use it as a starting point for building your own projects. Based on how you want to proceed, choose Run a demo or Build on it.
This sample project deploys the following resources:
-
An Amazon Athena query
-
An AWS Glue crawler
-
An Amazon SNS topic
-
An AWS Step Functions state machine
-
Related AWS Identity and Access Management (IAM) roles
The following image shows the workflow graph for the Start an Athena query sample project:
-
-
Choose Use template to continue with your selection.
-
Do one of the following:
-
If you selected Build on it, Step Functions creates the workflow prototype, but does not deploy the resources in the workflow definition, so you can continue building out your workflow prototype.
In Workflow Studio'sDesign mode, you can can additional states into your workflow protoype. Or, you can switch to the Code mode to use the integrated code editor to edit the Amazon States Language (ASL) definition of your state machine from the Step Functions console.
Important
You may need to update the placeholder Amazon Resource Name (ARN) for the resources used in the sample project before you can run your workflow.
-
If you selected Run a demo, Step Functions creates a read-only project which uses an AWS CloudFormation template to deploy the AWS resources in that template to your AWS account. You can view the state machine definition by choosing the Code mode.
Choose Deploy and run to deploy the project and create the resources.
Note that deploying can take up to 10 minutes for resources and IAM permissions to be created. While your resources are being deployed, you can open the AWS CloudFormation Stack ID link to see which resources are being provisioned.
After all the resources have been created, you should see the project on the State machines page in the console.
Important
Standard charges may apply for each service used in the CloudFormation template.
-
Step 2: Run the state machine
-
On the State machines page, choose your sample project.
-
On the sample project page, choose Start execution.
-
In the Start execution dialog box, do the following:
-
(Optional) To identify your execution, you can specify a name, or use the default generated execution name .
Note
Step Functions accepts names for state machines, executions, activities, and labels that contain non-ASCII characters. Because names with non-ASCII characters will not work with Amazon CloudWatch, we recommend using only ASCII charcters so you can track metrics in CloudWatch.
-
(Optional) In the Input box, enter input values in JSON format to run your workflow.
If you chose to Run a demo, you need not provide any execution input.
Note
If the demo project you deployed contains prepopulated execution input data, use that input to run the state machine.
-
Choose Start execution.
-
The Step Functions console directs you to a page that's titled with your execution ID. This page is known as the Execution Details page. On this page, you can review the execution results as the execution progresses or after it's complete.
To review the execution results, choose individual states on the Graph view, and then choose the individual tabs on the Step details pane to view each state's details including input, output, and definition respectively. For details about the execution information you can view on the Execution Details page, see Execution Details page – Interface overview.
-
Example State Machine Code
The state machine in this sample project integrates with Athena and AWS Lambda by passing parameters directly to those resources, and uses an SNS topic to return the results of the query.
Browse through this example state machine to see how Step Functions controls Lambda and Athena.
For more information about how AWS Step Functions can control other AWS services, see Integrating other services with Step Functions.
{
"StartAt": "Generate example log",
"States": {
"Generate example log": {
"Resource": "arn:aws:lambda:us-east-1:111122223333:function:StepFunctionsSample-Athena-LambdaForDataGeneration-AKIAIOSFODNN7EXAMPLE",
"Type": "Task",
"Next": "Run Glue crawler"
},
"Run Glue crawler": {
"Resource": "arn:aws:lambda:us-east-1:111122223333:function:StepFunctionsSample-Athen-LambdaForInvokingCrawler-AKIAI44QH8DHBEXAMPLE",
"Type": "Task",
"Next": "Start an Athena query"
},
"Start an Athena query": {
"Resource": "arn:aws:states:::athena:startQueryExecution.sync",
"Parameters": {
"QueryString": "SELECT * FROM \"athena-sample-project-db-wJalrXUtnFEMI\".\"log\" limit 1",
"WorkGroup": "stepfunctions-athena-sample-project-workgroup-wJalrXUtnFEMI"
},
"Type": "Task",
"Next": "Get query results"
},
"Get query results": {
"Resource": "arn:aws:states:::athena:getQueryResults",
"Parameters": {
"QueryExecutionId.$": "$.QueryExecution.QueryExecutionId"
},
"Type": "Task",
"Next": "Send query results"
},
"Send query results": {
"Resource": "arn:aws:states:::sns:publish",
"Parameters": {
"TopicArn": "arn:aws:sns:us-east-1:111122223333:StepFunctionsSample-AthenaDataQueryd1111-2222-3333-777788889999-SNSTopic-ANPAJ2UCCR6DPCEXAMPLE",
"Message": {
"Input.$": "$.ResultSet.Rows"
}
},
"Type": "Task",
"End": true
}
}
}
For information about how to configure IAM when using Step Functions with other AWS services, see How Step Functions generates IAM policies for integrated services.
IAM Example
These example AWS Identity and Access Management (IAM) policies generated by the sample project include 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.
{
"Version": "2012-10-17",
"Statement": [
{
"Action": [
"lambda:InvokeFunction"
],
"Resource": [
"arn:aws:lambda:us-east-1:111122223333:function:StepFunctionsSample-Athena-LambdaForDataGeneration-AKIAIOSFODNN7EXAMPLE",
"arn:aws:lambda:us-east-1:111122223333:function:StepFunctionsSample-Athen-LambdaForInvokingCrawler-AKIAI44QH8DHBEXAMPLE"
],
"Effect": "Allow"
},
{
"Action": [
"sns:Publish"
],
"Resource": [
"arn:aws:sns:us-east-1:111122223333:StepFunctionsSample-AthenaDataQueryd1111-2222-3333-777788889999-SNSTopic-ANPAJ2UCCR6DPCEXAMPLE"
],
"Effect": "Allow"
},
{
"Action": [
"athena:getQueryResults",
"athena:startQueryExecution",
"athena:stopQueryExecution",
"athena:getQueryExecution",
"athena:getDataCatalog"
],
"Resource": [
"arn:aws:athena:us-east-1:111122223333:workgroup/stepfunctions-athena-sample-project-workgroup-wJalrXUtnFEMI",
"arn:aws:athena:us-east-1:111122223333:datacatalog/*"
],
"Effect": "Allow"
},
{
"Action": [
"s3:GetBucketLocation",
"s3:GetObject",
"s3:ListBucket",
"s3:ListBucketMultipartUploads",
"s3:ListMultipartUploadParts",
"s3:AbortMultipartUpload",
"s3:CreateBucket",
"s3:PutObject"
],
"Resource": "arn:aws: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:glue:us-east-1:111122223333:database/*",
"arn:aws:glue:us-east-1:111122223333:table/*",
"arn:aws:glue:us-east-1:111122223333:catalog"
],
"Effect": "Allow"
}
]
}
For information about how to configure IAM when using Step Functions with other AWS services, see How Step Functions generates IAM policies for integrated services.