Method and device for generating data model script

Abstract

The present invention discloses a method and device for generating a data model script, and relates to the field of computer technology. A specific implementation of the method includes: parsing a data warehouse script to obtain an indicator configuration file and a dimension association path; parsing the indicator configuration file corresponding to the indicator according to the set indicator field value to generate a master table data model object; generating a dimension table data model object according to the set dimension field value and dimension association path, and associating the dimension table data model object with the master table data model object to obtain a data model; script-splitting the data model according to the indicator and dimension to obtain a unit model script, and saving the unit model script into a code unit to generate a data model script. This implementation can improve the efficiency of indicator configuration, improve the readability of the code, realize segmented code operation, debugging and modification, and improve the development efficiency of the data model script.

Description

Technical Field

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

[0002] Currently, when performing intelligent data model generation, manually configured indicator configuration files are used to encapsulate the indicator calculation logic. The main table and dimension tables are then automatically associated through the dimension association path algorithm. After the user selects the dimension / indicator through the code editor Sublime plug-in, the data model script can be automatically generated.

[0003] In the process of implementing the present invention, the inventors discovered that the prior art has at least the following problems:

[0004] (1) The indicator configuration file needs to be manually configured, which wastes manpower, and the hierarchical relationship of the indicator calculation logic requires learning costs;

[0005] (2) Using Sublime plug-ins as the interactive framework requires the installation of a plug-in environment, which has poor usability and versatility, and cannot achieve visual configuration. The generated data model scripts are poorly readable and have a complex code structure.

[0006] (3) The automatically generated data model script has low execution efficiency. Summary of the Invention

[0007] In view of this, an embodiment of the present invention provides a method and device for generating a data model script, which can improve the efficiency of indicator configuration, improve the readability of the code, realize segmented code operation, debugging and modification, and improve the development efficiency of the data model script.

[0008] To achieve the above objective, according to one aspect of an embodiment of the present invention, a method for generating a data model script is provided.

[0009] A method for generating a data model script, comprising:

[0010] Parse the data warehouse script to obtain indicator configuration files and dimension association paths;

[0011] According to the set indicator field value, the indicator configuration file corresponding to the indicator is parsed to generate the main table data model object;

[0012] Generate a dimension table data model object according to the set dimension field value and the dimension association path, and associate the dimension table data model object with the main table data model object to obtain a data model;

[0013] The data model is script-split according to indicators and dimensions to obtain a unit model script, and the unit model script is saved in a code unit to generate a data model script.

[0014] Optionally, parsing the data warehouse script to obtain the indicator configuration file includes:

[0015] Extract indicator fields and their aggregation logic from data warehouse scripts, as well as the indicator source fields that the aggregation operations depend on;

[0016] Obtain the processing logic of the indicator source field from the data warehouse script through database syntax tracing;

[0017] The aggregation logic and the processing logic are reorganized into an indicator configuration file according to rules.

[0018] Optionally, obtaining the processing logic of the indicator source field from the data warehouse script through database syntax tracing includes:

[0019] Obtain the source table of the indicator source field through database syntax tracing;

[0020] If the source table is a bottom table of a data warehouse, the processing logic of the indicator source field is obtained according to the source table;

[0021] Otherwise, the upstream data warehouse model script of the source table is obtained step by step until the underlying table of the data warehouse is obtained, and the processing logic of the indicator source field is obtained according to the source table and the upstream data warehouse model scripts at each level.

[0022] Optionally, parsing the data warehouse script to obtain the dimension association path includes:

[0023] Extract indicator fields and their aggregation logic from data warehouse scripts, as well as the indicator source fields that the aggregation operations depend on;

[0024] Determine dimension-related fields based on the similarity between the attributes of different indicator source fields, including field name, field value, field length, and the underlying table of the data warehouse corresponding to the source table of the indicator source field;

[0025] A dimension association path is generated based on the dimension association field.

[0026] Optionally, parsing the indicator configuration file corresponding to the indicator to generate the main table data model object includes:

[0027] Obtain the indicator configuration file of the main table corresponding to the indicator according to the indicator field value;

[0028] Obtaining an aggregation logic configuration file corresponding to the indicator from the indicator configuration file, and obtaining a processing logic configuration file corresponding to the indicator according to the aggregation logic configuration file;

[0029] Instantiate according to the aggregation logic configuration file and the processing logic configuration file to generate a main table data model object.

[0030] Optionally, generating a dimension table data model object according to the set dimension field value and the dimension association path includes:

[0031] Determine the dimension table ID associated with the primary table based on the set dimension field value;

[0032] Instantiation is performed according to the dimension association path, the primary table identifier, the dimension table identifier and the dimension field value to generate a dimension table data model object.

[0033] Optionally, before the data model is script-splitting according to indicators and dimensions to obtain unit model scripts, the method further includes:

[0034] Optimizing the script structure of the data model, wherein the script structure optimization includes:

[0035] Merge overlapping correlation paths among multiple dimension correlation paths of the same indicator in different dimensions into one dimension correlation path;

[0036] The dimension association paths corresponding to the same processing logic of different indicators are merged into one dimension association path.

[0037] According to another aspect of an embodiment of the present invention, a device for generating a data model script is provided.

[0038] A device for generating a data model script, comprising:

[0039] The script parsing module is used to parse the data warehouse script to obtain the indicator configuration file and dimension association path;

[0040] The main table object generation module is used to parse the indicator configuration file corresponding to the indicator according to the set indicator field value to generate the main table data model object;

[0041] A dimension table object generation module is used to generate a dimension table data model object according to the set dimension field value and the dimension association path, and associate the dimension table data model object with the main table data model object to obtain a data model;

[0042] The script splitting module is used to split the data model into unit model scripts according to indicators and dimensions, and save the unit model scripts into code units to generate data model scripts.

[0043] According to yet another aspect of an embodiment of the present invention, an electronic device for generating a data model script is provided.

[0044] An electronic device for generating a data model script comprises: one or more processors; a storage device for storing one or more programs, wherein when the one or more programs are executed by the one or more processors, the one or more processors implement the method for generating a data model script provided in an embodiment of the present invention.

[0045] According to yet another aspect of an embodiment of the present invention, a computer-readable medium is provided.

[0046] A computer-readable medium stores a computer program, which, when executed by a processor, implements a method for generating a data model script provided in an embodiment of the present invention.

[0047] An embodiment of the above invention has the following advantages or beneficial effects: by parsing the data warehouse script to obtain the indicator configuration file and the dimension association path; according to the set indicator field value, the indicator configuration file corresponding to the indicator is parsed to generate the main table data model object; according to the set dimension field value and dimension association path, the dimension table data model object is generated, and the dimension table data model object is associated with the main table data model object to obtain the data model; the data model is script-split according to the indicator and dimension to obtain the unit model script, and the unit model script is saved in the code unit to generate the data model script. The technical solution improves the efficiency of indicator configuration by parsing the existing data warehouse script, extracting the indicator processing logic and automatically generating the indicator configuration file in the yaml format; the generated data model script is split according to the execution logic and injected into the jupyter cell (code unit), integrating the data routing, making the data lineage visual, and having a data preview function, improving the readability of the code, realizing the segmented running, debugging and modification of the code, and improving the development efficiency of the data model script. In addition, the application layer uses the Jupyter Lab framework to replace the Sublime plug-in, which can be used directly after logging in without the need for environment deployment, improving the ease of use and versatility of the framework. It also includes a script optimization layer, which automatically performs syntax merging and optimization when the code is converted from data table (DataTable) or data cluster (DataCluster) operators to SQL scripts of the data model, thereby improving the execution efficiency of the generated SQL scripts.

[0048] The further effects of the above-mentioned non-conventional optional manner will be described below in conjunction with specific embodiments. BRIEF DESCRIPTION OF THE DRAWINGS

[0049] The accompanying drawings are provided for a better understanding of the present invention and are not intended to limit the present invention.

[0050] Figure 1 It is a framework diagram of the implementation principle of generating data model scripts in the prior art;

[0051] Figure 2 This is a schematic diagram of the implementation principle of generating a data model script according to an embodiment of the present invention;

[0052] Figure 3 1 is a schematic diagram of the main steps of a method for generating a data model script according to an embodiment of the present invention;

[0053] Figure 4 This is a schematic diagram of the data warehouse script parsing process according to an embodiment of the present invention;

[0054] Figure 5 This is a schematic diagram of the generation process of the main table data model object according to an embodiment of the present invention;

[0055] Figure 6 Schematic diagram of the data model generation process according to an embodiment of the present invention;

[0056] Figure 7 This is a schematic diagram of the script splitting implementation principle of an embodiment of the present invention;

[0057] Figure 8 This is a schematic diagram of a script optimization process according to an embodiment of the present invention;

[0058] Figure 9 is a schematic diagram of a script optimization process according to another embodiment of the present invention;

[0059] Figure 10 2 is a schematic diagram of main modules of a device for generating a data model script according to an embodiment of the present invention;

[0060] Figure 11 is an exemplary system architecture diagram in which embodiments of the present invention may be applied;

[0061] Figure 12 It is a schematic diagram of the structure of a computer system of a terminal device or a server suitable for implementing an embodiment of the present invention. DETAILED DESCRIPTION

[0062] The following description of exemplary embodiments of the present invention is made in conjunction with the accompanying drawings, in which various details of the embodiments of the present invention are included to facilitate understanding. These details should be considered as merely exemplary. Therefore, it should be appreciated by those skilled in the art that various changes and modifications may be made to the embodiments described herein without departing from the scope and spirit of the present invention. Similarly, for the sake of clarity and conciseness, descriptions of well-known functions and structures are omitted in the following description.

[0063] Figure 1 This is a diagram of the implementation principle framework of generating data model scripts in the prior art. Figure 1 As shown, in the prior art, when generating a data model script, the application layer uses a sublime plug-in to implement it. The application layer generates a data model script according to user input by calling various interfaces provided by the interface layer, wherein the interfaces provided by the interface layer include, for example: dimension association path interface, indicator configuration file interface, framework generation interface, SQL conversion interface, etc.; the basic data layer saves the indicator configuration file, dimension library table information, dimension association path and other data set by the user, wherein the indicator configuration file describes the attribute information of the main table, and the main table data model can be instantiated by parsing the indicator configuration file; the SQL generation layer is used to generate operator instances, framework scripts or conversion SQL scripts, that is, to generate data model scripts.

[0064] However, the implementation framework for generating data model scripts in the prior art has the following defects:

[0065] (1) The indicator configuration file needs to be manually configured. The configuration file in YAML format and the hierarchical relationship of the indicator processing logic require a certain learning cost;

[0066] (2) Using Sublime plug-ins as the interactive framework requires installing a plug-in environment, which has poor usability and versatility;

[0067] (3) Sublime plug-ins are limited by the framework and have poor interactivity. They can only rely on shortcut keys to input commands and cannot achieve visual configuration. In addition, the generated code is poorly readable when displayed on Sublime.

[0068] (4) The automatically generated SQL script lacks optimization. If a group of dimensions or indicators rely on the same database table, the database table read and write calculations will be repeated, reducing the SQL execution efficiency.

[0069] In order to solve the above technical problems, the present invention adopts Figure 2 The framework shown is used to automatically generate data model scripts. Figure 2 This is a schematic diagram of the implementation principle of generating a data model script according to an embodiment of the present invention. Figure 2As shown, when generating data model scripts, the application layer uses Jupyter The data warehouse is implemented using the lab framework. The application layer generates data model scripts based on user input by calling various interfaces provided by the interface layer. These interfaces include, for example, the dimension association path interface, the indicator configuration file interface, the framework generation interface, and the SQL conversion interface. The data warehouse foundation layer stores data tables, including the underlying table fdm, as well as intermediate data tables gdm, adm, dim, and app. The parsing layer parses data warehouse scripts, primarily performing metrics aggregation and processing logic parsing, single-model association field parsing, and cross-model field traceability. The intermediate storage layer stores the indicator configuration files, dimension library table information, and dimension association paths obtained by the parsing layer. The indicator configuration files describe the attributes of the primary table, and parsing the indicator configuration files instantiates the primary table data model. The SQL generation layer generates operator instances, framework scripts, or converts SQL scripts, i.e., generates data model scripts. The optimization layer optimizes the script structure after data model generation, primarily through dynamic injection of indicator fields, indicator logic merging, and dimension logic merging. After optimization, the unit model scripts optimized by the optimization layer are displayed in the application layer.

[0070] In an embodiment of the present invention, by parsing the existing data warehouse script, extracting the indicator processing logic and automatically generating an indicator configuration file in yaml format, the efficiency of indicator configuration is improved. The application layer uses the jupyter lab framework to replace the sublime plug-in, which can be used after logging in without the need for environment deployment, thereby improving the ease of use and versatility of the framework; and the generated data model script can also be injected into the jupyter cell (code unit) according to the execution logic split, integrating data routing, making data lineage visual, and having a data preview function, thereby improving the readability of the code, realizing segmented code running, debugging and modification, and improving the development efficiency of the data model script. The SQL optimization layer is added, and when the code is converted from a data table (datatable) or data cluster (datacluster) operator to an SQL script of a data model, syntax merging optimization is automatically performed to improve the execution efficiency of the generated SQL script.

[0071] Figure 3 FIG. 1 is a schematic diagram of the main steps of the method for generating a data model script according to an embodiment of the present invention. Figure 3 As shown, in an embodiment of the present invention, the method for generating a data model script mainly includes the following steps S301 to S304.

[0072] Step S301: Parse the data warehouse script to obtain the indicator configuration file and dimension association path;

[0073] Step S302: parsing the indicator configuration file corresponding to the indicator according to the set indicator field value to generate a main table data model object;

[0074] Step S303: Generate a dimension table data model object according to the set dimension field value and dimension association path, and associate the dimension table data model object with the main table data model object to obtain a data model;

[0075] Step S304: Split the data model into scripts according to indicators and dimensions to obtain unit model scripts, and save the unit model scripts into code units to generate data model scripts.

[0076] According to one embodiment of the present invention, in step S101, the data warehouse script is parsed to obtain an indicator configuration file, which may specifically include: extracting the indicator fields and their aggregation logic, as well as the indicator source fields on which the aggregation operation depends, from the data warehouse script; tracing the database syntax to obtain the processing logic of the indicator source fields from the data warehouse script; and reorganizing the aggregation logic and processing logic into an indicator configuration file according to the rules. Specifically, the data warehouse script can be obtained and parsed through the SQL parsing interface provided by frameworks such as Spark SQL and HiveSQL. After obtaining the indicator configuration file and dimension association path and other data, they will be stored in the redis database in a structured format of key-value.

[0077] According to an embodiment of the present invention, when obtaining the processing logic of the indicator source field from the data warehouse script through database syntax tracing, the following steps may be specifically included:

[0078] Obtain the source table of the indicator source field through database syntax tracing;

[0079] If the source table is a bottom table of a data warehouse, the processing logic of the indicator source field is obtained according to the source table;

[0080] Otherwise, the upstream data warehouse model script of the source table is obtained step by step until the underlying table of the data warehouse is obtained, and the processing logic of the indicator source field is obtained according to the source table and the upstream data warehouse model scripts at each level.

[0081] Figure 4 Schematic diagram of the data warehouse script parsing process according to an embodiment of the present invention. Figure 4 As shown, in an embodiment of the present invention, the parsing process of the data warehouse script is cross-model parsing, which mainly includes the following steps:

[0082] (1) Scan the data warehouse model script and extract the indicator fields and their aggregation logic;

[0083] (2) Analyze the indicator aggregation logic, extract the indicator source field that the indicator aggregation operation depends on, and find the source table of the indicator source field. For example, for the script Count(sku_id)as sku_number, sku_number is the indicator field, count refers to the aggregation logic, and sku_id refers to the indicator source field that the aggregation operation depends on;

[0084] (3) According to the SQL syntax traceability, analyze the processing logic of the indicator source field in the model script. For example, the following script:

[0085] Select

[0086] count(sku_id)as sku_number

[0087] from

[0088] (select*from table A where sku<>0)x

[0089] Join

[0090] (select*from table B where sku<>0)y

[0091] On x.sku_id = y.sku_id;

[0092] In Count(sku_id)as sku_number, sku_number is the indicator field, count refers to the aggregation logic, sku_id is the indicator source field, and the remaining where filter conditions and join logic are the indicator processing logic.

[0093] (4) If the indicator source field in the model script comes from the underlying table of the data warehouse (for example, fdm or bdm), that is, the source table of the indicator source field is the underlying table, then stop parsing and save its traceability results, where the traceability results are all the information in the process of processing the indicator; otherwise, if it does not come from the underlying table such as fdm or bdm, then search the upstream model script of the model one by one and repeat the parsing process until the underlying table is found. A complex indicator may depend on multiple upstream models. Repeating the parsing process means: assuming that model A depends on model B, and model B depends on model C, it is necessary to parse models A, B, and C in sequence until all the information in the indicator processing process is obtained. When parsing the upstream model script, if the source table corresponding to the upstream model has been traced, then directly extract the traceability information of the source table and add it to the traceability results. Otherwise, extract the model script of the source table and execute steps (1)-(4) again. Specifically, suppose indicator x depends on models A, B, and C; indicator y depends on models B, C, and D. If indicator x has already been parsed, then when parsing indicator y, there is no need to parse models B and C again. Instead, you only need to obtain the traceability information when parsing indicator x. This avoids repeated parsing processes and greatly improves script parsing efficiency.

[0094] (5) The parsed indicator processing logic is reorganized into an indicator configuration file in YAML format according to the rules and stored in redis.

[0095] According to another embodiment of the present invention, when parsing a data warehouse script to obtain a dimension association path, the following steps may be specifically performed: extracting indicator fields and their aggregation logic, as well as the indicator source fields on which the aggregation operation depends, from the data warehouse script; determining dimension association fields based on the similarity between the attributes of different indicator source fields, wherein the attributes include field name, field value, field length, and the underlying data warehouse table corresponding to the source table of the indicator source field; and generating a dimension association path based on the dimension association fields. The dimension association path represents the dimension association relationship between various tables or models, including the association order between dimension association fields and tables.

[0096] When constructing dimension association paths, dimension similarity is a key indicator for determining whether two fields can be associated. To improve the accuracy of dimension similarity, each field in the model is traced across models to eliminate interference from fields with the same source but different names in different models. Specifically, dimension association fields are determined based on the similarity between the attributes of different indicator source fields. These attributes include field name, field value, field length, and the underlying data warehouse table corresponding to the source table of the indicator source field. This allows for more accurate determination of field dimension similarity, thereby obtaining dimension association fields.

[0097] In an embodiment of the present invention, when generating dimensional association paths, the dimensional similarity between the upstream source fields of the indicator source field in the upstream models at all levels can be used to construct a dimensional classification model and a dimensional similarity model, and a model relationship graph is constructed based on this. The N-degree association path of each vertex (model or data table) in the graph is calculated through the model relationship graph; then, a regression model is introduced to find the optimal association path for all paths within the current degree, and the top N results are taken for pruning to reduce the overall data volume.

[0098] Following the steps described above, the data warehouse script can be parsed to obtain the indicator configuration file and dimension association path. Upon receiving the indicator field values and dimension field values set by the user through the Jupyter Lab framework at the application layer, the data model script can be generated based on the indicator configuration file and dimension association path.

[0099] According to one embodiment of the present invention, in step S102, parsing the indicator configuration file corresponding to the indicator to generate the main table data model object may specifically include:

[0100] Obtain the indicator configuration file of the main table corresponding to the indicator according to the indicator field value;

[0101] Obtaining an aggregation logic configuration file corresponding to the indicator from the indicator configuration file, and obtaining a processing logic configuration file corresponding to the indicator according to the aggregation logic configuration file;

[0102] Instantiate according to the aggregation logic configuration file and the processing logic configuration file to generate a main table data model object.

[0103] Figure 5 This is a schematic diagram of the generation process of the main table data model object in an embodiment of the present invention. The generation process of the main table data model object is: obtaining the aggregation logic and processing logic of the corresponding indicators in the indicator configuration file and abstracting them into dataTable / dataCluster objects. Figure 5 As shown in the figure, the generation process of the main table data model object is as follows:

[0104] (1) Match the indicator aggregation logic configuration file according to the indicator field value (for example, the indicator name in the embodiment of the present invention). The indicator aggregation logic configuration file mainly includes the following contents:

[0105] Indicator name:

[0106] depend indicator processing logic

[0107] logic: indicator aggregation logic

[0108] comment: indicator comment

[0109] type: indicator data type

[0110] period: indicator calculation period

[0111] business_type: indicator business limitation;

[0112] (2) According to the depend field in the indicator aggregation logic configuration file, match the indicator processing logic configuration file, where the depend field is used to point to the indicator processing logic configuration file. The indicator processing logic configuration file mainly includes the following contents:

[0113] traffic_virtual:#Indicator processing logic group name

[0114] depend:#Indicator processing logic depends on the model. For example, if processing this indicator requires T1 to join T2, then T1 and T2 need to be configured here.

[0115] T1:#Indicator processing logic depends on the model alias

[0116] table: indicator processing logic dependent model table name

[0117] column: indicator processing logic dependent model field name

[0118] filter: indicator processing logic depends on the model filtering conditions

[0119] alias: indicator processing logic dependent model alias

[0120] groupby: indicator processing logic dependency model groupby

[0121] grouping_sets: indicator processing logic depends on the model alias grouping_set

[0122] main: Whether this table is the main table

[0123] T2:

[0124] table: indicator processing logic dependent model table name

[0125] column: indicator processing logic dependent model field name

[0126] filter: indicator processing logic depends on the model filtering conditions

[0127] alias: indicator processing logic dependent model alias

[0128] groupby: indicator processing logic dependency model groupby

[0129] grouping_sets: indicator processing logic depends on the model alias grouping_set

[0130] process: indicator processing logic. For example, if processing this indicator requires T1 to join T2, then configure the process of T1 association with T2 here.

[0131] C1:

[0132] column:null#T1 joins the field required by the T2 result set. It can be null and can be automatically injected or hard-coded manually.

[0133] connect:#Indicator processing association logic

[0134] -LEFT_JOIN: #Association keywords (LEFT_JOIN, RIGHT_JOIN, UNION)

[0135] -T1

[0136] -T2

[0137] groupby:null#Indicator processing groupby, null is allowed, and can be automatically generated according to the dimension

[0138] grouping_sets:null#Indicator processing grouping_sets, null is allowed, and can be automatically generated

[0139] filter:null#Indicator processing filter conditions

[0140] alias:C1#The alias of the data set generated by JOIN of indicator processing dependent tables T1 and T2;

[0141] (3) Initialize the dataTable data table instance according to the depend field of the indicator processing logic configuration file and the indicator aggregation logic configuration file;

[0142] (4) According to the process field of the indicator processing logic configuration file, the dataTable data table instance is merged into the dataCluster data cluster object, that is, the main table data model object is obtained.

[0143] After generating the master table data model object, a data model object for a dimension table to be dimensionally associated with the master table can be generated so as to dimensionally associate the dimension with the master table data model object. According to one embodiment of the present invention, based on the set dimension field values and the dimension association path, the step of generating the dimension table data model object may specifically include:

[0144] Determine the dimension table ID associated with the primary table based on the set dimension field value;

[0145] Instantiation is performed according to the dimension association path, the primary table identifier, the dimension table identifier and the dimension field value to generate a dimension table data model object.

[0146] Figure 6 FIG. 1 is a schematic diagram of the generation process of the data model of an embodiment of the present invention. Figure 6 As shown, the JupyterLab framework at the application layer calls the corresponding dimension association path interface based on the indicator field values and dimension field values set by the user. This interface determines the dimension table identifier (e.g., the dimension table name) associated with the primary table and the dimension association path linking the dimension table to the primary table based on the dimension field value. The primary table identifier, dimension table identifier, dimension association path, and dimension field value are then used as dimension association path structured data. Subsequently, the dimension association path structured data is instantiated to generate a dimension table data model object, dataTable or dataCluster. The previously generated primary table data model object and dimension table data model object are then associated (e.g., through a left join, right join, etc.) to obtain a data model containing information such as the indicator's aggregation logic, dimension association path, and dependent library and table objects.

[0147] After obtaining the data model, the data model is script-split according to indicators and dimensions to obtain unit model scripts, and the unit model scripts are saved in code units to generate data model scripts. Figure 7 This is a schematic diagram of the script splitting implementation principle of an embodiment of the present invention. Splitting each dimension associated with each indicator to generate a unit model script is injected into the Jupytercell code unit, which can make the code structure clearer and more readable. In addition, each code unit can independently perform SQL generation, data preview, generation of lineage and other visual interactive functions, making the entire processing process completely transparent to the user.

[0148] When users view or modify data model scripts through the Jupyter Lab framework at the application layer, the framework displays the dimension association path with the fewest dimension association nodes by default. If this path does not meet user requirements, you can select a different association path from the drop-down box below each dimension code cell. After selection, the dimension cell will be automatically replaced with the new path script.

[0149] According to one embodiment of the present invention, after obtaining the data model, before the data model is script-splitting according to indicators and dimensions to obtain a unit model script, the present invention may further optimize the script structure of the data model to improve the execution efficiency of SQL, wherein the script structure optimization includes: merging overlapping association paths in multiple dimension association paths of the same indicator but different dimensions into one dimension association path; and merging dimension association paths corresponding to the same processing logic of different indicators into one dimension association path.

[0150] Figure 8 This is a schematic diagram of the script optimization process of an embodiment of the present invention. Figure 8 As shown in the example, for a given metric, the corresponding master table is Table A. Dimension Field 1 and Dimension Field 2 have corresponding dimension association paths: BCD and CDE, respectively. If some association paths overlap, the multiple association paths for each dimension are merged into a single path: BCDE. The dimension fields are extracted and injected into the new path. This simplifies the code and improves execution efficiency when SQL operations are required.

[0151] Figure 9 FIG. 1 is a schematic diagram of a script optimization process according to another embodiment of the present invention. Figure 9 As shown in the example, for different indicators 1 and 2, if the dimension association path of indicator 1 is BCD and the dimension association path of indicator 2 is CDE, and some of the association paths overlap, these two dimension association paths are merged to form the path BCDE, and the indicator fields are extracted and injected into the new path. This simplifies the code and improves execution efficiency when SQL operations need to be executed.

[0152] Figure 10 Schematic diagram of the main modules of the device for generating a data model script according to an embodiment of the present invention. Figure 10 As shown, the data model script generation device 1000 of the embodiment of the present invention mainly includes a script parsing module 1001, a main table object generation module 1002, a dimension table object generation module 1003 and a script splitting module 1004.

[0153] The script parsing module 1001 is used to parse the data warehouse script to obtain the indicator configuration file and dimension association path;

[0154] The main table object generation module 1002 is used to parse the indicator configuration file corresponding to the indicator according to the set indicator field value to generate a main table data model object;

[0155] A dimension table object generation module 1003 is configured to generate a dimension table data model object according to the set dimension field value and the dimension association path, and associate the dimension table data model object with the master table data model object to obtain a data model;

[0156] The script splitting module 1004 is used to split the data model into scripts according to indicators and dimensions to obtain unit model scripts, and save the unit model scripts into code units to generate data model scripts.

[0157] According to one embodiment of the present invention, the script parsing module 1001 may also be used to:

[0158] Extract indicator fields and their aggregation logic from data warehouse scripts, as well as the indicator source fields that the aggregation operations depend on;

[0159] Obtain the processing logic of the indicator source field from the data warehouse script through database syntax tracing;

[0160] The aggregation logic and the processing logic are reorganized into an indicator configuration file according to rules.

[0161] According to another embodiment of the present invention, the script parsing module 1001 may also be used to:

[0162] Obtain the source table of the indicator source field through database syntax tracing;

[0163] If the source table is a bottom table of a data warehouse, the processing logic of the indicator source field is obtained according to the source table;

[0164] Otherwise, the upstream data warehouse model script of the source table is obtained step by step until the underlying table of the data warehouse is obtained, and the processing logic of the indicator source field is obtained according to the source table and the upstream data warehouse model scripts at each level.

[0165] According to yet another embodiment of the present invention, the script parsing module 1001 may also be used to:

[0166] Extract indicator fields and their aggregation logic from data warehouse scripts, as well as the indicator source fields that the aggregation operations depend on;

[0167] Determine dimension-related fields based on the similarity between the attributes of different indicator source fields, including field name, field value, field length, and the underlying table of the data warehouse corresponding to the source table of the indicator source field;

[0168] A dimension association path is generated based on the dimension association field.

[0169] According to yet another embodiment of the present invention, the master table object generation module 1002 may also be used to:

[0170] Obtain the indicator configuration file of the main table corresponding to the indicator according to the indicator field value;

[0171] Obtaining an aggregation logic configuration file corresponding to the indicator from the indicator configuration file, and obtaining a processing logic configuration file corresponding to the indicator according to the aggregation logic configuration file;

[0172] Instantiate according to the aggregation logic configuration file and the processing logic configuration file to generate a main table data model object.

[0173] According to yet another embodiment of the present invention, the dimension table object generation module 1003 may also be used to:

[0174] Determine the dimension table ID associated with the primary table based on the set dimension field value;

[0175] Instantiation is performed according to the dimension association path, the primary table identifier, the dimension table identifier and the dimension field value to generate a dimension table data model object.

[0176] According to another embodiment of the present invention, the data model script generation device 1000 may further include a script optimization module (not shown in the figure) for:

[0177] Before the data model is script-split according to indicators and dimensions to obtain unit model scripts, the script structure of the data model is optimized, wherein the script structure optimization includes:

[0178] Merge overlapping correlation paths among multiple dimension correlation paths of the same indicator in different dimensions into one dimension correlation path;

[0179] The dimension association paths corresponding to the same processing logic of different indicators are merged into one dimension association path.

[0180] According to the technical solution of the embodiment of the present invention, the data warehouse script is parsed to obtain the indicator configuration file and the dimension association path; the indicator configuration file corresponding to the indicator is parsed according to the set indicator field value to generate the main table data model object; the dimension table data model object is generated according to the set dimension field value and the dimension association path, and the dimension table data model object is associated with the main table data model object to obtain the data model; the data model is script-split according to the indicator and dimension to obtain the unit model script, and the unit model script is saved in the code unit to generate the data model script. The technical solution improves the efficiency of indicator configuration by parsing the existing data warehouse script, extracting the indicator processing logic and automatically generating the indicator configuration file in the yaml format; the generated data model script is split according to the execution logic and injected into the jupyter cell (code unit), integrating the data routing, making the data lineage visual, and having the data preview function, improving the readability of the code, realizing the segmented running, debugging and modification of the code, and improving the development efficiency of the data model script. In addition, the application layer uses the Jupyter Lab framework to replace the Sublime plug-in, which can be used directly after logging in without the need for environment deployment, improving the ease of use and versatility of the framework. It also includes a script optimization layer, which automatically performs syntax merging and optimization when the code is converted from data table (DataTable) or data cluster (DataCluster) operators to SQL scripts of the data model, thereby improving the execution efficiency of the generated SQL scripts.

[0181] Figure 11 An exemplary system architecture 1100 is shown to which the method for generating a data model script or the apparatus for generating a data model script according to an embodiment of the present invention may be applied.

[0182] like Figure 11 As shown, system architecture 1100 may include terminal devices 1101, 1102, and 1103, a network 1104, and a server 1105. Network 1104 is used to provide a medium for communication links between terminal devices 1101, 1102, and 1103 and server 1105. Network 1104 may include various connection types, such as wired or wireless communication links or fiber optic cables.

[0183] Users can use terminal devices 1101, 1102, and 1103 to interact with server 1105 via network 1104 to receive or send messages, etc. Various communication client applications can be installed on terminal devices 1101, 1102, and 1103, such as script editing applications, development framework applications, search applications, database tools, email clients, social platform software, etc. (only as examples).

[0184] The terminal devices 1101 , 1102 , and 1103 may be various electronic devices having a display screen and supporting web browsing, including but not limited to smart phones, tablet computers, laptop computers, and desktop computers.

[0185] Server 1105 may be a server that provides various services, such as a background management server (for example only) that supports data model script generation requests sent by users using terminal devices 1101, 1102, and 1103. The background management server may parse received data such as data warehouse scripts to obtain indicator configuration files and dimension association paths, and then feed back the processing results (for example, indicator configuration files and dimension association paths—for example only) to the terminal device.

[0186] It should be noted that the method for generating a data model script provided in the embodiment of the present invention is generally executed by the server 1105 , and accordingly, the device for generating a data model script is generally set in the server 1105 .

[0187] It should be understood that Figure 11 The number of terminal devices, networks and servers in the embodiment is merely illustrative. Any number of terminal devices, networks and servers may be provided as required.

[0188] Reference below Figure 12 , which shows a schematic structural diagram of a computer system 1200 of a terminal device or server suitable for implementing an embodiment of the present invention. Figure 12 The terminal device or server shown is only an example and should not bring any limitation to the functions and scope of use of the embodiments of the present invention.

[0189] like Figure 12 As shown, the computer system 1200 includes a central processing unit (CPU) 1201, which can perform various appropriate actions and processes according to a program stored in a read-only memory (ROM) 1202 or a program loaded from a storage unit 1208 into a random access memory (RAM) 1203. Various programs and data required for the operation of the system 1200 are also stored in the RAM 1203. The CPU 1201, the ROM 1202, and the RAM 1203 are connected to each other via a bus 1204. An input / output (I / O) interface 1205 is also connected to the bus 1204.

[0190] The following components are connected to the I / O interface 1205: an input section 1206 including a keyboard, a mouse, and the like; an output section 1207 including devices such as a cathode ray tube (CRT), a liquid crystal display (LCD), and speakers; a storage section 1208 including a hard disk; and a communication section 1209 including a network interface card such as a LAN card or a modem. The communication section 1209 performs communication processing via a network such as the Internet. A drive 1210 is also connected to the I / O interface 1205 as needed. Removable media 1211, such as a magnetic disk, an optical disk, a magneto-optical disk, or a semiconductor memory, is installed in the drive 1210 as needed, so that computer programs read therefrom can be installed into the storage section 1208 as needed.

[0191] In particular, according to the embodiments disclosed herein, the processes described above with reference to the flowcharts can be implemented as computer software programs. For example, the embodiments disclosed herein include a computer program product comprising a computer program carried on a computer-readable medium, the computer program comprising program code for executing the method shown in the flowchart. In such an embodiment, the computer program can be downloaded and installed from a network via the communication section 1209, and / or installed from a removable medium 1211. When the computer program is executed by the central processing unit (CPU) 1201, the above-mentioned functions defined in the system of the present invention are performed.

[0192] It should be noted that the computer-readable medium described in the present invention can be a computer-readable signal medium or a computer-readable storage medium, or any combination thereof. A computer-readable storage medium can be, for example, but not limited to, an electrical, magnetic, optical, electromagnetic, infrared, or semiconductor system, apparatus, or device, or any combination thereof. More specific examples of computer-readable storage media can include, but are not limited to, an electrical connection having one or more conductors, a portable computer disk, a hard disk, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), optical fiber, a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination thereof. In the present invention, a computer-readable storage medium can be any tangible medium containing or storing a program that can be used by or in conjunction with an instruction execution system, apparatus, or device. In the present invention, a computer-readable signal medium can include a data signal propagated in baseband or as part of a carrier wave, carrying computer-readable program code. This propagated data signal can take a variety of forms, including but not limited to electromagnetic signals, optical signals, or any suitable combination thereof. A computer-readable signal medium may also be any computer-readable medium other than a computer-readable storage medium that can transmit, propagate, or transport a program for use by or in conjunction with an instruction execution system, apparatus, or device. Program code embodied on a computer-readable medium may be transmitted using any suitable medium, including but not limited to wireless, wireline, optical fiber cable, RF, or any suitable combination thereof.

[0193] The flowcharts and block diagrams in the accompanying drawings illustrate the possible implementation architecture, functions and operations of the systems, methods and computer program products according to various embodiments of the present invention. In this regard, each box in the flowchart or block diagram can represent a module, program segment, or a part of code, and the above-mentioned module, program segment, or a part of code contains one or more executable instructions for implementing the specified logical function. It should also be noted that in some alternative implementations, the functions marked in the box can also occur in an order different from that marked in the accompanying drawings. For example, two boxes represented in succession can actually be executed substantially in parallel, and they can sometimes be executed in the opposite order, depending on the functions involved. It should also be noted that each box in the block diagram or flowchart, and the combination of boxes in the block diagram or flowchart, can be implemented with a dedicated hardware-based system that performs the specified function or operation, or can be implemented with a combination of dedicated hardware and computer instructions.

[0194] The units or modules involved in the embodiments of the present invention may be implemented in software or in hardware. The units or modules described may also be provided in a processor. For example, they may be described as follows: a processor includes a script parsing module, a main table object generation module, a dimension table object generation module, and a script splitting module. The names of these units or modules do not, in some cases, constitute a limitation on the units or modules themselves. For example, the script parsing module may also be described as a "module for parsing data warehouse scripts to obtain indicator configuration files and dimension association paths."

[0195] As another aspect, the present invention further provides a computer-readable medium, which may be included in the device described in the above embodiment; or may exist independently and not be assembled into the device. The above computer-readable medium carries one or more programs, and when the above one or more programs are executed by a device, the device includes: parsing a data warehouse script to obtain an indicator configuration file and a dimension association path; parsing the indicator configuration file corresponding to the indicator according to the set indicator field value to generate a master table data model object; generating a dimension table data model object according to the set dimension field value and the dimension association path, and associating the dimension table data model object with the master table data model object to obtain a data model; script-splitting the data model according to the indicator and dimension to obtain a unit model script, and saving the unit model script into a code unit to generate a data model script.

[0196] According to the technical solution of the embodiment of the present invention, the data warehouse script is parsed to obtain the indicator configuration file and the dimension association path; the indicator configuration file corresponding to the indicator is parsed according to the set indicator field value to generate the main table data model object; the dimension table data model object is generated according to the set dimension field value and the dimension association path, and the dimension table data model object is associated with the main table data model object to obtain the data model; the data model is script-split according to the indicator and dimension to obtain the unit model script, and the unit model script is saved in the code unit to generate the data model script. The technical solution improves the efficiency of indicator configuration by parsing the existing data warehouse script, extracting the indicator processing logic and automatically generating the indicator configuration file in the yaml format; the generated data model script is split according to the execution logic and injected into the jupyter cell (code unit), integrating the data routing, making the data lineage visual, and having the data preview function, improving the readability of the code, realizing the segmented running, debugging and modification of the code, and improving the development efficiency of the data model script. In addition, the application layer uses the Jupyter Lab framework to replace the Sublime plug-in, which can be used directly after logging in without the need for environment deployment, improving the ease of use and versatility of the framework. It also includes a script optimization layer, which automatically performs syntax merging and optimization when the code is converted from data table (DataTable) or data cluster (DataCluster) operators to SQL scripts of the data model, thereby improving the execution efficiency of the generated SQL scripts.

[0197] The above specific embodiments do not limit the scope of protection of the present invention. Those skilled in the art will appreciate that various modifications, combinations, sub-combinations, and substitutions may occur depending on design requirements and other factors. Any modifications, equivalent substitutions, and improvements made within the spirit and principles of the present invention are intended to be included within the scope of protection of the present invention.

Claims

1. A method for generating a data model script, characterized in that: The application framework for generating data model scripts is the Jupyter Lab framework, and the method includes: Parse the data warehouse script to obtain indicator configuration files and dimension association paths; According to the set indicator field value, the indicator configuration file corresponding to the indicator is parsed to generate the main table data model object; Generate a dimension table data model object according to the set dimension field value and the dimension association path, and associate the dimension table data model object with the main table data model object to obtain a data model; Optimizing the script structure of the data model, wherein the script structure optimization includes: merging overlapping association paths in multiple dimension association paths of different dimensions of the same indicator into one dimension association path; merging dimension association paths corresponding to the same processing logic of different indicators into one dimension association path; The data model is script-split according to indicators and dimensions to obtain a unit model script, and the unit model script is saved in a code unit to generate a data model script.

2. The method according to claim 1, characterized in that Parsing the data warehouse script to obtain the indicator configuration file includes: Extract indicator fields and their aggregation logic from data warehouse scripts, as well as the indicator source fields that the aggregation operations depend on; Obtain the processing logic of the indicator source field from the data warehouse script through database syntax tracing; The aggregation logic and the processing logic are reorganized into an indicator configuration file according to rules.

3. The method according to claim 2, characterized in that By tracing back the database syntax, the processing logic for obtaining the indicator source field from the data warehouse script includes: Obtain the source table of the indicator source field through database syntax tracing; If the source table is a bottom table of a data warehouse, the processing logic of the indicator source field is obtained according to the source table; Otherwise, the upstream data warehouse model script of the source table is obtained step by step until the underlying table of the data warehouse is obtained, and the processing logic of the indicator source field is obtained according to the source table and the upstream data warehouse model scripts at each level.

4. The method according to claim 1, wherein Parsing the data warehouse script to obtain dimension association paths includes: Extract indicator fields and their aggregation logic from data warehouse scripts, as well as the indicator source fields that the aggregation operations depend on; Determine dimension-related fields based on the similarity between the attributes of different indicator source fields, including field name, field value, field length, and the underlying table of the data warehouse corresponding to the source table of the indicator source field; A dimension association path is generated based on the dimension association field.

5. The method according to claim 1, wherein Parsing the indicator configuration file corresponding to the indicator to generate the main table data model object includes: Obtain the indicator configuration file of the main table corresponding to the indicator according to the indicator field value; Obtaining an aggregation logic configuration file corresponding to the indicator from the indicator configuration file, and obtaining a processing logic configuration file corresponding to the indicator according to the aggregation logic configuration file; Instantiate according to the aggregation logic configuration file and the processing logic configuration file to generate a main table data model object.

6. The method according to claim 5, characterized in that Generating a dimension table data model object according to the set dimension field value and the dimension association path includes: Determine the dimension table ID associated with the primary table based on the set dimension field value; Instantiation is performed according to the dimension association path, the primary table identifier, the dimension table identifier and the dimension field value to generate a dimension table data model object.

7. A device for generating a data model script, characterized in that: The application framework for generating data model scripts is the Jupyter Lab framework, and the device includes: The script parsing module is used to parse the data warehouse script to obtain the indicator configuration file and dimension association path; The main table object generation module is used to parse the indicator configuration file corresponding to the indicator according to the set indicator field value to generate the main table data model object; A dimension table object generation module is used to generate a dimension table data model object according to the set dimension field value and the dimension association path, and associate the dimension table data model object with the main table data model object to obtain a data model; A script optimization module is used to optimize the script structure of the data model, wherein the script structure optimization includes: merging overlapping association paths in multiple dimension association paths of different dimensions of the same indicator into one dimension association path; merging dimension association paths corresponding to the same processing logic of different indicators into one dimension association path; The script splitting module is used to split the data model into unit model scripts according to indicators and dimensions, and save the unit model scripts into code units to generate data model scripts.

8. An electronic device for generating a data model script, characterized in that: include: one or more processors; a storage device for storing one or more programs, When the one or more programs are executed by the one or more processors, the one or more processors implement the method according to any one of claims 1 to 6.

9. A computer-readable medium having a computer program stored thereon, characterized in that: When the program is executed by a processor, the method according to any one of claims 1 to 6 is implemented.

Images