Repository: TobikoData/sqlmesh (python)

Issue:
SQLMesh fails to create a materialized views in BigQuery when registering comments
When using a materialized view that has comments, its creation succeeds in BQ, but later fails with the following trace:

```
2024-09-23 16:34:22,095 - MainThread - sqlmesh.core.context - ERROR - Apply Failure: Traceback (most recent call last):
  File ".venv/lib/python3.11/site-packages/sqlmesh/utils/concurrency.py", line 69, in _process_node
    self.fn(node)
  File ".venv/lib/python3.11/site-packages/sqlmesh/utils/concurrency.py", line 165, in <lambda>
    lambda s_id: fn(snapshots_by_id[s_id]),
                 ^^^^^^^^^^^^^^^^^^^^^^^^^
  File ".venv/lib/python3.11/site-packages/sqlmesh/core/snapshot/evaluator.py", line 300, in <lambda>
    lambda s: self._create_snapshot(
              ^^^^^^^^^^^^^^^^^^^^^^
  File ".venv/lib/python3.11/site-packages/sqlmesh/core/snapshot/evaluator.py", line 708, in _create_snapshot
    evaluation_strategy.create(
  File ".venv/lib/python3.11/site-packages/sqlmesh/core/snapshot/evaluator.py", line 1604, in create
    self.adapter.create_view(
  File ".venv/lib/python3.11/site-packages/sqlmesh/core/engine_adapter/shared.py", line 290, in internal_wrapper
    return func(*list_args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File ".venv/lib/python3.11/site-packages/sqlmesh/core/engine_adapter/base.py", line 1029, in create_view
    self._create_column_comments(view_name, column_descriptions, "VIEW")
  File ".venv/lib/python3.11/site-packages/sqlmesh/core/engine_adapter/bigquery.py", line 544, in _create_column_comments
    self._db_call(self.client.update_table, table=table, fields=["schema"])
  File ".venv/lib/python3.11/site-packages/sqlmesh/core/engine_adapter/bigquery.py", line 711, in _db_call
    return func(
           ^^^^^
  File ".venv/lib/python3.11/site-packages/google/cloud/bigquery/client.py", line 1431, in update_table
    api_response = self._call_api(
                   ^^^^^^^^^^^^^^^
  File ".venv/lib/python3.11/site-packages/google/cloud/bigquery/client.py", line 833, in _call_api
    return call()
           ^^^^^^
  File ".venv/lib/python3.11/site-packages/google/api_core/retry/retry_unary.py", line 293, in retry_wrapped_func
    return retry_target(
           ^^^^^^^^^^^^^
  File ".venv/lib/python3.11/site-packages/google/api_core/retry/retry_unary.py", line 153, in retry_target
    _retry_error_helper(
  File ".venv/lib/python3.11/site-packages/google/api_core/retry/retry_base.py", line 212, in _retry_error_helper
    raise final_exc from source_exc
  File ".venv/lib/python3.11/site-packages/google/api_core/retry/retry_unary.py", line 144, in retry_target
    result = target()
             ^^^^^^^^
  File ".venv/lib/python3.11/site-packages/google/cloud/_http/__init__.py", line 494, in api_request
    raise exceptions.from_http_response(response)
google.api_core.exceptions.BadRequest: 400 PATCH https://bigquery.googleapis.com/bigquery/v2/projects/<redacted>/datasets/<redacted>/tables/<redacted>?prettyPrint=false: Schema update for materialized views is not supported.
```

Using `v0.123.3`

Pull request:
Fix(bigquery): materialized views do not support column comments
Closes #3164 

Docs: https://cloud.google.com/bigquery/docs/materialized-views-intro#limitations

Patch:
diff --git a/sqlmesh/core/engine_adapter/base.py b/sqlmesh/core/engine_adapter/base.py
index e2be37ad..71b379e1 100644
--- a/sqlmesh/core/engine_adapter/base.py
+++ b/sqlmesh/core/engine_adapter/base.py
@@ -1026,7 +1026,7 @@ class EngineAdapter:
             )
             and self.comments_enabled
         ):
-            self._create_column_comments(view_name, column_descriptions, "VIEW")
+            self._create_column_comments(view_name, column_descriptions, "VIEW", materialized)
 
     @set_catalog()
     def create_schema(
@@ -2293,6 +2293,7 @@ class EngineAdapter:
         table_name: TableName,
         column_comments: t.Dict[str, str],
         table_kind: str = "TABLE",
+        materialized_view: bool = False,
     ) -> None:
         table = exp.to_table(table_name)
 
diff --git a/sqlmesh/core/engine_adapter/bigquery.py b/sqlmesh/core/engine_adapter/bigquery.py
index cdad08f1..08f3cc2b 100644
--- a/sqlmesh/core/engine_adapter/bigquery.py
+++ b/sqlmesh/core/engine_adapter/bigquery.py
@@ -547,31 +547,33 @@ class BigQueryEngineAdapter(InsertOverwriteWithMergeMixin, ClusteredByMixin):
         table_name: TableName,
         column_comments: t.Dict[str, str],
         table_kind: str = "TABLE",
+        materialized_view: bool = False,
     ) -> None:
-        table = self._get_table(table_name)
-
-        # convert Table object to dict
-        table_def = table.to_api_repr()
-
-        # set the column descriptions
-        for i in range(len(table_def["schema"]["fields"])):
-            comment = column_comments.get(table_def["schema"]["fields"][i]["name"], None)
-            if comment:
-                table_def["schema"]["fields"][i]["description"] = self._truncate_comment(
-                    comment, self.MAX_COLUMN_COMMENT_LENGTH
-                )
+        if not table_kind == "VIEW" and materialized_view:
+            table = self._get_table(table_name)
+
+            # convert Table object to dict
+            table_def = table.to_api_repr()
+
+            # set the column descriptions
+            for i in range(len(table_def["schema"]["fields"])):
+                comment = column_comments.get(table_def["schema"]["fields"][i]["name"], None)
+                if comment:
+                    table_def["schema"]["fields"][i]["description"] = self._truncate_comment(
+                        comment, self.MAX_COLUMN_COMMENT_LENGTH
+                    )
 
-        # An "etag" is BQ versioning metadata that changes when an object is updated/modified. `update_table`
-        # compares the etags of the table object passed to it and the remote table, erroring if the etags
-        # don't match. We set the local etag to None to avoid this check.
-        table_def["etag"] = None
+            # An "etag" is BQ versioning metadata that changes when an object is updated/modified. `update_table`
+            # compares the etags of the table object passed to it and the remote table, erroring if the etags
+            # don't match. We set the local etag to None to avoid this check.
+            table_def["etag"] = None
 
-        # convert dict back to a Table object
-        table = table.from_api_repr(table_def)
+            # convert dict back to a Table object
+            table = table.from_api_repr(table_def)
 
-        # update table schema
-        logger.info(f"Registering column comments for table {table_name}")
-        self._db_call(self.client.update_table, table=table, fields=["schema"])
+            # update table schema
+            logger.info(f"Registering column comments for table {table_name}")
+            self._db_call(self.client.update_table, table=table, fields=["schema"])
 
     def _build_description_property_exp(
         self,
diff --git a/sqlmesh/core/engine_adapter/mysql.py b/sqlmesh/core/engine_adapter/mysql.py
index ad244b79..7f208a32 100644
--- a/sqlmesh/core/engine_adapter/mysql.py
+++ b/sqlmesh/core/engine_adapter/mysql.py
@@ -130,6 +130,7 @@ class MySQLEngineAdapter(
         table_name: TableName,
         column_comments: t.Dict[str, str],
         table_kind: str = "TABLE",
+        materialized_view: bool = False,
     ) -> None:
         table = exp.to_table(table_name)
         table_sql = table.sql(dialect=self.dialect, identify=True)
diff --git a/sqlmesh/core/engine_adapter/snowflake.py b/sqlmesh/core/engine_adapter/snowflake.py
index d7f2bdb8..047c310a 100644
--- a/sqlmesh/core/engine_adapter/snowflake.py
+++ b/sqlmesh/core/engine_adapter/snowflake.py
@@ -394,6 +394,7 @@ class SnowflakeEngineAdapter(GetCurrentCatalogFromFunctionMixin, ClusteredByMixi
         table_name: TableName,
         column_comments: t.Dict[str, str],
         table_kind: str = "TABLE",
+        materialized_view: bool = False,
     ) -> None:
         """
         Reference: https://docs.snowflake.com/en/sql-reference/sql/alter-table-column#syntax


Test patch:
diff --git a/tests/core/engine_adapter/test_bigquery.py b/tests/core/engine_adapter/test_bigquery.py
index c197ad08..2ef36578 100644
--- a/tests/core/engine_adapter/test_bigquery.py
+++ b/tests/core/engine_adapter/test_bigquery.py
@@ -623,24 +623,39 @@ def test_comments(make_mocked_engine_adapter: t.Callable, mocker: MockerFixture)
         column_descriptions={"a": long_column_comment},
     )
 
+    adapter._create_table_comment(
+        "test_table",
+        long_table_comment,
+    )
+
     adapter.create_view(
         "test_table",
         parse_one("SELECT a, b FROM source_table"),
         table_description=long_table_comment,
     )
 
-    adapter._create_table_comment(
+    # Bigquery doesn't support column comments for materialized views
+    db_call_mock = mocker.patch(
+        "sqlmesh.core.engine_adapter.bigquery.BigQueryEngineAdapter._db_call"
+    )
+
+    adapter.create_view(
         "test_table",
-        long_table_comment,
+        parse_one("SELECT a, b FROM source_table"),
+        table_description=long_table_comment,
+        column_descriptions={"a": long_column_comment},
+        materialized=True,
     )
+    assert not db_call_mock.called
 
     sql_calls = _to_sql_calls(execute_mock)
     assert sql_calls == [
         f"CREATE TABLE IF NOT EXISTS `test_table` (`a` INT64 OPTIONS (description='{truncated_column_comment}'), `b` INT64) OPTIONS (description='{truncated_table_comment}')",
         "CREATE TABLE IF NOT EXISTS `test_table` (`a` INT64 OPTIONS (description='\\\\'), `b` INT64) OPTIONS (description='\\\\')",
         f"CREATE TABLE IF NOT EXISTS `test_table` (`a` INT64 OPTIONS (description='{truncated_column_comment}'), `b` INT64) OPTIONS (description='{truncated_table_comment}') AS SELECT CAST(`a` AS INT64) AS `a`, CAST(`b` AS INT64) AS `b` FROM (SELECT `a`, `b` FROM `source_table`) AS `_subquery`",
-        f"CREATE OR REPLACE VIEW `test_table` OPTIONS (description='{truncated_table_comment}') AS SELECT `a`, `b` FROM `source_table`",
         f"ALTER TABLE `test_table` SET OPTIONS(description = '{truncated_table_comment}')",
+        f"CREATE OR REPLACE VIEW `test_table` OPTIONS (description='{truncated_table_comment}') AS SELECT `a`, `b` FROM `source_table`",
+        f"CREATE OR REPLACE MATERIALIZED VIEW `test_table` OPTIONS (description='{truncated_table_comment}') AS SELECT `a`, `b` FROM `source_table`",
     ]