Class: Google::Cloud::Bigquery::View

Inherits:
Object
  • Object
show all
Defined in:
lib/google/cloud/bigquery/view.rb

Overview

View

A view is a virtual table defined by a SQL query. You can query views in the browser tool, or by using a query job.

BigQuery's views are logical views, not materialized views, which means that the query that defines the view is re-executed every time the view is queried. Queries are billed according to the total amount of data in all table fields referenced directly or indirectly by the top-level query.

Examples:

require "google/cloud/bigquery"

bigquery = Google::Cloud::Bigquery.new
dataset = bigquery.dataset "my_dataset"
view = dataset.create_view "my_view",
         "SELECT name, age FROM `my_project.my_dataset.my_table`"

Attributes collapse

Lifecycle collapse

Data collapse

Instance Method Details

#api_url ⇒ String

A URL that can be used to access the view using the REST API.

Returns:

  • (String) —

    A REST URL for the resource.



193
194
195
196
 
# File 'lib/google/cloud/bigquery/view.rb', line 193

def api_url
  ensure_full_data!
  @gapi.self_link
end

#created_at ⇒ Time?

The time when this view was created.

Returns:

  • (Time, nil) —

    The creation time.



229
230
231
232
233
234
235
236
 
# File 'lib/google/cloud/bigquery/view.rb', line 229

def created_at
  ensure_full_data!
  begin
    ::Time.at(Integer(@gapi.creation_time) / 1000.0)
  rescue
    nil
  end
end

#data(max: nil, cache: true) ⇒ Google::Cloud::Bigquery::Data

Runs a query to retrieve all data from the view, in a synchronous method that blocks for a response. In this method, a QueryJob is created and its results are saved to a temporary table, then read from the table. Timeouts and transient errors are generally handled as needed to complete the query.

Examples:

require "google/cloud/bigquery"

bigquery = Google::Cloud::Bigquery.new
dataset = bigquery.dataset "my_dataset"
view = dataset.table "my_view"

data = view.data
data.each do |row|
  puts row[:first_name]
end
more_data = data.next if data.next?

Parameters:

  • max (Integer) —

    The maximum number of rows of data to return per page of results. Setting this flag to a small value such as 1000 and then paging through results might improve reliability when the query result set is large. In addition to this limit, responses are also limited to 10 MB. By default, there is no maximum row count, and only the byte limit applies.

  • cache (Boolean) —

    Whether to look for the result in the query cache. The query cache is a best-effort cache that will be flushed whenever tables in the query are modified. The default value is true. For more information, see query caching.

Returns:



612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
 
# File 'lib/google/cloud/bigquery/view.rb', line 612

def data max: nil, cache: true
  sql = "SELECT * FROM #{query_id}"
  ensure_service!

  gapi = service.query_job sql, cache: cache
  job = Job.from_gapi gapi, service
  job.wait_until_done!

  if job.failed?
    begin
      # raise to activate ruby exception cause handling
      fail job.gapi_error
    rescue => e
      # wrap Google::Apis::Error with Google::Cloud::Error
      raise Google::Cloud::Error.from_error(e)
    end
  end

  job.data max: max
end

#dataset_id ⇒ String

The ID of the Dataset containing this view.

Returns:

  • (String) —

    The ID must contain only letters (a-z, A-Z), numbers (0-9), or underscores (_). The maximum length is 1,024 characters.



80
81
82
 
# File 'lib/google/cloud/bigquery/view.rb', line 80

def dataset_id
  @gapi.table_reference.dataset_id
end

#delete ⇒ Boolean

Permanently deletes the view.

Examples:

require "google/cloud/bigquery"

bigquery = Google::Cloud::Bigquery.new
dataset = bigquery.dataset "my_dataset"
view = dataset.table "my_view"

view.delete

Returns:

  • (Boolean) —

    Returns true if the view was deleted.



649
650
651
652
653
 
# File 'lib/google/cloud/bigquery/view.rb', line 649

def delete
  ensure_service!
  service.delete_table dataset_id, table_id
  true
end

#description ⇒ String

A user-friendly description of the view.

Returns:

  • (String) —

    The description.



205
206
207
208
 
# File 'lib/google/cloud/bigquery/view.rb', line 205

def description
  ensure_full_data!
  @gapi.description
end

#description=(new_description) ⇒ Object

Updates the user-friendly description of the view.

Parameters:

  • new_description (String) —

    The new user-friendly description.



217
218
219
220
 
# File 'lib/google/cloud/bigquery/view.rb', line 217

def description= new_description
  @gapi.update! description: new_description
  patch_gapi! :description
end

#etag ⇒ String

The ETag hash of the view.

Returns:

  • (String) —

    The ETag hash.



181
182
183
184
 
# File 'lib/google/cloud/bigquery/view.rb', line 181

def etag
  ensure_full_data!
  @gapi.etag
end

#expires_at ⇒ Time?

The time when this view expires. If not present, the view will persist indefinitely. Expired views will be deleted and their storage reclaimed.

Returns:

  • (Time, nil) —

    The expiration time.



247
248
249
250
251
252
253
254
 
# File 'lib/google/cloud/bigquery/view.rb', line 247

def expires_at
  ensure_full_data!
  begin
    ::Time.at(Integer(@gapi.expiration_time) / 1000.0)
  rescue
    nil
  end
end

#external? ⇒ Boolean

Checks if the view's type is "EXTERNAL".

Returns:

  • (Boolean) —

    true when the type is EXTERNAL, false otherwise.



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

def external?
  @gapi.type == "EXTERNAL"
end

#fields ⇒ Array<Schema::Field>

The fields of the view, obtained from its schema.

Examples:

require "google/cloud/bigquery"

bigquery = Google::Cloud::Bigquery.new
dataset = bigquery.dataset "my_dataset"
view = dataset.table "my_view"

view.fields.each do |field|
  puts field.name
end

Returns:



421
422
423
 
# File 'lib/google/cloud/bigquery/view.rb', line 421

def fields
  schema.fields
end

#headers ⇒ Array<Symbol>

The names of the columns in the view, obtained from its schema.

Examples:

require "google/cloud/bigquery"

bigquery = Google::Cloud::Bigquery.new
dataset = bigquery.dataset "my_dataset"
view = dataset.table "my_view"

view.headers.each do |header|
  puts header
end

Returns:

  • (Array<Symbol>) —

    An array of column names.



443
444
445
 
# File 'lib/google/cloud/bigquery/view.rb', line 443

def headers
  schema.headers
end

#id ⇒ Object

The combined Project ID, Dataset ID, and Table ID for this view, in the format specified by the Legacy SQL Query Reference: project_name:datasetId.tableId. To use this value in queries see #query_id.



113
114
115
 
# File 'lib/google/cloud/bigquery/view.rb', line 113

def id
  @gapi.id
end

#labels ⇒ Hash<String, String>

A hash of user-provided labels associated with this view. Labels are used to organize and group views and views. See Using Labels.

The returned hash is frozen and changes are not allowed. Use #labels= to replace the entire hash.

Examples:

require "google/cloud/bigquery"

bigquery = Google::Cloud::Bigquery.new
dataset = bigquery.dataset "my_dataset"
view = dataset.table "my_view"

labels = view.labels
labels["department"] #=> "shipping"

Returns:

  • (Hash<String, String>) —

    A hash containing key/value pairs.



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

def labels
  m = @gapi.labels
  m = m.to_h if m.respond_to? :to_h
  m.dup.freeze
end

#labels=(labels) ⇒ Object

Updates the hash of user-provided labels associated with this view. Labels are used to organize and group tables and views. See Using Labels.

Examples:

require "google/cloud/bigquery"

bigquery = Google::Cloud::Bigquery.new
dataset = bigquery.dataset "my_dataset"
view = dataset.table "my_view"

view.labels = { "department" => "shipping" }

Parameters:

  • labels (Hash<String, String>) —

    A hash containing key/value pairs.

    • Label keys and values can be no longer than 63 characters.
    • Label keys and values can contain only lowercase letters, numbers, underscores, hyphens, and international characters.
    • Label keys and values cannot exceed 128 bytes in size.
    • Label keys must begin with a letter.
    • Label keys must be unique within a view.


373
374
375
376
 
# File 'lib/google/cloud/bigquery/view.rb', line 373

def labels= labels
  @gapi.labels = labels
  patch_gapi! :labels
end

#location ⇒ String

The geographic location where the view should reside. Possible values include EU and US. The default value is US.

Returns:

  • (String) —

    The location code.



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

def location
  ensure_full_data!
  @gapi.location
end

#modified_at ⇒ Time?

The date when this view was last modified.

Returns:

  • (Time, nil) —

    The last modified time.



263
264
265
266
267
268
269
270
 
# File 'lib/google/cloud/bigquery/view.rb', line 263

def modified_at
  ensure_full_data!
  begin
    ::Time.at(Integer(@gapi.last_modified_time) / 1000.0)
  rescue
    nil
  end
end

#name ⇒ String

The name of the view.

Returns:

  • (String) —

    The friendly name.



158
159
160
 
# File 'lib/google/cloud/bigquery/view.rb', line 158

def name
  @gapi.friendly_name
end

#name=(new_name) ⇒ Object

Updates the name of the view.

Parameters:

  • new_name (String) —

    The new friendly name.



169
170
171
172
 
# File 'lib/google/cloud/bigquery/view.rb', line 169

def name= new_name
  @gapi.update! friendly_name: new_name
  patch_gapi! :friendly_name
end

#project_id ⇒ String

The ID of the Project containing this view.

Returns:

  • (String) —

    The project ID.



91
92
93
 
# File 'lib/google/cloud/bigquery/view.rb', line 91

def project_id
  @gapi.table_reference.project_id
end

#query ⇒ String

The query that executes each time the view is loaded.

Returns:

  • (String) —

    The query that defines the view.



454
455
456
 
# File 'lib/google/cloud/bigquery/view.rb', line 454

def query
  @gapi.view.query if @gapi.view
end

#query=(new_query) ⇒ Object

Updates the query that executes each time the view is loaded.

This sets the query using standard SQL. To specify legacy SQL or to use user-defined function resources use (#set_query) instead.

Examples:

require "google/cloud/bigquery"

bigquery = Google::Cloud::Bigquery.new
dataset = bigquery.dataset "my_dataset"
view = dataset.table "my_view"

view.query = "SELECT first_name FROM " \
               "`my_project.my_dataset.my_table`"

Parameters:

  • new_query (String) —

    The query that defines the view.

See Also:



481
482
483
 
# File 'lib/google/cloud/bigquery/view.rb', line 481

def query= new_query
  set_query new_query
end

#query_id(standard_sql: nil, legacy_sql: nil) ⇒ Object

The value returned by #id, wrapped in square brackets if the Project ID contains dashes, as specified by the Query Reference. Useful in queries.

Examples:

require "google/cloud/bigquery"

bigquery = Google::Cloud::Bigquery.new
dataset = bigquery.dataset "my_dataset"
view = dataset.table "my_view"

data = bigquery.query "SELECT name FROM #{view.query_id}"

Parameters:

  • standard_sql (Boolean) —

    Specifies whether to use BigQuery's standard SQL dialect. Optional. The default value is true.

  • legacy_sql (Boolean) —

    Specifies whether to use BigQuery's legacy SQL dialect. Optional. The default value is false.



143
144
145
146
147
148
149
 
# File 'lib/google/cloud/bigquery/view.rb', line 143

def query_id standard_sql: nil, legacy_sql: nil
  if Convert.resolve_legacy_sql standard_sql, legacy_sql
    "[#{id}]"
  else
    "`#{project_id}.#{dataset_id}.#{table_id}`"
  end
end

#query_legacy_sql? ⇒ Boolean

Checks if the view's query is using legacy sql.

Returns:

  • (Boolean) —

    true when legacy sql is used, false otherwise.



539
540
541
542
543
 
# File 'lib/google/cloud/bigquery/view.rb', line 539

def query_legacy_sql?
  val = @gapi.view.use_legacy_sql
  return true if val.nil?
  val
end

#query_standard_sql? ⇒ Boolean

Checks if the view's query is using standard sql.

Returns:

  • (Boolean) —

    true when standard sql is used, false otherwise.



552
553
554
 
# File 'lib/google/cloud/bigquery/view.rb', line 552

def query_standard_sql?
  !query_legacy_sql?
end

#query_udfs ⇒ Array<String>

The user-defined function resources used in the view's query. May be either a code resource to load from a Google Cloud Storage URI (gs://bucket/path), or an inline resource that contains code for a user-defined function (UDF). Providing an inline code resource is equivalent to providing a URI for a file containing the same code. See User-Defined Functions.

Returns:

  • (Array<String>) —

    An array containing Google Cloud Storage URIs and/or inline source code.



570
571
572
573
574
 
# File 'lib/google/cloud/bigquery/view.rb', line 570

def query_udfs
  udfs_gapi = @gapi.view.user_defined_function_resources
  return [] if udfs_gapi.nil?
  Array(udfs_gapi).map { |udf| udf.inline_code || udf.resource_uri }
end

#reload! ⇒ Object Also known as: refresh!

Reloads the view with current data from the BigQuery service.



660
661
662
663
664
 
# File 'lib/google/cloud/bigquery/view.rb', line 660

def reload!
  ensure_service!
  gapi = service.get_table dataset_id, table_id
  @gapi = gapi
end

#schema ⇒ Schema

The schema of the view.

The returned object is frozen and changes are not allowed.

Examples:

require "google/cloud/bigquery"

bigquery = Google::Cloud::Bigquery.new
dataset = bigquery.dataset "my_dataset"
view = dataset.table "my_view"

schema = view.schema
field = schema.field "name"
field.required? #=> true

Returns:

  • (Schema) —

    A schema object.



398
399
400
401
 
# File 'lib/google/cloud/bigquery/view.rb', line 398

def schema
  ensure_full_data!
  Schema.from_gapi(@gapi.schema).freeze
end

#set_query(query, standard_sql: nil, legacy_sql: nil, udfs: nil) ⇒ Object

Updates the query that executes each time the view is loaded. Allows setting of standard vs. legacy SQL and user-defined function resources.

Examples:

require "google/cloud/bigquery"

bigquery = Google::Cloud::Bigquery.new
dataset = bigquery.dataset "my_dataset"
view = dataset.table "my_view"

view.set_query "SELECT first_name FROM " \
                 "`my_project.my_dataset.my_table`",
               standard_sql: true

Parameters:

  • query (String) —

    The query that defines the view.

  • standard_sql (Boolean) —

    Specifies whether to use BigQuery's standard SQL dialect. Optional. The default value is true.

  • legacy_sql (Boolean) —

    Specifies whether to use BigQuery's legacy SQL dialect. Optional. The default value is false.

  • udfs (Array<String>, String) —

    User-defined function resources used in the query. May be either a code resource to load from a Google Cloud Storage URI (gs://bucket/path), or an inline resource that contains code for a user-defined function (UDF). Providing an inline code resource is equivalent to providing a URI for a file containing the same code. See User-Defined Functions.

See Also:



523
524
525
526
527
528
529
530
 
# File 'lib/google/cloud/bigquery/view.rb', line 523

def set_query query, standard_sql: nil, legacy_sql: nil, udfs: nil
  @gapi.view = Google::Apis::BigqueryV2::ViewDefinition.new \
    query: query,
    use_legacy_sql: Convert.resolve_legacy_sql(standard_sql,
                                               legacy_sql),
    user_defined_function_resources: udfs_gapi(udfs)
  patch_view_gapi!
end

#table? ⇒ Boolean

Checks if the view's type is "TABLE".

Returns:

  • (Boolean) —

    true when the type is TABLE, false otherwise.



279
280
281
 
# File 'lib/google/cloud/bigquery/view.rb', line 279

def table?
  @gapi.type == "TABLE"
end

#table_id ⇒ String

A unique ID for this view.

Returns:

  • (String) —

    The ID must contain only letters (a-z, A-Z), numbers (0-9), or underscores (_). The maximum length is 1,024 characters.



68
69
70
 
# File 'lib/google/cloud/bigquery/view.rb', line 68

def table_id
  @gapi.table_reference.table_id
end

#view? ⇒ Boolean

Checks if the view's type is "VIEW".

Returns:

  • (Boolean) —

    true when the type is VIEW, false otherwise.



290
291
292
 
# File 'lib/google/cloud/bigquery/view.rb', line 290

def view?
  @gapi.type == "VIEW"
end