DBT JJJA中是否有所有*有效*数据库和amp；模式组合的变量列表？ [英] Is there a variable list of all *valid* databases & schemas combinations in dbt jinja?

问题描述

对宏(grant_select_on_schemas.sql)的this example进行变体，以便在DBT运行后设置雪花实例上的授权。我的问题是，我继承了一个非标准DBT构建配置，其中包括一些静态定义的非目标模型位置。

示例：

snowflake-instance
    |
    |> raw_db
        |> elt_schema_1
        |> elt_schema_2
        |> elt_schema_3
    |> utils_db
        |> calendar_schema_1
    |> staging_db
        |> elt_staging_1
        |> elt_staging_2
        |> elt_staging_3
    |> analytics_db
        |> core_models
        |> mart_1
        |> mart_2

profiles.yml

  target: prod
  outputs:
    prod:
      type: snowflake
      account: my-account.region-1
      role: my-role

      # User/password auth
      user: <user>
      password: <pass>

      database: raw_db
      warehouse: my-warehouse
      schema: PUBLIC
      threads: 2
      client_session_keep_alive: False
      query_tag: my-dbt-local

dbt-project.yml

models:
    my-pro:
        +materialized: table   
        utils:
            +database: UTILS
            +materialized: table
            calendar:
                +schema: calendar_schema_1
        staging:
            +database: staging_db
            +materialized: view
            elt_staging_1:
                +schema: elt_staging_1
            elt_staging_2:
                +schema: elt_staging_2
            elt_staging_3:
                +schema: elt_staging_3

grant_select_on_schemas.sql

-- macros/grants/grant_select_on_schemas.sql

{% macro grant_select_on_schemas(schemas, role) %}
  {% for schema in schemas %}
    {% for role in roles %}
      grant usage on schema {{ schema }} to role {{ role }};
      grant select on all tables in schema {{ schema }} to role {{ role }};
      grant select on all views in schema {{ schema }} to role {{ role }};
      grant select on future tables in schema {{ schema }} to role {{ role }};
      grant select on future views in schema {{ schema }} to role {{ role }};
    {% endfor %}
  {% endfor %}
{% endmacro %}

目前，我遇到了此宏的问题，该宏尝试针对我的配置文件的{{ target.database }}(当前设置为staging_db)上的所有架构运行，因此在尝试执行以下操作时出错：

> Database Error
>   002003 (02000): SQL compilation error:
>   Schema 'staging_db.core_models' does not exist or not authorized.

我错过了什么？

推荐答案

我加入Stackoverflow只是为了回答您的问题，因为我在6-8个多月前也有过同样的问题(我甚至再次确认这不是我在问问题，因为那样会很尴尬)。

签出位于DBT文档深处的database_schemas变量：

https://docs.getdbt.com/reference/dbt-jinja-functions/on-run-end-context#database_schemas

您应该能够按如下方式将其添加：

{% macro grant_select_on_schemas(database_schemas, role) %}
  {% for (database,schema) in database_schemas %}
    {% for role in roles %}
      grant usage on schema {{ database }}.{{ schema }} to role {{ role }};
      grant select on all tables in schema {{ database }}.{{ schema }} to role {{ role }};
      grant select on all views in schema {{ database }}.{{ schema }} to role {{ role }};
      grant select on future tables in schema {{ database }}.{{ schema }} to role {{ role }};
      grant select on future views in schema {{ database }}.{{ schema }} to role {{ role }};
    {% endfor %}
  {% endfor %}
{% endmacro %}

这篇关于DBT JJJA中是否有所有*有效*数据库和amp；模式组合的变量列表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！