---
# ----------------------------------------------------------------------------
#
#     ***     AUTO GENERATED CODE    ***    Type: MMv1     ***
#
# ----------------------------------------------------------------------------
#
#     This file is automatically generated by Magic Modules and manual
#     changes will be clobbered when the file is regenerated.
#
#     Please read more about how to change this file in
#     .github/CONTRIBUTING.md.
#
# ----------------------------------------------------------------------------
subcategory: "BigQuery"
description: |-
  A user-defined function or a stored procedure that belongs to a Dataset
---

# google\_bigquery\_routine

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


To get more information about Routine, see:

* [API documentation](https://cloud.google.com/bigquery/docs/reference/rest/v2/routines)
* How-to Guides
    * [Routines Intro](https://cloud.google.com/bigquery/docs/reference/rest/v2/routines)

<div class = "oics-button" style="float: right; margin: 0 0 -15px">
  <a href="https://console.cloud.google.com/cloudshell/open?cloudshell_git_repo=https%3A%2F%2Fgithub.com%2Fterraform-google-modules%2Fdocs-examples.git&cloudshell_working_dir=big_query_routine_basic&cloudshell_image=gcr.io%2Fcloudshell-images%2Fcloudshell%3Alatest&open_in_editor=main.tf&cloudshell_print=.%2Fmotd&cloudshell_tutorial=.%2Ftutorial.md" target="_blank">
    <img alt="Open in Cloud Shell" src="//gstatic.com/cloudssh/images/open-btn.svg" style="max-height: 44px; margin: 32px auto; max-width: 100%;">
  </a>
</div>
## Example Usage - Big Query Routine Basic


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

resource "google_bigquery_routine" "sproc" {
  dataset_id = google_bigquery_dataset.test.dataset_id
  routine_id     = "routine_id"
  routine_type = "PROCEDURE"
  language = "SQL"
  definition_body = "CREATE FUNCTION Add(x FLOAT64, y FLOAT64) RETURNS FLOAT64 AS (x + y);"
}
```
<div class = "oics-button" style="float: right; margin: 0 0 -15px">
  <a href="https://console.cloud.google.com/cloudshell/open?cloudshell_git_repo=https%3A%2F%2Fgithub.com%2Fterraform-google-modules%2Fdocs-examples.git&cloudshell_working_dir=big_query_routine_json&cloudshell_image=gcr.io%2Fcloudshell-images%2Fcloudshell%3Alatest&open_in_editor=main.tf&cloudshell_print=.%2Fmotd&cloudshell_tutorial=.%2Ftutorial.md" target="_blank">
    <img alt="Open in Cloud Shell" src="//gstatic.com/cloudssh/images/open-btn.svg" style="max-height: 44px; margin: 32px auto; max-width: 100%;">
  </a>
</div>
## Example Usage - Big Query Routine Json


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

resource "google_bigquery_routine" "sproc" {
  dataset_id = google_bigquery_dataset.test.dataset_id
  routine_id     = "routine_id"
  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\"}"
}
```
<div class = "oics-button" style="float: right; margin: 0 0 -15px">
  <a href="https://console.cloud.google.com/cloudshell/open?cloudshell_git_repo=https%3A%2F%2Fgithub.com%2Fterraform-google-modules%2Fdocs-examples.git&cloudshell_working_dir=big_query_routine_tvf&cloudshell_image=gcr.io%2Fcloudshell-images%2Fcloudshell%3Alatest&open_in_editor=main.tf&cloudshell_print=.%2Fmotd&cloudshell_tutorial=.%2Ftutorial.md" target="_blank">
    <img alt="Open in Cloud Shell" src="//gstatic.com/cloudssh/images/open-btn.svg" style="max-height: 44px; margin: 32px auto; max-width: 100%;">
  </a>
</div>
## Example Usage - Big Query Routine Tvf


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

resource "google_bigquery_routine" "sproc" {
  dataset_id      = google_bigquery_dataset.test.dataset_id
  routine_id      = "routine_id"
  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" } },
  ] })
}
```
<div class = "oics-button" style="float: right; margin: 0 0 -15px">
  <a href="https://console.cloud.google.com/cloudshell/open?cloudshell_git_repo=https%3A%2F%2Fgithub.com%2Fterraform-google-modules%2Fdocs-examples.git&cloudshell_working_dir=big_query_routine_pyspark&cloudshell_image=gcr.io%2Fcloudshell-images%2Fcloudshell%3Alatest&open_in_editor=main.tf&cloudshell_print=.%2Fmotd&cloudshell_tutorial=.%2Ftutorial.md" target="_blank">
    <img alt="Open in Cloud Shell" src="//gstatic.com/cloudssh/images/open-btn.svg" style="max-height: 44px; margin: 32px auto; max-width: 100%;">
  </a>
</div>
## Example Usage - Big Query Routine Pyspark


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

resource "google_bigquery_connection" "test" {
  connection_id = "connection_id"
  location      = "US"
  spark { }
}

resource "google_bigquery_routine" "pyspark" {
  dataset_id      = google_bigquery_dataset.test.dataset_id
  routine_id      = "routine_id"
  routine_type    = "PROCEDURE"
  language        = "PYTHON"
  definition_body = <<-EOS
    from pyspark.sql import SparkSession

    spark = SparkSession.builder.appName("spark-bigquery-demo").getOrCreate()
    
    # Load data from BigQuery.
    words = spark.read.format("bigquery") \
      .option("table", "bigquery-public-data:samples.shakespeare") \
      .load()
    words.createOrReplaceTempView("words")
    
    # Perform word count.
    word_count = words.select('word', 'word_count').groupBy('word').sum('word_count').withColumnRenamed("sum(word_count)", "sum_word_count")
    word_count.show()
    word_count.printSchema()
    
    # Saving the data to BigQuery
    word_count.write.format("bigquery") \
      .option("writeMethod", "direct") \
      .save("wordcount_dataset.wordcount_output")
  EOS
  spark_options {
    connection          = google_bigquery_connection.test.name
    runtime_version     = "2.1"
  }
}
```
<div class = "oics-button" style="float: right; margin: 0 0 -15px">
  <a href="https://console.cloud.google.com/cloudshell/open?cloudshell_git_repo=https%3A%2F%2Fgithub.com%2Fterraform-google-modules%2Fdocs-examples.git&cloudshell_working_dir=big_query_routine_pyspark_mainfile&cloudshell_image=gcr.io%2Fcloudshell-images%2Fcloudshell%3Alatest&open_in_editor=main.tf&cloudshell_print=.%2Fmotd&cloudshell_tutorial=.%2Ftutorial.md" target="_blank">
    <img alt="Open in Cloud Shell" src="//gstatic.com/cloudssh/images/open-btn.svg" style="max-height: 44px; margin: 32px auto; max-width: 100%;">
  </a>
</div>
## Example Usage - Big Query Routine Pyspark Mainfile


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

resource "google_bigquery_connection" "test" {
  connection_id = "connection_id"
  location      = "US"
  spark { }
}

resource "google_bigquery_routine" "pyspark_mainfile" {
  dataset_id      = google_bigquery_dataset.test.dataset_id
  routine_id      = "routine_id"
  routine_type    = "PROCEDURE"
  language        = "PYTHON"
  definition_body = ""
  spark_options {
    connection      = google_bigquery_connection.test.name
    runtime_version = "2.1"
    main_file_uri   = "gs://test-bucket/main.py"
    py_file_uris    = ["gs://test-bucket/lib.py"]
    file_uris       = ["gs://test-bucket/distribute_in_executor.json"]
    archive_uris    = ["gs://test-bucket/distribute_in_executor.tar.gz"]
  }
}
```
<div class = "oics-button" style="float: right; margin: 0 0 -15px">
  <a href="https://console.cloud.google.com/cloudshell/open?cloudshell_git_repo=https%3A%2F%2Fgithub.com%2Fterraform-google-modules%2Fdocs-examples.git&cloudshell_working_dir=big_query_routine_spark_jar&cloudshell_image=gcr.io%2Fcloudshell-images%2Fcloudshell%3Alatest&open_in_editor=main.tf&cloudshell_print=.%2Fmotd&cloudshell_tutorial=.%2Ftutorial.md" target="_blank">
    <img alt="Open in Cloud Shell" src="//gstatic.com/cloudssh/images/open-btn.svg" style="max-height: 44px; margin: 32px auto; max-width: 100%;">
  </a>
</div>
## Example Usage - Big Query Routine Spark Jar


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

resource "google_bigquery_connection" "test" {
  connection_id = "connection_id"
  location      = "US"
  spark { }
}

resource "google_bigquery_routine" "spark_jar" {
  dataset_id      = google_bigquery_dataset.test.dataset_id
  routine_id      = "routine_id"
  routine_type    = "PROCEDURE"
  language        = "SCALA"
  definition_body = ""
  spark_options {
    connection      = google_bigquery_connection.test.name
    runtime_version = "2.1"
    container_image = "gcr.io/my-project-id/my-spark-image:latest"
    main_class      = "com.google.test.jar.MainClass"
    jar_uris        = [ "gs://test-bucket/uberjar_spark_spark3.jar" ]
    properties      = {
      "spark.dataproc.scaling.version" : "2",
      "spark.reducer.fetchMigratedShuffle.enabled" : "true",
    }
  }
}
```

## Argument Reference

The following arguments are supported:


* `dataset_id` -
  (Required)
  The ID of the dataset containing this routine

* `routine_id` -
  (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.

* `routine_type` -
  (Required)
  The type of routine.
  Possible values are: `SCALAR_FUNCTION`, `PROCEDURE`, `TABLE_VALUED_FUNCTION`.

* `definition_body` -
  (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.


- - -


* `language` -
  (Optional)
  The language of the routine.
  Possible values are: `SQL`, `JAVASCRIPT`, `PYTHON`, `JAVA`, `SCALA`.

* `arguments` -
  (Optional)
  Input/output argument of a function or a stored procedure.
  Structure is [documented below](#nested_arguments).

* `return_type` -
  (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.

* `return_table_type` -
  (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.

* `imported_libraries` -
  (Optional)
  Optional. If language = "JAVASCRIPT", this field stores the path of the
  imported JAVASCRIPT libraries.

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

* `determinism_level` -
  (Optional)
  The determinism level of the JavaScript UDF if defined.
  Possible values are: `DETERMINISM_LEVEL_UNSPECIFIED`, `DETERMINISTIC`, `NOT_DETERMINISTIC`.

* `spark_options` -
  (Optional)
  Optional. If language is one of "PYTHON", "JAVA", "SCALA", this field stores the options for spark stored procedure.
  Structure is [documented below](#nested_spark_options).

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


<a name="nested_arguments"></a>The `arguments` block supports:

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

* `argument_kind` -
  (Optional)
  Defaults to FIXED_TYPE.
  Default value is `FIXED_TYPE`.
  Possible values are: `FIXED_TYPE`, `ANY_TYPE`.

* `mode` -
  (Optional)
  Specifies whether the argument is input or output. Can be set for procedures only.
  Possible values are: `IN`, `OUT`, `INOUT`.

* `data_type` -
  (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.

<a name="nested_spark_options"></a>The `spark_options` block supports:

* `connection` -
  (Optional)
  Fully qualified name of the user-provided Spark connection object.
  Format: "projects/{projectId}/locations/{locationId}/connections/{connectionId}"

* `runtime_version` -
  (Optional)
  Runtime version. If not specified, the default runtime version is used.

* `container_image` -
  (Optional)
  Custom container image for the runtime environment.

* `properties` -
  (Optional)
  Configuration properties as a set of key/value pairs, which will be passed on to the Spark application.
  For more information, see Apache Spark and the procedure option list.
  An object containing a list of "key": value pairs. Example: { "name": "wrench", "mass": "1.3kg", "count": "3" }.

* `main_file_uri` -
  (Optional)
  The main file/jar URI of the Spark application.
  Exactly one of the definitionBody field and the mainFileUri field must be set for Python.
  Exactly one of mainClass and mainFileUri field should be set for Java/Scala language type.

* `py_file_uris` -
  (Optional)
  Python files to be placed on the PYTHONPATH for PySpark application. Supported file types: .py, .egg, and .zip. For more information about Apache Spark, see Apache Spark.

* `jar_uris` -
  (Optional)
  JARs to include on the driver and executor CLASSPATH. For more information about Apache Spark, see Apache Spark.

* `file_uris` -
  (Optional)
  Files to be placed in the working directory of each executor. For more information about Apache Spark, see Apache Spark.

* `archive_uris` -
  (Optional)
  Archive files to be extracted into the working directory of each executor. For more information about Apache Spark, see Apache Spark.

* `main_class` -
  (Optional)
  The fully qualified name of a class in jarUris, for example, com.example.wordcount.
  Exactly one of mainClass and main_jar_uri field should be set for Java/Scala language type.

## 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/{{dataset_id}}/routines/{{routine_id}}`

* `creation_time` -
  The time when this routine was created, in milliseconds since the
  epoch.

* `last_modified_time` -
  The time when this routine was modified, in milliseconds since the
  epoch.


## Timeouts

This resource provides the following
[Timeouts](https://developer.hashicorp.com/terraform/plugin/sdkv2/resources/retries-and-customizable-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:

* `projects/{{project}}/datasets/{{dataset_id}}/routines/{{routine_id}}`
* `{{project}}/{{dataset_id}}/{{routine_id}}`
* `{{dataset_id}}/{{routine_id}}`


In Terraform v1.5.0 and later, use an [`import` block](https://developer.hashicorp.com/terraform/language/import) to import Routine using one of the formats above. For example:

```tf
import {
  id = "projects/{{project}}/datasets/{{dataset_id}}/routines/{{routine_id}}"
  to = google_bigquery_routine.default
}
```

When using the [`terraform import` command](https://developer.hashicorp.com/terraform/cli/commands/import), Routine can be imported using one of the formats above. For example:

```
$ 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](https://registry.terraform.io/providers/hashicorp/google/latest/docs/guides/provider_reference#user_project_override).