DBT实现类递归查询

yipiantian

已于 2024-02-18 16:39:07 修改

阅读量382

点赞数 7

文章标签：数据库 etl

于 2024-01-24 21:02:30 首次发布

本文链接：https://blog.csdn.net/yipiantian/article/details/135831023

版权

本文介绍了如何在数据库中创建一个层次结构的维度模型，通过使用SQL和Hive在iceberg数据仓库中实现递归查询，将员工数据组织成多级关系。该模型用于`v_dimxxx_emp`表的查询和扩展，展示了逐步关联上级数据的过程。

摘要由CSDN通过智能技术生成

# model/xxx.yml

version: 2

sources:
  - name: dim_xxxdb
    description: "员工数据源，hive引擎"
    database: hive
    schema: dim_xxxdb
    tables:
      - name: v_dimxxx_emp

# macros/model_macro.sql

{% macro union_all(tbls=[]) %}
    {%- for i in tbls -%}
       {%- if loop.first %}
           select * from {{i}}
       {%- else %}
           union all select * from {{i}}
       {%- endif %}
    {%- endfor %}
{% endmacro %}

{% macro to_int(v) %}
    return int(v)
{% endmacro %}

{% macro add_column(tbl,col,coll_type) %}
    {% set add_col%}
        alter table {{ ref('{{tbl}}')}} add column {{col}} {{coll_type}}
    {% endset %}
    {% do run_query(add_col) %}
{% endmacro %}

{% macro drop_column(tbl,col) %}
    {% set add_col%}
        alter table {{ ref('{{tbl}}')}} drop column {{col}}
    {% endset %}
    {% do run_query(add_col) %}
{% endmacro %}

#dbt_project.yml

......
models:
  hello_world:
    # Config indicated by + and applies to all files under models/example/
    example:
      +materialized: view
vars:
  vlist: [1,2,3,4,5]

# model/dim_xxx.sql

{{
    config(
        materialized = 'table'
    )
}}
{%- set nums = var('vlist') -%}
{%- set cte_names = [] -%}
with
{% for i in nums %}
    {%- set cte_name = 'mid_' ~ i -%}
    {%- set i_next = i - 1 -%}
    {%- set cte_name1 = 'mid_' ~ i_next -%}
    {#{ print("i_next:" ~ i_next )}#}
    {%- do cte_names.append(cte_name) -%}
    {{ cte_name }} AS (
            select
                e.eid as leader_eid, e.userno as leader_userno, e.emp_name as leader_name, e.valid as leader_valid, e.dept as leader_dept, e.pathname as leader_pathname, e.orgcodepath as leader_orgcodepath,
        {# %- if i == 1 % #}
        {%- if loop.first -%}
                l.eid, l.userno, l.emp_name as name, l.valid, l.dept, l.pathname, l.orgcodepath,
                concat(e.userno, '>', l.userno) as path_userno, concat(e.emp_name, '>', l.emp_name) as path_name, 1 as lvl
            from {{ref('v_dimxxx_emp')}} l
            join {{ref('v_dimxxx_emp')}}  e on l.eidleader=e.userno
        {% else -%}
                e.eid, e.userno, e.emp_name as name, e.valid, e.dept, e.pathname, e.orgcodepath,
                concat(l.path_userno, '>', e.userno) as path_userno, concat(l.path_name, '>', e.emp_name) as path_name, l.lvl+1 as lvl
            from {{cte_name1}} l
            join {{ref('v_dimxxx_emp')}}  e on l.userno=e.eidleader
            where not exists (select 1 from {{cte_name1}} t where t.leader_userno=l.leader_userno and t.userno=e.userno)
        {%- endif %}
    )
    {%- if not loop.last -%},{%- endif -%}
{%- endfor %}
{{ union_all(cte_names) }}

# target/model/dim_xxx.sql

with
mid_1 AS (
            select
                e.eid as leader_eid, e.userno as leader_userno, e.emp_name as leader_name, e.valid as leader_valid, e.dept as leader_dept, e.pathname as leader_pathname, e.orgcodepath as leader_orgcodepath,
        l.eid, l.userno, l.emp_name as name, l.valid, l.dept, l.pathname, l.orgcodepath,
                concat(e.userno, '>', l.userno) as path_userno, concat(e.emp_name, '>', l.emp_name) as path_name, 1 as lvl
            from "iceberg"."ice_dw_xxxdb"."dim_xxx_emp" l
            join "iceberg"."ice_dw_xxxdb"."dim_xxx_emp"  e on l.eidleader=e.userno
        
    ),mid_2 AS (
            select
                e.eid as leader_eid, e.userno as leader_userno, e.emp_name as leader_name, e.valid as leader_valid, e.dept as leader_dept, e.pathname as leader_pathname, e.orgcodepath as leader_orgcodepath,
        e.eid, e.userno, e.emp_name as name, e.valid, e.dept, e.pathname, e.orgcodepath,
                concat(l.path_userno, '>', e.userno) as path_userno, concat(l.path_name, '>', e.emp_name) as path_name, l.lvl+1 as lvl
            from mid_1 l
            join "iceberg"."ice_dw_xxxdb"."dim_xxx_emp"  e on l.userno=e.eidleader
            where not exists (select 1 from mid_1 t where t.leader_userno=l.leader_userno and t.userno=e.userno)
    ),mid_3 AS (
            select
                e.eid as leader_eid, e.userno as leader_userno, e.emp_name as leader_name, e.valid as leader_valid, e.dept as leader_dept, e.pathname as leader_pathname, e.orgcodepath as leader_orgcodepath,
        e.eid, e.userno, e.emp_name as name, e.valid, e.dept, e.pathname, e.orgcodepath,
                concat(l.path_userno, '>', e.userno) as path_userno, concat(l.path_name, '>', e.emp_name) as path_name, l.lvl+1 as lvl
            from mid_2 l
            join "iceberg"."ice_dw_xxxdb"."dim_xxx_emp"  e on l.userno=e.eidleader
            where not exists (select 1 from mid_2 t where t.leader_userno=l.leader_userno and t.userno=e.userno)
    ),mid_4 AS (
            select
                e.eid as leader_eid, e.userno as leader_userno, e.emp_name as leader_name, e.valid as leader_valid, e.dept as leader_dept, e.pathname as leader_pathname, e.orgcodepath as leader_orgcodepath,
        e.eid, e.userno, e.emp_name as name, e.valid, e.dept, e.pathname, e.orgcodepath,
                concat(l.path_userno, '>', e.userno) as path_userno, concat(l.path_name, '>', e.emp_name) as path_name, l.lvl+1 as lvl
            from mid_3 l
            join "iceberg"."ice_dw_xxxdb"."dim_xxx_emp"  e on l.userno=e.eidleader
            where not exists (select 1 from mid_3 t where t.leader_userno=l.leader_userno and t.userno=e.userno)
    ),mid_5 AS (
            select
                e.eid as leader_eid, e.userno as leader_userno, e.emp_name as leader_name, e.valid as leader_valid, e.dept as leader_dept, e.pathname as leader_pathname, e.orgcodepath as leader_orgcodepath,
        e.eid, e.userno, e.emp_name as name, e.valid, e.dept, e.pathname, e.orgcodepath,
                concat(l.path_userno, '>', e.userno) as path_userno, concat(l.path_name, '>', e.emp_name) as path_name, l.lvl+1 as lvl
            from mid_4 l
            join "iceberg"."ice_dw_xxxdb"."dim_xxx_emp"  e on l.userno=e.eidleader
            where not exists (select 1 from mid_4 t where t.leader_userno=l.leader_userno and t.userno=e.userno)
    )

           select * from mid_1
           union all select * from mid_2
           union all select * from mid_3
           union all select * from mid_4
           union all select * from mid_5