MR2中如果需要从数据库中读取或者写入数据,需要自己实现Writable和DBWritable两个接口,同时还需在DBConfiguration对数据库的元信息做相应配置。
下面这个例子是Hadoop自带的,只是将数据库改为MySQL,在执行之前需要将Java连接MySQL的驱动上传到每台机器的${HADOOP_HOME}/share/hadoop/common路径下
这里需要说明两点:
1、一般不需要MR直接操作数据库,因为MR并发执行,很容易把数据库跑崩,此例只展示如何操作
2、如果必须要操作数据库,可以将数据库里面的数据导出到文本文件中,计算完成后,在通过SQLLoad的方式保存到数据库
例子比较简单,通过注释可以看懂
package com.test.mr2;
/**
*MapReduce操作数据库实例
*计算页面访问总量,Access表中保存了每个页面访问明细,Pageview表保存了每个页面被浏览的总次数
*sum Access表的每个url,将计算结果写到Pageview
*/
import java.io.DataInput;
import java.io.DataOutput;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Random;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.hadoop.conf.Configuration;
import org.apache.hadoop.conf.Configured;
import org.apache.hadoop.io.LongWritable;
import org.apache.hadoop.io.NullWritable;
import org.apache.hadoop.io.Text;
import org.apache.hadoop.io.Writable;
import org.apache.hadoop.mapreduce.Job;
import org.apache.hadoop.mapreduce.Mapper;
import org.apache.hadoop.mapreduce.Reducer;
import org.apache.hadoop.mapreduce.lib.db.DBConfiguration;
import org.apache.hadoop.mapreduce.lib.db.DBInputFormat;
import org.apache.hadoop.mapreduce.lib.db.DBOutputFormat;
import org.apache.hadoop.mapreduce.lib.db.DBWritable;
import org.apache.hadoop.mapreduce.lib.reduce.LongSumReducer;
import org.apache.hadoop.util.StringUtils;
import org.apache.hadoop.util.Tool;
import org.apache.hadoop.util.ToolRunner;
public class DBOperator extends Configured implements Tool {
private static final Log LOG = LogFactory.getLog(DBOperator.class);
private Connection connection;
private boolean initialized = false;
// Access表字段名称
private static final String[] AccessFieldNames = { "url", "referrer",
"time" };
// Pageview表字段名称
private static final String[] PageviewFieldNames = { "url", "pageview" };
private static final String DB_URL = "jdbc:mysql://192.168.1.182:3306/hadoop?createDatabaseIfNotExist=true";
private static final String DRIV