Data migration method and device, computer equipment and readable storage medium

Abstract

The invention relates to a data migration method and device, computer equipment and a readable storage medium. The method comprises the steps of obtaining a target structured query statement of a relational database, and determining an initial query key according to the target structured query statement; determining a column family and a row key according to the initial query key and the target structured query statement, and generating a storage structure of a column storage database according to the column family and the row key; and migrating the data in the relational database to the column storage database according to the storage structure of the column storage database. By adopting the method, the data migration efficiency from the relational database to the column storage database can be improved.

Description

Technical Field

[0001] This application relates to the field of database technology, and in particular to a data migration method, apparatus, computer equipment, and readable storage medium. Background Technology

[0002] With the rapid development of internet technology and the arrival of the big data era, data volume has exploded. Traditional relational databases have gradually shown their limitations when handling large-scale data, high-concurrency access, and diverse data types. These problems have spurred the rise of column-oriented database technology. Column-oriented databases, with their flexible data models, powerful horizontal scalability, and superior read / write performance, offer a new solution to the storage and access challenges of the big data era.

[0003] However, many existing business systems and applications are still built on relational databases. Migrating these systems to column-oriented databases involves not only changes in data format and storage methods, but also considerations for query performance, data consistency, and integrity after the migration. Traditional data migration solutions from relational databases to column-oriented databases rely heavily on the experience and manual operation of database administrators, resulting in low migration efficiency. Summary of the Invention

[0004] Therefore, it is necessary to provide a data migration method, apparatus, computer equipment, and readable storage medium that can improve the efficiency of data migration from relational databases to column-oriented databases, addressing the aforementioned technical problems.

[0005] Firstly, this application provides a data migration method, including:

[0006] Obtain the target structured query statement from the relational database, and determine the initial query key based on the target structured query statement;

[0007] The column family and row key are determined based on the initial query key and the target structured query statement, and the storage structure of the column storage database is generated based on the column family and row key.

[0008] Migrate data from a relational database to a column-oriented database using the same storage structure.

[0009] In one embodiment, determining the column family and row key based on the initial query key and the target structured query statement includes:

[0010] Based on the initial query key, determine the relational database tables with relationships and independent relational database tables in the relational database; merge the relational database tables with relationships into the same column family according to the target structured query statement; and generate independent column families based on the independent relational database tables.

[0011] The row key is determined based on the initial query key.

[0012] In one embodiment, determining the relational database tables with existing relationships based on the initial query key in the relational database includes:

[0013] In the initial query key, identify multiple first query keys with the same key value and corresponding second query keys for multiple relational database tables;

[0014] The relational database tables corresponding to multiple first query keys are identified as relational database tables with associated relationships, and the relational database tables corresponding to multiple second query keys are identified as relational database tables with associated relationships.

[0015] In one embodiment, determining the row key based on the initial query key includes:

[0016] For the primary key in the initial query key, the initial query key is determined as the row key;

[0017] For non-primary keys in the initial query key, combine the initial query key with the primary key of the relational database table corresponding to the initial query key to obtain the row key;

[0018] For primary keys in relational database tables that are not used as row keys, create a new columnstore table based on the primary key and use the primary key as the row key of the new columnstore table.

[0019] In one embodiment, obtaining the target structured query statement from the relational database and determining the initial query key based on the target structured query statement includes:

[0020] From the query logs of the relational database, obtain the structured query statements with read patterns, and identify the structured query statements with read patterns as the target structured query statements;

[0021] The initial query key is determined based on the condition fields in the target structured query statement.

[0022] In one embodiment, determining the initial query key based on the condition fields in the target structured query statement includes:

[0023] For empty condition fields, the primary key of the database table corresponding to the target structured query statement is used as the initial query key;

[0024] For condition fields that include a referenced field, the referenced field is used as the initial query key.

[0025] In one embodiment, the method further includes:

[0026] Based on the test data, relational databases and column-oriented databases were tested respectively, and performance index data of relational databases and column-oriented databases were obtained;

[0027] The performance metrics data of relational databases and column-store databases are weighted and summed to obtain performance evaluation scores;

[0028] Optimize row keys and column families of columnstore databases based on performance evaluation scores.

[0029] Secondly, this application also provides a data migration apparatus, comprising:

[0030] The query key determination module is used to obtain the target structured query statement of the relational database and determine the initial query key based on the target structured query statement.

[0031] The structure generation module is used to determine the column family and row key based on the initial query key and the target structured query statement, and to generate the storage structure of the column storage database based on the column family and row key.

[0032] The data migration module is used to migrate data from a relational database to a column-store database according to the storage structure of a column-store database.

[0033] Thirdly, this application also provides a computer device, including a memory and a processor, wherein the memory stores a computer program, and the processor executes the computer program to perform the following steps:

[0034] Obtain the target structured query statement from the relational database, and determine the initial query key based on the target structured query statement;

[0035] The column family and row key are determined based on the initial query key and the target structured query statement, and the storage structure of the column storage database is generated based on the column family and row key.

[0036] Migrate data from a relational database to a column-oriented database using the same storage structure.

[0037] Fourthly, this application also provides a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, performs the following steps:

[0038] Obtain the target structured query statement from the relational database, and determine the initial query key based on the target structured query statement;

[0039] The column family and row key are determined based on the initial query key and the target structured query statement, and the storage structure of the column storage database is generated based on the column family and row key.

[0040] Migrate data from a relational database to a column-oriented database using the same storage structure.

[0041] Fifthly, this application also provides a computer program product, including a computer program that, when executed by a processor, performs the following steps:

[0042] Obtain the target structured query statement from the relational database, and determine the initial query key based on the target structured query statement;

[0043] The column family and row key are determined based on the initial query key and the target structured query statement, and the storage structure of the column storage database is generated based on the column family and row key.

[0044] Migrate data from a relational database to a column-oriented database using the same storage structure.

[0045] The aforementioned data migration method, apparatus, computer equipment, and readable storage medium acquire the target structured query statement from the relational database and determine the initial query key based on the target structured query statement. Since the initial query key describes the actual access pattern of the application, column families and row keys are determined based on the initial query key and the target structured query statement. This aligns the column family design with the actual access pattern, avoids reading and writing irrelevant fields, reduces data redundancy, and improves data query efficiency. Determining the row key based on the initial query key ensures that the data distribution is consistent with the query path and also guarantees data uniqueness. A column-store database storage structure is generated based on the column families and row keys. Data from the relational database is migrated to the column-store database according to this storage structure, ensuring a high degree of matching between the column-store database storage structure and the actual access pattern. This achieves reduced data redundancy, improved query efficiency, improved migration efficiency, and guaranteed consistency during the data migration process. Furthermore, it addresses the shortcomings of traditional migration processes, such as insufficient decision support and schema evaluation tools and a lack of standard data migration guidelines. Attached Figure Description

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

[0047] Figure 1 This is a flowchart illustrating a data migration method in one embodiment;

[0048] Figure 2 This is a flowchart illustrating the process of determining column families and row keys based on an initial query key and a target structured query statement in one embodiment.

[0049] Figure 3 This is a structural block diagram of a data migration device in one embodiment;

[0050] Figure 4 This is an internal structural diagram of a computer device in one embodiment. Detailed Implementation

[0051] 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.

[0052] It should be noted that the terms "first," "second," etc., used in this application may be used to describe various elements, but these elements are not limited by these terms. These terms are only used to distinguish the first element from the second element. The terms "comprising" and "having," and any variations thereof, used in this application, are intended to cover non-exclusive inclusion. The term "multiple" used in this application refers to two or more.

[0053] In one exemplary embodiment, such as Figure 1 As shown, a data migration method is provided. This embodiment illustrates the method applied to a terminal, but it is understood that the method can also be applied to a server, and to a system including both a terminal and a server, and is implemented through interaction between the terminal and the server. In this embodiment, the method includes the following steps:

[0054] Step 102: Obtain the target structured query statement from the relational database, and determine the initial query key based on the target structured query statement.

[0055] Relational databases are database systems based on the relational model, which uses tables, rows, and columns to organize data. This model allows for flexible querying and data manipulation using SQL (Structured Query Language), supporting complex transaction processing and data integrity constraints. Relational databases are widely used in business scenarios requiring high consistency and reliability, such as financial, healthcare, and enterprise resource planning systems. The target structured query statement refers to the SQL used to characterize the application's data access patterns. The initial query key is extracted from the target structured query statement and is a key field used to filter data, describing the application's actual access patterns.

[0056] In some implementations, target structured query statements are read from the query log of a relational database, and initial query keys are determined in each target structured query statement.

[0057] Step 104: Determine the column family and row key based on the initial query key and the target structured query statement, and generate the storage structure of the column storage database based on the column family and row key.

[0058] Column-store databases optimize storage efficiency and query performance by storing data by columns instead of rows. This storage method makes data compression more effective, and queries for specific columns are faster, especially when performing data aggregation operations. Column-store databases support efficient parallel processing and are suitable for random read / write access to large datasets. While they may be slightly slower for update and delete operations, they perform well in data warehouses and Online Analytical Processing (OLAP) systems. A column family is a storage structure unit in a column-store database that groups multiple columns together based on logical relationships. A column family stores a set of columns with similar access patterns. The row key is a unique identifier for each record in a column-store database, used for quick data location.

[0059] In some implementations, column families are determined based on the initial query key and the target structured query statement. Row keys are then determined based on the initial query key. The storage structure of the columnstore database is generated based on the column families and row keys. This storage structure is essentially the columnstore table structure of the columnstore database.

[0060] Step 106: Migrate the data from the relational database to the column-store database according to the storage structure of the column-store database.

[0061] In some implementations, after the storage structure of a column-oriented database is designed based on a relational database, data migration can be performed. Data from the relational database is migrated to the column-oriented database according to its storage structure. For example, open-source tools can be used for data migration.

[0062] In the data migration method described above, the target structured query statement from the relational database is obtained, and the initial query key is determined based on the target structured query statement. Since the initial query key describes the actual access pattern of the application, column families and row keys are determined based on the initial query key and the target structured query statement. This aligns the column family design with the actual access pattern, avoids reading and writing irrelevant fields, reduces data redundancy, and improves data query efficiency. Determining the row key based on the initial query key ensures that the data distribution is consistent with the query path and also guarantees data uniqueness. A column-store database storage structure is generated based on the column families and row keys. Data from the relational database is then migrated to the column-store database according to this storage structure. This ensures that the column-store database storage structure is highly matched with the actual access pattern, thereby reducing data redundancy, improving query efficiency, improving migration efficiency, and ensuring consistency during the data migration process. Furthermore, this method addresses the problems of insufficient decision support and schema evaluation tools, and the lack of standard data migration guidelines in traditional migration processes.

[0063] In an exemplary embodiment, step 102, obtaining the target structured query statement of the relational database and determining the initial query key based on the target structured query statement, includes: obtaining the read-mode structured query statement from the query log of the relational database, determining the read-mode structured query statement as the target structured query statement; and determining the initial query key based on the condition fields in the target structured query statement.

[0064] In some implementations, read pattern SQL is extracted from the query logs of the relational database, focusing on the WHERE conditions in the SELECT statement, and used as the target structured query statement. Read patterns reflect the application's data access patterns and are crucial for designing efficient data structures.

[0065] The initial query key is determined based on the condition fields in the target structured query statement. For example, given a target structured query statement Q:SELECT... <list>From <list>Where <condition>The initial query key is determined based on the Condition field in Q.

[0066] In this embodiment, by analyzing the query logs of the relational database, the target structured query statement is automatically extracted from the query logs. This allows us to obtain the actual queries that occur during the actual runtime of the relational database, avoiding biases caused by analyzing SQL based on experience. Since the condition fields in the target structured query statement describe the filter fields and range query fields of the query, the initial query key can be accurately determined based on the condition fields in the target structured query statement.

[0067] In an exemplary embodiment, determining the initial query key based on the condition fields in the target structured query statement includes: for empty condition fields, determining the primary key of the database table corresponding to the target structured query statement as the initial query key; for condition fields including reference fields, determining the reference fields as the initial query key.

[0068] In some implementations, the specific rules for determining the initial query key are: (1) if <condition>If it is empty, then the primary key of the database table corresponding to the target structured query statement will be used as the initial query key; (2) If Condition is empty OPR , and If all fields are referenced, then the initial query key is... or Where OPR consists of logical operators such as =, <, and ≠; because and These are equivalent fields; selecting either side will describe the access pattern. If Condition is... If the OPR constant is used, then the initial lookup key is If the target structured query contains multiple <condition>So for each <condition>The system will determine whether the target structured query statement has multiple initial query keys.

[0069] In this embodiment, if the condition fields in the target structured query statement are empty, the access path cannot be inferred from the target structured query statement, and it is impossible to know which fields the user wants to filter or scan. In this case, directly using the primary key as the initial query key is the safest and most universal strategy, ensuring the reachability and uniqueness of subsequent queries. When the condition fields are not empty, the reference field that best reflects the data access pattern can be automatically extracted as the initial query key. By formulating corresponding rules for determining the initial query key for different types of condition fields, the key access fields affecting query performance, i.e., the initial query key, can be accurately identified. This is beneficial for generating the row key and column family structure that best matches the actual query path, significantly improving the access efficiency, sharding rationality, and data aggregation degree of the column-oriented database.

[0070] The data schema conversion process is described below. Data schema conversion: Columnstore tables in a columnstore database do not have direct relationships and do not support join queries. Typically, the conversion process requires designing column families to merge related tables from the relational database. Traditional nested loop methods generate many redundant column families, impacting the query performance of the columnstore database and causing additional disk space usage. Therefore, the main purpose of this step is to optimize query performance and reduce data redundancy, while simultaneously designing the row keys for the columnstore database to ensure unique data identification and efficient access.

[0071] In one exemplary embodiment, such as Figure 2 As shown, step 104, determining the column family and row key based on the initial query key and the target structured query statement, includes:

[0072] Step 202: Based on the initial query key, determine the relational database tables with relationships and the independent relational database tables in the relational database. Based on the target structured query statement, merge the relational database tables with relationships into the same column family, and generate independent column families based on the independent relational database tables.

[0073] Step 204: Determine the row key based on the initial query key.

[0074] Based on the initial query key, the relational database tables with existing relationships and independent relational database tables are identified within the relational database. Independent relational database tables are those without any existing relationships. Each group of relational database tables with existing relationships is merged into the same column family in the columnstore database. The first columnstore table stores the data from the relational database tables with existing relationships. For independent relational database tables, a separate column family is generated in the columnstore database for each independent relational database table. The second columnstore table stores the data from the independent relational database tables. The row key of the columnstore database is determined based on the initial query key.

[0075] In this embodiment of the application, by merging relational database tables with related relationships into a single column family, data fusion can be further reduced, thereby improving query efficiency and reducing storage costs.

[0076] In an exemplary embodiment, step 202, determining the relational database tables with associated relationships in the relational database based on the initial query key, includes: determining multiple first query keys with the same key value and second query keys corresponding to multiple relational database tables in the initial query key; determining the relational database tables corresponding to the multiple first query keys as relational database tables with associated relationships, and determining the multiple relational database tables corresponding to the second query keys as relational database tables with associated relationships.

[0077] The table merging is performed using the query key of the target structured query statement. The specific rules are as follows: (1) Given the target structured query statement and , and They are respectively and Determined initial query key, and Based on relational database tables and ,like and If they are the same, then the relational database table will be... and Tables identified as having relationships in a relational database will be... and Merging into a column family of a columnstore database (2) Given the target structured query statement. ,like The determined initial query keys are based on relational database tables. and Then, the relational database table and Tables identified as having relationships in a relational database will be... and Merge them into the same column family. By merging more related tables into a single column family, data redundancy can be further reduced, thereby improving query performance and reducing storage costs.

[0078] In an exemplary embodiment, step 204, determining the row key based on the initial query key, includes: for the primary key in the initial query key, determining the initial query key as the row key; for the non-primary key in the initial query key, combining the initial query key with the primary key of the relational database table corresponding to the initial query key to obtain the row key; for the primary key of the relational database table not used as the row key, adding a new column storage table based on the primary key, and using the primary key as the row key of the newly added column storage table.

[0079] During the process of determining row keys, if the initial query key is a primary key of the relational database table, it is used directly, i.e., the initial query key is determined as the row key; otherwise, the initial query key is combined with the primary key of the relational database table corresponding to the initial query key to form a unique key. To ensure that all relational database tables are migrated to the columnstore database, for primary keys of relational database tables that are not used as row keys, a new columnstore table is created based on the primary key, and that primary key is used as the row key.

[0080] In this embodiment, for the primary key in the initial query key, no additional operation is required; the initial query key can be directly determined as the row key, ensuring that the row key is always unique and distinguishable in the columnstore database, thus avoiding data conflicts. For non-primary keys in the initial query key, the initial query key is combined with the primary key of the corresponding relational database table to obtain the row key, giving the row key both query efficiency and uniqueness. For primary keys of relational database tables not used as row keys, a new columnstore table is created based on the primary key, and the primary key is used as the row key of the new columnstore table, ensuring that all relational database tables are migrated to the columnstore database.

[0081] In an exemplary embodiment, the method further includes: testing the relational database and the column-store database respectively based on the test data to obtain performance index data for the relational database and the column-store database; performing weighted summation on the performance index data of the relational database and the column-store database to obtain a performance evaluation score; and optimizing the row key and column family of the column-store database based on the performance evaluation score.

[0082] In some implementations, performance metrics and corresponding weights are designed before and after the column-store database conversion. For example, for relational databases and column-store databases, the performance metrics are as follows: throughput. Throughput weight ;Delay Delay weight IO utilization IO utilization weight CPU utilization CPU utilization weight Hardware costs (purchase and maintenance expenses for hardware equipment such as servers and storage) Operation and maintenance costs (including the costs of database maintenance, upgrades, monitoring, and other operations) Cost weight wait.

[0083] For relational databases and column-oriented databases undergoing long-term maintenance testing with the same test data, pay attention to the above performance metrics and derive the final performance evaluation score S according to the formula:

[0084]

[0085] The row keys and column families of the column-store database are fine-tuned by using performance evaluation scores to obtain an optimized storage structure for the column-store database.

[0086] In this embodiment, performance tests are conducted on both relational and column-oriented databases to obtain multi-dimensional performance metrics. These metrics are then weighted and summed to obtain a performance evaluation score, enabling a quantitative measurement of the column-oriented database's performance. Based on this performance evaluation score, the row keys and column families of the column-oriented database are optimized. This ensures that the row key structure matches the actual access path and the column family partitioning adapts to data access relevance, while maintaining row key uniqueness and column family semantic integrity. This significantly improves the query performance, write efficiency, and data distribution balance of the column-oriented database, achieving efficient migration and adaptive structural optimization from relational databases to column-oriented databases.

[0087] For example, suppose there is an e-commerce website (relational database) containing the following 8 tables, with a simplified table structure as follows:

[0088] Customers (customer table): Stores customer information (CustomerID, CustomerName, ...)

[0089] EmailAddress), which stores customer ID, customer name, and email address;

[0090] Orders (Order Table): Stores order information (OrderID, CustomerID, OrderDate, TotalAmount, ShipperID), namely, order number, customer ID, order date, total order amount, and shipping company ID;

[0091] Products (Product Table): Stores product information (ProductID, ProductName, CategoryID, Price), that is, it stores the product number, product name, category number, and price;

[0092] ProductCategories (Product Category Table): Stores product category information (CategoryID, CategoryName), that is, stores the category number and category name;

[0093] Suppliers (Supplier Table): Stores supplier information (SupplierID, SupplierName, Address), that is, it stores the supplier number, supplier name and address;

[0094] ProductSuppliers (Product Supply Relationship Table): Stores the association between products and suppliers (ProductID, SupplierID), that is, stores the product number and supplier number;

[0095] Shippers (Shipper Company Table): Stores shipping company information (ShipperID, ShipperName, ContactInformation), that is, it stores the shipping company number, shipping company name, and contact information;

[0096] OrderDetails: Stores detailed order information (OrderID, ProductID, Quantity), including the order number, product number, and quantity purchased.

[0097] The specific conversion process is as follows:

[0098] 1. The SQL statement to retrieve the read mode of a relational database, such as the one mentioned above, typically uses the following SQL statement:

[0099] Search for all customers' names and email addresses:

[0100] SELECT CustomerName,EmailAddress FROM Customers;

[0101] To retrieve the order number, order date, and total amount for all orders, provided the order date is after January 2023: `SELECT OrderID, OrderDate, TotalAmount FROM Orders WHERE OrderDate>'2023-01-01';`

[0102] To retrieve the name, category, and price of all products, where the price is greater than 100 yuan: `SELECT ProductName, CategoryName, Price FROM Products JOIN ProductCategories ON Products.CategoryID=ProductCategories.CategoryID WHERE Price>100;`

[0103] To retrieve the names and addresses of all suppliers, where the address contains "China": `SELECT SupplierName, Address FROM Suppliers WHERE Address LIKE '%China%';`

[0104] Query the order number, customer name, order date, and total amount for all orders, where the customer name is Alice: SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate,

[0105] Orders.TotalAmount FROM Orders JOIN Customers ON Orders.CustomerID=

[0106] Customers.CustomerID WHERE Customers.CustomerName='Alice';

[0107] To retrieve the order number, product name, quantity, and unit price for all orders, where the quantity is greater than 10: `SELECT Orders.OrderID, Products.ProductName, OrderDetails.Quantity, Products.Price FROM Orders JOIN OrderDetails ON Orders.OrderID=OrderDetails.OrderID JOIN Products ON OrderDetails.ProductID=Products.ProductID WHERE OrderDetails.Quantity`

[0108] >10;

[0109] To retrieve the names and contact information of all shipping companies, specifying the contact information as email, use the following command: SELECT ShipperName, ContactInformation FROM Shippers WHERE ContactInformation LIKE '%Email%';

[0110] Query all orders for order number, customer name, order date, total order amount, shipping company name, shipping date, and the name, quantity, and unit price of all products, provided the order date is after January 2023: SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate,

[0111] Orders.TotalAmount,Shippers.ShipperName,Orders.ShippingDate,Products.

[0112] ProductName,OrderDetails.Quantity,Products.Price FROM Orders JOINCustomers ON Orders.CustomerID=Customers.CustomerID JOIN Shippers ON Orders.

[0113] ShipperID=Shippers.ShipperID JOIN OrderDetails ON Orders.OrderID=

[0114] OrderDetails.OrderID JOIN Products ON OrderDetails.ProductID=Products.

[0115] ProductID WHERE Orders.OrderDate>'2023-01-01';

[0116] To retrieve the names, supplier names, and prices of all products, where the supplier name is 'XYZ': `SELECT Products.ProductName, Suppliers.SupplierName, Products.Price FROM Products JOIN ProductSuppliers ON Products.ProductID=ProductSuppliers.ProductID JOINS supplier ON ProductSuppliers.SupplierID=Suppliers.SupplierID WHERES supplier.SupplierName='XYZ';`

[0117] To retrieve the order number, customer name, order date, total order amount, shipping company name, shipping date, and the name, quantity, and unit price of all products for all orders, provided the total order amount is greater than 1000 yuan: `SELECT Orders.OrderID,Customers.CustomerName,Orders.OrderDate,Orders.TotalAmount,Shippers.ShipperName,Orders.ShippingDate,Products.ProductName,OrderDetails.Quantity,Products.Price FROM Orders JOIN Customers ON Orders.CustomerID=Customers.CustomerID JOIN Shippers ON Orders.ShipperID=Shippers.ShipperID JOIN OrderDetails ON Orders.OrderID=OrderDetails.OrderID JOIN Products ON OrderDetails.ProductID`

[0118] =Products.ProductID WHERE Orders.TotalAmount>1000.

[0119] 2. Determine the initial query key based on the target structured query statement:

[0120] Customers:CustomerName (used to search for customers whose names begin with "A")

[0121] Orders:OrderDate (used to query orders with an order date after January 2023)

[0122] Products:Price (used to search for products priced above 100 yuan)

[0123] Suppliers:Address (used to search for suppliers whose addresses contain "China")

[0124] Shippers: ContactInformation (used to find shipping companies whose contact information is "Email")

[0125] OrderDetails:Quantity (used to query order details with a quantity greater than 10)

[0126] 3. Based on the initial query key and the target structured query statement, merge relational database tables with relationships into the same column family to improve query efficiency:

[0127] Customers: CustomerName (stores customer name), EmailAddress (stores email address)

[0128] Orders: OrderID (stores the order number), CustomerName (stores the customer's name), OrderDate (stores the order date), TotalAmount (stores the total order amount), ShipperName (stores the shipping company name), ShippingDate (stores the shipping date).

[0129] Products: ProductName (Storage Product Name), CategoryName (Storage Category Name), Price (Storage Price)

[0130] Suppliers: SupplierName (stores the supplier name), Address (stores the address)

[0131] Shippers: ShipperName (Storage and shipping company name), ContactInformation (Storage contact information)

[0132] OrderDetails: OrderID (stored order number), ProductName (stored product name), Quantity (stored quantity), Price (stored unit price)

[0133] 4. Based on the initial query key, the following row keys can be determined:

[0134] Customers:CustomerID

[0135] Orders:OrderID

[0136] Products:ProductID

[0137] Suppliers: SupplierID

[0138] Shippers:ShipperID

[0139] OrderDetails:OrderID+ProductID

[0140] 5. The final design yields the following database table structure (storage structure of a column-oriented database): Customers:

[0141] Rowkey:CustomerID

[0142] ColumnFamilies:CustomerName,EmailAddress

[0143] Orders:

[0144] Rowkey:OrderID

[0145] ColumnFamilies:CustomerName,OrderDate,TotalAmount,ShipperName,ShippingDate

[0146] Products:

[0147] Rowkey:ProductID

[0148] ColumnFamilies:ProductName,CategoryName,Price

[0149] Suppliers:

[0150] Rowkey:SupplierID

[0151] ColumnFamilies:SupplierName,Address

[0152] Shippers:

[0153] Rowkey:ShipperID

[0154] ColumnFamilies:ShipperName,ContactInformation

[0155] OrderDetails:

[0156] Rowkey: OrderID + ProductID

[0157] ColumnFamilies:ProductName,Quantity,Price

[0158] 6. Use open-source tools to migrate data, perform performance testing on the converted columnstore database, test performance indicators such as throughput and CPU utilization, calculate performance evaluation scores, and adjust table structure design.

[0159] Through the above conversion process, the table structure in the relational database is converted into the table structure in the column-oriented database, and appropriate column families and row keys are designed to improve query efficiency and reduce data redundancy.

[0160] In one exemplary embodiment, such as Figure 3 As shown, a data migration method is provided, including the following steps:

[0161] Obtain the structured query statement with read pattern from the query log of the relational database, and identify the structured query statement with read pattern as the target structured query statement.

[0162] For empty condition fields, the primary key of the database table corresponding to the target structured query statement is used as the initial query key; for condition fields that include reference fields, the reference fields are used as the initial query key.

[0163] In the initial query key, identify multiple first query keys with the same key value and corresponding second query keys for multiple relational database tables; determine the relational database tables corresponding to the multiple first query keys as relational database tables with relationships, and determine the multiple relational database tables corresponding to the second query keys as relational database tables with relationships; for the primary key in the initial query key, determine the initial query key as the row key; for the non-primary key in the initial query key, combine the initial query key with the primary key of the relational database table corresponding to the initial query key to obtain the row key; for the primary key of the relational database table not used as the row key, add a new column store table based on the primary key, and use the primary key as the row key of the new column store table.

[0164] Generate the storage structure of a column-store database based on column families and row keys.

[0165] Migrate data from a relational database to a column-oriented database using the same storage structure.

[0166] Based on the test data, relational databases and column-oriented databases were tested separately to obtain performance index data for the relational databases and column-oriented databases. The performance index data of the relational databases and column-oriented databases were weighted and summed to obtain performance evaluation scores. Based on the performance evaluation scores, the row keys and column families of the column-oriented databases were optimized.

[0167] In this embodiment, the target structured query statement of the relational database is obtained, and the initial query key is determined based on the target structured query statement. Since the initial query key describes the actual access pattern of the application, the column family and row key are determined based on the initial query key and the target structured query statement. This aligns the column family design with the actual access pattern, avoids reading and writing irrelevant fields, reduces data redundancy, and improves data query efficiency. Determining the row key based on the initial query key ensures that the data distribution is consistent with the query path and also guarantees data uniqueness. The storage structure of the column-store database is generated based on the column family and row key. Data in the relational database is migrated to the column-store database according to the storage structure of the column-store database. This ensures that the storage structure of the column-store database is highly matched with the actual access pattern, thereby reducing data redundancy, improving query efficiency, improving migration efficiency, and ensuring consistency during the data migration process. It also solves the problems of insufficient decision support and schema evaluation tools and the lack of standard data migration guidelines in traditional migration processes.

[0168] It should be understood that although the steps in the flowcharts of the embodiments described above are shown sequentially according to the arrows, these steps are not necessarily executed in the order indicated by the arrows. Unless explicitly stated herein, there is no strict order restriction on the execution of these steps, and they can be executed in other orders. Moreover, at least some steps in the flowcharts of the embodiments described above may include multiple steps or multiple stages. These steps or stages are not necessarily completed at the same time, but can be executed at different times. The execution order of these steps or stages is not necessarily sequential, but can be performed alternately or in turn with other steps or at least some of the steps or stages in other steps. It is understood that the steps in different embodiments can be freely combined as needed, and all non-contradictory solutions formed by such combinations are within the scope of protection of this application.

[0169] Based on the same inventive concept, this application also provides a data migration apparatus for implementing the data migration method described above. The solution provided by this apparatus is similar to the implementation scheme described in the above method; therefore, the specific limitations in one or more data migration apparatus embodiments provided below can be found in the limitations of the data migration method described above, and will not be repeated here.

[0170] In one exemplary embodiment, such as Figure 3 As shown, a data migration device is provided, including: a query key determination module 302, a structure generation module 304, and a data migration module 306, wherein:

[0171] The query key determination module 302 is used to obtain the target structured query statement of the relational database and determine the initial query key based on the target structured query statement.

[0172] The structure generation module 304 is used to determine the column family and row key based on the initial query key and the target structured query statement, and to generate the storage structure of the column storage database based on the column family and row key.

[0173] The data migration module 306 is used to migrate data from a relational database to a column-store database according to the storage structure of a column-store database.

[0174] In an exemplary embodiment, the structure generation module 304 is further configured to determine, based on the initial query key, a relational database table and an independent relational database table that have an association relationship in the relational database; merge the relational database tables that have an association relationship into the same column family according to the target structured query statement; and generate an independent column family based on the independent relational database table; and determine the row key based on the initial query key.

[0175] In an exemplary embodiment, the structure generation module 304 is further configured to determine multiple first query keys with the same key value and multiple second query keys corresponding to multiple relational database tables in the initial query keys; determine the relational database tables corresponding to the multiple first query keys as relational database tables with associated relationships; and determine the multiple relational database tables corresponding to the second query keys as relational database tables with associated relationships.

[0176] In an exemplary embodiment, the structure generation module 304 is further configured to: determine the initial query key as a row key for the primary key in the initial query key; combine the initial query key with the primary key of the relational database table corresponding to the initial query key to obtain a row key for the non-primary key in the initial query key; and add a new column storage table based on the primary key for the relational database table that is not used as a row key, and use the primary key as the row key of the newly added column storage table.

[0177] In an exemplary embodiment, the query key determination module 302 is further configured to obtain a structured query statement with a read mode from the query log of the relational database, determine the structured query statement with the read mode as the target structured query statement, and determine the initial query key based on the condition fields in the target structured query statement.

[0178] In an exemplary embodiment, the query key determination module 302 is further configured to, for an empty condition field, determine the primary key of the database table corresponding to the target structured query statement as the initial query key; and for a condition field including a reference field, determine the reference field as the initial query key.

[0179] In one exemplary embodiment, the above-described apparatus further includes:

[0180] The database optimization module is used to test relational databases and column-store databases separately based on test data to obtain performance index data for the relational databases and column-store databases; to perform weighted summation of the performance index data of relational databases and column-store databases to obtain performance evaluation scores; and to optimize the row keys and column families of column-store databases based on the performance evaluation scores.

[0181] Each module in the aforementioned data migration device can be implemented entirely or partially through software, hardware, or a combination thereof. These modules can be embedded in the processor of a computer device in hardware form or independent of it, or stored in the memory of the computer device in software form, so that the processor can call and execute the operations corresponding to each module.

[0182] In one exemplary embodiment, a computer device is provided, which may be a terminal, and its internal structure diagram may be as follows: Figure 4 As shown, the computer device includes a processor, memory, input / output interfaces, a communication interface, a display unit, and an input device. The processor, memory, and input / output interfaces are connected via a system bus, and the communication interface, display unit, and input device are also connected to the system bus via the input / output interfaces. The processor provides computing and control capabilities. The memory includes non-volatile storage media and internal memory. The non-volatile storage media stores the operating system and computer programs. The internal memory provides an environment for the operation of the operating system and computer programs stored in the non-volatile storage media. The input / output interfaces are used for exchanging information between the processor and external devices. The communication interface is used for wired or wireless communication with external terminals; wireless communication can be achieved through Wi-Fi, mobile cellular networks, Near Field Communication (NFC), or other technologies. When the computer program is executed by the processor, it implements a data migration method. The display unit is used to form a visually visible image and can be a display screen, a projection device, or a virtual reality imaging device. The display screen can be an LCD screen or an e-ink screen. The input device of the computer device can be a touch layer covering the display screen, or buttons, trackballs, or touchpads set on the casing of the computer device, or external keyboards, touchpads, or mice, etc.

[0183] Those skilled in the art will understand that Figure 4 The structure shown is merely a block diagram of a portion of the structure related to the present application and does not constitute a limitation on the computer device to which the present application is applied. Specific computer devices may include more or fewer components than those shown in the figure, or combine certain components, or have different component arrangements.

[0184] In one embodiment, a computer device is provided, including a memory and a processor, wherein the memory stores a computer program, and the processor executes the computer program to implement the steps in the above-described method embodiments.

[0185] In one embodiment, a computer-readable storage medium is provided having a computer program stored thereon, which, when executed by a processor, implements the steps in the above method embodiments.

[0186] In one embodiment, a computer program product is provided, including a computer program that, when executed by a processor, implements the steps in the above method embodiments.

[0187] It should be noted that the user information (including but not limited to user device information, user personal information, etc.) and data (including but not limited to data used for analysis, data stored, data displayed, etc.) involved in this application are all information and data authorized by the user or fully authorized by all parties, and the collection, use and processing of the relevant data must comply with relevant regulations.

[0188] Those skilled in the art will understand that all or part of the processes in the methods of the above embodiments can be implemented by a computer program instructing related hardware. The computer program can be stored in a non-volatile computer-readable storage medium, and when executed, it can include the processes of the embodiments of the above methods. Any references to memory, databases, or other media used in the embodiments provided in this application can include at least one of non-volatile memory and volatile memory. Non-volatile memory can include read-only memory (ROM), magnetic tape, floppy disk, flash memory, optical memory, high-density embedded non-volatile memory, resistive random access memory (ReRAM), magnetic random access memory (MRAM), ferroelectric random access memory (FRAM), phase change memory (PCM), graphene memory, etc. Volatile memory can include random access memory (RAM) or external cache memory, etc. By way of illustration and not limitation, RAM can take many forms, such as Static Random Access Memory (SRAM) or Dynamic Random Access Memory (DRAM). The databases involved in the embodiments provided in this application may include at least one type of relational database and non-relational database. Non-relational databases may include, but are not limited to, blockchain-based distributed databases. The processors involved in the embodiments provided in this application may be general-purpose processors, central processing units, graphics processing units, digital signal processors, programmable logic devices, quantum computing-based data processing logic devices, artificial intelligence (AI) processors, etc., and are not limited to these.

[0189] The technical features of the above embodiments can be combined in any way. For the sake of brevity, not all possible combinations of the technical features in the above embodiments are described. However, as long as there is no contradiction in the combination of these technical features, they should be considered to be within the scope of this application.

[0190] The embodiments described above are merely illustrative of several implementation methods of this application, and while the descriptions are specific and detailed, they should not be construed as limiting the scope of this patent application. It should be noted that those skilled in the art can make various modifications and improvements without departing from the concept of this application, and these all fall within the protection scope of this application. Therefore, the protection scope of this application should be determined by the appended claims.< / condition> < / condition> < / condition> < / condition> < / list> < / list>

Claims

1. A data migration method, characterized in that, The method includes: Obtain the target structured query statement from the relational database, and determine the initial query key based on the target structured query statement; The column family and row key are determined based on the initial query key and the target structured query statement, and the storage structure of the column storage database is generated based on the column family and the row key; The data in the relational database is migrated to the column-store database according to the storage structure of the column-store database.

2. The method according to claim 1, characterized in that, The step of determining the column family and row key based on the initial query key and the target structured query statement includes: Based on the initial query key, determine the relational database tables and independent relational database tables with related relationships in the relational database; merge the relational database tables with related relationships into the same column family according to the target structured query statement; and generate independent column families based on the independent relational database tables. The row key is determined based on the initial query key.

3. The method according to claim 2, characterized in that, The step of determining the relational database tables with existing relationships in the relational database based on the initial query key includes: In the initial query key, determine multiple first query keys with the same key value and second query keys corresponding to multiple relational database tables; The relational database tables corresponding to the first query keys are identified as the relational database tables with associated relationships, and the relational database tables corresponding to the second query keys are identified as the relational database tables with associated relationships.

4. The method according to claim 2, characterized in that, Determining the row key based on the initial query key includes: For the primary key in the initial query key, the initial query key is determined as the row key; For non-primary keys in the initial query key, the initial query key is combined with the primary key of the relational database table corresponding to the initial query key to obtain the row key; For primary keys in relational database tables that are not used as row keys, a new column store table is created based on the primary key, and the primary key is used as the row key of the new column store table.

5. The method according to claim 1, characterized in that, The process of obtaining the target structured query statement from the relational database and determining the initial query key based on the target structured query statement includes: From the query log of the relational database, obtain the structured query statement of the read mode, and determine the structured query statement of the read mode as the target structured query statement; The initial query key is determined based on the condition fields in the target structured query statement.

6. The method according to claim 5, characterized in that, Determining the initial query key based on the condition fields in the target structured query statement includes: For empty condition fields, the primary key of the database table corresponding to the target structured query statement is determined as the initial query key; For condition fields that include a reference field, the reference field is determined as the initial query key.

7. The method according to any one of claims 1 to 6, characterized in that, The method further includes: The relational database and the column-store database were tested based on the test data to obtain the performance index data of the relational database and the column-store database respectively. The performance metrics data of the relational database and the column-store database are weighted and summed to obtain a performance evaluation score. The row keys and column families of the columnstore database are optimized based on the performance evaluation scores.

8. A data migration device, characterized in that, The device includes: The query key determination module is used to obtain the target structured query statement of the relational database and determine the initial query key based on the target structured query statement. The structure generation module is used to determine the column family and row key based on the initial query key and the target structured query statement, and to generate the storage structure of the column storage database based on the column family and the row key; The data migration module is used to migrate data from the relational database to the column-store database according to the storage structure of the column-store database.

9. A computer device comprising a memory and a processor, wherein the memory stores a computer program, characterized in that, When the processor executes the computer program, it implements the steps of the method according to any one of claims 1 to 7.

10. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the computer program is executed by a processor, it implements the steps of the method according to any one of claims 1 to 7.

Images