CN111581225A

CN111581225A - Database reconciliation method, system, server and storage medium

Info

Publication number: CN111581225A
Application number: CN202010388179.8A
Authority: CN
Inventors: 何益益
Original assignee: Shenzhen Mingshu Information Co Ltd
Current assignee: Shenzhen Mingshu Information Co Ltd
Priority date: 2020-05-09
Filing date: 2020-05-09
Publication date: 2020-08-25

Abstract

The invention provides a database reconciliation method, which comprises the following steps: the account checking system acquires the business data in the data warehouse; judging whether the service data is changed or not; if the change occurs, obtaining change information in the service data; and sending a data compensation instruction to a data organization system based on the change information so that the data organization system obtains the reported data in the data warehouse based on the data compensation instruction and updates the reported data. The invention combines and compares the service data and the report database through the bins, and realizes the data report of the cross-service system through a single report center, thereby simplifying the complexity of the system.

Description

Database reconciliation method, system, server and storage medium

Technical Field

The embodiment of the invention relates to the distributed field technology, in particular to a database reconciliation method, a database reconciliation device, a database reconciliation server and a storage medium.

Background

A distributed system is a system of computer nodes that communicate over a network and that work in concert to accomplish a common task. The distributed system can use common machines to complete calculation and storage tasks which cannot be completed by a single computer, so that more machines can be utilized, and more data can be processed.

In the reconciliation system in the prior art, a plurality of servers are respectively butted with a reporting center, reconciliation data are generated by multipoint joint, so that repeated workload is caused, and the system is difficult to maintain because of too many involved endpoints.

Disclosure of Invention

The invention provides a database account checking method, a system, a server and a storage medium, wherein business data and a reporting database are merged and compared through a plurality of bins, cross-business system data reporting is realized through a single reporting center, and the system complexity is simplified.

In a first aspect, the present invention provides a database reconciliation method, including:

the account checking system acquires the business data in the data warehouse;

judging whether the service data is changed or not;

if the change occurs, obtaining change information in the service data;

and sending a data compensation instruction to a data organization system based on the change information so that the data organization system obtains the reported data in the data warehouse based on the data compensation instruction and updates the reported data.

Further, the determining whether the service data is changed includes:

judging whether the service data has a preset updating mark;

and if the preset updating identification exists, the service data is changed.

Further, the acquiring change information in the service data includes:

based on the updating identification, reading a keyword corresponding to the change information in the service data;

judging whether the corresponding position of the reported data has the corresponding keyword or not;

and if not, acquiring the change information corresponding to the update identifier.

Further, the reconciliation system acquires the service data in the data warehouse by adopting a Hive tool.

Further, the update identification is a modification time field.

Further, before the reconciliation system obtains the business data in the data bin, the method further comprises:

judging whether business data updating information sent by the bins is received or not at preset time intervals;

and if so, acquiring the service data in the bins.

In a second aspect, the present invention provides a database reconciliation system, comprising:

the first acquisition module is used for acquiring the service data in the data bin;

the account checking module is used for judging whether the business data is changed or not;

the second acquisition module is used for acquiring the change information in the service data if the change occurs;

and the instruction sending module is used for sending a data compensation instruction to a data organization system based on the change information so that the data organization system obtains the reported data in the data warehouse based on the data compensation instruction and updates the reported data.

Further, the reconciliation module comprises:

the judging unit is used for judging whether the service data has a preset updating identifier or not;

and if the preset updating identification exists, the service data is changed.

In a third aspect, the present invention provides a server, including a memory, a processor, and a program stored in the memory and executable on the processor, where the processor executes the program to implement the database reconciliation method according to any one of the above.

In a fourth aspect, the present invention provides a terminal-readable storage medium, on which a program is stored, which, when executed by a processor, is capable of implementing a database reconciliation method as described in any of the above.

The invention combines and compares the service data and the report database through the bins, and realizes the data report of the cross-service system through a single report center, thereby simplifying the complexity of the system.

Drawings

Fig. 1 is a flowchart of a database reconciliation method according to the first embodiment.

Fig. 2 is a flowchart of a database reconciliation method according to the second embodiment.

Fig. 3 is a block diagram of a database reconciliation system in the third embodiment.

Fig. 4 is a block diagram of a database reconciliation system in accordance with three alternative embodiments of the present invention.

FIG. 5 is a diagram of a four server module according to an embodiment.

Detailed Description

The present invention will be described in further detail with reference to the accompanying drawings and examples. It is to be understood that the specific embodiments described herein are merely illustrative of the invention and are not limiting of the invention. It should be further noted that, for the convenience of description, only some of the structures related to the present invention are shown in the drawings, not all of the structures.

Before discussing exemplary embodiments in more detail, it should be noted that some exemplary embodiments are described as processes or methods depicted as flowcharts. Although a flowchart may describe the steps as a sequential process, many of the steps can be performed in parallel, concurrently or simultaneously. In addition, the order of the steps may be rearranged. A process may be terminated when its operations are completed, but may have additional steps not included in the figure. A process may correspond to a method, a function, a procedure, a subroutine, a subprogram, etc.

Furthermore, the terms "first," "second," and the like may be used herein to describe various orientations, actions, steps, elements, or the like, but the orientations, actions, steps, or elements are not limited by these terms. These terms are only used to distinguish one direction, action, step or element from another direction, action, step or element. For example, the first obtaining module may be the second obtaining module or the third obtaining module, and similarly, the second obtaining module and the third obtaining module may be the first obtaining module without departing from the scope of the present application. The first obtaining module, the second obtaining module and the third obtaining module are all obtaining modules of the distributed file system, but are not the same obtaining module. The terms "first", "second", etc. are not to be construed as indicating or implying relative importance or implicitly indicating the number of technical features indicated. Thus, a feature defined as "first" or "second" may explicitly or implicitly include one or more of that feature. In the description of the present invention, "plurality", "batch" means at least two, e.g., two, three, etc., unless specifically limited otherwise.

The terms and abbreviations used in the following examples have the following meanings:

counting bins: the Hbase-based data warehouse, the production environment synchronizes data from the relational database of each project to the bins.

HBase: hadoop Database is a distributed storage system with high reliability, high performance, orientation and scalability. HBase is different from a general relational database, and is a database suitable for unstructured data storage.

Hive: a data warehouse tool based on Hadoop can map a structured data file into a database table, provide a simple sql query function and convert sql statements into MapReduce tasks for operation. Hive provides SQL-like query language, designs calculation and expansion capability for an ultra-large data set, provides unified metadata management, supports user-defined functions, and enables users to achieve good fault tolerance of own functions according to own requirements, and SQL can still be executed when a node has a problem.

And (3) reporting data: the data reporting process in the following embodiments refers to the work of sending the loan transaction data to such regulatory agencies by the P2P financial company according to the requirements of the agencies such as "public information network security supervision", "china mutual education association", and the like.

SQL: structured Query Language (SQL), a Structured Query Language, is used for relational database management and standard computation of data operations, and can support querying, inserting, updating, and modifying data for multiple databases across platforms.

Example one

As shown in fig. 1, this embodiment provides a database reconciliation method, which includes one or more service databases, where each service database generates corresponding service data, a counting bin obtains and stores the service data of each service database, and the counting bin obtains and stores report data of a reporting center at the same time. The method is executed by a reconciliation system and comprises the following steps:

s101, the account checking system acquires business data in the bins.

The business data refers to business data generated by one or more trading centers, the bins are data warehouses, and one or more pieces of business data generated by different trading businesses (such as e-commerce shopping, loan business, banking business, network public information and the like) are collated and combined into one piece of business data and stored in the bins.

In this step, the account checking system obtains the business data from the data warehouse, and the business data records all the transaction information of the transaction center, such as the number of visitors, click rate, transaction amount, member information and the like of the e-commerce website in a period of time. Preferably, the reconciliation system acquires the service data in the several bins by using a Hive tool.

S102, judging whether the service data is changed.

The warehouse acquires the service data of one or more transaction centers regularly, generates report data based on the service data and sends the report data to the report center. And the data warehouse acquires the reported data from the reporting center and checks the reported data through a Hive scheme of the account checking system. Since the service data of one or more transaction centers may be changed, errors may occur between the reported data and the service data.

In this step, the reconciliation system can check the reported data and the service data and judge whether the data difference occurs. Optionally, the reconciliation process may obtain the reporting data and the service data at the same time, and perform integral scanning and checking on the reporting data file and the service data file to obtain the change information. Optionally, the reconciliation system may also obtain only the change information based on a preset change identifier.

S103, if the change occurs, obtaining the change information in the service data.

And S104, sending a data compensation instruction to a data organization system based on the change information so that the data organization system obtains the reported data in the data warehouse based on the data compensation instruction and updates the reported data.

In the step, the reporting data is not sent to the reporting center by each service system, but the reconciliation system generates a data compensation instruction based on the change information.

And the account checking system sends the data compensation instruction to a data organization system. Optionally, the data compensation instruction includes change information and change time, and the data organization system queries the change information from the data warehouse through the timing access template SQL and sends the change information to the reporting center, so that the reported data of the reporting center is updated to be consistent with the latest updated service data, thereby completing the whole compensation process.

In the embodiment, the service data and the report database are merged and compared through the bins, cross-service system data report is realized through a single report center, the account checking system is unified, account checking can be completed in a single point only by the account checking system, and the system complexity is simplified.

Example two

As shown in fig. 2, in this embodiment, steps are refined on the basis of the above embodiment, and a step of updating data based on service data update information sent by a plurality of bins by the reconciliation system is added. The method of the embodiment is executed by an account checking system, and specifically comprises the following steps:

and S2011, judging whether the service data updating information sent by the bins is received at preset time intervals.

The business data in the step refers to business data generated by one or more transaction centers, the bins are data warehouses, and one or more pieces of business data generated by different transaction businesses (such as e-commerce shopping, loan business, banking business, network public information and the like) are collated and combined into one piece of business data and sent to the bins for storage. Illustratively, the business data includes contents such as the number of visitors, click rate, transaction amount, member information, etc. of the e-commerce website in a certain period of time.

S2012, if receiving, acquiring the service data in the bins.

Due to the fact that data change may occur in the business data, for example, the member ID of the e-commerce shopping website changes, errors occur in the business data and the reported data to be reported. And when the digital warehouse receives the updated service data, generating service data updating information and sending the service data updating information to the account checking system, and executing subsequent data account checking work by the account checking system based on the service data updating information. Because a plurality of transaction centers generate multi-service data, a plurality of data forms need to be subjected to correlation query, and in order to avoid long query time, the reconciliation system preferably acquires and queries the data in the log cabin by using Hive.

S202, the account checking system acquires the business data in the bins.

S2031, judging whether the service data has a preset updating mark.

In this step, the update identifier is an identifier for locating the change information in the data file of the service data, and is illustratively a modification time field, i.e., a service data update _ time field. The service data stored in the bins is updated when a certain row of data is changed, and the update _ time field corresponds to a specific updating time period, such as 12:00-12: 05. The query mode reduces the data query range and can improve the account checking efficiency.

S2032, if the preset updating mark exists, the service data is changed.

S2041, based on the update identification, reading a keyword corresponding to the change information in the service data.

In this step, the reconciliation system uses the modification time update _ time field in the service data to query only for a certain time period, such as 12:00-12: 05.

S2042, judging whether the corresponding position of the reported data has the corresponding keyword.

In this step, the reconciliation system checks the queried changed data keyword information, for example, trade _ n, with the corresponding data of the reported data, and if the reported data has no corresponding data keyword information, it indicates that the reported data and the service data are different, in the form of: { title: transaction information missing, differential time: 12:00-12:05 }.

S2043, if not, obtaining the change information corresponding to the update identification.

S205, sending a data compensation instruction to a data organization system based on the change information, so that the data organization system obtains the reported data in the data warehouse based on the data compensation instruction and updates the reported data.

In the embodiment, the data update _ time field of the original table is used for inquiring, so that the data inquiry range is greatly reduced, and the account checking efficiency is improved. Meanwhile, the data compensation only aims at the changed data, so that the development and maintenance difficulty is simplified, and the system complexity is reduced.

EXAMPLE III

As shown in fig. 3, the present embodiment provides a database reconciliation system 3, which includes the following modules:

a first obtaining module 301, configured to obtain service data in a bin;

the reconciliation module 302 is configured to determine whether the service data is changed;

a second obtaining module 303, configured to obtain change information in the service data if the change occurs;

and the instruction sending module 304 is configured to send a data compensation instruction to a data organization system based on the change information, so that the data organization system obtains the reported data in the data warehouse based on the data compensation instruction and updates the reported data.

In an alternative embodiment, as shown in FIG. 4, the reconciliation module 302 comprises:

a first judging unit 3021, configured to judge whether the service data has a preset update identifier;

a changing unit 3022, configured to change the service data if the preset update identifier exists.

The second obtaining module 303 includes:

a reading unit 3031, configured to read, based on the update identifier, a keyword corresponding to the change information in the service data;

a second determining unit 3032, configured to determine whether there is a corresponding keyword at a corresponding position of the reported data; and if not, acquiring the change information corresponding to the update identifier.

In an alternative embodiment, the database reconciliation system 3 further comprises:

an updating module 305, configured to determine, every preset time interval, whether to receive service data update information sent by the bins;

a third obtaining module 306, configured to obtain the service data in the bins if the service data is received.

The embodiment provides a database reconciliation system, can execute the database reconciliation method provided by any embodiment of the invention, and has corresponding functional modules and beneficial effects of the execution method.

Example four

The present embodiment provides a schematic structural diagram of a server, as shown in fig. 5, the server includes a processor 401, a memory 402, an input device 403, and an output device 404; the number of the processors 401 in the server may be one or more, and one processor 401 is taken as an example in the figure; the processor 401, memory 402, input device 403 and output device 404 in the device/terminal/server may be linked by a bus or other means, as exemplified by the bus in fig. 5.

The memory 402 is a computer-readable storage medium, and can be used for storing software programs, computer-executable programs, and modules, such as program instructions/modules (e.g., the first obtaining module 301, the reconciliation module 302, etc.) corresponding to the gateway-based link generation method in the embodiment of the present invention. The processor 401 executes various functional applications of the device/terminal/server and data processing by running software programs, instructions and modules stored in the memory 402, that is, implements the above-described database reconciliation method.

The memory 402 may mainly include a program storage area and a data storage area, wherein the program storage area may store an operating system, an application program required for at least one function; the storage data area may store data created according to the use of the terminal, and the like. Further, the memory 402 may include high speed random access memory, and may also include non-volatile memory, such as at least one magnetic disk storage device, flash memory device, or other non-volatile solid state storage device. In some examples, the memory 402 may further include memory located remotely from the processor 401, which may be linked to the device/terminal/server through a network. Examples of such networks include, but are not limited to, the internet, intranets, local area networks, mobile communication networks, and combinations thereof.

The input means 403 may be used to receive input numeric or character information and generate key signal inputs related to user settings and function control of the device/terminal/server. The output device 404 may include a display device such as a display screen.

The embodiment of the invention also provides a server which can execute the database reconciliation method provided by any embodiment of the invention and has corresponding functional modules and beneficial effects of the execution method.

EXAMPLE five

An embodiment of the present invention further provides a computer-readable storage medium, on which a computer program is stored, where the computer program, when executed by a processor, implements a database reconciliation method provided in any embodiment of the present invention, where the method may include:

the account checking system acquires the business data in the data warehouse;

judging whether the service data is changed or not;

if the change occurs, obtaining change information in the service data;

The computer-readable storage media of embodiments of the invention may take any combination of one or more computer-readable media. The computer readable medium may be a computer readable signal medium or a computer readable storage medium. A computer readable storage medium may be, for example, but not limited to, an electronic, magnetic, optical, electromagnetic, infrared, or semiconductor system, apparatus, or device, or any combination of the foregoing. More specific examples (a non-exhaustive list) of the computer readable storage medium would include the following: an electrical link having one or more wires, a portable computer diskette, a hard disk, a Random Access Memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber, a portable compact disc read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the foregoing. In the context of this document, a computer readable storage medium may be any tangible medium that can contain, or store a program for use by or in connection with an instruction execution system, apparatus, or device.

A computer readable signal medium may include a propagated data signal with computer readable program code embodied therein, for example, in baseband or as part of a carrier wave. Such a propagated data signal may take many forms, including, but not limited to, electro-magnetic, optical, or any suitable combination thereof. A computer readable signal medium may also be any computer readable medium that is not a computer readable storage medium and that can communicate, propagate, or transport a program for use by or in connection with an instruction execution system, apparatus, or device.

Program code embodied on a storage medium may be transmitted over any appropriate medium, including but not limited to wireless, wireline, optical fiber cable, RF, etc., or any suitable combination of the foregoing.

Computer program code for carrying out operations for aspects of the present invention may be written in any combination of one or more programming languages, including an object oriented programming language such as Java, Smalltalk, C + +, or the like, as well as conventional procedural programming languages, such as the "C" programming language or similar programming languages. The program code may execute entirely on the user's computer, partly on the user's computer, as a stand-alone software package, partly on the user's computer and partly on a remote computer or entirely on the remote computer or terminal. In the case of a remote computer, the remote computer may be linked to the user's computer through any type of network, including a Local Area Network (LAN) or a Wide Area Network (WAN), or the link may be made to an external computer (for example, through the Internet using an Internet service provider).

It is to be noted that the foregoing is only illustrative of the preferred embodiments of the present invention and the technical principles employed. It will be understood by those skilled in the art that the present invention is not limited to the particular embodiments described herein, but is capable of various obvious changes, rearrangements and substitutions as will now become apparent to those skilled in the art without departing from the scope of the invention. Therefore, although the present invention has been described in greater detail by the above embodiments, the present invention is not limited to the above embodiments, and may include other equivalent embodiments without departing from the spirit of the present invention, and the scope of the present invention is determined by the scope of the appended claims.

Claims

1. A database reconciliation method, comprising:

the account checking system acquires the business data in the data warehouse;

judging whether the service data is changed or not;

if the change occurs, obtaining change information in the service data;

2. The database reconciliation method of claim 1, wherein the determining whether the business data is changed comprises:

judging whether the service data has a preset updating mark;

and if the preset updating identification exists, the service data is changed.

3. The database reconciliation method according to claim 2, wherein the acquiring change information in the business data comprises:

4. The database reconciliation method of claim 1, wherein the reconciliation system adopts a Hive tool to acquire the business data in the warehouse.

5. The database reconciliation method of claim 2 wherein the update identification is a modification time field.

6. The database reconciliation method of claim 1, wherein before the reconciliation system obtains the business data in the data warehouse, the method further comprises:

and if so, acquiring the service data in the bins.

7. A database reconciliation system comprising:

8. The database reconciliation system of claim 7, wherein the reconciliation module comprises:

and if the preset updating identification exists, the service data is changed.

9. A server comprising a memory, a processor, and a program stored on the memory and executable on the processor, wherein the processor implements the database reconciliation method of any of claims 1-6 when executing the program.

10. A terminal-readable storage medium, on which a program is stored, the program being capable of implementing the database reconciliation method according to any one of claims 1 to 6 when executed by a processor.