RetroSearch Browse

Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://developers.google.com/bigquery/docs/updating-views below:

Manage logical views | BigQuery

Stay organized with collections Save and categorize content based on your preferences.

Manage logical views

This document describes how to manage views in BigQuery. You can manage your BigQuery views in the following ways:

Before you begin

Grant Identity and Access Management (IAM) roles that give users the necessary permissions to perform each task in this document. The permissions required to perform a task (if any) are listed in the "Required permissions" section of the task.

Update a view

After creating a view, you can update the following view properties:

Required permissions

To update a view, you need the following IAM permissions:

bigquery.tables.update
bigquery.tables.get

Each of the following predefined IAM roles includes the permissions that you need in order to update a view:

roles/bigquery.dataEditor
roles/bigquery.dataOwner
roles/bigquery.admin

Additionally, if you have the bigquery.datasets.create permission, you can update tables and views in the datasets that you create.

To update the view's SQL query, you must also have permissions to query any tables referenced by the view's SQL query.

Note: To update the SQL of an authorized view, or a view in an authorized dataset, you need additional permissions. For more information, see required permissions for authorized views and required permissions for views in authorized datasets.

For more information on IAM roles and permissions in BigQuery, see Predefined roles and permissions.

Updating a view's SQL query

You can update the SQL query used to define a view by:

Using the Google Cloud console
Using the bq command-line tool's bq update command
Calling the tables.patch API method
Using the client libraries

You can change the SQL dialect from legacy SQL to GoogleSQL in the API or bq command-line tool. You cannot update a legacy SQL view to GoogleSQL in the Google Cloud console.

To update a view's SQL query:

Console

In the Explorer panel, expand your project and dataset, then select the view.
Click the Details tab.
Above the Query box, click the Edit query button. Click Open in the dialog that appears.
Edit the SQL query in the Query editor box and then click Save view.
Make sure all the fields are correct in the Save view dialog and then click Save.

Issue the bq update command with the --view flag. To use GoogleSQL or to update the query dialect from legacy SQL to GoogleSQL, include the --use_legacy_sql flag and set it to false.

If your query references external user-defined function resources stored in Cloud Storage or in local files, use the --view_udf_resource flag to specify those resources. The --view_udf_resource flag is not demonstrated here. For more information on using UDFs, see GoogleSQL User-Defined Functions.

If you are updating a view in a project other than your default project, add the project ID to the dataset name in the following format: project_id:dataset.

bq update \
    --use_legacy_sql=false \
    --view_udf_resource=path_to_file \
    --view='query' \
    project_id:dataset.view

Replace the following:

path_to_file: the URI or local file system path to a code file to be loaded and evaluated immediately as a user-defined function resource used by the view. Repeat the flag to specify multiple files.
query: a valid GoogleSQL query
project_id: your project ID
dataset: the name of the dataset containing the view you're updating
view: the name of the view you're updating

Examples

Enter the following command to update the SQL query for a view named myview in mydataset. mydataset is in your default project. The example query used to update the view queries data from the USA Name Data public dataset.

bq update \
    --use_legacy_sql=false \
    --view \
    'SELECT
      name,
      number
    FROM
      `bigquery-public-data.usa_names.usa_1910_current`
    WHERE
      gender = "M"
    ORDER BY
      number DESC;' \
    mydataset.myview

Enter the following command to update the SQL query for a view named myview in mydataset. mydataset is in myotherproject, not your default project. The example query used to update the view queries data from the USA Name Data public dataset.

bq update \
    --use_legacy_sql=false \
    --view \
    'SELECT
      name,
      number
    FROM
      `bigquery-public-data.usa_names.usa_1910_current`
    WHERE
      gender = "M"
    ORDER BY
      number DESC;' \
    myotherproject:mydataset.myview

API

You can update a view by calling the tables.patch method with a table resource that contains an updated view property. Because the tables.update method replaces the entire table resource, the tables.patch method is preferred.

Before trying this sample, follow the Go setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Go API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

Java

Before trying this sample, follow the Java setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Java API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

Node.js

Before trying this sample, follow the Node.js setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Node.js API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

Python

Before trying this sample, follow the Python setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Python API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

Note: If you update the datasets referenced by the query of an authorized view, you must authorize the view access to any new underlying datasets. Updating a view's expiration time

You can set a default table expiration time at the dataset level (which affects both tables and views), or you can set a view's expiration time when the view is created. If you set the expiration when the view is created, the dataset's default table expiration is ignored. If you do not set a default table expiration at the dataset level, and you do not set an expiration when the view is created, the view never expires and you must delete the view manually.

At any point after the view is created, you can update the view's expiration time by:

Using the Google Cloud console
Using a Data definition language (DDL) statement written in GoogleSQL syntax
Using the bq command-line tool's bq update command
Calling the tables.patch API method
Using the client libraries

Note: If you set an expiration time that has already passed, the view is deleted immediately.

To update a view's expiration time:

Console

In the navigation pane, select your view.
On the view Details page, click the Details tab.
To the right of View info, click the edit icon (pencil).
In the View info dialog, for View expiration, click Specify date.
In the date picker, enter the expiration date and time and then click Ok.
Click Update. The updated expiration time appears in the View info section.

SQL

Use the ALTER VIEW SET OPTIONS DDL statement:

In the Google Cloud console, go to the BigQuery page.

Go to BigQuery
In the query editor, enter the following statement:
```
 ALTER VIEW DATASET_ID.MY_VIEW
 SET OPTIONS (
  expiration_timestamp = TIMESTAMP('NEW_TIMESTAMP'));
```
Replace the following:
- DATASET_ID: the ID of the dataset containing your view
- MY_VIEW: the name of the view to be updated
- NEW_TIMESTAMP: a TIMESTAMP value
Click play_circle Run.

For more information about how to run queries, see Run an interactive query.

Issue the bq update command with the --expiration flag. If you are updating a view in a project other than your default project, add the project ID to the dataset name in the following format: project_id:dataset.

bq update \
    --expiration integer \
    project_id:dataset.view

Replace the following::

integer: the default lifetime (in seconds) for the table. The minimum value is 3600 seconds (one hour). The expiration time evaluates to the current time plus the integer value.
project_id: your project ID
dataset: the name of the dataset containing the view you're updating
view: the name of the view you're updating

Examples

Enter the following command to update the expiration time of myview in mydataset to 5 days (432000 seconds). mydataset is in your default project.

bq update \
    --expiration 432000 \
    mydataset.myview

Enter the following command to update the expiration time of myview in mydataset to 5 days (432000 seconds). mydataset is in myotherproject, not your default project.

bq update \
    --expiration 432000 \
    myotherproject:mydataset.myview

API

Call the tables.patch method and use the expirationTime property in the table resource. Because the tables.update method replaces the entire table resource, the tables.patch method is preferred. When you use the REST API, the view's expiration is expressed in milliseconds.

Before trying this sample, follow the Go setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Go API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

Java

Before trying this sample, follow the Java setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Java API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

Node.js

Before trying this sample, follow the Node.js setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Node.js API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

Python

Updating a view's expiration is the same process as updating a table's expiration.

Before trying this sample, follow the Python setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Python API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

Updating a view's description

You can update a view's description by:

Using the Google Cloud console
Using a Data definition language (DDL) statement written in GoogleSQL syntax
Using the bq command-line tool's bq update command
Calling the tables.patch API method
Using the client libraries

To update a view's description:

Console

You cannot add a description when you create a view using the Google Cloud console. After the view is created, you can add a description on the Details page.

In the Explorer panel, expand your project and dataset, then select the view.
Click the Details tab.
Click the pencil icon next to Description.
Enter a description in the dialog box. Click Update to save the new description.

SQL

Use the ALTER VIEW SET OPTIONS DDL statement:

In the Google Cloud console, go to the BigQuery page.

Go to BigQuery
In the query editor, enter the following statement:
```
 ALTER VIEW DATASET_ID.MY_VIEW
 SET OPTIONS (
  description = 'NEW_DESCRIPTION');
```
Replace the following:
- DATASET_ID: the ID of the dataset containing your view
- MY_VIEW: the name of the view to be updated
- NEW_DESCRIPTION: the new view description
Click play_circle Run.

For more information about how to run queries, see Run an interactive query.

Issue the bq update command with the --description flag. If you are updating a view in a project other than your default project, add the project ID to the dataset name in the following format: [PROJECT_ID]:[DATASET].

bq update \
    --description "description" \
    project_id:dataset.view

Replace the following:

description: the text describing the view in quotes
project_id: your project ID.
dataset: the name of the dataset containing the view you're updating
view: the name of the view you're updating

Examples

Enter the following command to change the description of myview in mydatasetto "Description of myview." mydataset is in your default project.

bq update \
    --description "Description of myview" \
    mydataset.myview

Enter the following command to change the description of myview in mydataset to "Description of myview." mydataset is in myotherproject, not your default project.

bq update \
    --description "Description of myview" \
    myotherproject:mydataset.myview

API

Call the tables.patch method and use the description property to update the view's description in the table resource. Because the tables.update method replaces the entire table resource, the tables.patch method is preferred.

Before trying this sample, follow the Go setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Go API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

Java

Updating a view's description is the same process as updating a table's description.

Before trying this sample, follow the Java setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Java API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

Node.js

Before trying this sample, follow the Node.js setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Node.js API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

Python

Updating a view's description is the same process as updating a table's description.

Before trying this sample, follow the Python setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Python API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

Copy views

You can copy a view using the Google Cloud console.

You cannot copy a view by using the bq command-line tool, the REST API, or the client libraries, but you can copy a view in the target dataset.

Required permissions

To copy a view in the Google Cloud console, you need IAM permissions on the source and destination datasets.

On the source dataset, you need the following:
- bigquery.tables.get
- bigquery.tables.getData (required to access the tables referenced by the view's SQL query)
On the destination dataset, you need the following:
- bigquery.tables.create (lets you create a copy of the view in the destination dataset)

Each of the following predefined IAM roles includes the permissions that you need in order to copy a view:

roles/bigquery.dataEditor
roles/bigquery.dataOwner
roles/bigquery.admin

Additionally, if you have the bigquery.datasets.create permission, you can copy views in the datasets that you create. You also need access to the destination dataset unless you created it.

Note: bigquery.jobs.create permissions are not required to copy a view. The Google Cloud console does not generate a copy job when you copy a view.

For more information on IAM roles and permissions in BigQuery, see Predefined roles and permissions.

Copy a view

To copy a view:

In the Explorer panel, expand your project and dataset, then select the view.
In the details panel, click Copy view.
In the Copy view dialog:
- In the Source section, verify that your Project name, Dataset name, and Table name are correct.
- In the Destination section:
  - For Project name, choose the project to which you are copying the view.
  - For Dataset name, choose the dataset that will contain the copied view.
  - For Table name, enter the name of the view. You can rename the view by entering a new view name in the box. If you enter a new name, it must follow the view naming rules.
- Click Copy.

Limits for copy jobs apply. For more information, see Quotas and limits.

Rename a view

Currently, you can rename a view only when you use the Google Cloud console to copy the view. For instructions on renaming a view when you copy it, see Copying a view.

You cannot change the name of an existing view by using the bq command-line tool, the API, or the client libraries. Instead, you must recreate the view with the new name.

Delete views

You can delete a view by:

Using the Google Cloud console
Using the bq command-line tool's bq rm command
Calling the tables.delete API method

Currently, using any available method, you can only delete one view at a time.

To automatically delete views after a specified period of time, set the default expiration time at the dataset level or set the expiration time when you create the view.

When you delete an authorized view, it might take up to 24 hours to remove the deleted view from the authorized views list of the source dataset.

Caution: Deleting a view cannot be undone. If you recreate an authorized view with the same name as the deleted view, you must add the new view to the authorized views list of the source dataset.

Deleting a view also deletes any permissions associated with this view. When you recreate a deleted view, you must also manually reconfigure any access permissions previously associated with it.

Note: You cannot recover views directly, but you can recover the view creation statement by searching for the corresponding audit log activity.

For information about using the log explorer to query the activity log by audit log name, see the audit logs overview.
For information about using projects/PROJECT_ID/logs/cloudaudit.googleapis.com%2Factivity, see BigQuery Data Policy audit logging.

Required permissions

To delete a view, you need the following IAM permissions:

bigquery.tables.delete

Each of the following predefined IAM roles includes the permissions that you need in order to delete a view:

roles/bigquery.dataOwner
roles/bigquery.dataEditor
roles/bigquery.admin

Additionally, if you have the bigquery.datasets.create permission, you can delete views in the datasets that you create.

For more information on IAM roles and permissions in BigQuery, see Predefined roles and permissions.

Delete a view

To delete a view:

Console

In the Google Cloud console, go to the BigQuery page.

Go to BigQuery
In the Explorer panel, expand your project and dataset, then select the view.
In the details panel, click Delete view.
Type "delete" in the dialog, and click Delete to confirm.

SQL

Use the DROP VIEW DDL statement:

In the Google Cloud console, go to the BigQuery page.

Go to BigQuery
In the query editor, enter the following statement:
```
DROP VIEW mydataset.myview;
```
Replace the following:
- DATASET_ID: the ID of the dataset containing your view
- MY_VIEW: the name of the view to be updated
- NEW_DESCRIPTION: the new view description
Click play_circle Run.

For more information about how to run queries, see Run an interactive query.

Use the bq rm command with the --table flag (or -t shortcut) to delete a view. When you use the bq command-line tool to remove a view, you must confirm the action. You can use the --force flag (or -f shortcut) to skip confirmation.

If the view is in a dataset in a project other than your default project, add the project ID to the dataset name in the following format: project_id:dataset.

bq rm \
-f \
-t \
project_id:dataset.view

Where:

project_id is your project ID.
dataset is the name of the dataset that contains the table.
view is the name of the view you're deleting.

Examples:

You can use the bq command-line tool to run bq commands.

In the Google Cloud console, activate Cloud Shell.

Activate Cloud Shell

Enter the following command to delete myview from mydataset. mydataset is in your default project.

bq rm -t mydataset.myview

Enter the following command to delete myview from mydataset. mydataset is in myotherproject, not your default project.

bq rm -t myotherproject:mydataset.myview

Enter the following command to delete myview from mydataset. mydataset is in your default project. The command uses the -f shortcut to bypass confirmation.

bq rm -f -t mydataset.myview

Note: You can enter the bq ls dataset command to confirm that a view was removed from a dataset. API

Call the tables.delete API method and specify the view to delete using the tableId parameter.

Before trying this sample, follow the C# setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery C# API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

Before trying this sample, follow the Go setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Go API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

Java

Before trying this sample, follow the Java setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Java API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

Node.js

Before trying this sample, follow the Node.js setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Node.js API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

PHP

Before trying this sample, follow the PHP setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery PHP API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

Python

Before trying this sample, follow the Python setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Python API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

Ruby

Before trying this sample, follow the Ruby setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Ruby API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

Restore a view

You can't restore a deleted view directly, but there are workarounds for certain scenarios:

If a view is deleted because the parent dataset was deleted, then you can undelete the dataset to retrieve the view.
If a view is deleted explicitly, then you can recreate the view by using the last query that was used to create or update the view. You can find the query definition of the view creation or update operation in logs.

View security

To control access to views in BigQuery, see Authorized views.

What's next

For information on creating views, see Create views.
For information on creating an authorized view, see Creating authorized views.
For information on getting view metadata, see Get information about views.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4