Data table row-column dual storage conversion method and device, electronic equipment and storage medium

Abstract

The application discloses a data table row-column dual storage conversion method and device, electronic equipment and a storage medium. The method comprises the following steps: determining a first table; scanning, segmenting and compressing a second table to obtain a first data block; and storing the first data block into the first table to obtain a third table. The method can generate the first data block, i.e. a column storage data block, by segmenting the second table, save the first data block in the first table, and merge the first data block with the second table, so that the row storage information and the column storage information can exist in one table simultaneously, and the performance requirement of OLTP and OLAP services cannot be met by a single storage format.

Description

Technical Field

[0001] This invention relates to the field of distributed database data storage technology, and in particular to a method, apparatus, electronic device, and storage medium for converting data tables to both row and column storage. Background Technology

[0002] In practical applications, a table needs to support both high-concurrency point queries or updates (OLTP business) (such as orders, invoices, etc.) and efficient batch queries and aggregation analysis (OLAP business) (such as statistical reports, trend analysis). Traditional row-store and column-store cannot simultaneously meet these two needs. Row-store is generally suitable for real-time scenarios with frequent point queries and updates, while column-store is suitable for large-volume analytical queries (range queries). In addition, column-store can only be used for offline data analysis, and its data update performance is very poor and cannot meet the needs of online business. Summary of the Invention

[0003] This invention provides a method, apparatus, electronic device, and storage medium for converting data tables to both rows and columns, in order to solve the problem of poor data update performance that fails to meet the needs of online business.

[0004] According to one aspect of the present invention, a method for converting data tables to both row and column storage is provided, comprising:

[0005] The first table is defined; the first table is a newly created empty column-store data table.

[0006] The second table is scanned, segmented, and compressed to obtain the first data block; the second table is a row-stored data table that requires both row tables and list tables to exist simultaneously.

[0007] The first data block is stored in the first table to obtain the third table; the third table is a data table that stores data in both rows and columns.

[0008] According to another aspect of the present invention, a data table row-column dual-storage conversion device is provided, comprising:

[0009] The first table determination module is used to determine the first table; the first table is a created empty column storage data table.

[0010] The first data block determination module is used to scan, segment, and compress the data of the second table to obtain the first data block; the second table is a row-stored data table that requires both row tables and list tables to exist simultaneously.

[0011] The storage module is used to store the first data block into a first table to obtain a third table; the third table is a data table with both row and column storage.

[0012] According to another aspect of the present invention, an electronic device is provided, the electronic device comprising:

[0013] At least one processor; and

[0014] A memory communicatively connected to the at least one processor; wherein,

[0015] The memory stores a computer program that can be executed by the at least one processor, which is then executed by the at least one processor to enable the at least one processor to perform the data table row and column dual-store conversion method according to any embodiment of the present invention.

[0016] According to another aspect of the present invention, a computer-readable storage medium is provided, the computer-readable storage medium storing computer instructions, the computer instructions being configured to cause a processor to execute and implement the data table row-column dual-storage conversion method according to any embodiment of the present invention.

[0017] The technical solution of this invention involves determining a first table; scanning, segmenting, and compressing the data in a second table to obtain a first data block, which facilitates subsequent data storage; storing the first data block in the first table to obtain a third table, which simultaneously stores row-stored data and column-stored data. The row-stored data provides optimization for OLTP queries, while the column-stored data provides performance optimization for OLAP queries. This method, by segmenting the second table to generate the first data block (i.e., the column-stored data block), storing it in the first table, and merging it with the second table, enables a single table to simultaneously contain both row-stored and column-stored information, solving the problem that a single storage format cannot meet the performance requirements of OLTP and OLAP services.

[0018] It should be understood that the description in this section is not intended to identify key or essential features of the embodiments of the present invention, nor is it intended to limit the scope of the invention. Other features of the invention will become readily apparent from the following description. Attached Figure Description

[0019] To more clearly illustrate the technical solutions in the embodiments of the present invention, the accompanying drawings used in the description of the embodiments will be briefly introduced below. Obviously, the accompanying drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0020] Figure 1 A flowchart of a data table row and column dual-storage conversion method provided in an embodiment of the present invention;

[0021] Figure 2A schematic diagram of a fourth table provided in an embodiment of the present invention;

[0022] Figure 3 This is a schematic diagram of the storage of a first data block provided in an embodiment of the present invention;

[0023] Figure 4 This is a schematic diagram of a third table generation system provided in an embodiment of the present invention;

[0024] Figure 5 This is a schematic diagram of the structure of a data table row and column dual-storage conversion device provided in an embodiment of the present invention;

[0025] Figure 6 A schematic diagram of the structure of an electronic device for implementing the data table row and column dual-storage conversion method of this embodiment of the invention. Detailed Implementation

[0026] To enable those skilled in the art to better understand the present invention, the technical solutions of the present invention will be clearly and completely described below with reference to the accompanying drawings of the embodiments of the present invention. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort should fall within the scope of protection of the present invention.

[0027] It should be noted that the terms "first," "second," etc., in the specification, claims, and accompanying drawings of this invention are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. It should be understood that such data can be interchanged where appropriate so that the embodiments of the invention described herein can be implemented in orders other than those illustrated or described herein. Furthermore, the terms "comprising" and "having," and any variations thereof, are intended to cover a non-exclusive inclusion; for example, a process, method, system, product, or apparatus that comprises a series of steps or units is not necessarily limited to those steps or units explicitly listed, but may include other steps or units not explicitly listed or inherent to such processes, methods, products, or apparatus.

[0028] Figure 1 This is a flowchart illustrating a data table row-column dual-storage conversion method provided in an embodiment of the present invention. This embodiment is applicable to situations involving data table conversion. The method can be executed by a data table row-column dual-storage conversion device, which can be implemented in hardware and / or software. This data table row-column dual-storage conversion device can be configured in any electronic device with network communication capabilities. For example... Figure 1 As shown, the method includes:

[0029] S110. Determine the first table; the first table is the created empty column storage data table.

[0030] Specifically, after receiving the row-column conversion instruction sent by the first CN node, the DN node creates the first table in the second table and creates a fourth table that matches the first table.

[0031] The first CN node is the CN node selected and logged into by the client. The CN node (Coordinator Node) is used to coordinate the execution of tasks on each node and to perform appropriate resource allocation for each node.

[0032] The second table is a row-based data table.

[0033] The fourth table records modification information for the first table. For example, the fourth table can be an auxiliary table (deta table).

[0034] Furthermore, the fourth table is as follows Figure 2 As shown, it includes cu_id; ctid; xid; op; rows. Among them, ctid is used to retrieve row storage data, xid is used to determine the visibility of row storage data; op is used to mark the addition or deletion status of row storage data; rows is used to determine whether the corresponding row group unit needs to be reorganized into a first data block and a new first data block needs to be generated and written to disk; the rows field is mainly used to restore row group unit information upon power-on, and the row group unit uses the fourth table to manage the modified data.

[0035] Further, before determining the first table, the process includes: the client selects any CN node as the first CN node and logs in to it. After logging in, the client inputs a row-column conversion command in the input module of the first CN node and sends the command to the CN node containing the second table. Upon receiving the command, the CN node containing the second table creates a row-column conversion descriptor and saves the information of the first table into the descriptor. Simultaneously, it saves the command to the system table (pg_htap). The first CN node then notifies the second CN node and the DN node of this command. The second CN node is any CN node other than the first CN node.

[0036] Among them, DN nodes (Data Nodes) are used to store and process data.

[0037] S120. Scan, segment, and compress the second table to obtain the first data block; the second table is a row-stored data table that requires both row tables and list tables to exist simultaneously.

[0038] The first data block is a Compression Unit (CU), the smallest storage unit in columnar storage, which is a compressed data block composed of a portion of the data in the second table.

[0039] Specifically, the second table is scanned according to the address order of its physical pages, and divided into at least one segmented data block according to a preset number. The row-based data in the obtained segmented data block is decomposed into column-based data segments, and each column-based data segment is compressed to obtain the first data block.

[0040] The preset quantity refers to the number of physical pages that should be included in a first data block. For example, the preset quantity can be 1000 physical pages.

[0041] Furthermore, the scanning method can employ offline batch scanning and incremental log synchronization. Offline batch scanning involves reading the physical pages of the second table through the row storage engine's snapshot read module during time intervals with low data generation frequency, ensuring that writes or updates to the second table are not blocked. Incremental log synchronization involves parsing the transaction logs of the second table in real time, triggering a split operation when the accumulated data volume reaches a preset amount.

[0042] Furthermore, the segmentation process is as follows: Each time a preset number of physical pages are scanned, the data is segmented in sequential order, and so on, until the second table is completely scanned. If, when the end of the second table is reached, the remaining physical pages do not meet the preset number, the remaining physical pages are merged with the previously segmented data block.

[0043] Furthermore, information about the segmented data is recorded during the segmentation process. This information includes at least: block ID (i.e., the second identifier), the range of physical pages contained within the block, the total number of rows within the block, and a timestamp.

[0044] For example, the first data block is as follows Figure 3 As shown, each column of the data block forms a CU, which is the first data block. It can be seen from the diagram that if the second table has three columns of data, three CUs are generated, and the three CUs share a second identifier, cu_id.

[0045] S130. Store the first data block in the first table to obtain the third table; the third table is a data table with both rows and columns stored.

[0046] Specifically, a row group unit is created for each first data block, and the first data block and its matching row group unit are written into the instruction storage path of the first table to obtain the third table.

[0047] Among them, the row group is the core logical unit used by columnar tables to organize and manage data.

[0048] For example, the CU is divided and assembled according to the physical pages in the second table. Every 1000 physical pages are divided into a segmented data block, and each segmented data block is matched with a RowGroup for data management.

[0049] Furthermore, such as Figure 4 As shown, row-level data and Deta data are stored in the row-level storage engine, while CU data, i.e., the first data block data, is stored in the column-level storage engine. When the column-level data needs to be updated, the row group unit updates the data at the position corresponding to the first identifier in the Deta table according to the first identifier.

[0050] Furthermore, such as Figure 4 As shown, the conversion of the second table to the third table is mainly achieved through the following modules: the HTAP module and the DN node. The HTAP module is responsible for managing the information in the third table and maintaining the system tables. The DN node is used to convert the second table into the third table.

[0051] Furthermore, the HTAP module is configured in the CN node, consisting of a system table and IMCSDes, and the HTAP module is located in real time on the coordinate or data node.

[0052] The management of information in the third table involves generating an IMCSDsc for each third table and storing the information in the IMCSDsc list.

[0053] The maintenance of the system table involves synchronously modifying the system table according to the change instructions in the third table.

[0054] Furthermore, an HTAP module is also configured in the DN node. The HTAP module consists of the system table, IMCSDesc, RowGroup, and IMCS_Deta. It is responsible for managing the information in the third table, maintaining the system table, managing static data, and managing the fourth table.

[0055] Static data management involves interacting with the row storage engine to convert row storage data in the second table to column storage data. It also maintains information about each CU (cuid, rows, min, max, etc.) and the number of rows in the fourth table through RowGroup.

[0056] The fourth table management function receives notifications from the SQL engine and is responsible for maintaining the data in the fourth table through addition, deletion, modification, and querying.

[0057] Furthermore, after obtaining the third table, the DN node sends an acknowledgment message to the first CN node. Upon receiving the row-column conversion instruction, the second CN node creates a row-column conversion descriptor, saves the acquired descriptor and instruction to its corresponding system table, and returns acknowledgment information to the first CN node. After receiving messages from all the second CN nodes and DN nodes, the first CN node returns a row-column conversion success message to the client.

[0058] Furthermore, after acquiring the row-column dual-store data table, the row-store data table can be restored. The specific steps are as follows: A row-column conversion cancellation instruction is generated and sent to the first CN node. Upon receiving the instruction, the first CN node deletes its local row-column conversion information and the row-column conversion instruction stored in the system table, and notifies the second CN node and DN node of the cancellation instruction. Upon receiving the instruction, the DN node deletes the first and fourth tables, then clears the row group cells corresponding to the first table, and sends a cancellation completion message to the first CN node. Upon receiving the instruction, the second CN node deletes its local row-column conversion information and system table information, and then returns a confirmation message to the first CN node. After receiving messages from all CN nodes and DN nodes, the first CN node returns a row-column conversion cancellation success message to the client.

[0059] Furthermore, after acquiring the row-column dual-store data table, the process also includes power-on recovery of the third table. The specific implementation steps are as follows: each CN node scans its matching system table and creates a memory record for each third table; each DN node scans its matching system table and creates a memory record for each third table, restoring the row group cell information for each table. Each CN node periodically checks whether the DN nodes have completed recovery. If all DN nodes have completed recovery, the row-column dual-store state of the CN node becomes available.

[0060] Furthermore, the row group cell information is recovered by scanning the CU descriptor table of the corresponding column storage table in the third table and the fourth table to obtain the cuid and cu_rows (dynamic row count) of the records in the table.

[0061] Optionally, the second table is scanned, segmented, and compressed to obtain the first data block, including steps A1-A2:

[0062] Step A1: Scan the second table and break down the row data in the second table into column data segments to obtain the first data.

[0063] The second table is a row-based data table that stores data.

[0064] Specifically, after generating the first table, a scan of the second table is triggered. During the scan of the second table, it is divided into segments according to a preset number, resulting in at least one segmented data block. The row-based data in each segmented data block is further divided into column-based data segments, and a common identifier is assigned to each column-based data segment corresponding to the segmented data block. The obtained column-based data segments are used as the first data.

[0065] Step A2: Compress the first data to generate the first data block.

[0066] Specifically, the columnar data segments in the acquired first data are compressed according to their corresponding identifiers to obtain at least one data block.

[0067] Optionally, after storing the first data block in the first table to obtain the third table, steps B1-B4 are included:

[0068] Step B1: Determine the first modification information; the first modification information is the information that needs to modify the data in the first table and the second table.

[0069] The first modification information includes at least: data modification information and the number of modified data rows.

[0070] Specifically, the client selects any CN node as the first CN node. After logging into the first CN node, the client enters the first modification information in the input module of the first CN node. The first CN node then sends the first modification information to the SQL engine of the DN node.

[0071] Step B2: Determine the second data based on the first modification information; the second data is the updated data in the second table.

[0072] Specifically, after receiving the first modification information, the DN node matches the corresponding row from the second table based on the number of modified data rows, and updates the row data according to the data modification information. After the update is complete, the updated data in the row storage is used as the second data.

[0073] Step B3: Determine the first identifier based on the second data; the first identifier uniquely represents the position of the second data in the second table.

[0074] Specifically, the position information of the data in the second table is determined based on the second data, and a row identifier is generated based on the position information.

[0075] The row identifier is the physical row information of the data record, specifically recording the displacement position of the data block where a row of data is stored. For example, the row identifier can be represented as: physical page number + row number.

[0076] Step B4: Determine the row group unit based on the first identifier. The row group unit saves the first modification information to the fourth table. The fourth table is used to record modification information.

[0077] Specifically, after obtaining the first identifier, the DN node notifies the column storage engine of the first table. The column storage engine matches the corresponding row group unit according to the obtained row identifier. The row group unit saves the data modification information in the first modification information to the position corresponding to the row identifier in the fourth table.

[0078] Optionally, after the row group cell saves the first modification information to the fourth table, steps C1-C4 are included:

[0079] Step C1: Determine the number of rows to be modified.

[0080] Specifically, when the row group unit saves data, it records the number of modified rows of newly updated data in the fourth table in real time.

[0081] Step C2: If the number of modified rows exceeds the preset number of rows, then obtain the third data from the second table; the third data is the original data and modified data corresponding to the second identifier in the second table; the second identifier is the identifier of the first data block corresponding to the row group unit.

[0082] Specifically, if the number of modified rows exceeds the preset number of rows, it indicates that the data record limit has been exceeded. A second identifier is determined based on the row group unit, and the corresponding original data and modified data are obtained from the second table based on the second identifier as the third data.

[0083] Step C3: Generate the second data block from the third data.

[0084] Specifically, the row-based data in the acquired third data is decomposed into column-based data segments, and the column-based data segments are compressed to obtain the second data block.

[0085] Step C4: Replace the first data block corresponding to the second identifier according to the second data block, and clear the fourth table.

[0086] Specifically, based on the second identifier, the corresponding first data block is matched from the first table, and the obtained second data block replaces the matched first data block, thus completing the modification of the first table. After the replacement is completed, the records in the fourth table are cleared based on the second identifier.

[0087] Furthermore, after completing the modifications, the columnar storage engine of the first table sends a columnar storage engine return message to the DN node. Upon receiving the columnar storage engine return message, the DN node returns a data modification completion message to the first CN node. After receiving modification completion messages from all DN nodes, the first CN node returns the modification result to the client.

[0088] Optionally, after storing the first data block in the first table to obtain the third table, steps D1-D5 are included:

[0089] Step D1: Determine the query information.

[0090] The query information is used to indicate how to query data in the second table that has been updated but not synchronized to the first table, and to update the second table based on the query results.

[0091] Specifically, the client selects any CN node as the first CN node and logs in to it. After logging in, the client enters the query information in the input module of the first CN node.

[0092] Step D2: Generate at least one first path based on the query information, and perform consumption calculation on at least one first path to obtain a first result; the consumption calculation is to calculate the resources of the device required to execute according to at least one first path.

[0093] Specifically, the query information is parsed to obtain the columns to be queried. An execution path is generated based on this column information, resulting in at least one first path. The resources required for the execution of each of the at least one first path are calculated to obtain the first result.

[0094] Among these, the resources consumed can be at least: IO time, CPU time, and network transmission cost.

[0095] Step D3: Filter at least one first path based on the first result to obtain a second path.

[0096] Specifically, based on the first result, at least one first path is sorted by consumption, and the first path with the least consumption is selected as the second path.

[0097] Step D4: Generate the first task based on the second path.

[0098] Specifically, after obtaining the second path, the CN node breaks down the second path according to the execution process and generates the first task from the decomposed execution process.

[0099] Step D5: Update the data in the first table according to the first task.

[0100] Specifically, after receiving the first task, the DN node matches the corresponding row group unit based on the data block information contained in the first task. The row group unit retrieves the row identifier corresponding to the data added in the second table but not synchronized to the first table from the fourth table. Based on the retrieved row identifier, it retrieves the corresponding data from the second table. Finally, it updates the first table based on the retrieved data.

[0101] Optionally, update the data in the first table according to the first task, including steps E1-E3:

[0102] Step E1: Obtain the third identifier from the fourth table; the third identifier is a new identifier added to the second table, but it has not been synchronized to the row identifier corresponding to the data in the third data block.

[0103] Specifically, after receiving the first task, the DN node matches the corresponding row group unit according to the information of the third data block contained in the first task. The row group unit obtains the row identifier corresponding to the data added in the second table but not synchronized to the third data block from the fourth table, and uses it as the third identifier.

[0104] The third data block is the data block in the first table where the column to be queried is located.

[0105] Step E2: Obtain the corresponding data from the second table based on the third identifier to obtain the fourth data.

[0106] Specifically, based on the third identifier, the row-specific new data corresponding to the row identifier is obtained from the second table, and the obtained row-specific new data is used as the fourth data.

[0107] Step E3: Update the third data block based on the fourth data.

[0108] Specifically, the fourth data is broken down from row-based new data into new column-based data, the new column-based data is compressed, and then added to the third data block.

[0109] Optionally, before updating the third data block based on the fourth data, steps F1-F2 are included:

[0110] Step F1: Obtain the fifth data from the third data block; the fifth data is the data obtained after decompressing the third data block.

[0111] Specifically, the third data block is decompressed and restored to a columnar data segment. The obtained columnar data segment is then used as the fifth data segment.

[0112] Step F2: Update the fifth data according to the first graph; the first graph is used to represent the status of data that has been deleted from the second table but not updated in the third data block.

[0113] Specifically, the fifth data is traversed, and during the traversal, the fifth data is updated according to the first data. After the update is completed, it is compressed to obtain a new third data block.

[0114] Furthermore, the update process is as follows: based on the "0 / 1" identifier of the first image, retain the rows in the third data block corresponding to the row with the identifier "0", and filter out the rows in the third data block corresponding to the row with the identifier "1".

[0115] Furthermore, the first bitmap is determined as follows: The corresponding row group unit is matched based on the third data block. The row group unit reads the portion corresponding to the third data block in the fourth table, and reads the row identifiers of data that has been deleted from the second table but not updated in the third data block. A bitmap with the same number of rows as the third data block is generated based on the obtained row identifiers, thus obtaining the first bitmap.

[0116] The technical solution of this embodiment involves determining a first table; scanning, segmenting, and compressing the data in a second table to obtain a first data block, which facilitates subsequent data storage; storing the first data block in the first table to obtain a third table, which simultaneously stores row-stored data and column-stored data. The row-stored data provides optimization for online transaction processing (OLTP) queries, while the column-stored data provides performance optimization for online analytical processing (OLAP). This method, by segmenting the second table to generate the first data block (i.e., the column-stored data block), storing it in the first table, and merging it with the second table, enables a single table to simultaneously contain row-stored and column-stored information, solving the problem that a single storage format cannot meet the performance requirements of OLTP and OLAP services.

[0117] Figure 5 This is a schematic diagram of a data table row-column dual-storage conversion device provided in an embodiment of the present invention. This embodiment is applicable to situations involving data table conversion. The data table row-column dual-storage conversion device can be implemented in hardware and / or software, and can be configured in any electronic device with network communication capabilities. Figure 5 As shown, the device includes: a first table determination module 210, a first data block determination module 220, and a storage module 230, wherein:

[0118] First Table Determination Module 210: Used to determine the first table; the first table is a created empty column storage table;

[0119] First data block determination module 220: used to scan, segment, and compress the data of the second table to obtain the first data block; the second table is a row-stored data table that requires both row tables and list tables to exist simultaneously.

[0120] Storage module 230: used to store the first data block into the first table to obtain the third table; the third table is a data table with both row and column storage.

[0121] Optionally, the first data block determination module 220 includes:

[0122] First data determination unit: used to scan the second table, decompose the row data in the second table into column data segments, and obtain the first data;

[0123] First data block determination unit: used to compress the first data and generate the first data block.

[0124] Optionally, the data table row-column dual-store conversion device includes:

[0125] Update module: Used to determine the first modification information; the first modification information is the information that needs to modify the data in the first table and the second table;

[0126] Second data determination module: used to determine second data based on first modification information; the second data is the updated data in the second table;

[0127] First identifier determination module: used to determine the first identifier based on the second data; the first identifier uniquely represents the position of the second data in the second table;

[0128] Data storage module: used to determine the row group unit based on the first identifier, and the row group unit saves the first modification information to the fourth table; the fourth table is used to record the modification information.

[0129] Optionally, the data table row-column dual-store conversion device includes:

[0130] The "Number of lines to modify" module is used to determine the number of lines to modify.

[0131] The third data determination module is used to obtain third data from the second table if the number of modified rows exceeds the preset number of rows; the third data consists of the original data and modified data corresponding to the second identifier in the second table; the second identifier is the identifier of the first data block corresponding to the row group unit;

[0132] Second data block determination module: used to generate the second data block from the third data;

[0133] Data replacement module: Used to replace the first data block corresponding to the second identifier according to the second data block, and clear the fourth table.

[0134] Optionally, the data table row-column dual-store conversion device includes:

[0135] Query Information Determination Module: Used to determine the query information;

[0136] First Result Determination Module: Used to generate at least one first path based on query information, and to perform consumption calculation on at least one first path to obtain a first result; the consumption calculation is to calculate the resources of the device required to execute according to at least one first path;

[0137] Second path determination module: used to filter at least one first path based on the first result to obtain a second path;

[0138] First task generation module: used to generate the first task based on the second path;

[0139] Data query module: used to update the data in the first table according to the first task.

[0140] Optional, the data query module includes:

[0141] The second identifier determination unit is used to obtain the third identifier from the fourth table; the third identifier is a new identifier added to the second table, but not synchronized to the row identifier of the data in the third data block.

[0142] Fourth data determination unit: used to obtain the corresponding data from the second table according to the third identifier, and thus obtain the fourth data;

[0143] Update unit: Used to update the third data block based on the fourth data.

[0144] Optional, the data query module includes:

[0145] Fifth data determination unit: used to obtain the fifth data from the third data block; the fifth data is the data obtained after decompressing the third data block;

[0146] Update Unit: Used to update the fifth data based on the first diagram; the first diagram is used to represent the status of data that has been deleted from the second table but not updated in the third data block.

[0147] The data table row and column dual storage conversion device provided in the embodiments of the present invention can execute the data table row and column dual storage conversion method provided in any of the embodiments of the present invention, and has the corresponding functions and beneficial effects of executing the data table row and column dual storage conversion method. For detailed process, please refer to the relevant operations of the data table row and column dual storage conversion method in the foregoing embodiments.

[0148] Figure 6 This is a schematic diagram of the structure of an electronic device for implementing the data table row-column dual-storage conversion method according to embodiments of the present invention. The electronic device is intended to represent various forms of digital computers, such as laptop computers, desktop computers, workstations, personal digital assistants, servers, blade servers, mainframe computers, and other suitable computers. The electronic device can also represent various forms of mobile devices, such as personal digital processors, cellular phones, smartphones, wearable devices (e.g., helmets, glasses, watches, etc.), and other similar computing devices. The components shown herein, their connections and relationships, and their functions are merely illustrative and are not intended to limit the implementation of the invention described and / or claimed herein.

[0149] like Figure 6As shown, the electronic device 10 includes at least one processor 11 and a memory, such as a read-only memory (ROM) 12 or a random access memory (RAM) 13, communicatively connected to the at least one processor 11. The memory stores computer programs executable by the at least one processor. The processor 11 can perform various appropriate actions and processes based on the computer program stored in the ROM 12 or loaded from storage unit 18 into the RAM 13. The RAM 13 can also store various programs and data required for the operation of the electronic device 10. The processor 11, ROM 12, and RAM 13 are interconnected via a bus 14. An input / output (I / O) interface 15 is also connected to the bus 14.

[0150] Multiple components in electronic device 10 are connected to I / O interface 15, including: input unit 16, such as keyboard, mouse, etc.; output unit 17, such as various types of displays, speakers, etc.; storage unit 18, such as disk, optical disk, etc.; and communication unit 19, such as network card, modem, wireless transceiver, etc. Communication unit 19 allows electronic device 10 to exchange information / data with other devices through computer networks such as the Internet and / or various telecommunications networks.

[0151] Processor 11 can be a variety of general-purpose and / or special-purpose processing components with processing and computing capabilities. Some examples of processor 11 include, but are not limited to, a central processing unit (CPU), a graphics processing unit (GPU), various special-purpose artificial intelligence (AI) computing chips, various processors running machine learning model algorithms, a digital signal processor (DSP), and any suitable processor, controller, microcontroller, etc. Processor 11 performs the various methods and processes described above, such as the data table row-column dual-memory conversion method.

[0152] In some embodiments, the data table row-column dual-access conversion method can be implemented as a computer program tangibly contained in a computer-readable storage medium, such as storage unit 18. In some embodiments, part or all of the computer program can be loaded and / or installed on electronic device 10 via ROM 12 and / or communication unit 19. When the computer program is loaded into RAM 13 and executed by processor 11, one or more steps of the data table row-column dual-access conversion method described above can be performed. Alternatively, in other embodiments, processor 11 can be configured to perform the data table row-column dual-access conversion method by any other suitable means (e.g., by means of firmware).

[0153] Various embodiments of the systems and techniques described above herein can be implemented in digital electronic circuit systems, integrated circuit systems, field-programmable gate arrays (FPGAs), application-specific integrated circuits (ASICs), application-specific standard products (ASSPs), systems-on-a-chip (SoCs), payload-programmable logic devices (CPLDs), computer hardware, firmware, software, and / or combinations thereof. These various embodiments may include implementations in one or more computer programs that can be executed and / or interpreted on a programmable system including at least one programmable processor, which may be a dedicated or general-purpose programmable processor, capable of receiving data and instructions from a storage system, at least one input device, and at least one output device, and transmitting data and instructions to the storage system, the at least one input device, and the at least one output device.

[0154] Computer programs used to implement the methods of the present invention may be written in any combination of one or more programming languages. These computer programs may be provided to a processor of a general-purpose computer, a special-purpose computer, or other programmable data processing device, such that when executed by the processor, the computer programs cause the functions / operations specified in the flowcharts and / or block diagrams to be performed. The computer programs may be executed entirely on a machine, partially on a machine, or as a standalone software package, partially on a machine and partially on a remote machine, or entirely on a remote machine or server.

[0155] In the context of this invention, a computer-readable storage medium can be a tangible medium that may contain or store a computer program for use by or in conjunction with an instruction execution system, apparatus, or device. A computer-readable storage medium may include, but is not limited to, electronic, magnetic, optical, electromagnetic, infrared, or semiconductor systems, apparatus, or devices, or any suitable combination thereof. Alternatively, a computer-readable storage medium may be a machine-readable signal medium. More specific examples of machine-readable storage media include electrical connections based on one or more wires, portable computer disks, hard disks, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), optical fibers, portable compact disk read-only memory (CD-ROM), optical storage devices, magnetic storage devices, or any suitable combination thereof.

[0156] To provide interaction with a user, the systems and techniques described herein can be implemented on an electronic device having: a display device (e.g., a CRT (cathode ray tube) or LCD (liquid crystal display) monitor) for displaying information to the user; and a keyboard and pointing device (e.g., a mouse or trackball) through which the user provides input to the electronic device. Other types of devices can also be used to provide interaction with the user; for example, feedback provided to the user can be any form of sensory feedback (e.g., visual feedback, auditory feedback, or tactile feedback); and input from the user can be received in any form (including sound input, voice input, or tactile input).

[0157] The systems and technologies described herein can be implemented in computing systems that include backend components (e.g., as data servers), or middleware components (e.g., application servers), or frontend components (e.g., user computers with graphical user interfaces or web browsers through which users can interact with implementations of the systems and technologies described herein), or any combination of such backend, middleware, or frontend components. The components of the system can be interconnected via digital data communication of any form or medium (e.g., communication networks). Examples of communication networks include local area networks (LANs), wide area networks (WANs), blockchain networks, and the Internet.

[0158] A computing system can include clients and servers. Clients and servers are generally located far apart and typically interact through communication networks. The client-server relationship is created by computer programs running on the respective computers and having a client-server relationship with each other. The server can be a cloud server, also known as a cloud computing server or cloud host, which is a hosting product within the cloud computing service system to address the shortcomings of traditional physical hosts and VPS services, such as high management difficulty and weak business scalability.

[0159] It should be understood that the various forms of processes shown above can be used, with steps reordered, added, or deleted. For example, the steps described in this invention can be executed in parallel, sequentially, or in different orders, as long as the desired result of the technical solution of this invention can be achieved, and this is not limited herein.

[0160] The specific embodiments described above do not constitute a limitation on the scope of protection of this invention. Those skilled in the art should understand that various modifications, combinations, sub-combinations, and substitutions can be made according to design requirements and other factors. Any modifications, equivalent substitutions, and improvements made within the spirit and principles of this invention should be included within the scope of protection of this invention.

Claims

1. A method for converting data tables to both row and column storage, characterized in that, include: Determine the first table; The first table is an empty column-store data table that has been created; The second table is scanned, segmented, and compressed to obtain the first data block; The second table is a row-based data table that requires both row tables and list tables to exist simultaneously; Store the first data block in the first table to obtain the third table; The third table is a data table with both rows and columns stored; The step of scanning, segmenting, and compressing the second table to obtain the first data block includes: The second table is scanned and divided into segments according to a preset number to obtain at least one segmented data block. The row data in each segmented data block is decomposed into column data segments, and a common identifier is assigned to the column data segments corresponding to each segmented data block. The obtained column data segments are used as the first data. The columnar data segments in the first data are compressed according to the identifiers corresponding to the columnar data segments to obtain at least one first data block; The step of dividing the second table according to a preset number includes: When a preset number of physical pages are scanned, the data is divided in the order of the physical pages until the second table is completely scanned. If the remaining physical pages of the second table do not meet the preset number when the end of the second table is scanned, the remaining physical pages are merged with the previous segmented data block. The process of obtaining the third table also includes power-on recovery of the third table, including: Each coordinating node scans its matching system tables, creates a memory record for each of the third tables, and each data node scans its matching system tables, creates a memory record for each of the third tables, and restores the row group unit information of each third table; wherein, the row group unit information is restored by scanning the compressed unit descriptor table of the column storage table corresponding to the third table and the fourth table to obtain the identifier and dynamic row number of the records in the table; the fourth table is used to record the modification information of the first table; Each of the coordinating nodes periodically checks whether the data nodes have been fully restored. If all the data nodes have been fully restored, the coordinating node's row and column dual-store state becomes available.

2. The method according to claim 1, characterized in that, After storing the first data block in the first table to obtain the third table, the process includes: Determine the first modification information; the first modification information is the information that requires modification of the data in the first table and the second table; The second data is determined based on the first modification information; the second data is the updated data in the second table. The first identifier is determined based on the second data; the first identifier uniquely represents the position of the second data in the second table. The row group unit is determined according to the first identifier, and the row group unit saves the first modification information to the fourth table; the fourth table is used to record the modification information.

3. The method according to claim 2, characterized in that, After the row group cell saves the first modification information to the fourth table, it includes: Determine the number of lines to modify; If the number of modified rows exceeds the preset number of rows, then third data is obtained from the second table; the third data is the original data and modified data corresponding to the second identifier in the second table; the second identifier is the identifier of the first data block corresponding to the row group unit; The third data is used to generate a second data block; The first data block corresponding to the second identifier is replaced according to the second data block, and the fourth table is cleared.

4. The method according to claim 1, characterized in that, After storing the first data block in the first table to obtain the third table, the process includes: Confirm the query information; At least one first path is generated based on the query information, and a consumption calculation is performed on the at least one first path to obtain a first result; the consumption calculation is to calculate the resources of the device required to execute according to at least one first path; Based on the first result, at least one first path is filtered to obtain a second path; The first task is generated based on the second path; Update the data in the first table according to the first task.

5. The method according to claim 4, characterized in that, The step of updating the data in the first table according to the first task includes: The third identifier is obtained from the fourth table; the third identifier is a new identifier added to the second table, but has not been synchronized to the row identifier corresponding to the data in the third data block. The fourth data is obtained by retrieving the corresponding data from the second table based on the third identifier; The third data block is updated based on the fourth data.

6. The method according to claim 5, characterized in that, Before updating the third data block based on the fourth data, the process includes: The fifth data is obtained from the third data block; the fifth data is the data obtained after decompressing the third data block. The fifth data is updated based on the first bitmap; the first bitmap is used to represent the state of data that has been deleted from the second table but not updated in the third data block.

7. A data table row and column dual-storage conversion device, characterized in that, include: The first table determination module is used to determine the first table; The first table is an empty column-store data table that has been created; The first data block determination module is used to scan, segment, and compress the second table to obtain the first data block. The second table is a row-based data table that requires both row tables and list tables to exist simultaneously; The storage module is used to store the first data block into the first table to obtain the third table; The third table is a data table with both rows and columns stored; The first data block determination module includes: The first data determination unit is used to scan the second table and divide the second table into at least one segmented data block according to a preset number. The row data in each segmented data block is decomposed into column data segments, and a common identifier is assigned to the column data segments corresponding to each segmented data block. The obtained column data segments are used as the first data. The first data block determination unit is used to compress the column data segment in the first data according to the identifier corresponding to the column data segment to obtain at least one first data block; The step of dividing the second table according to a preset number includes: When a preset number of physical pages are scanned, the data is divided in the order of the physical pages until the second table is completely scanned. If the remaining physical pages of the second table do not meet the preset number when the end of the second table is scanned, the remaining physical pages are merged with the previous segmented data block. The data table row-column dual-store conversion device is further used for: After obtaining the third table, power-on recovery of the third table includes: Each coordinating node scans its matching system tables, creates a memory record for each of the third tables, and each data node scans its matching system tables, creates a memory record for each of the third tables, and restores the row group unit information of each third table; wherein, the row group unit information is restored by scanning the compressed unit descriptor table of the column storage table corresponding to the third table and the fourth table to obtain the identifier and dynamic row number of the records in the table; the fourth table is used to record the modification information of the first table; Each of the coordinating nodes periodically checks whether the data nodes have been fully restored. If all the data nodes have been fully restored, the coordinating node's row and column dual-store state becomes available.

8. An electronic device, characterized in that, The electronic device includes: At least one processor; and A memory communicatively connected to the at least one processor; wherein, The memory stores a computer program that can be executed by the at least one processor, the computer program being executed by the at least one processor to enable the at least one processor to perform the data table row and column dual-store conversion method according to any one of claims 1-6.

9. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores computer instructions that are used to cause a processor to execute the data table row-column dual-storage conversion method according to any one of claims 1-6.

Images