CODEWITHSUNDEEP
aws-cloudformation
shantanuo
shantanuo

Reputation: 32346

Substitute variable with bucket name

I have this template that works as expected and creates a S3 bucket and a few athena queries.

https://datameetgeobk.s3.amazonaws.com/cftemplates/athena_saved_query_blog_partition_parquet.tpl

The question is: How do I replace S3 bucketname "athenadata162a" with the bucket name created in the first step.

external_location = 's3://athenadata162a/optimized-data11/'

The code that created a new bucket looks like this...

"myS3Bucket" : {
      "Type" : "AWS::S3::Bucket"
      },

As suggested, changed the code to yaml and still getting this error:

Template validation error: Template error: One or more Fn::Sub intrinsic functions don't specify expected arguments. Specify a string as first argument, and an optional second argument to specify a mapping of values to replace in the string

The code:

Resources:
  myS3Bucket:
    Type: 'AWS::S3::Bucket'
  createTableASParquetPartioned:
    Type: 'AWS::Athena::NamedQuery'
    Properties:
      Database: default
      Description: Create table as parquet partitioned
      Name: Blog2createTableAS
      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

Upvotes: 3

Views: 506

Answers (2)

lexicore
lexicore

Reputation: 43681

Typically you use Fn::Sub to substitute variables in some input string.

In your case in YAML this would be something like:

    QueryString: !Sub
      - |-
        CREATE table new_parquet11
        WITH (format='PARQUET', 
        parquet_compression='SNAPPY', 
        partitioned_by=array['year'], 
        external_location = 's3://${myS3Bucket}/optimized-data11/') 
        ...

Sorry I will not provide JSON syntax as it is too horrible for multiline values.

Upvotes: 3

Arun Kamalanathan
Arun Kamalanathan

Reputation: 8593

You can do external_location = 's3://${myS3Bucket}/optimized-data11/'.

Upvotes: 0

Related Questions