A data query method and device

Abstract

The application discloses a data query method and device, and relates to the field of database query. The method comprises the following steps: a server receives a data query request from a client, the data query request comprising a query time range and a query condition set; the data query request is used for querying a database of the server, the database comprising a plurality of partitions obtained by the database, each partition corresponding to a different time range, each partition comprising a plurality of data blocks sorted in time sequence, and the database being used for storing time series data; data blocks in at least one partition corresponding to the query time range are traversed; in the traversal process, data matching is performed on the data block at the current traversal position according to the query condition set, and after the data matching is completed, the data matched from the data block at the current traversal position according to the query condition set is output to the client, so that the resource consumption is reduced, the continuous response output is realized, the backend query performance is improved, and the frontend waiting time is reduced.

Description

Technical Field

[0001] This application relates to the field of database query technology, and in particular to a data query method and apparatus. Background Technology

[0002] In scenarios using ClickHouse to store protocol metadata, the metadata is already partitioned by day according to its generation time. During metadata queries, given the time span, partitions can be pruned to effectively improve query speed. However, this approach also has significant drawbacks. Specifically, each page of data needs to be fully populated before being returned to the front end. If the page's data comes from multiple time partitions, and the data volume of a single partition is too large, then under relatively complex query conditions, the query speed will significantly decrease, resulting in excessively long front-end waiting times and a poor user experience.

[0003] Therefore, improving backend query performance to reduce frontend waiting time has become an urgent problem to be solved. Summary of the Invention

[0004] In view of this, the main objective of this application is to provide a data query method and apparatus, which aims to improve backend query performance and reduce frontend waiting time.

[0005] The first aspect of this application provides a data query method, the method comprising:

[0006] The server receives data query requests from the client. The data query requests include: the query time range and the query condition set.

[0007] The data query request is used to query the database on the server side. The database includes multiple partitions obtained by dividing the database with a time type field as the partition key. Each partition corresponds to a different time range formed by the partition key. Each partition includes multiple data blocks sorted in time order. The database is used to store time series data.

[0008] The server iterates through and queries data blocks in at least one partition corresponding to the time range.

[0009] During the process of traversing data blocks in at least one partition corresponding to the query time range, the server performs data matching on the data block at the current traversal position according to the query condition set, and after completing the data matching, outputs the data matched from the data block at the current traversal position according to the query condition set to the client.

[0010] In some implementations of the first aspect of this application, the data query request also includes: page size;

[0011] Before the server receives a data query request from the client, the method also includes:

[0012] The client establishes a Server Push Event (SSE) connection with the server on a page-by-page basis, so that the server can output the data matched from the data block to the client through the SSE connection;

[0013] After outputting the data matched from the data block at the current traversal position based on the query condition set to the client, the method also includes:

[0014] When the accumulated data output from the server to the client reaches the page size, or when the server has traversed all the data blocks in at least one partition corresponding to the query time range, the client is considered to have completed one round of querying, and the client closes the SSE connection.

[0015] In some implementations of the first aspect of this application, when the accumulated data output from the server to the client reaches the page size, or when the server has traversed all data blocks in at least one partition corresponding to the query time range, the client is considered to have completed a round of querying, and the client closes the SSE connection, including:

[0016] When the accumulated data output from the server to the client reaches the page size, or when the server has traversed all the data blocks in at least one partition corresponding to the query time range, the server sends a query end flag to the client so that the client can close the SSE connection based on the query end flag.

[0017] In some implementations of the first aspect of this application, the method further includes:

[0018] In response to the client re-establishing an SSE connection with the server, the client replaces the minimum value of the query time range with the maximum value of the time corresponding to the data already output to the client, thus obtaining the first replaced query time range; then iterates through the data blocks in at least one partition corresponding to the first replaced query time range.

[0019] or,

[0020] In response to the client re-establishing an SSE connection with the server, the client replaces the maximum value of the query time range with the minimum value of the time corresponding to the data already output to the client to obtain the second-replaced query time range; and iterates through the data blocks in at least one partition corresponding to the second-replaced query time range.

[0021] In some implementations of the first aspect of this application, the server traverses data blocks in at least one partition corresponding to the query time range, including:

[0022] The server takes the partition corresponding to the minimum value of the query time range as the current traversal object and traverses the data blocks in the current traversal object. Then, the server takes the partition corresponding to the maximum value of the partition key of the current traversal object as the next current traversal object and traverses the data blocks in the next current traversal object until the traversal of the data blocks in the partition corresponding to the maximum value of the query time range is completed.

[0023] or,

[0024] The server takes the partition corresponding to the maximum value of the query time range as the current traversal object and traverses the data blocks in the current traversal object. Then, the server takes the partition corresponding to the minimum value of the partition key of the current traversal object as the next current traversal object and traverses the data blocks in the next current traversal object until the traversal of the data blocks in the partition corresponding to the minimum value of the query time range is completed.

[0025] In some implementations of the first aspect of this application, the method further includes:

[0026] When the accumulated data output from the server to the client reaches the page size, or when the server has traversed all the data blocks in at least one partition corresponding to the query time range, the server outputs the number of matched records to the client. The number of matched records is the number of data records that the server has matched from the database.

[0027] A second aspect of this application provides a data query apparatus, the apparatus comprising:

[0028] The query request receiving module is used by the server to receive data query requests from the client. The data query request includes: query time range and query condition set; wherein, the data query request is used to query the server's database, which includes: multiple partitions obtained by dividing the database with time type field as partition key, each partition corresponding to a different time range composed of partition key, each partition including: multiple data blocks sorted in time order, the database is used to store time series data;

[0029] The partition traversal module is used by the server to traverse data blocks in at least one partition corresponding to the query time range; it is also used by the server to perform data matching on the data block at the current traversal position according to the query condition set during the traversal of data blocks in at least one partition corresponding to the query time range, and after the data matching is completed, to output the data matched from the data block at the current traversal position according to the query condition set to the client.

[0030] In some implementations of the second aspect of this application, the data query request further includes: page size; the apparatus further includes:

[0031] The connection establishment module is used to enable the client to establish a Server Push Event (SSE) connection with the server on a page-by-page basis before receiving a data query request from the client, so that the server can output the data matched from the data block to the client through the SSE connection.

[0032] The partition traversal module is also used to consider a round of query completed by the client after outputting the data matched from the data block at the current traversal position according to the query condition set to the client, when the data output from the server to the client reaches the page size, or when the server has traversed the data blocks in at least one partition corresponding to the query time range, and the client closes the SSE connection.

[0033] In some implementations of the second aspect of this application, when the accumulated data output from the server to the client reaches the page size, or when the server has traversed all data blocks in at least one partition corresponding to the query time range, the client is considered to have completed a round of querying, and the client closes the SSE connection, including:

[0034] When the accumulated data output from the server to the client reaches the page size, or when the server has traversed all the data blocks in at least one partition corresponding to the query time range, the server sends a query end flag to the client so that the client can close the SSE connection based on the query end flag.

[0035] In some implementations of the second aspect of this application, the server traverses data blocks in at least one partition corresponding to the query time range, including:

[0036] The server takes the partition corresponding to the minimum value of the query time range as the current traversal object and traverses the data blocks in the current traversal object. Then, the server takes the partition corresponding to the maximum value of the partition key of the current traversal object as the next current traversal object and traverses the data blocks in the next current traversal object until the traversal of the data blocks in the partition corresponding to the maximum value of the query time range is completed.

[0037] or,

[0038] The server takes the partition corresponding to the maximum value of the query time range as the current traversal object and traverses the data blocks in the current traversal object. Then, the server takes the partition corresponding to the minimum value of the partition key of the current traversal object as the next current traversal object and traverses the data blocks in the next current traversal object until the traversal of the data blocks in the partition corresponding to the minimum value of the query time range is completed.

[0039] The technical solution provided in this application has the following beneficial effects:

[0040] First, the server receives a data query request from the client, which includes a query time range and a set of query conditions. The client queries the server's database storing time-series data. This database contains multiple partitions, each partitioned by a time type field as the partition key. Each partition consists of multiple data blocks sorted by time, and each partition corresponds to a different time range defined by the partition key. Then, the client iterates through the data blocks in at least one partition corresponding to the query time range. During this iteration, it matches the data in the currently traversed data block according to the query conditions. After completing the matching process, the client outputs the matched data from the currently traversed data block to the client. This application constrains the retrieval behavior of data blocks to a single data block. Each time data is matched against a data block, the matched data is output to the client, thus avoiding the performance degradation issues caused by any of the following in existing technologies: page data may come from multiple partitions, the data volume of a single partition is too large, or the query conditions are complex. Specifically, on the one hand, limiting the database retrieval behavior to a single data block reduces computer resource overhead; on the other hand, immediately outputting the matched data from a single data block to the client enables continuous response output to the client. Overall, this improves backend query performance and further reduces the waiting time for the client at the front end. Attached Figure Description

[0041] Figure 1 A flowchart illustrating a data query method provided in an embodiment of this application;

[0042] Figure 2 A flowchart illustrating another data query method provided in an embodiment of this application;

[0043] Figure 3 A flowchart illustrating another data query method provided in an embodiment of this application;

[0044] Figure 4 A flowchart illustrating another data query method provided in an embodiment of this application;

[0045] Figure 5 A schematic diagram illustrating the interaction between the server and the client provided in an embodiment of this application;

[0046] Figure 6 This is a schematic diagram of the structure of a data query device provided in an embodiment of this application. Detailed Implementation

[0047] The technical solutions of the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of this application, and not all embodiments. Based on the embodiments of this application, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this application.

[0048] The terms "first," "second," "third," "fourth," etc., used in the specification, claims, and accompanying drawings of this application, are used, if present, to distinguish similar objects and are not necessarily used to describe a specific order or sequence. It should be understood that such data used in this way can be interchanged where appropriate so that the embodiments described herein can be implemented in a sequence other than that illustrated or described herein. Furthermore, the terms "comprising" and "having," and any variations thereof, are intended to cover a non-exclusive inclusion; for example, a process, method, system, product, or apparatus that comprises a series of steps or units is not necessarily limited to those steps or units explicitly listed, but may include other steps or units not explicitly listed or inherent to such processes, methods, products, or apparatus.

[0049] See Figure 1 As shown in the figure, this application provides a data query method aimed at improving backend query performance and reducing frontend waiting time. The method specifically includes the following steps:

[0050] S101: The server receives a data query request from the client. The data query request includes: a query time range and a query condition set. The data query request is used to query the server's database. The database includes: multiple partitions obtained by dividing the database with a time type field as the partition key. Each partition corresponds to a different time range composed of the partition key. Each partition includes: multiple data blocks sorted in chronological order. The database is used to store time-series data.

[0051] In the embodiments of this application, the query time range refers to the time period corresponding to the data that the client at the front end wants to query. For example, querying data that meets the specified conditions within the range of 2024-01-01 00:00:00 to 2024-01-10 23:59:59; by querying the time range, the partition can be further located according to the partition key, reducing the scope of the query and further reducing the query time.

[0052] A query condition set refers to the conditions that the client wants to query for the data it must meet, such as data type, data value, data attribute, etc. Query condition sets can be used to match data blocks in a partition in order to filter data that meets the conditions.

[0053] A time-type field refers to a field stored in the database used to record the time corresponding to data. This application uses the value of the time-type field as the partition key to divide the database into multiple partitions, each corresponding to a time range, such as by month or by day. Taking daily partitioning as an example, assuming the database includes partitions a, b, and c, then partition a could correspond to 2024-01-01 00:00:00 to 2024-01-12 3:59:59, partition b to 2024-01-20 00:00:00 to 2024-01-22 3:59:59, partition d to 2024-01-03 00:00:00 to 2024-01-32 3:59:59, and so on.

[0054] Each partition contains multiple data blocks arranged in chronological order. Each data block stores data of a certain size or within a certain time range within its partition. For example, in the case of partitioning by day, partition A may include data blocks a1, a2, a3, and a4. Data block a1 stores data from 0:00 to 3:00, data block a2 stores data from 3:00 to 6:00, data block a3 stores data from 6:00 to 9:00, and data block a4 stores data from 9:00 to 12:00. Alternatively, the time range of data stored in each data block may not be specified. Instead, the database's own data block management capabilities may be used to allocate the data blocks. For example, the database may set an upper limit on the size of each data block. Whenever the size of a data block exceeds the upper limit, it will automatically split into at least two data blocks.

[0055] S102: The server iterates through and queries data blocks in at least one partition corresponding to the time range.

[0056] In the embodiments of this application, after receiving a data query request from a client, the server can determine which partitions need to be retrieved based on the query time range in the data query request. Then, it sequentially traverses the data blocks in each partition in ascending or descending order, and finally matches data from the data blocks based on the query condition set. For example, assuming the database partitions are divided by day, and the query time range is from 2024-01-01 00:00:00 to 2024-01-32 3:59:59, corresponding to partitions a, b, and c, the server will sequentially traverse partitions a, b, and c, or sequentially traverse partitions c, b, and a.

[0057] Understandably, since the data blocks within a partition are ordered chronologically, and the database stores time-series data, each data block within a partition corresponds to a different time range. Therefore, by querying the time range, a specific data block within a particular partition can be located. For example, in a partitioned database (days), partition A, used to store data for January 1, 2024, might include data blocks a1, a2, a3, and a4. Block a1 stores data from 0:00 to 3:00, block a2 stores data from 3:00 to 6:00, block a3 stores data from 6:00 to 9:00, and block a4 stores data from 9:00 to 12:00. When querying the time range from 2024-01-01 14:00:00 to 2024-01-01 11:00:00, the query can specifically locate data blocks a2, a3, and a4 within partition A, thus avoiding invalid matches on block a1.

[0058] In some implementations of this application, the partitions corresponding to the query time range can be traversed in ascending or descending order by setting on the server or issuing corresponding instructions on the client. Therefore, see... Figure 2 As shown, step S102 may specifically include any one of steps S201 or S202:

[0059] S201: The server takes the partition corresponding to the minimum value of the query time range as the current traversal object and traverses the data blocks in the current traversal object; then, the server takes the partition corresponding to the maximum value of the partition key of the current traversal object as the next current traversal object and traverses the data blocks in the next current traversal object until the traversal of the data blocks in the partition corresponding to the maximum value of the query time range is completed.

[0060] In the embodiments of this application, step S201 involves traversing each partition in ascending order. Specifically, firstly, the partition corresponding to the minimum value of the query time range is used as the current traversal object. For example, if the server learns from the data query request that the client wants to query data from 12:00:00 on 2024-01-01 to 12:00:00 on 2024-01-03, the partition corresponding to the minimum value of the query time range (12:00:00 on 2024-1-1) (the partition storing data from 2024-01-01) is used as the current traversal object, and the data blocks in that partition are traversed. Then, the server takes the partition corresponding to the maximum value of the partition key (2024-01-01 00:00:00) of the current traversal object (the partition storing data for 2024-01-01) as the next traversal object and traverses the data blocks in that partition. After that, the step of determining the next traversal object is executed repeatedly until the traversal of the data blocks in the partition corresponding to the maximum value of the query time range (2024-01-03 12:00:00) is completed (the partition storing data for 2024-01-03).

[0061] It should be noted that the partition key corresponding to each partition is different from the time range corresponding to the data stored in the partition. The partition key is used to divide the partition, not to limit the time range of the data stored in the partition. For example, suppose the partition key corresponding to partition b is 2024-01-01 23:59:59 and 2024-01-02 00:00:00, but partition b only stores data within the time range from 2024-01-02 00:00:00 to 2024-01-02 23:59:59, and does not store data within the time range of 2024-01-01 23:59:59 and 2024-01-02 00:00:00.

[0062] S202: The server takes the partition corresponding to the maximum value of the query time range as the current traversal object and traverses the data blocks in the current traversal object; then, the server takes the partition corresponding to the minimum value of the partition key of the current traversal object as the next current traversal object and traverses the data blocks in the next current traversal object until the traversal of the data blocks in the partition corresponding to the minimum value of the query time range is completed.

[0063] In the embodiments of this application, step S201 involves traversing each partition in descending order. Specifically, firstly, the partition corresponding to the maximum value of the query time range is used as the current traversal object. For example, if the server learns from a data query request that the client wants to query data between 12:00:00 on 2024-01-01 and 12:00:00 on 2024-01-03, the server will select the partition corresponding to the maximum value of the query time range (2024-01-03).

[0064] The server takes the partition corresponding to 12:00:00 (the partition storing data from 2024-01-03) as the current traversal object and iterates through the data blocks in that partition. Then, the server takes the partition corresponding to the minimum value of the partition key of the current traversal object (the partition storing data from 2024-01-03) (2024-01-02 23:59:59) (the partition storing data from 2024-01-02) as the next traversal object and iterates through the data blocks in that partition. This process continues until the data blocks in the partition corresponding to the minimum value of the query time range (2024-01-01 12:00:00) (the partition storing data from 2024-01-01) have been traversed.

[0065] It should be noted that S201 and S202 are optional steps to be executed, and the specific choice of execution can depend on the server's preset requirements or the client's specified requirements.

[0066] S103: During the process of traversing the data blocks in at least one partition corresponding to the query time range, the server performs data matching on the data block at the current traversal position according to the query condition set, and after completing the data matching, outputs the data matched from the data block at the current traversal position according to the query condition set to the client.

[0067] In the embodiments of this application, the server traverses the data blocks within a partition in chronological order and matches data from the data block at the current traversal position based on the query condition set. Specifically, after the server completes matching a data block and finds data within it, it outputs the matched data to the client, thus providing timely feedback of the data retrieved from the database to the client. It should be noted that the data blocks in each partition are arranged in chronological order. During the traversal of the partitions corresponding to the query time range, the data blocks also follow the traversal order of the partitions. For example, when the server traverses the partitions corresponding to the query time range in ascending order, if the data blocks within the partition are sorted in ascending chronological order, the server traverses the data blocks within the partition in ascending order.

[0068] In some implementations of this application, the inventors considered that the system resource consumption of the database is positively correlated with the amount of data queried; that is, the larger the amount of data queried, the more system resources are consumed. Therefore, if too many or too large data partitions are queried at once, it will excessively consume system resources, causing memory exhaustion in other programs and severely affecting their normal operation. Therefore, this application can further set an upper limit on the size of each data block within the partition through the database, for example, the memory occupied by each data block cannot exceed 50G. Since this application limits the database retrieval behavior to a single data block, and outputs the matched data after each data block is matched, it can ensure that there is no excessive memory overhead during each data query.

[0069] exist Figure 1 In the illustrated process, firstly, the server receives a data query request from the client, which includes a query time range and a set of query conditions. The client queries the server's database used to store time-series data. This database contains multiple partitions, each partitioned by a time type field as the partition key. Each partition consists of multiple data blocks sorted by time, and each partition corresponds to a different time range defined by the partition key. Then, the client iterates through the data blocks in at least one partition corresponding to the query time range, matching the data at the current traversal position according to the query conditions. After completing the matching, the client outputs the data matched from the current traversal position to the client. This application constrains the retrieval behavior of data blocks to a single data block. Each time data is matched against a data block, the matched data is output to the client, thus avoiding the performance degradation issues caused by any of the following in existing technologies: page data may come from multiple partitions, the data volume of a single partition is too large, or the query conditions are complex. Specifically, on the one hand, limiting the database retrieval behavior to a single data block reduces computer resource overhead; on the other hand, immediately outputting the matched data from a single data block to the client enables continuous response output to the client. Overall, this improves the query performance of the backend and further reduces the waiting time for the client at the frontend.

[0070] See Figure 3 As shown in the illustration, this application embodiment also provides a data query method, in which the client and server exchange data by establishing an SSE long connection, and the data query request also includes the page size. Specifically, it includes the following steps:

[0071] S301: The client establishes a Server Push Event (SSE) connection with the server on a page-by-page basis, so that the server can output the data matched from the data block to the client through the SSE connection.

[0072] Server-Sent Events (SSE) is a technology where the server proactively pushes messages to the client in real time based on the Hypertext Transfer Protocol (HTTP). In the embodiments of this application, establishing an SSE connection between the server and client on a page-by-page basis means that an SSE connection is established for each page, used by the server to push data to the client. It should be noted that the establishment and closure of the SSE connection are both initiated by the client; the server only uses the SSE connection to output data to the client. For example, the client displays data obtained from the server in paginated form. Each time, a connection is established with the server to retrieve one page of data. After retrieving one page, the SSE connection is closed, and then another SSE connection is established to wait for the next page of data, until the client has retrieved all the data within the query time range from the server's database.

[0073] S302: The server receives a data query request from the client. The data query request includes: query time range, query condition set, and page size. The data query request is used to query the server's database, which includes: multiple partitions obtained by dividing the database with a time type field as the partition key. Each partition corresponds to a different time range formed by the partition key. Each partition includes: multiple data blocks sorted in chronological order. The database is used to store time-series data.

[0074] In the embodiments of this application, the page size refers to the number of data items that the client specifies to be obtained from the server in each round. For example, if the client displays the data obtained from the server in pages and the number of data items displayed on each page is specified to be 100, then the page size is 100.

[0075] S303: The server iterates through and queries data blocks in at least one partition corresponding to the time range.

[0076] S304: During the process of traversing the data blocks in at least one partition corresponding to the query time range, the server performs data matching on the data block at the current traversal position according to the query condition set, and after completing the data matching, outputs the data matched from the data block at the current traversal position according to the query condition set to the client; when the data output by the server to the client accumulates to the page size, or when the server has traversed all the data blocks in at least one partition corresponding to the query time range, the client is considered to have completed one round of query, and the client closes the SSE connection.

[0077] In the embodiments of this application, the server will traverse the data blocks in each partition corresponding to the query time range until the SSE connection is closed, which is considered as completing one round of data matching. The condition for closing the SSE connection can be that the accumulated data output by the server to the client through the SSE connection reaches the page size, that is, the number of data items sent to the client is greater than or equal to the number of data items specified by the page size; or it can be that the server has completed traversing each data block in the partition corresponding to the query time range, which means that the server can no longer match data from the database that meets the query time range. In some implementations, the server will send a query end flag to the client. This query end flag indicates the end of this round of data query, thereby causing the client to close the SSE connection based on the query end flag and prepare to re-establish the SSE connection with the server for the next round of data query, or the next page of data query.

[0078] When displaying data received from the client in paginated format, the client obtains the amount of data required to display one page. The data obtained by the client in the first round is the data displayed on the homepage. It should be noted that if the amount of data obtained in this round of time query exceeds the page size, the client will display the excess data on the next page. For example, if the page size is 100, and the server outputs 110 data entries to the client in the first round of query, the client will display 100 data entries on the homepage and the extra 10 data entries on the second page. In some implementations of this application, after each round of query, or after the client receives enough data for one page of display, it will re-establish an SSE connection with the server until the client receives a certain amount of data from the server, or the server completes data matching within all partitions corresponding to the query time range based on the query condition set. It should be noted that stopping the client from re-establishing the SSE connection with the server using other methods does not affect the implementation of this application.

[0079] In some implementations of this application, to further ensure that the amount of data retrieved by the client in each round of querying exactly meets the data volume required for displaying one page, a data retrieval limit is added on the server side to limit the data output by the server to the client in each round to exactly meet the page size. For example, if the page size is 100, the initial data retrieval limit is 100. If the first data output to the client is 20 data entries, the limit for matching data in the next data block is 80 data entries; if the second data output to the client is 10 data entries, the limit for matching data in the next data block is 70 data entries; and so on.

[0080] In some implementations of the embodiments of this application, the following steps may be further performed: when the accumulated data output to the client reaches the page size, or when the data blocks in at least one partition corresponding to the query time range have been traversed, the number of matched records is output to the client, where the number of matched records is the number of data records that the server has matched from the database.

[0081] In the embodiments of this application, for each SSE connection initiated by the client, the server uses a thread to serve the client and records the number of matched records in real time through variables. Preferably, the number of matched records is only output after the data output by the server has accumulated to the page size. Thus, when the client displays the matched data in pages, priority is given to ensuring the response time of the page data.

[0082] exist Figure 3 In the illustrated process, firstly, the server and client establish an SSE connection on a page-by-page basis. The server receives data query requests from the client, which include at least the query time range, query condition set, and page size. Then, the server iterates through the data blocks in at least one partition corresponding to the query time range. During this iteration, the server matches data in the currently traversed data block according to the query condition set. After completing the matching, the server outputs the matched data from the currently traversed data block to the client. Whenever the accumulated data output to the client reaches the page size during the iteration of the data blocks in at least one partition corresponding to the query time range, the client is considered to have completed one round of querying, and the SSE connection is closed. This completes one page query for the client.

[0083] See Figure 4 This application embodiment also provides a data query method, which specifically includes the following steps:

[0084] S401: The client establishes a Server Push Event (SSE) connection with the server on a page-by-page basis, so that the server can output the data matched from the data block to the client through the SSE connection.

[0085] S402: The server receives a data query request from the client. The data query request includes: query time range, query condition set, and page size. The data query request is used to query the server's database, which includes: multiple partitions obtained by dividing the database with a time type field as the partition key. Each partition corresponds to a different time range formed by the partition key. Each partition includes: multiple data blocks sorted in chronological order. The database is used to store time-series data.

[0086] S403: The server iterates through and queries data blocks in at least one partition corresponding to the time range.

[0087] S404: During the process of traversing data blocks in at least one partition corresponding to the query time range, the server performs data matching on the data block at the current traversal position according to the query condition set, and after completing the data matching, outputs the data matched from the data block at the current traversal position according to the query condition set to the client; when the data output by the server to the client accumulates to the page size, or when the server has traversed all data blocks in at least one partition corresponding to the query time range, the client is considered to have completed one round of query, and the client closes the SSE connection.

[0088] S405: In response to the client re-establishing the SSE connection with the server, the client replaces the minimum value of the query time range with the maximum value of the time corresponding to the data already output to the client to obtain the first replaced query time range; iterates through the data blocks in at least one partition corresponding to the first replaced query time range.

[0089] In the embodiments of this application, after each round of querying by the client, the server will re-establish an SSE connection with the client until the server has matched all data within the query time range. At this time, the server will again execute the step of traversing the corresponding partitions within the query time range. To further improve query speed, this application further adopts a method of pruning the query time range to reduce the amount of data required for retrieval. Thus, for the database, each round or each page query is equivalent to responding to the server's homepage query. Specifically, when the server re-establishes an SSE connection with the client, the server will determine the maximum value of the corresponding time of the time-series data already transmitted to the client and replace it with the known minimum value of the query time range, thereby shortening the query time range. For example, if the query time range in a data query request is from 2024-01-01 00:00:00 to 2024-01-02 15:00:00, and the time range of the data transmitted by the server to the client via the SSE connection is from 2024-01-01 00:00:00 to 2024-01-01 15:00:00, then 2024-01-01 100:00:00 will be replaced with 2024-01-01 15:00:00, resulting in a new query time range of 2024-01-01 15:00:00 to 2024-01-02 15:00:00, which is the first replaced query time range. Then, the server iterates through the data blocks in the partitions corresponding to the first replaced query time range. Subsequently, when the client and server re-establish the SSE connection, step S406 is executed again to obtain the new first replaced query time range, until the data blocks in the partition corresponding to the initial query time range are traversed.

[0090] S406: In response to the client re-establishing the SSE connection with the server, the client replaces the maximum value of the query time range with the minimum value of the time corresponding to the data already output to the client to obtain the second-replaced query time range; iterates through the data blocks in at least one partition corresponding to the second-replaced query time range.

[0091] In the embodiments of this application, the specific implementation of S407 is similar to that of S406. The difference is that S407 replaces the maximum value of the query time range with the minimum value of the time corresponding to the time series data that has been output to the client. For example, if the query time range in a data query request is from 2024-01-01 00:00:00 to 2024-01-02 15:00:00, and the time-series data transmitted by the server to the client via SSE connection corresponds to the time interval from 2024-01-02 15:00:00 to 2024-01-02 22:00:00, then 2024-01-02 15:00:00 will be replaced with 2024-01-02 15:00:00, resulting in a new query time range of 2024-01-01 00:00:00 to 2024-01-02 12:00:00, which is the second replaced query time range. Then, the server iterates through the data blocks in the partitions corresponding to the second replaced query time range. Subsequently, when the client and server re-establish the SSE connection, step S406 is executed again to obtain the new first replaced query time range, until the data blocks in the partition corresponding to the initial query time range are traversed.

[0092] It should be noted that steps S405 and S406 are selectively executed, depending on the partition traversal order. If the partitions are traversed in ascending order, S405 is executed; if they are traversed in descending order, S406 is executed. Furthermore, the step of modifying the query time range can also be executed by the client. That is, the client generates the first / second replaced query time range, and then sends a data query request to the server again. The server then traverses the data based on the first / second replaced query time range, thus ensuring that each query on the server is equivalent to a homepage query.

[0093] exist Figure 4 In the process shown, the server and client establish an SSE connection on a page-by-page basis. In response to re-establishing an SSE connection with the client, the service replaces the minimum / maximum value of the query time range with the maximum / minimum value of the time corresponding to the data already output to the client, so as to obtain the first / second replaced query time range. The service then traverses the data blocks in at least one partition corresponding to the first / second replaced query time range, thereby realizing the ascending / descending order query of each partition in the database.

[0094] See Figure 5 As shown, the embodiments of this application will be described below in conjunction with a practical application scenario. In the practical application scenario, a browser is used as the client, ClickHouse is used as the server-side database, and SpringMVC + ClickHouse or other reactive programming frameworks + ClickHouse are used as the basic technical architecture; the data is stored as time-series data, that is, the data is partitioned and stored according to a certain time field or an incremental field. ClickHouse has a partition table, which indicates that the database is divided into multiple partitions by day, and currently, data from three partitions is stored. These are partition A with three data blocks, used to store data from 2023-01-01; partition B with four data blocks, used to store data from 2023-01-02; and partition C with two data blocks, used to store data from 2023-01-03.

[0095] The current browser query requires a time range from January 1, 2023 to January 3, 2023, with a page size of 100 (100 records per page), ordered chronologically. Based on this request, the browser establishes an SSE connection and initiates the first page query. The server queries each partition and data block sequentially, finding matching data in 2 partitions and 4 parts, and populating page1. After scanning each part, the data is immediately output to the browser in real-time via the SSE connection, with the following responses: Partition A: Data block part_1 (limited to 100 records, 30 records found); Partition A: Data block part_2 (limited to 70 records, 20 records found); Partition A: Data block part_3 (limited to 50 records, 10 records found); Partition B: Data block part_1 (limited to 40 records, 40 records found).

[0096] When the server finishes scanning part_1 of partition C, the client's first page is filled. At this time, the query ends and the current page ends at 2023-01-02 01:00:00 is returned.

[0097] After receiving the PageEnd query completion indicator, the browser closes the SSE connection. When the browser initiates the next page query, it similarly establishes an SSE connection first, specifying the actual query range as 2023-01-02 01:00:00 to 2023-01-03. The query matching results for the second page are as follows: Partition B: Data block part_4 with a data limit of 100 records, 20 records were matched; Partition C: Data block part_1 with a data limit of 80 records, 15 records were matched. After scanning all data within the query time range, no other data was matched, so the query for the second page also ended, returning the PageEnd query completion indicator, and the browser closed the SSE connection.

[0098] In this way, the browser retrieved 135 data entries from the server's database between January 1, 2023 and January 3, 2023, with 100 entries on the first page and 35 entries on the second page.

[0099] See Figure 6 As shown in the figure, this application embodiment provides a data query device, which includes:

[0100] The query request receiving module 601 is used for the server to receive data query requests from the client. The data query request includes: a query time range and a query condition set. The data query request is used to query the server's database, which includes: multiple partitions obtained by dividing the database with a time type field as the partition key. Each partition corresponds to a different time range composed of the partition key. Each partition includes: multiple data blocks sorted in chronological order. The database is used to store time-series data.

[0101] The partition traversal module 602 is used for the server to traverse data blocks in at least one partition corresponding to the query time range; it is also used for the server to perform data matching on the data block at the current traversal position according to the query condition set during the process of traversing data blocks in at least one partition corresponding to the query time range, and after completing the data matching, to output the data matched from the data block at the current traversal position according to the query condition set to the client.

[0102] In some implementations of this application's embodiments, the data query request further includes: page size; the device further includes:

[0103] The connection establishment module is used to enable the client to establish a Server Push Event (SSE) connection with the server on a page-by-page basis before receiving a data query request from the client, so that the server can output the data matched from the data block to the client through the SSE connection.

[0104] The partition traversal module is also used to consider a round of query completed by the client after outputting the data matched from the data block at the current traversal position according to the query condition set to the client, when the data output from the server to the client reaches the page size, or when the server has traversed the data blocks in at least one partition corresponding to the query time range, and the client closes the SSE connection.

[0105] In some implementations of this application, when the accumulated data output from the server to the client reaches the page size, or when the server has traversed all data blocks in at least one partition corresponding to the query time range, the client is considered to have completed one round of querying, and the client closes the SSE connection, including:

[0106] When the accumulated data output from the server to the client reaches the page size, or when the server has traversed all the data blocks in at least one partition corresponding to the query time range, the server sends a query end flag to the client so that the client can close the SSE connection based on the query end flag.

[0107] In some implementations of the embodiments of this application, the data query device is further configured to perform the following steps:

[0108] In response to the client re-establishing the SSE connection with the server, the client replaces the minimum value of the query time range with the maximum value of the time corresponding to the data already output to the client to obtain a first replaced query time range; and iterates through the data blocks in at least one partition corresponding to the first replaced query time range.

[0109] or,

[0110] In response to the client re-establishing the SSE connection with the server, the client replaces the maximum value of the query time range with the minimum value of the time corresponding to the data already output to the client to obtain a second replaced query time range; and iterates through the data blocks in at least one of the partitions corresponding to the second replaced query time range.

[0111] In some implementations of this application, the server traverses data blocks in at least one partition corresponding to the query time range, including:

[0112] The server takes the partition corresponding to the minimum value of the query time range as the current traversal object and traverses the data blocks in the current traversal object. Then, the server takes the partition corresponding to the maximum value of the partition key of the current traversal object as the next current traversal object and traverses the data blocks in the next current traversal object until the traversal of the data blocks in the partition corresponding to the maximum value of the query time range is completed.

[0113] or,

[0114] The server takes the partition corresponding to the maximum value of the query time range as the current traversal object and traverses the data blocks in the current traversal object. Then, the server takes the partition corresponding to the minimum value of the partition key of the current traversal object as the next current traversal object and traverses the data blocks in the next current traversal object until the traversal of the data blocks in the partition corresponding to the minimum value of the query time range is completed.

[0115] In some implementations of the embodiments of this application, the data query device is further configured to perform the following steps:

[0116] When the data output by the server to the client reaches the page size, or when the server has traversed all the data blocks in at least one of the partitions corresponding to the query time range, the server outputs the number of matched records to the client, where the number of matched records is the number of data records that the server has matched from the database.

[0117] Finally, it should be noted that in the embodiments of this application, relational terms such as "first" and "second" are used only to distinguish one entity or operation from another entity or operation, and do not necessarily require or imply any such actual relationship or order between these entities or operations. Furthermore, the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such a process, method, article, or apparatus. Without further limitations, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or apparatus that includes said element.

[0118] The above description of the disclosed embodiments enables those skilled in the art to make or use the invention. Various modifications to these embodiments will be readily apparent to those skilled in the art, and the general principles defined herein may be implemented in other embodiments without departing from the spirit or scope of the invention. Therefore, the invention is not to be limited to the embodiments shown herein, but is to be accorded the widest scope consistent with the principles and novel features disclosed herein.

Claims

1. A data query method, characterized in that, The method includes: The server receives a data query request from the client, the data query request including: a query time range and a query condition set; The data query request is used to query the database of the server. The database includes multiple partitions obtained by dividing the database with a time type field as the partition key. Each partition corresponds to a different time range formed by the partition key. Each partition includes multiple data blocks sorted in time order. The database is used to store time-series data. The server iterates through at least one data block in the partition corresponding to the query time range; During the process of traversing data blocks in at least one of the partitions corresponding to the query time range, the server performs data matching on the data block at the current traversal position according to the query condition set, and after completing the data matching, outputs the data matched from the data block at the current traversal position according to the query condition set to the client; The data query request also includes: page size; Before the server receives the data query request from the client, the method further includes: The client establishes a Server Push Event (SSE) connection with the server on a page-by-page basis, so that the server can output the data matched from the data block to the client through the SSE connection; After outputting the data matched from the data block at the current traversal position according to the query condition set to the client, the method further includes: When the data output from the server to the client reaches the page size, or when the server has traversed all the data blocks in at least one of the partitions corresponding to the query time range, the client is considered to have completed a round of querying, and the client closes the SSE connection.

2. The method according to claim 1, characterized in that, When the accumulated data output from the server to the client reaches the page size, or when the server has traversed all data blocks in at least one of the partitions corresponding to the query time range, the client is considered to have completed a round of querying, and the client closes the SSE connection, including: When the data output from the server to the client accumulates to the page size, or when the server has traversed all the data blocks in at least one of the partitions corresponding to the query time range, the server sends a query end marker to the client so that the client closes the SSE connection based on the query end marker.

3. The method according to claim 1, characterized in that, The method further includes: In response to the client re-establishing the SSE connection with the server, the client replaces the minimum value of the query time range with the maximum value of the time corresponding to the data already output to the client to obtain a first replaced query time range; and iterates through the data blocks in at least one partition corresponding to the first replaced query time range. or, In response to the client re-establishing the SSE connection with the server, the client replaces the maximum value of the query time range with the minimum value of the time corresponding to the data already output to the client to obtain a second replaced query time range; and iterates through the data blocks in at least one of the partitions corresponding to the second replaced query time range.

4. The method according to claim 1, characterized in that, The server traverses at least one data block in the partition corresponding to the query time range, including: The server takes the partition corresponding to the minimum value of the query time range as the current traversal object and traverses the data blocks in the current traversal object; then, the server takes the partition corresponding to the maximum value of the partition key of the current traversal object as the next current traversal object and traverses the data blocks in the next current traversal object until the traversal of the data blocks in the partition corresponding to the maximum value of the query time range is completed. or, The server takes the partition corresponding to the maximum value of the query time range as the current traversal object and traverses the data blocks in the current traversal object. Then, the server takes the partition corresponding to the minimum value of the partition key of the current traversal object as the next current traversal object and traverses the data blocks in the next current traversal object until the traversal of the data blocks in the partition corresponding to the minimum value of the query time range is completed.

5. The method according to claim 1, characterized in that, The method further includes: When the data output by the server to the client reaches the page size, or when the server has traversed all the data blocks in at least one of the partitions corresponding to the query time range, the server outputs the number of matched records to the client, where the number of matched records is the number of data records that the server has matched from the database.

6. A data query device, characterized in that, The device includes: A query request receiving module is used for the server to receive data query requests from the client. The data query request includes: a query time range and a query condition set; wherein, the data query request is used to query the server's database, the database includes: multiple partitions obtained by dividing the database with a time type field as the partition key, each partition corresponding to a different time range formed by the partition key, each partition including: multiple data blocks sorted in chronological order, the database being used to store time-series data; The partition traversal module is used for the server to traverse at least one data block in the partition corresponding to the query time range; it is also used for the server to perform data matching on the data block at the current traversal position according to the query condition set during the traversal of the data block at the at least one partition corresponding to the query time range, and after the data matching is completed, to output the data matched from the data block at the current traversal position according to the query condition set to the client. The data query request further includes: page size; the device further includes: A connection establishment module is used to enable the client to establish a Server Push Event (SSE) connection with the server on a page-by-page basis before receiving the data query request from the client, so that the server can output the data matched from the data block to the client through the SSE connection; The partition traversal module is further configured to, after outputting the data matched from the data block at the current traversal position according to the query condition set to the client, consider the client to have completed a round of query when the data output from the server to the client reaches the page size, or when the server has traversed at least one data block in the partition corresponding to the query time range, and the client closes the SSE connection.

7. The apparatus according to claim 6, characterized in that, When the accumulated data output from the server to the client reaches the page size, or when the server has traversed all data blocks in at least one of the partitions corresponding to the query time range, the client is considered to have completed a round of querying, and the client closes the SSE connection, including: When the data output from the server to the client accumulates to the page size, or when the server has traversed all the data blocks in at least one of the partitions corresponding to the query time range, the server sends a query end marker to the client so that the client closes the SSE connection based on the query end marker.

8. The apparatus according to claim 6, characterized in that, The server traverses at least one data block in the partition corresponding to the query time range, including: The server takes the partition corresponding to the minimum value of the query time range as the current traversal object and traverses the data blocks in the current traversal object; then, the server takes the partition corresponding to the maximum value of the partition key of the current traversal object as the next current traversal object and traverses the data blocks in the next current traversal object until the traversal of the data blocks in the partition corresponding to the maximum value of the query time range is completed. or, The server takes the partition corresponding to the maximum value of the query time range as the current traversal object and traverses the data blocks in the current traversal object. Then, the server takes the partition corresponding to the minimum value of the partition key of the current traversal object as the next current traversal object and traverses the data blocks in the next current traversal object until the traversal of the data blocks in the partition corresponding to the minimum value of the query time range is completed.

Images