这是最近项目中所遇到的一个比较奇葩的需求,重点是如何快速地实现这个需求。
思路一:
先考虑实现需求,后面再进行速度和性能上的进一步优化。所以先是考虑从hive的元数据库中把具有该字段的表信息都找出来,然后扫描这些表的时候只针对目标字段进行过滤和判断,避免直接针对3万张表进行全表扫描。
为了方便演示,简化需求为从hive数据仓库中的ods库的几十张表里找出字段sex=“UnKnown”的数据在哪些表中有存储。
采用Shell脚本来实现,代码如下:
#!/bin/bash
dName="ods"
resultTable="resultTable"
cat /dev/null > /tmp/temp.log
##############
# Step1:先从目标数据库中找出具有字段sex的所有表的信息并输出到文件/tmp/temp.log
##############
mysql -uroot -p123456 <<EOF
tee /tmp/temp.log
use metastore_spark;
select t1.TBL_NAME
from
(
select t.DB_ID,t.TBL_ID,t.TBL_NAME,t0.CD_ID,t0.COLUMN_NAME
from
(
select s.SD_ID,c.CD_ID,c.COLUMN_NAME
from columns_v2 c
left join sds s on c.CD_ID=s.CD_ID
where c.COLUMN_NAME in('sex')
)t0
left join tbls t on t0.SD_ID=t.SD_ID
where t.DB_ID=6
)t1
left join dbs d
on t1.DB_ID= d.DB_ID;
notee
EOF
###############
# Step2:遍历具有sex字段的所有表来找出存在sex='Unknown'数据的表信息
# 在shell脚本中每遍历每一个表,先统计出该表中含有sex='UnKnown'的条数,然后把条数和该表名一起作为
# 一条新的记录存到结果表resultTable中来记录每个表中具有几条目标数据
###############
hive -e "create table if not exists $dName.$resultTable(tableName string, UnKnownCount int);"
for table_name in `cat /tmp/temp.log`
do
hive -e "
use $dName;
insert into table $resultTable
select '"$table_name"' as tableName, t0.UnKnownCount as UnKnownCount
from
(
select count(*) as UnKnownCount
from $dName.$table_name
where sex='UnKnown'
)t0;
"
done
###############
# Step3:最后导出resultTable表中的所有数据到本地文件来进行查看统计
###############
hive -e "
insert overwrite local directory '/home/hadoop/datas/output3'
row format delimited fields terminated by ','
select * from $dName.$resultTable;"
exit;
查看统计出来的结果:在表t222和表t8两张表中都存有一条sex=“UnKnown”的数据,其它四张表只是具有sex这个字段而已。