topic Re: SQL View Formatting in Catalog - Can you turn it off? in Data Engineering

SQL View Formatting in Catalog - Can you turn it off?

SethParker — Mon, 04 Nov 2024 22:08:48 GMT

It appears as though Databricks now formats SQL View definitions when showing them in the Catalog. Our solution is based on views, and we have comment tags in those views. We format these views so that it is easy for us to find and update parts of the view based on these tags. When this generic formatting is applied, it breaks this process for us.

It looks like Databricks is storing the view correctly, as I get the view in the correct format when SHOW CREATE TABLE <view_name> is returned. But in Catalog, where we have trained people to get the view definition because you can easily copy it, the format is applied.

Has anyone found a way to turn this off?

Example - how it should be formatted:

What the generic formatted in Catalog does:

Re: SQL View Formatting in Catalog - Can you turn it off?

Walter_C — Mon, 04 Nov 2024 22:55:54 GMT

Can you please provide me with the exact steps you are following to get this sample from the Catalog page?

Re: SQL View Formatting in Catalog - Can you turn it off?

SethParker — Tue, 05 Nov 2024 14:17:49 GMT

Hello Walter,

Thank you for your time. In Databricks, I navigate to the Catalog tab, then to the view I want to look at. Then on the Overview tab, in the Definition section we copy the view definition out. This used to hold whatever format we used when we created the view. Now, formatting has been applied to the front end.

We can get the unformatted view with SHOW CREATE TABLE, but that adds the list of fields at the top, which we do not want either.

Example - how I created the view:

How it is shown in Catalog:

Thanks,
Seth

Re: SQL View Formatting in Catalog - Can you turn it off?

Walter_C — Tue, 05 Nov 2024 14:35:52 GMT

Got it, I was able to reproduce the same internally, I will check with our teams to confirm if there is anything that can be done to change this behavior but seems to be expected.

Re: SQL View Formatting in Catalog - Can you turn it off?

SethParker — Tue, 05 Nov 2024 15:31:00 GMT

Thank you again for looking into it! I agree that this is likely expected behavior. I just wish there was a way to turn it off. I love nearly everything about Databricks, but Views could use quite a bit of work.

Re: SQL View Formatting in Catalog - Can you turn it off?

Walter_C — Tue, 05 Nov 2024 16:16:16 GMT

I just confirmed with our internal teams that as of now there is no option to have this turned off. This will require a feature request to be submitted which you can do by following steps in https://docs.databricks.com/en/resources/ideas.html#create-an-idea-in-the-ideas-portal

Re: SQL View Formatting in Catalog - Can you turn it off?

SethParker — Wed, 06 Nov 2024 15:54:42 GMT

Thank you! I will submit that request.

In case anyone else stumbles upon this post, here is a function you can add that will return the view definition from information_schema, unformatted with an ALTER statement at the top:

DROP FUNCTION IF EXISTS <catalog>.<schema>.unformatedView; CREATE OR REPLACE FUNCTION <catalog>.<schema>.unformatedView(catalogName STRING, schemaName STRING, viewName STRING) RETURNS TABLE(viewDef STRING) RETURN SELECT CONCAT("ALTER VIEW ", catalogName, ".", schemaName, ".", viewName, " AS ", view_definition) as viewDef FROM system.information_schema.views WHERE table_catalog = catalogName and table_schema = schemaName and table_name = viewName;