A data processing method and device

By obtaining the full script of the data warehouse and the reinforcement learning algorithm, the problem of missing fields in the construction of two-dimensional tables is solved, fast and high-reliability correlation is achieved, and the data development cycle is shortened.

CN113779002BActive Publication Date: 2024-11-15BEIJING WODONG TIANJUN INFORMATION TECH CO LTD +1
View PDF 11 Cites 0 Cited by

Patent Information

Application Number
CN202011248188.3
Authority / Receiving Office
CN · China
Patent Type
Patents(China)
Current Assignee / Owner
Filing Date
2020-11-10
Publication Date
2024-11-15
Estimated Expiration
2040-11-10

AI Technical Summary

Technical Problem

During the construction of two-dimensional tables, when the fields are missing, the corresponding fields need to be obtained from other tables, resulting in large labor, time and energy consumption, and the data quality is not high, resulting in a longer data development cycle.

Method used

By receiving a two-dimensional table construction request, obtaining the full script of the data warehouse, extracting the association relationship between the table, using reinforcement learning algorithm to obtain the best association path, obtaining the corresponding fields, and constructing a two-dimensional table.

Benefits of technology

It realizes fast and high-confidence correlation of required fields, reducing manpower and time consumption, and shortening data development cycle.

✦ Generated by Eureka AI based on patent content.

Smart Images

  • Figure CN113779002B_ABST
    Figure CN113779002B_ABST
Patent Text Reader

Abstract

The present invention discloses a data processing method and device, and relates to the field of computer technology. A specific implementation of the method includes receiving a two-dimensional table construction request, obtaining a full script of a data warehouse, extracting tables in each script and the association relationship between tables, and then obtaining all reachable association paths from a start table to a target table; performing reinforcement learning on all reachable association paths according to a preset selection model to obtain an optimal association path; according to the optimal association path, obtaining corresponding fields with association relationships between different tables in the path, constructing a two-dimensional table, and outputting the two-dimensional table. Thus, the implementation of the present invention can solve the problems of low efficiency and high cost of existing data modeling.
Need to check novelty before this filing date? Find Prior Art

Description

Technical Field

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

[0002] Whether it is data modeling, data analysis, data development, or deep data mining, the association between tables is inevitable in the process of structured data application. The corresponding fields are obtained from different tables for modeling (referring to the establishment of a structured two-dimensional table model). At present, in the process of building a two-dimensional table, if a field is missing and needs to be obtained from other tables, it is basically based on business model research to obtain the corresponding fields and then associate. Among them, data modeling refers to the process of establishing a two-dimensional structured model in the data warehouse, that is, the process of building a two-dimensional table.

[0003] In the process of implementing the present invention, the inventors found that there are at least the following problems in the prior art:

[0004] At present, in the process of building a two-dimensional table, if a field is missing, it is necessary to obtain the field from other tables. Basically, the corresponding fields are obtained through research and then associated. The above method consumes a lot of manpower, time and energy, and the quality of the model data found is usually not reliable. In addition, if new employees are not familiar with the tables in the data warehouse, they need to rely heavily on the modeling experience of old employees for guidance, which wastes a lot of time of old employees. At the same time, it is impossible to know whether the online development has been associated, so it takes a lot of time to check and verify the data, which makes the data development cycle longer. Summary of the invention

[0005] In view of this, an embodiment of the present invention provides a data processing method and device, which can solve the problems of low efficiency and high cost of existing data modeling.

[0006] To achieve the above-mentioned purpose, according to one aspect of an embodiment of the present invention, a data processing method is provided, including receiving a two-dimensional table construction request, obtaining a full script of a data warehouse, extracting the tables in each script and the association relationships between the tables, and then obtaining all reachable association paths from the start table to the target table; performing reinforcement learning on all reachable association paths according to a preset selection model to obtain an optimal association path; and according to the optimal association path, obtaining corresponding fields with association relationships between different tables in the path, constructing a two-dimensional table, and outputting the two-dimensional table.

[0007] Optionally, after obtaining the full script of the data warehouse, include:

[0008] Call the preset cleaning component to perform data cleaning on the entire data warehouse script.

[0009] Optionally, extract the tables in each script and the relationships between the tables, including:

[0010] According to regular expression matching, the tables in each script and the relationships between the tables are extracted by keywords.

[0011] Optionally, before reinforcement learning is performed on all reachable associated paths according to a preset selection model, including:

[0012] Get the number of times users use each table and the duration of each execution, and calculate the average execution time;

[0013] The number of times the user uses the function is normalized, and the normalized number of times the user uses the function is summed with the average execution time to obtain a correlation coefficient between the tables, and the correlation coefficient is used as a reward value for reinforcement learning.

[0014] Optionally, extract the tables in each script and the associations between the tables, and then obtain all reachable association paths from the start table to the target table, including:

[0015] According to the tables in the script and the association relationships between tables, the Cartesian product algorithm is used to calculate the associated paths for the fields in all tables, and then all reachable association paths from the start table to the target table are obtained.

[0016] In addition, the present invention also provides a data processing device, including an acquisition module, which is used to receive a two-dimensional table construction request, obtain the full script of the data warehouse, extract the tables in each script and the association relationship between the tables, and then obtain all the reachable association paths from the starting table to the target table; a processing module, which is used to perform reinforcement learning on all the reachable association paths according to a preset selection model to obtain the best association path; according to the best association path, obtain the corresponding fields with association relationships between different tables in the path, construct a two-dimensional table, and output the two-dimensional table. .

[0017] Optionally, before the processing module performs reinforcement learning on all reachable associated paths according to a preset selection model, the processing module includes:

[0018] Get the number of times users use each table and the duration of each execution, and calculate the average execution time;

[0019] The number of times the user uses the function is normalized, and the normalized number of times the user uses the function is summed with the average execution time to obtain a correlation coefficient between the tables, and the correlation coefficient is used as a reward value for reinforcement learning.

[0020] Optionally, the acquisition module extracts the association relationship between tables in each script and between tables, and then obtains all reachable association paths from the starting table to the target table, including:

[0021] According to the tables in the script and the association relationships between tables, the Cartesian product algorithm is used to calculate the associated paths for the fields in all tables, and then all reachable association paths from the start table to the target table are obtained.

[0022] One embodiment of the above invention has the following advantages or beneficial effects: The present invention obtains the association relationship between tables based on the full online script. In actual development, the field required only needs to obtain the table including the field through metadata, and the association relationship between the table including the required field in other historical online task scripts and the main table being developed can be found, so that the required field can be quickly associated with high credibility. At the same time, combined with reinforcement learning, the optimal association relationship between tables is obtained.

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

[0024] The accompanying drawings are used to better understand the present invention and do not constitute an improper limitation of the present invention.

[0025] Figure 1 is a schematic diagram of the main flow of the data processing method according to the first embodiment of the present invention;

[0026] Figure 2 is a schematic diagram of an association path according to an embodiment of the present invention;

[0027] Figure 3 is a schematic diagram of the main flow of a data processing method according to a second embodiment of the present invention;

[0028] Figure 4 is a schematic diagram of main modules of a data processing device according to an embodiment of the present invention;

[0029] Figure 5 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

[0030] The following is a description of exemplary embodiments of the present invention in conjunction with the accompanying drawings, including various details of the embodiments of the present invention to facilitate understanding, which should be considered as merely exemplary. Therefore, it should be recognized by those of ordinary skill 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 clarity and conciseness, the description of well-known functions and structures is omitted in the following description.

[0031] Figure 1 is a schematic diagram of the main flow of the data processing method according to the first embodiment of the present invention. Figure 1 As shown, the data processing method includes:

[0032] Step S101, receiving a two-dimensional table construction request, obtaining the full script of the data warehouse, extracting the tables in each script and the association relationships between the tables, and then obtaining all reachable association paths from the starting table to the target table.

[0033] In the embodiment, step S101 calculates the association between all the fields extracted by the script, preferably by using the Cartesian product algorithm to calculate the associated path for the fields in all the tables, wherein the path includes the starting table and the target table. That is, a network connectivity graph is constructed by using the tables as nodes and the associated relationships as edges, and the associated path from one node to another node can be obtained from the connectivity graph.

[0034] In some embodiments, after obtaining the full script of the data warehouse, a preset cleaning component may be called to perform data cleaning on the full script of the data warehouse. For example, obtaining the full SQL script of the data warehouse and calling the desensitizing component to perform data cleaning.

[0035] As other embodiments, extracting the tables in each script and the associations between the tables specifically includes: extracting the tables in each script and the associations between the tables by keywords according to regular matching. For example: extracting the tables in each script and the associations between the tables by keywords on, fdm, adm data warehouse library name, etc., where on is a keyword used when associating tables in the SQL language, and fdm and adm are library names in the data warehouse) according to regular matching.

[0036] It is worth noting that all the pairwise associations between tables are obtained, and from this data, the reachable association path between the starting table and the target table can be obtained, for example: Figure 2 As shown, there are two association paths between the starting table adm.adm_s03 and the target table app.app_cmo.

[0037] Step S102: Perform reinforcement learning on all reachable associated paths according to a preset selection model to obtain an optimal associated path.

[0038] In some embodiments, before performing reinforcement learning on all reachable associated paths according to a preset selection model, the process includes:

[0039] Obtain the number of times users use the tables in each association and the duration of each execution, and calculate the average execution duration. Normalize the number of times users use, sum the normalized number of times users use and the average execution duration, and obtain the correlation coefficient between the tables, and then use the correlation coefficient as the reward value for reinforcement learning. For example: Figure 2As shown, the statistical usage count of all user scripts between table adm.adm_s03 and table adm.adm_s14 is 6 times, which is 0.06 after normalization. The average execution time of all users is 0.04, and the final sum score is 0.1.

[0040] As some other embodiments, before performing reinforcement learning on all reachable associated paths according to a preset selection model, the following steps are included:

[0041] The table in the association path is used as an agent (Agent, in the reinforcement learning algorithm, the action issuer who applies different actions in different environments is called an agent) for reinforcement learning using the selection model, and the association relationship between the tables is used as the environment (Environment) for reinforcement learning using the selection model. The action space (action) in the reinforcement learning model is executed from the starting table as the agent, and the execution result is sent to the environment so that the environment returns the association coefficient corresponding to the agent and each of the other associated agents. Then, according to the association coefficient, the agent that is best associated with the agent is selected from the other associated agents, for example, the agent with the largest association coefficient is the agent that is best associated with the agent. The above process is repeated until the target agent (i.e., the target table) is associated, and then the best association path from the starting table to the target table is obtained. Among them, the action space is the process of reaching other agents by selecting an agent among n agents to perform association operations.

[0042] In addition, in the process of reinforcement learning of the present invention, the state is the state of the node (i.e., table), and the paths from all tables to the target table are represented as a sequence, for example (0, 0, 0, 1, 0, 0...), where the node currently located is set to 1, and the other nodes are set to 0, and this sequence is used as the state.

[0043] It is worth noting that the path with the lowest comprehensive score is calculated by the following loss function as the best associated path:

[0044] loss=(r+y max Qtarge(s',a)-Qcurrent(s,a)) 2

[0045] Among them, r is the reward value in reinforcement learning, which is the reward value given by the environment after the agent takes a certain action; y is the greed threshold of the greedy strategy, Qcurrent(s',a) is the Q estimate, that is, the current estimated Q value, which is the value obtained after the current state state and action action are used as the input of the neural network; Qtarget(s,a) is the value obtained after the next state state and action action are used as the input of the neural network. S is the state of the agent during the exploration process; a is the action taken by the agent in each state.

[0046] Figure 3 is a schematic diagram of the main flow of a data processing method according to a second embodiment of the present invention. The data processing method may include:

[0047] Receive a two-dimensional table construction request, obtain the full script of the data warehouse, that is, trigger historical data collection, and extract historical task scripts. In this way, the association relationship between tables in each script and between tables is obtained, and then all reachable association paths between the starting table and the target table are obtained. All reachable association paths are learned using a preset selection model to obtain the best association path. According to the best association path, the corresponding fields with association relationships between different tables in the path are obtained, and a two-dimensional table is constructed to output the two-dimensional table.

[0048] Preferably, before using the preset selection model to learn all reachable associated paths, the selection model needs to be trained. The specific implementation process includes:

[0049] Initialize two networks with exactly the same structure but different parameters: Current Net and Target Net. The former is called the estimation network, which is used to train the input of the current state. It is also the network obtained by the actual training in DQN (a value-based algorithm in the reinforcement learning algorithm, a value function approximation algorithm), while the latter is called the actual network, which is used to save the execution parameters in the estimation network at each stage to cut off the correlation. Replay buffer is called memory or playback cache. The training data is randomly extracted from the memory bank, which records the action, reward and result of the next state in each state.

[0050] As an embodiment, first initialize the replay buffer, preprocess the environment, input the current state into the network Current Net, and return the Q values ​​of all possible actions in the state. Use a greedy strategy to select an action: for example, select a random action A, and when a certain probability threshold is reached, select the action with the highest Q value. After selecting action A, the agent performs the selected action in the state state, obtains a new state state, and receives a reward R. The conversion samples including the action action, reward (reward) and the result of the next state in the state are stored in the replay buffer. After that, a random batch of conversion samples are extracted from the replay buffer and the loss loss is calculated. For the parameters of the real network Target Net, gradient descent is performed to minimize the loss. After every few steps, copy the Target Net weights to the target network weights. Repeat these steps for several rounds to obtain a trained selection model.

[0051] Figure 4 is a schematic diagram of main modules of a data processing device according to an embodiment of the present invention, such as Figure 4 As shown, the data processing device 400 includes an acquisition module 401 and a processing module 402. The acquisition module 401 receives a two-dimensional table construction request, obtains the full script of the data warehouse, extracts the tables in each script and the associations between the tables, and then obtains all the reachable association paths from the start table to the target table; the processing module 402 performs reinforcement learning on all the reachable association paths according to a preset selection model to obtain the best association path; according to the best association path, obtains the corresponding fields with associations between different tables in the path, constructs a two-dimensional table, and outputs the two-dimensional table.

[0052] In some embodiments, after the acquisition module 401 acquires the full script of the data warehouse, it includes:

[0053] Call the preset cleaning component to perform data cleaning on the entire data warehouse script.

[0054] In some embodiments, the acquisition module 401 extracts the tables in each script and the associations between the tables, including:

[0055] According to regular expression matching, the tables in each script and the relationships between the tables are extracted by keywords.

[0056] In some embodiments, before the processing module 402 performs reinforcement learning on all reachable associated paths according to a preset selection model, the processing module 402 includes:

[0057] The number of times users use each table and the duration of each execution are obtained, and the average execution duration is calculated. The number of times users use is normalized, and the normalized number of times users use and the average execution duration are summed to obtain the correlation coefficient between the tables, and the correlation coefficient is used as the reward value of reinforcement learning.

[0058] Action Space In some embodiments, the acquisition module extracts the tables in each script and the associations between the tables, and then obtains all reachable association paths from the starting table to the target table, including:

[0059] According to the tables in the script and the association relationships between tables, the Cartesian product algorithm is used to calculate the associated paths for the fields in all tables, and then all reachable association paths from the start table to the target table are obtained.

[0060] It should be noted that the data processing method and the data processing device described in the present invention have a corresponding relationship in terms of specific implementation contents, so the repeated contents will not be described again.

[0061] Reference below Figure 5 , which shows a schematic diagram of the structure of a computer system 500 of a terminal device suitable for implementing an embodiment of the present invention. Figure 5 The terminal device 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.

[0062] like Figure 5 As shown, the computer system 500 includes a central processing unit (CPU) 501, which can perform various appropriate actions and processes according to a program stored in a read-only memory (ROM) 502 or a program loaded from a storage part 508 into a random access memory (RAM) 503. In the RAM 503, various programs and data required for the operation of the computer system 500 are also stored. The CPU 501, the ROM 502, and the RAM 503 are connected to each other via a bus 504. An input / output (I / O) interface 505 is also connected to the bus 504.

[0063] The following components are connected to the I / O interface 505: an input section 506 including a keyboard, a mouse, etc.; an output section 507 including a cathode ray tube (CRT), a liquid crystal data processor (LCD), etc., and a speaker, etc.; a storage section 508 including a hard disk, etc.; and a communication section 509 including a network interface card such as a LAN card, a modem, etc. The communication section 509 performs communication processing via a network such as the Internet. A drive 510 is also connected to the I / O interface 505 as needed. A removable medium 511, such as a magnetic disk, an optical disk, a magneto-optical disk, a semiconductor memory, etc., is installed on the drive 510 as needed, so that a computer program read therefrom is installed into the storage section 508 as needed.

[0064] In particular, according to the embodiments disclosed in the present invention, the process described above with reference to the flowchart can be implemented as a computer software program. For example, the embodiments disclosed in the present invention include a computer program product, which includes a computer program carried on a computer-readable medium, and the computer program includes a program code for executing the method shown in the flowchart. In such an embodiment, the computer program can be downloaded and installed from the network through the communication part 509, and / or installed from the removable medium 511. When the computer program is executed by the central processing unit (CPU) 501, the above-mentioned functions defined in the system of the present invention are executed.

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

[0066] The flow chart and block diagram in the accompanying drawings illustrate the possible architecture, function and operation of the system, method and computer program product according to various embodiments of the present invention. In this regard, each box in the flow chart or block diagram can represent a module, a program segment, or a part of a code, and the above-mentioned module, program segment, or a part of a code contains one or more executable instructions for realizing the specified logical function. It should also be noted that in some alternative implementations, the functions marked in the box can also occur in a different order from the order 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 flow chart, and the combination of the boxes in the block diagram or flow chart can be implemented with a dedicated hardware-based system that performs a specified function or operation, or can be implemented with a combination of dedicated hardware and computer instructions.

[0067] The modules involved in the embodiments of the present invention may be implemented by software or hardware. The modules described may also be set in a processor. For example, it may be described as follows: a processor includes an acquisition module and a processing module. The names of these modules do not, in some cases, limit the modules themselves.

[0068] As another aspect, the present invention also provides a computer-readable medium, which may be included in the device described in the above embodiment; or it may exist independently without being assembled into the device. The above computer-readable medium carries one or more programs. When the above one or more programs are executed by a device, the device includes receiving a two-dimensional table construction request, obtaining a full script of the data warehouse, extracting the tables in each script and the associations between the tables, and then obtaining all the reachable association paths from the starting table to the target table; performing reinforcement learning on all the reachable association paths according to a preset selection model to obtain the best association path; according to the best association path, obtaining the corresponding fields with associations between different tables in the path, constructing a two-dimensional table, and outputting the two-dimensional table.

[0069] The technical solution according to the embodiment of the present invention can solve the problems of low efficiency and high cost of existing data modeling.

[0070] The above specific implementations do not constitute a limitation on the protection scope of the present invention. It should be understood by those skilled in the art that various modifications, combinations, sub-combinations and substitutions may occur depending on design requirements and other factors. Any modification, equivalent substitution and improvement made within the spirit and principle of the present invention shall be included in the protection scope of the present invention.

Claims

1. A data processing method, characterized in that: include: Receive a two-dimensional table building request, obtain the full script of the data warehouse, extract the tables in each script and the associations between the tables, and then obtain all reachable association paths from the starting table to the target table; Perform reinforcement learning on all reachable association paths according to the preset selection model to obtain the best association path; According to the optimal association path, corresponding fields with association relationships between different tables in the path are obtained, and a two-dimensional table is constructed to output the two-dimensional table.

2. The method according to claim 1, characterized in that After obtaining the full script of the data warehouse, including: Call the preset cleaning component to perform data cleaning on the entire data warehouse script.

3. The method according to claim 1, characterized in that Extract the tables in each script and the relationships between them, including: According to regular expression matching, the tables in each script and the relationships between the tables are extracted by keywords.

4. The method according to claim 1, characterized in that Before reinforcement learning is performed on all reachable associated paths according to the preset selection model, including: Get the number of times users use each table and the duration of each execution, and calculate the average execution time; The number of times the user uses the function is normalized, and the normalized number of times the user uses the function is summed with the average execution time to obtain a correlation coefficient between the tables, and the correlation coefficient is used as a reward value for reinforcement learning.

5. The method according to any one of claims 1 to 4, characterized in that: Extract the tables in each script and the relationships between them, and then obtain all reachable relationship paths from the start table to the target table, including: According to the tables in the script and the association relationships between tables, the Cartesian product algorithm is used to calculate the associated paths for the fields in all tables, and then all reachable association paths from the start table to the target table are obtained.

6. A data processing device, characterized in that: include: The acquisition module is used to receive a two-dimensional table construction request and obtain the full script of the data warehouse to extract the tables in each script and the associations between the tables, and then obtain all the reachable association paths from the starting table to the target table; The processing module is used to perform reinforcement learning on all reachable association paths according to a preset selection model to obtain an optimal association path; according to the optimal association path, obtain corresponding fields with association relationships between different tables in the path, construct a two-dimensional table, and output the two-dimensional table.

7. The device according to claim 6, characterized in that Before the processing module performs reinforcement learning on all reachable associated paths according to the preset selection model, it includes: Get the number of times users use each table and the duration of each execution, and calculate the average execution time; The number of times the user uses the function is normalized, and the normalized number of times the user uses the function is summed with the average execution time to obtain a correlation coefficient between the tables, and the correlation coefficient is used as a reward value for reinforcement learning.

8. The device according to claim 6 or 7, characterized in that The acquisition module extracts the association relationship between tables in each script and between tables, and then obtains all reachable association paths from the starting table to the target table, including: According to the tables in the script and the association relationships between tables, the Cartesian product algorithm is used to calculate the associated paths for the fields in all tables, and then all reachable association paths from the start table to the target table are obtained.

9. An electronic device, 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 5.

10. 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 5 is implemented.

Citation Information

Patent Citations

  • Optimization method and device for open type data processing service

    CN104899209A

  • Memory association reinforcement learning-based embedded real-time underwater robot intelligent decision-making method

    CN108762281A

  • Method and device for constructing data model

    CN109002289A

  • Method, system, device and equipment for querying association relationship of data records

    CN111506613A

  • Information query method and device, electronic equipment and storage medium

    CN111552792A