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
Open the Amazon Athena console at https://console.amazonaws.cn/athena/
. -
In the left navigation pane, choose Workflows.
-
In the Keep data up to date 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
-
Amazon EventBridge rule
-
An Amazon S3 bucket
-
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.
-
(Optional) 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 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.