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

1,导入依赖

com.github.jsqlparserjsqlparser3.1

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 list = ReadSqlFileCreateDorisSqlResult.readFile("doris_test.sql");
//            List list = SqlFileUtil2.readFile("q2.sql");//todo 最后输出格式一List 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 columnList = null; //todo 列 字段名称跟信息。String tableName = ""; //todo 表名字List 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 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 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 readFile(String fileName) throws IOException {// list store return sqlList sqlList = new ArrayList();// add widonw pathif (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 existsif (!file.exists()) {System.out.println("File not found: " + fileName);System.exit(-1);}// read fileBufferedReader br = new BufferedReader(new FileReader(file));String line;StringBuilder sqlBuffer = new StringBuilder();while ((line = br.readLine()) != null) {// ignore empty line and comment lineif (StringUtils.isEmpty(line) || line.trim().startsWith("--")||line.trim().startsWith("DROP")) {continue;}// remove commentif (line.contains("--")) {line = line.substring(0, line.indexOf("--"));}// add current line to sqlBuffersqlBuffer.append(line);sqlBuffer.append("\n");// check sql endif (line.endsWith(";")) {// add sql to sqlListString tmpSql = sqlBuffer.toString();// remove last ";"tmpSql = tmpSql.substring(0, tmpSql.lastIndexOf(";"));sqlList.add(tmpSql);// remove StringBuildersqlBuffer.delete(0, sqlBuffer.length());}}// if last sql sentence not end with ";"if (sqlBuffer.length() != 0) {sqlList.add(sqlBuffer.toString());}return sqlList;}/*public static List getTableNameBySql(String sql) throws JSQLParserException {CCJSqlParserManager parser = new CCJSqlParserManager();List columnList = null;Statement stmt = parser.parse(new StringReader(sql));if (stmt instanceof CreateTable) {String name = ((CreateTable) stmt).getTable().getName();List 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/

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！