业务需求太多了，给完整导出为html文件，以及之前搞的
public static void main(String[] args) throws Exception {
kerberos();
}
public static void kerberos() {
URL resource = Thread.currentThread().getContextClassLoader().getResource("");
String basePath = resource.getPath();
System.setProperty("java.security.krb5.conf", basePath + "kerberos/krb5.conf");
UserGroupInformation.setConfiguration(HadoopKbsConfiguration.getHadoopKbsConfiguration());
try {
UserGroupInformation.loginUserFromKeytab("bigdata", basePath + "kerberos/bigdata.keytab");
UserGroupInformation logUser = UserGroupInformation.getLoginUser();
System.out.println("登录成功");
if (null == logUser) {
throw new BaseException("login user can not be empty!");
}
logUser.doAs(new PrivilegedAction<Object>() {
@Override
public Object run() {
System.out.println("start");
// impala sql
// dmlSqlFindByTableName();
// dmlAutoFindByDatabase();// 导出一个库下所有的建表语句
// getDml2Excel();
getDataBaseDict();
System.out.println("end");
return null;
}
});
} catch (IOException e) {
throw new RuntimeException(e);
}
}
public static void dmlAutoFindByMysql() {
}
//把单个的建表语句的字段搞成excel格式，目的提供数据字典
public static void getDml2Excel() {
String connectionUrl1 = "jdbc:impala://IP:25004/ic_cdm;AuthMech=1;KrbHostFQDN=master03-cdp;KrbServiceName=impala;OptimizedInsert=0;UseNativeQuery=1;";
String jdbcDriverName1 = "com.cloudera.impala.jdbc41.Driver";
//加载驱动
try {
Class.forName(jdbcDriverName1);
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
}
try (Connection con = DriverManager.getConnection(connectionUrl1)) {
DatabaseMetaData md = con.getMetaData();
ResultSet columns = md.getColumns(null, "ic_cdm", "t_dim_model_price_map_h", "%");
String table_name = columns.getString("TABLE_NAME");
String table_remark = columns.getString("REMARKS");
List<List<String>> rows = new ArrayList<>();
// hutools 工具
ExcelWriter writer = ExcelUtil.getWriter("D:/writeTest.xlsx");
while (columns.next()) {
String column_name = columns.getString("COLUMN_NAME");
String remark = columns.getString("REMARKS");
String type = columns.getString("TYPE_NAME");
System.out.println(column_name + " " + remark + " " + type);
List<String> row = CollUtil.newArrayList(column_name, remark, type);
rows.add(row);
}
writer.merge(rows.size() - 1, "测试标题");
writer.write(rows);
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
public static void getDataBaseDict() {
FileSystemView fsv = FileSystemView.getFileSystemView();
String path = fsv.getHomeDirectory().toString();//获取当前用户桌面路径
String connectionUrl1 = "jdbc:impala://IP:25004/ic_cdm;AuthMech=1;KrbHostFQDN=IP;KrbServiceName=impala;OptimizedInsert=0;UseNativeQuery=1;";
String jdbcDriverName1 = "com.cloudera.impala.jdbc41.Driver";
//加载驱动
try {
Class.forName(jdbcDriverName1);
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
}
try {
Connection con = DriverManager.getConnection(connectionUrl1);
DatabaseMetaData dbmd = con.getMetaData();
ResultSet resultSet = dbmd.getTables(null, "ic_cdm", "%", new String[]{"TABLE"});
File directory = new File(path);
FileWriter fw = new FileWriter(directory + "\\数据字典.html");
fw.write("<html>");
while (resultSet.next()) {
String tableName = resultSet.getString("TABLE_NAME");
String tableRemark = resultSet.getString("REMARKS");
System.out.println(tableRemark);
if (tableName.contains("_k") || tableName.contains("_bak") || tableName.contains("dim")
|| tableName.contains("dwd")) {
continue;
}
ResultSet rs = dbmd.getColumns(null, "%", tableName, "%");
fw.write("<table border=\"10\" cellspacing=\"0\" align=\"center\" cellpadding=\"30\" >"
+ "<caption>" +tableRemark +": "+tableName + "</caption>\n"
+ "<tr><th>列名</th><th>注释</th><th>类型</th></tr>");
while (rs.next()) {
fw.write("<tr>");
String column_name = rs.getString("COLUMN_NAME");
String remark = rs.getString("REMARKS");
String type = rs.getString("TYPE_NAME");
System.out.println(column_name + "," + remark + "," + type);
// System.out.println("字段名：" + rs.getString("COLUMN_NAME") + "\t字段注释：" + rs.getString("REMARKS") + "\t字段数据类型：" + rs.getString("TYPE_NAME"));
fw.write("<td>" + column_name + "</td>" + "<td>" + remark + "</td>" + "<td>" + type + "</td>");
fw.write("</tr>");
}
fw.write("</table>");
fw.write("<hr></hr>");
}
fw.write("</html>");
} catch (Exception e) {
e.printStackTrace();
}
}
public static void outDict2Word() {
}
public static void outDict2Excel() {
}
public static void outDict2Html() {
}
public static void dmlAutoFindByDatabase() {
String connectionUrl = "jdbc:impala://ip:25004/ic_ods;AuthMech=1;KrbHostFQDN=ip;KrbServiceName=impala;OptimizedInsert=0;UseNativeQuery=1;";
String jdbcDriverName = "com.cloudera.impala.jdbc41.Driver";
//简单的一个查询语句
String sqlStatement = "use ic_ods;";
String showTablesStatement = "show tables;";
String showDmlTableStatement = "show create table ";
String tableNames = "t_ods_api_ds_k";
//加载驱动
try {
Class.forName(jdbcDriverName);
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
}
try (Connection con = DriverManager.getConnection(connectionUrl)) {
Statement stmt = con.createStatement();
System.out.println("---begin query---");
boolean execute = stmt.execute(sqlStatement);
if (!execute) {
ResultSet rs = stmt.executeQuery(showTablesStatement);
while (rs.next()) {
String curName = rs.getString(1);
if (!curName.contains("bak") && !curName.contains("_k") && !curName.contains("_test") && !curName.contains("_tmp")) {
tableNames = tableNames + "," + rs.getString(1);
System.out.println(rs.getString(1));
}
}
}
String[] tables = tableNames.split(",");
//查询
Map<String, String> map = new HashMap<String, String>();
for (String table : tables) {
if (table == null || table == "" || table == " ") {
continue;
}
String sql = showDmlTableStatement + table + ";";
String curSql = "";
// Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(sql);
//打印输出
while (rs.next()) {
curSql = curSql + rs.getString(1) + "\r\n\r\n";
System.out.println(rs.getString(1));
}
map.put(table, curSql);
}
System.out.println("---end query---");
URL resource = Thread.currentThread().getContextClassLoader().getResource("");
String basePath = resource.getPath();
File file = new File("D:\\建表语句.txt");
if (!file.exists()) {
file.createNewFile();
}
FileWriter fw = new FileWriter(file);
for (Map.Entry<String, String> entity : map.entrySet()) {
String tName = entity.getKey();
String sqlTable = map.get(tName);
fw.write(sqlTable);
}
fw.close();
System.out.println("succ");
} catch (Exception e) {
e.printStackTrace();
}
}
public static void dmlSqlFindByTableName() {
String connectionUrl = "jdbc:impala://IP:25004/ic_cdm;AuthMech=1;KrbHostFQDN=ip;KrbServiceName=impala;OptimizedInsert=0;UseNativeQuery=1;";
String jdbcDriverName = "com.cloudera.impala.jdbc41.Driver";
//简单的一个查询语句
String[] tables = "".split(",");
String sqlStatement = "show create table ic_cdm.t_quantity_ds_h;";
//加载驱动
try {
Class.forName(jdbcDriverName);
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
}
try (Connection con = DriverManager.getConnection(connectionUrl)) {
//查询
Map<String, String> map = new HashMap<String, String>();
for (String table : tables) {
String sql = "show create table " + table + ";";
String curSql = "";
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(sql);
System.out.println("---begin query---");
//打印输出
while (rs.next()) {
curSql = curSql + rs.getString(1);
System.out.println(rs.getString(1));
}
map.put(table, curSql);
}
System.out.println("---end query---");
URL resource = Thread.currentThread().getContextClassLoader().getResource("");
String basePath = resource.getPath();
File file = new File("D:\\建表语句.txt");
if (!file.exists()) {
file.createNewFile();
}
FileWriter fw = new FileWriter(file);
for (Map.Entry<String, String> entity : map.entrySet()) {
String tName = entity.getKey();
String sqlTable = map.get(tName);
fw.write(sqlTable);
}
fw.close();
System.out.println("succ");
} catch (Exception e) {
e.printStackTrace();
}
}
