Azure Data Factory：如何按分组条件为多行分配相同值？

背景 #

我在Azure Blob Storage中存储了CSV文件，同时拥有结构如下的数据库表，表中文件名与Blob Storage中的一致，一个分类可对应多个不同文件：

需求目标 #

按category对文件分组，为每个组生成一个epoch_time并添加为新列，然后使用Azure Data Factory的Copy Data活动将文件内容插入到独立表中。同一分类的文件组需使用相同的epoch_time，该值可通过管道变量或SQL生成。

已尝试的方法 #

方法一 #

1. 使用Lookup活动查询distinct分类
2. 通过ForEach活动遍历每个分类：
   1. 设置epoch_time和category变量，epoch_time表达式为@{div(sub(ticks(adddays(utcNow(), -1)), ticks('1970-01-01')),10000000)}
   2. 使用Lookup活动执行查询：select file_name, category, @{variables('epoch_time')} as epoch_time from table where category = '@{variables('category')}'

该方法能成功按分类分组并为同组文件分配相同的epoch_time，但返回的JSON数组包含多个文件，无法直接用于Copy Data活动。

方法二 #

1. 使用Lookup活动查询表中所有文件名和分类
2. 通过ForEach活动遍历每个文件名：
   1. 设置file_name、epoch_time和category变量
   2. 执行Lookup查询

此方法可返回单个文件的结果，但因遍历每个文件，每个文件会生成独立的epoch_time，不符合需求。

期望效果 #

希望将结果用于Copy Data活动的源配置：在数据集属性的“Filename”选项中指定file_name值，在“附加列”中使用epoch_time值，最终生成包含CSV文件数据及epoch_time新列的表。现询问现有方法是否存在根本性问题，或是否有更优的实现方案。

解决方案 #

优化方法一：嵌套ForEach实现分类+文件的双层遍历 #

基于你已有的方法一，补充二次遍历即可解决多文件无法适配Copy Data的问题：

1. 保留初始步骤：用Lookup活动获取所有distinct分类，输出到外层ForEach活动
2. 在分类级别的ForEach内：
   • 计算当前分类对应的epoch_time变量（表达式不变）
   • 执行Lookup活动查询该分类下的所有file_name和category，并带上统一的epoch_time，得到该分类的文件列表数组
   • 添加内层ForEach活动，遍历这个文件列表数组
   • 在文件级别的ForEach内，从当前遍历项中直接取值：@{item().file_name}和@{item().epoch_time}，赋值给对应变量
   • 调用Copy Data活动：
     • 源数据集的Filename配置为@{variables('file_name')}
     • 附加列中添加epoch_time，值设为@{variables('epoch_time')}

这种方式既保证同一分类文件共用epoch_time，又能逐个处理文件，适配Copy Data的单文件参数要求。

高效SQL预生成方案（减少Lookup次数） #

如果你的数据库支持相关函数，可以直接通过一次Lookup完成所有文件的epoch_time分配，避免多次查询：

1. 用Lookup活动执行如下SQL，直接按category分组生成统一的epoch_time：

   SELECT 
       file_name, 
       category, 
       DATEDIFF(SECOND, '1970-01-01', DATEADD(DAY, -1, GETUTCDATE())) AS epoch_time
   FROM your_table
   GROUP BY category, file_name
   

   （注：SQL逻辑和你之前的ADF表达式一致，都是取UTC前一天的时间戳）
2. 将Lookup的输出数组传入ForEach活动，遍历每个文件项
3. 在ForEach内直接从item()中提取file_name和epoch_time，调用Copy Data活动完成处理

该方案仅需一次Lookup，减少活动数量，提升管道运行效率，同时确保同一分类的epoch_time完全一致。

现有方法的问题分析 #

• 方法一的核心问题：未对分类下的文件列表做二次遍历，Copy Data活动无法直接处理多文件数组，必须逐个传递文件参数
• 方法二的核心问题：在文件级别生成epoch_time，导致同一分类的文件无法共享时间戳，不符合需求逻辑

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