Hive/Inceptor SQL之工作记录

1、修改字段类型及长度

alter table t_user change column id id decimal(38,0);

2、事务表删除重复记录

-- 获取
SELECT cst_no,count(1)
FROM tb_user
WHERE batch_date='2021-11-24'
GROUP BY cst_no
HAVING count(1)>1;
-- 删除
DELETE FROM (
SELECT row_number() over(PARTITION BY cst_no) rn , *
FROM tb_user
WHERE batch_date='2021-11-24'
AND cst_no='58F291604B29DC8C40B2BD5FDF3B3377'
) tmp
WHERE rn > 1
;

3、inceptor之存储过程

CREATE OR REPLACE PACKAGE MYDB.BRAINS IS
     PROCEDURE PROC_STD_DELETE_TRANS(IN_SCHEMA_NAME IN STRING, OC_STATUS OUT STRING);
END;
/

CREATE OR REPLACE PACKAGE BODY MYDB.BRAINS IS
    PROCEDURE PROC_STD_DELETE_TRANS
      (IN_SCHEMA_NAME IN STRING
       , OC_STATUS OUT STRING) IS
          C_SQL STRING;
     BEGIN
	     BEGIN TRANSACTION;
          FOR AA IN (Select DISTINCT data_date From bp_std_trans_fd_tmp_del ORDER BY data_date) LOOP
          	C_SQL := 'DELETE FROM bp_std_trans_fd
					WHERE data_date ="'||AA.data_date||'" AND trxserno IN ( SELECT trxserno FROM bp_std_trans_fd_tmp);'
               EXECUTE IMMEDIATE C_SQL;
          END LOOP;
         COMMIT;
     END;
END;

调用方法：

set transaction.type= inceptor;
DECLARE
    IN_SCHEMA_NAME STRING;
    OC_STATUS STRING;
BEGIN
    MYDB.BRAINS.PROC_STD_DELETE_TRANS(IN_SCHEMA_NAME,OC_STATUS)
END;