Additional Lakehouse runtime catalog features

To customize your Lakehouse runtime catalog configuration, you can use the following additional features:

• Apache Spark Iceberg procedures.
• The filter option for unsupported tables.
• BigQuery connection overrides.
• Access control policies for Lakehouse runtime catalog Iceberg tables.

Use Apache Iceberg Spark procedures

To use Apache Spark procedures, you must include Apache Iceberg SQL extensions in your Apache Spark configuration. For example, you can create a procedure to roll back to a previous state.

Use interactive Apache Spark SQL to roll back to a previous state

You can use an Apache Spark procedure to create, modify, and roll back a table to its previous state. For example:

1. Create an Apache Spark table:

   spark-sql \
      --jars https://storage-download.googleapis.com/maven-central/maven2/org/apache/iceberg/iceberg-spark-runtime-3.5_2.12/1.6.1/iceberg-spark-runtime-3.5_2.12-1.6.1.jar,BIGLAKE_ICEBERG_CATALOG_JAR \
      --conf spark.sql.extensions=org.apache.iceberg.spark.extensions.IcebergSparkSessionExtensions \
      --conf spark.sql.catalog.CATALOG_NAME=org.apache.iceberg.spark.SparkCatalog \
      --conf spark.sql.catalog.CATALOG_NAME.catalog-impl=org.apache.iceberg.gcp.bigquery.BigQueryMetastoreCatalog \
      --conf spark.sql.catalog.CATALOG_NAME.gcp_project=PROJECT_ID \
      --conf spark.sql.catalog.CATALOG_NAME.warehouse=WAREHOUSE_DIRECTORY

   Replace the following:

   • BIGLAKE_ICEBERG_CATALOG_JAR: the Cloud Storage URI of the Apache Iceberg custom catalog plugin to use. Depending on your Apache Iceberg version number, select one of the following:
     • Iceberg 1.9.1: gs://spark-lib/bigquery/iceberg-bigquery-catalog-1.9.1-1.0.1.jar
     • Iceberg 1.6.1: gs://spark-lib/bigquery/iceberg-bigquery-catalog-1.6.1-1.0.2.jar
   • CATALOG_NAME: the catalog name that references your Apache Spark table.

   • PROJECT_ID: the ID of the Google Cloud project.

   • WAREHOUSE_DIRECTORY: the URI of the Cloud Storage folder where your data warehouse is stored.

   USE `CATALOG_NAME`;
   CREATE NAMESPACE NAMESPACE_NAME;
   USE NAMESPACE NAMESPACE_NAME;
   CREATE TABLE NAMESPACE_NAME.TABLE_NAME (id int, data string) USING ICEBERG LOCATION 'WAREHOUSE_DIRECTORY';
   INSERT INTO NAMESPACE_NAME.TABLE_NAME VALUES (1, "first row");
   DESCRIBE EXTENDED TABLE_NAME;

   Replace the following:

   • NAMESPACE_NAME: the namespace name that references your Apache Spark table.
   • TABLE_NAME: a table name that references your Apache Spark table.

   The output contains details about the table configuration:

   ...
   Table Properties [current-snapshot-id=1659239298328512231,format=iceberg/parquet,format-version=2,write.parquet.compression-codec=zstd]
   ...

2. Alter the table again, and then roll it back to the previously created snapshot 1659239298328512231:

   ALTER TABLE TABLE_NAME ADD COLUMNS (newDoubleCol double);
   INSERT INTO TABLE_NAME VALUES (2, "second row", 2.5);
   SELECT * FROM TABLE_NAME;
   CALL CATALOG_NAME.system.set_current_snapshot('NAMESPACE_NAME.TABLE_NAME', SNAPSHOT_ID);
   SELECT * FROM TABLE_NAME;

   Replace the following:

   • SNAPSHOT_ID: the ID of the snapshot you are rolling back to.

   The output is similar to the following:

   1 first row
   Time taken: 0.997 seconds, Fetched 1 row(s)

Filter unsupported tables from table listing functions

When you use Apache Spark SQL with the Lakehouse runtime catalog, the SHOW TABLES command shows all the tables in the specified namespace, even those that aren't compatible with Apache Spark.

To only display supported tables, turn on the filter_unsupported_tables option:

spark-sql
  --jars https://storage-download.googleapis.com/maven-central/maven2/org/apache/iceberg/iceberg-spark-runtime-3.5_2.12/1.6.1/iceberg-spark-runtime-3.5_2.12-1.6.1.jar,