So if I am understanding your question, the issue is that you have the primary key of ContactId, which limits the results to just a single contact record instead of including each of the correlated campaign records.
To get the 500k result, you would need to either include both ContactId and CampaignId as the primary keys or shift it to just show CampaignId ...
Here's the sargable method that I typically use to get yesterday's records:
from _open o
o.eventdate >= convert(date, getDate()-1)
and o.eventdate < convert(date, getDate())
and o.isunique = 1
Converting to date effectively resets time portion to midnight and using a range captures all times between midnights.
as per your last comment / screenshot -
EazyE's original reply is correct, but your screenshot shows a misinterpretation of what was suggested. I'll try and elaborate.
ID_MKT_Cloud__c apparently is a field in your target DE.
The error tells you that in your target DE, it is in fact a mandatory field, so nothing can write data into the target without ...