Untitled

mail@pastecode.io avatar
unknown
plain_text
a month ago
1.1 kB
23
Indexable
Never
INSERT INTO MY__DWH.l_groups_dialogs(hk_l_groups_dialogs, hk_message_id,hk_group_id,load_dt,load_src)
select

       hash(hd.hk_message_id,hg.hk_group_id),
       hd.hk_message_id,
       hg.hk_group_id,
       now() as load_dt,
       's3' as load_src

from MY__STAGING.dialogs as d
left join MY__DWH.h_groups as hg on d.message_group = hg.group_id
left join MY__DWH.h_dialogs as hd on d.message_id = hd.message_id
where d.message_group is not null and hash(hd.hk_message_id,hg.hk_group_id) not in (select hk_l_groups_dialogs from MY__DWH.l_groups_dialogs);


INSERT INTO MY__DWH.l_user_message(hk_l_user_message, hk_user_id,hk_message_id,load_dt,load_src)
select

       hash(hd.hk_message_id,hu.hk_user_id),
       hd.hk_message_id,
       hu.hk_user_id,
       now() as load_dt,
       's3' as load_src

from MY__STAGING.dialogs as d
left join MY__DWH.h_users as hu on d.message_from = hu.user_id
left join MY__DWH.h_dialogs as hd on d.message_id = hd.message_id
where  hash(hd.hk_message_id,hu.hk_user_id) not in (select hk_l_user_message from MY__DWH.l_user_message)
    and hu.hk_user_id is not null;
Leave a Comment