Streamline your data analytics with AWS Athena queries and Terraform
Tags: aws, todayilearned, tutorial, terraform
Published: 2023-03-29 19:01:19


Part I of this article will detail how to streamline your data analytics with AWS Athena using Terraform Infrastructure as Code.

**Requirements**
- AWS account
- Terraform

## Walkthrough
Configure AWS credentials with `aws configure`.

At the root of our project, create a file named `provider.tf` which we will specify the aws provider. Also we are going to read dynamically our columns and declare a variable for each column.

```tf
#provider.tf
terraform {
  required_providers {
    aws = {
      source  = "hashicorp/aws"
      version = "~> 4.59.0"
    }
  }
}
```

Next, let's define an S3 bucket in a file named `s3.tf` which we will use as a source of data.

```tf
#s3.tf
resource "aws_s3_bucket" "log_bucket" {
  bucket = "my-tf-log-bucket-source-athena"
}
```

Now we will define a file named `athena.tf`, in this file we are going to create an athena workgroup,db, table, and our query.

For our aws_glue_catalog_table we are expecting the table to serealize a json object.

> Note: Is important to note that our json files should have no spaces, otherwise it won't parse well other alternative could be the use of LazySimpleSerDe which could convert from byte stream

```tf
#athena.tf
resource "aws_glue_catalog_database" "myservice_athena_db" {
  name = "myservice"
}

resource "aws_glue_catalog_table" "athena_table" {
 name          = "mytable"
 database_name = aws_glue_catalog_database.myservice_athena_db.name
 description   = "Table containing the results stored in S3 as source"


 table_type = "EXTERNAL_TABLE"


 storage_descriptor {
   location      = "s3://${aws_s3_bucket.log_bucket.bucket}/mydata"
   input_format  = "org.apache.hadoop.mapred.TextInputFormat"
   output_format = "org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat"


   ser_de_info {
     name                  = "s3-stream"
     serialization_library = "org.openx.data.jsonserde.JsonSerDe"


     parameters = {
       "ignore.malformed.json" = "TRUE"
       "dots.in.keys"          = "FALSE"
       "case.insensitive"      = "TRUE"
       "mapping"               = "TRUE"
     }
   }


   dynamic "columns" {
     for_each = var.columns


     iterator = column
     content {
       name = column.value.name
       type = column.value.type
     }
   }
 }
}

resource "aws_athena_workgroup" "test" {
  name = "example123123s"

  configuration {
    enforce_workgroup_configuration    = true
    publish_cloudwatch_metrics_enabled = true

    result_configuration {
      output_location = "s3://${aws_s3_bucket.log_bucket.bucket}/output/"

    }
  }
}

resource "aws_athena_named_query" "athena_query" {
  name      = "test_query"
  workgroup = aws_athena_workgroup.test.id
  database  = aws_glue_catalog_database.myservice_athena_db.name
  query     = "SELECT json_extract_scalar(person, '$.name') as name, product as source FROM myservice.mytable;"
}
```
Our json example should look like this (but without spaces)
```json
{
  "product": "Live JSON generator",
  "version": 3.1,
  "releaseDate": "2014-06-25T00:00:00.000Z",
  "demo": true,
  "person": {
    "id": 12345,
    "name": "John Doe",
    "phones": {
      "home": "800-123-4567",
      "mobile": "877-123-1234"
    },
    "email": [
      "jd@example.com",
      "jd@example.org"
    ],
    "dateOfBirth": "1980-01-02T00:00:00.000Z",
    "registered": true,
    "emergencyContacts": [
      {
        "name": "Jane Doe",
        "phone": "888-555-1212",
        "relationship": "spouse"
      },
      {
        "name": "Justin Doe",
        "phone": "877-123-1212",
        "relationship": "parent"
      }
    ]
  }
}
```
In our variables.tf we will dynamically call all the columns
```tf
#variables.tf
variable "columns" {
  type = list(object({
    name = string,
    type = string,
  }))
  default     = []
  description = "The columns in the table, where the key is the name of the column and the value the type"
}
```
```tf
#terraform.tfvars
columns = [
    {
      name = "product"
      type = "string"
    },

     {
      name = "version"
      type = "int"
    },

     {
      name = "releaseDate"
      type = "string"
    },

     { 
      name = "demo" 
      type = "boolean" 
    } ,
     {
      name = "person"
      type = "string"
    }
]
```
After finishing our files let's use terraform to create our resources.
```
terraform init
```

![Plan init](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qsxowebiwhm4r9rrmqgg.png)


```
terraform plan
```

![Plan creation](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mz2u6w867ea82pxkxarg.png)


```
terraform apply
```

![Plan apply](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jlrnd98pvt8cczlhyaks.png)
Update our data.json file through s3 commands or directly in the interface
```
aws s3 cp data.json s3://<<your_bucket_name>>/mydata/
```
Now in Athena service in our AWS account we can see our db, table and saved query, running our query should return a value like this.

![Athena UI](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0siyemp4yy79kjhwpth8.png)
And save the results in our bucket.

![Bucket](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/10ignezo2vjishtqht4l.png)