hadoop读取mysql数据_一步一步跟我学习hadoop(7)----hadoop连接mysql数据库运行数据读写数据库操作...

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

执行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);

mysql数据库存储到hadoop hdfs

mysql表创建和数据初始化

DROP TABLE IF EXISTS `wu_testhadoop`;

CREATE TABLE `wu_testhadoop` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`title` varchar(255) DEFAULT NULL,

`content` varchar(255) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of wu_testhadoop

-- ----------------------------

INSERT INTO `wu_testhadoop` VALUES ('1', '123', '122312');

INSERT INTO `wu_testhadoop` VALUES ('2', '123', '123456');

定义hadoop数据訪问

mysql表创建完成后，我们须要定义hadoop訪问mysql的规则。

hadoop提供了org.apache.hadoop.io.Writable接口来实现简单的高效的可序列化的协议，该类基于DataInput和DataOutput来实现相关的功能。

hadoop对数据库訪问也提供了org.apache.hadoop.mapred.lib.db.DBWritable接口，当中write方法用于对PreparedStatement对象设定值，readFields方法用于对从数据库读取出来的对象进行列的值绑定。

以上两个接口的使用例如以下(内容是从源代码得来)

writable

public class MyWritable implements Writable {

// Some data

private int counter;

private long timestamp;

public void write(DataOutput out) throws IOException {

out.writeInt(counter);

out.writeLong(timestamp);

}

public void readFields(DataInput in) throws IOException {

counter = in.readInt();

timestamp = in.readLong();

}

public static MyWritable read(DataInput in) throws IOException {

MyWritable w = new MyWritable();

w.readFields(in);

return w;

}

}

DBWritable

public class MyWritable implements Writable, DBWritable {

// Some data

private int counter;

private long timestamp;

//Writable#write() implementation

public void write(DataOutput out) throws IOException {

out.writeInt(counter);

out.writeLong(timestamp);

}

//Writable#readFields() implementation

public void readFields(DataInput in) throws IOException {

counter = in.readInt();

timestamp = in.readLong();

}

public void write(PreparedStatement statement) throws SQLException {

statement.setInt(1, counter);

statement.setLong(2, timestamp);

}

public void readFields(ResultSet resultSet) throws SQLException {

counter = resultSet.getInt(1);

timestamp = resultSet.getLong(2);

}

}

数据库相应的实现

package com.wyg.hadoop.mysql.bean;

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 org.apache.hadoop.io.Text;

import org.apache.hadoop.io.Writable;

import org.apache.hadoop.mapred.lib.db.DBWritable;

public class DBRecord implements Writable, DBWritable{

private int id;

private String title;