采用MapReduce读取数据库的数据

最新推荐文章于 2024-05-15 22:52:13 发布

java庞

最新推荐文章于 2024-05-15 22:52:13 发布

阅读量272

点赞数 1

文章标签：数据库 mapreduce hadoop

本文链接：https://blog.csdn.net/weixin_44899752/article/details/129998534

版权

采用MapReduce读取数据库的数据

数据库表

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `usersex` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `yw` int(11) NULL DEFAULT NULL,
  `sx` int(11) NULL DEFAULT NULL,
  `yy` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, '小文', '男', 10, 20, 30);
INSERT INTO `user` VALUES (2, '李娜', '女', 100, 120, 130);
INSERT INTO `user` VALUES (3, '丽丽', '女', 90, 120, 120);
INSERT INTO `user` VALUES (4, '美娜', '女', 80, 90, 120);

SET FOREIGN_KEY_CHECKS = 1;

实体类

package com.hadoop.demo4;

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

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * 读取数据库的数据
 */
public class User implements DBWritable {
    int id;
    String username;
    String usersex;
    int yw;//语文
    int sx;//数学
    int yy;//英语

    /**
     * 序列化
     * @param preparedStatement
     * @throws SQLException
     */
    public void write(PreparedStatement preparedStatement) throws SQLException {
        preparedStatement.setInt(1,this.id);
        preparedStatement.setString(2,this.username);
        preparedStatement.setString(3,this.usersex);
        preparedStatement.setInt(4,this.yw);
        preparedStatement.setInt(5,this.sx);
        preparedStatement.setInt(6,this.yy);

    }

    /**
     * 反序列化
     * @param resultSet
     * @throws SQLException
     */
    public void readFields(ResultSet resultSet) throws SQLException {
            this.id = resultSet.getInt(1);
            this.username = resultSet.getString(2);
            this.usersex = resultSet.getString(3);
            this.yw = resultSet.getInt(4);
            this.sx = resultSet.getInt(5);
            this.yy = resultSet.getInt(6);
    }

    @Override
    public String toString() {
        return this.id+"\t"+this.username+"\t"+this.usersex+"\t"+this.yw+"\t"+this.sx+"\t"+this.yy;
    }
}

MapReduce

package com.hadoop.demo4;

import org.apache.hadoop.conf.Configuration;
import org.apache.hadoop.fs.Path;
import org.apache.hadoop.io.LongWritable;
import org.apache.hadoop.io.NullWritable;
import org.apache.hadoop.io.Text;
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.output.FileOutputFormat;

import java.io.IOException;

public class UserInputMap extends Mapper<LongWritable,User, Text, NullWritable> {
    @Override
    protected void map(LongWritable key, User value, Context context) throws IOException, InterruptedException {
        // "1   小文  男   10  20  30"
        context.write(new Text(value.toString()),NullWritable.get());

    }
}

class UserInputReduce extends Reducer<Text, NullWritable,Text, NullWritable>{
    @Override
    protected void reduce(Text key, Iterable<NullWritable> values, Context context) throws IOException, InterruptedException {
        context.write(key,NullWritable.get());
    }
}
class UserInputTest{
    public static void main(String[] args) throws IOException, ClassNotFoundException, InterruptedException {
        Configuration conf = new Configuration();
        DBConfiguration.configureDB(conf,"com.mysql.jdbc.Driver",
                "jdbc:mysql://127.0.0.1:3306/hadoop","root","12345678");
        Job job = Job.getInstance(conf);
        //加载类
        job.setJarByClass(UserInputTest.class);
        job.setMapperClass(UserInputMap.class);
        job.setReducerClass(UserInputReduce.class);

        //设置实体类与数据库的映射
        DBInputFormat.setInput(job,User.class,"user",null,null
        ,"id","username","usersex","yw","sx","yy");
        //设置map的key输出的类型和value输出的类型
        job.setMapOutputKeyClass(Text.class);
        job.setMapOutputValueClass(NullWritable.class);
        //设置reduce的输出的key的类型和value输出的类型
        job.setOutputKeyClass(Text.class);
        job.setOutputValueClass(NullWritable.class);

        Path outpath = new Path("user");
        outpath.getFileSystem(conf).delete(outpath,true);
        FileOutputFormat.setOutputPath(job,outpath);

        boolean b = job.waitForCompletion(true);
        System.exit(b?0:1);
    }
}