Accounting method, apparatus, device, medium and product

Abstract

The present disclosure provides a reconciliation method, device, equipment, medium and product, relates to the technical field of distribution, and can be applied to the technical field of financial technology. The method comprises the following steps: configuring a reconciliation definition table and a reconciliation field attribute table, wherein the reconciliation definition table is used for recording reconciliation data sources and reconciliation matching fields, and the reconciliation field attribute table is used for recording attribute fields of the reconciliation data sources. In response to a reconciliation starting instruction, the reconciliation definition table and the reconciliation field attribute table are called to obtain reconciliation data sources, reconciliation matching fields and attribute fields of the reconciliation data sources of a to-be-reconciled task. According to the reconciliation data sources, the reconciliation matching fields and the attribute fields of the reconciliation data sources of the to-be-reconciled task, a reconciliation job procedure corresponding to the to-be-reconciled task is generated. The reconciliation job procedure is executed to complete reconciliation of target tables and source tables in the to-be-reconciled task.

Description

Technical Field

[0001] This disclosure relates to the field of distributed technology and can be applied to the field of financial technology, and in particular to a reconciliation method, apparatus, device, medium and product. Background Technology

[0002] In credit operations, a robust reconciliation mechanism is crucial for ensuring the normal operation of the credit system. Currently, credit system reconciliation primarily relies on batch reconciliation at night using the mainframe. However, for scenarios with high timeliness requirements, nighttime batch reconciliation often fails to meet these requirements. Furthermore, adding new reconciliation processes necessitates the development of new batch programs, leading to redundant development and severely impacting the efficiency of reconciliation. Summary of the Invention

[0003] In view of this, the main purpose of this disclosure is to provide a reconciliation method, apparatus, equipment, medium and product, which aims to at least partially solve the technical problems of low timeliness of existing reconciliation methods and repetitive development of batch programs.

[0004] To achieve the above objectives, a first aspect of this disclosure provides a reconciliation method, comprising: configuring a reconciliation definition table and a reconciliation field attribute table, wherein the reconciliation definition table is used to record reconciliation data sources and reconciliation matching fields, and the reconciliation field attribute table is used to record attribute fields of the reconciliation data sources; responding to a reconciliation start command, invoking the reconciliation definition table and the reconciliation field attribute table to obtain the reconciliation data source, reconciliation matching fields, and attribute fields of the reconciliation data source for the reconciliation task to be reconciled; generating a reconciliation job program corresponding to the reconciliation task to be reconciled based on the reconciliation data source, reconciliation matching fields, and attribute fields of the reconciliation data source for the reconciliation task to be reconciled; and executing the reconciliation job program to complete the reconciliation of the target table and the source table in the reconciliation task to be reconciled.

[0005] According to an embodiment of this disclosure, generating a reconciliation job program corresponding to the reconciliation task based on the reconciliation data source, reconciliation matching field, and attribute field of the reconciliation data source includes: concatenating the reconciliation data source, reconciliation matching field, and attribute field of the reconciliation data source to generate an HQL query statement as the reconciliation job program.

[0006] According to an embodiment of this disclosure, the step of concatenating the reconciliation data source, the reconciliation matching field, and the attribute field of the reconciliation data source to generate an HQL query statement as the reconciliation job procedure includes: when the number of unilateral accounts in the source table is greater than the number of unilateral accounts in the target table, using the primary key field of the source table and the primary key field of the target table in the attribute field as left join conditions and the target table as the driving table, concatenating the reconciliation data source, the reconciliation matching field, and the attribute field to generate the HQL query statement.

[0007] According to an embodiment of this disclosure, the step of concatenating the reconciliation data source, the reconciliation matching field, and the attribute field of the reconciliation data source to generate an HQL query statement as the reconciliation job procedure further includes: when the number of unilateral accounts in the source table is less than the number of unilateral accounts in the target table, using the primary key field of the source table and the primary key field of the target table in the attribute field as left join conditions and the source table as the driving table, concatenating the reconciliation data source, the reconciliation matching field, and the attribute field to generate the HQL query statement.

[0008] According to an embodiment of this disclosure, the step of concatenating the reconciliation data source, the reconciliation matching field, and the attribute field of the reconciliation data source to generate an HQL query statement as the reconciliation job procedure further includes: when the data in the source table and the target table are inconsistent, using the primary key field of the source table and the primary key field of the target table in the attribute field as inner join conditions, concatenating the reconciliation data source, the reconciliation matching field, and the attribute field to generate the HQL query statement.

[0009] According to an embodiment of this disclosure, the step of executing the reconciliation job program to complete the reconciliation of the target table and the source table in the reconciliation task includes: calling the Spark application programming interface to execute the HQL query statement to complete the reconciliation of the target table and the source table in the reconciliation task.

[0010] According to embodiments of this disclosure, the reconciliation matching field includes a source type flag and a target type flag. Before generating a reconciliation job program corresponding to the reconciliation task based on the reconciliation data source, the reconciliation matching field, and the attribute fields of the reconciliation data source, the method further includes: determining whether the source type flag and the target type flag are HQL statements; if the source type flag and the target type flag are not HQL statements, directly generating the reconciliation job program based on the reconciliation definition table and the reconciliation field attribute table; if the source type flag and the target type flag are HQL statements, creating a view based on the reconciliation definition table and the reconciliation field attribute table, and then generating the reconciliation job program based on the view.

[0011] According to an embodiment of this disclosure, the method further includes: obtaining the reconciliation result obtained from executing the reconciliation operation procedure; analyzing the reconciliation result to determine the difference records between the target table and the source table; and concatenating the difference records according to the attribute fields.

[0012] According to embodiments of this disclosure, the reconciliation matching fields include job number, reconciliation name, source table name, target table name, and reconciliation date fields; the attribute fields include source field name, target field name, result field alias, field sequence number, primary key field, source field function, source field function parameter value, target field function, and target field function parameter value.

[0013] A second aspect of this disclosure provides a reconciliation apparatus, comprising: a configuration module for configuring a reconciliation definition table and a reconciliation field attribute table, wherein the reconciliation definition table records reconciliation data sources and reconciliation matching fields, and the reconciliation field attribute table records attribute fields of the reconciliation data sources; a first acquisition module for, in response to a reconciliation start command, invoking the reconciliation definition table and the reconciliation field attribute table to acquire the reconciliation data source, reconciliation matching fields, and attribute fields of the reconciliation data source for a reconciliation task; a first generation module for, generating a reconciliation job program corresponding to the reconciliation task based on the reconciliation data source, reconciliation matching fields, and attribute fields of the reconciliation data source for the reconciliation task; and an execution module for, executing the reconciliation job program to complete the reconciliation of the target table and the source table in the reconciliation task.

[0014] A third aspect of this disclosure provides an electronic device, including: one or more processors; and a storage device for storing one or more programs, wherein when the one or more programs are executed by the one or more processors, the one or more processors execute the reconciliation method described above.

[0015] A fourth aspect of this disclosure provides a computer-readable storage medium storing executable instructions that, when executed by a processor, cause the processor to perform the reconciliation method described above.

[0016] A fifth aspect of this disclosure provides a computer program product, including a computer program that, when executed by a processor, implements the reconciliation method described above.

[0017] The reconciliation method, apparatus, equipment, medium, and product provided according to the embodiments of this disclosure have at least the following beneficial effects:

[0018] By pre-configuring the reconciliation definition table to record the reconciliation data source and reconciliation matching fields, and pre-configuring the reconciliation field attribute table to govern the attribute fields of the reconciliation data source, the reconciliation job program can be directly generated by calling the reconciliation definition table and reconciliation field attribute table when reconciliation is needed. This effectively improves the efficiency and timeliness of reconciliation integration. When adding a new reconciliation, only the corresponding data in the reconciliation definition table and reconciliation field attributes needs to be configured, eliminating the need for repeated development, saving development costs and improving reconciliation efficiency. Since multiple reconciliations can be configured simultaneously in the reconciliation definition table and reconciliation field attributes, corresponding reconciliation job programs can be generated based on different reconciliation data sources, reconciliation matching fields, and reconciliation data sources. Therefore, multiple reconciliations can be integrated simultaneously, enabling more efficient batch reconciliation. High reusability is also achieved.

[0019] By generating HQL query statements as the reconciliation job program, and because HQL query statements are object-oriented encapsulations of query conditions, they have rich and flexible query features. Therefore, when applied to batch reconciliation and simultaneous access of multiple reconciliation systems, they can efficiently and accurately query reconciliation information, thus achieving efficient and accurate reconciliation.

[0020] Because different HQL query statements are generated for reconciliation based on the different data in the source and target tables, it can meet the reconciliation needs of different accounting situations, thereby improving the flexibility and efficiency of reconciliation.

[0021] Because Spark provides distributed queries and can optimize iterative workloads, executing HQL queries by calling Spark's API to perform reconciliation can better achieve batch reconciliation and reduce the workload of reconciliation equipment.

[0022] Because source type and target type flags are pre-configured to distinguish whether HQL is supported, and different reconciliation job generation methods are used for allocations that support HQL and those that do not, it can meet the reconciliation needs of different reconciliation scenarios and further improve the flexibility of reconciliation.

[0023] Because the difference records are concatenated and stored based on attribute fields, it is convenient to trace back the difference accounting information later. Attached Figure Description

[0024] To more clearly illustrate the technical solutions in the embodiments of this disclosure or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of this disclosure. For those skilled in the art, other drawings can be obtained based on the structures shown in these drawings without creative effort.

[0025] Figure 1 The system architecture 100 of the reconciliation method and apparatus according to an embodiment of the present disclosure is illustrated schematically;

[0026] Figure 2 A flowchart illustrating a reconciliation method according to an embodiment of the present disclosure is shown schematically;

[0027] Figure 3 The flowchart illustrating the generation of the reconciliation operation procedure corresponding to the reconciliation task in operation S203 according to an embodiment of the present disclosure is shown in the illustration.

[0028] Figure 4 A flowchart illustrating the generation of an HQL query statement in operation S301 according to an embodiment of the present disclosure is shown schematically.

[0029] Figure 5 A flowchart illustrating the generation of an HQL query statement in operation S301 according to another embodiment of the present disclosure is shown.

[0030] Figure 6 A flowchart illustrating the generation of an HQL query statement in operation S301 according to yet another embodiment of the present disclosure is shown.

[0031] Figure 7 A flowchart illustrating a reconciliation method according to another embodiment of this disclosure is shown schematically;

[0032] Figure 8 A flowchart illustrating a reconciliation method according to yet another embodiment of the present disclosure is shown schematically;

[0033] Figure 9 A block diagram of an account reconciliation apparatus according to an embodiment of the present disclosure is shown schematically;

[0034] Figure 10 A block diagram of a reconciliation apparatus according to another embodiment of the present disclosure is shown schematically;

[0035] Figure 11 A block diagram of a reconciliation apparatus according to another embodiment of the present disclosure is shown schematically;

[0036] Figure 12 A block diagram of an electronic device suitable for implementing the methods described above, according to embodiments of the present disclosure, is illustrated schematically. Detailed Implementation

[0037] The embodiments of the present disclosure will now be described with reference to the accompanying drawings. However, it should be understood that these descriptions are exemplary only and are not intended to limit the scope of the disclosure. In the following detailed description, numerous specific details are set forth to provide a thorough understanding of the embodiments of the present disclosure for ease of explanation. However, it will be apparent that one or more embodiments may be practiced without these specific details. Furthermore, descriptions of well-known structures and techniques are omitted in the following description to avoid unnecessarily obscuring the concepts of the present disclosure.

[0038] The terminology used herein is for the purpose of describing particular embodiments only and is not intended to limit this disclosure. The terms “comprising,” “including,” etc., as used herein indicate the presence of the stated features, steps, operations, and / or components, but do not exclude the presence or addition of one or more other features, steps, operations, or components.

[0039] All terms used herein (including technical and scientific terms) have the meanings commonly understood by those skilled in the art, unless otherwise defined. It should be noted that the terms used herein are to be interpreted in a manner consistent with the context of this specification, and not in an idealized or overly rigid way.

[0040] When using expressions such as "at least one of A, B, and C," the expression should generally be interpreted in accordance with the meaning commonly understood by a person skilled in the art (e.g., "a system having at least one of A, B, and C" should include, but is not limited to, systems having A alone, having B alone, having C alone, having A and B, having A and C, having B and C, and / or having A, B, and C, etc.). Similarly, when using expressions such as "at least one of A, B, or C," the expression should generally be interpreted in accordance with the meaning commonly understood by a person skilled in the art (e.g., "a system having at least one of A, B, or C" should include, but is not limited to, systems having A alone, having B alone, having C alone, having A and B, having A and C, having B and C, and / or having A, B, and C, etc.).

[0041] The accompanying drawings illustrate several block diagrams and / or flowcharts. It should be understood that some blocks, or combinations thereof, in the block diagrams and / or flowcharts can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, a special-purpose computer, or other programmable data integrity repair apparatus, such that, when executed by the processor, these instructions can create means for implementing the functions / operations described in these block diagrams and / or flowcharts. The technology of this disclosure can be implemented in hardware and / or software (including firmware, microcode, etc.). Alternatively, the technology of this disclosure can take the form of a computer program product stored on a computer-readable storage medium, which is available for use by or in conjunction with an instruction execution system.

[0042] In the technical solutions disclosed herein, the collection, storage, use, processing, transmission, provision, disclosure, and application of information all comply with the provisions of relevant laws and regulations, necessary confidentiality measures have been taken, and they do not violate public order and good morals.

[0043] In the technical solution disclosed herein, if it is necessary to obtain user personal information, the user's authorization or consent is obtained before obtaining or collecting the user's personal information.

[0044] To address the technical problems existing in related technologies, this disclosure provides a reconciliation method, including: configuring a reconciliation definition table and a reconciliation field attribute table, wherein the reconciliation definition table is used to record the reconciliation data source and reconciliation matching fields, and the reconciliation field attribute table is used to record the attribute fields of the reconciliation data source. In response to a reconciliation start command, the reconciliation definition table and the reconciliation field attribute table are invoked to obtain the reconciliation data source, reconciliation matching fields, and attribute fields of the reconciliation data source for the task to be reconciled. Based on the reconciliation data source, reconciliation matching fields, and attribute fields of the reconciliation data source for the task to be reconciled, a reconciliation job program corresponding to the task to be reconciled is generated. The reconciliation job program is executed to complete the reconciliation of the target table and the source table in the task to be reconciled.

[0045] Figure 1 The system architecture 100 of the reconciliation method and apparatus according to an embodiment of the present disclosure is illustrated schematically. It should be noted that... Figure 1 The examples shown are merely examples of system architectures that can be applied to the embodiments of this disclosure, in order to help those skilled in the art understand the technical content of this disclosure, but do not mean that the embodiments of this disclosure cannot be used in other devices, systems, environments or scenarios.

[0046] like Figure 1 As shown, the system architecture 100 according to this embodiment may include a database 101, a network 102, and a server 103. The database 101 and the server 103 communicate via the network 102.

[0047] Database 101 can store pre-configured reconciliation definition tables and reconciliation field attribute tables. The reconciliation definition table records the reconciliation data source and reconciliation matching fields, while the reconciliation field attribute table records the attribute fields of the reconciliation data source.

[0048] Network 102 can include various connection types, such as wired, wireless communication links, or fiber optic cables. Wired connections can utilize cables and any of the following interfaces: Fibre Channel, infrared interface, Type-D data interface, serial interface, USB interface, USB Type-C interface, or Dock interface. Wireless connections can utilize wireless communication technologies such as Bluetooth, Wi-Fi, Infrared, and ZigBee.

[0049] Server 103 can be a server providing various services, such as a backend management server for reconciliation (for example only). In response to the reconciliation initiation command, the backend management server retrieves the reconciliation definition table and reconciliation field attribute table from database 101 via network 102 to obtain the reconciliation data source, reconciliation matching field, and attribute fields of the reconciliation data source for the task to be reconciled. Based on these fields, the server generates a reconciliation job program corresponding to the task and executes it to complete the reconciliation between the target table and the source table in the task.

[0050] It should be noted that the reconciliation method provided in this embodiment can be executed by server 103. Accordingly, the reconciliation device provided in this embodiment can be located in server 103. Alternatively, the reconciliation method provided in this embodiment can also be executed by a server or server cluster that is different from server 103 and capable of local communication with database 101 and / or server 103. Accordingly, the reconciliation device provided in this embodiment can also be located in a server or server cluster that is different from server 103 and capable of local communication with database 101 and / or server 103. Alternatively, the reconciliation method provided in this embodiment can also be partially executed by server 103 and partially executed by database 101. Accordingly, the reconciliation device provided in this embodiment can also be partially located in server 103 and partially located in database 101.

[0051] It should be understood that Figure 1 The number of databases, networks, and servers shown is merely illustrative. Depending on implementation needs, any number of databases, networks, and servers can be included.

[0052] The reconciliation method provided in this disclosure can be applied to the fintech field. For example, with the increasing material and cultural needs, people's demand for loans is growing rapidly. Therefore, more and more financial institutions have developed credit businesses. In credit businesses, a sound reconciliation mechanism is a key factor in ensuring the normal operation of the credit system. However, existing reconciliation mechanisms mainly rely on batch reconciliation by the mainframe at night, which cannot meet the timeliness requirements of reconciliation. Furthermore, there is a situation of redundant development when adding new reconciliations. In this scenario, the reconciliation method provided in this disclosure, through a single reconciliation program, uses job configuration to realize functions such as reconciliation, reconciliation export, and reconciliation transfer at the credit domain level. It can quickly meet the reconciliation needs of the entire credit domain at near-zero cost, accelerate the research and development and promotion of new products, thereby improving the timeliness of reconciliation and avoiding the redundant development of batch programs.

[0053] It should be understood that the reconciliation method provided in this disclosure is not limited to the fintech field, but can also be used in any field other than finance. The above description is merely exemplary, and the reconciliation method of this disclosure can be applied to any field involving reconciliation, such as e-commerce, logistics, and other technical fields.

[0054] The following will be based on Figure 1 Describe the reconciliation scenario, through Figures 2-8 The reconciliation method of this disclosure will be described in detail.

[0055] Figure 2 A flowchart illustrating a reconciliation method according to an embodiment of the present disclosure is shown schematically.

[0056] like Figure 2 As shown, the reconciliation method may include, for example, operations S201 to S204.

[0057] In operation S201, configure the reconciliation definition table and the reconciliation field attribute table.

[0058] In operation S202, in response to the reconciliation start command, the reconciliation definition table and the reconciliation field attribute table are called to obtain the reconciliation data source, reconciliation matching field, and attribute field of the reconciliation data source for the reconciliation task.

[0059] In operation S203, a reconciliation job program corresponding to the reconciliation task is generated based on the reconciliation data source, reconciliation matching field, and attribute field of the reconciliation data source.

[0060] In operation S204, the reconciliation job procedure is executed to complete the reconciliation of the target table and the source table in the reconciliation task.

[0061] In the embodiments of this disclosure, the reconciliation definition table and the reconciliation field attribute table are configured to define different business reconciliation requirements, so as to support single-table reconciliation and HQL (Hibernate Query Language) statement reconciliation related requirements.

[0062] The reconciliation definition table defines the data source and matching fields for reconciliation, including the job number, reconciliation name, source and target table names, reconciliation date, data source (supporting tables and SQL statements), and result storage type. The reconciliation field attribute table defines the specific fields of the reconciliation data source, including the source field name, target field name, comparison result field alias, field sequence number, whether it is a primary key field, source field function, source field function parameter value, target field function, and target field function parameter value.

[0063] In embodiments of this disclosure, the parameters of the reconciliation start command may include, for example, the number of reconciliation job tasks, the reconciliation job queue version, the partition field value, and the job number. The job number is used to match the job numbers configured in the reconciliation definition table, retrieving relevant reconciliation data under the matching job number for reconciliation. The partition field value can be understood as a limiting condition for the data range; if the partition field value is empty, the server time is used to determine the date format of the partition value.

[0064] It should be understood that multiple sets of data related to reconciliation tasks can be configured simultaneously in the reconciliation definition table and the reconciliation field attribute table, and can be distinguished and queried by using the job number as an index.

[0065] According to embodiments of this disclosure, by pre-configuring the reconciliation definition table to record the reconciliation data source and reconciliation matching fields, and pre-configuring the reconciliation field attribute table to regulate the attribute fields of the reconciliation data source, when reconciliation is needed, the reconciliation definition table and reconciliation field attribute table are directly called to generate a reconciliation job program for reconciliation. Therefore, the efficiency of reconciliation access can be effectively improved, and the timeliness of reconciliation can be enhanced. Since adding a new reconciliation only requires configuring the corresponding data in the reconciliation definition table and reconciliation field attributes, no repetitive development is required, saving development costs and improving reconciliation efficiency. Because multiple reconciliations can be configured simultaneously in the reconciliation definition table and reconciliation field attributes, corresponding reconciliation job programs can be generated based on different reconciliation data sources, reconciliation matching fields, and reconciliation data sources. Therefore, multiple reconciliations can be accessed simultaneously, achieving more efficient batch reconciliation and high reusability.

[0066] Figure 3 The flowchart illustrating the generation of a reconciliation job procedure corresponding to the reconciliation task in operation S203 according to an embodiment of the present disclosure is shown.

[0067] like Figure 3As shown, the reconciliation job program corresponding to the reconciliation task in operation S203 may include, for example, operation S301.

[0068] In operation S301, the reconciliation data source, reconciliation matching field, and attribute field of the reconciliation data source are concatenated to generate an HQL query statement as the reconciliation job program.

[0069] In the embodiments of this disclosure, the reconciliation job program can use HQL to concatenate the reconciliation definition configuration based on the parameters in the reconciliation start command. The program searches the reconciliation definition table and reconciliation field attribute table based on the job number in the reconciliation start command to obtain the reconciliation data source, reconciliation matching field, and attribute fields of the reconciliation data source for the task to be reconciled. These are then concatenated to obtain the HQL query statement. For example, if the job number of the task to be reconciled is A, the reconciliation definition table and reconciliation field attribute table can store reconciliation tasks with job numbers 1, 2, 3, and 4. The reconciliation data source, reconciliation matching field, and attribute fields of the reconciliation data source for job number 1 can be obtained from these tables. The HQL query statement for the task to be reconciled with job number 1 can then be concatenated based on these parameters.

[0070] According to embodiments of this disclosure, HQL query statements are generated as reconciliation operation programs. Since HQL query statements are object-oriented encapsulations of query conditions, they have rich and flexible query features. Therefore, when applied to batch reconciliation and simultaneous access of multiple reconciliation sets, they can efficiently and accurately query reconciliation information, thus achieving efficient and accurate reconciliation.

[0071] Figure 4 The flowchart illustrating the generation of an HQL query statement in operation S301 according to an embodiment of the present disclosure is shown schematically.

[0072] like Figure 4 As shown, generating an HQL query statement in operation S301 may include, for example, operation S401.

[0073] In operation S401, when the number of one-sided accounts in the source table is greater than the number of one-sided accounts in the target table, the primary key fields of the source table and the target table in the attribute fields are used as left join conditions, and the target table is the driving table. The reconciliation data source, reconciliation matching field and attribute field are concatenated to generate an HQL query statement.

[0074] For example, if the source table is represented by A and the target table by B, and the number of one-sided transactions in the source table A is greater than the number of one-sided transactions in the target table B, the HQL query statement can be constructed by traversing the reconciliation definition table and the reconciliation field attribute table, and can be denoted as the HQL query statement AB, using the primary key field as the left join condition:

[0075] SELECT a.source_table_fields_result_field_name, ...

[0076] from source table name a

[0077] LEFT JOIN target_table_name b ON a.source_table_field(primary_key) = b.target_table_field(primary_key) AND ...

[0078] and a. Source table date partition field = b. Target partition field

[0079] where b. target table field (primary key) is null

[0080] and a. The source table's date partition field = the partition value.

[0081] Figure 5 A flowchart illustrating the generation of an HQL query statement in operation S301 according to another embodiment of the present disclosure is shown.

[0082] like Figure 5 As shown, generating an HQL query statement in operation S301 may include, for example, operation S501.

[0083] In operation S501, when the number of one-sided accounts in the source table is less than the number of one-sided accounts in the target table, the primary key fields of the source table and the target table in the attribute fields are used as left join conditions, and the source table is the driving table. The reconciliation data source, reconciliation matching field and attribute field are concatenated to generate an HQL query statement.

[0084] For example, if the source table is represented by A and the target table by B, and the number of one-sided accounts in the source table A is less than the number of one-sided accounts in the target table B, then we can iterate through the reconciliation definition table and the reconciliation field attribute table and concatenate the HQL query statement. This can be represented as an HQL query statement for BA, using the primary key field as the left join condition:

[0085] SELECT b.target_table_fields_result_field_name, ...

[0086] from target table name b

[0087] LEFT JOIN source_table_name_a ON a.source_table_field(primary_key) = b.target_table_field(primary_key) AND...

[0088] and a. Source table date partition field = b. Target partition field

[0089] where a.source table field (primary key) is null

[0090] and b. Target table date partition field = partition value.

[0091] Figure 6 The flowchart illustrating the generation of an HQL query statement in operation S301 according to yet another embodiment of the present disclosure is shown.

[0092] like Figure 6 As shown, generating an HQL query statement in operation S301 may include, for example, operation S601.

[0093] When operating S601, if the data in the source table and the target table are inconsistent, the primary key fields of the source table and the target table in the attribute fields are used as inner join conditions. The reconciliation data source, reconciliation matching field and attribute field are concatenated to generate an HQL query statement.

[0094] For example, if the source table is represented by A and the target table by B, and the number of one-sided accounts in the source table A is less than the number of one-sided accounts in the target table B, the HQL query statement can be constructed by traversing the reconciliation definition table and the reconciliation field attribute table, and can be denoted as the HQL query statement AB, using the primary key field as the inner join condition:

[0095] SELECT a.source_table_fields_result_field_name, ...

[0096] b. Target table field b, result field name, ...

[0097] from source table name a

[0098] Inner join target_table_name b on a.source_table_field(primary_key) = b.target_table_field(primary_key)

[0099] and......

[0100] and a. Source table date partition field = b. Target partition field

[0101] where a.source table date partition field = partition value.

[0102] According to the embodiments of this disclosure, since HQL query statements are generated for reconciliation based on the different data in the source and target tables, reconciliation can be performed for different accounting situations, thereby improving the flexibility and efficiency of reconciliation.

[0103] Furthermore, the execution of the reconciliation job in operation S204 to complete the reconciliation of the target table and source table in the reconciliation task may include: calling the Spark application programming interface to execute HQL query statements to complete the reconciliation of the target table and source table in the reconciliation task.

[0104] Spark can be understood as an open-source cluster computing environment similar to Hadoop, but there are some differences between the two. These useful differences make Spark perform better on certain workloads. In other words, Spark enables in-memory distributed datasets, which, in addition to providing interactive queries, can also optimize iterative workloads.

[0105] According to embodiments of this disclosure, since Spark can provide distributed queries and optimize iterative workloads, executing HQL queries by calling Spark's application programming interface to complete reconciliation can better achieve batch reconciliation and reduce the workload of reconciliation devices.

[0106] Figure 7 A flowchart illustrating a reconciliation method according to another embodiment of this disclosure is shown schematically.

[0107] like Figure 7 As shown, the reconciliation matching field may also include a source type flag and a target type flag. Before executing operation S203 to generate a reconciliation job program corresponding to the reconciliation task based on the reconciliation data source, the reconciliation matching field, and the attribute fields of the reconciliation data source, the reconciliation method may, for example, perform operations S701 to S703.

[0108] In operation S701, determine whether the source type flag and target type flag are HQL statements.

[0109] When operating S702, if the source type flag and target type flag are not HQL statements, the reconciliation job program is generated directly based on the reconciliation definition table and the reconciliation field attribute table.

[0110] When operating S703, with the source type flag and target type flag set to HQL statement, a view is created based on the reconciliation definition table and the reconciliation field attribute table, and then a reconciliation job program is generated based on the view.

[0111] For example, if you need to create a temporary view, you can use the statement: hiveContext.sql("hql statement").createOrReplaceTempView("view name") to create the view and return the view name.

[0112] According to embodiments of this disclosure, since source type flags and target type flags are pre-configured to distinguish whether HQL is supported, different reconciliation job program generation methods are adopted for the two types of allocations that support HQL and do not support HQL. Therefore, it can meet the reconciliation needs of different reconciliation scenarios and further improve the flexibility of reconciliation.

[0113] Figure 8A flowchart illustrating a reconciliation method according to yet another embodiment of the present disclosure is shown.

[0114] like Figure 8 As shown, the reconciliation method can also be operated through S801 to S803.

[0115] In operation S801, obtain the reconciliation results obtained from the execution of the reconciliation operation procedure.

[0116] In operation S802, the reconciliation results are analyzed to identify the discrepancies between the target table and the source table.

[0117] In operation S803, the difference records are concatenated based on the attribute fields.

[0118] For example, let's analyze the result sets obtained by executing the above three HQL query statements:

[0119] If the result of the HQL query in AB is not empty, each record in this result set can be, for example, 2-A single-sided account.

[0120] If the result of the HQL query in BA is not empty, each record in this result set can be, for example, 3-B single-sided ledger.

[0121] The execution results of the HQL query for AB are iterated through, and the elements of each row are compared. The comparison results may look like the following:

[0122] Elements of consistency: 1-consistency;

[0123] 4-Inconsistent elements: (Both A and B exist, but there are differences. It is necessary to describe which fields are different).

[0124] If the execution results of the HQL query statements of AB and BA are both empty, and there are no records with inconsistent elements in the execution results of the HQL query statements of AB, then the overall comparison result table (reconciliation result table) will be 1 - consistent.

[0125] After obtaining the comparison results, save them to the results table (details table). You can concatenate the difference records according to the field order configured in the reconciliation field attribute table, using ||| as the separator. If records A and B have inconsistent elements, only one record is saved: record A is stored in the result one field; record B is stored in the result two field; and the element differences are stored in the diff value field.

[0126] After saving the comparison results, you can export the results file. You can configure an export job to complete the export of the comparison results. The file name can be: YYYYMMDD.txt (the table name of the reconciliation details table). The file content can be: each row of data is composed of field values ​​from the "Reconciliation Result Details Table" concatenated using char esc=27 as intervals.

[0127] After the comparison results are completed, it is necessary to determine whether there are still tasks that need to be reconciled. If there are still tasks that need to be reconciled, return to operation S202, read the configuration data based on the job number of the current task to be reconciled, and continue to execute the reconciliation operation; if there are no tasks to be reconciled, the reconciliation operation ends.

[0128] According to embodiments of this disclosure, since the difference records are concatenated and stored based on attribute fields, it is convenient to retrospectively review the difference accounting information.

[0129] In summary, the reconciliation method provided by the embodiments of this disclosure, through a configurable reconciliation device, allows a single reconciliation logic to support multiple reconciliation jobs. The configurable reconciliation definition table and reconciliation field attribute table support multiple reconciliation accesses; only the data in these two tables needs to be configured to add new reconciliation scenarios. This near-zero-cost integration effectively improves access efficiency and saves development costs. Furthermore, it supports independent concurrent scheduling of multiple jobs, enabling efficient reconciliation. In addition, this reconciliation method has low maintenance costs and high reusability.

[0130] based on Figures 2-8 The reconciliation method shown in this disclosure also provides a reconciliation device, which will be based on the following embodiments. Figure 1 The described scene, through Figures 9-11 The reconciliation apparatus according to embodiments of this disclosure will be described.

[0131] Figure 9 A block diagram of a reconciliation apparatus according to an embodiment of the present disclosure is shown schematically.

[0132] like Figure 9 As shown, the reconciliation device 900 may include a configuration module 910, a first acquisition module 920, a first generation module 930, and an execution module 940.

[0133] Configuration module 910 is used to configure the reconciliation definition table and the reconciliation field attribute table. The reconciliation definition table records the reconciliation data source and the reconciliation matching fields, while the reconciliation field attribute table records the attribute fields of the reconciliation data source. Configuration module 910 can be used to execute the operation S201 described above, which will not be repeated here.

[0134] The first acquisition module 920 is used to respond to the reconciliation start command by calling the reconciliation definition table and the reconciliation field attribute table to obtain the reconciliation data source, reconciliation matching field, and attribute fields of the reconciliation data source for the reconciliation task. The acquisition module 920 can be used to perform the operation S202 described above, which will not be repeated here.

[0135] The first generation module 930 is used to generate a reconciliation job program corresponding to the reconciliation task based on the reconciliation data source, reconciliation matching fields, and attribute fields of the reconciliation data source. The generation module 930 can be used to execute the operation S203 described above, which will not be repeated here.

[0136] Execution module 940 is used to execute the reconciliation job program to complete the reconciliation of the target table and the source table in the reconciliation task. Execution module 940 can be used to execute the operation S204 described above, which will not be repeated here.

[0137] Figure 10 A block diagram of a reconciliation apparatus according to another embodiment of the present disclosure is shown schematically.

[0138] like Figure 10 As shown, the reconciliation device 900 may also include, for example, a determination module 950, a second generation module 960, and a third generation module 970.

[0139] The first determination module 950 is used to determine whether the source type flag and the target type flag are HQL statements. The determination module 950 can be used to execute the operation S701 described above, which will not be repeated here.

[0140] The second generation module 960 is used to directly generate a reconciliation job program based on the reconciliation definition table and the reconciliation field attribute table when the source type flag and the target type flag are not HQL statements. The second generation module 960 can be used to execute the operation S702 described above, which will not be repeated here.

[0141] The third generation module 970 is used to create a view based on the reconciliation definition table and the reconciliation field attribute table when the source type flag and the target type flag are HQL statements, and then generate a reconciliation job program based on the view. The third generation module 970 can be used to execute the operation S703 described above, which will not be repeated here.

[0142] Figure 11 A block diagram of a reconciliation apparatus according to another embodiment of the present disclosure is shown schematically.

[0143] like Figure 11 As shown, the reconciliation device 900 may also include, for example, a second acquisition module 980, a second determination module 990, and a splicing module 9100.

[0144] The second acquisition module 980 is used to acquire the reconciliation results obtained from the reconciliation operation. The second acquisition module 980 can be used to execute the operation S801 described above, which will not be repeated here.

[0145] The second determination module 990 is used to analyze the reconciliation results and identify the difference records between the target table and the source table. The second determination module 990 can be used to perform the operation S802 described above, which will not be repeated here.

[0146] The splicing module 9100 is used to splice difference records based on attribute fields. The splicing module 9100 can be used to perform the operation S803 described above, which will not be repeated here.

[0147] Any one or more of the modules, submodules, units, and subunits according to embodiments of this disclosure, or at least part of the functions of any one or more of them, can be implemented in one module. Any one or more of the modules, submodules, units, and subunits according to embodiments of this disclosure can be implemented by dividing them into multiple modules. Any one or more of the modules, submodules, units, and subunits according to embodiments of this disclosure can be at least partially implemented as hardware circuitry, such as Field Programmable Gate Arrays (FPGAs), Programmable Logic Arrays (PLAs), Systems-on-Chip, Systems-on-Substrate, Systems-on-Package, Application-Specific Integrated Circuits (ASICs), or implemented in hardware or firmware by any other reasonable means of integrating or packaging circuitry, or implemented in software, hardware, or firmware, or in any suitable combination of any of these three implementation methods. Alternatively, one or more of the modules, submodules, units, and subunits according to embodiments of this disclosure can be at least partially implemented as computer program modules, which, when run, can perform corresponding functions.

[0148] For example, any and multiple modules among the configuration module 910, the first acquisition module 920, the first generation module 930, the execution module 940, the determination module 950, the second generation module 960, the third generation module 970, the second acquisition module 980, the second determination module 990, and the splicing module 9100 can be combined into one module / unit / subunit, or any one of these modules / units / subunits can be split into multiple modules / units / subunits. Alternatively, at least some of the functions of one or more of these modules / units / subunits can be combined with at least some of the functions of other modules / units / subunits and implemented in one module / unit / subunit. According to embodiments of this disclosure, at least one of the configuration module 910, the first acquisition module 920, the first generation module 930, the execution module 940, the determination module 950, the second generation module 960, the third generation module 970, the second acquisition module 980, the second determination module 990, and the splicing module 9100 can be at least partially implemented as hardware circuits, such as field-programmable gate arrays (FPGAs), programmable logic arrays (PLAs), systems-on-a-chip, systems-on-a-substrate, systems-on-package, application-specific integrated circuits (ASICs), or implemented in hardware or firmware by any other reasonable means of integrating or packaging circuits, or implemented in any one of the three methods of software, hardware, and firmware, or in a suitable combination of any of them. Alternatively, at least one of the configuration module 910, the first acquisition module 920, the first generation module 930, the execution module 940, the determination module 950, the second generation module 960, the third generation module 970, the second acquisition module 980, the second determination module 990, and the splicing module 9100 can be at least partially implemented as a computer program module, which can perform corresponding functions when the computer program module is run.

[0149] It should be noted that the reconciliation device part in the embodiments of this disclosure corresponds to the reconciliation method part in the embodiments of this disclosure, and their specific implementation details and the resulting technical effects are the same, which will not be repeated here.

[0150] Figure 12 A block diagram of an electronic device suitable for implementing the methods described above, according to embodiments of the present disclosure, is illustrated schematically. Figure 12 The electronic device shown is merely an example and should not be construed as limiting the functionality and scope of the embodiments disclosed herein.

[0151] like Figure 12As shown, an electronic device 1200 according to an embodiment of the present disclosure includes a processor 1201, which can perform various appropriate actions and processes according to a program stored in a read-only memory (ROM) 1202 or a program loaded from a storage portion 1208 into a random access memory (RAM) 1203. The processor 1201 may include, for example, a general-purpose microprocessor (e.g., a CPU), an instruction set processor and / or an associated chipset and / or a special-purpose microprocessor (e.g., an application-specific integrated circuit (ASIC)), etc. The processor 1201 may also include onboard memory for caching purposes. The processor 1201 may include a single processing unit or multiple processing units for performing different actions of the method flow according to an embodiment of the present disclosure.

[0152] RAM 1203 stores various programs and data required for the operation of electronic device 1200. Processor 1201, ROM 1202, and RAM 1203 are interconnected via bus 1204. Processor 1201 performs various operations of the method flow according to embodiments of the present disclosure by executing programs in ROM 1202 and / or RAM 1203. It should be noted that the programs may also be stored in one or more memories other than ROM 1202 and RAM 1203. Processor 1201 may also perform various operations of the method flow according to embodiments of the present disclosure by executing programs stored in said one or more memories.

[0153] According to embodiments of this disclosure, the electronic device 1200 may further include an input / output (I / O) interface 1205, which is also connected to the bus 1204. The electronic device 1200 may also include one or more of the following components connected to the I / O interface 1205: an input section 1206 including a keyboard, mouse, etc.; an output section 1207 including a cathode ray tube (CRT), liquid crystal display (LCD), etc., and a speaker, etc.; a storage section 1208 including a hard disk, etc.; and a communication section 1209 including a network interface card such as a LAN card, modem, etc. The communication section 1209 performs communication processing via a network such as the Internet. A drive 1210 is also connected to the I / O interface 1205 as needed. A removable medium 1211, such as a disk, optical disk, magneto-optical disk, semiconductor memory, etc., is installed on the drive 1210 as needed so that computer programs read from it can be installed into the storage section 1208 as needed.

[0154] According to embodiments of this disclosure, the method flow according to embodiments of this disclosure can be implemented as a computer software program. For example, embodiments of this disclosure include a computer program product comprising a computer program carried on a computer-readable storage medium, the computer program containing program code for performing the methods shown in the flowchart. In such embodiments, the computer program can be downloaded and installed from a network via communication section 1209, and / or installed from removable medium 1211. When the computer program is executed by processor 1201, it performs the functions defined in the system of embodiments of this disclosure. According to embodiments of this disclosure, the systems, devices, apparatuses, modules, units, etc., described above can be implemented by computer program modules.

[0155] This disclosure also provides a computer-readable storage medium, which may be included in the device / apparatus / system described in the above embodiments; or it may exist independently and not assembled into the device / apparatus / system. The computer-readable storage medium carries one or more programs that, when executed, implement the method according to the embodiments of this disclosure.

[0156] According to embodiments of this disclosure, the computer-readable storage medium can be a non-volatile computer-readable storage medium. Examples include, but are not limited to: portable computer disks, hard disks, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), portable compact disk read-only memory (CD-ROM), optical storage devices, magnetic storage devices, or any suitable combination thereof. In this disclosure, the computer-readable storage medium can be any tangible medium that contains or stores a program that can be used by or in conjunction with an instruction execution system, apparatus, or device.

[0157] For example, according to embodiments of this disclosure, a computer-readable storage medium may include the ROM 1202 and / or RAM 1203 described above and / or one or more memories other than ROM 1202 and RAM 1203.

[0158] The flowcharts and block diagrams in the accompanying drawings illustrate the architecture, functionality, and operation of possible implementations of systems, methods, and computer program products according to various embodiments of the present disclosure. In this regard, each block in a flowchart or block diagram may represent a module, segment, or portion of code containing one or more executable instructions for implementing a specified logical function. It should also be noted that in some alternative implementations, the functions indicated in the blocks may occur in a different order than those indicated in the drawings. For example, two consecutively indicated blocks may actually be executed substantially in parallel, and they may sometimes be executed in reverse order, depending on the functions involved. It should also be noted that each block in a block diagram or flowchart, and combinations of blocks in a block diagram or flowchart, may be implemented using a dedicated hardware-based system that performs the specified function or operation, or using a combination of dedicated hardware and computer instructions. Those skilled in the art will understand that the features recited in the various embodiments and / or claims of this disclosure can be combined and / or combined in various ways, even if such combinations or combinations are not expressly described in this disclosure. In particular, the features described in the various embodiments and / or claims of this disclosure may be combined and / or combined in various ways without departing from the spirit and teachings of this disclosure. All such combinations and / or combinations fall within the scope of this disclosure.

Claims

1. A reconciliation method, comprising: Configure a reconciliation definition table and a reconciliation field attribute table, wherein the reconciliation definition table is used to record the reconciliation data source and the reconciliation matching field, and the reconciliation field attribute table is used to record the attribute fields of the reconciliation data source; In response to the reconciliation start command, the reconciliation definition table and the reconciliation field attribute table are called to obtain the reconciliation data source, reconciliation matching field and attribute field of the reconciliation data source for the reconciliation task to be reconciled; Based on the reconciliation data source, reconciliation matching field, and attribute field of the reconciliation data source of the reconciliation task to be reconciled, generate a reconciliation job program corresponding to the reconciliation task to be reconciled. The reconciliation operation procedure is executed to complete the reconciliation of the target table and the source table in the reconciliation task. The step of generating a reconciliation job program corresponding to the reconciliation task based on the reconciliation data source, reconciliation matching field, and attribute field of the reconciliation data source includes: The reconciliation data source, reconciliation matching field, and attribute field of the reconciliation data source are concatenated to generate an HQL query statement as the reconciliation operation program; The step of concatenating the reconciliation data source, the reconciliation matching field, and the attribute field of the reconciliation data source to generate an HQL query statement as the reconciliation operation program includes: Based on the number of one-sided ledgers in the source table and the number of one-sided ledgers in the target table, determine the connection conditions between the driving table and the primary key fields of the source table and the target table. The HQL query statement is generated by concatenating the reconciliation data source, the reconciliation matching field, and the attribute field based on the driving table and the join conditions.

2. The reconciliation method according to claim 1, wherein, The step of determining the join conditions for the driving table and the primary key fields of the source table and the target table based on the number of one-sided transactions in the source table and the number of one-sided transactions in the target table includes: If the number of one-sided accounts in the source table is greater than the number of one-sided accounts in the target table, the primary key fields of the source table and the target table in the attribute fields are used as the left join condition, and the target table is the driving table.

3. The reconciliation method according to claim 1, wherein, The step of determining the join conditions for the driving table and the primary key fields of the source table and the target table based on the number of one-sided transactions in the source table and the number of one-sided transactions in the target table further includes: If the number of one-sided accounts in the source table is less than the number of one-sided accounts in the target table, the primary key fields of the source table and the target table in the attribute fields are used as the left join condition, and the source table is the driving table.

4. The reconciliation method according to claim 1, wherein, The step of determining the join conditions for the driving table and the primary key fields of the source table and the target table based on the number of one-sided transactions in the source table and the number of one-sided transactions in the target table further includes: If the data in the source table and the target table are inconsistent, the primary key fields of the source table and the target table in the attribute fields are used as the inner join conditions.

5. The reconciliation method according to claim 1, wherein, The process of executing the reconciliation operation to complete the reconciliation of the target table and the source table in the reconciliation task includes: The Spark application programming interface is invoked to execute the HQL query statement to complete the reconciliation of the target table and the source table in the reconciliation task.

6. The reconciliation method according to claim 1, wherein the reconciliation matching field includes a source type flag and a target type flag; before generating a reconciliation job program corresponding to the reconciliation task based on the reconciliation data source, the reconciliation matching field, and the attribute fields of the reconciliation data source, the method further includes: Determine whether the source type flag and target type flag are HQL statements; If the source type flag and the target type flag are not HQL statements, the reconciliation job program is generated directly based on the reconciliation definition table and the reconciliation field attribute table. When the source type flag and target type flag are HQL statements, a view is created based on the reconciliation definition table and the reconciliation field attribute table, and then the reconciliation job program is generated based on the view.

7. The reconciliation method according to claim 1, further comprising: Obtain the reconciliation results obtained from executing the reconciliation operation procedure; The reconciliation results are analyzed to identify the discrepancies between the target table and the source table. The difference records are concatenated based on the attribute fields.

8. The reconciliation method according to claim 1, wherein, The reconciliation matching fields include job number, reconciliation name, source table name, target table name, and reconciliation date. The attribute fields include source field name, target field name, result field alias, field sequence number, primary key field, source field function, source field function parameter value, target field function, and target field function parameter value.

9. A reconciliation device, comprising: The configuration module is used to configure the reconciliation definition table and the reconciliation field attribute table. The reconciliation definition table is used to record the reconciliation data source and the reconciliation matching field, and the reconciliation field attribute table is used to record the attribute fields of the reconciliation data source. The first acquisition module is used to respond to the reconciliation start command by calling the reconciliation definition table and the reconciliation field attribute table to obtain the reconciliation data source, reconciliation matching field and attribute field of the reconciliation data source of the reconciliation task to be reconciled. The first generation module is used to generate a reconciliation job program corresponding to the reconciliation task based on the reconciliation data source, reconciliation matching field and attribute field of the reconciliation data source. The execution module is used to execute the reconciliation operation program to complete the reconciliation of the target table and the source table in the reconciliation task. The step of generating a reconciliation job program corresponding to the reconciliation task based on the reconciliation data source, reconciliation matching field, and attribute field of the reconciliation data source includes: The reconciliation data source, reconciliation matching field, and attribute field of the reconciliation data source are concatenated to generate an HQL query statement as the reconciliation operation program; The step of concatenating the reconciliation data source, the reconciliation matching field, and the attribute field of the reconciliation data source to generate an HQL query statement as the reconciliation operation program includes: Based on the number of one-sided ledgers in the source table and the number of one-sided ledgers in the target table, determine the connection conditions between the driving table and the primary key fields of the source table and the target table. The HQL query statement is generated by concatenating the reconciliation data source, the reconciliation matching field, and the attribute field based on the driving table and the join conditions.

10. An electronic device, comprising: One or more processors; Storage device for storing one or more programs. Wherein, when the one or more programs are executed by the one or more processors, the one or more processors perform the method according to any one of claims 1 to 8.

11. A computer-readable storage medium storing executable instructions that, when executed by a processor, cause the processor to perform the method according to any one of claims 1 to 8.

12. A computer program product comprising a computer program that, when executed by a processor, implements the method according to any one of claims 1 to 8.

Images