使用python脚本进行数据清洗（1）

CREATE TABLE ml_100k (userid INT,   movieid INT,  rating INT,  unixtime STRING) 
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ‘\t‘
LINES TERMINATED BY ‘\n‘    
STORED AS TEXTFILE;

CREATE TABLE ml_100k2 (userid INT,   movieid INT,  rating INT,  weekday int) 
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ‘\t‘
LINES TERMINATED BY ‘\n‘    
STORED AS TEXTFILE;

LOAD DATA LOCAL INPATH ‘/home/centos/ml-100k/u.data‘ into table ml_100k;

import sys
import datetime

for line in sys.stdin:  #接收输入
    line = line.strip()
    userid, movieid, rating, unixtime = line.split(‘\t‘)
    weekday = datetime.datetime.fromtimestamp(float(unixtime)).isoweekday() #转化unixtime时间戳为日期时间，获取对应的星期值
    print(‘\t‘.join([userid, movieid, rating, str(weekday)])) # 输出清洗后的数据

add file /home/centos/clean_ml_100k.py;

INSERT OVERWRITE TABLE ml_100k2
SELECT
TRANSFORM (userid, movieid, rating, unixtime)    --输入值（基表）
USING ‘python clean_ml_100k.py‘            --使用脚本清洗
AS (userid, movieid, rating, weekday)            --输出值（子表）
FROM ml_100k;