Create AWS Athena View Programmatically via Terraform

Question

I am trying to create Athena Views using SQL. And to be fair, I have achieve this - almost.

resource "null_resource" "views" {
    provisioner     "local-exec" {

        ## success using file://
        #command = "aws athena start-query-execution --query-string file://query11.sql  --output json --query-execution-context Database=${aws_athena_database.metadb.id} --result-configuration OutputLocation=s3://xxxxxxx2"

        ## fails when using string
        command = "aws athena start-query-execution --query-string \"CREATE OR REPLACE VIEW query11 AS SELECT * FROM  meta.getresources_vw\" --output json --query-execution-context Database=${aws_athena_database.metadb.id} --result-configuration OutputLocation=s3://xxxxxxx2"
        }
}

In the code, the command line that uses file to pass in the sql code, works perfectly.

However, the command line that tries to pass the sql in as a string - fails.

I have tried wrapping the string in single quotes, double quotes; escaped with a backslash and not .... all to no improvement.

The error when using a string is thus :

null_resource.views: Provisioning with 'local-exec'... null_resource.views (local-exec): Executing: ["cmd" "/C" "aws athena start-query-execution --query-string \"CREATE OR REPLACE VIEW query11 AS SELECT * FROM meta.getresources_vw\" --output json --query-execution-context Database=testbug --result-configuration OutputLocation=s3://xxxxxxx2"] null_resource.views (local-exec): usage: aws [options] [ ...] [parameters] null_resource.views (local-exec): To see help text, you can run:

null_resource.views (local-exec): aws help null_resource.views (local-exec): aws help null_resource.views (local-exec):
aws help

null_resource.views (local-exec): Unknown options: REPLACE, VIEW, query11, AS, SELECT, *, FROM, meta.getresources_vw", OR

Error: Error running command 'aws athena start-query-execution --query-string "CREATE OR REPLACE VIEW query11 AS SELECT * FROM meta.getresources_vw" --output json --query-execution-context Database=testbug --result-configuration OutputLocation=s3://xxxxxxx2': exit status 255. Output: usage: aws [options] [ ...] [parameters] To see help text, you can run:

aws help aws help aws help

Unknown options: REPLACE, VIEW, query11, AS, SELECT, *, FROM, meta.getresources_vw", OR

Interestingly enough, if I copy the command shown in the last part of the error message, and paste in directly into the CLI; it executes with success.

aws athena start-query-execution --query-string "CREATE OR REPLACE VIEW query11 AS SELECT * FROM meta.getresources_vw" --output json --query-execution-context Database=testbug --result-configuration OutputLocation=s3://xxxxxxx2

You can find an example of how to do it in this answer: https://stackoverflow.com/a/58370023/1109 — Theo, Sep 12 '20 at 07:25
Thanks Theo. In the end, I decided that using local_exec was not going to be robust enough for my use case. I now upload the ddl to s3 and use a lambda to synchronise and execute against the database. — SimonB, Sep 15 '20 at 16:25

Create AWS Athena View Programmatically via Terraform

0 Answers0