如何用dbt在非源数据库生成物化视图？Trino权限受限问题

问题背景 #

作为dbt新手，需求为：

• 从连接Cassandra的Trino读取源数据
• 将所有中间视图/表写入Postgres或ClickHouse等外部数据库
• 当前限制：Trino的Cassandra连接器不支持表重命名操作，且无创建视图权限

现有配置文件 #

profiles.yml

trino:
  target: dev
  outputs:
    dev:
      type: trino
      method: none
      user: admin
      host: trino.XX.adroot
      port: 80
      schema: demo
      catalog: cassandra

postgres:
  target: dev
  outputs:
    dev:
      type: postgres
      host: localhost
      user: postgres
      password: postgres
      port: 5432
      dbname: demo # or database instead of dbname
      schema: public

dbt_project.yml

# Name your project! Project names should contain only lowercase characters
# and underscores. A good package name should reflect your organization's
# name or the intended use of these models
name: 'pmrush_elt'
version: '1.0.0'
config-version: 2

# This setting configures which "profile" dbt uses for this project.
profile: 'trino'

# These configurations specify where dbt should look for different types of files.
# The `model-paths` config, for example, states that models in this project can be
# found in the "models/" directory. You probably won't need to change these!
model-paths: ["models"]
analysis-paths: ["analyses"]
test-paths: ["tests"]
seed-paths: ["seeds"]
macro-paths: ["macros"]
snapshot-paths: ["snapshots"]

target-path: "target"  # directory which will store compiled SQL files
clean-targets:         # directories to be removed by `dbt clean`
  - "target"
  - "dbt_packages"


# Configuring models
# Full documentation: https://docs.getdbt.com/docs/configuring-models

# In this example config, we tell dbt to build all models in the example/
# directory as views. These settings can be overridden in the individual model
# files using the `{{ config(...) }}` macro.
models:
  pmrush_elt:
    # Config indicated by + and applies to all files under models/example/
    staging:
      +materialized: table

源数据库配置（sources.yml）

version: 2
sources:
  - name: demo
    catalog: cassandra
    schema: demo
    tables:
      - name: keyword_data
      - name: serp

执行错误信息 #

15:11  Found 1 model, 0 tests, 0 snapshots, 0 analyses, 316 macros, 0 operations, 0 seed files, 2 sources, 0 exposures, 0 metrics
17:15:11  
17:15:12  Concurrency: 1 threads (target='dev')
17:15:12  
17:15:12  1 of 1 START sql table model demo.stg_keyword_data ....................... [RUN]
17:15:16  1 of 1 ERROR creating sql table model demo.stg_keyword_data .............. [ERROR in 4.87s]
17:15:16  
17:15:16  Finished running 1 table model in 0 hours 0 minutes and 5.11 seconds (5.11s).
17:15:16  
17:15:16  Completed with 1 error and 0 warnings:
17:15:16  
17:15:16  Database Error in model stg_keyword_data (models/staging/demo/stg_keyword_data.sql)
17:15:16    TrinoUserError(type=USER_ERROR, name=NOT_SUPPORTED, message="This connector does not support renaming tables", query_id=20230424_171516_00019_68nw3)
17:15:16    compiled Code at target/run/pmrush_elt/models/staging/demo/stg_keyword_data.sql
17:15:16  
17:15:16  Done. PASS=0 WARN=0 ERROR=1 SKIP=0 TOTAL=1

解决方案 #

核心问题是当前默认目标数据库为Trino，dbt尝试在Trino的Cassandra连接器中创建模型表，但该连接器不支持dbt默认的"先建临时表再重命名"的表创建逻辑。需要配置多数据库目标，让模型写入Postgres，同时从Trino读取源数据。

1. 全局配置模型的目标数据库 #

修改dbt_project.yml中的模型配置，给需要写入Postgres的模型层指定目标数据库和schema：

models:
  pmrush_elt:
    staging:
      +materialized: table
      +database: postgres  # 对应profiles.yml中的postgres配置项
      +schema: public      # Postgres中的目标schema

2. 单模型级别指定目标（更灵活） #

如果需要针对单个模型配置，在模型SQL文件顶部添加config：

{{ config(
    materialized='table',
    database='postgres',
    schema='public'
) }}

select * from {{ source('demo', 'keyword_data') }}

3. 验证编译结果 #

运行dbt compile命令，查看target/run目录下生成的SQL，确认模型是向Postgres写入，而非尝试在Trino的Cassandra目录下创建对象。

4. 特殊场景处理（如需在Trino中处理数据） #

如果必须在Trino内创建临时处理对象，由于Cassandra连接器不支持视图和表重命名，可使用materialized: ephemeral配置，这种方式不会在数据库中创建持久化对象，而是将当前模型的SQL嵌入到下游模型中，适合简单逻辑处理。

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