[KYLIN-4367] Build Global Dict by MR/Hive, Replace intermediate table Step implementation

• Type: Sub-task
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Sprint: Sprint 50
• Assignee: wangxiaojing
• Reporter: wangxiaojing
• Created: 02/Feb/20 14:59
• Updated: 30/May/20 10:31
• Resolved: 30/May/20 10:31

Description

Replace the original value in the flat table with the dictionary encoded value by HQL.

HQL example below:

 

INSERT OVERWRITE TABLE db.kylin_intermediate_kylin_sales_cube_mr_6222c210_ce2d_e8ce_dd0f_f12c38fa9115 
SELECT 
a.KYLIN_SALES_TRANS_ID 
,a.KYLIN_SALES_PART_DT 
,a.KYLIN_SALES_LEAF_CATEG_ID 
,a.KYLIN_SALES_LSTG_SITE_ID 
,a.KYLIN_CATEGORY_GROUPINGS_META_CATEG_NAME 
,a.KYLIN_CATEGORY_GROUPINGS_CATEG_LVL2_NAME 
,a.KYLIN_CATEGORY_GROUPINGS_CATEG_LVL3_NAME 
,a.KYLIN_SALES_LSTG_FORMAT_NAME 
,b.dict_val 
,a.KYLIN_SALES_BUYER_ID 
,a.BUYER_ACCOUNT_ACCOUNT_BUYER_LEVEL 
,a.SELLER_ACCOUNT_ACCOUNT_SELLER_LEVEL 
,a.BUYER_ACCOUNT_ACCOUNT_COUNTRY 
,a.SELLER_ACCOUNT_ACCOUNT_COUNTRY 
,a.BUYER_COUNTRY_NAME 
,a.SELLER_COUNTRY_NAME 
,a.KYLIN_SALES_OPS_USER_ID 
,a.KYLIN_SALES_OPS_REGION 
,a.KYLIN_SALES_PRICE 
,a.KYLIN_SALES_ITEM_COUNT 
FROM db.kylin_intermediate_kylin_sales_cube_mr_6222c210_ce2d_e8ce_dd0f_f12c38fa9115 a 
LEFT OUTER JOIN 
( 
SELECT dict_key, dict_val FROM db.kylin_sales_cube_mr_notopn_global_dict WHERE dict_column = 'KYLIN_SALES_SELLER_ID' 
) b 
 ON a.KYLIN_SALES_SELLER_ID = b.dict_key;INSERT OVERWRITE TABLE db.kylin_intermediate_kylin_sales_cube_mr_6222c210_ce2d_e8ce_dd0f_f12c38fa9115 
SELECT 
a.KYLIN_SALES_TRANS_ID 
,a.KYLIN_SALES_PART_DT 
,a.KYLIN_SALES_LEAF_CATEG_ID 
,a.KYLIN_SALES_LSTG_SITE_ID 
,a.KYLIN_CATEGORY_GROUPINGS_META_CATEG_NAME 
,a.KYLIN_CATEGORY_GROUPINGS_CATEG_LVL2_NAME 
,a.KYLIN_CATEGORY_GROUPINGS_CATEG_LVL3_NAME 
,a.KYLIN_SALES_LSTG_FORMAT_NAME 
,a.KYLIN_SALES_SELLER_ID 
,b.dict_val 
,a.BUYER_ACCOUNT_ACCOUNT_BUYER_LEVEL 
,a.SELLER_ACCOUNT_ACCOUNT_SELLER_LEVEL 
,a.BUYER_ACCOUNT_ACCOUNT_COUNTRY 
,a.SELLER_ACCOUNT_ACCOUNT_COUNTRY 
,a.BUYER_COUNTRY_NAME 
,a.SELLER_COUNTRY_NAME 
,a.KYLIN_SALES_OPS_USER_ID 
,a.KYLIN_SALES_OPS_REGION 
,a.KYLIN_SALES_PRICE 
,a.KYLIN_SALES_ITEM_COUNT 
FROM db.kylin_intermediate_kylin_sales_cube_mr_6222c210_ce2d_e8ce_dd0f_f12c38fa9115 a 
LEFT OUTER JOIN 
( 
SELECT dict_key, dict_val FROM db.kylin_sales_cube_mr_notopn_global_dict WHERE dict_column = 'KYLIN_SALES_BUYER_ID' 
) b 
 ON a.KYLIN_SALES_BUYER_ID = b.dict_key;

 

Issue Links

• links to: GitHub Pull Request #1094