This page describes how to update data in BigQuery. Note that DML in BigQuery has some limitations and known issues.
This guide does not cover adding rows to existing tables. To load data into BigQuery, see the guides on loading data.
Before you begin
To update data in BigQuery, you must set up billing and ensure that you have write access to the table.
-
Sign in to your Google account.
If you don't already have one, sign up for a new account.
- Select or create a Cloud Platform project.
- Enable billing for your project.
-
Ensure that you have write access to your table. If you are the owner of the
dataset that contains your table, you probably have write access.
To set write access to a dataset in Google BigQuery:
- Go to the BigQuery web UI.
Go to the BigQuery web UI -
In the navigation, hover on a dataset ID and click the down arrow icon
next to the ID and click Share dataset.
-
Add a person and give that person
editaccess, then click Save changes.
- Go to the BigQuery web UI.
Updating data
Follow along with the instructions below by using this sample file, which represents a dataset with an IP address column (values in the example file are selected from blocks reserved for documentation), which you'd like to mask for anonymization:
Load the JSON file into a UserSessions table.
To mask the last octet in every row's ip_address column, run the following DML
query:
UPDATE sample_db.UserSessions
SET ip_address = REGEXP_REPLACE(ip_address, r"(\.[0-9]+)$", ".0")
WHERE TRUE
Deleting data
Follow along with the instructions below by using the sample files, which represents a dataset with multiple tables of analytics for user sessions and a table of users to be deleted.
Load the JSON files into DeletedUsers, Users, and UserSessions tables. These tables were created by the sample code
Web UI
- Open the Google BigQuery web UI.
- Hover over your dataset ID.
- Click the arrow next to your dataset ID.
- Click Create new table.
- Select File upload for the Location.
- Select JSON (newline delimited) for the File format.
- Choose the Table name.
Under Schema enter a Name for each column in the table and select the appropriate Type.
- Click Add field and repeat until you have entered all the columns in your table.
Click Create table.
The schemas for the sample tables are:
- DeletedUsers
- Name
id, TypeINTEGER
- Name
- Users
- Name
id, TypeINTEGER - Name
date_joined, TypeTIMESTAMP
- Name
- UserSessions
- Name
id, TypeSTRING - Name
user_id, TypeINTEGER - Name
login_time, TypeTIMESTAMP - Name
logout_time, TypeTIMESTAMP - Name
ip_address, TypeSTRING
- Name
Command-line
To use the bq command-line tool to create
the tables, use the bq load command.
To create the DeleteUsers table
bq load --source_format=NEWLINE_DELIMITED_JSON \
sample_db.DeletedUsers \
deletedUsersData.json \
id:integer
To create the Users table
bq load --source_format=NEWLINE_DELIMITED_JSON \
sample_db.Users \
usersData.json \
id:integer,date_joined:timestamp
To create the UserSessions table
bq load --source_format=NEWLINE_DELIMITED_JSON \
sample_db.UserSessions \
userSessionsData.json \
id:string,user_id:integer,login_time:timestamp,logout_time:timestamp,ip_address:string
To delete the information about the users in the DeletedUsers table. Run the following DML queries:
Delete from UsersSessions
DELETE FROM sample_db.UserSessions
WHERE user_id in (SELECT id from sample_db.DeletedUsers)
Delete from Users
DELETE FROM sample_db.Users
WHERE id in (SELECT id from sample_db.DeletedUsers)
What's next
- See the DML reference page.
- See DML syntax and samples on the DML syntax page.
