Data model generation method and device

Abstract

The present invention provides a method and device for generating a data model, extracting characteristic fields of a target indicator and a target dimension from a first SQL statement input by a user, determining a replaceable indicator of the target indicator from a full feature library, wherein the full feature library stores characteristic fields of indicators and dimensions of SQL statements in a data warehouse, reorganizing a second SQL statement according to the replaceable indicator and the target dimension, and outputting the second SQL statement. The reorganized second SQL statement is a replaceable statement of the first SQL statement, which can meet the query requirements of the user. The method can automatically extract the dimensions and indicators required for this processing from the characteristic fields of the existing SQL statements, reorganize the SQL statements using the existing dimensions and indicators, complete the development of the data model, improve the utilization rate of the existing data model, and avoid the waste of resources caused by repeated development.

Description

Technical Field

[0001] The present invention relates to the field of big data technology, and in particular to a method and device for generating a data model. Background Art

[0002] The purpose of a data warehouse (DW) is to study and solve the problem of obtaining information from a database. The data in a data warehouse is obtained by extracting and cleaning data from the original scattered database, and then processing, summarizing and organizing it. The data in a data warehouse is mainly used for enterprise decision-making analysis. The data operations involved are mainly data queries. After entering the data warehouse, the data will be retained for a long time, but modification and deletion operations are rare.

[0003] The data in the data warehouse is usually stored in the form of data models (also called data tables). The establishment of data models is the difficulty and key to data development. In the existing technology, developers usually manually investigate whether the indicators and dimensions of the development data model exist, and then develop the data by manually writing scripts based on the investigation results.

[0004] However, the development efficiency of existing data models is low, and repeated development leads to a large amount of data redundancy. Summary of the invention

[0005] The present invention provides a method and device for generating a data model, which improves the utilization rate of the existing data model and avoids the waste of resources caused by repeated development.

[0006] In a first aspect, the present invention provides a method for generating a data model, comprising:

[0007] Extracting characteristic fields of target indicators and target dimensions from a first structured query language SQL statement input by a user;

[0008] Determine a replaceable indicator of the target indicator from the full feature library, wherein the full feature library stores the indicator and dimension feature fields of the SQL statement in the data warehouse, and the indicator and dimension feature fields include type, globally unique field name, and one or more of the following fields: field name, field table, source table path, source field path, filter condition, and calculation logic;

[0009] A second SQL statement is obtained by reorganizing according to the replaceable indicator and the target dimension, and the second SQL statement is output, where the second SQL statement is a replaceable statement of the first SQL statement.

[0010] Optionally, determining a replaceable indicator of the target indicator from the full feature library includes:

[0011] For each target indicator, obtain a globally unique name field of the target indicator;

[0012] Querying all indicators with the same global unique name field as the target indicator from the full feature library to form a first candidate indicator set;

[0013] According to the source table path of the target indicator, determine the indicator having the same source table path as the target indicator from the first candidate indicator set to obtain a second candidate indicator set;

[0014] According to the source field path of the target indicator, determine the indicator having the same source field path as the target indicator from the second candidate indicator set to obtain a third candidate indicator set;

[0015] According to the calculation logic of the target indicator, determine an indicator with the same calculation logic as the target indicator from the third candidate indicator set to obtain a fourth candidate indicator set;

[0016] According to the filtering condition of the target indicator, an indicator having the same filtering condition as the target indicator is determined from the fourth candidate indicator set to obtain a replaceable indicator of the target indicator.

[0017] Optionally, the first SQL statement includes a plurality of target indicators, and the reorganization according to the replaceable indicators and the target dimension to obtain the second SQL statement includes:

[0018] When the tables where the fields of the replaceable indicators of the multiple target indicators in the first SQL statement are located are all the same, the multiple target indicators in the first SQL statement are replaced with the replaceable indicators to obtain the second SQL statement.

[0019] Optionally, the first SQL statement includes a plurality of target indicators, and the reorganization according to the replaceable indicators and the target dimension to obtain the second SQL statement includes:

[0020] When the tables where the fields of the replaceable indicators of the multiple target indicators in the first SQL statement are located are all different, each replaceable indicator is respectively connected with all the dimension fields in the first SQL statement as the main field to obtain a temporary table of a single indicator, and each temporary table is inserted into the SQL statement to obtain a third SQL statement;

[0021] Perform inner connection on all the obtained third SQL statements to obtain the second SQL statement.

[0022] Optionally, the first SQL statement includes multiple target indicators, and the second SQL statement is obtained by reorganizing according to the replaceable indicators and the target dimension, including:

[0023] When the first target indicator in the first SQL statement has a replaceable indicator, and the second target indicator does not have a replaceable indicator, each replaceable indicator is used as a main field, and connected with all dimension fields in the first SQL statement to obtain a temporary table of a single indicator, and each temporary table is inserted into the SQL statement to obtain a third SQL statement;

[0024] Inner join all the third SQL statements obtained to obtain the fourth SQL statement;

[0025] The fourth SQL statement is inner-connected with the fifth SQL statement constructed by the second target indicator to obtain the second SQL statement.

[0026] Optionally, the method further includes:

[0027] Obtain the latest operation logs of all data models in the data warehouse;

[0028] Extracting SQL statements in the operation log and cleaning interference characters in the SQL statements;

[0029] Performing feature extraction on the cleaned SQL statement;

[0030] The features extracted from the SQL statement are stored in the full feature library.

[0031] In a second aspect, the present invention provides a device for generating a data model, comprising:

[0032] An extraction module, used to extract characteristic fields of target indicators and target dimensions from a first structured query language SQL statement input by a user;

[0033] A determination module, used to determine a replaceable indicator of the target indicator from a full feature library, wherein the full feature library stores the indicator and dimension feature fields of the SQL statement in the data warehouse, and the indicator and dimension feature fields include type, globally unique field name, and one or more of the following fields: field name, field table, source table path, source field path, filter condition, and calculation logic;

[0034] a reorganization module, configured to reorganize according to the replaceable indicator and the target dimension to obtain a second SQL statement, where the second SQL statement is a replaceable statement of the first SQL statement;

[0035] An output module is used to output the second SQL statement.

[0036] In a third aspect, the present invention provides an electronic device, comprising: at least one processor and a memory;

[0037] The memory stores computer-executable instructions;

[0038] The at least one processor executes the computer-executable instructions stored in the memory, so that the at least one processor performs the method according to the first aspect of the present invention.

[0039] In a fourth aspect, the present invention provides a computer-readable storage medium, wherein the computer-readable storage medium stores computer-executable instructions, and when the computer-executable instructions are executed by a processor, they are used to implement the method described in the first aspect of the present invention.

[0040] In a fifth aspect, the present invention provides a computer program product, comprising a computer program, wherein when the computer program is executed by a processor, the method described in the first aspect of the present invention is implemented.

[0041] The data model generation method and device provided by the embodiment of the present invention extracts the characteristic fields of the target indicator and the target dimension from the first SQL statement input by the user, determines the replaceable indicator of the target indicator from the full feature library, the full feature library stores the characteristic fields of the indicators and dimensions of the SQL statements in the data warehouse, reorganizes the second SQL statement according to the replaceable indicator and the target dimension, and outputs the second SQL statement. The reorganized second SQL statement is a replaceable statement of the first SQL statement, which can meet the query requirements of the user. The method can automatically extract the dimensions and indicators required for this processing from the characteristic fields of the existing SQL statements, reorganize the SQL statements using the existing dimensions and indicators, complete the development of the data model, improve the utilization rate of the existing data model, and avoid the waste of resources caused by repeated development. BRIEF DESCRIPTION OF THE DRAWINGS

[0042] The accompanying drawings, which are incorporated in and constitute a part of this specification, illustrate embodiments consistent with the present disclosure and, together with the description, serve to explain the principles of the present disclosure.

[0043] Figure 1 A flowchart of a method for generating a data model provided in Embodiment 1 of the present invention;

[0044] Figure 2 A schematic diagram of a mind map for SQL statement parsing;

[0045] Figure 3 A flowchart of a method for matching replaceable indicators provided in Embodiment 2 of the present invention;

[0046] Figure 4 A schematic diagram of the structure of a device for generating a data model provided in Embodiment 3 of the present invention;

[0047] Figure 5 A schematic diagram of the structure of an electronic device provided in Embodiment 4 of the present invention.

[0048] The above drawings show clear embodiments of the present disclosure, which will be described in more detail below. These drawings and text descriptions are not intended to limit the scope of the present disclosure in any way, but to illustrate the concepts of the present disclosure to those skilled in the art by referring to specific embodiments. DETAILED DESCRIPTION

[0049] Exemplary embodiments will be described in detail herein, examples of which are shown in the accompanying drawings. When the following description refers to the drawings, the same numbers in different drawings represent the same or similar elements unless otherwise indicated. The embodiments described in the following exemplary embodiments do not represent all embodiments consistent with the present disclosure. Instead, they are merely examples of devices and methods consistent with some aspects of the present disclosure as detailed in the appended claims.

[0050] Indicators and dimensions are the most commonly used terms in data analysis. Indicators are units or methods used to measure the development of things, such as population, GDP, income, number of users, profit margin, retention rate, coverage rate, etc. Indicators need to be obtained through aggregation and calculation methods such as addition and average, and they need to be aggregated and calculated under certain prerequisites, such as time, location, and scope, which is what we often call statistical caliber and scope.

[0051] Dimension is a certain characteristic of things or phenomena, such as gender, region, time, etc. Among them, time is a common and special dimension. By comparing before and after time, we can know the good or bad development of things.

[0052] Usually, the data size corresponding to the indicator has practical meaning, while the data size corresponding to the dimension has no practical meaning. For example, if the indicator is the order amount, the size of the order amount has practical meaning, while the dimension is the order number, the size of the order number has no practical meaning and is only used to distinguish different orders.

[0053] Currently, when developing data in a data warehouse, developers are required to proactively investigate whether development indicators and dimensions exist. Human factors can lead to low development efficiency and inaccurate development data, and repeated development leads to a large amount of data redundancy.

[0054] Based on this, the present invention provides a method for generating a data model, which can automatically detect whether the indicators and dimensions required to be processed by developers exist in other data models. If so, the required dimensions and indicators are extracted from the existing models, thereby improving the utilization rate and development efficiency of the data model and reducing the storage cost of the data model.

[0055] Figure 1 A flowchart of a method for generating a data model provided in Embodiment 1 of the present invention is shown in FIG. Figure 1As shown, the method provided in this embodiment includes the following steps.

[0056] S101. Extract characteristic fields of target indicators and target dimensions from a first SQL statement input by a user.

[0057] SQL operation statement is a database query and programming language used to access data and query, update and manage relational database systems. It is also the extension of database script files. When performing various corresponding data operations in the database, the user will enter the corresponding SQL operation statement according to the operation needs to perform the corresponding operation. At this time, the database client first obtains the SQL operation statement entered by the user, parses the operation statement, and implements various operations such as data creation, connection, deletion and grouping based on the parsing results.

[0058] The various parts (or keywords) of the first SQL statement can be parsed through an open source library or other SQL parsing technology to obtain characteristic fields of indicators and dimensions in the first SQL. The characteristic fields of indicators and dimensions are also called characteristics of indicators and dimensions.

[0059] Figure 2 A schematic diagram of a mind map for SQL statement parsing, such as Figure 2 As shown, the keyword SELECT of the SQL statement is obtained by parsing the SQL statement, and the indicators and dimensions, the source fields of the dimensions, the processing logic and the source fields of the indicators are further parsed according to the keyword SELECT. We will not introduce them one by one here.

[0060] One or more target indicators may be extracted from the first SQL statement, and one or more target dimensions may be extracted from the first SQL statement. The characteristic fields of indicators and dimensions include type (column type), global standard name field (global standard name) and one or more of the following fields: field name (dimension fields name), field write table (fields write table), origin table path (origin table path), origin field path (origin table path), filter condition (filter sql), calculation logic (metric field logic).

[0061] The field name refers to the name of the field used in the SQL statement. The field type is an indicator or dimension. In the data warehouse, the same name may have multiple meanings, or the same business may be represented by multiple different names. Therefore, the field names of dimensions and indicators need to be standardized to obtain globally unique names for dimensions and indicators.

[0062] The table where the field is located refers to the table where the calculated field is finally stored, and the source table refers to the source table that processes this field, that is, which table the field comes from. The source table is the upstream table of the field, and the table where the field is located is the downstream table of the field. The source field belongs to the source table. A source table includes multiple fields, and the source field refers to the field corresponding to the field in the source table.

[0063] Indicators and dimensions can be calculated in different ways to obtain globally unique names. For example, dimensions can be obtained by standardizing all fields in the data warehouse through an algorithm model, and indicator fields can be obtained through the term frequency-inverse document frequency (TF-IDF) algorithm.

[0064] S102. Determine replaceable indicators of the target indicator from the full feature library, wherein the full feature library stores the indicators of SQL statements in the data warehouse and characteristic fields of dimensions, and the characteristic fields of indicators and dimensions include type, globally unique field name, and one or more of the following fields: field name, table where the field is located, source table path, source field path, filter condition, and calculation logic.

[0065] The full feature library stores the feature fields of the indicators and dimensions of the SQL statements in the data warehouse. For example, the latest operation logs of all data models in the data warehouse are obtained, the SQL statements in the operation logs are extracted, the interference characters in the SQL statements are cleaned, the features of the cleaned SQL statements are extracted, and the features extracted from the SQL statements are stored in the full feature library. The process of extracting the feature fields of the indicators and dimensions of the SQL statements in the operation logs is the same as the method of extracting the feature fields of the indicators and dimensions in the first SQL statement.

[0066] The SQL statements extracted from the log include comments, garbled characters due to log printing, SQL that cannot be parsed due to the misalignment of the original SQL, and interference characters such as escape characters (such as &&). The interference characters need to be cleaned up first.

[0067] It can be understood that the SQL statements in the running log are not the SQL statements input by the user, but the SQL statements that have been processed by parsing, etc. The SQL statements in the log can be run directly.

[0068] The full feature library stores a large number of existing feature fields of indicators and dimensions. The full feature library can be continuously updated. For example, the full feature library can be updated periodically to add newly generated indicators and dimensions to the full feature library.

[0069] By matching the target indicator with the indicators in the full database, query the replaceable indicators of the target indicator, where the characteristic fields of the replaceable indicators are the same as the characteristic fields of the target indicator. When there are multiple target indicators in the first SQL statement, query each target indicator in turn to see if there is a replaceable indicator. The target indicator may or may not have a replaceable indicator.

[0070] S103: Reorganize to obtain a second SQL statement according to the replaceable indicator and the target dimension, and output the second SQL statement, where the second SQL statement is a replaceable statement of the first SQL statement.

[0071] When the first SQL statement includes multiple target indicators, the multiple target indicators may all have replaceable indicators, or only some of the target indicators may have replaceable indicators. Alternatively, when all of the multiple target indicators have replaceable indicators, the field source tables of the multiple replaceable indicators may be the same or different. In the above cases, the methods for reconstructing the second SQL statement are different, and each case will be described below.

[0072] In case 1, when the fields of the replaceable indicators of multiple target indicators in the first SQL statement are located in the same table, that is, the fields of the replaceable indicators of all target indicators are located in the same table, the multiple target indicators in the first SQL statement are replaced with replaceable indicators to obtain the second SQL statement. When the fields of the replaceable indicators of all target indicators are located in the same table, the table can be directly queried without repeated indicator calculation, which improves the reuse rate of the data model.

[0073] For example, the user inputs the SQL as: select item_sku_id, shop_id, count(user_log_acct) as pv, count(distinct user_log_acct) as uv from gdm_m14_glb_wireless_online_log where dt=sysdate(-1).

[0074] Among them, the dimensions are item_sku_id and shop_id, the indicators are pv and uv, the indicators pv and uv have replaceable indicators, and the table where the fields of the replaceable indicators are located is the same table, then the reconstructed second SQL statement is: select item_sku_id,shop_id,app_pv,app_uv from adm.adm_s14_glb_wireless_sum where dt=sysdate(-1).

[0075] Case 2: When the tables where the fields of the replaceable indicators of multiple target indicators in the first SQL statement are located are all different, that is, the tables where the fields of the replaceable indicators of all target indicators are located are different tables, each replaceable indicator is taken as the main field, and is inner-joined (joined) with all the dimensional fields in the first SQL statement to obtain a temporary table of a single indicator, each temporary table is inserted into the SQL statement to obtain a third SQL statement, and all the obtained third SQL statements are inner-joined to obtain the second SQL statement.

[0076] Each replaceable indicator is inner-connected with all dimension fields in the first SQL statement as a single indicator to obtain a temporary table of the single indicator. When there are N target indicators with replaceable indicators, N temporary tables are generated. Each temporary table is inserted into the SQL statement according to the preset rules to obtain the corresponding third SQL statement. The third SQL statement is inner-connected to obtain the second SQL statement, which can be considered to be obtained by inner-connecting all dimension fields as associated fields. The final data model includes all dimensions and indicators in the first SQL statement.

[0077] Still taking the first SQL statement in situation 1 as an example, the second SQL statement finally obtained is as follows.

[0078] select a.item_sku_id,a.shop_id,app_pv,app_uv from(select item_sku_id,shop_id,app_pv from adm.adm_s14_glb_wireless_pv_sum where dt=sysdate(-1)

[0079] )a inner join(

[0080] select item_sku_id,shop_id,app_uv from adm.adm_s14_glb_wireless_uv_sum where dt=sysdate(-1)

[0081] )b on a.item_sku_id=b.item_sku_id and a.shop_id=b.shop_id

[0082] Case 3: When the first target indicator in the first SQL statement has a replaceable indicator, and the second target indicator does not have a replaceable indicator, that is, some indicators in the first SQL statement have replaceable indicators, then each replaceable indicator is used as the main field, and is inner-connected with all dimensional fields in the first SQL statement to obtain a temporary table of a single indicator, and each temporary table is inserted into the SQL statement to obtain a third SQL statement; all the third SQL statements obtained are inner-connected to obtain a fourth SQL statement; the fourth SQL statement is inner-connected with the fifth SQL statement constructed by the second target indicator to obtain a second SQL statement.

[0083] In this case, some indicators in the first SQL statement have replaceable indicators. For the target indicators with replaceable indicators, the fourth SQL statement is generated according to the method of Case 2, and then the fourth SQL statement is inner-connected with the fifth SQL statement to obtain the second SQL, wherein the fifth SQL statement is a SQL statement constructed by the non-replaceable target indicator in the first SQL statement.

[0084] The above method is equivalent to using the existing indicators and dimensions in other SQL statements to replace the indicators and dimensions that the user needs to calculate in the first SQL statement, that is, reorganizing a new SQL statement. The query requirements of the reorganized second SQL statement are the same as the query requirements of the first SQL statement, thereby being able to meet the user's query requirements.

[0085] When there is no replaceable indicator for the target indicator in the full feature library, that is, all indicators in the first SQL statement are irreplaceable, it means that the indicators and dimensions of the data model developed this time do not exist in the existing data model and cannot be reused. In this case, the first SQL statement is output.

[0086] In this embodiment, the characteristic fields of the target indicator and the target dimension are extracted from the first SQL statement input by the user, and the replaceable indicator of the target indicator is determined from the full feature library, which stores the characteristic fields of the indicators and dimensions of the SQL statements in the data warehouse, and the second SQL statement is reorganized according to the replaceable indicator and the target dimension, and the second SQL statement is output. The reorganized second SQL statement is a replaceable statement of the first SQL statement, which can meet the query requirements of the user. The method can automatically extract the dimensions and indicators required for this processing from the characteristic fields of the existing SQL statements, reorganize the SQL statements using the existing dimensions and indicators, complete the development of the data model, improve the utilization rate of the existing data model, and avoid the waste of resources caused by repeated development.

[0087] Based on the first embodiment, the second embodiment of the present invention provides a matching method of replaceable indicators, which is used to explain step S102 of the first embodiment in detail. Figure 3This is a flow chart of a method for matching replaceable indicators provided in the second embodiment of the present invention, such as Figure 3 As shown, the method provided in this embodiment includes the following steps.

[0088] S1021. For each target indicator in the first SQL statement, obtain a globally unique name field of the target indicator.

[0089] The globally unique name field of the target indicator can be calculated using an existing algorithm, and the globally unique name field can be used for matching in subsequent matching.

[0090] S1022. Query all indicators with the same global unique name field as the target indicator from the full feature library to form a first candidate indicator set.

[0091] The full feature library includes multiple existing indicators and dimensions, as well as feature fields of existing indicators and dimensions. The feature fields of indicators and dimensions include type, globally unique field name, and one or more of the following fields: field name, field table, source table path, source field path, filter condition, and calculation logic. The number of fields included in different indicators may be different. For example, indicator 1 includes field name, type, globally unique name field, field table, and source table path, and indicator 2 includes field name, type, globally unique name field, field table, source table path, source field name, and calculation logic. The features of indicators and dimensions stored in the full feature library can be stored in a specific structure.

[0092] After calculating the globally unique field name of the target indicator, the globally unique field name of the target indicator is compared with the globally unique field name of each indicator in the full feature library, and all indicators with the same globally unique field name as the target indicator are found to form a first candidate indicator set.

[0093] S1023. According to the source table path of the target indicator, determine the indicator with the same source table path as the target indicator from the first candidate indicator set to obtain a second candidate indicator set.

[0094] S1024. According to the source field path of the target indicator, determine the indicator with the same source field path as the target indicator from the second candidate indicator set to obtain a third candidate indicator set.

[0095] S1025. According to the calculation logic of the target indicator, determine an indicator having the same calculation logic as the target indicator from the third candidate indicator set to obtain a fourth candidate indicator set.

[0096] S1026. According to the filtering condition of the target indicator, determine an indicator that is the same as the filtering condition of the target indicator from the fourth candidate indicator set to obtain a replaceable indicator of the target indicator.

[0097] It should be noted that there is no order in which steps S1025 and S1026 are executed. After obtaining the third candidate indicator set, the indicators that meet the filtering conditions may be matched first, and then the indicators that meet the calculation logic may be matched.

[0098] It can be understood that the matching process in this embodiment is explained by taking the target indicator including the globally unique field name, the table where the field is located, the source table path, the source field path, the filtering conditions and the calculation logic as an example. In the actual process, the target indicator may only include some feature fields. In this case, it is not necessary to execute all of the above steps. Only some steps need to be executed to obtain the replaceable indicator of the target indicator.

[0099] Figure 4 A schematic diagram of the structure of a device for generating a data model provided in Embodiment 3 of the present invention is shown in FIG. Figure 4 As shown, the device 100 of this embodiment includes the following steps.

[0100] An extraction module 11 is used to extract characteristic fields of target indicators and target dimensions from a first structured query language SQL statement input by a user;

[0101] A determination module 12 is used to determine a replaceable indicator of the target indicator from a full feature library, wherein the full feature library stores the indicator and dimension feature fields of the SQL statement in the data warehouse, and the indicator and dimension feature fields include a type, a globally unique field name, and one or more of the following fields: field name, field table, source table path, source field path, filter condition, and calculation logic;

[0102] A reorganization module 13, configured to reorganize a second SQL statement according to the replaceable indicator and the target dimension, wherein the second SQL statement is a replaceable statement of the first SQL statement;

[0103] The output module 14 is used to output the second SQL statement.

[0104] Optionally, the determining module 12 is specifically configured to:

[0105] For each target indicator, obtain a globally unique name field of the target indicator;

[0106] Querying all indicators with the same global unique name field as the target indicator from the full feature library to form a first candidate indicator set;

[0107] According to the source table path of the target indicator, determine the indicator having the same source table path as the target indicator from the first candidate indicator set to obtain a second candidate indicator set;

[0108] According to the source field path of the target indicator, determine the indicator having the same source field path as the target indicator from the second candidate indicator set to obtain a third candidate indicator set;

[0109] According to the calculation logic of the target indicator, determine an indicator with the same calculation logic as the target indicator from the third candidate indicator set to obtain a fourth candidate indicator set;

[0110] According to the filtering condition of the target indicator, an indicator having the same filtering condition as the target indicator is determined from the fourth candidate indicator set to obtain a replaceable indicator of the target indicator.

[0111] Optionally, the first SQL statement includes multiple target indicators, and the reorganization module 13 is specifically used to:

[0112] When the tables where the fields of the replaceable indicators of the multiple target indicators in the first SQL statement are located are all the same, the multiple target indicators in the first SQL statement are replaced with the replaceable indicators to obtain the second SQL statement.

[0113] Optionally, the first SQL statement includes multiple target indicators, and the reorganization module 13 is specifically used to:

[0114] When the tables where the fields of the replaceable indicators of the multiple target indicators in the first SQL statement are located are all different, each replaceable indicator is respectively connected with all the dimension fields in the first SQL statement as the main field to obtain a temporary table of a single indicator, and each temporary table is inserted into the SQL statement to obtain a third SQL statement;

[0115] Perform inner connection on all the obtained third SQL statements to obtain the second SQL statement.

[0116] Optionally, the first SQL statement includes multiple target indicators, and the reorganization module 13 is specifically used to:

[0117] When the first target indicator in the first SQL statement has a replaceable indicator, and the second target indicator does not have a replaceable indicator, each replaceable indicator is used as a main field, and connected with all dimension fields in the first SQL statement to obtain a temporary table of a single indicator, and each temporary table is inserted into the SQL statement to obtain a third SQL statement;

[0118] Inner join all the third SQL statements obtained to obtain the fourth SQL statement;

[0119] The fourth SQL statement is inner-connected with the fifth SQL statement constructed by the second target indicator to obtain the second SQL statement.

[0120] Optionally, the extraction module 11 is further used for:

[0121] Obtain the latest operation logs of all data models in the data warehouse;

[0122] Extracting SQL statements in the operation log and cleaning interference characters in the SQL statements;

[0123] Performing feature extraction on the cleaned SQL statement;

[0124] The features extracted from the SQL statement are stored in the full feature library.

[0125] The device of this embodiment can be used to execute the method described in the above-mentioned embodiment 1 or embodiment 2. The specific implementation method and technical effects are similar and will not be described in detail here.

[0126] Figure 5 A schematic diagram of the structure of an electronic device provided in Embodiment 4 of the present invention is shown in FIG. Figure 5 As shown, the electronic device 200 includes: a processor 21, a memory 22 and a transceiver 23, wherein the memory 22 is used to store instructions, the transceiver 23 is used to communicate with other devices, and the processor 21 is used to execute the instructions stored in the memory so that the electronic device 200 executes the method described in the above-mentioned embodiment 1 or embodiment 2. The specific implementation method and technical effect are similar and will not be repeated here.

[0127] Embodiment 5 of the present invention provides a computer-readable storage medium, in which computer execution instructions are stored. When the computer execution instructions are executed by a processor, they are used to implement the method described in Embodiment 1 or Embodiment 2 above. The specific implementation method and technical effect are similar and will not be repeated here.

[0128] Embodiment 6 of the present invention provides a computer program product, including a computer program. When the computer program is executed by a processor, the method described in Embodiment 1 or Embodiment 2 above is implemented. The specific implementation method and technical effects are similar and will not be described in detail here.

[0129] Those skilled in the art will readily appreciate other embodiments of the present disclosure after considering the specification and practicing the invention disclosed herein. The present invention is intended to cover any variations, uses or adaptations of the present disclosure that follow the general principles of the present disclosure and include common knowledge or customary techniques in the art that are not disclosed in the present disclosure. The description and examples are to be regarded as exemplary only, and the true scope and spirit of the present disclosure are indicated by the following claims.

[0130] It should be understood that the present disclosure is not limited to the exact structures that have been described above and shown in the drawings, and that various modifications and changes may be made without departing from the scope thereof. The scope of the present disclosure is limited only by the appended claims.

Claims

1. A method for generating a data model, characterized in that: include: Extracting characteristic fields of target indicators and target dimensions from a first structured query language SQL statement input by a user; Match the characteristic fields of the target indicator with the characteristic fields of the indicators in the full feature library to query the replaceable indicators of the target indicator, wherein the full feature library stores the characteristic fields of the indicators and dimensions of the SQL statements in the data warehouse, and the characteristic fields of the indicators and dimensions include type, globally unique field name and one or more of the following fields: field name, table where the field is located, source table path, source field path, filter condition, and calculation logic; A second SQL statement is obtained by reorganizing according to the replaceable indicator and the target dimension, and the second SQL statement is output, where the second SQL statement is a replaceable statement of the first SQL statement.

2. The method according to claim 1, characterized in that The matching of the characteristic field of the target indicator with the characteristic field of the indicator in the full feature library to query the replaceable indicator of the target indicator includes: For each target indicator, obtain a globally unique name field of the target indicator; Querying all indicators with the same global unique name field as the target indicator from the full feature library to form a first candidate indicator set; According to the source table path of the target indicator, determine the indicator having the same source table path as the target indicator from the first candidate indicator set to obtain a second candidate indicator set; According to the source field path of the target indicator, determine the indicator having the same source field path as the target indicator from the second candidate indicator set to obtain a third candidate indicator set; According to the calculation logic of the target indicator, determine an indicator with the same calculation logic as the target indicator from the third candidate indicator set to obtain a fourth candidate indicator set; According to the filtering condition of the target indicator, an indicator having the same filtering condition as the target indicator is determined from the fourth candidate indicator set to obtain a replaceable indicator of the target indicator.

3. The method according to claim 1 or 2, characterized in that: The first SQL statement includes a plurality of target indicators, and the second SQL statement is obtained by reorganizing the replaceable indicators and the target dimension, including: When the tables where the fields of the replaceable indicators of the multiple target indicators in the first SQL statement are located are all the same, the multiple target indicators in the first SQL statement are replaced with the replaceable indicators to obtain the second SQL statement.

4. The method according to claim 1 or 2, characterized in that: The first SQL statement includes a plurality of target indicators, and the second SQL statement is obtained by reorganizing the replaceable indicators and the target dimension, including: When the tables where the fields of the replaceable indicators of the multiple target indicators in the first SQL statement are located are all different, each replaceable indicator is respectively connected with all the dimension fields in the first SQL statement as the main field to obtain a temporary table of a single indicator, and each temporary table is inserted into the SQL statement to obtain a third SQL statement; Perform inner connection on all the obtained third SQL statements to obtain the second SQL statement.

5. The method according to claim 1 or 2, characterized in that: The first SQL statement includes a plurality of target indicators, and the second SQL statement is obtained by reorganizing the replaceable indicators and the target dimension, including: When the first target indicator in the first SQL statement has a replaceable indicator, and the second target indicator does not have a replaceable indicator, each replaceable indicator is used as a main field, and connected with all dimension fields in the first SQL statement to obtain a temporary table of a single indicator, and each temporary table is inserted into the SQL statement to obtain a third SQL statement; Inner join all the third SQL statements obtained to obtain the fourth SQL statement; The fourth SQL statement is inner-connected with the fifth SQL statement constructed by the second target indicator to obtain the second SQL statement.

6. The method according to claim 1, characterized in that Also includes: Obtain the latest operation logs of all data models in the data warehouse; Extracting SQL statements in the operation log and cleaning interference characters in the SQL statements; Performing feature extraction on the cleaned SQL statement; The features extracted from the SQL statement are stored in the full feature library.

7. A data model generation device, characterized in that: include: An extraction module, used to extract characteristic fields of target indicators and target dimensions from a first structured query language SQL statement input by a user; A determination module, used to match the characteristic fields of the target indicator with the characteristic fields of the indicators in the full feature library to query the replaceable indicators of the target indicator, wherein the full feature library stores the characteristic fields of the indicators and dimensions of the SQL statements in the data warehouse, and the characteristic fields of the indicators and dimensions include type, globally unique field name and one or more of the following fields: field name, field table, source table path, source field path, filter condition, calculation logic; A reorganization module, used for reorganizing according to the replaceable index and the target dimension to obtain a second SQL statement, where the second SQL statement is a replaceable statement of the first SQL statement; An output module is used to output the second SQL statement.

8. An electronic device, characterized in that: include: at least one processor and memory; The memory stores computer-executable instructions; The at least one processor executes the computer-executable instructions stored in the memory, so that the at least one processor performs the method according to any one of claims 1 to 6.

9. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores computer-executable instructions, which are used to implement the method according to any one of claims 1 to 6 when executed by a processor.

10. A computer program product, comprising a computer program, characterized in that When the computer program is executed by a processor, the method according to any one of claims 1 to 6 is implemented.

Images