hive 存储过程(HPL)

2021年3月3日 40点热度 0条评论 来源: date-date

简述:

官网:http://www.hplsql.org

网上的安装教程很多,本博客就并不包含安装了。本博客主要是用一个完整的例子来展现如何写hplsql,执行和调试。

背景:由于数据量越来越大,mysql存储过程执行缓慢。想要把数据应用大数据存储框架进行存储,并尽量不要进行大量代码重构。所以最后采用hive。

demo:

CREATE PROCEDURE P_ETL_F_DFNSBQ_TOP1000(IN v_month1  STRING, OUT v_retcode  STRING,OUT v_retinfo STRING)
  v_month  string;
  v_bbq string;
  v_test  string ;
  s_day string;
  s_last_day  string;
  s_day1       string;
  s_last_day1  string;
  d_flag decimal(38,0);
  v_user string;
  v_rowcount decimal(38,6);
  v_procname string;
  v_table_name string;
  v_start TIMESTAMP;

BEGIN
set v_month=v_month1;
set v_test='select from_unixtime(unix_timestamp(to_date('''|| v_month||''')),'||'''yyyyMMdd'||''')';

execute IMMEDIATE  v_test into v_bbq ;
print v_bbq;

set v_test='select from_unixtime(unix_timestamp(trunc(to_date('''||v_month ||'''),'||'''yyyy'||''')),'||'concat(''yyyy'||''','||'upper('''||'MM'||'''),'||'''dd'''||'))';
print v_test;
execute IMMEDIATE  v_test into s_day ;
print s_day;
set v_test='select from_unixtime(unix_timestamp(days_sub(months_add(trunc(to_date('''||v_month ||'''),'||'upper('''||'MM'||''')),1),1)),'||'concat(''yyyy'||''','||'upper('''||'MM'||'''),'||'''dd'''||'))';
execute v_test into s_last_day ;
print s_last_day;
set v_test='select from_unixtime(unix_timestamp(trunc(years_sub(to_date('''||v_month ||'''),1),'||'''yyyy'||''')),'||'concat(''yyyy'||''','||'upper('''||'MM'||'''),'||'''dd'''||'))';
execute v_test into s_day1 ;
print s_day1;
set v_test='select from_unixtime(unix_timestamp(years_sub(days_sub(months_add(trunc(to_date('''||v_month ||'''),'||'upper('''||'MM'||''')),1),1),1)),'||'concat(''yyyy'||''','||'upper('''||'MM'||'''),'||'''dd'''||'))';
execute v_test into s_last_day1;
print s_last_day1;

v_user='DC_MID';
v_procname='P_ETL_F_DFNSBQ_TOP1000';
v_table_name='F_BI_IN_DFNSBQ_TOP1000';
v_start=SYSDATE;

--CALL P_INSERT_ETL_LOG(v_bbq,v_user,v_procname,v_table_name,v_start,'',v_rowcount,v_retcode,v_retinfo,'');

IF 1=1 THEN
/*
set v_test='select count(*) from  M_BI_IN_DFNSBQ_TOP1000 A '||' WHERE A.ACCT_YEAR='''||v_bbq||'''';
execute v_test into d_flag;
IF d_flag>0 then
TRUNCATE table M_BI_IN_DFNSBQ_TOP1000 A;
END IF;*/
-- if the table is kudu
DELETE FROM  M_BI_IN_DFNSBQ_TOP1000 A  WHERE A.ACCT_YEAR=v_bbq;

INSERT INTO M_BI_IN_DFNSBQ_TOP1000
    SELECT v_bbq ACCT_YEAR, PRIMARY_ID, XZQH, HY_DM, RN
      FROM (SELECT PRIMARY_ID,
                   XZQH,
                   HY_DM,
                   ROW_NUMBER() OVER( ORDER BY AMT DESC) RN
              FROM (SELECT PRIMARY_ID,
                           MAX(CASE WHEN SDS_AMT = 0  
                               THEN 
                                   CASE WHEN XZQH_RN=1 
                                   THEN  XZQH ELSE NULL END 
                               ELSE 
                                   CASE WHEN SDS_RN=1
                                   THEN  XZQH ELSE NULL END
                               END) XZQH,                              
                           MAX(CASE WHEN HY_RN=1 THEN HY_DM ELSE NULL END) HY_DM,
                           MAX(AMT) AMT
                      FROM (SELECT PRIMARY_ID,
                                   XZQH,
                                   HY_DM,
                                   ROW_NUMBER() OVER(PARTITION BY PRIMARY_ID ORDER BY AMT_XZQH DESC) XZQH_RN,
                                   ROW_NUMBER() OVER(PARTITION BY PRIMARY_ID ORDER BY AMT_HY DESC) HY_RN,
                                   ROW_NUMBER() OVER(PARTITION BY PRIMARY_ID ORDER BY SDS_XZQH DESC) SDS_RN,
                                   SDS_AMT,
                                   AMT
                              FROM (SELECT 
                                     PRIMARY_ID,
                                     DIVISIONS XZQH,
                                     A.IND_PHY HY_DM,                                      
                                     SUM(NVL(CITY_DIV_TRAAMT, 0.0) +
                                         NVL(COUNTY_DIV_TRAAMT, 0)) OVER(PARTITION BY A.PRIMARY_ID, A.DIVISIONS) AMT_XZQH,
                                     SUM(NVL(CITY_DIV_TRAAMT, 0) +
                                         NVL(COUNTY_DIV_TRAAMT, 0)) OVER(PARTITION BY A.PRIMARY_ID, A.IND_PHY) AMT_HY,
                                     SUM( CASE WHEN SUBSTR(BUDGETSUBJECTCODE, 1, 7)='AA10104' THEN  NVL(CITY_DIV_TRAAMT, 0.0) +
                                                NVL(COUNTY_DIV_TRAAMT, 0.0) ELSE 0 END) OVER(PARTITION BY A.PRIMARY_ID, A.DIVISIONS) SDS_XZQH ,              
                                     SUM(NVL(CITY_DIV_TRAAMT, 0) +
                                         NVL(COUNTY_DIV_TRAAMT, 0)) OVER(PARTITION BY A.PRIMARY_ID) AMT,                                                                      
                                     SUM(CASE WHEN SUBSTR(BUDGETSUBJECTCODE, 1, 7)='AA10104' THEN  NVL(CITY_DIV_TRAAMT, 0) +
                                                     NVL(COUNTY_DIV_TRAAMT, 0) ELSE 0 END ) OVER(PARTITION BY A.PRIMARY_ID) SDS_AMT
                                      FROM F_BI_IN_RHDZSP_CX_DAY_HZ A
                                     WHERE A.APPLYDATE BETWEEN s_day AND s_last_day
                                       AND A.PRIMARY_ID NOT LIKE '%11111111'                                      
                                     AND REGEXP_EXTRACT(A.TAXPAYNAME, '(^[A-Za-z]+$)',1) is null
                                       /*AND  NOT REGEXP_LIKE(A.TAXPAYNAME, '(^[A-Za-z]+$)')*/
                                       AND A.BUDGETSUBJECTCODE LIKE 'A%')B)C
                                   GROUP BY PRIMARY_ID)D)E
     WHERE RN <= 1000;

 TRUNCATE M_BI_IN_DFNSBQ_TOP1000_QYXX;


   INSERT INTO M_BI_IN_DFNSBQ_TOP1000_QYXX
      SELECT TTT.PRIMARY_ID, TTT.TAXPAYNAME, TTT.TAXPAYCODE
        FROM (  SELECT HZ.PRIMARY_ID,HZ.TAXPAYNAME,HZ.TAXPAYCODE,HZ.APPLYDATE,ROW_NUMBER() OVER(PARTITION BY HZ.PRIMARY_ID ORDER BY HZ.APPLYDATE DESC) RN
                FROM F_BI_IN_RHDZSP_CX_DAY_HZ HZ
                inner JOIN
                (select distinct PRIMARY_ID  from M_BI_IN_DFNSBQ_TOP1000 where ACCT_YEAR = v_month )TT
                 on TT.PRIMARY_ID = HZ.PRIMARY_ID
                 WHERE HZ.APPLYDATE BETWEEN s_day AND s_last_day
                 AND HZ.BUDGETSUBJECTCODE LIKE 'A%'
                ) TTT
       WHERE RN = 1;

/*set v_test='select count(*) from  F_BI_IN_DFNSBQ_TOP1000 A '||' WHERE A.ACCT_MONTH ='''||v_bbq||'''';
execute v_test into d_flag;
IF d_flag>0 then
TRUNCATE table F_BI_IN_DFNSBQ_TOP1000 A;
END IF;*/
-- if the table is kudu
DELETE FROM F_BI_IN_DFNSBQ_TOP1000 A  WHERE A.ACCT_MONTH=v_bbq;


    INSERT INTO F_BI_IN_DFNSBQ_TOP1000
    SELECT V_MONTH ACCT_MONTH,
             A1.RN,
             A1.PRIMARY_ID,
             QYBC.TAXPAYNAME NSRMC,
             B1.SK_GKCODE XZQH,
             B1.ORGNAME XZQH_NAME,
             NVL(C1.CODE1, 'VVVV') HY_DM,
             NVL(C1.NAME1, '第三产业其他') HY_NAME,
             A1.B_AMT,
             A1.S_AMT,
             A1.CENT_AMT,
             A1.CITY_AMT,
             A1.COUNTY_AMT,
             A1.S_CENT_AMT,
             A1.S_CITY_AMT,
             A1.S_COUNTY_AMT
        FROM (SELECT
               RN,
               PRIMARY_ID,
               MAX(NSRMC) NSRMC,
               XZQH,
               HY_DM,
               SUM(B_AMT) B_AMT,
               SUM(S_AMT) S_AMT,
               SUM(CENT_AMT) CENT_AMT,
               SUM(CITY_AMT) CITY_AMT,
               SUM(COUNTY_AMT) COUNTY_AMT,
               SUM(S_CENT_AMT) S_CENT_AMT,
               SUM(S_CITY_AMT) S_CITY_AMT,
               SUM(S_COUNTY_AMT) S_COUNTY_AMT
               FROM (SELECT B.RN,
                             A.PRIMARY_ID,
                             MAX(A.TAXPAYNAME) NSRMC,
                             B.XZQH,
                             B.HY_DM,
                             SUM(TRAAMT) B_AMT,
                             0 S_AMT,
                             SUM(A.CENT_DIV_TRAAMT) CENT_AMT,
                             SUM(A.CITY_DIV_TRAAMT) CITY_AMT,
                             SUM(A.COUNTY_DIV_TRAAMT) COUNTY_AMT,
                             0 S_CENT_AMT,
                             0 S_CITY_AMT,
                             0 S_COUNTY_AMT
                        FROM F_BI_IN_RHDZSP_CX_DAY_HZ A
                        inner join                   
                             M_BI_IN_DFNSBQ_TOP1000   B
                             on A.PRIMARY_ID = B.PRIMARY_ID
                       WHERE A.APPLYDATE BETWEEN s_day AND s_last_day                         
                         AND B.ACCT_YEAR = v_month
                         AND A.BUDGETSUBJECTCODE LIKE 'A%'
                      GROUP BY B.RN, A.PRIMARY_ID, B.XZQH, B.HY_DM
                      UNION ALL
                      SELECT
                       B.RN,
                       A.PRIMARY_ID,
                       MAX(A.TAXPAYNAME),
                       B.XZQH,
                       B.HY_DM,
                       0 B_AMT,
                       SUM(TRAAMT) S_AMT,
                       0,
                       0,
                       0,
                       SUM(A.CENT_DIV_TRAAMT) S_CENT_AMT,
                       SUM(A.CITY_DIV_TRAAMT) S_CITY_AMT,
                       SUM(A.COUNTY_DIV_TRAAMT) S_COUNTY_AMT
                        FROM  F_BI_IN_RHDZSP_CX_DAY_HZ A
                        inner join                         
                             M_BI_IN_DFNSBQ_TOP1000   B
                        on  A.PRIMARY_ID = B.PRIMARY_ID
                       WHERE A.APPLYDATE BETWEEN s_day1 AND s_last_day1                         
                         AND B.ACCT_YEAR = v_month
                         AND A.BUDGETSUBJECTCODE LIKE 'A%'
                       GROUP BY B.RN, A.PRIMARY_ID, B.XZQH, B.HY_DM)A0
             GROUP BY RN, PRIMARY_ID, XZQH, HY_DM) A1
             right join 
             D_IN_ORGCODE_17 B1
             on  A1.XZQH = B1.SK_GKCODE
             right join
             (SELECT * FROM D_HY_QM T WHERE T.DHY_HYZT = '01') C1
             on A1.HY_DM = C1.CODE1
             inner join 
             M_BI_IN_DFNSBQ_TOP1000_QYXX QYBC
             on A1.PRIMARY_ID=QYBC.PRIMARY_ID; 




    v_retcode = 'SUCCESS';
    v_retinfo = '执行成功!';

    --CALL P_UPDATE_ETL_LOG(v_bbq,v_user,v_procname,v_table_name,v_start,SYSDATE,v_rowcount,v_retcode,v_retinfo,'');  
END IF;
EXCEPTION
  WHEN OTHERS THEN
    v_retcode = 'FAIL';
    v_retinfo = SQLERRM;
    --CALL P_UPDATE_ETL_LOG(v_bbq,v_user,v_procname,v_table_name,v_start,SYSDATE,v_rowcount,v_retcode,v_retinfo,'');

END;

 

 

 

 

 

    原文作者:date-date
    原文地址: https://blog.csdn.net/learner_up/article/details/108007504
    本文转自网络文章,转载此文章仅为分享知识,如有侵权,请联系管理员进行删除。