hive数仓-数据的质量管理

版本20231116 要理解数据的质量管理，应具备hive数据仓库的相关知识

1.理解什么是数据的质量管理：

数据的质量管理，表现保障在数据的健康性，即满足消费者期望程度，体现在他们对数据的使用预期，只有达到预期才能满足决策层的参考。

大数据大而价值密度低，在有效信息数据挖掘上，可能会出现错误，在这个基础上，分析师会对数据有一个预期分数，如果他觉得数据的准确率在百分之八十以上就算是满足需求。
我们对数据的质量控制达到了这个标准，就算达到了数据的质量要求。

2.数据质量管理的规划

数据的质量管理作为数据仓库的一个重要模快，主要可以分为数据的健康标准量化，监控和保障。

数据质量标准的分类

1）数据的完整性
数据不存在大量的缺失值，不缺少某一日期的/部门、地点等部分维度的数据。随时间的推移，数据量符合正常的趋势

2）数据的一致性
数据各层的数据应与上层保持一致，经过elt处理后的宽表和指标能与数据源保持一致

3）数据的不重复性
同一个数据集当中统一个数据不能出现多次，不能出现数据的大量冗余保证数据的唯一性

3.数据质量管理解决方案

使用shell命令和hive脚本的方式，通过验证增量数据的记录数，全表空值记录数，全表空值记录数，全表记录数是否在合理的范围之内，以及验证数据来源表和目标表的一致性，确定当日的数据是否符合健康标准，达到数据质量的检测和管理。

1.ods层的数据质量校验

1）首先在hive上建立一个仓库，添加数据质量监控表

create database datacheck;
1

2）然后建立检查检查表

create table datacheck.table_count_add_standard(
data_date string comment '数据时间分区',
database_name string comment '库名',
table_name string comment '表名',
table_type string comment '表类型（增量/全量）'
add_count bigint comment '当日增量的数据数',
null_count bigint comment '表空值记录数',
duplicate_count bigint comment '表重复值记录数',
total_count bigint comment '全表记录数'
)
1
2
3
4
5
6
7
8
9
10
11

3）创建数据检验曾量表通用的脚本

[lyc@hadoop102 check]$ vim increment_data_check_public.sh
1

#!/bin/bash
#增量数据所在的日期分区
check_date=$1
#校验数据的表名
table_name=$2
#需要校验控制的列名，以‘，’号隔开
null_column=$3
#初始化sql查询语句
null_where_sql_str=''
#将控制检验字符切割成列名，数组
array=({null_column//,/})
#遍历数组，拼接空值查询条件
for(( i=0;i<${#array[@]};i++)) do
	if [ $i -eq 0 ];then
		null_where_sql_str=" where ${array[i]} is null "
	else
		null_where_sql_str="$null_where_sql_str or ${array[i]} is null"
	fi
done
#执行当日增量数据记录数量sql查询语句; gmall 为数据仓库所在的库名
add_count_quary_result=`hive -e "select count(*) from gmall.$table_name where dt='$check_date'"`
#取出当日增量数据记录数量
add_count=${add_count_quary_result:3}
#执行当日全表数据记录数量的查询sql查询语句
total_count_quary=`hive -e "select count(*) from gmall.$table_name"`
#取出当日全量数据的记录数量
total_count=${total_count_quary:3}
#执行全表空值数据记录数sql查询语句
table_null_quary_result=`hive -e "select  count(*) from gmail.$table_name$null_where_sql_str"`
#取出全表空值数据记录数量
null_count=${table_null_quary_result:3}
#执行全表重复值的记录数量sql查询语句
table_duplicate_quary_result=`hive -e "select sum(tmp.duplicate_count) as duplicate_sum from (select count(*) as duplicate_count  from gmall.$table_name group by $null_column having count(*)>1) as tmp"`
#取出全表重复值的数据记录数量
duplicate_count=${table_duplicate_quary_result:3}

#将所有的数据检验结果插入表中
hive -e "insert into datacheck.table_count_add_standard values ('$check_date','gmall','$table_name','increment_table','$add_count','$null_count','duplicate_count','total_count')"
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

脚本参数注释：
第一个参数：传入时间分区参数(dt)
第二个参数：需要进行数据校验的表名(table_name)
第三个参数：为查询的字段名,用“,”号隔开。如 cl1,cl2,cl3

4）创建数据检测全量表的脚本

全量表的脚本相比于增量表的区别在于，不需要计算增量表的结果

[lyc@hadoop102 check]$ vim increment_data_check_public.sh
1

#!/bin/bash
#全量数据所在的日期
check_date=$1
#校验数据的表名
table_name=$2
#需要校验控制的列名，以‘，’号隔开
null_column=$3
#初始化sql查询语句
null_where_sql_str=''
#将控制检验字符切割成列名，数组
array=({null_column//,/})
#遍历数组，拼接空值查询条件
for(( i=0;i<${#array[@]};i++)) do
	if [ $i -eq 0 ];then
		null_where_sql_str=" where ${array[i]} is null "
	else
		null_where_sql_str="$null_where_sql_str or ${array[i]} is null"
	fi
done
#执行当日增量数据记录数量sql查询语句; gmall 为数据仓库所在的库名
#add_count_quary_result=`hive -e "select count(*) from gmall.$table_name where dt='$check_date'"`
#取出当日增量数据记录数量
#add_count=${add_count_quary_result:3}
#执行当日全表数据记录数量的查询sql查询语句
total_count_quary=`hive -e "select count(*) from gmall.$table_name"`
#取出当日全量数据的记录数量
total_count=${total_count_quary:3}
#执行全表空值数据记录数sql查询语句
table_null_quary_result=`hive -e "select  count(*) from gmail.$table_name$null_where_sql_str"`
#取出全表空值数据记录数量
null_count=${table_null_quary_result:3}
#执行全表重复值的记录数量sql查询语句
table_duplicate_quary_result=`hive -e "select sum(tmp.duplicate_count) as duplicate_sum from (select count(*) as duplicate_count  from gmall.$table_name group by $null_column having count(*)>1) as tmp"`
#取出全表重复值的数据记录数量
duplicate_count=${table_duplicate_quary_result:3}

#将所有的数据检验结果插入表中
hive -e "insert into datacheck.table_count_add_standard values ('$check_date','gmall','$table_name','total_table',null,'$null_count','duplicate_count','total_count')"
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

5）脚本的运行

单独一张表的脚本运行

[lyc@hadoop102 check]$ total_data_check_public.sh 2023-06-14 activity_rule id,activity_id
1

如果表多可以使用以下脚本

vim ods_data_check.sh
1

#!/bin/bash
check_date=$1

/opt/module/check/total_data_check_public.sh $check_date ods_user_info  id,name,birthday,email

 /opt/module/check/total_data_check_public.sh $check_date ods_order_status_log  id,order_id,order_status,operater_time
1
2
3
4
5
6

2.dwd层的数据质量校验

dwd校验数据于ods需要考虑的差不多，主要增加了相较于ods数据的一致性，同时因为我们在ods层已经校验了数据的空值，所以只要保持数据的一致性，就可以从而验证控制值率

1）建立dwd层校验表

create table datacheck.dwd_table_data_check(
data_date string comment '数据时间分区',
database_name string comment '库名',
source_table_name string comment '数据源表表名',
source_column string comment '数据源字段名',
target_table_name string comment '数据目标表表名',
target__column string comment '数据目标表字段名',
consistent_data_count bigint comment '全表数据一致记录数',
source_table_count bigint comment '数据源表全表记录数',
target_table_count bigint comment '数据目标表全表记录数',
target_duplicate_count bigint comment '数据目标表重复记录数'
);
1
2
3
4
5
6
7
8
9
10
11
12

2）编写数据检测脚本

vim table_consistent_check_public.sh
1

#!/bin/bash
#全量数据所在的日期
check_date=$1
#校验数据源表的表名
source_table_name=$2
#需要校验控制的列名(与目标表顺序一致才能对比两个字段)
source_column=$3
#检查数据目标表的表名
target_table_name=$4
#检查数据目标表的字段
target_column=$5

#初始化sql查询语句
join_on_sql_str=''
#将检验数据源表的字符切割成列名数组
source_column_array=({null_column//,/})
target_column_array=({null_column//,/})
#遍历数组，拼接关联条件，输入字段全部关联
for(( i=0;i<${#source_column_array[@]};i++)) do
	if [ $i -eq 0 ];then
		join_on_sql_str=" on $source_table_name.${source_column_array[i]}=$target_table_name.${target_column_array[i]}"
	else
		join_on_sql_str="$join_on_sql_str and $source_table_name.${source_column_array[i]}=$target_table_name.${target_column_array[i]}"
	fi
done
echo "----------ods-dwd 一致性检查--------------"
#执行数据源目标表和目标表关联的查询sql语句，产线数据一致的条数。
consistent_data_quary_result=`hive -e "select count(*) from gmall.$source_table_name join gmall.$target_table_name$join_on_sql_str"`
#取出全表一致查询的条数
consistent_data_count=${consistent_data_quary_result:3}
echo "----------ods层记录条数--------------"
#执行查询源表的记录条数
source_table_quary_result=`hive -e "select count(*) from gmall.$source_table_name"`
#取出全表数据源表的记录条数
source_table_count=${source_table_quary_result:3}

echo "----------dwd层记录条数--------------"
#执行查询目标表记录条数sql查语句; gmall 为数据仓库所在的库名
target_table_quary_result=`hive -e "select count(*) from gmall.S$target_table_name"`
#取出全表数据目标表的记录条数
target_table_count=${target_table_quary_result:3}
#执行全表重复值的记录数量sql查询语句
table_duplicate_quary_result=`hive -e "select sum(tmp.duplicate_count) as duplicate_sum from (select count(*) as duplicate_count  from gmall.$table_name group by $null_column having count(*)>1) as tmp"`
#取出全表重复值的数据记录数量
duplicate_count=${table_duplicate_quary_result:3}

#将所有数据检验结果插入到表中
hive -e "insert into datacheck.dwd_table_data_check values('$check_date','gmall','$source_table_name','$source_column','$target_table_name','target_column','$consistent_data_count','$source_table_count','$target_table_count','$duplicate_count')"
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

3.dws-dwt层数据校验

dws层和dwt层已经进行了轻度聚合，不需要计算数据量前后的一致，依旧保持和ods相同的计算脚本就可以了

vim dws_data_check.sh
1

#!/bin/bash
check_date=$1

/opt/module/check/total_data_check_public.sh $check_date dws_user_info  id,name,birthday,email

 /opt/module/check/total_data_check_public.sh $check_date dws_order_status_log  id,order_id,order_status,operater_time
1
2
3
4
5
6

4.ads层数据校验

因为ads层已经进行的高度的聚合，这一层的数据校验采用订制的方法来解决，来对每一个指标进行单独的测评

1）建立ads校验表

create table datacheck.ads_table_data_check(
data_date string comment '数据时间分区',
database_name string comment '库名',
table_name string comment '表名',
column_name string comment '指标名',
healthy_value string comment '该指标合理值',
now_value bigint comment '该指标当前值',
is_healthy bigint comment '该指标是否合理： 1合理/0不合理'
);
1
2
3
4
5
6
7
8
9

2）编写数据检测脚本

[lyc@hadoop102 check]$ vim ads_data_check_public.sh
1

#!/bin/bash
#增量数据所在的日期分区
check_date=$1
hive -e "insert into datacheck.ads_table_data_check select
		temp.data_date,
		temp.database_name,
		temp.table_name,
		temp.column_name,
		temp.healthy_value,
		temp.new_mid_count,
		temp.is_healthy
	from(
		select
			\"$do_date\" as data_date,
			\"gmall\") as database_name,
			\"ads_new_mid_count\" as table_name
			\"bigger then 300\" as healthy_value,
			new_mid_count,
			if(new_mid_count>300,1,0) as is_healthy   --校验数大约等于300为正确，否则错误
			from gmall.ads_new_mid_count
		 )as temp
		 "
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22