Google Cloud Big Data and Machine Learning Blog

Innovation in data processing and machine learning technology

Focus on Security: BigQuery announces support for customer managed encryption keys and custom roles for Identity Access and Management

Monday, December 4, 2017

By Chad W. Jennings, BigQuery Product Manager, and Maya Kaczorowski, Security & Privacy Product Manager

Today, Google Cloud adds several new BigQuery features and usability enhancements for the enterprise. In particular, we’ve worked hard to let you use BigQuery for more complex and sensitive workloads.

This post contains the following announcements:

  • Customer managed encryption keys for BigQuery (beta)
  • BigQuery support for IAM (Identity Access and Management) custom roles
  • Enhanced query filtering in the BigQuery web user interface
  • Querying multiple tables using wildcard tables

More control over encryption with customer-managed encryption keys

When using BigQuery, your data is already encrypted at rest by default. BigQuery manages this for you without any additional action on your part, using multiple layers of encryption known as envelope encryption. With envelope encryption, your data is encrypted with a data encryption key, which is subsequently encrypted with a key encryption key managed by Google.

Today, we’re excited to introduce customer-managed encryption keys for BigQuery, now in beta. If managing your own encryption keys is a requirement for your organization, you can now associate a key you manage in Cloud KMS (Key Management Service) with a table in BigQuery. With customer-managed encryption keys, you control and manage your data’s key encryption keys in Cloud KMS rather than Google. Learn more about the options you have to encrypt your data.

BigQuery supports IAM custom roles

BigQuery supports Cloud Identity Access and Management (IAM) custom roles, currently in beta. IAM custom roles can tailor BigQuery’s pre-defined roles by adding or subtracting BigQuery permissions to meet the needs of users in the organization. Learn more about IAM custom roles in this blog post and get started here.

Enhanced query filtering

To allow users to better understand how their queries are running, and to search query history, we’ve added several new sorting dimensions to the BigQuery user interface.  Go to and (1) click on query history, then (2) toggle the dropdown menu.

These sorting functions are useful for things like workload management and debugging.

  • Workload Management: Sorting by date is very handy to find recent queries.  
  • Debugging: Sorting queries by duration or duration/byte can reveal queries that are good candidates for optimization.

Sorting options are documented here and summarized below:

Date Sorts queries by run date.
Duration Sorts queries by total run time.
Duration/byte Sorts queries by duration, normalized by input bytes. This allows you to compare the run time of queries with varying input sizes. For example, you might have a set of tables that grow over time and want to compare the efficiency of your query while discounting the difference caused only by the change in input size. This assumes that the relationship between input size and run-time is linear.
Input bytes Sorts queries by bytes read by the query.
Slot-ms Sorts queries by the sum of milliseconds active across all slots used by the query.  Sorting by slot-ms is a more interesting measure of processing effort because it is less affected by system delays and other sources of uncertainty that can impact the observed duration.
Slot-ms/byte Similar to Duration/byte, Slot-ms/byte is a normalized form of slot-ms.

Querying multiple tables using wildcard tables

Wildcard tables enable you to query multiple tables using concise SQL statements. A wildcard table represents a union of all the tables that match the wildcard expression. Wildcard tables can also be combined with partitioned tables to filter on both tables and partitions. This documentation page shows how to use wildcard tables and gives several examples of how using this tool can make your SQL much more concise and clean.


All BigQuery users will benefit from more flexible query sorting options in the UI for debugging and workload management, and from using Wildcard Tables over partitions.

Security-minded customers will appreciate the added functionality of customer-managed encryption keys and Identity Access and Management enhancements that may allow BigQuery to be used on highly sensitive workloads, and in tightly regulated industries such as financial services, insurance, and healthcare.

Also, check out Developer Advocate Felipe Hoffa's informative walkthrough here:

  • Big Data Solutions

  • Product deep dives, technical comparisons, how-to's and tips and tricks for using the latest data processing and machine learning technologies.

  • Learn More

12 Months FREE TRIAL

Try BigQuery, Machine Learning and other cloud products and get $300 free credit to spend over 12 months.