When trying to create Data Transfer from MySQL to BigQuery using Terraform, on Google Cloud I receive the below error, and using this syntax 'connector.authentication.password' in Terraform make it looks like I'm referencing a resource in Terrafrom, which is I'm not, so how to fix?

module.bq.google_bigquery_data_transfer_config.mysql_to_bq_transfer: Creating...
╷
│ Error: Error creating Config: googleapi: Error 400: Parameters: fields {
│ key: "data_path"
│ value {
│ string_value: "TABLE_NAME"
│ }
│ }
│ fields {
│ key: "write_disposition"
│ value {
│ string_value: "APPEND"
│ }
│ }
│ Missing parameter: connector.endpoint.host. Config name:
│ 
│ with module.bq.google_bigquery_data_transfer_config.mysql_to_bq_transfer,
│ on ../../modules/bq/main.tf line 26, in resource "google_bigquery_data_transfer_config" "mysql_to_bq_transfer":
│ 26: resource "google_bigquery_data_transfer_config" "mysql_to_bq_transfer" {

resource "google_bigquery_data_transfer_config" "mysql_to_bq_transfer" {
  display_name           = "TABLE_NAME"
  location              = "EU"
  data_source_id        = "mysql"
  destination_dataset_id = google_bigquery_dataset.bq_dataset.dataset_id
  schedule = "every 24 hours"
  service_account_name = google_bigquery_connection.bq_mysql_connection.connection_id

  params = {
    destination_table_name_template = "TABLE_NAME"
    write_disposition                = "APPEND"
    query                            = "SELECT * FROM 'TABLE_NAME';"
    database_name                   = "DB_NAME"
    data_path                        = "TABLE_NAME"
    username                         = "USERNAME"
    password                         = data.google_secret_manager_secret_version.mysql_password.secret_data
  }
}

When trying to create Data Transfer from MySQL to BigQuery using Terraform, on Google Cloud I receive the below error, and using this syntax 'connector.authentication.password' in Terraform make it looks like I'm referencing a resource in Terrafrom, which is I'm not, so how to fix?

module.bq.google_bigquery_data_transfer_config.mysql_to_bq_transfer: Creating...
╷
│ Error: Error creating Config: googleapi: Error 400: Parameters: fields {
│ key: "data_path"
│ value {
│ string_value: "TABLE_NAME"
│ }
│ }
│ fields {
│ key: "write_disposition"
│ value {
│ string_value: "APPEND"
│ }
│ }
│ Missing parameter: connector.endpoint.host. Config name:
│ 
│ with module.bq.google_bigquery_data_transfer_config.mysql_to_bq_transfer,
│ on ../../modules/bq/main.tf line 26, in resource "google_bigquery_data_transfer_config" "mysql_to_bq_transfer":
│ 26: resource "google_bigquery_data_transfer_config" "mysql_to_bq_transfer" {

resource "google_bigquery_data_transfer_config" "mysql_to_bq_transfer" {
  display_name           = "TABLE_NAME"
  location              = "EU"
  data_source_id        = "mysql"
  destination_dataset_id = google_bigquery_dataset.bq_dataset.dataset_id
  schedule = "every 24 hours"
  service_account_name = google_bigquery_connection.bq_mysql_connection.connection_id

  params = {
    destination_table_name_template = "TABLE_NAME"
    write_disposition                = "APPEND"
    query                            = "SELECT * FROM 'TABLE_NAME';"
    database_name                   = "DB_NAME"
    data_path                        = "TABLE_NAME"
    username                         = "USERNAME"
    password                         = data.google_secret_manager_secret_version.mysql_password.secret_data
  }
}

• google-cloud-platform
• terraform

• Hello @devops on the road, Could you please add connector.endpoint.host and connector.endpoint.port in parameters and try again. Refer to this documentation and does this resolves your issue? – PUTHINEEDI RAMOJI RAO Commented Feb 6 at 5:18

1 Answer 1

Fixed by putting it in "

resource "google_bigquery_data_transfer_config" "mysql_to_bq_transfer2" {
  display_name           = "NAME"
  location               = "EU"
  data_source_id         = "mysql"
  destination_dataset_id = google_bigquery_dataset.bq_dataset.dataset_id
  schedule               = "every 24 hours"
  service_account_name   = google_service_account.bq_service_account.account_id

  params = {
    assets                              = "
[\"course_overviews_courseoverview\", ]"
    "connector.endpoint.host"           = var.sql_instance.private_ip_address
    "connector.endpoint.port"           = 3306
    "connector.database"                = ""
    "connector.authentication.username" = ""
    "connector.authentication.password" = data.google_secret_manager_secret_version.mysql_password.secret_data
  }
}