CN108255852B - SQL execution method and device - Google Patents
SQL execution method and device Download PDFInfo
- Publication number
- CN108255852B CN108255852B CN201611244308.6A CN201611244308A CN108255852B CN 108255852 B CN108255852 B CN 108255852B CN 201611244308 A CN201611244308 A CN 201611244308A CN 108255852 B CN108255852 B CN 108255852B
- Authority
- CN
- China
- Prior art keywords
- sql
- processed
- distributed memory
- memory database
- type
- 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.)
- Active
Links
- 238000000034 method Methods 0.000 title claims abstract description 92
- 238000007781 pre-processing Methods 0.000 claims abstract description 24
- 230000008569 process Effects 0.000 claims description 43
- 238000012549 training Methods 0.000 claims description 38
- 238000012545 processing Methods 0.000 claims description 23
- 238000009826 distribution Methods 0.000 claims description 22
- 238000011161 development Methods 0.000 abstract description 12
- 238000012423 maintenance Methods 0.000 abstract description 8
- 238000010586 diagram Methods 0.000 description 12
- 238000004590 computer program Methods 0.000 description 11
- 238000003860 storage Methods 0.000 description 8
- 230000006870 function Effects 0.000 description 5
- 238000004458 analytical method Methods 0.000 description 4
- 238000005457 optimization Methods 0.000 description 4
- 230000009471 action Effects 0.000 description 3
- 238000013508 migration Methods 0.000 description 3
- 230000005012 migration Effects 0.000 description 3
- 238000002360 preparation method Methods 0.000 description 3
- 238000004364 calculation method Methods 0.000 description 2
- 238000004891 communication Methods 0.000 description 2
- 238000005516 engineering process Methods 0.000 description 2
- 238000012986 modification Methods 0.000 description 2
- 230000004048 modification Effects 0.000 description 2
- 238000013515 script Methods 0.000 description 2
- 238000012360 testing method Methods 0.000 description 2
- WVCHIGAIXREVNS-UHFFFAOYSA-N 2-hydroxy-1,4-naphthoquinone Chemical compound C1=CC=C2C(O)=CC(=O)C(=O)C2=C1 WVCHIGAIXREVNS-UHFFFAOYSA-N 0.000 description 1
- 230000009286 beneficial effect Effects 0.000 description 1
- 238000000354 decomposition reaction Methods 0.000 description 1
- 230000007547 defect Effects 0.000 description 1
- 230000000694 effects Effects 0.000 description 1
- 238000001914 filtration Methods 0.000 description 1
- 230000003993 interaction Effects 0.000 description 1
- 238000004519 manufacturing process Methods 0.000 description 1
- 238000013507 mapping Methods 0.000 description 1
- 230000003287 optical effect Effects 0.000 description 1
- 230000010076 replication Effects 0.000 description 1
- 238000011160 research Methods 0.000 description 1
- 238000000638 solvent extraction Methods 0.000 description 1
- 239000000126 substance Substances 0.000 description 1
- 238000006467 substitution reaction 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/27—Replication, distribution or synchronisation of data between databases or within a distributed database system; Distributed database system architectures therefor
-
- 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
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/30—Information retrieval; Database structures therefor; File system structures therefor of unstructured textual data
- G06F16/35—Clustering; Classification
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F2216/00—Indexing scheme relating to additional aspects of information retrieval not explicitly covered by G06F16/00 and subgroups
- G06F2216/03—Data mining
Abstract
The invention discloses an SQL (structured query language) execution method and an SQL execution device, which can solve the problems of high product learning difficulty, high development, operation and maintenance difficulty and high project management difficulty and high cost caused by different SQL support standards in the prior art and realize the unified development of different SQL type distributed memory databases. The method comprises the following steps: acquiring a business SQL request submitted by a client, wherein the business SQL request comprises SQL to be processed; classifying the SQL to be processed by utilizing a trained classifier, and if the SQL to be processed does not belong to a pre-divided SQL type, preprocessing the SQL to be processed to obtain standard SQL; and submitting the standard SQL to a corresponding SQL type distributed memory database for execution, acquiring an execution result after the SQL type distributed memory database is executed, and feeding back the execution result to the client, wherein the pre-divided SQL types comprise the SQL type of the lowest SQL standard commonly supported by all the SQL type distributed memory databases.
Description
Technical Field
The invention relates to the field of business support, in particular to a method and a device for executing SQL.
Background
The memory database technology greatly improves the processing capacity of the database system by directly storing the contents of the database in the memory for processing and calculation. At present, the memory database is widely used in the aspects of real-time charging, real-time credit and big data application of the communication industry, and the types of products are various, such as: redis, eXtreeDB, HANA, Timestan, BerkeleyDB, FastDB, GemFireXD, VoltDB, MemSQL, SQLite, GBase8m, GigaBASE, etc., but most businesses select SQL type distributed memory databases to handle relatively complex OLTP or OLAP businesses. When a user submits a database in a common SQL writing mode, some memory databases have high execution efficiency, some memory databases have low execution efficiency, and some scripts cannot be executed at all. The reason why the same SQL cannot be executed or is different in execution efficiency is that the appropriate SQL is not written according to the characteristics of the corresponding product. For developers, each new project may need to learn a new product technology, research a new development specification and increase learning cost; for the DBA, the operation and maintenance requirements of different products are different, the script optimization mode is different, the cross-platform migration project cannot be realized at all, and the operation and maintenance difficulty is very high; in the project management perspective, the whole project needs great investment from development to online interaction, and the cost and the difficulty of the project management are increased a lot.
Currently, both open-source and commercial versions of in-memory database products are largely classified into centralized and distributed in-memory databases. Centralized computing is mostly applied to OLTP scenarios, while distributed computing is mostly applied to OLAP scenarios or OLTP scenarios. Because the real-time memory database products are not mature, the SQL support degrees of the respective products are different, and no scheme can solve the problems.
Disclosure of Invention
Aiming at the defects and shortcomings of the prior art, the invention provides an SQL execution method and device, which are used for realizing the unified development of different SQL type distributed memory databases.
In one aspect, an embodiment of the present invention provides an SQL executing method, including:
acquiring a business SQL request submitted by a client, wherein the business SQL request comprises SQL to be processed;
classifying the SQL to be processed by utilizing a trained classifier, and if the SQL to be processed does not belong to a pre-divided SQL type, preprocessing the SQL to be processed to obtain standard SQL;
and submitting the standard SQL to a corresponding SQL type distributed memory database for execution, acquiring an execution result after the SQL type distributed memory database is executed, and feeding back the execution result to the client, wherein the pre-divided SQL types comprise the SQL type of the lowest SQL standard commonly supported by all the SQL type distributed memory databases.
Optionally, the preprocessing the SQL to be processed includes:
analyzing the SQL to be processed to obtain SQL attributes, wherein the SQL attributes comprise an SQL type, an operation object, operation object related attributes and/or object processing requirements;
determining a key condition corresponding to the SQL attribute according to the SQL attribute, judging whether the SQL to be processed meets the key condition or not, and editing the SQL to be processed according to a judgment result to enable the SQL to be processed to meet the SQL standard of the SQL type distributed memory database, wherein the key condition is a limiting condition related to standard SQL corresponding to the SQL attribute in the SQL type distributed memory database;
wherein, the submitting the standard SQL to a corresponding SQL type distributed memory database for execution comprises:
and submitting the edited SQL to be processed to the SQL type distributed memory database for execution.
Optionally, the method further comprises:
recording the editing process of the SQL to be processed to obtain editing process information, retraining the classifier by using the SQL attribute and a training sample used for training the classifier for the first time, and storing the editing process information and a corresponding SQL type, wherein the corresponding SQL type is a newly-added type in the SQL types which can be effectively identified by the classifier after retraining the classifier, and the training sample used for training the classifier for the first time comprises SOL which is not supported by the SQL type distributed memory database.
Optionally, if the SQL-type distributed memory database is GemFireXD, the SQL type is DQL, and the operation object is two tables, the relevant attributes of the operation object include: the method comprises the following steps of distributing attributes, distributing field attributes and specifying associated object table attributes, wherein the key conditions comprise: the two tables are both distribution tables, the distribution fields of the two tables are consistent, and the table building statement of one table in the two tables contains a colocate with keyword;
wherein, according to the judgment result, editing the SQL to be processed comprises:
if the two tables are both distributed tables, the distribution fields of the two tables are consistent, and the table building sentences of the two tables do not contain a colocate with key word, a temporary table of a smaller table of the two tables is created, wherein the table building sentences of the temporary table contain the colocate with key word for appointing the larger table of the two tables as an associated object table;
and importing the data of the smaller table into the temporary table, and replacing the name of the smaller table in the SQL to be processed with the name of the temporary table.
Optionally, the table building statement of the temporary table further includes an SQL statement for specifying a release time of the temporary table;
after acquiring other SQL types which are DQL and operation objects of which are a first data table and a second data table and are submitted by a client connected with an SQL type distributed memory database GemFireXD, editing the SQL to be processed according to a judgment result, wherein the method comprises the following steps:
if the temporary table is not released, replacing the name of the first data table in the other SQL to be processed with the name of the temporary table, wherein the first data table and the second data table are both distribution tables, the distribution fields of the first data table and the second data table are consistent, the table building sentences of the first data table and the second data table do not contain a coordinate with keyword, the first data table is the smaller table, and the first data table is the smaller table in the first data table and the second data table.
Optionally, if the obtained other to-be-processed SQL submitted by the client belongs to an SQL type corresponding to the editing process information, where the SQL type corresponding to the editing process information includes an SQL type to which SOL not supported by the SQL type distributed memory database belongs,
the preprocessing the SQL to be processed comprises the following steps:
and editing the other SQL to be processed according to the editing process information corresponding to the SQL type to which the SQL belongs.
Optionally, the method further comprises:
and if the SQL to be processed belongs to the standard SQL type supported by the corresponding SQL type distributed memory database, directly submitting the SQL to be processed to the corresponding SQL type distributed memory database for execution.
On the other hand, an embodiment of the present invention provides an SQL executing apparatus, including:
the system comprises an acquisition unit, a processing unit and a processing unit, wherein the acquisition unit is used for acquiring a business SQL request submitted by a client, and the business SQL request comprises SQL to be processed;
the preprocessing unit is used for classifying the SQL to be processed by utilizing the trained classifier, and if the SQL to be processed does not belong to the pre-divided SQL type, preprocessing the SQL to be processed to obtain standard SQL;
and the feedback unit is used for submitting the standard SQL to the corresponding SQL type distributed memory database for execution, acquiring an execution result after the SQL type distributed memory database is executed, and feeding back the execution result to the client, wherein the pre-divided SQL types comprise the SQL type of the lowest SQL standard commonly supported by all the SQL type distributed memory databases.
Optionally, the preprocessing unit is specifically configured to:
analyzing the SQL to be processed to obtain SQL attributes, wherein the SQL attributes comprise an SQL type, an operation object, operation object related attributes and/or object processing requirements;
determining a key condition corresponding to the SQL attribute according to the SQL attribute, judging whether the SQL to be processed meets the key condition or not, and editing the SQL to be processed according to a judgment result to enable the SQL to be processed to meet the SQL standard of the SQL type distributed memory database, wherein the key condition is a limiting condition related to standard SQL corresponding to the SQL attribute in the SQL type distributed memory database;
wherein, the feedback unit is specifically configured to:
and submitting the edited SQL to be processed to the SQL type distributed memory database for execution.
Optionally, the apparatus further comprises:
the training unit is used for recording the editing process of the SQL to be processed to obtain editing process information, retraining the classifier by using the SQL attributes and the training samples used for the first training of the classifier, and storing the editing process information and the corresponding SQL types, wherein the corresponding SQL types are newly-increased types in the SQL types which can be effectively identified by the classifier after the classifier is retrained, and the training samples used for the first training of the classifier comprise SOL which is not supported by the SQL type distributed memory database.
According to the SQL execution method and device provided by the embodiment of the invention, for the SQL to be processed which belongs to the standard SQL type supported by the corresponding SQL type distributed memory database, the SQL to be processed can be directly submitted to the corresponding SQL type distributed memory database for execution; for the SQL to be processed which can be effectively identified and classified by the classifier and does not belong to the standard SQL type supported by the corresponding SQL type distributed memory database, the SQL to be processed can be edited according to the editing process information corresponding to the SQL type to which the SQL belongs, and the edited SQL to be processed is submitted to the corresponding SQL type distributed memory database for execution; the SQL to be processed can be preprocessed to obtain standard SQL, and the standard SQL is submitted to a corresponding SQL type distributed memory database for execution, when the SQL to be processed cannot be effectively recognized by a classifier, the classifier can be used for training the classifier by utilizing the whole scheme except for preprocessing the SQL, so that the classifier can classify more types of SQL sentences, different businesses can still be executed after being migrated to any SQL type distributed memory database platform, and the problem that cross-platform SQL migration cannot be realized due to the fact that SQL supporting standards among different SQL type distributed memory databases are not uniform in the prior art is solved; by implementing the scheme, developers only need to develop according to the standard SQL, and only need to normally test, line and exchange the dimension after development, and operation and maintenance personnel only need to consider the problems of standard SQL optimization and capacity management, so that the problems of large product learning difficulty, large development, operation and maintenance and project management difficulty, high cost and the like caused by different SQL support standards in the prior art are solved, and the unified development of different SQL type distributed memory databases is realized; in addition, the setting based on the release time can reuse the already-established data table without re-establishment, and thus can appropriately reduce resource consumption.
Drawings
FIG. 1 is a schematic flow chart of an embodiment of an SQL execution method according to the invention;
FIG. 2 is a partial flow diagram of another embodiment of the SQL execution method of the invention;
FIG. 3 is a flow chart of a naive Bayes classification;
FIG. 4 is a schematic structural diagram of an SQL execution device according to an embodiment of the invention;
fig. 5 is a schematic physical structure diagram of an electronic device according to an embodiment of the invention.
Detailed Description
In order to make the objects, technical solutions and advantages of the embodiments of the present invention clearer, the technical solutions in the embodiments of the present invention will be clearly described below with reference to the drawings in the embodiments of the present invention, and it is obvious that the described embodiments are some embodiments, but not all embodiments, of the present invention. All other embodiments, which can be derived by a person skilled in the art from the embodiments given herein without making any creative effort, shall fall within the protection scope of the present invention.
Referring to fig. 1, the present embodiment discloses an SQL executing method, which includes:
s1, acquiring a business SQL request submitted by a client, wherein the business SQL request comprises SQL to be processed;
s2, classifying the SQL to be processed by using the trained classifier, and if the SQL to be processed does not belong to the pre-divided SQL types, preprocessing the SQL to be processed to obtain standard SQL;
and S3, submitting the standard SQL to a corresponding SQL type distributed memory database for execution, and after the SQL type distributed memory database is executed, acquiring an execution result and feeding back the execution result to the client, wherein the pre-divided SQL types comprise the SQL type of the lowest SQL standard supported by all the SQL type distributed memory databases.
It should be noted that the standard SQL type is based on the lowest SQL standard (such as SQL92, SQL99, etc.) type commonly supported by all SQL type distributed memory databases incorporated by the platform, and is a new SQL type that can be effectively identified by the classifier after the initial training of the classifier. And if the SQL to be processed belongs to the standard SQL type supported by the corresponding SQL type distributed memory database, directly submitting the SQL to be processed to the corresponding SQL type distributed memory database for execution.
The SQL execution method provided by the embodiment of the invention comprises the steps of firstly obtaining a business SQL request submitted by a client; then, classifying SQL to be processed contained in the business SQL request by using a trained classifier, and if the SQL to be processed does not belong to a pre-divided SQL type, preprocessing the SQL to be processed to obtain standard SQL; and finally submitting the standard SQL to a corresponding SQL type distributed memory database for execution, acquiring an execution result after the SQL type distributed memory database is executed, and feeding back the execution result to the client.
On the basis of the foregoing method embodiment, the preprocessing the SQL to be processed may include:
analyzing the SQL to be processed to obtain SQL attributes, wherein the SQL attributes comprise an SQL type, an operation object, operation object related attributes and/or object processing requirements;
determining a key condition corresponding to the SQL attribute according to the SQL attribute, judging whether the SQL to be processed meets the key condition or not, and editing the SQL to be processed according to a judgment result to enable the SQL to be processed to meet the SQL standard of the SQL type distributed memory database, wherein the key condition is a limiting condition related to standard SQL corresponding to the SQL attribute in the SQL type distributed memory database;
wherein, submitting the standard SQL to a corresponding SQL type distributed memory database for execution may include:
and submitting the edited SQL to be processed to the SQL type distributed memory database for execution.
In this embodiment, for example, in the case of common two-table association query, both general databases support, but the GemFireXD requires many limiting conditions, otherwise it cannot execute, and needs to optimize or reconstruct the corresponding data table in a targeted manner (for example, modify the replication table into the distribution table, specify the corresponding field coordinate with attribute, etc.). For the situation that a coordinate with attribute needs to be specified, this embodiment will automatically create a temporary table that the SQL statement submitted by the service can be directly applied to replace the original table for statistical calculation, and the following query by using two distribution tables in the distributed memory database GemFireXD needs to be specially set as an example description, a specific processing procedure of the SQL preprocessing procedure. As shown in fig. 2, the SQL preprocessing flow of the present invention is disclosed, and the specific processing procedure is as follows:
the SQL to be processed obtained in step 1 is selected a.city _ id, sum (USER _ prepare) from USER _ lacci a, USER _ info b where a.user _ id is b.user _ id group by a.city _ id;
1) SQL type: DDL, DML, DCL, DQL, etc., the SQL type to be processed obtained in step 1 is DQL;
2) the operation object is as follows: the SQL to be processed obtained in the step 1 relates to two tables, namely user _ lacci (abbreviated as A table) and user _ info (abbreviated as B table);
3) operation object related attributes: because the database object attributes are more, after filtering, targeted analysis is required, if the SQL to be processed acquired in step 1 involves 2 tables of associated query (i.e. the SQL type is DQL, and the operation object is two tables), only the distribution attribute, the distribution field attribute, and the colocate with attribute of the 2 tables need to be acquired, i.e. the distribution attribute, the distribution field attribute, and the colocate with attribute are the operation object related attributes under the condition;
4) object handling requirements: according to the analysis of the keywords (including select, sum and group by) of the SQL to be processed obtained in step 1, it can be obtained that the SQL needs to be grouped for summation query;
6-7, performing intermediate process treatment according to an analysis result: for the situation shown in fig. 2, the table building statement needs to be modified, a temporary table needs to be created, data is imported, SQL is modified, and SQL is submitted, and the specific process is as follows: creating a smaller table in the A table and the B table, namely a temporary table tmp _ user _ lacci of the A table, and designating the B table as an associated object table by using a colocate with a keyword in a table building statement; importing the data of the A table into a temporary table tmp _ user _ lacci, and replacing the name of the A table in the SQL to be processed, which is obtained in the step 1, with the name tmp _ user _ lacci of the temporary table tmp _ user _ lacci; submitting the modified SQL to be processed to an SQL type distributed memory database GemFireXD;
it should be noted that, because the temporary table is usually only used for the current SQL request operation, the system will automatically recycle the corresponding resource within the specified time after the operation is completed, and for the case shown in fig. 2, the release time is set in the table creation statement of the temporary table tmp _ user _ lacci. Therefore, when the requests of a certain type of pseudo standard SQL (the other SQL types submitted by a client connected with the SQL type distributed memory database GemFireXD are DQL and the operation objects are other SQL to be processed of two tables) are very frequent (the next request is received within the set release time), and a temporary table with the same content as the temporary table tmp _ user _ lacci is created according to the normal processing flow, the temporary table can be directly used without being re-established, and the resource consumption is further reduced.
And 8, waiting for the SQL to be processed after the SQL type distributed memory database executes the modification, acquiring an execution result after the execution is finished, and feeding the execution result back to the client.
For example, when a complicated SQL query is made, the storage process needs to be converted into the storage process in the VoltDB for execution, and the storage process corresponding to the complicated query SQL submitted by the service is automatically created and then submitted to the database for execution.
On the basis of the foregoing method embodiment, the method may further include the following steps not shown in the figure:
recording the editing process of the SQL to be processed to obtain editing process information, retraining the classifier by using the SQL attribute and a training sample used for training the classifier for the first time, and storing the editing process information and a corresponding SQL type, wherein the corresponding SQL type is a newly-added type in the SQL types which can be effectively identified by the classifier after retraining the classifier, and the training sample used for training the classifier for the first time comprises SOL which is not supported by the SQL type distributed memory database.
In this embodiment, it should be noted that the initial classifier can identify the lowest SQL standard type commonly supported by all SQL-type distributed memory databases, and a new SQL type which can be effectively identified by the classifier after the initial training of the classifier (the new SQL type is the SQL type to which SOL which is not supported by the corresponding SQL type distributed memory database belongs), during the process of processing the SQL to be processed submitted by the client, if the SQL to be processed can not be effectively identified and classified by the classifier, that is, the SQL to be processed is a new type of SQL, and besides the SQL to be processed is preprocessed, the SQL attribute of the SQL to be processed is also utilized, and retraining the classifier in conjunction with the training samples used to initially train the classifier, so that the classifier can effectively classify the SQL to be processed when the SQL with the same type as the SQL to be processed appears subsequently. In order to improve the processing efficiency, besides the editing process information of the SQL of the new SQL type which can be effectively identified by the classifier after the initial training of the classifier is prestored, the editing process of the preprocessed SQL can be recorded to obtain the editing process information, and the SQL type to which the preprocessed SQL belongs and the corresponding editing process information are stored, so that the subsequent SQL is directly edited according to the editing process information corresponding to the SQL type to which the SOL belongs when the classifier can be effectively identified and classified and belongs to the SQL type which the corresponding SQL type distributed memory database does not support.
The aforementioned classifier can use a naive bayes classifier, and of course, other classifiers can be used, which are not described herein again.
Fig. 3 shows a flow chart of the naive bayes classification. As can be seen from fig. 3, the overall na iotave bayes classification is divided into three stages:
the method comprises a first stage, namely a preparation working stage, wherein the task of the preparation working stage is to make necessary preparation for naive Bayes classification, the main work is to determine characteristic attributes (namely the SQL attributes) according to specific situations, properly divide each characteristic attribute, and manually classify a part of items to be classified to form a training sample set. The input of this stage is all the data to be classified, and the output is the feature attributes and training samples. The stage is the only stage needing manual completion in the whole naive Bayes classification, the quality of the stage has important influence on the whole process, and the quality of the classifier is determined by the characteristic attributes, the characteristic attribute division and the quality of the training samples to a great extent.
The second stage, classifier training stage, is to generate classifiers by calculating each class yiFrequency of occurrence P (y) in training samplesi) And each characteristic attribute xjPartitioning the conditional probability estimates P (x) for each classj|yi)
And the results are recorded. The inputs are feature attributes and training samples X, and the output is a classifier. This phase is a mechanical phase and can be calculated automatically by a program.
Third phase-application phase. The task of this stage is to classify the items to be classified by using a classifier, the input of which is the classifier and the items to be classified, and the output is the mapping relation between the items to be classified and the categories. This phase is also a mechanical phase, and is performed by a program.
Referring to fig. 4, the present embodiment discloses an SQL executing apparatus, which includes:
the system comprises an acquisition unit 1, a processing unit and a processing unit, wherein the acquisition unit is used for acquiring a business SQL request submitted by a client, and the business SQL request comprises SQL to be processed;
the preprocessing unit 2 is configured to classify the to-be-processed SQL by using a trained classifier, and if the to-be-processed SQL does not belong to a pre-divided SQL type, preprocess the to-be-processed SQL to obtain standard SQL;
and the feedback unit 3 is used for submitting the standard SQL to the corresponding SQL type distributed memory database for execution, acquiring an execution result after the SQL type distributed memory database is executed, and feeding back the execution result to the client, wherein the pre-divided SQL types comprise the SQL type of the lowest SQL standard commonly supported by all the SQL type distributed memory databases.
In this embodiment, the preprocessing unit may be specifically configured to:
analyzing the SQL to be processed to obtain SQL attributes, wherein the SQL attributes comprise an SQL type, an operation object, operation object related attributes and/or object processing requirements;
determining a key condition corresponding to the SQL attribute according to the SQL attribute, judging whether the SQL to be processed meets the key condition or not, and editing the SQL to be processed according to a judgment result to enable the SQL to be processed to meet the SQL standard of the SQL type distributed memory database, wherein the key condition is a limiting condition related to standard SQL corresponding to the SQL attribute in the SQL type distributed memory database;
the feedback unit may specifically be configured to:
and submitting the edited SQL to be processed to the SQL type distributed memory database for execution.
On the basis of the foregoing embodiment of the apparatus, the apparatus may further include the following structure not shown in the drawings:
the training unit is used for recording the editing process of the SQL to be processed to obtain editing process information, retraining the classifier by using the SQL attributes and the training samples used for the first training of the classifier, and storing the editing process information and the corresponding SQL types, wherein the corresponding SQL types are newly-increased types in the SQL types which can be effectively identified by the classifier after the classifier is retrained, and the training samples used for the first training of the classifier comprise SOL which is not supported by the SQL type distributed memory database.
The device can execute any SQL execution method, thereby solving the same technical problem and achieving the same technical effect.
To sum up, the SQL execution device provided in the embodiment of the present invention enables developers to compile SQL requests and submit based on unified standards, and enables the heterogeneous SQL type distributed memory database to process normally, thereby solving the problem that the existing SQL type memory database has a great variety but the SQL support standards are not unified, and specifically compared with the prior art, the SQL execution device has the following beneficial effects:
1. the original problems that a service developer needs to learn various memory bank products, the learning cost is high, and the development difficulty is large are solved only by developing according to the standard SQL;
2. the DBA needs to solve the problems of high operation and maintenance difficulty and high operation and maintenance cost caused by the model selection and later-stage optimization of various memory database products, and only needs to consider the problems of standard SQL optimization and capacity management at present;
3. the problem that the SQL standard among different memory banks is not unified originally and cross-platform SQL migration cannot be realized is solved, the cross-platform problem does not need to be considered at present, and only the business function is realized by paying attention;
4. the problems of high development, operation and maintenance cost, low project development efficiency, long period, high management cost and the like exist in the original project management aspect, and the cost is not increased at present and only needs normal development, test, online and dimension exchanging.
Fig. 5 is a schematic entity structure diagram of an electronic device according to an embodiment of the present invention, and as shown in fig. 5, the electronic device may include: at least one processor 101, at least one memory 102, and a bus 103; wherein the content of the first and second substances,
the processor 101 and the memory 102 complete mutual communication through the bus 103;
the memory 102 stores program instructions executable by the processor 101, and the processor 101 may call the program instructions to perform the method provided by the above method embodiments, for example, the method includes: acquiring a business SQL request submitted by a client, wherein the business SQL request comprises SQL to be processed; classifying the SQL to be processed by utilizing a trained classifier, and if the SQL to be processed does not belong to a pre-divided SQL type, preprocessing the SQL to be processed to obtain standard SQL; and submitting the standard SQL to a corresponding SQL type distributed memory database for execution, acquiring an execution result after the SQL type distributed memory database is executed, and feeding back the execution result to the client, wherein the pre-divided SQL types comprise the SQL type of the lowest SQL standard commonly supported by all the SQL type distributed memory databases.
An embodiment of the present invention discloses a computer program product, the computer program product includes a computer program stored on a non-transitory computer readable storage medium, the computer program includes program instructions, when the program instructions are executed by a computer, the computer can execute the method provided by the above method embodiment, for example, the method includes: acquiring a business SQL request submitted by a client, wherein the business SQL request comprises SQL to be processed; classifying the SQL to be processed by utilizing a trained classifier, and if the SQL to be processed does not belong to a pre-divided SQL type, preprocessing the SQL to be processed to obtain standard SQL; and submitting the standard SQL to a corresponding SQL type distributed memory database for execution, acquiring an execution result after the SQL type distributed memory database is executed, and feeding back the execution result to the client, wherein the pre-divided SQL types comprise the SQL type of the lowest SQL standard commonly supported by all the SQL type distributed memory databases.
Embodiments of the present invention provide a non-transitory computer-readable storage medium storing computer instructions, which cause a computer to execute a method provided by the above method embodiments, for example, including: acquiring a business SQL request submitted by a client, wherein the business SQL request comprises SQL to be processed; classifying the SQL to be processed by utilizing a trained classifier, and if the SQL to be processed does not belong to a pre-divided SQL type, preprocessing the SQL to be processed to obtain standard SQL; and submitting the standard SQL to a corresponding SQL type distributed memory database for execution, acquiring an execution result after the SQL type distributed memory database is executed, and feeding back the execution result to the client, wherein the pre-divided SQL types comprise the SQL type of the lowest SQL standard commonly supported by all the SQL type distributed memory databases.
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.
It is noted that, herein, relational terms such as first and second, and the like may be used solely to distinguish one entity or action from another entity or action without necessarily requiring or implying any actual such relationship or order between such entities or actions. Also, the terms "comprises," "comprising," or any other variation thereof, are intended to cover a non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements does not include only those elements but may include other elements not expressly listed or inherent to such process, method, article, or apparatus. Without further limitation, an element defined by the phrase "comprising an … …" does not exclude the presence of other identical elements in a process, method, article, or apparatus that comprises the element. The terms "upper", "lower", and the like, indicate orientations or positional relationships based on the orientations or positional relationships shown in the drawings, and are only for convenience in describing the present invention and simplifying the description, but do not indicate or imply that the referred devices or elements must have a specific orientation, be constructed and operated in a specific orientation, and thus, should not be construed as limiting the present invention. Unless expressly stated or limited otherwise, the terms "mounted," "connected," and "connected" are intended to be inclusive and mean, for example, that they may be fixedly connected, detachably connected, or integrally connected; can be mechanically or electrically connected; they may be connected directly or indirectly through intervening media, or they may be interconnected between two elements. The specific meanings of the above terms in the present invention can be understood by those skilled in the art according to specific situations.
In the description of the present invention, numerous specific details are set forth. It is understood, however, that embodiments of the invention may be practiced without these specific details. In some instances, well-known methods, structures and techniques have not been shown in detail in order not to obscure an understanding of this description. Similarly, it should be appreciated that in the foregoing description of exemplary embodiments of the invention, various features of the invention are sometimes grouped together in a single embodiment, figure, or description thereof for the purpose of streamlining the disclosure and aiding in the understanding of one or more of the various inventive aspects. However, the disclosed method should not be interpreted as reflecting an intention that: that the invention as claimed requires more features than are expressly recited in each claim. Rather, as the following claims reflect, inventive aspects lie in less than all features of a single foregoing disclosed embodiment. Thus, the claims following the detailed description are hereby expressly incorporated into this detailed description, with each claim standing on its own as a separate embodiment of this invention. It should be noted that the embodiments and features of the embodiments in the present application may be combined with each other without conflict. The present invention is not limited to any single aspect, nor is it limited to any single embodiment, nor is it limited to any combination and/or permutation of these aspects and/or embodiments. Moreover, each aspect and/or embodiment of the present invention may be utilized alone or in combination with one or more other aspects and/or embodiments thereof.
Finally, it should be noted that: the above embodiments are only used to illustrate the technical solution of the present invention, and not to limit the same; while the invention has been described in detail and with reference to the foregoing embodiments, it will be understood by those skilled in the art that: the technical solutions described in the foregoing embodiments may still be modified, or some or all of the technical features may be equivalently replaced; such modifications and substitutions do not depart from the spirit and scope of the present invention, and they should be construed as being included in the following claims and description.
Claims (10)
1. An SQL execution method, comprising:
acquiring a business SQL request submitted by a client, wherein the business SQL request comprises SQL to be processed;
classifying the SQL to be processed by utilizing a trained classifier, and if the SQL to be processed does not belong to a pre-divided SQL type, preprocessing the SQL to be processed to obtain standard SQL;
submitting the standard SQL to a corresponding SQL type distributed memory database for execution, acquiring an execution result after the SQL type distributed memory database is executed, and feeding back the execution result to the client, wherein the pre-divided SQL types comprise the SQL type of the lowest SQL standard supported by all the SQL type distributed memory databases;
and if the SQL to be processed belongs to the standard SQL type supported by the corresponding SQL type distributed memory database, directly submitting the SQL to be processed to the corresponding SQL type distributed memory database for execution.
2. The method according to claim 1, wherein the preprocessing the SQL to be processed comprises:
analyzing the SQL to be processed to obtain SQL attributes, wherein the SQL attributes comprise an SQL type, an operation object, operation object related attributes and/or object processing requirements;
determining a key condition corresponding to the SQL attribute according to the SQL attribute, judging whether the SQL to be processed meets the key condition or not, and editing the SQL to be processed according to a judgment result to enable the SQL to be processed to meet the SQL standard of the SQL type distributed memory database, wherein the key condition is a limiting condition related to standard SQL corresponding to the SQL attribute in the SQL type distributed memory database;
wherein, the submitting the standard SQL to a corresponding SQL type distributed memory database for execution comprises:
and submitting the edited SQL to be processed to the SQL type distributed memory database for execution.
3. The method of claim 2, further comprising:
recording the editing process of the SQL to be processed to obtain editing process information, retraining the classifier by using the SQL attribute and a training sample used for training the classifier for the first time, and storing the editing process information and a corresponding SQL type, wherein the corresponding SQL type is a newly-added type in the SQL types which can be effectively identified by the classifier after retraining the classifier, and the training sample used for training the classifier for the first time comprises SOL which is not supported by the SQL type distributed memory database.
4. The method according to claim 2 or 3, wherein if the SQL-type distributed memory database is GemFireXD, the SQL type is DQL, and the operation object is two tables, the related attributes of the operation object include: the method comprises the following steps of distributing attributes, distributing field attributes and specifying associated object table attributes, wherein the key conditions comprise: the two tables are both distribution tables, the distribution fields of the two tables are consistent, and the table building statement of one table in the two tables contains a colocate with keyword;
wherein, according to the judgment result, editing the SQL to be processed comprises:
if the two tables are both distributed tables, the distribution fields of the two tables are consistent, and the table building sentences of the two tables do not contain a colocate with key word, a temporary table of a smaller table of the two tables is created, wherein the table building sentences of the temporary table contain the colocate with key word for appointing the larger table of the two tables as an associated object table;
and importing the data of the smaller table into the temporary table, and replacing the name of the smaller table in the SQL to be processed with the name of the temporary table.
5. The method according to claim 4, wherein the table building statement of the temporary table further includes an SQL statement for specifying a release time of the temporary table;
after acquiring other SQL types which are DQL and operation objects of which are a first data table and a second data table and are submitted by a client connected with an SQL type distributed memory database GemFireXD, editing the SQL to be processed according to a judgment result, wherein the method comprises the following steps:
if the temporary table is not released, replacing the name of the first data table in the other SQL to be processed with the name of the temporary table, wherein the first data table and the second data table are both distribution tables, the distribution fields of the first data table and the second data table are consistent, the table building sentences of the first data table and the second data table do not contain a colocate with keyword, the first data table is the smaller table, and the first data table is the smaller table in the first data table and the second data table.
6. The method of claim 3,
if the obtained other SQL to be processed submitted by the client belongs to the SQL type corresponding to the editing process information, wherein the SQL type corresponding to the editing process information comprises the SQL type to which SOL which is not supported by the SQL type distributed memory database belongs,
the preprocessing the SQL to be processed comprises the following steps:
and editing the other SQL to be processed according to the editing process information corresponding to the SQL type to which the SQL belongs.
7. The method of claim 1, further comprising:
and if the SQL to be processed belongs to the standard SQL type supported by the corresponding SQL type distributed memory database, directly submitting the SQL to be processed to the corresponding SQL type distributed memory database for execution.
8. An SQL execution apparatus, comprising:
the system comprises an acquisition unit, a processing unit and a processing unit, wherein the acquisition unit is used for acquiring a business SQL request submitted by a client, and the business SQL request comprises SQL to be processed;
the preprocessing unit is used for classifying the SQL to be processed by utilizing the trained classifier, and if the SQL to be processed does not belong to the pre-divided SQL type, preprocessing the SQL to be processed to obtain standard SQL;
the feedback unit is used for submitting the standard SQL to a corresponding SQL type distributed memory database for execution, acquiring an execution result after the SQL type distributed memory database is executed, and feeding back the execution result to the client, wherein the pre-divided SQL types comprise the SQL type of the lowest SQL standard commonly supported by all the SQL type distributed memory databases;
and if the SQL to be processed belongs to the standard SQL type supported by the corresponding SQL type distributed memory database, directly submitting the SQL to be processed to the corresponding SQL type distributed memory database for execution.
9. The apparatus according to claim 8, wherein the preprocessing unit is specifically configured to:
analyzing the SQL to be processed to obtain SQL attributes, wherein the SQL attributes comprise an SQL type, an operation object, operation object related attributes and/or object processing requirements;
determining a key condition corresponding to the SQL attribute according to the SQL attribute, judging whether the SQL to be processed meets the key condition or not, and editing the SQL to be processed according to a judgment result to enable the SQL to be processed to meet the SQL standard of the SQL type distributed memory database, wherein the key condition is a limiting condition related to standard SQL corresponding to the SQL attribute in the SQL type distributed memory database;
wherein, the feedback unit is specifically configured to:
and submitting the edited SQL to be processed to the SQL type distributed memory database for execution.
10. The apparatus of claim 9, further comprising:
the training unit is used for recording the editing process of the SQL to be processed to obtain editing process information, retraining the classifier by using the SQL attributes and the training samples used for the first training of the classifier, and storing the editing process information and the corresponding SQL types, wherein the corresponding SQL types are newly-increased types in the SQL types which can be effectively identified by the classifier after the classifier is retrained, and the training samples used for the first training of the classifier comprise SOL which is not supported by the SQL type distributed memory database.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201611244308.6A CN108255852B (en) | 2016-12-29 | 2016-12-29 | SQL execution method and device |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201611244308.6A CN108255852B (en) | 2016-12-29 | 2016-12-29 | SQL execution method and device |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN108255852A CN108255852A (en) | 2018-07-06 |
| CN108255852B true CN108255852B (en) | 2020-09-11 |
Family
ID=62719143
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201611244308.6A Active CN108255852B (en) | 2016-12-29 | 2016-12-29 | SQL execution method and device |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN108255852B (en) |
Families Citing this family (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN111190898B (en) * | 2019-11-25 | 2023-07-14 | 泰康保险集团股份有限公司 | Data processing method and device, electronic equipment and storage medium |
| CN112115150B (en) * | 2020-08-03 | 2024-03-19 | 上海金仕达软件科技股份有限公司 | Data management method, terminal equipment and medium of embedded memory database |
| CN113111356A (en) * | 2021-03-09 | 2021-07-13 | 深圳市教育信息技术中心(深圳市教育装备中心) | Data encryption method, device, equipment and medium |
| CN116150161B (en) * | 2023-04-18 | 2023-08-01 | 广东广宇科技发展有限公司 | Multidimensional data processing method based on Redis database |
Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| WO2008156044A1 (en) * | 2007-06-19 | 2008-12-24 | Japan Lucida Co., Ltd. | Data management device, data management method, data management program, and data management program storage medium |
| CN101706810A (en) * | 2009-11-23 | 2010-05-12 | 北京中创信测科技股份有限公司 | Method and device for querying databases |
| CN101980207A (en) * | 2010-11-09 | 2011-02-23 | 中国电信股份有限公司 | Method and system for implementing database access |
| CN102521254A (en) * | 2011-11-17 | 2012-06-27 | 广东电网公司电力科学研究院 | Uniform access method of isomeric database |
-
2016
- 2016-12-29 CN CN201611244308.6A patent/CN108255852B/en active Active
Patent Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| WO2008156044A1 (en) * | 2007-06-19 | 2008-12-24 | Japan Lucida Co., Ltd. | Data management device, data management method, data management program, and data management program storage medium |
| CN101706810A (en) * | 2009-11-23 | 2010-05-12 | 北京中创信测科技股份有限公司 | Method and device for querying databases |
| CN101980207A (en) * | 2010-11-09 | 2011-02-23 | 中国电信股份有限公司 | Method and system for implementing database access |
| CN102521254A (en) * | 2011-11-17 | 2012-06-27 | 广东电网公司电力科学研究院 | Uniform access method of isomeric database |
Also Published As
| Publication number | Publication date |
|---|---|
| CN108255852A (en) | 2018-07-06 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US10642832B1 (en) | Reducing the domain of a subquery by retrieving constraints from the outer query | |
| CN105608086B (en) | Transaction processing method and device for distributed database system | |
| US9280568B2 (en) | Zero downtime schema evolution | |
| CN108255852B (en) | SQL execution method and device | |
| WO2020233330A1 (en) | Batch testing method, apparatus, and computer-readable storage medium | |
| CN110291517A (en) | Query language interoperability in chart database | |
| US9165049B2 (en) | Translating business scenario definitions into corresponding database artifacts | |
| US20150269215A1 (en) | Dependency-aware transaction batching for data replication | |
| US20060271504A1 (en) | Performance data for query optimization of database partitions | |
| CN111712809A (en) | Learning ETL rules by example | |
| CN104899295B (en) | A kind of heterogeneous data source data relation analysis method | |
| JP2010524060A (en) | Data merging in distributed computing | |
| WO2011149666A2 (en) | Data mart automation | |
| US11269880B2 (en) | Retroreflective clustered join graph generation for relational database queries | |
| CN104572895A (en) | MPP (Massively Parallel Processor) database and Hadoop cluster data intercommunication method, tool and realization method | |
| WO2019228015A1 (en) | Index creating method and apparatus based on nosql database of mobile terminal | |
| US20180357298A1 (en) | Performance of Distributed Databases and Database-Dependent Software Applications | |
| CN106354729A (en) | Graph data handling method, device and system | |
| CN112579586A (en) | Data processing method, device, equipment and storage medium | |
| CN109408591B (en) | Decision-making distributed database system supporting SQL (structured query language) driven AI (Artificial Intelligence) and feature engineering | |
| US11615076B2 (en) | Monolith database to distributed database transformation | |
| US20100161682A1 (en) | Metadata model repository | |
| CN115857918A (en) | Data processing method and device, electronic equipment and storage medium | |
| CN106383893A (en) | Time sequence data management method and system | |
| CN111177181A (en) | SQL text auditing method, system, storage medium and device |
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 | ||
| CP01 | Change in the name or title of a patent holder | ||
| CP01 | Change in the name or title of a patent holder |
Address after: No. 19, Jiefang East Road, Hangzhou, Zhejiang Province, 310016 Patentee after: CHINA MOBILE GROUP ZHEJIANG Co.,Ltd. Patentee after: CHINA MOBILE COMMUNICATIONS GROUP Co.,Ltd. Address before: No. 19, Jiefang East Road, Hangzhou, Zhejiang Province, 310016 Patentee before: CHINA MOBILE GROUP ZHEJIANG Co.,Ltd. Patentee before: CHINA MOBILE COMMUNICATIONS Corp. |
|
| TR01 | Transfer of patent right | ||
| TR01 | Transfer of patent right |
Effective date of registration: 20231218 Address after: No. 19, Jiefang East Road, Hangzhou, Zhejiang Province, 310016 Patentee after: CHINA MOBILE GROUP ZHEJIANG Co.,Ltd. Patentee after: China Mobile (Zhejiang) Innovation Research Institute Co.,Ltd. Patentee after: CHINA MOBILE COMMUNICATIONS GROUP Co.,Ltd. Address before: No. 19, Jiefang East Road, Hangzhou, Zhejiang Province, 310016 Patentee before: CHINA MOBILE GROUP ZHEJIANG Co.,Ltd. Patentee before: CHINA MOBILE COMMUNICATIONS GROUP Co.,Ltd. |