Method, device, equipment, medium and product for adding field to data warehouse

Abstract

The application relates to a method, device, equipment, medium and product for adding a field to a data warehouse, the data warehouse comprising a plurality of data levels, wherein a first target table and a second target table, which need to add a target field, are located in two adjacent levels in the data warehouse respectively, the method comprising: generating a statement for modifying a table structure of the first target table; obtaining a first task id corresponding to the first target table from metadata of the first target table, and obtaining meta code corresponding to the first task id, wherein the first task id is used for identifying a data operation task performed on the first target table; adding one or more preset field information comprising the target field in the meta code of the first task id to obtain modified meta code of the first task id. The embodiment of the application can improve the efficiency of adding a field to a data warehouse.

Description

Technical Field

[0001] This application relates to the field of data processing technology, and in particular to a method, apparatus, device, medium and product for adding fields to a data warehouse. Background Technology

[0002] A data warehouse is a subject-oriented, integrated, and non-volatile collection of data used to support enterprise decision-making and business intelligence applications, providing flexible data analysis and query capabilities. Data layering is a common technique in data warehouses, helping to improve data quality, reduce data preparation time and costs, and support data governance. Common layers in a data warehouse include Operational Data Store (ODS), Data Warehouse Detail (DWD), and Data Warehouse Service (DWS). Each layer of a data warehouse contains data storage tables. The tables in the ODS layer store raw, real-time operational data, providing real-time data support. The tables in the DWD layer store cleaned and processed detailed data, providing basic data support for the data warehouse and supporting complex data analysis and reporting query needs. The tables in the DWS layer store processed data and provide support for data analysis and reporting for users.

[0003] During data warehouse development, fields from other data sources need to be added to different layers of the data warehouse according to business requirements. However, currently, different tables are used for different business operations in the data warehouse, requiring repetitive and tedious operations between these tables when adding fields. For example, during business processing, new fields are frequently added. To facilitate data querying and usage by business users, these new fields need to be synchronized to various layers of the data warehouse. During the update process, these fields do not involve logical calculations; it's simply a synchronization process. For example, synchronizing from an external database to the data warehouse ODS layer, then to the DWD layer, and even to the DWM layer is a cumbersome and inefficient process. Furthermore, errors in this operation will affect business usage, severely impacting overall efficiency. To address this need for complex processes without logical logic, a fundamental optimization of the data warehouse's processing mechanism is required. Summary of the Invention

[0004] In view of the technical problems existing in the prior art, this application proposes a method, apparatus, computer-readable storage medium and computer program product for adding fields to a data warehouse, so as to solve at least one technical problem.

[0005] This application proposes a method for adding fields to a data warehouse. The data warehouse includes multiple data levels, where a first target table and a second target table, to which the target field needs to be added, are located in two adjacent levels within the data warehouse. The method for adding fields to the data warehouse includes: generating statements for modifying the table structure of the first target table; obtaining a first task ID corresponding to the first target table from the metadata of the first target table, and obtaining the metacode corresponding to the first task ID, wherein the first task ID is used to identify the data operation task performed on the first target table; adding one or more preset field information, including the target field, to the metacode of the first task ID to obtain the modified metacode of the first task ID; executing the statements for modifying the table structure of the first target table, and running the modified metacode of the first task ID to obtain a modified first target table, which includes the target field; replacing the data source where the target field is located with the modified first target table, and performing the above steps on the second target table to add the target field to the second target table.

[0006] Optionally, the steps performed on the second target table include: generating statements for modifying the table structure of the second target table; obtaining the second task ID corresponding to the second target table from the metadata of the second target table, and obtaining the metacode corresponding to the second task ID, wherein the second task ID is used to identify the data operation task performed on the second target table; adding one or more preset field information including the target field to the metacode of the second task ID to obtain the modified metacode of the second task ID; executing the statements for modifying the table structure of the second target table, and running the modified metacode of the second task ID to obtain the modified second target table, wherein the modified second target table includes the target field.

[0007] Optionally, the preset field information is a code parameter containing the fields to be added. The code parameter is used to filter the fields to be added contained in the code parameter when running the modified metacode of the first task id, so that the fields to be added with empty values ​​are replaced with empty strings.

[0008] Optionally, the metacode is SQL code, where the data source information is added after the "from" keyword in the SQL code to locate the data source.

[0009] Optionally, the first target table and the second target table are located at the i-th level and the (i+1)-th level in the data warehouse, respectively. If i = 1, the data source is located outside the data warehouse; if i > 1, the data source is located at the (i-1)-th level of the data warehouse.

[0010] Optionally, the data warehouse includes three data layers, from the first to the third layer: Operational Data Storage (ODS) layer, Data Detail (DWD) layer, and Data Service (DWS) layer. If the first target table is located in the ODS layer of the data warehouse, the data source is located outside the data warehouse, and the second target table is located in the DWD layer of the data warehouse. If the first target table is located in the DWD layer of the data warehouse, the data source is located in the ODS layer of the data warehouse, and the second target table is located in the DWS layer of the data warehouse.

[0011] Optionally, the data source can be any of the following: MySQL database, Oracle database, Hive data warehouse, Starrocks data warehouse, or MaxCompute data warehouse.

[0012] Optionally, methods for adding fields to a data warehouse may also include providing a configuration interface for inputting preset field information, the name of the data source, the address of the data source, and the name of the first target table or the second target.

[0013] This application proposes an apparatus for adding fields to a data warehouse. The data warehouse includes multiple data levels, wherein a first target table and a second target table to which a target field needs to be added are located in two adjacent levels of the data warehouse. The apparatus for adding fields to the data warehouse includes: a generation module for generating statements for modifying the table structure of the first target table; an acquisition module for obtaining a first task ID corresponding to the first target table from the metadata of the first target table and obtaining the metacode corresponding to the first task ID, wherein the first task ID is used to identify the data operation task performed on the first target table; an addition module for adding one or more preset field information including the field to be added and the information of the data source where the field to be added is located to the metacode of the first task ID, thereby obtaining the modified metacode of the first task ID; a first execution module for executing the statements for modifying the table structure of the first target table and running the modified metacode of the first task ID, thereby obtaining a modified first target table, wherein the modified first target table includes the target field; and a second execution module for replacing the data source where the field to be added is located with the modified first target table and performing the above steps on the second target table to add the target field to the second target table.

[0014] This application also proposes an electronic device, which includes a processor and a memory storing computer program instructions; the electronic device executes the computer program instructions to implement the aforementioned method.

[0015] This application also proposes a computer-readable storage medium storing computer program instructions that, when executed by a processor, implement the aforementioned method.

[0016] This application also proposes a computer program product, which includes computer program instructions that, when executed by a processor, implement the aforementioned method.

[0017] The method for adding fields to a data warehouse proposed in this application only requires simple configuration of the address of the data source where the target field is located, the name of the target table to which the target field is to be added, the name of the data warehouse, the target field, and other information to quickly add fields to various levels of the data warehouse. The whole process is very simple and easy to operate, which improves overall efficiency and reduces the error rate. Even non-professionals can add new fields. Attached Figure Description

[0018] The preferred embodiments of this application will now be described in further detail with reference to the accompanying drawings, wherein:

[0019] Figure 1 This is a schematic diagram of the system architecture of an embodiment of this application.

[0020] Figure 2 This is a flowchart illustrating a method for adding fields to a data warehouse according to an embodiment of this application.

[0021] Figure 3 This is a flowchart illustrating a method for adding fields to a data warehouse according to an embodiment of this application.

[0022] Figure 4 This is a schematic diagram of the structure of an apparatus for adding fields to a data warehouse according to some embodiments of this application.

[0023] Figure 5 This is a schematic diagram of the hardware structure of an electronic device according to an embodiment of this application. Detailed Implementation

[0024] The principles and spirit of this application will be described below with reference to several exemplary embodiments. It should be understood that these embodiments are provided to make the principles and spirit of this application clearer and more thorough, enabling those skilled in the art to better understand and implement the principles and spirit of this application. The exemplary embodiments provided herein are only a part of the embodiments of this application, and not all of them. All other embodiments obtained by those skilled in the art based on the embodiments described herein without inventive effort are within the scope of protection of this application.

[0025] Embodiments of this application relate to terminal devices and / or servers. Those skilled in the art will understand that embodiments of this application can be implemented as a system, apparatus, device, method, computer-readable storage medium, or computer program product. Therefore, this disclosure can be specifically implemented in at least one of the following forms: entirely hardware, entirely software, or a combination of hardware and software. According to embodiments of this application, this application claims protection for an embodiment of a method, apparatus, electronic device, and computer-readable storage medium for adding fields to a data warehouse. Figure 1 This is a schematic diagram of a system architecture according to an embodiment of this application. For example... Figure 1 As shown, the system includes a terminal device 102 and a server 104. The terminal device 102 can include at least one of the following: a smartphone, tablet, laptop, desktop computer, smart TV, various wearable devices, augmented reality (AR) devices, virtual reality (VR) devices, etc. A client can be installed on the terminal device 102. For example, the client can be a client specifically designed to perform a particular function (such as an application app), or a client with multiple embedded application applets (with different functions), or a client logged in via a browser. Users can operate on the terminal device 102. For example, a user can open the client installed on the terminal device 102 and input commands through the client, or a user can open the browser installed on the terminal device 102 and input commands through the browser. After receiving the user's input command, the terminal device 102 sends a request message containing the command to the server 104. After receiving the request message, the server 104 performs the corresponding processing and then returns the processing result information to the terminal device 102. The user command is completed through a series of data processing and information interaction.

[0026] In this document, terms such as first, second, and third are used only to distinguish one entity (or operation) from another, and are not intended to require or imply any order or relationship between these entities (or operations).

[0027] In this embodiment of the application, the data warehouse includes multiple data levels, wherein the first target table and the second target table to which the target field needs to be added are located in two adjacent levels in the data warehouse. Figure 2 This is a flowchart illustrating a method for adding fields to a data warehouse according to an embodiment of this application. Figure 2 As shown, the method for adding fields to a data warehouse includes the following steps:

[0028] S101: Generate statements for modifying the structure of the first target table;

[0029] S102: Obtain the first task ID corresponding to the first target table from the metadata of the first target table, and obtain the meta-code corresponding to the first task ID, wherein the first task ID is used to identify the data operation task performed on the first target table;

[0030] S103: Add one or more preset field information including the target field to the metacode of the first task ID to obtain the modified metacode of the first task ID;

[0031] S104: Execute the statement used to modify the table structure of the first target table, and run the modified metacode of the first task id to obtain the modified first target table, which includes the target field;

[0032] S105: Replace the data source containing the target field with the modified first target table, and perform the above steps on the second target table to add the target field to the second target table.

[0033] In the embodiments of this application, there can be many types of fields in the data warehouse, depending on the design and needs of the data warehouse. As business expands, new fields need to be added to the data warehouse to meet business requirements. In some embodiments of this application, optionally, the table structure of the data table needs to be modified before adding fields to the data table. For example, according to the number of fields to be added, a corresponding number of columns are added to the data table where the new fields are to be added, so that the new fields can be added to the header positions corresponding to the new columns.

[0034] In addition, in a data warehouse architecture, data tables typically reflect various business processes and transaction data. These data tables can be populated using ETL (Extract, Transform, Load) jobs. Each ETL job is usually assigned a unique identifier, namely a "task ID" or "job ID". The corresponding task ID can be obtained based on the information in the data table, and the metacode for manipulating the data table can be obtained based on the task ID. In some embodiments, optionally, the metacode corresponding to the task ID can be obtained through data warehouse management tools or by querying the ETL job configuration repository. The ETL job configuration repository stores detailed information related to each task ID, including SQL code, runtime parameters, dependencies, etc. In some highly automated ETL platforms, the relevant SQL script can be obtained directly through the interface or API based on the task ID.

[0035] In the embodiments of this application, after the system finds the metacode corresponding to the task ID, it adds preset field information, including the newly added field, to the metacode. The preset field information is added in the metacode before the keyword "from," and the name of the data source where the target field resides is following the keyword "from." Therefore, the location of the data source can be located by locating the keyword "from," and then the preset field information is added before the keyword "from." For example, if the new field to be added is "jobwelf," the original metacode is: (select jobid, jobname, nvl(jobarea, '000000') as jobarea, from ods.sjobs;); the modified metacode is: (select jobid, jobname, nvl(jobarea, '000000') as jobarea, jobwelf from ods.sjobs;). The system can add the new field to the data table by executing the modified metacode and the statement that modifies the table structure.

[0036] In practice, the first step is to add the new field to the first layer of the data warehouse, and then gradually add it to other layers. When adding a new field to a table in the second layer adjacent to the first layer, the table structure of the table in the second layer and the metacode corresponding to the task ID of the table in the second layer need to be modified. Preset information including the new field is added to this metacode. If the metacode already includes the data source information of the new field, it is not necessary to modify the data source information. For example, in some embodiments, the data warehouse may optionally include an ODS layer, a DWD layer, and a DWS layer. After adding the new field to a table in the ODS layer, statements for modifying the table structure in the DWD layer need to be generated. Then, the metacode corresponding to the task ID of the DWD layer table is found, and the preset field including the new field is added to this metacode. Executing the modified metacode and the statements for modifying the table structure will add the new field to the DWD layer. Adding new fields to the DWS layer is similar to the above-described DWD process, and will not be described in detail here.

[0037] According to embodiments of this application, only the field to be added, the address of the data source where the field is located, the name of the data table in the data warehouse to which the field will be added, and the address of the data warehouse are required to add the field to various levels of the data warehouse. In some embodiments, optionally, configuring these contents can be done simply by selecting from a drop-down page option; the entire operation is quick, simple, and efficient.

[0038] This application provides an easy-to-use method for adding fields. The entire operation is very simple and does not require cumbersome synchronization operations. You only need to select the corresponding settings. This simplifies the operation steps and is of great significance for large data warehouses with many fields and rapid updates.

[0039] In some embodiments of this application, optionally, the steps performed on the second target table include:

[0040] S201: Generate statements for modifying the structure of the second target table;

[0041] S202: Obtain the second task ID corresponding to the second target table from the metadata of the second target table, and obtain the meta-code corresponding to the second task ID, wherein the second task ID is used to identify the data operation task performed on the second target table;

[0042] S203: Add one or more preset field information including the target field to the metacode of the second task ID to obtain the modified metacode of the second task ID;

[0043] S204: Execute the statement used to modify the table structure of the second target table, and run the modified metacode of the second task id to obtain the modified second target table, which includes the target field.

[0044] In this embodiment, taking the example of a first target table located in the ODS layer of the data warehouse and a second target table located in the DWD layer of the data warehouse, the addition of new fields in the next lower layer is explained. Specifically, firstly, an SQL statement is generated to modify the structure of the second target table, adding corresponding columns to the table. Next, the task ID corresponding to the second target table is obtained from its metadata. This task ID is an identifier used to identify the data operation task performed on the second target table. Based on the task ID, the relevant metacode is obtained; metacode is code or script used to describe the data processing procedure. One or more preset field information is added to the metacode corresponding to the task ID, and this metacode includes information from the first target table. This allows fields to be added automatically by modifying the metacode corresponding to the task ID. Finally, the generated SQL statement for modifying the structure of the second target table is executed, and then the modified metacode is run. This yields the modified second target table.

[0045] In some embodiments of this application, optionally, the preset field information is a code parameter containing the field to be added, wherein the code parameter is used to filter the field to be added contained in the code parameter when running the modified metacode of the first task id, so that the field to be added with an empty value is replaced with an empty string.

[0046] In the embodiments of this application, the preset field information added to the corresponding metacode in the target table is a code parameter containing the field to be added. Adding this code parameter to the metacode can filter the added field, thereby replacing null values ​​of the field to be added with an empty string. For example, the preset field information can be "(nvl(jobphone,")as jobphone)", so if the newly added field jobphone is null, it will be replaced with "000000" in the target table. Because the data table containing the newly added field may contain null values, null values ​​may affect the integrity and accuracy of the data. Null value filtering of fields can ensure that the data stored in the data warehouse is complete and accurate. In addition, null values ​​may affect the accuracy of data mining and prediction models. Null value filtering of fields can improve the accuracy of data mining and prediction models. This application simplifies the operation steps by directly obtaining the preset field information including the target field, realizes the filtering of null values ​​in the field, further improves the accuracy of data synchronization, and ensures the integrity, accuracy, and quality of the data.

[0047] In some embodiments of this application, optionally, the metacode is SQL code, and the data source information is added after the "from" keyword in the SQL code to locate the location of the data source.

[0048] In this embodiment, the data source information added after the "from" keyword is the name of the table above the table to which the field is to be added, as well as the name of the hierarchy, which allows the location of the data source to be determined.

[0049] In some embodiments of this application, optionally, the first target table and the second target table are located at the i-th level and the (i+1)-th level in the data warehouse, respectively, wherein if i = 1, the data source is located outside the data warehouse; if i > 1, the data source is located at the (i-1)-th level of the data warehouse.

[0050] In the embodiments of this application, the fields to be added can come from a database outside the data warehouse or from the data warehouse itself. For example, it can be a typical database such as MySQL or Oracle, or a data warehouse such as Hive, Starrocks, or MaxCompute. After adding the fields to be added from outside the data warehouse to the first level of the data warehouse, they are then added step by step to the next level or the level after that, until they are added to all levels.

[0051] In some embodiments of this application, optionally, the data warehouse includes three data layers, from the first layer to the third layer: an operational data storage layer (ODS), a data detail layer (DWD), and a data service layer (DWS). If the first target table is located in the ODS layer of the data warehouse, then the data source is located outside the data warehouse, and the second target table is located in the DWD layer of the data warehouse; if the first target table is located in the DWD layer of the data warehouse, then the data source is located in the ODS layer of the data warehouse, and the second target table is located in the DWS layer of the data warehouse.

[0052] In the embodiments of this application, the data warehouse includes an operational data storage layer (ODS), a data detail layer (DWD), and a data service layer (DWS). The first target table and the second target table are located in two adjacent layers within the data warehouse. When adding a field, the new field is first added to the ODS layer of the data warehouse by modifying the corresponding metacode of the data table in the ODS layer. The ODS layer is a data storage area oriented towards the business operating system, typically used to store raw data extracted from various business systems (such as CRM and ERP systems), after cleaning, merging, and processing. Then, the metacode of the data table in the DWD layer is modified, where the data source after the `from` keyword in the metacode is the ODS layer of the data warehouse. Operations on the DWS layer are similar to those on the DWD layer, and will not be described in detail here.

[0053] In some embodiments of this application, the data source may optionally be any of the following: MySQL database, Oracle database, Hive data warehouse, Starrocks data warehouse, or MaxCompute data warehouse.

[0054] Optionally, in some embodiments of this application, a configuration interface is provided for inputting preset field information, the name of the data source, the address of the data source, and the name of the first target table or the second target.

[0055] In the embodiments of this application, a configuration interface can be provided, which allows users to input preset field information, the name of the data source, the address of the data source, and the name of the first target table or the second target table via drop-down options, thereby further improving the efficiency of adding fields.

[0056] For clarity, the operation process of the method for adding fields to a data warehouse according to the embodiments of this application will be described in detail below with reference to specific embodiments. In the following application scenario, the first target table is a data table in the ODS layer of the data warehouse named sjobs, and the second target table is a data table in the DWD layer of the sjobs data warehouse. The target fields operator, jobphone, and jobwelf come from table A of the Oracle database.

[0057] Figure 3 This is a flowchart illustrating a method for adding fields to a data warehouse according to an embodiment of this application. (In conjunction with...) Figure 3 As shown, the method of this application includes the following steps:

[0058] Step 1: Select the Oracle database (data source), configure the Oracle database address, and select the data table containing the target field in the Oracle database.

[0059] Step 2: Select the data table (target table) in the ODS layer of the sjobs data warehouse, and specify the target fields to be added this time (operator, nvl(jobphone,)as jobphone, jobwelf).

[0060] Step 3: Click "Generate with one click". The background will automatically add the fields from the Oracle database to the data table in the ODS layer of the Sjobs data warehouse, completing the addition of the fields.

[0061] Add fields to the ODS layer through steps one through three described above.

[0062] Step 4: Select the Sjobs data warehouse, configure the address of the Sjobs data warehouse, and select the data tables in the ODS layer.

[0063] Step 5: Select the data table in the DWD layer of the sjobs data warehouse and specify the target fields to be added this time (operator, nvl(jobphone,)as jobphone, jobwelf).

[0064] Step Six: Click the "Confirm" button. The background will automatically add the fields from the Oracle database to the data tables in the DWD layer of the Sjobs data warehouse, completing the field addition.

[0065] Add fields to the DWD layer through steps four through six above.

[0066] Additionally, if you want to add more fields to the DWS layer, simply make the corresponding adjustments based on the steps described above.

[0067] Accordingly, the operation steps triggered by the background system include:

[0068] Step 1: Generate statements to modify the table structure of data tables in the ODS layer of the Sjobs data warehouse. This language can be DDL.

[0069] Step 2: Obtain the backend code (metadata) of the data table in the ODS layer, determine the task ID corresponding to the data table in the ODS layer, and find the metacode that performs data operations on the data table in the ODS layer based on the task ID;

[0070] Step 3: Add the target fields operator, jobphone, and jobwelf to the metacode before the keyword "from". The data source after the keyword "from" in the metacode is the Oracle database. For example: (select jobid, jobname, nvl(jobarea, '000000') as jobarea, operator, nvl(jobphone, ") as jobphone, jobwelf from A.oracle;

[0071] Step 4: Execute the statements that modify the table structure of the data tables in the ODS layer of the Sjobs data warehouse and the modified metacode to add the target fields to the ODS layer of the Sjobs data warehouse.

[0072] Step 5: Generate statements to modify the table structure of data tables in the DWD layer of the Sjobs data warehouse;

[0073] Step 6: Obtain the backend code of the data table in the DWD layer, determine the task ID corresponding to the data table in the DWD layer, and find the metacode that performs data operations on the data table in the ODS layer based on the task ID;

[0074] Step 7: Add the target fields operator, jobphone, and jobwelf to the metacode before the keyword "from". The data source after the keyword "from" in the metacode is a data table in the DWD layer of the sjobs data warehouse. For example: (select jobid, jobname, nvl(jobarea, '000000') as jobarea, operator, nvl(jobphone, ") as jobphone, jobwelf from ods.sjobs;

[0075] Step 8: Execute the statements that modify the table structure of the data tables in the DWD layer of the sjobs data warehouse and the modified metacode to add the target fields to the DWD layer of the sjobs data warehouse.

[0076] Additionally, if you continue to add fields to the DWS layer, the code will continue to execute according to the steps above until the fields are added to the DWS layer of the data warehouse.

[0077] The method proposed in this application addresses scenarios where logical processing is not required, but new synchronized fields are frequently added. This rapid generation method improves code development efficiency and saves labor costs. Furthermore, for intermediate generation processes, such as table creation statements and SQL statements, a sample version is generated first and then approved, further improving the efficiency of adding fields to the data warehouse.

[0078] Corresponding to the method embodiments of this application, this application also provides an apparatus for adding fields to a data warehouse, the data warehouse including multiple data levels, wherein a first target table and a second target table to which the target field needs to be added are located in two adjacent levels in the data warehouse. Figure 4 As shown, the apparatus 300 for adding fields to a data warehouse includes:

[0079] The generation module 301 is used to generate statements for modifying the structure of the first target table.

[0080] The acquisition module 302 is used to obtain the first task ID corresponding to the first target table from the metadata of the first target table, and to obtain the meta-code corresponding to the first task ID, wherein the first task ID is used to identify the data operation task performed on the first target table;

[0081] Add module 303, which is used to add one or more preset field information including the field to be added and the data source information where the field to be added is located to the metacode of the first task ID, so as to obtain the modified metacode of the first task ID;

[0082] The first execution module 304 executes statements used to modify the table structure of the first target table, and runs the modified metacode of the first task id to obtain the modified first target table, which includes the target field.

[0083] The second execution module 305 is used to replace the data source of the field to be added with the modified first target table, and to perform the above steps on the second target table to add the target field to the second target table.

[0084] The apparatus for adding fields to a data warehouse according to the embodiments of this application can quickly add target fields to data tables in a data warehouse by simply configuring the data source location where the target field is located, the name of the target table to which the target field will be added, the name of the data warehouse, the target field, and other information.

[0085] In some embodiments, the second execution module 305 can be used to generate statements for modifying the structure of the second target table; obtain the second task ID corresponding to the second target table from the metadata of the second target table, and obtain the metacode corresponding to the second task ID, wherein the second task ID is used to identify the data operation task performed on the second target table; add one or more preset field information including the target field and information of the first target table to the metacode of the second task ID to obtain the modified metacode of the second task ID; execute the statements for modifying the structure of the second target table, and run the modified metacode of the second task ID to obtain the modified second target table, wherein the modified second target table includes the target field.

[0086] In some embodiments, the preset field information is a code parameter containing the field to be added. The code parameter is used to filter the field to be added contained in the code parameter when running the modified metacode of the first task id, so that the field to be added with an empty value is replaced with an empty string.

[0087] In some embodiments, the metacode is SQL code, with data source information added after the "from" keyword in the SQL code to locate the data source.

[0088] In some embodiments, the first target table and the second target table are located at the i-th level and the (i+1)-th level in the data warehouse, respectively, wherein if i = 1, the data source is located outside the data warehouse; if i > 1, the data source is located at the (i-1)-th level of the data warehouse.

[0089] In some embodiments, the data warehouse includes three data layers, from the first layer to the third layer: an operational data storage layer (ODS), a data detail layer (DWD), and a data service layer (DWS). If the first target table is located in the ODS layer of the data warehouse, the data source is located outside the data warehouse, and the second target table is located in the DWD layer of the data warehouse. If the first target table is located in the DWD layer of the data warehouse, the data source is located in the ODS layer of the data warehouse, and the second target table is located in the DWS layer of the data warehouse.

[0090] In some embodiments, the data source is any of the following: MySQL database, Oracle database, Hive data warehouse, Starrocks data warehouse, or MaxCompute data warehouse.

[0091] In some embodiments, the apparatus 300 for adding fields to a data warehouse further includes a configuration module for providing a configuration interface for inputting preset field information, the name of the data source, the address of the data source, and the name of a first target table or a second target.

[0092] The electronic device in this application embodiment may be a user terminal device, a server, other computing devices, or a cloud server. Figure 5 This is a schematic diagram of the hardware structure of an electronic device according to an embodiment of this application. The electronic device may include a processor 401 and a memory 402 storing computer program instructions. When the processor 401 executes the computer program instructions, it implements the process or function of any of the methods described above.

[0093] Specifically, processor 401 may include a central processing unit (CPU), or an application-specific integrated circuit (ASIC), or one or more integrated circuits configured to implement the embodiments of this application. Memory 402 may include mass storage for data or instructions. For example, memory 402 may be at least one of the following: hard disk drive (HDD), read-only memory (ROM), random access memory (RAM), floppy disk drive, flash memory, optical disk, magneto-optical disk, magnetic tape, universal serial bus (USB) drive, or other physical / tangible memory storage device. Alternatively, memory 402 may include removable or non-removable (or fixed) media. Furthermore, memory 402 may be internal or external to the integrated gateway disaster recovery device. Memory 402 may be non-volatile solid-state memory. In other words, typically memory 402 includes a tangible (non-transitory) computer-readable storage medium (such as a memory device) encoded with computer-executable instructions, and when the software is executed (e.g., by one or more processors), it can perform the operations described in the methods of the embodiments of this application. The processor 401 implements the process or function of any of the methods described in the above embodiments by reading and executing computer program instructions stored in the memory 402.

[0094] In one example Figure 5The illustrated electronic device may also include a communication interface 403 and a bus 410. The processor 401, memory 402, and communication interface 403 are connected via bus 410 and communicate with each other. Communication interface 403 is primarily used to enable communication between modules, devices, units, and / or equipment in the embodiments of this application. Bus 410 may include hardware, software, or both, and can couple components of the online data traffic billing device together. For example, the bus may include at least one of the following: Accelerated Graphics Port (AGP) or other graphics bus, Enhanced Industry Standard Architecture (EISA) bus, Front Side Bus (FSB), HyperTransport (HT) Interconnect, Industry Standard Architecture (ISA) bus, Infinite Bandwidth Interconnect, Low Pin Count (LPC) bus, memory bus, Microchannel Architecture (MCA) bus, Peripheral Component Interconnect (PCI) bus, PCI-Express (PCI-X) bus, Serial Advanced Technology Attachment (SATA) bus, Video Electronics Standards Association Local (VLB) bus, or other suitable buses. Bus 410 may include one or more buses. Although specific buses are described or illustrated in the embodiments of this application, any suitable bus or interconnection method may be considered in the embodiments of this application.

[0095] In conjunction with the methods in the above embodiments, this application also provides a computer-readable storage medium storing computer program instructions, which, when executed by a processor, implement the process or function of any of the methods in the above embodiments.

[0096] In addition, this application also provides a computer program product that stores computer program instructions, which, when executed by a processor, implement the process or function of any of the methods described above.

[0097] The flowcharts and / or block diagrams of methods, apparatuses, systems, and computer program products according to embodiments of this application have been exemplarily described above, and related aspects have been described. It should be understood that each block or combination thereof in the flowcharts and / or block diagrams may be implemented by computer program instructions, by dedicated hardware performing a specified function or action, or by a combination of dedicated hardware and computer instructions. For example, these computer program instructions may be provided to a processor of a general-purpose computer, a special-purpose computer, or other programmable data processing apparatus to form a machine that enables the implementation of the function / action specified in each block or combination thereof in the flowcharts and / or block diagrams, executable via such processor. Such a processor may be a general-purpose processor, a dedicated processor, a special-purpose application processor, or a field-programmable logic circuit.

[0098] The functional blocks shown in the structural block diagrams of this application can be implemented as hardware, software, firmware, or a combination thereof. When implemented in hardware, they can be, for example, electronic circuits, application-specific integrated circuits (ASICs), appropriate firmware, plug-ins, function cards, etc.; when implemented in software, they are programs or code segments used to perform the required tasks. Programs or code segments can be stored in memory or transmitted over a transmission medium or communication link via data signals carried on a carrier wave. Code segments can be downloaded via computer networks such as the Internet or intranets.

[0099] It should be noted that this application is not limited to the specific configurations and processes described above or shown in the figures. The above descriptions are merely specific embodiments of this application. Those skilled in the art will clearly understand that, for the sake of convenience and brevity, the specific working processes of the described systems, devices, modules, or units can be referred to the corresponding processes in the method embodiments, and need not be repeated here. It should be understood that the scope of protection of this application is not limited thereto. Any person skilled in the art can conceive of various equivalent modifications or substitutions within the scope of the technology disclosed in this application, and these modifications or substitutions should all be covered within the scope of protection of this application.

Claims

1. A method for adding fields to a data warehouse, characterized in that, The data warehouse includes multiple data levels, wherein the first target table and the second target table to which the target field needs to be added are located in two adjacent levels of the data warehouse, respectively. The method includes: Generate statements for modifying the structure of the first target table; Obtain the first task ID corresponding to the first target table from the metadata of the first target table, and obtain the meta-code corresponding to the first task ID, wherein the first task ID is used to identify the data operation task performed on the first target table, and the meta-code is SQL code used to describe the data processing process; Add one or more preset field information, including the target field, to the metacode of the first task ID to obtain the modified metacode of the first task ID; Execute statements to modify the table structure of the first target table, and run the modified metacode of the first task ID to obtain the modified first target table, wherein the modified first target table includes the target field; Replace the data source containing the target field with the modified first target table, and perform the following steps on the second target table to add the target field to the second target table: Generate statements for modifying the structure of the second target table; Obtain the second task ID corresponding to the second target table from the metadata of the second target table, and obtain the meta-code corresponding to the second task ID, wherein the second task ID is used to identify the data operation task performed on the second target table; Add one or more preset field information, including the target field, to the metacode of the second task ID to obtain the modified metacode of the second task ID; Execute statements to modify the table structure of the second target table, and run the modified metacode of the second task ID to obtain the modified second target table, which includes the target field.

2. The method according to claim 1, characterized in that, The preset field information is a code parameter containing fields to be added. The code parameter is used to filter the fields to be added contained in the code parameter when running the modified metacode of the first task id, so that the fields to be added with empty values ​​are replaced with empty strings.

3. The method according to claim 1, characterized in that, Add the data source information after the "from" keyword in the SQL code to locate the data source.

4. The method according to claim 1, characterized in that, The first target table and the second target table are located at the i-th level and the (i+1)-th level in the data warehouse, respectively. If i=1, the data source is located outside the data warehouse; if i>1, the data source is located at the (i-1)-th level of the data warehouse.

5. The method according to claim 4, characterized in that, The data warehouse comprises three data layers, from the first to the third layer: Operational Data Storage (ODS) layer, Data Detailed Storage (DWD) layer, and Data Service (DWS) layer. If the first target table is located in the ODS layer of the data warehouse, then the data source is located outside the data warehouse, and the second target table is located in the DWD layer of the data warehouse; If the first target table is located in the DWD layer of the data warehouse, then the data source is located in the ODS layer of the data warehouse, and the second target table is located in the DWD layer of the data warehouse.

6. The method according to claim 1, characterized in that, The data source can be any of the following: MySQL database, Oracle database, Hive data warehouse, Starrocks data warehouse, or MaxCompute data warehouse.

7. The method according to claim 1, characterized in that, Also includes: A configuration interface is provided, which is used to input the preset field information, the name of the data source, the address of the data source, and the name of the first target table or the second target table.

8. An apparatus for adding fields to a data warehouse, characterized in that, The data warehouse includes multiple data levels, wherein the first target table and the second target table to which the target field needs to be added are located in two adjacent levels of the data warehouse, respectively. The apparatus includes: The generation module is used to generate statements for modifying the structure of the first target table. The acquisition module is used to obtain the first task ID corresponding to the first target table from the metadata of the first target table, and to obtain the meta-code corresponding to the first task ID, wherein the first task ID is used to identify the data operation task performed on the first target table, and the meta-code is SQL code used to describe the data processing process. The module is used to add one or more preset field information, including the field to be added, to the metacode of the first task ID, so as to obtain the modified metacode of the first task ID. The first execution module executes statements for modifying the table structure of the first target table and runs the modified metacode of the first task ID to obtain the modified first target table, wherein the modified first target table includes the target field; The second execution module is used to replace the data source of the field to be added with the modified first target table, and to perform the following steps on the second target table to add the target field to the second target table: Generate statements for modifying the structure of the second target table; Obtain the second task ID corresponding to the second target table from the metadata of the second target table, and obtain the meta-code corresponding to the second task ID, wherein the second task ID is used to identify the data operation task performed on the second target table; Add one or more preset field information, including the target field, to the metacode of the second task ID to obtain the modified metacode of the second task ID; Execute statements to modify the table structure of the second target table, and run the modified metacode of the second task ID to obtain the modified second target table, which includes the target field.

9. An electronic device, characterized in that, The electronic device includes a processor and a memory storing computer program instructions; when the electronic device executes the computer program instructions, it implements the method as described in any one of claims 1-7.

10. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores computer program instructions that, when executed by a processor, implement the method as described in any one of claims 1-7.

11. A computer program product, characterized in that, It includes computer program instructions that, when executed by a processor, implement the method as described in any one of claims 1-7.

Images