Persist Column level comments when creating views

[shuaahvee]

Describe the feature

The persist_docs config works for tables by materializing a model as a table and then running ALTER TABLE statements to update the comment on each column. This method does not work for views because ALTER VIEW doesn't support adding comments. Instead, comments need to be defined when running the CREATE OR REPLACE VIEW statement. There should be an option to handle column level comments for models materialized as views

Describe alternatives you've considered

I tried creating a macro that adds the necessary comment clause and put it at the top of my model but the SQL gets inserted after the AS in CREATE OR REPLACE VIEW AS

Who will this benefit?

This will benefit anyone using column level comments on views in databricks

Are you interested in contributing this feature?

Yes! I overwrote the adapters.sql macro to do this. I'll create and link to a PR!

[shuaahvee]

{% macro column_comment_clause() %}
{%- set raw_persist_docs = config.get('persist_docs', {}) -%}

{%- if raw_persist_docs is mapping -%}
    {%- set raw_relation_column_comments = raw_persist_docs.get('columns', false) -%}
    {%- if raw_relation_column_comments -%}
    (
    {% for col in model.columns.values() %}
    {{col["name"] + ' comment ' + "'" + col["description"] | replace("'", "\\'") + "'" }}
    {% if not loop.last %},{% endif %}
    {% endfor %}
    )
    {% endif %}
{%- elif raw_persist_docs -%}
    {{ exceptions.raise_compiler_error("Invalid value provided for 'persist_docs'. Expected dict but got value: " ~ raw_persist_docs) }}
{% endif %}
{%- endmacro -%}


{% macro comment_clause() %}
{%- set raw_persist_docs = config.get('persist_docs', {}) -%}

{%- if raw_persist_docs is mapping -%}
    {%- set raw_relation = raw_persist_docs.get('relation', false) -%}
    {%- if raw_relation -%}
    comment '{{ model.description | replace("'", "\\'") }}'
    {% endif %}
{%- elif raw_persist_docs -%}
    {{ exceptions.raise_compiler_error("Invalid value provided for 'persist_docs'. Expected dict but got value: " ~ raw_persist_docs) }}
{% endif %}
{%- endmacro -%}

{% macro databricks__create_view_as(relation, sql) -%}
create or replace view {{ relation }}
{{ column_comment_clause() }}
{{ comment_clause() }}
as
    {{ sql }}
{% endmacro %}

[allisonwang-db]

Hi @shuaahvee thanks for opening the issue. I think it would be better to add this feature in dbt-spark. Feel free to create an issue in the dbt-spark repository.

[shuaahvee]

Thanks! Will do!

[anairax28]

@shuaahvee Thank you for the macros