topic Refresh a External table metadata in Data Governance

Refresh a External table metadata

Dp15 — Wed, 17 Jan 2024 15:44:48 GMT

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

Re: Refresh a External table metadata

Dp15 — Wed, 17 Jan 2024 16:43:45 GMT

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?

Re: Refresh a External table metadata

cgrant — Mon, 16 Dec 2024 22:16:45 GMT

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.