Run federated queries on Google BigQuery

Preview

This article describes how to set up Lakehouse Federation to run federated queries on BigQuery data that is not managed by Databricks. To learn more about Lakehouse Federation, see What is Lakehouse Federation?.

To connect to your BigQuery database using Lakehouse Federation, you must create the following in your Databricks Unity Catalog metastore:

A connection to your BigQuery database.
A foreign catalog that mirrors your BigQuery database in Unity Catalog so that you can use Unity Catalog query syntax and data governance tools to manage Databricks user access to the database.

Before you begin

Workspace requirements:

Workspace enabled for Unity Catalog.

Compute requirements:

Network connectivity from your Databricks Runtime cluster or SQL warehouse to the target database systems. See Networking recommendations for Lakehouse Federation.
Databricks clusters must use Databricks Runtime 13.3 LTS or above and shared or single user access mode.
SQL warehouses must be Pro or Serverless.

Permissions required:

To create a connection, you must be a metastore admin or a user with the CREATE CONNECTION privilege on the Unity Catalog metastore attached to the workspace.
To create a foreign catalog, you must have the CREATE CATALOG permission on the metastore and be either the owner of the connection or have the CREATE FOREIGN CATALOG privilege on the connection.

Additional permission requirements are specified in each task-based section that follows.

Create a connection

A connection specifies a path and credentials for accessing an external database system. To create a connection, you can use Catalog Explorer or the CREATE CONNECTION SQL command in a Databricks notebook or the Databricks SQL query editor.

Note

You can also use the Databricks REST API or the Databricks CLI to create a connection. See POST /api/2.1/unity-catalog/connections and Unity Catalog commands.

Permissions required: Metastore admin or user with the CREATE CONNECTION privilege.

In your Databricks workspace, click Catalog.
At the top of the Catalog pane, click the Add icon and select Add a connection from the menu.

Alternatively, from the Quick access page, click the External data > button, go to the Connections tab, and click Create connection.
Enter a user-friendly Connection name.
Select a Connection type of BigQuery.

Enter the following connection property for your BigQuery instance.

GoogleServiceAccountKeyJson: A raw JSON object that is used to specify the BigQuery project and provide authentication. You can generate this JSON object and download it from the service account details page in Google Cloud under ‘KEYS’. The service account must have proper permissions granted in BigQuery, including BigQuery User and BigQuery Data Viewer. The following is an example.

{
  "type": "service_account",
  "project_id": "PROJECT_ID",
  "private_key_id": "KEY_ID",
  "private_key": "-----BEGIN PRIVATE KEY-----\nPRIVATE_KEY\n-----END PRIVATE KEY-----\n",
  "client_email": "SERVICE_ACCOUNT_EMAIL",
  "client_id": "CLIENT_ID",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://accounts.google.com/o/oauth2/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/SERVICE_ACCOUNT_EMAIL",
  "universe_domain": "googleapis.com"
}

(Optional) Enter the following connection property for your BigQuery instance:

Project Id: A name for the BigQuery project used for billing for all queries run under this connection. Defaults to the project ID of your service account.
(Optional) Click Test connection to confirm network connectivity. This action does not test authentication.
(Optional) Add a comment.
Click Create.

Run the following command in a notebook or the Databricks SQL query editor. Replace <GoogleServiceAccountKeyJson> with a raw JSON object that specifies the BigQuery project and provides authentication. You can generate this JSON object and download it from the service account details page in Google Cloud under ‘KEYS’. The service account needs to have proper permissions granted in BigQuery, including BigQuery User and BigQuery Data Viewer. For an example JSON object, view the Catalog Explorer tab on this page.

CREATE CONNECTION <connection-name> TYPE bigquery
OPTIONS (
  GoogleServiceAccountKeyJson '<GoogleServiceAccountKeyJson>'
);

We recommend that you use Databricks secrets instead of plaintext strings for sensitive values like credentials. For example:

CREATE CONNECTION <connection-name> TYPE bigquery
OPTIONS (
  GoogleServiceAccountKeyJson secret ('<secret-scope>','<secret-key-user>')
)

For information about setting up secrets, see Secret management.

Create a foreign catalog

A foreign catalog mirrors a database in an external data system so that you can query and manage access to data in that database using Databricks and Unity Catalog. To create a foreign catalog, use a connection to the data source that has already been defined.

To create a foreign catalog, you can use Catalog Explorer or CREATE FOREIGN CATALOG in a Databricks notebook or the Databricks SQL query editor.

Note

You can also use the Databricks REST API or the Databricks CLI to create a catalog. See POST /api/2.1/unity-catalog/catalogs or Unity Catalog commands.

Permissions required: CREATE CATALOG permission on the metastore and either ownership of the connection or the CREATE FOREIGN CATALOG privilege on the connection.

In your Databricks workspace, click Catalog to open Catalog Explorer.
At the top of the Catalog pane, click the Add icon and select Add a catalog from the menu.

Alternatively, from the Quick access page, click the Catalogs button, and then click the Create catalog button.
(Optional) Enter the following catalog property:

Data Project Id: A name for the BigQuery project containing data that will be mapped to this catalog. Defaults to the billing project ID set at the connection level.
Follow the instructions for creating foreign catalogs in Create catalogs.

Run the following SQL command in a notebook or the Databricks SQL editor. Items in brackets are optional. Replace the placeholder values.

<catalog-name>: Name for the catalog in Databricks.
<connection-name>: The connection object that specifies the data source, path, and access credentials.

CREATE FOREIGN CATALOG [IF NOT EXISTS] <catalog-name> USING CONNECTION <connection-name>;

Supported pushdowns

The following pushdowns are supported:

Filters
Projections
Limit
Functions: partial, only for filter expressions. (String functions, Mathematical functions, Data, Time and Timestamp functions, and other miscellaneous functions, such as Alias, Cast, SortOrder)
Aggregates
Sorting, when used with limit

The following pushdowns are not supported:

Joins
Windows functions

Data type mappings

The following table shows the BigQuery to Spark data type mapping.

BigQuery type	Spark type
bignumeric, numeric	DecimalType
int64	LongType
float64	DoubleType
array, geography, interval, json, string, struct	VarcharType
bytes	BinaryType
bool	BooleanType
date	DateType
datetime, time, timestamp	TimestampType/TimestampNTZType

When you read from BigQuery, BigQuery Timestamp is mapped to Spark TimestampType if preferTimestampNTZ = false (default). BigQuery Timestamp is mapped to TimestampNTZType if preferTimestampNTZ = true.