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.
-
(Array<Argument>, nil) — An array of argument objects, or
nil
if the object is a reference (see #reference?).
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.
- new_arguments (Array<Argument>) — The new arguments.
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 "\n";\n"`
Note that both \n
are replaced with linebreaks.
-
(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 "\n";\n"`
Note that both \n
are replaced with linebreaks.
- new_body (String) — The new body of the routine.
#created_at
def created_at() -> Time, nil
The time when this routine was created.
-
(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.
- (String) — The dataset ID.
#delete
def delete() -> Boolean
Permanently deletes the routine.
-
(Boolean) — Returns
true
if the routine was deleted.
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]
-
(String, nil) — The routine description, or
nil
if the object is a reference (see #reference?).
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]
- new_description (String) — The new routine description.
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 areDETERMINISTIC
, 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 isNOT_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.
-
(String, nil) — The routine determinism level in upper case, or
nil
if not set or the object is a reference (see #reference?).
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 areDETERMINISTIC
, 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 isNOT_DETERMINISTIC
, the query result is not cacheable.
- new_determinism_level (String, nil) — The new routine determinism level in upper case.
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
.
-
(Boolean) —
true
whenDETERMINISTIC
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
.
-
(Boolean) —
true
whenNOT_DETERMINISTIC
and the object is not a reference (see #reference?),false
otherwise.
#etag
def etag() -> String, nil
The ETag hash of the routine.
-
(String, nil) — The ETag hash, or
nil
if the object is a reference (see #reference?).
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
.
-
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 isfalse
.
-
(Boolean) —
true
when the routine exists in the BigQuery service,false
otherwise.
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?).
-
(Array<String>, nil) — A frozen array of Google Cloud Storage URIs, e.g.
["gs://cloud-samples-data/bigquery/udfs/max-value.js"]
, ornil
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?).
-
new_imported_libraries (Array<String>, nil) — An array of Google Cloud Storage URIs, e.g.
["gs://cloud-samples-data/bigquery/udfs/max-value.js"]
.
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
.
-
(Boolean) —
true
whenJAVASCRIPT
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.
-
(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.
- new_language (String) — The new language in upper case.
#modified_at
def modified_at() -> Time, nil
The time when this routine was last modified.
-
(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
.
-
(Boolean) —
true
whenPROCEDURE
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.
- (String) — The project ID.
#reference?
def reference?() -> Boolean
Whether the routine was created without retrieving the resource representation from the BigQuery service.
-
(Boolean) —
true
when the routine is just a local reference object,false
otherwise.
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
Reloads the routine with current data from the BigQuery service.
- (Google::Cloud::Bigquery::Routine) — Returns the reloaded routine.
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
Reloads the routine with current data from the BigQuery service.
- (Google::Cloud::Bigquery::Routine) — Returns the reloaded routine.
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.
-
(Boolean) —
true
when the routine was created with a resource representation,false
otherwise.
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.
-
(Boolean) —
true
when the routine was created with a full resource representation,false
otherwise.
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.
-
(Boolean) —
true
when the routine was created with a partial resource representation,false
otherwise.
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
.
-
(Google::Cloud::Bigquery::StandardSql::DataType, nil) — The return type in upper case, or
nil
if the object is a reference (see #reference?).
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
.
- new_return_type (Google::Cloud::Bigquery::StandardSql::DataType, String) — The new return type for the routine.
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.
-
(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.
-
(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.
- 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
.
-
(Boolean) —
true
whenSCALAR_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
.
-
(Boolean) —
true
whenSQL
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=.
- (routine) — A block for setting properties on the routine.
- routine (Google::Cloud::Bigquery::Routine::Updater) — An updater to set additional properties on the 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.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