有时候我们在项目中会遇到输入结果集很大，但是输出结果很小，比如一些 pv、uv 数据，然后为了实时查询的需求，或者一些 OLAP 的需求，我们需要 mapreduce 与 mysql 进行数据的交互，而这些特性正是 hbase 或者 hive 目前亟待改进的地方。
好了言归正传，简单的说说背景、原理以及需要注意的地方：
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);
（3）虽然API用的是0.19的，但是使用0.20的API一样可用，只是会提示方法已过时而已。
4、测试数据：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
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"
);
|
5、代码：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
|
import
java.io.DataInput;
import
java.io.DataOutput;
import
java.io.IOException;
import
java.sql.PreparedStatement;
import
java.sql.ResultSet;
import
java.sql.SQLException;
import
java.util.Iterator;
import
org.apache.hadoop.filecache.DistributedCache;
import
org.apache.hadoop.fs.Path;
import
org.apache.hadoop.io.LongWritable;
import
org.apache.hadoop.io.Text;
import
org.apache.hadoop.io.Writable;
import
org.apache.hadoop.mapred.JobClient;
import
org.apache.hadoop.mapred.JobConf;
import
org.apache.hadoop.mapred.MapReduceBase;
import
org.apache.hadoop.mapred.Mapper;
import
org.apache.hadoop.mapred.OutputCollector;
import
org.apache.hadoop.mapred.Reducer;
import
org.apache.hadoop.mapred.Reporter;
import
org.apache.hadoop.mapred.lib.IdentityReducer;
import
org.apache.hadoop.mapred.lib.db.DBConfiguration;
import
org.apache.hadoop.mapred.lib.db.DBInputFormat;
import
org.apache.hadoop.mapred.lib.db.DBOutputFormat;
import
org.apache.hadoop.mapred.lib.db.DBWritable;
/**
* Function: 测试 mr 与 mysql 的数据交互，此测试用例将一个表中的数据复制到另一张表中
* 实际当中，可能只需要从 mysql 读，或者写到 mysql 中。
* date: 2013-7-29 上午2:34:04 <br/>
* @author june
*/
public
class
Mysql2Mr {
// DROP TABLE IF EXISTS `hadoop`.`studentinfo`;
// CREATE TABLE studentinfo (
// id INTEGER NOT NULL PRIMARY KEY,
// name VARCHAR(32) NOT NULL);
public
static
class
StudentinfoRecord
implements
Writable, DBWritable {
int
id;
String name;
public
StudentinfoRecord() {
}
public
void
readFields(DataInput in)
throws
IOException {
this
.id = in.readInt();
this
.name = Text.readString(in);
}
public
String toString() {
return
new
String(
this
.id +
" "
+
this
.name);
}
@Override
public
void
write(PreparedStatement stmt)
throws
SQLException {
stmt.setInt(
1
,
this
.id);
stmt.setString(
2
,
this
.name);
}
@Override
public
void
readFields(ResultSet result)
throws
SQLException {
this
.id = result.getInt(
1
);
this
.name = result.getString(
2
);
}
@Override
public
void
write(DataOutput out)
throws
IOException {
out.writeInt(
this
.id);
Text.writeString(out,
this
.name);
}
}
// 记住此处是静态内部类，要不然你自己实现无参构造器，或者等着抛异常：
// Caused by: java.lang.NoSuchMethodException: DBInputMapper.<init>()
// http://stackoverflow.com/questions/7154125/custom-mapreduce-input-format-cant-find-constructor
// 网上脑残式的转帖，没见到一个写对的。。。
public
static
class
DBInputMapper
extends
MapReduceBase
implements
Mapper<LongWritable, StudentinfoRecord, LongWritable, Text> {
public
void
map(LongWritable key, StudentinfoRecord value,
OutputCollector<LongWritable, Text> collector, Reporter reporter)
throws
IOException {
collector.collect(
new
LongWritable(value.id),
new
Text(value.toString()));
}
}
public
static
class
MyReducer
extends
MapReduceBase
implements
Reducer<LongWritable, Text, StudentinfoRecord, Text> {
@Override
public
void
reduce(LongWritable key, Iterator<Text> values,
OutputCollector<StudentinfoRecord, Text> output, Reporter reporter)
throws
IOException {
String[] splits = values.next().toString().split(
" "
);
StudentinfoRecord r =
new
StudentinfoRecord();
r.id = Integer.parseInt(splits[
0
]);
r.name = splits[
1
];
output.collect(r,
new
Text(r.name));
}
}
public
static
void
main(String[] args)
throws
IOException {
JobConf conf =
new
JobConf(Mysql2Mr.
class
);
DistributedCache.addFileToClassPath(
new
Path(
"/tmp/mysql-connector-java-5.0.8-bin.jar"
), conf);
conf.setMapOutputKeyClass(LongWritable.
class
);
conf.setMapOutputValueClass(Text.
class
);
conf.setOutputKeyClass(LongWritable.
class
);
conf.setOutputValueClass(Text.
class
);
conf.setOutputFormat(DBOutputFormat.
class
);
conf.setInputFormat(DBInputFormat.
class
);
// // mysql to hdfs
// conf.setReducerClass(IdentityReducer.class);
// Path outPath = new Path("/tmp/1");
// FileSystem.get(conf).delete(outPath, true);
// FileOutputFormat.setOutputPath(conf, outPath);
DBConfiguration.configureDB(conf,
"com.mysql.jdbc.Driver"
,
"jdbc:mysql://192.168.1.101:3306/test"
,
"root"
,
"root"
);
String[] fields = {
"id"
,
"name"
};
// 从 t 表读数据
DBInputFormat.setInput(conf, StudentinfoRecord.
class
,
"t"
,
null
,
"id"
, fields);
// mapreduce 将数据输出到 t2 表
DBOutputFormat.setOutput(conf,
"t2"
,
"id"
,
"name"
);
// conf.setMapperClass(org.apache.hadoop.mapred.lib.IdentityMapper.class);
conf.setMapperClass(DBInputMapper.
class
);
conf.setReducerClass(MyReducer.
class
);
JobClient.runJob(conf);
}
}
|
6、结果：
执行两次后，你可以看到mysql结果：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
|
mysql>
select
*
from
t2;
+
------+-------+
| id |
name
|
+
------+-------+
| 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 |
| 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 |
+
------+-------+
26
rows
in
set
(0.00 sec)
mysql>
|
7、日志：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
|
13
/07/29
02:33:03 WARN mapred.JobClient: Use GenericOptionsParser
for
parsing the arguments. Applications should implement Tool
for
the same.
13
/07/29
02:33:03 INFO filecache.TrackerDistributedCacheManager: Creating mysql-connector-java-5.0.8-bin.jar
in
/tmp/hadoop-june/mapred/local/archive/-8943686319031389138_-1232673160_640840668/192
.168.1.101
/tmp-work--8372797484204470322
with rwxr-xr-x
13
/07/29
02:33:03 INFO filecache.TrackerDistributedCacheManager: Cached hdfs:
//192
.168.1.101:9000
/tmp/mysql-connector-java-5
.0.8-bin.jar as
/tmp/hadoop-june/mapred/local/archive/-8943686319031389138_-1232673160_640840668/192
.168.1.101
/tmp/mysql-connector-java-5
.0.8-bin.jar
13
/07/29
02:33:03 INFO filecache.TrackerDistributedCacheManager: Cached hdfs:
//192
.168.1.101:9000
/tmp/mysql-connector-java-5
.0.8-bin.jar as
/tmp/hadoop-june/mapred/local/archive/-8943686319031389138_-1232673160_640840668/192
.168.1.101
/tmp/mysql-connector-java-5
.0.8-bin.jar
13
/07/29
02:33:03 INFO mapred.JobClient: Running job: job_local_0001
13
/07/29
02:33:03 INFO mapred.MapTask: numReduceTasks: 1
13
/07/29
02:33:03 INFO mapred.MapTask: io.
sort
.mb = 100
13
/07/29
02:33:03 INFO mapred.MapTask: data buffer = 79691776
/99614720
13
/07/29
02:33:03 INFO mapred.MapTask: record buffer = 262144
/327680
13
/07/29
02:33:03 INFO mapred.MapTask: Starting flush of map output
13
/07/29
02:33:03 INFO mapred.MapTask: Finished spill 0
13
/07/29
02:33:03 INFO mapred.Task: Task:attempt_local_0001_m_000000_0 is
done
. And is
in
the process of commiting
13
/07/29
02:33:04 INFO mapred.JobClient: map 0% reduce 0%
13
/07/29
02:33:06 INFO mapred.LocalJobRunner:
13
/07/29
02:33:06 INFO mapred.Task: Task
'attempt_local_0001_m_000000_0'
done
.
13
/07/29
02:33:06 INFO mapred.LocalJobRunner:
13
/07/29
02:33:06 INFO mapred.Merger: Merging 1 sorted segments
13
/07/29
02:33:06 INFO mapred.Merger: Down to the last merge-pass, with 1 segments left of total size: 235 bytes
13
/07/29
02:33:06 INFO mapred.LocalJobRunner:
13
/07/29
02:33:06 INFO mapred.Task: Task:attempt_local_0001_r_000000_0 is
done
. And is
in
the process of commiting
13
/07/29
02:33:07 INFO mapred.JobClient: map 100% reduce 0%
13
/07/29
02:33:09 INFO mapred.LocalJobRunner: reduce > reduce
13
/07/29
02:33:09 INFO mapred.Task: Task
'attempt_local_0001_r_000000_0'
done
.
13
/07/29
02:33:09 WARN mapred.FileOutputCommitter: Output path is null
in
cleanup
13
/07/29
02:33:10 INFO mapred.JobClient: map 100% reduce 100%
13
/07/29
02:33:10 INFO mapred.JobClient: Job complete: job_local_0001
13
/07/29
02:33:10 INFO mapred.JobClient: Counters: 18
13
/07/29
02:33:10 INFO mapred.JobClient: File Input Format Counters
13
/07/29
02:33:10 INFO mapred.JobClient: Bytes Read=0
13
/07/29
02:33:10 INFO mapred.JobClient: File Output Format Counters
13
/07/29
02:33:10 INFO mapred.JobClient: Bytes Written=0
13
/07/29
02:33:10 INFO mapred.JobClient: FileSystemCounters
13
/07/29
02:33:10 INFO mapred.JobClient: FILE_BYTES_READ=1211691
13
/07/29
02:33:10 INFO mapred.JobClient: HDFS_BYTES_READ=1081704
13
/07/29
02:33:10 INFO mapred.JobClient: FILE_BYTES_WRITTEN=2392844
13
/07/29
02:33:10 INFO mapred.JobClient: Map-Reduce Framework
13
/07/29
02:33:10 INFO mapred.JobClient: Map output materialized bytes=239
13
/07/29
02:33:10 INFO mapred.JobClient: Map input records=13
13
/07/29
02:33:10 INFO mapred.JobClient: Reduce shuffle bytes=0
13
/07/29
02:33:10 INFO mapred.JobClient: Spilled Records=26
13
/07/29
02:33:10 INFO mapred.JobClient: Map output bytes=207
13
/07/29
02:33:10 INFO mapred.JobClient: Map input bytes=13
13
/07/29
02:33:10 INFO mapred.JobClient: SPLIT_RAW_BYTES=75
13
/07/29
02:33:10 INFO mapred.JobClient: Combine input records=0
13
/07/29
02:33:10 INFO mapred.JobClient: Reduce input records=13
13
/07/29
02:33:10 INFO mapred.JobClient: Reduce input
groups
=13
13
/07/29
02:33:10 INFO mapred.JobClient: Combine output records=0
13
/07/29
02:33:10 INFO mapred.JobClient: Reduce output records=13
13
/07/29
02:33:10 INFO mapred.JobClient: Map output records=13
|
8、REF：
新版 API 写法：
http://superlxw1234.iteye.com/blog/1880712
老版：
http://blog.youkuaiyun.com/dajuezhao/article/details/5799371
http://www.zhengmenbb.com/archives/583.htm
扫一扫
1677
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
