A method for storing and querying massive conversation data

Abstract

The present invention proposes a method for storing and querying massive session data, including: creating an index bucket in a database, the index bucket is composed of multiple layers of index space, and different time precision labels are assigned to each layer of index space; monitoring the target data interface, saving the session data obtained from the target data interface into a preset minute precision table in the database, and saving the index information of the minute precision table into the first layer of index space of the index bucket; configuring materialized views for the first to second to last layers of index space to monitor the data changes in the previous layer of index space, wherein the materialized view in the first layer of index space is used to monitor the data update of the minute precision table; in response to the monitored data changes in the minute precision table, the index information in the multiple layers of index space is updated in sequence. The present invention proposes to create an index bucket with time precision in the process of generating a data table to improve the efficiency of data query and reduce the CPU occupancy.

Description

Technical Field

[0001] The invention belongs to the field of computer data storage, and in particular relates to a method for storing and querying massive session data. Background Art

[0002] At present, the existing technology manages session data by creating data tables and storing them according to interfaces. In addition, considering the need to meet the statistics of various detailed lists (such as hosts, IP sessions, TCP sessions, UDP sessions, server accesses, server binary lists, etc.) and the display of multi-dimensional data (such as time series diagrams, pie charts, TOP charts), when designing interface data tables, data is generally stored with minute precision, that is, each data table only stores session data within one minute (hereinafter referred to as minute precision table).

[0003] However, minute-precision tables are not suitable for table sharding, which will cause data queries to be slow and easily exhaust the memory, leading to system abnormalities. Summary of the invention

[0004] In order to improve the query speed of session data and reduce memory usage, in a first aspect of the present invention, a method for storing massive session data is proposed, the method comprising: creating an index bucket in a database, the index bucket consisting of multiple layers of index space, and assigning different time precision labels to each layer of index space; monitoring a target data interface, saving the session data obtained from the target data interface into a minute precision table preset in the database, and saving the index information of the minute precision table into the first layer of index space of the index bucket; configuring materialized views for the first to second to last layers of index space to monitor data changes in the previous layer of index space, wherein the materialized views in the first layer of index space are used to monitor data updates in the minute precision table; in response to monitoring changes in the data in the minute precision table, updating the index information in the multiple layers of index space in sequence.

[0005] In one or more embodiments, an index bucket is created in a database, wherein the index bucket is composed of multiple layers of index space, and different time precision labels are assigned to each layer of index space, including: creating an index bucket with a bucket depth of at least 3 in the database to form at least three layers of index space, and assigning ten minutes, hours, and days as time precision labels to the first to third layers of index space, respectively.

[0006] In one or more embodiments, the session data obtained from the target data interface is saved in a minute precision table preset in a database, including: monitoring the target data interface, extracting the session data obtained from the target data interface into a memory; aggregating the session data with the same quadruple and time precision in the memory according to a preset matching rule; and saving the aggregated session data in the minute precision table.

[0007] In one or more embodiments, session data with the same quadruple and time precision are aggregated in the memory, including: obtaining quadruple information and timestamp information in the session data; determining the time precision of the session data according to the timestamp information; and aggregating session data with the same source address and destination address and the same time precision.

[0008] In one or more embodiments, saving the aggregated session data to the minute precision table includes: saving the aggregated session data to the minute precision table, and configuring the minute time precision of the first session data as the table name of the minute precision table as index information.

[0009] In one or more embodiments, in response to monitoring changes in the data of the minute precision table, the index information in the multi-layer index space is updated in sequence, including: in response to monitoring the generation of a new minute precision table, determining whether the time precision of the new minute precision table belongs to the current 10-minute precision table in the first-layer index space; if it belongs to the current 10-minute precision table in the first-layer index space, saving the index information of the new minute precision table to the 10-minute precision table; if it does not belong to the current 10-minute precision table in the first-layer index space, generating a new 10-minute precision table in the first-layer index space, and saving the index information of the new minute precision table to the new 10-minute precision table.

[0010] In one or more embodiments, in response to monitoring changes in the data of the minute precision table, the index information in the multi-layer index space is updated in sequence, and it also includes: in response to monitoring the generation of a new 10-minute precision table, determining whether the time precision of the new 10-minute precision table belongs to the current hourly precision table in the second-layer index space; if it belongs to the current hourly precision table in the second-layer index space, saving the index information of the new 10-minute precision table to the hourly precision table; if it does not belong to the current hourly precision table in the second-layer index space, generating a new hourly precision table in the second-layer index space, and saving the index information of the new 10-minute precision table to the new hourly precision table.

[0011] In one or more embodiments, in response to monitoring changes in the data of the minute precision table, the index information in the multi-layer index space is updated in sequence, and it also includes: in response to monitoring the generation of a new hourly precision table, determining whether the time precision of the new hourly precision table belongs to the current day precision table in the third-layer index space; if it belongs to the current day precision table in the third-layer index space, saving the index information of the new hourly precision table to the day precision table; if it does not belong to the current day precision table in the third-layer index space, generating a new day precision table in the third-layer index space, and saving the index information of the new hourly precision table to the new day precision table.

[0012] In one or more embodiments, a method for determining whether the time precision of a newly generated precision table in an upper-level index space belongs to a current precision table in a lower-level index space includes: determining whether the time difference between the table name of the newly generated precision table in the upper-level index space and the table name of the current precision table in the lower-level index space is less than a preset threshold; if it is less than the preset threshold, determining that the time precision of the newly generated precision table in the upper layer belongs to the current precision table in the lower layer; wherein the preset thresholds of the first to third-level index spaces are 10 minutes, 1 hour, and 24 hours, respectively.

[0013] In a second aspect of the present invention, a massive data query method based on any of the above-mentioned massive data query method embodiments is proposed, and the query method includes: inputting a time range of the data to be queried; splitting the time range into corresponding time precisions; performing matching indexes in the third-layer to the first-layer index space in sequence according to the time precision, and determining the range of the minute data table according to the final index precision.

[0014] The beneficial effects of the present invention include: the present invention proposes to simultaneously create index buckets during the process of creating a data table in the corresponding data interface and manage the index information of the data table according to time precision, so that in the subsequent data query process, data query can be performed based on the time precision range of the requested query, thereby improving data query efficiency and reducing CPU occupancy. BRIEF DESCRIPTION OF THE DRAWINGS

[0015] In order to more clearly illustrate the embodiments of the present invention 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 in the following description are only some embodiments of the present invention. For ordinary technicians in this field, other embodiments can be obtained based on these drawings without paying creative work.

[0016] Figure 1 A flowchart of a method for storing massive data according to an embodiment of the present invention;

[0017] Figure 2 A schematic diagram of a process of updating data in an index bucket through a materialized view according to an embodiment of the present invention;

[0018] Figure 3 The figure is a schematic diagram of a process of performing data query based on an index bucket according to an embodiment of the present invention. DETAILED DESCRIPTION

[0019] In order to make the objectives, technical solutions and advantages of the present invention more clearly understood, the embodiments of the present invention are further described in detail below in combination with specific embodiments and with reference to the accompanying drawings.

[0020] It should be noted that all expressions using "first" and "second" in the embodiments of the present invention are for distinguishing two non-identical entities with the same name or non-identical parameters. It can be seen that "first" and "second" are only for the convenience of expression and should not be understood as limitations on the embodiments of the present invention. The subsequent embodiments will not explain this one by one.

[0021] In order to improve the query speed of session data and reduce memory usage, in one embodiment, the present invention proposes a method for storing massive session data. Figure 1 The method includes: step S1, creating an index bucket in a database, the index bucket consists of multiple layers of index space, and assigning different time precision labels to each layer of index space; step S2, monitoring the target data interface, saving the session data obtained from the target data interface into a preset minute precision table in the database, and saving the index information of the minute precision table into the first layer of index space in the index bucket; step S3, configuring materialized views for the first layer to the penultimate layer of index space to monitor data changes in the previous layer of index space, wherein the materialized view in the first layer of index space is used to monitor data updates in the minute precision table; step S4, in response to monitoring changes in the data in the minute precision table, updating the index information in the multiple layers of index space in sequence.

[0022] Specifically, this embodiment proposes to create an index bucket at the same time as creating a data table in the corresponding data interface and manage the index information of the data table according to time precision, so that in the subsequent data query process, data query can be performed based on the time precision range of the requested query, thereby improving data query efficiency and reducing CPU occupancy.

[0023] In one or more embodiments, an index bucket is created in a database, the index bucket consists of multiple layers of index space, and different time precision labels are assigned to each layer of index space, including: creating an index bucket with a bucket depth of at least 3 in the database to form at least three layers of index space, and assigning ten minutes, hours, and days as time precision labels to the first to third layers of index space, respectively.

[0024] Specifically, the depth of the index bucket corresponds to a multi-layer index space, and the multi-layer index space corresponds to multiple index precisions, so the depth of the index bucket needs to be set according to the index precision. Since the basic precision of the present application is the minute table, the index bucket stores the data index after the minute table is aggregated according to the preset rules, and forms a 10-minute table, an hour table, and a day table respectively. Among them, the minute table stores the main content of the session data, while the 10-minute table, the hour table, and the day table only store the index information of the upper-level precision table, such as the index information of the hour table is stored in the day table, the index information of the 10-minute table is stored in the hour table, and the index information of the final minute table is stored in the 10-minute table.

[0025] In one embodiment, the index precision may further include year and month, and the depth of the corresponding index bucket should be increased accordingly.

[0026] In an optional embodiment, since the index information in the first to third layer index spaces will gradually decrease, different index spaces may be allocated to the first to third layer index spaces.

[0027] In one embodiment, the method of the present invention also includes: monitoring the target data interface, and saving the session data obtained from the target data interface to a minute precision table preset in the database, including: monitoring the target data interface, and extracting the session data obtained from the target data interface into the memory; according to the preset matching rules, aggregating the session data with the same quadruple and time precision in the memory; and saving the aggregated session data to the minute precision table.

[0028] Specifically, after obtaining data from the data interface, after matching the preset rules, the system will aggregate the session data with the same quadruple + time precision in the memory according to the minute precision and then write it into the minute table of the CH library. Among them, the time precision of the session data needs to be determined by splitting the time when the session data is generated or sent, and according to the preset rules (such as rounding up / down): For example:

[0029] The start time of a session is: 2024-10-10 12:23:34.634718152. The time accuracy of this record is as follows:

[0030] Minute precision: 2024-10-10 12:23:00;

[0031] 10-minute accuracy: 2024-10-10 12:30:00 (rounded up is used here);

[0032] Hour accuracy: 2024-10-10 12:00:00;

[0033] Day precision: 2024-10-10 00:00:00.

[0034] More specifically, the purpose of storing data with the same four-tuple into the same minute table is to facilitate subsequent queries and detailed statistics (such as hosts, IP sessions, TCP sessions, UDP sessions, server accesses, server two-tuples, etc.) and multi-dimensional data (such as time series diagrams, pie charts, TOP charts) display.

[0035] In one embodiment, according to preset matching rules, session data with the same quadruple and time precision are aggregated in memory, including: obtaining quadruple information and timestamp information in the session data; determining the time precision of the session data according to the timestamp information; and aggregating session data with the same source address and destination address and the same time precision.

[0036] Specifically, the time accuracy of the data is determined based on the timestamp that accompanies the data, and the timestamp is applied by the data sending end to record the information of the generation time or sending time of the data.

[0037] In one embodiment, saving the aggregated session data into a minute precision table includes: saving the aggregated session data into the minute precision table, and configuring the minute time precision of the first session data as the table name of the minute precision table as index information.

[0038] Specifically, since multiple session data may be stored in the minute table, and the time when these session data are generated or sent is different, in order to unify the index information of the minute table formed after aggregation, this implementation chooses to use the minute time accuracy of the first session data as the index information of the minute table. The time difference between the last session data and the first session data in the minute table is within one minute, that is, they have the same minute time accuracy.

[0039] In an optional implementation, when the timestamp information of the session data is accurate to seconds, it needs to be rounded up. For example, if the timestamp information of a session data is: 2020-12-1-15:30:59, the index information of the minute table should be set to 2020-12-1-15:30. The process of determining the minute time accuracy of the session data will be performed in memory.

[0040] In one embodiment, in response to monitoring that data in the minute precision table changes, the index information in the multi-layer index space is updated in sequence, including: in response to monitoring that a new minute precision table is generated, determining whether the time precision of the new minute precision table belongs to the current 10-minute precision table in the first-layer index space; if it belongs to the current 10-minute precision table in the first-layer index space, saving the index information of the new minute precision table to the 10-minute precision table; if it does not belong to the current 10-minute precision table in the first-layer index space, generating a new 10-minute precision table in the first-layer index space, and saving the index information of the new minute precision table to the new 10-minute precision table;

[0041] By analogy, the updating process of the 10-minute table includes: in response to monitoring that a new 10-minute precision table is generated, determining whether the time precision of the new 10-minute precision table belongs to the current hourly precision table in the second-level index space; if it belongs to the current hourly precision table in the second-level index space, saving the index information of the new 10-minute precision table to the hourly precision table; if it does not belong to the current hourly precision table in the second-level index space, generating a new hourly precision table in the second-level index space, and saving the index information of the new 10-minute precision table to the new hourly precision table.

[0042] By analogy, the updating process of the hourly table includes: in response to monitoring that a new hourly precision table is generated, determining whether the time precision of the new hourly precision table belongs to the current day precision table in the third-layer index space; if it belongs to the current day precision table in the third-layer index space, saving the index information of the new hourly precision table to the day precision table; if it does not belong to the current day precision table in the third-layer index space, generating a new day precision table in the third-layer index space, and saving the index information of the new hourly precision table to the new day precision table.

[0043] For details, see Figure 2 In order to ensure that the data update of the minute table can be updated to each precision table in real time and achieve aggregation, the present invention will use the materialized view and SummingMergeTree table engine provided by Clickhouse to complete it. Among them, the materialized view is mainly used to monitor data updates, and only needs to create corresponding materialized views for the minute table, 10-minute table, and hour table, and generate corresponding precision tables according to the data update situation. The format of generating each precision table is as follows:

[0044] Minute table yyyy-MM-dd HH:mm:00 formatted according to the session start time

[0045] The 10-minute table yyyy-MM-dd HH:mm:00 is formatted according to the session start time;

[0046] The hour table yyyy-MM-dd HH:00:00 is formatted according to the session start time;

[0047] The daily table yyyy-MM-dd 00:00:00 is formatted according to the session start time;

[0048] In the above format, yyyy represents the year, MM represents the month, dd represents the day, HH represents the hour, and mm represents the number of minutes, where 00≤mm≤59. The materialized view is automatically executed. When data is inserted into the minute table, the materialized view immediately aggregates the newly inserted data with 10-minute precision and inserts it into the corresponding 10-minute table. When the materialized view of the 10-minute table finds that data is inserted, it immediately aggregates the inserted data with hourly precision and inserts it into the corresponding hourly table. When the materialized view of the hourly table finds that data is inserted, it immediately aggregates the inserted data with day precision and inserts it into the corresponding day table. The above is the main workflow of the materialized view of the index bucket.

[0049] For the data inserted into the precision table, if the same quadruple + corresponding precision data is found, the SummingMergeTree engine is needed to determine the duplicate data based on the specified field and sum the other data; Clickhouse defines that order by is added after the specified engine, and duplicates are determined based on the fields after order by, and other data refers to fields other than the fields specified by the engine table order by.

[0050] The present invention needs to create a SummingMergeTree engine for the minute table, the 10-minute table, the hour table, and the day table. When data enters the minute table, if the specified order by field in these data is repeated, the SummingMergeTree engine will be triggered to perform sum calculation on the newly entered data and the existing identical data.

[0051] In an optional embodiment, the sum calculation in the present invention refers to customizing each field through the sum(column) method, and not all fields can only be summed. For example: to find the maximum or minimum value of a field, max(column1)min(column2), where column1 and column2 correspond to different fields.

[0052] In one embodiment, a method for determining whether the time precision of a newly generated precision table in an upper-level index space belongs to a current precision table in a lower-level index space includes: determining whether the time difference between a table name of a newly generated precision table in an upper-level index space and a table name of a current precision table in a lower-level index space is less than a preset threshold; if it is less than the preset threshold, determining that the time precision of the newly generated precision table in the upper layer belongs to the current precision table in the lower layer; wherein the preset thresholds for the first to third-level index spaces are 10 minutes, 1 hour, and 24 hours, respectively.

[0053] In a second aspect of the present invention, a method for performing data query based on the index bucket formed in the above embodiment is proposed, comprising:

[0054] Step 100: input the time range of the data to be queried;

[0055] Step 200: split the time range into corresponding time precisions;

[0056] Step 300: perform matching indexes in the third layer to the first layer index space in sequence according to the time precision, and determine the range of the minute data table according to the final index precision.

[0057] Specifically, the present invention will use the time splitting method to split the query time into the corresponding precision range and query in the specified precision table. For example, suppose the time range of the data to be queried is: 2024-06-12 12:34:00 - 2024-06-16 18:22:00: For the query process, please refer to Figure 3 :

[0058] If the last two hours are used as the aggregation time for data entry into the bucket, the above time range can be split into:

[0059] 2024-06-12 12:34:00 - 2024-06-12 12:40:00 Check the minute table;

[0060] 2024-06-12 12:40:00 - 2024-06-12 13:00:00 Check the 10-minute table;

[0061] 2024-06-12 13:00:00 - 2024-06-13 00:00:00 Check the hour meter;

[0062] 2024-06-13 00:00:00 - 2024-06-16 00:00:00 Check the daily table;

[0063] 2024-06-16 00:00:00 - 2024-06-16 16:00:00 Check the hour meter;

[0064] 2024-06-16 16:00:00 - 2024-06-16 18:10:00 Check the 10-minute table;

[0065] 2024-06-16 18:10:00 - 2024-06-16 18:22:00 Check the minute table;

[0066] And other precision ranges.

[0067] In one embodiment, in order to avoid memory exhaustion, query requests for hourly tables and daily tables can be split into multiple query requests with smaller precision, such as splitting a day into 24 hours and splitting an hour into six 10-minute periods, and the precision of the split requests is determined based on the remaining space in the current memory.

[0068] The present invention uses index buckets to establish an index relationship between an index space and a minute table, can achieve accurate search of a data table, and facilitates splitting of query requests, thereby avoiding excessive memory usage and reducing the impact of data query on system operation.

[0069] The above are exemplary embodiments disclosed in the present invention, but it should be noted that various changes and modifications may be made without departing from the scope of the embodiments disclosed in the claims. The functions, steps and / or actions of the method claims according to the disclosed embodiments described herein do not need to be performed in any particular order.

[0070] A person skilled in the art should understand that the discussion of any of the above embodiments is only exemplary and is not intended to imply that the scope of the disclosure of the embodiments of the present invention (including the claims) is limited to these examples; under the concept of the embodiments of the present invention, the technical features in the above embodiments or different embodiments can also be combined, and there are many other changes in different aspects of the above embodiments of the present invention, which are not provided in detail for the sake of simplicity. Therefore, any omissions, modifications, equivalent substitutions, improvements, etc. made within the spirit and principles of the embodiments of the present invention should be included in the protection scope of the embodiments of the present invention.

Claims

1. A method for storing massive conversation data, characterized in that: The method comprises: Create an index bucket in the database, where the index bucket consists of multiple layers of index space, and assign different time precision labels to each layer of index space; Monitor the target data interface, save the session data obtained from the target data interface into a minute precision table preset in the database, and save the index information of the minute precision table into the first-level index space of the index bucket; Materialized views are configured for the first to the penultimate index spaces to monitor data changes in the previous index space, wherein the materialized view in the first index space is used to monitor data updates in the minute precision table; In response to monitoring changes in the data of the minute precision table, updating the index information in the multi-layer index space in sequence; Monitoring a target data interface and saving session data obtained from the target data interface into a minute precision table preset in a database includes: monitoring a target data interface and extracting session data obtained from the target data interface into a memory; obtaining quadruple information and timestamp information in the session data in the memory; determining the time precision of the session data according to the timestamp information; aggregating session data having the same source address and destination address and the same time precision; and saving the aggregated session data into the minute precision table.

2. The method for storing massive conversation data according to claim 1, characterized in that: Create an index bucket in the database. The index bucket consists of multiple layers of index space, and assign different time precision labels to each layer of index space, including: An index bucket with a bucket depth of at least 3 is created in the database to form at least three layers of index space, and ten minutes, hours, and days are assigned as time precision labels to the first to third layers of index space, respectively.

3. The method for storing massive conversation data according to claim 1, characterized in that: The aggregated session data is saved to the minute-precision table, including: The aggregated session data is saved in the minute precision table, and the minute time precision of the first session data is configured as the table name of the minute precision table as index information.

4. The method for storing massive conversation data according to claim 1, characterized in that: In response to monitoring changes in the data of the minute precision table, updating the index information in the multi-layer index space in sequence, including: In response to monitoring that a new minute precision table is generated, determining whether the time precision of the new minute precision table belongs to the current 10-minute precision table in the first-layer index space; If it belongs to the current 10-minute precision table in the first-level index space, the index information of the new minute precision table is saved in the 10-minute precision table; If it does not belong to the current 10-minute precision table in the first-layer index space, a new 10-minute precision table is generated in the first-layer index space, and the index information of the new minute precision table is saved in the new 10-minute precision table.

5. The method for storing massive conversation data according to claim 4, characterized in that: The method further comprises: In response to monitoring that a new 10-minute precision table is generated, determining whether the time precision of the new 10-minute precision table belongs to the current hourly precision table in the second-layer index space; If it belongs to the current hourly precision table in the second-level index space, the index information of the new 10-minute precision table is saved in the hourly precision table; If it does not belong to the current hourly precision table in the second-layer index space, a new hourly precision table is generated in the second-layer index space, and the index information of the new 10-minute precision table is saved in the new hourly precision table.

6. The method for storing massive conversation data according to claim 5, characterized in that: The method further comprises: In response to monitoring that a new hourly precision table is generated, determining whether the time precision of the new hourly precision table belongs to the current daily precision table in the third-level index space; If it belongs to the current day-precision table in the third-level index space, the index information of the new hour-precision table is saved in the day-precision table; If it does not belong to the current day-precision table in the third-level index space, a new day-precision table is generated in the third-level index space, and the index information of the new hour-precision table is saved in the new day-precision table.

7. The method for storing massive conversation data according to any one of claims 4 to 6, characterized in that: Methods for determining whether the time precision of the newly generated precision table in the previous index space belongs to the current precision table in the next index space include: Determine whether the time difference between the table name of the newly generated precision table in the previous index space and the table name of the current precision table in the next index space is less than a preset threshold; If it is less than the preset threshold, it is determined that the time accuracy of the newly generated accuracy table in the previous layer belongs to the current accuracy table in the next layer; The preset thresholds of the first to third index spaces are 10 minutes, 1 hour, and 24 hours, respectively.

8. A method for querying massive session data based on the method for storing massive session data according to any one of claims 1 to 7, characterized in that: The query method comprises: Enter the time range of the data to be queried; Splitting the time range into corresponding time precisions; According to the time precision, matching indexes are performed in the third layer to the first layer index space in sequence, and the range of the minute data table is determined according to the final index precision.

Images