MR输出到Mysql

 

1.自定义一个实体类用于接收属性数据。

实现Writable, DBWritable接口，实现四个抽象方法

例：

public class DataFilter implements Writable, DBWritable {
private String day = null;
private String app_token = null;
private String version = null;
private String channel = null;
private String city = null;
private String commit_time = null;
private String user_id = null;
public DataFilter() {
}
public DataFilter(String day, String app_token, String version, String channel, String city, String commit_time, String user_id) {
this.day = day;
this.app_token = app_token;
this.version = version;
this.channel = channel;
this.city = city;
this.commit_time = commit_time;
this.user_id = user_id;
}
//day+"|"+app_token+"|"+version+"|"+channel+"|"+city+"|"+commit_time+"|"+user_id
public void write(DataOutput dataOutput) throws IOException {
dataOutput.writeUTF(day);
dataOutput.writeUTF(app_token);
dataOutput.writeUTF(version);
dataOutput.writeUTF(channel);
dataOutput.writeUTF(city);
dataOutput.writeUTF(commit_time);
dataOutput.writeUTF(user_id);
}
public void readFields(DataInput dataInput) throws IOException {
day = dataInput.readUTF();
app_token = dataInput.readUTF();
version = dataInput.readUTF();
channel = dataInput.readUTF();
city = dataInput.readUTF();
commit_time = dataInput.readUTF();
user_id = dataInput.readUTF();
}
public void write(PreparedStatement preparedStatement) throws SQLException {
preparedStatement.setString(1,this.day);
preparedStatement.setString(2,this.app_token);
preparedStatement.setString(3,this.version);
preparedStatement.setString(4,this.channel);
preparedStatement.setString(5,this.city);
preparedStatement.setString(6,this.commit_time);
preparedStatement.setString(7,this.user_id);
}
public void readFields(ResultSet resultSet) throws SQLException {
this.day = resultSet.getString(1);
this.app_token = resultSet.getString(2);
this.version = resultSet.getString(3);
this.channel = resultSet.getString(4);
this.city = resultSet.getString(5);
this.commit_time = resultSet.getString(6);
this.user_id = resultSet.getString(7);
}
@Override
public String toString() {
return "DataFilter{" +
"day='" + day + '\'' +
", app_token='" + app_token + '\'' +
", version='" + version + '\'' +
", channel='" + channel + '\'' +
", city='" + city + '\'' +
", commit_time='" + commit_time + '\'' +
", user_id='" + user_id + '\'' +
'}';
}
}
//正常写Mapreduce程序。
public class DataFilterMapper extends Mapper<LongWritable, Text,Text,Text> {
Text mapKey = null;
Text mapValue = null;
String day = null;
@Override
protected void setup(Context context) throws IOException, InterruptedException {
mapKey = new Text();
mapValue =new Text();
day = context.getConfiguration().get("date");
super.setup(context);
}
@Override
protected void map(LongWritable key, Text value, Context context) throws IOException, InterruptedException {
String str = value.toString();
JSONObject jsonObject = JSON.parseObject(str);
JSONObject header = jsonObject.getJSONObject("header");
//app_token应用名称
String app_token = header.getString("app_token");
//app_ver_name版本名称
String version = header.getString("app_ver_name");
//release_channel获取渠道
String channel = header.getString("release_channel");
//city获取城市
String city = header.getString("city");
//commit_time数据提交的时间
String commit_time = header.getString("commit_time");
//user_id用户标识
String user_id = header.getString("user_id");
mapKey = new Text(app_token+user_id);
mapValue = new Text(day+"|"+app_token+"|"+version+"|"+channel+"|"+city+"|"+commit_time+"|"+user_id);
//day + "|" + app_token + "|" + user_id + "|" + commit_time + "|" + version + "|" + channel + "|" + city
context.write(mapKey,mapValue);
}
@Override
protected void cleanup(Context context) throws IOException, InterruptedException {
super.cleanup(context);
}
}
//在reduce段的输出类型K值为自定义的实体类
例：
public class DataFilterReduce extends Reducer<Text,Text,DataFilter, NullWritable> {
TreeMap<Long ,String> treeMap = null;
@Override
protected void setup(Context context) throws IOException, InterruptedException {
treeMap = new TreeMap<Long,String>();
super.setup(context);
}
@Override
protected void reduce(Text key, Iterable<Text> values, Context context) throws IOException, InterruptedException {
for (Text value : values) {
String logs = value.toString();
Long commitTime =Long.parseLong( logs.split("\\|")[5]);
treeMap.put(commitTime,logs);
}
Long firstKey = treeMap.firstKey();
String redKey = treeMap.get(firstKey);
String[] split = redKey.split("\\|");
context.write(new DataFilter(split[0],split[1],split[2],split[3],split[4],split[5],split[6]),NullWritable.get());
}
@Override
protected void cleanup(Context context) throws IOException, InterruptedException {
super.cleanup(context);
}
}
//driver端写上连接数据库所需要的参数
例：
public class DataFilterDirver {
public static void main(String[] args) throws IOException, ClassNotFoundException, InterruptedException {
Configuration conf = new Configuration();
String DriverClass = "com.mysql.jdbc.Driver";
String dbUrl = "jdbc:mysql://192.168.56.3:3306/bigdata?characterEncoding=utf8";
String user = "root";
String password = "root";
//数据库的上下文
DBConfiguration.configureDB(conf, DriverClass, dbUrl, user, password);
SimpleDateFormat sdf = new SimpleDateFormat();
String date = sdf.format(new Date());
conf.set("date", date);
Job job = Job.getInstance(conf);
job.setJarByClass(DataFilterDirver.class);
job.setMapperClass(DataFilterMapper.class);
job.setReducerClass(DataFilterReduce.class);
job.setMapOutputKeyClass(Text.class);
job.setMapOutputValueClass(Text.class);
job.setOutputKeyClass(DataFilter.class);
job.setOutputValueClass(NullWritable.class);
FileSystem fs = FileSystem.get(conf);
boolean directory = fs.isDirectory(new Path(args[0]));
if (directory) {
FileStatus[] fileStatuses = fs.listStatus(new Path(args[0]));
for (FileStatus fileStatus : fileStatuses) {
Path path = fileStatus.getPath();
if (fs.isDirectory(path)) {
FileInputFormat.addInputPath(job, path);
}
}
}
// FileInputFormat.setInputPaths(job,new Path(args[0]));
DBOutputFormat.setOutput(job,"DataFilter1","day"+"app_token"+"version"+"channel"+"city"+"commit_time"+"user_id");
boolean b = job.waitForCompletion(true);
System.exit(b ? 0 : 1);
}
}