修改完整版的sql存储（积分兑换脚本），改写出可执行的hive truncate table "rptdev"."rpt_wjq_jf_value_dhfz_list"; commit work; message "DATEFORMAT"("NOW"(),'YYYYMMDD HH:NN:SS') || '清空底层清单表：' || @@ROWCOUNT || ' 条' type info to client; insert into "rpt_wjq_jf_value_dhfz_list"( "cust_id","cust_name","price","gift_code","gift_name" ) select "cust_id","cust_name","price","gift_code","gift_name" --into rpt_wjq_jf_value_dhfz_list from "zwfxdev"."TB_COMM_CM_INT_USE_GIF_ALL" where convert(date,"change_date") >= @month_first_date and convert(date,"change_date") <= @stat_date; commit work; message "DATEFORMAT"("NOW"(),'YYYYMMDD HH:NN:SS') || '插入底层清单表：' || @@ROWCOUNT || ' 条' type info to client; --alter table rpt_wjq_jf_value_dhfz_list add (acc_nbr varchar(21),cust_nbr varchar(30),subst_id numeric(18));commit; --更新号码、局向 update "rpt_wjq_jf_value_dhfz_list" as "a" set "a"."acc_nbr" = "b"."acc_nbr", "a"."cust_nbr" = "b"."cust_nbr", "a"."subst_id" = "b"."subst_id" from "rptdev"."rpt_comm_cm_serv_cdma" as "b" where "a"."cust_id" = "b"."cust_id"; commit work;

以下是修改后的可执行的Hive代码：

-- 清空底层清单表
truncate table rptdev.rpt_wjq_jf_value_dhfz_list;
-- 插入底层清单表
insert into rptdev.rpt_wjq_jf_value_dhfz_list(cust_id, cust_name, price, gift_code, gift_name)
select cust_id, cust_name, price, gift_code, gift_name
from zwfxdev.TB_COMM_CM_INT_USE_GIF_ALL
where cast(change_date as date) >= '${month_first_date}' and cast(change_date as date) <= '${stat_date}';

-- 更新号码、局向
add columns if not exists (acc_nbr string, cust_nbr string, subst_id decimal(18));
update rptdev.rpt_wjq_jf_value_dhfz_list as a
set acc_nbr = b.acc_nbr, cust_nbr = b.cust_nbr, subst_id = b.subst_id
from rptdev.rpt_comm_cm_serv_cdma as b
where a.cust_id = b.cust_id;

相关问题

将代码转换成hive语法 update rpt_wjq_jf_value_dhfz_list as a set a.acc_nbr = b.acc_nbr, a.cust_nbr = b.cust_nbr, a.subst_id = b.subst_id from rptdev.rpt_comm_cm_serv_cdma as b where a.cust_id = b.cust_id;

在Hive中，不支持Update语法，但可以使用INSERT OVERWRITE语句进行更新操作。以下是将代码转换为Hive语法的示例：

INSERT OVERWRITE TABLE rpt_wjq_jf_value_dhfz_list
SELECT b.acc_nbr, b.cust_nbr, b.subst_id, a.*
FROM rpt_wjq_jf_value_dhfz_list a
JOIN rptdev.rpt_comm_cm_serv_cdma b ON a.cust_id = b.cust_id;

该语句将会把rpt_wjq_jf_value_dhfz_list表中的a.acc_nbr, a.cust_nbr, a.subst_id字段更新为rptdev.rpt_comm_cm_serv_cdma表中的b.acc_nbr, b.cust_nbr, b.subst_id字段。JOIN条件为a.cust_id = b.cust_id。

将下面代码改写成hive create procedure "rptdev"."sp_wjq_jf_value_dhfz_show"( in @data_date integer default cast("dateformat"("now"()-1,'yyyymmdd') as integer), in @is_run_flag1 integer default 1, in @is_run_flag2 integer default 10 ) on exception resume begin declare @stat_date varchar(8); --统计日期 declare @month_id integer; --统计月份 declare @month_first_date varchar(8); --统计月的第一天 set @stat_date = convert(varchar(8),@data_date); set @month_id = @data_date/100; set @month_first_date = "dateformat"(@data_date,'yyyymm01'); --统计月的第一天 truncate table "rptdev"."rpt_wjq_jf_value_dhfz_list"; insert into "rpt_wjq_jf_value_dhfz_list"( "cust_id","cust_name","price","gift_code","gift_name" ) select "cust_id","cust_name","price","gift_code","gift_name" --into rpt_wjq_jf_value_dhfz_list from "zwfxdev"."TB_COMM_CM_INT_USE_GIF_ALL" where convert(date,"change_date") >= @month_first_date and convert(date,"change_date") <= @stat_date; end

下面是改写成Hive存储过程的代码：

CREATE PROCEDURE `rptdev`.`sp_wjq_jf_value_dhfz_show`(
    IN data_date INT,
    IN is_run_flag1 INT DEFAULT 1,
    IN is_run_flag2 INT DEFAULT 10
)
BEGIN
    DECLARE stat_date STRING; --统计日期
    DECLARE month_id INT; --统计月份
    DECLARE month_first_date STRING; --统计月的第一天
    SET stat_date = CAST(data_date AS STRING);
    SET month_id = data_date / 100;
    SET month_first_date = DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP(CAST(month_id AS STRING), 'yyyyMM')), 'yyyyMMdd'); --统计月的第一天
    TRUNCATE TABLE rptdev.rpt_wjq_jf_value_dhfz_list;
    INSERT INTO TABLE rptdev.rpt_wjq_jf_value_dhfz_list(
        cust_id,
        cust_name,
        price,
        gift_code,
        gift_name
    )
    SELECT
        cust_id,
        cust_name,
        price,
        gift_code,
        gift_name
    FROM zwfxdev.TB_COMM_CM_INT_USE_GIF_ALL
    WHERE CAST(change_date AS DATE) >= CAST(month_first_date AS DATE)
        AND CAST(change_date AS DATE) <= CAST(stat_date AS DATE);
END;

需要注意的是，Hive中不支持在存储过程中使用异常处理语句，因此将原来的 `ON EXCEPTION RESUME` 去掉了。此外，Hive中也不支持在存储过程中使用 `TRUNCATE TABLE` 语句，因此将其改为了 `TRUNCATE TABLE ...` 的形式。还有一个需要注意的地方是，Hive中的日期格式化函数与SQL Server中的略有不同，因此将 `DATEFORMAT` 改为了 `DATE_FORMAT`。