topic Re: Unable to add column comment in Materialized View (MV) in Data Engineering

Unable to add column comment in Materialized View (MV)

leungi — Fri, 10 May 2024 21:29:57 GMT

The following doc suggests the ability to add column comments during MV creation via the `column list` parameter.

Thus, the SQL code below is expected to generate a table where the columns `col_1` and `col_2` are commented; however, this is not the case.

Note that the underlying table for the MV, `my_streaming_table`, is a Streaming Table.

%sql USE CATALOG my_catalog; USE SCHEMA my_schema; DROP MATERIALIZED VIEW IF EXISTS my_mv; CREATE MATERIALIZED VIEW my_mv (col_1 COMMENT 'Column 1', col_2 COMMENT 'Column 2') COMMENT 'MV of a streaming table.' AS ( SELECT col_1 , col_2 FROM my_streaming_table )

Re: Unable to add column comment in Materialized View (MV)

raphaelblg — Wed, 22 May 2024 00:31:32 GMT

Hello @leungi ,

I've reproduced the issue and it appears that specifying the data type of the column is necessary for the statement to work. Here's an example:

CREATE MATERIALIZED VIEW my_mv (col_1 STRING COMMENT 'Column 1', col_2 INT COMMENT 'Column 2')

I will consult with Databricks engineering to determine if this is the expected behavior or if there is potential for enhancement.

Re: Unable to add column comment in Materialized View (MV)

leungi — Fri, 31 May 2024 15:34:48 GMT

@raphaelblg thanks for the reply.

I tried the suggested edits, but came up with the following error:

[DLT_VIEW_SCHEMA_WITH_TYPE_NOT_SUPPORTED] MATERIALIZED VIEW schemas with a specified type are supported only in a Delta Live Tables pipeline. SQLSTATE: 56038; line 1 pos 0

The docs for DLT to create materialized views doesn't indicate a way to add column comment as you noted.

Re: Unable to add column comment in Materialized View (MV)

raphaelblg — Fri, 31 May 2024 16:02:07 GMT

@leungi you've shared the python language reference. This is the SQL Reference from where I've based my example.

Re: Unable to add column comment in Materialized View (MV)

leungi — Fri, 31 May 2024 16:06:06 GMT

Noted.

I tried the following in both SQL Editor and Notebook and got the same outcome:

%sql USE CATALOG my_catalog; USE SCHEMA my_schema; DROP MATERIALIZED VIEW IF EXISTS my_mv; CREATE MATERIALIZED VIEW my_mv (col_1 STRING COMMENT 'Column 1', col_2 STRING COMMENT 'Column 2') COMMENT 'MV of a streaming table.' AS ( SELECT col_1 , col_2 FROM my_streaming_table )

Re: Unable to add column comment in Materialized View (MV)

raphaelblg — Fri, 31 May 2024 16:09:01 GMT

@leungi that's because:

"All materialized views are backed by a DLT pipeline. You can refresh materialized views manually, on a schedule, or by scheduling the DLT pipeline in which they’re contained."

At https://docs.databricks.com/en/sql/language-manual/sql-ref-syntax-ddl-create-materialized-view.html

In order to create Materialized Views, you need to use Delta Live Tables

Re: Unable to add column comment in Materialized View (MV)

leungi — Fri, 31 May 2024 16:41:30 GMT

Managed to get it working per your guidance 😀

For future me:

1. Create a SQL notebook.

CREATE OR REFRESH LIVE TABLE my_dlt_mv (col_1 STRING COMMENT 'column_1', col_2 STRING COMMENT 'column_2') COMMENT 'MV of a streaming table.' AS ( SELECT col_1 , col_2 FROM catalog.schema.streaming_table )

2. Create a pipeline for the notebook.