数据库探测器

前言

    年前的一些产品有对数据库（mysql、oracle、odps、elasticsearch。。。）进行结构探测以及相关的一些基本信息抓取，多个项目中都有牵涉到，一些功能也类似于navicat这样的软件功能，但该探测器更多的是代码层面，适用于一些对多种数据源进行管理的项目。代码地址https://github.com/ChenBoEason/detector 由于个人工作时间原因，博客介绍较少，原谅楼主的懒，后面将会一步步的完善，这只是一个很简单的工具，只是一个很小很小的个人项目，还在思考怎样去壮大它，也想早一点开源出来让有想法的朋友一起去发展它，完善它。

当前满足功能

1.对mysql、oracle、odps数据库进行结构以及基本信息探测。

2.支持基于数据配置进行sql语句生成（搭配前端界面可进行sql动态配置生成）。

3.支持功能函数，对数据进行过滤（类似于数据清洗标化，目前满足功能较少，还在完善中）。

 

核心代码

  顶层入口接口com.cbmiddleware.detector.Detector 方法如下，各个数据源根据个性化差异进行处理，

public interface Detector {
    /**
     * 探测
     *
     * @param detectRequest 探测请求参数
     * @return
     * @throws DetectorException
     */
    DetectResponse detect(DetectRequest detectRequest) throws DetectorException;

    /**
     * 同步回调探测
     *
     * @param detectRequest
     * @param threshold     探测参数回调阀值 如 100，当探测到100张表或列数达到1000时调用回调监听器
     * @param listener
     * @return
     * @throws DetectorException
     */
    void detect(DetectRequest detectRequest, Threshold threshold, DetectorActionListener<DetectResponse> listener) throws DetectorException;

    /**
     * 异步回调阀值探测
     *
     * @param detectRequest 探测请求参数
     * @param listener      回调监听器
     * @return
     * @throws DetectorException
     */
    void detectAsync(DetectRequest detectRequest, Threshold threshold, DetectorActionListener<DetectResponse> listener) throws DetectorException;

    /**
     * 测试连通性
     *
     * @param detectRequest
     * @return
     */
    boolean isConnect(DetectRequest detectRequest) throws DetectorException;

    /**
     * 探测表大小（存储大小，数据量）
     *
     * @param detectRequest
     * @return
     * @throws DetectorException
     */
    DetectResponse detectTableSize(DetectRequest detectRequest, List<String> tableNames) throws DetectorException;

    /**
     * 执行sql
     *
     * @param detectRequest
     * @param sql
     * @return
     * @throws DetectorException
     */
    DetectResponse executeSql(DetectRequest detectRequest, String sql) throws DetectorException;

    /**
     * 探测器类型
     *
     * @return
     */
    DetectorType detectorType();

    /**
     * 探测数据库类型
     *
     * @return
     */
    DataBaseType databaseType();
}

 

 

使用

探测mysql数据库表结构

@Slf4j
public class MysqlDetectorTest {

    private static Detector detector = new MysqlTableDetector();
    private static MysqlDetectRequest request = new MysqlDetectRequest();

    @Before
    public void init(){
        request.setUsername("用户名");
        request.setPassword("密码");
        request.setProject("项目名");
        request.setAddress("连接地址");
        request.setPort("3306");
    }

    @Test
    public void connectTest(){
        boolean connect = false;
        try {
            connect = detector.isConnect(request);
        } catch (DetectorException e) {
            e.printStackTrace();
        }

        System.out.println(connect);
    }

    @Test
    public void detect(){
        List<String> tableNames = new ArrayList<>();
      
        tableNames.add("standard_union_copy1");
        /* 设置后将会探测指定表名信息 */
        //request.setTableNames(tableNames);

        for (int i = 0; i < 1; i++) {
            new Thread(() -> {
                try {


                    MysqlDetectResponse response = (MysqlDetectResponse) detector.detect(request);

                    System.out.println(JSON.toJSONString(response));
                    System.out.println(response.getTableInfos().keySet());;
                } catch (DetectorException e) {
                    e.printStackTrace();
                }
            }).start();
        }
    }

    @Test
    public void sqlTest(){
        String sql = "SELECT * FROM validation_dic_group g\n" +
                "INNER JOIN validation_dic_data d on d.dic_key = g.dic_grp_key\n" +
                "where g.dic_grp_key = 'CVX_Right' limit 0,10";

        try {
            MysqlDetectExecuteSqlResponse response = (MysqlDetectExecuteSqlResponse)detector.executeSql(request, sql);
            System.out.println();
        } catch (DetectorException e) {
            e.printStackTrace();
        }
    }

}

探测oracle

public class OracleDetectorTest {

    private static Detector detector = null;
    private static OracleDetectRequest request = null;

    @Before
    public void init() {
        detector = new OracleTableDetector();
        request = new OracleDetectRequest();
        request.setUsername("sys as sysdba");
        request.setPassword("system");
        request.setProject("");
        request.setAddress("");
        request.setPort("1521");
        request.setSid("orcl");
        request.setTestConnection(false);
    }

    @Test
    public void detectorTest() {
        try {
            OracleDetectResponse response = (OracleDetectResponse) detector.detect(request);


            System.out.println(JSON.toJSONString(response));
        } catch (DetectorException e) {
            e.printStackTrace();
        }
    }

    @Test
    public void detectorListenerTest() {
        Threshold threshold = new Threshold();
        threshold.setTableCount(1);
        threshold.setColumnCount(10);
        try {
            detector.detect(request, threshold, new DetectorActionListener<DetectResponse>() {
                @Override
                public void onResponse(DetectResponse detectResponse) {
                    OracleDetectResponse response = (OracleDetectResponse) detectResponse;

                    System.out.println(JSON.toJSONString(response));
                }

                @Override
                public void onFailure(Exception e) {

                }
            });


        } catch (DetectorException e) {
            e.printStackTrace();
        }
    }

    @Test
    public void sqlTest(){
        String sql = "SELECT\n" +
                "\t* \n" +
                "FROM\n" +
                "\tDATAQULITY.MZ_GUAHAO1 mz \n" +
                "LEFT JOIN DATAQULITY.SYS_USER s on s.ID != mz.BINGRENID";

        try {
           OracleDetectExecuteSqlResponse response = (OracleDetectExecuteSqlResponse) detector.executeSql(request, sql);
            System.out.println(JSON.toJSONString(response));
        } catch (DetectorException e) {
            e.printStackTrace();
        }
    }
}

odps的探测

@Slf4j
public class OdpsDetectorTest {

    private static Detector detector = null;

    private static OdpsDetectRequest request = null;

    @Before
    public void init(){
        detector = new OdpsTableDetector();
        OdpsDetectRequest request = new OdpsDetectRequest();
        request.setAccessId("");
        request.setAccessKey("");
        request.setProject("");
        request.setUrl("");
        request.setTunnelUrl("");
    }


    @Test
    public void connectTest(){
        try {
            doConnect(detector, request);
        } catch (DetectorException e) {
            e.printStackTrace();
        }
    }

    @Test
    public void detectTest(){
        try {
            detect(detector, request);
        } catch (DetectorException e) {
            e.printStackTrace();
        }
    }

    @Test
    public void tableSizetest(){
        try {
            detectTableSize(detector, request);
        } catch (DetectorException e) {
            e.printStackTrace();
        }
    }

    @Test
    public void detectByListenerTest(){
        try {
            detectByListener(detector, request);
        } catch (DetectorException e) {
            e.printStackTrace();
        }
    }



    private static void detectByListener(Detector detector, OdpsDetectRequest request) throws DetectorException{
        Threshold threshold = new Threshold();

        threshold.setTableCount(10);

        detector.detect(request, threshold, new DetectorActionListener<DetectResponse>() {
            @Override
            public void onResponse(DetectResponse detectResponse) {
                OdpsDetectResponse response = (OdpsDetectResponse) detectResponse;
                log.info("listener response result:{}", JSON.toJSONString(response));
            }

            @Override
            public void onFailure(Exception e) {

            }
        });
    }

    private static void executeSql(Detector detector, OdpsDetectRequest request) throws DetectorException {
        OdpsDetectExecuteSqlResponse response = (OdpsDetectExecuteSqlResponse)detector.executeSql(request, "select * from ods_mh_yhqdyrmyy_zy_feiyong1_his limit 20;");
        log.info("result :{}",JSON.toJSONString(response));
    }

    private static void detectTableSize(Detector detector, OdpsDetectRequest request) throws DetectorException {
        List<String> tableNames = new ArrayList<>();
        
        request.setTableNames(tableNames);
        OdpsDetectTableSizeResponse response = (OdpsDetectTableSizeResponse)detector.detectTableSize(request, tableNames);
        log.info("result :{}",JSON.toJSONString(response));
    }

    private static void detect(Detector detector, OdpsDetectRequest request) throws DetectorException {
        OdpsDetectResponse response = (OdpsDetectResponse) detector.detect(request);
        log.info("result :{}",JSON.toJSONString(response));
    }

    private static void doConnect(Detector detector, OdpsDetectRequest request) throws DetectorException {

        boolean connect = detector.isConnect(request);
        log.info("connect status :{}", connect);
    }
}

sql动态生成测试

   下面测试只是个人的一个基于sql转换为代码，使用场景更多的是搭配前端进行配置生成。

public class MysqlSqlGeneratorTest {


    @Test
    public void threeTableJoin() {

        String sql = "SELECT\n" +
                "\tt.id AS id,\n" +
                "\tt.project AS project,\n" +
                "\tt.table_name AS tableName,\n" +
                "\tc.column_comment AS columnComment,\n" +
                "\tc.column_name AS columnName,\n" +
                "\ts.url as jdbcUrl,\n" +
                "\ts.username as dbUsername,\n" +
                "\tem.data_element_column_name as elementColmnName,\n" +
                "\tNULL AS emptyColumn \n" +
                "FROM\n" +
                "\tmed_detect_table t\n" +
                "\tINNER JOIN med_detect_table_column c ON c.table_name = t.name\n" +
                "\tLEFT JOIN med_data_source s on  t.db_code = s.db_code\n" +
                "\tLEFT JOIN med_data_element_mapping_info em on em.column_name = c.column_name and em.name = 'chenbo'\n" +
                "WHERE\n" +
                "\tt.db_code = 'xBbGIw2C' \n" +
                "\tAND t.creator = 'SYSTEM'\n" +
                "\tAND t.gmt_create <= NOW()";

        GenerateSqlConfInfo generateSqlConf = new GenerateSqlConfInfo();

        ExtraCondition extraCondition = new ExtraCondition();
        extraCondition.setTableName("med_data_source");
        extraCondition.setConditionType(ConditionType.gt);
        extraCondition.setColumnName("time");
        extraCondition.setValue("%s");
        extraCondition.setRelation(ColumnRelation.and);

        System.out.println(JSON.toJSONString(extraCondition));

        /**
         * 构建 med_detect_table 表配置
         */
        QueryTable detectTable = new QueryTable("med_detect_table", "t");
        /* gmt_create 条件 */
        ConditionInfo gmtContidion = new ConditionInfo("gmt_create", ConditionType.lte, new Date(), ColumnRelation.and);
        ConditionInfo creatorCondition = new ConditionInfo("creator", ConditionType.eq, "SYSTEM", ColumnRelation.and, gmtContidion);
        ConditionInfo dbCodeCondition = new ConditionInfo("db_code", ConditionType.eq,"xBbGIw2C",null, creatorCondition);


        /* 设置基表where条件 */
        generateSqlConf.setWhere(dbCodeCondition);

        /**
         * 构建med_detect_table_column表配置
         */
        QueryTable columnTable = new QueryTable("med_detect_table_column", "c");
        /* 与med_detect_table inner join */
        columnTable.setTableRelation(TableRelation.inner_join);
        columnTable.setConditionInfo(new ConditionInfo("table_name", ConditionType.eq, "t", "name"));

        /**
         * med_data_source
         */
        QueryTable datasourceTable = new QueryTable("med_data_source", "s");
        /* 与med_detect_table left join */
        datasourceTable.setTableRelation(TableRelation.left_join);
        datasourceTable.setConditionInfo(new ConditionInfo("db_code", ConditionType.eq, "t", "db_code"));

        /**
         * med_data_element_mapping_info
         */
        QueryTable dataElementTable = new QueryTable("med_data_element_mapping_info", "em");
        /* 与med_detect_table_column left join */
        dataElementTable.setTableRelation(TableRelation.left_join);
        dataElementTable.setConditionInfo(new ConditionInfo("column_name", ConditionType.eq, "c", "column_name"));


        List<QueryTable> queryTables = new ArrayList<>();
        /* med_detect_table_column 和med_data_element_mapping_info与med_detect_table关联 */
        queryTables.add(columnTable);
        //columnTable.setQueryTables(datasourceTable);
       // queryTables.add(datasourceTable);
        queryTables.add(datasourceTable);
        detectTable.setQueryTables(queryTables);

        /* med_data_element_mapping_info 与构建med_detect_table_column表配置关联 */
        List<QueryTable> columnQueryTables = new ArrayList<>();
        columnQueryTables.add(dataElementTable);
        detectTable.setQueryTables(columnQueryTables);

        generateSqlConf.setQueryTable(detectTable);

        /**
         * 返回字段
         */
        List<QueryColumn> returnColumns = new ArrayList<>();

        returnColumns.add(new QueryColumn("t", "id", "id"));
        returnColumns.add(new QueryColumn("t", "project", "project"));
        returnColumns.add(new QueryColumn("t", "table_name", "tableName"));
        returnColumns.add(new QueryColumn("c", "column_comment", "columnComment"));


        QueryColumn queryColumn = new QueryColumn("c", "column_name", "columnName");


        ColumnCaseWhen columnCaseWhen = new ColumnCaseWhen();
        columnCaseWhen.setTableAlias("c");
        columnCaseWhen.setColumnName("column_name");

        /**
         * case when
         */
        /*List<CaseWhenCondition> conditions = new ArrayList<>();
        columnCaseWhen.setConditions(conditions);

        conditions.add(new CaseWhenCondition("eason", ConditionType.eq, "chenbo"));

        conditions.add(new CaseWhenCondition(1, ConditionType.eq, "one"));

        queryColumn.setFunctionParam(columnCaseWhen);*/
        returnColumns.add(queryColumn);



        returnColumns.add(new QueryColumn(null, null, "columnName"));

        returnColumns.add(new QueryColumn("s", "url", "jdbcUrl"));
        returnColumns.add(new QueryColumn("s", "username", "dbUsername"));
        returnColumns.add(new QueryColumn("em", "data_element_column_name", "elementColmnName"));

        generateSqlConf.setReturnColumns(returnColumns);

        SqlGenerator sqlGenerator = new MysqlSqlGenerator();

        /*try {
            sqlGenerator.join(extraCondition, generateSqlConf.getQueryTable(), 0, 50);
        } catch (DetectorException e) {
            e.printStackTrace();
        }*/

        String generateSql = null;
        try {
            System.out.println(JSON.toJSONString(generateSqlConf));
            generateSql = sqlGenerator.generate(generateSqlConf);
        } catch (DetectorException e) {
            e.printStackTrace();
        }

        /*Map<String, Object> sqlConfMap = new HashMap<>(2);
        sqlConfMap.put("extraCondition", extraCondition);
        sqlConfMap.put("sqlConfInfo", generateSqlConf);
        //System.out.println(JSON.toJSONString(sqlConfMap));
        System.out.println(JSON.toJSONString(extraCondition));
        System.out.println(JSON.toJSONString(generateSqlConf));*/
    }

    @Test
    public void twoTableJoin() {

        SqlGenerator sqlGenerator = new MysqlSqlGenerator();


        String sql = "SELECT\n" +
                "\tt.id as id,\n" +
                "\tt.project as project,\n" +
                "\tt.table_name as tableName,\n" +
                "\tc.column_comment as columnComment,\n" +
                "\tc.column_name as columnName\n" +
                "FROM\n" +
                "\tmed_detect_table t\n" +
                "\tINNER JOIN med_detect_table_column c ON c.table_name = t.table_name \n" +
                "WHERE\n" +
                "\tt.db_code = 'xBbGIw2C' \n" +
                "\tAND t.creator = 'SYSTEM'";

        GenerateSqlConfInfo generateSqlConf = new GenerateSqlConfInfo();

        /**
         * 构建 med_detect_table 表配置
         */
        QueryTable detectTable = new QueryTable("med_detect_table", "t");

        ConditionInfo dbCodeCondition = new ConditionInfo("db_code", ConditionType.eq,
                "xBbGIw2C",
                null,
                new ConditionInfo("creator", ConditionType.eq, "SYSTEM", ColumnRelation.and));

        generateSqlConf.setWhere(dbCodeCondition);

        /**
         * 构建med_detect_table_column表配置
         */
        QueryTable columnTable = new QueryTable("med_detect_table_column", "c");
        columnTable.setTableRelation(TableRelation.inner_join);
        columnTable.setConditionInfo(new ConditionInfo("table_name", ConditionType.eq, "t", "table_name"));

        /**
         * 两表关联
         */
        List<QueryTable> queryTables = new ArrayList<>();
        queryTables.add(columnTable);
        detectTable.setQueryTables(queryTables);

        generateSqlConf.setQueryTable(detectTable);

        /**
         * 字段设置
         */
        List<QueryColumn> returnColumns = new ArrayList<>();

        returnColumns.add(new QueryColumn("t", "id", "id"));
        returnColumns.add(new QueryColumn("t", "project", "project"));
        returnColumns.add(new QueryColumn("t", "table_name", "tableName"));
        returnColumns.add(new QueryColumn("c", "column_comment", "columnComment"));
        returnColumns.add(new QueryColumn("c", "column_name", "columnName"));
        returnColumns.add(new QueryColumn(null, null, "columnName"));

        generateSqlConf.setReturnColumns(returnColumns);


        String generateSql = null;
        try {
            generateSql = sqlGenerator.generate(generateSqlConf);
        } catch (DetectorException e) {
            e.printStackTrace();
        }
        //System.out.println(generateSql);

        //System.out.println(JSON.toJSONString(generateSqlConf));
    }
}