CODEWITHSUNDEEP
aws-cloudformation
shantanuo
shantanuo

Reputation: 32346

substitute variables in query string

This template is working as expected. But when I change it to function Substitution, it throws an error.

Working template:

Parameters:
  MyS3Bucket:
    Type: String
Resources:
  createTableOriginalCsv:
    Type: 'AWS::Athena::NamedQuery'
    Properties:
      Database: default
      Description: First query to create table based on AWS bucket data
      Name: Blog1CreateTableOriginalCsv
      QueryString: >-
        CREATE table new_parquet11 WITH (format='PARQUET',
        parquet_compression='SNAPPY',  partitioned_by=array['year'],
        external_location = 's3://MyS3Bucket/optimized-data11/') AS SELECT
        id, date, element, datavalue, mflag, qflag, sflag, obstime,
        substr("date",1,4) AS year FROM original_csv WHERE
        cast(substr("date",1,4) AS bigint) >= 2015 AND cast(substr("date",1,4)
        AS bigint) <= 2019

Chagne the Query string above to this and it will not work:

  QueryString: !Sub
    - |-
      CREATE table new_parquet11 WITH (format='PARQUET',
      parquet_compression='SNAPPY',  partitioned_by=array['year'],
      external_location = 's3://${MyS3Bucket}/optimized-data11/') AS SELECT
      id, date, element, datavalue, mflag, qflag, sflag, obstime,
      substr("date",1,4) AS year FROM original_csv WHERE
      cast(substr("date",1,4) AS bigint) >= 2015 AND cast(substr("date",1,4)
      AS bigint) <= 2019

How do I substitute variables in query string?

Upvotes: 2

Views: 1304

Answers (1)

Pat Myron
Pat Myron

Reputation: 4638

The CloudFormation Linter warns:

E1019 Sub should be an array of 2 for Resources/createTableOriginalCsv/Properties/QueryString/Fn::Sub

because Fn::Sub either needs a list of two elements (a string and a variable map) or just a string. You can make it a string by removing the first - so that it is no longer a list containing one string:

  QueryString: !Sub
    |-
      CREATE table new_parquet11 WITH (format='PARQUET',
      parquet_compression='SNAPPY',  partitioned_by=array['year'],
      external_location = 's3://${MyS3Bucket}/optimized-data11/') AS SELECT
      id, date, element, datavalue, mflag, qflag, sflag, obstime,
      substr("date",1,4) AS year FROM original_csv WHERE
      cast(substr("date",1,4) AS bigint) >= 2015 AND cast(substr("date",1,4)
      AS bigint) <= 2019

Everything after the first - on that line is part of YAML's multiline syntax explained here:

https://yaml-multiline.info/

Upvotes: 1

Related Questions