Skip to content

googleBigqueryRoutine

A user-defined function or a stored procedure that belongs to a Dataset

To get more information about Routine, see:

Open in Cloud Shell

Example Usage - Big Query Routine Basic

/*Provider bindings are generated by running cdktf get.
See https://cdk.tf/provider-generation for more details.*/
import * as google from "./.gen/providers/google";
/*The following providers are missing schema information and might need manual adjustments to synthesize correctly: google.
For a more precise conversion please use the --provider flag in convert.*/
const googleBigqueryDatasetTest = new google.bigqueryDataset.BigqueryDataset(
  this,
  "test",
  {
    dataset_id: "dataset_id",
  }
);
new google.bigqueryRoutine.BigqueryRoutine(this, "sproc", {
  dataset_id: googleBigqueryDatasetTest.datasetId,
  definition_body:
    "CREATE FUNCTION Add(x FLOAT64, y FLOAT64) RETURNS FLOAT64 AS (x + y);",
  language: "SQL",
  routine_id: "routine_id",
  routine_type: "PROCEDURE",
});
Open in Cloud Shell

Example Usage - Big Query Routine Json

resource "google_bigquery_dataset" "test" {
    dataset_id = "dataset_id"
}

resource "google_bigquery_routine" "sproc" {
  dataset_id = google_bigquery_dataset.test.dataset_id
  routine_id     = "tf_test_routine_id%{random_suffix}"
  routine_type = "SCALAR_FUNCTION"
  language = "JAVASCRIPT"
  definition_body = "CREATE FUNCTION multiplyInputs return x*y;"
  arguments {
    name = "x"
    data_type = "{\"typeKind\" :  \"FLOAT64\"}"
  } 
  arguments {
    name = "y"
    data_type = "{\"typeKind\" :  \"FLOAT64\"}"
  }

  return_type = "{\"typeKind\" :  \"FLOAT64\"}"
}
Open in Cloud Shell

Example Usage - Big Query Routine Tvf

resource "google_bigquery_dataset" "test" {
    dataset_id = "dataset_id"
}

resource "google_bigquery_routine" "sproc" {
  dataset_id      = google_bigquery_dataset.test.dataset_id
  routine_id      = "tf_test_routine_id%{random_suffix}"
  routine_type    = "TABLE_VALUED_FUNCTION"
  language        = "SQL"
  definition_body = <<-EOS
    SELECT 1 + value AS value
  EOS
  arguments {
    name          = "value"
    argument_kind = "FIXED_TYPE"
    data_type     = jsonencode({ "typeKind" : "INT64" })
  }
  return_table_type = jsonencode({"columns" : [
    { "name" : "value", "type" : { "typeKind" : "INT64" } },
  ] })
}

Argument Reference

The following arguments are supported:

  • datasetId - (Required) The ID of the dataset containing this routine

  • routineId - (Required) The ID of the the routine. The ID must contain only letters (a-z, A-Z), numbers (0-9), or underscores (_). The maximum length is 256 characters.

  • definitionBody - (Required) The body of the routine. For functions, this is the expression in the AS clause. If language=SQL, it is the substring inside (but excluding) the parentheses.

  • routineType - (Optional) The type of routine. Possible values are scalarFunction, procedure, and tableValuedFunction.

  • language - (Optional) The language of the routine. Possible values are sql and javascript.

  • arguments - (Optional) Input/output argument of a function or a stored procedure. Structure is documented below.

  • returnType - (Optional) A JSON schema for the return type. Optional if language = "SQL"; required otherwise. If absent, the return type is inferred from definitionBody at query time in each query that references this routine. If present, then the evaluated result will be cast to the specified returned type at query time. ~>NOTE: Because this field expects a JSON string, any changes to the string will create a diff, even if the JSON itself hasn't changed. If the API returns a different value for the same schema, e.g. it switche d the order of values or replaced STRUCT field type with RECORD field type, we currently cannot suppress the recurring diff this causes. As a workaround, we recommend using the schema as returned by the API.

  • returnTableType - (Optional) Optional. Can be set only if routineType = "TABLE_VALUED_FUNCTION". If absent, the return table type is inferred from definitionBody at query time in each query that references this routine. If present, then the columns in the evaluated table result will be cast to match the column types specificed in return table type, at query time.

  • importedLibraries - (Optional) Optional. If language = "JAVASCRIPT", this field stores the path of the imported JAVASCRIPT libraries.

  • description - (Optional) The description of the routine if defined.

  • determinismLevel - (Optional) The determinism level of the JavaScript UDF if defined. Possible values are determinismLevelUnspecified, deterministic, and notDeterministic.

  • project - (Optional) The ID of the project in which the resource belongs. If it is not provided, the provider project is used.

The arguments block supports:

  • name - (Optional) The name of this argument. Can be absent for function return argument.

  • argumentKind - (Optional) Defaults to FIXED_TYPE. Default value is fixedType. Possible values are fixedType and anyType.

  • mode - (Optional) Specifies whether the argument is input or output. Can be set for procedures only. Possible values are in, out, and inout.

  • dataType - (Optional) A JSON schema for the data type. Required unless argumentKind = ANY_TYPE. \~>NOTE: Because this field expects a JSON string, any changes to the string will create a diff, even if the JSON itself hasn't changed. If the API returns a different value for the same schema, e.g. it switched the order of values or replaced STRUCT field type with RECORD field type, we currently cannot suppress the recurring diff this causes. As a workaround, we recommend using the schema as returned by the API.

Attributes Reference

In addition to the arguments listed above, the following computed attributes are exported:

  • id - an identifier for the resource with format projects/{{project}}/datasets/{{datasetId}}/routines/{{routineId}}

  • creationTime - The time when this routine was created, in milliseconds since the epoch.

  • lastModifiedTime - The time when this routine was modified, in milliseconds since the epoch.

Timeouts

This resource provides the following Timeouts configuration options:

  • create - Default is 20 minutes.
  • update - Default is 20 minutes.
  • delete - Default is 20 minutes.

Import

Routine can be imported using any of these accepted formats:

$ terraform import google_bigquery_routine.default projects/{{project}}/datasets/{{dataset_id}}/routines/{{routine_id}}
$ terraform import google_bigquery_routine.default {{project}}/{{dataset_id}}/{{routine_id}}
$ terraform import google_bigquery_routine.default {{dataset_id}}/{{routine_id}}

User Project Overrides

This resource supports User Project Overrides.