Processing method and device for cold and hot separation of transaction data

Abstract

The invention discloses a processing method and device for cold and hot separation of transaction data, and the method comprises the steps: calculating cold and hot demarcation parameters of different products through an optimized logistic regression algorithm, calculating a cold and hot demarcation time point according to the cold and hot demarcation parameters, scanning the transaction data in a hot database, and carrying out the calculation of the cold and hot demarcation time point. The transaction data in the hot database is copied to the cold database within preset time, the preset time is determined by a cold and hot boundary time point, the historical transaction data in the hot database is deleted within the cold and hot boundary time point to complete cold and hot separation of the database, and when an instruction is received, the cold and hot data in the hot database are copied to the cold database. And executing an operation corresponding to the instruction on the transaction data in the database after cold and hot separation through a database routing function.

Description

Technical Field

[0001] The present application relates to the field of data processing technology, and more specifically, to a method and device for processing hot and cold separation of transaction data. Background Art

[0002] Open banking exports multiple products, such as opening second and third-class accounts, transfers in and out, etc. With the surge in the number of products and the number of access partners, the number of users continues to increase, and open banking will record user transaction data in the database. Open banking refers to the ability of banks to export their financial services to third-party partners through open banking platforms, allowing third-party partners to run their own applications on their own platforms and obtain benefits from them.

[0003] Generally, the transaction data recently written to the database is accessed much more frequently than data from a long time ago, and this data is considered "hot". Over time, the data that was considered "hot" when it was initially written will gradually decrease in access frequency. As the number of days increases, when the transaction data is not accessed at all, or the frequency drops to a few times a month or once every few months, it is defined as "cold" data.

[0004] The common cold and hot data separation storage mechanism can reduce the amount of data in the hot database by storing cold and hot data in different databases, improve the efficiency of data reading, and store cold data in compressed tables to save database storage space costs. However, the algorithms used to determine whether data is hot or cold are Newton temperature algorithms, machine learning algorithms, etc., which do not take into account the actual form of open bank transaction data, resulting in high complexity in the calculation of cold and hot separation judgment. When reading the database, the cold database is read first, and then the hot database is read, resulting in low query efficiency.

[0005] Therefore, how to improve the query efficiency of transaction data in the database is a problem that needs to be solved urgently in this application. Summary of the invention

[0006] In view of this, the present application discloses a method and device for processing hot and cold separation of transaction data, which aims to separate transaction data that is relatively old and has a low access frequency into a cold database. When an instruction is received, the operation corresponding to the instruction is executed on the transaction data in the database after the hot and cold separation is completed through the database routing function, so as to reduce the amount of data in the hot database and improve data query efficiency.

[0007] In order to achieve the above purpose, the disclosed technical solution is as follows:

[0008] In a first aspect, the present application discloses a method for processing cold and hot separation of transaction data, the method comprising:

[0009] The hot and cold boundary parameters of different products are calculated through the optimized logistic regression algorithm;

[0010] Calculating the hot and cold boundary time point according to the hot and cold boundary parameter;

[0011] Scanning the transaction data in the hot database, and copying the transaction data in the hot database to the cold database within a preset time; wherein the preset time is determined by the hot-cold boundary time point;

[0012] Deleting historical transaction data in the hot database within the hot-cold demarcation time point to complete the hot-cold separation of the database;

[0013] When an instruction is received, the operation corresponding to the instruction is executed on the transaction data in the database after hot and cold separation is completed through the database routing function.

[0014] Preferably, the hot and cold boundary parameters of different products are calculated by the optimized logistic regression algorithm, including:

[0015] Through the hot and cold data statistics and hot and cold separation parameter learning functions, the hot and cold situations of transaction data of different products can be counted;

[0016] Obtaining the number of days after the transaction data is generated, and determining a feature segmentation according to the number of days after the transaction data is generated and a preset time period;

[0017] The hot and cold boundary parameters of different products are obtained by calculating according to the hot and cold conditions of the transaction data of the different products, the feature segmentation and the optimized gradient descent method.

[0018] Preferably, the step of calculating the hot and cold boundary time point according to the hot and cold boundary parameter includes:

[0019] Enable the configuration and prediction function of the hot and cold boundary time points;

[0020] Under the condition that the hot and cold boundary time point configuration and prediction function is enabled, the hot and cold boundary parameters and the cold data probability formula are used to calculate the fastest cold data days;

[0021] The day with the fastest cold data is determined as the cold-hot boundary time point.

[0022] Preferably, it also includes:

[0023] The hot-cold boundary time point is configured to the configuration center according to a preset data structure; wherein the preset data structure is determined by the table name and the hot-cold boundary time point;

[0024] In the process of querying transaction data, determine the hot and cold boundary time points corresponding to the transaction data in the configuration center, and obtain the transaction time and query time of the transaction data on the same day;

[0025] Whether the transaction data is cold data is predicted based on the hot-cold demarcation time point corresponding to the transaction data, the transaction time and the query time of the day.

[0026] Preferably, scanning the transaction data in the hot database and copying the transaction data in the hot database to the cold database within a preset time includes:

[0027] Scan the transaction data in the hot database to obtain the hot and cold boundary time points corresponding to the transaction data in the configuration center;

[0028] The day before the hot-cold boundary time point is determined as the preset time;

[0029] The transaction data in the hot database is copied to the cold database within the preset time.

[0030] Preferably, the deleting of historical transaction data in the hot database within the hot-cold demarcation time point to complete the hot-cold separation of the database includes:

[0031] Use the scheduled deletion task to delete the historical transaction data in the hot database within the cold-hot demarcation time point to complete the cold-hot separation of the database.

[0032] Preferably, when receiving the instruction, the operation corresponding to the instruction is executed on the transaction data in the database after hot and cold separation is completed through the database routing function, including:

[0033] When receiving an instruction, determining the transaction data corresponding to the instruction; wherein the instruction at least includes a query instruction, a modification instruction or a deletion instruction;

[0034] Determine the transaction time corresponding to the transaction data and the execution time of the instruction on the same day;

[0035] Determine the transaction type corresponding to the transaction data by the hot-cold demarcation time point, the transaction time and the daily running time of the instruction; wherein the transaction type includes at least a cold transaction data type or a hot transaction data type;

[0036] According to the database routing function, the operation corresponding to the instruction is executed on the transaction data in the database corresponding to the transaction type after the hot and cold separation is completed.

[0037] The second aspect of the present application discloses a processing device for separating hot and cold transaction data, the device comprising:

[0038] The first calculation unit is used to calculate the hot and cold boundary parameters of different products through an optimized logistic regression algorithm;

[0039] A second calculation unit is used to calculate the hot and cold boundary time point according to the hot and cold boundary parameter;

[0040] A scanning and copying unit, used for scanning the transaction data in the hot database, and copying the transaction data in the hot database to the cold database within a preset time; wherein the preset time is determined by the hot-cold boundary time point;

[0041] A hot-cold separation unit, used to delete the historical transaction data in the hot database within the hot-cold separation time point to complete the hot-cold separation of the database;

[0042] The query unit is used to execute the operation corresponding to the instruction on the transaction data in the database after the hot and cold separation is completed through the database routing function when the instruction is received.

[0043] Preferably, the first computing unit comprises:

[0044] The statistics module is used to count the hot and cold situations of transaction data of different products through the hot and cold data statistics and hot and cold separation parameter learning functions;

[0045] A first acquisition module, used to acquire the number of days after the transaction data is generated, and determine the feature segmentation according to the number of days after the transaction data is generated and a preset time period;

[0046] The first calculation module is used to calculate according to the hot and cold conditions of the transaction data of the different products, the feature segmentation and the optimized gradient descent method to obtain the hot and cold boundary parameters of different products.

[0047] Preferably, the second computing unit comprises:

[0048] The opening module is used to enable the configuration and prediction function of the hot and cold boundary time points;

[0049] The second calculation module is used to calculate the fastest cold data days according to the hot and cold boundary parameters and the cold data probability formula under the condition that the hot and cold boundary time point configuration and prediction function is turned on;

[0050] The first determination module is used to determine the day with the fastest cold data as the cold-hot boundary time point.

[0051] It can be known from the above technical scheme that the present application discloses a method and device for processing hot and cold separation of transaction data, which calculates the hot and cold boundary parameters of different products through an optimized logistic regression algorithm, calculates the hot and cold boundary time points according to the hot and cold boundary parameters, scans the transaction data in the hot database, and copies the transaction data in the hot database to the cold database within a preset time, wherein the preset time is determined by the hot and cold boundary time points, deletes the historical transaction data in the hot database within the hot and cold boundary time points to complete the hot and cold separation of the database, and when an instruction is received, executes the operation corresponding to the instruction on the transaction data in the database after the hot and cold separation is completed through the database routing function.

[0052] Through the above solution, there is no need to use complex algorithms. It is only necessary to use the logistic regression algorithm optimized by the gradient descent algorithm based on feature segmentation and optimization according to the time characteristics of open bank transaction data to solve the hot and cold separation parameters that can determine whether the data is hot or cold. When the system is running, it can directly determine whether it is cold data based on the number of days the data exists and the hot and cold separation parameters. Use scheduled tasks to separate the transaction data with a longer time and lower access frequency into the cold database according to the hot and cold separation parameters and transaction time. When receiving an instruction, the database routing function executes the operation corresponding to the instruction on the transaction data in the database after the hot and cold separation is completed, so as to reduce the amount of data in the hot database and improve the efficiency of data query. BRIEF DESCRIPTION OF THE DRAWINGS

[0053] In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are only embodiments of the present application. For ordinary technicians in this field, other drawings can be obtained based on the provided drawings without paying any creative work.

[0054] Figure 1 A flowchart of a method for processing hot and cold separation of transaction data disclosed in an embodiment of the present application;

[0055] Figure 2 A schematic diagram of the hot and cold data migration solution disclosed in the embodiment of the present application;

[0056] Figure 3 A flowchart of the scheduled migration and deletion of hot and cold data disclosed in the embodiment of the present application;

[0057] Figure 4 A schematic diagram of the structure of a processing device for separating hot and cold transaction data disclosed in an embodiment of the present application;

[0058] Figure 5 This is a schematic diagram of the structure of an electronic device disclosed in an embodiment of the present application. DETAILED DESCRIPTION

[0059] The following will be combined with the drawings in the embodiments of the present application to clearly and completely describe the technical solutions in the embodiments of the present application. Obviously, the described embodiments are only part of the embodiments of the present application, not all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of this application.

[0060] In this application, the terms "comprises", "comprising" or any other variations thereof are intended to cover non-exclusive inclusion, so that a process, method, article or device comprising a series of elements includes not only those elements, but also other elements not explicitly listed, or also includes elements inherent to such process, method, article or device. In the absence of further restrictions, an element defined by the sentence "comprising a ..." does not exclude the presence of other identical elements in the process, method, article or device comprising the element.

[0061] As can be seen from the background technology, the common cold and hot data separation storage mechanism can improve the efficiency of data reading by storing cold and hot data in different databases to reduce the amount of data in the hot database, and can store cold data in a compressed table to save database storage space costs. However, the algorithms used to determine whether data is hot or cold are Newton temperature algorithms, machine learning algorithms, etc., which do not take into account the actual form of open bank transaction data, resulting in high computational complexity in cold and hot separation judgment. When reading the database, the cold database is read first, and then the hot database is read, resulting in low query efficiency. Therefore, how to improve the query efficiency of transaction data in the database is a problem that needs to be solved urgently in this application.

[0062] In order to solve the above problems, the present application discloses a method and device for processing hot and cold separation of transaction data. There is no need to use complex algorithms. It only needs to use a logistic regression algorithm optimized by a gradient descent algorithm based on feature segmentation and optimization according to the time characteristics of open bank transaction data to solve the hot and cold separation parameters that can determine whether the data is hot or cold. When the subsequent system is running, it can directly determine whether it is cold data based on the number of days the data has existed and the hot and cold separation parameters. A scheduled task is used to separate transaction data that is relatively old and has a low access frequency into a cold database based on the hot and cold separation parameters and transaction time. When an instruction is received, the operation corresponding to the instruction is executed on the transaction data in the database after the hot and cold separation is completed through the database routing function, so as to reduce the amount of data in the hot database and improve data query efficiency. The specific implementation method is specifically described through the following embodiments.

[0063] refer to Figure 1As shown, a method for processing hot and cold separation of transaction data disclosed in an embodiment of the present application is provided. The method for processing hot and cold separation of transaction data mainly includes the following steps:

[0064] S101: Calculate the hot and cold boundary parameters of different products through the optimized logistic regression algorithm.

[0065] In S101, the hot and cold boundary parameters of different open banking products are calculated through the optimized logistic regression algorithm.

[0066] Open banking refers to a cooperation model in which banks export their financial services to third-party partners through open banking platforms, allowing third-party partners to run their own applications on their own platforms and gain revenue from them.

[0067] Specifically, the process of calculating the hot and cold boundary parameters of different products through the optimized logistic regression algorithm is shown in A1-A3 below.

[0068] A1: Through the hot and cold data statistics and hot and cold separation parameter learning functions, statistics are collected on the hot and cold situations of transaction data of different products of Open Bank.

[0069] Among them, open banking transaction data is stored in a relational database, and the transaction data of each product exists in a separate table with the table structure: <transaction data ID, transaction details, transaction time>.

[0070] The transaction data ID also contains the timestamp of the transaction. In the cold database, a table with the same structure as the hot database table should be created. Among them, the transaction data ID is the primary key of the data written into the database, and the ID is the unique identifier of this data. A table is a data structure that stores a type of data information in the database, such as a student table: <student ID, name, age, gender>.

[0071] Open banking transaction data is mainly provided to external partners for query and use, and is also used for internal statistical transaction data. Transaction data will also be retained to preserve key information. According to the actual business situation, after the transaction data is generated and recorded in the database, external partners will generally only access it on the same day. For statistical needs, it is generally a low-frequency access every month or year. Therefore, the transaction data of open banks can be judged as hot or cold data based on the date. The existence time of transaction data and the recent access frequency can be counted first, and then the logistic regression algorithm in machine learning can be used to learn a hot-cold boundary parameter that can be used to classify hot and cold data.

[0072] It should be noted that since the transaction data access frequencies of different products are different, it is necessary to count and learn a separate hot and cold separation parameter for each product.

[0073] Hot and cold data statistics and hot and cold separation parameter learning function:

[0074] The hot and cold data statistics and hot and cold separation parameter learning functions first count the hot and cold conditions of the transaction data, and then use the logistic regression algorithm improved and optimized based on feature segmentation and gradient descent algorithm (Adam) to learn the hot and cold separation parameters.

[0075] Cold data refers to data that is rarely accessed after a period of time.

[0076] Hot data refers to data that has just been created and is accessed frequently.

[0077] The specific process of cold and hot data statistics is as follows:

[0078] For example, first export the data in each product transaction table, and count the total number of visits to each data in the last three days. If the total number of visits in three days is 0, it can be considered cold data, otherwise it is hot data.

[0079] According to the above method, the time t1, t2, t3, ..., t i , ..., t n , whether all data are cold data y1, y2, y3, ..., y i , ..., y n .

[0080] y i =1 indicates cold data, y i =0 indicates hot data. i = 1 is used as the cold data set. i = 0 as the hot data set.

[0081] The sigmoid fitting function is used to represent the probability that the predicted data is cold data, as shown in formula (1).

[0082]

[0083] Among them, P is the probability of cold data; y is the cold data; ω is the optimal parameter obtained by logistic regression, that is, the cold and hot separation parameter; t is the feature matrix (that is, feature segmentation); e is the natural logarithm.

[0084] The expressions of ω and t are shown in formula (2).

[0085]

[0086] In formula (2), ω is the optimal parameter of logistic regression, which directly determines whether the data is cold data based on the length of time; t1 is the number of days after the transaction data was generated.

[0087] Since the probability of open bank transaction data becoming cold data varies in different time periods, different features can be configured according to the time period to increase the probability of correct judgment of hot and cold data. According to actual experience statistics, transaction data has the most access times in the 0-1 month time period, and the probability of becoming cold data is the lowest; the number of access times in the 1-3 month time period is large, and the probability of becoming cold data is low; the number of access times in the 3-6 month time period is small, and the probability of becoming cold data is high; the number of access times in the time period of more than 6 months is the least, and the probability of becoming cold data is the lowest. In order to set four different groups of features according to the above four time periods, the above formula (2) can be modified to obtain formula (3).

[0088]

[0089] In the hot and cold data sets, we expect to obtain the maximum value of the correct prediction probability of all samples. The calculation formula of L(ω) is shown in formula (4).

[0090]

[0091] Where L(ω) is the maximum value of the correct prediction probability of all samples; t is the feature segment; e is the natural logarithm; ω is the optimal parameter obtained by logistic regression; y i It is cold data.

[0092] In order to conveniently solve ω that maximizes the probability of correct prediction of all samples, we take the logarithm to obtain formula (5).

[0093]

[0094] In order to solve ω that makes the above logL(ω) reach the maximum value, it is necessary to first take the inverse to obtain the loss function g(ω). The calculation formula of g(ω) is shown in formula (6).

[0095]

[0096] Among them, Loss(ω) is the loss function of g(ω).

[0097] Initialize ω to 0, then calculate g(ω), and the gradient G of g(ω), use the Adam-optimized logistic regression algorithm to adjust the parameter ω, repeat the above process, continue to calculate g(ω), until g(ω) converges, and get ω. The Adam-optimized logistic regression algorithm uses an exponentially weighted moving average to estimate the momentum and quadratic moment of the gradient. Compared with SGD (a gradient descent algorithm that only adjusts parameters based on gradient momentum), it can speed up the convergence of the loss function and is more robust. The loss function is used to evaluate the gap between the predicted classification and the actual classification of the classifier.

[0098] Adam is an optimized gradient descent algorithm that uses an exponentially weighted moving average to estimate the momentum and quadratic moment of the gradient, which can speed up the convergence of the loss function and has strong robustness.

[0099] The specific process of Adam adjusting the parameter ω is as follows:

[0100] Taking the partial derivative of g(ω) with respect to ω, we obtain formula (7).

[0101]

[0102] Among them, G is the partial derivative; is the sign of the partial derivative; t i The time from when the data was generated to now.

[0103] According to G, we can obtain formula (8), formula (9), formula (10), formula (11) and formula (12).

[0104] V t =β1V t-1 +(1-β1)G(8)

[0105] S t =β2S t-1 +(1-β2)G 2 (9)

[0106]

[0107]

[0108] Where t represents the number of iterations, V t is the first-order integral of the gradient; S t is the second-order integral of the gradient; and is an unbiased estimate; β1 is set to 0.9; β2 is set to 0.999; ε is set to 10 -8 ; η is set to 0.001.

[0109] A2: Obtain the number of days after the transaction data is generated, and determine the feature segmentation based on the number of days after the transaction data is generated and the preset time period.

[0110] Since the probability of open banking transaction data becoming cold data varies in different time periods, different features can be configured according to the time period to increase the probability of correct judgment of hot and cold data.

[0111] According to actual experience statistics, transaction data has the most access times in the 0-1 month period, and the probability of becoming cold data is the lowest; the number of access times in the 1-3 month period is more, and the probability of becoming cold data is lower; the number of access times in the 3-6 month period is less, and the probability of becoming cold data is higher; the number of access times in the period of more than 6 months is the least, and the probability of becoming cold data is the lowest. In order to set four different groups of features according to the above four time periods, the four different groups of features are the feature matrix, that is, feature segmentation. Specifically, as shown in t in the above formula (3).

[0112] A3: Calculate the hot and cold boundary parameters of different products based on the hot and cold conditions of transaction data of different products, feature segmentation, and the Adam optimized logistic regression algorithm.

[0113] S102: Calculate the hot-cold boundary time point according to the hot-cold boundary parameter.

[0114] The specific process of calculating the hot and cold boundary time points according to the hot and cold boundary parameters is shown in B1-B3.

[0115] B1: Enable the hot and cold boundary time point configuration and prediction function.

[0116] B2: When the hot and cold boundary time point configuration and prediction function is enabled, the hot and cold boundary parameters and the cold data probability formula are used to calculate the fastest days of cold data.

[0117] Among them, the cold data probability formula is determined by the logistic function (sigmoid fitting function); the sigmoid fitting function is used to predict the probability that the data is cold data.

[0118] Hot and cold boundary time point configuration and prediction function:

[0119] After statistics and learning are completed, the number of days t that become cold data the fastest is calculated according to the hot and cold boundary parameter ω and the cold data probability formula corresponding to each table. In order to prevent too fast hot and cold separation, the number of days t cannot be less than 3 days, as shown in formula (13).

[0120]

[0121] According to formula (13), the fastest number of days to become cold data, t, can be obtained, i.e., the hot-cold boundary time point. The hot-cold boundary time point means that after the data is generated, it will change from hot data to cold data after t days. The specific time when hot data becomes cold data on the tth day is as follows: Figure 2 shown. Figure 2 A schematic diagram of a cold-hot data migration solution is shown.

[0122] Figure 2In the example above, create a separation record table in the hot database in advance. The table structure of the separation record table is: <table name, transaction data ID, number of deletion attempts, separation time>. Configure the system start date D0 in the configuration center. Create two topics in the message queue Kafka: Topic0 and Topic1.

[0123] Among them, Kafka is a message queue and software that can transmit messages.

[0124] The topic in Kafka is the storage directory in the Kafka message queue. Each topic corresponds to a message subject. The data in the message can be a string, JSON object, and other types.

[0125] Topic0 is a topic that stores deleted messages on even-numbered days starting from the system start date. Topic1 is a topic that stores deleted messages on odd-numbered days starting from the system start date. Cold databases use compressed tables to further reduce the storage space occupied by data.

[0126] 1. Assign a scheduled task to each table (a program that is executed at a fixed period, such as once every three days, which can be implemented using frameworks such as Quartz), and start scanning the table at dawn every day. First, find out the transaction time t0 in a row of data, and obtain today's time t1 based on the hot and cold boundary parameters of the table name in the configuration center. According to t1-t0=x-1, determine whether the transaction in this row has reached the day before the hot and cold boundary time point.

[0127] 2. If the transaction data reaches the day before the hot and cold boundary time, copy the transaction data and store it in the cold database. Add a new record in the separation record table, write the table name and transaction data ID in the separation record table, and the number of attempts is 0.

[0128] 3. According to the current date D1 and the system start date D0, choose to send a message to the message queue Kafka Topic ((D1+1-D0)%2): <table name, transaction data ID>, and hand it over to the hot database historical transaction data deletion task for deletion.

[0129] After obtaining the hot and cold boundary time point, it is assigned to the configuration center. The configured data structure is: <table name, hot and cold boundary time point>. Other modules of this method can read the hot and cold boundary time point from the configuration center. The table name is the table name where each product transaction data is located.

[0130] After querying the hot and cold boundary time points corresponding to the transaction data, you can predict whether the transaction is cold data based on the hot and cold boundary points, transaction time and today's time.

[0131] B3: The day with the fastest cold data is determined as the cold-hot boundary time point.

[0132] In the embodiment scenario, the hot and cold demarcation time point is configured to the configuration center according to the preset data structure. In the process of querying transaction data, the hot and cold demarcation time point corresponding to the transaction data in the configuration center is determined, and the transaction time and query time of the day of the transaction data are obtained. According to the hot and cold demarcation time point, transaction time and query time of the day corresponding to the transaction data, it is predicted whether the transaction data is cold data. The hot and cold demarcation time point refers to how long it takes for hot data to become cold data. For example, if the hot and cold demarcation time point is 3 days, the data becomes cold data 3 days after it is generated.

[0133] The preset data structure is determined by the table name and the hot and cold boundary time point.

[0134] S103: Scan the transaction data in the hot database, and copy the transaction data in the hot database to the cold database within a preset time; wherein the preset time is determined by the hot-cold boundary time point.

[0135] The transaction data in the hot database is copied to the cold database within a preset time, which is the scheduled migration task of the hot database transaction data.

[0136] The specific process of scanning the transaction data in the hot database and copying the transaction data in the hot database to the cold database within a preset time is shown in C1-C3.

[0137] C1: Scan the transaction data in the hot database to obtain the hot and cold boundary time points corresponding to the transaction data in the configuration center.

[0138] Among them, the configuration software is saved, and other programs can read the configuration from the configuration center.

[0139] C2: The day before the hot and cold boundary time point is determined as the preset time.

[0140] For example, the hot and cold dividing time point is 4 days, and the day before (3 days) is the preset time.

[0141] C3: Copy the transaction data in the hot database to the cold database within the preset time.

[0142] The execution process and principle of C1-C3 are similar to the above Figure 2 The execution process and execution principle are consistent, which can be used as a reference and will not be repeated here.

[0143] S104: Delete the historical transaction data in the hot database within the hot-cold demarcation time point to complete the hot-cold separation of the database.

[0144] In S104, a scheduled deletion task is used to delete the historical transaction data in the hot database within the cold-hot separation time point to complete the cold-hot separation of the database.

[0145] In the task of deleting historical transaction data in the hot database, there are two Kafka topics, both of which are started in the early morning of the same day, sleep after consumption, and then another topic consumer starts consumption.

[0146] Specifically, the historical transaction data in the hot database is deleted at the hot and cold demarcation time point to complete the hot and cold separation process of the database. Figure 3 Provide explanation. Figure 3 The figure shows the process flow of scheduled migration and deletion of hot and cold data.

[0147] Every morning, based on the current date D1 and the system start date D0, the consumer of KafkaTopic ((D1-D0)%2) starts to pull the delete message in the topic, and deletes the corresponding transaction data in the corresponding table of the hot database according to the transaction data ID in the delete message received yesterday, and deletes the data corresponding to the transaction ID in the separation record table;

[0148] If the deletion fails, the data in the separated record table will not be deleted, and only the number of deletion attempts will be increased by 1. If the current consumption coordinate offset of the consumer is equal to the end coordinate endOffset of the consumer's partition, it means that the processing of the deletion message is completed, the deletion of the transaction data in the hot database is completed, and then the consumer is set to terminate consumption.

[0149] Finally, it is necessary to execute the scheduled deletion task of historical transaction data in the hot database and the scheduled task of checking and alarming for hot and cold separation.

[0150] The task of regularly deleting historical transaction data in the hot database:

[0151] For example, after 18:00 every day, check whether there is any data in the separation record table with a separation time before today. If there is, delete the data in the hot database transaction database according to the table name and transaction data ID, and delete the records in the separation record table. If the deletion fails, the number of deletion attempts for the record in the separation record table will be increased by 1.

[0152] Cold and hot separation check alarm timing task:

[0153] For example, after 20:00 every day, check whether there is any data in the separation record table with the number of attempts greater than or equal to 1 and the separation time before today. If there is, delete the data in the hot database transaction database according to the table name and transaction data ID, and delete the records in the separation record table. If it still fails to trigger an alarm, send a text message to the operation and maintenance personnel, and then manually check and delete it manually.

[0154] S105: When receiving an instruction, perform the operation corresponding to the instruction on the transaction data in the database after completing the cold and hot separation through the database routing function.

[0155] Specifically, when receiving an instruction, the process of performing the operation corresponding to the instruction on the transaction data in the database after completing the cold and hot separation through the database routing function is as shown in D1 - D4.

[0156] D1: When receiving an instruction, determine the transaction data corresponding to the instruction; wherein, the instruction includes at least a query instruction, a modification instruction, or a deletion instruction.

[0157] Among them, the cold and hot database routing function is required when querying, modifying, or deleting transaction data in the program.

[0158] D2: Determine the transaction time corresponding to the transaction data and the running time of the day of the instruction.

[0159] D3: Determine the transaction type corresponding to the transaction data through the cold and hot demarcation time point, the transaction time, and the running time of the day of the instruction; wherein, the transaction type includes at least a cold transaction data type or a hot transaction data type.

[0160] D4: According to the database routing function, perform the operation corresponding to the instruction on the transaction data in the database after completing the cold and hot separation corresponding to the transaction type.

[0161] If the instruction is a query instruction and there is a transaction ID in the condition, query the demarcation time point x corresponding to the table name in the configuration center according to the table name, and then judge according to the time t0 in the transaction ID, the current time t1, and the demarcation point x. If t1 - t0 >= x, query in the cold database; if t1 - t0 < x, query in the hot database. If there is no transaction ID, first query the relevant data in the cold database, and then query the data in the hot database where the time t2 > t1 + x.

[0162] If the instruction is a modification instruction and there is a transaction ID in the condition, query the demarcation time point x corresponding to the table name in the configuration center according to the table name, and then judge according to the time t0 in the transaction ID, the current time t1, and the demarcation point x. If t1 - t0 > x, modify the transaction data corresponding to the modification instruction in the cold database; if t1 - t0 < x - 1, modify the transaction data corresponding to the modification instruction in the hot database; if x - 1 <= t1 - t0 <= x, it is necessary to modify the data in both the cold database and the hot database. If there is no transaction ID in the modification condition, first modify in the cold database and then modify in the hot database.

[0163] If the instruction is a deletion instruction and there is a transaction ID in the condition, query the corresponding demarcation time point x of the table name in the configuration center according to the table name, and judge based on the time t0 in the transaction ID, the current time t1, and the demarcation point x. If t1 - t0 > x, delete the transaction data corresponding to the deletion instruction in the cold database. If t1 - t0 < x - 1, delete the transaction data corresponding to the deletion instruction in the hot database. If x - 1 <= t1 - t0 <= x, it is necessary to delete the data in both the cold database and the hot database simultaneously. If there is no transaction ID in the deletion condition, first delete from the cold database and then from the hot database.

[0164] This application is applicable to relational databases. It separates hot and cold data according to different products of open banks. First, it counts the access frequency of transaction data and uses a logistic regression algorithm with feature segmentation and Adam optimization to learn the parameters that can judge data coldness and hotness. It uses a scheduled task to separate the transaction data with a relatively long time and low access frequency into the cold database according to the hot and cold separation parameters and transaction time, so as to reduce the amount of data in the hot database, improve data query efficiency, and perform data compression to reduce the burden on the database storage space and avoid service downtime caused by database expansion. When querying data, it routes to the cold database or the hot database for query according to the transaction time.

[0165] The specific module composition of this solution includes: the function of hot and cold data statistics and learning of hot and cold separation parameters, the function of configuring and predicting the hot and cold demarcation time point, the scheduled migration task of transaction data in the hot database, the function of deleting historical transaction data in the hot database, the scheduled backup deletion task of historical transaction data in the hot database, the scheduled check and alarm task for hot and cold separation, and the routing function between hot and cold databases.

[0166] Advantages of this application:

[0167] 1. Separating hot and cold data helps reduce the amount of data in the hot database and improve data query speed;

[0168] 2. Automatically migrating cold data to the cold database regularly, without the problem of downtime caused by database expansion;

[0169] 3. The cold database uses compressed tables, saving the cost of database storage devices;

[0170] 4. Combining the actual situation of open bank transaction data, it can learn hot and cold separation parameters according to the different query frequencies of transaction data of different products, and perform customized configuration of hot and cold times, saving database space and improving query speed considering the business situation;

[0171] 5. Before the system is run, according to the time characteristics of open bank transaction data, the logistic regression algorithm (Logistic Regression) based on feature segmentation and Adam improvement is used to solve the cold and hot separation parameters. When the system is running later, it can directly determine whether it is cold data based on the number of days the data exists and the cold and hot separation parameters, without using complex algorithms, which speeds up data query efficiency; Logistic Regression is a machine learning algorithm for binary classification problems. Its basic idea is to find a function so that the relationship between the output value of the function for the sample and the true label presents a shape close to 0-1, thereby achieving the purpose of classification.

[0172] 6. Routing hot and cold databases based on time. Unlike other methods, it is not necessary to first determine whether there is data in the cold database and then query the data in the hot database.

[0173] 7. For open banking scenarios, based on the transaction data time, a logistic regression algorithm based on feature segmentation and Adam gradient descent method optimization is used to predict the cold and hot separation time of the data;

[0174] 8. Configure the hot and cold demarcation time according to the transaction data of different products of the open bank, use the technical solutions of scheduled migration, scheduled deletion, backup, and alarm to separate the hot and cold databases, and perform the hot and cold database routing method according to the transaction time;

[0175] 9. When accessing the database, it is necessary to first distinguish between cold and hot data based on time. Other solutions, such as using Newton temperature to determine whether it is cold data, involve exponential operations on the natural constant e, which has a high computational complexity. This application only uses multiplication and addition operations, and has a low computational complexity.

[0176] The beneficial effects of the embodiments of the present application are as follows: there is no need to use complex algorithms. It is only necessary to use a logistic regression algorithm optimized by a gradient descent algorithm based on feature segmentation and optimization according to the time characteristics of the transaction data to solve the hot-cold separation parameters that can determine whether the data is hot or cold. When the system is running, it can directly determine whether it is cold data based on the number of days the data exists and the hot-cold separation parameters. A scheduled task is used to separate the transaction data with a longer time and a lower access frequency into a cold database according to the hot-cold separation parameters and the transaction time. When an instruction is received, the operation corresponding to the instruction is executed on the transaction data in the database after the hot-cold separation is completed through the database routing function, so as to reduce the amount of data in the hot database and improve the efficiency of data query.

[0177] Based on the above embodiments Figure 1 A method for processing hot and cold separation of transaction data is disclosed. The embodiment of the present application also discloses a processing device for processing hot and cold separation of transaction data. Figure 4The cold and hot separation processing device of the transaction data includes:

[0178] The first calculation unit 401 is used to calculate the hot and cold boundary parameters of different products through an optimized logistic regression algorithm;

[0179] The second calculation unit 402 is used to calculate the hot-cold boundary time point according to the hot-cold boundary parameter;

[0180] The scanning and copying unit 403 is used to scan the transaction data in the hot database and copy the transaction data in the hot database to the cold database within a preset time; wherein the preset time is determined by the hot-cold boundary time point;

[0181] The hot-cold separation unit 404 is used to delete the historical transaction data in the hot database within the hot-cold separation time point to complete the hot-cold separation of the database;

[0182] The query unit 405 is used to execute the operation corresponding to the instruction on the transaction data in the database after the hot and cold separation is completed through the database routing function when receiving the instruction.

[0183] Furthermore, the first computing unit 401 includes:

[0184] The statistics module is used to count the hot and cold situations of transaction data of different products through the hot and cold data statistics and hot and cold separation parameter learning functions;

[0185] A first acquisition module is used to acquire the number of days after the transaction data is generated, and determine the feature segmentation according to the number of days after the transaction data is generated and a preset time period;

[0186] The first calculation module is used to calculate according to the hot and cold conditions of transaction data of different products, feature segmentation and optimized gradient descent method to obtain hot and cold boundary parameters of different products.

[0187] Furthermore, the second computing unit 402 includes:

[0188] The opening module is used to enable the configuration and prediction function of the hot and cold boundary time points;

[0189] The second calculation module is used to calculate the fastest cold data days according to the hot and cold boundary parameters and the cold data probability formula under the condition that the hot and cold boundary time point configuration and prediction function is turned on;

[0190] The first determination module is used to determine the day with the fastest cold data as the cold-hot boundary time point.

[0191] Furthermore, the processing device for separating hot and cold transaction data also includes:

[0192] A configuration module, used to configure the hot and cold boundary time points to the configuration center according to a preset data structure; wherein the preset data structure is determined by the table name and the hot and cold boundary time points;

[0193] The second determination module is used to determine the hot and cold boundary time point corresponding to the transaction data in the configuration center during the process of querying the transaction data, and obtain the transaction time and query time of the transaction data on the same day;

[0194] The prediction module is used to predict whether the transaction data is cold data according to the hot and cold boundary time point corresponding to the transaction data, the transaction time and the query time of the day.

[0195] Furthermore, the scanning and copying unit 403 includes:

[0196] A scanning module, used to scan the transaction data in the hot database and obtain the hot and cold boundary time points corresponding to the transaction data in the configuration center;

[0197] The third determining module is used to determine the day before the hot-cold boundary time point as the preset time;

[0198] The replication module is used to replicate the transaction data in the hot database to the cold database within a preset time.

[0199] Furthermore, the hot-cold separation unit 404 is specifically used to use a scheduled deletion task to delete the historical transaction data in the hot database within the hot-cold boundary time point to complete the hot-cold separation of the database.

[0200] Furthermore, the query unit 405 includes:

[0201] A fourth determination module is used to determine the transaction data corresponding to the instruction when receiving the instruction; wherein the instruction at least includes a query instruction, a modification instruction or a deletion instruction;

[0202] A fifth determination module is used to determine the transaction time corresponding to the transaction data and the execution time of the instruction on the same day;

[0203] A sixth determination module is used to determine the transaction type corresponding to the transaction data according to the cold and hot demarcation time point, the transaction time and the daily running time of the instruction; wherein the transaction type includes at least a cold transaction data type or a hot transaction data type;

[0204] The query module is used to execute the operation corresponding to the instruction of the transaction data in the database after the cold and hot separation is completed corresponding to the transaction type according to the database routing function.

[0205] The beneficial effects of the embodiments of the present application are as follows: there is no need to use complex algorithms. It is only necessary to use a logistic regression algorithm optimized by a gradient descent algorithm based on feature segmentation and optimization according to the time characteristics of the transaction data to solve the hot-cold separation parameters that can determine whether the data is hot or cold. When the system is running, it can directly determine whether it is cold data based on the number of days the data exists and the hot-cold separation parameters. A scheduled task is used to separate the transaction data with a longer time and a lower access frequency into a cold database according to the hot-cold separation parameters and the transaction time. When an instruction is received, the operation corresponding to the instruction is executed on the transaction data in the database after the hot-cold separation is completed through the database routing function, so as to reduce the amount of data in the hot database and improve the efficiency of data query.

[0206] An embodiment of the present application further provides a storage medium, which includes stored instructions, wherein when the instructions are executed, the device where the storage medium is located is controlled to execute the processing method for separating hot and cold transaction data as described above.

[0207] The present application also provides an electronic device, whose structural diagram is as follows: Figure 5 As shown, it specifically includes a memory 501 and one or more instructions 502, wherein the one or more instructions 502 are stored in the memory 501 and are configured to be executed by one or more processors 503 to execute the one or more instructions 502 to perform the above-mentioned hot and cold separation processing method of the transaction data.

[0208] For the aforementioned method embodiments, for the sake of simplicity, they are all described as a series of action combinations, but those skilled in the art should be aware that the present application is not limited by the order of the actions described, because according to the present application, some steps can be performed in other orders or simultaneously. Secondly, those skilled in the art should also be aware that the embodiments described in the specification are all preferred embodiments, and the actions and modules involved are not necessarily required by the present application.

[0209] It should be noted that each embodiment in this specification is described in a progressive manner, and each embodiment focuses on the differences from other embodiments, and the same or similar parts between the embodiments can be referred to each other. For system embodiments, since they are basically similar to method embodiments, the description is relatively simple, and the relevant parts can be referred to the partial description of the method embodiment.

[0210] The steps in the methods of the embodiments of the present application can be adjusted in order, combined, and deleted according to actual needs.

[0211] Finally, it should be noted that, in this article, relational terms such as first and second, etc. are merely used to distinguish one entity or operation from another entity or operation, and do not necessarily require or imply any actual relationship or order between these entities or operations.

[0212] The above description of the disclosed embodiments enables those skilled in the art to implement or use the present application. Various modifications to these embodiments will be apparent to those skilled in the art, and the general principles defined herein may be implemented in other embodiments without departing from the scope of the present application. Therefore, the present application will not be limited to the embodiments shown herein, but will conform to the widest scope consistent with the principles and novel features disclosed herein.

[0213] The above is only a preferred implementation of the present application. It should be pointed out that for ordinary technicians in this technical field, several improvements and modifications can be made without departing from the principles of the present application. These improvements and modifications should also be regarded as the scope of protection of the present application.

Claims

1. A method for processing cold and hot separation of transaction data, characterized in that: The method comprises: The hot and cold boundary parameters of different products are calculated through the optimized logistic regression algorithm; Calculating the hot and cold boundary time point according to the hot and cold boundary parameter; Scanning the transaction data in the hot database, and copying the transaction data in the hot database to the cold database within a preset time; wherein the preset time is determined by the hot-cold boundary time point; Deleting historical transaction data in the hot database within the hot-cold demarcation time point to complete the hot-cold separation of the database; When an instruction is received, the operation corresponding to the instruction is executed on the transaction data in the database after hot and cold separation is completed through the database routing function.

2. The method according to claim 1, characterized in that The optimized logistic regression algorithm is used to calculate the hot and cold boundary parameters of different products, including: Through the hot and cold data statistics and hot and cold separation parameter learning functions, the hot and cold situations of transaction data of different products can be counted; Obtaining the number of days after the transaction data is generated, and determining a feature segmentation according to the number of days after the transaction data is generated and a preset time period; The hot and cold boundary parameters of different products are obtained by calculating according to the hot and cold conditions of the transaction data of the different products, the feature segmentation and the optimized gradient descent method.

3. The method according to claim 1, characterized in that The step of calculating the hot and cold boundary time point according to the hot and cold boundary parameter comprises: Enable the configuration and prediction function of the hot and cold boundary time points; Under the condition that the hot and cold boundary time point configuration and prediction function is enabled, the hot and cold boundary parameters and the cold data probability formula are used to calculate the fastest cold data days; The day with the fastest cold data is determined as the cold-hot boundary time point.

4. The method according to claim 1, characterized in that: Also includes: The hot-cold boundary time point is configured to the configuration center according to a preset data structure; wherein the preset data structure is determined by the table name and the hot-cold boundary time point; In the process of querying transaction data, determine the hot and cold boundary time points corresponding to the transaction data in the configuration center, and obtain the transaction time and query time of the transaction data on the same day; Whether the transaction data is cold data is predicted based on the hot-cold demarcation time point corresponding to the transaction data, the transaction time and the query time of the day.

5. The method according to claim 4, characterized in that The scanning of the transaction data in the hot database and copying the transaction data in the hot database to the cold database within a preset time includes: Scan the transaction data in the hot database to obtain the hot and cold boundary time points corresponding to the transaction data in the configuration center; The day before the hot-cold boundary time point is determined as the preset time; The transaction data in the hot database is copied to the cold database within the preset time.

6. The method according to claim 1, characterized in that The deleting of historical transaction data in the hot database within the hot-cold demarcation time point to complete the hot-cold separation of the database includes: Use the scheduled deletion task to delete the historical transaction data in the hot database within the cold-hot demarcation time point to complete the cold-hot separation of the database.

7. The method according to claim 1, characterized in that When receiving the instruction, the operation corresponding to the instruction is executed on the transaction data in the database after the hot and cold separation is completed through the database routing function, including: When receiving an instruction, determining the transaction data corresponding to the instruction; wherein the instruction at least includes a query instruction, a modification instruction or a deletion instruction; Determine the transaction time corresponding to the transaction data and the execution time of the instruction on the same day; Determine the transaction type corresponding to the transaction data by the hot-cold demarcation time point, the transaction time and the daily running time of the instruction; wherein the transaction type includes at least a cold transaction data type or a hot transaction data type; According to the database routing function, the operation corresponding to the instruction is executed on the transaction data in the database corresponding to the transaction type after the hot and cold separation is completed.

8. A processing device for separating hot and cold transaction data, characterized in that: The device comprises: The first calculation unit is used to calculate the hot and cold boundary parameters of different products through an optimized logistic regression algorithm; A second calculation unit is used to calculate the hot and cold boundary time point according to the hot and cold boundary parameter; A scanning and copying unit, used for scanning the transaction data in the hot database, and copying the transaction data in the hot database to the cold database within a preset time; wherein the preset time is determined by the hot-cold boundary time point; A hot-cold separation unit, used to delete the historical transaction data in the hot database within the hot-cold separation time point to complete the hot-cold separation of the database; The query unit is used to execute the operation corresponding to the instruction on the transaction data in the database after the hot and cold separation is completed through the database routing function when the instruction is received.

9. The device according to claim 8, characterized in that The first computing unit comprises: The statistics module is used to count the hot and cold situations of transaction data of different products through the hot and cold data statistics and hot and cold separation parameter learning functions; A first acquisition module, used to acquire the number of days after the transaction data is generated, and determine the feature segmentation according to the number of days after the transaction data is generated and a preset time period; The first calculation module is used to calculate according to the hot and cold conditions of the transaction data of the different products, the feature segmentation and the optimized gradient descent method to obtain the hot and cold boundary parameters of different products.

10. The device according to claim 8, characterized in that The second computing unit comprises: The opening module is used to enable the configuration and prediction function of the hot and cold boundary time points; The second calculation module is used to calculate the fastest cold data days according to the hot and cold boundary parameters and the cold data probability formula under the condition that the hot and cold boundary time point configuration and prediction function is turned on; The first determination module is used to determine the day with the fastest cold data as the cold-hot boundary time point.

Images