清洗有缺失的行(存在空值、null值等)
- 源数据
[yao@master data]$ head -2 tmall-201412-1w.csv
13764633023 2014-12-01 02:20:42.000 全视目Allseelook 原宿风暴显色美瞳彩色隐形艺术眼镜1片 拍2包邮 33.6 2 18067781305
13377918580 2014-12-17 08:10:25.000 kilala可啦啦大美目大直径混血美瞳年抛彩色近视隐形眼镜2片包邮 19.8 2 17359010576
这个数据有1w行,一共9列,但是有些行的列上有空值、null值、空格等
- 方法一:
通过awk命令去掉这些有缺失的行
[yao@master data]$ cat tmall_filter.sh
#!/bin/bash
infile=$1
outfile=$2
awk -F"\t" '{if($1 != "" && $2 != "" && $3 != "" && $4 != "" && $5 != "" && $6 != "" && $1 != "null" && $2 != "null" && $3 != "null" && $4 != "null" && $5 != "null" && $6 != "null" && $1 != " " && $2 != " " && $3 != " " && $4 != " " && $5 != " " && $6 != " ") print $0}' $infile > $outfile
- 方法二:
1.创建临时表
CREATE TABLE IF NOT EXISTS tmall.tmall_201412_uid_pid(
uid STRING,
pid STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE;
2.数据清洗
(1)初步填充、check 结果
INSERT OVERWRITE TABLE tmall.tmall_201412_uid_pid SELECT uid, pid from
tmall.tmall_201412;
下载 HDFS 文件到本地:
hadoop fs -get /user/hive/warehouse/tmall.db/tmall_201412_uid_pid/000000_0 .
打开本地文件:
vi 000000_0
执行查找命令:
/null
可以看到不少噪声数据,这些数据需要清洗,带 null、字段为""的等
(2)初步清洗
INSERT OVERWRITE TABLE tmall.tmall_201412_uid_pid select regexp_extract(uid, '^[0-9]*$', 0),regexp_extract(pid, '^[0-9]*$', 0) from tmall.tmall_201412 where regexp_extract(uid, '^[0-9]*$', 0) is not null and regexp_extract(uid, '^[0-9]*$', 0) != 'NULL' and regexp_extract(uid, '^[0-9]*$', 0) !='' and regexp_extract(uid, '^[0-9]*$', 0) != ' ' and regexp_extract(uid, '^[0-9]*$', 0) != 'null' and regexp_extract(pid, '^[0-9]*$', 0) is not null and regexp_extract(pid, '^[0-9]*$', 0) != 'NULL' and regexp_extract(pid, '^[0-9]*$', 0) !='' and regexp_extract(pid, '^[0-9]*$', 0) != ' ' and regexp_extract(pid, '^[0-9]*$', 0) !='null' ;
改变分隔符
- 源数据
[yao@master product]$ head -5 1-5.csv
香菜,2.8,4,4,4,2.2,山西汾阳市晋阳农副产品批发市场,山西,汾阳
大葱,2.8,2.8,2.8,2.8,2.6,山西汾阳市晋阳农副产品批发市场,山西,汾阳
葱头,1.6,1.6,1.6,1.6,1.6,山西汾阳市晋阳农副产品批发市场,山西,汾阳
大蒜,3.6,3.6,3.6,3.6,3,山西汾阳市晋阳农副产品批发市场,山西,汾阳
蒜苔,6.2,6.4,6.4,6.4,5.2,山西汾阳市晋阳农副产品批发市场,山西,汾阳
[yao@master product]$ cat china-province.csv
河北省,山西省,辽宁省,吉林省,黑龙江省,江苏省,浙江省,安徽省,福建省,江西省,山东省,河南省,湖北省,湖南省,广东省,海南省,四川省,贵州省,云南省,陕西省,甘肃省,青海省,台湾省,内蒙古自治区,广西壮族自治区,西藏自治区,宁夏回族自治区,新疆维吾尔自治区,香港特别行政区,澳门特别行政区
- 方法一:
(1)将1-5.csv文件中的逗号分隔改为\t分隔
打开本地文件
vim 1-5.csv
执行替换命令
:%s/','/'\t'/g
(2)清洗china-province.txt中数据,按照逗号切分,每行一个省份
vi filter.sh
#!/bin/bash
infile=$1
outfile=$2
awk -F"," '{for(i=1;i<=NF,i++)print $i}'$infile>$outfile
[yao@master product]$ bash filter.sh /home/yao/data/product/china-province.csv /home/yao/data/product/province.csv
- 方法二
(1)使用linux下的iconv 命令改变文件的编码(编码转换):
iconv -f GB2312 -t UTF-8 china-province01 -o china-province1
---------原本编码----新编码------原文件名---------新生成文件名---------
//用\t分隔
awk -F "\t" '{print $1"\t"$2"\t2014/1/1\t"$7"\t"$8"\t"$9}' 1-5.csv > data
//将逗号替换为\t
sed 's/,/\n/g' china-province.txt > china-province
vim编辑器中,将china-province中的省、自治区等字段去掉
:%s/,/\r/g
:%s/省//g
提取年、月、日等信息
- 源数据
[yao@master hw]$ head -10 sogou_10w_ext
20171230000005 57375476989eea12893c0c3811607bcf 奇艺高清 1 1 http://www.qiyi.com/ 2017 12 30 00
20171230000005 66c5bb7774e31d0a22278249b26bc83a 凡人修仙传 3 1 http://www.booksky.org/BookDetail.aspx?BookID=1050804&Level=1 2017 130 00
20171230000007 b97920521c78de70ac38e3713f524b50 本本联盟 1 1 http://www.bblianmeng.com/ 2017 12 30 00
20171230000008 6961d0c97fe93701fc9c0d861d096cd9 华南师范大学图书馆 1 1 http://lib.scnu.edu.cn/ 2017 12 30 00
20171230000008 f2f5a21c764aebde1e8afcc2871e086f 在线代理 2 1 http://proxyie.cn/ 2017 12 30 00
20171230000009 96994a0480e7e1edcaef67b20d8816b7 伟大导演 1 1 http://movie.douban.com/review/1128960/ 2017 12 30 00
20171230000009 698956eb07815439fe5f46e9a4503997 youku 1 1 http://www.youku.com/ 2017 12 30 00
20171230000009 599cd26984f72ee68b2b6ebefccf6aed 安徽合肥365房产网 1 1 http://hf.house365.com/ 2017 12 30 00
20171230000010 f577230df7b6c532837cd16ab731f874 哈萨克网址大全 1 1 http://www.kz321.com/ 2017 12 30 00
20171230000010 285f88780dd0659f5fc8acc7cc4949f2 IQ数码 1 1 http://www.iqshuma.com/ 2017 12 30 00
这个数据有500万行,第一列表示时间,但不是标准的时间格式如yyyy-MM-dd HH:mm:ss;同时还有一些缺失,比如存在null值、空值等
- 扩展数据
将第一列的时间的年、月、日、时通过substr()截取出来
[yao@master data]$ cat sogou-log-extend.sh
#!/bin/bash
#infile=/sogou.500w.utf8
infile=$1
#outfile=/sogou.500w.utf8.final
outfile=$2
awk -F '\t' '{print $0"\t"substr($1,0,4)"\t"substr($1,5,2)"\t"substr($1,7,2)"\t"substr($1,9,2)}' $infile > $outfile
后四列表示的分别是年、月、日、时
[yao@master hw]$ head -1 sogou_10w_ext
20171230000005 57375476989eea12893c0c3811607bcf 奇艺高清 1 1 http://www.qiyi.com/ 2017 12 30 00
20171230000005 66c5bb7774e31d0a22278249b26bc83a 凡人修仙传 3 1 http://www.booksky.org/BookDetail.aspx?BookID=1050804&Level=1 2017 12 30 00
- 清洗数据
删除空值和空格
[yao@master data]$ cat sogou-log-filter.sh
#!/bin/bash
#infile=/data/sogou-data/sogou.500w.utf8
infile=$1
#outfile=/data/sogou-data/sogou.500w.utf8.final
outfile=$2
awk -F"\t" '{if($2 != "" && $3 != "" && $2 != " " && $3 != " ") print $0}' $infile > $outfile
去除第一个和最后一个字符
- 测试数据
[yao@master data]$ cat test.txt
[12345]
[12345]
[12345]
[12345]
- 去除第一个字符
[yao@master data]$ sed 's/.//' test.txt
12345]
12345]
12345]
12345]
- 去掉第二个字符
[yao@master data]$ sed -r 's/]$//g' test.txt
[12345
[12345
[12345
[12345
[yao@master data]$ sed 's/.$//' test.txt
[12345
[12345
[12345
[12345
[yao@master data]$ awk '{sub(/.$/,"")}1' test.txt
[12345
[12345
[12345
[12345
[yao@master data]$ awk '{sub(/.{2}$/,"")}1' test.txt
[1234
[1234
[1234
[1234
- 源数据
[{"beCommentWeiboId":"","beForwardWeiboId":"","catchTime":"1387159495","commentCount":"1419","content":"分享图片","createTime":"1386981067","info1":"","info2":"","info3":"","mlevel":"","musicurl":[],"pic_list":["http://ww3.sinaimg.cn/thumbnail/40d61044jw1ebixhnsiknj20qo0qognx.jpg"],"praiseCount":"5265","reportCount":"1285","source":"iPad客户端","userId":"1087770692","videourl":[],"weiboId":"3655325888057474","weiboUrl":"http://weibo.com/1087770692/AndhixO7g"}]
[{"beCommentWeiboId":"","beForwardWeiboId":"","catchTime":"1387159495","commentCount":"91","content":"行走:#去远方发现自己#@费勇主编,跨界明星联合执笔,分享他们观行思趣的心发现、他们的成长与心路历程,当当网限量赠送出品人@陈坤抄诵印刷版《心经》,赠完不再加印哦!详情请戳:http://t.cn/8k622Sj","createTime":"1386925242","info1":"","info2":"","info3":"","mlevel":"","musicurl":[],"pic_list":["http://ww4.sinaimg.cn/thumbnail/b2336177jw1ebi6j4twk7j20m80tkgra.jpg"],"praiseCount":"1","reportCount":"721","source":"","userId":"2989711735","videourl":[],"weiboId":"3655091741442099","weiboUrl":"http://weibo.com/2989711735/An7bE639F"}]
[{"beCommentWeiboId":"","beForwardWeiboId":"3655091741442099","catchTime":"1387159495","commentCount":"838","content":"抄诵“心经”为大家祈福,字不好,见谅!","createTime":"1386926798","info1":"","info2":"","info3":"","mlevel":"","musicurl":[],"pic_list":[],"praiseCount":"2586","reportCount":"693","source":"iPad客户端","userId":"1087770692","videourl":[],"weiboId":"3655098267456453","weiboUrl":"http://weibo.com/1087770692/An7mavhLn"}]
该数据需要用json解析,但不符合json的格式。并且解压后在一个目录下有多个文件。
- 方法一:
//去掉第一个括号的脚本
#!/bin/bash
dir=`ls /home/yao/data/weibo/619893` //查看这个目录
dir_input="/home/yao/data/weibo/619893/"
dir_out="/home/yao/data/weibo/test/"
for i in $dir //如果文件在这个目录下就执行
do
infile=$dir_input$i //要被执行的文件就是dir_input下的第一个到最后一个文件
sed 's/.//' $infile >> $dir_out/test.json //执行后输出到dir_out目录下
done
//去掉最后一个括号
awk '{sub(/.{2}$/,"")}1' test.json > data.json
清洗后load到hive的表中在进行json解析
- 方法二:
在hive里创建表,在加载的过程中通过substr()截取第二位和倒数第二位的字段
create table rate_weibo
select
get_json_object(line,'$.catchTime') as catchTime
as select substr(2,length(line-2) b from weibo_json)
或创建表并加载数据
create table weibo_json(line string) row format delimited;
load data local inpath '/home/yao/data/weibo/test/data.json' overwrite into table weibo_json;
总结
在无数次的实验中,源数据去掉最后一个字符并没有去掉中括号,可能是中括号后有空格等因素影响,导致通过去掉最后两个字符才实现需求。
而substr()函数直接截取到我们想要的字符,更加的实用