INSERT OVERWRITE TABLE ldldws.fact_mars_offtake PARTITION (partition_year_month)
SELECT *,DATE_FORMAT(mars_offtake_date,'yyyy-MM') AS partition_year_month
FROM
(
SELECT
t1.DOCNO AS mars_offtake_document,
t1.ODATE AS mars_offtake_date,
t1.CTMSI AS terminal_code,
t1.PDTID AS material_code,
CASE WHEN t1.bomfg = 'NR' THEN 'FG' ELSE t1.bomfg END AS complex_flag_code,
mat.sub_material_code AS sub_material_code,
t1.DIVID AS division_code,
t3.company_code AS company_code,
t4.sales_organization_code AS sales_organization_code,
t1.SOLDT AS customer_code,
t1.SHIPT AS ship_to_party,
t1.OQTY AS sellout_offtake_quantity,
t1.OQTY * t2.sales_price AS sellout_retail_value,
NULL AS sellout_promotion_value,
t1.OAMT AS sellout_actual_value,
NULL AS sellout_recommend_sales_valued,
t1.OTYPE AS sales_type,
t1.DELAY AS delay_falg,
t1.OTYPE AS mars_offtake_type_code,
t1.BACODE AS ba_code,
t1.GP_TIERS AS tires,
t1.GL_NUMORDRE AS num_ordre,
t1.GP_REFEXTERNE AS refex_terne,
t1.GP_TICKETANNULE AS ticket_annule,
t1.GL_DPA AS price,
t1.EDATE AS mars_offtake_document_date,
t1.O2OType AS o2o_type,
t1.PDTID AS bom_code
FROM
(
SELECT * FROM ldldwd.mars_offtake_smb
WHERE ODATE >= '2020-01-01'
) t1
LEFT JOIN ldldws.vw_dim_terminal t3
ON t1.CTMSI = t3.terminal_code AND t3.is_current_flag=1
LEFT JOIN ldldws.dim_company_division t4
ON t1.DIVID = t4.division_code AND t3.company_code = t4.company_code
LEFT JOIN
(
SELECT material_code, sales_price, from_date, to_date,company_code FROM ldldws.dim_material_price_list
) t2
ON t1.PDTID = t2.material_code AND to_date(t1.ODATE, 'yyyy-MM-dd') BETWEEN t2.from_date AND t2.to_date and t2.company_code = '0888'
LEFT JOIN
(
select material_code,sub_material_code,from_date, to_date,company_code FROM ldldws.dim_material
) mat
on t1.PDTID = mat.material_code AND to_date(t1.ODATE, 'yyyy-MM-dd') BETWEEN mat.from_date AND mat.to_date and mat.company_code = '0888'
)t5
DISTRIBUTE BY partition_year_month, mars_offtake_document, terminal_code, material_code, division_code, company_code, sales_organization_code
;
INSERT OVERWRITE TABLE ldldws.i_mars_offtake partition(offtake_year)
SELECT
/*+ BROADCAST(current_tem,current_mat,offtake_type) */
fact.mars_offtake_document AS mars_offtake_document,
fact.terminal_code AS terminal_code,
fact.mars_offtake_date AS mars_offtake_date,
fact.material_code AS material_code,
fact.complex_flag_code AS complex_flag_code,
fact.sub_material_code AS sub_material_code,
fact.division_code AS division_code,
fact.company_code AS company_code,
fact.sales_organization_code AS sales_organization_code,
fact.ba_code AS ba_code,
fact.tiers AS tiers,
CASE WHEN fact.mars_offtake_type_code='N' THEN 'NS' ELSE 'SR' END AS mars_offtake_type_code,
fact.sellout_quantity AS sellout_quantity,
fact.sellout_retail_value AS sellout_retail_value,
fact.sellout_recommend_sales_valued AS sellout_recommend_sales_valued,
fact.sellout_promotion_value AS sellout_promotion_value,
fact.sellout_actual_value AS sellout_actual_value,
fact.sales_type AS sales_type,
fact.customer_code AS customer_code,
fact.num_ordre AS num_ordre,
fact.refex_terne AS refex_terne,
fact.ticket_annule AS ticket_annule,
fact.price AS price,
fact.mars_offtake_document_date AS mars_offtake_document_date,
fact.o2o_type AS o2o_type,
fact.delay_flag AS delay_flag,
fact.ship_to_party AS ship_to_party,
current_tem.terminal_name_cn ,
current_tem.division_region_name_cn ,
current_tem.region_code ,
current_tem.region_name_cn ,
current_tem.crm_counter_code ,
current_tem.personname_zh ,
current_tem.parent_person_name ,
current_tem.terminal_level ,
current_tem.is_ka ,
current_tem.ka_type ,
current_tem.ka_name ,
current_tem.city_code ,
current_tem.city_name_cn ,
current_tem.city_name_en ,
current_tem.line_city_name_cn ,
current_tem.line_city_name_en ,
current_tem.province_code ,
current_tem.province_name_cn ,
current_tem.province_name_en ,
current_tem.channel_code ,
current_tem.channel_name_cn ,
current_tem.level1_name_cn ,
current_tem.level2_name_cn ,
current_tem.level3_name_cn ,
current_tem.distributor_code as dis_code ,
current_mat.material_name_cn ,
current_mat.material_type_code ,
current_mat.material_name_en ,
current_mat.material_group_code ,
current_mat.material_group_name_en ,
current_mat.material_class_name_en ,
current_mat.material_class_name_en_lv1 ,
current_mat.material_class_name_en_lv2 ,
current_mat.material_class_name_en_lv3 ,
current_mat.material_class_name_en_lv4 ,
current_mat.material_class_name_en_lv5 ,
current_mat.material_class_name_en_lv6 ,
current_mat.material_class_name_en_lv7 ,
current_mat.material_class_name_en_lv8 ,
current_mat.material_class_name_en_lv9 ,
current_mat.material_class_code_local ,
current_mat.material_class_name_en_local ,
current_mat.material_class_name_en_lv1_local ,
current_mat.material_class_name_en_lv2_local ,
current_mat.material_class_name_en_lv3_local ,
current_mat.material_class_name_en_lv4_local ,
current_mat.material_class_name_en_lv5_local ,
current_mat.material_class_name_en_lv6_local ,
current_mat.material_class_name_en_lv7_local ,
current_mat.material_class_name_en_lv8_local ,
current_mat.material_class_name_en_lv9_local ,
current_mat.material_sales_price ,
current_mat.material_sales_price_full ,
current_mat.bom_flag ,
current_mat.pcb_flag ,
current_mat.cas_flag ,
offtake_type.mars_offtake_type_name AS mars_offtake_type_name,
date_format(fact.mars_offtake_date,'yyyy') as offtake_year
FROM
(
SELECT
mars_offtake_document,
terminal_code,
mars_offtake_date,
material_code,
complex_flag_code,
sub_material_code,
division_code,
company_code,
sales_organization_code,
ba_code,
tiers,
mars_offtake_type_code,
sellout_quantity,
sellout_retail_value,
sellout_recommend_sales_valued,
sellout_promotion_value,
sellout_actual_value,
sales_type,
customer_code,
num_ordre,
refex_terne,
ticket_annule,
price,
mars_offtake_document_date,
o2o_type,
delay_flag,
ship_to_party
FROM ldldws.fact_mars_offtake
) fact
left join
(
select
terminal_code,terminal_name_cn,division_region_name_cn,region_code,region_name_cn,crm_counter_code,personname_zh,
parent_person_name,terminal_memo1 as terminal_level,is_ka,ka_type,city_code,ka_name,city_name_cn,city_name_en,
province_code,province_name_cn,province_name_en,channel_code,channel_name_cn,level1_name_cn,level2_name_cn,level3_name_cn,
distributor_code,line_city_name_cn,line_city_name_en
from ldldws.dim_terminal
where is_current_flag=1
)current_tem
on fact.terminal_code = current_tem.terminal_code
left join
(
select
material_code,material_name_cn,material_type_code,material_name_en,material_group_code,material_group_name_en,
material_class_name_en,material_class_name_en_lv1,material_class_name_en_lv2,material_class_name_en_lv3,material_class_name_en_lv4,
material_class_name_en_lv5,material_class_name_en_lv6,material_class_name_en_lv7,material_class_name_en_lv8,material_class_name_en_lv9,
material_class_code_local,material_class_name_en_local,material_class_name_en_lv1_local,material_class_name_en_lv2_local,material_class_name_en_lv3_local ,
material_class_name_en_lv4_local,material_class_name_en_lv5_local,material_class_name_en_lv6_local,material_class_name_en_lv7_local,material_class_name_en_lv8_local,
material_class_name_en_lv9_local,material_sales_price,
material_sales_price_full,bom_flag,pcb_flag,cas_flag
from ldldws.dim_material
where is_current_flag=1
)current_mat
on fact.material_code = current_mat.material_code
LEFT JOIN
(
SELECT
CASE WHEN mars_offtake_type_code='N' THEN 'NS' WHEN mars_offtake_type_code='R' THEN 'SR' ELSE mars_offtake_type_code END AS mars_offtake_type_code,
mars_offtake_type_name
FROM ldldws.dim_mars_offtake_type
) offtake_type
ON fact.mars_offtake_type_code = offtake_type.mars_offtake_type_code
;这两个spark sql中 目标表和源表都是哪些？
