【windows】MapReduce 项目从MySQL中获取数据，再存到MySQL中

最新推荐文章于 2022-06-27 16:02:39 发布

han_mj

最新推荐文章于 2022-06-27 16:02:39 发布

阅读量241

点赞数

分类专栏： Hadoop 文章标签： mysql mapreduce hadoop

本文链接：https://blog.csdn.net/qq_41726230/article/details/105779519

版权

Hadoop 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

环境：
hadoop: 2.7.7
MySQL: 5.5
Windows 10
Java: 1.8
IDEA: 2018.3

上一篇文章：【windows】IDEA新建MapReduce项目

上一次已经研究过如何用IDEA写一个简单的MapReduce项目，然而很明显实际项目中不需要从文件系统（HDFS）中读入，从文件系统中读出。而是需要从数据库（MySQL）中读入，再将计算的结果输入到数据库中。所以这一次我们建立一个简单的基于MySQL的MapReduce项目。

一、建立数据库study，mptest表和xieru表。mptest表是输入表，xieru表是输出表。

DROP TABLE IF EXISTS `mptest`;
CREATE TABLE `mptest` (
  `id` varchar(30) NOT NULL DEFAULT '',
  `name` varchar(255) DEFAULT NULL,
  `txt` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOCK TABLES `mptest` WRITE;
INSERT INTO `mptest` VALUES ('1','zhangsan','javascript'),('2','lisi','C'),('3','wangwu','C++'),('4','chenliu','java'),('5','zoutao5','java'),('6','zoutao','python');
UNLOCK TABLES;

DROP TABLE IF EXISTS `xieru`;
CREATE TABLE `xieru` (
  `word` varchar(255) DEFAULT NULL COMMENT 'leibie',
  `count` int(11) DEFAULT NULL COMMENT 'shul'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='xie';

二、IDEA新建Maven项目，命名为mapreduce-mysql-test

三、项目结构如下。项目文件的具体代码见最后。
在这里插入图片描述

四、实现从数据库中得到数据，并输出到数据库。

首先通过JDBC连接数据库：

String driverClass = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://127.0.0.1/study";
String userName = "root";
String passWord = "root";
// 设置数据库配置
DBConfiguration.configureDB(job.getConfiguration(), driverClass, url, userName, passWord);

再使用DBInputFormat类的setInput方法从数据库中得到数据，setInput方法的第二个参数是输入信息的实体类，需要实现DBWritable接口，覆盖readFields方法。第三个参数是查询SQL语句用来得到数据。第四个参数是记录的数量。

DBInputFormat.setInput(job, MapTestWritable.class, "select id,name,txt from mptest", "select count(*) from mptest");

再利用DBOutputFormat类的setOutput方法配置输出的表和对应的字段。setOutput方法的第二个参数是表明，从第3个参数开始都是字段名。

DBOutputFormat.setOutput(job,"xieru","word","count");

同时，输入的实体类也需要实现DBWritable接口，覆盖write方法。

五、具体代码

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.hadoop</groupId>
    <artifactId>mapreduce-mysql-test</artifactId>
    <version>1.0-SNAPSHOT</version>

    <dependencies>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>3.8.1</version>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>org.apache.hadoop</groupId>
            <artifactId>hadoop-core</artifactId>
        </dependency>

        <dependency>
            <groupId>org.apache.hadoop</groupId>
            <artifactId>hadoop-common</artifactId>
            <version>2.7.7</version>
        </dependency>

        <dependency>
            <groupId>org.apache.hadoop</groupId>
            <artifactId>hadoop-hdfs</artifactId>
            <version>2.7.7</version>
        </dependency>

        <dependency>
            <groupId>org.apache.hadoop</groupId>
            <artifactId>hadoop-mapreduce-client-core</artifactId>
            <version>2.7.7</version>
        </dependency>

        <dependency>
            <groupId>org.apache.hadoop</groupId>
            <artifactId>hadoop-mapreduce-client-common</artifactId>
            <version>2.7.7</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.48</version>
        </dependency>

    </dependencies>


</project>

MapTestWritable.java

package pojo;

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

import java.io.DataInput;
import java.io.DataOutput;
import java.io.IOException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class MapTestWritable implements DBWritable {

    private String id;
    private String name;
    private String txt;

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getTxt() {
        return txt;
    }

    public void setTxt(String txt) {
        this.txt = txt;
    }

    public void write(PreparedStatement preparedStatement) throws SQLException {

    }

    public void readFields(ResultSet resultSet) throws SQLException {
        id = resultSet.getString(1);
        name = resultSet.getString(2);
        txt = resultSet.getString(3);
    }
}

XieruWritable.java

package pojo;

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

import java.io.DataInput;
import java.io.DataOutput;
import java.io.IOException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class XieruWritable implements DBWritable{

    private String word = "";
    private int count = 0;

    public String getWord() {
        return word;
    }

    public void setWord(String word) {
        this.word = word;
    }

    public int getCount() {
        return count;
    }

    public void setCount(int count) {
        this.count = count;
    }

    public void write(PreparedStatement preparedStatement) throws SQLException {
        preparedStatement.setString(1,word);
        preparedStatement.setInt(2,count);
    }

    public void readFields(ResultSet resultSet) throws SQLException {

    }
}

JDBCMapper.java

package mapper;

import org.apache.hadoop.io.IntWritable;
import org.apache.hadoop.io.LongWritable;
import org.apache.hadoop.io.Text;
import org.apache.hadoop.mapreduce.Mapper;
import pojo.MapTestWritable;

import java.io.IOException;

public class JDBCMapper extends Mapper<LongWritable, MapTestWritable, Text, IntWritable>{

    @Override
    protected void map(LongWritable key, MapTestWritable value, Context context) throws IOException, InterruptedException {
        //拿到一行文本内容，记得要是String类型
        String line = value.getTxt();
        //将这行文本切分成单词，以空格切分
        String[] arr = line.split(" ");
        for(String s : arr){
            //<单词，1>
            context.write(new Text(s),new IntWritable(1));
        }
    }

}

JDBCReducer.java。这一步中，输出的键是一个XieruWritable对象，输出的值是一个空值。在输出XieruWritable对象到数据库的时候，就使用了XieruWritable类的write方法。

package reducer;

import org.apache.hadoop.io.IntWritable;
import org.apache.hadoop.io.NullWritable;
import org.apache.hadoop.io.Text;
import org.apache.hadoop.mapreduce.Reducer;
import pojo.XieruWritable;

import java.io.IOException;

public class JDBCReducer extends Reducer<Text, IntWritable, XieruWritable, NullWritable> {

    @Override
    protected void reduce(Text key,Iterable<IntWritable> values, Context context) throws IOException, InterruptedException {
        //定义一个计数器
        int count = 0;
        //遍历这一组kv的所有v，累加到count中
        for (IntWritable value : values) {
            count += value.get();
        }
        XieruWritable keyOut = new XieruWritable();
        keyOut.setWord(key.toString());
        keyOut.setCount(count);
        // NullWritable 充当占位符，意思就是只输出MyDBWritable对象
        context.write(keyOut, NullWritable.get());
    }
}

AppConf.java。加载hadoop.dll文件。

public class AppConf {

    static {
        try {
            // 设置 HADOOP_HOME 目录
            System.setProperty("hadoop.home.dir", "D:/hadoop-2.7.7/");
            // 加载库文件
            System.load("D:/hadoop-2.7.7/bin/hadoop.dll");
        } catch (UnsatisfiedLinkError e) {
            System.err.println("Native code library failed to load.\n" + e);
            System.exit(1);
        }
    }

}

Application.java。主程序类，从这里启动。


import mapper.JDBCMapper;
import org.apache.hadoop.io.NullWritable;
import pojo.MapTestWritable;

import pojo.XieruWritable;


import org.apache.hadoop.conf.Configuration;
import org.apache.hadoop.fs.FileSystem;
import org.apache.hadoop.fs.Path;
import org.apache.hadoop.io.LongWritable;
import org.apache.hadoop.io.IntWritable;
import org.apache.hadoop.io.Text;
import org.apache.hadoop.mapreduce.Job;
import org.apache.hadoop.mapreduce.lib.input.FileInputFormat;
import org.apache.hadoop.mapreduce.lib.output.FileOutputFormat;
import org.apache.hadoop.mapreduce.lib.db.DBConfiguration;
import org.apache.hadoop.mapreduce.lib.db.DBInputFormat;
import org.apache.hadoop.mapreduce.lib.db.DBOutputFormat;
import reducer.JDBCReducer;

import java.io.IOException;

public class Application {

    public static void main(String[] args){
        try {
            Configuration configuration = new Configuration();
            configuration.set("fs.defaultFS", "hdfs://localhost:9000");
            Job job = Job.getInstance(configuration);

            job.setJarByClass(Application.class);

            job.setInputFormatClass(DBInputFormat.class); //read

            String driverClass = "com.mysql.jdbc.Driver";
            String url = "jdbc:mysql://127.0.0.1/study";
            String userName = "root";
            String passWord = "root";
            // 设置数据库配置
            DBConfiguration.configureDB(job.getConfiguration(), driverClass, url,
                    userName, passWord);

            // 设置数据输入内容-sql查询数据作为输入数据
            DBInputFormat.setInput(job, MapTestWritable.class,
                    "select id,name,txt from mptest",
                    "select count(*) from mptest");


            //把结果写入到数据表中对应字段
            DBOutputFormat.setOutput(job,"xieru","word","count");

            job.setMapperClass(JDBCMapper.class); // 设置mapper类
            job.setReducerClass(JDBCReducer.class); // 设置reduecer类

            job.setMapOutputKeyClass(Text.class); // 设置之map输出key
            job.setMapOutputValueClass(IntWritable.class); // 设置map输出value
            job.setOutputKeyClass(XieruWritable.class); // 设置reduce 输出key
            job.setOutputValueClass(NullWritable.class); // 设置reduce输出value
            System.exit(job.waitForCompletion(true) ? 0 : 1);


        }catch (Exception e){
            e.printStackTrace();
        }


    }
}