hive仓库元数据管理系统

package org.caesar.hivemeta.controller; import com.alibaba.fastjson.JSONArray; import com.alibaba.fastjson.JSONObject; import org.apache.hadoop.conf.Configuration; import org.apache.hadoop.fs.FSDataInputStream; import org.apache.hadoop.fs.FileSystem; import org.apache.hadoop.fs.Path; import org.caesar.hivemeta.common.CommonUtil; import org.caesar.hivemeta.common.Constants; import org.caesar.hivemeta.common.Response; import org.caesar.hivemeta.dao.CommonDML; import org.caesar.hivemeta.entity.hive.*; import org.caesar.hivemeta.entity.zeus.TbZeusFile; import org.caesar.utils.db.ExecutorDB; import org.caesar.utils.encryption.MD5; import org.caesar.utils.hdfs.HDFSUtil2; import org.caesar.utils.hive.HiveDBOperator; import org.caesar.utils.os.Systems; import org.caesar.utils.properties.PropertiesUtil; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestMethod; import org.springframework.web.bind.annotation.ResponseBody; import javax.servlet.http.HttpServletResponse; import java.io.*; import java.sql.*; import java.text.DateFormat; import java.text.SimpleDateFormat; import java.util.*; import java.util.regex.Pattern; /** * Created by caesar on 2016/7/4. */ @Controller @RequestMapping("/") public class CommonController { private static Logger logger = LoggerFactory.getLogger(LoginController.class); private static Map<String,HiveDBOperator.HiveQueryResult> searchDatacaches = Collections.synchronizedMap(new HashMap<String, HiveDBOperator.HiveQueryResult>()); ExecutorDB hive = new ExecutorDB(2); ExecutorDB zeus = new ExecutorDB(3); @RequestMapping(value = "readDatabaseTree", method = RequestMethod.GET) @ResponseBody public String read() throws Exception { JSONObject rootJson = new JSONObject(); JSONArray arrayJson = new JSONArray(); rootJson.put("sucess","true"); String sql = "select * from DBS"; StringBuffer sb = new StringBuffer(); sb.append("{sucess:true,children:["); List<TbDBS> dbsList = hive.queryList(sql,TbDBS.class); for(TbDBS dbs : dbsList) { List<TbTBLS> tbTBLSList = CommonDML.getTablesListByDBId(dbs.getDbID()); // System.out.println(dbs.getName()); if (tbTBLSList.size() == 0) { sb.append("{id:\"" + "DB_" + dbs.getDbID() + "\",name:\"" + dbs.getName() + "\",qtip:\"" + dbs.getName() + "[库]\",icon:'static/images/extIcons/icons/database.png',leaf:true},"); } else { sb.append("{id:\"" + "DB_" + dbs.getDbID() + "\",name:\"" + dbs.getName() + "\",qtip:\"" + dbs.getName() + "[库]\",icon:'static/images/extIcons/icons/database.png',expanded:false,children:["); for (TbTBLS tbs : tbTBLSList) { // System.out.println(tbs.getTableName()); sb.append("{id:\"" + "TB_"+ tbs.getTableID() + "\",name:\"" + tbs.getTableName() + "\",qtip:\"" + tbs.getTableName() + "[表]\",icon:'static/images/extIcons/icons/table.png',leaf:true},"); } sb.deleteCharAt(sb.length() - 1); sb.append("]},"); } } sb.deleteCharAt(sb.length()-1); sb.append("]}"); System.out.println(sb); return sb.toString(); } @RequestMapping(value = "readTablePartitionInfo", method = RequestMethod.GET) @ResponseBody public String readTablePartition(String tableId) throws Exception { /** * 目前分区只支持深度为1的分区 */ if(tableId.equals("null")){ return null; } DateFormat df = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss"); TbTBLS table = hive.queryOne(String.format("select * from TBLS where TBL_ID='%s'", tableId), TbTBLS.class); TbPartitionKeys tbPartitionKeys = hive.queryOne(String.format("select * from PARTITION_KEYS where TBL_ID='%s'", tableId), TbPartitionKeys.class); if(tbPartitionKeys == null){ return null; } List<TbPartitions> tbPartitionsList = hive.queryList(String.format("select * from PARTITIONS where TBL_ID='%s' ORDER BY PART_NAME ASC", tableId), TbPartitions.class); StringBuffer sb = new StringBuffer(); //相关表的创建时间当作该分区的创建时间 String partitionFeildCreateTime = df.format(CommonUtil.convertHiveDateTime(table.getCreateTime())); //相关表的uri路径即为该分区字段的路径 int id=1953548;//ext中tree类型需要指定id，否则在做节点展开时会有问题 String partitionFeildURI = hive.queryOne(String.format("select LOCATION from SDS where SD_ID='%s'",table.getSdID()), TbSDS.class).getLocation(); sb.append("{\"sucess\":true,\"children\":[{\"name\":\""+tbPartitionKeys.getpKeyName()+"\",\"uri\":\""+partitionFeildURI + "\",\"createTime\":\"" + partitionFeildCreateTime + "\",id:\"" + (id++) + "\",\"tableID\":\"" + table.getTableID() + "\",\"expanded\":" + true + ",\"children\":["); for(TbPartitions tbPartitions : tbPartitionsList) { String partitionValue = tbPartitions.getPartName(); String partitionValueCreateTime = df.format(CommonUtil.convertHiveDateTime(tbPartitions.getCreateTime())); String partitionValueURI = hive.queryOne(String.format("select LOCATION from SDS where SD_ID='%s'", tbPartitions.getSdID()), TbSDS.class).getLocation(); sb.append("{\"name\":\"" + partitionValue + "\",\"uri\":\"" + partitionValueURI + "\",\"tableID\":\"" + table.getTableID() + "\",id:\"" + (id++) + "\",\"partitionID\":\"" + tbPartitions.getPartID() + "\",\"createTime\":\"" + partitionValueCreateTime + "\",\"leaf\":" + true+ "},"); } if(tbPartitionsList.size()!=0){ sb.deleteCharAt(sb.length()-1); } sb.append("]}]}"); System.out.println(sb); return sb.toString(); } @RequestMapping(value = "readTableFieldsInfo", method = RequestMethod.GET) @ResponseBody public String readTableFieldsInfo(String tableId) throws Exception { //查看索引字段 Set<String> indexFields = new HashSet<String>(); List<TbIDXS> tbIDXSList = hive.queryList(String.format("select * from IDXS where ORIG_TBL_ID='%s'", tableId), TbIDXS.class); for(TbIDXS tbIDXS:tbIDXSList) { TbSDS tbSDS = hive.queryOne(String.format("select * from SDS where SD_ID='%s'", tbIDXS.getSdID()), TbSDS.class); TbColumns tbColumns = hive.queryOne(String.format("select * from COLUMNS_V2 where CD_ID='%s'", tbSDS.getCdID()), TbColumns.class); indexFields.add(tbColumns.getColumnName()); } List<TbColumns> columns = CommonDML.getColumnsListByTableID(tableId); List<TbPartitionKeys> tbPartitionKeysList = hive.queryList(String.format("select * from PARTITION_KEYS where TBL_ID='%s'", tableId), TbPartitionKeys.class); StringBuffer json = new StringBuffer(); json.append("{\"sucess\":true,\"total\":" + (columns.size()+tbPartitionKeysList.size()) + ",\"fields\":["); for(TbPartitionKeys tbPartitionKeys:tbPartitionKeysList){ String comment = tbPartitionKeys.getpKeyComment(); comment=comment == null||"null".equals(comment)?"":comment; comment=CommonUtil.filterSpecialCharactersForJson(comment); json.append("{\"field\":\""+tbPartitionKeys.getpKeyName() +"\",\"tableID\":\""+tableId +"\",\"fieldID\":\""+(tbPartitionKeys.getIntegerIDX()+100000000)//为了保证前台按照fieldid排序后，索引字段可以放在最后一个位置 +"\",\"type\":\""+tbPartitio