shells/insertToTable.sh

#!/bin/bash
/opt/module/hive/bin/hive -e "
with
a as (
    select
    user_id,
    count(0) order_count,
    sum(final_total_amount) order_amount
    from $1.dwd_order_info
    group by user_id
),
b as (
    select
    user_id,
    count(0) pay_count,
    sum(total_amount) pay_amount
    from $1.dwd_payment_info
    group by user_id
),
     c as(
         select
         user_id,
                count(appraise) comment_count
         from $1.dwd_comment_info
         group by user_id
     )
     insert overwrite table  $1.dws_user_action
select
    user_id,
       sum(d.order_count) order_count,
       sum(d.order_amount) order_amount,
       sum(d.pay_count)  pay_count,
       sum(d.pay_amount) pay_amount,
       sum(d.comment_count) comment_count
from (
    select
    user_id,
           order_count,
           order_amount,
           0 pay_count,
           0 pay_amount,
           0 comment_count
    from a
    union all
    select
        user_id,
        0,
        0,
        pay_count,
        pay_amount,
        0
    from b
    union all
    select
        user_id,
        0,
        0,
        0 ,
        0 ,
        comment_count
    from c
         ) d
group by user_id;"