Hive过滤脏数据的一些经验-白红宇

如下文件需要处理，每个文件大概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_20150506Failed 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) aselect 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) binsert overwrite table tmp_20150506 partition(month = '2015-04')select *