HBase与MapReduce集成3-HBase2RDBMS

3）将HBase表的数据导出到RDBMS中

  HBase2RDBMS

       51.7. HBase MapReduce Summary to RDBMS

import java.io.IOException;
import org.apache.hadoop.conf.Configuration;
import org.apache.hadoop.conf.Configured;
import org.apache.hadoop.fs.Path;
import org.apache.hadoop.hbase.Cell;
import org.apache.hadoop.hbase.CellUtil;
import org.apache.hadoop.hbase.HBaseConfiguration;
import org.apache.hadoop.hbase.client.Put;
import org.apache.hadoop.hbase.client.Result;
import org.apache.hadoop.hbase.client.Scan;
import org.apache.hadoop.hbase.io.ImmutableBytesWritable;
import org.apache.hadoop.hbase.mapreduce.TableMapReduceUtil;
import org.apache.hadoop.hbase.mapreduce.TableMapper;
import org.apache.hadoop.hbase.util.Bytes;
import org.apache.hadoop.io.Text;
import org.apache.hadoop.mapreduce.Job;
import org.apache.hadoop.mapreduce.lib.output.FileOutputFormat;
import org.apache.hadoop.mapreduce.lib.output.TextOutputFormat;
import org.apache.hadoop.util.Tool;
import org.apache.hadoop.util.ToolRunner;

/*
 需求：
     将HBase表的数据导出到RDBMS中
 */
public class User2RDBMSMapReduce extends Configured implements Tool {

	// Mapper Class
	// Mapper<ImmutableBytesWritable, Result, KEYOUT, VALUEOUT>
	public static class ReadUserMapper extends TableMapper<Text, Text> {
		private Text mapOutputKey = new Text();
		@Override
		protected void setup(Context context) throws IOException,
				InterruptedException {
			JDBCHelper.createTable();
		}

		@Override
		protected void map(ImmutableBytesWritable key, Result value,
				Context context) throws IOException, InterruptedException {
			// get rowkey 
			String rowkey = Bytes.toString(key.get());
			
			// iterator
			for(Cell cell : value.rawCells()){
				String columnfamily =Bytes.toString(CellUtil.cloneFamily(cell));
				String columnqualifier =Bytes.toString(CellUtil.cloneQualifier(cell));
				String val=Bytes.toString(CellUtil.cloneValue(cell));
				int  result =JDBCHelper.insert(rowkey, columnfamily, columnqualifier, val);
				
				if(result !=1)
				{
					mapOutputKey.set(rowkey);
					context.write(mapOutputKey, null);
				}
			}
		}
	}



	// Driver
	public int run(String[] args) throws Exception {
		   if (args.length < 2) {
			      usage("Wrong number of arguments: " + args.length);
			      System.exit(-1);
			 }
		
		// create job
		Job job = Job.getInstance(this.getConf(),//
				this.getClass().getSimpleName());
		
		// set run job class
		job.setJarByClass(this.getClass());
		
		// set job
		Scan scan = new Scan();
		scan.setCaching(500);        // 1 is the default in Scan, which will be bad for MapReduce jobs
		scan.setCacheBlocks(false);  // don't set to true for MR jobs
		// set other scan attrs

		TableMapReduceUtil.initTableMapperJob(
		  args[0],        // input table
		  scan,               // Scan instance to control CF and attribute selection
		  ReadUserMapper.class,     // mapper class
		  Text.class,         // mapper output key
		  Put.class,  // mapper output value
		  job//
		 );
		
		//output format
	    job.setOutputFormatClass(TextOutputFormat.class);
	    FileOutputFormat.setOutputPath(job, new Path(args[1]));
		
		job.setNumReduceTasks(0);   // at least one, adjust as required

		boolean b = job.waitForCompletion(true);
		if (!b) {
		  throw new IOException("error with job!");
		}
		return 0;
	}

	  /*
	   * @param errorMsg Error message.  Can be null.
	   */
	  private static void usage(final String errorMsg) {
	    if (errorMsg != null && errorMsg.length() > 0) {
	      System.err.println("ERROR: " + errorMsg);
	    }
	    System.err.println("Usage: User2RDBMSMapReduce <tablename> <errorputdir> ");
	    System.err.println("Examples:  user hdfs://172.27.35.8:8020/user/hadoop/export/user/error");
	
	  }
	public static void main(String[] args) throws Exception {
		// get configuration
		Configuration conf = HBaseConfiguration.create();
		
		// submit job
		int status = ToolRunner.run(//
			conf, //
			new User2RDBMSMapReduce(), //
			args//
		);
		// exit program
		System.exit(status);
	}
}

import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import javax.sql.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.mchange.v2.c3p0.ComboPooledDataSource;

public class JDBCHelper
{
	protected static final Logger log = LoggerFactory.getLogger(JDBCHelper.class);
	
	private static String driverClass ="com.mysql.jdbc.Driver";
	private static String jdbcUrl="jdbc:mysql://172.27.35.1:3306/cyhp";
	private static String user ="root";
	private static String passwd ="root";
    //定义数据源
    private static DataSource ds;
    
    public static final String TABLE_NAME="user";
    public static final String ROW_KEY="rowkey";
    public static final String COLUMN_FAMILY="columnfamily";
    public static final String COLUMN_QUALIFIER="columnqualifier";
    public static final String VALUE ="value";
    //初始化数据库信息
    static 
    {
    	try {
			ds=getMySQLDataSource(jdbcUrl,user,passwd);
		} catch (PropertyVetoException e1) {
			e1.printStackTrace();
		}		
    }
    /**
     * create table if not exists 
     */
    public static void createTable()
    {
    	Connection con =null;
    	try
        {
    	    con=ds.getConnection();
    	    Object[] params = {TABLE_NAME,ROW_KEY, COLUMN_FAMILY, COLUMN_QUALIFIER,VALUE};
	        String sql= String.format("create table if not exists %1$s (%2$s varchar(50), %3$s varchar(50),%4$s varchar(50),%5$s varchar(50))", params);
	        update(con,sql);
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }finally
        {
        	if(con!=null)
        	{
        		try {
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
        	}
        }
    }
    public static int insert(String rowkey,String columnfamily,String columnqualifier,String value)
    {
    	Connection    con=null;
    	try
        {
            con=ds.getConnection();
    	    Object[] params = {TABLE_NAME, ROW_KEY,COLUMN_FAMILY, COLUMN_QUALIFIER,VALUE,rowkey,columnfamily,columnqualifier,value};
	        String insert =   String.format("insert into %1$s (%2$s,%3$s,%4$s,%5$s) values('%6$s','%7$s','%8$s','%9$s')", params);
	        int result =update(con,insert);
    	    return result;
        }
        catch (SQLException e)
        {
            e.printStackTrace();
            return 0;
        }
    	finally 
        {
    		if(con!=null)
        	{
        		try {
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
        	}
        }
    } 
    public static int update(Connection c, String sql) throws SQLException
    {
    	log.info("RUN SQL: {} on {}", sql, c);
        try (Statement stmt = c.createStatement())
        {
            stmt.execute(sql);
            log.info("UPDATE COUNT: " + stmt.getUpdateCount());
            return stmt.getUpdateCount();
        }
    }
    public static ComboPooledDataSource getMySQLDataSource(String jdbcUrl,String user,String passwd) throws PropertyVetoException
    {
        ComboPooledDataSource cpds = new ComboPooledDataSource();
        cpds.setDriverClass(driverClass);
        cpds.setJdbcUrl(jdbcUrl);
        cpds.setUser(user);
        cpds.setPassword(passwd);
        return cpds;
    }
    public static void main(String[] args)
    {
    	createTable();
    	insert("1002","info","age","18");
    }
    
}