Predefined roles

Roles, such as the BigQuery dataViewer role, are predefined and consist of a combination of permissions that are frequently required. For example, the dataViewer role provides, among others, the bigquery.datasets.get permission to get metadata about a dataset, and bigquery.tables.getData to get table data, but not the bigquery.datasets.delete, which would allow any identity with that permission to delete the dataset.

As of this writing, there are eight predefined roles, including four roles associated with access to datasets and associated tables and views. Loosely, in order of increasing capability, these are:

1. metadataViewer (the fully qualified name is roles/bigquery.metadataViewer) provides metadata-only access to datasets, tables, and/or views.

2. dataViewer provides permissions to read data as well as metadata.

3. dataEditor provides the ability to read a dataset and list, create, update, read, and delete tables in that dataset.

4. dataOwner adds the ability to also delete the dataset.

5. readSessionUser provides access to the BigQuery Storage API sessions that are billed to a project.

6. jobUser can run jobs (including queries) that are billed to the project.

7. user can run jobs and create datasets whose storage is billed to the project.

8. admin can manage all data within the project and cancel jobs by other users.

Treat these two sets of roles as being independent and orthogonal. Users granted the bigquery.readSessionUser do not have access to table data—it might be that they need to read data from datasets belonging to a different project! To read data, you must also grant them bigquery.tables.getData permissions. Similarly, having the jobUser role does not grant the ability to create, modify, or delete tables (only to do a SELECT from them); you need to specifically assign the dataEditor role in addition to the jobUser role if you want the user to be able to run Data Definition Language (DDL)/Data Manipulation Language (DML) queries.

It is also quite conceivable that you might want to provide only the dataViewer role without providing the user role. This will be the situation when you want users to pay for their own queries (i.e., to create query jobs in their own projects, but be able to query datasets that belong to you). For the specific capabilities of each role, the full set of permissions, and which REST API methods require what permissions, refer to the BigQuery documentation.

Primitive roles

In addition to predefined roles and custom roles, BigQuery supports primitive roles, which date back to before GCP had support for Cloud IAM. Mostly, you will use the aforementioned predefined roles, but in some cases, it can be more convenient to assign users a role in the project (viewer, editor, or owner) and have the permissions for all BigQuery datasets and jobs in the project be inherited from this project role.

Identities that have viewing rights on the project get dataViewer on all datasets in the project as well as the ability to create jobs (i.e., run queries) that are billed to the project. Project editors get the dataEditor role in addition to project viewer privileges, and project owners get the dataOwner role in addition to project editor privileges. One exception is that only the user who runs a query has access to the cached results table (because of the implications of sharing access to the results of queries that join against datasets to which other project owners might not have access). To grant or revoke primitive roles for projects, use the GCP console.

The primitive roles that provide reading, writing, or ownership access to datasets translate neatly to dataViewer, dataEditor, or dataOwner roles, respectively. As such, they can be granted in all the ways that predefined roles can be granted, but the simplest way is to click the link to share a dataset from the BigQuery web UI.

Custom roles

If the predefined roles don’t meet your specific needs, consider creating a custom role, but note that needing to grant multiple roles (such as jobUser as well as dataViewer) to groups of people is not a good reason to create a custom role.

One reason to create a custom role is to subtract permissions from the predefined roles. For example, the predefined role dataEditor allows the possessor to create, modify, and delete tables. Suppose that you want to allow your data suppliers to create tables but not to modify or delete any existing tables. In that case, you could create a new role named dataSupplier and provide it with the specific list of permissions. You would start by creating a YAML file (called, for instance, dataSupplier.yaml) with the following contents:

title: "Data Supplier"
description: "Can create, but not delete tables"
stage: "ALPHA"
includedPermissions:
- bigquery.datasets.get
- bigquery.tables.list
- bigquery.tables.get
- bigquery.tables.getData
- bigquery.tables.export
- bigquery.datasets.create
- bigquery.tables.create
- bigquery.tables.updateData

Then you would run the following gcloud command to create the custom role:

PROJECT=$(gcloud config get-value project)
gcloud iam roles create dataSupplier --project $PROJECT 
       --file dataSupplier.yaml

You can verify the permissions associated with this role by obtaining its current definition using:

gcloud iam roles describe dataSupplier --project $PROJECT

This works for predefined roles also.

It’s a good idea when creating a new role to set its stage to be ALPHA and try it out on a smaller set of users before upgrading the stage to BETA or GA. This way, you can fine-tune the set of permissions (start with the most restrictive set) before rolling it out widely.

Invoking BigQuery from a Cloud Function

In your Cloud Source Repository, create an .sql file containing the BigQuery SQL query and a Python file implementing the Cloud Function and place both under version control. The Cloud Function could then use the BigQuery client library to submit the query to BigQuery and export results to Google Cloud Storage as long as the query time is less than the timeout of the Cloud Function.⁸

You can create a Cloud Function from the GCP Cloud Console. In the text window, type this code:

from google.cloud import bigquery
def query_to_gcs():
client = bigquery.Client()

# Run query and wait for it to complete
query_job = client.query("""
    ...
    """)
query_job.result()

# Extract to GCS, and wait for it to complete
extract_job = client.extract_table(
    query_job.destination, "gs://bucket/file.csv")
extract_job.result()

Now, instead of scheduling the query, you will schedule the Cloud Function using Cloud Scheduler.

Putting table, view, and function creation under version control

Having version control and repeatability is important not just for queries but also for tables, views, models, stored procedures, and functions. It is preferable, therefore, to put all creation code into a script that you can invoke every time you want to re-create the table, view, model, or function in question.

To create a table from a query result, you can use the BigQuery client library and set the job destination to be the desired table:

from google.cloud import bigquery
client = bigquery.Client()
sql = """
WITH stations AS (
   SELECT [300, 314, 287] AS closed
)
SELECT
  station_id
  , (SELECT name FROM `bigquery-public-data`.london_bicycles.cycle_stations WHERE
id=station_id) AS name
FROM
  stations, UNNEST(closed) AS station_id
"""
job_config = bigquery.QueryJobConfig()
job_config.destination = (
client.dataset('ch10eu').table('stations_under_construction'))
query_job = client.query(sql, location='EU', job_config=job_config)
query_job.result() # Waits for the query to finish

Here’s the equivalent using the DDL approach:

CREATE OR REPLACE TABLE -- or TABLE/MODEL/FUNCTION
ch10eu.stations_under_construction
(
  station_id INT64 OPTIONS(description = 'Station ID'),
  name string OPTIONS(description = 'Official station name')
)
OPTIONS(
    description = 'Stations in London.',
    labels=[("pii", "none")] -- Must be lowercase.
)
AS
 
WITH stations AS (
   SELECT [300, 314, 287] AS closed
)
 
SELECT
  station_id
  , (SELECT name FROM `bigquery-public-data`.london_bicycles.cycle_stations WHERE
id=station_id) AS name
FROM
  stations, UNNEST(closed) AS station_id

Note in this example how the table and column descriptions are stored directly within the CREATE statements in version control. If you already have existing BigQuery tables, you can query the list of tables and columns in your metadata and programmatically create those SQL DDL statements through functions.

Costs by month by product

To get the monthly invoice amount by product, use this query:

SELECT
  invoice.month
  , product
  , ROUND(SUM(cost)
          + SUM(IFNULL((SELECT SUM(c.amount) FROM UNNEST(credits) c),
                0))
          , 2) AS monthly_cost
FROM ch10eu.gcp_billing_export_v1_XXXXXX_XXXXXX_XXXXXX
GROUP BY 1, 2
ORDER BY 1 ASC, 2 ASC

The monthly cost is the sum of the costs for that product corrected by the sum of the credits.

Visualizing the billing report

As illustrated in Figure 10-1, a starter Data Studio dashboard for visualizing the billing data is available.

For details on how to make a copy and start working in Data Studio, see https://cloud.google.com/billing/docs/how-to/visualize-data.

Figure 10-1. Example Data Studio dashboard of the billing export data in BigQuery

Labels

Although obtaining a cost breakdown by product is useful, what you often want is a cost breakdown by cost center within your organization. To enable this level of detail in your billing reports, you need to apply labels (which are key/value pairs) to your GCP resources. Then each row in the billing export will contain values for two columns, labels.key and labels.value, that correspond to the label applied to the GCP resource usage for which you’re being billed.

If the labels are based on team or cost center, the key could be team and the value could be marketing or research. Labels could also be based on environment (e.g., key=environment, value=production, or value=test), application, or component.

You can assign labels to GCP resources like Compute Engine virtual machines (VMs), Dataproc clusters, or Dataflow jobs. Of course, you can also assign labels to BigQuery datasets, tables, models, and even query jobs.

Here’s how to apply the label “environment:learning” to the dataset ch10eu:

bq update --set_label environment:learning ch10eu

You can also apply labels to table and views in a similar way, but (as of this writing) table/view labels do not show up in the billing data:

bq update --set_label environment:learning ch10eu.restored_table

It also possible to assign a label when you submit a job through the command line:

bq query --label environment:learning --nouse_legacy_sql 'SELECT 17'

When submitting through the REST API, populate the labels property for the job resource.

Then the billing export will reflect query costs, and you can aggregate costs by label for the purpose of apportioning costs between environments, or cost centers, or any other label key:

SELECT
  invoice.month
  , label.value
  , ROUND(SUM(cost)
          + SUM(IFNULL((SELECT SUM(c.amount) FROM UNNEST(credits) c),
                0))
          , 2) AS monthly_cost
FROM 
  ch10eu.gcp_billing_export_v1_XXXXXX_XXXXXX_XXXXXX
  , UNNEST(labels) AS label
WHERE
  label.key = 'environment'
GROUP BY 1, 2
ORDER BY 1 ASC, 2 ASC

Cloud Security Command Center

The Cloud Security Command Center (SCC) provides a comprehensive security management and data-risk platform for GCP. By providing visibility into what assets you have and what security state they are in, Cloud SCC makes it easier to prevent, detect, and respond to threats. There are built-in threat detectors that can alert you to suspicious activity.

You can access Cloud SCC from the GCP Cloud Console Security Command Center Marketplace page and launch asset discovery. After your projects have been scanned, you can use the Cloud SCC dashboard to look for common problems like an open port 22 (for Secure Shell [SSH]). After this, asset discovery runs at least once per day.

Stackdriver monitoring and audit logging

You can use Stackdriver to monitor BigQuery resources. These include visualizations of metrics such as overall query times, the number of slots available, and more. BigQuery also automatically sends audit logs to Stackdriver Logging. Stackdriver Logging allows users to filter and export messages to other services, including Cloud Pub/Sub, Cloud Storage, and BigQuery.

In addition to providing long-term log retention functionality, log exports to BigQuery are recommended as a way to provide the ability to do aggregated analysis on logs data. Here is a query that estimates costs (before any discounts are applied) by user identity:

WITH data as
  (
   SELECT
        protopayload_auditlog.authenticationInfo.principalEmail as principalEmail,
        protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent AS
jobCompletedEvent
     FROM
       ch10.cloudaudit_googleapis_com_data_access_2019*
  )
  SELECT
    principalEmail,
    SUM(jobCompletedEvent.job.jobStatistics.totalBilledBytes)/POWER(2, 40)) AS
Estimated_USD_Cost
  FROM
    data
  WHERE
    jobCompletedEvent.eventName = 'query_job_completed'
  GROUP BY principalEmail
  ORDER BY Estimated_USD_Cost DESC

BigQuery audit logs are reported as the protoPayload.metadata within a LogEntry message. They are organized into three streams: admin activity, system events, and data access. The admin activity includes events such as job insertions and completions. System events are events such as the TableDeletion event logged when a table or partition expires and is removed. The data access stream contains information about new jobs, jobs that changed state, table data changes, and table data reading.

Disk failures

Spinning disks have moving parts, and like many things with moving parts, they fail pretty frequently. Because of the way data is encoded in Colossus (the storage infrastructure that underlies BigQuery; see Chapter 6) using erasure encoding, lots of disks can fail without any loss of data. If you have 100,000 disks, you can expect that dozens will fail every day. If you consider that routine maintenance can cause some disks to be unavailable, the number is probably in the hundreds. If there is a power outage, when disks start up again you can lose a lot more. Even with all of these factors, losing data due to hardware failure in BigQuery would be extremely rare.¹⁰

When a disk fails, Colossus detects it and replicates the data to another disk. Google Datacenter Hardware Operations staff will remove the disk and securely destroy it. Software and services using that disk will not notice any disruption, other than certain requests perhaps taking a few milliseconds longer to complete.

Machine failures

Despite all attempts to keep servers up and running for long periods of time, they crash. Operating systems have bugs, cosmic rays corrupt memory, CPUs die, power supplies fail, software has memory leaks, and lots of other things can go wrong. The common approach in server software is to add redundancy and hardening to prevent these types of failures. Expensive servers have backup power supplies and hot-swappable memory and CPUs, so the server never needs to go down.

Google, as a philosophy, takes a different approach. Google datacenters are set up so that they expect any machine to die at any time. Software must be written in a way that handles this. In order to allow any machine to die at any time, Google invented a number of scale-out distributed systems, like MapReduce, Google File System (GFS, the precursor to Colossus), and Dremel.

BigQuery servers crash all the time. When you have hundreds of thousands or millions of independent workers running, some of those are going to hit problems. Virtually any BigQuery server can crash at any time and users will not see more than a small hiccup in their queries. The cluster management software that runs Google datacenters, Borg, will restart any tasks that don’t respond to a health check within a few seconds, sometimes on a different machine. For the most part, even the software running in the query engine doesn’t notice these problems; it just retries on a different task and keeps going.

Even larger problems, like rack failures or network switch failures, are handled transparently. Because of the scale of the datacenter clusters, if a rack or switch fails, it will affect only a few of the tasks running the service, so the service can route around the problem. Nobody gets paged, except perhaps the hardware operations personnel in the datacenter who will need to address the issue directly. But the service operations (SRE) staff sleep soundly.

Zonal failures

So what happens when there are larger failures in which the built-in self-healing can no longer automatically handle the problem? To be clear, these failures are pretty rare. Zones are designed for high resilience to hardware and network failures. Anything that can cause the zone to go offline, like network switches or transformers, usually has a redundant backup. But there have been cases in which someone cuts the wrong fiber cable with a backhoe, or transformers catch fire, and it causes a zone-wide outage.

Some services, like Google Compute Engine, are, as of this writing, tied to a single availability zone. If that zone goes down, the VM instances in that zone go down. BigQuery, on the other hand, is designed to be able to handle almost all zonal failures. In BigQuery, all Cloud Projects have a primary and a secondary location. If there is an outage in the primary location, BigQuery will seamlessly fail over to the secondary location.

There are two types of zonal failures: soft and hard. Soft failures mean that there are problems in the zone but things are still progressing, perhaps with degraded capacity. Soft failures are often the result of a software failure, rather than a hardware issue. Perhaps quota servers are failing, or Bigtable is stuck, or the BigQuery scheduler is taking too long to schedule. Hard failures mean that the zone is down. Maybe it had a power failure or some sort of unrecoverable hardware issue.

BigQuery reacts to soft failures by proactively draining the zone. A drain means that new queries are sent somewhere else, but existing queries are allowed to continue. New requests are routed to the secondary zone. Queries that are in progress might be allowed to continue; if, however, the outage is severe enough, they will be restarted in the new secondary zone.

Soft failures happen fairly frequently. BigQuery operates in dozens of availability zones around the world, and the odds of some service behaving badly on some zone somewhere is pretty high. The good news is that this allows BigQuery to exercise failover code, and users almost never actually notice any hiccup.

Hard failures are much rarer; they generally mean that the entire zone is undergoing severe problems. In the event of a hardware zonal failure, users might notice disruption; existing queries will be cancelled and restarted in the new zone, for example, so they might take up to twice as long to complete. And particularly bad zonal failures could mean that recent data has not been replicated to the secondary zone, and will be unavailable until the zone is brought back online. In the event this happens, queries to the affected tables will fail. BigQuery would rather fail queries than return inconsistent data.

Regional failures

The next level of failure type is a failure in which an entire region goes offline. This is much rarer than a zonal outage. Like zones, regions can have soft failures in which the entire region needs to be neatly shut down. Regions that are in the path of a hurricane might be shut down cleanly before the hurricane hits. By going into shutdown before the arrival of the storm, Google can minimize the chance of data loss when the region is restarted, or in the rare event that the region is damaged. A power outage shouldn’t cause a region to go down because there are backup systems in place, but if the power outage lasts too long, the backup systems can run out of capacity, and so an orderly shutdown of the region would be initiated.

Regions can have hard failures, too, but these are even more rare. Like many “black swan”–type events,¹¹ it is difficult to estimate how rare they are in practice. A catastrophic earthquake could take down a region with little or no warning. Other extreme weather events or natural disasters could also occur unexpectedly. A hard region failure could cause damage to hardware and could mean that data not replicated offsite could be lost.

As of this writing, single-region BigQuery locations (asia-east1, or europe-north1, for example) do not store a physical copy in another site. In general, the reason is that there might not be a place to store a backup copy in another location without violating customers’ requirements for their data. For example, Singapore is an island only about 30 miles across; if a customer requires that their data is not stored outside of Singapore for regulatory purposes, there isn’t much opportunity to store the data elsewhere. However, before making any durability assumptions about your data, you should check the up-to-date documentation provided by Google.

Multiregional BigQuery locations, like the US and the EU, store a backup copy offsite in another region. In the event of a catastrophic failure of a region, the data would be safe. However, it might take some time before that backup becomes available.

Access transparency

Google takes a number of steps to safeguard access to the data. To ensure the safety and reliable running of the system, only a small number of on-call engineers can get access to user data. The Access Transparency program in GCP means that whenever someone at Google accesses your data, you are notified through audit log records. It is generally as simple as that; if someone reads your data, you can find out about it.

Virtual Private Cloud Service Controls

Virtual Private Cloud Service Controls (VPC-SC) is a mouthful to pronounce, but it is a mechanism that gives you fine-grained control over how services can be accessed and where data can flow within GCP. For example, you can limit BigQuery access to a narrow range of IP addresses from your company’s network. Or you can ensure control over how data flows between services by preventing data export from BigQuery to Google Cloud Storage. Alternatively, you might allow export to Google Cloud Storage, but only to Cloud Storage buckets that are owned by your organization.

VPC-SC is not merely a BigQuery feature; it is a feature that works across a number of GCP products. This lets you create one overall policy describing data exfiltration and movement policies. You might decide that you don’t want people to access BigQuery at all (that seems like a shame, though). For more information on VPC, check out the Google Cloud Documentation.

Customer-Managed Encryption Keys

All data is encrypted at rest in BigQuery, but what if you want to make sure that your data is encrypted with your own keys? In these cases, you can use Customer-Managed Encryption Keys (CMEK) in BigQuery. You can manage your keys in Cloud KMS, GCP’s central key management service. You can then designate datasets or tables that you want to be encrypted using those keys.

BigQuery uses multiple layers of key wrapping; that is, the master keys aren’t exposed outside of KMS. Every CMEK-protected table has a wrapped key as part of the table metadata. When BigQuery accesses the table, it sends a request to Cloud KMS to unwrap the key. The unwrapped table key is then used to unwrap separate keys for each file. There are a number of advantages to this key-wrapping protocol that reduce the risk should an unwrapped key be leaked. If you have an unwrapped file key, you can’t read any other files. If you have an unwrapped table key, you can only unwrap file keys after you pass access control checks. And Cloud KMS never discloses the master key. If you delete the key from KMS, the other keys can never be unwrapped (so be careful with your keys!).