Query large datasets (Amazon Athena, Amazon S3, AWS Glue, Amazon SNS) - AWS Step Functions

Query large datasets (Amazon Athena, Amazon S3, AWS Glue, Amazon SNS)

This sample project demonstrates how to ingest a large data set in Amazon S3 and partition it through AWS Glue Crawlers, then execute Amazon Athena queries against that partition.

In this project, the Step Functions state machine invokes an AWS Glue crawler that partitions a large dataset in Amazon S3. Once the AWS Glue crawler returns a success message, the workflow executes Athena queries against that partition. Once query execution is successfully complete, an Amazon SNS notification is sent to an Amazon SNS topic.

Step 1: Create the state machine and provision resources

  1. Open the Step Functions console and choose Create state machine.

  2. Type Query large datasets in the search box, and then choose Query large datasets from the search results that are returned.

  3. Choose Next to continue.

  4. 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 S3 bucket

    • 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 Query large datasets sample project:

    Workflow graph of the Query large datasets sample project.
  5. Choose Use template to continue with your selection.

  6. Do one of the following:

    • If you selected Build on it, Step Functions creates the workflow prototype for the sample project you selected. Step Functions doesn't deploy the resources listed in the workflow definition.

      In Workflow Studio's Design mode, drag and drop states from the States browser to continue building your workflow protoype. Or switch to the Code mode that provides an integrated code editor similar to VS Code for updating the Amazon States Language (ASL) definition of your state machine within the Step Functions console. For more information about using Workflow Studio to build your state machines, see Using Workflow Studio.


      Remember to update the placeholder Amazon Resource Name (ARN) for the resources used in the sample project before you run your workflow.

    • If you selected Run a demo, Step Functions creates a read-only sample project which uses an AWS CloudFormation template to deploy the AWS resources listed in that template to your AWS account.


      To view the state machine definition of the sample project, choose Code.

      When you're ready, choose Deploy and run to deploy the sample project and create the resources.

      It can take up to 10 minutes for these resources and related IAM permissions to be created. While your resources are being deployed, you can open the CloudFormation Stack ID link to see which resources are being provisioned.

      After all the resources in the sample project are created, you can see the new sample project listed on the State machines page.


      Standard charges may apply for each service used in the CloudFormation template.

Step 2: Run the state machine

  1. On the State machines page, choose your sample project.

  2. On the sample project page, choose Start execution.

  3. In the Start execution dialog box, do the following:

    1. (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.


      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.

    2. (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.

    3. Choose Start execution.

    4. 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 Amazon S3, AWS Glue, 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 S3, AWS Glue, 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 AWS Step Functions can control other AWS services, see Using AWS Step Functions with other services.

{ "Comment": "An example demonstrates how to ingest a large data set in Amazon S3 and partition it through aws Glue Crawlers, then execute Amazon Athena queries against that partition.", "StartAt": "Start Crawler", "States": { "Start Crawler": { "Type": "Task", "Next": "Get Crawler status", "Parameters": { "Name": "<GLUE_CRAWLER_NAME>" }, "Resource": "arn:aws:states:::aws-sdk:glue:startCrawler" }, "Get Crawler status": { "Type": "Task", "Parameters": { "Name": "<GLUE_CRAWLER_NAME>" }, "Resource": "arn:aws:arn:aws:states:::aws-sdk:glue:getCrawler", "Next": "Check Crawler status" }, "Check Crawler status": { "Type": "Choice", "Choices": [ { "Variable": "$.Crawler.State", "StringEquals": "RUNNING", "Next": "Wait" } ], "Default": "Start an Athena query" }, "Wait": { "Type": "Wait", "Seconds": 30, "Next": "Get Crawler status" }, "Start an Athena query": { "Resource": "arn:aws:states:::athena:startQueryExecution.sync", "Parameters": { "QueryString": "<ATHENA_QUERYSTRING>", "WorkGroup": "<ATHENA_WORKGROUP>" }, "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": "<SNS_TOPIC_ARN>", "Message": { "Input.$": "$.ResultSet.Rows" } }, "Type": "Task", "End": true } } }

IAM Examples

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": [ { "Effect": "Allow", "Action": [ "athena:getQueryResults" ], "Resource": [ "arn:aws:athena:us-east-2:123456789012:workgroup/*" ] }, { "Effect": "Allow", "Action": [ "s3:GetObject" ], "Resource": [ "arn:aws:s3:::*" ] } ] }

{ "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Action": [ "athena:startQueryExecution", "athena:stopQueryExecution", "athena:getQueryExecution", "athena:getDataCatalog" ], "Resource": [ "arn:aws:athena:us-east-2:123456789012:workgroup/stepfunctions-athena-sample-project-workgroup-8v7bshiv70", "arn:aws:athena:us-east-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: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-2:123456789012:catalog", "arn:aws:glue:us-east-2:123456789012:database/*", "arn:aws:glue:us-east-2:123456789012:table/*", "arn:aws:glue:us-east-2:123456789012:userDefinedFunction/*" ] }, { "Effect": "Allow", "Action": [ "lakeformation:GetDataAccess" ], "Resource": [ "*" ] } ] }

{ "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Action": [ "sns:Publish" ], "Resource": [ "arn:aws:sns:us-east-2:123456789012:StepFunctionsSample-AthenaIngestLargeDataset92bc4949-abf8-4a1e-9236-5b7c81b3efa3-SNSTopic-8Y5ZLI5AASXV" ] } ] }

For information about how to configure IAM when using Step Functions with other AWS services, see IAM Policies for integrated services.