MapReduce InputFormat——DBInputFormat
一、背景
為了方便MapReduce直接訪問關係型資料庫(Mysql,Oracle),Hadoop提供了DBInputFormat和DBOutputFormat兩個類。通過
DBInputFormat類把資料庫表資料讀入到HDFS,根據DBOutputFormat類把MapReduce產生的結果集匯入到資料庫表中。
二、技術細節
1、DBInputFormat(Mysql為例),先建立表:
CREATE TABLE studentinfo ( id INTEGER NOT NULL PRIMARY KEY, name VARCHAR(32) NOT NULL);2、由於0.20版本對DBInputFormat和DBOutputFormat支援不是很好,該例用了0.19版本來說明這兩個類的用法。3、DBInputFormat用法如下:
- public class DBInput {
- // 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 void write(DataOutput out) throws IOException {
- out.writeInt(this.id);
- Text.writeString(out, this.name);
- }
- public void readFields(ResultSet result) throws SQLException {
- this.id = result.getInt(1);
- this.name = result.getString(2);
- }
- public void write(PreparedStatement stmt) throws SQLException {
- stmt.setInt(1, this.id);
- stmt.setString(2, this.name);
- }
- public String toString() {
- return new String(this.id + " " + this.name);
- }
- }
- public 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 void main(String[] args) throws IOException {
- JobConf conf = new JobConf(DBInput.class);
- DistributedCache.addFileToClassPath(new Path(
- "/lib/mysql-connector-java-5.1.0-bin.jar"), conf);
- conf.setMapperClass(DBInputMapper.class);
- conf.setReducerClass(IdentityReducer.class);
- conf.setMapOutputKeyClass(LongWritable.class);
- conf.setMapOutputValueClass(Text.class);
- conf.setOutputKeyClass(LongWritable.class);
- conf.setOutputValueClass(Text.class);
- conf.setInputFormat(DBInputFormat.class);
- FileOutputFormat.setOutputPath(conf, new Path("/hua01"));
- DBConfiguration.configureDB(conf, "com.mysql.jdbc.Driver",
- "jdbc:mysql://192.168.3.244:3306/hadoop", "hua", "hadoop");
- String[] fields = { "id", "name" };
- DBInputFormat.setInput(conf, StudentinfoRecord.class, "studentinfo",
- null, "id", fields);
- JobClient.runJob(conf);
- }
- }
a)StudnetinfoRecord類的變數為表欄位,實現Writable和DBWritable兩個介面。
實現Writable的方法:
- public void readFields(DataInput in) throws IOException {
- this.id = in.readInt();
- this.name = Text.readString(in);
- }
- public void write(DataOutput out) throws IOException {
- out.writeInt(this.id);
- Text.writeString(out, this.name);
- }
實現DBWritable的方法:
- public void readFields(ResultSet result) throws SQLException {
- this.id = result.getInt(1);
- this.name = result.getString(2);
- }
- public void write(PreparedStatement stmt) throws SQLException {
- stmt.setInt(1, this.id);
- stmt.setString(2, this.name);
- }
b)讀入Mapper的value型別是StudnetinfoRecord。
c)配置如何連入資料庫,讀出表studentinfo資料。
- DBConfiguration.configureDB(conf, "com.mysql.jdbc.Driver",
- "jdbc:mysql://192.168.3.244:3306/hadoop", "hua", "hadoop");
- String[] fields = { "id", "name" };
- DBInputFormat.setInput(conf, StudentinfoRecord.class, "studentinfo", null, "id", fields);
4、DBOutputFormat用法如下:
- public class DBOutput {
- 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 void write(DataOutput out) throws IOException {
- out.writeInt(this.id);
- Text.writeString(out, this.name);
- }
- public void readFields(ResultSet result) throws SQLException {
- this.id = result.getInt(1);
- this.name = result.getString(2);
- }
- public void write(PreparedStatement stmt) throws SQLException {
- stmt.setInt(1, this.id);
- stmt.setString(2, this.name);
- }
- public String toString() {
- return new String(this.id + " " + this.name);
- }
- }
- public static class MyReducer extends MapReduceBase implements
- Reducer<LongWritable, Text, StudentinfoRecord, Text> {
- public void reduce(LongWritable key, Iterator<Text> values,
- OutputCollector<StudentinfoRecord, Text> output, Reporter reporter)
- throws IOException {
- String[] splits = values.next().toString().split("/t");
- 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(DBOutput.class);
- conf.setInputFormat(TextInputFormat.class);
- conf.setOutputFormat(DBOutputFormat.class);
- FileInputFormat.setInputPaths(conf, new Path("/hua/hua.bcp"));
- DBConfiguration.configureDB(conf, "com.mysql.jdbc.Driver",
- "jdbc:mysql://192.168.3.244:3306/hadoop", "hua", "hadoop");
- DBOutputFormat.setOutput(conf, "studentinfo", "id", "name");
- conf.setMapperClass(org.apache.hadoop.mapred.lib.IdentityMapper.class);
- conf.setReducerClass(MyReducer.class);
- JobClient.runJob(conf);
- }
- }
a)StudnetinfoRecord類的變數為表欄位,實現Writable和DBWritable兩個介面,同.DBInputFormat的StudnetinfoRecord類。
b)輸出Reducer的key/value型別是StudnetinfoRecord。
c)配置如何連入資料庫,輸出結果到表studentinfo。
- DBConfiguration.configureDB(conf, "com.mysql.jdbc.Driver",
- "jdbc:mysql://192.168.3.244:3306/hadoop", "hua", "hadoop");
- DBOutputFormat.setOutput(conf, "studentinfo", "id", "name");
三、總結
執行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 /lib
b)在mr程式提交job前,新增語句:istributedCache.addFileToClassPath(new Path("/lib/mysql- connector-java- 5.1.0-bin.jar"), conf);
3、雖然API用的是0.19的,但是使用0.20的API一樣可用,只是會提示方法已過時而已。
相關文章
- MapReduce InputFormat之FileInputFormatORM
- MapReduce之自定義InputFormatORM
- MapReduce初探
- MapReduce理解
- MapReduce: 提高MapReduce效能的七點建議[譯]
- MapReduce 簡介
- MapReduce之topN
- Mongodb MapReduce使用MongoDB
- Lab 1: MapReduce
- MapReduce 示例:減少 Hadoop MapReduce 中的側連線Hadoop
- MapReduce執行流程
- MapReduce工作流程
- mapreduce框架詳解框架
- MapReduce模型講解模型
- MapReduce&&HadoopHadoop
- MapReduce(四):shuffer原理
- 一個簡單的MapReduce示例(多個MapReduce任務處理)
- MapReduce之自定義OutputFormatORM
- MapReduce之WritableComparable排序排序
- Hadoop學習——MapReduceHadoop
- MapReduce過程詳解
- MapReduce框架-Join的使用框架
- hadoop_MapReduce yarnHadoopYarn
- HDFS/MapReduce及應用
- MapReduce程式執行流程
- MapReduce實戰:倒排索引索引
- mapreduce實現倒排索引索引
- MapReduce之自定義partitioner
- [ZT]MapReduce explained in 41 wordsAI
- ODPS MapReduce入門
- MapReduce: number of mappers/reducersAPP
- MapReduce 大量小檔案
- Hadoop(十四)MapReduce概述Hadoop
- 詞頻統計mapreduce
- 使用MapReduce執行WordCount案例
- MapReduce的執行流程概述
- Hadoop面試題之MapReduceHadoop面試題
- MapReduce--程式設計模板程式設計