A method for writing and querying massive account flow data in a database based on Kafka

Abstract

The present invention relates to the field of database technology, and in particular to a method for writing massive account flow data into a database based on Kafka, comprising the following steps: configuring a master database and a data synchronization tool for monitoring data change events, and establishing a self-built application service; the master database generates binlog information according to a write request, and writes the binlog information into a Kafka message queue; the self-built application service consumes the binlog information, parses and obtains data change events; the routing shards in the slave database are calculated according to the data change events and the transaction information is processed, and the slave database is configured with multi-level hot data sets and a full data set according to time, and each level of hot data sets and full data sets are fragmented and store corresponding data based on user ID numbers. The present invention reduces the query pressure of the master database by performing write operations in the master database and reading operations in the slave database, reduces the amount of data queried by users by grading by time, and reduces the data retrieval range by user ID number fragmentation, thereby optimizing query efficiency.

Description

Technical Field

[0001] The present invention relates to the technical field of databases, and in particular to a method for writing and querying massive account flow data in a database based on Kafka, a database and a storage medium. Background Art

[0002] Modern Internet companies have a huge number of users. As their business develops, data begins to accumulate and the amount of data increases accordingly. There are generally two solutions at this time. One is to directly use advanced database products such as Oracle and let database vendors solve related problems. However, this method brings expensive costs and high dependence on database vendors; or directly use technologies such as sharding for business production data. Even after the data is sharded, due to the continuous accumulation of data, the data on a single shard will become larger and larger, eventually dragging down the system, and also bringing complexity to business processing.

[0003] The current query scenarios for massive data have the following problems: Problem 1: All user data is stored in one piece, and both read and write operations use the same server resources, which brings great database pressure; Problem 2: The data is not segmented or archived, and all query scenarios need to read the full amount of data, resulting in poor system performance and waste of system resources; Problem 3: Data sharding is done, but as data continues to accumulate, the data in a single shard will become larger and larger. If hot and cold data are isolated, it will cause logical complexity for users and cannot be easily accessed. Summary of the invention

[0004] The purpose of the present invention is to solve the shortcomings of the prior art and provide a method for writing massive account flow data into a database based on Kafka, comprising the following steps:

[0005] Configure the master database and data synchronization tool for monitoring data change events, and establish self-built application services;

[0006] When receiving a write request from a client application service, the master database generates binlog information according to the write request and writes the binlog information into a Kafka message queue;

[0007] The self-built application service consumes the binlog information from the Kafka topic through the Kafka consumer API, parses and obtains data change events, and the data change events include time information and user ID number information;

[0008] The routing shards in the slave database are calculated and the transaction information is processed according to the time information and / or user ID number information of the data change event, wherein the slave database is configured with multiple levels of hot data sets and a full data set according to time, and the corresponding data is stored in each level of the hot data set and the full data set based on the user ID number shards.

[0009] Preferably, the master database and data synchronization tool configured to monitor data change events further include:

[0010] Creating a user account for the data synchronization tool in the master database, and granting the data synchronization tool REPLICATION SLAVE permissions and REPLICATION CLIENT permissions;

[0011] Configuring the master database information including at least the host, port, user name and password in the data synchronization tool;

[0012] The binlog of the primary database is started and set to row mode to monitor data change events of the primary database and generate binlog information, wherein the data change events include operation information such as addition, deletion, and modification.

[0013] Preferably, parsing the data change event to obtain the time and the user ID number, and processing the transaction information from the database according to the time and the user ID number further comprises:

[0014] If the data change event is analyzed as an add operation, the routing shard is calculated according to the user ID number, and the transaction information is stored in the corresponding shard data table of each level of hot data set and the corresponding shard data table of the full data set according to the routing shard;

[0015] If the data change event is analyzed as a deletion operation, the routing shard is calculated according to the time and the user ID number, and the corresponding transaction information in the corresponding shard data table of each level of hot data set and the corresponding shard data table of the full data set is deleted according to the routing shard;

[0016] If the data change event is analyzed as a modification operation, the routing shard is calculated according to the time and the user ID number, and the corresponding transaction information in the corresponding shard data table of each level of the hot data set and the corresponding shard data table of the full data set is modified according to the routing shard.

[0017] Preferably, configuring the multi-level hot data set and a full data set from the database according to time further includes:

[0018] In the slave database, a first-level storage for storing data within one month from the current time is established, and an independent shard data table is created according to the user ID number;

[0019] In the slave database, a second-level storage for storing data within three months from the current time is established, and an independent shard data table is created according to the user ID number;

[0020] In the slave database, a third-level storage for storing data within six months from the current time is established, and an independent shard data table is created according to the user ID number;

[0021] In the slave database, a fourth-level storage for storing the full amount of data is established, and an independent shard data table is created according to the user ID number;

[0022] The shard data table in the fourth-level storage is a cold data table, and the shard data table not in the fourth-level storage is a hot data table.

[0023] Preferably, the shard data table includes at least the following information: transaction account name, transaction time, transaction type, transaction amount, account balance, and transaction institution. Based on the transaction time, the current transaction data is written into multiple corresponding shard data tables in the multi-level hot data set and the full data set. There is a many-to-one unique mapping relationship between the multiple corresponding shard data tables and the transaction account name.

[0024] Preferably, the method further includes cleaning up the shard data tables in the multi-level data sets according to the created scheduled tasks:

[0025] A scheduled task of cleaning the sharded data table is executed in the early morning. The scheduled task cleans the data in the sharded data table that is one day farthest from the current time, and sets an anti-deletion and conflict mechanism to store data one day more than the time range in each level of data table to prevent the deletion and search of the same transaction information at the same time, resulting in the failure to query the information.

[0026] Preferably, the writing method further includes:

[0027] At preset time intervals, the account flow data associated with the data change events that occurred in the slave database within the last 24 hours are checked with the corresponding data in the master database to prevent data loss.

[0028] Based on the same concept, the present invention also provides a method for querying massive account flow data in a database based on Kafka, comprising the following steps:

[0029] The self-built application service receives the query request of the client application service, transfers the query request from the master database to the slave database, and confirms the data table level according to the request time of the query request, and confirms the data table shard according to the user request ID number of the query request;

[0030] The target shard data table is confirmed according to the data table level and the data table shard, and then the transaction information is obtained in the target shard data table and returned to the client application service.

[0031] Based on the same concept, the present invention also provides a database, which uses the Kafka-based database massive account flow data writing method as described in any one of the embodiments to write data or the Kafka-based database massive account flow data query method as described in the embodiments to query data.

[0032] Based on the same concept, the present invention also provides a storage medium storing computer-readable instructions. When the computer-readable instructions are executed by one or more processors, the one or more processors execute the steps of the method for writing massive account flow data to a database based on Kafka as described in any one of the embodiments.

[0033] Compared with the prior art, the present invention has the following beneficial effects:

[0034] (1) When receiving write requests from clients and application services, the present invention completes data write, update and delete operations in the master database, and further completes data grading, shard write, update and delete operations in the slave database based on monitoring of data change events in the master database. When receiving read requests from clients and application services, the self-built application service is transferred to the slave database, and the shard routing is calculated according to the read request to query the transaction data, thereby reducing the query pressure of the master database and improving the query response efficiency.

[0035] (2) The present invention classifies and fragments the account flow data in the database by time and user ID number. According to the user's regular query needs, the present invention first divides the multi-level hot data set according to the current time, such as 1 month, 3 months, 6 months, 1 year, etc., so that according to the query time of the user's query operation, it is possible to accurately locate the hot data set at which level to pull data, thereby solving the problem in the prior art that although the database is read-write separated and even data fragmentation is performed, as the business develops, the continuous accumulation of data leads to the slow query response of each fragmented data table. In this way, the data volume of a single data table is reduced, and the data retrieval scope is shortened during the query process, thereby optimizing the query efficiency;

[0036] (3) The present invention also regularly processes each shard data table in each level of hot data sets, so that the shard data tables in each level of hot data sets are kept at a relatively controllable size. Most user requests will hit the hot data sets, and a small number of user requests will hit the full data set. In this way, the amount of data that needs to be polled for user queries is reduced, thereby achieving the purpose of improving query response time. BRIEF DESCRIPTION OF THE DRAWINGS

[0037] Various other advantages and benefits will become apparent to those of ordinary skill in the art by reading the following detailed description of the preferred embodiment.The drawings are only for the purpose of illustrating the preferred embodiments and are not to be construed as limiting the invention.

[0038] Figure 1 This is a flow chart of a method for writing massive account flow data into a database based on Kafka according to the present invention;

[0039] Figure 2 A flow chart of monitoring data modification and multi-level redundant storage of the present invention;

[0040] Figure 3 The present invention is a client query flow chart of the method for writing massive account flow data into a database based on Kafka. DETAILED DESCRIPTION

[0041] In order to make the purpose, technical scheme and advantages of the present invention clearer, the present invention is further described in detail below in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are only used to explain the present invention and are not intended to limit the present invention. Obviously, the described embodiments are part of the embodiments of the present application, rather than all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in the field without making creative work are within the scope of protection of this application.

[0042] Those skilled in the art will appreciate that, unless otherwise stated, the singular forms "a", "an", and "the" used herein may also include plural forms. It should be further understood that the term "comprising" used in the specification of the present invention refers to the presence of the features, integers, steps, operations, elements and / or components, but does not exclude the presence or addition of one or more other features, integers, steps, operations, elements, components and / or groups thereof.

[0043] The technical terms involved in the embodiments of the present invention are defined as follows:

[0044] 1. Read-write separation. Read-write separation is a database architecture design that aims to improve system performance and scalability by separating read operations from write operations.

[0045] 2. Data sharding. Data sharding is a technology that divides a data set into multiple smaller parts (called "shards"). Each shard is stored and managed independently. Shards can be divided based on various strategies, such as by user ID number, hash value or other sharding key. Data sharding helps improve the scalability and performance of the system because each shard can handle read and write operations independently and is distributed on different nodes.

[0046] 3. Multi-level hot and cold isolation segmentation: data is divided into different levels (hot data and cold data) according to the access frequency and time range of the data. Hot data refers to data with high access frequency and requires a quick response, while cold data is data with low access frequency. Multi-level means isolating multiple copies of hot data according to different time ranges. As the time range increases, the amount of data will also increase. For example, level 1 hot data is data within one month, level 2 hot data is data within three months, level 3 cold data is data within half a year, and so on.

[0047] Example 1

[0048] As the number of users and transactions increases, the user's fund account flow bills will gradually increase and become a massive data set. However, the inventor of the present application found that the user's account flow data is strongly correlated with time. Therefore, based on this finding, the present invention further studies the technical solution of this embodiment to optimize the management of massive account flow data to improve the data responsiveness.

[0049] See also Figure 1 and Figure 2 As shown, the method for writing massive account flow data into a database based on Kafka provided in this embodiment includes the following steps:

[0050] Configure the master database and data synchronization tool for monitoring data change events, and establish self-built application services.

[0051] Preferably, the master database and data synchronization tool configured to monitor data change events further include:

[0052] Create a user account for the data synchronization tool in the master database, and grant the data synchronization tool REPLICATION SLAVE permissions and REPLICATION CLIENT permissions. Specifically, in this embodiment, create a dedicated user account for the data synchronization tool Canal or DTS in MySQL, and grant REPLICATION SLAVE and REPLICATION CLIENT permissions.

[0053] Configure the master database information including at least the host, port, user name and password in the data synchronization tool. Specifically, in this embodiment, specify the MySQL information in the configuration of Canal or DTS;

[0054] Start the binlog of the main database and set it to row mode to monitor the data change events of the main database and generate binlog information, where the data change events include operation information such as addition, deletion, and modification. Specifically, in this embodiment, binlog is enabled on the MySQL server and set to row mode to facilitate monitoring of data changes.

[0055] When monitoring the user's operations of adding, modifying, and deleting transaction records in the main database, an SQL statement is generated including changes in the transaction account name, transaction time, transaction type, transaction amount, account balance, etc. Among them, binlog is specified as row mode. The recorded content is no longer a simple SQL statement, but also includes the specific data of the operation to ensure absolute consistency of the data.

[0056] In some existing technologies, although the application layer sends read requests and write requests to different database servers, reducing the pressure on the main database, it does not solve the problem from the root. As the amount of data increases, the pressure on the database read will become greater and the performance will become worse.

[0057] In this embodiment, in the database architecture, the master database handles write operations, and multiple slave databases handle read operations. The slave databases of the database are used to make logical changes in the application system, and write requests are sent to the master database server to complete data writing, updating, and deleting operations.

[0058] When receiving a write request from a client application service, the primary database generates binlog information based on the write request and writes the binlog information into the Kafka message queue;

[0059] Self-built application services consume binlog information from Kafka topics through Kafka's consumer API, parse and obtain data change events, which contain time information and user ID number information;

[0060] The routing shards in the slave database are calculated and the transaction information is processed according to the time information and / or user ID number information of the data change event, wherein a multi-level hot data set and a full data set are configured in the slave database according to time, and the corresponding data is stored in each level of the hot data set and the full data set based on the user ID number shards. Specifically, in this embodiment, the binlog information includes all statements that update the main database, but does not include statements such as select and show that do not change the data, and Kafka has the advantages of easy expansion of new servers and high throughput.

[0061] Preferably, the MySQL database address and Kafka cluster address and other information are configured in the self-built application service or Canal to receive binlog information and write change events into the specified topic. The self-built application service consumes and parses binlog information in the specified topic and obtains data change events based on the subscription topic function of the consumer API.

[0062] Preferably, parsing the data change event to obtain the time and the user ID number, and processing the transaction information from the database according to the time and the user ID number further includes:

[0063] If the data change event is analyzed as an add operation, the routing shard is calculated according to the user ID number, and the transaction information is stored in the corresponding shard data table of each level of hot data set and the corresponding shard data table of the full data set according to the routing shard. Specifically, in this embodiment, the routing shard is calculated according to the user ID number, and the shard data table in which the user's transaction data should be stored is calculated, and the data change is applied to the database product. If it is the latest data at the current time, the corresponding shard data table of each level needs to be processed;

[0064] If the parsed data change event is a delete operation, the routing shard is calculated based on the time and user ID number, and the corresponding transaction information in the corresponding shard data table of each level of hot data set and the corresponding shard data table of the full data set is deleted based on the routing shard;

[0065] If the data change event is parsed as a modification operation, the routing shard is calculated based on the time and user ID number, and the transaction information in the corresponding shard data table of each level of hot data set and the corresponding shard data table of the full data set is modified based on the routing shard.

[0066] Preferably, after parsing the data change event, the transaction information is segmented and stored. This is equivalent to sharding and redundantly storing the transaction information in another link. The stored database product is not necessarily the same as the main database. Some database products with high-performance query and search databases can be used, such as ElasticSearch.

[0067] After selecting the database product to be used, you need to create multiple tables in this database product in advance.

[0068] Preferably, configuring a multi-level hot data set and a full data set from a database according to time further includes:

[0069] In the slave database, a first-level storage is established for storing data within one month from the current time, and an independent sharding data table is created according to the user ID number. Specifically, in this embodiment, a first-level data table is established in ElasticSearch to store data for the past month, and is sharded according to the last four digits of the user ID number. If the last four digits of the user ID number are 0001, it is stored in table 1_0001 (1 represents the first-level storage, 0001 represents the last four digits of the user ID), and if the last four digits of the user ID number are 0002, it is stored in table 1_0002. In this embodiment, the time range of the first-level table is not limited, and the time range can be set as needed, which can be 15 days, two months, or three months, etc.;

[0070] In the slave database, a second-level storage is established for storing data within three months from the current time, and an independent sharding data table is created according to the user ID number. Specifically, in this embodiment, the second-level data table is used to store data in the past three months, and is sharded according to the last four digits of the user ID number. If the last four digits of the user ID number are 0001, it is stored in table 2_0001 (2 represents the second-level storage, 0001 represents the last four digits of the user ID), and if the last four digits of the user ID number are 0002, it is stored in table 2_0002. This embodiment does not limit the time range of the second-level table, and the time range can be set as needed, which can be one month, two months, three months, six months, etc., but the time range of the second-level data table is larger than the time range of the first-level data table;

[0071] In the slave database, a third-level storage is established for storing data within six months from the current time, and an independent shard data table is created according to the user ID number. Specifically, in this embodiment, the time range of the third-level data table is not limited, and the time range can be set as needed, but the time range of the third-level data table is larger than the time range of the second-level data table;

[0072] In the slave database, establish a fourth-level storage for storing historical data, and create independent shard data tables based on user ID numbers;

[0073] Among them, the sharded data table in the fourth-level storage is a cold data table, and the sharded data table in non-fourth-level storage is a hot data table. Specifically, in this embodiment, the specific time period corresponding to the data table level is not limited and can be set as needed, but the time range gradually expands with the increase of the data table level. The data table of the last level is used to store the full amount of data, and the data table of the last level is a cold data table, and the data table of non-last level is a hot data table.

[0074] In the existing technical solutions for sharding databases and tables in application systems, developers need to deal with issues such as data sharding, routing, and cross-database queries. The management of cross-database transactions becomes more complicated and single-database transactions cannot be directly applied. In order to deal with this complexity, distributed transaction mechanisms (such as two-phase commit, TCC, etc.) are needed to ensure data consistency, which increases the complexity of data access. In addition, the increase in the number of database instances and tables makes database management, backup, monitoring, and troubleshooting more complicated, increasing the complexity of operation and maintenance.

[0075] In this embodiment, when receiving a query request from a client application service, the self-built application service confirms the data table in the data based on the request time and the user request ID number, and then queries the transaction information in the data table. In this way, the amount of data in a single database table is reduced, thereby achieving the purpose of increasing the query speed.

[0076] Preferably, the shard data table includes at least the following information: transaction account name, transaction time, transaction type, transaction amount, account balance, and transaction institution. Based on the transaction time, the current transaction data is written into multiple corresponding shard data tables in the multi-level hot data set and the full data set. There is a many-to-one unique mapping relationship between the multiple corresponding shard data tables and the transaction account name.

[0077] Different from conventional storage data, account flow data is strongly correlated with time, and users' queries on data are often based on time. Based on this, the present invention performs multi-level hot and cold isolation segmentation based on the time dimension, and further divides the hot data set into multiple levels, thereby realizing refined processing of account flow data based on the time dimension. When querying, the data is accurately located in the hot data set of the target level according to the time of the query request. On this basis, each level of the data set is further sharded based on the transaction account name, thereby realizing the maximum improvement of response efficiency.

[0078] Preferably, this embodiment further includes cleaning up the shard data table in the multi-level data table according to the created scheduled task:

[0079] A scheduled task for cleaning up the sharded data table is executed in the early morning. The scheduled task cleans up the data in the sharded data table that is one day farthest from the current time, and sets up an anti-deletion and conflict mechanism to store data one day more than the time range in each level of data table to prevent deletion and search of the same transaction information at the same time, resulting in failure to query the information. Specifically, in this embodiment, it is necessary to clean up the hot data table at each level except the cold data table at the last level. In this way, the hot data table at each level only retains the data within the range of this level, ensuring that the data size of each shard will not grow indefinitely. Although the full data of the last level will continue to increase, the query request for the data at the last level will be very small and will hardly be accessed. At the same time, the full data is also sharded according to the user ID number, so the overall data volume is controllable.

[0080] Preferably, the writing method further includes:

[0081] At preset time intervals, the account transaction data associated with data change events that occurred in the database within the last 24 hours will be checked against the corresponding data in the main database to prevent data loss.

[0082] By implementing this method, it was found that the probability of the client's query request hitting the first-level hot data reached more than 95%, and the average response time was within 100 milliseconds. The business system did not require sharding of libraries and tables, or separation of database reads and writes, which reduced the complexity of cross-database transactions and operations and maintenance.

[0083] Example 2

[0084] See also Figure 3 As shown, this embodiment provides a method for querying massive account flow data in a database based on Kafka, which is used to execute the method in the above embodiment 1, including the following steps:

[0085] The self-built application service receives the query request from the client application service, transfers from the master database to the slave database, and confirms the data table level according to the request time of the query request, and confirms the data table sharding according to the user request ID number of the query request. Specifically, in this embodiment, the routing sharding is calculated based on the user request ID number, and the time range is determined according to the request time. Assuming that the last four digits of the user ID number are 0001, it is confirmed that table 0001 should be queried. Assuming that the time range of the requested data is within one month, it is confirmed that the data of the first-level table should be queried, and then the transaction information in the 1_0001 data table is obtained. If the time range of the requested data is data between the last 1 month and 1.5 months, the data in the first-level table can no longer meet the requirements, and the second-level table needs to be queried. In this case, table 2_0001 needs to be queried, and the time range is obtained by analogy. If the data hot data is multi-level stored, the longest data is stored in the past two years, but the time range of the requested data is data two years ago, then the last level of full data is queried.

[0086] The target shard data table is confirmed according to the data table level and the data table shard, and then the transaction information is obtained in the target shard data table and returned to the client application service.

[0087] In this way, the client only needs to make a forwarding request modification to improve the query speed, without having to worry about other technical issues caused by sharding, hot and cold isolation.

[0088] The existing separation of hot and cold data requires additional design and maintenance of the system, which increases the complexity of the system. For example, the judgment logic of hot and cold data needs to be implemented in the business code, and the boundary of hot and cold data is difficult to set. If it is not set properly, a large number of requests will hit the cold data, resulting in the problem of hot data failure. In addition, if the user's query conditions happen to be part of both hot and cold data, paging and sorting are required, the logic of the application system will be very complicated.

[0089] In this embodiment, transaction information is stored separately according to the time dimension to reduce the amount of data queried by users, thereby achieving the purpose of improving query response time, and redundant storage of transaction information is achieved from the database. In order to control the size of the data, the transaction information needs to be cleaned up so that it is within a certain size range.

[0090] In this embodiment, the data query operation is completed by sending a read request to the slave database server, which can reduce the load of the master database, thereby achieving the purpose of reducing the query pressure of the master database and improving the concurrent processing capability of the system.

[0091] Example 3

[0092] In some embodiments of the present application, a database is also provided, which uses the Kafka-based database massive account flow data writing method as described in any one of Example 1 to write data or the Kafka-based database massive account flow data query method as described in Example 2 to query data.

[0093] The present invention also provides a storage medium storing computer-readable instructions. When the computer-readable instructions are executed by one or more processors, the one or more processors execute the steps of the method for writing massive account flow data to a database based on Kafka as described in any one of Example 1 or the steps of the method for querying massive account flow data in a database based on Kafka as described in Example 2.

[0094] It is understandable that, for the aforementioned method for writing massive account flow data into a database based on Kafka or the aforementioned method for querying massive account flow data in a database based on Kafka, if both are implemented in the form of software function modules and sold or used as independent products, they can be stored in a computer-readable storage medium. Based on such an understanding, the technical solution of the present invention is essentially or part of the contribution to the prior art or all or part of the technical solution can be embodied in the form of a software product, and the computer software product is stored in a storage medium, including several instructions for enabling a computer device (which can be a personal computer server, or a network device, etc.) to perform all or part of the steps of the methods of various embodiments of the present invention. The aforementioned storage medium includes: various media that can store program codes, such as a U disk, a mobile hard disk, a read-only memory (ROM), a random access memory (RAM), a disk or an optical disk.

[0095] Computer readable storage media may include data signals propagated in baseband or as part of a carrier wave, wherein readable program codes are carried. Such propagated data signals may take various forms, including but not limited to electromagnetic signals, optical signals, or any suitable combination thereof. The readable storage medium may also be any readable medium other than a readable storage medium, which may send, propagate, or transmit a program for use by or in conjunction with an instruction execution system, device, or device. The program codes contained on the readable storage medium may be transmitted using any suitable medium, including but not limited to wireless, wired, optical cable, RF, etc., or any suitable combination thereof.

[0096] The above is only a preferred embodiment of the present invention, and the protection scope of the present invention is not limited to the above embodiments. All technical solutions under the concept of the present invention belong to the protection scope of the present invention. It should be pointed out that for ordinary technicians in this technical field, some improvements and modifications without departing from the principle of the present invention should also be regarded as the protection scope of the present invention.

Claims

1. A method for writing massive account flow data into a database based on Kafka, characterized in that: The following steps are involved: Configure the master database and data synchronization tool for monitoring data change events, and establish self-built application services; When receiving a write request from a client application service, the master database generates binlog information according to the write request and writes the binlog information into a Kafka message queue; The self-built application service consumes the binlog information from the Kafka topic through the Kafka consumer API, parses and obtains data change events, and the data change events include time information and user ID number information; Calculate the routing shards in the slave database and process the transaction information according to the time information of the data change event and / or the user ID number information, wherein the slave database configures multiple levels of hot data sets and a full data set according to time, and each level of the hot data set and the full data set stores the corresponding data in shards based on the user ID number; Wherein, configuring a multi-level hot data set and a full data set from the database according to time further includes: In the slave database, a first-level storage for storing data within one month from the current time is established, and an independent shard data table is created according to the user ID number; In the slave database, a second-level storage for storing data within three months from the current time is established, and an independent shard data table is created according to the user ID number; In the slave database, a third-level storage for storing data within six months from the current time is established, and an independent shard data table is created according to the user ID number; In the slave database, a fourth-level storage for storing the full amount of data is established, and an independent shard data table is created according to the user ID number; The shard data table in the fourth-level storage is a cold data table, and the shard data table not in the fourth-level storage is a hot data table.

2. The method for writing massive account flow data into a database based on Kafka according to claim 1, characterized in that: The master database and data synchronization tool configured to monitor data change events further include: Creating a user account for the data synchronization tool in the master database, and granting the data synchronization tool REPLICATION SLAVE permissions and REPLICATION CLIENT permissions; Configuring the master database information including at least the host, port, user name and password in the data synchronization tool; The binlog of the primary database is started and set to row mode to monitor data change events of the primary database and generate binlog information, wherein the data change events include operation information such as addition, deletion, and modification.

3. The method for writing massive account flow data into a database based on Kafka according to claim 2, characterized in that: Parsing the data change event to obtain the time and the user ID number, and processing the transaction information from the database according to the time and the user ID number further includes: If the data change event is analyzed as an add operation, the routing shard is calculated according to the user ID number, and the transaction information is stored in the corresponding shard data table of each level of hot data set and the corresponding shard data table of the full data set according to the routing shard; If the data change event is analyzed as a deletion operation, the routing shard is calculated according to the time and the user ID number, and the corresponding transaction information in the corresponding shard data table of each level of hot data set and the corresponding shard data table of the full data set is deleted according to the routing shard; If the data change event is analyzed as a modification operation, the routing shard is calculated according to the time and the user ID number, and the corresponding transaction information in the corresponding shard data table of each level of the hot data set and the corresponding shard data table of the full data set is modified according to the routing shard.

4. The method for writing massive account flow data into a database based on Kafka according to claim 1, characterized in that: The shard data table includes at least the following information: transaction account name, transaction time, transaction type, transaction amount, account balance, and transaction institution. Based on the transaction time, the current transaction data is written into multiple corresponding shard data tables in the multi-level hot data set and the full data set. There is a many-to-one unique mapping relationship between the multiple corresponding shard data tables and the transaction account name.

5. The method for writing massive account flow data into a database based on Kafka according to claim 4, characterized in that: It also includes cleaning up the hot data tables in the data table according to the created scheduled tasks: A scheduled task of cleaning the sharded data table is executed in the early morning. The scheduled task cleans the data in the sharded data table that is one day farthest from the current time, and sets an anti-deletion and conflict mechanism to store data one day more than the time range in each level of data table to prevent the deletion and search of the same transaction information at the same time, resulting in the failure to query the transaction information.

6. The method for writing massive account flow data into a database based on Kafka according to claim 1, characterized in that: The method further comprises: At preset time intervals, the account flow data associated with the data change events that occurred in the slave database within the last 24 hours are checked with the corresponding data in the master database to prevent data loss.

7. A method for querying massive account flow data in a database based on Kafka, applied to a database adapted by any one of the writing methods of claims 1 to 6, characterized in that: The steps include: The self-built application service receives the query request of the client application service, transfers the query request from the master database to the slave database, and confirms the data table level according to the request time of the query request, and confirms the data table shard according to the user request ID number of the query request; The target shard data table is confirmed according to the data table level and the data table shard, and then the transaction information is obtained in the target shard data table and returned to the client application service.

8. A database, characterized in that: The database adopts the Kafka-based database massive account flow data writing method described in any one of claims 1 to 6 to write data or the Kafka-based database massive account flow data query method described in claim 7 to query data.

9. A storage medium storing computer-readable instructions, characterized in that: When the computer-readable instructions are executed by one or more processors, the one or more processors execute the steps of the method for writing massive account flow data to a database based on Kafka as described in any one of claims 1 to 7.

Images