Hadoop实战演练：搜索数据分析----计算结果存储到Mysql（3）

Myth丶恋晨 2021-09-11 03:42 283阅读 0赞

林炳文Evankaka原创作品。转载请注明出处[http://blog.csdn.net/evankaka][http_blog.csdn.net_evankaka]

摘要：本文主要讲了如何将hadoop的运行结果存储到mysql

工程源码下载：[https://github.com/appleappleapple/BigDataLearning/tree/master/Hadoop-Demo][https_github.com_appleappleapple_BigDataLearning_tree_master_Hadoop-Demo]

此文接：[Hadoop实战演练：搜索数据分析----TopK计算（2）][Hadoop_----TopK_2]在上中我们得到Hadoop输出的关键词搜索量TopK数据格式如下，这里将当成本文计算的输入数据

![20160915150413541][]

接下来我们要将这100条数据存储到Mysql

数据库建表语句：

CREATE TABLE `key_word` (
      `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
      `word` varchar(255),
      `total` bigint(20),
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8

2、代码

package com.lin.keyword;
    
    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.conf.Configuration;
    import org.apache.hadoop.fs.Path;
    import org.apache.hadoop.io.LongWritable;
    import org.apache.hadoop.io.Text;
    import org.apache.hadoop.io.Writable;
    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.DBOutputFormat;
    import org.apache.hadoop.mapreduce.lib.db.DBWritable;
    import org.apache.hadoop.mapreduce.lib.input.FileInputFormat;
    import org.apache.hadoop.util.GenericOptionsParser;
    
    /**
     * 功能概要：将top 搜索词保存到数据库
     * 
     * @author linbingwen
     */
    public class SaveResult {
        /**  
        * 实现DBWritable  
        *   
        * TblsWritable需要向mysql中写入数据  
        */  
       public static class TblsWritable implements Writable, DBWritable {  
           String tbl_name;  
           int tbl_age;  
     
           public TblsWritable() {  
           }  
     
           public TblsWritable(String name, int age) {  
               this.tbl_name = name;  
               this.tbl_age = age;  
           }  
    
           @Override  
           public void write(PreparedStatement statement) throws SQLException {  
               statement.setString(1, this.tbl_name);  
               statement.setInt(2, this.tbl_age);  
           }  
    
           @Override  
           public void readFields(ResultSet resultSet) throws SQLException {  
               this.tbl_name = resultSet.getString(1);  
               this.tbl_age = resultSet.getInt(2);  
           }  
    
           @Override  
           public void write(DataOutput out) throws IOException {  
               out.writeUTF(this.tbl_name);  
               out.writeInt(this.tbl_age);  
           }  
    
           @Override  
           public void readFields(DataInput in) throws IOException {  
               this.tbl_name = in.readUTF();  
               this.tbl_age = in.readInt();  
           }  
     
           public String toString() {  
               return new String(this.tbl_name + " " + this.tbl_age);  
           }  
       }
       
       public static class StudentMapper extends Mapper<LongWritable, Text, LongWritable, Text>{
           @Override
           protected void map(LongWritable key, Text value,Context context) throws IOException, InterruptedException {
               context.write(key, value);
           }
       }
       
       public static class StudentReducer extends Reducer<LongWritable, Text, TblsWritable, TblsWritable> {
           @Override
           protected void reduce(LongWritable key, Iterable<Text> values,Context context) throws IOException, InterruptedException {
               // values只有一个值，因为key没有相同的
               StringBuilder value = new StringBuilder();
               for(Text text : values){
                   value.append(text);
               }
               
               String[] studentArr = value.toString().split("\t");
               
               if(studentArr[0] != null){
                   String name = studentArr[1].trim();
                   
                   int age = 0;
                   try{
                       age = Integer.parseInt(studentArr[0].trim());
                   }catch(NumberFormatException e){
                   }
                   
                   context.write(new TblsWritable(name, age), null);  
               }
           }
       }
       
       public static void main(String[] args) throws Exception {
    	   Configuration conf = new Configuration();
    		
    	   DBConfiguration.configureDB(conf, "com.mysql.cj.jdbc.Driver","jdbc:mysql://localhost:3306/learning?serverTimezone=UTC","root", "linlin");  
    		
    		//设置hadoop的机器、端口
    		conf.set("mapred.job.tracker", "10.75.201.125:9000");
    		//设置输入输出文件目录
    		String[] ioArgs = new String[] { "hdfs://hmaster:9000/top_out"};
    		String[] otherArgs = new GenericOptionsParser(conf, ioArgs).getRemainingArgs();
    		if (otherArgs.length != 1) {
    			System.err.println("Usage:  <in> <out>");
    			System.exit(2);
    		}
    		//设置一个job
    		Job job = Job.getInstance(conf, "SaveResult");
    		job.setJarByClass(SaveResult.class);
    		
    		// 输入路径
    		FileInputFormat.addInputPath(job, new Path(otherArgs[0]));
            
            // Mapper
            job.setMapperClass(StudentMapper.class);
            // Reducer
            job.setReducerClass(StudentReducer.class);
            
            // mapper输出格式
            job.setOutputKeyClass(LongWritable.class);
            job.setOutputValueClass(Text.class);
            
            // 输入格式，默认就是TextInputFormat
            job.setOutputFormatClass(DBOutputFormat.class);  
            
            // 输出到哪些表、字段
            DBOutputFormat.setOutput(job, "key_word", "word", "total");
            System.exit(job.waitForCompletion(true) ? 0 : 1);
       }
       
    
    }

输出结果：

![20160915150706889][]

然后笔者用百度的Echars作了一个展示页面如下：

![20160915151804674][]

[http_blog.csdn.net_evankaka]: http://blog.csdn.net/evankaka
[https_github.com_appleappleapple_BigDataLearning_tree_master_Hadoop-Demo]: https://github.com/appleappleapple/BigDataLearning/tree/master/Hadoop-Demo
[Hadoop_----TopK_2]: http://blog.csdn.net/evankaka/article/details/52549730
[20160915150413541]: /images/20210811/c067b70e06ad44ea8dc7224a1f46adda.png
[20160915150706889]: /images/20210811/d80e28cae3cc46348dd7ab9744f190b1.png
[20160915151804674]: /images/20210811/a91e69b2f94d4259b6e9448b88e3cf51.png