Class: Google::Cloud::Bigquery::Routine

Inherits:
Object
  • Object
show all
Defined in:
lib/google/cloud/bigquery/routine.rb,
lib/google/cloud/bigquery/routine/list.rb

Overview

Routine

A user-defined function or a stored procedure.

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

Direct Known Subclasses

Updater

Defined Under Namespace

Classes: List, Updater

Attributes collapse

Lifecycle collapse

Instance Method Details

#arguments ⇒ Array<Argument>?

The input/output arguments of the routine. Optional.

Examples:

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

Returns:

  • (Array<Argument>, nil) —

    An array of argument objects, or nil if the object is a reference (see #reference?).



340
341
342
343
344
345
 
# File 'lib/google/cloud/bigquery/routine.rb', line 340

def arguments
  return nil if reference?
  ensure_full_data!
  # always return frozen arguments
  Array(@gapi.arguments).map { |a| Argument.from_gapi a }.freeze
end

#arguments=(new_arguments) ⇒ Object

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

Examples:

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")
]

Parameters:

  • new_arguments (Array<Argument>) —

    The new arguments.



365
366
367
368
369
 
# File 'lib/google/cloud/bigquery/routine.rb', line 365

def arguments= new_arguments
  ensure_full_data!
  @gapi.update! arguments: new_arguments.map(&:to_gapi)
  update_gapi!
end

#body ⇒ String?

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.

Returns:

  • (String, nil) —

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



523
524
525
526
527
 
# File 'lib/google/cloud/bigquery/routine.rb', line 523

def body
  return nil if reference?
  ensure_full_data!
  @gapi.definition_body
end

#body=(new_body) ⇒ Object

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.

Parameters:

  • new_body (String) —

    The new body of the routine.



556
557
558
559
560
 
# File 'lib/google/cloud/bigquery/routine.rb', line 556

def body= new_body
  ensure_full_data!
  @gapi.definition_body = new_body
  update_gapi!
end

#created_at ⇒ Time?

The time when this routine was created.

Returns:

  • (Time, nil) —

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



247
248
249
250
 
# File 'lib/google/cloud/bigquery/routine.rb', line 247

def created_at
  return nil if reference?
  Convert.millis_to_time @gapi.creation_time
end

#data_governance_type ⇒ String?

The data governance type of the routine. Optional.

If set to DATA_MASKING, the function is validated and made available as a masking function. For more information, see Create custom masking routines.

Examples:

require "google/cloud/bigquery"

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

routine.data_governance_type #=> "DATA_MASKING"

Returns:

  • (String, nil) —

    The data governance type, or nil if not set or the object is a reference (see #reference?).



715
716
717
718
719
 
# File 'lib/google/cloud/bigquery/routine.rb', line 715

def data_governance_type
  return nil if reference?
  ensure_full_data!
  @gapi.data_governance_type
end

#data_governance_type=(new_data_governance_type) ⇒ Object

Updates the data governance type of the routine. Optional.

If set to DATA_MASKING, the function is validated and made available as a masking function. For more information, see Create custom masking routines.

Examples:

require "google/cloud/bigquery"

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

routine.data_governance_type = "DATA_MASKING"

Parameters:

  • new_data_governance_type (String, nil) —

    The new data governance type. nil to unset.



741
742
743
744
745
 
# File 'lib/google/cloud/bigquery/routine.rb', line 741

def data_governance_type= new_data_governance_type
  ensure_full_data!
  @gapi.data_governance_type = new_data_governance_type
  update_gapi!
end

#dataset_id ⇒ String

The ID of the dataset containing this routine.

Returns:

  • (String) —

    The dataset ID.



137
138
139
140
 
# File 'lib/google/cloud/bigquery/routine.rb', line 137

def dataset_id
  return reference.dataset_id if reference?
  @gapi.routine_reference.dataset_id
end

#delete ⇒ Boolean

Permanently deletes the routine.

Examples:

require "google/cloud/bigquery"

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

routine.delete

Returns:

  • (Boolean) —

    Returns true if the routine was deleted.



847
848
849
850
851
852
853
 
# File 'lib/google/cloud/bigquery/routine.rb', line 847

def delete
  ensure_service!
  service.delete_routine dataset_id, routine_id
  # Set flag for #exists?
  @exists = false
  true
end

#description ⇒ String?

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

Examples:

require "google/cloud/bigquery"

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

routine.description #=> "My routine description"

Returns:

  • (String, nil) —

    The routine description, or nil if the object is a reference (see #reference?).



578
579
580
581
582
 
# File 'lib/google/cloud/bigquery/routine.rb', line 578

def description
  return nil if reference?
  ensure_full_data!
  @gapi.description
end

#description=(new_description) ⇒ Object

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

Examples:

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"

Parameters:

  • new_description (String) —

    The new routine description.



601
602
603
604
605
 
# File 'lib/google/cloud/bigquery/routine.rb', line 601

def description= new_description
  ensure_full_data!
  @gapi.description = new_description
  update_gapi!
end

#determinism_level ⇒ String?

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.

Examples:

require "google/cloud/bigquery"

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

routine.determinism_level #=> "NOT_DETERMINISTIC"

Returns:

  • (String, nil) —

    The routine determinism level in upper case, or nil if not set or the object is a reference (see #reference?).



635
636
637
638
639
 
# File 'lib/google/cloud/bigquery/routine.rb', line 635

def determinism_level
  return nil if reference?
  ensure_full_data!
  @gapi.determinism_level
end

#determinism_level=(new_determinism_level) ⇒ Object

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.

Examples:

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"

Parameters:

  • new_determinism_level (String, nil) —

    The new routine determinism level in upper case.



664
665
666
667
668
 
# File 'lib/google/cloud/bigquery/routine.rb', line 664

def determinism_level= new_determinism_level
  ensure_full_data!
  @gapi.determinism_level = new_determinism_level
  update_gapi!
end

#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.



678
679
680
 
# File 'lib/google/cloud/bigquery/routine.rb', line 678

def determinism_level_deterministic?
  @gapi.determinism_level == "DETERMINISTIC"
end

#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.



690
691
692
 
# File 'lib/google/cloud/bigquery/routine.rb', line 690

def determinism_level_not_deterministic?
  @gapi.determinism_level == "NOT_DETERMINISTIC"
end

#etag ⇒ String?

The ETag hash of the routine.

Examples:

require "google/cloud/bigquery"

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

routine.etag # "etag123456789"

Returns:

  • (String, nil) —

    The ETag hash, or nil if the object is a reference (see #reference?).



179
180
181
182
 
# File 'lib/google/cloud/bigquery/routine.rb', line 179

def etag
  return nil if reference?
  @gapi.etag
end

#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.

Examples:

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

Parameters:

  • 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.



903
904
905
906
907
908
909
910
 
# File 'lib/google/cloud/bigquery/routine.rb', line 903

def exists? force: false
  return resource_exists? if force
  # If we have a value, return it
  return @exists unless @exists.nil?
  # Always true if we have a gapi object
  return true if resource?
  resource_exists?
end

#imported_libraries ⇒ Array<String>?

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?).



464
465
466
467
468
 
# File 'lib/google/cloud/bigquery/routine.rb', line 464

def imported_libraries
  return nil if reference?
  ensure_full_data!
  @gapi.imported_libraries.freeze
end

#imported_libraries=(new_imported_libraries) ⇒ Object

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

Examples:

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"
]

Parameters:

  • new_imported_libraries (Array<String>, nil) —

    An array of Google Cloud Storage URIs, e.g. ["gs://cloud-samples-data/bigquery/udfs/max-value.js"].



490
491
492
493
494
 
# File 'lib/google/cloud/bigquery/routine.rb', line 490

def imported_libraries= new_imported_libraries
  ensure_full_data!
  @gapi.imported_libraries = new_imported_libraries
  update_gapi!
end

#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.



303
304
305
 
# File 'lib/google/cloud/bigquery/routine.rb', line 303

def javascript?
  @gapi.language == "JAVASCRIPT"
end

#language ⇒ String?

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?).



274
275
276
277
 
# File 'lib/google/cloud/bigquery/routine.rb', line 274

def language
  return nil if reference?
  @gapi.language
end

#language=(new_language) ⇒ Object

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

  • SQL - SQL language.
  • JAVASCRIPT - JavaScript language.

Parameters:

  • new_language (String) —

    The new language in upper case.



289
290
291
292
293
 
# File 'lib/google/cloud/bigquery/routine.rb', line 289

def language= new_language
  ensure_full_data!
  @gapi.language = new_language
  update_gapi!
end

#modified_at ⇒ Time?

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?).



259
260
261
262
 
# File 'lib/google/cloud/bigquery/routine.rb', line 259

def modified_at
  return nil if reference?
  Convert.millis_to_time @gapi.last_modified_time
end

#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.



224
225
226
 
# File 'lib/google/cloud/bigquery/routine.rb', line 224

def procedure?
  @gapi.routine_type == "PROCEDURE"
end

#project_id ⇒ String

The ID of the project containing this routine.

Returns:

  • (String) —

    The project ID.



149
150
151
152
 
# File 'lib/google/cloud/bigquery/routine.rb', line 149

def project_id
  return reference.project_id if reference?
  @gapi.routine_reference.project_id
end

#reference? ⇒ Boolean

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

Examples:

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

Returns:

  • (Boolean) —

    true when the routine is just a local reference object, false otherwise.



931
932
933
 
# File 'lib/google/cloud/bigquery/routine.rb', line 931

def reference?
  @gapi.nil?
end

#reload! ⇒ Google::Cloud::Bigquery::Routine Also known as: refresh!

Reloads the routine with current data from the BigQuery service.

Examples:

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!

Returns:



873
874
875
876
877
878
879
 
# File 'lib/google/cloud/bigquery/routine.rb', line 873

def reload!
  ensure_service!
  @gapi = service.get_routine dataset_id, routine_id
  @reference = nil
  @exists = nil
  self
end

#remote_function_options ⇒ RemoteFunctionOptions?

Remote function specific options. Optional.

Examples:

require "google/cloud/bigquery"

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

puts routine.remote_function_options.endpoint

Returns:



764
765
766
767
768
 
# File 'lib/google/cloud/bigquery/routine.rb', line 764

def remote_function_options
  return nil if reference?
  ensure_full_data!
  RemoteFunctionOptions.from_gapi @gapi.remote_function_options
end

#remote_function_options=(new_remote_function_options) ⇒ Object

Updates the remote function specific options. Optional.

Examples:

require "google/cloud/bigquery"

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

rfo = Google::Cloud::Bigquery::RemoteFunctionOptions.new.tap do |rfo|
  rfo.endpoint = "https://us-east1-my_gcf_project.cloudfunctions.net/remote_add"
  rfo.connection = "projects/my-project/locations/us-east1/connections/my-connection"
end
routine.remote_function_options = rfo

Parameters:



790
791
792
793
794
 
# File 'lib/google/cloud/bigquery/routine.rb', line 790

def remote_function_options= new_remote_function_options
  ensure_full_data!
  @gapi.remote_function_options = new_remote_function_options.to_gapi
  update_gapi!
end

#resource? ⇒ Boolean

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

Examples:

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

Returns:

  • (Boolean) —

    true when the routine was created with a resource representation, false otherwise.



954
955
956
 
# File 'lib/google/cloud/bigquery/routine.rb', line 954

def resource?
  !@gapi.nil?
end

#resource_full? ⇒ Boolean

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

Examples:

require "google/cloud/bigquery"

bigquery = Google::Cloud::Bigquery.new

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

routine.resource_full? #=> true

Returns:

  • (Boolean) —

    true when the routine was created with a full resource representation, false otherwise.



1003
1004
1005
 
# File 'lib/google/cloud/bigquery/routine.rb', line 1003

def resource_full?
  resource? && !@gapi.definition_body.nil?
end

#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.

Examples:

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

Returns:

  • (Boolean) —

    true when the routine was created with a partial resource representation, false otherwise.



982
983
984
 
# File 'lib/google/cloud/bigquery/routine.rb', line 982

def resource_partial?
  resource? && !resource_full?
end

#return_type ⇒ Google::Cloud::Bigquery::StandardSql::DataType?

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.

Examples:

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"

Returns:



405
406
407
408
409
410
 
# File 'lib/google/cloud/bigquery/routine.rb', line 405

def return_type
  return nil if reference?
  ensure_full_data!
  return nil unless @gapi.return_type
  StandardSql::DataType.from_gapi @gapi.return_type
end

#return_type=(new_return_type) ⇒ Object

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.

Examples:

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"

Parameters:



448
449
450
451
452
 
# File 'lib/google/cloud/bigquery/routine.rb', line 448

def return_type= new_return_type
  ensure_full_data!
  @gapi.return_type = StandardSql::DataType.gapi_from_string_or_data_type new_return_type
  update_gapi!
end

#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.



125
126
127
128
 
# File 'lib/google/cloud/bigquery/routine.rb', line 125

def routine_id
  return reference.routine_id if reference?
  @gapi.routine_reference.routine_id
end

#routine_type ⇒ String?

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?).



195
196
197
198
 
# File 'lib/google/cloud/bigquery/routine.rb', line 195

def routine_type
  return nil if reference?
  @gapi.routine_type
end

#routine_type=(new_routine_type) ⇒ Object

Updates the type of routine. Required.

  • SCALAR_FUNCTION - Non-builtin permanent scalar function.
  • PROCEDURE - Stored procedure.

Parameters:

  • new_routine_type (String) —

    The new type of the routine in upper case.



210
211
212
213
214
 
# File 'lib/google/cloud/bigquery/routine.rb', line 210

def routine_type= new_routine_type
  ensure_full_data!
  @gapi.routine_type = new_routine_type
  update_gapi!
end

#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.



236
237
238
 
# File 'lib/google/cloud/bigquery/routine.rb', line 236

def scalar_function?
  @gapi.routine_type == "SCALAR_FUNCTION"
end

#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.



315
316
317
318
 
# File 'lib/google/cloud/bigquery/routine.rb', line 315

def sql?
  return true if @gapi.language.nil?
  @gapi.language == "SQL"
end

#update {|routine| ... } ⇒ Object

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

Examples:

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

Yields:

  • (routine) —

    A block for setting properties on the routine.

Yield Parameters:



824
825
826
827
828
829
 
# File 'lib/google/cloud/bigquery/routine.rb', line 824

def update
  ensure_full_data!
  updater = Updater.new @gapi
  yield updater
  update_gapi! updater.to_gapi if updater.updates?
end