BigQuery API - Class Google::Cloud::Bigquery::Routine (v1.41.0)

Reference documentation and code samples for the BigQuery API class Google::Cloud::Bigquery::Routine.

Routine

A user-defined function or a stored procedure.

Inherits

  • Object

Examples

Creating a new routine:

require "google/cloud/bigquery"

bigquery = Google::Cloud::Bigquery.new
dataset = bigquery.dataset "my_dataset"

routine = dataset.create_routine "my_routine" do |r|
  r.routine_type = "SCALAR_FUNCTION"
  r.language = "SQL"
  r.arguments = [
    Google::Cloud::Bigquery::Argument.new(name: "x", data_type: "INT64")
  ]
  r.body = "x * 3"
  r.description = "My routine description"
end

puts routine.routine_id

Extended example:

require "google/cloud/bigquery"

bigquery = Google::Cloud::Bigquery.new
dataset = bigquery.dataset "my_dataset"
routine = dataset.create_routine "my_routine" do |r|
  r.routine_type = "SCALAR_FUNCTION"
  r.language = :SQL
  r.body = "(SELECT SUM(IF(elem.name = \"foo\",elem.val,null)) FROM UNNEST(arr) AS elem)"
  r.arguments = [
    Google::Cloud::Bigquery::Argument.new(
      name: "arr",
      argument_kind: "FIXED_TYPE",
      data_type: Google::Cloud::Bigquery::StandardSql::DataType.new(
        type_kind: "ARRAY",
        array_element_type: Google::Cloud::Bigquery::StandardSql::DataType.new(
          type_kind: "STRUCT",
          struct_type: Google::Cloud::Bigquery::StandardSql::StructType.new(
            fields: [
              Google::Cloud::Bigquery::StandardSql::Field.new(
                name: "name",
                type: Google::Cloud::Bigquery::StandardSql::DataType.new(type_kind: "STRING")
              ),
              Google::Cloud::Bigquery::StandardSql::Field.new(
                name: "val",
                type: Google::Cloud::Bigquery::StandardSql::DataType.new(type_kind: "INT64")
              )
            ]
          )
        )
      )
    )
  ]
end

Retrieving and updating an existing routine:

require "google/cloud/bigquery"

bigquery = Google::Cloud::Bigquery.new
dataset = bigquery.dataset "my_dataset"
routine = dataset.routine "my_routine"

routine.update do |r|
  r.body = "x * 4"
  r.description = "My new routine description"
end

Methods

#arguments

def arguments() -> Array<Argument>, nil

The input/output arguments of the routine. Optional.

Returns
  • (Array<Argument>, nil) — An array of argument objects, or nil if the object is a reference (see #reference?).
Example 
require "google/cloud/bigquery"

bigquery = Google::Cloud::Bigquery.new
dataset = bigquery.dataset "my_dataset"
routine = dataset.routine "my_routine"

puts "#{routine.routine_id} arguments:"
routine.arguments.each do |arguments|
  puts "* #{arguments.name}"
end

#arguments=

def arguments=(new_arguments)

Updates the input/output arguments of the routine. Optional.

Parameter
  • new_arguments (Array<Argument>) — The new arguments.
Example 
require "google/cloud/bigquery"

bigquery = Google::Cloud::Bigquery.new
dataset = bigquery.dataset "my_dataset"
routine = dataset.routine "my_routine"

routine.arguments = [
  Google::Cloud::Bigquery::Argument.new(name: "x", data_type: "INT64")
]

#body

def body() -> String, nil

The body of the routine. Required.

For functions (#scalar_function?), this is the expression in the AS clause.

When the routine is a SQL function (#sql?), it is the substring inside (but excluding) the parentheses. For example, for the function created with the following statement: CREATE FUNCTION JoinLines(x string, y string) as (concat(x, "\n", y)) The definition_body is concat(x, "\n", y) (\n is not replaced with linebreak).

When the routine is a JavaScript function (#javascript?), it is the evaluated string in the AS clause. For example, for the function created with the following statement: CREATE FUNCTION f() RETURNS STRING LANGUAGE js AS 'return "\n";\n' The definition_body is "return &quot;\n&quot;;\n"` Note that both \n are replaced with linebreaks.

Returns
  • (String, nil) — The body of the routine, or nil if the object is a reference (see #reference?).

#body=

def body=(new_body)

Updates the body of the routine. Required.

For functions (#scalar_function?), this is the expression in the AS clause.

When the routine is a SQL function (#sql?), it is the substring inside (but excluding) the parentheses. For example, for the function created with the following statement: CREATE FUNCTION JoinLines(x string, y string) as (concat(x, "\n", y)) The definition_body is concat(x, "\n", y) (\n is not replaced with linebreak).

When the routine is a JavaScript function (#javascript?), it is the evaluated string in the AS clause. For example, for the function created with the following statement: CREATE FUNCTION f() RETURNS STRING LANGUAGE js AS 'return "\n";\n' The definition_body is "return &quot;\n&quot;;\n"` Note that both \n are replaced with linebreaks.

Parameter
  • new_body (String) — The new body of the routine.

#created_at

def created_at() -> Time, nil

The time when this routine was created.

Returns
  • (Time, nil) — The creation time, or nil if the object is a reference (see #reference?).

#dataset_id

def dataset_id() -> String

The ID of the dataset containing this routine.

Returns
  • (String) — The dataset ID.

#delete

def delete() -> Boolean

Permanently deletes the routine.

Returns
  • (Boolean) — Returns true if the routine was deleted.
Example 
require "google/cloud/bigquery"

bigquery = Google::Cloud::Bigquery.new
dataset = bigquery.dataset "my_dataset"
routine = dataset.routine "my_routine"

routine.delete

#description

def description() -> String, nil

The description of the routine if defined. Optional. [Experimental]

Returns
  • (String, nil) — The routine description, or nil if the object is a reference (see #reference?).
Example 
require "google/cloud/bigquery"

bigquery = Google::Cloud::Bigquery.new
dataset = bigquery.dataset "my_dataset"
routine = dataset.routine "my_routine"

routine.description #=> "My routine description"

#description=

def description=(new_description)

Updates the description of the routine. Optional. [Experimental]

Parameter
  • new_description (String) — The new routine description.
Example 
require "google/cloud/bigquery"

bigquery = Google::Cloud::Bigquery.new
dataset = bigquery.dataset "my_dataset"
routine = dataset.routine "my_routine"

routine.description #=> "My routine description"
routine.description = "My updated routine description"

#determinism_level

def determinism_level() -> String, nil

The JavaScript UDF determinism level. Optional.

  • DETERMINISTIC - Deterministic indicates that two calls with the same input to a UDF yield the same output. If all JavaScript UDFs are DETERMINISTIC, the query result is potentially cachable.
  • NOT_DETERMINISTIC - Not deterministic indicates that the output of the UDF is not guaranteed to yield the same output each time for a given set of inputs. If any JavaScript UDF is NOT_DETERMINISTIC, the query result is not cacheable.

Even if a JavaScript UDF is deterministic, many other factors can prevent usage of cached query results. Example factors include but not limited to: DDL/DML, non-deterministic SQL function calls, update of referenced tables/views/UDFs or imported JavaScript libraries. SQL UDFs cannot have determinism specified. Their determinism is automatically determined.

Returns
  • (String, nil) — The routine determinism level in upper case, or nil if not set or the object is a reference (see #reference?).
Example 
require "google/cloud/bigquery"

bigquery = Google::Cloud::Bigquery.new
dataset = bigquery.dataset "my_dataset"
routine = dataset.routine "my_routine"

routine.determinism_level #=> "NOT_DETERMINISTIC"

#determinism_level=

def determinism_level=(new_determinism_level)

Updates the JavaScript UDF determinism level. Optional.

  • DETERMINISTIC - Deterministic indicates that two calls with the same input to a UDF yield the same output. If all JavaScript UDFs are DETERMINISTIC, the query result is potentially cachable.
  • NOT_DETERMINISTIC - Not deterministic indicates that the output of the UDF is not guaranteed to yield the same output each time for a given set of inputs. If any JavaScript UDF is NOT_DETERMINISTIC, the query result is not cacheable.
Parameter
  • new_determinism_level (String, nil) — The new routine determinism level in upper case.
Example 
require "google/cloud/bigquery"

bigquery = Google::Cloud::Bigquery.new
dataset = bigquery.dataset "my_dataset"
routine = dataset.routine "my_routine"

routine.determinism_level #=> "NOT_DETERMINISTIC"
routine.determinism_level = "DETERMINISTIC"

#determinism_level_deterministic?

def determinism_level_deterministic?() -> Boolean

Checks if the value of #determinism_level is DETERMINISTIC. The default is false.

Returns
  • (Boolean) — true when DETERMINISTIC and the object is not a reference (see #reference?), false otherwise.

#determinism_level_not_deterministic?

def determinism_level_not_deterministic?() -> Boolean

Checks if the value of #determinism_level is NOT_DETERMINISTIC. The default is false.

Returns
  • (Boolean) — true when NOT_DETERMINISTIC and the object is not a reference (see #reference?), false otherwise.

#etag

def etag() -> String, nil

The ETag hash of the routine.

Returns
  • (String, nil) — The ETag hash, or nil if the object is a reference (see #reference?).
Example 
require "google/cloud/bigquery"

bigquery = Google::Cloud::Bigquery.new
dataset = bigquery.dataset "my_dataset"
routine = dataset.routine "my_routine"

routine.etag # "etag123456789"

#exists?

def exists?(force: false) -> Boolean

Determines whether the routine exists in the BigQuery service. The result is cached locally. To refresh state, set force to true.

Parameter
  • force (Boolean) (defaults to: false) — Force the latest resource representation to be retrieved from the BigQuery service when true. Otherwise the return value of this method will be memoized to reduce the number of API calls made to the BigQuery service. The default is false.
Returns
  • (Boolean) — true when the routine exists in the BigQuery service, false otherwise.
Example 
require "google/cloud/bigquery"

bigquery = Google::Cloud::Bigquery.new

dataset = bigquery.dataset "my_dataset"
routine = dataset.routine "my_routine", skip_lookup: true
routine.exists? #=> true

#imported_libraries

def imported_libraries() -> Array<String>, nil

The list of the Google Cloud Storage URIs of imported JavaScript libraries. Optional. Only used if #language is JAVASCRIPT (#javascript?).

Returns
  • (Array<String>, nil) — A frozen array of Google Cloud Storage URIs, e.g. ["gs://cloud-samples-data/bigquery/udfs/max-value.js"], or nil if the object is a reference (see #reference?).

#imported_libraries=

def imported_libraries=(new_imported_libraries)

Updates the list of the Google Cloud Storage URIs of imported JavaScript libraries. Optional. Only used if #language is JAVASCRIPT (#javascript?).

Parameter
  • new_imported_libraries (Array<String>, nil) — An array of Google Cloud Storage URIs, e.g. ["gs://cloud-samples-data/bigquery/udfs/max-value.js"].
Example 
require "google/cloud/bigquery"

bigquery = Google::Cloud::Bigquery.new
dataset = bigquery.dataset "my_dataset"
routine = dataset.routine "my_routine"

routine.imported_libraries = [
  "gs://cloud-samples-data/bigquery/udfs/max-value.js"
]

#javascript?

def javascript?() -> Boolean

Checks if the value of #language is JAVASCRIPT. The default is false.

Returns
  • (Boolean) — true when JAVASCRIPT and the object is not a reference (see #reference?), false otherwise.

#language

def language() -> String, nil

The programming language of routine. Optional. Defaults to "SQL".

  • SQL - SQL language.
  • JAVASCRIPT - JavaScript language.
Returns
  • (String, nil) — The language in upper case, or nil if the object is a reference (see #reference?).

#language=

def language=(new_language)

Updates the programming language of routine. Optional. Defaults to "SQL".

  • SQL - SQL language.
  • JAVASCRIPT - JavaScript language.
Parameter
  • new_language (String) — The new language in upper case.

#modified_at

def modified_at() -> Time, nil

The time when this routine was last modified.

Returns
  • (Time, nil) — The last modified time, or nil if the object is a reference (see #reference?).

#procedure?

def procedure?() -> Boolean

Checks if the value of #routine_type is PROCEDURE. The default is false.

Returns
  • (Boolean) — true when PROCEDURE and the object is not a reference (see #reference?), false otherwise.

#project_id

def project_id() -> String

The ID of the project containing this routine.

Returns
  • (String) — The project ID.

#reference?

def reference?() -> Boolean

Whether the routine was created without retrieving the resource representation from the BigQuery service.

Returns
  • (Boolean) — true when the routine is just a local reference object, false otherwise.
Example 
require "google/cloud/bigquery"

bigquery = Google::Cloud::Bigquery.new

dataset = bigquery.dataset "my_dataset"
routine = dataset.routine "my_routine", skip_lookup: true

routine.reference? #=> true
routine.reload!
routine.reference? #=> false

#refresh!

def refresh!() -> Google::Cloud::Bigquery::Routine
Alias Of: #reload!

Reloads the routine with current data from the BigQuery service.

Returns
Example

Skip retrieving the routine from the service, then load it:

require "google/cloud/bigquery"

bigquery = Google::Cloud::Bigquery.new

dataset = bigquery.dataset "my_dataset"
routine = dataset.routine "my_routine", skip_lookup: true

routine.reload!

#reload!

def reload!() -> Google::Cloud::Bigquery::Routine
Aliases

Reloads the routine with current data from the BigQuery service.

Returns
Example

Skip retrieving the routine from the service, then load it:

require "google/cloud/bigquery"

bigquery = Google::Cloud::Bigquery.new

dataset = bigquery.dataset "my_dataset"
routine = dataset.routine "my_routine", skip_lookup: true

routine.reload!

#resource?

def resource?() -> Boolean

Whether the routine was created with a resource representation from the BigQuery service.

Returns
  • (Boolean) — true when the routine was created with a resource representation, false otherwise.
Example 
require "google/cloud/bigquery"

bigquery = Google::Cloud::Bigquery.new

dataset = bigquery.dataset "my_dataset"
routine = dataset.routine "my_routine", skip_lookup: true

routine.resource? #=> false
routine.reload!
routine.resource? #=> true

#resource_full?

def resource_full?() -> Boolean

Whether the routine was created with a full resource representation from the BigQuery service.

Returns
  • (Boolean) — true when the routine was created with a full resource representation, false otherwise.
Example 
require "google/cloud/bigquery"

bigquery = Google::Cloud::Bigquery.new

dataset = bigquery.dataset "my_dataset"
routine = dataset.routine "my_routine"

routine.resource_full? #=> true

#resource_partial?

def resource_partial?() -> Boolean

Whether the routine was created with a partial resource representation from the BigQuery service by retrieval through Dataset#routines. See Models: list response for the contents of the partial representation. Accessing any attribute outside of the partial representation will result in loading the full representation.

Returns
  • (Boolean) — true when the routine was created with a partial resource representation, false otherwise.
Example 
require "google/cloud/bigquery"

bigquery = Google::Cloud::Bigquery.new

dataset = bigquery.dataset "my_dataset"
routine = dataset.routines.first

routine.resource_partial? #=> true
routine.description # Loads the full resource.
routine.resource_partial? #=> false

#return_type

def return_type() -> Google::Cloud::Bigquery::StandardSql::DataType, nil

The return type of the routine. Optional if the routine is a SQL function (#sql?); required otherwise.

If absent, the return type is inferred from #body 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.

For example, for the functions created with the following statements:

  • CREATE FUNCTION Add(x FLOAT64, y FLOAT64) RETURNS FLOAT64 AS (x + y);
  • CREATE FUNCTION Increment(x FLOAT64) AS (Add(x, 1));
  • CREATE FUNCTION Decrement(x FLOAT64) RETURNS FLOAT64 AS (Add(x, -1));

The returnType is {typeKind: "FLOAT64"} for Add and Decrement, and is absent for Increment (inferred as FLOAT64 at query time).

Suppose the function Add is replaced by CREATE OR REPLACE FUNCTION Add(x INT64, y INT64) AS (x + y);

Then the inferred return type of Increment is automatically changed to INT64 at query time, while the return type of Decrement remains FLOAT64.

Returns
Example 
require "google/cloud/bigquery"

bigquery = Google::Cloud::Bigquery.new
dataset = bigquery.dataset "my_dataset"
routine = dataset.routine "my_routine"

routine.return_type.type_kind #=> "INT64"

#return_type=

def return_type=(new_return_type)

Updates the return type of the routine. Optional if the routine is a SQL function (#sql?); required otherwise.

If absent, the return type is inferred from #body 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.

For example, for the functions created with the following statements:

  • CREATE FUNCTION Add(x FLOAT64, y FLOAT64) RETURNS FLOAT64 AS (x + y);
  • CREATE FUNCTION Increment(x FLOAT64) AS (Add(x, 1));
  • CREATE FUNCTION Decrement(x FLOAT64) RETURNS FLOAT64 AS (Add(x, -1));

The returnType is {typeKind: "FLOAT64"} for Add and Decrement, and is absent for Increment (inferred as FLOAT64 at query time).

Suppose the function Add is replaced by CREATE OR REPLACE FUNCTION Add(x INT64, y INT64) AS (x + y);

Then the inferred return type of Increment is automatically changed to INT64 at query time, while the return type of Decrement remains FLOAT64.

Parameter
Example 
require "google/cloud/bigquery"

bigquery = Google::Cloud::Bigquery.new
dataset = bigquery.dataset "my_dataset"
routine = dataset.routine "my_routine"

routine.return_type.type_kind #=> "INT64"
routine.return_type = "STRING"

#routine_id

def routine_id() -> String

A unique ID for this routine, without the project name.

Returns
  • (String) — The ID must contain only letters ([A-Za-z]), numbers ([0-9]), or underscores (_). The maximum length is 256 characters.

#routine_type

def routine_type() -> String, nil

The type of routine. Required.

  • SCALAR_FUNCTION - Non-builtin permanent scalar function.
  • PROCEDURE - Stored procedure.
Returns
  • (String, nil) — The type of routine in upper case, or nil if the object is a reference (see #reference?).

#routine_type=

def routine_type=(new_routine_type)

Updates the type of routine. Required.

  • SCALAR_FUNCTION - Non-builtin permanent scalar function.
  • PROCEDURE - Stored procedure.
Parameter
  • new_routine_type (String) — The new type of the routine in upper case.

#scalar_function?

def scalar_function?() -> Boolean

Checks if the value of #routine_type is SCALAR_FUNCTION. The default is true.

Returns
  • (Boolean) — true when SCALAR_FUNCTION and the object is not a reference (see #reference?), false otherwise.

#sql?

def sql?() -> Boolean

Checks if the value of #language is SQL. The default is true.

Returns
  • (Boolean) — true when SQL and the object is not a reference (see #reference?), false otherwise.

#update

def update() { |routine| ... }

Updates the routine with changes made in the given block in a single update request. The following attributes may be set: Updater#routine_type=, Updater#language=, Updater#arguments=, Updater#return_type=, Updater#imported_libraries=, Updater#body=, and Updater#description=.

Yields
  • (routine) — A block for setting properties on the routine.
Yield Parameter
Example 
require "google/cloud/bigquery"

bigquery = Google::Cloud::Bigquery.new
dataset = bigquery.dataset "my_dataset"
routine = dataset.routine "my_routine"

routine.update do |r|
  r.routine_type = "SCALAR_FUNCTION"
  r.language = "SQL"
  r.arguments = [
    Google::Cloud::Bigquery::Argument.new(name: "x", data_type: "INT64")
  ]
  r.body = "x * 3"
  r.description = "My new routine description"
end