导出hbase数据 动态创建greenplum表 字段 和数据

public void exportToGreenPlum(String datasrcId,String hbaseZookeeperHosts)
throws SQLException, IOException {
int commitSize = 100; // 提交数量
logger.info("exportToGreenPlum start: datasrcId:" + datasrcId
+ "\thbaseZookeeperHosts" + hbaseZookeeperHosts);
// 1.查询所有的列(列编号 : 列标题)
Map<String, String> columnMap = new HashMap<String, String>(); // 保存列编号好列名称
List<OecAnalyCorpusColumn> oecAnalyCorpusColumns = columnDao
.selectOecAnalyCorpusColumns(datasrcId);
if (oecAnalyCorpusColumns != null) {
for (OecAnalyCorpusColumn oecAnalyCorpusColumn : oecAnalyCorpusColumns) {
columnMap.put(oecAnalyCorpusColumn.getColumnId(),
oecAnalyCorpusColumn.getColumnTitle());
}
}
// 2.查询所有规则(规则编号 : 本体名称:根节点名称)
Map<String, String> ruleMap = new HashMap<String, String>(); // 保存规则编号和规则名称
List<OecAnalyCorpusColumnRule> oecAnalyCorpusColumnRules = ruleDao
.selectOecAnalyCorpusColumnRuleByDataSrcId(datasrcId);
if (oecAnalyCorpusColumnRules != null) {
for (OecAnalyCorpusColumnRule oecAnalyCorpusColumnRule : oecAnalyCorpusColumnRules) {
ruleMap.put(oecAnalyCorpusColumnRule.getRuleId(),
oecAnalyCorpusColumnRule.getOntoName() + ":"
+ oecAnalyCorpusColumnRule.getClasName());
}
}
// 1.从HBase中查询数据
Map<String, String> fieldMap = new HashMap<String, String>(); // 保存所有列标题
fieldMap.put("rowKey", "rowKey");
OecHBaseBaseDaoImpl hbaseDao = new OecHBaseBaseDaoImpl(
hbaseZookeeperHosts);
HTable table = hbaseDao.getHTable(datasrcId);
List<Map<String, String>> rowList = new ArrayList<Map<String, String>>();
ResultScanner scanner = table.getScanner(new Scan());
Result result = null;
while ((result = scanner.next()) != null) {
Map<String, String> rowMap = new HashMap<String, String>();
rowMap.put("rowKey", Bytes.toString(result.getRow()));
for (KeyValue keyValue : result.raw()) {
String columnFamily = new String(keyValue.getFamily());
String columnName = columnMap.get(columnFamily);
String qualifier = new String(keyValue.getQualifier());
String ruleName = ruleMap.get(qualifier);


if (!fieldMap.containsKey(columnFamily + "_" + qualifier)) {
fieldMap.put(columnFamily + "_" + qualifier, columnName
+ "_" + ruleName);
}
rowMap.put(columnFamily + "_" + qualifier,
Bytes.toString(keyValue.getValue()));
}
rowList.add(rowMap);
}
scanner.close();
logger.info("exportToGreenPlum 数据查询完毕:" + rowList.size());
// 2.向greenplum中写入数据
// 2.1 清表
if (super.tableExit("tb_"+datasrcId)) {
super.deleteTable("tb_"+datasrcId);
logger.info("exportToGreenPlum 表存在，删除");
}
// 2.2创建表
StringBuffer createTableSQL = new StringBuffer();
createTableSQL.append("create table " + "tb_"+datasrcId);
createTableSQL.append("( ");
for (Entry<String, String> field : fieldMap.entrySet()) {
createTableSQL.append("oec_"+field.getKey() + " text,");
}
createTableSQL.deleteCharAt(createTableSQL.length() - 1);
createTableSQL.append(")");
logger.info("exportToGreenPlum 生成建表语句:" + createTableSQL.toString());
super.createTable(createTableSQL.toString());
logger.info("exportToGreenPlum 建表完毕");
Connection conn = null;
Statement statement = null;
try {
conn = super.getConnection();
statement = conn.createStatement();
for (int i = 0, n = rowList.size(); i < n; i++) {
Map<String, String> rowMap = rowList.get(i);
StringBuffer sqlBuffPrex = new StringBuffer("insert into "
+ "tb_"+datasrcId.toLowerCase() + "( ");
StringBuffer sqlBuffEnd = new StringBuffer(")values( ");
for (Entry<String, String> fieldEntry : rowMap.entrySet()) {
sqlBuffPrex.append("oec_"+fieldEntry.getKey() + ",");
sqlBuffEnd.append("'" + fieldEntry.getValue() + "',");
}
sqlBuffPrex.deleteCharAt(sqlBuffPrex.length() - 1);
sqlBuffEnd.deleteCharAt(sqlBuffEnd.length() - 1);
statement.executeUpdate(sqlBuffPrex.toString()
+ sqlBuffEnd.toString() + ")");
if ((i + 1) % commitSize == 0) {
logger.info("exportToGreenPlum 已提交:"
+ (i * 1.0 / rowList.size()) * 100 + "%");
}
}
logger.info("exportToGreenPlum 数据导入完毕");
} finally {
if (statement != null) {
statement.close();
}
if (conn != null) {
conn.close();
}
}

}

public boolean createTable(String sql) throws SQLException {
Connection conn = null;
Statement statement = null;
try {
conn = getConnection();
statement = conn.createStatement();
statement.execute(sql);
return true;
} catch (SQLException e) {
throw e;
} finally {
try {
if (statement != null) {
statement.close();
}
} catch (Exception e) {
} finally {
if (conn != null) {
conn.close();
}
}
}
}


/**
* 删除表
* <p>Description:</p>
* <p>Remark:</p>
* @param tableName 表名
* @return
* @throws SQLException
*/
public boolean deleteTable(String tableName) throws SQLException{


Connection conn = null;
Statement statement = null;
try {
conn = getConnection();
statement = conn.createStatement();
statement.execute("drop table "+tableName);
return true;
} catch (SQLException e) {
throw e;
} finally {
try {
if (statement != null) {
statement.close();
}
} catch (Exception e) {
} finally {
if (conn != null) {
conn.close();
}
}
}

}


/**
* 获取列信息
* <p>Description:</p>
* <p>Remark:</p>
* @param sql
* @return
* @throws SQLException
*/
public Map<String, String> getColumnMetaInfo(String sql)
throws Exception {
Map<String, String> fieldInfoMap = new HashMap<String, String>();
Connection conn = null;
Statement statement = null;
try {
conn = getConnection();
statement = conn.createStatement();
ResultSet rs = statement.executeQuery(sql);
ResultSetMetaData metaData = rs.getMetaData();
int columnCount = metaData.getColumnCount();
for (int i = 0; i < columnCount; i++) {
fieldInfoMap.put(metaData.getColumnName(i + 1),
metaData.getColumnTypeName(i + 1));
}
}finally {
try {
if (statement != null) {
statement.close();
}
} catch (Exception e) {
} finally {
if (conn != null) {
conn.close();
}
}
}
return fieldInfoMap;


}


/**
* 查询
* <p>
* Description:
* </p>
* <p>
* Remark:
* </p>
*
* @param sql
* @return
* @throws SQLException
*/
public List<Map<String, Object>> select(String sql) throws SQLException {
List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
Connection conn = null;
Statement statement = null;
try {
conn = getConnection();
statement = conn.createStatement();
ResultSet rs = statement.executeQuery(sql);
ResultSetMetaData metaData = rs.getMetaData();
int columnCount = metaData.getColumnCount();
while (rs.next()) {
Map<String, Object> map = new HashMap<String, Object>();
for (int i = 1; i <= columnCount; i++) {
map.put(metaData.getColumnName(i), rs.getObject(i));
}
resultList.add(map);
}
} catch (SQLException e) {
throw e;
} finally {
try {
if (statement != null) {
statement.close();
}
} catch (Exception e) {
} finally {
if (conn != null) {
conn.close();
}
}
}
return resultList;
}