將mysql表創建到hive腳本

將mysql表創建到hive腳本

mysql.java

import java.util.ArrayList;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class mysql {

    private static String[] deleteArrayNull(String string[]) {
        ArrayList<String> strList = new ArrayList<>();

        // Loop through the input array and add non-null elements to the ArrayList
        for (String str : string) {
            if (str != null && !str.isEmpty()) {
                strList.add(str);
            }
        }

        // Convert the ArrayList back to an array of strings
        return strList.toArray(new String[0]);
    }

    public static String findColumnType(String str) {
        str = str.toLowerCase();
        String type;

        if (str.startsWith("int")) {
            type = "int";
        } else if (str.startsWith("bigint")) {
            type = "bigint";
        } else if (str.startsWith("decimal")) {
            type = "decimal"; // Assuming Hive's decimal type matches SQL's decimal type
        } else if (str.startsWith("bit")) {
            type = "int"; // Assuming mapping to INT in Hive
        } else if (str.startsWith("datetime") || str.startsWith("date") || str.startsWith("time")) {
            type = "string"; // These date-time related types mapped to STRING in Hive
        } else if (str.startsWith("float")) {
            type = "float";
        } else if (str.startsWith("double")) {
            type = "double";
        } else if (str.startsWith("boolean")) {
            type = "boolean";
        } else if (str.startsWith("tinyint")) {
            type = "int";
        }else {
            type = "string"; // Defaulting to STRING for unmatched types
        }

        return type;
    }

    public static void main(String[] args) {

        //mysql
        String str9 = "CREATE TABLE `us_position_md` (\n" +
                "  `pk_pos_id` int(10) NOT NULL AUTO_INCREMENT,\n" +
                "  `id` char(36) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,\n" +
                "  `unit_id` char(36) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,\n" +
                "  `position_name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,\n" +
                "  `bk_amount` varchar(250) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,\n" +
                "  `department_id` int(11) NOT NULL,\n" +
                "  `status` tinyint(1) NOT NULL,\n" +
                "  `car_status` tinyint(1) DEFAULT NULL,\n" +
                "  `explain` varchar(250) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '岗位说明',\n" +
                "  `ex01` char(1) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,\n" +
                "  `ex02` char(1) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,\n" +
                "  `updated_at` datetime DEFAULT NULL,\n" +
                "  `updated_by` char(36) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,\n" +
                "  `created_at` datetime DEFAULT NULL,\n" +
                "  `created_by` char(36) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,\n" +
                "  `is_job` tinyint(4) DEFAULT NULL,\n" +
                "  PRIMARY KEY (`pk_pos_id`)\n" +
                ") ENGINE=InnoDB AUTO_INCREMENT=2852 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC";

        //mysql
        String tableName = str9.split("` \\(\n")[0].split("`")[1].toLowerCase();
        String[] columnLines = str9.split("` \\(\n")[1].split("PRIMARY KEY \\(")[0].split(",\n");
        StringBuilder hiveSqlStr = new StringBuilder();



        // Begin building the Hive SQL script
        hiveSqlStr.append("drop table if exists hr_us.ods_").append(tableName).append("_full;\n")
                .append("create external table if not exists hr_us.ods_").append(tableName).append("_full (").append("\n");

        String tableComment = "";
        Pattern tableCommentPattern = Pattern.compile("COMMENT='([^']*)'");
        Matcher tableCommentMatcher = tableCommentPattern.matcher(str9);
        if (tableCommentMatcher.find()) {
            tableComment = tableCommentMatcher.group(1);
        }
        // Regular expression to match comments in SQL
        Pattern commentPattern = Pattern.compile("COMMENT '([^']*)'");

        for (String line : columnLines) {
            String[] column = deleteArrayNull(line.replace("\n", "").split(" "));
            if (column.length >= 2) {
                String columnName = column[0].replace("[", "").replace("]", "").replace("`","").toLowerCase();

                Matcher matcher = commentPattern.matcher(line);
                String comment = "";
                if (columnName.equals("id")) {
                    comment = "id"; // Set comment as "id" if column name is "ID"
                }
                if (matcher.find()) {
                    comment = matcher.group(1);
                }
                if (columnName.equals("id") && comment.isEmpty()) {
                    comment = "id"; // Set default comment as "id" if the column name is "id" and the comment is empty
                }
                String typeName = findColumnType(column[1]);
                hiveSqlStr.append("  ").append(columnName).append("  ").append(typeName).append(" comment '").append(comment).append("',\n");
            }
        }

        hiveSqlStr.delete(hiveSqlStr.length() - 2, hiveSqlStr.length());
        hiveSqlStr.append("\n) comment '").append(tableComment).append("'\n")
                .append("partitioned by (dt string)\n")
                .append("ROW FORMAT DELIMITED FIELDS TERMINATED BY '\\001'\nNULL DEFINED AS ''\nLOCATION '/warehouse/hr_us/ods/ods_")
                .append(tableName).append("_full';");
        System.out.println(hiveSqlStr);
    }
}