CN111177124A

CN111177124A - Data processing method and system based on PG database and CITUS component

Info

Publication number: CN111177124A
Application number: CN201911410991.XA
Authority: CN
Inventors: 王旋; 邓留高; 程龙; 李盘; 燕斌祺
Original assignee: Jiangsu Suning Logistics Co ltd
Current assignee: Jiangsu Suning Logistics Co ltd
Priority date: 2019-12-31
Filing date: 2019-12-31
Publication date: 2020-05-19

Abstract

The application discloses a data processing method and a system based on a PG database and a CITUS component, wherein the method is applied to a computer cluster, and the cluster comprises a CN node and at least one WK node, wherein the CN node is provided with the PG database and the CITUS component; the CN node determines a target fragment field according to a first mapping relation by using the received query field, then determines a target WK node according to a second mapping relation, sends a query request comprising the target fragment field to the target WK node for query, and enables each SQL query to achieve the most efficiency when the fragment field is used for query by using PGSQL and CITUS frameworks and establishing the mapping relation among the query field, the target fragment field and the target WK node; meanwhile, the query requests corresponding to the same fragments are sent to the corresponding WK nodes as a group for query, only one database connection is occupied, and the defects of the architecture are overcome.

Description

Data processing method and system based on PG database and CITUS component

Technical Field

The invention relates to the field of data processing in enterprise application, in particular to a data processing method and system based on a PG database and a CITUS component.

Background

In recent years, with the rapid expansion of logistics business, the data required to process calculation is changed in a volume level type every day, and a scene of hundreds of millions of days and 10 hundreds of millions of years has appeared. In the data storage of the magnitude, the sub-warehouse and the sub-table are a choice, and the management work of the existing sub-warehouse and the sub-table is realized by service logic, which is very responsible and tedious work and occupies a large amount of development resources; meanwhile, the change of the database and table rules caused by the change of the business or the change of the data structure needs to be operated by manpower, which is a work with high maintenance cost and great risk.

The prior art has the following defects:

in a conventional database and table division mode, rules are maintained and managed by a client, a script presents a multiple increasing trend, maintenance cost is high, and the phenomenon of inconsistent scripts is easy to occur, so that production accidents are caused.

The conventional database and table partitioning increases the development complexity and code amount of research and development personnel, greatly reduces the research and development efficiency, and also introduces new problems.

Disclosure of Invention

Aiming at the defects of the prior art, the invention provides a data processing method and a system based on a PG database and a CITUS component, wherein PGSQL and CITUS frameworks are used, and the CITUS component is used for managing the sub-database and sub-table of the database, so that the maintenance cost of the database is reduced; by establishing a mapping table between the fragment fields and the query conditions, the fragment fields are used for query, so that each SQL query can achieve the highest efficiency; meanwhile, by combining the characteristics of the database and utilizing the database function, the data are grouped according to the fragments, the data of the same fragment are put together for inquiry, and only one database connection is occupied, so that the defects of the architecture are overcome.

According to one aspect of the invention, the invention provides a data processing method based on a PG database and a CITUS component, which is applied to a computer cluster, wherein the cluster comprises a CN node and at least one WK node, wherein the CN node is provided with the PG database and the CITUS component; the method comprises the following steps:

the CN node receives a query field in a query request input by a user and matches a target fragment field corresponding to the query field according to a pre-stored first mapping relation; the first mapping relation is a corresponding relation between the query field and the fragment field;

the CN node determines a target WK node according to the target fragment field and a pre-stored second mapping relation and sends a query request comprising the target fragment field to the target WK node for query; the second mapping relation is the mapping relation between the fragment field and the WK node;

and the CN node receives a query result returned after the target WK node queries in the fragments of the PG database of the CN node and sends the query result to the user.

Preferably, the method further includes a process of establishing the first mapping relationship in advance:

the CN node receives a query field mapping request input by a user; the query field mapping request comprises a fragment field and at least one query field;

and constructing a first mapping relation between the query field and the fragment field in the query field mapping request.

Preferably, the method further includes a process of pre-establishing the second mapping relationship:

and the CN node fragments the source data according to the fragment fields in the preset fragment rule and then distributively stores the fragmented source data in the PG database corresponding to the WK node, and establishes a second mapping relation between the fragment fields and the WK node.

Preferably, the step of the CN node fragmenting the source data according to a fragmentation field in a preset fragmentation rule and then storing the fragmented source data in the PG database corresponding to the WK node in a distributed manner includes: and the CN node receives a fragmentation command of a user, calculates the fragmentation data volume which should be created by each WK node by using a database function, and sends a command to the WK node to instruct the WK node to create the fragmentation of the specified data.

Preferably, when the CN node determines that the number of the query requests received in batch exceeds a certain number, determining the fragment corresponding to each query request according to the fragment field corresponding to the query request;

and sending the query requests corresponding to the same fragments as a group to the corresponding WK nodes for query.

Preferably, the query fields are client, origin and destination;

the target fragment field is a charging serial number.

According to another aspect of the present invention, the present invention also provides a data processing system based on a PG database and a CITUS component, the system comprising a CN node and at least one WK node, wherein the CN node is provided with the PG database and the CITUS component;

the CN node is used for receiving a query field in a query request input by a user and matching a target fragment field corresponding to the query field according to a pre-stored first mapping relation; the first mapping relation is a corresponding relation between a query field and a fragment field;

the CN node is also used for determining a target WK node according to the target fragment field and a pre-stored second mapping relation and sending a query request comprising the target fragment field to the target WK node for query; the second mapping relation is the mapping relation between the fragment field and the WK node;

the CN node is also used for receiving a query result returned by the target WK node after the target WK node queries the fragments of the PG database of the target WK node and sending the query result to the user;

and the WK node is used for returning a query result after querying in the fragments of the PG database according to the instruction of the CN node.

Preferably, the CN node is further configured to receive a query field mapping request input by a user; the query field mapping request comprises a fragment field and at least one query field;

Preferably, the CN node is further configured to fragment the source data according to a fragmentation field in a preset fragmentation rule, and then store the fragmented source data in the PG database corresponding to the WK node in a distributed manner, and establish a second mapping relationship between the fragmentation field and the WK node.

According to another aspect of the present invention, there is also provided a computer system, the system comprising:

one or more processors; and

memory associated with the one or more processors for storing program instructions that, when read and executed by the one or more processors, perform the operations of any of the above method claims.

The invention has the beneficial effects that: the PGSQL and CITUS architecture is used, and the CITUS component is used for managing the sub-database and sub-table of the database, so that the maintenance cost of the database is reduced; by establishing a mapping table between the fragment fields and the query conditions, the fragment fields are used for query, so that each SQL query can achieve the most efficient; meanwhile, the data are grouped according to the fragments by combining the characteristics of the database and utilizing the database function, the data of the same fragment are put together for inquiry, and only one database connection is occupied, so that the defects of the architecture are avoided.

The features and advantages of the present invention will become apparent by reference to the following drawings and detailed description of specific embodiments of the invention.

Drawings

FIG. 1 is a flow chart of a method according to a first embodiment of the present invention;

FIG. 2 is a flowchart of a data processing method according to a second embodiment of the present invention;

FIG. 3 is a block diagram of a data processing system according to the present invention;

FIG. 4 is a schematic diagram of a computer system according to an embodiment of the present invention.

Detailed Description

The technical solutions in the embodiments of the present invention are clearly and completely described below with reference to the drawings in the embodiments of the present invention, and it is obvious that the described embodiments are only a part of the embodiments of the present invention, and not all embodiments. All other embodiments, which can be derived by a person skilled in the art from the embodiments given herein without any inventive step, are within the scope of the present invention.

Unless defined otherwise, all technical and scientific terms used herein have the same meaning as commonly understood by one of ordinary skill in the art to which this invention belongs. The terminology used in the description of the invention herein is for the purpose of describing particular embodiments only and is not intended to be limiting of the invention.

PGSQL is used as an open-source database, has strong flexibility and inclusion, and the generation of the CITUS component is the best embodiment. The PGSQL serves as a bottom database to carry the database, and the CITUS serves as a manager's responsibility to manage the sub-table rules of the database, the convergence and expansion functions among the sub-databases, and the like, and in cooperation with the PGSQL and the cis, the technical problem to be solved by the application can be solved.

Example one

As a basis for the following second to fifth embodiments, the present invention first provides a data processing method based on the PG database and the cis element, as shown in fig. 1, the method includes the following steps:

s1, constructing a PG database cluster and installing a CITUS component;

s2, arranging the database script and creating a database fragment table;

and S3, filtering according to the fragment fields in the fragment table.

Preferably, the step S1 includes:

the application machine is respectively provided with CN nodes and WK node database software, CITUS components are arranged on the CN nodes and the WK nodes, and the CN nodes and the WK nodes are connected through CITUS; the CN node is used for calculating and decomposing command requests sent by users and dispatching the command requests to the designated WK node, and the WK node receives the request commands of the CN node, executes corresponding operations and returns operation results to the CN node.

In the step, the CITUS component manages the sub-database and sub-table, actual data is stored in the WK node, and the CN node performs data integration and management. The CN node is equivalent to a scheduling and is responsible for interfacing with a user, calculating and decomposing a command request sent by the user, and scheduling the command request to a designated WK node, wherein the CN node does not actually store data; the data is actually landed on the WK node, the WK node receives the request command of the CN node, executes real operations of adding, deleting, modifying and searching, and returns the data to the CN node.

Preferably, the step S2 includes:

setting an access white list between the CN node and the WK node, starting the CN node and the WK node, creating a TABLE on the CN node through a script, executing a function SN _ CREATE _ DISTRIBUTED _ TABLE on the CN node through the script, receiving a creation command of a user by the CN node, calculating the data volume of a fragment TABLE which should be created by each WK node, and sending a command to the WK node to instruct the WK node to CREATE the fragment TABLE of specified data.

In the step, database scripts are sorted, a fragment table is created, the scripts only need to create one table, and specific database partitioning and table division are achieved through database functions. The method comprises the steps that a developer CREATEs a TABLE on a CN node through a script, then the developer executes a function SN _ CREATE _ DISTRIBUTED _ TABLE on the CN node through the script, the CN node receives a creation command of a user, the data volume of a fragmentation TABLE which is to be created by each WK node is calculated, the command is sent to the WK node, and the fragmentation TABLE of specified data is created.

Preferably, the step S3 includes:

and pre-establishing a mapping relation between a filtering condition and the fragment field, acquiring the fragment field according to the mapping relation, and filtering by using the fragment field by the service SQL.

In the step, a table structure and fragment fields are reasonably designed according to a requirement document, and the service SQL uses the fragment fields for filtering; filtering here refers to WHERE conditional queries in SQL.

If a scenario that cannot be queried using the fragment field exists, a mapping relationship between the filter condition and the fragment field is established in advance, for example, in an exemplary scenario, the charging flow numbers of all the charging tables are queried according to the client + the origin + the destination, the fragment field of the charging table is the charging flow number, but the query condition is the client + the origin + the destination, and the fragment field is not included, so that the required charging table cannot be queried when the query is performed, therefore, to solve the above problem, a mapping table is created in advance, the client + the origin + the destination are spliced into a dimension field and the charging flow number, the two fields are in one table, the dimension field is the fragment field, so that when the query is required, the charging flow number (i.e. the fragment field) is obtained according to the client + the origin + the destination query mapping table, and then, inquiring the billing table according to the billing serial number, so as to obtain the required billing table.

Preferably, the step S3 includes:

and grouping the data according to the fragments by using a database function in combination with the characteristics of the database.

When a large number of queries are encountered, such as 1: n, the scenes with very large N have respective disadvantages by directly carrying out batch query or circulating traversal query, the method firstly groups N according to fragments according to a database lightweight function pg _ get _ dist _ board _ displacement, and the pg _ get _ dist _ board _ displacement function is a function provided for a CN node and is used for calculating the fragment where the CN node is located according to fragment field data; the specific fragment of the data can be calculated by a function according to the fragment field, and then the fragments are put in a group.

Subsequent filtering depends on the result set to carry out batch operation, the maximum is 1000, and the efficiency can be improved by 1000 times while the normal operation of the database is ensured.

In the invention, the query and the addition (modification and deletion) can be separated in the service realization, the characteristics of the PGSQL database are represented in the way that the smaller the transaction, the less the locked resources and the higher the performance, for example, when a function is added, the data of a plurality of tables are queried and then integrated, and finally the data are input into the tables; the query data and the integration link can be placed outside the transaction, and only the table entry logic is controlled in the transaction.

The method is applied to the settlement field, the mapping relation is established between the query words in the settlement field and the fragment fields in the settlement field, and the client, the origin and the destination are mapped into the fragment field charging serial number as described above.

The database architecture is applied to the field of mass data settlement, so that on one hand, the workload of data structure maintenance can be simplified, and the probability of production accidents caused by complex and fussy data structures is reduced; on the other hand, the existing service is improved, so that the characteristics of the database are more reasonably utilized, and on the basis of ensuring the healthy operation of the database, the query efficiency is improved and a faster response can be obtained.

The application of the database framework provides templates for other mass data processing applications, provides more real and reliable data for the functions and the performance of the database, and provides basis for the calibration standards of the database.

Example two

In summary, as shown in fig. 2, the present invention provides a data processing method based on a PG database and a CITUS component, which is applied to a computer cluster, where the cluster includes a CN node and at least one WK node, where the PG database and the CITUS component are installed; the method comprises the following steps:

s101, the CN node receives a query field in a query request input by a user and matches a target fragment field corresponding to the query field according to a pre-stored first mapping relation; the first mapping relation is a corresponding relation between a query field and a fragment field;

s102, the CN node determines a target WK node according to the target fragment field and a pre-stored second mapping relation and sends a query request comprising the target fragment field to the target WK node for query; the second mapping relation is the mapping relation between the fragment field and the WK node;

and S103, the CN node receives a query result returned after the target WK node queries in the fragments of the PG database of the CN node and sends the query result to the user.

Preferably, the query fields are client, origin and destination;

the target fragment field is a charging serial number.

In the above steps, when the CN node establishes the first mapping relationship and the second mapping relationship, the CN node may store the corresponding relationship between the query field, the target fragment field, and the target WK node in the request in the form of a table.

In this embodiment, by establishing a mapping relationship among the query field, the target fragment field, and the target WK node, when performing query using the fragment field, each SQL query can achieve the most efficient; meanwhile, query requests corresponding to the same fragments are sent to corresponding WK nodes as a group for query, data of the same fragment are put together for query, and only one database connection is occupied, so that the defects of the architecture are overcome.

EXAMPLE III

As shown in fig. 3, according to another aspect of the present invention, the present invention also provides a data processing system based on a PG database and a CITUS component, the system comprising a CN node and at least one WK node, in which the PG database and the CITUS component are installed;

The specific implementation process of the method steps executed by each module in this embodiment is the same as the implementation process of each method step in the second embodiment, and is not described herein again.

Example four

The present invention also provides a computer system, the system comprising:

one or more processors; and

a memory associated with the one or more processors, the memory to store program instructions that, when read and executed by the one or more processors, perform operations corresponding to the method steps in the first and second embodiments described above.

Fig. 4 illustrates an architecture of a computer system, which may include, in particular, a processor 1510, a video display adapter 1511, a disk drive 1512, an input/output interface 1513, a network interface 1514, and a memory 1520. The processor 1510, video display adapter 1511, disk drive 1512, input/output interface 1513, and network interface 1514 may be communicatively coupled to the memory 1520 via a communication bus 1530.

The processor 1510 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 solution provided in the present Application.

The Memory 1520 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 1520 may store an operating system 1521 for controlling the operation of the computer system 1500, a Basic Input Output System (BIOS) for controlling low-level operations of the computer system 1500. In addition, a web browser 1523, a data storage management system 1524, an icon font processing system 1525, and the like can also be stored. The icon font processing system 1525 may be an application program that specifically implements the operations of the foregoing steps in this embodiment of the application. In summary, when the technical solution provided by the present application is implemented by software or firmware, the relevant program codes are stored in the memory 1520 and called for execution by the processor 1510.

The input/output interface 1513 is used for connecting an input/output module to realize information input and output. 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. The input devices may include a keyboard, a mouse, a touch screen, a microphone, various sensors, etc., and the output devices may include a display, a speaker, a vibrator, an indicator light, etc.

The network interface 1514 is used to connect a communication module (not shown) to enable the device to interact with other devices for communication. 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).

The bus 1530 includes a path to transfer information between the various components of the device, such as the processor 1510, the video display adapter 1511, the disk drive 1512, the input/output interface 1513, the network interface 1514, and the memory 1520.

In addition, the computer system 1500 may also obtain information of specific extraction conditions from the virtual resource object extraction condition information database 1541 for performing condition judgment, and the like.

It should be noted that although the above devices only show the processor 1510, the video display adapter 1511, the disk drive 1512, the input/output interface 1513, the network interface 1514, the memory 1520, the bus 1530, etc., in a specific implementation, the devices may also include other components necessary for proper operation. Furthermore, it will be understood by those skilled in the art that the apparatus described above may also include only the components necessary to implement the solution of the present application, and not necessarily all of the components shown in the figures.

From the above description of the embodiments, it is clear to those skilled in the art that the present application can be implemented by software plus necessary general hardware platform. Based on such understanding, the technical solutions of the present application may be essentially implemented or a part contributing to the prior art may be embodied in the form of a software product, which may be stored in a storage medium, such as a ROM/RAM, a magnetic disk, an optical disk, etc., and includes several instructions for enabling a computer device (which may be a personal computer, a cloud server, or a network device) to execute the method described in the embodiments or some parts of the embodiments of the present application.

The embodiments in the present specification are described in a progressive manner, and portions that are similar to each other in the embodiments are referred to each other, and each embodiment focuses on differences from other embodiments. In particular, the system or system embodiments are substantially similar to the method embodiments and therefore are described in a relatively simple manner, and reference may be made to some of the descriptions of the method embodiments for related points. The above-described system and system embodiments are only illustrative, wherein the units described as separate parts may or may not be physically separate, and the parts displayed as units may or may not be physical units, may be located in one place, or may be distributed on a plurality of network units. Some or all of the modules may be selected according to actual needs to achieve the purpose of the solution of the embodiment. One of ordinary skill in the art can understand and implement it without inventive effort.

The method and system provided by the present application are introduced in detail, and specific examples are applied in the present application to explain the principle and the implementation manner of the present application, and the descriptions of the above examples are only used to help understand the method and the core idea of the present application; meanwhile, for a person skilled in the art, according to the idea of the present application, the specific embodiments and the application range may be changed. In view of the foregoing, the description should not be construed as limiting the application.

Claims

1. A data processing method based on a PG database and a CITUS component is characterized in that the method is applied to a computer cluster, and the cluster comprises a CN node and at least one WK node, wherein the CN node is provided with the PG database and the CITUS component; the method comprises the following steps:

2. The method of claim 1, further comprising a process of pre-establishing the first mapping relationship:

3. The method of claim 1, further comprising a process of pre-establishing the second mapping relationship:

4. The method of claim 3,

the CN node fragments the source data according to the fragmentation field in the preset fragmentation rule and then distributively stores the fragmented source data in the PG database corresponding to the WK node, and the method comprises the following steps: and the CN node receives the fragmentation command of the user, calculates the fragmentation data volume which should be created by each WK node by using a database function, and sends a command to the WK node to instruct the WK node to create the fragmentation of the specified data.

5. The method of claim 1,

when the CN node judges that the quantity of the query requests received in batch exceeds a certain quantity, determining the fragments corresponding to each query request according to the fragment fields corresponding to the query requests;

6. The method of any one of claims 1 to 5,

the query fields are client, origin and destination;

the target fragment field is a charging serial number.

7. A data processing system based on a PG database and a CITUS component is characterized by comprising a CN node and at least one WK node, wherein the CN node is provided with the PG database and the CITUS component;

the CN node is used for receiving a query field in a query request input by a user and matching a target fragment field corresponding to the query field according to a pre-stored first mapping relation; the first mapping relation is a corresponding relation between the query field and the fragment field;

the CN node is also used for receiving a query result returned after the target WK node queries in the fragments of the PG database of the CN node and sending the query result to the user;

8. The system of claim 7,

the CN node is also used for receiving a query field mapping request input by a user; the query field mapping request comprises a fragment field and at least one query field;

9. The system of claim 7,

and the CN node is also used for fragmenting the source data according to the fragmentation field in the preset fragmentation rule, then storing the fragmented source data in the PG database corresponding to the WK node in a distributed manner, and establishing a second mapping relation between the fragmentation field and the WK node.

10. A computer system, comprising:

one or more processors; and

a memory associated with the one or more processors, the memory to store program instructions that, when read and executed by the one or more processors, perform operations of any of claims 1-6.