REFRESH FOREIGN (CATALOG, SCHEMA, and TABLE)

Applies to: Databricks SQL Databricks Runtime 13.3 LTS and above Unity Catalog only

Refreshes the metadata maintained in Unity Catalog for a:

• Foreign catalog and its securables,
• Foreign schema and its securables, or
• Foreign table.

To run REFRESH FOREIGN CATALOG, you need at least one of the following:

• Be a metastore admin
• Have the USE CATALOG privilege on the catalog, the USE SCHEMA privilege on all child schemas, and the SELECT privilege on all child tables

To run REFRESH FOREIGN SCHEMA, you need at least one of the following:

• Be metastore admin
• Be the owner of the parent catalog
• Have MANAGE and USE SCHEMA privileges on the schema and USE CATALOG on the parent catalog
• Have the USE CATALOG privilege on the parent catalog, the USE SCHEMA privilege on the schema, and the SELECT privilege on all child tables

To execute REFRESH FOREIGN TABLE you need at least one of the following:

• Be metastore admin
• Be the owner of the parent catalog
• Be the owner of the parent schema and have the USE CATALOG privilege on the parent catalog
• Have MANAGE and USE SCHEMA privileges on the parent schema and USE CATALOG on the parent catalog
• Have USE CATALOG and USE SCHEMA privileges on the parent catalog and schema, respectively, and the SELECT privilege on the table

To execute the optional RESOLVE DBFS LOCATION clause to update the resolved DBFS location of a Hive metastore federated table, you need at least one of the following:

• Be the table owner.
• Have the MANAGE privilege on the table, along with USE SCHEMA on the schema and USE CATALOG on the parent catalog

Syntax

REFRESH FOREIGN
 { CATALOG foreign_catalog_name |
   SCHEMA foreign_schema_name [ RESOLVE DBFS LOCATION ] |
   TABLE foreign_table_name [ RESOLVE DBFS LOCATION ] }

Parameters

• foreign_catalog_name

  Identifies the foreign catalog to refresh.

• foreign_schema_name

  Identifies the foreign schema to refresh.

• foreign_table_name

  Identifies the foreign table to refresh. The name must not include a temporal specification or options specification.

• RESOLVE DBFS LOCATION

  Use this option to update the resolved DBFS location if the foreign table or schema is Hive metastore federated, stored on DBFS, and the DBFS mount point that backs the table has been re-mapped since the table was last accessed.

  You must be an OWNER or have the MANAGE privilege on the table or schema. For more information about Hive metastore federation, see Hive metastore federation: enable Unity Catalog to govern tables registered in a Hive metastore.

  When executing REFRESH FOREIGN SCHEMA with this option, Unity Catalog updates both the schema's resolved DBFS location and cascades the resolution to all tables within the schema.

Examples

> REFRESH FOREIGN CATALOG some_catalog;

> REFRESH FOREIGN SCHEMA some_catalog.some_schema;

> REFRESH FOREIGN TABLE some_catalog.some_schema.some_table;

—- Resolves the DBFS location of the foreign table and updates it in Unity Catalog.
> REFRESH FOREIGN TABLE hms_fed_catalog.schema.table RESOLVE DBFS LOCATION;

-- Resolves the DBFS location of the foreign schema and all eligible foreign tables within the schema.
> REFRESH FOREIGN SCHEMA hms_fed_catalog.schema RESOLVE DBFS LOCATION;

Limitations

For Delta tables, REFRESH FOREIGN CATALOG and REFRESH FOREIGN SCHEMA do not refresh the table schema and properties key-value map (columns field and properties field, respectively). To do this, run REFRESH FOREIGN TABLE.

Related statements

• CREATE CATALOG