读取Hive中所有表的表结构,并在新Hive库中创建表,索引代码,Java代码库,德仔网

代码语言
代码分类
【Java】读取Hive中所有表的表结构,并在新Hive库中创建表,索引
作者:金璇 / 发布于2016/12/9/ 866
读取Hive中所有表的表结构,并在新Hive库中创建表,索引读取Hive中表的结构。本文中包含Table类，Field类用于封装表结构，粗略瞅一遍就ok等
1. Table类

public class Table {
    private String tableName;
    private List<Field> field;

    public Table() {
    }

    public Table(String tableName, List<Field> field) {
        this.tableName = tableName;
        this.field = field;
    }

    public String getTableName() {
        return tableName;
    }

    public void setTableName(String tableName) {
        this.tableName = tableName;
    }

    public List<Field> getField() {
        return field;
    }

    public void setField(List<Field> field) {
        this.field = field;
    }

    @Override
    public String toString() {
        return "Table{" + "tableName=" + tableName + ", field=" + field + '}';
    }

}
2. Field类
public class Field {
    private String columnName;
    private String typeName;
    private int columnSize;
    private int decimal_digits;
    private int nullable;

    public Field() {
    }

    public Field(String columnName, String typeName, int columnSize, int decimal_digits, int nullable) {
        this.columnName = columnName;
        this.typeName = typeName;
        this.columnSize = columnSize;
        this.decimal_digits = decimal_digits;
        this.nullable = nullable;
    }

    public String getColumnName() {
        return columnName;
    }

    public void setColumnName(String columnName) {
        this.columnName = columnName;
    }

    public String getTypeName() {
        return typeName;
    }

    public void setTypeName(String typeName) {
        this.typeName = typeName;
    }

    public int getColumnSize() {
        return columnSize;
    }

    public void setColumnSize(int columnSize) {
        this.columnSize = columnSize;
    }

    public int getDecimal_digits() {
        return decimal_digits;
    }

    public void setDecimal_digits(int decimal_digits) {
        this.decimal_digits = decimal_digits;
    }

    public int getNullable() {
        return nullable;
    }

    public void setNullable(int nullable) {
        this.nullable = nullable;
    }

    @Override
    public String toString() {
        return "Field{" + "columnName=" + columnName + ", typeName=" + typeName + ", columnSize=" + columnSize + ", decimal_digits=" + decimal_digits + ", nullable=" + nullable + '}';
    }
    
}
3. 主要方法
public List<Table> export() {
        String showtablesSQL = "show tables";
        List<Table> tableList = new ArrayList<Table>();
        List<String> tableNameList = new ArrayList<String>();

        Connection conn = HiveUtil.getConnection();
        Statement stmt = null;
        ResultSet tableRs = null; // 存库元数据
        ResultSet colRs = null;//存储表元数据
        try {
            stmt = conn.createStatement();
            stmt.executeQuery("use database");

            //获取表名
            tableRs = stmt.executeQuery(showtablesSQL);
            while (tableRs.next()) {
                String table = tableRs.getString(1);
                tableNameList.add(table);
            }
            //获取表结构
            Field field = null;
            Table table = null;
            for (int i = 0; i < tableNameList.size(); i++) {
                String descTableSQL = "describe ";
                List<Field> fieldList = new ArrayList<Field>();
                descTableSQL = descTableSQL + tableNameList.get(i).trim();//拼接sql
                
                colRs = stmt.executeQuery(descTableSQL);
                while (colRs.next()) {
                    field = new Field();
                    field.setColumnName(colRs.getString(1));
                    field.setTypeName(colRs.getString(2));//测试大小
                    fieldList.add(field);
                }
                table = new Table();
                table.setTableName(tableNameList.get(i).trim());
                table.setField(fieldList);
                tableList.add(table);
            }

        } catch (SQLException ex) {
            Logger.getLogger(ExportOracleTable.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (colRs != null) {
                try {
                    colRs.close();
                } catch (SQLException ex) {
                    Logger.getLogger(ExportOracleTable.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
            if (tableRs != null) {
                try {
                    tableRs.close();
                } catch (SQLException ex) {
                    Logger.getLogger(ExportOracleTable.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ex) {
                    Logger.getLogger(ExportOracleTable.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return tableList;
    }
4. 在新库中建表
public void createTableToHive(List<Table> list) {
        Connection conn = HiveUtil.getTargetConnection();
        Statement stmt = null;
        try {
            for (Table table : list) {
                StringBuffer sql = new StringBuffer();
                StringBuffer schemaSQL = new StringBuffer();
                StringBuffer indexSQL = new StringBuffer();
                
                sql.append("create table if not exists ");
                sql.append(table.getTableName());//create table test
                for (int i = 0; i < table.getField().size(); i++) {
                    sql.append(" (" + table.getField().get(i).getColumnName() + " " + table.getField().get(i).getTypeName());//create table test (name varchar(2), age int)
//                    if (table.getField().get(i).getColumnSize() == 0) {//条件需要测试
//                        sql.append("(" + table.getField().get(i).getColumnSize() + ")");
//                    }
                    if (i == table.getField().size() - 1) {
                        continue;
                    }
                    sql.append(",");
                }
                //执行
                stmt = conn.createStatement();
                stmt.executeQuery("use database");
                stmt.executeQuery(sql.toString().trim());
//                stmt.executeQuery(schemaSQL );
            }
            conn.commit();
            
        } catch (SQLException ex) {
            Logger.getLogger(CreateHiveTable.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if(stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    Logger.getLogger(CreateHiveTable.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
            if(conn != null) {
                try {
                    conn.close();
                } catch (SQLException ex) {
                    Logger.getLogger(CreateHiveTable.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
    }
试试其它关键字
同语言下
可能有用的
金璇贡献的其它代码(13)