Refresh a External table metadata

Dp15
Contributor

‎01-17-2024 07:44 AM

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
 
0 Kudos

Dp15
Contributor

‎01-17-2024 08:43 AM

Hi @Retired_mod 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? 

cgrant
Databricks Employee
Databricks Employee

‎12-16-2024 02:16 PM

Please try partition discovery for external tables. This feature should make it so that you can successfully run the MSCK REPAIR command, and more importantly, query external Parquet tables in a more performant way.

0 Kudos