利用MySQL原数据信息批量转换指定库数据表生成Hive建表语句

Z时代
2024-01-10
分类：综合

database

1.写出文件工具类

package ccc.utile;
import java.io.*;
/**
 * @author ccc
 * @version 1.0.0
 * @ClassName WriteToFileExample.java
 * @Description TODO IO流
 * @ProjectName ccc
 * @createTime 2021年08月07日 18:32:00
*/
publicclass WriteToFileExample {
/**
     * 追加写入数据到指定文件
     *
     * @param str
     * @param path
*/
publicvoid writeFileSQL(String str, String path) {
        FileWriter fw = null;
try {
            File f = new File(path);
            fw = new FileWriter(f, true);
        } catch (IOException e) {
            e.printStackTrace();
        }
        PrintWriter pw = new PrintWriter(fw);
        pw.println(str);
        pw.flush();
try {
            fw.flush();
            pw.close();
            fw.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
/**
     * 清空文件内容
     *
     * @param fileName
*/
publicvoid clearInfoForFile(String fileName) {
        File file = new File(fileName);
try {
if (!file.exists()) {
                file.createNewFile();
            }
            FileWriter fileWriter = new FileWriter(file);
            fileWriter.write("");
            fileWriter.flush();
            fileWriter.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

2.jdbc工具类：

package ccc.utile;
import java.sql.*;
import java.util.Map;
/**
 * @author ccc
 * @version 1.0.0
 * @ClassName JDBCMySQL.java
 * @Description TODO MySQLJDBC链接
 * @ProjectName ccc
 * @createTime 2021年08月06日 14:19:00
*/
publicclass JDBCJAVAMySQL {
publicstatic Connection getConnection() {
//定义Connection对象
        Connection conn = null;
try {
            Class.forName("com.mysql.jdbc.Driver");//            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/", "root", "123456");
        } catch (Exception e) {
            e.printStackTrace();
        }
return conn;
    }
privatestaticvoid connection(Connection connection) {
if (connection != null) {
try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
privatestaticvoid resultSet(ResultSet resultSet) {
if (resultSet != null) {
try {
                resultSet.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
privatestaticvoid preparedStatement(PreparedStatement preparedStatement) {
if (preparedStatement != null) {
try {
                preparedStatement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
/*
     * @Description TODO 关闭连接
     * @Date 2021/7/21 22:42
     * @param
     * @return
*/
publicstaticvoid close(Connection connection, ResultSet resultSet, PreparedStatement preparedStatement) {
        connection(connection);
        resultSet(resultSet);
        preparedStatement(preparedStatement);
    }
}

3.表属性实体类：

package ccc.enty;
/**
 * @author ccc
 * @version 1.0.0
 * @ClassName TableSchema.java
 * @Description TODO
 * @ProjectName ccc
 * @createTime 2021年08月06日 14:58:00
*/
publicclass TableSchema {
private String table_name;
private String table_comment;
public String getTable_name() {
return table_name;
    }
publicvoid setTable_name(String table_name) {
this.table_name = table_name;
    }
public String getTable_comment() {
return table_comment;
    }
publicvoid setTable_comment(String table_comment) {
this.table_comment = table_comment;
    }
    @Override
public String toString() {
return "TableSchema{" +
                "table_name="" + table_name + """ +
                ", table_comment="" + table_comment + """ +
                "}";
    }
}

4.表结构实体类：

package ccc.enty;
/**
 * @author ccc
 * @version 1.0.0
 * @ClassName ColumnSchema.java
 * @Description TODO
 * @ProjectName ccc
 * @createTime 2021年08月06日 14:59:00
*/
publicclass ColumnSchema {
private String column_name;
private String column_comment;
private String column_type;
public String getColumn_name() {
return column_name;
    }
publicvoid setColumn_name(String column_name) {
this.column_name = column_name;
    }
public String getColumn_comment() {
return column_comment;
    }
publicvoid setColumn_comment(String column_comment) {
this.column_comment = column_comment;
    }
public String getColumn_type() {
return column_type;
    }
publicvoid setColumn_type(String column_type) {
this.column_type = column_type;
    }
    @Override
public String toString() {
return "ColumnSchema{" +
                "column_name="" + column_name + """ +
                ", column_comment="" + column_comment + """ +
                ", column_type="" + column_type + """ +
                "}";
    }
}

5.启动类：

package ccc.contorller;
import ccc.enty.ColumnSchema;
import ccc.enty.TableSchema;
import ccc.utile.JDBCJAVAMySQL;
import ccc.utile.WriteToFileExample;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
/** 需关闭连接
 * @author ccc
 * @version 1.0.0
 * @ClassName BatchMySQL2HIVE.java
 * @Description TODO 通过MySQL原数据信息，生成HIVE建表语句
 * @ProjectName ccc
 * @createTime 2021年08月06日 14:52:00
*/
publicclass BatchMySQL2HIVE {
/**
     * 获取表信息
     *
     * @return
*/
publicstatic List<TableSchema> getTable_schema(String databases) {
        List<TableSchema> list = new ArrayList<TableSchema>();
        String sql = "SELECT a.table_name,a.table_comment FROM information_schema.`TABLES` a where a.table_schema=" + """ + databases + """;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        Connection connection = JDBCJAVAMySQL.getConnection();
try {
            ps = connection.prepareStatement(sql);
            resultSet = ps.executeQuery();
while (resultSet.next()) {
                TableSchema a = new TableSchema();
                a.setTable_name(resultSet.getString("table_name"));
                a.setTable_comment(resultSet.getString("table_comment"));
                list.add(a);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCJAVAMySQL.close(connection, resultSet, ps);
        }
return list;
    }
/**
     * 获取表结构信息
     *
     * @return
*/
publicstatic List<ColumnSchema> getColumn_schema(String database, String table_name) {
        List<ColumnSchema> list = new ArrayList<ColumnSchema>();
        String c = "SELECT a.column_name,a.column_comment,a.data_type FROM information_schema.`COLUMNS` a where a.table_schema=" + """ + database + "" ";
        String b = " and a.table_name=" + """ + table_name + """;
        String sql = c + b;
        System.out.println(sql);
        Connection connection = JDBCJAVAMySQL.getConnection();
        PreparedStatement ps = null;
        ResultSet resultSet = null;
try {
            ps = connection.prepareStatement(sql);
            resultSet = ps.executeQuery();
while (resultSet.next()) {
                ColumnSchema a = new ColumnSchema();
                a.setColumn_comment(resultSet.getString("column_comment"));
                a.setColumn_name(resultSet.getString("column_name"));
                a.setColumn_type(resultSet.getString("data_type"));
                list.add(a);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
return list;
    }
/**
     * 生成表结构
     *
     * @param j
     * @return
*/
publicstatic String createTable(String database, int j) {
        StringBuffer sb = new StringBuffer();
        List<TableSchema> table_schema = getTable_schema(database);
        List<ColumnSchema> column_schema = getColumn_schema(database, table_schema.get(j).getTable_name());
        sb.append("--" + getTable_comment(table_schema.get(j).getTable_comment(), table_schema.get(j).getTable_name()) + ":" + table_schema.get(j).getTable_name() + "
");
        sb.append("CREATE TABLE IF NOT EXISTS " + table_schema.get(j).getTable_name() + "(" + "
");
int f = 0;
for (int i = 0; i < column_schema.size(); i++) {
//判断是否是最后一个字段，如果是则不加都号
if (f == column_schema.size() - 1) {
                sb.append("  " + tranColumn2xx(column_schema.get(i).getColumn_name()) + " " + getColumn_type(column_schema.get(i).getColumn_type()) + " COMMENT " + getColumn_Comment(column_schema.get(i).getColumn_comment()) + "
");
            } else {
                sb.append("  " + tranColumn2xx(column_schema.get(i).getColumn_name()) + " " + getColumn_type(column_schema.get(i).getColumn_type()) + " COMMENT " + getColumn_Comment(column_schema.get(i).getColumn_comment()) + "," + "
");
            }
            f++;
        }
        sb.append(") COMMENT " + """ + getTable_comment(table_schema.get(j).getTable_comment(), table_schema.get(j).getTable_name()) + """ + ";" + "
");
return sb.toString();
    }
/**
     * 填充字段注释
     *
     * @param comment
     * @return
*/
publicstatic String getColumn_Comment(String comment) {
if (comment == null || comment.equals("")) {
return """";
        } else {
return """ + comment + """;
        }
    }
/**
     * 填充表注释
     *
     * @param comment
     * @param table_name
     * @return
*/
publicstatic String getTable_comment(String comment, String table_name) {
if (comment == null || comment.equals("")) {
return table_name;
        } else {
return comment;
        }
    }
/**
     * 匹配类型
     *
     * @param column_type
     * @return
*/
publicstatic String getColumn_type(String column_type) {
if ("int".equals(column_type)) {
return "BIGINT";
        } elseif ("tinyint".equals(column_type)) {
return "BIGINT";
        } elseif ("bigint".equals(column_type)) {
return "BIGINT";
        } elseif ("smallint".equals(column_type)) {
return "BIGINT";
        } elseif ("mediumint".equals(column_type)) {
return "BIGINT";
        } elseif ("float".equals(column_type)) {
return "DOUBLE";
        } elseif ("double".equals(column_type)) {
return "DOUBLE";
        } elseif ("decimal".equals(column_type)) {
return "STRING";
        } elseif ("numeric".equals(column_type)) {
return "STRING";
        } elseif ("bit".equals(column_type)) {
return "STRING";
        } elseif ("char".equals(column_type)) {
return "STRING";
        } elseif ("varchar".equals(column_type)) {
return "STRING";
        } elseif ("blob".equals(column_type)) {
return "STRING";
        } elseif ("mediumblob".equals(column_type)) {
return "STRING";
        } elseif ("longblob".equals(column_type)) {
return "STRING";
        } elseif ("tinytext".equals(column_type)) {
return "STRING";
        } elseif ("mediumtext".equals(column_type)) {
return "STRING";
        } elseif ("longtext".equals(column_type)) {
return "STRING";
        } elseif ("binary".equals(column_type)) {
return "STRING";
        } elseif ("varbinary".equals(column_type)) {
return "STRING";
        } elseif ("time".equals(column_type)) {
return "STRING";
        } elseif ("datetime".equals(column_type)) {
return "STRING";
        } elseif ("timestemp".equals(column_type)) {
return "STRING";
        } elseif ("year".equals(column_type)) {
return "STRING";
        } elseif ("date".equals(column_type)) {
return "STRING";
        } elseif ("text".equals(column_type)) {
return "STRING";
        }elseif ("longtext".equals(column_type)) {
return "STRING";
        } else {
return "STRING";
        }
    }
/**
     * 字段转小写
     *
     * @param column_name 传入原始字段
     * @return 返回转换字段
*/
publicstatic String tranColumn2xx(String column_name) {
return column_name.toLowerCase();
    }
/**
     * 批量启动
     *
     * @param database 数据库名称
     * @param path     写入文件路径
*/
publicstaticvoid start(String database, String path) {
        List<TableSchema> table_schema = getTable_schema(database);
        WriteToFileExample writeToFileExample = new WriteToFileExample();
        writeToFileExample.clearInfoForFile(path);
int f = 0;
for (int i = 0; i < table_schema.size(); i++) {
            String table = createTable(database, i);
            System.out.println(table);
            writeToFileExample.writeFileSQL(table, path);
            f++;
        }
        System.out.println("共记录：" + f + "条数据！");
    }
publicstaticvoid main(String[] args) {
        start("CCC", "mysql2HIVE.sql");
    }
}