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 ⇒ Object

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



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

def api_url
  ensure_full_data!
  @gapi.self_link
end

#created_at ⇒ Object

The time when this table was created.



210
211
212
213
214
215
216
217
 
# File 'lib/google/cloud/bigquery/view.rb', line 210

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

#data(max: nil, timeout: 10000, cache: true, dryrun: nil) ⇒ Google::Cloud::Bigquery::QueryData

Runs a query to retrieve all data from the view.

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.

  • timeout (Integer) —

    How long to wait for the query to complete, in milliseconds, before the request times out and returns. Note that this is only a timeout for the request, not the query. If the query takes longer to run than the timeout value, the call returns without any results and with QueryData#complete? set to false. The default value is 10000 milliseconds (10 seconds).

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

  • dryrun (Boolean) —

    If set to true, BigQuery doesn't run the job. Instead, if the query is valid, BigQuery returns statistics about the job such as how many bytes would be processed. If the query is invalid, an error returns. The default value is false.

Returns:



393
394
395
396
397
398
399
 
# File 'lib/google/cloud/bigquery/view.rb', line 393

def data max: nil, timeout: 10000, cache: true, dryrun: nil
  sql = "SELECT * FROM #{query_id}"
  ensure_service!
  options = { max: max, timeout: timeout, cache: cache, dryrun: dryrun }
  gapi = service.query sql, options
  QueryData.from_gapi gapi, service
end

#dataset_id ⇒ Object

The ID of the Dataset containing this table.



77
78
79
 
# File 'lib/google/cloud/bigquery/view.rb', line 77

def dataset_id
  @gapi.table_reference.dataset_id
end

#delete ⇒ Boolean

Permanently deletes the table.

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 table was deleted.



417
418
419
420
421
 
# File 'lib/google/cloud/bigquery/view.rb', line 417

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

#description ⇒ Object

The description of the table.



190
191
192
193
 
# File 'lib/google/cloud/bigquery/view.rb', line 190

def description
  ensure_full_data!
  @gapi.description
end

#description=(new_description) ⇒ Object

Updates the description of the table.



200
201
202
203
 
# File 'lib/google/cloud/bigquery/view.rb', line 200

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

#etag ⇒ Object

A string hash of the dataset.



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

def etag
  ensure_full_data!
  @gapi.etag
end

#expires_at ⇒ Object

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



226
227
228
229
230
231
232
233
 
# File 'lib/google/cloud/bigquery/view.rb', line 226

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

#fields ⇒ Object

The fields of the view.



293
294
295
 
# File 'lib/google/cloud/bigquery/view.rb', line 293

def fields
  schema.fields
end

#headers ⇒ Object

The names of the columns in the view.



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

def headers
  schema.headers
end

#id ⇒ Object

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



108
109
110
 
# File 'lib/google/cloud/bigquery/view.rb', line 108

def id
  @gapi.id
end

#location ⇒ Object

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



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

def location
  ensure_full_data!
  @gapi.location
end

#modified_at ⇒ Object

The date when this table was last modified.



240
241
242
243
244
245
246
247
 
# File 'lib/google/cloud/bigquery/view.rb', line 240

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

#name ⇒ Object

The name of the table.



151
152
153
 
# File 'lib/google/cloud/bigquery/view.rb', line 151

def name
  @gapi.friendly_name
end

#name=(new_name) ⇒ Object

Updates the name of the table.



160
161
162
163
 
# File 'lib/google/cloud/bigquery/view.rb', line 160

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

#project_id ⇒ Object

The ID of the Project containing this table.



86
87
88
 
# File 'lib/google/cloud/bigquery/view.rb', line 86

def project_id
  @gapi.table_reference.project_id
end

#query ⇒ Object

The query that executes each time the view is loaded.



311
312
313
 
# File 'lib/google/cloud/bigquery/view.rb', line 311

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

#query=(new_query, standard_sql: nil, legacy_sql: nil) ⇒ Object

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

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.

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

See Also:



343
344
345
346
347
348
349
 
# File 'lib/google/cloud/bigquery/view.rb', line 343

def query= new_query, standard_sql: nil, legacy_sql: nil
  @gapi.view ||= Google::Apis::BigqueryV2::ViewDefinition.new
  @gapi.view.update! query: new_query
  @gapi.view.update! use_legacy_sql: \
    Convert.resolve_legacy_sql(standard_sql, legacy_sql)
  patch_view_gapi! :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.



138
139
140
141
142
143
144
 
# File 'lib/google/cloud/bigquery/view.rb', line 138

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

#reload! ⇒ Object Also known as: refresh!

Reloads the table with current data from the BigQuery service.



428
429
430
431
432
 
# File 'lib/google/cloud/bigquery/view.rb', line 428

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

#schema ⇒ Object

The schema of the view.



283
284
285
286
 
# File 'lib/google/cloud/bigquery/view.rb', line 283

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

#table? ⇒ Boolean

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

Returns:

  • (Boolean) 


254
255
256
 
# File 'lib/google/cloud/bigquery/view.rb', line 254

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

#table_id ⇒ Object

A unique ID for this table. 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 table's type is "VIEW".

Returns:

  • (Boolean) 


263
264
265
 
# File 'lib/google/cloud/bigquery/view.rb', line 263

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