ETL过程中将扁平jsonb数据库记录转换为多表记录的方案探讨

针对你要把扁平JSONB表单数据映射到逻辑分析表的需求，下面逐个拆解三种方案的可行性、优缺点，再给出实际建议：

1. 纯SQL实现 #

完全可行，尤其是PostgreSQL这类原生支持JSONB的数据库，自带的JSON操作函数足够搞定提取逻辑。

实现思路 #

针对每个逻辑分析表，直接写SQL从源表的form_data列提取对应key的值，结合includeNoneValue配置过滤空值，再关联project_id写入目标表。

示例代码（PostgreSQL） #

-- 生成category_1下model1的逻辑表数据
INSERT INTO category_1_model1 (project_id, type, date, comment)
SELECT 
    project_id,
    form_data->>'key1' AS type,
    form_data->>'key1_date' AS date,
    form_data->>'key1_comment' AS comment
FROM form_pages
WHERE 
    form_data ? 'key1' -- 先检查key是否存在
    AND form_data->>'key1' IS NOT NULL; -- 对应includeNoneValue: False

-- 同理生成model2的数据
INSERT INTO category_1_model2 (project_id, type, date, comment)
SELECT 
    project_id,
    form_data->>'key2' AS type,
    form_data->>'key2_date' AS date,
    form_data->>'key2_comment' AS comment
FROM form_pages
WHERE 
    form_data ? 'key2'
    AND form_data->>'key2' IS NOT NULL;

优缺点 #

• ✅ 优点：性能拉满（数据库原生处理，大数据量下比Python脚本快得多）、无需额外工具、上手简单。
• ❌ 缺点：映射规则硬编码在SQL里，规则变化时要改SQL；规则多了SQL会很冗长，维护成本上升。

2. dbt + Jinja模板 #

这是最适合你场景的方案——既保留SQL的性能，又用YAML实现配置化管理映射规则，扩展性拉满。

实现思路 #

1. 把你的YAML映射文档存成dbt的配置文件（比如models/mappings.yml）。
2. 在dbt模型里用Jinja循环遍历YAML里的规则，动态生成提取JSONB字段的SQL，自动按category和modelName生成对应逻辑表的插入语句。

示例Jinja代码（dbt模型文件） #

{% set mappings = load_yaml('models/mappings.yml') %}

{% for mapping in mappings %}
    {% set target_table = mapping.category ~ '_' ~ mapping.modelName %}
    INSERT INTO {{ target_table }} (project_id, type, date, comment)
    SELECT 
        project_id,
        form_data->>'{{ mapping.type.split('.')[1] }}' AS type,
        form_data->>'{{ mapping.date.split('.')[1] }}' AS date,
        form_data->>'{{ mapping.comment.split('.')[1] }}' AS comment
    FROM form_pages
    WHERE 
        form_data ? '{{ mapping.type.split('.')[1] }}'
        {% if not mapping.includeNoneValue %}
            AND form_data->>'{{ mapping.type.split('.')[1] }}' IS NOT NULL
        {% endif %}
    {% if not loop.last %}UNION ALL{% endif %}
{% endfor %}

优缺点 #

• ✅ 优点：配置化管理规则（改YAML就行，不用碰SQL）、继承SQL的高性能、dbt自带版本控制、测试、文档、调度等全套ETL工具链，扩展性极强。
• ❌ 缺点：需要花点时间学dbt和Jinja的语法，团队没接触过的话有上手成本。

3. YAML + Pandas/Polars #

能实现，但性能是硬伤，只适合小数据量场景。

实现思路 #

读取YAML映射规则，从数据库拉取源数据到DataFrame，用矢量化操作（别用apply()，慢死）提取对应字段，按规则分组后写入目标表。

示例Polars代码（比Pandas性能好） #

import polars as pl
import yaml

# 读取映射规则
with open('mappings.yml', 'r') as f:
    mappings = yaml.safe_load(f)

# 从数据库读取源数据
df = pl.read_database(
    "SELECT project_id, form_data FROM form_pages",
    connection_uri="postgresql://user:pass@host:port/db"
)

# 遍历映射规则生成目标数据
for mapping in mappings:
    # 提取YAML里的key（比如从form_data.key1.value拿到key1）
    type_key = mapping['type'].split('.')[1]
    date_key = mapping['date'].split('.')[1]
    comment_key = mapping['comment'].split('.')[1]
    
    # 提取字段
    target_df = df.select(
        pl.col('project_id'),
        pl.col('form_data').struct.field(type_key).alias('type'),
        pl.col('form_data').struct.field(date_key).alias('date'),
        pl.col('form_data').struct.field(comment_key).alias('comment')
    )
    
    # 过滤空值
    if not mapping['includeNoneValue']:
        target_df = target_df.filter(pl.col('type').is_not_null())
    
    # 写入目标表
    target_df.write_database(
        table_name=f"{mapping['category']}_{mapping['modelName']}",
        connection_uri="postgresql://user:pass@host:port/db",
        if_exists='append'
    )

优缺点 #

• ✅ 优点：Python生态灵活，能处理SQL搞不定的复杂自定义逻辑（比如特殊字段转换）。
• ❌ 缺点：性能差（大数据量下，数据导出再导入的IO开销+Python处理速度远不如数据库原生SQL）；要维护Python脚本，依赖第三方库。

最终建议 #

• 如果数据量大、映射规则可能频繁变化：优先选dbt + Jinja模板，兼顾性能和扩展性，还能享受dbt的全套ETL能力。
• 如果团队没dbt经验、映射规则稳定：选纯SQL方案，直接高效，不用额外工具。
• 只有当需要处理SQL搞不定的复杂逻辑，且数据量不大时，才考虑YAML + Pandas/Polars。

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