执行dbt run后DBT未在PostgreSQL中创建表的技术求助

配置文件内容 #

schema.yml #

version: 2

models:
  - name: my_first_dbt_model
    description: "A starter dbt model"
    columns:
      - name: id
        description: "The primary key for this table"
        data_tests:
          - unique
          - not_null

  - name: my_second_dbt_model
    description: "A starter dbt model"
    columns:
      - name: id
        description: "The primary key for this table"
        data_tests:
          - unique
          - not_null

my_first_dbt_model.sql #

{{ config(materialized='table') }}

with source_data as (

    select 1 as id
    union all
    select null as id

)

select *
from source_data
where id is not null

my_second_dbt_model.sql #

select *
from {{ ref('my_first_dbt_model') }}
where id = 1

dbt run 执行输出 #

22:21:18  1 of 6 START sql table model my_schemas.my_first_dbt_model ................ [RUN]
22:21:18  1 of 6 OK created sql table model my_schemas.my_first_dbt_model ........... [SELECT 1 in 0.06s]
22:21:18  2 of 6 START test not_null_my_first_dbt_model_id ............................... [RUN]
22:21:18  3 of 6 START test unique_my_first_dbt_model_id ................................. [RUN]
22:21:18  3 of 6 PASS unique_my_first_dbt_model_id ....................................... [PASS in 0.04s]
22:21:18  2 of 6 PASS not_null_my_first_dbt_model_id ..................................... [PASS in 0.04s]
22:21:18  4 of 6 START sql table model my_schemas.my_second_dbt_model ............... [RUN]
22:21:18  4 of 6 OK created sql table model my_schemas.my_second_dbt_model .......... [SELECT 1 in 0.02s]
22:21:18  5 of 6 START test not_null_my_second_dbt_model_id .............................. [RUN]
22:21:18  6 of 6 START test unique_my_second_dbt_model_id ................................ [RUN]
22:21:18  5 of 6 PASS not_null_my_second_dbt_model_id .................................... [PASS in 0.03s]
22:21:18  6 of 6 PASS unique_my_second_dbt_model_id ...................................... [PASS in 0.03s]
22:21:18
22:21:18  Finished running 2 table models, 4 data tests in 0 hours 0 minutes and 0.23 seconds (0.23s).
22:21:18
22:21:18  Completed successfully
22:21:18
22:21:18  Done. PASS=6 WARN=0 ERROR=0 SKIP=0 TOTAL=6

数据库查询结果 #

my_database=# \d+
Did not find any relations.

核心疑问 #

为何日志显示表已创建成功，但数据库中却不存在对应的表？

排查与解决办法 #

• 检查schema匹配性
  日志明确显示表创建在my_schemas下，而psql默认可能使用public schema。执行\dn查看所有schema，切换到目标schema：

  SET search_path TO my_schemas;
  

  再执行\d+即可查看创建的表。

• 确认数据库一致性
  检查dbt项目的profiles.yml，确认database参数对应的库名，和你psql登录的my_database是否一致。如果不一致，切换到正确数据库：

  \c 目标数据库名称;
  

  再重复schema检查步骤。

• 验证事务状态
  极少数情况下，dbt的事务可能未正常提交。执行以下命令查看未提交事务：

  SELECT * FROM pg_stat_activity WHERE state = 'idle in transaction';
  

  若存在未提交事务，可手动提交或重新执行dbt run确保事务完成。

• 检查用户权限
  若登录psql的用户没有my_schemas的访问权限，也会看不到表。执行以下命令赋予权限：

  GRANT USAGE ON SCHEMA my_schemas TO 你的用户名;
  GRANT SELECT ON ALL TABLES IN SCHEMA my_schemas TO 你的用户名;
  

  之后重新查询表即可。

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