CN111966707A - Query statement generation method and device, electronic equipment and computer readable medium - Google Patents
Query statement generation method and device, electronic equipment and computer readable medium Download PDFInfo
- Publication number
- CN111966707A CN111966707A CN202010852477.8A CN202010852477A CN111966707A CN 111966707 A CN111966707 A CN 111966707A CN 202010852477 A CN202010852477 A CN 202010852477A CN 111966707 A CN111966707 A CN 111966707A
- Authority
- CN
- China
- Prior art keywords
- target
- field
- data
- configuration file
- key
- 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.)
- Pending
Links
- 238000000034 method Methods 0.000 title claims abstract description 51
- 238000006243 chemical reaction Methods 0.000 claims abstract description 11
- 230000006870 function Effects 0.000 claims description 50
- 238000013507 mapping Methods 0.000 claims description 48
- 238000004891 communication Methods 0.000 claims description 19
- 230000002159 abnormal effect Effects 0.000 claims description 17
- 238000004590 computer program Methods 0.000 claims description 10
- 238000012360 testing method Methods 0.000 description 19
- 230000008569 process Effects 0.000 description 7
- 238000010586 diagram Methods 0.000 description 5
- 230000009471 action Effects 0.000 description 3
- 238000004519 manufacturing process Methods 0.000 description 2
- 238000003491 array Methods 0.000 description 1
- 230000009286 beneficial effect Effects 0.000 description 1
- 230000008859 change Effects 0.000 description 1
- 238000013500 data storage Methods 0.000 description 1
- 238000005516 engineering process Methods 0.000 description 1
- 239000000835 fiber Substances 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
- 230000002093 peripheral effect Effects 0.000 description 1
- 238000012545 processing Methods 0.000 description 1
- 239000004065 semiconductor Substances 0.000 description 1
- 239000007787 solid Substances 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
-
- 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/21—Design, administration or maintenance of databases
-
- 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/25—Integrating or interfacing systems involving database management systems
- G06F16/254—Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Databases & Information Systems (AREA)
- Physics & Mathematics (AREA)
- Data Mining & Analysis (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Mathematical Physics (AREA)
- Computational Linguistics (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The application provides a query statement generation method, a query statement generation device, electronic equipment and a computer readable medium, which belong to the technical field of statement generation, and the method comprises the following steps: acquiring the comparator, a target configuration file of the target data and a reference configuration file of the reference data; determining a field corresponding relation between a target field in the target field list and a reference field with the same position as the target field in the reference field list, and converting the comparator into a comparison function through a preset conversion condition; and generating a query statement according to the comparison function, the target configuration file and the reference configuration file through the field corresponding relation. According to the method and the device, the query statement is automatically generated through the configuration information, and the efficiency is improved.
Description
Technical Field
The present application relates to the field of statement generation technologies, and in particular, to a query statement generation method, an apparatus, an electronic device, and a computer-readable medium.
Background
The role of the data warehouse as a production analytic report and providing support for enterprise decision making is widely used by large enterprises, and currently, an Extract-Transform-Load (ETL) step is required to be performed during the process of constructing the data warehouse, i.e., data is extracted, converted and loaded from a source end to a destination end.
After the technical staff writes the ETL program, a HiveQL query statement needs to be written for specific data to test whether the data after ETL execution is consistent with the original data, so as to determine whether the ETL program has a problem. After an ETL program is written each time, a HiveQL query statement needs to be manually written for specific data, so that the efficiency is low.
Disclosure of Invention
An object of the embodiments of the present application is to provide a query statement generation method, an apparatus, an electronic device, and a computer-readable medium, so as to solve the problem of low labor efficiency. The specific technical scheme is as follows:
in a first aspect, the present application provides a query statement generation method, including:
acquiring a comparator, a target configuration file of target data and a reference configuration file of reference data, wherein the target configuration file comprises a target field list and target configuration information, and the reference configuration file comprises a reference field list and reference configuration information;
determining a field corresponding relation between a target field in the target field list and a reference field with the same position as the target field in the reference field list, and converting the comparator into a comparison function through a preset conversion condition;
and generating a query statement according to the comparison function, the target configuration file and the reference configuration file through the field corresponding relation.
Optionally, the generating, according to the field correspondence, a query statement according to the comparison function, the target configuration file, and the reference configuration file includes:
and filling the comparison function, the data in the target configuration file and the data in the reference configuration file into corresponding positions in a preset template respectively through the field corresponding relation, a first mapping relation of the data identification and the position of the data identification in the preset template and a second mapping relation of the data identification and the data to generate a query statement.
Optionally, the target configuration information includes a target database, a target data table, and a target primary key, and the reference configuration information includes a reference database, a reference data table, and a reference primary key; the filling the comparison function, the data in the target configuration file and the data in the reference configuration file into corresponding positions in a preset template respectively according to the field corresponding relationship, the first mapping relationship between the data identifier and the position of the data identifier in the preset template and the second mapping relationship between the data identifier and the data comprises:
determining the field source positions of the identification of the target database, the identification of the target data table, the identification of the reference database and the identification of the reference data table in the preset template according to the first mapping relation, and filling the field source positions with the target database, the target data table, the reference database and the reference data table respectively according to the second mapping relation;
determining the field positions of the target field and the reference field in the preset template according to the field corresponding relation and the field source position, and filling the field positions with the target field and the reference field;
determining the positions of the main keys of the marks of the target main key and the reference main key in the preset template according to the first mapping relation, and filling the positions of the main keys with the target main key and the reference main key according to the second mapping relation;
and determining a data comparison position of the identifier of the comparison function in the preset template according to the first mapping relation, and filling the comparison function into the data comparison position according to the second mapping relation.
Optionally, the target field and the reference field each include a plurality of field data; after the generating the query statement, the method further comprises:
connecting the target data table and the reference data table through the target primary key and the reference primary key;
traversing the target field and the reference field as follows: acquiring target field data of a target field in the target data table and reference field data of a reference field in the reference data table, and adding 1 to a value of a recorded abnormal parameter which indicates that a preset condition is not met under the condition that the target field data and the reference field data do not meet the preset condition;
and outputting the value of the abnormal parameter.
Optionally, the target primary key and the reference primary key each comprise a plurality of sub-keys; the acquiring of the target field data of the target field in the target data table and the reference field data of the reference field in the reference data table includes:
determining a target sub-key of the target main key and a reference sub-key of the reference main key, wherein the target sub-key and the reference sub-key have a key correspondence relationship;
and determining target field data corresponding to the target sub-key in the target field and reference field data corresponding to the reference sub-key in the reference field.
Optionally, the method further comprises:
acquiring a first quantity of all field data in the target field list and a second quantity of all field data in the reference field list;
and taking the sum of the first number and the second number as a total number, and outputting the total number.
In a second aspect, the present application provides a query statement generation apparatus, including:
the device comprises an acquisition module, a comparison module and a comparison module, wherein the acquisition module is used for acquiring a target configuration file of target data and a reference configuration file of reference data, the target configuration file comprises a target field list and target configuration information, and the reference configuration file comprises a reference field list and reference configuration information;
a determining module, configured to determine a field correspondence between a target field in the target field list and a reference field in the reference field list, where the position of the reference field is the same as that of the target field, and convert the comparator into a comparison function through a preset conversion condition;
and the generation module is used for generating a query statement according to the comparison function, the target configuration file and the reference configuration file through the field corresponding relation.
Optionally, the generating module includes:
and the filling-in unit is used for respectively filling the comparison function, the data in the target configuration file and the data in the reference configuration file into corresponding positions in a preset template through the field corresponding relation, the first mapping relation of the positions of the data identification and the data identification in the preset template and the second mapping relation of the data identification and the data, so as to generate the query statement.
In a third aspect, the present application provides an electronic device, including a processor, a communication interface, a memory, and a communication bus, where the processor, the communication interface, and the memory complete mutual communication through the communication bus;
a memory for storing a computer program;
a processor for implementing any of the method steps described herein when executing the program stored in the memory.
In a fourth aspect, the present application provides a computer readable storage medium having stored thereon a computer program which, when executed by a processor, performs any of the method steps described herein.
The embodiment of the application has the following beneficial effects:
the embodiment of the application provides a query statement generation method, which comprises the following steps: the processor obtains the comparator, a target configuration file of the target data and a reference configuration file of the reference data, determines a field corresponding relation between a target field in the target field list and a reference field with the same position as the target field in the reference field list, converts the comparator into a comparison function through a preset conversion condition, and then generates a query statement according to the comparison function, the target configuration file and the reference configuration file through the field corresponding relation. According to the method and the device, the query statement is automatically generated through the configuration information, the query statement does not need to be manually compiled, and the efficiency is improved.
Of course, not all of the above advantages need be achieved in the practice of any one product or method of the present application.
Drawings
In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, the drawings needed to be used in the description of the embodiments or the prior art will be briefly described below, and it is obvious for those skilled in the art to obtain other drawings without inventive exercise.
Fig. 1 is a flowchart of a method for generating a query statement according to an embodiment of the present application;
fig. 2 is a flowchart of a method for filling data into a preset template according to an embodiment of the present disclosure;
fig. 3 is a flowchart of a method for acquiring target field data and reference field data according to an embodiment of the present application;
fig. 4 is a schematic structural diagram of a query statement generating apparatus according to an embodiment of the present application;
fig. 5 is a schematic structural diagram of an electronic device according to an embodiment of the present application.
Detailed Description
The technical solutions in the embodiments of the present invention will be clearly and completely described below with reference to the drawings in the embodiments of the present invention, and it is obvious that the described embodiments are only a part of the embodiments of the present invention, and not all of the embodiments. 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.
The embodiment of the application provides a query statement generation method, which can be applied to a processor and used for automatically generating a query statement from configuration data.
The following describes in detail a query statement generation method provided in an embodiment of the present application with reference to a specific embodiment, as shown in fig. 1, the specific steps are as follows:
step 101: a comparator, a target profile of the target data, and a reference profile of the reference data are obtained.
The target configuration file comprises a target field list and target configuration information, and the reference configuration file comprises a reference field list and reference configuration information.
In the embodiment of the present application, data may change before and after the execution of the ELT program, and therefore, the processor obtains configuration information of the data before and after the execution of the ELT program, including the comparator, the reference configuration file of the reference data before the execution of the ELT program, and the target configuration file of the target data after the execution of the ELT program. The configuration information may be in a JSON (JSON encoding) format, and the specific format of the configuration file is not limited in the embodiment of the present application.
The reference configuration information includes a reference database, a reference data table, and a reference primary key, and the target configuration information includes a target database, a target data table, and a target primary key. Each data has a corresponding data identifier, wherein the identifier of the reference database is the identifier of the database in which the reference data is located, each database comprises a plurality of data tables, the identifier of the reference database is the identifier of the data table in which the reference data is located, the identifier of the target database is the identifier of the database in which the target data is located, each database comprises a plurality of data tables, and the identifier of the target database is the identifier of the data table in which the target data is located. The reference primary key is a field primary key of the reference data table, and the target primary key is a field primary key of the target data table. A comparator is a symbol used for comparison, comprising: "═ <", ">", "≦ and" ≧ respectively.
In the embodiment of the present application, the comparator, the target configuration file, and the reference configuration file may be configured manually, or may be configured automatically by the processor through data, and the source of the configuration information is not specifically limited in the present application.
Step 102: and determining the field corresponding relation between the target field in the target field list and the reference field with the same position as the target field in the reference field list, and converting the comparator into a comparison function through a preset conversion condition.
In this embodiment of the present application, the target field list includes a plurality of fields, the reference field list includes a plurality of fields, and the processor needs to establish a field correspondence between the target field and the reference field, including two ways:
the first method is as follows: the processor determines a target field located at a preset position in the target list and a reference field located at the same preset position in the reference list, and establishes a field corresponding relation between the target field and the reference field.
The second method comprises the following steps: the processor firstly establishes a plurality of field corresponding relations, wherein the field corresponding relations comprise the positions of fields existing in the field list, and then fills the field corresponding relations into the target fields located at the corresponding positions in the target field list and the reference fields located at the corresponding positions in the reference field list.
The processor also converts the comparator into a comparison function by presetting a conversion condition. The preset conversion condition is the corresponding relation between the symbol of the comparator and the comparison function. The comparison function may be UDF (User-Defined Functions).
Exemplarily, the comparator "═ is" is converted into a comparison function "is _ equal"; the comparator ">" is converted into a comparison function "is _ more _ than".
Step 103: and generating a query statement according to the comparison function, the target configuration file and the reference configuration file through the field corresponding relation.
In the embodiment of the application, after determining the field correspondence, the processor generates a query statement according to the comparison function, the target configuration file and the reference configuration file.
Illustratively, the Query statement may be a HiveQL statement, and may also be an SQL (Structured Query Language) statement.
As an optional implementation manner, the processor fills the comparison function, the data in the target configuration file, and the data in the reference configuration file into corresponding positions in the preset template respectively through the field corresponding relationship, the first mapping relationship between the data identifier and the position of the data identifier in the preset template, and the second mapping relationship between the data identifier and the data, so as to generate the query statement.
In the embodiment of the application, the processor automatically generates the query statement according to the configuration information, manual compiling of the query statement is not needed, manual operation is reduced, and production efficiency is improved.
As an optional implementation manner, as shown in fig. 2, the process of the processor filling the comparison function, the data in the target configuration file, and the data in the reference configuration file into corresponding positions in the preset template through the field corresponding relationship, the first mapping relationship between the data identifier and the position of the data identifier in the preset template, and the second mapping relationship between the data identifier and the data is as follows:
step 201: and according to the first mapping relation, determining the field source positions of the identification of the target database, the identification of the target data table, the identification of the reference database and the identification of the reference data table in a preset template, and respectively filling the field source positions of the target database, the target data table, the reference database and the reference data table in the second mapping relation.
The processor may determine, according to a first mapping relationship between the data identifier and a position of the data identifier in the preset template, an identifier of the target database, an identifier of the target data table, an identifier of the reference database, and a field source position of the identifier of the reference data table in the preset template. And after determining the field source position, the processor fills the target database, the target data table, the reference database and the reference data table into the field source position according to the second mapping relation between the data identification and the data.
In the embodiment of the application, the database, the data table, the field list and the comparator all have identification. Programming 1 is a schematic programming of configuration information, and as can be seen from programming 1, a target configuration file includes a target database "test _ db 1", a target data table "test _ table 1", a target field list "a, b", and a target primary key "id 1", where the target database is identified as "db 1", the target data table is identified as "table 1", the target field list is identified as "fields 1", and the target primary key is identified as "key 1". The reference profile includes a reference database "test _ db 2", a reference data table "test _ table 2", a reference field list "x, y", and a reference primary key "id 2", wherein the reference database is identified as "db 2", the reference data table is identified as "table 2", the reference field list is identified as "fields 2", and the reference primary key is identified as "key 2". The comparator is "═ and the identifier of the comparator is" comparator ".
Programming 1
Programming 2 is schematic programming of a query statement, and as can be seen from the programming 2, the identifier of the target database is "db 1", the identifier of the target data table is "table 1", and the processor fills the data of the target database and the data of the target data table "test _ db1.test _ table 1" into the field source position in the preset template according to "db 1" and "table 1"; the reference database is identified as "db 2", the reference data table is identified as "table 2", and the processor fills the data of the reference database and the data of the reference data table "test _ db2.test _ table 2" into the field source position in the preset template according to "db 2" and "table 2".
Programming 2
Step 202: and determining the field positions of the target field and the reference field in the preset template according to the field corresponding relation and the field source position, and filling the field positions with the target field and the reference field.
In an embodiment of the application, the target field is derived from a target data table in a target database, the processor determines a target field source of the target field according to the target database and the target data table, the reference field is derived from a reference data table in a reference database, and the processor determines a reference field source of the reference field according to the reference database and the reference data table.
The processor determines a first position of a target field in a preset template according to a source of the target field, determines a second position of a reference field in the preset template according to the source of the reference field, associates the first position and the second position according to a field corresponding relation, finally determines field positions of the target field and the reference field in the preset template, and fills the target field and the reference field in the field positions respectively.
Illustratively, as can be seen from the programming 2, "test _ db1.test _ table 1" is equivalent to "t 1", the target field source of the target field "x" is "t 1", the "test _ db2.test _ table 2" is equivalent to "t 2", the reference field source of the reference field "y" is "t 2", and there is a field correspondence between the target field "x" and the reference field "y", so that the target field "x" and the reference field "y" can be filled in the field positions to form (t1.a, t2. x).
Step 203: and determining the positions of the main keys of the mark of the target main key and the mark of the reference main key in the preset template according to the first mapping relation, and filling the positions of the main keys of the target main key and the reference main key according to the second mapping relation.
In this embodiment, the processor may determine, according to a first mapping relationship between the data identifier and the position of the data identifier in the preset template, the position of the primary key of the identifier of the target primary key and the position of the identifier of the reference primary key in the preset template, and then fill, according to a second mapping relationship, the position of the primary key with the target primary key corresponding to the identifier of the target primary key and the reference primary key corresponding to the identifier of the reference primary key.
Illustratively, as can be seen from program 1, the identification of the target primary key is "key 1", and referring to the identification of the primary key as "key 2", in program 2, the processor fills in the primary key position with "id 1" according to "key 1", and fills in the primary key position with "id 2" according to "key 2".
Step 204: and determining the data comparison position of the identifier of the comparison function in the preset template according to the first mapping relation, and filling the comparison function into the data comparison position according to the second mapping relation.
In this embodiment, the identifiers of the comparison function and the comparator are the same, and the processor may determine the data comparison position of the identifier of the comparison function in the preset template according to a first mapping relationship between the data identifier and the position of the data identifier in the preset template, and then fill the comparison function in the data comparison position according to a second mapping relationship.
Illustratively, as can be seen in program 2, the processor populates the compare function "is _ equal" into the data compare location based on the compare function's identification "composer".
The processes in steps 201 to 204 may be executed in parallel or in sequence, and the present application is not limited specifically.
And the processor fills the comparison function, the data in the target configuration file and the data in the reference configuration file into corresponding positions in a preset template to generate a query statement.
As an optional implementation method, after generating the query statement, the method further includes: connecting the target data table and the reference data table through the target main key and the reference main key; the target field and the reference field are traversed as follows: acquiring target field data of a target field in a target data table and reference field data of a reference field in the reference data table, and adding 1 to a value of a recorded abnormal parameter which indicates that the preset condition is not met under the condition that the target field data and the reference field data do not meet the preset condition; and outputting the value of the abnormal parameter.
In the embodiment of the application, the target data table has a corresponding target primary key, the reference data table has a corresponding reference primary key, and the processor is connected with the target data table and the reference data table through the target primary key and the reference primary key. The target field includes a plurality of field data, and the reference field includes a plurality of field data.
The processor traverses the target field and the reference field as follows: the processor acquires target field data of a target field in the target data table and reference field data of a reference field in the reference data table, and judges whether the target field data and the reference field data meet a preset condition, wherein the preset condition is a comparison size condition. If the processor judges that the target field data and the reference field data meet the preset condition, adding 0 to the recorded value of the abnormal parameter which indicates that the target field data and the reference field data do not meet the preset condition; and if the processor judges that the target field data and the reference field data do not meet the preset condition, adding 1 to the recorded value of the abnormal parameter which shows that the preset condition is not met, and outputting the value of the abnormal parameter after the processor traverses the target field and the reference field.
In the embodiment of the present application, the preset function is _ equivalent, and before and after the ETL program is executed, if the ETL program has no fault, the target field data at the target position in the target field is the same as the target field data at the same position in the reference field. If the processor determines that the target field data is not the same as the reference field data, the abnormal parameter is a value at which the target field data is not the same as the reference field data. The processor adds 1 to the recorded values representing the non-identical exception parameters and outputs the value of the exception parameter.
Watch 1
| total_count | a_x_count |
| 10000 | 122 |
As shown in table one, the table one is a field comparison result diagram, and "a _ x _ count" indicates the number of abnormal parameters in the target field a and the reference field x, where the target field data is different from the reference field data. The a _ x _ count is 122, indicating that the number of exception parameters in the target field a and the reference field x is 122.
As an alternative embodiment, the method further comprises: acquiring a first quantity of all field data in the target field list and a second quantity of all field data in the reference field list; and taking the sum of the first number and the second number as the total number, and outputting the total number.
The processor acquires field data of all fields in the target field list and determines that the number of the field data of all the fields in the target field list is a first number, the processor acquires field data of all the fields in the reference field list and determines that the number of the field data of all the fields in the reference field list is a second number, and the processor adds the first number and the second number to form the total number of the field data and outputs the total number. By setting the total number and the value of the abnormal parameter, the proportion of the abnormal parameter in the total number can be inferred, and therefore the accuracy of the ETL program is determined. Wherein the proportion of the abnormal parameters in the total amount is inversely proportional to the total amount, and the higher the proportion of the abnormal parameters in the total amount is, the lower the accuracy of the ETL procedure is.
As an alternative embodiment, if the processor determines that the target field data and the reference field data do not satisfy the preset condition, adding 0 to the value of the recorded normal parameter indicating that the preset condition is satisfied; and if the processor judges that the target field data and the reference field data meet the preset condition, adding 1 to the recorded value of the normal parameter which shows that the preset condition is met, and outputting the value of the normal parameter.
As an alternative embodiment, as shown in fig. 3, the acquiring of the target field data of the target field in the target data table and the reference field data of the reference field in the reference data table includes:
step 301: and determining a target sub-key of the target main key and a reference sub-key in the reference main key.
Wherein the target sub-key and the reference sub-key have a key correspondence.
In the embodiment of the application, the target main key and the reference main key both comprise a plurality of sub-keys, the processor determines a target sub-key of the target main key and a reference sub-key of the reference main key, and the target sub-key and the reference sub-key have a key corresponding relationship.
Watch two
| id1 | a |
| 1 | Test_a |
| 2 | Test_c |
As shown in Table II, Table II is a schematic diagram of the target data table, and Table II includes two sub-keys "id 1-1" and "id 1-2", and the field a includes field data "Test _ a" and "Test _ c".
Watch III
| id2 | x |
| 1 | Test_a |
| 2 | Test_c |
As shown in Table III, Table III is a schematic diagram referring to a data table, and table III includes two sub-keys "id 2-1" and "id 2-2", and field x includes field data "Test _ a" and "Test _ c".
The processor determines the target sub-key "id 1-1" and the reference sub-key "id 2-1".
Step 302: target field data in the target field corresponding to the target sub-key and reference field data in the reference field corresponding to the reference sub-key are determined.
In an embodiment of the present application, the processor determines target field data in a target field corresponding to a target sub-key and reference field data in a reference field corresponding to a reference sub-key.
Illustratively, the target field data in the target field corresponding to the target sub-key "id 1-1" is "Test _ a", and the reference field data in the reference field corresponding to the reference sub-key "id 2-1" is "Test _ a".
Based on the same technical concept, an embodiment of the present application further provides a query statement generating apparatus, as shown in fig. 4, the apparatus includes:
an obtaining module 401, configured to obtain the comparator, a target configuration file of the target data, and a reference configuration file of the reference data, where the target configuration file includes a target field list and target configuration information, and the reference configuration file includes a reference field list and reference configuration information;
a determining module 402, configured to determine a field correspondence between a target field in the target field list and a reference field in the reference field list, where the position of the reference field is the same as that of the target field, and convert the comparator into a comparison function according to a preset conversion condition;
a generating module 403, configured to generate a query statement according to the comparison function, the target configuration file, and the reference configuration file through the field correspondence relationship.
Optionally, the generating module 403 includes:
and the filling-in unit is used for respectively filling the comparison function, the data in the target configuration file and the data in the reference configuration file into corresponding positions in the preset template through the field corresponding relation, the first mapping relation of the positions of the data identification and the data identification in the preset template and the second mapping relation of the data identification and the data, so as to generate the query statement.
Optionally, the target configuration information includes a target database, a target data table, and a target primary key, and the reference configuration information includes a reference database, a reference data table, and a reference primary key; the filling unit includes:
the first filling subunit is used for determining the field source positions of the identifier of the target database, the identifier of the target data table, the identifier of the reference database and the identifier of the reference data table in a preset template according to the first mapping relation, and respectively filling the field source positions of the target database, the target data table, the reference database and the reference data table according to the second mapping relation;
the second filling-in subunit is used for determining the field positions of the target field and the reference field in the preset template according to the field corresponding relation and the field source position, and filling the field positions with the target field and the reference field;
the third filling subunit is used for determining the positions of the main keys of the identifier of the target main key and the identifier of the reference main key in the preset template according to the first mapping relation and filling the positions of the main keys of the target main key and the reference main key according to the second mapping relation;
and the fourth filling subunit is used for determining the data comparison position of the identifier of the comparison function in the preset template according to the first mapping relation and filling the comparison function into the data comparison position according to the second mapping relation.
Optionally, the target field and the reference field each comprise a plurality of field data; the device still includes:
the connection module is used for connecting the target data table and the reference data table through the target main key and the reference main key;
a traversing module for traversing the target field and the reference field as follows: acquiring target field data of a target field in a target data table and reference field data of a reference field in the reference data table, and adding 1 to a value of a recorded abnormal parameter which indicates that the preset condition is not met under the condition that the target field data and the reference field data do not meet the preset condition;
and the first output module is used for outputting the value of the abnormal parameter.
Optionally, the target primary key and the reference primary key each comprise a plurality of sub-keys; the traversal module comprises:
a first determining subunit, configured to determine a target sub-key of the target main key and a reference sub-key of the reference main key, where the target sub-key and the reference sub-key have a key correspondence relationship;
a second determining subunit for determining target field data corresponding to the target sub-key in the target field and reference field data corresponding to the reference sub-key in the reference field.
Optionally, the apparatus further comprises:
the acquisition module is used for acquiring a first quantity of all field data in the target field list and a second quantity of all field data in the reference field list;
and the second output module is used for taking the sum of the first quantity and the second quantity as the total quantity and outputting the total quantity.
The embodiment of the application provides a query statement generation method, which comprises the following steps: the processor obtains the comparator, a target configuration file of the target data and a reference configuration file of the reference data, determines a field corresponding relation between a target field in the target field list and a reference field with the same position as the target field in the reference field list, converts the comparator into a comparison function through a preset conversion condition, and then generates a query statement according to the comparison function, the target configuration file and the reference configuration file through the field corresponding relation. According to the method and the device, the query statement is automatically generated through the configuration information, the query statement does not need to be manually compiled, and the efficiency is improved.
Based on the same technical concept, the embodiment of the present invention further provides an electronic device, as shown in fig. 5, including a processor 501, a communication interface 502, a memory 503 and a communication bus 504, where the processor 501, the communication interface 502 and the memory 503 complete mutual communication through the communication bus 504,
a memory 503 for storing a computer program;
the processor 501 is configured to implement the above steps when executing the program stored in the memory 503.
The communication bus mentioned in the electronic device may be a Peripheral Component Interconnect (PCI) bus, an Extended Industry Standard Architecture (EISA) bus, or the like. The communication bus may be divided into an address bus, a data bus, a control bus, etc. For ease of illustration, only one thick line is shown, but this does not mean that there is only one bus or one type of bus.
The communication interface is used for communication between the electronic equipment and other equipment.
The Memory may include a Random Access Memory (RAM) or a Non-Volatile Memory (NVM), such as at least one disk Memory. Optionally, the memory may also be at least one memory device located remotely from the processor.
The Processor may be a general-purpose Processor, including a Central Processing Unit (CPU), a Network Processor (NP), and the like; but also Digital Signal Processors (DSPs), Application Specific Integrated Circuits (ASICs), Field Programmable Gate Arrays (FPGAs) or other Programmable logic devices, discrete Gate or transistor logic devices, discrete hardware components.
In a further embodiment provided by the present invention, there is also provided a computer readable storage medium having stored therein a computer program which, when executed by a processor, implements the steps of any of the methods described above.
In a further embodiment provided by the present invention, there is also provided a computer program product containing instructions which, when run on a computer, cause the computer to perform any of the methods of the above embodiments.
In the above embodiments, the implementation may be wholly or partially realized by software, hardware, firmware, or any combination thereof. When implemented in software, may be implemented in whole or in part in the form of a computer program product. The computer program product includes one or more computer instructions. When loaded and executed on a computer, cause the processes or functions described in accordance with the embodiments of the invention to occur, in whole or in part. The computer may be a general purpose computer, a special purpose computer, a network of computers, or other programmable device. The computer instructions may be stored in a computer readable storage medium or transmitted from one computer readable storage medium to another, for example, from one website site, computer, server, or data center to another website site, computer, server, or data center via wired (e.g., coaxial cable, fiber optic, Digital Subscriber Line (DSL)) or wireless (e.g., infrared, wireless, microwave, etc.). The computer-readable storage medium can be any available medium that can be accessed by a computer or a data storage device, such as a server, a data center, etc., that incorporates one or more of the available media. The usable medium may be a magnetic medium (e.g., floppy Disk, hard Disk, magnetic tape), an optical medium (e.g., DVD), or a semiconductor medium (e.g., Solid State Disk (SSD)), among others.
It is noted that, in this document, 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 above description is merely exemplary of the present application and is presented to enable those skilled in the art to understand and practice the present application. Various modifications to these embodiments will be readily apparent to those skilled in the art, and the generic principles defined herein may be applied to other embodiments without departing from the spirit or scope of the application. Thus, the present application is not intended to be limited to the embodiments shown herein but is to be accorded the widest scope consistent with the principles and novel features disclosed herein.
Claims (10)
1.A query statement generation method, characterized in that the method comprises:
acquiring a comparator, a target configuration file of target data and a reference configuration file of reference data, wherein the target configuration file comprises a target field list and target configuration information, and the reference configuration file comprises a reference field list and reference configuration information;
determining a field corresponding relation between a target field in the target field list and a reference field with the same position as the target field in the reference field list, and converting the comparator into a comparison function through a preset conversion condition;
and generating a query statement according to the comparison function, the target configuration file and the reference configuration file through the field corresponding relation.
2. The method of claim 1, wherein generating a query statement according to the comparison function, the target configuration file, and the reference configuration file by using the field correspondence comprises:
and filling the comparison function, the data in the target configuration file and the data in the reference configuration file into corresponding positions in a preset template respectively through the field corresponding relation, a first mapping relation of the data identification and the position of the data identification in the preset template and a second mapping relation of the data identification and the data to generate a query statement.
3. The method of claim 2, wherein the target configuration information comprises a target database, a target data table, and a target primary key, and the reference configuration information comprises a reference database, a reference data table, and a reference primary key; the filling the comparison function, the data in the target configuration file and the data in the reference configuration file into corresponding positions in a preset template respectively according to the field corresponding relationship, the first mapping relationship between the data identifier and the position of the data identifier in the preset template and the second mapping relationship between the data identifier and the data comprises:
determining the field source positions of the identification of the target database, the identification of the target data table, the identification of the reference database and the identification of the reference data table in the preset template according to the first mapping relation, and filling the field source positions with the target database, the target data table, the reference database and the reference data table respectively according to the second mapping relation;
determining the field positions of the target field and the reference field in the preset template according to the field corresponding relation and the field source position, and filling the field positions with the target field and the reference field;
determining the positions of the main keys of the marks of the target main key and the reference main key in the preset template according to the first mapping relation, and filling the positions of the main keys with the target main key and the reference main key according to the second mapping relation;
and determining a data comparison position of the identifier of the comparison function in the preset template according to the first mapping relation, and filling the comparison function into the data comparison position according to the second mapping relation.
4. The method of claim 3, wherein the target field and the reference field each comprise a plurality of field data; after the generating the query statement, the method further comprises:
connecting the target data table and the reference data table through the target primary key and the reference primary key;
traversing the target field and the reference field as follows: acquiring target field data of a target field in the target data table and reference field data of a reference field in the reference data table, and adding 1 to a value of a recorded abnormal parameter which indicates that a preset condition is not met under the condition that the target field data and the reference field data do not meet the preset condition;
and outputting the value of the abnormal parameter.
5. The method of claim 4, wherein the target primary key and the reference primary key each comprise a plurality of sub-keys; the acquiring of the target field data of the target field in the target data table and the reference field data of the reference field in the reference data table includes:
determining a target sub-key of the target main key and a reference sub-key of the reference main key, wherein the target sub-key and the reference sub-key have a key correspondence relationship;
and determining target field data corresponding to the target sub-key in the target field and reference field data corresponding to the reference sub-key in the reference field.
6. The method according to claim 4 or 5, characterized in that the method further comprises:
acquiring a first quantity of all field data in the target field list and a second quantity of all field data in the reference field list;
and taking the sum of the first number and the second number as a total number, and outputting the total number.
7. An apparatus for generating a query statement, the apparatus comprising:
the device comprises an acquisition module, a comparison module and a comparison module, wherein the acquisition module is used for acquiring a target configuration file of target data and a reference configuration file of reference data, the target configuration file comprises a target field list and target configuration information, and the reference configuration file comprises a reference field list and reference configuration information;
a determining module, configured to determine a field correspondence between a target field in the target field list and a reference field in the reference field list, where the position of the reference field is the same as that of the target field, and convert the comparator into a comparison function through a preset conversion condition;
and the generation module is used for generating a query statement according to the comparison function, the target configuration file and the reference configuration file through the field corresponding relation.
8. The apparatus of claim 7, wherein the generating module comprises:
and the filling-in unit is used for respectively filling the comparison function, the data in the target configuration file and the data in the reference configuration file into corresponding positions in a preset template through the field corresponding relation, the first mapping relation of the positions of the data identification and the data identification in the preset template and the second mapping relation of the data identification and the data, so as to generate the query statement.
9. An electronic device is characterized by comprising a processor, a communication interface, a memory and a communication bus, wherein the processor and the communication interface are used for realizing mutual communication by the memory through the communication bus;
a memory for storing a computer program;
a processor for implementing the method steps of any of claims 1-6 when executing a program stored in the memory.
10. A computer-readable storage medium, characterized in that a computer program is stored in the computer-readable storage medium, which computer program, when being executed by a processor, carries out the method steps of any one of claims 1 to 6.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202010852477.8A CN111966707A (en) | 2020-08-21 | 2020-08-21 | Query statement generation method and device, electronic equipment and computer readable medium |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202010852477.8A CN111966707A (en) | 2020-08-21 | 2020-08-21 | Query statement generation method and device, electronic equipment and computer readable medium |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN111966707A true CN111966707A (en) | 2020-11-20 |
Family
ID=73391149
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202010852477.8A Pending CN111966707A (en) | 2020-08-21 | 2020-08-21 | Query statement generation method and device, electronic equipment and computer readable medium |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN111966707A (en) |
Cited By (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN113190218A (en) * | 2021-04-28 | 2021-07-30 | 中国邮政储蓄银行股份有限公司 | Target operation function generation method and device |
| CN113486022A (en) * | 2021-07-27 | 2021-10-08 | 中国银行股份有限公司 | Data consistency verification method and related device |
| CN113836212A (en) * | 2021-09-27 | 2021-12-24 | 易保网络技术(上海)有限公司 | Method for automatically generating Json data by database data, readable medium and electronic equipment |
| CN114661751A (en) * | 2022-03-22 | 2022-06-24 | 医渡云（北京）技术有限公司 | Data production method, device, system, equipment and medium based on SQL (structured query language) knowledge base |
| CN115062055A (en) * | 2022-06-21 | 2022-09-16 | 深圳华策辉弘科技有限公司 | Data table processing method, device, equipment and storage medium |
Citations (10)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN106570022A (en) * | 2015-10-10 | 2017-04-19 | 阿里巴巴集团控股有限公司 | Cross-data-source query method, apparatus and system |
| CN106649378A (en) * | 2015-11-02 | 2017-05-10 | 北大方正集团有限公司 | Data synchronizing method and device |
| CN107391739A (en) * | 2017-08-07 | 2017-11-24 | 北京奇艺世纪科技有限公司 | A kind of query statement generation method, device and electronic equipment |
| US20180095972A1 (en) * | 2016-10-04 | 2018-04-05 | Datavard Gmbh | Method and system for determining data usage behavior in a database system |
| CN109062952A (en) * | 2018-06-22 | 2018-12-21 | 北京奇艺世纪科技有限公司 | A kind of data query method, apparatus and electronic equipment |
| WO2019165691A1 (en) * | 2018-02-28 | 2019-09-06 | 平安科技（深圳）有限公司 | Method, apparatus and device for automatically generating test case, and readable storage medium |
| CN111090640A (en) * | 2019-11-13 | 2020-05-01 | 山东中磁视讯股份有限公司 | ETL data cleaning method and system |
| CN111177174A (en) * | 2018-11-09 | 2020-05-19 | 百度在线网络技术（北京）有限公司 | SQL statement generation method, device, equipment and computer readable storage medium |
| CN111177180A (en) * | 2019-12-11 | 2020-05-19 | 北京百分点信息科技有限公司 | Data query method and device and electronic equipment |
| CN111414377A (en) * | 2020-03-05 | 2020-07-14 | 微民保险代理有限公司 | Method and device for processing structured query statement and storage medium |
-
2020
- 2020-08-21 CN CN202010852477.8A patent/CN111966707A/en active Pending
Patent Citations (10)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN106570022A (en) * | 2015-10-10 | 2017-04-19 | 阿里巴巴集团控股有限公司 | Cross-data-source query method, apparatus and system |
| CN106649378A (en) * | 2015-11-02 | 2017-05-10 | 北大方正集团有限公司 | Data synchronizing method and device |
| US20180095972A1 (en) * | 2016-10-04 | 2018-04-05 | Datavard Gmbh | Method and system for determining data usage behavior in a database system |
| CN107391739A (en) * | 2017-08-07 | 2017-11-24 | 北京奇艺世纪科技有限公司 | A kind of query statement generation method, device and electronic equipment |
| WO2019165691A1 (en) * | 2018-02-28 | 2019-09-06 | 平安科技（深圳）有限公司 | Method, apparatus and device for automatically generating test case, and readable storage medium |
| CN109062952A (en) * | 2018-06-22 | 2018-12-21 | 北京奇艺世纪科技有限公司 | A kind of data query method, apparatus and electronic equipment |
| CN111177174A (en) * | 2018-11-09 | 2020-05-19 | 百度在线网络技术（北京）有限公司 | SQL statement generation method, device, equipment and computer readable storage medium |
| CN111090640A (en) * | 2019-11-13 | 2020-05-01 | 山东中磁视讯股份有限公司 | ETL data cleaning method and system |
| CN111177180A (en) * | 2019-12-11 | 2020-05-19 | 北京百分点信息科技有限公司 | Data query method and device and electronic equipment |
| CN111414377A (en) * | 2020-03-05 | 2020-07-14 | 微民保险代理有限公司 | Method and device for processing structured query statement and storage medium |
Cited By (6)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN113190218A (en) * | 2021-04-28 | 2021-07-30 | 中国邮政储蓄银行股份有限公司 | Target operation function generation method and device |
| CN113486022A (en) * | 2021-07-27 | 2021-10-08 | 中国银行股份有限公司 | Data consistency verification method and related device |
| CN113836212A (en) * | 2021-09-27 | 2021-12-24 | 易保网络技术(上海)有限公司 | Method for automatically generating Json data by database data, readable medium and electronic equipment |
| CN113836212B (en) * | 2021-09-27 | 2023-09-08 | 易保网络技术(上海)有限公司 | Method for automatically generating Json data by database data, readable medium and electronic equipment |
| CN114661751A (en) * | 2022-03-22 | 2022-06-24 | 医渡云（北京）技术有限公司 | Data production method, device, system, equipment and medium based on SQL (structured query language) knowledge base |
| CN115062055A (en) * | 2022-06-21 | 2022-09-16 | 深圳华策辉弘科技有限公司 | Data table processing method, device, equipment and storage medium |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN111966707A (en) | Query statement generation method and device, electronic equipment and computer readable medium | |
| CN107798038B (en) | Data response method and data response equipment | |
| US20070276970A1 (en) | Data Consistency Validation | |
| CN108958959B (en) | Method and device for detecting hive data table | |
| CN107168977A (en) | A kind of optimization method and device of data query | |
| CN108388640A (en) | A kind of data transfer device, device and data processing system | |
| CN112328631A (en) | Production fault analysis method and device, electronic equipment and storage medium | |
| CN115617773A (en) | Data migration method, device and system | |
| CN116719799A (en) | Environment-friendly data management method, device, computer equipment and storage medium | |
| CN112416904A (en) | Electric power data standardization processing method and device | |
| CN111949550A (en) | Method, device and equipment for automatically generating test data and storage medium | |
| CN109947797B (en) | Data inspection device and method | |
| CN111401023B (en) | Report generation method, report generation device, server and storage medium | |
| CN108196921B (en) | Document development method and device, computer equipment and storage medium | |
| CN112596703B (en) | Method, system and storage medium for realizing demand development inheritance | |
| CN116340883A (en) | Power distribution network data resource fusion method, device, equipment and storage medium | |
| CN115617623A (en) | Performance index data visualization method and device, electronic equipment and storage medium | |
| CN114860759A (en) | Data processing method, device and equipment and readable storage medium | |
| CN112287027A (en) | Database synchronization method, device, equipment and storage medium | |
| CN112559331A (en) | Test method and device | |
| CN112445918A (en) | Knowledge graph generation method and device, electronic equipment and storage medium | |
| CN116010349B (en) | Metadata-based data checking method and device, electronic equipment and storage medium | |
| CN100365626C (en) | Database optimizing method | |
| CN112395850B (en) | Method and device for checking data definition consistency and electronic equipment | |
| CN109344369B (en) | Certificate making method based on original value verification and terminal 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 |