AWS Athena : Create table/view with sql DDL

Terraform
1

I am trying to create Athena Views by executing SQL code.

resource "aws_athena_database" "metadb" {
  name   = "mydb" 
  bucket = aws_s3_bucket.meta_target_bucket.id
}
resource "null_resource" "views" {
  for_each = {
    for filename in fileset("${var.sql_files_dir}/", "**/*.sql") :
    replace(replace(filename, "/", "_"), ".sql", "") => "${var.sql_files_dir}/${filename}"
  }
    provisioner     "local-exec" {
    command = <<-EOF
aws athena start-query-execution --query-string file://${each.value} --output json --query-execution-context Database=${aws_athena_database.metadb.id} --result-configuration OutputLocation=s3://${aws_s3_bucket.meta_target_bucket.id}
    EOF
  }

  provisioner "local-exec" {
    when    = destroy
    command = <<EOF
aws athena start-query-execution --query-string 'DROP VIEW IF EXISTS ${each.key}' --output json --query-execution-context Database=${aws_athena_database.metadb.id} --result-configuration OutputLocation=s3://${aws_s3_bucket.meta_target_bucket.id}
     EOF
  }
}

As a result, the creation section works well, and it does this by passing the SQL code in from a file.

However, in the destroy section, I have to pass in the ‘Drop’ SQL to be executed as a string - not from a file - as it is dynamic. This is where my problem lies. Whilst the CLI output shows the command being executed appearing valid

aws athena start-query-execution --query-string 'DROP VIEW IF EXISTS Query6' --output json --query-execution-context Database=mydb --result-configuration OutputLocation=s3://mybucket

I get the following:

Error: Error running command 'aws athena start-query-execution --query-string ‘DROP VIEW IF EXISTS Query6’ --output json --query-execution-context Database=mydb --result-configuration OutputLocation=s3://mybucket ': exit status 255. Output: usage: aws [options] [ …] [parameters] To see help text, you can run:

What confuses mem is that if I copy that command and paste it into CLI without the TF, then it executes perfectly. Any ideas as to why it will not execute when run by the provisioner ??

2

Did you find any solutions to this?

3

No, I now use a lambda function & python to read my files and execute calls to Athena.