数仓搬迁:从方法到实践,带你解决数据一致性对比
摘要:数据仓库建设过程中,总是会涉及到不同平台、同一平台物理环境搬迁,由于数据仓库数据量庞大,往往数据搬迁不可能在一个短周期内完成,会涉及数据同步、校验、追批并跑、再校验过程。
本文主要阐述数据校验过程设计,供相关人员借鉴。
数据一致性校验最核心内容项
校验手段
根据常见数据存储类型,主要完成对象级记录一致性校验,字段级数值、时间、字符类型的数据一致性校验,即常规提到的表级count、字段级数值的sum、时间类型差值sum、字符类型的checksum。
下文以TD搬迁GaussDB(DWS)为例,
1. 记录数
a) 表级count(*)
2. 数据类型
a) 汇总Sum(cola)
b) 平均Avg(cola)
c) 例外,针对float类型这种存在精度缺失场景,或直接不校验,或比对两个平台差异小于5%范围即可接受;
d) 注意事项,不同平台可能存在sum放大小数点位数问题,可通过指定小数点位置,让两个平台数值完全一致;
3. 时间类型
a) 汇总Sum(时间转换数字)
b) Date类型实现样式
i. DWS:select extract(epoch from '20200819'::date);
ii. TD:sel (date'2020-08-19'-date'1970-01-01')*86400;
c) Time类型实现样式
i. DWS:select extract(epoch from '12:12:12'::time);
ii. TD:sel extract(hour from time'12:12:12')*3600+extract(minute from time'12:12:12')*60+extract(second from time'12:12:12');
d) TimeStamp类型实现
i. DWS:select extract(epoch from '2020-08-19 12:12:12'::timestamp);
ii. TD:sel ( (timestamp'2020-08-19 12:12:12'(date))-date'1970-01-01')*86400+extract(hour from timestamp'2020-08-19 12:12:12')*3600+extract(minute from timestamp'2020-08-19 12:12:12')*60+extract(second from timestamp'2020-08-19 12:12:12');
e) 即将两个平台将采用同一周期进行时间类型汇总,保证数据求和一致性;
4. 字符类型
a) 整体思路是将字符串在同一字符集条件下计算MD5值,再进行MD5串切分、异或(XOR),得到相对较小数值,再进行数值sum求和;
注,若两个平台均支持自定义聚合函数,可以直接进行每个字符串MD5值计算,再进行全部MD5值异或操作,得到最终值,用于识别数据一致性;
b) DWS实现逻辑
创建自定义函数,
Create or replace function xor_md5(text) returns bigint immutable as $$
select ('x'||lpad(substr(md5($1),1,8),16,'0'))::bit(64)::bigint
# ('x'||lpad(substr(md5($1),9,8),16,'0'))::bit(64)::bigint
# ('x'||lpad(substr(md5($1),17,8),16,'0'))::bit(64)::bigint
# ('x'||lpad(substr(md5($1),25,8),16,'0'))::bit(64)::bigint;
$$ language sql;
create function xor_md5_latin(text) returns bigint immutable as $$
select ('x'||lpad(substr(md5(convert($1,'GB18030')),1,8),16,'0'))::bit(64)::bigint
# ('x'||lpad(substr(md5(convert($1,'GB18030')),9,8),16,'0'))::bit(64)::bigint
# ('x'||lpad(substr(md5(convert($1,'GB18030')),17,8),16,'0'))::bit(64)::bigint
# ('x'||lpad(substr(md5(convert($1,'GB18030')),25,8),16,'0'))::bit(64)::bigint;
$$ language sql;
c) TD实现逻辑
i. 方式一:创建SQL自定义函数
replace function xor_md5(inpt varchar(32000))
returns bigint
language sql
deterministic contains sql
sql SECURITY DEFINER
collation INVOKER
INLINE TYPE 1
return
bitxor( bitxor ( bitxor (
cast(from_bytes(to_bytes(
translate(substr(hash_md5(udf_utf16to8(udf_w936to16(inpt,''))),1,8)
using latin_to_unicode),'base16'),'base10') as bigint)
,cast(from_bytes(to_bytes(
translate(substr(hash_md5(udf_utf16to8(udf_w936to16(inpt,''))),9,8)
using latin_to_unicode),'base16'),'base10') as bigint))
,cast(from_bytes(to_bytes(
translate(substr(hash_md5(udf_utf16to8(udf_w936to16(inpt,''))),17,8)
using latin_to_unicode),'base16'),'base10') as bigint))
,cast(from_bytes(to_bytes(
translate(substr(hash_md5(udf_utf16to8(udf_w936to16(inpt,''))),25,8)
using latin_to_unicode),'base16'),'base10') as bigint));
注,TD通常实践中以Latin存储GBK中文数据,而GaussDB(DWS)一般推荐采用主流UTF8字符集存储数据,则双方进行字符md5计算时,需转换成同一个字符集,其中涉及hash_md5、udf_utf16to8、udf_w936to16三个非TD内置函数,需要https://downloads.teradata.com/查找下载
ii. 方式二:创建C自定义函数
replace function xor_md5_latin ( arg varchar(32000) character set latin)
returns bigint
language c
no sql
external name 'ci:md5:md5.h:cs:xor_md5:xor_md5_latin.c:cs:md5:md5.c:F:xor_md5'
parameter style td_general;
replace function xor_md5(inpt varchar(32000))
returns bigint
language sql
deterministic contains sql
sql SECURITY DEFINER
collation INVOKER
INLINE TYPE 1
return
xor_md5_latin(udf_utf16to8(udf_w936to16(inpt,'')));
C代码若有需要私聊留言
d) 测试数据如下
i. TD执行情况
Select xor_md5(‘a’);
------------------------
2491710610
Select xor_md5_latin(‘中国’);
------------------------
66934228
Select xor_md5(‘中国’);
------------------------
1004201695
ii. GS执行情况
Select xor_md5(‘a’);
------------------------
2491710610
Select xor_md5_latin(‘中国’);
------------------------
66934228
Select xor_md5(‘中国’);
------------------------
1004201695
校验数据范围
根据数据仓库数据特性,进行切片粒度数据收集,除记录数统计之外,其它均以字段级粒度收集数据,即一个切片一个字段产生一条记录信息。
注:针对VA类型,存在多日流水数据发生变化场景,将最新变化流水数据打包成一个切片进行校验;
通过以上切片粒度,实现数据增量校验;而每隔一个周期,再进行全量校验,保障数据完整、一致;
校验方式
根据数据仓库特性,可以分为常规批量校验和自定义校验;
通常,可以将校验嵌入批量过程中,即对应作业的数据表发生变化后,直接触发相关数据表校验,这样实现数据准实时校验,保证两个平台并跑期间数据一致性;另外,在确保两个平台数据稳定前提下,可以固定时间段或用户直接发起实时校验,用于核对平台一致性;
校验策略
可进行数据表分级分类,将有限校验计算资源投入到数据一致性校验中,可参照以下分类,
自动化服务
以上阐述了相关实现的方法论,最后再探讨一下如何实践;
自动化校验框架,建议常驻rest服务,用于接收客户端触发的校验请求,分发各数据平台执行校验采集工作,利用rest服务实现跨服务器交互问题;采用一个portal展现界面,用于用户录入自定义校验规则以及查看数据校验情况。
相关文章
- PHP导出数据到CSV文件函数/方法
- 使用Visual Studio将C#生成DLL文件的方法
- 数据分析师:避免低质量数据的5个方法
- java hibernate Criteria 删除数据 delete data 2种方法
- 数据分页 THINKPHP3.2 分页 三种分页方法
- Windows操作系统远程Linux服务器传输文件方法(以EasyDSS云平台、EasyNVR上传部署为例)
- A Baseline for 3D Multi-odject Tracking:多目标跟踪方法
- SpringBoot配置在应用启动后立即执行某些方法代码案例
- Oracle数据库备份与恢复的三种方法
- OLEDB Excel 与C# 的数据流通方法
- SAP CRM产品主数据页面的ON_NEW_FOCUS方法设计
- Atitit 非结构化数据管理法 目录 1. 什么是非结构化数据?1 2. 对非结构化数据也即对全文数据的搜索主要有两种方法:2 2.1. 顺序扫描法(Serial Scanning):2 2
- 2021前端常见面试题必考必问:JS中null和undefined与NaN的判断方法和区别?
- 线程同步方法和差别~(高并发中多个线程访问统一资源域,容易出现线程安全性)
- Dataset:White Wine Quality白葡萄酒品质数据集的简介、下载、使用方法之详细攻略
- ML之FE:特征工程中常用的五大数据集划分方法(留1法/留p法、随机划分法、K折交叉验证法、自定义分割法、时间序列数据分割法)全部代码实现
- sklearn:sklearn.feature_selection的SelectFromModel函数的简介、使用方法之详细攻略
- Python之pandas:将dict字典格式数据保存为dataframe格式数据的几种方法
- Python 制作进度条,原来有这么多方法!
- C语言给结构体赋数据值和带有结构体指针变量的赋值方法
- 3_使用seurat sct方法中的reference based处理大数据超过100000个细胞 science advance
- C# 几种方法来复制的阵列
- 向子进程传递大量数据的方法
- Python爬虫入门之爬虫解析提取数据的四种方法
- WPF / Win Form:多线程去修改或访问UI线程数据的方法( winform 跨线程访问UI控件 )
- 通过GAN绕过基于机器学习的IDS检测系统,IDSGAN(也是对IDS ML检测算法进行绕过,数据集使用NSL-KDD,DoS、U2R、R2L三种攻击)——也有最新防御的方法
- 机器学习 不均衡数据的处理方法