DBT源配置：解决以数字开头的标识符引用问题

本文探讨并解决了dbt中源表标识符以数字开头时引发的sql编译错误。通过配置`quoting.identifier: true`，dbt能够自动为这些特殊标识符添加引号，确保模型编译和数据抽取顺利进行，从而避免因数据库语法错误导致的项目中断。

在数据建模和转换过程中，DBT（data build tool）通过定义源（sources）来引用外部数据库中的原始数据表。然而，当这些源表的实际标识符（identifier）以数字开头时，可能会遇到SQL编译错误，尤其是在诸如Snowflake等严格遵守SQL命名规范的数据库系统中。

问题描述

考虑以下DBT源定义和模型引用场景：

源定义 (_sources.yml) 示例:

  - name: emspdb_archive
    database: lake
    schema: emspdb_archiveschema
    tables:
      - name: s_2020_09_history_logs
        identifier: "2020_09_history_logs" # 实际数据库表名为 2020_09_history_logs

DBT模型引用 (.sql) 示例:

with unioned_archived_history_logs as (
    select * from {{ source('emspdb_archive', 's_2020_09_history_logs') }}
)
-- ... 后续逻辑

在这种配置下，如果实际的数据库表名 2020_09_history_logs 以数字开头，DBT在生成SQL查询时，可能会直接将其作为未引用的对象名传递给数据库。例如，在Snowflake中，未引用的标识符不能以数字开头。这将导致类似于以下的SQL编译错误：

Database Error 001003 (42000): SQL compilation error: syntax error line 4 at position 43 unexpected '.2020'.

这个错误表明数据库解析器无法识别以数字开头的未引用标识符，将其视为语法错误。

解决方案

解决此问题的核心在于确保DBT在生成SQL查询时，能够正确地引用（即用双引号包裹）那些以数字开头或包含特殊字符的数据库标识符。DBT为此提供了quoting配置选项。

通过在_sources.yml文件中为受影响的表添加quoting.identifier: true配置，可以强制DBT在生成SQL时为该标识符添加双引号。

修正后的 _sources.yml 示例:

Eva Design System

基于深度学习的色彩生成器

86

查看详情

  - name: emspdb_archive
    database: lake
    schema: emspdb_archiveschema
    tables:
      - name: s_2020_09_history_logs
        identifier: "2020_09_history_logs"
        quoting:
          identifier: true # 明确指示DBT引用此标识符

工作原理:

当quoting.identifier设置为true时，DBT在内部构建SQL查询时，会确保将identifier字段的值用双引号包裹起来。例如，对于上述配置，DBT生成的SQL片段将不再是 ... from lake.emspdb_archiveschema.2020_09_history_logs，而是 ... from lake.emspdb_archiveschema."2020_09_history_logs"。

这样，数据库（如Snowflake）就能正确地将其识别为一个有效的、被引用的对象名，从而避免了SQL编译错误。

注意事项与最佳实践

1. 何时使用 quoting.identifier: true:

   • 当数据库表名或视图名以数字开头时。
   • 当数据库表名或视图名包含特殊字符（如空格、连字符等）时。
   • 当数据库表名或视图名是数据库的保留关键字时（虽然不常见，但某些情况下可能发生）。
   • 当数据库系统对未引用标识符有严格的命名限制时。

2. 理解 identifier 和 name:

   • name: 这是DBT内部用来引用源的逻辑名称，例如在 {{ source('emspdb_archive', 's_2020_09_history_logs') }} 中使用的 's_2020_09_history_logs'。DBT会根据这个逻辑名称生成一个规范化的内部标识符。
   • identifier: 这是实际数据库中表的物理名称。DBT在生成SQL时会使用这个值。当identifier与DBT自动生成的规范化名称不同时，需要明确指定。
   • 即使identifier被指定为字符串，例如identifier: "2020_09_history_logs"，DBT默认情况下也不会自动引用它，除非显式设置quoting.identifier: true。

3. 引用粒度: quoting配置可以应用于整个源（source级别）或单个表（table级别）。如果一个源下的所有表都需要引用标识符，可以在源级别进行配置。

     - name: my_source
       database: my_db
       schema: my_schema
       quoting:
         identifier: true # 对此源下的所有表标识符生效
       tables:
         - name: table_one
         - name: 2nd_table # 即使没有单独配置，也会被引用

   登录后复制

4. 官方文档参考: 建议查阅DBT官方关于资源属性和引用的文档，以获取最全面和最新的信息。

总结

正确处理数据库标识符的引用是DBT项目成功的关键一环。当遇到以数字开头或其他特殊字符的数据库表名时，通过在_sources.yml中为受影响的表配置quoting.identifier: true，可以有效地解决SQL编译错误，确保DBT模型能够顺利地从外部源抽取数据。这不仅是解决特定问题的有效方法，也是DBT项目配置中的一项重要最佳实践。

以上就是DBT源配置：解决以数字开头的标识符引用问题的详细内容，更多请关注php中文网其它相关文章！