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



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

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.



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

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



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

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.



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

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



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

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

#dataset_id ⇒ String

The ID of the dataset containing this routine.

Returns:

  • (String) —

    The dataset ID.



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

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.



657
658
659
660
661
662
663
 
# File 'lib/google/cloud/bigquery/routine.rb', line 657

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



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

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.



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

def description= new_description
  ensure_full_data!
  @gapi.description = new_description
  update_gapi!
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?).



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

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.



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

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



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

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



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

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.



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

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



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

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.



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

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



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

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.



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

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

#project_id ⇒ String

The ID of the project containing this routine.

Returns:

  • (String) —

    The project ID.



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

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.



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

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:



683
684
685
686
687
688
689
 
# File 'lib/google/cloud/bigquery/routine.rb', line 683

def reload!
  ensure_service!
  @gapi = service.get_routine dataset_id, routine_id
  @reference = nil
  @exists = nil
  self
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.



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

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.



813
814
815
 
# File 'lib/google/cloud/bigquery/routine.rb', line 813

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.



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

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:



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

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:



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

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-z, A-Z), numbers (0-9), or underscores (_). The maximum length is 256 characters.



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

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



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

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.



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

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.



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

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.



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

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:



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

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