Stay organized with collections Save and categorize content based on your preferences.

Introduction to object tables

This document describes object tables, which are read-only tables over unstructured data objects that reside in Cloud Storage.

Object tables let you to analyze unstructured data in Cloud Storage. You can perform analysis with remote functions or perform inference by using BigQuery ML, and then join the results of these operations with the rest of your structured data in BigQuery. To enroll in this preview, complete the enrollment form. To get help once enrolled in this preview, email bq-unstructured-help@google.com.

Like BigLake tables, object tables use access delegation, which decouples access to the object table from access to the Cloud Storage objects. An external connection associated with a service account is used to connect to Cloud Storage, so you only have to grant users access to the object table. This lets you enforce row-level security and manage which objects users have access to.

Object table schema

An object table provides a metadata index over the unstructured data objects in a specified Cloud Storage bucket. Each row of the table corresponds to an object, and the table columns correspond to the object metadata generated by Cloud Storage, including any custom metadata.

An object table also contains a data pseudo-column that represents the file content in raw bytes, which is auto-populated when the object table is created. This pseudo-column is used by the ML.DECODE_IMAGE function when you run inference on image data. You can't include the data pseudo-column in queries, and it doesn't appear as part of the object table schema.

The following table describes the fixed schema used by object tables:

Field name Type Mode Description
uri STRING NULLABLE uri: the Uniform Resource Identifier (URI) of the object, in the format gs://bucket_name/[folder_name/]object_name.
generation INTEGER NULLABLE The generation of this object, which identifies the object version.
content_type STRING NULLABLE The Content-Type of the object data, which identifies what kind of media it is. If an object is stored without a Content-Type, it is served as application/octet-stream.
size INTEGER NULLABLE The Content-Length of the data in bytes.
md5_hash STRING NULLABLE The MD5 hash of the data, encoded using base64. For more information about using the MD5 hash, see Cloud Storage Hashes and ETags.
updated TIMESTAMP NULLABLE The last time the object's metadata was modified.
metadata RECORD REPEATED Custom metadata for the object. Each piece of metadata is represented as a key-value pair in the child (metadata.)name and (metadata.)value fields of the metadata field.
(metadata.)name STRING NULLABLE Key in an individual metadata entry.
(metadata.)value STRING NULLABLE Value in an individual metadata entry.

The rows in an object table look similar to the following:

------------------------------------------------------------------------------------------------------------------------------------------------
|  uri                 | generation | content_type | size  | md5_hash   | updated                        | metadata...name | metadata...value  |
—-----------------------------------------------------------------------------------------------------------------------------------------------
| gs://mybucket/a.jpeg | 165842…    | image/jpeg   | 26797 | 8c33be10f… | 2022-07-21 17:35:40.148000 UTC | null            | null              |
—-----------------------------------------------------------------------------------------------------------------------------------------------
| gs://mybucket/b.bmp  | 305722…    | image/bmp    | 57932 | 44eb90cd1… | 2022-05-14 12:09:38.114000 UTC | null            | null              |
—-----------------------------------------------------------------------------------------------------------------------------------------------

Use cases

You can query the metadata in an object table in the same way you would query any other BigQuery table. However, the primary use case for object tables is to make unstructured data accessible for analysis. You can use BigQuery ML to run inference on image object tables with TensorFlow models. You can also use remote functions to analyze unstructured data almost any way you want to. For example, you could create a remote function that allows you to analyze images by using Cloud Vision, or one that lets you extract metadata from PDF documents by using Apache Tika.

You can create a view or table from the results of your analysis if you want to join your results with other structured data. For example, the following statement creates a table based on inference results:

CREATE TABLE my_dataset.my_inference_results AS
SELECT uri, content_type, vision_feature
FROM ML.PREDICT(
  MODEL my_dataset.vision_model,
  SELECT ML.DECODE_IMAGE(data) AS vision_input
  FROM my_dataset.object_table
);

After the table is created, you can join it with other tables based on either standard or custom metadata fields, as shown following:

SELECT a.vision_feature, a.uri, b.description
FROM my_dataset.my_inference_results a
JOIN my_dataset.image_description b
ON a.uri = b.uri;

You can also create a search index to power searches over the results of your analysis. For example, the following statement creates a search index over data extracted from PDF files:

CREATE SEARCH INDEX my_index ON pdf_text_extract(ALL COLUMNS);

You can then use the index to find what you need in those results:

SELECT * FROM pdf_text_extract WHERE SEARCH(pdf_text, 'Google');

Benefits

Analyzing unstructured data natively in BigQuery provides the following benefits:

  • It reduces manual effort by letting you automate pre-processing steps such as tuning image sizes to model requirements.
  • It lets you use the simple and familiar SQL interface to work with unstructured data.
  • It helps you save costs by utilizing existing BigQuery slots instead of having to provision new forms of compute.

Signed URLs

To get access to the data represented by an object, generate a signed URL. You can use the signed URL to directly view the object data, and you can also pass signed URLs to remote functions to enable them to work with object table data.

Use the EXTERNAL_OBJECT_TRANSFORM function to generate signed URLs, as shown in the following example:

SELECT uri, signed_url
FROM EXTERNAL_OBJECT_TRANSFORM(TABLE mydataset.myobjecttable, ['SIGNED_URL']);

This returns results similar to the following:

---------------------------------------------------------------------------------------------------
|  uri                 | signed_url                                                               |
—--------------------------------------------------------------------------------------------------
| gs://mybucket/a.docx | https://storage.googleapis.com/mybucket/a.docx?X-Goog-SignedHeaders=abcd |
—-------------------------------------------------------------------------------------------------
| gs://mybucket/b.pdf  | https://storage.googleapis.com/mybucket/b.pdf?X-Goog-SignedHeaders=wxyz  |
—--------------------------------------------------------------------------------------------------

Signed URLs generated from object tables allow any user or procedure that possesses them to read the corresponding objects. Generated signed URLs expire after 6 hours. For more information, see Cloud Storage Signed URLs.

Access control

Object tables are built on top of BigLake, so they use an external connection based on a service account to access Cloud Storage data. This decouples access to the table from access to the underlying object store through access delegation. You grant the service account permissions to access data and metadata from the objects and surface it in the table. You grant users permissions only on the table, where you can govern data access by using Identity and Access Management (IAM) and row-level security.

Object tables vary from other tables that use access delegation, in that having access to a row of an object table confers access to the underlying file content. While a user can't access the object directly, they can generate a signed URL that lets them see the file contents. For example, if the user has access to the object table row representing the flower.jpg image file, they can generate a signed URL to display the file and see that it is a picture of a daisy.

Setting a row-level access policy on an object table restricts a user or group's access to the object metadata in selected rows, and also to the objects represented by those rows. For example, the following statement grants the user Alice access only to rows that represent objects created before June 25, 2022:

CREATE ROW ACCESS POLICY before_20220625
ON my_dataset.my_object_table
GRANT TO ("user:alice@example.com")
FILTER USING (updated < TIMESTAMP("2022-06-25"));

With this row-level access policy in place, the following outcomes are true for Alice:

  • Running the query SELECT * FROM my_dataset.my_object_table; only returns rows that have an updated value prior to June 25, 2022.
  • Running inference on my_dataset.my_object_table only returns predictions for objects that have an updated value prior to June 25, 2022.
  • Generating signed URLs for my_dataset.my_object_table only creates URLs for objects that have an updated value prior to June 25, 2022.

You can also restrict access to object table rows by using custom metadata. For example, the following statement restricts the users group to only access rows where the object has been tagged as not containing any personally identifiable information:

CREATE ROW ACCESS POLICY no_pii
ON my_dataset.my_object_table
GRANT TO ("group:users@example.com")
FILTER USING (ARRAY_LENGTH(metadata)=1
AND metadata[OFFSET(0)].name="no_pii")

Security model

The following organizational roles are typically involved in managing and using object tables:

  • Data lake administrators. These administrators typically manage Identity and Access Management (IAM) policies on Cloud Storage buckets and objects.
  • Data warehouse administrators. These administrators typically create, delete, and update tables. Data warehouse administrators need the following IAM roles:
    • BigQuery Admin or BigQuery Data Owner
    • BigQuery Connection Admin
  • Data analysts. Analysts typically have the BigQuery User role and can read data and run queries.

Data lake administrators are responsible for granting read privileges to connections that data warehouse administrators manage. In turn, data warehouse administrators define tables, set appropriate access controls, and share the tables with data analysts.

Supported object files

You can create an object table over any type and size of unstructured data file, and you can create remote functions to work with any type of unstructured data. However, to perform inference by using BigQuery ML, an object table can only be over image files that meet several size and type requirements. For more information, see Limitions.

Metadata caching for performance

You can use cached metadata to improve the performance of inference and other types of analysis on object tables. It is especially helpful in cases where the object table is referencing large numbers of objects.

BigLake and object tables support caching metadata about files from Cloud Storage. The metadata includes file names, partitioning information, and physical metadata from files such as row counts. You can choose whether or not to enable metadata caching on a table. Queries with a large number of files and with Hive partition filters benefit the most from metadata caching.

If you don't enable metadata caching, queries on the table must read Cloud Storage to get object metadata which increases the query latency; listing millions of files from Cloud Storage can take several minutes. If you enable metadata caching, queries can avoid listing files from Cloud Storage and achieve faster partition and file pruning.

There are two properties that control this feature:

  • Maximum staleness, which controls when queries use cached metadata.
  • Metadata cache mode, which controls how the metadata is collected.

When you have metadata caching enabled, you specify the maximum interval of metadata staleness that is acceptable for operations against the table. For example, if you specify an interval of 1 hour, then operations against the table use cached metadata if it has been refreshed within the past hour. If the cached metadata is older than that, the operation falls back to retrieving metadata from Cloud Storage instead. You can specify a staleness interval between 30 minutes and 7 days.

You can choose to refresh the cache either automatically or manually:

  • For automatic refreshes, the cache is refreshed at a system defined interval, usually somewhere between 30 and 60 minutes. Refreshing the cache automatically is a good approach if the files in Cloud Storage are added, deleted, or modified at random intervals. If you need to control the timing of the refresh, for example to trigger the refresh at the end of an extract-transform-load job, use manual refresh.
  • For manual refreshes, you run the BQ.REFRESH_EXTERNAL_METADATA_CACHE system procedure to refresh the metadata cache on whatever schedule you determine. Refreshing the cache manually is a good approach if the files in Cloud Storage are added, deleted, or modified at known intervals, for example as the output of a pipeline.

    If you issue multiple concurrent manual refreshes, only one will succeed.

The metadata cache expires after 7 days if it isn't refreshed.

You should consider how the staleness interval and metadata caching mode values will interact before you set them. Consider the following examples:

  • If you are manually refreshing the metadata cache for a table, and you set the staleness interval to 2 days, you must run the BQ.REFRESH_EXTERNAL_METADATA_CACHE system procedure every 2 days or less if you want operations against the table to use cached metadata.
  • If you are automatically refreshing the metadata cache for a table, and you set the staleness interval to 30 minutes, it is possible that some of your operations against the table will read from Cloud Storage if the metadata cache refresh takes on the longer side of the usual 30 to 60 minute window.

To find information about metadata refresh jobs, query the INFORMATION_SCHEMA.JOBS view, as shown in the following example:

SELECT *
FROM `region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT`
WHERE job_id LIKE '%metadata_cache_refresh%'
AND creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 6 HOUR)
ORDER BY start_time DESC
LIMIT 10;

For more information on setting caching options for an object table, see Create an object table.

Limitations

  • Object tables are only supported when using flat-rate pricing through reservations; on-demand pricing isn't supported.
  • Object tables are read-only, because they map to unstructured data objects in Cloud Storage. You can't alter an object table or modify object table data.
  • Object table support isn't available in Legacy SQL, or other cloud environments such as AWS and Microsoft Azure.
  • If you want to perform inference by using BigQuery ML, the model and the object table you use must meet the requirements described in Limitations.
  • Object tables are subject to the same limitations as all other BigQuery tables. For more information, see Quotas.
  • Queries over object tables are subject to the same limitations as as all other BigQuery queries. For more information, see Quotas.
  • Remote functions that process unstructured data from object tables are subject to to the same limitations as all other remote functions.
  • Signed URLs generated for the objects in an object tables expire after 6 hours, which is the query execution time limit.

Pricing

Using object tables with Analytics Hub

Object tables are compatible with Analytics Hub. Datasets containing object tables can be published as Analytics Hub listings. Analytics Hub subscribers can subscribe to these listings, which provision a read-only dataset, called a linked dataset, in their project. Subscribers can query all tables in the linked dataset, including all object tables. For more information, see Subscribe to a listing.

What's next