Use the Lakehouse runtime catalog with Apache Spark stored procedures

Apache Spark stored procedures execute jobs within borderless Lakehouse. By connecting to the Lakehouse runtime catalog, these procedures run Spark SQL commands directly against your underlying data warehouse.

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 use Apache Spark stored procedures, review the required roles for stored procedures and grant the necessary roles.

To get the permissions that you need to use Apache Spark and stored procedures with the Lakehouse runtime catalog as a metadata service, ask your administrator to grant you the following IAM roles:

• Create Lakehouse runtime catalog tables in Apache Spark:
  • BigQuery Data Editor (roles/bigquery.dataEditor) on the Spark Connection service account in the project
  • Storage Object User (roles/storage.objectUser) on the Spark Connection service account in the project
• Query Lakehouse runtime catalog tables in BigQuery :
  • BigQuery Data Viewer (roles/bigquery.dataViewer) on the project
  • BigQuery User (roles/bigquery.user) on the project
  • Storage Object Viewer (roles/storage.objectViewer) on the project

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.

Create and run a stored procedure

The following example shows you how to create and run a stored procedure with the Lakehouse runtime catalog.

1. Go to the BigQuery page.

   Go to BigQuery

2. In the query editor, add the following sample code for the CREATE PROCEDURE statement.

   CREATEORREPLACEPROCEDURE
   `PROJECT_ID.BQ_DATASET_ID.PROCEDURE_NAME`()
   WITHCONNECTION`PROJECT_ID.REGION.SPARK_CONNECTION_ID`OPTIONS(engine='SPARK',
   runtime_version='1.1',
   properties=[("spark.sql.catalog.CATALOG_NAME.warehouse",
   "WAREHOUSE_DIRECTORY"),
   ("spark.sql.catalog.CATALOG_NAME.gcp_location",
   "LOCATION"),
   ("spark.sql.catalog.CATALOG_NAME.gcp_project",
   "PROJECT_ID"),
   ("spark.sql.catalog.CATALOG_NAME",
   "org.apache.iceberg.spark.SparkCatalog"),
   ("spark.sql.catalog.CATALOG_NAME.catalog-impl",
   "org.apache.iceberg.gcp.bigquery.BigQueryMetastoreCatalog"),
   ("spark.jars.packages",
   "org.apache.iceberg:iceberg-spark-runtime-3.5_2.12:1.6.1")],
   jar_uris=["gs://spark-lib/bigquery/iceberg-bigquery-catalog-1.6.1-1.0.2.jar"])
   LANGUAGEpythonASR"""
   from pyspark.sql import SparkSession
   spark = SparkSession \
   .builder \
   .appName("LakehouseruntimecatalogIceberg") \
   .getOrCreate()
   spark.sql("USECATALOG_NAME;")
   spark.sql("CREATENAMESPACEIFNOTEXISTSNAMESPACE_NAME;")
   spark.sql("USENAMESPACE_NAME;")
   spark.sql("CREATETABLETABLE_NAME(idint,datastring)USINGICEBERGLOCATION'WAREHOUSE_DIRECTORY'")
   spark.sql("DESCRIBETABLE_NAME;")
   spark.sql("INSERTINTOTABLE_NAMEVALUES(1,\"first row\");")
   spark.sql("SELECT * from TABLE_NAME;")
   spark.sql("ALTER TABLE TABLE_NAME ADD COLUMNS (newDoubleCol double);")
   spark.sql("DESCRIBE TABLE_NAME;")
   """;
   CALL `PROJECT_ID.BQ_DATASET_ID.PROCEDURE_NAME`();

   Replace the following:

   • PROJECT_ID: the ID of your Google Cloud project.
   • BQ_DATASET_ID: the ID of the dataset in BigQuery that contains the procedure.
   • PROCEDURE_NAME: the name of the procedure that you're creating or replacing.
   • REGION: the location of your Apache Spark connection.
   • LOCATION: the location of your BigQuery resources.
   • SPARK_CONNECTION_ID: the ID of your Apache Spark connection.
   • CATALOG_NAME: the name of the catalog that you're using.
   • WAREHOUSE_DIRECTORY: the URI of the Cloud Storage folder that contains your data warehouse.
   • NAMESPACE_NAME: the namespace that you're using.

What's next

• Set up optional Lakehouse runtime catalog features.