Pig关联Events和mortality表提取非mortality记录的过滤条件问题

问题核心原因 #

你当前使用的是Pig默认的内连接（INNER JOIN），这种连接模式只会保留两张表patientid完全匹配的记录，所以本身events里存在但mortality里不存在的记录已经在JOIN步骤被过滤掉了，后续不管用什么过滤条件都拿不到目标数据，这也是你得到空数据集的直接原因。

过滤条件选择说明 #

• 你需求是提取「存在于events但不存在于mortality」的记录，最终过滤条件应该用label IS NULL
• 你提到的label != 1不适用，因为你已经说明mortality表的label固定为1，内连接后所有保留的记录label都是1，label !=1自然筛选不到任何数据

修改后的完整代码 #

-- load events file
events = LOAD '../sample_test/sample_events.csv' USING PigStorage(',') AS (patientid:int, eventid:chararray, eventdesc:chararray, timestamp:chararray, value:float);

-- select required columns from events
events = FOREACH events GENERATE patientid, eventid, ToDate(timestamp, 'yyyy-MM-dd') AS etimestamp, value;

-- load mortality file
mortality = LOAD '../sample_test/sample_mortality.csv' USING PigStorage(',') as (patientid:int, timestamp:chararray, label:int);

mortality = FOREACH mortality GENERATE patientid, ToDate(timestamp, 'yyyy-MM-dd') AS mtimestamp, label;

-- 改用左外连接，保留events所有记录，匹配不到mortality的字段会置为null
eventswithmort = JOIN events BY patientid LEFT OUTER, mortality BY patientid;
eventswithmort = FOREACH eventswithmort GENERATE events.patientid, events.eventid, events.etimestamp, events.value, mortality.mtimestamp, mortality.label;

-- 过滤出mortality中没有匹配的记录，就是events独有的数据
aliveevents = FILTER eventswithmort BY label IS NULL;

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