Refresh a External table metadata

[Dp15]

Hi,

I have an external table which is created out of a S3 bucket. The first time I am creating the table I am using the following command :

query = """CREATE TABLE IF NOT EXISTS catalog.schema.external_table_s3

           USING PARQUET

           LOCATION '{}'""".format(bucket_location)

The first time the table is created the files in the 'bucket_location' are loaded into the table. Now, I have a use case where when new files are added in the S3, I would like the metadata of this external table to be refreshed. Is it possible to refresh the metadata of the external table? I tried using the MSCK Repair command 

MSCK REPAIR TABLE catalog.schema.external_table_s3

but my command failed with the following error:

AnalysisException: [UC_COMMAND_NOT_SUPPORTED.WITHOUT_RECOMMENDATION] The command(s): Repair Table are not supported in Unity Catalog. ;
RepairTableCommand catalog.schema.external_table_s3, true, false, MSCK REPAIR TABLE

[Kaniz · Accepted solution]

Hi @Dp15, Yes, it is possible to refresh the metadata of an external table in Databricks. However, the MSCK REPAIR TABLE command is not supported in the Unity Catalog.

Instead, you can use the REFRESH TABLE command to invalidate the cached entries for data and metadat.... The invalidated cache is populated in a lazy manner when the cached table or the query associated wi.... 

Please note that you need to have the necessary privileges to execute these commands.

[Dp15]

Hi @Kaniz thank you for reply, how can we handle the schema changes in the external location, what if there are additions or deletions on the schema, will the refresh table work then too? 

[Kaniz]

Hi @Dp15, According to the documentation, you can update the schema of a Delta Lake table using DDL or DML. You can add, reorder, or rename columns in a table. However, you cannot change the data type or nullability of existing columns.

If you use an external location to store your data, you need to create a storage credential and an e.... You can alter the properties of an external location or rename it using the ALTER EXTERNAL LOCATION .... You can also refresh the metadata of an external table using the REFRESH TABLE command.

I hope this helps you understand how to handle schema changes in external locations in Databricks. 😊