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

Agile Board

Attach files

Attach Screenshot

Voters

Stop watching

Watchers

Link

Clone

Update Comment Author

Replace String in Comment

Update Comment Visibility

Delete Comments

XML

Word

Printable

JSON

Details

Type: Sub-task
Status: Resolved
Priority: Major
Resolution: Fixed
Affects Version/s: None
Fix Version/s: None
Component/s: None
Labels:
None

Sprint:
Sprint 50

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;