Data processing method and device for linked list

Abstract

This application provides a data processing method for a zipper table. The method includes: obtaining an initial SQL statement to update incremental data from an incremental table to the zipper table; obtaining zipper table information and incremental information based on the initial SQL statement; obtaining the zipper table name based on the zipper table information; obtaining the incremental table name based on the incremental information; generating a first query statement based on the zipper table name and the incremental table name, the first query statement being used to query the target existing data in the zipper table; and generating a target SQL statement based on the zipper table information, the first query statement, and the incremental information, used to add the incremental data to the zipper table and update the end date of the target existing data. The technical solution of this application can automatically generate production logic for zipper tables, effectively reducing development costs, lowering development complexity, and mitigating the probability of human error during development.

Description

Technical Field

[0001] This application relates to the field of data processing technology, and in particular to a data processing method, apparatus, computer device, and computer-readable storage medium for zipper tables. Background Technology

[0002] A data warehouse is a database system used to integrate, store, and manage large amounts of data. It offers advantages such as subject-oriented design, integration, non-volatility, and the ability to reflect historical changes, and can be used as a dataset to support user decision-making. Data warehouses can store valuable business data long-term or permanently to facilitate data analysis, decision support, and data mining. For ease of generation and use, data can be stored in the form of linked lists.

[0003] However, due to the complexity and cumbersome nature of the business logic of zipper tables, it is impossible to automatically generate production logic for zipper tables, resulting in a complex development process with problems such as high development difficulty, high probability of human error during development, and high development costs.

[0004] It should be noted that the above content is not necessarily prior art, nor is it intended to limit the scope of patent protection of this application. Summary of the Invention

[0005] This application provides a data processing method, apparatus, computer device, and computer-readable storage medium for zipper tables to solve or alleviate one or more of the technical problems mentioned above.

[0006] One aspect of this application provides a data processing method for a zipper table, the method comprising:

[0007] Obtain the initial SQL statement, which is used to update the incremental data in the incremental table to the zipper table;

[0008] Based on the initial SQL statement, obtain the zipper table information and incremental information;

[0009] Obtain the zipper table name based on the zipper table information;

[0010] Obtain the name of the incremental table based on the incremental information;

[0011] Based on the zipper table name and the incremental table name, a first query statement is generated. The first query statement is used to query the target existing data of the zipper table.

[0012] Based on the zipper table information, the first query statement, and the incremental information, generate the target SQL statement;

[0013] The target SQL statement is used to: add the incremental data to the zipper table and update the end date of the target existing data to record historical data changes.

[0014] Optionally, the zipper table information and incremental information are obtained based on the initial statement, including:

[0015] Parse the initial SQL statement to obtain an abstract syntax tree, which includes directory table nodes and query nodes;

[0016] The zipper table information is determined based on the contents of the directory table nodes;

[0017] The incremental information is determined based on the content of the query node.

[0018] Optionally, based on the zipper table information, the first query statement, and the incremental information, a target SQL statement is generated, including:

[0019] The first query statement and the incremental information are merged to obtain the second query statement;

[0020] The information in the zipper table and the second query statement are merged to obtain the target SQL statement.

[0021] Optionally, a first query statement is generated based on the zipper table name and the incremental table name, including:

[0022] Based on the zipper table name, the incremental table name, and the pre-defined zipper table stock data query logic, the first query statement is generated, which includes query conditions and update rules.

[0023] The query conditions are used to: find the target existing data in the zipper table;

[0024] The update rule is used to update the target existing data when the target existing data exists.

[0025] Optionally, the query conditions include a first query condition and a second query condition;

[0026] Correspondingly, the step of searching for the target existing data in the zipper table includes:

[0027] Based on the first query condition, determine the latest historical data in the zipper table;

[0028] If the latest historical data exists, the target stock data is determined according to the second query condition.

[0029] Optionally, if the latest historical data exists, the target existing data is determined according to the second query condition, including:

[0030] The incremental table is queried according to the second query condition; wherein, the second query condition is used to determine whether there is incremental data in the incremental table that corresponds to the zipper table;

[0031] If there is incremental data in the incremental table that corresponds to the zipper table, the latest historical data is determined as the target stock data.

[0032] Optionally, if the target existing data exists, updating the target existing data includes:

[0033] The end date of the target stock data is updated according to the update rules;

[0034] The update rule is set based on the start date of the incremental data.

[0035] Another aspect of this application provides a data processing apparatus for a zipper table, the apparatus comprising:

[0036] The first acquisition module is used to acquire an initial SQL statement, which is used to update the incremental data in the incremental table to the zipper table.

[0037] The second acquisition module is used to acquire the zipper table information and incremental information based on the initial SQL statement;

[0038] The third acquisition module is used to obtain the zipper table name based on the zipper table information;

[0039] The fourth acquisition module is used to acquire the name of the incremental table based on the incremental information;

[0040] The first generation module is used to generate a first query statement based on the zipper table name and the incremental table name. The first query statement is used to query the target stock data of the zipper table.

[0041] The second generation module is used to generate a target SQL statement based on the zipper table information, the first query statement, and the incremental information.

[0042] The target SQL statement is used to: add the incremental data to the zipper table and update the end date of the target existing data to record historical data changes.

[0043] Another aspect of this application provides a computer device, including:

[0044] At least one processor; and

[0045] A memory that is communicatively connected to the at least one processor;

[0046] Wherein: the memory stores instructions that can be executed by the at least one processor, the instructions being executed by the at least one processor to enable the at least one processor to perform the method as described above.

[0047] Another aspect of this application provides a computer-readable storage medium storing computer instructions that, when executed by a processor, implement the method described above.

[0048] The embodiments of this application employing the above-described technical solution may have the following advantages:

[0049] First, an initial SQL statement is obtained, which contains only the logic for incremental updates to the zipper table. Then, zipper table information and incremental information are obtained based on this initial SQL statement. The zipper table name is obtained from the zipper table information. The incremental information contains incremental table information, from which the incremental table name is obtained. Next, a first query statement is generated based on the zipper table name and the incremental table name. This first query statement is used to query the target existing data in the zipper table. Finally, a target SQL statement is generated based on the zipper table information, the first query statement, and the incremental information. The target SQL statement is used to add incremental data to the zipper table and update the end date of the target existing data to record historical data changes. It can be seen that this embodiment can automatically generate the production logic of the zipper table based on the initial SQL statement, add incremental data to the zipper table, and update the expiration time of historical data. That is, during development, only the incremental data needs to be considered to achieve zipper table data production; there is no need to pay additional attention to the historical data of the zipper table itself. This can effectively reduce development costs, lower development complexity, and mitigate the probability of human error during development. Attached Figure Description

[0050] The accompanying drawings exemplify embodiments and form part of the specification, serving together with the textual description to explain exemplary implementations of the embodiments. The illustrated embodiments are for illustrative purposes only and do not limit the scope of the claims. Throughout the drawings, the same reference numerals refer to similar but not necessarily identical elements.

[0051] Figure 1 A flowchart illustrating the data processing method for a zipper table according to Embodiment 1 of this application is shown schematically.

[0052] Figure 2 Schematic illustration Figure 1 Flowchart of the sub-steps in step S102;

[0053] Figure 3 The diagram illustrates a new flowchart of the data processing method for a zipper table according to Embodiment 1 of this application;

[0054] Figure 4 Schematic illustration Figure 3 Flowchart of the sub-steps in step S302;

[0055] Figure 5 Schematic illustration Figure 1 Flowchart of the sub-steps in step S110;

[0056] Figure 6 The diagram illustrates an application example of the data processing method for a zipper table according to Embodiment 1 of this application.

[0057] Figure 7 This illustration schematically shows another application example of the data processing method for a zipper table according to Embodiment 1 of this application;

[0058] Figure 8 This illustration schematically shows another application example of the data processing method for a zipper table according to Embodiment 1 of this application;

[0059] Figure 9 A block diagram schematically illustrates a data processing apparatus for a zipper table according to Embodiment 2 of this application; and

[0060] Figure 10 A schematic diagram of the hardware architecture of a computer device according to Embodiment 3 of this application is shown. Detailed Implementation

[0061] To make the objectives, technical solutions, and advantages of this application clearer, the following detailed description is provided in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative and not intended to limit the scope of this application. All other embodiments obtained by those skilled in the art based on the embodiments in this application without inventive effort are within the scope of protection of this application.

[0062] It should be noted that the descriptions involving "first," "second," etc., in the embodiments of this application are for descriptive purposes only and should not be construed as indicating or implying their relative importance or implicitly specifying the number of technical features indicated. Therefore, a feature defined with "first" or "second" may explicitly or implicitly include at least one of that feature. Furthermore, the technical solutions of the various embodiments can be combined with each other, but this must be based on the ability of those skilled in the art to implement them. If the combination of technical solutions is contradictory or impossible to implement, it should be considered that such a combination of technical solutions does not exist and is not within the scope of protection claimed in this application.

[0063] In the description of this application, it should be understood that the numerical labels before the steps do not indicate the order of the steps, but are only used to facilitate the description of this application and to distinguish each step, and therefore should not be construed as a limitation of this application.

[0064] First, a definition of the terminology used in this application is provided:

[0065] A data warehouse is a central repository of information. Typically, data is periodically introduced into the data warehouse from transactional systems, relational databases, and other sources through data cleansing processes such as extract, transform, and load (ETL). The data is then archived and stored systematically in a data model according to subject areas and hierarchical structures. Business analysts, data engineers, data scientists, and decision-makers can access the data model in the data warehouse to perform queries and analyses using business intelligence (BI) tools, SQL clients, and other analytical applications.

[0066] A table is an object used to store data in a database; it is a structured collection of data. It is defined as a collection of columns. Similar to a spreadsheet, data in a table is organized in rows and columns. Each column in a table is designed to store some type of information (such as date, name, dollar amount, or number).

[0067] Field: Also known as a column. It contains information on a specific topic. Just like in an "address book" database, "name" and "phone number" are attributes common to all rows in the table, so these columns are called "name" fields and "phone number" fields.

[0068] A primary key is a column or combination of columns whose values ​​uniquely identify each row in a table, thus enforcing the integrity of the table's entity. Primary keys are mainly used for foreign key relationships with other tables, and for modifying and deleting text records.

[0069] Partitioning: Physically splitting data to reduce total I / O and response time during SQL operations.

[0070] Full partitioned table: Data is organized in the form of partitions, and each partition stores the current full data at the time of data operation.

[0071] Incremental table: Stores only the latest data, including newly added data and data updated within a specified time period.

[0072] SQL: Structured Query Language, abbreviated as SQL, is a special-purpose programming language used for database querying and programming. It is used to access, query, update, and manage relational database systems. It comprises six parts: DQL, DML, TCL, DCL, and CCL.

[0073] Data Query Language (DQL): Its statements, also known as "data retrieval statements," are used to retrieve data from tables and determine how the data is presented in the application. The reserved word SELECT is the most frequently used verb in DQL (and indeed all SQL). Other commonly used DQL reserved words include WHERE, ORDER BY, GROUP BY, and HAVING. These DQL reserved words are often used in conjunction with other types of SQL statements.

[0074] Data Manipulation Language (DML): Its statements include the verbs INSERT, UPDATE, and DELETE. These are used to add, modify, and delete data, respectively.

[0075] Transaction Control Language (TCL): Its statements ensure that all rows of a table affected by DML statements are updated in a timely manner, including the COMMIT, SAVEPOINT, and ROLLBACK commands.

[0076] Data Control Language (DCL): Its statements use GRANT or REVOKE to implement access control, determining the access of individual users and user groups to database objects. Some RDBMSs allow GRANT or REVOKE to control access to individual columns of a table.

[0077] Pointer Control Language (CCL): Its statements, such as DECLAR E CURSOR, FETCH INTO, and UPDATE WHERE CURRENT, are used to operate on individual rows of one or more tables.

[0078] Hadoop is a distributed system infrastructure. Users can develop distributed programs without understanding the underlying details of distributed systems, fully utilizing the capabilities of clusters for high-speed computing and storage.

[0079] MapReduce is a programming model for parallel computation of large datasets (greater than 1TB). Its core concepts are "Map" and "Reduce," incorporating features of functional and vector programming languages. It greatly simplifies programming, enabling programmers to run their programs on distributed systems even without prior knowledge of distributed parallel programming. Current software implementations specify a Map function to map a set of key-value pairs into a new set of key-value pairs, and concurrent Reduce functions to ensure that all mapped key-value pairs share the same key.

[0080] Secondly, to facilitate understanding of the technical solutions provided in the embodiments of this application by those skilled in the art, the relevant technologies are described below:

[0081] A data warehouse is a subject-oriented, integrated, non-volatile, historically reflective collection of data used to support management decision-making. Data warehouses can store valuable business data long-term or permanently. For ease of generation and use, data can be stored in the form of linked lists. However, due to the complex and convoluted logic of linked lists, the production logic for them cannot be automatically generated, making their development process complex, error-prone, labor-intensive, and labor-intensive.

[0082] The applicant understands that the processing of related linked tables is accomplished using MapReduce or SQL. However, both of these methods have certain drawbacks, specifically:

[0083] (1) Using MapReduce to develop a zipper table: The code is complex, the amount of code is large, and the labor cost is high, which can easily lead to problems such as task errors, multiple reworks, and extended task completion time.

[0084] (2) Using SQL to develop zipper tables: Due to the characteristics of SQL itself, the SQL logic developed is complex and prone to errors. Moreover, compared with the development of ordinary tables, using SQL to develop zipper tables will cause developers to need to consider the usage of more different types of tables during the development process, thereby increasing the complexity and cost of development.

[0085] Therefore, this application provides a data processing technology solution for zipper tables. In this technology solution, (1) by developing an SQL-based algorithm, the production of zipper tables is supported, and all production is done in the form of SQL; (2) SQL is simplified, and when developing zipper tables, only incremental data needs to be considered, without needing to pay extra attention to the historical data of the zipper table itself; (3) the production logic of zipper tables is automatically generated through an automated algorithm, reducing the learning cost for developers of the zipper table technology itself; (4) the zipper table generation algorithm is optimized, effectively reducing development costs, reducing development complexity, and avoiding the probability of human error during development; (5) the amount of code is reduced, and code efficiency is improved. See below for details.

[0086] The technical solutions of this application are described below through several embodiments. It should be understood that these embodiments can be implemented in many different forms and should not be construed as being limited to the embodiments set forth herein.

[0087] Example 1

[0088] Figure 1 A flowchart illustrating a data processing method for a zipper table according to Embodiment 1 of this application is shown schematically.

[0089] like Figure 1 As shown, the data processing method for the zipper table may include steps S100 to S110, wherein:

[0090] Step S100: Obtain the initial SQL statement, which is used to update the incremental data in the incremental table to the zipper table.

[0091] Step S102: Obtain the zipper table information and incremental information according to the initial SQL statement.

[0092] Step S104: Obtain the zipper table name based on the zipper table information.

[0093] Step S106: Obtain the name of the incremental table based on the incremental information.

[0094] Step S108: Generate a first query statement based on the zipper table name and the incremental table name. The first query statement is used to query the target stock data of the zipper table.

[0095] Step S110: Generate a target SQL statement based on the zipper table information, the first query statement, and the incremental information; wherein, the target SQL statement is used to: add the incremental data to the zipper table, and update the end date of the target existing data to record historical data changes.

[0096] The data processing method for the zipper table provided in this embodiment first obtains an initial SQL statement, which contains only logic for incremental updates of the zipper table. Then, based on the initial SQL statement, zipper table information and incremental information are obtained. The zipper table name is obtained from the zipper table information. The incremental information contains incremental table information, and the incremental table name is obtained from the incremental table information. Next, a first query statement is generated based on the zipper table name and the incremental table name. The first query statement is used to query the target existing data in the zipper table. Finally, a target SQL statement is generated based on the zipper table information, the first query statement, and the incremental information. The target SQL statement is used to add incremental data to the zipper table and update the end date of the target existing data to record historical data changes. It can be seen that this embodiment can automatically generate the production logic of the zipper table based on the initial SQL statement, add incremental data to the zipper table, and update the expiration time of historical data. In other words, during the development process, you only need to focus on incremental data to generate zipper table data, without having to pay extra attention to the historical data of the zipper table itself. This can effectively reduce development costs, reduce development complexity, and mitigate the probability of human error during development.

[0097] The following combination Figure 1 The steps in steps S100 to S110, as well as other optional steps, are described in detail.

[0098] Step S100 Obtain the initial SQL statement, which is used to update the incremental data in the incremental table to the zipper table.

[0099] Incremental data is used to record changes in data. Each data update generates an incremental data entry, which can include details of the data changes, such as the updated fields, the updated values, timestamps, etc.

[0100] An incremental table is a special data table used to store incremental data and can be used to record incremental updates of data.

[0101] For example: Suppose we have an increment table append1, the structure of which is shown in Table 1:

[0102] field name Data types Notes id int primary key name string Business fields modify_time timestamp Update field

[0103] Table 1

[0104] Suppose that on February 5, 2022, the incremental table append1 added one record, and the specific incremental data is as follows:

[0105] As shown in Table 2:

[0106] id name modify_time 1 Zhang San 2022-02-05

[0107] Table 2

[0108] It should be noted that Tables 1 and 2 are merely examples of incremental table structure and incremental data, and are not intended to limit the incremental table structure and incremental data content.

[0109] Whenever there is new incremental data, the incremental table will record the new incremental data.

[0110] For example, on February 11, 2022, the data in the incremental table append1 was modified, which generated incremental data.

[0111] The name field was changed to "Li Si", and the modify_time field was automatically changed to 2022-02-11 by the system.

[0112] Therefore, the incremental data in the incremental table append1 is shown in Table 3:

[0113] id name modify_time 1 Li Si 2022-02-11

[0114] Table 3

[0115] A zipper table is a special data table used to record historical changes in data. It can be used to track data changes over time and record the effective time period for each data row. For example, it can be used to track changes in anonymized product, employee, or customer information over time.

[0116] For example, suppose we have a zipper table history2, the structure of which is shown in Table 4:

[0117] field name Data types Notes id int primary key name string Business fields modify_time timestamp Update field start_date date Record validity period start time end_date date Record validity period expiration time

[0118] Table 4

[0119] It should be noted that Table 4 is only an example of the zipper table structure and is not intended to limit the specific structure of the zipper table.

[0120] When new data is generated, the incremental data in the incremental table needs to be synchronized to the zipper table so that the zipper table can record the latest / current state of the data and ensure data integrity. Therefore, in the example above, data from February 11, 2022, also needs to be appended to the zipper table. Specifically, if an incremental data entry is added to the incremental table append1 on February 11, 2022, this incremental data also needs to be synchronized to the zipper table history2, as shown in Table 5.

[0121] id name modify_time start_date end_date 1 Li Si 2022-02-11 2022-02-11 9999-12-31

[0122] Table 5

[0123] in:

[0124] 9999-12-31: A special date representing the future, used to identify the latest data.

[0125] Latest data: A piece of data may undergo multiple updates, and each update will generate a new state for the data; the latest data is used to describe the current state of the data as of the current moment.

[0126] It can be seen that the production of data in the zipper table includes updating the incremental data in the incremental table to the zipper table.

[0127] The initial SQL statement can be manually entered code. It can include logic for incremental updates to the linked list.

[0128] Here is an example of an initial SQL statement:

[0129] INSERT OVERWRITE TABLE (zipper table)

[0130] SELECT primary key, business field,

[0131] '<%=log_date%>'as start_date,

[0132] '9999-12-31' as end_date

[0133] FROM incremental table

[0134] As can be seen, the initial SQL statement is used to select the primary key and business field from the incremental table, using the current date as the start date and setting the end date to "9999-12-31", indicating that the data is the latest valid data (incremental data), and using the query result of SELECT to update the data in the zipper table.

[0135] As in the example above, the initial SQL statement can include only simple logic for incremental updates to the zipper table, without processing logic for historical data in the zipper table. In practical applications, if only the incremental data from the incremental table is added to the zipper table without any processing of the historical data in the zipper table, it may lead to problems such as duplicate data and data conflicts in the zipper table, resulting in incorrect records of historical data changes, as detailed below:

[0136] For example, suppose we have a zipper table named history3, the contents of which are shown in Table 6:

[0137] id name start_date end_date 1 Alice 2022-01-01 2022-06.30 1 Alice 2022-07-01 2023-01-31 1 Alice 2023-02-01 9999-12-31

[0138] Table 6

[0139] In the example of the zipper table history3, the data record with id 1 can represent Alice's valid information in different time periods. The first row of data indicates that Alice's information is valid from January 1, 2022 to June 30, 2022; the second row indicates that it is valid from July 1, 2022 to January 31, 2023; and the third row indicates that this information is valid from February 1, 2023 (9999-12-31 can be interpreted as permanently valid).

[0140] Assume that the incremental table append3 contains incremental data corresponding to Alice, as shown in Table 7:

[0141] id name start_date end_date 1 Alice 2023-06-01 9999-12-31

[0142] Table 7

[0143] If the incremental data in the incremental table append3 is added to the zipper table history3, the specific content is shown in Table 8:

[0144] id name start_date end_date 1 Alice 2022-01-01 2022-06.30 1 Alice 2022-07-01 2023-01-31 1 Alice 2023-02-01 9999-12-31 1 Alice 2023-06-01 9999-12-31

[0145] Table 8

[0146] As shown in Table 8, the fourth row of data (incremental data) indicates that Alice's information has been valid since June 1, 2023. It can be seen that the third row of data (historical data) and the fourth row of data (incremental data) overlap on the timeline, making it impossible to determine which row of data is valid, thus resulting in a data conflict.

[0147] Therefore, to truly generate data for the zipper table, in addition to updating the incremental data from the incremental table to the zipper table, it is also necessary to modify the historical data in the zipper table accordingly. For example, the expiration date of the historical data may need to be modified to reduce duplicate data and data conflicts, and improve the accuracy of historical data change records. In the example above, this could be achieved by changing the end_date in the third row from "9999-12-31" to "2023-05-31" to complete the data generation for the zipper table.

[0148] To generate zipper table data, developers could write additional SQL code to modify historical data in the zipper table. However, this approach would obviously impose an extra burden on developers. Therefore, this application provides a solution that automatically generates the entire logic for generating zipper table data based on an initial SQL statement, which can reduce development difficulty. An example of the specific implementation process of this solution is as follows.

[0149] Step S102 Based on the initial SQL statement, obtain the zipper table information and incremental information.

[0150] The zipper table information is related to the zipper table into which incremental data needs to be inserted, and may include the zipper table name, primary key or identity column, data fields, version number, and other data.

[0151] Incremental information can include information related to the incremental table, query logic for incremental data, etc.

[0152] Since the initial SQL statement includes the logic of updating the incremental data in the incremental table to the zipper table, it can be inferred that the initial SQL statement includes both zipper table information and incremental information.

[0153] In this embodiment, by parsing the initial SQL statement, the information of the zipper table and incremental information can be quickly obtained, which can be used for the subsequent production logic of automatically generating the zipper table and improving the overall execution efficiency.

[0154] In practical applications, there are various ways to extract chain table information and incremental information from SQL statements. The following is an example solution.

[0155] In optional embodiments, such as Figure 2 As shown, step S102 may include:

[0156] Step S200: Parse the initial SQL statement to obtain an abstract syntax tree, which includes directory table nodes and query nodes.

[0157] Step S202: Determine the zipper table information based on the content of the directory table node.

[0158] Step S204: Determine the incremental information based on the content of the query node.

[0159] An Abstract Syntax Tree (AST) is an abstract representation of source code, using a tree-like structure to represent the syntactic structure of a program. An AST can consist of a series of nodes, where each node represents a syntactic element in the source code, including expressions, statements, functions, variable declarations, etc.

[0160] By parsing the initial SQL statement, an abstract syntax tree corresponding to the initial SQL statement can be generated. The generated syntax tree is a unified, standardized, and formatted specification, which allows subsequent operations to be performed in a standardized and formatted manner without having to consider the specific SQL usage methods with different business meanings, thereby reducing development complexity and the learning cost for developers.

[0161] For example, the abstract syntax tree obtained after parsing the initial SQL statement can consist of two parts, such as a directory table node and a query node. The directory table node and the query node can be different nodes divided according to different content or functions.

[0162] The following is an example of the content of a directory table node:

[0163] INSERT OVERWRITE TABLE (zipper table)

[0164] Here is an example of querying node content:

[0165] SELECT primary key, business field,

[0166] '<%=log_date%>'as start_date,

[0167] '9999-12-31' as end_date

[0168] FROM incremental table

[0169] As we can see, the directory table node contains information about the zipper table where data needs to be inserted; therefore, the zipper table information can be determined based on the contents of the directory table node. The query node contains information about the incremental table; therefore, the incremental information can be determined based on the contents of the query node.

[0170] In this embodiment, an abstract syntax tree is generated by parsing the initial SQL statement. The required zipper table information and incremental information are quickly and accurately determined from different nodes of the normalized and standardized abstract syntax tree. This information is then used for the subsequent automatic generation logic of the zipper table. In this process, it is not necessary to consider and configure the SQL for different specific businesses, which reduces development costs and complexity.

[0171] Step S104 The zipper table name is obtained based on the zipper table information.

[0172] In this embodiment, the zipper table name is obtained from the zipper table information. The zipper table name can be used to identify zipper tables in the data warehouse, thereby quickly and accurately determining the zipper tables where incremental data needs to be inserted, without requiring additional information from developers, thus improving execution efficiency.

[0173] Step S106 The incremental table name is obtained based on the incremental information.

[0174] In this embodiment, since the incremental information includes information about the incremental table, the table name can be obtained from it. The incremental table name can be used to identify the incremental table in the data warehouse, thereby quickly and accurately identifying the incremental table where the incremental data is located without requiring additional information from developers, thus improving execution efficiency.

[0175] Step S108 Based on the zipper table name and the incremental table name, a first query statement is generated. The first query statement is used to query the target existing data of the zipper table.

[0176] Existing data can be all the data that already exists or has been collected at a specific point in time or within a certain time period. This data was collected, recorded or generated in the past and is unrelated to the current point in time.

[0177] For a zipper table, a primary key (such as Alice) may have multiple existing data entries, each recording its different state and lifecycle, that is, the point in time when the data was first produced and the point in time when it will expire.

[0178] Among these data entries with different lifecycles, the most unique one is the one with an expiration date of "9999-12-31" (end_date). This data entry represents the latest state of the primary key (Alice) at the current moment.

[0179] Add the corresponding incremental data to the zipper table. That is, the data of the primary key (Alice) has been updated. Then the data of the "latest status" mentioned earlier (i.e., end_date = "9999-12-31") will become "historical data" and no longer represent the current status of the data. Therefore, it is necessary to update the end_date field value of the historical data. For example, update the expiration time (end_date) to the day before the latest data (incremental data).

[0180] In this embodiment of the application, the historical data that needs to be modified can be referred to as the target stock data.

[0181] After obtaining the names of the zipper table and the incremental table, the zipper table to which data needs to be inserted and the incremental table containing the incremental data can be identified in the data warehouse. If the initial SQL statement already provides the incremental update logic for the zipper table, a first query statement can be generated based on the zipper table name and the incremental table name. This first query statement is an SQL statement containing the logic for querying and modifying the target existing data in the zipper table, and can be used to modify historical data in the zipper table. In other words, obtaining the first query statement based on the initial SQL statement automatically generates all the logic required for zipper table data production. This way, developers only need to develop the incremental data update logic and do not need to pay extra attention to the historical data of the zipper table itself, resulting in less manually entered code, higher code efficiency, lower development costs, simpler development, and a reduced probability of human error during development.

[0182] In an optional embodiment, step S108 may include: generating a first query statement based on the zipper table name, the incremental table name, and the pre-defined zipper table inventory data query logic. The first query statement includes query conditions and update rules. The query conditions are used to find target inventory data in the zipper table. The update rules are used to update the target inventory data if it exists.

[0183] The query logic for existing data in the zipper table can be pre-defined and standardized based on requirements. For example, if the target existing data needs to be queried, the query logic could include a query condition: the end_date field value equals 9999-12-31. This helps to quickly find the target existing data from a zipper table containing a large amount of existing data. In the above embodiment, by pre-standardizing the query logic for existing data in the zipper table, data retrieval can be made simpler, more efficient, and more accurate.

[0184] Based on the pre-defined query logic for existing data in the zipper table, the first query statement can be generated efficiently once the names of the zipper table and the incremental table are determined.

[0185] The first query statement is shown below:

[0186]

[0187]

[0188] The `SELECT` statement retrieves data from the linked list table, including the primary key, business field, start date, and end date. The `CASE` statement dynamically calculates the value of `end_date` based on the query results. If the query results meet the conditions, the `end_date` field is updated to "<%=log_date%>-1", where "<%=log_date%>-1" is a placeholder that will be replaced by a specific value during query execution. The `end_date` field value is updated to the date at the time of query execution minus one day, representing the end date of the data record's validity period. If the query results do not meet the conditions, no modifications are made. The SQL join operation `FROM linked_list_n1 LEFT OUTER JOIN incremental_list_n2 ON n1.primary_key=n2.primary_key` uses a left outer join (LEFT OUTER JOIN) to connect the linked list table and the incremental table, allowing for the retrieval of related data between the two tables. The primary key field is used for the connection.

[0189] As can be seen, the first query statement can include query conditions and update rules. The query conditions are used to retrieve the target existing data in the zipper table. If the target existing data exists in the zipper table, the update rules can be used to update that data. The first query statement contains the logic for updating historical data in the zipper table, which can reduce problems such as duplicate data and data conflicts caused by incremental data insertion, thus achieving zippering.

[0190] In an optional embodiment, the query conditions may include a first query condition and a second query condition. Correspondingly, as... Figure 3 As shown, searching for the target existing data in the zipper table may include:

[0191] Step S300: Determine the latest historical data in the zipper table according to the first query condition.

[0192] Step S302: If the latest historical data exists, determine the target stock data according to the second query condition.

[0193] The first query condition can be the value of the "end_date" field in existing data, which helps to quickly identify the historical data in the linked list that needs to be updated. Specifically, the first query condition can be configured as "end_date = 9999-12-31". If there is existing data in the linked list that meets the first query condition, based on the specific characteristics of its end_date field value, it can be determined that the existing data is the latest historical data. If there is no existing data that meets the first query condition, it means that there is no target existing data in the linked list, and no modification to the existing data in the linked list is needed.

[0194] In practical applications, when incremental data exists in the incremental table that corresponds to incremental data in the zipper table, the incremental data needs to be inserted into the zipper table. To prevent data conflicts, the `end_date` field of the historical data in the zipper table will be modified. Conversely, if there is no corresponding incremental data, the historical data in the zipper table does not need to be modified.

[0195] Therefore, even when the latest historical data is found, a second judgment is needed using the second query condition to determine whether the latest historical data needs to be modified. By refining the query conditions into a first query condition and a second query condition, and using two judgment steps to determine the target existing data, the accuracy and reliability of the query results are improved.

[0196] In optional embodiments, such as Figure 4 As shown, step S302 may include:

[0197] Step S400: Query the incremental table according to the second query condition; wherein, the second query condition is used to determine whether there is incremental data in the incremental table that corresponds to the zipper table.

[0198] Step S402: If there is incremental data in the incremental table that corresponds to the zipper table, the latest historical data is determined as the target stock data.

[0199] The second query condition is used to determine whether there is corresponding incremental data in the incremental table. For example, the second query condition can be configured as "n2.Primary key IS NOT NULL", thereby determining whether the historical data in the linked list needs to be modified. If the corresponding incremental data exists in the incremental table, the latest historical data can be identified as the target existing data. If the corresponding incremental data does not exist in the incremental table, it means that the historical data in the linked list does not need to be modified.

[0200] In this embodiment, by setting a second query condition, it is determined whether there is corresponding incremental data in the incremental table. This allows for an accurate determination of whether the latest historical data found needs to be modified, thereby identifying the target existing data, reducing the probability of erroneous modification, and improving the stability and reliability of the zipper table.

[0201] In an optional embodiment, if the target existing data exists, updating the target existing data includes:

[0202] The end date of the target stock data is updated according to the update rules;

[0203] The update rule is set based on the start date of the incremental data.

[0204] Update rules can be used to update the end date of target stock data, i.e., the end_date field, which is the expiration date, thereby avoiding data conflicts. For example, in Table 8, the end_date value in Alice's third row of data can be updated according to the update rules to the day before the start_date value in the fourth row of data.

[0205] Update rules can be set according to actual needs, such as the day before, week before, or month before the start date of incremental data. For example, an update rule can be configured as '<%=log_date%>-1', which means updating the date to the day before the current date.

[0206] In this embodiment, the target existing data is found when the query conditions are met, and the expiration time of the target existing data is updated according to the update rules. This can effectively reduce data conflicts, implement chaining logic, and improve the stability and reliability of the chained table.

[0207] Step S110: Generate a target SQL statement based on the zipper table information, the first query statement, and the incremental information; wherein, the target SQL statement is used to: add the incremental data to the zipper table, and update the end date of the target existing data to record historical data changes.

[0208] As mentioned earlier, the information in the zipper table refers to the information of the zipper table into which incremental data needs to be inserted. This information may include the zipper table name, primary key or identity column, data fields, version number, and other data.

[0209] Incremental information can include information related to the incremental table, query logic for incremental data, and so on.

[0210] The first query statement contains the logic for querying and modifying the target existing data.

[0211] Generate the target SQL statement based on the information from the zipper table, the first query statement, and the incremental information.

[0212] The target SQL statement is shown below:

[0213]

[0214] The target SQL statement can include query logic for incremental data obtained from incremental information, and logic for modifying historical data in the zipper table obtained from the zipper table information and incremental information. Therefore, the target SQL statement can be used to write the query results of incremental data into the zipper table and update the target existing data in the zipper table to record changes in historical data, thereby completing the zipperization and realizing the production of zipper table data.

[0215] In this embodiment, a target SQL statement is generated by merging the zipper table information, the first query statement, and the incremental information. This target SQL statement is used for incremental updates of the zipper table and recording historical data changes. After executing this target SQL statement, the latest zipper table data is obtained, completing the zipper table data production.

[0216] There are several ways to generate the target SQL statement; one example is provided below.

[0217] In optional embodiments, such as Figure 5 As shown, step S110 may include:

[0218] Step S500: Merge the first query statement and the incremental information to obtain the second query statement.

[0219] Step S502: Merge the zipper table information and the second query statement to obtain the target SQL statement.

[0220] First, merge the first query statement and the incremental information so that the second query statement can include the query logic for incremental data and the logic for modifying historical data of the zipper table, thus obtaining all the logic required for producing zipper table data.

[0221] The target SQL statement is obtained by merging the information from the zipper table and the second query statement. During execution, the query results of incremental data can be written to the zipper table, and the target existing data in the zipper table can be updated to record changes in historical data.

[0222] In this embodiment, the first query statement and the incremental information are first merged to obtain the second query statement, and then the chained table information and the second query statement are merged to obtain the target SQL statement. This can make the business logic of the chained SQL statement simple and the understanding cost low.

[0223] To make this application easier to understand, the following is combined with... Figures 6-8 An example application is provided.

[0224] S11, obtain the initial SQL statement.

[0225] S12 parses the original SQL and generates an abstract syntax tree, which includes a CatalogTable part and a Query part.

[0226] The CatalogTable part is the catalog table node in the abstract syntax tree, and the Query part is the query node in the abstract syntax tree.

[0227] S13, retrieve the zipper table name from the CatalogTable section.

[0228] S14, retrieve the incremental table name from the Query section.

[0229] S15, based on the general zipper table inventory query logic, generates SQL (first query statement) to query the inventory data of the zipper table, and updates the "expiration time" of the zipper table data that meets the conditions.

[0230] S16, merge the SQL for querying the existing data of the zipper table (first query statement) and the SQL for querying the incremental data in the Query part (incremental information) to generate the complete query SQL (second query statement) that needs to be zipped.

[0231] S17, merge the CatalogTable part (zipper table information) and the SQL (second query statement) that generates the latest zippered data to generate the final target SQL statement.

[0232] In this exemplary application, (1) the production logic of the zipper table can be automatically generated based on the initial SQL statement. Incremental data can be added to the zipper table, and the expiration time of historical data can be updated. That is, during the development process, only incremental data needs to be focused on to realize the production of zipper table data. There is no need to pay extra attention to the historical data of the zipper table itself, which can effectively reduce development costs, reduce development complexity, and alleviate the probability of human error during development; (2) the production of zipper tables is supported by developing SQL-based algorithms, and all production is done in the form of SQL; (3) the SQL is simplified. When developing zipper tables, only incremental data needs to be focused on, and there is no need to pay extra attention to the historical data of the zipper table itself; (4) the production logic of zipper tables is automatically generated through automated algorithms, reducing the learning cost of zipper table technology for developers; (5) the zipper table generation algorithm is optimized, which effectively reduces development costs, reduces development complexity, and avoids the probability of human error during development; (6) as an example, the amount of code is optimized from 16 lines to 5 lines, and the code efficiency is improved by 68.75%.

[0233] Example 2

[0234] Figure 9 The diagram schematically illustrates a data processing apparatus for a zipper table according to Embodiment 2 of this application. This apparatus can be divided into one or more program modules. One or more program modules are stored in a storage medium and executed by one or more processors to complete the embodiment of this application. The program module referred to in this embodiment is a series of computer program instruction segments capable of performing a specific function. The following description will specifically introduce the function of each program module in this embodiment. For example... Figure 9 As shown, the device 9000 may include: a first acquisition module 9100, a second acquisition module 9200, a third acquisition module 9300, a fourth acquisition module 9400, a first generation module 9500, and a second generation module 9600, wherein:

[0235] The first acquisition module 9100 is used to acquire an initial SQL statement, which is used to update the incremental data in the incremental table to the zipper table.

[0236] The second acquisition module 9200 is used to acquire the zipper table information and incremental information according to the initial SQL statement;

[0237] The third acquisition module 9300 is used to obtain the zipper table name based on the zipper table information;

[0238] The fourth acquisition module 9400 is used to acquire the name of the incremental table based on the incremental information;

[0239] The first generation module 9500 is used to generate a first query statement based on the zipper table name and the incremental table name. The first query statement is used to query the target stock data of the zipper table.

[0240] The second generation module 9600 is used to generate a target SQL statement based on the zipper table information, the first query statement, and the incremental information.

[0241] The target SQL statement is used to: add the incremental data to the zipper table and update the end date of the target existing data to record historical data changes.

[0242] As an optional embodiment, the second acquisition module 9200 is further configured to:

[0243] Parse the initial SQL statement to obtain an abstract syntax tree, which includes directory table nodes and query nodes;

[0244] The zipper table information is determined based on the contents of the directory table nodes;

[0245] The incremental information is determined based on the content of the query node.

[0246] As an optional embodiment, the second generation module 9600 is further configured to:

[0247] The first query statement and the incremental information are merged to obtain the second query statement;

[0248] The information in the zipper table and the second query statement are merged to obtain the target SQL statement.

[0249] As an optional embodiment, the first generation module 9500 is further configured to:

[0250] Based on the zipper table name, the incremental table name, and the pre-defined zipper table stock data query logic, the first query statement is generated, which includes query conditions and update rules.

[0251] The query conditions are used to: find the target existing data in the zipper table;

[0252] The update rule is used to update the target existing data when the target existing data exists.

[0253] As an optional embodiment, the query conditions include a first query condition and a second query condition;

[0254] Correspondingly, the step of searching for the target existing data in the zipper table includes:

[0255] Based on the first query condition, determine the latest historical data in the zipper table;

[0256] If the latest historical data exists, the target stock data is determined according to the second query condition.

[0257] As an optional embodiment, if the latest historical data exists, determining the target existing data based on the second query condition includes:

[0258] The incremental table is queried according to the second query condition; wherein, the second query condition is used to determine whether there is incremental data in the incremental table that corresponds to the zipper table;

[0259] If there is incremental data in the incremental table that corresponds to the zipper table, the latest historical data is determined as the target stock data.

[0260] As an optional embodiment, if the target existing data exists, updating the target existing data includes:

[0261] The end date of the target stock data is updated according to the update rules;

[0262] The update rule is set based on the creation date of the incremental data.

[0263] Example 3

[0264] Figure 10 This illustration schematically shows a hardware architecture diagram of a computer device 10000 suitable for implementing a data processing method for zipper tables according to Embodiment 3 of this application. In some embodiments, the computer device 10000 may be a terminal device such as a smartphone, tablet computer, personal computer, workbench, digital assistant, set-top box, or robot. In other embodiments, the computer device 10000 may be a rack server, blade server, tower server, or cabinet server (including standalone servers or server clusters composed of multiple servers), etc. Figure 9 As shown, the computer device 10000 includes, but is not limited to: a memory 10010, a processor 10020, and a network interface 10030 that can communicate and be linked with each other via a system bus. Wherein:

[0265] The memory 10010 includes at least one type of computer-readable storage medium, including flash memory, hard disk, multimedia card, card-type memory (e.g., SD or DX memory), random access memory (RAM), static random access memory (SRAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), programmable read-only memory (PROM), magnetic memory, magnetic disk, optical disk, etc. In some embodiments, the memory 10010 may be an internal storage module of a computer device 10000, such as the hard disk or memory of the computer device 10000. In other embodiments, the memory 10010 may also be an external storage device of the computer device 10000, such as a plug-in hard disk, smart media card (SMC), secure digital (SD) card, flash card, etc., equipped on the computer device 10000. Of course, the memory 10010 may also include both the internal storage module and the external storage device of the computer device 10000. In this embodiment, the memory 10010 is typically used to store the operating system and various application software installed on the computer device 10000, such as program code for data processing methods of zipper tables. In addition, the memory 10010 can also be used to temporarily store various types of data that have been output or will be output.

[0266] In some embodiments, processor 10020 may be a central processing unit (CPU), controller, microcontroller, microprocessor, or other chip. Processor 10020 is typically used to control the overall operation of computer device 10000, such as performing control and processing related to data interaction or communication with computer device 10000. In this embodiment, processor 10020 is used to run program code stored in memory 10010 or process data.

[0267] Network interface 10030 may include a wireless network interface or a wired network interface, which is typically used to establish a communication link between computer device 10000 and other computer devices. For example, network interface 10030 is used to connect computer device 10000 to an external terminal via a network, establishing a data transmission channel and communication link between computer device 10000 and the external terminal. The network may be an intranet, the Internet, Global System for Mobile Communications (GSM), Wideband Code Division Multiple Access (WCDMA), 4G network, 5G network, Bluetooth, Wi-Fi, or other wireless or wired networks.

[0268] It should be pointed out that, Figure 9 Only computer devices with components 10010-10030 are shown; however, it should be understood that it is not required to implement all of the shown components, and more or fewer components may be implemented instead.

[0269] In this embodiment, the data processing method of the zipper table stored in the memory 10010 can also be divided into one or more program modules and executed by one or more processors (such as processor 10020) to complete the embodiment of this application.

[0270] Example 4

[0271] This application also provides a computer-readable storage medium storing a computer program thereon, wherein the computer program, when executed by a processor, implements the steps of the data processing method for the zipper table in the embodiments.

[0272] In this embodiment, the computer-readable storage medium includes flash memory, hard disk, multimedia card, card-type memory (e.g., SD or DX memory), random access memory (RAM), static random access memory (SRAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEP ROM), programmable read-only memory (PROM), magnetic memory, magnetic disk, optical disk, etc. In some embodiments, the computer-readable storage medium can be an internal storage unit of a computer device, such as the hard disk or memory of the computer device. In other embodiments, the computer-readable storage medium can also be an external storage device of the computer device, such as a plug-in hard disk, smart media card (SMC), secure digital (SD) card, flash card, etc., equipped on the computer device. Of course, the computer-readable storage medium can also include both the internal storage unit and the external storage device of the computer device. In this embodiment, the computer-readable storage medium is typically used to store the operating system and various application software installed on the computer device, such as the program code of the data processing method of the zipper table in the embodiment. In addition, the computer-readable storage medium can also be used to temporarily store various types of data that have been output or will be output.

[0273] Obviously, those skilled in the art should understand that the modules or steps of the embodiments of this application described above can be implemented using general-purpose computer devices. They can be centralized on a single computer device or distributed across a network of multiple computer devices. Optionally, they can be implemented using computer-executable program code, thereby storing them in a storage device for execution by a computer device. In some cases, the steps shown or described can be performed in a different order than those presented here, or they can be fabricated as separate integrated circuit modules, or multiple modules or steps can be fabricated as a single integrated circuit module. Thus, the embodiments of this application are not limited to any particular combination of hardware and software.

[0274] It should be noted that the above are merely preferred embodiments of this application and do not limit the scope of patent protection of this application. Any equivalent structural or procedural changes made using the content of this application's specification and drawings, or direct or indirect applications in other related technical fields, are similarly included within the scope of patent protection of this application.

Claims

1. A data processing method for a zipper table, characterized in that, The method includes: Obtain the initial SQL statement, which is used to update the incremental data in the incremental table to the zipper table; Based on the initial SQL statement, obtain the zipper table information and incremental information; Obtain the zipper table name based on the zipper table information; Obtain the name of the incremental table based on the incremental information; Based on the zipper table name and the incremental table name, a first query statement is generated. The first query statement is used to query the target existing data of the zipper table. Based on the zipper table information, the first query statement, and the incremental information, generate the target SQL statement; The target SQL statement is used to: add the incremental data to the zipper table and update the end date of the target existing data to record historical data changes.

2. The method according to claim 1, characterized in that, The initial SQL statement is used to obtain the linked list information and incremental information, including: Parse the initial SQL statement to obtain an abstract syntax tree, which includes directory table nodes and query nodes; The zipper table information is determined based on the contents of the directory table nodes; The incremental information is determined based on the content of the query node.

3. The method according to claim 1, characterized in that, Based on the information in the linked list, the first query statement, and the incremental information, a target SQL statement is generated, including: The first query statement and the incremental information are merged to obtain the second query statement; The information in the zipper table and the second query statement are merged to obtain the target SQL statement.

4. The method according to any one of claims 1 to 3, characterized in that, Based on the names of the zipper table and the incremental table, a first query statement is generated, including: Based on the zipper table name, the incremental table name, and the pre-defined zipper table stock data query logic, the first query statement is generated, which includes query conditions and update rules. The query conditions are used to: find the target existing data in the zipper table; The update rule is used to update the target existing data when the target existing data exists.

5. The method according to claim 4, characterized in that, The query conditions include a first query condition and a second query condition; Correspondingly, the step of searching for the target existing data in the zipper table includes: Based on the first query condition, determine the latest historical data in the zipper table; If the latest historical data exists, the target stock data is determined according to the second query condition.

6. The method according to claim 5, characterized in that, In the case that the latest historical data exists, the target existing data is determined according to the second query condition, including: The incremental table is queried according to the second query condition; wherein, the second query condition is used to determine whether there is incremental data in the incremental table that corresponds to the zipper table; If there is incremental data in the incremental table that corresponds to the zipper table, the latest historical data is determined as the target stock data.

7. The method according to claim 4, characterized in that, If the target existing data exists, updating the target existing data includes: The end date of the target stock data is updated according to the update rules; The update rule is set based on the start date of the incremental data.

8. A data processing device for a zipper watch, characterized in that, The device includes: The first acquisition module is used to acquire an initial SQL statement, which is used to update the incremental data in the incremental table to the zipper table. The second acquisition module is used to acquire the zipper table information and incremental information based on the initial SQL statement; The third acquisition module is used to obtain the zipper table name based on the zipper table information; The fourth acquisition module is used to acquire the name of the incremental table based on the incremental information; The first generation module is used to generate a first query statement based on the zipper table name and the incremental table name. The first query statement is used to query the target stock data of the zipper table. The second generation module is used to generate a target SQL statement based on the zipper table information, the first query statement, and the incremental information. The target SQL statement is used to: add the incremental data to the zipper table and update the end date of the target existing data to record historical data changes.

9. A computer device, characterized in that, include: At least one processor; and A memory communicatively connected to the at least one processor; wherein: The memory stores instructions that can be executed by the at least one processor to enable the at least one processor to perform the method of any one of claims 1 to 7.

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

Images