A data extraction method and a distributed computing system

Abstract

The present application relates to a kind of data extraction method and distributed computing system, the present application is handled by using the parallel computing capacity of distributed computing framework, cluster computing resources, task error retry capability to process database table split data extraction task.According to the database table split rule of logical table, the database configuration information of extraction physical table and the rule of extracting table data are generated, according to the rule of generating subtask of distributed computing framework, ensure that each subtask extracts several physical tables, in the subtask program, according to the database configuration information and data extraction rule read, complete the extraction task of single physical table, distributed computing framework ensures the successful execution of each physical table extraction task, distributed task is successfully executed, otherwise delete the data extracted, distributed task is declared failure, ensure the data integrity after extraction task is completed.

Description

Technical Field

[0001] This invention relates to a data extraction method and distributed computing system for big data, belonging to the field of database data extraction technology, and particularly to a method and computer system for data extraction using computer technology. Background Technology

[0002] With the development of the internet, digital transformation, and computer technology, the demands on databases for data volume and execution efficiency are increasing. Single-database, single-table data processing is far from sufficient to meet system requirements. Many software systems employ database sharding to improve performance, splitting a single data table into multiple physical tables (typically more than 1000 tables) and storing them across multiple databases to distribute database load and improve system performance. However, in scenarios requiring data analysis such as user behavior analysis and business growth statistics, it is necessary to extract and aggregate data from sharded databases and tables into a distributed file system or BI database for centralized data analysis. Because database sharding results in numerous source system databases and tables, the extraction process becomes complex, difficult to maintain, and the completeness of the extraction results is hard to guarantee.

[0003] Currently, the data extraction methods in the industry are all single-database, single-table extraction methods, such as using database tools like Sqoop, DataX, and Embulk to maintain thousands of tasks processing a single data table. This results in low development and maintenance efficiency, low concurrency processing capability, and the failure of a certain task leads to the failure of the corresponding data extraction, making it difficult to guarantee the integrity of the extracted data.

[0004] Patent document CN108984549A discloses a method for extracting data from sharded databases and tables. Essentially, this method still relies on extracting data from one physical table per task. This method suffers from issues such as a large number of tasks, insufficient server computing resources for extraction tasks, and the failure of some tasks leading to corresponding data extraction failures, making it difficult to guarantee the integrity and concurrency of data extraction. Summary of the Invention

[0005] The purpose of this invention is to provide a data extraction method and a distributed computing system to solve the problems of insufficient data extraction integrity and concurrency in the existing technology when extracting data from sharded databases and tables.

[0006] To achieve the above objectives, the present invention includes:

[0007] The present invention provides a method for extracting data from sharded databases and tables, which involves building a distributed computing system including at least one master node and several slave nodes. The master node generates database configuration information for each physical table based on the sharding rules of the logical tables, and also generates data extraction rules for each physical table based on the data extraction rules of the logical tables. The master node also stores the database configuration information and data extraction rules of each physical table as an extraction record.

[0008] Each slave node runs several subtasks. Each subtask reads at least one extraction record, creates a database connection with the corresponding database based on the database configuration information of the physical table corresponding to the extraction record, and extracts data from the corresponding physical table through the corresponding database connection according to the data extraction rules of the physical table corresponding to the extraction record.

[0009] When processing data from sharded databases and tables, this invention employs a distributed computing framework to extract the data, reducing the complexity of the extraction process, significantly improving the concurrency and efficiency of data extraction, and effectively utilizing the error handling mechanism of the distributed computing framework for node execution of subtasks to ensure the integrity of the extracted data.

[0010] Specifically, a task within a distributed computing framework (e.g., a MapReduce task) extracts a logical table. This invention utilizes the parallel computing capabilities, cluster computing resources, and task retry capabilities of the distributed computing framework (the task is declared successful only if all subtasks succeed; otherwise, it is declared a failure) to handle the data extraction task from sharded databases and tables. Based on the sharding rules of the logical table, database configuration information for extracting physical tables and rules for extracting table data (e.g., SQL, Structured Query Language) are generated. Following the rules for generating subtasks (Tasks) by the distributed computing framework, each subtask extracts several physical tables, ensuring that each physical table has a corresponding subtask for processing. Within each subtask program, the extraction task for a single physical table is completed according to the read database configuration information and data extraction rules. The distributed computing framework ensures that the distributed task only succeeds if the extraction task for each physical table is successfully executed; otherwise, the extracted data is deleted, and the distributed task is declared a failure. This ensures the data integrity of the extracted data after the extraction task is completed.

[0011] Furthermore, the extracted records are evenly distributed among the subtasks.

[0012] The physical tables to be extracted are evenly distributed across each subtask to ensure load balancing of the distributed computing framework and improve data processing (extraction) efficiency.

[0013] Furthermore, the number of extraction records is the same as the number of subtasks, with each subtask reading one extraction record.

[0014] As the best implementation, the number of subtasks in the distributed computing architecture is set according to the number of physical tables in the sharded database, or the number of shards is based on the computing resources, i.e., the number of subtasks in the distributed computing architecture. This ensures that each subtask extracts data from only one physical table, improving the execution efficiency of the processing tasks and maximizing the utilization of computing resources.

[0015] Furthermore, the number of retries for each slave node's subtask that extracts data from the corresponding physical table is the same.

[0016] Set the same number of retries for each subtask to ensure the success rate of distributed tasks while also taking into account the load balancing of the distributed computing system.

[0017] The present invention discloses a distributed computing system comprising at least one master node and several slave nodes; the master node generates database configuration information for each physical table according to the database sharding and partitioning rules of the logical table, and also generates data extraction rules for each physical table according to the data extraction rules of the logical table; the master node also stores the database configuration information and data extraction rules of each physical table as an extraction record;

[0018] Each slave node runs several subtasks. Each subtask reads at least one extraction record, creates a database connection with the corresponding database based on the database configuration information of the physical table corresponding to the extraction record, and extracts data from the corresponding physical table through the corresponding database connection according to the data extraction rules of the physical table corresponding to the extraction record.

[0019] Furthermore, the extracted records are evenly distributed among the subtasks.

[0020] Furthermore, the number of extraction records is the same as the number of subtasks, with each subtask reading one extraction record.

[0021] Furthermore, the number of retries for each slave node's subtask that extracts data from the corresponding physical table is the same.

[0022] The present invention provides a data extraction method, which establishes a distributed computing system including at least one master node and several slave nodes; the master node stores the configuration information of the database containing the physical table of the data to be extracted and the corresponding data extraction rules as an extraction record; the extraction rules define the range of data to be extracted from the physical table of the corresponding slave node.

[0023] Each slave node runs several subtasks. Each subtask reads at least one extraction record, creates a database connection to the corresponding database based on the database configuration information of the extraction record, and extracts data from the corresponding physical table through the corresponding database connection according to the data extraction rules of the extraction record.

[0024] Furthermore, the data extraction rules include: extracting different types of data from different slave nodes; or, extracting data from different ID ranges from different slave nodes.

[0025] The method of this invention is used to extract data from a single large physical table. The task of extracting a large physical table can be divided into multiple smaller tasks and distributed to a distributed framework by configuring rules. For example, to extract data from a user table with 10 billion records, extraction rules can be configured based on the users' different countries. Each smaller task can extract users from only one country, or a sub-task can extract a certain range of data based on an ID range. This achieves the purpose of task splitting, improving the concurrency and success rate of data extraction, or utilizing cluster resources. Attached Figure Description

[0026] Figure 1 This is a schematic diagram of the architecture of a distributed computing system that applies a data extraction method of the present invention. Detailed Implementation

[0027] The present invention will now be described in further detail with reference to the accompanying drawings.

[0028] Data extraction method example 1:

[0029] The data extraction method in this embodiment is for data extraction from sharded databases and tables. A single database and single table means there is only one physical database and one physical table. Applications access data by directly connecting to the database. When dealing with large amounts of data, a single database cannot meet performance requirements. Therefore, existing technologies use database sharding and table partitioning to solve this problem. Database sharding and table partitioning divides a single data table into multiple parts and places them on different tables in different databases (servers), thereby alleviating the performance problems of a single data table.

[0030] A physical table is a table that stores data in a specific database, such as tables in databases like MySQL, Oracle, DB2, Microsoft SQL Server, Sybase, Aurora, PostgreSQL, MariaDB, MongoDB, Greenplum, Phoenix, and HBase.

[0031] Logical tables are a product of database sharding and table partitioning. A logical table is a virtual table composed of multiple physical tables. These physical tables may originate from multiple tables within the same database or from multiple tables in different databases. These physical tables have similar table structures (fields and field types are the same or similar). Configurable rules can identify which physical tables a logical table contains. In other words, logical tables record the sharding rules, or database sharding rules, used when splitting a large physical table into multiple smaller physical tables.

[0032] Building such Figure 1 The distributed computing system shown includes a master node (Driver node) and several (N) slave nodes. A distributed computing system (also known as a distributed computing framework) divides a large computing task into multiple smaller computing tasks, assigns each smaller task to each computing node in the cluster, continuously tracks the progress of each computing node to determine whether to re-execute the task, and finally collects and outputs the computing results from each node. Common distributed system infrastructures include Hadoop MapReduce, Apache Spark, Apache Storm, Apache Flink, and Apache Tez. Distributed computing frameworks are mature existing technologies; for details, please refer to the published Chinese patent text CN103562868A. This embodiment will not elaborate further.

[0033] In a distributed computing framework, the master node runs the main function (Master Main). The main function reads the logical table configuration from the sharded database and sharded tables, generates a single physical table configuration, and distributes it to subtasks (Tasks) for execution by the slave nodes.

[0034] Specifically, in the master node, the sharding and data extraction rules of the logical tables are read through the main function. This generates database configuration information (database address, account password, etc., used to establish database connections) and data extraction rules (data reading language and data range to be extracted, etc.) for each physical table. The configuration and extraction rules for each physical table are stored as an extraction record. M (M>=1, the purpose is to ensure that the amount of data extracted by a single subtask is not too large and is distributed as evenly as possible) extraction records are distributed in a partition on the master node or stored in a file (related to the rules for allocating subtasks in the distributed computing framework). This ensures that each subsequent subtask extracts data from only M physical tables. The output path of the entire distributed task is the storage path of the data to be extracted from the logical tables.

[0035] As another implementation, each subtask includes only one extracted record (i.e., M=1), meaning that each subtask is only responsible for extracting data from one physical table, maximizing the efficiency of the data extraction task (e.g., during error retries).

[0036] In a distributed computing framework, subtasks (Task 0, 1, 2, ...) are run from nodes (Slave 0 to N). The subtasks read the configuration and connect to the corresponding database to complete the data extraction task.

[0037] Specifically, it includes the following steps:

[0038] ① Read configuration: The subtask reads the database configuration information and data extraction rules used to connect to the database from several shards (Partition 0 to X) or files allocated by the master node.

[0039] ② Create a database connection: Use the database configuration read in ①: IP address, username, password, etc., and use JDBC, ODBC, or other database-related connection methods to create a database connection.

[0040] ③ Data extraction: Using the data extraction rules read in ①, such as SQL and which data to extract, perform data extraction through the database connection created in ②, and output the data of the extracted physical table (Result 0~N).

[0041] The distributed computing framework should have the following parameter settings:

[0042] ① The number of retries for tasks that extract a single table when an error occurs (the same as the number of retries for subtasks in a distributed computing framework), such as the parameter in the Hadoop MapReduce framework: mapreduce.map.maxattempts.

[0043] ② The number of physical tables to be extracted simultaneously. A higher number results in higher parallelism, faster extraction speed, and greater pressure on the database; a lower number results in lower parallelism and slower extraction. This parameter can be set reasonably, such as the parameter in the Hadoop MapReduce framework: mapreduce.job.running.map.limit.

[0044] To better understand this invention, an example from a specific scenario will be used below to illustrate it.

[0045] 1) Scene Description

[0046] An e-commerce platform has billions of users and uses MySQL to store user information (table name: user logical table). Because modifying and querying data becomes very slow when a single MySQL table contains millions of records, database sharding and table partitioning were adopted to solve the MySQL storage problem. User information is stored in 1024 tables across four databases (table names: user_0, user_1, up to user_1023) using the modulo operation on user IDs. The table and database configurations are as follows:

[0047] First database

[0048] IP: 1.0.0.0; Username: root; Password: 123456; Include tables: user_0, user_1…user_255;

[0049] Second database

[0050] IP: 1.0.0.1; Username: root; Password: 123456; Includes tables: user_256, user_257…user_511;

[0051] The third database

[0052] IP: 1.0.0.2; Username: root; Password: 123456; Includes tables: user_512, user_513…user_767;

[0053] The fourth database

[0054] IP: 1.0.0.3; Username: root; Password: 123456; Table names: user_768, user_769…user_1023.

[0055] There are various statistical needs, such as counting the number of registered users and the number of modified users in different time periods. Therefore, it is necessary to extract user data from various databases to HDFS (a distributed file storage) on the Hadoop cluster, with the storage path ( / user_data), for convenient subsequent statistics.

[0056] 2) Based on the above scenario description, the method of this invention is used to accomplish the following:

[0057] ①Main function

[0058] Based on the scenario description, the configuration rules for the logical table 'user' are as follows:

[0059] Database: i:0--3, IP:1.0.0.i; Username: root, Password: 123456; Table name: user_x, x = i*256--((i+1)*256-1), Extraction rule is to extract all records from each table: select*from user_x.

[0060] Based on the rules of the logical table above, generate the configuration for extracting records from each physical table:

[0061] Database IP: 1.0.0.0; Username: root; Password: 123456; Table name: user_0; Extraction rule: select * from user_0 for all records in each table;

[0062] Database IP: 1.0.0.0; Username: root; Password: 123456; Table name: user_1; Extraction rule: select * from user_1 for all records in each table;

[0063] ...

[0064] Database IP: 1.0.0.0; Username: root; Password: 123456; Table name: user_255; Extraction rule: select * from user_255 for all records in each table;

[0065] Database IP: 1.0.0.1; Username: root, Password: 123456; Table name: user_256; Extraction rule: select * from user_256 for all records in each table;

[0066] Database IP: 1.0.0.1; Username: root, Password: 123456; Table name: user_257; Extraction rule: select * from user_257 for all records in each table;

[0067] ...

[0068] Database IP: 1.0.0.3; Username: root, Password: 123456; Table name: user_1023; Extraction rule: All records from each table: select * from user_1023;

[0069] The configuration (i.e., record extraction) for each physical table is stored as a file in HDFS, for a total of 1024 files. The file path is / user_table_conf, and the file names are (file_0.txt, file_1.txt, ..., file_1023.txt).

[0070] ② The subtask reads the configuration, connects to the corresponding database, and completes the data extraction task.

[0071] A distributed framework randomly assigns one or more subtasks to a server:

[0072] For example, server1 is assigned Task0 to read the configuration file file_0.txt. Task0 reads the database, table name, and extraction rule from file_0.txt: Database IP: 1.0.0.0; Username: root, Password: 123456; Table name: user_0; Extraction rule: select * from user_0. Based on this configuration, server1 establishes a connection with MySQL via the MySQL driver's JDBC, completes the data extraction from the physical table user_0, and uses the extracted data records as the output of this subtask.

[0073] ③ The distributed computing framework Hadoop MapReduce organizes and manages the tasks in ① and ② above, and ensures that the main task succeeds only after all subtasks are completed. If any subtask fails after a retry, the main task fails.

[0074] The generated configuration file directory ( / user_table_conf) serves as the input directory for this MapReduce task, and the data target storage directory ( / user_data) serves as the output directory for the task.

[0075] The number of retries for a failed subtask is set to 3: mapreduce.map.maxattempts = 3;

[0076] Set the concurrency of subtasks to 100: mapreduce.job.running.map.limit=100.

[0077] Data extraction method example 2:

[0078] The data extraction method in this embodiment is a method for extracting data from a single large physical table.

[0079] When using a distributed computing framework to extract data from a large physical table, the task can be divided into multiple smaller tasks (i.e., sub-tasks) by configuring rules and assigning them to the distributed framework. Task splitting can divide the extraction of data from different ranges into different smaller tasks. For example, to extract data from a user table with 10 billion records, extraction rules can be configured based on the users' countries. Each sub-task can extract users from only one country, or based on an ID range, a sub-task can extract a certain range of data. This achieves the purpose of task splitting, improving the concurrency and success rate of data extraction, or utilizing cluster resources.

[0080] Examples of distributed computing systems:

[0081] The present invention provides a distributed computing system for extracting data from sharded databases or tables, or data from a large physical table. The extraction method has been clearly described in Data Extraction Method Embodiment 1 and Data Extraction Method Embodiment 2, and will not be repeated in this embodiment.

Claims

1. A method for extracting data from sharded databases and tables, characterized in that, Includes the following steps: 1) Build a distributed computing system that includes at least one master node and several slave nodes; 2) The master node generates database configuration information and data extraction rules for each physical table based on the sharding and data extraction rules of the logical table; it stores the database configuration information and data extraction rules of each physical table as an extraction record, and assigns multiple extraction records to each subtask. 3) Run several subtasks on each slave node. In response to the successful data extraction of all subtasks on all slave nodes, the successful extraction of data from the sharded database and tables is confirmed. When a subtask is extracting data, it extracts data based on all the extraction records assigned to that subtask. When extracting data from a specific extraction record of that subtask, it creates a database connection to the corresponding database based on the database configuration information of the physical table corresponding to that extraction record. Based on the data extraction rules of the physical table corresponding to that extraction record, it extracts data from the corresponding physical table through the corresponding database connection.

2. The data extraction method for database sharding and table partitioning according to claim 1, characterized in that, The extracted records are evenly distributed among the subtasks.

3. The data extraction method for database sharding and table partitioning according to claim 1, characterized in that, If any subtask fails to extract data, then the extraction of data from the sharded database and tables will also fail.

4. A distributed computing system, characterized in that, It includes at least one master node and several slave nodes; the master node generates database configuration information and data extraction rules for each physical table according to the database sharding and data extraction rules of the logical table; the master node also stores the database configuration information and data extraction rules of each physical table as an extraction record, and assigns multiple extraction records to each subtask; Several subtasks are run on each slave node. The successful extraction of data from all subtasks on all slave nodes is confirmed as a successful extraction of data from the sharded database and tables. Specifically, when a subtask is extracting data, it extracts data based on all the extraction records assigned to that subtask. When extracting data from a specific extraction record of that subtask, it creates a database connection to the corresponding database based on the database configuration information of the physical table corresponding to that extraction record. Then, it extracts data from the corresponding physical table through the corresponding database connection according to the data extraction rules of the physical table corresponding to that extraction record.

5. The distributed computing system according to claim 4, characterized in that, The extracted records are evenly distributed among the subtasks.

6. The distributed computing system according to claim 4, characterized in that, If any subtask fails to extract data, then the extraction of data from the sharded database and tables will also fail.

7. A data extraction method, characterized in that, A distributed computing system is constructed, comprising at least one master node and several slave nodes; the master node stores the configuration information of the database containing the physical table of the data to be extracted and the corresponding data extraction rules as an extraction record, and assigns multiple extraction records to each subtask; the extraction rules define the range of data to be extracted from the physical table of the corresponding slave node. Several subtasks are run on each slave node. The successful extraction of data from all subtasks on all slave nodes is confirmed as a successful extraction of data from the sharded database and tables. Specifically, when a subtask is extracting data, it extracts data based on all the extraction records assigned to that subtask. When extracting data from a specific extraction record of that subtask, it creates a database connection to the corresponding database based on the database configuration information of that extraction record, and extracts data from the corresponding physical table through the corresponding database connection according to the data extraction rules corresponding to that extraction record.

8. The data extraction method according to claim 7, characterized in that, The data extraction rules include: extracting different types of data from different slave nodes; or, extracting data from different ID ranges from different slave nodes.

Images