Hadoop,MapReduce操作Mysql

1、为了方便 MapReduce 直接访问关系型数据库（Mysql,Oracle），Hadoop提供了DBInputFormat和DBOutputFormat两个类。通过DBInputFormat类把数据库表数据读入到HDFS，根据DBOutputFormat类把MapReduce产生的结果集导入到数据库表中。

2、由于0.20版本对DBInputFormat和DBOutputFormat支持不是很好，该例用了0.19版本来说明这两个类的用法。

至少在我的 0.20.203 中的 org.apache.hadoop.mapreduce.lib 下是没见到 db 包，所以本文也是以老版的 API 来为例说明的。

3、运行MapReduce时候报错：java.io.IOException: com.mysql.jdbc.Driver，一般是由于程序找不到mysql驱动包。解决方法是让每个tasktracker运行MapReduce程序时都可以找到该驱动包。

添加包有两种方式：

（1）在每个节点下的${HADOOP_HOME}/lib下添加该包。重启集群，一般是比较原始的方法。

（2）a)把包传到集群上： hadoop fs -put mysql-connector-java-5.1.0- bin.jar /hdfsPath/

       b)在mr程序提交job前，添加语句：DistributedCache.addFileToClassPath(new Path(“/hdfsPath/mysql- connector-java- 5.1.0-bin.jar”), conf);

一、数据库操作

CREATE TABLE `t` (
`id` int DEFAULT NULL,
`name` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
CREATE TABLE `t2` (
`id` int DEFAULT NULL,
`name` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
insert into t values (1,"june"),(2,"decli"),(3,"hello"),
    (4,"june"),(5,"decli"),(6,"hello"),(7,"june"),
    (8,"decli"),(9,"hello"),(10,"june"),
    (11,"june"),(12,"decli"),(13,"hello");

二、MapReduce代码

  1 import java.io.DataInput;
  2 import java.io.DataOutput;
  3 import java.io.File;
  4 import java.io.IOException;
  5 import java.sql.PreparedStatement;
  6 import java.sql.ResultSet;
  7 import java.sql.SQLException;
  8  
  9 import mapr.EJob;
 10  
 11 import org.apache.hadoop.conf.Configuration;
 12 import org.apache.hadoop.filecache.DistributedCache;
 13 import org.apache.hadoop.fs.Path;
 14 import org.apache.hadoop.io.LongWritable;
 15 import org.apache.hadoop.io.Text;
 16 import org.apache.hadoop.io.Writable;
 17 import org.apache.hadoop.mapred.JobConf;
 18 import org.apache.hadoop.mapreduce.Job;
 19 import org.apache.hadoop.mapreduce.Mapper;
 20 import org.apache.hadoop.mapreduce.Reducer;
 21 import org.apache.hadoop.mapreduce.lib.db.DBConfiguration;
 22 import org.apache.hadoop.mapreduce.lib.db.DBInputFormat;
 23 import org.apache.hadoop.mapreduce.lib.db.DBOutputFormat;
 24 import org.apache.hadoop.mapreduce.lib.db.DBWritable;
 25  
 26 /**
 27  * Function: 测试 mr 与 mysql 的数据交互，此测试用例将一个表中的数据复制到另一张表中 实际当中，可能只需要从 mysql 读，或者写到
 28  * mysql 中。
 29  * 
 30  * @author administrator
 31  * 
 32  */
 33 public class Mysql2Mr {
 34     public static class StudentinfoRecord implements Writable, DBWritable {
 35         int id;
 36         String name;
 37  
 38         public StudentinfoRecord() {
 39  
 40         }
 41  
 42         public String toString() {
 43             return new String(this.id + " " + this.name);
 44         }
 45  
 46         @Override
 47         public void readFields(ResultSet result) throws SQLException {
 48             this.id = result.getInt(1);
 49             this.name = result.getString(2);
 50         }
 51  
 52         @Override
 53         public void write(PreparedStatement stmt) throws SQLException {
 54             stmt.setInt(1, this.id);
 55             stmt.setString(2, this.name);
 56         }
 57  
 58         @Override
 59         public void readFields(DataInput in) throws IOException {
 60             this.id = in.readInt();
 61             this.name = Text.readString(in);
 62         }
 63  
 64         @Override
 65         public void write(DataOutput out) throws IOException {
 66             out.writeInt(this.id);
 67             Text.writeString(out, this.name);
 68         }
 69  
 70     }
 71  
 72     // 记住此处是静态内部类，要不然你自己实现无参构造器，或者等着抛异常：
 73     // Caused by: java.lang.NoSuchMethodException: DBInputMapper.<init>()
 74     // http://stackoverflow.com/questions/7154125/custom-mapreduce-input-format-cant-find-constructor
 75     // 网上脑残式的转帖，没见到一个写对的。。。
 76     public static class DBInputMapper extends
 77             Mapper<LongWritable, StudentinfoRecord, LongWritable, Text> {
 78         @Override
 79         public void map(LongWritable key, StudentinfoRecord value,
 80                 Context context) throws IOException, InterruptedException {
 81             context.write(new LongWritable(value.id), new Text(value.toString()));
 82         }
 83     }
 84  
 85     
 86     public static class MyReducer extends Reducer<LongWritable, Text, StudentinfoRecord, Text> {
 87         @Override
 88         public void reduce(LongWritable key, Iterable<Text> values, Context context) throws IOException, InterruptedException {
 89             String[] splits = values.iterator().next().toString().split(" ");
 90             StudentinfoRecord r = new StudentinfoRecord();
 91             r.id = Integer.parseInt(splits[0]);
 92             r.name = splits[1];
 93             context.write(r, new Text(r.name));
 94             
 95         }
 96     }
 97     
 98     @SuppressWarnings("deprecation")
 99     public static void main(String[] args) throws IOException, InterruptedException, ClassNotFoundException {
100         File jarfile = EJob.createTempJar("bin");
101         EJob.addClasspath("usr/hadoop/conf");
102  
103         ClassLoader classLoader = EJob.getClassLoader();
104         Thread.currentThread().setContextClassLoader(classLoader);
105         
106         Configuration conf = new Configuration();
107         // 这句话很关键
108         conf.set("mapred.job.tracker", "172.30.1.245:9001");
109         DistributedCache.addFileToClassPath(new Path(
110                 "hdfs://172.30.1.245:9000/user/hadoop/jar/mysql-connector-java-5.1.6-bin.jar"), conf);
111         DBConfiguration.configureDB(conf, "com.mysql.jdbc.Driver", "jdbc:mysql://172.30.1.245:3306/sqooptest", "sqoop", "sqoop");
112         
113         Job job = new  Job(conf, "Mysql2Mr");
114 //        job.setJarByClass(Mysql2Mr.class);
115         ((JobConf)job.getConfiguration()).setJar(jarfile.toString());
116         job.setMapOutputKeyClass(LongWritable.class);
117         job.setMapOutputValueClass(Text.class);
118         
119         job.setMapperClass(DBInputMapper.class);
120         job.setReducerClass(MyReducer.class);
121         
122         job.setOutputKeyClass(LongWritable.class);
123         job.setOutputValueClass(Text.class);
124         
125         job.setOutputFormatClass(DBOutputFormat.class);
126         job.setInputFormatClass(DBInputFormat.class);
127         
128         String[] fields = {"id","name"};
129          // 从 t 表读数据
130         DBInputFormat.setInput(job, StudentinfoRecord.class, "t", null, "id", fields);
131         // mapreduce 将数据输出到 t2 表
132         DBOutputFormat.setOutput(job, "t2", "id", "name");
133         
134         System.exit(job.waitForCompletion(true)? 0:1);
135     }
136 }
View Code

三、数据库结果

 1 mysql> select * from t2;
 2 +------+-------+
 3 | id   | name  |
 4 +------+-------+
 5 |    1 | june  |
 6 |    2 | decli |
 7 |    3 | hello |
 8 |    4 | june  |
 9 |    5 | decli |
10 |    6 | hello |
11 |    7 | june  |
12 |    8 | decli |
13 |    9 | hello |
14 |   10 | june  |
15 |   11 | june  |
16 |   12 | decli |
17 |   13 | hello |
18 |    1 | june  |
19 |    2 | decli |
20 |    3 | hello |
21 |    4 | june  |
22 |    5 | decli |
23 |    6 | hello |
24 |    7 | june  |
25 |    8 | decli |
26 |    9 | hello |
27 |   10 | june  |
28 |   11 | june  |
29 |   12 | decli |
30 |   13 | hello |
31 +------+-------+
32 26 rows in set (0.00 sec)

View Code

 

posted @ 2018-10-22 17:03  孟阳miss  阅读(353)  评论(0)    收藏  举报