Unable to add column comment in Materialized View (MV)

[leungi]

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
)

[raphaelblg]

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.

[leungi]

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

[raphaelblg]

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

[leungi]

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
)

[raphaelblg · Accepted solution]

@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 

[leungi]

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.