MapReduce读取普通写进数据库

首先创建一个空表

CREATE TABLE `itheima_goods_mr_write` (
`goodsId` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '商品id',
`goodsSn` varchar(20) NOT NULL COMMENT '商品编号',
`goodsName` varchar(200) NOT NULL COMMENT '商品名称',
`marketPrice` decimal(11,2) NOT NULL DEFAULT '0.00' COMMENT '市场价',
`shopPrice` decimal(11,2) NOT NULL DEFAULT '0.00' COMMENT '门店价',
`saleNum` int(11) NOT NULL DEFAULT '0' COMMENT '总销售量',
PRIMARY KEY (`goodsId`)
) ENGINE=InnoDB AUTO_INCREMENT=115909 DEFAULT CHARSET=utf8;

然后编写他JavaBean  记住要去继承

Writable, DBWritable这两个类，实现里面的方法

Writable是序列化的类   DBWritable是设置数据库的类

package com.zhan.mapreduce.db.read;

import org.apache.hadoop.io.Writable;
import org.apache.hadoop.mapreduce.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;

/**
 * @Classname GoodBean
 * @Description MrZhan
 * @Date 2021/10/10 12:33
 * @Created by 1
 */
public class GoodBean implements Writable, DBWritable {
    private long goodsId;//商品ID
    private String goodsSn;//商品编号
    private String goodsName;//商品名称
    private double marketPrice;//市场价
    private double shopPrice;//门店价
    private long saleNum;//总销售量

    public GoodBean() {
    }

    public GoodBean(long goodsId, String goodsSn, String goodsName, double marketPrice, double shopPrice, long saleNum) {
        this.goodsId = goodsId;
        this.goodsSn = goodsSn;
        this.goodsName = goodsName;
        this.marketPrice = marketPrice;
        this.shopPrice = shopPrice;
        this.saleNum = saleNum;
    }
    public void set(long goodsId, String goodsSn, String goodsName, double marketPrice, double shopPrice, long saleNum) {
        this.goodsId = goodsId;
        this.goodsSn = goodsSn;
        this.goodsName = goodsName;
        this.marketPrice = marketPrice;
        this.shopPrice = shopPrice;
        this.saleNum = saleNum;
    }

    public long getGoodsId() {
        return goodsId;
    }

    public void setGoodsId(long goodsId) {
        this.goodsId = goodsId;
    }

    public String getGoodsSn() {
        return goodsSn;
    }

    public void setGoodsSn(String goodsSn) {
        this.goodsSn = goodsSn;
    }

    public String getGoodsName() {
        return goodsName;
    }

    public void setGoodsName(String goodsName) {
        this.goodsName = goodsName;
    }

    public double getMarketPrice() {
        return marketPrice;
    }

    public void setMarketPrice(double marketPrice) {
        this.marketPrice = marketPrice;
    }

    public double getShopPrice() {
        return shopPrice;
    }

    public void setShopPrice(double shopPrice) {
        this.shopPrice = shopPrice;
    }

    public long getSaleNum() {
        return saleNum;
    }

    public void setSaleNum(long saleNum) {
        this.saleNum = saleNum;
    }

    @Override
    public String toString() {
        return  goodsId
                +"\t"+ goodsSn
                +"\t"+goodsName
                +"\t"+marketPrice
                +"\t"+shopPrice
                +"\t"+saleNum;
    }

    //序列方法
    @Override
    public void write(DataOutput dataOutput) throws IOException {
        dataOutput.writeLong(goodsId);
        dataOutput.writeUTF(goodsSn);
        dataOutput.writeUTF(goodsName);
        dataOutput.writeDouble(marketPrice);
        dataOutput.writeDouble(shopPrice);
        dataOutput.writeLong(saleNum);
    }
    //反序列方法 //注意顺序
    @Override
    public void readFields(DataInput dataInput) throws IOException {
        this.goodsId = dataInput.readLong();
        this.goodsSn = dataInput.readUTF();
        this.goodsName =dataInput.readUTF();
        this.marketPrice =dataInput.readDouble();
        this.shopPrice =dataInput.readDouble();
        this.saleNum =dataInput.readLong();
    }
    //在PreparedStatement中设置对象的字段 写数据操作
    @Override
    public void write(PreparedStatement statement) throws SQLException {
        statement.setLong(1,goodsId);
        statement.setString(2,goodsSn);
        statement.setString(3,goodsName);
        statement.setDouble(4,marketPrice);
        statement.setDouble(5,shopPrice);
        statement.setLong(6,saleNum);
    }
    //从ResultSet读取查询的结果  赋值给对象属性   读数据库操作
    @Override
    public void readFields(ResultSet resultSet) throws SQLException {
        this.goodsId = resultSet.getLong(1);
        this.goodsSn = resultSet.getString(2);
        this.goodsName =resultSet.getString(3);
        this.marketPrice =resultSet.getDouble(4);
        this.shopPrice =resultSet.getDouble(5);
        this.saleNum =resultSet.getLong(6);
    }
}

编写Mapper阶段的代码

package com.zhan.mapreduce.db.write;

import com.zhan.mapreduce.db.read.GoodBean;
import org.apache.hadoop.io.LongWritable;
import org.apache.hadoop.io.NullWritable;
import org.apache.hadoop.io.Text;
import org.apache.hadoop.mapreduce.Counter;
import org.apache.hadoop.mapreduce.Mapper;

import java.io.IOException;

/**
 * @Classname WriteMapper
 * @Description MrZhan
 * @Date 2021/10/10 16:13
 * @Created by 1
 */

/**
 * 读取文本数据，写入Mysql中  在map阶段中做读取文件  解析  封装对象
 */
public class WriteDBMapper extends Mapper<LongWritable, Text, NullWritable, GoodBean> {
    NullWritable outKey =NullWritable.get();
    GoodBean outValue = new GoodBean();

    @Override
    protected void map(LongWritable key, Text value, Context context) throws IOException, InterruptedException {
        //获取两个计数器  用于统计合法数据 和非法数据
        Counter sc = context.getCounter("mr_to_sql", "SUCCESS");
        Counter fc = context.getCounter("mr_to_sqk", "FAILED");

        String[] strings = value.toString().split("\t");
        //判断输入的数据是否有缺失   如果不满足需求 就是非法数据
        if (strings.length > 6){
            //合法数据  提取字段封装成对象的属性
            outValue.set(
                    Long.parseLong(strings[1]),
                    strings[2],
                    strings[3],
                    Double.parseDouble(strings[4]),
                    Double.parseDouble(strings[5]),
                    Long.parseLong(strings[6])
            );
            context.write(outKey,outValue);
            //合法数据加一
            sc.increment(1);
        }else{
            //非法数据 计数器加一
            fc.increment(1);
        }
    }
}

编写reduce阶段的代码

package com.zhan.mapreduce.db.write;

import com.zhan.mapreduce.db.read.GoodBean;
import org.apache.hadoop.io.NullWritable;
import org.apache.hadoop.mapreduce.Reducer;

import java.io.IOException;

/**
 * @Classname WriteDBMReduce
 * @Description MrZhan
 * @Date 2021/10/10 16:27
 * @Created by 1
 */
//todo 在使用过DBoutputFormat的时候 要求需要出去的Kye必须是DBWritable的实现  因为只会把Key写入数据库
public class WriteDBMReduce extends Reducer<NullWritable, GoodBean,GoodBean,NullWritable> {
    @Override
    protected void reduce(NullWritable key, Iterable<GoodBean> values, Context context) throws IOException, InterruptedException {
        for (GoodBean value : values) {
            context.write(value,key);
        }
    }
}

最后编写驱动类，注意数据库的文件格式要和当初的设置的一直

package com.zhan.mapreduce.db.write;

import com.zhan.mapreduce.db.read.GoodBean;
import com.zhan.mapreduce.db.read.ReadDBMapper;
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.lib.db.DBConfiguration;
import org.apache.hadoop.mapreduce.lib.db.DBInputFormat;
import org.apache.hadoop.mapreduce.lib.db.DBOutputFormat;
import org.apache.hadoop.mapreduce.lib.input.FileInputFormat;
import org.apache.hadoop.mapreduce.lib.output.FileOutputFormat;

import java.io.IOException;

/**
 * @Classname ReadDBDriver
 * @Description MrZhan
 * @Date 2021/10/10 13:39
 * @Created by 1
 */
public class WriteDBDriver {
    public static void main(String[] args) throws IOException, ClassNotFoundException, InterruptedException {
        //配置文件对象类
        Configuration conf = new Configuration();
        //todo 配置当前1作业需要的JDBC信息
        DBConfiguration.configureDB(
                conf,
                "com.mysql.jdbc.Driver",
                "jdbc:mysql:///itcast_shop?useUnicode=true&characterEncoding=utf8",
                "root",
                "root"
        );

        //创建作业的job类
        Job job = Job.getInstance(conf, WriteDBDriver.class.getSimpleName());
        //设置本次mr程序的驱动类
        job.setJarByClass(WriteDBDriver.class);

        //设置mapper类
        job.setMapperClass(WriteDBMapper.class);
        //设置程序最终输出的key 和 value类型
        job.setMapOutputKeyClass(NullWritable.class);
        job.setMapOutputValueClass(GoodBean.class);

        //设置reduce类
        job.setReducerClass(WriteDBMReduce.class);
        job.setOutputKeyClass(GoodBean.class);
        job.setOutputValueClass(NullWritable.class);

        //设置当前作业的输入文件路径
        FileInputFormat.setInputPaths(job,new Path("D:\\mysqlOut"));

        //todo 设置程序的输出类
        job.setOutputFormatClass(DBOutputFormat.class);
        //todo 设置当前作业写入数据库的类  字段
        DBOutputFormat.setOutput(
                job,
                "itheima_goods_mr_write",
                "goodsId","goodsSn","goodsName","marketPrice","shopPrice","saleNum"
        );
        boolean b = job.waitForCompletion(true);
        System.exit(b ? 0 : 1);
    }
}