Batch warehousing method and device using distributed database, server and medium

Abstract

This application provides a method, apparatus, server, and medium for batch data import using a distributed database, relating to the field of data processing technology. The method employs a data loading executor to obtain task configuration information for batch data loading tasks targeting a target business database using a stream-batch integrated computing engine; the data loading executor generates a control file based on the task configuration information; the data loading executor pulls batch business data from the target business database based on the task configuration information and generates data files based on the batch business data; and the data loading executor executes preset data loading commands according to the control file to write the data files to the target distributed database. This saves resources and improves data import efficiency in batch task scenarios.

Description

Technical Field

[0001] This invention relates to the field of data processing technology, and more specifically, to a batch data entry method, apparatus, server, and medium using a distributed database. Background Technology

[0002] With the rapid development of the internet, massive amounts of data are being generated and collected. How to effectively process and mine this data has become an urgent problem to be solved.

[0003] In existing technologies, gpcopy is a data migration utility that can transfer data between different clusters, replicating metadata and data from heterogeneous databases to a Greenplum database. gpcopy can migrate the entire database, including the database schema, table data, indexes, views, roles, user-defined functions, resource queues, and resource groups.

[0004] Flink, as a data warehouse architecture that integrates stream and batch processing, uses the same SQL for both, thus improving development efficiency. However, Gpcopy is relatively inefficient in batch task scenarios due to its low execution efficiency for small data volumes during Flink real-time task execution. Summary of the Invention

[0005] The purpose of this invention is to address the shortcomings of the prior art by providing a batch database insertion method, apparatus, server, and medium using a distributed database, thereby solving the problem of relatively low efficiency in batch task scenarios in the prior art.

[0006] To achieve the above objectives, the technical solutions adopted in the embodiments of this application are as follows:

[0007] In a first aspect, embodiments of this application provide a batch database loading method using a distributed database, applied to a server integrated with a stream-batch computing engine, wherein the stream-batch computing engine pre-integrates a data loading executor for the target distributed database, and the method includes:

[0008] The data loading executor is used to obtain the task configuration information of the batch data loading task for the target business database by the integrated stream and batch computing engine;

[0009] The data loading executor is used to generate a control file based on the task configuration information;

[0010] Using the data loading executor, based on the task configuration information, a batch of business data is retrieved from the target business database, and a data file is generated based on the batch of business data;

[0011] The data loading executor is used to execute a preset data loading command according to the control file, so as to write the data file to the target distributed database.

[0012] Optionally, the step of using the data loading executor to generate a control file based on the task configuration information includes:

[0013] The data loading executor is used to parse the metadata of the task configuration information to obtain the database information of the target business database and the data table information of the target distributed database.

[0014] The control file is generated using the data loading executor based on the database information and the data table information.

[0015] Optionally, the step of using the data loading executor to pull batch business data from the target business database based on the task configuration information, and generating a data file based on the batch business data, includes:

[0016] Using the data loading executor, the batch business data is retrieved from the target business database based on the database information;

[0017] The data file is generated using the data loading executor based on the data table information and the batch business data.

[0018] Optionally, the step of using the data loading executor to execute a preset data loading command according to the control file to write the data file to the target distributed database includes:

[0019] After the preset data loading command is successfully started, the data loading executor starts a preset file disk service, which transfers the data file to the target distributed database. The target distributed database loads the data file as an external table through the preset file disk service and merges the data in the data file into the target distributed database.

[0020] Optionally, after the data loading executor starts a preset file disk service and transfers the data file to the target distributed database through the preset file disk service, the method further includes:

[0021] The data is loaded and executed, and the data file is deleted.

[0022] Optionally, the database information of the target business database includes: the identifier of the target database, the target port, and the target account;

[0023] The data table information of the target distributed database includes: the table name of the target data table, the fields of the target data table, and the primary key information.

[0024] Optionally, the step of using the data loading executor to execute a preset data loading command according to the control file to write the data file to the target distributed database includes:

[0025] The data loading executor is used to execute a preset data loading command according to the control file, so as to write the data file into the target data table.

[0026] Secondly, embodiments of this application provide a batch business data import device using a distributed database, applied to a server integrated with a stream-batch computing engine, wherein the stream-batch computing engine pre-integrates a data loading executor for the target distributed database, and the device includes:

[0027] The acquisition module is used to acquire the task configuration information of the batch data loading task of the stream-batch integrated computing engine for the target business database using the data loading executor;

[0028] The generation module is used to generate a control file based on the task configuration information using the data loading executor.

[0029] The pull module is used to pull batch business data from the target business database based on the task configuration information using the data loading executor, and generate a data file based on the batch business data;

[0030] The loading module is used to execute a preset data loading command according to the control file using the data loading executor, so as to write the data file to the target distributed database.

[0031] Thirdly, embodiments of this application provide a server, including: a processor and a storage medium, wherein the processor and the storage medium are connected via a bus for communication, and the storage medium stores program instructions executable by the processor, wherein the processor calls the program stored in the storage medium to execute the steps of the batch database insertion method using a distributed database as described in any of the first aspects above.

[0032] Fourthly, embodiments of this application provide a storage medium storing a computer program, which, when executed by a processor, performs the steps of the batch data entry method using a distributed database as described in any of the first aspects above.

[0033] Compared with the prior art, this application has the following beneficial effects:

[0034] This application provides a method, apparatus, server, and medium for batch data import using a distributed database. The method employs a data loading executor to obtain task configuration information for batch data loading tasks targeting a target business database from a stream-batch integrated computing engine; the data loading executor generates a control file based on the task configuration information; the data loading executor pulls batch business data from the target business database based on the task configuration information and generates data files based on the batch business data; and the data loading executor executes preset data loading commands according to the control file to write the data files to the target distributed database. This saves resources and improves data import efficiency in batch task scenarios. Attached Figure Description

[0035] To more clearly illustrate the technical solutions of the embodiments of the present invention, the accompanying drawings used in the embodiments will be briefly introduced below. It should be understood that the following drawings only show some embodiments of the present invention and should not be regarded as a limitation on the scope. For those skilled in the art, other related drawings can be obtained based on these drawings without creative effort.

[0036] Figure 1 A flowchart illustrating a batch data entry method using a distributed database provided in this application;

[0037] Figure 2 A flowchart illustrating a method for generating a control file provided in an embodiment of this application;

[0038] Figure 3 A flowchart illustrating a method for generating data files based on batch business data, provided in an embodiment of this application;

[0039] Figure 4 A schematic diagram of a batch business data import device using a distributed database provided in an embodiment of this application;

[0040] Figure 5 This is a schematic diagram of a server provided in an embodiment of this application.

[0041] Icons: 401 - Acquisition Module, 402 - Generation Module, 403 - Pull Module, 404 - Load Module, 501 - Processor, 502 - Storage Medium. Detailed Implementation

[0042] To make the objectives, technical solutions, and advantages of the embodiments of the present invention clearer, the technical solutions of the embodiments of the present application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present application, and not all embodiments. The components of the embodiments of the present application described and shown in the accompanying drawings can generally be arranged and designed in various different configurations.

[0043] Therefore, the following detailed description of the embodiments of this application provided in the accompanying drawings is not intended to limit the scope of the claimed application, but merely to illustrate selected embodiments of the application. All other embodiments obtained by those skilled in the art based on the embodiments of this application without inventive effort are within the scope of protection of this application.

[0044] It should be noted that similar labels and letters in the following figures indicate similar items. Therefore, once an item is defined in one figure, it does not need to be further defined and explained in subsequent figures.

[0045] Furthermore, the terms "first" and "second" are used only to distinguish descriptions and should not be interpreted as indicating or implying relative importance.

[0046] It should be noted that, where there is no conflict, the features in the embodiments of the present invention can be combined with each other.

[0047] The following specific examples illustrate a batch data insertion method using a distributed database provided in this application. Figure 1 The flowchart provided in this application illustrates a batch database loading method using a distributed database. The execution entity of this method is a server that integrates a stream-batch computing engine. The stream-batch computing engine has a data loading executor pre-integrated for the target distributed database.

[0048] For example, the streaming / batch computing engine is Flink. The target distributed database is Greenplum, which is a relational database cluster that is actually a logical database composed of multiple independent database services. The data loading executor is the executor (GploadExecutor) that executes gpload. A new executor (GploadExecutor) is added for Greenplum in flink-connector-jdbc, and this executor is initialized in Flink batch mode.

[0049] like Figure 1 As shown, the method includes:

[0050] S101. Use the data loading executor to obtain the task configuration information of the batch data loading task for the target business database by the integrated stream and batch computing engine.

[0051] The task configuration information for the batch data loading task includes the source of the batch data and the target source for loading the batch data, so that the batch data loading task can be executed according to the task configuration information.

[0052] S102. A data loading executor is used to generate a control file based on the task configuration information.

[0053] The data loading executor is the gpload executor, and the control file is the control file required by gpload. For example, the control file is a Yaml format file, generated using SnakeYaml from the task configuration information.

[0054] The control file contains both task configuration information and can execute gpload's data loading task.

[0055] S103. Using a data loading executor, based on task configuration information, batch business data is retrieved from the target business database, and a data file is generated based on the batch business data.

[0056] The task configuration information includes the source of the batch data: the target business database. A data loading executor, based on this configuration information, can pull batch business data from the target business database. The data file is in a format executable by the data loading executor. For example, the data file is in CSV format; the javacsv tool is used to generate the CSV data file from the batch business data.

[0057] For example, in Flink batch mode, every 20,000 data entries are flushed as a batch. Each time the data loading executor receives a batch of data, it generates a data file based on the business data in that batch to perform the data loading task.

[0058] For example, the amount of data per batch in Flink is configurable. With 32GB of server memory, each batch can be adjusted to 100,000 records, which can significantly reduce the time to generate data files and improve data loading efficiency.

[0059] S104. Using a data loading executor, execute preset data loading commands according to the control file to write the data file to the target distributed database.

[0060] For example, the stream-batch integrated computing engine only needs to write the data file to the disk at high speed and execute the preset data loading command, and the target distributed database will complete the data entry. This saves the server's memory and CPU resources while also significantly improving the performance of the entire data extraction chain.

[0061] For example, after introducing gpload, when the target table is empty, the insertion performance is about twice as fast as gpcopy, reaching 20,000-40,000 rows / second. When the target table's data volume increases to tens of millions, hundreds of millions, or more, the data update performance can still reach 10,000-20,000 rows / second, achieving a performance improvement of tens of times compared to gpcopy. This saves resources and improves data ingestion efficiency in batch task scenarios.

[0062] In summary, this embodiment employs a data loading executor to obtain the task configuration information for batch data loading tasks targeting the target business database from the integrated stream and batch computing engine; the data loading executor generates a control file based on the task configuration information; the data loading executor pulls batch business data from the target business database based on the task configuration information and generates a data file based on the batch business data; and the data loading executor executes preset data loading commands according to the control file to write the data file to the target distributed database. This saves resources and improves data ingestion efficiency in batch task scenarios.

[0063] This embodiment processes batch data. After a batch of data is stored, S101-S104 are repeated to store the next batch of data.

[0064] In the above Figure 1 Based on the corresponding embodiments, this application also provides a method for generating control files. Figure 2 This is a flowchart illustrating a method for generating a control file according to an embodiment of this application. Figure 2 As shown, in S102, a data loading executor is used to generate a control file based on the task configuration information, including:

[0065] S201. Using a data loading executor, the task configuration information is parsed to obtain the database information of the target business database and the data table information of the target distributed database.

[0066] The database information of the target business database indicates which database the data comes from, while the data table information of the target distributed database indicates which data table the data will be loaded into.

[0067] S202. Using a data loading executor, a control file is generated based on database information and data table information.

[0068] The control file can identify which database the data comes from and which data table the data will be loaded into, making it easy to accurately load the data according to the control file.

[0069] In summary, this embodiment employs a data loading executor to parse the metadata of the task configuration information, obtaining the database information of the target business database and the data table information of the target distributed database. Based on the database and data table information, the data loading executor generates a control file. This facilitates precise data loading according to the control file.

[0070] In the above Figure 2 Based on the corresponding embodiments, this application also provides a method for generating data files based on batch business data. Figure 3 This is a flowchart illustrating a method for generating data files based on batch business data, provided as an embodiment of this application. Figure 3 As shown, in S103, a data loading executor is used to pull batch business data from the target business database based on task configuration information, and generates data files based on the batch business data, including:

[0071] S301. Using a data loading executor, based on database information, batch business data is pulled from the target business database.

[0072] Database information indicates which database the data comes from. Based on this information, batch business data to be loaded can be accurately retrieved from the target business database.

[0073] S302. A data loading executor is used to generate a data file based on data table information and batch business data.

[0074] Generate the header of the data file based on the field information of the target data table in the data table information, then traverse the received business data, assemble it into rows according to column order, and use the javacsv tool to generate a CSV format data file from the assembled row data and write it to the disk.

[0075] For example, the filename is doctor_calrecord_data.csv.

[0076] In summary, this embodiment employs a data loading executor to retrieve batch business data from the target business database based on database information; and then, based on data table information and the batch business data, generates a data file. This accurately retrieves batch business data and generates a data file that is easy for the data loading executor to execute.

[0077] In the above Figure 1Based on the corresponding embodiments, in another embodiment of this application, a data loading executor is used in S104 to execute a preset data loading command according to the control file to write the data file to the target distributed database, including:

[0078] After the preset data loading command is successfully started, the data loading executor starts the preset file disk service. The preset file disk service transfers the data file to the target distributed database, so that the target distributed database loads the data file as an external table through the preset file disk service and merges the data in the data file into the target distributed database.

[0079] For example, the default file disk service is gpfdist. Taking Greenlum as an example, some pre-deployment preparation is required. Install the greenlum-client tool on the server where Flink is located. This tool includes gpload and gpfdist services. The deployment package can be the version provided on the Greenlum official website.

[0080] For example, after the `gpload` command starts successfully, it first starts the `gpfdist` service locally, which exposes the data file to the Greenplum cluster. Second, it notifies the Greenplum cluster to load the `gpfdist` service as an external table. Greenplum then loads the data from this external table (the data file) into the target distributed database. Compared to Flink using `gpcopy` (essentially JDBC) to write data to Greenplum, using `gpload` fully utilizes Greenplum's capabilities. That is, Flink only needs to quickly write the CSV file to disk and start a single `gpload` command; the time-consuming and performance-intensive operation of writing data to the target table is handled by Greenplum. This saves Flink server memory and CPU resources while significantly improving the performance of the entire data extraction chain.

[0081] In summary, in this embodiment, after the preset data loading command is successfully initiated, the data loading executor starts a preset file disk service. This preset file disk service then transfers the data file to the target distributed database. The target distributed database then loads the data file as an external table through the preset file disk service and merges the data from the data file into the target distributed database. This saves resources and improves data loading efficiency.

[0082] Based on the above embodiments, in another embodiment of this application, after the data loading executor successfully starts the preset data loading command, it starts a preset file disk service. After the data file is transferred to the target distributed database through the preset file disk service, the method further includes:

[0083] The data loading process is executed, and the data file is then deleted.

[0084] After merging the data from the CSV file into the target distributed database, the data file is no longer needed and can be deleted, freeing up memory and facilitating subsequent data import.

[0085] In summary, this embodiment employs data loading and execution followed by data file deletion. This frees up memory, facilitating subsequent data import.

[0086] Based on the above embodiments, in another embodiment of this application, the database information of the target business database includes: the identifier of the target database, the target port, and the target account. The identifier, target port, and target account of the target database can uniquely identify the target database, facilitating the retrieval of business data from the target database.

[0087] The target distributed database's table information includes: the table name, fields, and primary key information. The table name, fields, and primary key information uniquely identify the target table, facilitating data import.

[0088] Based on the above embodiments, in another embodiment of this application, a data loading executor is used in S104 to execute a preset data loading command according to a control file to write the data file to the target distributed database, including:

[0089] A data loading executor is used to execute preset data loading commands according to the control file, so as to write the data file to the target data table.

[0090] The target data table can be uniquely identified based on the information in the data table. Therefore, the data file can be directly written to the target data table to accurately realize the data entry into the database.

[0091] In summary, this embodiment employs a data loading executor to execute preset data loading commands according to a control file, thereby writing the data file into the target data table. This ensures accurate data import.

[0092] The following describes the batch business data entry device, equipment, and storage medium using a distributed database provided in this application for execution. The specific implementation process and technical effects are described above and will not be repeated below.

[0093] Figure 4 This application provides a schematic diagram of a batch business data import device using a distributed database, applied to a server integrated with a stream-batch computing engine. The stream-batch computing engine pre-integrates a data loading executor for the target distributed database, such as... Figure 4 As shown, the device includes:

[0094] The acquisition module 401 is used to acquire the task configuration information of the batch data loading task for the target business database by the stream-batch integrated computing engine using the data loading executor.

[0095] The generation module 402 is used to generate a control file based on the task configuration information using a data loading executor.

[0096] The pull module 403 is used to pull batch business data from the target business database based on task configuration information using a data loading executor, and generate data files based on the batch business data.

[0097] Loading module 404 is used to execute preset data loading commands according to the control file using a data loading executor to write the data file to the target distributed database.

[0098] Furthermore, the generation module 402 is specifically used to use a data loading executor to perform metadata parsing on the task configuration information to obtain the database information of the target business database and the data table information of the target distributed database; and to use the data loading executor to generate a control file based on the database information and the data table information.

[0099] Furthermore, the pull module 403 is specifically used to use a data loading executor to pull batch business data from the target business database based on database information; and to use the data loading executor to generate a data file based on data table information and batch business data.

[0100] Furthermore, the loading module 404 is specifically used to start the preset file disk service after the preset data loading command is successfully started by the data loading executor, and to transfer the data file to the target distributed database through the preset file disk service, so that the target distributed database loads the data file through the preset file disk service in the form of an external table, and merges the data in the data file into the target distributed database.

[0101] Furthermore, the loading module 404 is also used to perform data loading execution and delete data files.

[0102] Furthermore, module 403 retrieves database information specifically for the target business database, including: the target database identifier, target port, and target account; and table information for the target distributed database, including: the table name, fields, and primary key information of the target table.

[0103] Furthermore, the loading module 404 is specifically used to employ a data loading executor to execute a preset data loading command according to the control file, so as to write the data file to the target data table.

[0104] Figure 5 This is a schematic diagram of a server provided in an embodiment of this application. The server may be a device with computing processing capabilities.

[0105] The server includes a processor 501 and a storage medium 502. The processor 501 and the storage medium 502 are connected via a bus.

[0106] Storage medium 502 is used to store programs, and processor 501 calls the programs stored in storage medium 502 to execute the above method embodiments. The specific implementation and technical effects are similar, and will not be described again here.

[0107] Optionally, the present invention also provides a storage medium including a program, which, when executed by a processor, is used to perform the above-described method embodiments. In the several embodiments provided by the present invention, it should be understood that the disclosed apparatus and methods can be implemented in other ways. For example, the apparatus embodiments described above are merely illustrative; for example, the division of units is merely a logical functional division, and in actual implementation, there may be other division methods. For example, multiple units or components may be combined or integrated into another system, or some features may be ignored or not executed. Furthermore, the displayed or discussed mutual coupling or direct coupling or communication connection may be through some interfaces; the indirect coupling or communication connection between devices or units may be electrical, mechanical, or other forms.

[0108] The units described as separate components may or may not be physically separate. The components shown as units may or may not be physical units; that is, they may be located in one place or distributed across multiple network units. Some or all of the units can be selected to achieve the purpose of this embodiment according to actual needs.

[0109] Furthermore, the functional units in the various embodiments of the present invention can be integrated into one processing unit, or each unit can exist physically separately, or two or more units can be integrated into one unit. The integrated unit can be implemented in hardware or in the form of hardware plus software functional units.

[0110] The integrated units implemented as software functional units described above can be stored in a storage medium. These software functional units, stored in a storage medium, include several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) or processor to execute some steps of the methods described in the various embodiments of the present invention. The aforementioned storage medium includes various media capable of storing program code, such as USB flash drives, portable hard drives, read-only memory (ROM), random access memory (RAM), magnetic disks, or optical disks.

Claims

1. A bulk warehousing method using a distributed database, characterized by, The method, applied to a server integrated with a stream-batch computing engine, wherein the stream-batch computing engine pre-integrates a data loading executor for a target distributed database, includes: The data loading executor is used to obtain the task configuration information of the batch data loading task for the target business database by the integrated stream and batch computing engine; The data loading executor is used to generate a control file based on the task configuration information; Using the data loading executor, based on the task configuration information, a batch of business data is retrieved from the target business database, and a data file is generated based on the batch of business data; The data loading executor is used to execute a preset data loading command according to the control file, so as to write the data file to the target distributed database.

2. The method of claim 1, wherein, The process of using the data loading executor to generate a control file based on the task configuration information includes: The data loading executor is used to parse the metadata of the task configuration information to obtain the database information of the target business database and the data table information of the target distributed database. The control file is generated using the data loading executor based on the database information and the data table information.

3. The method of claim 2, wherein, The process of using the data loading executor to retrieve batch business data from the target business database based on the task configuration information, and generating a data file based on the batch business data, includes: Using the data loading executor, the batch business data is retrieved from the target business database based on the database information; The data file is generated using the data loading executor based on the data table information and the batch business data.

4. The method of claim 1, wherein, The step of using the data loading executor to execute a preset data loading command according to the control file to write the data file to the target distributed database includes: After the preset data loading command is successfully started, the data loading executor starts a preset file disk service, which transfers the data file to the target distributed database. The target distributed database loads the data file as an external table through the preset file disk service and merges the data in the data file into the target distributed database.

5. The method of claim 4, wherein, After the preset data loading command is successfully initiated, the data loading executor starts a preset file disk service, and the data file is transferred to the target distributed database through the preset file disk service. The method further includes: The data is loaded and executed, and the data file is deleted.

6. The method of claim 3, wherein, The database information of the target business database includes: the identifier of the target database, the target port, and the target account; The data table information of the target distributed database includes: the table name of the target data table, the fields of the target data table, and the primary key information.

7. The method of claim 6, wherein, The step of using the data loading executor to execute a preset data loading command according to the control file to write the data file to the target distributed database includes: The data loading executor is used to execute a preset data loading command according to the control file, so as to write the data file into the target data table.

8. A batch business data import device using a distributed database, characterized in that, An apparatus for use on a server integrating a stream-batch integrated computing engine, wherein the stream-batch integrated computing engine pre-integrates a data loading executor for a target distributed database, the apparatus comprising: The acquisition module is used to acquire the task configuration information of the batch data loading task of the stream-batch integrated computing engine for the target business database using the data loading executor; The generation module is used to generate a control file based on the task configuration information using the data loading executor. The pull module is used to pull batch business data from the target business database based on the task configuration information using the data loading executor, and generate a data file based on the batch business data; The loading module is used to execute a preset data loading command according to the control file using the data loading executor, so as to write the data file to the target distributed database.

9. A server, characterized by include: The processor and the storage medium are connected via a bus for communication. The storage medium stores program instructions executable by the processor. The processor calls the program stored in the storage medium to execute the steps of the batch database insertion method using a distributed database as described in any one of claims 1-7.

10. A storage medium, characterized by The storage medium stores a computer program, which, when executed by a processor, performs the steps of the batch data entry method using a distributed database as described in any one of claims 1-7.

Images