Exporting method for super-large-scale data
Through the BINLOG log analysis tool and distributed query technology of TIDB cluster, the problem of low timeliness and consistency of large-scale data export in the existing technology is solved, and fast and stable large-scale data export is achieved, improving customer experience and business stability is achieved. sex.
Patent Information
- Application Number
- CN202411042091.5
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2024-07-31
- Publication Date
- 2024-11-15
AI Technical Summary
When exporting large-scale data, existing technology has problems such as low export timeliness, slow performance, inability to support real-time data export, data consistency problems, and memory overflow, which is difficult to meet the rapid export needs of large companies for 10 billion data, affecting customers. Experience and online business stability.
Configure the replication channel through the BINLOG log analysis tool, write the historical data of the source database to the terminal database, and use the search engine or task center to submit the SQL module for data export, and combine the multi-node distributed query and column storage nodes of the TIDB cluster for concurrency Scan for quick export and data consistency.
It realizes the rapid export of large-scale data without affecting the normal operation of the business, ensuring data consistency and performance, avoiding memory overflow problems, and improving customer experience and export efficiency.
Smart Images

Figure CN118964459A_ABST
Abstract
Description
Technical Field
[0001] The present invention relates to the technical field of software services, and in particular to a method for exporting ultra-large-scale data. Background Art
[0002] The existing data export methods in the SaaS and enterprise service industries are basically pre-generated, and are exported using offline platforms, or can only export small batches of tens of thousands of data. The general solutions in the industry have the following disadvantages: using offline platforms to export, because the technology systems used by offline platforms such as Hadoop and Java developers are different, this type of development requires the involvement of big data developers, and the overall table structure is designed by Java developers. Compared with big data developers, Java developers are more familiar with their own business. The current solution cannot support Java developers in various business lines to quickly iterate functions; the export timeliness is low, the performance is slow, and it often takes dozens of minutes to export. In addition, since the offline computing platform and business data cannot be synchronized in real time, it is basically impossible to export real-time data, and only offline data can be exported, which leads to poor customer experience; for scenarios with a large volume of more than 10 billion data, it is impossible to solve the problem of large-scale data query and export consistency.
[0003] In addition, due to the inconsistency between the word segmentation strategy of the search engine elasticsearch and the offline query strategy, there is a problem of inconsistency between the exported and search result data. For small companies, they may directly develop and export in the business database Mysql; while large companies cannot quickly export businesses with data volumes exceeding tens of billions or hundreds of billions, and often encounter problems related to memory overflow. Traditional relational databases often have memory overflows for slightly complex queries, or the execution time exceeds ten hours, and often face export failures. Since the database is shared with online businesses, it also poses a hidden danger to the stability of the online business system. Summary of the invention
[0004] The purpose of the present invention is to solve the shortcomings of the prior art and provide a method for exporting ultra-large-scale data, comprising the following steps:
[0005] S1: Use the BINLOG log parsing tool to quickly configure the source database and configure the replication channel;
[0006] S2: writing the historical data of the source database into the terminal database through a search engine or submitting an SQL module through a task center, and the historical data is automatically collected into the terminal database through the BINLOG log parsing tool;
[0007] S3: After the background monitoring detects that the historical data is written into the terminal database, the background calls the SQL execution module to export the data into a file. The file is uploaded to the object storage service, so that the client can download the file according to the object storage service.
[0008] Preferably, in step S1, the rapid configuration further comprises:
[0009] Fill in the source database information including the source database IP address, source database account, and source database password of the source database, as well as the terminal database information including the terminal database address, terminal database account, and terminal database password of the terminal database through the platform management interface, and fill in the source database information and the terminal database information into the platform management console.
[0010] Preferably, in step S1, the BINLOG log parsing tool further comprises:
[0011] The BINLOG log parsing tool is linked to the source database through the configured source database address, the source database account and the source database password. The BINLOG log parsing tool captures the BINLOG events of the DML operation in real time, so as to ensure the consistency of the source database and the terminal database through the BINLOG log parsing tool.
[0012] Preferably, the ensuring the consistency of the source database and the terminal database further includes:
[0013] The update operation and the insert operation in the BINLOG event are converted into replace operations, the captured delete operation remains unchanged, the delete operation and the replace operation are copied to the terminal database to execute corresponding SQL statements, and the data content and table structure of the terminal database are the same as the data information of the source database.
[0014] Preferably, in step S2, the step of writing the historical data of the source database into the terminal database by submitting the SQL module through the task center further includes:
[0015] Submit the SQL module to the task center into the TIDB cluster, and the TIDB cluster writes the historical data into the terminal database according to the SQL execution module of the SQL module.
[0016] Preferably, in step S2, writing the historical data of the source database into the terminal database through a search engine further comprises:
[0017] The historical data is searched in the background, and after the export button is clicked, the search engine obtains the search data from the source database according to the search conditions, and writes the search data into the terminal database.
[0018] Preferably, the search engine further comprises
[0019] The search engine records the search condition and the primary key field corresponding to each piece of data in the search data, and the search engine batch searches the primary key field of the table data of the source database according to the search condition.
[0020] Preferably, the primary key field further includes:
[0021] The primary key field adopts the int type. Since the int type occupies a small memory space, the data volume for downloading ultra-large-scale data is also small. The primary key field is quickly written into the table of the terminal database in batches through search after.
[0022] Preferably, the TIDB cluster includes:
[0023] According to the multi-node distributed query of the TIDB cluster, the tikv node and column storage node of the TIDB cluster concurrently scan the primary key field to query the source database to obtain data corresponding to the search condition.
[0024] Preferably, the column storage node further comprises:
[0025] The column storage node provides column storage, and the column storage realizes fast return of column data. The TIDB cluster searches the data content according to the search conditions, returns the search data to the server level of the TIDB cluster, constructs a hash memory table to perform a hash join operation, and realizes fast return of the search data.
[0026] Compared with the prior art, the present invention has the following beneficial effects:
[0027] (1) The present invention uses the BIN10G log parsing platform tool to quickly configure the replication channel and replicate it to the tidb database in real time, supporting R&D personnel in each business line to quickly carry out development; and realizes large-scale data export for the B-side without affecting the normal operation of the business and user experience.
[0028] (2) The present invention searches through a search engine, records the primary key, and uses the primary key to query between tables. Even if queries are performed on data at the level of tens of billions, the execution plan will be very stable and will not have problems related to memory overflow, thereby ensuring the consistency of search and exported data for complex associated queries.
[0029] (3) The present invention gives full play to and utilizes the multi-node distributed query of the TIDB cluster. Each tikv node and column storage node concurrently scans the primary key to query and obtain the corresponding data. The tikv node also scans the data according to the primary key. The column storage node can quickly return the required columns because the data is stored in columnar format. All data is returned to the server level of tidb and constructed into a hash memory table for hash join operation. The data is returned quickly, which can ensure that millions of data can be quickly exported in the query of tens of billions of data.
[0030] (4) The present invention writes the historical data of the source database into the terminal database by submitting an SQL module through a search engine or through a task center, which can enable the back-end Java research and development to quickly carry out functional development, and can also realize real-time replication of data on heterogeneous platforms and complete the rapid export of real-time data. BRIEF DESCRIPTION OF THE DRAWINGS
[0031] Various other advantages and benefits will become apparent to those of ordinary skill in the art by reading the following detailed description of the preferred embodiment.The drawings are only for the purpose of illustrating the preferred embodiments and are not to be construed as limiting the invention.
[0032] Figure 1 A flowchart of a method for exporting ultra-large-scale data according to the present invention;
[0033] Figure 2 Another flow chart of a method for exporting ultra-large-scale data according to the present invention;
[0034] Figure 3 A configuration diagram of a method for exporting ultra-large-scale data according to the present invention;
[0035] Figure 4 A task center diagram of a method for exporting ultra-large-scale data according to the present invention;
[0036] Figure 5 A search engine diagram of a method for exporting ultra-large-scale data according to the present invention. DETAILED DESCRIPTION
[0037] In order to make the purpose, technical scheme and advantages of the present invention clearer, the present invention is further described in detail below in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are only used to explain the present invention and are not intended to limit the present invention. Obviously, the described embodiments are part of the embodiments of the present application, rather than all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in the field without making creative work are within the scope of protection of this application.
[0038] Those skilled in the art will appreciate that, unless otherwise stated, the singular forms "a", "an", and "the" used herein may also include plural forms. It should be further understood that the term "comprising" used in the specification of the present invention refers to the presence of the features, integers, steps, operations, elements and / or components, but does not exclude the presence or addition of one or more other features, integers, steps, operations, elements, components and / or groups thereof.
[0039] Example
[0040] See also Figure 1 and Figure 2 As shown, the present embodiment provides a method for exporting ultra-large-scale data. The log subscription of the business database MySQL binlog is used for standardized collection. The data in all online business databases MySQL are copied to the tidb cluster in real time to ensure that the data of the tidb cluster and each online business line are copied in real time. The table structure is the same as that of the business database MySQL, which can meet the requirements of rapid development in Java just like in the business database MySQL. At the same time, a table structure will be pre-generated in the export module in advance. When the customer clicks the export button, the primary key of the table searched by elasticsearch will be quickly recorded in the temporary table dw_plan_ord_entity_relation of tidb. By using the primary key association query and the column storage principle of tidb, the data exceeding one million required by the customer can be quickly exported at the minute level. The steps include:
[0041] S1: Use the BINLOG log parsing tool to quickly configure the source database and configure the replication channel. Specifically, in this embodiment, the source database includes the order database, member database, and product database. All table data of the online order database Mysql and member database Mysql are copied to the TIDB database in real time through the self-developed BINLOG log parsing platform tool;
[0042] S2: Write the historical data of the source database to the terminal database through the search engine or submit the SQL module through the task center. The historical data is automatically collected to the terminal database through the BINLOG log parsing tool;
[0043] S3: After the background monitoring completes writing of historical data into the terminal database, the background calls the SQL execution module to export the data to a file, and the file is uploaded to the object storage service, so that the client can download the data according to the object storage service. Specifically, in this embodiment, the export of various modules of the merchant is supported, and the data of more than 500 types of various business scenarios are exported.
[0044] Preferably, in step S1, the rapid configuration further comprises:
[0045] Fill in the source database information including the source database IP address, source database account, and source database password of the source database, and the terminal database information including the terminal database address, terminal database account, and terminal database password of the terminal database through the platform management interface, and fill in the source database information and terminal database information in the platform management console.
[0046] Preferably, in step S1, the BINLOG log parsing tool further comprises:
[0047] The BINLOG log parsing tool is linked to the source database through the configured source database address, source database account and source database password. The BINLOG log parsing tool captures the BINLOG events of DML operations in real time, and ensures the consistency of the source database and the terminal database through the BINLOG log parsing tool.
[0048] See also Figure 3 As shown, ensuring the consistency of the source database and the terminal database further includes:
[0049] The update operation and insert operation in the BINLOG event are converted into replace operations, the captured delete operation remains unchanged, and the delete operation and replace operation are copied to the terminal database to execute the corresponding SQL statements. The data content and table structure of the terminal database are the same as the data information of the source database. Specifically, in this embodiment, by simulating the Slave thread of Mysql, the BINLOG event of all DML operations is captured in real time, the update and insert operations in the BINLOG event are converted into replace operations, the captured delete operation remains unchanged, and it is copied to the target end to execute the corresponding SQL. This process is replayed to ensure the consistency of the source database and the target database.
[0050] Better yet, the order table data and member table data of the terminal database in the TIDB cluster will remain exactly the same as the online database in terms of table structure and data content. At this point, the overall member and order data have been completely separated from the business system, and even if tens of millions of data are exported, it will not affect the stability of the online business system.
[0051] See also Figure 4 As shown, in step S2, the historical data of the source database is written to the terminal database through the SQL module submitted by the task center, further comprising:
[0052] Submit the SQL module to the TIDB cluster in the task center. The TIDB cluster writes the historical data to the terminal database according to the SQL execution module of the SQL module. Specifically, in this embodiment, the back-end R&D students only need to submit the module SQL exported by the development order in the task center, and there is no need to learn the big data addition technology for development.
[0053] Preferably, in step S2, writing the historical data of the source database into the terminal database through the search engine further includes:
[0054] Search for historical data in the background. After clicking the Export button, the search engine obtains the search data from the source database based on the search conditions and writes the search data to the terminal database.
[0055] Preferably, the search engine further comprises
[0056] The search engine records the search conditions and the primary key field corresponding to each piece of data in the search data. The search engine batch searches the primary key field of the table data in the source database according to the search conditions.
[0057] Preferably, the primary key field further includes:
[0058] The primary key field uses the int type. Since the int type occupies a small amount of memory space, the data volume required to download ultra-large-scale data is also small. The primary key field is quickly written into the table of the terminal database in batches through search after.
[0059] See also Figure 5As shown, a merchant searches for historical orders from January 1 to January 31, 2024 in the background, and then clicks the Export button. After clicking the Export button, since the elasticsearch engine will record the primary key corresponding to each piece of data in addition to the required search conditions, the elasticsearch search engine will batch search for the unique primary key of the corresponding table data from January 1 to January 31 according to the customer's search conditions. Since the primary key field is of int type and is relatively small, even if millions of data are downloaded, the overall data volume is small. The primary key id is quickly written into the dw_plan_ord_entity_relation table in batches through search after. Usually, millions of data are completed in ten seconds, and then the query is performed in the associated specific business table. At this time, the problem of inconsistent search and export under large data volume can be solved. When the search results are written to the TIDB cluster, the background scheduling system monitors that the task writing is completed, and starts calling this SQL to execute the export output to csv.
[0060] Preferably, the TIDB cluster includes:
[0061] According to the multi-node distributed query of the source database of the TIDB cluster, the tikv node and column storage node of the TIDB cluster concurrently scan the primary key field to query the source database to obtain the data corresponding to the search criteria.
[0062] Preferably, the column storage node further comprises:
[0063] The column storage node provides column storage, which enables fast return of column data. The TIDB cluster searches for data content based on the search conditions, returns the search data to the server level of the TIDB cluster, constructs it into a hash memory table for hashjoin operations, and achieves fast return of search data.
[0064] At this time, since the overall SQL execution template has been solidified, the overall SQL execution plan will be very stable. At the same time, the elasticsearch search engine is fully utilized to record the primary key and use the primary key for query. Between the associations between tables, even if the query is performed in the tens of billions of data, the execution plan will be very stable and there will be no memory overflow related problems. At the same time, the multi-node distributed query of the TIDB cluster is fully utilized to ensure that the data export of millions of levels can be quickly returned in the tens of billions of data queries. Each tikv node and column storage node concurrently scans the primary key to query and obtain the corresponding data. For example, there are 16 tikv nodes. At this time, the 16 tikv nodes scan the data according to the primary key at the same time. The column storage node can quickly return the required columns because the data is stored in columns. All data is returned to the server level of tidb and constructed into a hash memory table for hash join operation. The data is returned quickly, which can ensure that the data export of millions of levels can be quickly returned in the tens of billions of data queries. At this time, the overall data has been fully executed, and the generated csv file is uploaded to OSS, which customers can download in the background.
[0065] The above is only a preferred embodiment of the present invention, and the protection scope of the present invention is not limited to the above embodiments. All technical solutions under the concept of the present invention belong to the protection scope of the present invention. It should be pointed out that for ordinary technicians in this technical field, some improvements and modifications without departing from the principle of the present invention should also be regarded as the protection scope of the present invention.
Claims
1. A method for exporting ultra-large-scale data, characterized in that: The following steps are involved: S1: Use the BINLOG log parsing tool to quickly configure the source database and configure the replication channel; S2: writing the historical data of the source database into the terminal database through a search engine or submitting an SQL module through a task center, and the historical data is automatically collected into the terminal database through the BINLOG log parsing tool; S3: After the background monitoring detects that the historical data is written into the terminal database, the background calls the SQL execution module to export the data into a file. The file is uploaded to the object storage service, so that the client can download the file according to the object storage service.
2. The method for exporting ultra-large-scale data according to claim 1, characterized in that: In step S1, the rapid configuration further includes: Fill in the source database information including the source database IP address, source database account, and source database password of the source database, as well as the terminal database information including the terminal database address, terminal database account, and terminal database password of the terminal database through the platform management interface, and fill in the source database information and the terminal database information into the platform management console.
3. The method for exporting ultra-large-scale data according to claim 2, characterized in that: In step S1, the BINLOG log parsing tool further includes: The BINLOG log parsing tool is linked to the source database through the configured source database address, the source database account and the source database password. The BINLOG log parsing tool captures the BINLOG events of the DML operation in real time, so as to ensure the consistency of the source database and the terminal database through the BINLOG log parsing tool.
4. The method for exporting ultra-large-scale data according to claim 3, characterized in that: The ensuring the consistency of the source database and the terminal database further includes: The update operation and the insert operation in the BINLOG event are converted into replace operations, the captured delete operation remains unchanged, the delete operation and the replace operation are copied to the terminal database to execute corresponding SQL statements, and the data content and table structure of the terminal database are the same as the data information of the source database.
5. The method for exporting ultra-large-scale data according to claim 4, characterized in that: In step S2, the step of submitting the SQL module through the task center to write the historical data of the source database into the terminal database further includes: Submit the SQL module to the task center into the TIDB cluster, and the TIDB cluster writes the historical data into the terminal database according to the SQL execution module of the SQL module.
6. The method for exporting ultra-large-scale data according to claim 5, characterized in that: In step S2, the historical data of the source database is written into the terminal database through a search engine, further comprising: The historical data is searched in the background, and after the export button is clicked, the search engine obtains the search data from the source database according to the search conditions, and writes the search data into the terminal database.
7. The method for exporting ultra-large-scale data according to claim 6, characterized in that: The search engine further comprises The search engine records the search condition and the primary key field corresponding to each piece of data in the search data, and the search engine batch searches the primary key field of the table data of the source database according to the search condition.
8. The method for exporting ultra-large-scale data according to claim 7, characterized in that: The primary key field further includes: The primary key field adopts the int type. Since the int type occupies a small memory space, the data volume for downloading ultra-large-scale data is also small. The primary key field is quickly written into the table of the terminal database in batches through search after.
9. The method for exporting ultra-large-scale data according to claim 8, characterized in that: The TIDB cluster includes: According to the multi-node distributed query of the TIDB cluster, the tikv node and column storage node of the TIDB cluster concurrently scan the primary key field to query the source database to obtain data corresponding to the search condition.
10. The method for exporting ultra-large-scale data according to claim 9, characterized in that: The column storage node further comprises: The column storage node provides column storage, and the column storage realizes fast return of column data. The TIDB cluster searches the data content according to the search conditions, returns the search data to the server level of the TIDB cluster, constructs a hash memory table to perform a hash join operation, and realizes fast return of the search data.
