[Bug] Athena query literals with interpolated variables require terminated quotes

[armenr]

How to reproduce:

• Follow the well-architected-labs Level200 guide
• Try to deploy the linked cfn template for CUDOS CID
• CFN run fails due to Lambda failing --> QSCURBuildViews fails

    "Status": "FAILED",
    "Reason": "See the details in CloudWatch Log Stream: 2022/01/23/[$LATEST]3e8ecbd7664945e1af7e5db003a18cb0",
    "PhysicalResourceId": "2022/01/23/[$LATEST]3e8ecbd7664945e1af7e5db003a18cb0",
    "StackId": "arn:aws:cloudformation:us-west-2:<REDACTED_ACCT>:stack/CUR-200-QS/cbe38200-7c77-11ec-8ec0-06f60e5b9835",
    "RequestId": "953e69de-4320-4d7f-9f30-ca45474cc9dc",
    "LogicalResourceId": "QSCURBuildViewsLambdaExecutor",
    "NoEcho": false,
    "Data": {
        "Reason": "Step: 'Identify RI SP configuration' Exception: [<class 'botocore.errorfactory.InvalidRequestException'>] An error occurred (InvalidRequestException) when calling the StartQueryExecution operation: line 2:49: mismatched input '.5'. Expecting: ',', '.', 'AS', 'CROSS', 'EXCEPT', 'FOR', 'FULL', 'GROUP', 'HAVING', 'INNER', 'INTERSECT', 'JOIN', 'LEFT', 'LIMIT', 'NATURAL', 'OFFSET', 'ORDER', 'RIGHT', 'TABLESAMPLE', 'UNION', 'WHERE', <EOF>, <identifier>"
    }
}```

[armenr]

I've found & fixed the problem, I'll try to open up a PR soon...

Problem exists in the following places:

• https://github.com/aws-samples/aws-cudos-framework-deployment/blob/main/cfn-templates/cudos-cfn.yml#L295

Fixed code:

                  account_map_query = f"""
                      CREATE OR REPLACE VIEW account_map
                      AS SELECT DISTINCT
                          line_item_usage_account_id account_id,
                          line_item_usage_account_id account_name
                      FROM \"{table_name}\"

• https://github.com/aws-samples/aws-cudos-framework-deployment/blob/main/cfn-templates/cudos-cfn.yml#L315

Fixed code:

              queryId = athena.start_query_execution(
                  QueryString=f"""
                      SELECT COUNT(distinct {fieldName})
                      FROM \"{database_name}\".\"{table_name}\"
                      WHERE {fieldName} <> ''
                  """.strip(),

[armenr]

This is as straight-forward as a pull-request can get. Is anyone going to review or merge this?

[cristian-popa]

Thanks for the fix, merged, closing issue