# AWS Athena : Create table/view with sql DDL

**URL:** <https://discuss.hashicorp.com/t/aws-athena-create-table-view-with-sql-ddl/6743>\
**Category:** Terraform\
**Created:** [March 12, 2020, 9:05am UTC](https://discuss.hashicorp.com/t/aws-athena-create-table-view-with-sql-ddl/6743 "2020-03-12T09:05:50Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![simonB2020](https://sea2.discourse-cdn.com/hashicorp/user_avatar/discuss.hashicorp.com/simonb2020/32/5272_2.png) [@simonB2020](https://discuss.hashicorp.com/u/simonB2020)\
**Post date:** [March 12, 2020, 9:05am UTC](https://discuss.hashicorp.com/t/aws-athena-create-table-view-with-sql-ddl/6743/1 "2020-03-12T09:05:50Z")

</div>

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 ??

---

<div class="post-metadata">

**Author:** ![slavekjurkowski2](https://sea2.discourse-cdn.com/hashicorp/user_avatar/discuss.hashicorp.com/slavekjurkowski2/32/19592_2.png) [@slavekjurkowski2](https://discuss.hashicorp.com/u/slavekjurkowski2)\
**Post date:** [May 24, 2021, 9:55pm UTC](https://discuss.hashicorp.com/t/aws-athena-create-table-view-with-sql-ddl/6743/2 "2021-05-24T21:55:26Z")

</div>

Did you find any solutions to this?

---

<div class="post-metadata">

**Author:** ![simonB2020](https://sea2.discourse-cdn.com/hashicorp/user_avatar/discuss.hashicorp.com/simonb2020/32/5272_2.png) [@simonB2020](https://discuss.hashicorp.com/u/simonB2020)\
**Post date:** [May 25, 2021, 8:12am UTC](https://discuss.hashicorp.com/t/aws-athena-create-table-view-with-sql-ddl/6743/3 "2021-05-25T08:12:59Z")

</div>

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