cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results for 
Search instead for 
Did you mean: 

Unable to add column comment in Materialized View (MV)

leungi
New Contributor III

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
)

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

@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 

Best regards,

Raphael Balogo
Sr. Technical Solutions Engineer
Databricks

View solution in original post

6 REPLIES 6

raphaelblg
Contributor III
Contributor III

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.

 

Best regards,

Raphael Balogo
Sr. Technical Solutions Engineer
Databricks

leungi
New Contributor III

@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
Contributor III
Contributor III

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

Best regards,

Raphael Balogo
Sr. Technical Solutions Engineer
Databricks

leungi
New Contributor III

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
)

 

 

@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 

Best regards,

Raphael Balogo
Sr. Technical Solutions Engineer
Databricks

leungi
New Contributor III

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.

Join 100K+ Data Experts: Register Now & Grow with Us!

Excited to expand your horizons with us? Click here to Register and begin your journey to success!

Already a member? Login and join your local regional user group! If there isn’t one near you, fill out this form and we’ll create one for you to join!