A method, system and device for multi-source data acquisition based on increment

Abstract

The present invention discloses a multi-source data acquisition method, system and device based on increment, and relates to the technical field of data acquisition. The method comprises: determining an incremental data packet based on a logic log of a preset database; storing the incremental data packet to an FTP server and generating a data packet acquisition log; polling the data packet acquisition log, and when the polling result indicates that the data packet acquisition log has been updated, determining a set of newly added data packets to be processed; splitting the incremental data packets in the set of newly added data packets to be processed to different FTP servers, and decompressing the incremental data packets from the FTP server to an ETL server to obtain corresponding business data files and SQL files; executing the SQL file to mount the business data file on the incremental layer of the ODS cache; merging the business data file with the historical business data in the Target database of the ODS cache to obtain the currently collected business data. The present invention can efficiently collect massive data.

Description

Technical Field

[0001] The present invention relates to the technical field of data acquisition, and in particular to an incremental-based multi-source data acquisition method, system and device. Background Art

[0002] With the advent of the big data era, more and more diverse data are coming into view. In order to make better use of these massive data, data analysis is an inevitable link. However, there is a more important process between data analysis and data production, that is, data collection (Extract-Transform-Load, ETL). ETL is used to describe the process of extracting (extract), transforming (transform), and loading (load) data from the source end to the destination end. For medium and large enterprises, especially Internet companies and financial companies, when they are faced with huge underlying data and data from different data sources, they first need to ensure the consistency of the source layer data and the original system data. Secondly, on the premise of ensuring consistency, technical means or data processing methods are used to ensure the efficiency of data collection and the stability between servers. The current existing technical solutions mainly collect data in the following two ways:

[0003] First, the big data platform provides a set of standardized interfaces such as JDBC or ODBC to the outside world. JDBC stands for Java Database Connectivity, which is an application programming interface, or API; ODBC is an open database connection. Like JDBC, ODBC is also an API interface that acts as an interface between the client application and the server database. The big data platform can complete real-time or non-real-time data collection and storage through the above API interfaces. The big data platform provides a unified API interface for data collection, which has the following disadvantages: First, any data source needs to be developed based on the standard API interface; second, the interface development consumes a lot of human and material resources; third, the scalability is poor, and for newly developed businesses, the previously developed interface needs to be modified; fourth, the performance of massive data collection is low.

[0004] The second is the system log collection method, which realizes data collection by reading database log information or monitoring data changes on the data source. However, when realizing data collection by reading database logs, it is necessary to associate the attribute information of the data source with the big data platform through the configuration table. The data collection method of reading database logical logs has the following disadvantages: first, it is difficult to monitor the huge amount of data changes in the data source in real time; second, it is easy to lose logs and has relatively poor stability; third, the collection method through system logs is inefficient; fourth, log storage takes up a lot of memory, and long-term use is prone to more memory fragmentation; this method has high manual operation and maintenance costs, and the data collection platform configuration information needs to be adjusted for new data sources, tables, and fields. Summary of the invention

[0005] The purpose of the present invention is to provide an incremental-based multi-source data acquisition method, system and device to achieve efficient acquisition of massive data from multiple data sources.

[0006] To achieve the above object, the present invention provides the following solutions:

[0007] In a first aspect, the present invention provides an incremental-based multi-source data acquisition method, comprising:

[0008] Determine the incremental data packet based on the logical log of a preset database; the preset database stores insurance business source data of multiple platforms;

[0009] The incremental data packets are stored in the FTP server and a data packet acquisition log is generated;

[0010] Polling the data packet acquisition log, and when the polling result indicates that the data packet acquisition log has been updated, determining a set of newly added data packets to be processed according to the data packet acquisition log;

[0011] Splitting the incremental data packets in the set of newly added data packets to be processed to different FTP servers, and decompressing the incremental data packets from the FTP servers to the ETL server to obtain corresponding business data files and SQL files;

[0012] Execute the SQL file to mount the business data file in the incremental layer of the ODS cache;

[0013] The business data file mounted on the incremental layer of the ODS cache is merged with the historical business data in the Target database of the ODS cache to obtain the currently collected business data.

[0014] Optionally, the data packet acquisition log is stored in the form of a Proclog table.

[0015] Optionally, polling the data packet acquisition log specifically includes:

[0016] Celery scheduling is used to scan the data packet acquisition log at a preset interval.

[0017] Optionally, before executing the SQL file, the method further includes:

[0018] Remove the carriage returns, line feeds, spaces, special characters and null values ​​in the SQL file, and adjust the statements in the SQL file to SQL statements suitable for creating external tables in the PG library.

[0019] Optionally, determining a set of newly added data packets to be processed according to the data packet acquisition log specifically includes:

[0020] Obtain local storage logs of insurance business source data in the data warehouse;

[0021] The locally stored log is compared with the data packet acquisition log to determine a set of newly added data packets to be processed.

[0022] Optionally, the business data file mounted on the incremental layer of the ODS cache is merged with the historical business data in the Target database of the ODS cache to obtain the currently collected business data, specifically including:

[0023] Concurrently unloading the historical business data in the Target database cached by the ODS into historical data files;

[0024] Based on the same table name, the historical data file is merged with the business data file mounted in the incremental layer of the ODS cache to obtain an initial merged file;

[0025] The initial merged file is cleaned to obtain the currently collected business data.

[0026] Optionally, the method further comprises:

[0027] The currently collected business data is transferred to the data warehouse, and the local storage log is updated.

[0028] In a second aspect, the present invention provides an incremental-based multi-source data acquisition system, comprising:

[0029] An incremental data acquisition module is used to determine an incremental data packet based on a logical log of a preset database; the preset database stores insurance business source data of multiple platforms;

[0030] A log generation module, used for storing the incremental data packets to the FTP server and generating a data packet acquisition log;

[0031] A module for determining data packets to be processed, configured to poll the data packet acquisition log, and when a polling result indicates that the data packet acquisition log has been updated, determine a set of newly added data packets to be processed according to the data packet acquisition log;

[0032] A data parallel processing module is used to split the incremental data packets in the set of newly added data packets to be processed to different FTP servers, and decompress the incremental data packets from the FTP servers to the ETL server to obtain corresponding business data files and SQL files;

[0033] A business data mounting module, used for executing the SQL file to mount the business data file in the incremental layer of the ODS cache;

[0034] The currently collected business data determination module is used to merge the business data file mounted on the incremental layer of the ODS cache with the historical business data in the Target database of the ODS cache to obtain the currently collected business data.

[0035] In a third aspect, the present invention provides an electronic device, the electronic device comprising a memory and a processor;

[0036] The memory is used to store a computer program, and the processor is used to run the computer program to execute an increment-based multi-source data acquisition method.

[0037] According to the specific embodiments provided by the present invention, the present invention discloses the following technical effects:

[0038] The present invention discloses a multi-source data acquisition method, system and device based on increments, which determines an incremental data packet based on a logical log of a preset database, wherein the preset database stores insurance business source data of multiple platforms, and it can be known that the incremental data packet is the incremental data obtained from multiple data sources. The incremental data packet is stored in an FTP server and a data packet acquisition log is generated, and the data packet acquisition log is polled, and when the polling result indicates that the data packet acquisition log has been updated, a set of newly added data packets to be processed is determined; then the incremental data packets in the set of newly added data packets to be processed are split to different FTP servers to realize concurrent data extraction; at the same time, the incremental data packet is decompressed from the FTP server to the ETL server to obtain the corresponding business data file and SQL file, thereby realizing high concurrent data decompression of multiple processes; then the SQL file is executed to mount the business data file in the incremental layer of the ODS cache, thereby obtaining actual newly added business data, that is, incremental data; the business data file mounted in the incremental layer of the ODS cache is merged with the historical business data in the Target database of the ODS cache to obtain the current collected business data. The present invention splits multi-source business data to different FTP servers and ETL servers, reducing the pressure of a single server in processing data; at the same time, through multi-threading including multi-process high-concurrency data extraction, decompression processing and final data merging processing, efficient collection of massive data is achieved. BRIEF DESCRIPTION OF THE DRAWINGS

[0039] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings required for use in the embodiments will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative labor.

[0040] Figure 1 It is a flowchart of the incremental multi-source data acquisition method of the present invention;

[0041] Figure 2 It is a schematic diagram of a specific example of the incremental-based multi-source data acquisition method of the present invention;

[0042] Figure 3 It is a structural schematic diagram of the incremental multi-source data acquisition method of the present invention. DETAILED DESCRIPTION

[0043] The following will be combined with the drawings in the embodiments of the present invention to clearly and completely describe the technical solutions in the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.

[0044] The present invention provides an incremental-based multi-source data acquisition method, system and device, which have the characteristics of high performance and high scalability, and can provide general processing capabilities for ultra-large-scale data acquisition.

[0045] In order to make the above-mentioned objects, features and advantages of the present invention more obvious and easy to understand, the present invention is further described in detail below with reference to the accompanying drawings and specific embodiments.

[0046] Embodiment 1

[0047] like Figure 1 As shown, the present invention proposes a multi-source data acquisition method based on increment, comprising:

[0048] Step 100, based on the logical log of the preset database, determine the incremental data package; the preset database stores insurance business source data of multiple platforms. The insurance business source data includes insurance business system data and system internal related system business data; the insurance business system data includes corresponding underwriting, claims, auto insurance, non-auto insurance, agricultural insurance, inclusive finance and other business policy data; the system internal related system business data includes corresponding product information, employee information, customer information, etc.

[0049] Specifically, the database logical log is monitored in real time through the IIE data collection tool, and the incremental data obtained after processing by the IIE data collection tool is packaged by institution and instance every 30 minutes to obtain a tar incremental data package. Among them, the institution refers to the collection platform of the insurance business source data, which can be divided according to the province and city; the instance refers to the specific table in the business system database, which can be divided according to the specific insurance business source (such as underwriting, claims, collection and payment, finance, etc.) and table name.

[0050] Step 200, the incremental data packet is stored in the FTP server, and a data packet acquisition log is generated. Specifically, the tar incremental data packet is placed in the FTP server, and the data packet acquisition log is generated or updated according to the basic information such as the number of files, file names, and data acquisition time when the IIE data acquisition tool processes the incremental data acquisition.

[0051] In a specific example, the data packet acquisition log is stored in the form of a Proclog table.

[0052] Step 300, polling the data packet acquisition log, and when the polling result indicates that the data packet acquisition log has been updated, determining a set of newly added data packets to be processed according to the data packet acquisition log;

[0053] The polling of the data packet acquisition log specifically includes: the data warehouse uses Celery scheduling to scan the data packet acquisition log according to a preset interval time. In a specific practical application, the preset interval time is 5 minutes.

[0054] Among them, determining a set of newly added data packets to be processed based on the data packet acquisition log specifically includes: 1) obtaining a local storage log of insurance business source data in a data warehouse; 2) comparing the local storage log with the data packet acquisition log to determine a set of newly added data packets to be processed; the set of newly added data packets to be processed includes new data packets that are not recorded in the local storage log.

[0055] Step 400, split the incremental data packets in the set of newly added data packets to be processed to different FTP servers, and decompress the incremental data packets from the FTP servers to the ETL server to obtain corresponding business data files and SQL files.

[0056] In a specific practical application, the incremental data packets in the set of newly added data packets to be processed are split to multiple FTP servers according to different table names and Celery scheduling; each FTP server can support 15 tables for data extraction at the same time every day. Among them, different table names refer to the names of the data tables storing insurance business source data in the incremental data packets. Splitting the incremental data packets to different multiple FTP servers can realize multi-threaded processing tasks. At the same time, according to the multi-process of multiple provincial agencies, multiple incremental data packets are decompressed from the corresponding FTP servers to the ETL server respectively, and 1 data file and 1 SQL (Structured Query Language) file are obtained accordingly, thereby realizing a multi-threaded and multi-process high-concurrency data extraction and decompression process.

[0057] Before executing step 500, the method further includes: cleaning the SQL file. Specifically, removing the carriage return, line feed, space, special characters and null values ​​in the SQL file, and adjusting the statements in the SQL file to SQL statements suitable for creating external tables in the PG library (postgrel database).

[0058] Step 500, execute the SQL file to mount the business data file in the incremental layer of the ODS (Operational Data Store, temporary storage data operation layer) cache. Specifically, execute the cleaned SQL statement, so that all business data files have been mounted in the incremental layer of the ODS cache.

[0059] Step 600: merge the business data file mounted on the incremental layer of the ODS cache with the historical business data in the Target database of the ODS cache to obtain the currently collected business data.

[0060] Step 600 specifically includes: 1) concurrently unloading the historical business data in the Target database of the ODS cache into historical data files; 2) merging the historical data files with the business data files of the incremental layer mounted in the ODS cache based on the same table name to obtain an initial merged file; 3) performing data cleaning on the initial merged file to obtain the currently collected business data.

[0061] In a specific practical application, at a fixed time every day (such as 1 a.m. or 2 a.m.), the historical business data stored in the Target database of the ODS cache the previous day is concurrently unloaded as historical data files according to the organization and table name; on the ETL server, the historical data files with the same table name are merged with the business data files, and data cleaning is performed during the merging process to remove carriage returns and line feeds.

[0062] Preferably, the method further comprises: reposting the currently collected business data to the data warehouse, and updating the local storage log.

[0063] In a specific practical application, the present invention applies a distributed architecture, based on RPC (Remote Procedure Call) remote calls, distributes data extraction tasks to different servers through Celery to extract data concurrently, thereby achieving the purpose of system module splitting; using interface communication to reduce the coupling between system modules. Based on the above architecture, the present invention splits the tables corresponding to different businesses collected to different ETL servers, reducing the pressure of a single server processing data.

[0064] The above-mentioned distributed system mainly refers to high-concurrency data extraction, which uses a two-level concurrency method to extract multiple tables at the same time. Each table extracts data from all institutions at the same time to improve the efficiency of data collection. Among them, high concurrency refers to taking tables as units, and then splitting data files for extraction through provincial institutions. Multi-process includes multi-threading.

[0065] The present invention adopts a layered concept, splitting data collection, cleaning, loading, conversion, and multi-cluster data push into independent modules, and realizes the docking of multiple clusters and the push of specified tables by modifying the configuration.

[0066] In the present invention, multiple ETL server backups are set. When an ETL server is abnormal, by modifying the configuration, it can be switched to other ETL servers at any time to continue working, thereby ensuring the timeliness of data push.

[0067] The present invention can also ensure the accuracy of data through business primary key comparison, calculated amount comparison and multi-channel verification. In addition, the present invention is all linear logic, and can be horizontally expanded by modifying the configuration to connect multiple clusters. The present invention loads data files into data tables of different clusters in parallel through FTP.

[0068] Preferably, the present invention provides a personalized data collection process, and adopts different data collection logics for different databases and collection tools to ensure data collection efficiency.

[0069] In a specific example, if Figure 2 As shown, the incremental multi-source data acquisition method specifically includes the following steps:

[0070] 1) IIE software analyzes logical logs, extracts incremental data, and generates tarballs every 0.5 hours.

[0071] 2) The IIE software puts the tar package on the FTP server and inserts the tar package information into the proclog table.

[0072] 3) The ETL system polls the proclog table every 5 minutes to obtain the latest information, distributes tasks to the work queue of the FTP server, and fetches the latest tar package to the ETL server.

[0073] 4) The ETL system decompresses the tarball and mounts the data to the incremental layer of the ODS cache in the form of an external file.

[0074] 5) The ETL server merges the incremental layer data into the target database and assigns a serial number to each piece of data.

[0075] 6) Every day at T+1, the ETL system reads the main driver table, distributes tasks to the work queue, goes to the target database cached by the ODS, and unloads the incremental data of each table to the ETL server.

[0076] 7) The ETL system loads the data into the ODS layer SDATA library to achieve incremental multi-source data collection.

[0077] Embodiment 2

[0078] like Figure 3 As shown, in order to implement the technical solution in the first embodiment, this embodiment provides an incremental-based multi-source data acquisition system, including:

[0079] The incremental data acquisition module 101 is used to determine the incremental data packet based on the logical log of the preset database; the preset database stores insurance business source data of multiple platforms.

[0080] The log generation module 201 is used to store the incremental data packet to the FTP server and generate a data packet acquisition log.

[0081] The to-be-processed data packet determination module 301 is used to poll the data packet acquisition log, and when the polling result indicates that the data packet acquisition log has been updated, determine a set of newly added data packets to be processed according to the data packet acquisition log.

[0082] The data parallel processing module 401 is used to split the incremental data packets in the set of newly added data packets to be processed to different FTP servers, and at the same time decompress the incremental data packets from the FTP servers to the ETL server to obtain corresponding business data files and SQL files.

[0083] The business data mounting module 501 is used to execute the SQL file to mount the business data file in the incremental layer of the ODS cache.

[0084] The currently collected business data determination module 601 is used to merge the business data file mounted on the incremental layer of the ODS cache with the historical business data in the Target database of the ODS cache to obtain the currently collected business data.

[0085] Embodiment 3

[0086] This embodiment provides an electronic device, which includes a memory and a processor; the memory is used to store a computer program, and the processor is used to run the computer program to execute the incremental-based multi-source data acquisition method described in Embodiment 1.

[0087] Optionally, the electronic device is a server.

[0088] In addition, this embodiment further provides a computer-readable storage medium, which stores a computer program; when the computer program is executed by a processor, the steps of the incremental-based multi-source data acquisition method described in Embodiment 1 are implemented.

[0089] In this specification, each embodiment is described in a progressive manner, and each embodiment focuses on the differences from other embodiments. The same or similar parts between the embodiments can be referred to each other. For the system disclosed in the embodiment, since it corresponds to the method disclosed in the embodiment, the description is relatively simple, and the relevant parts can be referred to the method part.

[0090] The principles and implementation methods of the present invention are described in this article using specific examples. The description of the above embodiments is only used to help understand the method and core idea of ​​the present invention. At the same time, for those skilled in the art, according to the idea of ​​the present invention, there will be changes in the specific implementation methods and application scope. In summary, the content of this specification should not be understood as limiting the present invention.

Claims

1. A multi-source data acquisition method based on increment, characterized in that: Methods include: Determine the incremental data packet based on the logical log of the preset database; The preset database stores insurance business source data from multiple platforms; The incremental data packets are stored in the FTP server and a data packet acquisition log is generated; Polling the data packet acquisition log, and when the polling result indicates that the data packet acquisition log has been updated, determining a set of newly added data packets to be processed according to the data packet acquisition log; Splitting the incremental data packets in the set of newly added data packets to be processed to different FTP servers, and decompressing the incremental data packets from the FTP servers to the ETL server to obtain corresponding business data files and SQL files; The incremental data packets in the set of newly added data packets to be processed are split to multiple FTP servers according to different table names through Celery scheduling; the different table names refer to the names of the data tables storing the insurance business source data in the incremental data packets; splitting the incremental data packets to different multiple FTP servers can realize multi-threaded processing tasks; each institution decompresses multiple incremental data packets from the corresponding FTP server to the ETL server in multiple processes, and obtains 1 business data file and 1 SQL file respectively, thereby realizing a multi-threaded and multi-process high-concurrency data extraction and decompression process; applying a distributed architecture, based on RPC remote calls, and distributing data extraction tasks to different servers through Celery to extract data concurrently; Execute the SQL file to mount the business data file in the incremental layer of the ODS cache; Merge the business data file mounted on the incremental layer of the ODS cache with the historical business data in the Target database of the ODS cache to obtain the currently collected business data; specifically include: concurrently unloading the historical business data in the Target database of the ODS cache into historical data files; merging the historical data files with the business data files mounted on the incremental layer of the ODS cache based on the same table name to obtain an initial merged file; and performing data cleaning on the initial merged file to obtain the currently collected business data; At a fixed time every day, the historical business data stored in the Target database cached by ODS the previous day is concurrently unloaded as historical data files according to the organization and table name; the historical data files with the same table name are merged with the business data files on the ETL server, and data cleaning is performed during the merging process to remove carriage returns and line feeds; organization refers to the collection platform for insurance business source data.

2. The incremental-based multi-source data acquisition method according to claim 1, characterized in that: The data packet acquisition log is stored in the form of a Proclog table.

3. The incremental-based multi-source data acquisition method according to claim 2, characterized in that: Polling the data packet acquisition log specifically includes: Celery scheduling is used to scan the data packet acquisition log at a preset interval.

4. The incremental-based multi-source data acquisition method according to claim 1, characterized in that: Before executing the SQL file, the method further includes: Remove the carriage returns, line feeds, spaces, special characters, and null values ​​in the SQL file, and adjust the statements in the SQL file to SQL statements suitable for creating external tables in the PG library.

5. The incremental-based multi-source data acquisition method according to claim 1, characterized in that: Determining a set of newly added data packets to be processed according to the data packet acquisition log specifically includes: Obtain local storage logs of insurance business source data in the data warehouse; The locally stored log is compared with the data packet acquisition log to determine a set of newly added data packets to be processed.

6. The incremental-based multi-source data acquisition method according to claim 5, characterized in that: The method also includes: The currently collected business data is transferred to the data warehouse, and the local storage log is updated.

7. An incremental-based multi-source data acquisition system, characterized in that: The system includes: An incremental data acquisition module is used to determine incremental data packets based on the logical log of a preset database; The preset database stores insurance business source data from multiple platforms; A log generation module, used for storing the incremental data packets to the FTP server and generating a data packet acquisition log; A module for determining data packets to be processed, configured to poll the data packet acquisition log, and when a polling result indicates that the data packet acquisition log has been updated, determine a set of newly added data packets to be processed according to the data packet acquisition log; A data parallel processing module is used to split the incremental data packets in the set of newly added data packets to be processed to different FTP servers, and decompress the incremental data packets from the FTP servers to the ETL server to obtain corresponding business data files and SQL files; The incremental data packets in the set of newly added data packets to be processed are split to multiple FTP servers according to different table names through Celery scheduling; the different table names refer to the names of the data tables storing the insurance business source data in the incremental data packets; splitting the incremental data packets to different multiple FTP servers can realize multi-threaded processing tasks; each institution decompresses multiple incremental data packets from the corresponding FTP server to the ETL server in multiple processes, and obtains 1 business data file and 1 SQL file respectively, thereby realizing a multi-threaded and multi-process high-concurrency data extraction and decompression process; applying a distributed architecture, based on RPC remote calls, and distributing data extraction tasks to different servers through Celery to extract data concurrently; A business data mounting module, used for executing the SQL file to mount the business data file in the incremental layer of the ODS cache; The current collected business data determination module is used to merge the business data file mounted on the incremental layer of the ODS cache with the historical business data in the Target database of the ODS cache to obtain the current collected business data; specifically includes: concurrently unloading the historical business data in the Target database of the ODS cache into historical data files; merging the historical data files with the business data files mounted on the incremental layer of the ODS cache based on the same table name to obtain an initial merged file; and performing data cleaning on the initial merged file to obtain the current collected business data; At a fixed time every day, the historical business data stored in the Target database cached by ODS the previous day is concurrently unloaded as historical data files according to the organization and table name; the historical data files with the same table name are merged with the business data files on the ETL server, and data cleaning is performed during the merging process to remove carriage returns and line feeds; organization refers to the collection platform for insurance business source data.

8. An electronic device, characterized in that: The electronic device comprises a memory and a processor; The memory is used to store a computer program, and the processor is used to run the computer program to execute the incremental-based multi-source data acquisition method according to any one of claims 1 to 6.

Images