Url column issue in UC information_schema.schemata view definition

[gardener]

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'.

[Kaniz]

Hi @gardener, Based on the Databricks documentation, the information_schema.schemata view should contain the following columns:

• catalog_name: Catalog containing the schema.
• schema_name: Name of the schema.
• schema_owner: User or group (principal) that currently owns the schema.
• comment: An optional comment that describes the relation.
• created: Timestamp when the relation was created.
• created_by: Principal which created the relation.
• last_altered: Timestamp when the relation definition was last altered in any way.
• last_altered_by: Principal which last altered the relation.

As suggested by the error message, you could try to re-create the view without the URL column:

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

[gardener · Accepted solution]

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'