CN114942953A

CN114942953A - Cross-system data updating and querying method and related equipment

Info

Publication number: CN114942953A
Application number: CN202210332054.2A
Authority: CN
Inventors: 马德民; 曲明钰; 王佳玺; 陆智卿
Original assignee: China Life Insurance Co ltd
Current assignee: China Life Insurance Co ltd
Priority date: 2022-03-30
Filing date: 2022-03-30
Publication date: 2022-08-26

Abstract

The application provides a cross-system data updating query method and related equipment, wherein the method comprises the following steps: establishing a standard table structure of a database, and establishing connection with a plurality of source systems; storing stock data in different source systems into the database, and integrating the stock data of the same category according to the standard table structure to form a unified data table; transferring the incremental data in different source systems to a message queue system to form a message queue, and sequentially storing the incremental data into corresponding data tables; and establishing an index corresponding to the data table, and performing data query according to the index. The cross-system data updating query method and the related equipment have the advantages of high query efficiency and good user experience, can efficiently query while updating data, support cross-system integration and updating storage of mass data, and provide a solution for multi-dimensional query.

Description

Cross-system data updating query method and related equipment

Technical Field

The present application relates to the field of data query technologies, and in particular, to a cross-system data update query method and related devices.

Background

When a front-end user inquires data, massive data is required to be searched, the data of the same type is required to be stored in different source systems, the data are scattered, cross-system searching is required, the inquiry time consumption is increased, the data storage formats in each source system are different, the stored data can be updated in real time, complex inquiry sentences are required to be constructed during inquiry, even if a big data technology is used, efficient inquiry while data updating is still difficult to achieve, and a multi-dimensional inquiry scene cannot be supported.

Disclosure of Invention

In view of the above, an objective of the present invention is to provide a cross-system data update query method and related apparatus to solve the above technical problems.

In a first aspect of the present application, a cross-system data update query method is provided, including: establishing a standard table structure of a database, and establishing connection with a plurality of source systems; storing stock data in different source systems into the database, and integrating the stock data of the same category according to the standard table structure to form a unified data table; transferring the incremental data in different source systems to a message queue system to form a message queue, and sequentially storing the incremental data into corresponding data tables; and establishing an index corresponding to the data table, and performing data query according to the index.

Further, the database is OCeanBase.

Further, the standard table structure includes a table name, a table field and a table record, the table name corresponds to the category, and the type of the table field includes a data source and a storage time.

Further, the storing inventory data in different source systems into the database includes: and concurrently transferring the stock data in the source system by adopting a DataX tool, wherein the concurrent transfer comprises starting a plurality of task groups, each task group comprises a plurality of subtasks, each task group is responsible for transferring all the stock data of one table in the source system, and each subtask is responsible for transferring part of the stock data in one table.

Further, the message queue system is Kafka, and the incremental data in the message queue are arranged according to the sequence of incremental time.

Further, said sequentially storing said incremental data into respective said data tables, comprising: and sequentially reading the incremental data in the message queue by adopting a spark Steaming program, and storing the incremental data into the corresponding data table according to the type of the incremental data.

Further, the establishing of the index corresponding to the data table includes: and asynchronously establishing the index according to the table field of the data table.

Further, the querying data according to the index includes: and accessing the database by adopting a front-end checking service system, executing SQL query statements according to the index, and querying the data stored in the database.

And querying the data stored in the database.

In a second aspect of the present application, a cross-system data update query apparatus is provided, including: an initial module configured to set up a standard table structure of a database and establish a connection with a plurality of source systems; the integration module is configured to store stock data of the same type in different source systems into the database and integrate the stock data to form a unified data table according to the standard table structure; the updating module is configured to transfer the incremental data in different source systems to a message queue system to form a message queue, and store the incremental data in the corresponding data tables in sequence; and the query module is configured to establish an index corresponding to the data table and perform data query according to the index.

In a third aspect of the present application, there is provided an electronic device, including a memory, a processor, and a computer program stored on the memory and executable on the processor, wherein the processor implements the cross-system data update query method according to the first aspect when executing the computer program.

In a fourth aspect of the present application, there is provided a non-transitory computer readable storage medium storing computer instructions for causing the computer to execute the cross-system data update query method according to the first aspect.

From the above, the application provides a cross-system data updating query method and related equipment, and by setting a database, data of different source systems can be stored together, so that cross-system query is avoided; by setting the standard table structure, formats of data stored by different source systems can be unified, the query difficulty is reduced, and the query efficiency is improved; by setting the message queue, incremental data of different source systems can be sequentially stored in the database, so that data updating of the existing data table is realized, the format is unified, and the branches, the number and the nesting layer number of query statements are effectively reduced; by establishing corresponding indexes for the data table, query operation is facilitated, and multi-dimensional query efficiency is improved; the cross-system data updating query method and the related equipment have high query efficiency and good user experience, can perform efficient query while updating data, support cross-system integration and updating storage of mass data, and provide a solution for multi-dimensional query.

Drawings

In order to more clearly illustrate the technical solutions in the present application or the related art, the drawings needed to be used in the description of the embodiments or the related art will be briefly introduced below, and it is obvious that the drawings in the following description are only embodiments of the present application, and it is obvious for those skilled in the art that other drawings can be obtained according to these drawings without creative efforts.

Fig. 1 is a schematic flowchart of a cross-system data update query method according to an embodiment of the present application;

FIG. 2 is a schematic diagram illustrating a cross-system data update query method according to an embodiment of the present disclosure;

fig. 3 is a schematic structural diagram of a cross-system data update query device according to an embodiment of the present application;

fig. 4 is a schematic structural diagram of an electronic device according to an embodiment of the present application.

Detailed Description

In order to make the objects, technical solutions and advantages of the present application more apparent, the present application is further described in detail below with reference to specific embodiments and the accompanying drawings.

It should be noted that technical terms or scientific terms used in the embodiments of the present application should have a general meaning as understood by those having ordinary skill in the art to which the present application belongs, unless otherwise defined. The use of "first," "second," and similar terms in the embodiments of the present application do not denote any order, quantity, or importance, but rather the terms are used to distinguish one element from another. The word "comprising" or "comprises", and the like, means that the element or item listed before the word covers the element or item listed after the word and its equivalents, but does not exclude other elements or items. The terms "connected" or "coupled" and the like are not restricted to physical or mechanical connections, but may include electrical connections, whether direct or indirect.

When a front-end user queries data, massive data are often searched, the data of the same type are often stored in different source systems, the data are scattered, cross-system searching is needed, time consumption of querying is increased, data storage formats in each source system are different, the stored data can be updated in real time, complex query sentences need to be constructed during querying, efficient querying is still difficult to perform while the data are updated even if a big data technology is used, and a multi-dimensional query scene cannot be supported.

For example, in the insurance industry, policy information is queried from a sales front end or a contract platform, policy Data is distributed in different source systems, the source systems include, for example, an SMDS (Switched multi-megabit Data Service) system or a Sap system, then multiple source systems need to be searched separately, and the policies have different storage formats in different source systems, new policies are continuously added in different source systems, a complex nested Query statement needs to be constructed when SQL (Structured Query Language) is used, the Query process is time consuming and inefficient, and particularly for some multi-dimensional Query scenarios, for example, the Query condition is a dynamic combination of multiple conditions, the Query statement becomes complex due to too many branches and multiple nesting of IF statements, and the Query operation is difficult to implement.

In the process of implementing the application, it is found that setting a unified database between the source system and the front end can be considered, synchronizing data information of different source systems into the database to be stored in a unified format, and then querying data in the database, so that the number of branches and nested layers of query statements can be effectively reduced, and the query efficiency is improved.

The technical solution of the present application is described in detail below by specific examples with reference to fig. 1 to 4.

Some embodiments of the present application provide a cross-system data update query method, as shown in fig. 1 and fig. 2, including the following steps:

and S1, establishing a standard table structure of the database, and establishing connection with a plurality of source systems.

A database is arranged between the front end and the source systems, and the database is connected with the source systems, so that the data of different source systems can be conveniently stored together in the follow-up process, cross-system query is avoided, and query sentences are simplified; by setting the standard table structure, formats of data of different source systems can be unified, query difficulty is reduced, and query efficiency is improved.

The front end is, for example, a contract platform, and the like, and is not particularly limited, the source System is, for example, an SMDS System or an Sap System, and is not particularly limited, the database may be OCeanBase, which is an enterprise-level Distributed relational database, and has a better update performance compared to HBase and HDFS (Hadoop Distributed File System).

And S2, storing the stock data in different source systems into the database, and integrating the stock data of the same category according to the standard table structure to form a unified data table.

The stock data refers to data held by a source system before a certain time node, the stock data of the same type are integrated according to a standard table structure to form a unified data table of the type, the unified data table avoids format differentiation of the stock data among different source systems, query statements are convenient to simplify, query efficiency is improved, the structures of the data tables of different types are also similar, and the query statements are further simplified to improve the query efficiency; step S2 may be understood as storing the existing data of the target category into the database for the first time, and initializing to form an initial data table containing all stock data of the category.

The category of the data is, for example, but not limited to, policy data, receipt and payment data, claim settlement data, or marketer data, and the corresponding data table is, for example, but not limited to, a policy table, a receipt and payment table, a claim settlement table, or a marketer table.

S3, transferring the incremental data in different source systems to a message queue system to form a message queue, and storing the incremental data in the corresponding data table in sequence.

Incremental data refers to the change of stock data in an internal source system in a certain period of time, namely the updating of the data, the updating of the data in different source systems is asynchronous, and by setting a message queue system, the incremental data of different source systems can form message queues in sequence and store the incremental data in the message queues into a database item by item, so that the problem of synchronous updating of mass data is solved, the format is unified, the number of branches and nested layers of query statements is effectively reduced, and the query efficiency is improved; step S3 may be understood as storing the target category increment data into the category data table existing in the database for data updating.

And S4, establishing an index corresponding to the data table, and performing data query according to the index.

The index is used for providing a pointer pointing to specified data stored in the data table, and can be sorted according to a specified sequence, so that SQL query statements corresponding to the data table can be executed more quickly, and specific information in the data table can be accessed quickly; corresponding indexes are established for the initial data table and the updated data table, query operation is convenient to conduct, on the basis that query statements are designed and simplified through the steps S1-S3, the step S4 can support a multi-dimensional query scene, the query conditions are dynamic combinations of multiple conditions, such as the guarantee amount, the guarantee time and the claim amount, and the like, specific limitations are not made, multiple nested query statements can be constructed, and multi-dimensional query efficiency is improved.

The cross-system data updating query method is high in query efficiency and good in user experience, can perform efficient query while updating data, supports cross-system integration and updating storage of mass data, and provides a solution for multi-dimensional query.

In some embodiments, the standard table structure includes a table name corresponding to the category of the data, a table field whose type includes a data source and a storage time, and a table record.

The table name is the category of the data table, which corresponds to the category of the data, such as a policy table, a receipt and payment table, a claim settlement table or a marketer table, and is not limited specifically; the table field can be understood as the column content of the data table, such as the name, age and the like of an applicant without specific limitation, and can be formulated by combining various requirements such as the function of a service, the expansion of data, the performance of query and use and the like; the table record can be understood as the row item content of the data table, namely the actual specific content; the data source comprises a source system where the data are located, a region where the data are located and the like, and the storage time is the time when the data are stored in the corresponding data table, wherein the data comprise stock data and incremental data.

When the stock data are integrated to form the data table, the fields of the stock data correspond to the table fields of the standard table structure to be correspondingly stored, the data sources and the storage time of the stock data are increased to form an initial data table of the category data, and the subsequent incremental data of different source systems can be stored into the initial data table of the corresponding category according to the data category to update the data.

In some embodiments, step S2 includes:

s201, concurrently transferring the stock data in the source system by adopting a DataX tool, wherein the concurrent transfer comprises starting a plurality of task groups, each task group comprises a plurality of subtasks, each task group is responsible for transferring all the stock data of one table in the source system, and each subtask is responsible for transferring part of the stock data in one table.

The data X is a tool for mutually transferring data in various types of databases, the transfer speed can be improved by adopting a concurrent transfer mode for stock data transfer, subtasks are the minimum units of data X operation, each subtask is responsible for transferring part of stock data in one table in a source system, a plurality of subtasks are assembled into Task groups, and each Task group is responsible for transferring all stock data in one table in the source system.

Each Task is started by a Task group, after the Task is started, a thread of a Reader-Channel-Writer is started to finish reading and writing data into an OCeanBase database, the concurrency of the tasks can be 20, the concurrency of the Task group can be 5, and the details are not limited.

In some embodiments, the message queue system is Kafka, and the incremental data in the message queue are arranged according to an order of increment time.

The incremental data of different source systems are arranged in the message queue system according to the sequence of the incremental time to form a message queue so as to transfer the incremental data in sequence; the message queue system can be Kafka, which is a distributed, high-throughput and high-expansibility message queue system, can support millions of messages per second, and has high stability and efficiency.

In some embodiments, said sequentially storing said incremental data into respective said data tables comprises: and sequentially reading the incremental data in the message queue by adopting a spark Steaming program, and storing the incremental data into the corresponding data table according to the type of the incremental data.

Spark Streaming is a real-time calculation program constructed on Spark, and can read incremental data in Kafka, store the incremental data in a corresponding data table according to the type of the incremental data, and update the data table.

In some embodiments, step S4 includes:

s401, the index is established asynchronously according to the table field of the data table.

The asynchronization and the synchronization are relative, the asynchronization is that the main thread of the calling method does not need to synchronously wait for the completion of another thread, so that the main thread can do other things, the establishment of the index in an asynchronization mode can be executed separately from the storage process of data, the increase of time consumption caused by the establishment of the index after the data is written or updated is avoided, the work efficiency is improved by asynchronously establishing the index, and the possibility of program deadlock is also reduced.

S402, accessing the database by adopting a front-end checking service system, executing SQL query statements according to the index, and querying the data stored in the database.

The front-end checking service system is built by adopting a micro-service SpringBoot framework, so that the downtime can be reduced, the high usability of the service can be kept, the database is accessed through the front-end checking service system, SQL query statements are executed according to the index, the data stored in the database is queried, and the query efficiency is improved.

In some embodiments, the principle of the cross-system data update query method is as shown in fig. 2, the architecture may be divided into four layers, the fourth layer is a data source layer, and the data source of the insurance industry is generally a service source system of each core, including the SMDS system 211 and the Sap system 212; the third layer is a data synchronization layer, and comprises the steps of transferring stock data of different source systems by adopting a DataX tool 221 and transferring incremental data of the different source systems by adopting a Kafka + SparkStreaming tool 222; the second layer is a data storage layer and comprises an OCeanBase database 23; the first layer is the application layer, which includes the front-end undersea service system 24.

Stock data and incremental data of different source systems are summarized and updated in the OCeanBase database 23, and data query is performed on the OCeanBase database 23 through the front-end downloading service system 24, so that query time consumption is reduced, and query efficiency is improved.

It should be noted that the above describes some embodiments of the present application. Other embodiments are within the scope of the following claims. In some cases, the actions or steps recited in the claims may be performed in a different order than in the embodiments described above and still achieve desirable results. In addition, the processes depicted in the accompanying figures do not necessarily require the particular order shown, or sequential order, to achieve desirable results. In some embodiments, multitasking and parallel processing may also be possible or may be advantageous.

Based on the same inventive concept, corresponding to the method of any of the above embodiments, the present application further provides another cross-system data update query apparatus, referring to fig. 3, including:

an initial module 31 configured to set up a standard table structure of a database and establish a connection with a plurality of source systems;

the integration module 32 is configured to store the stock data in different source systems into the database, and integrate the stock data of the same category according to the standard table structure to form a unified data table;

the updating module 33 is configured to transfer the incremental data in different source systems to a message queue system to form a message queue, and store the incremental data in the corresponding data table in sequence;

and the query module 34 is configured to establish an index corresponding to the data table, and perform data query according to the index.

For convenience of description, the above devices are described as being divided into various modules by functions, and are described separately. Of course, the functionality of the various modules may be implemented in the same one or more software and/or hardware implementations as the present application.

The apparatus of the foregoing embodiment is used to implement the corresponding cross-system data update query method in any of the foregoing embodiments, and has the beneficial effects of the corresponding method embodiment, which are not described herein again.

Based on the same inventive concept, corresponding to the method of any embodiment described above, the present application further provides an electronic device, which includes a memory, a processor, and a computer program stored on the memory and executable on the processor, and when the processor executes the program, the cross-system data update query method described in any embodiment above is implemented.

Fig. 4 is a schematic diagram illustrating a more specific hardware structure of an electronic device according to this embodiment, where the device may include: a processor 1010, a memory 1020, an input/output interface 1030, a communication interface 1040, and a bus 1050. Characterized in that processor 1010, memory 1020, input/output interface 1030, and communication interface 1040 are communicatively coupled to each other within the device via bus 1050.

The processor 1010 may be implemented by a general-purpose CPU (Central Processing Unit), a microprocessor, an Application Specific Integrated Circuit (ASIC), or one or more Integrated circuits, and is configured to execute related programs to implement the technical solutions provided in the embodiments of the present disclosure.

The Memory 1020 may be implemented in the form of a ROM (Read Only Memory), a RAM (random access Memory), a static storage device, a dynamic storage device, or the like. The memory 1020 may store an operating system and other application programs, and when the technical solution provided by the embodiments of the present specification is implemented by software or firmware, the relevant program codes are stored in the memory 1020 and called to be executed by the processor 1010.

The input/output interface 1030 is used for connecting an input/output module to input and output information. The i/o module may be configured as a component in a device (not shown) or may be external to the device to provide a corresponding function. It is characterized in that the input device can include a keyboard, a mouse, a touch screen, a microphone, various sensors, etc., and the output device can include a display, a loudspeaker, a vibrator, an indicator light, etc.

The communication interface 1040 is used for connecting a communication module (not shown in the drawings) to implement communication interaction between the present apparatus and other apparatuses. The communication module is characterized in that the communication module can realize communication in a wired mode (such as USB, network cable and the like) and also can realize communication in a wireless mode (such as mobile network, WIFI, Bluetooth and the like).

Bus 1050 includes a path that transfers information between various components of the device, such as processor 1010, memory 1020, input/output interface 1030, and communication interface 1040.

It should be noted that although the above-mentioned device only shows the processor 1010, the memory 1020, the input/output interface 1030, the communication interface 1040 and the bus 1050, in a specific implementation, the device may also include other components necessary for normal operation. In addition, those skilled in the art will appreciate that the above-described apparatus may also include only those components necessary to implement the embodiments of the present description, and not necessarily all of the components shown in the figures.

The electronic device of the foregoing embodiment is used to implement the corresponding cross-system data update query method in any one of the foregoing embodiments, and has the beneficial effects of the corresponding method embodiment, which are not described herein again.

Based on the same inventive concept, corresponding to any of the above embodiments, the present application also provides a non-transitory computer readable storage medium storing computer instructions for causing the computer to execute the cross-system data update query method according to any of the above embodiments.

Computer-readable media of the present embodiments, including both non-transitory and non-transitory, removable and non-removable media, may implement information storage by any method or technology. The information may be computer readable instructions, data structures, modules of a program, or other data. Examples of computer storage media include, but are not limited to, phase change memory (PRAM), Static Random Access Memory (SRAM), Dynamic Random Access Memory (DRAM), other types of Random Access Memory (RAM), Read Only Memory (ROM), Electrically Erasable Programmable Read Only Memory (EEPROM), flash memory or other memory technology, compact disc read only memory (CD-ROM), Digital Versatile Discs (DVD) or other optical storage, magnetic cassettes, magnetic tape magnetic disk storage or other magnetic storage devices, or any other non-transmission medium that can be used to store information that can be accessed by a computing device.

The computer instructions stored in the storage medium of the foregoing embodiment are used to enable the computer to execute the cross-system data update query method according to any embodiment, and have the beneficial effects of the corresponding method embodiments, which are not described herein again.

Those of ordinary skill in the art will understand that: the discussion of any embodiment above is meant to be exemplary only, and is not intended to intimate that the scope of the disclosure, including the claims, is limited to these examples; within the context of the present application, features from the above embodiments or from different embodiments may also be combined, steps may be implemented in any order, and there are many other variations of the different aspects of the embodiments of the present application as described above, which are not provided in detail for the sake of brevity.

Additionally, for simplicity of explanation and discussion, and so as not to obscure the embodiments of the present application, apparatus may be shown in block diagram form in order to avoid obscuring the embodiments of the present application, and this also takes into account the fact that specifics with respect to implementation of such block diagram apparatus are highly dependent upon the platform within which the embodiments of the present application are to be implemented (i.e., specifics should be well within purview of one skilled in the art). Where specific details are set forth in order to describe example embodiments of the application, it will be apparent to one skilled in the art that the embodiments of the application can be practiced without, or with variation of, these specific details. Accordingly, the description is to be regarded as illustrative instead of restrictive.

While the present application has been described in conjunction with specific embodiments thereof, many alternatives, modifications, and variations of these embodiments will be apparent to those of ordinary skill in the art in light of the foregoing description.

The present embodiments are intended to embrace all such alternatives, modifications and variances which fall within the broad scope of the appended claims. Therefore, any omissions, modifications, substitutions, improvements, and the like that may be made without departing from the spirit and principles of the embodiments of the present application are intended to be included within the scope of the present application.

Claims

1. A cross-system data update query method is characterized by comprising the following steps:

establishing a standard table structure of a database, and establishing connection with a plurality of source systems;

storing stock data in different source systems into the database, and integrating the stock data of the same category according to the standard table structure to form a unified data table;

transferring the incremental data in different source systems to a message queue system to form a message queue, and sequentially storing the incremental data into corresponding data tables;

and establishing an index corresponding to the data table, and performing data query according to the index.

2. The cross-system data update query method of claim 1, wherein the database is OCeanBase.

3. The cross-system data update query method according to claim 1, wherein the standard table structure comprises a table name, a table field and a table record, the table name corresponds to the category, and the type of the table field comprises a data source and a storage time.

4. The cross-system data update query method according to claim 1, wherein the storing inventory data in different source systems into the database comprises:

and concurrently transferring the stock data in the source system by adopting a DataX tool, wherein the concurrent transfer comprises starting a plurality of task groups, each task group comprises a plurality of subtasks, each task group is responsible for transferring all the stock data of one table in the source system, and each subtask is responsible for transferring part of the stock data in one table.

5. The cross-system data updating query method according to claim 1, wherein the message queue system is Kafka, and the incremental data in the message queue are arranged according to the chronological order of the incremental time.

6. The cross-system data update query method according to claim 1, wherein the sequentially storing the incremental data into the corresponding data table comprises:

and sequentially reading the incremental data in the message queue by adopting a spark Steaming program, and storing the incremental data into the corresponding data table according to the type of the incremental data.

7. The cross-system data updating query method according to claim 3, wherein the establishing the index corresponding to the data table comprises: and asynchronously establishing the index according to the table field of the data table.

8. The cross-system data update query method according to claim 1, wherein the querying data according to the index includes: and accessing the database by adopting a front-end checking service system, executing SQL query statements according to the index, and querying the data stored in the database.

9. A cross-system data update query apparatus, comprising:

an initial module configured to set up a standard table structure of a database and establish a connection with a plurality of source systems;

the integration module is configured to store stock data in different source systems into the database, and integrate the stock data of the same category according to the standard table structure to form a unified data table;

the updating module is configured to transfer the incremental data in different source systems to a message queue system to form a message queue, and sequentially store the incremental data in the corresponding data tables;

and the query module is configured to establish an index corresponding to the data table and perform data query according to the index.

10. An electronic device comprising a memory, a processor, and a computer program stored on the memory and executable on the processor, wherein the processor implements the cross-system data update query method of any one of claims 1-8 when executing the computer program.