BigLake and External Tables in BigQuery

Not all data belongs inside the warehouse. Files that other tools also read, history that is rarely queried, or a lake that existed long before BigQuery arrived can all stay where they are in Cloud Storage and still be queried with SQL. BigQuery offers two kinds of table for that: the external table and the BigLake table. They point at the same files and look identical in a query. They differ in one thing that turns out to matter a great deal: whose permission is used to read the files.

A table that holds no data

Both kinds are tables whose schema and metadata live in BigQuery while the rows stay in the files (Google Cloud: external data sources). You query them like any table and can join them to native tables.

An external table is the simple form:

CREATE EXTERNAL TABLE lake.trips_raw
OPTIONS (
  format = 'PARQUET',
  uris = ['gs://example-lake/trips/*.parquet']
);

To query it, a user needs permission on the table and on the bucket's objects (Google Cloud: external tables). BigQuery reads the files as that user.

A BigLake table adds a connection:

CREATE EXTERNAL TABLE lake.trips
WITH CONNECTION `analytics-prod.us.lake-connection`
OPTIONS (
  format = 'PARQUET',
  uris = ['gs://example-lake/trips/*.parquet'],
  max_staleness = INTERVAL 1 DAY,
  metadata_cache_mode = 'AUTOMATIC'
);

The connection has its own service account. BigQuery reads the files as that account, so users need permission only on the table. Google calls this access delegation (Google Cloud: BigLake tables).

Why delegation matters

With an external table, giving someone access to the data means giving them access to the files. Once they can read the files, nothing in BigQuery can restrict which rows or columns they see, because they can simply open the files.

With a BigLake table the bucket stays closed to users. The table becomes the only door, and a door can have rules:

Who can read what

The script models the rules for three principals and one small file of three rows. A row policy on the BigLake table limits the two analysts to rows for the United States. It is a model of the documented rules, not BigQuery or IAM.

# A model of who can read the same files through an external table, through a BigLake table, and
# directly from the bucket. It applies the documented access rules; it is not BigQuery or IAM.
ROWS = [("US", "phone", 100), ("JP", "tablet", 300), ("UK", "laptop", 200)]     # the files' content

PRINCIPALS = {            # permission on the table, permission to read objects in the bucket
    "kim, analyst":                {"table": True,  "bucket": False},
    "lee, analyst with bucket":    {"table": True,  "bucket": True},
    "loader, pipeline account":    {"table": False, "bucket": True},
}
CONNECTION_CAN_READ_BUCKET = True                       # the BigLake connection's service account
ROW_POLICY = {"kim, analyst": "US", "lee, analyst with bucket": "US"}     # on the BigLake table only


def external_table(who):
    access = PRINCIPALS[who]
    if not access["table"]:
        return "denied: no access to the table"
    if not access["bucket"]:
        return "denied: no access to the bucket"       # both permissions are required
    return f"{len(ROWS)} rows"


def biglake_table(who):
    if not PRINCIPALS[who]["table"]:
        return "denied: no access to the table"
    if not CONNECTION_CAN_READ_BUCKET:
        return "denied: the connection cannot read the bucket"
    visible = [row for row in ROWS if who not in ROW_POLICY or row[0] == ROW_POLICY[who]]
    return f"{len(visible)} row{'s' if len(visible) != 1 else ''} ({', '.join(row[0] for row in visible)})"


def direct_read(who):
    return f"{len(ROWS)} rows" if PRINCIPALS[who]["bucket"] else "denied: no access to the bucket"


print(f"{'principal':26} {'external table':33} {'BigLake table':33} reading the files directly")
for who in PRINCIPALS:
    print(f"{who:26} {external_table(who):33} {biglake_table(who):33} {direct_read(who)}")

principal                  external table                    BigLake table                     reading the files directly
kim, analyst               denied: no access to the bucket   1 row (US)                        denied: no access to the bucket
lee, analyst with bucket   3 rows                            1 row (US)                        3 rows
loader, pipeline account   denied: no access to the table    denied: no access to the table    3 rows

• Kim has access to the tables only. The external table refuses her, because it needs bucket access as well. The BigLake table serves her the one row her policy allows.
• Lee has been given bucket access too. Through the BigLake table Lee still sees one row. Through the external table, or by reading the files directly, Lee sees all three. The row policy has been bypassed, without any error.
• The pipeline account can read the files and nothing else, which is right for an account whose job is to write them.

Lee's line is the warning Google gives in its own documentation: analysts should not be able to read the objects directly, because that circumvents the controls placed on the table. BigLake's security is only as good as the discipline of keeping the bucket closed.

Setting up a BigLake table

Four steps, of which the third is the one people forget.

# 1. Create a connection
bq mk --connection --location=us --project_id=analytics-prod \
  --connection_type=CLOUD_RESOURCE lake-connection

# 2. Find the service account that the connection uses
bq show --connection analytics-prod.us.lake-connection

# 3. Let that account, and only that account, read the bucket
gcloud storage buckets add-iam-policy-binding gs://example-lake \
  --member="serviceAccount:CONNECTION_SERVICE_ACCOUNT" \
  --role="roles/storage.objectViewer"

The commands follow the documented forms (Google Cloud: Cloud resource connection). The fourth step is the CREATE EXTERNAL TABLE ... WITH CONNECTION statement shown earlier. After that, security is added on the table (Google Cloud: create BigLake tables):

CREATE ROW ACCESS POLICY only_us
ON lake.trips
GRANT TO ('group:analysts-us@example.com')
FILTER USING (country = 'US');

The same governance follows the data into other engines. Spark, Trino and similar tools can read a BigLake table through the BigQuery Storage API, which enforces the row and column policies on that path too.

Metadata caching

Before it can read a table made of files, BigQuery has to list them and learn what each contains. With many files, that listing can take longer than the query. A BigLake table can cache this metadata: file names, partition information and row counts.

Two options control it:

• max_staleness sets how old cached metadata may be and still be used, from 30 minutes to 7 days.
• metadata_cache_mode is AUTOMATIC, refreshed by the system roughly every 30 to 60 minutes, or MANUAL, refreshed when you call a system procedure, for example at the end of the pipeline that writes the files.

The trade-off is visible in the name. Within the staleness window, a file added a minute ago may not appear in results. For a lake loaded once a day, a manual refresh after the load gives both speed and correctness.

What these tables cannot do

They are read-only views of files, and the limitations follow from that:

• No changes. DML is not supported.
• No consistency guarantee if files change while a query runs.
• Possibly slower than native tables. Google's advice is to load the data if query speed is the priority.
• No clustering, time travel or BI Engine, and partitioning only through the layout of the files.
• Same location. The bucket must be colocated with the dataset.

Load, external or BigLake?

1. Is the data queried often, by many people, with performance expectations? Load it into a native table. See Cloud Storage to BigQuery.
2. Must the files stay in the bucket, because other engines use them or the volume is large and rarely read? Use a BigLake table.
3. Is it a one-off look at some files, by people who already have access to the bucket? An external table is enough.
4. Is the source a database, not files? That is a federated query.

A common arrangement uses all three: raw files behind BigLake tables, cleaned and modelled data in native tables, and external tables for the occasional investigation.

For the access model underneath, see GCP IAM explained and service accounts. For the bucket itself, see Google Cloud Storage.

Lake and warehouse design are covered in the Data Engineering course.

Sources

• Introduction to external data sources (Google Cloud Documentation) — Google Cloud
• Introduction to external tables (Google Cloud Documentation) — Google Cloud
• Introduction to BigLake tables (Google Cloud Documentation) — Google Cloud
• Create and set up a Cloud resource connection (Google Cloud Documentation) — Google Cloud
• Create Cloud Storage BigLake tables (Google Cloud Documentation) — Google Cloud