windows下把資料從oracle匯入hbase
隨著資料量的增大，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了。
相關文章
- 如何把資料從Mysql匯入到GreenplumMySql
- Windows 下 MySQL 資料匯入 RedisWindowsMySqlRedis
- 資料匯入終章：如何將HBase的資料匯入HDFS？
- Windows DOS窗體下Oracle 資料庫的匯入匯出(IMP/EXP)命令WindowsOracle資料庫
- 把csv檔案的資料匯入到oracle資料庫中Oracle資料庫
- Oracle：從SQL檔案批量匯入資料OracleSQL
- Oracle 資料匯入匯出Oracle
- oracle資料匯入匯出Oracle
- 用Navicat把SQLServer資料匯入MySQLServerMySql
- 把Excel表資料匯入到mysqlExcelMySql
- Oracle 資料匯入ExcelOracleExcel
- Hive資料匯入HBase引起資料膨脹引發的思考Hive
- oracle資料匯出匯入（exp/imp）Oracle
- Oracle資料泵－schema匯入匯出Oracle
- oracle資料庫匯入匯出命令！Oracle資料庫
- Oracle資料匯入匯出詳解Oracle
- 把資料匯入到不同的表空間
- 如何把資料匯入不同的表空間
- 把ACCESS的資料匯入到Mysql中(轉)MySql
- 使用FSO把文字資訊匯入資料庫 (轉)資料庫
- Access 匯入 oracle 資料庫Oracle資料庫
- Oracle使用dump匯入資料Oracle
- SQL server資料匯入OracleSQLServerOracle
- ORACLE MYSQL互相匯入資料OracleMySql
- Oracle資料泵的匯入和匯出Oracle
- 【oracle 資料匯入匯出字元問題】Oracle字元
- Oracle資料泵匯出匯入（expdp/impdp）Oracle
- 匯入匯出 Oracle 分割槽表資料Oracle
- Oracle使用資料泵匯出匯入表Oracle
- Oracle資料匯入匯出imp/exp命令Oracle
- oracle資料的匯入匯出imp/expOracle
- 遠端登入server匯出linux下oracle資料庫中的資料並從本地下載ServerLinuxOracle資料庫
- 將informix匯出的文字資料匯入oracle資料庫ORMOracle資料庫
- 採用importtsv匯入外部資料到hbase中ImportTTS
- 資料匯入shell指令碼(下）指令碼
- Oracle資料庫匯入匯出。imp匯入命令和exp匯出命令Oracle資料庫
- java 從EXCEL匯入到資料庫JavaExcel資料庫
- 從Excel到匯入MYSQL資料庫ExcelMySql資料庫