用mapreduce将hdfs的数据存到mysql中（虚拟机的mysql）

依赖

 

        <dependency>
            <groupId>org.apache.hadoop</groupId>
            <artifactId>hadoop-client</artifactId>
            <version>2.9.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.hadoop</groupId>
            <artifactId>hadoop-auth</artifactId>
            <version>2.9.2</version>
        </dependency>

        <!--对接MysQL-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.38</version>
        </dependency>

代码

package com.baizhi.demo06;

import org.apache.hadoop.conf.Configuration;
import org.apache.hadoop.fs.Path;
import org.apache.hadoop.io.IntWritable;
import org.apache.hadoop.io.Text;
import org.apache.hadoop.mapreduce.Job;
import org.apache.hadoop.mapreduce.MRJobConfig;
import org.apache.hadoop.mapreduce.filecache.DistributedCache;
import org.apache.hadoop.mapreduce.lib.db.DBConfiguration;
import org.apache.hadoop.mapreduce.lib.db.DBOutputFormat;
import org.apache.hadoop.mapreduce.lib.input.FileInputFormat;

import java.io.IOException;

public class WordCountJob {
    public static String inputFilePath = "hdfs://spark:9000/input/word.txt";
    public static String tableName = "ksy";
    public static String[] fields = {"word", "total"};

    public static void main(String[] args) {
        System.setProperty("HADOOP_USER_NAME","root");
        Configuration conf = new Configuration();
        DBConfiguration.configureDB(conf,
                "com.mysql.jdbc.Driver",
                "jdbc:mysql://spark:3306/keyword",
                "root", "123456");
        try {
            conf.addResource("core-site.xml");
            conf.addResource("hdfs-site.xml");
            conf.addResource("yarn-site.xml");
            conf.addResource("mapred-site.xml");
            conf.set(MRJobConfig.JAR,"D:\\Program Files\\feiq\\Recv Files\\SparkRDD\\target\\sparkrdd-1.0-SNAPSHOT.jar");
            conf.set("mapreduce.app-submission.cross-platform", "true");
            Job job = Job.getInstance(conf);
            job.addArchiveToClassPath(new Path("hdfs://spark:9000/lib/mysql/mysql-connector-java-5.1.38.jar"));
            job.setJarByClass(WordCountJob.class);
            job.setJobName("MyWordCountDB");
            job.setMapOutputKeyClass(Text.class);
            job.setMapOutputValueClass(IntWritable.class);
            job.setMapperClass(WordCountMapper.class);
            job.setReducerClass(WordCountReducer.class);
            FileInputFormat.setInputPaths(job, new Path(inputFilePath));
            DBOutputFormat.setOutput(job, tableName, fields);
            job.waitForCompletion(true);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (InterruptedException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
}

package com.baizhi.demo06;
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 java.io.IOException;

public class WordCountMapper extends Mapper<LongWritable,Text,Text,IntWritable> {
    @Override
    protected void map(LongWritable key, Text value, Context context)
            throws IOException, InterruptedException {
        //获取每一个输入行
        String line = value.toString();
        //get every separated word
        String [] word = line.split(" ");

        for(int i = 0;i< word.length;i++){
            System.out.println(i);
            context.write(new Text(word[i]),new IntWritable(1));
        }
    }
}

package com.baizhi.demo06;

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 java.io.IOException;

public class WordCountReducer extends Reducer<Text,IntWritable,ReceiveTable,NullWritable> {
    @Override
    protected void reduce(Text key, Iterable<IntWritable> values, Context context)
            throws IOException, InterruptedException {
        int sum = 0;
        for(IntWritable intW : values){
            sum += intW.get();
        }
        System.out.println("sum"+sum);
        ReceiveTable receiveTable = new ReceiveTable(key.toString(),sum);
        context.write(receiveTable,null);
    }
}

package com.baizhi.demo06;

import org.apache.hadoop.io.Text;
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 ReceiveTable implements Writable,DBWritable{
    //column1:keyword  column2:number
    private String keyWord;
    private int number;

    public ReceiveTable(){

    }
    public ReceiveTable(String keyWord,int number){
        this.keyWord = keyWord;
        this.number = number;
    }
    /**Writable  only serializable and deseiralizable
     *
     * @param out
     * @throws IOException
     */
    public void write(DataOutput out) throws IOException {
        out.writeInt(this.number);
        /*1.将this.keyWord以UTF8的编码方式写入到out中[Write a UTF8 encoded string to out]
        2.其实这个效果和out.writeInt(this.number)是一样的，只不过是DataOutput类型没有writeString()这个方法，
        所以借用了Text.writeString(...)这个方法
         */
        Text.writeString(out, this.keyWord);
    }

    public void readFields(DataInput in) throws IOException {
        this.number = in.readInt();
        this.keyWord = in.readUTF();
    }


    public void write(PreparedStatement statement) throws SQLException {
        statement.setString(1,this.keyWord);
        statement.setInt(2,this.number);
    }

    public void readFields(ResultSet resultSet) throws SQLException {
        this.keyWord = resultSet.getString(1);
        this.number = resultSet.getInt(2);
    }
}

效果

mysql> select * from ksy;
+--------+-------+
| word   | total |
+--------+-------+
| baby   |     2 |
| come   |     1 |
| hello  |     1 |
| is     |     1 |
| la     |     2 |
| lal    |     1 |
| list   |     1 |
| mysqll |     1 |
| on     |     1 |
| world  |     1 |
+--------+-------+
10 rows in set (0.00 sec)

mysql>