Additional BigLake metastore features

To customize your BigLake metastore 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 BigLake metastore Iceberg tables

Use Iceberg Spark procedures

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

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

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

Create a 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,gs://spark-lib/bigquery/iceberg-bigquery-catalog-1.6.1-1.0.1-beta.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:

CATALOG_NAME : the catalog name that references your 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 Spark table.
TABLE_NAME : a table name that references your 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 ] 
...

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 Spark SQL with the BigLake metastore catalog, the SHOW TABLES command shows all the tables in the specified namespace, even those that aren't compatible with 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,gs://spark-lib/bigquery/iceberg-bigquery-catalog-1.6.1-1.0.1-beta.jar  
 \ 
  
--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 
.gcp_location = 
 LOCATION 
  
 \ 
  
--conf  
spark.sql.catalog. CATALOG_NAME 
.warehouse = 
 WAREHOUSE_DIRECTORY 
  
 \ 
  
--conf  
spark.sql.catalog. CATALOG_NAME 
.filter_unsupported_tables = 
 "true"

Replace the following:

CATALOG_NAME : the name of the Spark catalog to use.
PROJECT_ID : the ID of the Google Cloud project to use.
LOCATION : the location of the BigQuery resources.
WAREHOUSE_DIRECTORY : the Cloud Storage folder to use as the data warehouse.

Set a BigQuery connection override

You can use BigQuery connections to access data stored outside of BigQuery, such as in Cloud Storage.

To set a BigQuery connection override that provides access to a Cloud Storage bucket, complete the following steps:

In your BigQuery project, create a new connection to your Cloud Storage resource. This connection defines how BigQuery accesses your data.
Grant the user or service account accessing the data the roles/bigquery.connectionUser role on the connection.

Make sure that the connection resource shares the same location as the target resources in BigQuery. For more information, see Manage connections .

Specify the connection in your Iceberg table with the bq_connection property:

 CREATE 
  
 TABLE 
  
  TABLE_NAME 
 
  
 ( 
 id 
  
 int 
 , 
  
 data 
  
 string 
 ) 
  
 USING 
  
 ICEBERG 
  
 LOCATION 
  
 ' WAREHOUSE_DIRECTORY 
' 
  
 TBLPROPERTIES 
  
 ( 
 'bq_connection' 
 = 
 'projects/ PROJECT_ID 
/locations/ LOCATION 
/connections/ CONNECTION_ID 
' 
 );

Replace the following:

TABLE_NAME : a table name for your Spark table.
WAREHOUSE_DIRECTORY : the URI of the Cloud Storage bucket that stores your data.
PROJECT_ID : the ID of the Google Cloud project to use.
LOCATION : the location of the connection.
CONNECTION_ID : the ID of the connection.

Set access control policies

You can enable fine-grained access control (FGAC) on BigLake metastore Iceberg tables by configuring access control policies. You can only set access control policies on tables that use a BigQuery connection override . You can set these policies in the following ways:

After you configure your FGAC policies, you can query the table from Spark using the following example:

 from 
  
 pyspark.sql 
  
 import 
 SparkSession 
 # Create a Spark session 
 spark 
 = 
 SparkSession 
 . 
 builder 
\ . 
 appName 
 ( 
 "BigLake Metastore 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 
 . 
 sql 
 ( 
 "USE ` CATALOG_NAME 
`;" 
 ) 
 # Configure spark for storing temp results 
 spark 
 . 
 conf 
 . 
 set 
 ( 
 "viewsEnabled" 
 , 
 "true" 
 ) 
 spark 
 . 
 sql 
 ( 
 "CREATE namespace if not exists MATERIALIZATION_NAMESPACE 
" 
 ); 
 spark 
 . 
 conf 
 . 
 set 
 ( 
 "materializationDataset" 
 , 
 " MATERIALIZATION_NAMESPACE 
" 
 ) 
 spark 
 . 
 sql 
 ( 
 "USE NAMESPACE DATASET_NAME 
;" 
 ) 
 sql 
 = 
 """SELECT * FROM DATASET_NAME 
. ICEBERG_TABLE_NAME 
""" 
 df 
 = 
 spark 
 . 
 read 
 . 
 format 
 ( 
 "bigquery" 
 ) 
 . 
 load 
 ( 
 sql 
 ) 
 df 
 . 
 show 
 ()

Replace the following:

CATALOG_NAME : the name of your catalog.
PROJECT_ID : the ID of the project that contains your BigQuery resources.
LOCATION : the location of the BigQuery resources.
WAREHOUSE_DIRECTORY : the URI of the Cloud Storage folder that contains your data warehouse.
MATERIALIZATION_NAMESPACE : the namespace where you want to store temporary results.
DATASET_NAME : the name of your dataset that contains the table that you are querying.
ICEBERG_TABLE_NAME : the name the table that you are querying.

Additional BigLake metastore features

Use Iceberg Spark procedures

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

Filter unsupported tables from table listing functions

Set a BigQuery connection override

Set access control policies

What's next