將SqlServer表創建到hive腳本

將SqlServer表創建到hive腳本

sqlserver.java

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

public class sqlserver {

    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 {
            type = "string"; // Defaulting to STRING for unmatched types
        }

        return type;
    }

    public static void main(String[] args) {

        //sql server
        String str9 = "create table [dbo].[RM_Unit] (\n" +
                "\t[ID] uniqueidentifier not null constraint PK_RM_Unit primary key,\n" +
                "\t[Name] nvarchar(50),\n" +
                "\t[TypeID] int,\n" +
                "\t[ParentID] uniqueidentifier,\n" +
                "\t[LeaderID] uniqueidentifier,\n" +
                "\t[LeaderCountryCode] int,\n" +
                "\t[LocationCode] nvarchar(max),\n" +
                "\t[Description] nvarchar(200),\n" +
                "\t[CountryCode] int,\n" +
                "\t[IsActive] nvarchar(200),\n" +
                ")";

        //sqlserver
        String tableName = str9.split("] \\(\n")[0].split("].\\[")[1].toLowerCase();
        String[] columnLines = str9.split("] \\(\n")[1].split("CONSTRAINT")[0].split(",\n");


        StringBuilder hiveSqlStr = new StringBuilder();
        int columnNum = 0;

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

        // Regular expression to match comments in SQL
        Pattern commentPattern = Pattern.compile(".*?\\[([^\\]]+)\\].*?comment\\s+'([^']*)'");

        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(2);
                }
                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");
                columnNum++;
            }
        }

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

        System.out.println(hiveSqlStr);
    }
}