Multi-source database automatic discovery and exploration method

Abstract

The invention relates to an automatic discovery and exploration method for a multi-source database, and belongs to the technical field of big data. According to the method, the multi-source database is automatically discovered, the newly discovered database is connected, and metadata exploration is performed according to the metadata model; and executing database quality automatic exploration. According to the method, the databases in the specified IP range can be automatically searched, the port type, the database type and the drive type can be automatically judged, various multi-source databases capable of being connected can be quickly found, and the working efficiency of accessing the databases is remarkably improved; after the database is successfully connected, the metadata of the database is automatically explored according to the user-defined metadata exploration model, preliminary exploration of a database table structure is achieved, in-depth quality exploration is conducted on data content according to the user-defined quality model, a quality report is generated, and the working efficiency of subsequent data governance and data integration is remarkably improved.

Description

Technical Field

[0001] This invention belongs to the field of big data technology, specifically relating to a method for automatic discovery and exploration of multi-source databases. Background Technology

[0002] In the big data field, a data platform aggregates and governs multimodal data, abstracts and encapsulates data into services, and provides them to specific business applications. Currently, data platforms have become an important measure for enterprises to optimize existing businesses and achieve new business transformation, enabling them to build data-driven, automated enterprises.

[0003] Data access is the entry point for the entire data platform. Efficient and high-quality access to multi-source databases is the foundation for data governance, integration, and other operations. Most existing data platforms require manual configuration of the databases to be accessed, manual exploration and quality analysis of metadata, which greatly reduces the efficiency and quality of data access. How to quickly and efficiently access multi-source databases and assist in the automatic discovery of databases and exploration of data content and quality has become an urgent problem to be solved. Summary of the Invention (a) Technical problems to be solved The technical problem to be solved by this invention is how to provide a method for automatically discovering and exploring multi-source databases, so as to solve the problem of quickly and efficiently accessing multi-source databases and assisting in the automatic discovery of databases and exploration of data content and data quality.

[0004] (II) Technical Solution To address the aforementioned technical problems, this invention proposes a method for automatic discovery and exploration of multi-source databases, comprising the following steps: S1. Automatic discovery of multi-source databases, specifically including: S11. Based on the provided IP address range and the built-in port list, automatically discover which IP addresses host databases; determine the database type on the server based on the IP address and port. S12. Determine the driver for the database based on its IP address, port, and database type; S13. Display the database automatically discovered based on IP address, port, and driver, and finally ask the user to enter the username and password; S2. Automatic Database Metadata Probing: Connect to newly discovered databases and perform metadata probing based on the metadata model. This includes probing all tables in each database schema, including table structure and all fields under each table, as well as detailed attributes of views and constraints. The metadata probing results are stored in the middle platform metadata database. S3, Automatic Database Quality Check S31. Based on the metadata exploration results, connect to the newly discovered databases and analyze them according to the quality model; S32. Generate a quality analysis report based on metadata and quality exploration results.

[0005] (III) Beneficial Effects This invention proposes an automatic discovery and exploration method for multi-source databases. This method can automatically search for databases within a specified IP range and automatically determine the port, database type, and driver type, quickly discovering various connectable multi-source databases and significantly improving the efficiency of database access. After successfully connecting to the database, it automatically explores the database metadata based on a custom metadata exploration model, achieving a preliminary exploration of the database table structure. Based on a custom quality model, it performs in-depth quality exploration of the data content and generates a quality report, significantly improving the efficiency of subsequent data governance and data integration. This invention also supports various multi-source relational databases, big data platforms, object storage, etc. Attached Figure Description

[0006] Figure 1 This is a flowchart of the method of the present invention. Detailed Implementation

[0007] To make the objectives, contents, and advantages of the present invention clearer, the specific embodiments of the present invention will be described in further detail below with reference to the accompanying drawings and examples.

[0008] Therefore, the purpose of this invention is to propose a system and method for automatic discovery and exploration of multi-source databases.

[0009] This invention provides a system and method for automatically discovering and exploring multi-source databases, comprising the following steps: S1. Automatic discovery of multi-source databases, specifically including: S11. Based on the provided IP address range and the built-in port list, automatically discover which IP addresses host databases; determine the database type on the server based on the IP address and port. S12. Determine the driver for the database based on its IP address, port, and database type; S13. The system displays the database automatically discovered based on IP address, port, and driver. Finally, the user enters the username and password. S2. Automatic Database Metadata Probing: Connect to newly discovered databases and perform metadata probing based on the metadata model. This includes probing all tables in each database schema, including table structure and all fields under each table, as well as detailed attributes of views and constraints. The metadata probing results are stored in the middle platform metadata database. S3, Automatic Database Quality Check S31. Based on the metadata exploration results, connect to the newly discovered databases and analyze them according to the quality model; S32. Generate a quality analysis report based on metadata and quality exploration results.

[0010] Example 1: 1. Automatic discovery of multi-source databases: (1) Based on the provided IP address range and the built-in port list, automatically discover which IP addresses have databases; (2) Based on the IP address and port, determine the database type on the server; (3) Based on the IP address, port and database type, determine the driver of the database; (4) The system displays the databases automatically discovered based on the IP address, port and driver, and finally the user enters the username and password.

[0011] This invention supports multiple database types, including relational databases such as DM, Kingbase, Shentong, Nanda General, Oracle, and MySQL, as well as big data platforms such as Hive, HBase, and HDFS, and graph databases such as GStore, Neo4j, spatiotemporal databases such as PostgreSQL, FTP, SFTP, Minio, and Ceph.

[0012] (1) IP detection The most comprehensive probing solution would be to search for all available IP addresses on the current host and then check connectivity one by one based on commonly used ports. However, this method requires access to a massive number of IP addresses, making it impractical. Therefore, a user-input IP address method is used for probing. Multiple IP addresses can be entered by the user, and a range of IP addresses can be input, as shown below. Based on the IP information entered by the user, first determine whether the IP can communicate. If it cannot communicate, return failure directly and record the IP address that can communicate.

[0013] (2) Port and database type probing List the commonly used default ports and similar port items for multi-source databases as the basis for automatic database discovery.

[0014] Attempt to connect via IP address and default port. If the port connection fails, try connecting to other ports similar to the default port. For example, if the default port is 1521, try connecting via IP address and port 1521. If the port connection fails, try connecting via 1522, 1523, 11521, etc. If none of these connections are successful, the probe fails. If a connection is successful, attempt to connect based on the database type corresponding to the port. Based on the returned information, determine if the database type is correct. If it is, return the IP address, port, and database type.

[0015] Each port is assigned a weight. If a port connects successfully, its weight is automatically increased, and the next connection will prioritize the port with the higher weight. At the same time, the system automatically records each database port that the user manually adds and successfully connects to. If the port is not in the port list, it will be automatically added to the port list.

[0016] (3) Drive exploration This situation is mainly used when there are multiple driver versions for the database type. In this case, the system will try to connect using different driver versions based on the IP address, port, and database type to determine the specific driver version.

[0017] (4) Connect to the database The system displays databases automatically discovered based on IP address, port, and driver. The user then enters their username and password. Once the system successfully connects, it automatically discovers and accesses the database.

[0018] 2. Automatic Database Metadata Probing: Connect to newly discovered databases and perform metadata probing based on the metadata model. This includes probing all database schemas and all tables within each schema, including table structure and all fields, as well as detailed attributes of views and constraints. The metadata probing results are stored in the central platform metadata database.

[0019] (1) Metadata is a description of data. Metadata models are divided into library models, table models, field models, unstructured file models, and custom metadata models for big data platforms, object storage, etc.

[0020] The metadata information of a database includes database name, database type, database version number, database URL, number of tables in the database, database IP address, port, database storage size, last update time of the database structure, and last update time of the database data. The metadata information of a table includes field table name, database, number of data resources, database version number, database IP address, port, table comments, number of fields, number of records, table storage size, and last update time of the table structure. The metadata information of a field includes field name, field comments, table, database, field data type, field length, field precision, default value, whether it is a primary key, whether it is nullable, whether it is unique, whether it is auto-incrementing, and last update time. The metadata information of a file includes file name, file directory path, file type, file storage location, file size, and data file format.

[0021] (2) Perform metadata exploration based on the metadata model. The results of the metadata exploration are stored in the corresponding tables of the middle platform metadata database according to different metadata models.

[0022] 3. Automatic database quality detection: (1) Based on the metadata detection results, connect to newly discovered databases and analyze them according to the quality model; (2) Generate a quality analysis report based on the metadata and quality detection results.

[0023] (1) The quality model at the database level includes the number of data tables, the total number of data rows, the total size of data storage, the data distribution, the incremental data volume analysis, the null value analysis of tables, and the proportion of comments, etc.; at the data table level, it includes the number of fields, the size of data, the size of data storage, the incremental data analysis, the null value analysis of fields, the proportion of comments, and the format analysis of some common fields, including ID card number format check, mobile phone number format check, date format check, and abnormal field check, including abnormal numerical length, abnormal enumeration format, warning of excessive field length, warning of missing index, etc.

[0024] (2) Perform database content quality analysis based on the quality model, and store the quality analysis results in the middle platform quality analysis database.

[0025] (3) Generate a quality analysis report based on metadata and quality exploration results.

[0026] This invention designs a highly efficient and automated database discovery method. This method can automatically search for databases within a specified IP range and automatically determine the port, database type, and driver type, quickly discovering various multi-source databases that can be connected, significantly improving the efficiency of database access. After successfully connecting to the database, it automatically probes the database metadata based on a custom metadata probing model, achieving a preliminary exploration of the database table structure. Based on a custom quality model, it performs in-depth quality probing of the data content, generating a quality report, significantly improving the efficiency of subsequent data governance and data integration. This invention also supports various multi-source relational databases, big data platforms, object storage, etc.

[0027] The above description is only a preferred embodiment of the present invention. It should be noted that for those skilled in the art, several improvements and modifications can be made without departing from the technical principles of the present invention, and these improvements and modifications should also be considered within the scope of protection of the present invention.

Claims

1. A method for automatically discovering and exploring multi-source databases, characterized in that, The method includes the following steps: S1. Automatic discovery of multi-source databases, specifically including: S11. Based on the provided IP address range and the built-in port list, automatically discover which IP addresses host databases; determine the database type on the server based on the IP address and port. S12. Determine the driver for the database based on its IP address, port, and database type; S13. Display the database automatically discovered based on IP address, port, and driver, and finally ask the user to enter the username and password; S2. Automatic Database Metadata Probing: Connect to newly discovered databases and perform metadata probing based on the metadata model. This includes probing all tables in each database schema, including table structure and all fields under each table, as well as detailed attributes of views and constraints. The metadata probing results are stored in the middle platform metadata database. S3, Automatic Database Quality Check S31. Based on the metadata exploration results, connect to the newly discovered databases and analyze them according to the quality model; S32. Generate a quality analysis report based on metadata and quality exploration results.

2. The method for automatic discovery and exploration of multi-source databases as described in claim 1, characterized in that, The multi-source databases include: relational databases, graph databases, and spatiotemporal databases; relational databases include: DM, Kingbase, Shentong, Nanda General, Oracle, and MySQL; graph databases include: GStore and Neo4j; and spatiotemporal databases include: PostgreSQL, FTP, SFTP, Minio, and Ceph.

3. The method for automatic discovery and exploration of multi-source databases as described in claim 1, characterized in that, S11 includes: probing using user-input IP addresses, where multiple IP addresses can be input by the user, and a range of IP addresses can be input; based on the user-input IP information, first determine whether the IP address can communicate, and if it cannot communicate, return failure directly and record the IP addresses that can communicate. List the commonly used default ports and similar port items of multi-source databases as the basis for automatic database discovery; Attempt to connect via IP address and default port. If the port connection fails, try connecting to other ports similar to the default port. If all ports fail to connect, the probe fails. If a connection is established, attempt to connect based on the database type corresponding to the port. Based on the returned information, determine if the database type is correct. If it is, return the IP address, port, and database type.

4. The method for automatic discovery and exploration of multi-source databases as described in claim 3, characterized in that, S11 further includes: assigning weights to each port; if a port is successfully connected, its weight is automatically increased, and the port with higher weight is selected first for the next connection; at the same time, the database port that is successfully connected manually by the user is automatically recorded each time, and if it is not in the port list, it is automatically added to the port list.

5. The method for automatic discovery and exploration of multi-source databases as described in claim 3, characterized in that, S12 includes: based on the IP address, port, and database type, attempting to connect using different driver versions to determine the specific driver version.

6. The method for automatic discovery and exploration of multi-source databases as described in claim 5, characterized in that, S13 includes: displaying a database automatically discovered based on IP address, port, and driver, whereby the user finally enters the username and password, and after the system successfully connects, it automatically discovers and successfully accesses the database.

7. The method for automatic discovery and exploration of multi-source databases as described in claim 6, characterized in that, In S2, the metadata model is divided into a library model, a table model, a field model, and an unstructured file model. The database's metadata information includes the database name, database type, database version number, database URL, number of tables in the database, database IP address, port, database storage size, last update time of the database structure, and last update time of the database data. The metadata information of a data table includes the following fields: table name, database, number of data resources, database version number, database IP address, port, table comments, number of fields, number of records, table size, and last updated time of the table structure. The metadata information of a field includes the field name, field comment, data table, database, data type, field length, field precision, default value, whether it is a primary key, whether it is NOT nullable, whether it is unique, whether it is auto-incrementing, and last update time. The file's metadata information includes the file name, file directory path, file type, file storage location, file size, and data file format.

8. The method for automatic discovery and exploration of multi-source databases as described in claim 6 or 7, characterized in that, S2 includes: performing metadata exploration based on the metadata model, and storing the metadata exploration results into the corresponding tables of the middle platform metadata database according to different metadata models.

9. The method for automatic discovery and exploration of multi-source databases as described in claim 8, characterized in that, In S31, the quality model at the database level includes the number of data tables, the total number of data entries, the total size of data storage, data distribution, data volume increment analysis, table null value analysis, and comment ratio; at the data table level, it includes the number of fields, data volume, data storage size, data increment analysis, field null value analysis, comment ratio, and format analysis of some common fields, including ID card number format check, mobile phone number format check, date format check, and abnormal field check, including abnormal numerical length, abnormal enumeration format, field length too large warning, and missing index warning.

10. The method for automatic discovery and exploration of multi-source databases as described in claim 9, characterized in that, S31 includes: performing database content quality analysis based on the quality model, storing the quality analysis results in the middle platform quality analysis database; and generating a quality analysis report based on metadata and quality exploration results.

Images