CN111752959A - Real-time database cross-database SQL interaction method and system - Google Patents
Real-time database cross-database SQL interaction method and system Download PDFInfo
- Publication number
- CN111752959A CN111752959A CN202010473224.XA CN202010473224A CN111752959A CN 111752959 A CN111752959 A CN 111752959A CN 202010473224 A CN202010473224 A CN 202010473224A CN 111752959 A CN111752959 A CN 111752959A
- Authority
- CN
- China
- Prior art keywords
- sql
- real
- time
- database
- time database
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Granted
Links
- 238000000034 method Methods 0.000 title claims abstract description 27
- 230000003993 interaction Effects 0.000 title claims abstract description 17
- 238000004458 analytical method Methods 0.000 claims abstract description 15
- 230000004044 response Effects 0.000 claims description 9
- 238000004891 communication Methods 0.000 claims description 3
- 230000002452 interceptive effect Effects 0.000 claims 2
- 230000008520 organization Effects 0.000 abstract description 5
- 238000010586 diagram Methods 0.000 description 10
- 238000004590 computer program Methods 0.000 description 7
- 238000010276 construction Methods 0.000 description 6
- 230000006870 function Effects 0.000 description 6
- 238000012545 processing Methods 0.000 description 6
- 238000005516 engineering process Methods 0.000 description 5
- 230000008569 process Effects 0.000 description 4
- 230000002159 abnormal effect Effects 0.000 description 3
- 238000012544 monitoring process Methods 0.000 description 3
- 230000007246 mechanism Effects 0.000 description 2
- 238000012351 Integrated analysis Methods 0.000 description 1
- 238000004364 calculation method Methods 0.000 description 1
- 238000000354 decomposition reaction Methods 0.000 description 1
- 230000000694 effects Effects 0.000 description 1
- 238000004519 manufacturing process Methods 0.000 description 1
- 238000013507 mapping Methods 0.000 description 1
- 238000005259 measurement Methods 0.000 description 1
- 238000012986 modification Methods 0.000 description 1
- 230000004048 modification Effects 0.000 description 1
- 230000003287 optical effect Effects 0.000 description 1
- 238000012546 transfer Methods 0.000 description 1
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/242—Query formulation
- G06F16/2433—Query languages
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/23—Updating
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/245—Query processing
- G06F16/2453—Query optimisation
- G06F16/24534—Query rewriting; Transformation
- G06F16/24539—Query rewriting; Transformation using cached or materialised query results
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/245—Query processing
- G06F16/2453—Query optimisation
- G06F16/24534—Query rewriting; Transformation
- G06F16/24542—Plan optimisation
- G06F16/24544—Join order optimisation
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06Q—INFORMATION AND COMMUNICATION TECHNOLOGY [ICT] SPECIALLY ADAPTED FOR ADMINISTRATIVE, COMMERCIAL, FINANCIAL, MANAGERIAL OR SUPERVISORY PURPOSES; SYSTEMS OR METHODS SPECIALLY ADAPTED FOR ADMINISTRATIVE, COMMERCIAL, FINANCIAL, MANAGERIAL OR SUPERVISORY PURPOSES, NOT OTHERWISE PROVIDED FOR
- G06Q50/00—Information and communication technology [ICT] specially adapted for implementation of business processes of specific business sectors, e.g. utilities or tourism
- G06Q50/06—Energy or water supply
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Physics & Mathematics (AREA)
- General Physics & Mathematics (AREA)
- Data Mining & Analysis (AREA)
- Databases & Information Systems (AREA)
- General Engineering & Computer Science (AREA)
- Business, Economics & Management (AREA)
- Computational Linguistics (AREA)
- Health & Medical Sciences (AREA)
- Economics (AREA)
- Water Supply & Treatment (AREA)
- Operations Research (AREA)
- Public Health (AREA)
- Mathematical Physics (AREA)
- General Health & Medical Sciences (AREA)
- Human Resources & Organizations (AREA)
- Marketing (AREA)
- Primary Health Care (AREA)
- Strategic Management (AREA)
- Tourism & Hospitality (AREA)
- General Business, Economics & Management (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention discloses a cross-database SQL interaction method and a system for a real-time database, wherein the method comprises the following steps: acquiring and updating metadata information of a plurality of real-time databases at set time intervals; responding to an external SQL access request, analyzing the SQL access request to obtain all SQL operations and a real-time database related to the SQL operations; according to the real-time database related to the SQL operation, dividing the SQL operation into single-database SQL execution sequences respectively corresponding to the single real-time database; respectively executing SQL operation in the corresponding single-library SQL execution sequence on each related real-time database to obtain SQL execution result data; constructing a result data set comprising all SQL execution results according to the current metadata information of the corresponding real-time database based on the SQL execution result data; and returning the result data set to the external SQL access requester. The invention can realize rapid multi-real-time library cross-library combined query, improve the data access efficiency of the comprehensive analysis service and reduce the complexity of data organization.
Description
Technical Field
The invention relates to the technical field of real-time database access of a power dispatching automation system, in particular to a cross-database SQL interaction method and a cross-database SQL interaction system based on a container real-time database.
Background
In the field of power monitoring, an application program caches real-time data in a real-time database for fast operation, and a service module usually accesses a specific real-time database through an access interface to acquire required data. However, for a service module of the comprehensive analysis class, it needs to acquire cross-professional field data such as power grid topology information, real-time measurement information, and device state information, and these data are distributed in different real-time databases.
The current real-time database architecture only supports that a service application program links each real-time database to be accessed respectively to acquire data into an application process, and stores the data into a data structure cache in the process for calculation, which causes the problems of low efficiency of accessing multiple databases respectively, complex data organization and the like when a service module of an integrated analysis class acquires and analyzes data.
Disclosure of Invention
The invention aims to provide a cross-database SQL interaction method and a cross-database SQL interaction system for a real-time database, which can realize rapid multi-real-time database cross-database combined query, improve the data access efficiency of comprehensive analysis services and reduce the complexity of data organization.
The technical scheme adopted by the invention is as follows:
on one hand, the invention provides a cross-database SQL interaction method for a real-time database, which comprises the following steps:
acquiring and updating metadata information of a plurality of real-time databases at set time intervals;
responding to an external SQL access request, analyzing the SQL access request to obtain all SQL operations and a real-time database related to the SQL operations;
according to the real-time database related to the SQL operation, dividing the SQL operation into single-database SQL execution sequences respectively corresponding to the single real-time database;
respectively executing SQL operation in the corresponding single-library SQL execution sequence on each related real-time database to obtain SQL execution result data;
constructing a result data set comprising the execution result information of all SQL operations according to the current metadata information of the corresponding real-time database based on the SQL execution result data;
and returning the result data set to the external SQL access requester.
Optionally, the metadata information includes a table structure, a domain data type, and a relationship between tables of the real-time database;
the method further comprises the following steps: establishing a real-time database list, and determining a data updating mark of each real-time database in the real-time database list according to the metadata updating result and the updating time of each real-time database;
obtaining and updating metadata information for a plurality of real-time databases includes:
inquiring data updating marks of all real-time databases in the real-time database list, and linking the real-time databases which do not acquire the metadata information to acquire the metadata information of the corresponding real-time databases;
in response to the unsuccessful acquisition of the metadata information, marking a data update mark of the corresponding real-time database as unacquired metadata;
updating the metadata information of the real-time database to a metadata cache in response to the metadata information being successfully acquired;
responding to the successful updating of the metadata information, updating the data updating state of the corresponding real-time database in the real-time database list to be the acquired metadata, and setting a time mark according to the updating time;
in response to the unsuccessful updating of the metadata information, updating the data updating mark of the corresponding real-time database to be the unacquired metadata;
polling the real-time database list at a set polling interval period, checking whether a real-time database without updating metadata information overtime exists, and if yes, updating a data updating mark of the corresponding real-time database to be the metadata which is not acquired;
the real-time database of the metadata information which is not updated in time-out is as follows: the interval between the update time corresponding to the time mark and the current time is larger than the real-time database of the polling interval period.
Optionally, the analyzing the SQL access request includes:
carrying out SQL syntax analysis to form a semantic tree;
judging whether the SQL access request is a cross-database request related to a plurality of real-time databases according to the semantic tree: if the request is a cross-database request, scanning a semantic tree, classifying SQL operations related to different real-time databases according to real-time database names to obtain single-database SQL operation statements respectively corresponding to the single real-time database; if not, all SQL operations are directly used as single-library SQL operation statements.
The judgment of whether the cross-library request is based on whether SQL operations related to different database names exist in the semantic tree or not.
Optionally, the single-library SQL execution sequence that divides the SQL operation into the single real-time databases respectively is: determining the execution sequence of SQL operations related to the same real-time database according to the logical hierarchical relationship of the semantic tree, and forming single-database SQL execution sequences respectively corresponding to the single real-time database;
respectively executing SQL operations in the corresponding single-library SQL execution sequence on the related real-time database, wherein the SQL operations comprise:
determining SQL operations with dependency relationships according to the semantic tree to form a dependency relationship list;
taking SQL operations without dependency relationship between the SQL operations as the SQL operations which can be parallel to form a single-library SQL parallel task list;
and executing each single-library SQL operation according to the dependency relationship list and the single-library SQL parallel task list to obtain a corresponding SQL execution result.
Optionally, executing each single-library SQL operation according to the dependency relationship list and the single-library SQL parallel task list comprises: instantiating a single-library SQL execution container for each single-library SQL operation which can be executed in parallel, and determining the execution sequence of the corresponding single-library SQL operation according to the dependency relationship list;
and after the single-library SQL operation is executed, the operation of the corresponding single-library SQL execution container is quitted, and the corresponding operation resource is recovered. The dynamic instantiation and exit mechanism of the execution container can realize dynamic allocation of resources, improve the use efficiency of the resources and adapt to the dynamic requirements of a large number of concurrent accesses on the resources.
Optionally, the constructing of the result data set includes:
determining a real-time database and a table to which each domain of the result data set belongs according to the semantic tree, and further acquiring current metadata information of the corresponding real-time database and table to determine a data structure of each domain in the result data set;
responding to the successful execution of all single-library SQL operations in the single-library SQL parallel task list, and selecting a data column required by a result data set from the execution result of each single-library SQL operation;
copying the selected data columns into the result data set according to the data structure of the corresponding domain in the result data set;
and responding to the fact that all the single-base SQL operations in all the single-base SQL parallel task lists are executed and data columns corresponding to the execution results are copied to the result data set, and returning the result data set to the external SQL access requester.
In a second aspect, the present invention further provides a cross-database SQL interaction system, including:
the metadata management module is configured for acquiring and updating metadata information of a plurality of real-time databases at set time intervals;
the SQL engine module is configured for responding to an external SQL access request, analyzing the SQL access request to obtain all SQL operations and the real-time databases related to the SQL operations, and then dividing the SQL operations into single-base SQL execution sequences respectively corresponding to the single real-time databases according to the real-time databases related to the SQL operations;
the single-base SQL execution scheduling module is configured to execute the SQL operation in the corresponding single-base SQL execution sequence on each related real-time database respectively to obtain SQL execution result data, and return the SQL execution result data to the SQL engine module;
and the SQL engine module constructs a result data set comprising the execution result information of all SQL operations according to the current metadata information of the corresponding real-time database in the metadata management module based on the SQL execution result data, and then returns the result data set to the external SQL access requester.
Optionally, the real-time database cross-repository SQL interaction system further includes a single-repository SQL execution container, where the single-repository SQL execution container is generated by the single-repository SQL execution scheduling module according to an instantiation of a parallelizable single-repository SQL operation to be executed in a running memory resource, and exits from running after the execution of the corresponding single-repository SQL operation is completed;
the metadata management modules are provided with a plurality of modules and comprise a main metadata management module and at least one standby metadata management module which are in communication connection with each other, and each metadata management module can be respectively linked with all pre-configured real-time databases to acquire and update metadata information of the corresponding real-time database.
The single-library SQL execution container is realized by using a Docker technology.
Optionally, the metadata management module is configured with a real-time database list, where the real-time database list includes metadata update results and data update flags corresponding to update time of each real-time database;
the metadata management module polls the real-time database list at a set polling interval period, checks whether a real-time database which does not update the metadata information overtime exists, and if yes, updates a data updating mark of the corresponding real-time database to be the unacquired metadata;
for the real-time database which does not acquire the metadata, the metadata management module links the corresponding real-time database to acquire the metadata information, updates the metadata information to the metadata cache after the metadata information is successfully acquired, and updates the data updating mark of the corresponding real-time database in the real-time database list;
the real-time database of the metadata information which is not updated in time-out is as follows: the interval between the updating time corresponding to the time scale and the polling moment is larger than the real-time database of the polling interval period.
Advantageous effects
The invention realizes the cache updating of the metadata of the real-time databases by managing the metadata of the real-time databases, and can support the analysis of cross-database SQL operation and the result construction after the SQL is executed. The method has the advantages that key information such as real-time database names, table names and domain names is extracted from a semantic tree formed by SQL analysis, cross-database SQL decomposition and execution sequence generation are achieved by retrieving cross-database metadata, the problem of cross-database SQL analysis is solved, cross-database operation is conveniently decomposed into single-database operation, operation difficulty is reduced, rapid multi-real-time database cross-database combined query can be achieved, data access efficiency of comprehensive analysis services is improved, data organization complexity is reduced, the problems of low access efficiency of multiple databases, complex data organization and the like are solved, the operation efficiency of comprehensive analysis service modules in an electric power monitoring system can be improved, and support is provided for rapid access and analysis of complex multi-source data.
Meanwhile, the invention realizes the dynamic expansion of the single-library SQL execution container instance by introducing the execution container technology in the single-library SQL execution stage, on one hand, the parallelism of the single-library SQL execution is improved, and the execution efficiency is improved, on the other hand, the resources are dynamically distributed and released once being used up, the use efficiency is improved, the invention can adapt to the dynamic requirements of a large amount of concurrent accesses on the resources, and the overall performance of the system is improved.
Drawings
FIG. 1 is a block diagram of the structure of cross-library SQL interaction for container-based real-time database of the present invention;
FIG. 2 is a cross-library metadata management flow diagram of the present invention;
FIG. 3 is a flow chart of SQL parsing according to the present invention;
FIG. 4 is a flow chart of SQL result construction of the present invention;
FIG. 5 is a flowchart of the single-base SQL execution scheduling of the present invention;
FIG. 6 is a flow chart of a single library execution container of the present invention.
Detailed Description
The following further description is made in conjunction with the accompanying drawings and the specific embodiments.
The technical conception of the invention is as follows: and forming a cross-library metadata cache by managing the real-time library metadata, constructing an SQL engine, splitting the cross-library operation into single-library operation sub-operations, converting and mapping the sub-operation results into final results, and returning the final results to the access party. Meanwhile, query parallelization and elastic expansion are realized by using a Docker technology, and elastic expansion of access processing capacity is realized by distributing single-library sub-operations which can be parallelized to a plurality of Docker containers for operation.
Example 1
The embodiment introduces a cross-database SQL interaction method for a real-time database, which includes:
acquiring and updating metadata information of a plurality of real-time databases at set time intervals;
responding to an external SQL access request, analyzing the SQL access request to obtain all SQL operations and a real-time database related to the SQL operations;
according to the real-time database related to the SQL operation, dividing the SQL operation into single-database SQL execution sequences respectively corresponding to the single real-time database;
respectively executing SQL operation in the corresponding single-library SQL execution sequence on each related real-time database to obtain SQL execution result data;
constructing a result data set comprising the execution result information of all SQL operations according to the current metadata information of the corresponding real-time database based on the SQL execution result data;
and returning the result data set to the external SQL access requester.
Examples 1 to 1
This embodiment specifically describes an implementation manner of embodiment 1.
On the basis of embodiment 1, in this embodiment:
the metadata information comprises a table structure, a domain data type and a relationship among tables of the real-time database.
The method of the invention mainly relates to the following aspects:
cross-database metadata management of real-time database
In order to realize cross-database metadata management, the method needs to construct a real-time database list, and determines a data updating mark of each real-time database in the real-time database list according to the metadata updating result and the updating time of each real-time database.
The embodiment of obtaining and updating metadata information of a plurality of real-time databases includes:
inquiring data updating marks of all real-time databases in the real-time database list, and linking the real-time databases which do not acquire the metadata information to acquire the metadata information of the corresponding real-time databases;
in response to the unsuccessful acquisition of the metadata information, marking a data update mark of the corresponding real-time database as unacquired metadata;
updating the metadata information of the real-time database to a metadata cache in response to the metadata information being successfully acquired;
responding to the successful updating of the metadata information, updating the data updating state of the corresponding real-time database in the real-time database list to be the acquired metadata, and setting a time mark according to the updating time;
in response to the unsuccessful updating of the metadata information, updating the data updating mark of the corresponding real-time database to be the unacquired metadata;
polling the real-time database list at a set polling interval period, checking whether a real-time database without updating metadata information overtime exists, and if yes, updating a data updating mark of the corresponding real-time database to be the metadata which is not acquired;
the real-time database of the metadata information which is not updated in time-out is as follows: the interval between the update time corresponding to the time mark and the current time is larger than the real-time database of the polling interval period.
The specific steps are shown in fig. 2, and include:
step 101: querying a list of real-time databases, and checking the metadata update state of each real-time database;
step 102: judging whether a real-time database of which the metadata is not acquired exists, if so, executing a step 103, and if not, executing a step 104;
step 103: linking the real-time database of which the metadata is not acquired, acquiring the metadata, and executing the step 105;
step 104: step 110 is executed after waiting for a polling interval period;
step 105: judging whether the metadata acquisition is successful, if so, executing step 106, and if not, executing step 107;
step 106: updating the metadata information of the relevant real-time database in the metadata cache, and executing step 108;
step 107: updating the metadata acquisition state of the real-time database in the real-time database list to be 'metadata not acquired', and executing the step 101;
step 108: judging whether the metadata cache is updated successfully or not, if so, executing the step 109, and if not, executing the step 107;
step 109: updating the metadata acquisition status in the real-time database record in the real-time database list to be 'acquired metadata', setting the current time scale in the record, and executing the step 104;
step 110: polling the real-time database list to find out whether a real-time database with the metadata not updated exceeds the polling interval period, wherein the judgment basis of the exceeding of the polling interval period is that the difference between the time scale in the record of the real-time database and the current time is greater than the polling interval period. If the polling interval period is exceeded and has not been updated, step 107 is performed, otherwise step 101 is performed.
The metadata cache in this embodiment is designed as follows:
table 1 real-time database metadata table
TABLE 1
| Domain name | Format | Description of the invention |
| rtdb_id | String (64 bytes) | Real-time database ID |
| rtdb_ name | String (64 bytes) | Real-time database name |
| rtdb_access_url | String (128 bytes) | Real-time database access url |
| rtdb_access_param | String (128 bytes) | Real-time database access parameters |
| rtdb_desc | String (128 bytes) | Real-time library description information |
Table 2 a metadata table of a real-time database table,
| domain name | Format | Description of the invention |
| table_id | String (64 bytes) | Table id |
| table_name | String (64 bytes) | Table name |
| rtdb_id | String (64 bytes) | Real-time database ID to which table belongs |
| table_desc | String (128 bytes) | Table description information |
Table 3 metadata table for real-time database fields:
| domain name | Format | Description of the invention |
| field_id | String (64 bytes) | Domain ID |
| field_name | String (64 bytes) | Domain name |
| field_data_type | Enum | Enumeration of domain data types |
| table_id | String (64 bytes) | Id of the table to which the domain belongs |
| field_ empty | Boolean | Whether a domain can be empty |
| field_is_foreign_key | Boolean | Whether a domain is a foreign key |
Second, SQL access request parsing
The content for analyzing the SQL access request in this embodiment includes:
carrying out SQL syntax analysis to form a semantic tree;
judging whether the SQL access request is a cross-database request related to a plurality of real-time databases according to the semantic tree: if the request is a cross-database request, scanning a semantic tree, classifying SQL operations related to different real-time databases according to real-time database names to obtain single-database SQL operation statements respectively corresponding to the single real-time database; if not, all SQL operations are directly used as single-library SQL operation statements.
The judgment of whether the cross-library request is based on whether SQL operations related to different database names exist in the semantic tree or not.
The single-library SQL execution sequence for dividing the SQL operation into the single real-time databases respectively comprises the following steps: and determining the execution sequence of the SQL operations related to the same real-time database according to the logical hierarchical relation of the semantic tree, and forming single-library SQL execution sequences respectively corresponding to the single real-time database.
The specific steps of SQL parsing are shown in fig. 3, and include:
step 201: the external SQL request is parsed by SQL syntax to form a semantic tree, and step 202 is executed;
step 202: judging whether a cross-library request exists in the semantic tree or not according to whether SQL operation related to different database names exists in the semantic tree or not, if so, executing a step 203, and if not, executing a step 204;
step 203: scanning the semantic tree, splitting the operation related to different real-time databases in the semantic tree into SQL sentences executed by a single database according to database names, and executing the step 205;
step 204: forming a single-base SQL operation statement, wherein the SQL execution sequence is one, namely the single-base SQL operation, and executing the step 206;
step 205: analyzing the execution sequence according to the logical hierarchical relationship of the semantic tree, forming an SQL execution sequence according to the execution sequence, and executing step 206;
step 206: and calling a single-library SQL execution scheduler, and taking an SQL execution sequence as input.
Three, single library SQL execution scheduling
After obtaining the corresponding single-library SQL execution sequence, the scheduling of the corresponding single-library SQL operation can be respectively carried out on the related real-time database, and the scheduling comprises the following steps:
determining SQL operations with dependency relationships according to the semantic tree to form a dependency relationship list;
taking SQL operations without dependency relationship between the SQL operations as the SQL operations which can be parallel to form a single-library SQL parallel task list;
and executing each single-library SQL operation according to the dependency relationship list and the single-library SQL parallel task list to obtain a corresponding SQL execution result.
Executing each single-library SQL operation according to the dependency relationship list and the single-library SQL parallel task list as follows: instantiating a single-library SQL execution container for each single-library SQL operation which can be executed in parallel, and determining the execution sequence of the corresponding single-library SQL operation according to the dependency relationship list;
and after the single-library SQL operation is executed, the operation of the corresponding single-library SQL execution container is quitted, and the corresponding operation resource is recovered. The dynamic instantiation and exit mechanism of the execution container can realize dynamic allocation of resources, improve the use efficiency of the resources and adapt to the dynamic requirements of a large number of concurrent accesses on the resources.
Referring to fig. 5, the specific steps of executing and scheduling the single-library SQL include:
step 401: receiving a single library SQL execution sequence and executing step 402;
step 402: judging whether the execution sequences can be executed in parallel, if so, executing the step 404, and if not, executing the step 403;
step 403: forming a dependency relationship list for the parts with dependency relationship in the execution sequence, and executing step 405;
step 404: forming a single-library SQL parallel task list and executing step 407;
step 405: outputting the dependency relationship list to the SQL engine as input for the SQL result construction, and performing step 406;
step 406: forming a single-library SQL parallel task list by the parts which can be parallel in the execution sequence, and executing step 407;
step 407: forming an independent single-library SQL execution container example for each single-library SQL task according to the single-library SQL parallel task list, and executing the step 408;
step 408: collecting the operation information of the single-library SQL execution container, and executing the step 409;
step 409: judging whether the container instance normally ends the task, if so, executing the step 410, and if not, executing the step 411;
step 410: outputting the single-library SQL execution result to an SQL engine as the input of the SQL result construction;
step 411: forming a new container instance for the container that is abnormal continues with the associated single library SQL operation and performs step 408.
Four, single library SQL execution
The single-library SQL execution container is responsible for executing specific SQL operations of a single real-time database, and returns a result of the single-library SQL operations, and the specific logic steps are as shown in fig. 6, and include:
step 501: initializing a container operating environment, including loading an operating file system directory, a third party class library, an environment parameter, and a single library SQL executive required by SQL execution, and executing step 502;
step 502: linking the real-time database corresponding to the SQL operation to be executed by the single-library SQL executive program, and executing step 503;
step 503: judging whether the operation of linking the real-time database is successful or not, if so, executing a step 504, and if not, executing a step 507;
step 504: the single library SQL executive executes the SQL operation and executes step 505;
step 505: judging whether the operation returns a result or not, judging whether abnormal error output or normal result output exists within a timeout time range according to the judgment, if so, executing a step 506, otherwise, executing a step 507;
step 506: outputting the returned result to the single-base SQL execution scheduler, and executing step 508;
step 507: sending abnormal information which cannot be normally finished to the single-base SQL execution scheduler, and executing step 508;
step 508: and recovering the container operation resources and quitting the container operation.
Fifth, structure of result data set
The construction of the result data set in this embodiment includes:
determining a real-time database and a table to which each domain of the result data set belongs according to the semantic tree, and further acquiring current metadata information of the corresponding real-time database and table to determine a data structure of each domain in the result data set;
responding to the successful execution of all single-library SQL operations in the single-library SQL parallel task list, and selecting a data column required by a result data set from the execution result of each single-library SQL operation;
copying the selected data columns into the result data set according to the data structure of the corresponding domain in the result data set;
and responding to the fact that all the single-base SQL operations in all the single-base SQL parallel task lists are executed and data columns corresponding to the execution results are copied to the result data set, and returning the result data set to the external SQL access requester.
Referring to fig. 4, the specific implementation includes the steps of:
step 301: receiving the single-base SQL execution result returned by the single-base SQL execution scheduler, and executing step 302;
step 302: judging whether the execution of the single-library SQL is successful, if so, executing a step 303, otherwise, executing a step 304;
step 303: judging whether the SQL execution sequence is completely finished, if so, executing the step 305, and if not, executing the step 306;
step 304: returning the error code and the error reason of the SQL caller;
step 305: constructing a result set, analyzing a real-time database and a table of each domain of the result set according to an SQL semantic tree, acquiring data structures of the domains from a cross-database metadata management module, constructing a return result set according to the data structures, and executing step 308;
step 306: judging whether a waiting task is to be executed in the dependency relationship list executed by the SQL, if so, executing a step 307, otherwise, executing a step 301, and waiting and processing a subsequent single-base SQL execution result;
step 307: calling a single-library SQL execution scheduler, continuing to execute subsequent single-library SQL tasks, and executing the step 301;
step 308: selecting data columns needed in the result set from the execution results, and executing step 309;
step 309: judging whether all the columns in the result set find corresponding data columns in the single library execution result, if so, executing the step 310, and if not, executing the step 311;
step 310: copying the data columns in the single library execution result to a result set, and returning the result set to a calling party;
step 311: the caller error code and error reason are returned.
Example 2
This embodiment introduces a cross-database SQL interaction system, as shown in fig. 1, the system includes:
a (cross-database) metadata management module configured to acquire and update metadata information of a plurality of real-time databases at set time intervals;
the SQL engine module is configured for responding to an external SQL access request, analyzing the SQL access request to obtain all SQL operations and the real-time databases related to the SQL operations, and then dividing the SQL operations into single-base SQL execution sequences respectively corresponding to the single real-time databases according to the real-time databases related to the SQL operations;
the single-base SQL execution scheduling module (single-base SQL execution scheduler) is configured for respectively executing the SQL operation in the corresponding single-base SQL execution sequence on each related real-time database to obtain SQL execution result data and returning the SQL execution result data to the SQL engine module;
and the SQL engine module constructs a result data set comprising the execution result information of all SQL operations according to the current metadata information of the corresponding real-time database in the metadata management module based on the SQL execution result data, and then returns the result data set to the external SQL access requester.
The SQL engine can transfer the SQL request statement to an antlr library analysis function as a parameter by calling the antlr library, can analyze the SQL statement into a semantic tree of the SQL request according to SQL syntax rules and return the semantic tree of the SQL request to the SQL engine module to further analyze specific semantics, and then the subsequent SQL engine analyzes database, table and domain information related to SQL operation by traversing the semantic tree to carry out subsequent SQL operation analysis.
The specific implementation of each module refers to the implementation contents of the related functions in embodiment 1 and embodiment 1-1. The SQL engine module is responsible for analyzing the SQL access request, calling the cross-library metadata management module to inquire metadata information, calling the single-library SQL execution scheduling module to start single-library SQL operation execution and constructing a result set. The single-base SQL execution scheduler is responsible for analyzing the single-base SQL execution sequence, distributing the tasks which can be executed in parallel to the single-base SQL execution container for executing in parallel, monitoring the execution process of the container, and returning the single-base SQL execution result to the SQL engine.
In the embodiment of fig. 1, the real-time database cross-repository SQL interaction system further includes a single-repository SQL execution container, which is generated by instantiating the single-repository SQL execution scheduling module in the running memory resource according to the parallelizable single-repository SQL operation to be executed, and exits from running after the execution of the corresponding single-repository SQL operation is completed. The single-library SQL execution container is realized by using a Docker technology.
A real-time database list is configured in the cross-database metadata management module, and the real-time database list comprises metadata updating results and data updating marks of updating time corresponding to each real-time database;
the metadata management module polls the real-time database list at a set polling interval period, checks whether a real-time database which does not update the metadata information overtime exists, and if yes, updates a data updating mark of the corresponding real-time database to be the unacquired metadata;
for the real-time database which does not acquire the metadata, the metadata management module links the corresponding real-time database to acquire the metadata information, updates the metadata information to the metadata cache after the metadata information is successfully acquired, and updates the data updating mark of the corresponding real-time database in the real-time database list;
the judgment basis of the real-time database overtime non-updated metadata information is as follows: and during polling, the interval between the updating time corresponding to the real-time database time scale and the polling time is larger than the polling interval period.
Example 2-1
On the basis of embodiment 2, in the system of this embodiment, a plurality of cross-library metadata management modules are provided, and each cross-library metadata management module includes a main cross-library metadata management module and at least one standby cross-library metadata management module that are in communication connection with each other, and each cross-library metadata management module can link all pre-configured real-time databases to acquire and update corresponding real-time database metadata information. The cross-library metadata management module can be deployed on 3 or 5 nodes, and when the main node fails, one standby node is upgraded to operate as the main node. The active-standby configuration of the cross-database metadata management module can guarantee the reliability of metadata information management and provide support for the accuracy of SQL analysis and result construction.
In the above embodiments, the method realizes a container-based real-time database cross-database SQL interaction scheme, realizes query parallelization and elastic expansion by combining a Docker technology, provides a container-based real-time database cross-database SQL solution, and can provide a powerful guarantee for improving the query efficiency of a real-time database.
As will be appreciated by one skilled in the art, embodiments of the present application may be provided as a method, system, or computer program product. Accordingly, the present application may take the form of an entirely hardware embodiment, an entirely software embodiment or an embodiment combining software and hardware aspects. Furthermore, the present application may take the form of a computer program product embodied on one or more computer-usable storage media (including, but not limited to, disk storage, CD-ROM, optical storage, and the like) having computer-usable program code embodied therein.
The present application is described with reference to flowchart illustrations and/or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of the application. It will be understood that each flow and/or block of the flow diagrams and/or block diagrams, and combinations of flows and/or blocks in the flow diagrams and/or block diagrams, can be implemented by computer program instructions. These computer program instructions may be provided to a processor of a general purpose computer, special purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, create means for implementing the functions specified in the flowchart flow or flows and/or block diagram block or blocks.
These computer program instructions may also be stored in a computer-readable memory that can direct a computer or other programmable data processing apparatus to function in a particular manner, such that the instructions stored in the computer-readable memory produce an article of manufacture including instruction means which implement the function specified in the flowchart flow or flows and/or block diagram block or blocks.
These computer program instructions may also be loaded onto a computer or other programmable data processing apparatus to cause a series of operational steps to be performed on the computer or other programmable apparatus to produce a computer implemented process such that the instructions which execute on the computer or other programmable apparatus provide steps for implementing the functions specified in the flowchart flow or flows and/or block diagram block or blocks.
While the present invention has been described with reference to the embodiments shown in the drawings, the present invention is not limited to the embodiments, which are illustrative and not restrictive, and it will be apparent to those skilled in the art that various changes and modifications can be made therein without departing from the spirit and scope of the invention as defined in the appended claims.
Claims (10)
1. A cross-database SQL interaction method for a real-time database is characterized by comprising the following steps:
acquiring and updating metadata information of a plurality of real-time databases at set time intervals;
responding to an external SQL access request, analyzing the SQL access request to obtain all SQL operations and a real-time database related to the SQL operations;
according to the real-time database related to the SQL operation, dividing the SQL operation into single-database SQL execution sequences respectively corresponding to the single real-time database;
respectively executing SQL operation in the corresponding single-library SQL execution sequence on each related real-time database to obtain SQL execution result data;
constructing a result data set comprising the execution result information of all SQL operations according to the current metadata information of the corresponding real-time database based on the SQL execution result data;
and returning the result data set to the external SQL access requester.
2. The method of claim 1, wherein the metadata information includes table structure, domain data type, and inter-table relationships of the real-time database.
3. The method according to claim 1 or 2, characterized in that the method further comprises: establishing a real-time database list, and determining a data updating mark of each real-time database in the real-time database list according to the metadata updating result and the updating time of each real-time database;
obtaining and updating metadata information for a plurality of real-time databases includes:
inquiring data updating marks of all real-time databases in the real-time database list, and linking the real-time databases which do not acquire the metadata information to acquire the metadata information of the corresponding real-time databases;
in response to the unsuccessful acquisition of the metadata information, marking a data update mark of the corresponding real-time database as unacquired metadata;
updating the metadata information of the real-time database to a metadata cache in response to the metadata information being successfully acquired;
responding to the successful updating of the metadata information, updating the data updating state of the corresponding real-time database in the real-time database list to be the acquired metadata, and setting a time mark according to the updating time;
in response to the unsuccessful updating of the metadata information, updating the data updating mark of the corresponding real-time database to be the unacquired metadata;
polling the real-time database list at a set polling interval period, checking whether a real-time database without updating metadata information overtime exists, and if yes, updating a data updating mark of the corresponding real-time database to be the metadata which is not acquired;
the real-time database of the metadata information which is not updated in time-out is as follows: the interval between the update time corresponding to the time mark and the current time is larger than the real-time database of the polling interval period.
4. The method of claim 1 or 2, wherein parsing the SQL access request comprises:
carrying out SQL syntax analysis to form a semantic tree;
judging whether the SQL access request is a cross-database request related to a plurality of real-time databases according to the semantic tree: if the request is a cross-database request, scanning a semantic tree, classifying SQL operations related to different real-time databases according to real-time database names to obtain single-database SQL operation statements respectively corresponding to the single real-time database; if not, all SQL operations are directly used as single-library SQL operation statements.
5. The method of claim 4, wherein the splitting of SQL operations into single-library SQL execution sequences that respectively correspond to a single real-time database is: determining the execution sequence of SQL operations related to the same real-time database according to the logical hierarchical relationship of the semantic tree, and forming single-database SQL execution sequences respectively corresponding to the single real-time database;
respectively executing SQL operations in the corresponding single-library SQL execution sequence on the related real-time database, wherein the SQL operations comprise:
determining SQL operations with dependency relationships according to the semantic tree to form a dependency relationship list;
taking SQL operations without dependency relationship between the SQL operations as the SQL operations which can be parallel to form a single-library SQL parallel task list;
and executing each single-library SQL operation according to the dependency relationship list and the single-library SQL parallel task list to obtain a corresponding SQL execution result.
6. The method of claim 5, wherein performing each single-library SQL operation according to the dependency list and the single-library SQL parallel task list is: instantiating a single-library SQL execution container for each single-library SQL operation which can be executed in parallel, and determining the execution sequence of the corresponding single-library SQL operation according to the dependency relationship list;
and after the single-library SQL operation is executed, the operation of the corresponding single-library SQL execution container is quitted, and the corresponding operation resource is recovered.
7. The method of claim 4, wherein the constructing of the result dataset comprises:
determining a real-time database and a table to which each domain of the result data set belongs according to the semantic tree, and further acquiring current metadata information of the corresponding real-time database and table to determine a data structure of each domain in the result data set;
responding to the successful execution of all single-library SQL operations in the single-library SQL parallel task list, and selecting a data column required by a result data set from the execution result of each single-library SQL operation;
copying the selected data columns into the result data set according to the data structure of the corresponding domain in the result data set;
and responding to the fact that all the single-base SQL operations in all the single-base SQL parallel task lists are executed and data columns corresponding to the execution results are copied to the result data set, and returning the result data set to the external SQL access requester.
8. A cross-database SQL interactive system for a real-time database is characterized by comprising the following steps:
the metadata management module is configured for acquiring and updating metadata information of a plurality of real-time databases at set time intervals;
the SQL engine module is configured for responding to an external SQL access request, analyzing the SQL access request to obtain all SQL operations and the real-time databases related to the SQL operations, and then dividing the SQL operations into single-base SQL execution sequences respectively corresponding to the single real-time databases according to the real-time databases related to the SQL operations;
the single-base SQL execution scheduling module is configured to execute the SQL operation in the corresponding single-base SQL execution sequence on each related real-time database respectively to obtain SQL execution result data, and return the SQL execution result data to the SQL engine module;
and the SQL engine module constructs a result data set comprising the execution result information of all SQL operations according to the current metadata information of the corresponding real-time database in the metadata management module based on the SQL execution result data, and then returns the result data set to the external SQL access requester.
9. The cross-library SQL interactive system of the real-time database according to claim 8, further comprising a single-library SQL execution container, wherein the single-library SQL execution container is generated by instantiating the single-library SQL execution scheduling module in the running memory resource according to the parallelizable single-library SQL operation to be executed, and exits from running after the execution of the corresponding single-library SQL operation is finished;
the metadata management modules are provided with a plurality of modules and comprise a main metadata management module and at least one standby metadata management module which are in communication connection with each other, and each metadata management module can be respectively linked with all pre-configured real-time databases to acquire and update metadata information of the corresponding real-time database.
10. The cross-database SQL interaction system of the real-time database according to claim 8 or 9, wherein the metadata management module is configured with a real-time database list, and the real-time database list includes a data update flag corresponding to the metadata update result and the update time of each real-time database;
the metadata management module polls the real-time database list at a set polling interval period, checks whether a real-time database which does not update the metadata information overtime exists, and if yes, updates a data updating mark of the corresponding real-time database to be the unacquired metadata;
for the real-time database which does not acquire the metadata, the metadata management module links the corresponding real-time database to acquire the metadata information, updates the metadata information to the metadata cache after the metadata information is successfully acquired, and updates the data updating mark of the corresponding real-time database in the real-time database list;
the real-time database of the metadata information which is not updated in time-out is as follows: the interval between the updating time corresponding to the time scale and the polling moment is larger than the real-time database of the polling interval period.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202010473224.XA CN111752959B (en) | 2020-05-29 | 2020-05-29 | Real-time database cross-database SQL interaction method and system |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202010473224.XA CN111752959B (en) | 2020-05-29 | 2020-05-29 | Real-time database cross-database SQL interaction method and system |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN111752959A true CN111752959A (en) | 2020-10-09 |
| CN111752959B CN111752959B (en) | 2022-07-22 |
Family
ID=72673726
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202010473224.XA Active CN111752959B (en) | 2020-05-29 | 2020-05-29 | Real-time database cross-database SQL interaction method and system |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN111752959B (en) |
Cited By (7)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN112328455A (en) * | 2020-12-02 | 2021-02-05 | 国泰君安证券股份有限公司 | System for realizing general service monitoring based on database in computer software system |
| CN112328624A (en) * | 2020-11-06 | 2021-02-05 | 瀚高基础软件股份有限公司 | SQL forwarding method and device and readable storage medium |
| CN113515564A (en) * | 2021-05-18 | 2021-10-19 | 平安国际智慧城市科技股份有限公司 | Data access method, device, equipment and storage medium based on J2EE |
| CN114124477A (en) * | 2021-11-05 | 2022-03-01 | 深圳市联软科技股份有限公司 | Business service system and method |
| CN114676291A (en) * | 2022-03-30 | 2022-06-28 | 苏州浪潮智能科技有限公司 | Database system and control method thereof |
| CN114756569A (en) * | 2022-03-25 | 2022-07-15 | 北京友友天宇系统技术有限公司 | Multi-layer parsing method of structured query statement, computer device and storage medium |
| CN116595232A (en) * | 2023-05-24 | 2023-08-15 | 杭州金智塔科技有限公司 | Cross-data-source data processing system, method and device |
-
2020
- 2020-05-29 CN CN202010473224.XA patent/CN111752959B/en active Active
Cited By (11)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN112328624A (en) * | 2020-11-06 | 2021-02-05 | 瀚高基础软件股份有限公司 | SQL forwarding method and device and readable storage medium |
| CN112328624B (en) * | 2020-11-06 | 2022-07-01 | 瀚高基础软件股份有限公司 | SQL forwarding method and device and readable storage medium |
| CN112328455A (en) * | 2020-12-02 | 2021-02-05 | 国泰君安证券股份有限公司 | System for realizing general service monitoring based on database in computer software system |
| CN113515564A (en) * | 2021-05-18 | 2021-10-19 | 平安国际智慧城市科技股份有限公司 | Data access method, device, equipment and storage medium based on J2EE |
| CN114124477A (en) * | 2021-11-05 | 2022-03-01 | 深圳市联软科技股份有限公司 | Business service system and method |
| CN114124477B (en) * | 2021-11-05 | 2024-04-05 | 深圳市联软科技股份有限公司 | Business service system and method |
| CN114756569A (en) * | 2022-03-25 | 2022-07-15 | 北京友友天宇系统技术有限公司 | Multi-layer parsing method of structured query statement, computer device and storage medium |
| CN114756569B (en) * | 2022-03-25 | 2024-09-20 | 北京友友天宇系统技术有限公司 | Multi-layer parsing method for structured query statement, computer equipment and storage medium |
| CN114676291A (en) * | 2022-03-30 | 2022-06-28 | 苏州浪潮智能科技有限公司 | Database system and control method thereof |
| CN114676291B (en) * | 2022-03-30 | 2024-01-09 | 苏州浪潮智能科技有限公司 | Database system and control method thereof |
| CN116595232A (en) * | 2023-05-24 | 2023-08-15 | 杭州金智塔科技有限公司 | Cross-data-source data processing system, method and device |
Also Published As
| Publication number | Publication date |
|---|---|
| CN111752959B (en) | 2022-07-22 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN111752959B (en) | Real-time database cross-database SQL interaction method and system | |
| US11200229B2 (en) | Expansion of a tree hierarchy | |
| Zhou et al. | SCOPE: parallel databases meet MapReduce | |
| WO2020238597A1 (en) | Hadoop-based data updating method, device, system and medium | |
| EP2831767B1 (en) | Method and system for processing data queries | |
| CN111061788B (en) | Multi-source heterogeneous data conversion integration system based on cloud architecture and implementation method thereof | |
| CN107038222B (en) | Database cache implementation method and system | |
| CN103631870B (en) | System and method used for large-scale distributed data processing | |
| CN109491989B (en) | Data processing method and device, electronic equipment and storage medium | |
| US20130138626A1 (en) | Table Parameterized Functions in Database | |
| CN111324610A (en) | Data synchronization method and device | |
| US11593357B2 (en) | Databases and methods of storing, retrieving, and processing data | |
| US20070250517A1 (en) | Method and Apparatus for Autonomically Maintaining Latent Auxiliary Database Structures for Use in Executing Database Queries | |
| US8862637B2 (en) | Generating data access operations based on a data model using a data services model | |
| CN113515564B (en) | J2 EE-based data access method, device, equipment and storage medium | |
| CN111752945A (en) | Time sequence database data interaction method and system based on container and hierarchical model | |
| EP4155965A1 (en) | System and method for facilitating metadata identification and import | |
| CN113886111B (en) | Workflow-based data analysis model calculation engine system and operation method | |
| Zou et al. | From a stream of relational queries to distributed stream processing | |
| Yuan et al. | VDB-MR: MapReduce-based distributed data integration using virtual database | |
| CN110245184B (en) | Data processing method, system and device based on tagSQL | |
| CN117131230A (en) | Data blood edge analysis method, device, equipment and storage medium | |
| EP4155968A1 (en) | Identification and import of metadata for extensions to database artefacts | |
| WO2024016594A1 (en) | Pseudo column implementation method and apparatus, electronic device, and storage medium | |
| CN114461454A (en) | Data recovery method and device, storage medium and electronic equipment |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |