Url column issue in UC information_schema.schemata view definition

gardener — Mon, 08 Jan 2024 12:50:46 GMT

Hi, I recently observed that, after creating a new catalog (without a managed location) in Unity Catalog, a column named 'url' is included in the definition of the information_schema.schemata view.

However, there is no url column in the underlying table system.information_schema.schemata, causing queries on the schemata view to fail.

The view definitions of schemata views in catalogs created a few months ago do not contain the url column.

I cannot find an explanation for this change in the Databricks documentation. Can anyone advise if I am doing something wrong?

An example:

CREATE CATALOG testcatalog SHOW CREATE TABLE testcatalog.information_schema.schemata

Results:

CREATE VIEW information_schema.schemata (
catalog_name COMMENT 'Catalog containing the schema.',
schema_name COMMENT 'Name of the schema.',
schema_owner COMMENT 'User or group (principal) that currently owns the schema.',
comment COMMENT 'An optional comment that describes the relation.',
created COMMENT 'Timestamp when the relation was created.',
created_by COMMENT 'Principal which created the relation.',
last_altered COMMENT 'Timestamp when the relation definition was last altered in any way.',
last_altered_by COMMENT 'Principal which last altered the relation.',
url COMMENT 'The external location where the schema was stored'
)
COMMENT 'Describes schemas within the catalog.'
AS SELECT * FROM system.information_schema.schemata WHERE catalog_name = 'testcatalog'

SELECT * FROM testcatalog.information_schema.schemata

Results:

[INCOMPATIBLE_VIEW_SCHEMA_CHANGE] The SQL query of view `testcatalog`.`information_schema`.`schemata` has an incompatible schema change and column url cannot be resolved. Expected 1 columns named url but got [].
Please try to re-create the view by running: CREATE OR REPLACE VIEW testcatalog.information_schema.schemata AS SELECT * FROM system.information_schema.schemata WHERE catalog_name = 'testcatalog'.

Re: Url column issue in UC information_schema.schemata view definition

gardener — Tue, 09 Jan 2024 15:03:31 GMT

Hi, re-creating the view is unfortunately not possible because it is system owned.

CREATE OR REPLACE VIEW testcatalog.information_schema.schemata AS SELECT * FROM system.information_schema.schemata WHERE catalog_name = 'testcatalog'

Results:

[CANNOT_DELETE_SYSTEM_OWNED] System owned table cannot be deleted.

Obviously, there's a workaround to query the underlying schemata table in the system catalog directly instead of the schemata view in the testcatalog:

SELECT * FROM system.information_schema.schemata WHERE catalog_name = 'testcatalog'

topic Url column issue in UC information_schema.schemata view definition in Data Engineering

Url column issue in UC information_schema.schemata view definition

Re: Url column issue in UC information_schema.schemata view definition