-We have a master view that is built on top of many views -Almost all the relevant mart data is in this view and most of the jobs extract data from this view.
-Simple select to this view translates in 43 maps and 37 reducers and takes around 1 hour which puts lot of load on the cluster
Following things I tried :
set hive.vectorized.execution.reduce.groupby.enabled=true;
set hive.exec.orc.split.strategy=BI;
set hive.merge.tezfiles=true;
SET hive.vectorized.execution.enabled=true;
SET hive.vectorized.execution.reduce.enabled=true;
SET hive.cbo.enable=true;
SET hive.compute.query.using.stats=true;
set hive.exec.compress.intermediate = true;
Here is the query :
CREATE VIEW `mstr_pub.vw_customer_info` AS
SELECT
field 1, field2, field 3 .....
SUM(`PTS`.`current_balance`) OVER (PARTITION BY `voucher_cust`.`rel_child`) AS `HPTS`,
FLOOR(SUM(`PTS`.`c_balance`) OVER (PARTITION BY `voucher_cust`.`relationships_child`) / 10000)*1000 AS `HPTS_VAL`,
CAST(IF(UPPER(TRIM(`table1`.`pcplus`)) = 'ACKN', TRUE, FALSE) AS BOOLEAN) AS `NO_PC_PLUS`,
GET_JSON_OBJECT(`VIRTUAL`.`json`, '$.FIRST_EARN_DATE') AS `FIRST_EARN_DATE`,
... many such GET_JSON_OBJECT calculation fields...
FROM
`mstr_work`.`vw_voucher_cust_LATEST` `table1`
LEFT OUTER JOIN
`mstr_work`.`table2` `table2`
ON `table1`.`voucher_id` = `table2`.`voucher_id`
LEFT OUTER JOIN
`mstr_work`.`table3` `table3`
ON `table2`.`cust_id` = `table3`.`cust_id`
LEFT OUTER JOIN
(
SELECT
`table4`.`voucher_id`,
SUM(`table4`.`current_balance`) AS `CURRENT_BALANCE`
FROM
`mstr_work`.`table4`
WHERE
`table4`.`account_status` = 'ACTIVE'
GROUP BY
`table4`.`voucher_id`
)
`PTS`
ON `table1`.`voucher_ID` = `PTS`.`voucher_id`
LEFT OUTER JOIN
(
SELECT
`t`.`voucher_id`,
`t`.`capture_source`
FROM
(
SELECT
`table5`.`voucher_id`,
`table5`.`status_capture_source`,
ROW_NUMBER() OVER (PARTITION BY `table5`.`cust_id`
ORDER BY
`table5`.`status_capture_datetime` DESC) AS `RANK_`
FROM
`MSTR_CORE`.`table5`
WHERE
`table5`.`marketing_status` = 'COMMUNICATE'
AND `table5`.`capture_datetime` IS NOT NULL
)
`T`
WHERE
`t`.`rank_` = 1
)
`table6`
ON `table1`.`voucher_ID` = `table6`.`voucher_id`
LEFT OUTER JOIN
`mstr_work`.`table7` `table7`
ON `table2`.`cust_id` = `table7`.`cust_id`
AND `table7`.`notification_type` = 'SURVEYS'
AND `table2`.`business_effective_ts` = `table7`.`business_effective_ts`
LEFT OUTER JOIN
`mstr_work`.`table7` `table7`
ON `table2`.`cust_ID` = `table7`.`cust_id`
AND `table7`.`notification_type` = 'OFFERALERTS'
AND `table7`.`communication_type` = 'EMAIL'
AND `table2`.`BUSINESS_EFFECTIVE_TS` = `table7`.`business_effective_ts`
LEFT OUTER JOIN
`mstr_work`.`table8` `table8`
ON `table1`.`voucher_ID` = `table8`.`voucher_id`
AND `table8`.`rbc_customer` IS NOT NULL
LEFT OUTER JOIN
`mstr_work`.`table10` `table10`
ON `table2`.`cust_ID` = `table10`.`cust_id`
AND `table10`.`home_banner_id` = 'RETAIL'
LEFT OUTER JOIN
(
SELECT DISTINCT
`table9`.`zrks`,
`table9`.`zorg`,
`table9`.`zext`,
`table9`.`zweg`,
`table9`.`zrext`
FROM
`mstr_work`.`table9`
)
`table11`
ON `table10`.`store_id` = `table11`.`zrks`
LEFT OUTER JOIN
`mstr_work`.`table10` `table10`
ON `table2`.`cust_ID` = `table10`.`cust_id`
AND `table10`.`home_id` = 'SHOPPERS'
LEFT OUTER JOIN
`mstr_work`.`table11` `table12`
ON `table10`.`home_store_id` = `table12`.`org`
AND `table12`.`parent_type` = 'MKR'
AND `table12`.`relationship` = 'CONTAINS'
LEFT OUTER JOIN
`mstr_work`.`table13` as `table13`
ON `table1`.`relationships_child` = `table13`.`household_voucher_id`
AND UPPER(`table13`.`seg_attr`) = "SEG_ENTERPRISE"
LEFT OUTER JOIN
`mstr_work`.`table13` as `table13`
ON `table1`.`RELATIONSHIPS_CHILD` = `table13`.`household_voucher_id`
AND UPPER(`table13`.`seg_attr`) = "NATIONAL_PURCHASE"
LEFT OUTER JOIN
`mstr_work`.`table13` as `table13`
ON `table1`.`RELATIONSHIPS_CHILD` = `table13`.`household_voucher_id`
AND UPPER(`table13`.`seg_attr`) = "VALUE_SEG_SDM"
LEFT OUTER JOIN
(
SELECT
`table14`.`voucher_id`,
concat('{', concat_ws(',', collect_set(concat('"', `table14`.`attribute_name`, '":"', `table14`.`attribute_value`, '"'))), '}') `JSON`
FROM
`MSTR_CORE`.`table14`
GROUP BY
`table14`.`voucher_id`
)
`VIRTUAL`
ON `table1`.`voucher_ID` = `VIRTUAL`.`voucher_id`
WHERE
`table2`.`email_address` LIKE '%@%'
OR `table1`.`voucher_status` = "DELETED"
Request :
Is there anything we can do about this query. Only thing i can think of is - to simplify the json set up in the last table before "where clause" and avoid json parsing in "select" parsing to help reduce couple of reducers. I see same table1 i.e table1 is used many times in join. how can i reduce and merge the joins or simplify. is there such option
any pointers will be of great help
There are multiple joins with the same table using like this:
LEFT OUTER JOIN
`mstr_work`.`table13` as `table13`
ON `table1`.`relationships_child` = `table13`.`household_voucher_id`
AND UPPER(`table13`.`seg_attr`) = "SEG_ENTERPRISE"
LEFT OUTER JOIN
`mstr_work`.`table13` as `table13`
ON `table1`.`RELATIONSHIPS_CHILD` = `table13`.`household_voucher_id`
AND UPPER(`table13`.`seg_attr`) = "NATIONAL_PURCHASE"
LEFT OUTER JOIN
`mstr_work`.`table13` as `table13`
ON `table1`.`RELATIONSHIPS_CHILD` = `table13`.`household_voucher_id`
AND UPPER(`table13`.`seg_attr`) = "VALUE_SEG_SDM"
You can easily get rid of multiple joins with the same table using single join:
LEFT OUTER JOIN
`mstr_work`.`table13` as `table13`
ON `table1`.`RELATIONSHIPS_CHILD` = `table13`.`household_voucher_id`
AND UPPER(`table13`.`seg_attr`) in ("SEG_ENTERPRISE","NATIONAL_PURCHASE","VALUE_SEG_SDM")
And when selecting columns from table13 use max(), sum(), etc aggregation with case statement:
max(case when UPPER(`table13`.`seg_attr`) = SEG_ENTERPRISE then <some column> end) end as SEG_ENTERPRISE_data
And the same for table7, table10