Method and apparatus for integrating row store database metadata in column store database engine

Abstract

The application relates to the technical field of databases, and discloses a method and device for integrating row storage database metadata in a column storage database engine, which comprises the following steps: synchronously mapping row storage database metadata to a column storage database engine; performing a persistent operation on the row storage database metadata by a master node of the column storage database engine to generate metadata change logs and full-amount mirror files; and synchronously sending the change logs and the full-amount mirror files to all other backup nodes in the cluster by the master node of the column storage database engine. The method for integrating row storage database metadata in the column storage database engine can greatly reduce resource occupation when complex business scenarios and large-scale data sets are processed, and can significantly improve performance, thereby providing more powerful and flexible support for data-driven business decisions, data mining and complex data processing scenarios.

Description

Technical Field

[0001] This invention relates to the field of database technology, and specifically to a method for integrating row-based database metadata into a column-based database engine. Background Technology

[0002] In today's era of widespread big data applications, databases have become crucial for data storage and management. Currently, the commonly available independent row-oriented and independent column-oriented databases each have their own advantages and characteristics in terms of data query processing speed and large-scale data analysis. However, with the continuous development of database technology, many business models require the simultaneous use of both row-oriented and column-oriented databases to meet diverse needs for data storage, querying, and analysis. How to better integrate the two to fully leverage their respective advantages has become a pressing issue for the industry.

[0003] In existing database application scenarios, the following situations are often encountered: On the one hand, column-oriented databases are widely used in analytical scenarios such as data warehouses due to their advantages such as efficient data compression and fast aggregation queries, but they are relatively insufficient in supporting frequent update and insert operations in transactional scenarios; on the other hand, row-oriented databases perform well in handling transactional scenarios and are relatively efficient in reading and writing row-level data, but their performance is often not as good as column-oriented databases when analyzing and querying large amounts of data.

[0004] This means that in complex real-world business scenarios, when both transaction processing and data analysis need to be handled simultaneously, a single storage model cannot meet the requirements. For example, in an e-commerce system containing massive amounts of user transaction records, on the one hand, it is necessary to frequently update user purchasing behavior and order status in real time (which is more suitable for row-based databases), and on the other hand, it is necessary to perform multi-dimensional statistical analysis on this massive amount of transaction data, such as sales trend analysis by region, time, and product category (which is more suitable for column-based databases).

[0005] Currently, although some databases have attempted to switch between or merge columnar and row-based storage, most methods involve repeatedly converting data between the two storage structures. This not only increases system resource consumption but can also lead to data inconsistency and low query efficiency. Particularly in columnar database engines, managing and integrating metadata from row-based databases faces numerous challenges, including complex metadata interaction and difficulties in update synchronization. Metadata, as data describing data, plays a crucial role in the efficient operation and accurate querying of databases, including table structure information, index information, and constraint information. However, current technologies often only perform simple copy-and-paste processing when introducing metadata from row-based databases into columnar database engines, failing to consider the fundamental differences between the two in terms of storage structure, data access patterns, and transaction processing mechanisms. This results in metadata not being effectively utilized, failing to fully leverage the respective advantages of columnar and row-based databases, and impacting the overall performance and functionality of the database. Summary of the Invention

[0006] This application aims to solve the problems of difficulty in integrating metadata between column-oriented database engines and row-oriented databases in the prior art, and the inability to utilize it efficiently. It aims to achieve efficient integration of metadata between column-oriented and row-oriented databases at the metadata level, give full play to the advantages of both, and improve the overall performance and availability of the database in complex business scenarios.

[0007] To achieve the objective of this invention, embodiments of this application provide a method for integrating row-oriented database metadata into a column-oriented database engine, characterized by comprising: Row-oriented database metadata is synchronously mapped to column-oriented database engine; The column-oriented database engine master node performs persistence operations on the metadata of the row-oriented database, generating metadata change logs and full image files; The column-oriented database engine master node synchronizes change logs and full image files to all other standby nodes in the cluster.

[0008] b. Metadata Dynamic Update and Synchronization Mechanism: This device is used when the row-based database data is constantly being updated. To achieve the objectives of this invention, this application also provides an electronic device, including a processor and a memory, wherein the memory stores a program that can run on the processor, characterized in that, when the program is executed by the processor, it implements the steps of the method for integrating row-oriented database metadata in a column-oriented database engine as described above.

[0009] To achieve the objectives of this invention, embodiments of this application also provide a computer-readable storage medium storing at least one program, characterized in that the at least one program can be executed by at least one processor to implement the steps of the above-described method for integrating row-oriented database metadata in a column-oriented database engine.

[0010] The method for integrating row-based database metadata into the column-based database engine of this invention can significantly reduce resource consumption and improve performance when dealing with complex business scenarios and large-scale datasets, providing more powerful and flexible support for data-driven business decisions, data mining, and complex data processing scenarios. Attached Figure Description

[0011] Figure 1 A flowchart illustrating the process of integrating row-based database metadata into a column-based database engine; Figure 2 This is a sequence diagram of the synchronous mapping row storage database metadata in synchronous mode; Figure 3 This is a sequence diagram of synchronous mapping row storage database metadata in asynchronous mode; Figure 4 This is a schematic diagram of the metadata persistence process for a row-based database. Figure 5 The sequence diagram for internal master-slave synchronization of metadata columns in the row-based database is provided. Detailed Implementation

[0012] To make the technical solution of the present invention easier to understand, the technical solution of the present invention will be further described in detail below with reference to specific embodiments and accompanying drawings. Obviously, the following embodiments are merely some embodiments of the present invention and do not constitute a limitation on the technical solution of the present invention. For those skilled in the art, other embodiments can be obtained based on the embodiments of this application without creative effort.

[0013] It should be noted that the order of steps described in the method embodiments below does not constitute a limitation on the technical solution of the present invention. For those skilled in the art, some steps can be reasonably adjusted according to the embodiments of this application without creative effort.

[0014] Example 1

[0015] This embodiment provides a method for integrating row-oriented database metadata into a column-oriented database engine, such as... Figure 1 As shown, it includes the following steps: Step S1: Synchronize and map row-based database metadata to column-based database engine. The column-oriented database (column-oriented) engine synchronizes metadata with the row-oriented database (row-oriented) in two ways: synchronous mode and asynchronous mode. In synchronous mode, when metadata changes occur in the row-oriented database, it sends a specified SQL statement to the column-oriented engine after the row-oriented database completes its synchronization. It then waits for the column-oriented database to complete the synchronization before returning a response upstream. The column-oriented database synchronizes metadata as part of the overall row-oriented database metadata change process. If the column-oriented database metadata synchronization fails, the row-oriented database metadata synchronization will also fail and roll back the data to the state before the metadata change operation.

[0016] In asynchronous mode, after a change to the row-level metadata, the corresponding ZooKeeper node will update its own metadata storage. The column-level engine listens to this node and updates its own row-level metadata upon receiving the corresponding metadata change event. This process is asynchronous with the row-level metadata change operation; success or failure will not affect the row-level metadata operation. The method for obtaining metadata is consistent in both modes: first, the distributed row-level database data distribution node information is obtained from the ZooKeeper node; then, a JDBC connection is used to connect to the corresponding data node and execute a query to obtain the metadata. Furthermore, in the column-level engine, for data types, indexes, and statistics that differ between the row-level database and the column-level engine, data type compatibility mapping, index type conversion, and filtering and supplementing statistical information are used to map them to the column-level engine, providing a foundation for subsequent queries of row-level data.

[0017] like Figure 2 The diagram shown is a sequence diagram of the synchronous mapping row storage database metadata in synchronous mode, which includes the following steps: Step 1-1: Issue SQL to the row storage database to start the metadata synchronization process.

[0018] Steps 1-2: The columnar storage engine identifies the data node to be queried by storing data distribution information in memory or in ZooKeeper metadata nodes.

[0019] Steps 1-3: The columnar storage engine retrieves metadata information by querying data nodes via JDBC.

[0020] Specifically, when retrieving metadata, the column-oriented storage engine first integrates heterogeneous metadata from multiple sources. The engine receives external data source access requests submitted by users via the CREATE CATALOG statement, parses the jdbc_url connection string carried in the request, identifies the column-oriented database type through prefix matching (including MySQL, PostgreSQL, Oracle, SQL Server, DB2, ClickHouse, GBase, GoldenDB, etc.), and determines the driver class and dialect adapter required for subsequent metadata retrieval based on this.

[0021] Steps 1-4: The column storage engine maps row storage metadata information to the column storage engine through data type compatibility mapping, index type conversion, and filtering and supplementing statistical information.

[0022] Specifically, the column-oriented storage engine performs precise type-by-type mapping on the private type systems of each row-oriented database, converting them into the standard type representation of the column-oriented storage engine. For private types that exist in the row-oriented database but for which the column-oriented storage engine does not have a corresponding type (such as Oracle's NCLOB, some spatial types, etc.), the column-oriented storage engine performs fallback mapping using a variable string type to ensure uninterrupted metadata access.

[0023] Steps 1-5: The columnar storage engine returns the metadata change result response to the row storage database.

[0024] like Figure 3 The diagram shown is a sequence diagram of synchronous mapping row storage database metadata in asynchronous mode, which includes the following steps: Step 1-1: The columnar storage engine listens for ZooKeeper node metadata change events.

[0025] Step 1-2: When a metadata change event is detected, the columnar storage engine verifies the metadata version number in memory with the metadata version number in the changed node to confirm whether to perform metadata synchronization.

[0026] The conditions for performing metadata synchronization are: the version number in the change node is greater than the current version number in memory, and the corresponding status of this version number in the version status list is ready (i.e., the row storage DDL has been successfully completed). When the above two conditions are met, the column storage engine pulls the latest metadata from the row storage data node in full, including column definitions, index information and statistical information, and updates the metadata objects in memory and persistent logs.

[0027] Conditions under which metadata synchronization will not be performed: The synchronization will be skipped if any of the following conditions exist: First, the version number in the change node is equal to the version number in memory, indicating that the metadata has not changed substantially and there is no need to fetch it again; Second, the status corresponding to the version number is a failed state, a blacklist state, or a recovery state, indicating that the row storage side DDL has not been successfully completed or is in an abnormal process, and the column storage engine should not perform synchronization at this time to avoid writing inconsistent metadata; Third, the change event is in synchronous mode (not asynchronous mode). Such events are processed through other channels and the ZK listening path does not respond.

[0028] Steps 1-3: Identify the data node to be queried by using the data distribution information stored in memory or in the ZooKeeper metadata node.

[0029] Steps 1-4: The columnar storage engine retrieves metadata information by querying data nodes via JDBC.

[0030] Specifically, when retrieving metadata, the column-oriented storage engine first integrates heterogeneous metadata from multiple sources. The engine receives external data source access requests submitted by users via the CREATE CATALOG statement, parses the jdbc_url connection string carried in the request, identifies the column-oriented database type through prefix matching (including MySQL, PostgreSQL, Oracle, SQL Server, DB2, ClickHouse, GBase, GoldenDB, etc.), and determines the driver class and dialect adapter required for subsequent metadata retrieval based on this.

[0031] Steps 1-5: The column storage engine maps row storage metadata information to the column storage engine through data type compatibility mapping, index type conversion, and filtering and supplementing statistical information.

[0032] Specifically, the column-oriented storage engine performs precise type-by-type mapping on the private type systems of each row-oriented database, converting them into the standard type representation of the column-oriented storage engine. For private types that exist in the row-oriented database but for which the column-oriented storage engine does not have a corresponding type (such as Oracle's NCLOB, some spatial types, etc.), the column-oriented storage engine performs fallback mapping using a variable string type to ensure uninterrupted metadata access.

[0033] Step S2: Persistence of row-based database metadata In database systems, metadata is typically persisted to reduce metadata query interactions and meet requirements such as master-slave metadata synchronization. In this embodiment, row-level metadata also needs to be persisted after processing to facilitate subsequent master-slave synchronization and master-slave failover for column-level nodes. The persistence of row-level metadata begins by generating a change log, which records metadata changes chronologically, such as database table information and operation types. After certain conditions are met, a checkpoint thread is initiated to generate a mirror file containing all metadata of the current column-level engine (including row-level metadata and the column-level engine's own metadata), and all change logs prior to the generation of the mirror file are cleared.

[0034] like Figure 4 The diagram illustrates the metadata persistence process in a row-based database, which includes the following steps: Step 2-1: After the metadata synchronization is completed, the columnar storage engine master node serializes the corresponding change operation into a change log according to the change type; Step 2-2: The columnar storage engine master node determines whether the number of change logs has reached the columnar storage engine's set threshold or whether the columnar storage engine's running time has reached the metadata refresh time threshold; Steps 2-3: When the number of change logs reaches the threshold set by the columnar storage engine or the columnar storage engine running time reaches the threshold for refreshing metadata, the columnar storage engine master node starts the checkpoint thread to save the overall metadata in JSON format and persist it as a full image file.

[0035] At this point, the column-oriented engine has completed the persistence of metadata in the row-oriented database.

[0036] Step S3: Synchronize row-based database metadata across multiple nodes within the column-oriented storage engine. After the columnar storage engine master node generates change logs and full image files, it will synchronize them with all other standby nodes in the cluster, enabling the standby nodes to synchronize their row storage metadata information with the master node.

[0037] like Figure 5 The diagram shown is a sequence diagram of the master-slave synchronization mechanism within the row-based database metadata column-based storage engine. The specific steps include: Step 3-1: After the master node generates the change log file, it sends it to other backup nodes according to the set synchronization strategy (write majority / all write).

[0038] Step 3-2: The standby node replays the change log, modifies the metadata in memory, and completes the synchronization of row-store metadata with the master node.

[0039] Step 3-3: After the master node generates a brand new full image file, it replaces the old local image file and notifies the backup nodes to obtain the new image file.

[0040] Steps 3-4: The standby node actively pulls the new image file via HTTP to replace the old local image file.

[0041] Steps 3-5: The primary and backup nodes each start their own checkpoint threads to replay the latest metadata in memory.

[0042] Steps 3-6: The primary and backup nodes respectively clean up the change log files up to the point before the image file was generated.

[0043] This completes the entire process of integrating row-oriented database metadata into a column-oriented database engine.

[0044] The method of integrating row-store database metadata into the column-store database engine in this embodiment effectively improves the fusion efficiency of the column-store database engine and row-store database metadata. This has a significant positive impact on the database system, especially when handling complex business scenarios and large-scale data analysis. Through metadata mapping logic, seamless integration of metadata under the two storage modes is achieved, providing a unified and accurate metadata foundation for data querying and processing. This ensures data consistency and integrity, and avoids query errors caused by metadata differences. Query optimization based on metadata fusion can intelligently select the most suitable storage mode and data access path. The intelligent query routing and data reorganization strategies fully utilize the advantages of both column-store and row-store databases, achieving efficient data querying and processing, significantly improving query efficiency and overall database performance. The performance improvement is particularly noticeable for queries involving multi-table joins and complex aggregations, allowing users to obtain accurate query results faster and enhancing the overall user experience.

[0045] The method for integrating row-oriented database metadata in the column-oriented database engine of this embodiment integrates row-oriented database metadata of different structures and formats through multi-source heterogeneous metadata integration, forming a standardized metadata view suitable for column-oriented database engines. This enables the system to be compatible with multiple row-oriented database data sources, effectively solves the metadata fusion problem in multi-source heterogeneous data environments, and improves the system's compatibility and scalability.

[0046] The dynamic metadata update and synchronization mechanism can monitor changes in metadata in real time and synchronize the updated metadata to the columnar database engine in a timely manner, ensuring the timeliness and accuracy of metadata, avoiding data query errors or performance degradation caused by inconsistent metadata, and improving the stability and reliability of the database system.

[0047] Example 2

[0048] This embodiment provides an electronic device, including a processor and a memory communicatively connected to the processor. The memory stores a program executable on the processor, which, when executed by the processor, implements the steps of the method for integrating row-oriented database metadata in the column-oriented database engine provided in the above embodiment.

[0049] The above programs can be written in one or more programming languages ​​or a combination thereof, including object-oriented programming languages ​​such as Java, Smalltalk, and C++, as well as conventional procedural programming languages ​​such as C or similar languages. The program code can be executed entirely on the user's computer, partially on the user's computer, as a standalone software package, partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server.

[0050] Example 3

[0051] This embodiment provides a computer-readable storage medium storing at least one program that can be executed by at least one processor to implement the steps of the method for integrating row-oriented database metadata in a column-oriented database engine provided in the above embodiment.

[0052] The aforementioned computer-readable storage media include: USB flash drives, portable hard drives, read-only memory, random access memory, magnetic disks, optical disks, and other media and combinations thereof capable of storing program code. With the development of science and technology, the meaning of storage media may become increasingly broad, extending beyond physical media.

[0053] Those skilled in the art will understand that the above embodiments are specific embodiments for implementing the present invention. In addition to the above embodiments, the present invention may have other implementation methods. All technical solutions formed by equivalent substitution or equivalent transformation fall within the protection scope claimed by the present invention.

Claims

1. A method for integrating row-oriented database metadata into a column-oriented database engine, characterized in that, include: Row-oriented database metadata is synchronously mapped to column-oriented database engine; The column-oriented database engine master node performs persistence operations on the metadata of the row-oriented database, generating metadata change logs and full image files; The column-oriented database engine master node synchronizes change logs and full image files to all other standby nodes in the cluster.

2. The method for integrating row-oriented database metadata in a column-oriented database engine as described in claim 1, characterized in that, The synchronous mapping of row-oriented database metadata to column-oriented database engine includes using a synchronous mode, specifically including: The SQL statement is sent to the row-level database to initiate the metadata synchronization process. The column-oriented database engine identifies the data node to be queried based on data distribution information; The column-oriented database engine queries data nodes to obtain metadata information and maps the metadata information to the column-oriented database. The column-oriented database engine returns metadata change results in response to the row-oriented database.

3. The method for integrating row-oriented database metadata in a column-oriented database engine as described in claim 1, characterized in that, The synchronous mapping of row-oriented database metadata to column-oriented database engine includes using an asynchronous mode, specifically including: The columnar database engine listens for ZooKeeper node metadata change events; When a metadata change event is detected, the column-store database engine verifies the version number of the metadata in memory with the version number of the metadata in the change node to determine whether to perform metadata synchronization. The column-oriented database engine identifies the data node to be queried based on data distribution information; The column-oriented database engine retrieves metadata information and maps row-oriented metadata information to the column-oriented database engine.

4. The method for integrating row-oriented database metadata in a column-oriented database engine as described in claim 1, characterized in that, The column-oriented database engine master node performs persistence operations on the row-oriented database metadata, generating a metadata change log and a full image file, including: Serialize the corresponding change operations into change logs based on the metadata change type; Determine whether the number of change logs has reached the threshold set by the column-oriented database engine or whether the column-oriented database engine has reached the metadata refresh time threshold. When the number of change logs reaches the threshold set by the column-oriented database engine or when the column-oriented database engine reaches the metadata refresh time threshold, the overall metadata will be saved and persisted as a full image file.

5. The method for integrating row-oriented database metadata in a column-oriented database engine as described in claim 1, characterized in that, Also includes: The primary and backup nodes respectively clean up the change log files up to the point before the image file was generated.

6. The method for integrating row-oriented database metadata in a column-oriented database engine as described in claim 1, characterized in that, The column-oriented database engine master node synchronizes the change logs and full image files to all other standby nodes in the cluster, including: The backup node replays the change log, modifies the metadata in memory, and completes the synchronization of the row storage database metadata information with the master node; The backup node actively obtains the image file via HTTP and replaces the old local image file.

7. An electronic device comprising a processor and a memory, the memory storing a program executable on the processor, characterized in that, When the program is executed by the processor, it implements the steps of the method for integrating row-oriented database metadata in a column-oriented database engine as described in any one of claims 1-6.

8. A computer-readable storage medium storing at least one program, characterized in that, The at least one program may be executed by at least one processor to implement the steps of the method for integrating row-store database metadata in a column-store database engine as described in any one of claims 1-6.

Images