简单程序实现 mysql 表创建sql 转成 doris 表创建sql

谁践踏了优雅 2022-12-17 11:56 780阅读 0赞

# 1,导入依赖 #

<dependency>
        <groupId>com.github.jsqlparser</groupId>
        <artifactId>jsqlparser</artifactId>
        <version>3.1</version>
    </dependency>

# 2,读取sql文件,sql转换 #

>     package readsql;
>     
>     import net.sf.jsqlparser.JSQLParserException;
>     import net.sf.jsqlparser.parser.CCJSqlParserManager;
>     import net.sf.jsqlparser.statement.Statement;
>     import net.sf.jsqlparser.statement.create.table.ColDataType;
>     import net.sf.jsqlparser.statement.create.table.ColumnDefinition;
>     import net.sf.jsqlparser.statement.create.table.CreateTable;
>     import org.apache.commons.lang3.StringUtils;
>     
>     import java.io.*;
>     import java.util.ArrayList;
>     import java.util.List;
>     
>     public class ReadSqlFileCreateDorisSqlResult {
>     
>         public static void main(String[] args) throws JSQLParserException {
>     
>     
>             try {
>                 List<String> list = ReadSqlFileCreateDorisSqlResult.readFile("doris_test.sql");
>     //            List<String> list = SqlFileUtil2.readFile("q2.sql");
>     
>     
>                 //todo 最后输出格式一
>                 List<String> createSqlList = new ArrayList<>();
>     
>                 //todo 最后输出格式二，字符串
>                 StringBuilder finalSqlBuilder = new StringBuilder();
>     
>                 for (String sql : list) {
>                     //todo 字段到结尾
>                     StringBuilder sqlBuilder = new StringBuilder();
>     
>                     //todo 建表语句+主键
>                     StringBuilder prefixBuilder = new StringBuilder();
>     
>                    CCJSqlParserManager parser = new CCJSqlParserManager();
>                     List<ColumnDefinition> columnList = null; //todo 列 字段名称跟信息。
>                     String tableName = ""; //todo 表名字
>                     List<String> pkColumnsList = null ; //todo 主键名称
>     
>                     if(sql.contains("AUTO_INCREMENT")){
>                         sql = sql.replaceAll("AUTO_INCREMENT","");
>                     }
>                     Statement stmt = parser.parse(new StringReader(sql));
>                     if (stmt instanceof CreateTable) {
>                         tableName= ((CreateTable) stmt).getTable().getName();
>                         ((CreateTable) stmt).getIndexes();
>                         pkColumnsList = ((CreateTable) stmt).getIndexes().get(0).getColumnsNames();
>                         columnList = ((CreateTable) stmt).getColumnDefinitions();
>                     }
>     
>                     //todo 创建语句
>                     prefixBuilder.append("CREATE TABLE ")
>                             .append(tableName)
>                             .append(" ")
>                             .append("(").append("\n");
>     
>     
>                     //todo 先将主键字段放在第一位
>     
>                    //todo 全部变成varchar()
>                     for (int i = 0; i < columnList.size(); i++) {
>                         String columnString = columnList.get(i).toString();
>                         String columnName = columnList.get(i).getColumnName();
>                         ColDataType columnDataType = columnList.get(i).getColDataType();
>     
>                         //todo 字段的类型
>                         String dataType = columnDataType.getDataType();
>     
>     
>                         //todo  是主键的情况下 主键放最前面。
>                         if (pkColumnsList.contains(columnName)){
>                             if ("varchar".equals(dataType)){
>                                 List<String> argumentsStringList = columnDataType.getArgumentsStringList();
>                                 Integer typeValueTriple = Integer.valueOf(argumentsStringList.get(0)) * 5;
>                                 prefixBuilder.append(columnName).append(" ").append(dataType).append("(").append(typeValueTriple).append(")");
>     
>                             }else if("datetime".equals(dataType)|| "date".equals(dataType)){
>                                 prefixBuilder.append(columnName).append(" ").append(dataType);
>                             }else {
>                                 prefixBuilder.append(columnString);
>                             }
>                             prefixBuilder.append(",").append("\n");
>                         }else {
>                             //todo 如果是varchar 类型就放大三倍
>                             if ("varchar".equals(dataType)){
>                                 List<String> argumentsStringList = columnDataType.getArgumentsStringList();
>                                 Integer typeValueTriple = Integer.valueOf(argumentsStringList.get(0)) * 5;
>     
>                                 sqlBuilder.append(columnName).append(" ").append(dataType).append("(").append(typeValueTriple).append(")");
>     
>                             }else if("datetime".equals(dataType)|| "date".equals(dataType)){
>                                 sqlBuilder.append(columnName).append(" ").append(dataType);
>                             }else {
>                                 sqlBuilder.append(columnString);
>                             }
>                             sqlBuilder.append(",").append("\n");
>                         }
>     
>     
>     
>                     }
>                     if (sqlBuilder.length()>0){
>                         sqlBuilder.delete(sqlBuilder.length()-2, sqlBuilder.length());
>                         sqlBuilder.append("\n").append(")").append("\n");
>                     }else {
>                         //todo 字段全是主键
>                         System.out.println("111111 = " + 111111);
>                     }
>                  
>     
>     
>                     sqlBuilder.append("UNIQUE KEY(");
>                     for (int i = 0; i < pkColumnsList.size(); i++) {
>                         sqlBuilder.append(pkColumnsList.get(i)+",");
>                     }
>                     sqlBuilder.delete(sqlBuilder.length()-1,sqlBuilder.length());
>                     sqlBuilder.append(")").append("\n").append("DISTRIBUTED BY HASH(");
>                     for (int i = 0; i < pkColumnsList.size(); i++) {
>                         sqlBuilder.append(pkColumnsList.get(i)+",");
>                     }
>                     sqlBuilder.delete(sqlBuilder.length()-1,sqlBuilder.length());
>                     sqlBuilder.append(")").append(" ").append("BUCKETS 10").append("\n").append("PROPERTIES(\"replication_num\" = \"3\")");
>     
>                     prefixBuilder.append(sqlBuilder);
>                     createSqlList.add(prefixBuilder.toString());
>                     finalSqlBuilder.append(prefixBuilder.toString()+";\n\n");
>                 }
>     
>                 System.out.println("最后的SQL = " + createSqlList.toString());
>                 System.out.println("最后的SQL2 = " + finalSqlBuilder.toString());
>             } catch (IOException e) {
>                 e.printStackTrace();
>             }
>             
>         }
>     
>     
>         public static List<String> readFile(String fileName) throws IOException {
>             // list store return sql
>             List<String> sqlList = new ArrayList<String>();
>             // add widonw path
>             if (File.separator.equals("\\")) {
>                 System.out.println("XXXX = " + ReadSqlFileCreateDorisSqlResult.class.getClassLoader().getResource("").getPath());
>                 fileName = ReadSqlFileCreateDorisSqlResult.class.getClassLoader().getResource("").getPath() + fileName;
>             }
>             File file = new File(fileName);
>             // check file exists
>             if (!file.exists()) {
>                 System.out.println("File not found: " + fileName);
>                 System.exit(-1);
>             }
>             // read file
>             BufferedReader br = new BufferedReader(new FileReader(file));
>             String line;
>             StringBuilder sqlBuffer = new StringBuilder();
>             while ((line = br.readLine()) != null) {
>                 // ignore empty line and comment line
>                 if (StringUtils.isEmpty(line) || line.trim().startsWith("--")||line.trim().startsWith("DROP")) {
>                     continue;
>                 }
>     
>                 // remove comment
>                 if (line.contains("--")) {
>                     line = line.substring(0, line.indexOf("--"));
>                 }
>                 // add current line to sqlBuffer
>                 sqlBuffer.append(line);
>                 sqlBuffer.append("\n");
>                 // check sql end
>                 if (line.endsWith(";")) {
>                     // add sql to sqlList
>                     String tmpSql = sqlBuffer.toString();
>                     // remove last ";"
>                     tmpSql = tmpSql.substring(0, tmpSql.lastIndexOf(";"));
>                     sqlList.add(tmpSql);
>                     // remove StringBuilder
>                     sqlBuffer.delete(0, sqlBuffer.length());
>                 }
>             }
>             // if last sql sentence not end with ";"
>             if (sqlBuffer.length() != 0) {
>                 sqlList.add(sqlBuffer.toString());
>             }
>             return sqlList;
>     
>     
>         }
>     
>     
>         /*public static List<ColumnDefinition> getTableNameBySql(String sql) throws JSQLParserException {
>             CCJSqlParserManager parser = new CCJSqlParserManager();
>             List<ColumnDefinition> columnList = null;
>             Statement stmt = parser.parse(new StringReader(sql));
>             if (stmt instanceof CreateTable) {
>                 String name = ((CreateTable) stmt).getTable().getName();
>     
>                 List<String> columnsNames = ((CreateTable) stmt).getIndexes().get(0).getColumnsNames();
>                 columnList = ((CreateTable) stmt).getColumnDefinitions();
>     
>             }
>             return columnList;
>         }*/
>     }

# 3，结果验证 #

doris\_test.sql 文件

DROP TABLE IF EXISTS `test2`;
    CREATE TABLE `test2` (
      `id` bigint(32) NOT NULL AUTO_INCREMENT,
      `name` varchar(255) NOT NULL,
      `etl_date` varchar(32) NOT NULL,
      PRIMARY KEY (`id`,`name`)
    ) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;

## 运行结果： ##

XXXX = /G:/flink-projects/flink-zeppelin-scala/target/classes/  
最后的SQL = \[CREATE TABLE \`test2\` (  
\`id\` bigint (32) NOT NULL,  
\`name\` varchar(1275),  
\`etl\_date\` varchar(160)  
)  
UNIQUE KEY(\`id\`,\`name\`)  
DISTRIBUTED BY HASH(\`id\`,\`name\`) BUCKETS 10  
PROPERTIES("replication\_num" = "3")\]  
最后的SQL2 = CREATE TABLE \`test2\` (  
\`id\` bigint (32) NOT NULL,  
\`name\` varchar(1275),  
\`etl\_date\` varchar(160)  
)  
UNIQUE KEY(\`id\`,\`name\`)  
DISTRIBUTED BY HASH(\`id\`,\`name\`) BUCKETS 10  
PROPERTIES("replication\_num" = "3");

## 4，注意点 ##

代码没什么难点，就是要注意sql文件所在路径，放在resource下 在打包的时候自己会加载到target下

XXXX = /G:/flink-projects/flink-zeppelin-scala/target/classes/