Business data extraction method and system

Abstract

The application discloses a kind of service data extraction method and system, it is related to data processing technical field.The method comprises: establishing data warehouse, data warehouse includes real-time library, history library, today library and repair history library;Business data is extracted from production database, and stored in mirror database;The statistical business data in mirror database is extracted to real-time library, the business data extracted to real-time library is multidimensional processing and aggregation processing, and aggregation data is obtained and synchronized to history library;Today library and repair history library synchronize aggregation data to history library according to preset update rule.The application can reduce the coupling line of data statistics and data acquisition, share system pressure, improve the stability of application system, and improve the statistical timeliness in the scene of larger data statistics range, in addition, when part of the key information of data changes, a verification mechanism is established, which can improve the accuracy of data statistics result.

Description

Technical Field

[0001] This invention relates to the field of data processing technology, and in particular to a method and system for extracting business data. Background Technology

[0002] Currently, business data is usually read directly from the production database. For example, in the Maritime Safety Administration's Dangerous Goods Management System, the dangerous goods query and statistics module reads data directly from the business production database.

[0003] Retrieving data directly from the production database can burden the system, create I / O bottlenecks, reduce the stability of the application system, and when the data statistics range is large, retrieving aggregated data from the production database will take a lot of time, reducing the timeliness of the data statistics results. In addition, when some key information in the data changes, historical information cannot be updated synchronously, reducing the accuracy of the data statistics results. Summary of the Invention

[0004] The present invention aims to solve the problems of poor stability, poor timeliness, and low accuracy in directly obtaining data from production databases. In order to solve the above technical problems, the present invention provides a business data extraction method and system.

[0005] The first aspect provides a method for extracting business data, including:

[0006] Establish a data warehouse, which includes a real-time warehouse, a historical warehouse, a today warehouse, and a repaired historical warehouse. The real-time warehouse is used to store all aggregated data, the historical warehouse is used to store aggregated data except for the target day, the today warehouse is used to update all aggregated data generated on the target day, and the repaired historical warehouse is used to update all aggregated data within a preset number of days.

[0007] Business data is extracted from the production database and stored in the mirror database;

[0008] The business data to be statistically analyzed in the mirror database is extracted into the real-time database. The extracted business data in the real-time database is then processed and aggregated in multiple dimensions to obtain aggregated data, which is then synchronized to the historical database.

[0009] The today database and the repair history database synchronize the aggregated data to the history database according to preset update rules.

[0010] In one possible implementation of the first aspect, the business data to be statistically analyzed in the mirror database is extracted into the real-time database. The extracted business data in the real-time database undergoes multi-dimensional processing and aggregation to obtain aggregated data, which is then synchronized to the historical database. Specifically, this includes:

[0011] Determine whether the real-time database has a timestamp table;

[0012] When the real-time library has the timestamp table, determine whether the real-time library has a jobname table, which is used to record jobs that have finished running;

[0013] When the real-time database has the jobname table, delete the data in the jobname table;

[0014] The timestamps in the timestamp table are passed as variables to the SQL statement. Business data is extracted from the mirror database according to the timestamps. The business data is aggregated and input into the workflow. The data in the workflow is transformed, processed, and loaded, and then synchronized to the historical database.

[0015] In one possible implementation of the first aspect, the data in the workflow is transformed, processed, and loaded, specifically including:

[0016] Select the output fields you want to output;

[0017] The data in the workflow is sorted according to preset field values ​​to obtain a sequence field;

[0018] Establish a mapping relationship between the output fields and the sequence fields, and determine the fields that need to be updated and / or inserted in the sequence fields based on preset key fields;

[0019] Update the target table in the real-time database according to the fields that need to be updated, and / or insert data into the target table in the real-time database according to the fields that need to be inserted.

[0020] One possible implementation of the first aspect also includes:

[0021] The names of completed jobs are stored in the jobname table. The number of rows in the jobname table is used to determine whether all jobs have finished running. When all jobs have finished running or a preset time threshold has been exceeded, the timestamps in the timestamp table are updated.

[0022] One possible implementation of the first aspect also includes:

[0023] When the real-time library does not have the timestamp table, create the timestamp table and initialize a timestamp;

[0024] If the real-time database does not have the jobname table, create the jobname table.

[0025] Secondly, a business data extraction system is provided, including: a database creation unit, an extraction unit, an aggregation unit, and an update unit, wherein:

[0026] The database building unit is used to build a data warehouse, which includes a real-time database, a historical database, a today database, and a repaired historical database. The real-time database is used to store all aggregated data, the historical database is used to store aggregated data except for the target day, the today database is used to update all aggregated data generated on the target day, and the repaired historical database is used to update all aggregated data within a preset number of days.

[0027] The extraction unit is used to extract business data from the production database and store it in the mirror database;

[0028] The aggregation unit is used to extract the business data to be statistically analyzed from the mirror database into the real-time database, perform multi-dimensional processing and aggregation on the business data extracted into the real-time database, obtain aggregated data, and synchronize it to the historical database.

[0029] The update unit is used to control the today database and the repair history database to synchronize the aggregated data to the history database according to the preset update rules.

[0030] In one possible implementation of the second aspect, the aggregation unit is specifically used to determine whether the real-time database has a timestamp table; when the real-time database has the timestamp table, it determines whether the real-time database has a jobname table, which is used to record jobs that have finished running; when the real-time database has the jobname table, it deletes the data in the jobname table; it passes the timestamp in the timestamp table as a variable to the SQL statement, extracts business data from the mirror database according to the timestamp, aggregates the business data and inputs it into the job flow, transforms and processes the data in the job flow and loads the data, and synchronizes it to the historical database.

[0031] In one possible implementation of the second aspect, the aggregation unit is specifically used to select the output fields to be output; sort the data in the job flow according to preset field values ​​to obtain a sequence field; establish a mapping relationship between the output fields and the sequence field; determine the fields that need to be updated and / or the fields that need to be inserted in the sequence field according to preset key fields; update the target table in the real-time database according to the fields that need to be updated, and / or insert data into the target table in the real-time database according to the fields that need to be inserted.

[0032] In one possible implementation of the second aspect, the aggregation unit is further configured to store the names of completed jobs in the jobname table, determine whether all jobs have finished running based on the number of rows in the jobname table, and update the timestamps in the timestamp table when all jobs have finished running or when a preset time threshold has been exceeded.

[0033] In one possible implementation of the second aspect, the aggregation unit is further configured to create a timestamp table and initialize a timestamp when the real-time library does not have the timestamp table; and to create a jobname table when the real-time library does not have the jobname table.

[0034] By establishing real-time, historical, current, and repaired historical databases, the extraction, processing, multi-dimensional fusion, and aggregation of statistical data to be collected in the mirror database, and by maintaining historical versions of the extracted data, the coupling between data statistics and data collection can be reduced, system pressure can be shared, the stability of the application system can be improved, and the timeliness of statistics in scenarios with a large data statistical scope can be enhanced. In addition, when key information in the data changes, a verification mechanism can be established to improve the accuracy of the data statistical results.

[0035] The advantages of additional aspects of the invention will be set forth in part in the description which follows, and in part will be obvious from the description, or may be learned by practice of the invention. Attached Figure Description

[0036] Figure 1 A flowchart illustrating an embodiment of the business data extraction method of the present invention;

[0037] Figure 2 A schematic diagram of a data warehouse structure provided for an embodiment of the business data extraction method of the present invention;

[0038] Figure 3 A schematic diagram illustrating the full extraction and implementation of the business data extraction method of the present invention;

[0039] Figure 4 A flowchart illustrating other embodiments of the business data extraction method of the present invention;

[0040] Figure 5 This is a schematic diagram of the structural framework provided for an embodiment of the business data extraction system of the present invention. Detailed Implementation

[0041] The principles and features of the present invention are described below with reference to the accompanying drawings. The embodiments described are only for explaining the present invention and are not intended to limit the scope of the present invention.

[0042] like Figure 1The diagram shown is a flowchart illustrating an embodiment of the business data extraction method of the present invention. The business data extraction method includes:

[0043] S1. Establish a data warehouse, which includes a real-time warehouse, a historical warehouse, a today warehouse, and a repaired historical warehouse. The real-time warehouse is used to store all aggregated data, the historical warehouse is used to store aggregated data except for the target day, the today warehouse is used to update all aggregated data generated on the target day, and the repaired historical warehouse is used to update all aggregated data within a preset number of days.

[0044] It should be noted that the target day refers to the day the data is stored. In addition to the four databases, the data warehouse can also be configured with other structures according to actual needs.

[0045] For example, such as Figure 2 The diagram illustrates an exemplary data warehouse structure, which may include a source database layer, a data acquisition layer, a data storage and analysis layer, and a data application layer. The source database layer uses a unified aggregation database, which can later be transferred to a standard database. The data acquisition layer collects data from the source database layer and stores it in the data storage layer; most operations are performed using ETL (Extract-Transform-Load, data warehouse technology). ETL describes the process of extracting, transforming, and loading data from the source to the destination. The data storage and analysis layer mainly includes a real-time database, a historical database, a current-day database, and a repaired historical database. The historical database, current-day database, and repaired historical database can store aggregated data, multidimensional data models, and business models. The real-time database stores aggregated data. The data application layer can be configured with report display functions, ad-hoc query functions, data analysis functions, and data mining functions.

[0046] As shown in Table 1, an example of the database functions of the data storage and analysis layer is provided, taking the dangerous goods data of the maritime administration's dangerous goods management system as an example.

[0047] Table 1. Functions of each database in the data storage and analysis layer

[0048]

[0049] Optionally, the fields in the real-time database may include: registered vessel counts, total registered vessel tonnage, vessel counts returning to China, total registered vessel tonnage, vessel counts registered in the free trade zone, total registered vessel tonnage in the free trade zone, vessel counts scrapped and deregistered, total scrapped and deregistered vessel tonnage, total number of vessels entering and leaving the port, total passenger volume of vessels entering and leaving the port, total cargo volume of vessels entering and leaving the port, total container volume of vessels entering and leaving the port, total number of vessels entering and leaving the port, total passenger volume of vessels entering and leaving the port, cargo volume of vessels entering and leaving the port, container volume of vessels entering and leaving the port, number of vessels under on-site supervision, number of vessels inspected by the FSC, and number of vessels inspected by the PSC.

[0050] S2 extracts business data from the production database and stores it in the mirror database;

[0051] S3 extracts the business data to be statistically analyzed from the mirror database into the real-time database, performs multi-dimensional processing and aggregation on the extracted business data in the real-time database, obtains aggregated data, and synchronizes it to the historical database.

[0052] It should be noted that the specific extraction strategy can be set according to actual needs. For example, to ensure statistical completeness, a full ETL process can be optionally used during extraction. Furthermore, to improve the timeliness of statistics in scenarios with a large data scope, the statistical scope can optionally be divided into finer-grained segments and cached in advance. Additionally, to ensure the accuracy of cached data, historical versions of the data can optionally be maintained and periodically backtracked.

[0053] like Figure 3 As shown, an exemplary full extraction and implementation diagram is provided, which illustrates the full extraction and real-time extraction from the aggregation database to the historical database.

[0054] S4, Today's database and the repair history database will synchronize the aggregated data to the history database according to the preset update rules.

[0055] Specifically, for the "Today" database, the entire day's data can be extracted at preset time intervals and compared with the tables in the "Today" database to update them. For example, the preset time interval could be 30 minutes. For the "Repair History" database, data for a preset number of days can be extracted daily based on timestamps to maintain data in the history database that has not changed due to changes in business system data. For example, the preset number of days could be 30 days.

[0056] The business data extraction method provided in this embodiment establishes a real-time database, a historical database, a today's database, and a repaired historical database. It extracts, processes, multi-dimensionally integrates, and aggregates the statistical data to be collected from the mirror database. It also maintains the historical versions of the extracted data. This reduces the coupling between data statistics and data collection, alleviates system pressure, improves the stability of the application system, and enhances the timeliness of statistics in scenarios with a large data statistical scope. In addition, when key information in the data changes, a verification mechanism is established to improve the accuracy of the data statistical results.

[0057] like Figure 4 The diagram shown illustrates a process flow for another embodiment of the business data extraction method of the present invention. Figure 4 The specific instructions for each step are shown in Table 2.

[0058] Table 2. Explanation of Business Data Extraction Methods and Steps

[0059]

[0060]

[0061]

[0062] The following is combined Figure 4 The following describes some possible embodiments of the present invention.

[0063] Alternatively, in some possible implementations, such as Figure 4 As shown, the business data to be statistically analyzed is extracted from the mirror database into the real-time database. The extracted business data in the real-time database undergoes multi-dimensional processing and aggregation to obtain aggregated data, which is then synchronized to the historical database. Specifically, this includes:

[0064] Determine if the real-time database has a timestamp table;

[0065] If the real-time library has a timestamp table, determine whether the real-time library has a jobname table, which is used to record jobs that have finished running;

[0066] If the real-time database has a jobname table, delete the data in the jobname table;

[0067] The timestamps in the timestamp table are passed as variables to the SQL statement. Business data is extracted from the mirror database according to the timestamps. The business data is aggregated and input into the workflow. The data in the workflow is transformed, processed, and loaded, and then synchronized to the historical database.

[0068] By passing the timestamps from the timestamp table as variables to the SQL statement, business data can be extracted from the mirror database according to the timestamps. This reduces the coupling between data statistics and data collection, alleviates system pressure, and improves the stability of the application system.

[0069] Alternatively, in some possible implementations, such as Figure 4 As shown, the data in the workflow undergoes transformation, processing, and loading, specifically including:

[0070] Select the output fields you want to output;

[0071] Sort the data in the workflow according to the preset field values ​​to obtain the sequence field;

[0072] Establish a mapping relationship between the output fields and the sequence fields, and determine the fields that need to be updated and / or inserted in the sequence fields based on the preset key fields;

[0073] Update the target table in the real-time database with the fields that need to be updated, and / or insert data into the target table in the real-time database with the fields that need to be inserted.

[0074] It should be understood that data in the workflow can be sorted in ascending or descending order. By transforming, processing, and loading the data in the workflow in the above manner, the timeliness of statistics in scenarios with a large data statistical range can be improved.

[0075] Alternatively, in some possible implementations, such as Figure 4 As shown, it also includes:

[0076] Store the names of completed jobs in the jobname table. Determine whether all jobs have finished running based on the number of rows in the jobname table. When all jobs have finished running or the preset time threshold has been exceeded, update the timestamps in the timestamp table.

[0077] Alternatively, in some possible implementations, such as Figure 4 As shown, it also includes:

[0078] If the real-time database does not have a timestamp table, create the timestamp table and initialize a timestamp.

[0079] If the jobname table does not exist in the real-time database, create the jobname table.

[0080] like Figure 5 The diagram shown illustrates the structural framework of an embodiment of the business data extraction system of the present invention. The system includes: a database construction unit, an extraction unit, an aggregation unit, and an update unit, wherein:

[0081] The database building unit is used to build a data warehouse. The data warehouse includes a real-time database, a historical database, a today database, and a repaired historical database. The real-time database is used to store all aggregated data, the historical database is used to store aggregated data except for the target day, the today database is used to update all aggregated data generated on the target day, and the repaired historical database is used to update all aggregated data within a preset number of days.

[0082] The extraction unit is used to extract business data from the production database and store it in the mirror database;

[0083] The aggregation unit is used to extract the business data to be statistically analyzed from the mirror database into the real-time database, perform multi-dimensional processing and aggregation on the business data extracted into the real-time database, obtain aggregated data, and synchronize it to the historical database.

[0084] The update unit is used to control the synchronization of aggregated data to the historical database according to preset update rules between the today database and the repair history database.

[0085] Optionally, in some possible implementations, the aggregation unit is specifically used to determine whether the real-time database has a timestamp table; when the real-time database has a timestamp table, it determines whether the real-time database has a jobname table, which is used to record jobs that have finished running; when the real-time database has a jobname table, it deletes the data in the jobname table; it passes the timestamps in the timestamp table as variables to the SQL statement, extracts business data from the mirror database according to the timestamps, aggregates the business data and inputs it into the job flow, transforms and processes the data in the job flow and loads the data, and synchronizes it to the historical database.

[0086] Optionally, in some possible implementations, the aggregation unit is specifically used to select the output fields to be output; sort the data in the workflow according to preset field values ​​to obtain sequential fields; establish a mapping relationship between the output fields and the sequential fields; determine the fields that need to be updated and / or the fields that need to be inserted in the sequential fields according to preset key fields; update the target table in the real-time database according to the fields to be updated, and / or insert the target table in the real-time database according to the fields to be inserted.

[0087] Optionally, in some possible implementations, the aggregation unit is also used to store the names of completed jobs in the jobname table, determine whether all jobs have finished running based on the number of rows in the jobname table, and update the timestamps in the timestamp table when all jobs have finished running or when a preset time threshold has been exceeded.

[0088] Optionally, in some possible implementations, the aggregation unit is also used to create a timestamp table and initialize a timestamp when the real-time library does not have a timestamp table; and to create a jobname table when the real-time library does not have a jobname table.

[0089] It should be understood that the above implementation methods are product implementation methods corresponding to the prior method implementation methods. For a description of the product implementation methods, please refer to the description of the prior method implementation methods, which will not be repeated here.

[0090] It should be understood that, without departing from the concept of the present invention, those skilled in the art can combine the above embodiments in any way, and all such combinations are within the protection scope of the present invention.

[0091] Readers should understand that in the description of this specification, the references to terms such as "one embodiment," "some embodiments," "example," "specific example," or "some examples," etc., refer to specific features, structures, materials, or characteristics described in connection with that embodiment or example, which are included in at least one embodiment or example of the present invention. In this specification, the illustrative expressions of the above terms do not necessarily refer to the same embodiment or example. Moreover, the specific features, structures, materials, or characteristics described may be combined in any suitable manner in one or more embodiments or examples. Furthermore, without contradiction, those skilled in the art can combine and integrate the different embodiments or examples described in this specification, as well as the features of different embodiments or examples.

[0092] In the several embodiments provided in this application, it should be understood that the disclosed apparatus and methods can be implemented in other ways. For example, the method embodiments described above are merely illustrative. For instance, the division of steps is only a logical functional division, and there may be other division methods in actual implementation. For example, multiple steps may be combined or integrated into another step, or some features may be ignored or not executed.

[0093] If the above methods are implemented as software functional units and sold or used as independent products, they can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of this invention, in essence, or the part that contributes to the prior art, or all or part of the technical solution, can be embodied in the form of a software product. This computer software product is stored in a storage medium and includes several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute all or part of the steps of the methods of the various embodiments of this 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.

[0094] The above are merely specific embodiments of the present invention, but the scope of protection of the present invention is not limited thereto. Any person skilled in the art can easily conceive of various equivalent modifications or substitutions within the technical scope disclosed in the present invention, and these modifications or substitutions should all be covered within the scope of protection of the present invention. Therefore, the scope of protection of the present invention should be determined by the scope of the claims.

Claims

1. A method for extracting business data, characterized in that, include: Establish a data warehouse, which includes a real-time warehouse, a historical warehouse, a today warehouse, and a repaired historical warehouse. The real-time warehouse is used to store all aggregated data, the historical warehouse is used to store aggregated data except for the target day, the today warehouse is used to update all aggregated data generated on the target day, and the repaired historical warehouse is used to update all aggregated data within a preset number of days. Business data is extracted from the production database and stored in the mirror database; The business data to be statistically analyzed in the mirror database is extracted into the real-time database. The extracted business data in the real-time database is then processed and aggregated in multiple dimensions to obtain aggregated data, which is then synchronized to the historical database. The today database and the repair history database synchronize the aggregated data to the history database according to preset update rules; The business data to be statistically analyzed is extracted from the mirror database and transferred to the real-time database. The extracted business data in the real-time database undergoes multi-dimensional processing and aggregation to obtain aggregated data, which is then synchronized to the historical database. Specifically, this includes: Determine whether the real-time database has a timestamp table; When the real-time library has the timestamp table, determine whether the real-time library has a jobname table, which is used to record jobs that have finished running; When the real-time database has the jobname table, delete the data in the jobname table; The timestamps in the timestamp table are passed as variables to the SQL statement. Business data is extracted from the mirror database according to the timestamps. The business data is aggregated and input into the workflow. The data in the workflow is transformed, processed, and loaded, and then synchronized to the historical database.

2. The business data extraction method according to claim 1, characterized in that, The data in the workflow is transformed, processed, and loaded, specifically including: Select the output fields you want to output; The data in the workflow is sorted according to preset field values ​​to obtain a sequence field; Establish a mapping relationship between the output fields and the sequence fields, and determine the fields that need to be updated and / or inserted in the sequence fields based on preset key fields; Update the target table in the real-time database according to the fields that need to be updated, and / or insert data into the target table in the real-time database according to the fields that need to be inserted.

3. The business data extraction method according to claim 2, characterized in that, Also includes: The names of completed jobs are stored in the jobname table. The number of rows in the jobname table is used to determine whether all jobs have finished running. When all jobs have finished running or a preset time threshold has been exceeded, the timestamps in the timestamp table are updated.

4. The business data extraction method according to any one of claims 1 to 3, characterized in that, Also includes: When the real-time library does not have the timestamp table, create the timestamp table and initialize a timestamp; If the real-time database does not have the jobname table, create the jobname table.

5. A business data extraction system, characterized in that, include: The system comprises a database creation unit, an extraction unit, an aggregation unit, and an update unit, among which: The database building unit is used to build a data warehouse, which includes a real-time database, a historical database, a today database, and a repaired historical database. The real-time database is used to store all aggregated data, the historical database is used to store aggregated data except for the target day, the today database is used to update all aggregated data generated on the target day, and the repaired historical database is used to update all aggregated data within a preset number of days. The extraction unit is used to extract business data from the production database and store it in the mirror database; The aggregation unit is used to extract the business data to be statistically analyzed from the mirror database into the real-time database, perform multi-dimensional processing and aggregation on the business data extracted into the real-time database, obtain aggregated data, and synchronize it to the historical database. The update unit is used to control the today database and the repair history database to synchronize the aggregated data to the history database according to the preset update rules; The aggregation unit is specifically used to determine whether the real-time database has a timestamp table; when the real-time database has the timestamp table, it determines whether the real-time database has a jobname table, which is used to record completed jobs; when the real-time database has the jobname table, it deletes the data in the jobname table; it passes the timestamp in the timestamp table as a variable to the SQL statement, extracts business data from the mirror database according to the timestamp, aggregates the business data and inputs it into the job flow, transforms and processes the data in the job flow and loads the data, and synchronizes it to the historical database.

6. The business data extraction system according to claim 5, characterized in that, The aggregation unit is specifically used to select the output fields that need to be output; sort the data in the workflow according to preset field values ​​to obtain the sequence fields; establish a mapping relationship between the output fields and the sequence fields; and determine the fields that need to be updated and / or inserted in the sequence fields based on preset key fields. Update the target table in the real-time database according to the fields that need to be updated, and / or insert data into the target table in the real-time database according to the fields that need to be inserted.

7. The business data extraction system according to claim 6, characterized in that, The aggregation unit is also used to store the names of completed jobs in the jobname table, determine whether all jobs have finished running based on the number of rows in the jobname table, and update the timestamps in the timestamp table when all jobs have finished running or when a preset time threshold has been exceeded.

8. The business data extraction system according to any one of claims 5 to 7, characterized in that, The aggregation unit is also used to create a timestamp table and initialize a timestamp when the real-time library does not have the timestamp table; and to create the jobname table when the real-time library does not have the jobname table.

Images