CN117056569A

CN117056569A - SQL blood margin influence analysis method and system

Info

Publication number: CN117056569A
Application number: CN202311093360.6A
Authority: CN
Inventors: 鄢泽来; 吴弟忠; 谢长江
Original assignee: Chongqing Fumin Bank Co Ltd
Current assignee: Chongqing Fumin Bank Co Ltd
Priority date: 2023-08-28
Filing date: 2023-08-28
Publication date: 2023-11-14

Abstract

The application relates to the field of SQL blood margin influence analysis, and discloses a method and a system based on SQL blood margin influence analysis. The method comprises the steps of S1, carrying out data embedding on the basis of a wind control system in a big data intelligent wind control management system, extracting wind control blood edge metadata in real time, abstracting the wind control blood edge metadata into nodes by utilizing a graph database technology, abstracting the association relationship among the wind control blood edge metadata into edges, abstracting the wind control blood edge metadata related characteristics into node attributes, and constructing a wind control component blood edge map; step S2, analyzing the SQL statement, simultaneously rewriting SQL and executing the displain plan, and analyzing to obtain an SQL analysis result; and S3, carrying out multidimensional and multipath query through a graph database engine based on the blood-source map of the wind control component according to the SQL analysis result, and carrying out data revision relevance influence analysis. The application can make objective influence assessment with data support for the business influence range of the wind control management system.

Description

SQL blood margin influence analysis method and system

Technical Field

The application relates to the field of SQL blood margin influence analysis, in particular to a method and a system based on SQL blood margin influence analysis.

Background

In the big data intelligent wind control management system of internet banking, the system mainly comprises three plates: and (3) controlling a part feeding flow, approving a decision rule and processing and calculating characteristic variables. Each panel relies on a series of systems and components for service support. The system and the components are numerous, and the interaction between the systems and the components is extremely complex. Even a sophisticated risk domain expert has difficulty in describing and knowing which systems, components and variables are associated with which wind control components and which databases and which database tables under specific conditions, the traditional document-based form maintenance workload is large, updating is not timely, and meanwhile, documents cannot vividly express the association relationship among the system components.

For example, a credit risk policy may have multiple policy sets for splitting, under which a policy set is formed by multiple policies together, under which a policy execution flow (decision flow) is arranged for arranging policy execution flows under each policy set, each policy under which multiple risk policy rules are included, each rule then involving multiple expression calculations, each expression calculation involving a reference to multiple feature variables, each feature variable being attributed to a different data service interface distributed among third party companies, several bins and wind-controlled internal business services, and the processing of feature variables being dependent on the single data service interface or multiple service interface data mix processing. There are many other risk components such as: decision tables, decision trees, decision matrices, scoring cards, data fits, script rules, champion challenges, and the like.

For the above complex network association, it is difficult to quickly comb out the associated components of a certain credit product from the document accumulated in history, or find the association between components, such as: what data services it depends on, which tables the data services correspond to are used and which fields are involved in the process? What feed parameters need to be used as dynamic conditions? What its complete processing logic is.

Objectively, because the reliability of the system is limited by the limited resources such as CPU, memory, disk capacity, network bandwidth and the like, when equipment is aged, failed, flow fluctuation and security hole reach the set capacity upper limit and programming defect, data storage failure or storage error is caused, namely, abnormal data needs to be repaired. Simultaneously, with each updating and upgrading of the system, strategy iteration and variable logic change, carrying out certain operations of new and deletion of a database table, table structure change and change of data in the table; for each change, which system, which risk component, which variable and which credit product business are affected, the scope of the influence is large, objective evaluation of data support is not needed, and the associated influence and problem caused by data revision operation are difficult to check. Therefore, it is necessary to make an objective impact assessment with data support for system stability and business impact scope.

Disclosure of Invention

The application aims to provide a SQL blood-margin-based influence analysis method and system, which aim to make objective influence assessment with data as support on the business influence range of a wind control management system, thereby reducing operation risk, quickly positioning problems and reducing user loss.

In order to achieve the above purpose, the application adopts the following technical scheme:

an SQL blood margin based impact analysis method comprises the following steps:

step S1, carrying out data embedding on a wind control system based on a piece feeding flow control, decision rule approval and characteristic variable calculation plate in a big data intelligent wind control management system, extracting wind control blood margin metadata in real time, abstracting the wind control blood margin metadata into nodes by using a graph database technology, abstracting the association relationship among the wind control blood margin metadata into edges, abstracting the wind control blood margin metadata related characteristics into node attributes, and constructing a wind control component blood margin map;

step S2, analyzing the SQL statement, simultaneously rewriting SQL and executing the displain plan, and analyzing to obtain an SQL analysis result;

and S3, carrying out multidimensional and multipath query through a graph database engine based on the blood-source map of the wind control component according to the SQL analysis result, and carrying out data revision relevance influence analysis.

The principle and the advantages of the scheme are as follows: when the method is actually applied, the wind control system of the feeding flow control, decision rule approval and characteristic variable computing plate is three plates in a big data intelligent wind control management system of an internet bank, wind control blood margin metadata is obtained from the wind control system of the feeding flow control, decision rule approval and characteristic variable computing plate, and a data source of the wind control blood margin metadata covers the whole bank risk management field, and the wind control blood margin metadata has wide coverage and strong universality; by constructing the wind control component blood margin map, the wind control blood margin metadata and the relationship between the wind control blood margin metadata are simple and visual, and the method is favorable for quickly inquiring the association relationship of a large number of wind control blood margin metadata, and has high inquiring efficiency; by carrying out data revision relevance influence analysis, the business influence range can be mastered when abnormal data is repaired, so that the system is maintained stably.

Preferably, as a modification, the step S1 further includes:

s11, initializing the blood-margin map data of the wind control assembly by using a MERGE grammar, and recording the node data of the blood-margin map of the wind control assembly;

and step S12, real-time data synchronization, namely, based on buried point data, sending MQ information and graph data recording engine consumption information, initiating single-node data synchronization by calling a dubbo service interface provided by a wind control system corresponding to the buried point data, and finally storing the latest nodes, node attributes and edges among the nodes into a graph database.

The technical effects are as follows: the timeliness can be improved, and each change in the wind control field is timely synchronized to the blood margin map of the wind control component.

Preferably, in step S1, a blood-margin map of the net-shaped visualized wind-control component is constructed by using Neo4 j.

The technical effects are as follows: the Neo4j is adopted to construct the blood margin map of the net-shaped visual wind control assembly, so that the problems of high system design complexity and difficult maintenance existing in the relational database in the prior art can be reduced, the visual image is visual, the maintenance is easy, and the path retrieval before the nodes is convenient and efficient.

Preferably, as a modification, the SQL parsing result in the step S2 includes a database name, a database table name, and a database table field.

The technical effects are as follows: the method can be used for supporting data for the next step of data correction influence analysis, and the database names, the table names and the field names are used as query conditions and put into a graph data engine to search the influence analysis.

Preferably, in step S2, a ruid SQL Parser is used to parse the SQL statement.

The technical effects are as follows: the drud SQL Parser technology is packaged based on a tool package provided by the drud, and can support SQL with higher complexity and wider coverage.

Preferably, as an improvement, the data revision relevance impact analysis result includes a record number and a graph data analysis result, and the graph data analysis result includes a node type total number, a node number and a detail list.

The technical effects are as follows: after the record number and the graph data analysis result are obtained, it is determined whether the repair number operation can be executed or the direct repair number is not allowed.

Preferably, as an improvement, the data embedding point specifically comprises: each wind control system related to wind control blood margin metadata is used as an event message production end, event messages are pushed to message queue middleware RocketMQ/Kafka, the event messages comprise event IDs, event types, service contexts and event occurrence time, a wind control component blood margin map construction end is used as a consumption end of the event messages, the messages in the event message middleware are consumed, a corresponding metadata query interface is called according to the event types, and the wind control blood margin metadata of the type is queried; the interfaces of the query support http and dubbo type interfaces.

The technical effects are as follows: wind control blood margin metadata can be extracted in real time through the data embedding points.

An SQL-based blood-lineage impact analysis system, comprising:

the wind control component blood margin map construction module is used for carrying out data embedding on the wind control system based on the inlet flow control, decision rule approval and characteristic variable calculation plate in the big data intelligent wind control management system, extracting wind control blood margin metadata in real time, abstracting the wind control blood margin metadata into nodes by utilizing a graph database technology, abstracting the association relationship among the wind control blood margin metadata into edges, abstracting the wind control blood margin metadata related characteristics into node attributes, and constructing a wind control component blood margin map;

the SQL analysis module is used for analyzing the SQL statement, simultaneously rewriting the SQL and executing the explatin plan, and analyzing to obtain an SQL analysis result;

and the influence analysis module is used for carrying out multidimensional and multipath query through a graph database engine based on the blood-cause map of the wind control component according to the SQL analysis result and carrying out data revision relevance influence analysis.

Preferably, as an improvement, the wind control component blood-edge map construction module further comprises:

the initialization submodule initializes the blood-margin map data of the wind control assembly by using the MERGE grammar and records the node data of the blood-margin map of the wind control assembly;

and the real-time data synchronization sub-module is used for sending MQ information based on the buried point data, the graph data recording engine consumes the information, initiating single-node data synchronization by calling a dubbo service interface provided by the wind control system corresponding to the buried point data, and finally storing the latest nodes, node attributes and edges among the nodes into the graph database.

Preferably, as an improvement, the method further comprises a result export module exporting the data revision relevance influence analysis result to excel.

Drawings

FIG. 1 is a flow chart of a method for analyzing blood-lineage influence based on SQL.

FIG. 2 is a schematic diagram of an SQL-based blood-margin impact analysis system.

Detailed Description

The following is a further detailed description of the embodiments:

the embodiment is basically as shown in fig. 1 and 2:

an SQL blood margin based impact analysis method comprises the following steps:

step S1, carrying out data embedding on a wind control system based on a piece feeding flow control, decision rule approval and characteristic variable calculation plate in a big data intelligent wind control management system, extracting wind control blood margin metadata in real time, abstracting the wind control blood margin metadata into nodes by using a graph database technology, abstracting the association relationship among the wind control blood margin metadata into edges, abstracting the wind control blood margin metadata related characteristics into node attributes, and constructing a net-shaped visual wind control component blood margin map by adopting Neo4 j;

the existing relation-based database is used for constructing the blood-relation, more than dozens of hundred tables can be maintained, if the node relation is changed, the table structure change range is relatively large, the original association relation among the nodes cannot be intuitively displayed, the path retrieval among the nodes is limited, more than 3 layers are realized by adding auxiliary tables, the complexity of system design is increased, and the maintenance is difficult;

the number of tables can be exponentially increased along with the number of association layers, if all relation table structures related to relation change among components are required to be updated, the relation structures are difficult to maintain and have high coupling degree, the common multi-level relation query requirement can be realized by writing complex SQL (structured query language) and sometimes by means of code logic, slow query is easy to cause, database performance is dragged, and meanwhile, the analysis function also causes challenges to program writing and data analysis personnel; the Neo4j graph database can solve the problems, the true relationship among the node data is a net structure, the node data is visual and image and easy to maintain, and the node data path searching method is very convenient and efficient.

The types of nodes include: product nodes, decision strategy nodes, decision model nodes, decision matrix nodes, decision table nodes, decision tree nodes, grading card nodes, decision model nodes, rule nodes, characteristic variable nodes, data source field nodes, data source table nodes and data source service nodes; node attributes are determined by characteristics of each node; the association relationship between wind control blood margin metadata refers to the association relationship between components, such as feature variables, data source fields, data source tables and data source services, wherein each component is an independent module or component, and the association relationship exists between the components.

The data embedding points are specifically as follows: each wind control system related to wind control blood margin metadata is used as an event message production end, event messages are pushed to message queue middleware RocketMQ/Kafka, the event messages comprise event IDs, event types, service contexts and event occurrence time, a wind control component blood margin map construction end is used as a consumption end of the event messages, the messages in the event message middleware are consumed, and a corresponding metadata query interface can be called according to the event types to query the wind control blood margin metadata of the type; the interfaces of the query support http and dubbo type interfaces.

For example: when the characteristic variable is created, edited and released, as the characteristic variable is one of metadata in the wind control blood margin, namely, the data embedding point is carried out at the moment, and the data information of the embedding point comprises: event ID (generating globally unique event ID), event type (feature variable), business context (including feature variable ID, feature variable Code, and business occurrence event); and packaging the data information, and sending a message to a topic appointed in the queue middleware, namely completing the event burying point. Constructing a blood margin map of the wind control component, subscribing the same topic as a consumer end, and obtaining corresponding event content; according to the event type in the content, a corresponding metadata query interface can be adapted to be a characteristic variable metadata query interface, characteristic variable ID and characteristic variable Code in a business context are used as input parameters, characteristic variable metadata information and the relationship between upstream and downstream nodes of the variable are queried, and finally the relationship between the nodes is updated and stored in a graph data engine Neo4j, so that the process completes the real-time construction of the blood-edge relationship of the characteristic variable. Compared with the prior art, the data map can be updated timely through the data embedded points, and the accuracy of influence analysis is improved.

The step S1 further includes:

s11, initializing the blood-margin map data of the wind control assembly by using a MERGE grammar, and recording the node data of the blood-margin map of the wind control assembly; each node type has a single synchronous interface, executes a single node calling function, and synchronizes all the nodes corresponding to the node type and the side relationship among the nodes in a full amount. The full-volume synchronization interface of each node type has a metadata provider, provides a full-volume synchronization RPC interface (such as HTTP and Dubbo), and simultaneously provides a data synchronization interface based on manual intervention, and performs full-volume synchronization according to the node type or all types.

Step S2, analyzing the SQL statement by adopting a guide SQL Parser, and simultaneously rewriting and executing an explatin plan to the SQL to obtain an SQL analysis result by analysis; the SQL parsing result includes a database name, a database table name, and a database table field.

The displain plan is the original analysis plan of the database, and the number of data with influence in the result is output.

Compiling SQL based on a guide toolkit to generate an abstract syntax tree AST, then carrying out syntax and lexical analysis based on the AST, and finally obtaining an SQL analysis result; the SQL parsing result includes a database name, a database table name, and a database table field. According to the method, the Druid SQL Parser is adopted to analyze the SQL statement, SQL rewriting and analysis can be supported, the record number is affected, the method is light and convenient, and the method can be used after unpacking.

For example, the SQL is rewritten as follows:

update db_risk.risk_limit_info set available_limit＝total_limit-100000000,used_limit＝used_limit+100000000where product_no＝'110052'

the SQL content after being rewritten is as follows:

select count(*)from risk_limit_info where product_no＝'110052'

finally, analyzing the result:

{

"data":{

"database": "db_risk",// database name

"table": "risk_limit_info",// database table

"fields" [ "available_limit", "used_limit" ]// database field

"cnt":12550// influence record number

}

And putting the SQL sentences into a corresponding storage engine for execution, and analyzing the database record number influenced by the current SQL sentences based on the execution plan of the storage engine after the storage engine is executed and optimized.

The record number is the number of data in the affected database, and the modified number is the original record in the database.

The optimized execution plan refers to the rewritten SQL content, SQL execution optimization is not performed, and the optimization plan is executed through a specific database.

The number of records of the affected database can assist in evaluating and analyzing the influence range, for example, the influence on several pieces of data and the influence on hundreds of thousands of data are different in risks caused by operation, the change of the repair scheme of hundreds of thousands of data is possibly influenced, and the repair cannot be directly performed through SQL.

Multidimensional refers to a query with SQL analysis results (database names, database table names and database table fields) as starting points and other arbitrary node types or a specific node as an end point, such as a node type product, a characteristic index, a decision rule, a part-in field and the like, or a specific product and a specific characteristic index; the node type is understood as a face, the specific data node is a point, the starting point is a special point (the node type is a database name, a database table name and a database table field), the end point is any point or combination of faces, so that multidimensional and multipath inquiry is realized, inquiry influence analysis is that a communication path between the inquiry starting point and the end point exists, and the communication path has influence.

Based on SQL analysis results (database names, database table names and database table fields) as input parameters, namely the initial node, any node or node type in other graph engines is an end node, such as a product, a channel, a scene, a model, a rule, a characteristic index, a part feeding field, a data source interface, a data source manufacturer or a formulated value of the node type is the end node. The paths between the start node and the end node are queried, the number of paths is collected, and the node types contained between the paths, and the number of nodes below each type of node type.

The final analysis result is two parts of record number and graph data analysis result obtained by SQL analysis, and the graph data analysis result comprises: total number of node types, number of nodes, and specific details list.

The detail list contains feature variables, original feed fields, data service split tables, data services, data service providers, list.

And exporting the data revision relevance influence analysis result to excel, wherein each node type corresponds to one sheet, and flattening the node details of the response exported by the sheet into a two-dimensional data table format so as to facilitate table lookup, transmission and data analysis.

An SQL-based blood-lineage impact analysis system, comprising:

The foregoing is merely exemplary of the present application, and specific technical solutions and/or features that are well known in the art have not been described in detail herein. It should be noted that, for those skilled in the art, several variations and modifications can be made without departing from the technical solution of the present application, and these should also be regarded as the protection scope of the present application, which does not affect the effect of the implementation of the present application and the practical applicability of the patent. The protection scope of the present application is subject to the content of the claims, and the description of the specific embodiments and the like in the specification can be used for explaining the content of the claims.

Claims

1. An SQL blood margin influence analysis method is characterized by comprising the following steps:

2. The SQL blood-margin-based impact analysis method according to claim 1, wherein: the step S1 further includes:

3. The SQL blood-margin-based impact analysis method according to claim 1, wherein: in the step S1, a net-shaped visual wind control component blood margin map is constructed by adopting Neo4 j.

4. The SQL blood-margin-based impact analysis method according to claim 1, wherein: the SQL analysis result in the step S2 comprises a database name, a database table name and a database table field.

5. The SQL blood-margin-based impact analysis method according to claim 1, wherein: in the step S2, the method uses a ruid SQL Parser to parse the SQL statement.

6. The SQL blood-margin-based impact analysis method according to claim 1, wherein: the data revision relevance impact analysis result comprises a record number and a graph data analysis result, wherein the graph data analysis result comprises a node type total number, a node number and a detail list.

7. The SQL blood-margin-based impact analysis method according to claim 1, wherein: the data embedding points are specifically as follows: each wind control system related to wind control blood margin metadata is used as an event message production end, event messages are pushed to message queue middleware RocketMQ/Kafka, the event messages comprise event IDs, event types, service contexts and event occurrence time, a wind control component blood margin map construction end is used as a consumption end of the event messages, the messages in the event message middleware are consumed, a corresponding metadata query interface is called according to the event types, and the wind control blood margin metadata of the type is queried; the interfaces of the query support http and dubbo type interfaces.

8. An SQL-based blood-margin impact analysis system, comprising:

9. The SQL-based blood-margin impact analysis system according to claim 8, wherein: the wind control assembly blood-margin map construction module further comprises:

10. The SQL-based blood-margin impact analysis system according to claim 9, wherein: the data revision relevance influence analysis module is used for revising the data revision relevance influence analysis result to excel.