1、创建合适格式的表result10

create table result10(
ip String,
time1 String,
day String,
traffic String,
type String,
id String)
row format delimited fields terminated by ',' stored as textfile;

2、将txt文件的数据插入到表中：

load data local inpath '/data/result.txt' into table result10;

3、将数据清洗后的数据加载到一个新的表中

create table newdata as
select ip,
date_format(from_unixtime(unix_timestamp(`time1`,'dd/MMM/yyyy:HH:mm:ss Z'), 'yyyy-MM-dd HH:mm:ss'),
'yyyy-MM-dd HH:mm:ss') as `time1`,
day,
traffic,
type,
id
from result10;

数据清洗完成：

内部清洗和外部清洗都ok啦~~