Google BigQuery Routine

google_bigquery_routine (Terraform)

The Routine in BigQuery can be configured in Terraform with the resource name google_bigquery_routine. The following sections describe 2 examples of how to use the resource and its parameters.

Example Usage from GitHub

GoogleCloudPlatform/professional-services

resource "google_bigquery_routine" "IP_FROM_CIDR_STRING" {
  project      = var.logs_project_id
  dataset_id   = var.dataset_name
  routine_id   = "IP_FROM_CIDR_STRING"
  language     = "SQL"
  routine_type = "SCALAR_FUNCTION"

bmwieczorek/my-apache-beam-dataflow

resource "google_bigquery_routine" "my_sum_udf" {
  project         = var.project
  dataset_id      = google_bigquery_dataset.dataset.dataset_id
  routine_id      = "my_sum"
  routine_type    = "SCALAR_FUNCTION"
  //  language        = "SQL"

Parameters

• creation_time optional computed - number

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

• dataset_id required - string

The ID of the dataset containing this routine

• definition_body required - string

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.

• description optional - string

The description of the routine if defined.

• determinism_level optional - string

The determinism level of the JavaScript UDF if defined. Possible values: ["DETERMINISM_LEVEL_UNSPECIFIED", "DETERMINISTIC", "NOT_DETERMINISTIC"]

• id optional computed - string
• imported_libraries optional - list of string

Optional. If language = "JAVASCRIPT", this field stores the path of the imported JAVASCRIPT libraries.

• language optional - string

The language of the routine. Possible values: ["SQL", "JAVASCRIPT"]

• last_modified_time optional computed - number

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

• project optional computed - string
• return_type optional - string

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.

• routine_id required - string

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.

• routine_type optional - string

The type of routine. Possible values: ["SCALAR_FUNCTION", "PROCEDURE"]

• arguments list block
  • argument_kind optional - string

  Defaults to FIXED_TYPE. Default value: "FIXED_TYPE" Possible values: ["FIXED_TYPE", "ANY_TYPE"]

  • data_type optional - string

  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.

  • mode optional - string

  Specifies whether the argument is input or output. Can be set for procedures only. Possible values: ["IN", "OUT", "INOUT"]

  • name optional - string

  The name of this argument. Can be absent for function return argument.

• timeouts single block
  • create optional - string
  • delete optional - string
  • update optional - string

Explanation in Terraform Registry

A user-defined function or a stored procedure that belongs to a Dataset To get more information about Routine, see:

• API documentation
• How-to Guides
  • Routines Intro

Tips: Best Practices for The Other Google BigQuery Resources

In addition to the google_bigquery_dataset, Google BigQuery has the other resources that should be configured for security reasons. Please check some examples of those resources and precautions.

google_bigquery_dataset

Ensure your BigQuery dataset blocks unwanted access

It is better to block unwanted access from users outside the organization.