Kettle-based automatic data extraction and integration method and system

Abstract

The invention relates to a Kettle-based automatic data extraction and integration method and system, and the method comprises the following steps: configuring the basic information of a business library and a query library through a Kettle tool, and creating the connection between the business library and the query library; extracting corresponding information from the plurality of business libraries according to task requirements; decrypting the extracted information in the service library by using an analysis program, and storing the decrypted data in a query library; and carrying out verification and deduplication operation on the decrypted data, carrying out standardization processing on the data, and synchronizing data contents to a query library. The method is suitable for obtaining medical data from different data sources, and the medical data extraction and integration efficiency is improved.

Description

Technical Field

[0001] The present application relates to the field of computer technology, and more specifically, to a Kettle-based automated data extraction and integration method and system. Background Art

[0002] As the amount of business data related to medical bills accumulates and increases annually, the National Health Commission needs to analyze this data across various dimensions to support decision-making and policymaking. Current medical data synchronization faces the following challenges: Support for a single data source: The current process only supports data acquisition from a single source, which limits the system's ability to process multiple data sources and makes it difficult to integrate data from different sources for unified analysis. Low data utilization: This low data utilization can lead to duplicate data processing or omission of important information, impacting data processing efficiency and accuracy. Long processing times and poor real-time performance: Due to the inefficiency of the existing process, data processing takes a long time, failing to meet the needs of real-time query and analysis. This can affect business responsiveness. Complex processes and difficult maintenance: The addition of new businesses requires frequent modifications to operational processes, making development and maintenance cumbersome and making it difficult to quickly respond to changing business needs. There is an urgent need to optimize the medical data synchronization process and algorithms to improve extraction efficiency and query experience.

[0003] Existing technologies such as the Chinese patent with the patent number "CN114300144A" disclose a standard method for the data source interface of a medical data mining and application system and device. The method includes the following steps: (1) configuring a unique identification code for the data source; (2) when the data source accesses the system, the data source submits the identification code to the system for authentication, the system verifies the unique identification code, and feedbacks the authorization authentication status to the data source, if the data source is authorized. The present invention configures a unique identification code for the data source, and all data sources use a unified standard identification code, so that during the data upload process, it is convenient for the MRSS system to verify the data source, and the data source that has passed the verification will be gradually verified according to the identification code when uploading and checking data to ensure the security of the data source. At the same time, the unified standard interface facilitates the access to massive data, so that the MRSS system data mining discovers more and more information, and the scope of scientific research and the population that can be supported are becoming more and more extensive.

[0004] The problems existing in the above prior art are:

[0005] 1. As technology continues to evolve and new data sources emerge, the system may need to be constantly adjusted and updated to accommodate new data source types and interface standards. This may lead to compatibility issues and increase system upgrade and maintenance costs.

[0006] 2. Integration with other systems: If integration with other medical information systems or platforms is required, a unified standard interface may not necessarily meet all integration requirements. Differences between different systems can lead to integration difficulties, limiting the system's scalability and interoperability. Summary of the Invention

[0007] In order to solve the above technical problems, the present invention proposes a Kettle-based automated data extraction and integration method and system.

[0008] The technical solutions of the present invention are as follows:

[0009] The present invention proposes a Kettle-based automated data extraction and integration method, comprising the following steps:

[0010] Step S1: Use the Kettle tool to configure the basic information of the business library and query library, and create a connection between the business library and query library;

[0011] Step S2: extract corresponding information from multiple business libraries according to task requirements;

[0012] Step S3, using the parsing program to decrypt the extracted information in the business database, and storing the decrypted data in the query database;

[0013] Step S4: Verify and deduplicate the decrypted data, standardize the data, and synchronize the data content to the query library.

[0014] As a preferred implementation, the basic information of the business library and query library is configured, wherein the types of the business library and query library include: ClickHouse, MySQL, Oracle or DAMO.

[0015] As a preferred implementation, the basic information of the business library and query library is configured, including: database type of the business library and query library, database URL, database driver class, database account password, extraction mode, and extraction area.

[0016] As a preferred implementation, in the process of extracting corresponding information from multiple business libraries and integrating it into the query library, an adaptive SQL statement is automatically generated based on the configured database information and task requirements.

[0017] As a preferred embodiment, the extraction mode includes: full extraction and breakpoint resume extraction; wherein, in the full extraction mode, the task is extracted from the beginning according to the extraction task requirements; in the breakpoint resume extraction mode, the extraction is continued from the point where the last extraction task was interrupted.

[0018] On the other hand, the present invention also provides an automated data extraction and integration system based on Kettle, comprising:

[0019] The data source management module pre-sets database information according to the system configuration, including detailed information of the business database and query database; and realizes the connection between the business database and query database through the set database information and communication interface;

[0020] The data extraction module supports extracting data from multiple business databases at the same time according to task requirements;

[0021] The data parsing module uses the parsing program to decrypt the extracted information in the business database and stores the decrypted data in the query database;

[0022] Clean the storage module, verify and deduplicate the decrypted data, standardize the data, and synchronize the data content to the query library.

[0023] As a preferred embodiment, the data extraction module includes: a database SQL adaptation unit and a database sub-table name parsing unit; wherein the database SQL adaptation unit automatically generates an adapted SQL statement based on the configured database information and task requirements; the SQL statement is customized for the source data and can extract the required data from different types of data sources; the database sub-table name parsing unit configures the sub-table rules and the original table name according to the task requirements, and automatically obtains the corresponding sub-table name for data partition storage and management.

[0024] As a preferred embodiment, the cleaning storage module includes a data synchronization mode management unit for synchronizing data between multiple databases, adjusting configuration according to task requirements, and standardizing multiple data formats.

[0025] On the other hand, the present invention further provides an electronic device having a computer program stored thereon, wherein when the computer program is executed by a processor, the Kettle-based automated data extraction and integration method as described in any embodiment of the present invention is implemented.

[0026] On the other hand, the present invention also provides a computer-readable medium for storing one or more programs, which, when executed by the one or more processors, enables the one or more processors to implement a Kettle-based automated data extraction and integration method as described in any embodiment of the present invention.

[0027] The present invention has the following beneficial effects:

[0028] Improved data processing efficiency: By pre-setting data source information and supporting collaborative processing of multiple data sources, this invention can efficiently extract and aggregate data from multiple sources, providing powerful support for business data processing in the medical industry. Verification date calculation and SQL adaptation functions further ensure the continuity and efficiency of data extraction.

[0029] Enhanced data management flexibility: Configurable table sharding strategies and data synchronization modes enable the system to adapt to varying data growth and access requirements. Automated table sharding and synchronization management reduces manual intervention and increases data management flexibility.

[0030] Ensure data consistency and availability: Supports data synchronization between multiple databases and can be configured according to actual needs, ensuring data consistency and availability across different platforms. This is crucial for the storage and subsequent analysis of medical data.

[0031] Support for personalized data extraction: Users can flexibly configure various aspects of data extraction according to specific business needs, greatly improving the pertinence and efficiency of data processing. BRIEF DESCRIPTION OF THE DRAWINGS

[0032] In order to more clearly illustrate the technical solutions of the embodiments of the present application, the following is a brief introduction to the drawings required for use in the embodiments of the present application. It should be understood that the following drawings only show certain embodiments of the present application and therefore should not be regarded as limiting the scope. For ordinary technicians in this field, other relevant drawings can be obtained based on these drawings without creative work.

[0033] Figure 1 This is a schematic flow chart of the method of Example 1;

[0034] Figure 2 A flow chart for implementing data extraction in the present invention;

[0035] Figure 3 This is the flow chart for extracting bill information. DETAILED DESCRIPTION

[0036] The following will clearly and completely describe the technical solutions in the embodiments of the present invention in conjunction with the accompanying drawings. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. All other embodiments obtained by ordinary technicians in this field based on the embodiments of the present invention without making any creative efforts shall fall within the scope of protection of the present invention.

[0037] It should be understood that the step numbers used herein are only for convenience of description and are not intended to limit the order in which the steps are to be executed.

[0038] It should be understood that the terms used in the present specification are only for the purpose of describing specific embodiments and are not intended to limit the present invention. As used in the present specification and the appended claims, the singular forms "a", "an" and "the" are intended to include the plural forms unless the context clearly indicates otherwise.

[0039] The terms “include” and “comprising” indicate the presence of described features, integers, steps, operations, elements and / or components, but do not preclude the presence or addition of one or more other features, integers, steps, operations, elements, components and / or groups thereof.

[0040] The term "and / or" refers to and includes any and all possible combinations of one or more of the associated listed items.

[0041] Example 1:

[0042] In order to make the purpose, technical solutions and advantages of the present invention more clear, the following will be combined with the specific embodiments of the present application and refer to the attached Figure 1 , clearly and completely describe the technical solution of the present invention.

[0043] To solve the problems of the prior art, the present invention provides an automated data extraction and integration method based on Kettle, comprising the following steps:

[0044] Step S1: Use the Kettle tool to configure the basic information of the business library and query library, and create a connection between the business library and query library;

[0045] Before running the extraction job, you need to use the Kettle tool to configure the basic information of the business library and query library. The configuration content includes the input and output database types, database URL, database driver class, database account and password, extraction mode, extraction partition, etc. When the extraction mode is multiple data sources, you need to upload the database information to the query library and configure multiple input data sources (business libraries). The data verification history number of days is used to determine the verification day range of business data. The business data partitioning strategy is used to determine the business data partitioning rules. The data range to be extracted can be configured as full data or partial partitioned data.

[0046] Extraction mode includes: full extraction and breakpoint resume extraction; in full extraction mode, the extraction task starts from the beginning according to the extraction task requirements; in breakpoint resume extraction mode, the extraction task continues from the point where the last extraction task was interrupted.

[0047] Step S2: extract corresponding information from multiple business libraries according to task requirements;

[0048] Step S201: Select the data synchronization mode based on the query database type. If the query database is ClickHouse, it is necessary to synchronize basic information tables in the business database, such as the unit table, bill type table, billing point table, district information table, and configuration tables such as medical institution classification and project type connection, to ClickHouse for subsequent analysis and processing. If it is a traditional relational database, go directly to the aggregation step.

[0049] Step S202, automatic table structure generation. By configuring the table name, the system can automatically read metadata such as the source database table field, number type, field length, etc. Due to differences in data types and underlying data structures of different databases, the corresponding table creation job will be run according to the type of input database to generate the table structure Sql of the original table in ClickHouse and run. During the first synchronization, the system will extract the data from the source database to the ClickHouse database. During subsequent synchronization processes, the system will automatically check the changes in the table structure and data records to determine whether resynchronization is required.

[0050] Step S203, real-time operation process: the operation frequency can be customized, and the default is once every five minutes. The business data is analyzed in real time, and the real-time business data of the day is incrementally extracted to the query library. According to the configured business rules, the data is matched by amount, payer, custom fields, etc., and finally the data that meets the rules is output. This can not only improve the real-time performance of the system query but also discover business data that meets the rules.

[0051] Further, such as Figure 3 As shown in FIG, the specific process of extracting bill information in this embodiment is as follows:

[0052] Configure partition filtering conditions: Filter business data based on the configuration. You can configure full data or partial partition data. When extracting full data, no processing is performed. When configuring partial partition data, SQL is generated based on the configured partition information. When querying the source database, the query range is specified by splicing SQL to meet data permission issues.

[0053] Extraction date calculation: The business system contains a large amount of bill and HIS-related business data, which increases daily. Processing business data by date can improve data processing efficiency. The extraction job supports two data extraction modes: full extraction and breakpoint-resume extraction. In full extraction mode, the system will re-extract all data based on the date starting from the initial date of the business. If the task is interrupted, the program will enter breakpoint-resume extraction mode the next time it runs, and continue the extraction operation from the last interrupted date, preventing data re-extraction and improving extraction efficiency.

[0054] Cycle date: Cycle the obtained date range by day and execute the extraction task every day.

[0055] Determine whether there is multiple data: When the extraction mode is single data source extraction mode, extract from the input data source (business database) to the output data source (query database). When the extraction mode is multiple data sources, query the database to obtain information from multiple data sources, and execute the following extraction tasks in parallel based on these data source information

[0056] The process of extracting HIS data and extracting redemption ticket information is similar to the bill data processing.

[0057] Acquiring SQL statements based on synchronization modes: Defined data sources are divided into source databases (business data sources) and query libraries. Based on the configured data source information, the system can obtain SQL statements that adapt to the data source. This allows the system to synchronize and extract data between multiple databases, including source databases such as MySQL, Oracle, and DM. The target query database can be ClickHouse, MySQL, Oracle, or DM. The system can successfully handle multiple data synchronization modes, ensuring data consistency and availability across different database platforms.

[0058] Step S3, using the parsing program to decrypt the extracted information in the business database, and storing the decrypted data in the query database;

[0059] The parser is an independently deployed service used to parse the manifest file in the file system (MINIO), decrypt the business data contained therein, and decrypt the business manifest JSON file, and finally store it in the query library for analysis. The parser uses multi-threading technology to improve efficiency. Multiple threads are configured to query the storage location of each business data file from the business library. Then one thread is responsible for parsing the data. Read-write separation is used to increase processing speed. The parsing thread obtains the decryption key of the current business data, obtains the business JSON file from the file system, decrypts the business data, and parses the data into rows and stores them in the database. It then calls Kettle's job to compare the business data stored in the database. According to the preset amount range, the data that does not exist in the range is output to the abnormal business table of the query library.

[0060] Step S4: Verify and deduplicate the decrypted data, standardize the data, and synchronize the data content to the query library.

[0061] Retrieving table names based on date sharding: Business data storage utilizes sharding technology. When extracting this data, it's processed according to configured sharding rules, which include the original table name, sharding strategy, and sharding start date. When extracting data from a specific business date, if this date is greater than the sharding start date, the corresponding sharded table name is automatically retrieved based on the query date and table name. Finally, the sharded table data can be aggregated into a query library, improving data query and operation efficiency.

[0062] Get data from the master table and detail table: Business data is divided into the master table and detail table. Run the spliced ​​SQL to get data separately, clean the data, remove duplicate data, and finally write the data in batches to the temporary table of the query library to improve data storage efficiency. After the data is processed, transfer the data from the temporary table to the formal table by replacing the partition. This does not affect the query of the formal table and also improves the data processing speed.

[0063] Summary data: Summary data consists of two parts. The first part is to query the data of the master table and detail table, and then connect the master table and detail table to generate a wide table. The second part is to perform group calculations in memory based on pre-set statistical dimensions, and finally store them in the query library.

[0064] Example 2:

[0065] This embodiment provides an automated data extraction and integration system based on Kettle, including:

[0066] The data source management module pre-sets database information according to the system configuration, including detailed information of the business database and query database; and realizes the connection between the business database and query database through the set database information and communication interface;

[0067] The data extraction module supports extracting data from multiple business databases at the same time according to task requirements;

[0068] The data parsing module uses the parsing program to decrypt the extracted information in the business database and stores the decrypted data in the query database;

[0069] Clean the storage module, verify and deduplicate the decrypted data, standardize the data, and synchronize the data content to the query library.

[0070] Example 3:

[0071] This embodiment provides an electronic device having a computer program stored thereon. When the computer program is executed by a processor, the Kettle-based automated data extraction and integration method as described in any embodiment of the present invention is implemented.

[0072] Example 4:

[0073] This embodiment provides a computer-readable medium for storing one or more programs. When the one or more programs are executed by the one or more processors, the one or more processors implement a Kettle-based automated data extraction and integration method as described in any embodiment of the present invention.

[0074] In the embodiments of the present application, "at least one" refers to one or more, and "more" refers to two or more. "And / or" describes the association relationship of associated objects, indicating that three relationships may exist. For example, A and / or B can represent the existence of A alone, the existence of A and B at the same time, and the existence of B alone. Among them, A and B can be singular or plural. The character " / " generally indicates that the previous and next associated objects are in an "or" relationship. "At least one of the following" and similar expressions refer to any combination of these items, including any combination of single or plural items. For example, at least one of a, b and c can represent: a, b, c, a and b, a and c, b and c or a and b and c, where a, b, c can be single or multiple.

[0075] Those skilled in the art will appreciate that the various units and algorithm steps described in the embodiments disclosed herein can be implemented using a combination of electronic hardware, computer software, and electronic hardware. Whether these functions are performed in hardware or software depends on the specific application and design constraints of the technical solution. Professionals and technicians can use different methods to implement the described functions for each specific application, but such implementation should not be considered beyond the scope of this application.

[0076] Those skilled in the art will clearly understand that, for the convenience and brevity of description, the specific working processes of the systems, devices and units described above can refer to the corresponding processes in the aforementioned method embodiments and will not be repeated here.

[0077] In the several embodiments provided in this application, if any function is implemented in the form of a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of this application, or the part that contributes to the prior art, or the part of the technical solution, can be embodied in the form of a software product. The computer software product is stored in a storage medium and includes several instructions for enabling a computer device (which can be a personal computer, server, or network device, etc.) to execute all or part of the steps of the method described in each embodiment of this application. The aforementioned storage medium includes: U disk, mobile hard disk, read-only memory (Read-Only Memory; hereinafter referred to as: ROM), random access memory (Random Access Memory; hereinafter referred to as: RAM), magnetic disk or optical disk, and other media that can store program code.

[0078] The above descriptions are merely embodiments of the present invention and are not intended to limit the patent scope of the present invention. Any equivalent structure or equivalent process transformation made using the contents of the present invention's description and drawings, or directly or indirectly applied in other related technical fields, are also included in the patent protection scope of the present invention.

Claims

1. A Kettle-based automated data extraction and integration method, characterized in that: The following steps are involved: Step S1: Use the Kettle tool to configure the basic information of the business library and query library, and create a connection between the business library and query library; Step S2: extract corresponding information from multiple business libraries according to task requirements; Step S3, using the parsing program to decrypt the extracted information in the business database, and storing the decrypted data in the query database; Step S4: Verify and deduplicate the decrypted data, standardize the data, and synchronize the data content to the query library.

2. The automated data extraction and integration method based on Kettle according to claim 1, characterized in that: The basic information of the business library and query library is configured, wherein the business library and query library types include: ClickHouse, MySQL, Oracle or DAMO.

3. The automated data extraction and integration method based on Kettle according to claim 1, characterized in that: The basic information of the business library and query library is configured, including: the database type of the business library and query library, database URL, database driver class, database account password, extraction mode, and extraction area.

4. The automated data extraction and integration method based on Kettle according to claim 1, characterized in that: In the process of extracting corresponding information from multiple business databases and integrating it into the query database, adaptive SQL statements are automatically generated based on the configured database information and task requirements.

5. The Kettle-based automated data extraction and integration method according to claim 3, characterized in that: The extraction mode includes: full extraction and breakpoint resume extraction; wherein, in the full extraction mode, the task is extracted from the beginning according to the extraction task requirements; in the breakpoint resume extraction mode, the extraction is continued from the point where the last extraction task was interrupted.

6. An automated data extraction and integration system based on Kettle, characterized in that: include: The data source management module pre-sets database information according to the system configuration, including detailed information of the business database and query database; The connection between the business database and the query database is achieved through the set database information and communication interface; The data extraction module supports extracting data from multiple business databases at the same time according to task requirements; The data parsing module uses the parsing program to decrypt the extracted information in the business database and stores the decrypted data in the query database; Clean the storage module, verify and deduplicate the decrypted data, standardize the data, and synchronize the data content to the query library.

7. The Kettle-based automated data extraction and integration system according to claim 6, characterized in that: The data extraction module includes: a database SQL adapter unit and a database table name parsing unit; wherein the database SQL adapter unit automatically generates adapted SQL statements based on the configured database information and task requirements; the SQL statements are customized for the source data and can extract the required data from different types of data sources; the database table name parsing unit configures the table sharding rules and original table names according to the task requirements, and automatically obtains the corresponding table sharding names for data partition storage and management.

8. The Kettle-based automated data extraction and integration system according to claim 6, characterized in that: The cleaning storage module includes a data synchronization mode management unit, which is used for data synchronization between multiple databases, adjusting the configuration according to task requirements, and standardizing multiple data formats.

9. An electronic device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein: When the processor executes the program, it implements the Kettle-based automated data extraction and integration method as described in any one of claims 1 to 5.

10. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the computer program is executed by a processor, it implements a Kettle-based automated data extraction and integration method as described in any one of claims 1 to 5.

Images