windows下把資料從oracle匯入hbase

勿在浮沙築高臺LS發表於2016-11-30
WindowsOracle

隨著資料量的增大，oracle的查詢速度就有點捉襟見肘，這個時候我們需要把資料從oracle資料庫匯入到hbase，然後使用hbase進行查詢資料。
廢話不多說，直接上程式碼.

public class Getdata {
    private static Connection conn;
    private static Statement st;
    private ResultSet rs;
    static {
        conn = Getconn.getConntection();
        try {
            st = conn.createStatement();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
public List<Cfmx> getCfmxInfo(String tableName, int pagebegin, int pageEnd) {
        String sql = "select id, ghdjid, xmmc, xmdm, je, sl, gg, cfrq, jx from (select rownum rn, id, ghdjid, xmmc, xmdm, je, sl, gg, cfrq, jx from "
                + tableName
                + " where rownum<"
                + pageEnd
                + ") s where s.rn >"
                + pagebegin;
        System.out.println(sql);
        List<Cfmx> list = new ArrayList<Cfmx>();
        try {
            rs = st.executeQuery(sql);
            int count = 0;
            while (rs.next()) {
                Cfmx c = new Cfmx();
                c.setId(String.valueOf(rs.getDouble(1)));
                c.setGhdjid(rs.getString(2));
                c.setXmmc(rs.getString(3));
                c.setXmdm(rs.getString(4));
                c.setJe(rs.getString(5));
                c.setSl(rs.getString(6));
                c.setGg(rs.getString(7));
                c.setCfrq(rs.getString(8));
                c.setJx(rs.getString(9));
                // c.setJgdj(rs.getString(10));
                // c.setJgid(rs.getString(11));
                list.add(c);
                count++;
            }
            System.out.println("共匯出" + count + "條資料");
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return list;
    }
    }
public class Getconn {
     static String driver = "oracle.jdbc.driver.OracleDriver";  
     static String url = "jdbc:oracle:thin:@192.168.1.234:1521:orcl";  
     static String user = "LS";  
     static String password = "LS"; 
     static Connection conn;
     public static Connection getConntection()
     {
         try {
            Class.forName(driver);  
            conn = DriverManager.getConnection(url, user, password);
            System.out.println("connect database success");
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return conn;
     }
     }
public class Cfmx {
  private String id;
  private String ghdjid;
  private String xmmc;
  private String xmdm;
  private String je;
  private String sl;
  private String gg;
  private String cfrq;
  private String jx;
  private String jgdj;
  private String jgid;

/**
 * @return the id
 */
public String getId() {
    return id;
}
/**
 * @param id the id to set
 */
public void setId(String id) {
    this.id = id;
}
/**
 * @return the xmmc
 */
public String getXmmc() {
    return xmmc;
}
/**
 * @param xmmc the xmmc to set
 */
public void setXmmc(String xmmc) {
    this.xmmc = xmmc;
}
/**
 * @return the xmdm
 */
public String getXmdm() {
    return xmdm;
}
/**
 * @param xmdm the xmdm to set
 */
public void setXmdm(String xmdm) {
    this.xmdm = xmdm;
}
/**
 * @return the je
 */
public String getJe() {
    return je;
}
/**
 * @param je the je to set
 */
public void setJe(String je) {
    this.je = je;
}
/**
 * @return the sl
 */
public String getSl() {
    return sl;
}
/**
 * @param sl the sl to set
 */
public void setSl(String sl) {
    this.sl = sl;
}
/**
 * @return the gg
 */
public String getGg() {
    return gg;
}
/**
 * @param gg the gg to set
 */
public void setGg(String gg) {
    this.gg = gg;
}
/**
 * @return the cfrq
 */
public String getCfrq() {
    return cfrq;
}
/**
 * @param cfrq the cfrq to set
 */
public void setCfrq(String cfrq) {
    this.cfrq = cfrq;
}
/**
 * @return the jx
 */
public String getJx() {
    return jx;
}
/**
 * @param jx the jx to set
 */
public void setJx(String jx) {
    this.jx = jx;
}
/**
 * @return the jgdj
 */
public String getJgdj() {
    return jgdj;
}
/**
 * @param jgdj the jgdj to set
 */
public void setJgdj(String jgdj) {
    this.jgdj = jgdj;
}
/**
 * @return the jgid
 */
public String getJgid() {
    return jgid;
}
/**
 * @param jgid the jgid to set
 */
public void setJgid(String jgid) {
    this.jgid = jgid;
}
/**
 * @return the ghdjid
 */
public String getGhdjid() {
    return ghdjid;
}
/**
 * @param ghdjid the ghdjid to set
 */
public void setGhdjid(String ghdjid) {
    this.ghdjid = ghdjid;
}


}

上面是實體以及連線oracle和獲取資料的程式碼，由於考慮到outofmemory，這裡我們就可以一次只讀取20000行記錄來解決。
下面附上連線hbase的程式碼

public class HbaseConfigNew {
    private static Configuration config;
    final private static String SOCKET_TIMEOUT = "ipc.socket.timeout";
    final static int DEFAULT_SOCKET_TIMEOUT = 100000; // 100 seconds
    public static Configuration getHBaseConfig(){
        config=HBaseConfiguration.create();
        config.set("hbase.zookeeper.quorum","192.168.13.129");
        config.set("hbase.zookeeper.property.clientPort", "2181");
        config.set("hbase.master", "192.168.13.129:16010"); 
        config.getInt(SOCKET_TIMEOUT, DEFAULT_SOCKET_TIMEOUT);//設定超時時間
        System.setProperty("hadoop.home.dir", "D:\\linux\\hadoop-2.6.4\\hadoop-2.6.4");
        return config;
    }
    public static HTable getTable(  Connection conn,String tableName,String familyName){
     HTable hTable=null;
     try {
         conn = ConnectionFactory.createConnection(getHBaseConfig());
         HBaseAdmin hBaseAdmin=(HBaseAdmin)conn.getAdmin();
         HTableDescriptor desc = new HTableDescriptor(TableName.valueOf(tableName));
         desc.addFamily(new HColumnDescriptor(familyName));
         if(!hBaseAdmin.tableExists(tableName))
         {
             System.out.println("已經存在表");
             hBaseAdmin.createTable(desc);
         }
         hTable = (HTable) conn.getTable(TableName.valueOf(tableName));
         System.out.println("獲取表成功");

    } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
     return hTable;
  }

下面是匯入資料的程式碼

public static void importCfmx(int count, List<Cfmx> list, String tableName,
            String familyname) {
        HTable hTable = null;
        Connection conn = null;
//       int count = 0;
        List<Put> puts = new ArrayList<Put>();
        try {
            hTable = HbaseConfigNew.getTable(conn, tableName, familyname);
            for (Cfmx c : list) {
                String rowKey = "row" + count;
                Put putRow = new Put(rowKey.getBytes());
                // Put putRow = new
                // Put((String.valueOf(System.currentTimeMillis())).getBytes());
                putRow.addColumn(familyname.getBytes(), "id".getBytes(),
                        StringUtils.isBlank(c.getId()) ? " ".getBytes() : c
                                .getId().getBytes());
                putRow.addColumn(familyname.getBytes(), "ghdjid".getBytes(),
                        StringUtils.isBlank(c.getGhdjid()) ? " ".getBytes() : c
                                .getGhdjid().getBytes());
                putRow.addColumn(familyname.getBytes(), "xmmc".getBytes(),
                        StringUtils.isBlank(c.getXmmc()) ? " ".getBytes() : c
                                .getXmmc().getBytes());
                putRow.addColumn(familyname.getBytes(), "xmdm".getBytes(),
                        StringUtils.isBlank(c.getXmdm()) ? " ".getBytes() : c
                                .getXmdm().getBytes());
                putRow.addColumn(familyname.getBytes(), "je".getBytes(),
                        StringUtils.isBlank(c.getJe()) ? " ".getBytes() : c
                                .getJe().getBytes());
                putRow.addColumn(familyname.getBytes(), "sl".getBytes(),
                        StringUtils.isBlank(c.getSl()) ? " ".getBytes() : c
                                .getSl().getBytes());
                putRow.addColumn(familyname.getBytes(), "gg".getBytes(),
                        StringUtils.isBlank(c.getGg()) ? " ".getBytes() : c
                                .getGg().getBytes());
                putRow.addColumn(familyname.getBytes(), "cfrq".getBytes(),
                        StringUtils.isBlank(c.getCfrq()) ? " ".getBytes() : c
                                .getCfrq().getBytes());
                putRow.addColumn(familyname.getBytes(), "jx".getBytes(),
                        StringUtils.isBlank(c.getJx()) ? " ".getBytes() : c
                                .getJx().getBytes());
//              putRow.addColumn(familyname.getBytes(), "jgdj".getBytes(),
//                      StringUtils.isBlank(c.getJgdj()) ? " ".getBytes() : c
//                              .getJgdj().getBytes());
//              putRow.addColumn(familyname.getBytes(), "jgid".getBytes(),
//                      StringUtils.isBlank(c.getJgid()) ? " ".getBytes() : c
//                              .getJgid().getBytes());
                puts.add(putRow);
                count++;
            }
            hTable.put(puts);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            HbaseConfigNew.closeAll(hTable, conn);
        }
    }

資料就這樣從oracle資料庫匯入到hbase了。

相關文章