Create and manage resources in the Lakehouse runtime catalog

API

Use the datasets.insert method , and specify the ExternalCatalogDatasetOptions field in the dataset resource that you pass in.

{
  "datasetReference": {
    "projectId": " PROJECT_ID 
",
    "datasetId": " DATASET_ID 
"
  },
  "externalCatalogDatasetOptions": {
    "defaultStorageLocationUri": " URI 
",
    "parameters": {
      ...
    }
  },
  "location": " LOCATION 
"
}

Replace the following:

• PROJECT_ID : the ID of the project that contains your target dataset.
• DATASET_ID : the ID of your target dataset
• URI : the Cloud Storage URI for all tables in the dataset.
• LOCATION : the BigQuery location that you want to create the dataset in.

Apache Spark SQL

 CREATE 
  
 NAMESPACE 
  
  SPARK_CATALOG 
 
 . 
  NAMESPACE 
 
 ; 

Replace the following:

• SPARK_CATALOG : the name of your Apache Spark catalog.
• NAMESPACE : the name of your new namespace.

Terraform

 provider 
  
 "google" 
  
 { 
  
 project 
  
 = 
  
 " PROJECT_ID 
" 
 } 
 resource 
  
 "google_bigquery_dataset" 
  
 "default" 
  
 { 
  
 dataset_id 
  
 = 
  
 " DATASET_ID 
" 
  
 location 
  
 = 
  
 " LOCATION 
" 
  
 external_catalog_dataset_options 
  
 { 
  
 default_storage_location_uri 
  
 = 
  
 " URI 
" 
  
 parameters 
  
 = 
  
 { 
  
 ... 
  
 } 
  
 } 
 } 

Replace the following:

• PROJECT_ID : the ID of the project that contains your target dataset.
• DATASET_ID : the ID of your target dataset.
• LOCATION : the BigQuery location that you want to create the dataset in.
• URI : the Cloud Storage URI for all tables in the dataset.

API

Use the tables.insert method , and specify the ExternalCatalogTableOptions field in the table resource that you pass in.

 { 
  
 "tableReference" 
 : 
  
 { 
  
 "projectId" 
 : 
  
 " PROJECT_ID 
" 
 , 
  
 "datasetId" 
 : 
  
 " DATASET_ID 
" 
 , 
  
 "tableId" 
 : 
  
 " TABLE_ID 
" 
  
 }, 
  
 "externalCatalogTableOptions" 
 : 
  
 { 
  
 "parameters" 
 : 
  
 { 
  
 "table_type" 
 : 
  
 "iceberg" 
 , 
  
 "metadata_location" 
 : 
  
 " METADATA_URI 
" 
  
 }, 
  
 "connection_id" 
 : 
  
 " CONNECTION_ID 
" 
  
 } 
 } 

Replace the following:

• PROJECT_ID : the ID of the project that contains your target table.
• DATASET_ID : the ID of the dataset that contains your target table.
• TABLE_ID : the ID of your target table.
• METADATA_URI : the Cloud Storage URI for the latest Apache Iceberg metadata file. For example, gs://mybucket/mytable/metadata/1234.metadata.json .
• CONNECTION_ID : the ID of your connection to Cloud Storage.

Apache Spark SQL

 CREATE 
  
 TABLE 
  
  SPARK_CATALOG 
 
 . 
  NAMESPACE 
 
 . 
  TABLE 
 
  
 ( 
 id 
  
 bigint 
 , 
  
 data 
  
 string 
 ) 
  
 USING 
  
 iceberg 
 ; 

Replace the following:

• SPARK_CATALOG : the name of your Apache Spark catalog
• NAMESPACE : the name of your namespace
• TABLE : the name of your new table

Terraform

 resource 
  
 "google_bigquery_table" 
  
 "default" 
  
 { 
  
 deletion_protection 
  
 = 
  
 false 
  
 dataset_id 
  
 = 
  
 google_bigquery_dataset.default.dataset_id 
  
 table_id 
  
 = 
  
 " TABLE 
" 
  
 external_catalog_table_options 
  
 { 
  
 storage_descriptor 
  
 { 
  
 location_uri 
  
 = 
  
 " STORAGE_URI 
" 
  
 input_format 
  
 = 
  
 "org.apache.hadoop.mapred.FileInputFormat" 
  
 output_format 
  
 = 
  
 "org.apache.hadoop.mapred.FileOutputFormat" 
  
 } 
  
 parameters 
  
 = 
  
 { 
  
 "table_type" 
  
 = 
  
 "iceberg" 
  
 "metadata_location" 
  
 = 
  
 " METADATA_URI 
" 
  
 "write.parquet.compression-codec" 
  
 : 
  
 "zstd" 
  
 "EXTERNAL" 
  
 : 
  
 "TRUE" 
  
 } 
  
 } 
 } 

Replace the following:

• TABLE : the name of the target table.
• STORAGE_URI : the Cloud Storage URI where the table data is stored, starting with gs:// .
• METADATA_URI : the Cloud Storage URI for the latest Apache Iceberg metadata file. For example, gs://mybucket/mytable/metadata/1234.metadata.json .

API

Use the datasets.list method to view all namespaces, or use the datasets.get method to view information about a defined namespace.

Apache Spark SQL

To view all namespaces in a catalog, use the following statement:

 SHOW 
  
 { 
  
 DATABASES 
  
 | 
  
 NAMESPACES 
  
 } 
  
 IN 
  
  SPARK_CATALOG 
 
 ; 

Replace SPARK_CATALOG with the name of your Apache Spark catalog.

To view information about a defined namespace, use the following statement:

 DESCRIBE 
  
 { 
  
 DATABASE 
  
 | 
  
 NAMESPACE 
  
 } 
  
 [ 
 EXTENDED 
 ] 
  SPARK_CATALOG 
 
 . 
  NAMESPACE 
 
 ; 

Replace the following:

• SPARK_CATALOG : the name of your Apache Spark catalog.
• NAMESPACE : the name of your namespace.

API

Use the tables.list method to view all tables in a namespace, or use the tables.get method to view information about a defined table.

Apache Spark SQL

To view all tables in a namespace, use the following statement:

 SHOW 
  
 TABLES 
  
 IN 
  
  SPARK_CATALOG 
 
 . 
  NAMESPACE 
 
 ; 

Replace the following:

• SPARK_CATALOG : the name of your Apache Spark catalog.
• NAMESPACE : the name of your namespace.

To view information about a defined table, use the following statement:

 DESCRIBE 
  
 TABLE 
  
 [ 
 EXTENDED 
 ] 
  SPARK_CATALOG 
 
 . 
  NAMESPACE 
 
 . 
  TABLE 
 
 ; 

Replace the following:

• SPARK_CATALOG : the name of your Apache Spark catalog.
• NAMESPACE : the name of your namespace.
• TABLE : the name of your table.

API

Use the datasets.patch method , and update the ExternalCatalogDatasetOptions field in the dataset resource . The datasets.update method is not recommended because it replaces the entire dataset resource.

Apache Spark SQL

Use the ALTER DATABASE statement .

API

Use the tables.patch method , and update the ExternalCatalogTableOptions field in the table resource . The tables.update method is not recommended because it replaces the entire table resource.

To update the schema or metadata file, use the tables.patch method and set the autodetect_schema property to true:

PATCH https://bigquery.googleapis.com/bigquery/v2/projects/ PROJECT_ID 
/datasets/ DATASET_ID 
/tables/ TABLE_ID 
?autodetect_schema=true

Replace the following:

• PROJECT_ID : the ID of the project that contains the table that you want to update.
• DATASET_ID : the ID of the dataset that contains the table that you want to update.
• TABLE_ID : the ID of the table that you want to update.

In the body of the request, specify the updated value for each field. For example, to update the Apache Iceberg table's metadata location, specify the updated value for the metadata_location field:

{
  "externalCatalogTableOptions": {
    "parameters": {"metadata_location": " METADATA_URI 
"}
  },
  "schema": null
}'

Replace METADATA_URI with the Cloud Storage URI for the latest Apache Iceberg metadata file. For example, gs://mybucket/mytable/metadata/1234.metadata.json .

Apache Spark SQL

Use the ALTER TABLE statement .

API

Use the datasets.delete method . Set the deleteContents parameter to true to delete the tables in your namespace.

Apache Spark SQL

 DROP 
  
 NAMESPACE 
  
  SPARK_CATALOG 
 
 . 
  NAMESPACE 
 
 ; 

Replace the following:

• SPARK_CATALOG : the name of your Apache Spark catalog.
• NAMESPACE : the name of your namespace.

API

Use the tables.delete method and specify the name of the table. This method doesn't delete the associated files in Cloud Storage.

Apache Spark SQL

To only drop the table, use the following statement:

 DROP 
  
 TABLE 
  
  SPARK_CATALOG 
 
 . 
  NAMESPACE 
 
 . 
  TABLE 
 
 ; 

Replace the following:

• SPARK_CATALOG : the name of your Apache Spark catalog.
• NAMESPACE : the name of your namespace.
• TABLE : the name of the table to drop.

To drop the table and delete the associated files in Cloud Storage, use the following statement:

 DROP 
  
 TABLE 
  
  SPARK_CATALOG 
 
 . 
  NAMESPACE 
 
 . 
  TABLE 
 
  
 PURGE 
 ; 

Replace the following:

• SPARK_CATALOG : the name of your Apache Spark catalog.
• NAMESPACE : the name of your namespace.
• TABLE : the name of the table to delete.