CN110147396A

CN110147396A - A kind of mapping relations generation method and device

Info

Publication number: CN110147396A
Application number: CN201711091430.9A
Authority: CN
Inventors: 朱刘江
Original assignee: China Mobile Communications Group Co Ltd; China Mobile Suzhou Software Technology Co Ltd
Current assignee: China Mobile Communications Group Co Ltd; China Mobile Suzhou Software Technology Co Ltd
Priority date: 2017-11-08
Filing date: 2017-11-08
Publication date: 2019-08-20
Anticipated expiration: 2037-11-08
Also published as: CN110147396B

Abstract

The invention discloses a kind of mapping relations generation method and devices, to solve the problems, such as that the mapping relations accuracy generated in the prior art is lower.This method comprises: carrying out syntactic analysis to structured query language SQL program, the abstract syntax tree AST of the SQL program is constructed；AST described in recursive traversal, obtains the information for the SQL element that each recurrence obtains, and the information of the SQL element includes at least table name and field name；It is established according to the information of the SQL element got and saves first set；Wherein, i-th of key-value pair in the first set is used to save the information for the SQL element that jth time recurrence obtains, key in i-th of key-value pair includes at least the table name that the jth time recurrence obtains, and the value in i-th of key-value pair includes at least the field name that the jth time recurrence obtains, i=1,2, ... N, j=1,2, ... N, N are recurrence number；Mapping relations are generated according to the first set.

Description

A kind of mapping relations generation method and device

Technical field

The present invention relates to computer and database field more particularly to a kind of mapping relations generation method and devices.

Background technique

Data pick-up, conversion, loading (Extract-Transform-Load, ETL) technology make data warehouse (Data Warehouse, DW) core and soul, be responsible for process from data source to target data warehouse that convert from of data, i.e., from data source Required data are extracted, by data cleansing, finally according to the data warehouse model pre-defined, load data into number According in warehouse, wherein DW be used for the Transaction Processing of enterprise (On-Line Transaction Processing, OLTP) data that system generates are integrated, and are excavated commercial value therein, are provided decision support for enterprise.

In the ETL technology based on structured query language (Structured Query Language, SQL) script, It needs to parse SQL program to extract SQL element in SQL syntax book, be mapped according to the SQL Element generation table level extracted Relationship and field level mappings relationship.In the prior art, by suitable according to occurring to SQL program SQL element obtained after parsing Sequence number generates table level mapping relations and field level mappings relationship, SQL member according to the number of SQL element and respective rule The correctness of element number directly affects the correctness of mapping relations.

Therefore, table level mapping relations and field level are generated according to the number of SQL infrastructure elements object and respective rule Mapping relations are easy to cause mapping relations to malfunction, especially for complicated SQL program.By taking Connection inquiring as an example, it is assumed that Table A by Table B connect generation with table C, i.e. [B, C] → A, in abstract syntax tree (Abstract Syntax Tree, AST), table B and table C Sequence be first obtain table B, then obtain table C, i.e. B → C → A, so that table B and the number of table C are different, and then lead to basis Mistake occurs for the mapping relations that the number of table B and table C generates.

Summary of the invention

The embodiment of the present invention provides a kind of mapping relations generation method and device, to solve in the prior art according to SQL member The number of element generates the lower problem of mapping relations accuracy.

Specific technical solution provided in an embodiment of the present invention is as follows:

In a first aspect, the embodiment of the invention provides a kind of mapping relations generation methods, comprising:

Syntactic analysis is carried out to structured query language SQL program, constructs the abstract syntax tree AST of the SQL program；

AST described in recursive traversal obtains the information for the SQL element that each recurrence obtains, and the information of the SQL element is at least Including table name and field name；

It is established according to the information of the SQL element got and saves first set；Wherein, i-th in the first set A key-value pair is used to save the information for the SQL element that jth time recurrence obtains, and the key in i-th of key-value pair includes at least institute The table name that jth time recurrence obtains is stated, the value in i-th of key-value pair includes at least the field name that the jth time recurrence obtains, I=1,2 ... N, j=1,2 ... N, N are recurrence number, and i, j, N are positive integer；

Mapping relations are generated according to the first set.

Optionally, if the information for the SQL element that the jth time recurrence obtains further includes subquery name, table alias and/or word Section alias, then the key in i-th of key-value pair further includes the subquery name and/or table alias that the jth time recurrence obtains, institute Stating the value in i-th of key-value pair further includes the table alias and/or field alias that the jth time recurrence obtains.

Optionally, when the mapping relations are table level mapping relations, mapping relations, packet are generated according to the first set It includes:

The key in i-th of key-value pair is parsed, if in the key of i-th of key-value pair including subquery name or the first table The subquery name or the first table name that include in the key of i-th of key-value pair, then is determined as the key of second set by name, otherwise will The second table name is determined as the key of second set in the key of i-th of key-value pair；Wherein, first table name is the SQL journey The table name of the table created in sequence, second table name are the table name in addition to first table name for including, institute in the SQL program Second set is stated for saving the table level mapping relations；

The value in i-th of key-value pair is parsed, when in the value of i-th of key-value pair including table alias, according to institute The table alias inquiry third set for including in the value of i-th of key-value pair is stated, includes if finding in the value of i-th of key-value pair The corresponding table name of table alias for including in the value of i-th of key-value pair is then determined as described by the corresponding table name of table alias The corresponding value of key of two set, is otherwise determined as the second set for the table alias for including in the value of i-th of key-value pair The corresponding value of key, wherein the third set is used to save the corresponding relationship of table name and table alias in the SQL program；

When not including table alias in the value in i-th of key-value pair, will include in the key of i-th of key-value pair Second table name is determined as the corresponding value of key of the second set.

The key in i-th of key-value pair is parsed, if in the key of i-th of key-value pair including subquery name or the first table The subquery name or the first table name that include in the key of i-th of key-value pair, then is determined as the key of second set by name, otherwise will The second table name for including in the key of i-th of key-value pair is determined as the key of second set, and first table name is the SQL journey The table name of the table created in sequence, second table name are table name in addition to first table name in the SQL program, described the Two set are for saving the table level mapping relations；

If including the first table name in the key of i-th of key-value pair, the value in i-th of key-value pair is parsed, institute is worked as When stating in the value of i-th of key-value pair including table alias, third is inquired according to the table alias for including in the value of i-th of key-value pair Set, if the corresponding table name of table alias for including in the value of i-th of key-value pair is found, by i-th of key-value pair The corresponding table name of the table alias for including in value is determined as the corresponding value of key of the second set, otherwise by i-th of key assignments Pair value in include table alias be determined as the corresponding value of key of the second set, wherein the third set is for saving The corresponding relationship of table name and table alias in the SQL program；When not including the first table name in the key in i-th of key-value pair When, the second table name for including in the key of i-th of key-value pair is determined as to the corresponding value of key of the second set.

Optionally, when the mapping relations are field level mappings relationship, mapping relations are generated according to the first set, Include:

I-th of key-value pair is parsed, when in the value of i-th of key-value pair including table/alias, according to described i-th The table alias inquiry third set for including in the value of a key-value pair, if finding the table for including in the value of i-th of key-value pair The corresponding table name of alias, by the corresponding table name of table alias for including in the value of i-th of key-value pair be determined as source subquery name/ Otherwise the table alias for including in the value of i-th of key-value pair is determined as source and looks into sub- inquiry name/table name by table name；When described i-th When not including table alias in the value of a key-value pair, the second table name for including in the key of i-th of key-value pair is determined as source Query name/table name, wherein second table name is the table name in the SQL program in addition to the first table name, and first table name is The table name of the table created in the SQL program；

If the key in i-th of key-value pair includes subquery name or the first table name, by the key of i-th of key-value pair In include subquery name or the first table name be determined as the corresponding target subquery name/table name of the source subquery name/table name, it is no Then, the second table name for including in the key of i-th of key-value pair is determined as target subquery name/table name, first table name Table name for the table created in the SQL program；

The field name for including in the value of i-th of key-value pair is determined as source field name；

If in the value of i-th of key-value pair including field alias, the word that will include in the value of i-th of key-value pair Section alias is determined as the corresponding aiming field name of the source field name, otherwise, the word that will include in the value of i-th of key-value pair Section name is determined as the corresponding aiming field name of the source field name；

According to the target subquery name/table name, the aiming field name, the source subquery name/table name and described Source field name generates field level mappings relationship.

Optionally, it is inquired before third set according to the table alias for including in the value of i-th of key-value pair, further includes: The key of i-th of key-value pair is parsed, if the key of i-th of key-value pair includes table alias, by i-th of key-value pair The table alias for including in key is determined as the key in the third set, otherwise will include in the key of i-th of key-value pair Two table names are determined as the key in the third set；The second table name for including in the key of i-th of key-value pair is determined as institute The corresponding value of key in third set is stated, the third set is obtained.

Optionally, the key in i-th of key-value pair include the first field, the second field and third field, described first Field is used for for saving subquery name or the first table name, second field for saving the second table name, the third field Save table alias；Any one element includes the 4th field, the 5th field and the 6th field in the value of i-th of key-value pair, 4th field is for saving table alias, the 5th field for saving field name, and the 6th field is for saving word Section alias；

Wherein, if the jth time recurrence does not obtain subquery name and the first table name, the of i-th of key-value pair One field is indicated with the second table name that the jth time recurrence obtains；If the jth time recurrence does not obtain table alias, i-th of institute The table name table that the first setting character mark of the third field and the 4th field of key-value pair or the jth time recurrence obtain Show；If the jth time recurrence does not obtain field alias, the second setting character mark of the 6th field of i-th of key-value pair Know.

Second aspect, the embodiment of the invention provides a kind of for generating the device of mapping relations, comprising:

Syntax tree establishes module, for carrying out syntactic analysis to structured query language SQL program, constructs the SQL journey The abstract syntax tree AST of sequence；

Data obtaining module obtains the information for the SQL element that each recurrence obtains for AST described in recursive traversal, described The information of SQL element includes at least table name and field name；

Processing module, for establishing and save first set according to the information of SQL element got, and according to described First set generates mapping relations；Wherein, i-th of key-value pair in the first set is used to save what jth time recurrence obtained The information of SQL element, the key in i-th of key-value pair include at least the table name that the jth time recurrence obtains, and described i-th Value in key-value pair includes at least the field name that the jth time recurrence obtains, and i=1,2 ... N, j=1,2 ... N, N are recurrence time Number, i, j, N are positive integer.

Optionally, the processing module is specifically used for:

The key in i-th of key-value pair is parsed, if in the key of i-th of key-value pair including subquery name or the first table The subquery name or the first table name that include in the key of i-th of key-value pair, then is determined as the key of second set by name, otherwise will The second table name for including in the key of i-th of key-value pair is determined as the key of second set；Wherein, first table name is described The table name of the table created in SQL program, second table name are the table in addition to first table name for including in the SQL program Name, the second set is for saving the table level mapping relations；

Optionally, the processing module is specifically used for:

I-th of key-value pair is parsed, when in the value of i-th of key-value pair including table alias, according to described i-th The table alias inquiry third set for including in the value of key-value pair, if it is other to find the table for including in the value of i-th of key-value pair The corresponding table name of name, is determined as source subquery name/table for the corresponding table name of table alias for including in the value of i-th of key-value pair Name, otherwise, is determined as source for the table alias for including in the value of i-th of key-value pair and looks into sub- inquiry name/table name；When described i-th When not including table alias in the value of key-value pair, the second table name for including in the key of i-th of key-value pair is determined as source and is looked into Ask name/table name, wherein second table name is the table name in the SQL program in addition to the first table name, and first table name is institute State the table name of the table created in SQL program；

Optionally, the processing module is also used to:

Before inquiring third set according to the table alias for including in the value of i-th of key-value pair, i-th of key is parsed The key of value pair, is determined as the key in the third set for the second table alias for including in the key of i-th of key-value pair, by institute The corresponding value of key that the second table name for including in the key of i-th of key-value pair is determined as in the third set is stated, obtains described Three set.

Based on the above-mentioned technical proposal, in the embodiment of the present invention, syntactic analysis is carried out to SQL program, constructs the SQL program AST；The recursive traversal AST obtains the information for the SQL element that each recurrence obtains；It is built according to the information of the SQL element got Found and save first set, according to first set generate mapping relations, wherein the information of the SQL element include at least table name and Field name, i-th of key-value pair in first set are used to save the information for the SQL element that jth time recurrence obtains, i-th of key assignments The key of centering includes at least the table name that jth time recurrence obtains, and the value in i-th of key-value pair includes at least what jth time recurrence obtained Field name, i=1,2 ... N, j=1,2 ... N, N are recurrence number, and i, j, N are positive integer.Mapping provided in an embodiment of the present invention Relational approach does not need that SQL element is numbered, mapping relations entanglement caused by can be avoided because of SQL element number mistake Problem, and then achieve the purpose that the accuracy for improving mapping relations generated.

Detailed description of the invention

Fig. 1 is a kind of flow diagram of mapping relations generation method in the embodiment of the present invention；

Fig. 2 is the schematic diagram of table level mapping relations in the specific embodiment of the invention；

Fig. 3 is the schematic diagram of field level mappings relationship in the specific embodiment of the invention；

Fig. 4 is a kind of for generating the structural schematic diagram of the device of mapping relations in the embodiment of the present invention.

Specific embodiment

To make the objectives, technical solutions, and advantages of the present invention clearer, below in conjunction with attached drawing to the present invention make into One step it is described in detail.Based on the embodiments of the present invention, those of ordinary skill in the art are not before making creative work All other embodiment obtained is put, shall fall within the protection scope of the present invention.

Mapping relations generation method provided in an embodiment of the present invention is executed by the equipment with database access function, such as a People's computer, server etc., the Data Definition Language (Data Definition Language, DDL) suitable for SQL.

In the embodiment of the present invention, for the first table name by the table name of the table created in SQL program, the second table name is SQL program In table name in addition to the first table name.The SQL element that the AST tree that first set is used to save each recurrence SQL program obtains Information, for second set for saving table level mapping relations, third set is corresponding with table alias for saving table name in SQL program Relationship.

In addition, it is necessary to which explanation, multiple in the application description, refer to two or more.And/or description is closed Join the incidence relation of object, indicates may exist three kinds of relationships.For example, A and/or B, can indicate: individualism A is deposited simultaneously In A and B, these three situations of individualism B.Character "/" typicallys represent the relationship that forward-backward correlation object is a kind of "or"." the One ", the vocabulary such as " second " are only used for distinguishing description, are not understood to indicate or imply relative importance, can not be interpreted as Indication or suggestion sequence.

As shown in fig.1, mapping relations generation method provided in an embodiment of the present invention specifically includes the following steps:

S101: syntactic analysis is carried out to SQL program, constructs the AST of the SQL program.

Wherein, for the SQL program for creating new table, which created according to tables other in database.

S102: AST constructed by recursive traversal, obtain the information for the SQL element that each recurrence obtains.

Wherein, the information of the SQL element includes at least table name and field name, which includes sub- SQL, table and word Section etc..

S103: it is established according to the information of the SQL element got and saves first set.

Wherein, i-th of key-value pair in the first set is used to save the information for the SQL element that jth time recurrence obtains, should Key (key) in i-th of key-value pair includes at least the table name that jth time recurrence obtains, the value (value) in i-th of key-value pair Including at least the field name that jth time recurrence obtains, i=1,2 ... N, j=1,2 ... N, N are recurrence total degree, and i, j, N are positive whole Number, that is to say, that each key-value pair in the first set is respectively used to save the SQL element information that each recurrence obtains.It needs It is noted that the corresponding value of any one key in the first set, the corresponding value of any one key of the first set In may include one or more elements, in the first set key-value pair storage sequence, i.e. i and j unrelated with recursive order Can be identical, it can also be different.

In implementation, if the information for the SQL element that jth time recurrence obtains further includes that subquery name, table alias and/or field are other Name, then the key in i-th of key-value pair further includes the subquery name and/or table alias that jth time recurrence obtains, i-th of key assignments The value of centering further includes the table alias and/or field alias that jth time recurrence obtains.Specifically, as the SQL that jth time recurrence obtains The information of element further includes subquery name, then the key in i-th of key-value pair further includes the subquery name that jth time recurrence obtains； When the information for the SQL element that jth time recurrence obtains further includes table alias, then the key in i-th of key-value pair further includes that jth time is passed The table alias returned, at least one element in the value of i-th of key-value pair further include the table alias that jth time recurrence obtains； When the information for the SQL element that jth time recurrence obtains further includes field alias, then at least one of the value of i-th of key-value pair Element further includes the field alias that jth time recurrence obtains；When the information for the SQL element that jth time recurrence obtains further includes subquery Name and table alias, then the key in i-th of key-value pair further includes the subquery name and table alias that jth time recurrence obtains, this is i-th At least one element in the value of key-value pair further includes the table alias that jth time recurrence obtains；When the SQL member that jth time recurrence obtains The information of element further includes subquery name and field alias, then the key in i-th of key-value pair further includes the son that jth time recurrence obtains Query name, at least one element in the value of i-th of key-value pair further include the field alias that jth time recurrence obtains；When jth time The information for the SQL element that recurrence obtains further includes subquery name, table alias and field alias, then the key in i-th of key-value pair It further include the subquery name and table alias that jth time recurrence obtains, at least one element in the value of i-th of key-value pair further includes The table alias and field alias that jth time recurrence obtains.Wherein, subquery name is also a kind of table alias.

In one specific embodiment, the key in i-th of key-value pair includes the first field, the second field and third word Section, first field is for saving subquery name or the first table name, and second field is for saving the second table name, the third field For saving table alias；Any one element includes the 4th field, the 5th field and the 6th word in the value of i-th of key-value pair Section, for the 4th field for saving table alias, the 5th field for saving field name, the 6th field is other for saving field Name.Wherein, if jth time recurrence does not obtain subquery name and the first table name, described in the first field of i-th of key-value pair is used The table name that jth time recurrence obtains indicates；If jth time recurrence does not obtain table alias, the third field of i-th of key-value pair and The table name that the first setting character mark of 4th field or jth time recurrence obtain indicates；If it is other that jth time recurrence does not obtain field Name, then the 6th field of i-th of key-value pair sets character representation with second.The first setting character and the second setting character It may be the same or different, such as the first setting character and the second setting character can be " NONE ".

In implementation, the first field, the second field and third field that the key in i-th of key-value pair of the first set includes Between can be separated by separating identifier, the 4th field that the value in i-th of key-value pair of the first set includes, the 5th Field and the 6th field can be separated by separating identifier, identified when i-th of key-value pair to parse and extracted difference The content saved in field, wherein the separation identifier can be " # ", " " etc..

For example, packet enclosed tool the query name B, table name BILL_ of the SQL element that AST constructed by jth time recurrence is obtained MON, field name SUBS_ID, FEE_NOTAX and INC_NOTAX there is no table alias and field alias, then first set In the key of i-th of key-value pair be B $ BILL_MON $ BILL_MON, the value of i-th of key-value pair is NONE#SUBS_ID#NONE, NONE#FEE_NOTAX#NONE and NONE#INC_NOTAX#NONE.

S104: mapping relations are generated according to the first set.

Specifically, mapping relations are generated by parsing the key-value pair in the first set, wherein generate table level mapping relations Method mainly include following two:

Method one parses each key-value pair in the first set, generates the second collection for saving table level mapping relations It closes, wherein parse a key-value pair in available second set of a key-value pair in the first set.This method master Want the following steps are included:

Step 1a: parsing the key in i-th of key-value pair of the first set, if in the key of i-th of key-value pair including son The subquery name or the first table name that include in the key of i-th of key-value pair are then determined as second by query name or the first table name Otherwise the second table name for including in the key of i-th of key-value pair is determined as the key of second set by the key of set.

Step 2a: parsing the value in i-th of key-value pair, when in the value of i-th of key-value pair including table alias, according to The table alias inquiry third set for including in the value of i-th of key-value pair includes if finding in the value of i-th of key-value pair The corresponding table name of the table alias for including in the value of i-th of key-value pair is then determined as second collection by the corresponding table name of table alias The corresponding value of the key of conjunction, the key that the table alias for including in the value of i-th of key-value pair is otherwise determined as the second set are corresponding Value；When not including table alias in the value in i-th of key-value pair, the second table that will include in the key of i-th of key-value pair Name is determined as the corresponding value of key of the second set.

For example, when the key of i-th of key-value pair in the first set is B $ BILL_MON $ BILL_MON, i-th of key-value pair Value be NONE#SUBS_ID#NONE, NONE#FEE_NOTAX#NONE and NONE#INC_NOTAX#NONE when, parse i-th The key of key-value pair determines in the key of i-th of key-value pair to include subquery name, by the key neutron interrogation name B of i-th of key-value pair As the key in the second set, the value of i-th of key-value pair is parsed, determines in the value of i-th of key-value pair not include table alias, Using the table name BILL_MON in the key of i-th of key-value pair as the corresponding value of the key in the second set.

In implementation, if including multiple elements in the value of i-th of key-value pair, to every in the value of i-th of key-value pair A element executes above-mentioned steps 2a, will execute what above-mentioned steps 2a was obtained for all elements in the value of i-th of key-value pair As a result, the corresponding value of key as the second set according to determined by the key in i-th of key-value pair.

Method two parses each key in the first set and the corresponding value of the key including the first table name, generation are used for Save the second set of table level mapping relations.This method mainly comprises the steps that

Step 1b: parsing the key in i-th of key-value pair of the first set, if in the key of i-th of key-value pair including son The subquery name for including in the key of i-th of key-value pair or the first table name are then determined as the second collection by query name or the first table name Otherwise the second table name for including in the key of i-th of key-value pair is determined as the key of second set by the key of conjunction.

Step 2b: if including the first table name in the key of i-th of key-value pair, parsing the value in i-th of key-value pair, When in the value of i-th of key-value pair including table alias, third is inquired according to the table alias for including in the value of i-th of key-value pair Set will be in the value of i-th of key-value pair if finding the corresponding table name of table alias for including in the value of i-th of key-value pair Including the corresponding table name of table alias be determined as the corresponding value of key of the second set, otherwise will be in the value of i-th of key-value pair Including table alias be determined as the corresponding value of key of the second set；When not including the first table in the key in i-th of key-value pair When name, the second table name for including in the key of i-th of key-value pair is determined as to the corresponding value of key of the second set.

It if including the first table name, and include more in the value of i-th of key-value pair in the key of i-th of key-value pair in implementation A element executes above-mentioned steps 2b then to each element in the value of i-th of key-value pair, will be directed to the value of i-th of key-value pair In all elements execute above-mentioned steps 2b obtain as a result, as second according to determined by the key in i-th of key-value pair The corresponding value of the key of set.

For example, when the key of i-th of key-value pair in the first set is B $ BILL_MON $ BILL_MON, i-th of key-value pair Value be NONE#SUBS_ID#NONE, NONE#FEE_NOTAX#NONE and NONE#INC_NOTAX#NONE when, parse i-th The key of key-value pair determines do not include the first table name in the key of i-th of key-value pair, by the key neutron interrogation of i-th of key-value pair B is as the key in the second set for name, using the table name BILL_MON in the key of i-th of key-value pair as in the second set The corresponding value of key.For another example, the key of i-th of key-value pair in the first set is DM_SUM $ CUST_TO_MON $ CUST_TO_ MON, the value of i-th of key-value pair are A#SUBS_ID#NONE, B#FEE_NOTAX#CNSM_MON_NOTAX, and wherein DM_SUM is the One table name, CUST_TO_MON are table name, and A, B are table alias, and SUBS_ID, FEE_NOTAX are field name, CNSM_MON_NOTAX For field alias, parse the key of i-th of key-value pair, determine to include the first table name in the key of i-th of key-value pair, parse this i-th The value of a key-value pair, determine in the value of i-th of key-value pair include table alias, according in the value of i-th of key-value pair include table Alias A, B inquire third set, do not inquire the corresponding table name of table alias A, B, then the table in the value of i-th of key-value pair is other A, B are as the corresponding value of key in the second set for name.

It should be noted that the corresponding value of any one key in the second set, the second set it is any one The corresponding value of a key may include one or more elements.

, can be using the first table name as entrance after generating the second set for saving field level mappings relationship in implementation, root According to the second set, table level mapping relations are inversely obtained, and render in page-imagesization and show the table level mapping relations.

Specifically, by parse each key-value pair in the first set generate field level mappings relationship, mainly include with Lower step:

Step 1c: i-th of key-value pair of parsing, when in the value of i-th of key-value pair including table alias, according to i-th of key assignments Pair value in include table alias inquiry third set, if it is corresponding to find the table alias for including in the value of i-th of key-value pair The corresponding table name of the table alias for including in the value of i-th of key-value pair is determined as source subquery name/table name by table name, otherwise, by The table alias for including in the value of i key-value pair is determined as source and looks into sub- inquiry name/table name；When not including table in the value of i-th of key-value pair When alias, the second table name for including in the key of i-th of key-value pair is determined as source subquery name/table name.

Step 2c: if the key in i-th of key-value pair includes subquery name or the first table name, by the key of i-th of key-value pair In include subquery name or the first table name be determined as determined by source subquery name/corresponding target subquery name/table of table name Name, otherwise, is determined as target subquery name/table name for the second table name for including in the key of i-th of key-value pair.

Step 3c: the field name for including in the value of i-th of key-value pair is determined as source field name.

Step 4c: if in the value of i-th of key-value pair including field alias, the word that will include in the value of i-th of key-value pair Section alias is determined as the corresponding aiming field name of the source field name, otherwise, the field name that will include in the value of i-th of key-value pair The corresponding aiming field name of source field name determined by being determined as.

Step 5c: according to identified target subquery name/table name, aiming field name, source subquery name/table name and source Field name generates field level mappings relationship.

In implementation, if in the value of i-th of key-value pair including multiple elements, to each in the value of i-th of key-value pair Element executes above-mentioned steps 1c-4c, and the corresponding field mapping relations of an element in the value of i-th of key-value pair generate Field level mappings relationship be made of the corresponding field mapping relations of all elements in the value of the first set.

It should be noted that the key and value of i-th of key-value pair can be parsed in the embodiment of the present invention respectively, it can also be same When parsing i-th key-value pair key and value.In addition, the embodiment of the present invention is not sub to source subquery name/table name, target is determined The sequencing of query name/table name, source field name and aiming field name limits.

In implementation, during generating mapping relations, inquired according to the table alias for including in the value of i-th of key-value pair Before third set, the third set also is established by following steps: parsing the key of i-th of key-value pair, if i-th of key assignments Pair key include table alias, then the table alias for including in the key of i-th of key-value pair is determined as the key in the third set, Otherwise the key the second table name for including in the key of i-th of key-value pair being determined as in the third set；By i-th of key-value pair Key in include the second table name be determined as the corresponding value of key in the third set, obtain the third set.

In conclusion carrying out syntactic analysis in the embodiment of the present invention to SQL program, constructing the AST of the SQL program；Recurrence The AST is traversed, the information for the SQL element that each recurrence obtains is obtained；It establishes and saves according to the information of the SQL element got First set generates mapping relations according to first set, wherein and the information of the SQL element includes at least table name and field name, the I-th of key-value pair in one set is used to save the information for the SQL element that jth time recurrence obtains, and the key in i-th of key-value pair is extremely It less include the table name that jth time recurrence obtains, the value in i-th of key-value pair includes at least the field name that jth time recurrence obtains, i= 1,2 ... N, j=1,2 ... N, N is recurrence number, and i, j, N are positive integer.Mapping relations method provided in an embodiment of the present invention is not It needs that SQL element is numbered, caused by can be avoided because of SQL element number mistake the problem of mapping relations entanglement, in turn Achieve the purpose that the accuracy for improving mapping relations generated.

Below by way of a specific embodiment, mapping relations generation method proposed by the present invention is described in detail.

The SQL program to be parsed is as follows:

Generate the mapping relations of above-mentioned SQL program, comprising the following steps:

1, according to SQL syntax rule, syntactic analysis, morphological analysis is carried out to above-mentioned SQL program, construct above-mentioned SQL program AST.

2, AST syntax tree constructed by recursive traversal obtains the SQL information that each recurrence obtains.

Wherein, the SQL information got, can also be other including subquery name, table alias, field in addition to table name and field name Name and connection (join) information.

3, it according to the SQL information got, establishes and saves first set, wherein first set is as shown in table 1:

1 first set of table

Wherein, the key in first set is by the from sentence in parsing SQL program or to build what table statement obtained, the Value in one set is obtained by the select sentence in parsing SQL program.Key's includes first in first set Field, the second field and third field, the first field is for saving subquery name or the first table name, and the second field is for saving the Two table names, third field are separated between different field with separation identifier, i.e., key in first set for saving table alias Format is subquery name/first table name $ the second table name $ table alias；Any one element includes the in value in first set Four fields, the 5th field and the 6th field, the 4th field is for saving table alias, and the 5th field is for saving field name, and the 6th Field is separated between different field with separation identifier, i.e. the member for including in the value of first set for saving field alias The format of element is table alias # field name # field alias.If certain recurrence does not obtain subquery name or the first table name, with this time The second table name that recurrence obtains indicates subquery name/first table name in the first field, if certain recurrence does not obtain table alias, The table alias in the second table name or setting the second field of character representation and the 4th field obtained with the secondary recurrence, if certain is passed Gui Wei obtains field alias, then the field alias in the 6th field is replaced with setting character.

4, each key in first set is parsed, the second table name in first set key is determined as in third set Table alias in first set key is determined as the value in third set by key, obtains third set as shown in Table 2.

2 third set of table

Wherein, when parsing DM_SUM $ CUST_TO_MON $ this key of CUST_TO_MON in first set, third collection is obtained Key is CUST_TO_MON, value CUST_TO_MON in key-value pair in conjunction, and parses A $ CUST_TO_ in first set When MON $ this key of TMP, obtaining key in the key-value pair in third set is CUST_TO_MON, value TMP, i.e. CUST_ The different entitled TMP of TO_MON, therefore, by this key of DM_SUM $ CUST_TO_MON $ CUST_TO_MON in parsing first set Value in the key-value pair of obtained third set replaces with TMP, obtains result as shown in Table 2.

5, according to first set and third set, table level mapping relations and field level mappings relationship are generated.

(a) table level mapping relations are generated

For the key DM_SUM $ CUST_TO_MON $ CUST_TO_MON in first set, due to including the in the key One table name DM_SUM, therefore the first table name DM_SUM is determined as to the key of the second concentration, the key DM_SUM $ in first set Include table alias A, B in the corresponding value of CUST_TO_MON $ CUST_TO_MON, third set inquired according to table alias A, B, The corresponding table name of table alias A, B is not found, therefore it is corresponding that table alias A, B be determined as key DM_SUM in third set Value；For the key B $ BILL_MON $ BILL_MON in first set, subquery name B is determined as the key in second set, Due to not including table alias in the corresponding value of key B $ BILL_MON $ BILL_MON in first set, by the first collection It is corresponding that the table name BILL_MON in key B $ BILL_MON $ BILL_MON in conjunction is determined as key B in second set value；For the key A $ CUST_TO_MON $ TMP in first set, subquery name A is determined as the key in second set, Due to including table alias TMP in the corresponding value of key A $ CUST_TO_MON $ TMP in first set, according to table alias TMP Third set is inquired, the corresponding table name CUST_TO_MON of table alias TMP is obtained, table name CUST_TO_MON is determined as the second collection The corresponding value of key A in conjunction, the results are shown in Table 3 for obtained second set:

3 second set of table

Table level mapping as shown in Figure 2 can be inversely released using the first table name DM_SUM as entrance according to second set Relationship.

(b) field level mappings relationship is generated

It, will be in key DM_SUM $ CUST_TO_MON $ CUST_TO_MON for first key-value pair in first set First table name DM_SUM is determined as target subquery name/table name；For key DM_SUM $ CUST_TO_MON $ CUST_TO_MON Corresponding value A#SUBS_ID#NONE is looked into due to including table alias A in A#SUBS_ID#NONE according to table alias A Third set is ask, does not inquire the corresponding table name of table alias A, therefore table alias A is determined as source subquery name/table name；Due to Do not include field alias in value A#SUBS_ID#NONE, therefore the field name in value A#SUBS_ID#NONE is determined For source field name and aiming field name, by identified target subquery name/table name DM_SUM, source subquery name/table name A, source Field name SUBS_ID and aiming field name SUBS_ID are determined as a field mapping relations.For key DM_SUM $ CUST_ The corresponding value B#FEE_NOTAX#CNSM_MON_NOTAX of TO_MON $ CUST_TO_MON, due to B#FEE_NOTAX# Include table alias B in CNSM_MON_NOTAX, therefore third set is inquired according to table alias B, it is corresponding not inquire table alias B Table name, therefore table alias B is determined as source subquery name/table name, by the field in B#FEE_NOTAX#CNSM_MON_NOTAX Name FEE_NOTAX is determined as source field name, due to including field alias CNSM_ in B#FEE_NOTAX#CNSM_MON_NOTAX Field alias CNSM_MON_NOTAX is determined as aiming field name by MON_NOTAX, by identified target subquery name/table Name DM_SUM, source subquery name/table name B, source field name FEE_NOTAX and aiming field name CNSM_MON_NOTAX are determined as one A field mapping relations.Similarly, value B#INC_ corresponding for key DM_SUM $ CUST_TO_MON $ CUST_TO_MON NOTAX#NB_CNSM_MON_NOTAX, the source subquery name/table name determined are B, the entitled INC_NOTAX of source field, target word Duan Mingwei NB_CNSM_MON_NOTAX, by identified target subquery name/table name DM_SUM, source subquery name/table name B, source Field name INC_NOTAX and aiming field name NB_CNSM_MON_NOTAX are determined as a field mapping relations.

It is for second key-value pair in first set, the subquery name B in key B $ BILL_MON $ BILL_MON is true It is set to target subquery name/table name；Value NONE#SUBS_ID# corresponding for key B $ BILL_MON $ BILL_MON NONE, due to not including table alias in value NONE#SUBS_ID#NONE, by key B $ BILL_MON $ BILL_MON In table name BILL_MON be determined as source subquery name/table name；Due to not including field in value NONE#SUBS_ID#NONE Alias, therefore the field name SUBS_ID in NONE#SUBS_ID#NONE is determined as source field name and aiming field name；It will Identified target subquery name/table name B, source subquery name/table name BILL_MON, source field name SUBS_ID and target word Section name SUBS_ID is determined as a field mapping relations.Similarly, value corresponding for key B $ BILL_MON $ BILL_MON NONE#FEE_NOTAX#NONE, the target subquery name/table name B determined, source subquery name/table name are BILL_MON, source word Duan Mingwei FEE_NOTAX, the entitled FEE_NOTAX of aiming field, by identified target subquery name/table name B, source subquery Name/table name BILL_MON, source field name FEE_NOTAX and aiming field name FEE_NOTAX are determined as a field mapping and close System；Value NONE#INC_NOTAX#NONE corresponding for key B $ BILL_MON $ BILL_MON, target determined Query name/table name B, source subquery name/table name are BILL_MON, the entitled FEE_NOTAX of source field, the entitled FEE_ of aiming field NOTAX, by identified target subquery name/table name B, source subquery name/table name BILL_MON, source field name INC_NOTAX And aiming field name INC_NOTAX is determined as a field mapping relations.

For the third key-value pair in first set, the subquery name A in key A $ CUST_TO_MON $ TMP is determined For target subquery name/table name；Due to being wrapped in the corresponding value TMP#SUBS_ID#NONE of key A $ CUST_TO_MON $ TMP Table alias TMP is included, therefore third set is inquired according to table alias TMP, obtains the corresponding table name CUST_TO_MON of table alias TMP, Table name CUST_TO_MON is determined as source subquery name/table name；Due to not including field in value TMP#SUBS_ID#NONE Alias, therefore the field name SUBS_ID in value TMP#SUBS_ID#NONE is determined as source field name and aiming field Name；The target subquery name/table name A that will be determined, source subquery name/table name CUST_TO_MON, source field name SUBS_ID with And aiming field name SUBS_ID is determined as a field mapping relations.

The field mapping relations determined according to each key-value pair in first set are stored according to the format of setting In 4th set, field level mappings relationship is obtained.Wherein, the format of each field mapping relations can for target subquery name/ The source table name $ aiming field name $ subquery name/table name $ source field name, the results are shown in Table 4 for the 4th set at this time:

Table 4 the 4th is gathered

4th set
	B$SUBS_ID$BILL_MON$SUBS_ID
B$FEE_NOTAX$BILL_MON$FEE_NOTAX
	B$INC_NOTAX$BILL_MON$INC_NOTAX
DM_SUM$SUBS_ID$A$SUBS_ID
	DM_SUM$CNSM_MON_NOTAX$B$FEE_NOTAX
DM_SUM$NB_CNSM_MON_NOTAX$B$INC_NOTAX
	A$SUBS_ID$CUST_TO_MON$SUBS_ID

After obtaining the 4th set, the field level mappings relationship saved in the 4th set can be obtained using the first table name as entrance, And render and show in page-imagesization, as shown in Figure 3.

Based on above embodiments, the embodiment of the present application also provides a kind of for generating the device of mapping relations, to realize Mapping relations generation method as shown in Figure 1, as shown in fig.4, including: that syntax tree establishes module 401, letter in the device 400 Breath obtains module 402 and processing module 403.

Syntax tree establishes module 401, for carrying out syntactic analysis to structured query language SQL program, constructs the SQL The abstract syntax tree AST of program；

Data obtaining module 402 obtains the information for the SQL element that each recurrence obtains for AST described in recursive traversal, The information of the SQL element includes at least table name and field name；

Processing module 403, for establishing and save first set according to the information of SQL element got, and according to The first set generates mapping relations；Wherein, i-th of key-value pair in the first set is for saving jth time recursive call The information of the SQL element arrived, the key in i-th of key-value pair include at least the table name that the jth time recurrence obtains, and described the Value in i key-value pair includes at least the field name that the jth time recurrence obtains, and i=1,2 ... N, j=1,2 ... N, N are to pass Return number, i, j, N are positive integer.

Optionally, the processing module 403 is specifically used for:

Optionally, the processing module 403 is also used to:

It should be understood by those skilled in the art that, the embodiment of the present invention can provide as method, system or computer program Product.Therefore, complete hardware embodiment, complete software embodiment or reality combining software and hardware aspects can be used in the present invention Apply the form of example.Moreover, it wherein includes the computer of computer usable program code that the present invention, which can be used in one or more, The shape for the computer program product implemented in usable storage medium (including but not limited to magnetic disk storage and optical memory etc.) Formula.

The present invention be referring to according to the method for the embodiment of the present invention, the process of equipment (system) and computer program product Figure and/or block diagram describe.It should be understood that every one stream in flowchart and/or the block diagram can be realized by computer program instructions The combination of process and/or box in journey and/or box and flowchart and/or the block diagram.It can provide these computer programs Instruct the processor of general purpose computer, special purpose computer, Embedded Processor or other programmable data processing devices to produce A raw machine, so that being generated by the instruction that computer or the processor of other programmable data processing devices execute for real The device for the function of being specified in present one or more flows of the flowchart and/or one or more blocks of the block diagram.

These computer program instructions, which may also be stored in, is able to guide computer or other programmable data processing devices with spy Determine in the computer-readable memory that mode works, so that it includes referring to that instruction stored in the computer readable memory, which generates, Enable the manufacture of device, the command device realize in one box of one or more flows of the flowchart and/or block diagram or The function of being specified in multiple boxes.

These computer program instructions also can be loaded onto a computer or other programmable data processing device, so that counting Series of operation steps are executed on calculation machine or other programmable devices to generate computer implemented processing, thus in computer or The instruction executed on other programmable devices is provided for realizing in one or more flows of the flowchart and/or block diagram one The step of function of being specified in a box or multiple boxes.

Obviously, various changes and modifications can be made to the invention without departing from essence of the invention by those skilled in the art Mind and range.In this way, if these modifications and changes of the present invention belongs to the range of the claims in the present invention and its equivalent technologies Within, then the present invention is also intended to include these modifications and variations.

Claims

1. a kind of mapping relations generation method characterized by comprising

AST described in recursive traversal, obtains the information for the SQL element that each recurrence obtains, and the information of the SQL element includes at least Table name and field name；

It is established according to the information of the SQL element got and saves first set；Wherein, i-th of key in the first set For value to the information for saving the SQL element that jth time recurrence obtains, the key in i-th of key-value pair includes at least the jth The table name that secondary recurrence obtains, the value in i-th of key-value pair include at least the field name that the jth time recurrence obtains, i=1, 2 ... N, j=1,2 ... N, N are recurrence number, and i, j, N are positive integer；

Mapping relations are generated according to the first set.

2. the method as described in claim 1, which is characterized in that if the information for the SQL element that the jth time recurrence obtains also is wrapped Enclosed tool query name, table alias and/or field alias, then the key in i-th of key-value pair further includes that the jth time recurrence obtains Subquery name and/or table alias, the value in i-th of key-value pair further include the table alias that the jth time recurrence obtains and/ Or field alias.

3. method according to claim 2, which is characterized in that when the mapping relations are table level mapping relations, according to institute It states first set and generates mapping relations, comprising:

The key in i-th of key-value pair is parsed, if in the key of i-th of key-value pair including subquery name or the first table name, The subquery name or the first table name that include in the key of i-th of key-value pair are then determined as to the key of second set, otherwise by institute State the key that the second table name in the key of i-th of key-value pair is determined as second set；Wherein, first table name is the SQL program The table name of the table of middle creation, second table name is the table name in addition to first table name for including in the SQL program, described Second set is for saving the table level mapping relations；

The value in i-th of key-value pair is parsed, when in the value of i-th of key-value pair including table alias, according to described i-th The table alias inquiry third set for including in the value of a key-value pair, if it is other to find the table for including in the value of i-th of key-value pair The corresponding table name of table alias for including in the value of i-th of key-value pair is then determined as second collection by the corresponding table name of name Otherwise the table alias for including in the value of i-th of key-value pair is determined as the key of the second set by the corresponding value of the key of conjunction Corresponding value, wherein the third set is used to save the corresponding relationship of table name and table alias in the SQL program；

4. method according to claim 2, which is characterized in that when the mapping relations are table level mapping relations, according to institute It states first set and generates mapping relations, comprising:

The key in i-th of key-value pair is parsed, if in the key of i-th of key-value pair including subquery name or the first table name, The subquery name or the first table name that include in the key of i-th of key-value pair are then determined as to the key of second set, otherwise by institute The key that the second table name for including in the key of i-th of key-value pair is determined as second set is stated, first table name is the SQL program The table name of middle created table, second table name are table name in addition to first table name in the SQL program, described second Set is for saving the table level mapping relations；

If including the first table name in the key of i-th of key-value pair, the value in i-th of key-value pair is parsed, when described i-th When including table alias in the value of a key-value pair, third set is inquired according to the table alias for including in the value of i-th of key-value pair, If finding the corresponding table name of table alias for including in the value of i-th of key-value pair, will be wrapped in the value of i-th of key-value pair The corresponding table name of the table alias included is determined as the corresponding value of key of the second set, otherwise by the value of i-th of key-value pair In include table alias be determined as the corresponding value of key of the second set, wherein the third set is for saving the SQL The corresponding relationship of table name and table alias in program；It, will be described when not including the first table name in the key in i-th of key-value pair The second table name for including in the key of i-th of key-value pair is determined as the corresponding value of key of the second set.

5. method according to claim 2, which is characterized in that when the mapping relations are field level mappings relationship, according to The first set generates mapping relations, comprising:

I-th of key-value pair is parsed, when in the value of i-th of key-value pair including table alias, according to i-th of key assignments Pair value in include table alias inquiry third set, if finding the table alias pair for including in the value of i-th of key-value pair The corresponding table name of table alias for including in the value of i-th of key-value pair is determined as source subquery name/table name by the table name answered, Otherwise, the table alias for including in the value of i-th of key-value pair is determined as source and looks into sub- inquiry name/table name；When i-th of key assignments Pair value in when not including table alias, by the second table name for including in the key of i-th of key-value pair be determined as source subquery name/ Table name, wherein second table name is the table name in the SQL program in addition to the first table name, and first table name is the SQL The table name of the table created in program；

If the key in i-th of key-value pair includes subquery name or the first table name, will be wrapped in the key of i-th of key-value pair The subquery name or the first table name included is determined as the corresponding target subquery name/table name of the source subquery name/table name, otherwise, The second table name for including in the key of i-th of key-value pair is determined as target subquery name/table name；

It is if in the value of i-th of key-value pair including field alias, the field for including in the value of i-th of key-value pair is other Name is determined as the corresponding aiming field name of the source field name, otherwise, the field name that will include in the value of i-th of key-value pair It is determined as the corresponding aiming field name of the source field name；

According to the target subquery name/table name, the aiming field name, the source subquery name/table name and the source word Section name, generates field level mappings relationship.

6. the method as described in claim 3-5 any one, which is characterized in that wrapped according in the value of i-th of key-value pair Before the table alias inquiry third set included, further includes:

The key of i-th of key-value pair is parsed, if the key of i-th of key-value pair includes table alias, by i-th of key assignments Pair key in include table alias be determined as key in the third set, otherwise will include in the key of i-th of key-value pair The second table name be determined as the key in the third set；The second table name for including in the key of i-th of key-value pair is determined For the corresponding value of key in the third set, the third set is obtained.

7. the method as described in claim 1-5 any one, which is characterized in that key in i-th of key-value pair includes the One field, the second field and third field, first field is for saving subquery name or the first table name, second field For saving the second table name, the third field is in the SQL program except described for saving table alias, second table name Table name outside first table name；

In the value of i-th of key-value pair any one element include the 4th field, the 5th field and the 6th field, the described 4th Field is for saving table alias, the 5th field for saving field name, and the 6th field is for saving field alias；

Wherein, if the jth time recurrence does not obtain subquery name and the first table name, the first word of i-th of key-value pair Section is indicated with the second table name that the jth time recurrence obtains；If the jth time recurrence does not obtain table alias, i-th of key assignments Pair third field and the 4th field indicated with the table name that the first setting character mark or the jth time recurrence obtain；If The jth time recurrence does not obtain field alias, then the second setting character representation of the 6th field of i-th of key-value pair.

8. a kind of for generating the device of mapping relations characterized by comprising

Syntax tree establishes module, for carrying out syntactic analysis to structured query language SQL program, constructs the SQL program Abstract syntax tree AST；

Data obtaining module obtains the information for the SQL element that each recurrence obtains, the SQL for AST described in recursive traversal The information of element includes at least table name and field name；

Processing module, for establishing and save first set according to the information of SQL element got, and according to described first Set generates mapping relations；Wherein, i-th of key-value pair in the first set is used to save the SQL member that jth time recurrence obtains The information of element, the key in i-th of key-value pair include at least the table name that the jth time recurrence obtains, i-th of key-value pair In value include at least the field name that the jth time recurrence obtains, i=1,2 ... N, j=1,2 ... N, N are recurrence number, i, J, N is positive integer.

9. device as claimed in claim 8, which is characterized in that if the information for the SQL element that the jth time recurrence obtains also is wrapped Enclosed tool query name, table alias and/or field alias, then the key in i-th of key-value pair further includes that the jth time recurrence obtains Subquery name and/or table alias, the value in i-th of key-value pair further include the table alias that the jth time recurrence obtains and/ Or field alias.

10. device as claimed in claim 9, which is characterized in that the processing module is specifically used for:

The key in i-th of key-value pair is parsed, if in the key of i-th of key-value pair including subquery name or the first table name, The subquery name or the first table name that include in the key of i-th of key-value pair are then determined as to the key of second set, otherwise by institute State the key that the second table name for including in the key of i-th of key-value pair is determined as second set；Wherein, first table name is described The table name of the table created in SQL program, second table name are the table in addition to first table name for including in the SQL program Name, the second set is for saving the table level mapping relations；

11. device as claimed in claim 9, which is characterized in that the processing module is specifically used for:

12. device as claimed in claim 9, which is characterized in that the processing module is specifically used for:

13. the device as described in claim 10-12 any one, which is characterized in that the processing module is also used to:

Before inquiring third set according to the table alias for including in the value of i-th of key-value pair, i-th of key-value pair is parsed Key, it is if the key of i-th of key-value pair includes table alias, the table alias for including in the key of i-th of key-value pair is true Otherwise the second table name for including in the key of i-th of key-value pair is determined as described by the key being set in the third set Key in three set；The second table name for including in the key of i-th of key-value pair is determined as the key pair in the third set The value answered obtains the third set.