阿里云DataWorks数据集成OSS数据源行分隔符\n常用SQL

-- dwide存量数据查询check
select count(*) from file
where file_type= 23
and content like '%"lineDelimiter":"\\\\\\\\n"%' 
and content like '%"stepType":"oss"%'
and app_id in ('10001', '10002')
and file_delete=0
and file_name='test_2';

-- dwide单个项目空间修改
update file
set content = REPLACE(`content`, '"lineDelimiter":"\\\\n"', '"lineDelimite r":"\\n"')
where file_type= 23
and content like '%"lineDelimiter":"\\\\\\\\n"%'
and content like '%"stepType":"oss"%'
and file_delete=0
and file_name='test_2'
and app_id = '10033';

-- dwtsp存量数据查询check
select
b.file_content, b.file_content_md5, b.file_id as file_id,
b.file_version as file_version
from dw_file_ext a inner join dw_file_version b
on a.file_id = b.file_id   and a.version = b.file_version 
where a.task_type = 23
and file_content like '%"lineDelimiter":"\\\\\\\\n"%'
and file_content like '%"stepType":"oss"%'
and a.app_id='10080'
and a.file_name='test_2'
limit 2 \G;

select count(1)
from dw_file_ext a inner join dw_file_version b
on a.file_id = b.file_id   and a.version = b.file_version 
where a.task_type = 23
and file_content like '%"lineDelimiter":"\\\\\\\\n"%'
and file_content like '%"stepType":"oss"%'
and a.app_id='10080'
and a.file_name='test_2';

说明：dw_file_version是保存了代码的所有版本信息，是数据开发的库表需要修改。

-- dwtsp单个项目空间订正  
create table dw_file_version_context_tmp as select b.id, a.app_id, a.fi le_id, a.version from dw_file_ext a inner join dw_file_version b on a.file _id = b.file_id  and a.version = b.file_version where a.task_type = 23;

update
dw_file_version a RIGHT JOIN dw_file_version_context_tmp b on a.id=b.id
set a.file_content = REPLACE(a.`file_content`, '"lineDelimiter":"\\\\n"', '"lineDelimiter":"\\n"'),
a.file_content_md5 = md5(REPLACE(a.`file_content`, '"lineDelimiter":"\\\\n"', '"lineDelimiter":"\\n"'))
WHERE a.file_content like '%"lineDelimiter":"\\\\\\\\n"%' 
and a.file_content like '%"stepType":"oss"%'
and app_id='10080';


select count(*) from 
dw_file_version a RIGHT JOIN dw_file_version_context_tmp b on a.id=b.id
WHERE a.file_content like '%"lineDelimiter":"\\\\\\\\n"%' 
and a.file_content like '%"stepType":"oss"%'
and app_id='10080'
and a.file_name='test_2';