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();@Overrideprotected void setup(Context context) throws IOException,InterruptedException {JDBCHelper.createTable();}@Overrideprotected void map(ImmutableBytesWritable key, Result value,Context context) throws IOException, InterruptedException {// get rowkey String rowkey = Bytes.toString(key.get());// iteratorfor(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);}}}}// Driverpublic int run(String[] args) throws Exception {   if (args.length < 2) {      usage("Wrong number of arguments: " + args.length);      System.exit(-1); }// create jobJob job = Job.getInstance(this.getConf(),//this.getClass().getSimpleName());// set run job classjob.setJarByClass(this.getClass());// set jobScan scan = new Scan();scan.setCaching(500);        // 1 is the default in Scan, which will be bad for MapReduce jobsscan.setCacheBlocks(false);  // don't set to true for MR jobs// set other scan attrsTableMapReduceUtil.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 requiredboolean 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 configurationConfiguration conf = HBaseConfiguration.create();// submit jobint status = ToolRunner.run(//conf, //new User2RDBMSMapReduce(), //args//);// exit programSystem.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");    }    }