macros - 错误地使用 dbt_utils.union_relations 但不知道如何/为什么?
问题描述
所以我是一个新的 DBT 用户,超级酷的东西,但我遇到了 union_relations 宏的问题。我正在为这个函数提供关系,但编译/运行的查询没有从关系中找到任何列。
这是我正在运行的代码:
{{dbt_utils.union_relations(relations=[ref('transform_hist_ca_map_stu_obj_assessment'), ref('transform_hist_sc_map_stu_obj_assessment')])}}
)
select *
from conformed_obj_assessment
where student_assessment_identifier is not null
and assessment_identifier is not null
and identification_code is not null
and student_unique_id is not null
and performance_level is not null
这是我收到的错误:
syntax error at or near "from" LINE 1706: from __dbt__CTE__transform_hist_ca_map_stu_obj_a... ^ compiled SQL at target/run/rally_dw/conformed/conformed_student_objective_assessment.sql
基本上第一列是 DBT 生成的列,之后应该有来自关系的列,但由于某种原因,这些列没有被拉入。我想知道这是否是因为我想从中拉出关系目前是短暂的,所以没有实现,所以我想知道这是否会导致问题。这是编译后的 SQL,CTE 返回数据,但由于某种原因,它没有被拉入最后一个 CTE。
create table "dashboarding"."dev_em_conformed"."conformed_student_objective_assessment__dbt_tmp"
as (
with __dbt__CTE__historical_ca_map_stu_obj_assessment as (
with hist_ca_map_stu_obj_assess as (
select * from "dashboarding"."raw_ea"."historical_ca_map_student_obj_assessment"
),
cleaned as (
select distinct
source_org,
assessment_id as assessment_identifier,
student_assessment_identifier,
student_unique_id,
performance_levels as performance_level,
scale_score as score,
assessment_id,
to_date(test_date, 'YYYY-MM-DD') as test_date,
identification_code,
null as parent_objective_assessment_name
from hist_ca_map_stu_obj_assess
)
select * from cleaned
), __dbt__CTE__transform_hist_ca_map_stu_obj_assessment as (
with hist_ca_stu_obj_assess as (
select * from __dbt__CTE__historical_ca_map_stu_obj_assessment
),
final as(
select
null as source_org,
student_assessment_identifier,
assessment_id as assessment_identifier,
identification_code as identification_code,
null as school_year,
student_unique_id,
null as student_grade_level,
null as assessment_grade_level,
NULL as administration_date,
null as administration_end_date,
null as objective_assessment_name,
score,
performance_level,
parent_objective_assessment_name,
null as parent_objective_assessment_id
from hist_ca_stu_obj_assess
)
select * from final
), __dbt__CTE__historical_sc_map_stu_obj_assessment as (
with hist_sc_map_soa as (
select * from "dashboarding"."raw_ea"."historical_sc_map_student_obj_assessment"
),
cleaned as (
select distinct
source_org,
assessment_id as assessment_identifier,
student_assessment_identifier,
student_unique_id,
performance_levels as performance_level,
scale_score as score,
assessment_id,
to_date(test_date, 'YYYY-MM-DD') as test_date,
identification_code,
null as parent_objective_assessment_name
from hist_sc_map_soa
)
select * from cleaned
), __dbt__CTE__transform_hist_sc_map_stu_obj_assessment as (
with hist_sc_stu_obj_assess as (
select * from __dbt__CTE__historical_sc_map_stu_obj_assessment
),
final as(
select
null as source_org,
student_assessment_identifier,
assessment_id as assessment_identifier,
identification_code as identification_code,
null as school_year,
student_unique_id,
null as student_grade_level,
null as assessment_grade_level,
NULL as administration_date,
null as administration_end_date,
null as objective_assessment_name,
score,
performance_level,
parent_objective_assessment_name,
null as parent_objective_assessment_id
from hist_sc_stu_obj_assess
)
select * from final
), conformed_obj_assessment as(
(
select
cast('__dbt__CTE__transform_hist_ca_map_stu_obj_assessment' as
varchar
) as _dbt_source_relation,
---NO MORE COLUMNS???
from __dbt__CTE__transform_hist_ca_map_stu_obj_assessment
)
union all
(
select
cast('__dbt__CTE__transform_hist_sc_map_stu_obj_assessment' as
varchar
) as _dbt_source_relation,
---NO MORE COLUMNS??
from __dbt__CTE__transform_hist_sc_map_stu_obj_assessment
)
)
select *
from conformed_obj_assessment
where student_assessment_identifier is not null
and assessment_identifier is not null
and identification_code is not null
and student_unique_id is not null
and performance_level is not null
);
任何想法将不胜感激,谢谢!
解决方案
该union_relations
宏依赖于了解您的关系(表/视图)中的哪些列,这些列存储在信息模式中。由于这个模型是短暂的,信息模式中没有任何记录,这就是为什么会有这样的 SQL:
select
cast('__dbt__CTE__transform_hist_ca_map_stu_obj_assessment' as
varchar
) as _dbt_source_relation,
from __dbt__CTE__transform_hist_ca_map_stu_obj_assessment
我注意到您使用的是稍旧版本的 dbt-utils — 虽然我们尚未修复此问题,但我们改进了处理此问题的方式(在 v0.5.0 中发布)。
较新版本的 dbt-utils 将有助于告诉您以下信息:
Compilation Error in model test_ephemeral (models/test_ephemeral.sql)
The `union_relations` macro cannot be used with ephemeral models, as it relies on the information schema.
`__dbt__CTE__my_ephemeral` is an ephemeral model. Consider making is a view or table instead.` is an ephemeral model. Consider making is a view or table instead.
因此,正如(新)错误消息所暗示的那样——解决这个问题的唯一方法是让您的上游模型成为视图或表格。
推荐阅读
- php - 需要帮助使其与 PHP 7.1 一起使用吗?
- mysql - 如何用 created_at 修复?
- reactjs - 如何通过 fetchPolicy='cache-and-network' 直接使用 apollo-client API 进行查询?
- three.js - three.js 中的生活就像纸一样
- ruby-on-rails - :id=> nil 缺少必需的键 :id
- php - 显示 PHP 表单,但提交时除了清除自身之外不执行任何操作
- vbscript - 索引到 Byte() 变量时的 VBScript 类型不匹配
- python - Tkinter如何使用按钮隐藏当前窗口并打开新窗口
- php - 使用 PHP 将字符串的特定部分的逗号转换为分号
- javascript - 如何在一个模板中识别多个表单?