Step Functionsだけで、S3バケットにアップロードしたSQLファイルのクエリをAthenaで実行する

SELECT *
FROM "cm_nayuts"."iris";

CREATE EXTERNAL TABLE `iris`(
  `sepal_length` double, 
  `sepal_width` double, 
  `petal_length` double, 
  `petal_width` double, 
  `species` string)
ROW FORMAT DELIMITED 
  FIELDS TERMINATED BY ',' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://バケット名/ファイルのあるS3パス'
TBLPROPERTIES (
  'classification'='csv', 
  'columnsOrdered'='true', 
  'compressionType'='none', 
  'delimiter'=',', 
  'skip.header.line.count'='1')

import { Construct } from 'constructs';
import {
  aws_s3,
  aws_iam,
  aws_stepfunctions,
  aws_stepfunctions_tasks,
  aws_athena,
  RemovalPolicy,
  Duration,
  Stack,
  StackProps,
} from 'aws-cdk-lib';

export class ExecAthenaQueryWithS3SQLStack extends Stack {
  constructor(scope: Construct, id: string, props?: StackProps) {
    super(scope, id, props);

    //-------------------AthenaおよびS3の定義--------------------------
    // Athenaクエリ結果格納バケット
    const athenaQueryResultBucket = new aws_s3.Bucket(
      this,
      'athenaQueryResultBucket',
      {
        bucketName: `athena-query-result-${this.account}`,
        removalPolicy: RemovalPolicy.DESTROY,
      },
    );

    // Athenaワークグループ
    const athenaWorkGroup = new aws_athena.CfnWorkGroup(
      this,
      'athenaWorkGroup',
      {
        name: this.node.tryGetContext('athena_work_group_name'),
        workGroupConfiguration: {
          resultConfiguration: {
            outputLocation: `s3://${athenaQueryResultBucket.bucketName}/result-data`,
          },
        },
      },
    );

    //-------------------Athenaへのクエリ実行用のステートマシンの定義--------------------------
    // Athenaクエリ実行開始
    const startAthenaQueryExecutionTask =
      new aws_stepfunctions_tasks.AthenaStartQueryExecution(
        this,
        'startAthenaQueryExecutionTask',
        {
          queryString: aws_stepfunctions.JsonPath.stringAt(
            '$.getObjectTaskOutPut.body',
          ),
          workGroup: athenaWorkGroup.name,
          resultPath: '$.startAthenaQueryExecutionTaskOutPut',
          integrationPattern: aws_stepfunctions.IntegrationPattern.RUN_JOB,
        },
      );

    // ステートマシンの定義
    const execAthenaQueryStateMachine = new aws_stepfunctions.StateMachine(this, 'execAthenaQueryStateMachine', {
      stateMachineName: 'ExecAthenaQueryStateMachine',
      definition: startAthenaQueryExecutionTask
          .next(new aws_stepfunctions.Succeed(this, "FinishQuery")),
    });

    //SQL実行のための追加ポリシー
    execAthenaQueryStateMachine.addToRolePolicy(new aws_iam.PolicyStatement({
        actions: ['glue:*'],
        resources: [
          `arn:aws:glue:ap-northeast-1:${this.account}:database/*`,
          `arn:aws:glue:ap-northeast-1:${this.account}:table/*`
        ],
    }));


    //---------S3からSQLファイルを取得し、クエリ実行用のステートマシンを起動するステートマシンの定義-----------
    // SQLファイルの取得
    const getObjectTask = new aws_stepfunctions_tasks.CallAwsService(
      this,
      'getObjectTask',
      {
        service: 's3',
        action: 'getObject',
        parameters: {
          Bucket: this.node.tryGetContext('sql_bucket_name'),
          Key: this.node.tryGetContext('sql_file_path'),
        },
        iamResources: [`arn:aws:s3:::${this.node.tryGetContext('sql_bucket_name')}/*`],
        iamAction: 's3:GetObject',
        resultSelector: {
          body: aws_stepfunctions.JsonPath.stringAt('$.Body'),
        },
        resultPath: '$.getObjectTaskOutPut',
      },
    );

    // ステートマシンの定義
    new aws_stepfunctions.StateMachine(this, 'startStateMachineWithS3SQL', {
      stateMachineName: 'StartStateMachineWithS3SQL',
      definition: getObjectTask
          .next(
            new aws_stepfunctions_tasks.StepFunctionsStartExecution(
              this,
              "StartExecAthenaQueryStateMachine",
              {
                stateMachine: execAthenaQueryStateMachine,
                inputPath: "$",
                input: aws_stepfunctions.TaskInput.fromObject({"getObjectTaskOutPut": aws_stepfunctions.JsonPath.stringAt('$.getObjectTaskOutPut')}),
                associateWithParent: true,
                integrationPattern: aws_stepfunctions.IntegrationPattern.RUN_JOB
              }
            )
          )
          .next(new aws_stepfunctions.Succeed(this, "FinishStateMachine")),
    });

  }
}