如何用dbt external_table包为Redshift Spectrum添加分区及报错排查

报错信息 #

08:19:05  Postgres adapter: Rolling back transaction.
08:19:05  On macro_stage_external_sources: ROLLBACK
08:19:05  On macro_stage_external_sources: Close
uncaught python exception
'dict' object has no attribute 'resource_type'

原配置文件 #

version: 2

sources:
  - name: NAME
    schema: schema.NAME
    description: External schema from Amazon S3. Data catalog stored on AWS Glue.
    loader: s3
    tables:
      - name: TABLE_NAME
        description: External table from Amazon S3.
        external:
          location: "s3://S3_file_path/dt={{var('date', modules.datetime.date.today())}}" ## Remove the last "/" from s3 path
          partitions:
            - name: partition_date
              data_type: date
              vals:
                macro: dbt.dates_in_range
                args:
                  start_date_str: '2023-03-01'
                  end_date_str: '{{modules.datetime.date.today().strftime("%Y-%m-%d")}}'
                  in_fmt: "%Y-%m-%d"
                  out_fmt: "%Y-%m-%d"
              path_macro: dbt_external_tables.value_only
        columns:
          - name: 'col_1'
            data_type: varchar(255)
          - name: 'col_2'
            data_type: varchar(255)
            description: 'col_2'

修复步骤 #

1. 版本兼容性检查 #

确保dbt-redshift与dbt-external-tables包版本匹配：

• 若使用dbt-core >=1.0.0，需搭配dbt-external-tables >=0.8.0
• 执行dbt deps更新依赖，保证版本兼容

2. 修正S3路径配置 #

原配置中location指向带分区的子路径，会导致分区识别失败，应改为指向分区的父路径：

location: "s3://S3_file_path/"

3. 调整分区配置格式 #

原vals的字典写法不符合dbt-external-tables要求，需直接调用宏；同时分区字段名建议与路径中的dt保持一致，便于Spectrum自动识别：

partitions:
  - name: dt
    data_type: date
    vals: "{{ dbt.dates_in_range('2023-03-01', modules.datetime.date.today().strftime('%Y-%m-%d'), '%Y-%m-%d', '%Y-%m-%d') }}"
    path_macro: dbt_external_tables.value_only

若坚持使用partition_date作为分区名，需修改path_macro映射路径中的dt值：

partitions:
  - name: partition_date
    data_type: date
    vals: "{{ dbt.dates_in_range('2023-03-01', modules.datetime.date.today().strftime('%Y-%m-%d'), '%Y-%m-%d', '%Y-%m-%d') }}"
    path_macro: "lambda x: f'dt={x}'"

4. 完整修正后的配置示例 #

version: 2

sources:
  - name: NAME
    schema: schema.NAME
    description: External schema from Amazon S3. Data catalog stored on AWS Glue.
    loader: s3
    tables:
      - name: TABLE_NAME
        description: External table from Amazon S3.
        external:
          location: "s3://S3_file_path/"
          partitions:
            - name: dt
              data_type: date
              vals: "{{ dbt.dates_in_range('2023-03-01', modules.datetime.date.today().strftime('%Y-%m-%d'), '%Y-%m-%d', '%Y-%m-%d') }}"
              path_macro: dbt_external_tables.value_only
        columns:
          - name: 'col_1'
            data_type: varchar(255)
          - name: 'col_2'
            data_type: varchar(255)
            description: 'col_2'

内容的提问来源于stack exchange，提问作者brunondjoyer