根据业务需求对饿了么数据进行数据清洗（三）

一、明确业务需求

统计商家所有商品的平均评分作为该商店的评分，取出分数最高的前三十商家。（有效数据不包括月售量小于10、评分为0的）

二、进行MapReduce清洗

package com.yc.elm.utils;import java.io.IOException;import org.apache.hadoop.conf.Configuration;import org.apache.hadoop.fs.Path;import org.apache.hadoop.io.LongWritable;import org.apache.hadoop.io.NullWritable;import org.apache.hadoop.io.Text;import org.apache.hadoop.mapreduce.Job;import org.apache.hadoop.mapreduce.Mapper;import org.apache.hadoop.mapreduce.Reducer;import org.apache.hadoop.mapreduce.lib.input.FileInputFormat;import org.apache.hadoop.mapreduce.lib.output.FileOutputFormat;public class RatingAnalysisJob {public static class RatingAnalysisJobMapper extends Mapper<LongWritable, Text, NullWritable, Text> {private Text text = new Text();@Overrideprotected void map(LongWritable key, Text value, Mapper<LongWritable, Text, NullWritable, Text>.Context context)throws IOException, InterruptedException {if (value != null) {String[] strs = value.toString().split("\\t");if (strs.length == 7 && !strs[5].equals("0.0") && !strs[2].equals("152161001")&& Integer.parseInt(strs[6]) >= 10) {text.set(strs[0] + "\t" + strs[1] + "\t" + strs[2] + "\t" + strs[3] + "\t" + strs[4] + "\t"+ strs[5] + "\t" + strs[6]);context.write(NullWritable.get(), text);}}}}public static class RatingAnalysisJobReducer extends Reducer<NullWritable, Text, NullWritable, Text> {@Overrideprotected void reduce(NullWritable arg0, Iterable<Text> arg1,Reducer<NullWritable, Text, NullWritable, Text>.Context arg2) throws IOException, InterruptedException {super.reduce(arg0, arg1, arg2);}}public static void main(String[] args) {try {Configuration config = new Configuration();Job job = Job.getInstance(config, "RatingAnalysisJob");job.setJarByClass(RatingAnalysisJob.class);job.setMapperClass(RatingAnalysisJobMapper.class);job.setMapOutputKeyClass(NullWritable.class);job.setMapOutputValueClass(Text.class);job.setReducerClass(RatingAnalysisJobReducer.class);FileInputFormat.setInputPaths(job, new Path("hdfs://master:9000/elm/elm_data.txt"));FileOutputFormat.setOutputPath(job, new Path("hdfs://master:9000/elm/result02/"));System.out.println(job.waitForCompletion(true) ? 0 : 1);} catch (Exception e) {e.printStackTrace();}}}

结果：

三、在Hive中创建elm_rating表并插入数据

create external table  elm_rating(foodID string,foodName string,restaurantID string,restaurantName string,price double,rating float,saleCount int)row format delimitedfields terminated by '\t'location '/elm/result02/';

结果：

四、查出符合条件的前三十商家，并存入表rating

create table rating(restaurantID string,restaurantName string,rating double)row format delimited  fields terminated by ','location '/elm/rating/'

insert into rating select a.restaurantid id, a.restaurantname name, a.rating rating from ( select restaurantID, restaurantName ,avg(rating ) rating from elm_rating group by restaurantID, restaurantName )  a order by rating desc limit 30;

结果：

rating 建表是一定要以“，”分割，否则在后面用Sqoop做数据迁移的时候会出问题。