Keep data up to date (Amazon Athena, Amazon S3, Amazon Glue) - Amazon Step Functions
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China.

Keep data up to date (Amazon Athena, Amazon S3, Amazon Glue)

This sample project demonstrates how to query a target table to get current data with Amazon Glue Catalog, then update it with new data from other sources using Amazon Athena. Deploying this sample project creates an Amazon Step Functions state machine, an Amazon S3 Bucket, Amazon Athena queries, and an Amazon Glue Data Catalog call.

In this project, the Step Functions state machine calls Amazon Glue Catalog to verify if a target table exists in an Amazon S3 Bucket. If no table is found one, it will create a new table. Then an Athena query will be run to add rows to the target table from a different data source: first querying the target table to get the most recent date, then querying the source table for more recent data and inserting it into the target table.

Create the State Machine and Provision Resources

  1. Open the Amazon Athena console at https://console.amazonaws.cn/athena/.

  2. In the left navigation pane, choose Workflows.

  3. In the Keep data up to date tile, choose Get started.

  4. In the Get started dialog box, choose Deploy a sample project, and then choose Continue.

  5. 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.

    
            Keep data updated workflow.
  6. 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

    • Amazon EventBridge rule

    • An Amazon S3 bucket

    • A Amazon Glue database

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

  1. On the New execution page, enter an execution name (optional), and then choose Start Execution.

  2. (Optional) To identify your execution, you can specify an ID for it in the Name box. Step Functions generates a unique execution name automatically if you don't enter a name.

    Note

    Step Functions allows you to create state machine, execution, and activity names 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.

  3. (Optional) You can go to the newly created state machine on the Step Functions Dashboard, and then choose New execution.

  4. 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 S3, Amazon Glue, and Amazon Athena by passing parameters directly to those resources.

Browse through this example state machine to see how Step Functions controls Amazon S3, Amazon Glue, and Amazon Athena 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 demonstrates how to use Athena to query a target table to get current data, then update it with new data from other sources.", "StartAt": "Get Target Table", "States": { "Get Target Table": { "Type": "Task", "Parameters": { "DatabaseName": "<GLUE_DATABASE_NAME>", "Name": "target" }, "Catch": [ { "ErrorEquals": [ "Glue.EntityNotFoundException" ], "Next": "Create Target Table" } ], "Resource": "arn:aws-cn:states:::aws-sdk:glue:getTable", "Next": "Update Target Table" }, "Create Target Table": { "Resource": "arn:aws-cn:states:::athena:startQueryExecution.sync", "Parameters": { "QueryString": "<ATHENA_QUERYSTRING>", "WorkGroup": "<ATHENA_WORKGROUP>" }, "Type": "Task", "Next": "Update Target Table" }, "Update Target Table": { "Resource": "arn:aws-cn:states:::athena:startQueryExecution.sync", "Parameters": { "QueryString": "<ATHENA_QUERYSTRING>", "WorkGroup": "<ATHENA_WORKGROUP>" }, "Type": "Task", "End": true } } }

IAM Example

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-26ujlyawxg", "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: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": [ "*" ] } ] }

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