By using AWS re:Post, you agree to the AWS re:Post Terms of Use
AWS re:Postre:Post

Scheduling Queries in redshift using cloudformation

0

I have few queries in aws redshift which I want to schedule using redshift schedule feature so that I can run those queries according to specific requirements. I want to do this work using cloudformation yaml script. I have been trying to do this but I am unable to find out which properties/keys will be used in the script. I have checked the documentation as well but couldn't get any working solution. To make schedule work I require 2 resources which are iam role with relevant permissions and redshift schedule resource. Below is the snippet of the query schedule resource which I am using

Code Snipet:

ScheduledQuery1:
    Type: AWS::Redshift::ScheduledAction
    Properties:
      Enable: true
      IamRole: !GetAtt RedshiftSchedulerRole.Arn
      ScheduledActionName: ScheduledQuery1
      ScheduledActionDescription: "Runs query 1 every day at 12:00 AM PST"
      Schedule: cron(45 14 * * ? *)  # Every day at 12:00 AM PST (adjust as needed)
     TargetAction:
        #Query: "SELECT * FROM foodics_reports.foodics_daily_sales limit 100"
        #ExecuteStatement:
          #Database: dfsftdb
          #SQL: "SELECT * FROM foodics_reports.foodics_daily_sales limit 100"

TargetAction section of script is problematic where i tried multiple ways to make it work but it always gave this error Properties validation failed for resource ScheduledQuery1 with message: [#/TargetAction: extraneous key [Database] is not permitted, #/TargetAction: extraneous key [Sql] is not permitted, #/TargetAction: extraneous key [Database] is not permitted, #/TargetAction: extraneous key [Sql] is not permitted, #/TargetAction: extraneous key [Database] is not permitted, #/TargetAction: extraneous key [Sql] is not permitted

Kindly provide solution of this error, what is wrong and what is the solution/ correct working yaml script for this scenario.

  • Also provide me solution on how to add saved queried in redshift using cloudformation yaml script.
Topics
Management & GovernanceServerlessApplication IntegrationAnalytics
Tags
AWS CloudFormationAmazon EventBridgeAmazon RedshiftAmazon Redshift Serverless
Language
English
saim
asked 21 days ago249 views
2 Answers
0

Looking at the documentation, what you are trying to do is not supported. TargetAction is limited to

{
  "PauseCluster" : PauseClusterMessage,
  "ResizeCluster" : ResizeClusterMessage,
  "ResumeCluster" : ResumeClusterMessage
}

You could use EventBridge instead to schedule some queries and use CloudFormation to do it.

profile pictureAWS
EXPERT
kentrad
answered 21 days ago
0

I tried to schedule the redshift query using eventbridge, at max it led to adding the query in the list of scheduled queries in redshift but query was never executed and I got the info that eventbridge cannot send request or make call to redshift data api.

Code Snippet using eventbridge

RedshiftSchedule:
    Type: AWS::Events::Rule
    DependsOn: [QrLogGroup, RedshiftScheduledQueryRoles]
    Properties:
      EventBusName: default
      Description: "Schedule for Redshift Query Execution"
      ScheduleExpression: cron(52 18 * * ? *) 
      State: "ENABLED"
      Targets:
        - Id: QueryExecution
          Arn: arn:aws:redshift:eu-west-1:880478141414:cluster:alamar-data-platform-redshift-dev
          RoleArn: !GetAtt RedshiftScheduledQueryRoles.Arn
          RedshiftDataParameters:
            DbUser: rsadmin
            #ClusterIdentifier: alamar-data-platform-redshift-dev
            #SecretManagerArn: "arn:aws:secretsmanager:eu-west-1:880478141414:secret:testing-secret-VQU8jo"
            Database: redshiftdb
            Sql: select gross_sales, profit, order_count, branch_localized, branch_reference, foodics_account_id, 
            market, order_date from foodics_reports.foodics_daily_sales where market='Morocco' and 
            order_date='2024-05-20' order by order_date desc;
           StatementName: test-redshift-cluster-data
            WithEvent: true
        - Id: QueryLoging
          Arn: !GetAtt QrLogGroup.Arn

provide me solution on how to schedule redshift queries how to add saved query in redshift using cloudformation yaml script (esp without using any third service) and what is the scope and usage of cloudformation custom resource in this regard?

saim
answered 21 days ago

Relevant content