Informatica无过滤查询全量数据时字段拼接功能失效问题

问题场景 #

源表结构与样例数据：

P_ID  USER_ID UNIT_NAME
390    ABC    IT
390    PQR    Operations
420    LMT    OPERAITONS
420    PQR    IT

需求为按P_ID分组，将同组的USER_ID、UNIT_NAME分别拼接为逗号分隔的字符串，期望输出如下：

p_id user_id  unit_name
390  abc,pqr  it,operations
420  lmt,pqr  operations,IT

异常表现：在Informatica的源查询SQL中添加WHERE P_ID = 具体值（如390）时，拼接逻辑可正常返回结果；移除WHERE条件执行全量数据时，拼接结果不符合预期。

核心成因 #

这类问题基本都是采用Informatica表达式转换的变量端口做逐行拼接时，隐藏的配置问题导致的，单P_ID查询时问题被场景特性掩盖，因此表现正常：

• 输入数据未按分组键P_ID做全局排序：单P_ID过滤时，返回的所有行都属于同一个P_ID，天然连续，逐行累加拼接不会串到其他组的数据；全量拉取时如果没有提前排序，不同P_ID的数据会穿插出现在数据流中，累加变量会把其他组的字段值拼到当前组结果中，直接出现串值、拼接错乱。
• 表达式端口顺序配置错误：Informatica表达式转换的端口严格按照从上到下的顺序逐行计算，如果把存储上一行P_ID值的变量端口放在拼接逻辑端口的上方，判断同组时拿到的就不是上一行的P_ID，而是当前行的P_ID。单P_ID场景下所有行P_ID完全相同，这个逻辑错误不会影响结果，全量跨P_ID时就会出现拼接值没有按组重置的问题。

解决方案 #

方案1：修正Informatica转换组件配置（适合需要在ETL层做逻辑的场景） #

按以下步骤配置即可保证全量执行结果正确：

• 源表读取组件后新增排序转换（Sorter），将P_ID设置为唯一排序键，升序、降序均可，必须保证同一个P_ID的所有数据在数据流中连续出现。
• 新增表达式转换（Expression），严格按照以下顺序配置端口（顺序不可调换）：
  • 输入端口：P_ID（数值型）、USER_ID（字符串型）、UNIT_NAME（字符串型），直接映射上游排序转换输出的同名字段
  • 变量端口1（第一个变量端口，位置在所有输入端口之后）：v_prev_pid，数值型，表达式直接写P_ID，作用是缓存当前行的P_ID，下一行计算时取到的就是上一行的P_ID值
  • 变量端口2：v_concat_user，字符串型，表达式写IIF(P_ID = v_prev_pid, v_concat_user || ',' || LOWER(USER_ID), LOWER(USER_ID))，如果不需要统一转小写可去掉LOWER函数
  • 变量端口3：v_concat_unit，字符串型，表达式写IIF(P_ID = v_prev_pid, v_concat_unit || ',' || LOWER(UNIT_NAME), LOWER(UNIT_NAME))，大小写规则同上
  • 输出端口：out_p_id = P_ID、out_user_id = v_concat_user、out_unit_name = v_concat_unit
• 表达式转换后新增聚合转换（Aggregator），分组键设置为out_p_id，out_user_id和out_unit_name的聚合规则选择MAX()，即可取到每个P_ID对应的完整拼接结果（同组最后一行的拼接值为完整值，MAX可以稳定取到最长的完整串）。

方案2：源端SQL直接聚合（优先推荐，配置简单性能高） #

直接在源限定符（Source Qualifier）中写分组聚合SQL完成拼接，不需要额外配置多个转换组件，不存在配置顺序、排序遗漏导致的异常，性能远高于ETL层逐行累加，不同数据库使用对应聚合函数即可：

• Oracle/PostgreSQL/达梦等支持LISTAGG的数据库：

SELECT 
  P_ID,
  LISTAGG(LOWER(USER_ID), ',') WITHIN GROUP (ORDER BY USER_ID) AS user_id,
  LISTAGG(LOWER(UNIT_NAME), ',') WITHIN GROUP (ORDER BY UNIT_NAME) AS unit_name
FROM 你的源表名
GROUP BY P_ID

• MySQL/HP Vertica等支持GROUP_CONCAT的数据库：

SELECT 
  P_ID,
  GROUP_CONCAT(LOWER(USER_ID) SEPARATOR ',') AS user_id,
  GROUP_CONCAT(LOWER(UNIT_NAME) SEPARATOR ',') AS unit_name
FROM 你的源表名
GROUP BY P_ID

• SQL Server 2017及以上版本：

SELECT 
  P_ID,
  STRING_AGG(LOWER(USER_ID), ',') AS user_id,
  STRING_AGG(LOWER(UNIT_NAME), ',') AS unit_name
FROM 你的源表名
GROUP BY P_ID

如果不需要统一转小写，去掉SQL中的LOWER()函数即可。

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