Use the Lakehouse runtime catalog with tables in BigQuery

This document provides an example of how to use the Lakehouse runtime catalog with BigQuery tables and Managed Service for Apache Spark.

With the Lakehouse runtime catalog, you can create and use standard (built-in) tables, BigQuery managed Apache Iceberg tables, and external Apache Iceberg tables from BigQuery.

Before you begin

  1. Enable billing for your Google Cloud project. Learn how to check if billing is enabled on a project.

  2. Enable the BigQuery and Dataproc APIs.

    Enable the APIs

Required roles

To get the permissions that you need to use Managed Service for Apache Spark with the Lakehouse runtime catalog as a metadata store, ask your administrator to grant you the following IAM roles:

  • Create Lakehouse runtime catalog tables in Apache Spark:
    • Dataproc Worker (roles/dataproc.worker) on the Managed Service for Apache Spark service account in the project
    • BigQuery Data Editor (roles/bigquery.dataEditor) on the Managed Service for Apache Spark service account in the project
    • Storage Object User (roles/storage.objectUser) on the Managed Service for Apache Spark service account in the project
  • Query Lakehouse runtime catalog tables in BigQuery:

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

You might also be able to get the required permissions through custom roles or other predefined roles.

Connect to a table

  1. Create a dataset in the Google Cloud console.

    CREATE SCHEMA `PROJECT_ID`.DATASET_NAME;

    Replace the following:

    • PROJECT_ID: the ID of the Google Cloud project to create the dataset.
    • DATASET_NAME: a name for your dataset.

  2. Create a Cloud resource connection.

  3. Create a standard BigQuery table.

    CREATE TABLE `PROJECT_ID`.DATASET_NAME.TABLE_NAME (name STRING,id INT64);

    Replace the following:

    • TABLE_NAME: a name for your table.

  4. Insert data into the standard BigQuery table.

    INSERT INTO `PROJECT_ID`.DATASET_NAME.TABLE_NAME VALUES ('test_name1', 123),('test_name2', 456),('test_name3', 789);

  5. Create a BigQuery managed Apache Iceberg table.

    For example, to create a table, run the following CREATE statement.

    CREATE TABLE `PROJECT_ID`.DATASET_NAME.ICEBERG_TABLE_NAME(
name STRING,id INT64
)
WITH CONNECTION `CONNECTION_NAME`
OPTIONS (
file_format = 'PARQUET',
table_format = 'ICEBERG',
storage_uri = 'STORAGE_URI');

    Replace the following:

    • ICEBERG_TABLE_NAME: a name for your managed Apache Iceberg table. For example, iceberg_managed_table.
    • CONNECTION_NAME: the name of your connection. You created this in the previous step. For example, myproject.us.myconnection.
    • STORAGE_URI: a fully qualified Cloud Storage URI. For example, gs://mybucket/table.

  6. Insert data into the BigQuery managed Apache Iceberg table.

    INSERT INTO `PROJECT_ID`.DATASET_NAME.ICEBERG_TABLE_NAME VALUES ('test_name1', 123),('test_name2', 456),('test_name3', 789);

  7. Create an external Apache Iceberg table.

    For example, to create an external Apache Iceberg table, run the following CREATE statement.

    CREATE OR REPLACE EXTERNAL TABLE  `PROJECT_ID`.DATASET_NAME.READONLY_ICEBERG_TABLE_NAME
WITH CONNECTION `CONNECTION_NAME`
OPTIONS (
  format = 'ICEBERG',
  uris =
    ['BUCKET_PATH'],
  require_partition_filter = FALSE);

    Replace the following:

    • READONLY_ICEBERG_TABLE_NAME: a name for your read-only table.
    • BUCKET_PATH: the path to the Cloud Storage bucket that contains the data for the external table, in the format ['gs://bucket_name/[folder_name/]file_name'].

  8. From Apache Spark, query the standard table, BigQuery managed Apache Iceberg table, and external Apache Iceberg table.

    from pyspark.sql import SparkSession

# Create a spark session
spark = SparkSession.builder \
.appName("Lakehouse runtime catalog Iceberg") \
.config("spark.sql.catalog.CATALOG_NAME", "org.apache.iceberg.spark.SparkCatalog") \
.config("spark.sql.catalog.CATALOG_NAME.catalog-impl", "org.apache.iceberg.gcp.bigquery.BigQueryMetastoreCatalog") \
.config("spark.sql.catalog.CATALOG_NAME.gcp_project", "PROJECT_ID") \
.config("spark.sql.catalog.CATALOG_NAME.gcp_location", "LOCATION") \
.config("spark.sql.catalog.CATALOG_NAME.warehouse", "WAREHOUSE_DIRECTORY") \
.getOrCreate()
spark.conf.set("viewsEnabled","true")

# Use the Lakehouse runtime catalog
spark.sql("USE `CATALOG_NAME`;")
spark.sql("USE NAMESPACE DATASET_NAME;")

# Configure spark for temp results
spark.sql("CREATE namespace if not exists MATERIALIZATION_NAMESPACE");
spark.conf.set("materializationDataset","MATERIALIZATION_NAMESPACE")

# List the tables in the dataset
df = spark.sql("SHOW TABLES;")
df.show();

# Query the tables
sql = """SELECT * FROM DATASET_NAME.TABLE_NAME"""
df = spark.read.format("bigquery").load(sql)
df.show()

sql = """SELECT * FROM DATASET_NAME.ICEBERG_TABLE_NAME"""
df = spark.read.format("bigquery").load(sql)
df.show()

sql = """SELECT * FROM DATASET_NAME.READONLY_ICEBERG_TABLE_NAME"""
df = spark.read.format("bigquery").load(sql)
df.show()

    Replace the following:

    • WAREHOUSE_DIRECTORY: the URI of the Cloud Storage folder that's connected to your BigQuery managed Apache Iceberg table and your external Apache Iceberg table.
    • CATALOG_NAME: the name of the catalog that you're using.
    • MATERIALIZATION_NAMESPACE: the namespace for storing temp results.

  9. Run the Apache Spark script using Managed Service for Apache Spark.

    gcloud dataproc batches submit pyspark SCRIPT_PATH \
  --version=2.2 \
  --project=PROJECT_ID \
  --region=REGION \
  --deps-bucket=YOUR_BUCKET \

    Replace the following:

    • SCRIPT_PATH: the path to the script that the batch job uses.
    • PROJECT_ID: the ID of the Google Cloud project to run the batch job in.
    • REGION: the region where your workload runs.
    • YOUR_BUCKET: the location of the Cloud Storage bucket to upload workload dependencies. The gs:// URI prefix of the bucket is not required. You can specify the bucket path or bucket name, for example, mybucketname1.

What's next