sed -i 修改脚本内容
sed -i 修改脚本内容
今天工作中遇到需要将上线的表进行初始化刷一遍全量数据的问题
但是sql脚本已经上线了，不能手动修改了（没权限）.
所以今天学了一个新命令可以让有权限的同事帮我执行一下，然后等我跑批刷数据之后再恢复过来
sed -i '80s/^/--/' /home/etl/app/sql/app_api_sec_pre_ipo_situation_di.sql
sed 命令是一个面向行处理的工具，它以“行”为处理单位，针对每一行进行处理，处理后的结果会输出到标准输出（STDOUT）
sed -i:插入， i 的后面可以接字串，而这些字串会在新的一行出现(目前的上一行)80 : /home/etl/app/sql/app_api_sec_pre_ipo_situation_di.sql 该文件的第80行
s/^/--/ ：vim编辑器中的命令
s/被替换的/替换成/^表示一行的开头（需要被替换的）、-- （被替换成的）/home/etl/app/sql/app_api_sec_pre_ipo_situation_di.sql ：需要被操作的文件路径
该命令修改的文件可以通过sed -i '80s/^--//' /home/etl/app/sql/app_api_sec_pre_ipo_situation_di.sql 命令恢复
下面看一下该命令的效果：
/home/etl/app/sql/app_api_sec_pre_ipo_situation_di.sql
use fin_dw;
--将过去第7天的数据插入到历史分区
insert into table app_api_sec_pre_ipo_situation_di partition (dt,data_status)
select serial_no,
formatted_ent_name,
ent_name,
ent_code,
addr,
ind,
guar_inst,
guar_per,
acct_firm,
sign_acct,
lawyer_firm,
sign_lawyer,
list_status,
is_submit_fin_rpt,
end_date,
pre_list_market,
remark1,
fin_type,
remark2,
update_date,
dw_ins_date,
query_org_id,
'his' as dt,
data_status
from app_api_sec_pre_ipo_situation_di
where dt >=concat(from_unixtime(unix_timestamp(days_sub(now(), 7)), 'yyyyMMdd'),'0000')
and dt <=concat(from_unixtime(unix_timestamp(days_sub(now(), 7)), 'yyyyMMdd'),'2359');
--删除过去第7天的数据
alter table app_api_sec_pre_ipo_situation_di drop partition(dt between concat(from_unixtime(unix_timestamp(days_sub(now(),7)),'yyyyMMdd'),'0000') and concat(from_unixtime(unix_timestamp(days_sub(now(),7)),'yyyyMMdd'),'2359'));
--拿目标表中最新数据插入临时表
insert overwrite table t_app_api_sec_pre_ipo_situation_di_01
select tmp.ent_name,tmp.ent_code,tmp.guar_inst,tmp.acct_firm,tmp.lawyer_firm,tmp.list_status,tmp.update_date,tmp.query_org_id,tmp.pre_list_market
from (select ent_name,ent_code,guar_inst,acct_firm,lawyer_firm,list_status,update_date,query_org_id,pre_list_market,
row_number() over(partition by ent_code,guar_inst,acct_firm,lawyer_firm,list_status,query_org_id,pre_list_market order by nvl(update_date,'1990-01-01') desc) as rn
from app_api_sec_pre_ipo_situation_di ) tmp
where tmp.rn=1;
compute stats t_app_api_sec_pre_ipo_situation_di_01;
--将新增数据插入到目标表
insert overwrite app_api_sec_pre_ipo_situation_di partition (dt,data_status)
select getmd5(concat(nvl(a.ent_code,''),nvl(a.guar_inst,''),nvl(a.acct_firm,''),nvl(a.lawyer_firm,''),nvl(a.list_status,''),nvl(a.pre_list_market,''))) as serial_no,
a.formatted_ent_name,
a.ent_name,
a.ent_code,
a.addr,
a.ind,
a.guar_inst,
a.guar_per,
a.acct_firm,
a.sign_acct,
a.lawyer_firm,
a.sign_lawyer,
a.list_status,
a.is_submit_fin_rpt,
a.end_date,
a.pre_list_market,
a.remark1,
a.fin_type,
a.remark2,
a.update_date,
from_unixtime(unix_timestamp(now())) as dw_ins_date,
a.bankid,
from_unixtime(unix_timestamp(now()),'yyyyMMddHHmm') as dt,
case when a.data_status='1' then 'D'
when b.ent_name is null then 'I'
when b.ent_name is not null then 'U' end as data_status
from (
select a1.*,a2.bankid
from(
select *
from dwd_sec_pre_ipo_situation_dd
where dt = regexp_replace(to_date(subdate(now(), 1)), '-', '')
and update_date>=to_timestamp(to_date(subdate(now(), 3)),'yyyy-MM-dd') --为了防止丢数据，拿近三天更新时间的数据进行增存量判断
) a1 cross join (
select distinct bankid
from fin_dw.interface_account_info where interfaceno = 'B4G13'
) a2
) a
left join t_app_api_sec_pre_ipo_situation_di_01 b
on a.ent_code=b.ent_code and a.guar_inst=b.guar_inst and a.acct_firm=b.acct_firm and a.lawyer_firm=b.lawyer_firm and a.list_status=b.list_status and a.bankid = b.query_org_id and a.pre_list_market = b.pre_list_market
where b.ent_name is null or (b.ent_name is not null and a.update_date > b.update_date);
使用sed -i '80s/^/--/' /home/etl/app/sql/app_api_sec_pre_ipo_situation_di.sql命令修改之后：
use fin_dw;
--将过去第7天的数据插入到历史分区
insert into table app_api_sec_pre_ipo_situation_di partition (dt,data_status)
select serial_no,
formatted_ent_name,
ent_name,
ent_code,
addr,
ind,
guar_inst,
guar_per,
acct_firm,
sign_acct,
lawyer_firm,
sign_lawyer,
list_status,
is_submit_fin_rpt,
end_date,
pre_list_market,
remark1,
fin_type,
remark2,
update_date,
dw_ins_date,
query_org_id,
'his' as dt,
data_status
from app_api_sec_pre_ipo_situation_di
where dt >=concat(from_unixtime(unix_timestamp(days_sub(now(), 7)), 'yyyyMMdd'),'0000')
and dt <=concat(from_unixtime(unix_timestamp(days_sub(now(), 7)), 'yyyyMMdd'),'2359');
--删除过去第7天的数据
alter table app_api_sec_pre_ipo_situation_di drop partition(dt between concat(from_unixtime(unix_timestamp(days_sub(now(),7)),'yyyyMMdd'),'0000') and concat(from_unixtime(unix_timestamp(days_sub(now(),7)),'yyyyMMdd'),'2359'));
--拿目标表中最新数据插入临时表
insert overwrite table t_app_api_sec_pre_ipo_situation_di_01
select tmp.ent_name,tmp.ent_code,tmp.guar_inst,tmp.acct_firm,tmp.lawyer_firm,tmp.list_status,tmp.update_date,tmp.query_org_id,tmp.pre_list_market
from (select ent_name,ent_code,guar_inst,acct_firm,lawyer_firm,list_status,update_date,query_org_id,pre_list_market,
row_number() over(partition by ent_code,guar_inst,acct_firm,lawyer_firm,list_status,query_org_id,pre_list_market order by nvl(update_date,'1990-01-01') desc) as rn
from app_api_sec_pre_ipo_situation_di ) tmp
where tmp.rn=1;
compute stats t_app_api_sec_pre_ipo_situation_di_01;
--将新增数据插入到目标表
insert overwrite app_api_sec_pre_ipo_situation_di partition (dt,data_status)
select getmd5(concat(nvl(a.ent_code,''),nvl(a.guar_inst,''),nvl(a.acct_firm,''),nvl(a.lawyer_firm,''),nvl(a.list_status,''),nvl(a.pre_list_market,''))) as serial_no,
a.formatted_ent_name,
a.ent_name,
a.ent_code,
a.addr,
a.ind,
a.guar_inst,
a.guar_per,
a.acct_firm,
a.sign_acct,
a.lawyer_firm,
a.sign_lawyer,
a.list_status,
a.is_submit_fin_rpt,
a.end_date,
a.pre_list_market,
a.remark1,
a.fin_type,
a.remark2,
a.update_date,
from_unixtime(unix_timestamp(now())) as dw_ins_date,
a.bankid,
from_unixtime(unix_timestamp(now()),'yyyyMMddHHmm') as dt,
case when a.data_status='1' then 'D'
when b.ent_name is null then 'I'
when b.ent_name is not null then 'U' end as data_status
from (
select a1.*,a2.bankid
from(
select *
from dwd_sec_pre_ipo_situation_dd
where dt = regexp_replace(to_date(subdate(now(), 1)), '-', '')
-- and update_date>=to_timestamp(to_date(subdate(now(), 3)),'yyyy-MM-dd') --为了防止丢数据，拿近三天更新时间的数据进行增存量判断
) a1 cross join (
select distinct bankid
from fin_dw.interface_account_info where interfaceno = 'B4G13'
) a2
) a
left join t_app_api_sec_pre_ipo_situation_di_01 b
on a.ent_code=b.ent_code and a.guar_inst=b.guar_inst and a.acct_firm=b.acct_firm and a.lawyer_firm=b.lawyer_firm and a.list_status=b.list_status and a.bankid = b.query_org_id and a.pre_list_market = b.pre_list_market
where b.ent_name is null or (b.ent_name is not null and a.update_date > b.update_date);
使用sed -i '80s/^--//' /home/etl/app/sql/app_api_sec_pre_ipo_situation_di.sql命令恢复后：
use fin_dw;
--将过去第7天的数据插入到历史分区
insert into table app_api_sec_pre_ipo_situation_di partition (dt,data_status)
select serial_no,
formatted_ent_name,
ent_name,
ent_code,
addr,
ind,
guar_inst,
guar_per,
acct_firm,
sign_acct,
lawyer_firm,
sign_lawyer,
list_status,
is_submit_fin_rpt,
end_date,
pre_list_market,
remark1,
fin_type,
remark2,
update_date,
dw_ins_date,
query_org_id,
'his' as dt,
data_status
from app_api_sec_pre_ipo_situation_di
where dt >=concat(from_unixtime(unix_timestamp(days_sub(now(), 7)), 'yyyyMMdd'),'0000')
and dt <=concat(from_unixtime(unix_timestamp(days_sub(now(), 7)), 'yyyyMMdd'),'2359');
--删除过去第7天的数据
alter table app_api_sec_pre_ipo_situation_di drop partition(dt between concat(from_unixtime(unix_timestamp(days_sub(now(),7)),'yyyyMMdd'),'0000') and concat(from_unixtime(unix_timestamp(days_sub(now(),7)),'yyyyMMdd'),'2359'));
--拿目标表中最新数据插入临时表
insert overwrite table t_app_api_sec_pre_ipo_situation_di_01
select tmp.ent_name,tmp.ent_code,tmp.guar_inst,tmp.acct_firm,tmp.lawyer_firm,tmp.list_status,tmp.update_date,tmp.query_org_id,tmp.pre_list_market
from (select ent_name,ent_code,guar_inst,acct_firm,lawyer_firm,list_status,update_date,query_org_id,pre_list_market,
row_number() over(partition by ent_code,guar_inst,acct_firm,lawyer_firm,list_status,query_org_id,pre_list_market order by nvl(update_date,'1990-01-01') desc) as rn
from app_api_sec_pre_ipo_situation_di ) tmp
where tmp.rn=1;
compute stats t_app_api_sec_pre_ipo_situation_di_01;
--将新增数据插入到目标表
insert overwrite app_api_sec_pre_ipo_situation_di partition (dt,data_status)
select getmd5(concat(nvl(a.ent_code,''),nvl(a.guar_inst,''),nvl(a.acct_firm,''),nvl(a.lawyer_firm,''),nvl(a.list_status,''),nvl(a.pre_list_market,''))) as serial_no,
a.formatted_ent_name,
a.ent_name,
a.ent_code,
a.addr,
a.ind,
a.guar_inst,
a.guar_per,
a.acct_firm,
a.sign_acct,
a.lawyer_firm,
a.sign_lawyer,
a.list_status,
a.is_submit_fin_rpt,
a.end_date,
a.pre_list_market,
a.remark1,
a.fin_type,
a.remark2,
a.update_date,
from_unixtime(unix_timestamp(now())) as dw_ins_date,
a.bankid,
from_unixtime(unix_timestamp(now()),'yyyyMMddHHmm') as dt,
case when a.data_status='1' then 'D'
when b.ent_name is null then 'I'
when b.ent_name is not null then 'U' end as data_status
from (
select a1.*,a2.bankid
from(
select *
from dwd_sec_pre_ipo_situation_dd
where dt = regexp_replace(to_date(subdate(now(), 1)), '-', '')
and update_date>=to_timestamp(to_date(subdate(now(), 3)),'yyyy-MM-dd') --为了防止丢数据，拿近三天更新时间的数据进行增存量判断
) a1 cross join (
select distinct bankid
from fin_dw.interface_account_info where interfaceno = 'B4G13'
) a2
) a
left join t_app_api_sec_pre_ipo_situation_di_01 b
on a.ent_code=b.ent_code and a.guar_inst=b.guar_inst and a.acct_firm=b.acct_firm and a.lawyer_firm=b.lawyer_firm and a.list_status=b.list_status and a.bankid = b.query_org_id and a.pre_list_market = b.pre_list_market
where b.ent_name is null or (b.ent_name is not null and a.update_date > b.update_date);
浙公网安备 33010602011771号