您现在的位置是:首页 > 文章详情

Hive过滤脏数据的一些经验

日期:2015-05-05点击:639

如下文件需要处理,每个文件大概13G,其中字段以空格(32)分隔的7个字段;最麻烦的是中间有脏数据:

-rw-r--r-- 1 hadoop ifengdev 1895843464 May 6 14:56 feedback201503_201.tar.gz -rw-r--r-- 1 hadoop ifengdev 1896885848 May 6 14:59 feedback201503_202.tar.gz -rw-r--r-- 1 hadoop ifengdev 1891790676 May 6 15:00 feedback201503_203.tar.gz -rw-r--r-- 1 hadoop ifengdev 1894197100 May 6 15:01 feedback201503_204.tar.gz -rw-r--r-- 1 hadoop ifengdev 1894074074 May 6 15:02 feedback201503_205.tar.gz -rw-r--r-- 1 hadoop ifengdev 1829224750 May 6 16:13 feedback201504_201.tar.gz -rw-r--r-- 1 hadoop ifengdev 1831709571 May 6 16:14 feedback201504_202.tar.gz -rw-r--r-- 1 hadoop ifengdev 1824710879 May 6 16:30 feedback201504_203.tar.gz -rw-r--r-- 1 hadoop ifengdev 1827164031 May 6 16:31 feedback201504_204.tar.gz -rw-r--r-- 1 hadoop ifengdev 1827911208 May 6 16:31 feedback201504_205.tar.gz

直接Load进Hive报错:

Loading data to table default.tmp_20150506 Failed with exception Wrong file format. Please check the file's format. FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.MoveTask 

没办法中间格式有问题:

网上说改变存储格式可以避免报错:

CREATE TABLE tmp_20150506( dt string, unknown1 string, unknown2 string, reurl string, uid string, num1 int, num2 int) ROW FORMAT DELIMITED FIELDS TERMINATED BY '32' LINES TERMINATED BY '10' STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.RCFileInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.RCFileOutputFormat' 改为: CREATE TABLE tmp_20150506( dt string, unknown1 string, unknown2 string, reurl string, uid string, num1 int, num2 int) ROW FORMAT DELIMITED FIELDS TERMINATED BY '32' LINES TERMINATED BY '10' STORED AS TEXTFILE;

确实不报错了,根据具体需求也算一个方法;

 

最直接的方法:

zcat feedback201503_201.tar.gz|gawk -F ' ' 'NF==7 {print $1, "\t", $2, "\t", $3, "\t", $4, "\t", $5, F ' ' 'NF==7 {print $1, "\t", $2, "\t", $3, "\t", $4, "\t", $5, "\t", $6, "\t", $7}' >> feedback20150, "\t", $6, "\t", $7}' >> feedback201503_204.log 

功能:替换空格为制表符;并且过滤字段不满足要求的脏数据;

接着Load进Hive即可;

上述方法比较直接,但觉得“体力劳动“过多,可能我比较懒,所以相对喜欢下边的方法:

基本思路就是把一行作为一个字段load进Hive,利用Hive本身筛选数据:

CREATE TABLE tmp_20150506_raw( allfilds string ) ROW FORMAT DELIMITED FIELDS TERMINATED BY '10' LINES TERMINATED BY '10' STORED AS TEXTFILE;
FIELDS TERMINATED BY '10'
LINES TERMINATED BY '10'
都设置成换行符即可,进入Hive以后使用Hive筛选数据即可。
筛选数据并存入另外一张表中,
本例的后续处理过程如下
from ( from ( select allfilds from tmp_20150506_raw where size(split(allfilds, ' ')) = 7) a select split(allfilds, ' ')[0] as dt, split(allfilds, ' ')[1] as unknown1, split(allfilds, ' ')[2] as unknown2, split(allfilds, ' ')[3] as reurl, split(allfilds, ' ')[4] as uid, split(allfilds, ' ')[5] as num1, split(allfilds, ' ')[6] as num2) b insert overwrite table tmp_20150506 partition(month = '2015-04') select *




 

原文链接:https://yq.aliyun.com/articles/622428
关注公众号

低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。

持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。

转载内容版权归作者及来源网站所有,本站原创内容转载请注明来源。

文章评论

共有0条评论来说两句吧...

文章二维码

扫描即可查看该文章

点击排行

推荐阅读

最新文章