[
https://issues.apache.org/jira/browse/KYLIN-4367?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
wangxiaojing updated KYLIN-4367:
--------------------------------
Description:
Replace the original value in the flat table with the dictionary encoded value
by HQL.
HQL example below:
{code:java}
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;
{code}
was:
Replace the original value in the flat table with the dictionary encoded value
by HQL.
HQL example below:
{code:java}
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
,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
,b.dict_val
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_ITEM_COUNT'
) b
ON a.KYLIN_SALES_ITEM_COUNT = 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
,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;
{code}
> Build Global Dict by MR/Hive, Replace intermediate table Step implementation
> ----------------------------------------------------------------------------
>
> Key: KYLIN-4367
> URL: https://issues.apache.org/jira/browse/KYLIN-4367
> Project: Kylin
> Issue Type: Sub-task
> Reporter: wangxiaojing
> Assignee: wangxiaojing
> Priority: Major
>
> Replace the original value in the flat table with the dictionary encoded
> value by HQL.
> HQL example below:
>
> {code:java}
> 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;
> {code}
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
