Untitled
/* собираем временную таблицу с данными из всех источников */ DROP TABLE IF EXISTS tmp_sources; CREATE TEMP TABLE tmp_sources AS SELECT distinct order_id, order_created_date, order_completion_date, order_status, craftsman_id, craftsman_name, craftsman_address, craftsman_birthday, craftsman_email, product_id, product_name, product_description, product_type, product_price, customer_id, customer_name, customer_address, customer_birthday, customer_email FROM source1.craft_market_wide UNION SELECT distinct t2.order_id, t2.order_created_date, t2.order_completion_date, t2.order_status, t1.craftsman_id, t1.craftsman_name, t1.craftsman_address, t1.craftsman_birthday, t1.craftsman_email, t1.product_id, t1.product_name, t1.product_description, t1.product_type, t1.product_price, t2.customer_id, t2.customer_name, t2.customer_address, t2.customer_birthday, t2.customer_email FROM source2.craft_market_masters_products t1 inner JOIN source2.craft_market_orders_customers t2 ON t2.product_id = t1.product_id and t1.craftsman_id = t2.craftsman_id UNION SELECT distinct t1.order_id, t1.order_created_date, t1.order_completion_date, t1.order_status, t2.craftsman_id, t2.craftsman_name, t2.craftsman_address, t2.craftsman_birthday, t2.craftsman_email, t1.product_id, t1.product_name, t1.product_description, t1.product_type, t1.product_price, t3.customer_id, t3.customer_name, t3.customer_address, t3.customer_birthday, t3.customer_email FROM source3.craft_market_orders t1 inner JOIN source3.craft_market_craftsmans t2 ON t1.craftsman_id = t2.craftsman_id inner JOIN source3.craft_market_customers t3 ON t1.customer_id = t3.customer_id; /*Вывод содержимого временной таблицы*/ SELECT * FROM tmp_sources; MERGE INTO dwh.d_craftsman d USING tmp_sources t ON d.craftsman_name = t.craftsman_name AND d.craftsman_email = t.craftsman_email WHEN MATCHED THEN UPDATE SET craftsman_address = t.craftsman_address, craftsman_birthday = t.craftsman_birthday, load_dttm = current_timestamp WHEN NOT MATCHED THEN INSERT (craftsman_name, craftsman_address, craftsman_birthday, craftsman_email, load_dttm) VALUES (t.craftsman_name, t.craftsman_address, t.craftsman_birthday, t.craftsman_email, current_timestamp); -- Обновление таблицы dwh.d_product MERGE INTO dwh.d_product p USING tmp_sources t ON p.product_name = t.product_name AND p.product_description = t.product_description AND p.product_price = t.product_price WHEN MATCHED THEN UPDATE SET product_name = t.product_name, product_description = t.product_description, product_price = t.product_price WHEN NOT MATCHED THEN INSERT (product_name, product_description, product_price) VALUES (t.product_name, t.product_description, t.product_price); -- Обновление таблицы dwh.d_customer MERGE INTO dwh.d_customer d USING tmp_sources t ON d.customer_name = t.customer_name AND d.customer_email = t.customer_email WHEN MATCHED THEN UPDATE SET customer_address= t.customer_address, customer_birthday= t.customer_birthday, load_dttm = current_timestamp WHEN NOT MATCHED THEN INSERT (customer_name, customer_address, customer_birthday, customer_email, load_dttm) VALUES (t.customer_name, t.customer_address, t.customer_birthday, t.customer_email, current_timestamp);
Leave a Comment