google-bigqueryterraformterraform-template-fileterraform-google-cloud

Terraform dynamically assign value using for_each


I am attempting to use for_each in combination with fileset() to provision multiple scheduled queries in GCP using Terraform. So far, I've successfully used the following configuration:

resource "google_bigquery_data_transfer_config" "dashboard_queries" {

  for_each                  = fileset("${path.module}/templates/performance", "*.sql")
  data_source_id            = "scheduled_query"
  location                  = var.bq_region
  destination_dataset_id    = "monitoring"
  display_name              = "${substr(each.value, 0, length(each.value) -4)}"
  schedule                  = "every 6 hours"
  disabled                  = false

  params                    = {
    destination_table_name_template = substr(each.value,0,length(each.value) -4)
    write_disposition               = "WRITE_APPEND"
    query                           = templatefile("${path.module}/templates/performance/${each.value}", {
      user_mail             = var.user_mail_query 
      events_table          = var.events_table
      ingest_table          = var.ingest_table 
    })
  }
}

It works perfectly in my case, since the number of scheduled queries to be added will eventually grow, and I will not have to manually update the resource part of the configuration (for the most part at least).

However, I have now realized I need to run different queries at different times, so the schedule = "every 6 hours" needs to change dynamically, depending on which template file we're referencing in the for-loop. How would I go about doing this if I want to retain the current structure where I use the fileset() as my for_each?

I'm thinking I can create a locals block with a list of objects and then change my for_each as follows (I'm not confident in this approach and I do not want to jump the gun just yet):

locals {
  queries = [
    {
      file     = "query_1.sql",
      name     = "query_1",
      schedule = "every day at 09:00"
    },
    {
      file     = "query_2.sql",
      name     = "query_2",
      schedule = "every day at 09:05"
    },

  ]
}


resource "google_bigquery_data_transfer_config" "dashboard_queries" {

  for_each                  = {for index, q in locals.queries: q.name => q}
  data_source_id            = "scheduled_query"
  location                  = var.bq_region
  destination_dataset_id    = "monitoring"
  display_name              = each.value.name
  schedule                  = each.value.schedule
  disabled                  = false

  params                    = {
    destination_table_name_template = each.value.name
    write_disposition               = "WRITE_APPEND"
    query                           = templatefile("${path.module}/templates/performance/${each.value.file}", {
      user_mail             = var.user_mail_query 
      events_table          = var.events_table
      ingest_table          = var.ingest_table 
    })
  }

}

Let me know if there is anything I need to clarify. Thank you!


Solution

  • For anyone else who encounters a similar situation: luckily, after a little bit of research into how for-loops can be used to generate maps dynamically, I was able to find a neat solution to achieve what I wanted. See the solution below:

    /* Dynamically generate map with filename, display name and schedule values based on the number of total template files in the performance
     This map is then used to automatically assign the appropriate values in the configuration of the scheduled query resources being provisioned below. */
    
    locals {
    
      files     = [ for file in fileset("${path.module}/templates/performance", "*.sql"): tostring(file) ]
      names     = [ for name in fileset("${path.module}/templates/performance", "*.sql"): substr(name, 0, length(name) -4) ]
      total     = length(local.files)
      schedules = [ 
        "every day 09:10",
        "every day 09:10",
        "every day 09:00",
        "every day 09:00",
        "every day 09:05",
        "every day 09:00" 
      ]
      query_config = [ 
        for i in range(0, local.total): {
            file     = tolist(local.files)[i]
            name     = tolist(local.names)[i]
            schedule = tolist(local.schedules)[i]
        }
      ]
    }
    
    
    
    
    /* Creates the 6 scheduled queries that run regular updates to feed the tables used by the performance section of dashboard.  */
    
    resource "google_bigquery_data_transfer_config" "dashboard_queries" {
    
      for_each                  = {for index, q in local.query_config: q.name => q}
      data_source_id            = "scheduled_query"
      location                  = var.bq_region
      destination_dataset_id    = "monitoring"
      display_name              = each.value.name
      schedule                  = each.value.schedule
      disabled                  = false
    
      params = {
        destination_table_name_template = each.value.name
        write_disposition = "WRITE_APPEND"
        query = templatefile("${path.module}/templates/performance/${each.value.file}", {
          user_mail             = var.user_mail_query 
          events_table          = var.events_table
          ingest_table          = var.ingest_table 
        })
      }
    }