Introduction to BigLake tables

This document provides an overview of BigLake and assumes familiarity with database tables and Identity and Access Management (IAM). To query data stored in the supported data stores, you must first create BigLake tables and then query them using GoogleSQL syntax:

You can also upgrade an external table to BigLake. For more information, see Upgrade an external table to BigLake.

BigLake tables let you query structured data in external data stores with access delegation. Access delegation decouples access to the BigLake table from access to the underlying data store. An external connection associated with a service account is used to connect to the data store. Because the service account handles retrieving data from the data store, you only have to grant users access to the BigLake table. This lets you enforce fine-grained security at the table level, including row-level and column-level security. For BigLake tables based on Cloud Storage, you can also use dynamic data masking. To learn more about multi-cloud analytic solutions using BigLake tables with Amazon S3 or Blob Storage data, see BigQuery Omni.

Supported data stores

You can use BigLake tables with the following data stores:

Temporary table support

BigLake tables based on Cloud Storage can be temporary or permanent. BigLake tables based on Amazon S3 or Blob Storage must be permanent.

Multiple source files

You can create a BigLake table based on multiple external data sources, provided those data sources have the same schema.

BigQuery Omni joins

BigQuery Omni joins let you run queries that span both Google Cloud and BigQuery Omni regions. You can use GoogleSQL JOIN operations to analyze data across many different storage solutions, such as AWS, Azure, public datasets, and other Google Cloud services. BigQuery Omni joins eliminate the need to copy data across sources before running queries.

You can reference BigLake tables anywhere in a SELECT statement as if they were standard BigQuery tables, including in data manipulation language (DML) and data definition language (DDL) statements that use subqueries to retrieve data. You can use multiple BigLake tables from different clouds and BigQuery tables in the same query. All BigQuery tables must be from the same region.

BigQuery Omni join required permissions

To get the permissions that you need to run a BigQuery Omni join, ask your administrator to grant you the following IAM roles on the project where the join is executed:

For more information about granting roles, see Manage access to projects, folders, and organizations.

These predefined roles contain the permissions required to run a BigQuery Omni join. To see the exact permissions that are required, expand the Required permissions section:

Required permissions

The following permissions are required to run a BigQuery Omni join:

  • bigquery.jobs.create
  • bigquery.tables.getData

You might also be able to get these permissions with custom roles or other predefined roles.

BigQuery Omni join costs

When you run a BigQuery Omni join operation, BigQuery parses the query into local and remote parts. The local part is treated as a standard query in the BigQuery region. The remote part is converted into a CREATE TABLE AS SELECT (CTAS) operation on the referenced BigLake table in the BigQuery Omni region, which creates a temporary table in your BigQuery region. BigQuery then uses this temporary table to execute your BigQuery Omni join and deletes the table automatically after eight hours.

You incur data transfer costs for data in the referenced BigLake tables. However, BigQuery helps reduce these costs by only transferring columns and rows in the BigLake table that are referenced in the query, rather than the entire table. We recommend specifying a column filter that is as narrow as possible to further reduce transfer costs. The CTAS job appears in your job history and displays information such as the number of transferred bytes. Successful transfers incur costs even if the main query job fails. For more information, see BigQuery Omni pricing.

Consider the following query as an example:

SELECT *
FROM bigquery_dataset.bigquery_table AS clients
WHERE clients.sales_rep IN (
  SELECT id
  FROM aws_dataset.aws_table1 AS employees
  INNER JOIN aws_dataset.aws_table2 AS active_employees
    ON employees.id = active_employees.id
  WHERE employees.level > 3
);

This example has two transfers: one from an employees table (with a level filter) and one from an active employees table. The join is performed in the BigQuery region after the transfer occurs. If one transfer fails and the other succeeds, data transfer charges are still applied for the successful transfer.

BigQuery Omni join limitations

  • BigQuery Omni joins aren't supported in the BigQuery free tier and in the BigQuery sandbox.
  • Aggregations might not be pushed down to the BigQuery Omni regions if the query contains JOIN statements.
  • Each temporary table is only used for a single BigQuery Omni query and is not reused even if the same query is repeated multiple times.
  • The transfer size limit for each transfer is 60 GB. Specifically, if you apply a filter on a BigLake table and load the result, it must be smaller than 60 GB. To request an increase, contact Support. There is no limit on scanned bytes.
  • BigQuery Omni join queries employ an internal quota on the rate of queries. If the rate of queries exceeds the quota, you might receive an All our servers are busy processing data transferred between regions error. Retrying the query should work in most cases. Contact support to increase the internal quota to support a higher rate of queries.
  • BigQuery Omni joins are only supported in colocated BigQuery regions with their corresponding BigQuery Omni regions and in the US and EU multi-regions. BigQuery Omni joins that are run in the US or EU multi-regions can only access data in US or EU BigQuery Omni regions respectively.
  • If a BigQuery Omni join query references 10 or more datasets from BigQuery Omni regions, it might fail with an error Not found: Dataset <BigQuery dataset> was not found in location <BigQuery Omni region>. To avoid this issue, we recommend explicitly specifying a location when you run a BigQuery Omni join that references more than 10 datasets. Be aware that if you explicitly specify a BigQuery region and your query only contains BigLake tables, then your query is run as a BigQuery Omni query and incurs data transfer costs.
  • You can't query the _FILE_NAME pseudo-column with BigQuery Omni joins.
  • When you reference the columns of a BigLake table in a WHERE clause, you can't use INTERVAL or RANGE literals.
  • BigQuery Omni join jobs don't report the number of bytes that are processed and transferred from other clouds. This information is available in the child CTAS jobs that are created as part of BigQuery Omni query execution.
  • Authorized views and authorized routines referencing BigQuery Omni tables or views are only supported in BigQuery Omni regions.
  • If your BigQuery Omni query references STRUCT columns, no pushdowns are applied to any remote subqueries. To optimize performance, consider creating a view in the BigQuery Omni region that unpacks necessary fields from STRUCT columns into individual columns.
  • Collation isn't supported by BigQuery Omni joins.
  • BigQuery Omni joins don't support joining BigQuery Omni views using the ORDER BY clause.

BigQuery Omni join examples

The following query joins an orders table in a BigQuery region with a lineitem table in a BigQuery Omni region:

SELECT
  l_shipmode,
  o_orderpriority,
  count(l_linenumber) AS num_lineitems
FROM bigquery_dataset.orders
JOIN aws_dataset.lineitem
  ON orders.o_orderkey = lineitem.l_orderkey
WHERE
  l_shipmode IN ('AIR', 'REG AIR')
  AND l_commitdate < l_receiptdate
  AND l_shipdate < l_commitdate
  AND l_receiptdate >= DATE '1997-01-01'
  AND l_receiptdate < DATE '1997-02-01'
GROUP BY l_shipmode, o_orderpriority
ORDER BY l_shipmode, o_orderpriority;

This query is broken into local and remote parts. The following query is sent to the BigQuery Omni region to execute first. The result is a temporary table in the BigQuery region. You can view this child CTAS job and its metadata in your job history.

CREATE OR REPLACE TABLE temp_table
AS (
  SELECT
    l_shipmode,
    l_linenumber,
    l_orderkey
  FROM aws_dataset.lineitem
  WHERE
    l_shipmode IN ('AIR', 'REG AIR')
    AND l_commitdate < l_receiptdate
    AND l_shipdate < l_commitdate
    AND l_receiptdate >= DATE '1997-01-01'
    AND l_receiptdate < DATE '1997-02-01'
);

After the temporary table is created, the JOIN operation completes, and the following query is run:

SELECT
  l_shipmode,
  o_orderpriority,
  count(l_linenumber) AS num_lineitems
FROM bigquery_dataset.orders
JOIN temp_table
  ON orders.o_orderkey = lineitem.l_orderkey
GROUP BY l_shipmode, o_orderpriority
ORDER BY l_shipmode, o_orderpriority;

As another example, consider the following BigQuery Omni join:

SELECT c_mktsegment, c_name
FROM bigquery_dataset.customer
WHERE c_mktsegment = 'BUILDING'
UNION ALL
SELECT c_mktsegment, c_name
FROM aws_dataset.customer
WHERE c_mktsegment = 'FURNITURE'