Data query method and related device

Abstract

The description provides a data query method and a related device. The method comprises: analyzing a historical query statement for a target data table to obtain at least one data dimension comprised in a query condition in the historical query statement; determining a target data dimension from among the at least one data dimension, and performing partition processing on the target data table on the basis of the target data dimension so as to divide the target data table into a plurality of data partitions; and obtaining a target query statement to be executed, and executing the target query statement to perform query processing in the plurality of data partitions.

Description

A data query method and related equipment Technical Field

[0001] One or more embodiments of this specification relate to the field of data query technology, and more particularly, to a data query method and related devices. Background Art

[0002] When querying large-scale data tables containing large amounts of data, in order to improve query performance, it is often necessary to partition the data tables according to certain data dimensions (such as user, region, or time, etc.) so that large amounts of data can be stored in multiple partitions. This allows for fast and efficient query processing in each partition.

[0003] However, different users or the same user at different times may have different data query requirements. If the data dimension targeted by subsequent users during the query is different from the partition dimension of the data table (for example, the user wants to query data about user Zhang San, but the data table is partitioned by region), the data table needs to be repartitioned according to the new dimension, resulting in a large amount of data being moved. This will not only take a long time, but also put great pressure on the disk, thereby seriously affecting query performance.

[0004] Summary of the Invention

[0005] In view of this, one or more embodiments of this specification provide a data query method and related devices.

[0006] In a first aspect, the present specification provides a data query method, comprising: parsing historical query statements for a target data table to obtain at least one data dimension contained in the query conditions in the historical query statements; determining a target data dimension from the at least one data dimension, and partitioning the target data table based on the target data dimension to divide the target data table into multiple data partitions; obtaining a target query statement to be executed, and executing the target query statement to perform query processing in the multiple data partitions respectively.

[0007] In the second aspect, the present specification provides a data query device, comprising: a parsing unit, used to parse historical query statements for a target data table to obtain at least one data dimension contained in the query conditions in the historical query statements; a partitioning processing unit, used to determine a target data dimension from the at least one data dimension, and perform partitioning processing on the target data table based on the target data dimension to divide the target data table into multiple data partitions; a data query unit, used to obtain a target query statement to be executed, and execute the target query statement to perform query processing separately in the multiple data partitions.

[0008] Accordingly, this specification also provides a computer device, comprising: a memory and a processor; the memory stores a computer program that can be executed by the processor; when the processor runs the computer program, it executes the data query method described in the first aspect above.

[0009] Accordingly, this specification also provides a computer-readable storage medium on which a computer program is stored. When the computer program is executed by a processor, the data query method as described in the first aspect above is executed.

[0010] In summary, the present application can obtain historical query statements for the target data table and parse the historical query statements to obtain at least one data dimension contained in the historical query statements, and then determine the target data dimension from the at least one data dimension. Furthermore, the present application can partition the target data table based on the target data dimension to divide the target data table into multiple data partitions, each of which stores part of the data in the target data table. Subsequently, after receiving the target query statement to be executed, since the present application has already partitioned the target data table based on the user's historical query requirements in advance, it can directly perform query processing in the multiple data partitions obtained by the division, without the need to repartition the data table, which greatly improves the data query performance. BRIEF DESCRIPTION OF THE DRAWINGS

[0011] FIG1 is a schematic diagram of a system architecture provided by an exemplary embodiment;

[0012] FIG2 is a flow chart of a data query method provided by an exemplary embodiment;

[0013] FIG3 is a schematic structural diagram of a data query device provided by an exemplary embodiment;

[0014] FIG4 is a schematic structural diagram of a computer device provided by an exemplary embodiment. DETAILED DESCRIPTION

[0015] Exemplary embodiments will be described in detail herein, with examples illustrated in the accompanying drawings. In the following description, when referring to the drawings, identical numerals in different figures represent identical or similar elements, unless otherwise indicated. The implementations described in the following exemplary embodiments are not intended to represent all implementations consistent with one or more embodiments of this specification. Rather, they are merely examples of apparatuses and methods consistent with certain aspects of one or more embodiments of this specification, as detailed in the appended claims.

[0016] It should be noted that in other embodiments, the steps of the corresponding method are not necessarily performed in the order shown and described in this specification. In some other embodiments, the method may include more or fewer steps than those described in this specification. In addition, a single step described in this specification may be broken down into multiple steps for description in other embodiments, and multiple steps described in this specification may be combined into a single step for description in other embodiments.

[0017] It should be noted that the “plurality” mentioned in this application refers to two or more.

[0018] In addition, the user information (including but not limited to user device information, user personal information, etc.) and data (including but not limited to data used for analysis, stored data, displayed data, etc.) involved in this application are all information and data authorized by the user or fully authorized by all parties, and the collection, use and processing of relevant data must comply with the relevant laws, regulations and standards of relevant countries and regions, and provide corresponding operation entrances for users to choose to authorize or refuse.

[0019] First, some of the terms in this specification are explained to facilitate understanding by those skilled in the art.

[0020] (1) Partitioning refers to dividing a data table into multiple data partitions. Each data partition can store part of the data in the data table, but logically it is still the original complete data table. In other words, partitioning does not generate a new data table, but only distributes the data in the data table to different storage spaces. In this way, for large-scale data tables containing massive amounts of data, partitioning can prevent the massive data from being stored in one place. Subsequent queries on the data table can also be processed in multiple data partitions, thereby improving the efficiency of data queries.

[0021] Furthermore, when partitioning data, the data table is usually divided according to a certain data dimension in the data table. This data dimension is the partition key. It is understandable that different data dimensions generally correspond to different data columns (i.e., different keys) in the data table.

[0022] For example, the data dimension may be city. After partitioning the data table according to the city dimension, one data partition may store only data related to city A, another data partition may store only data related to city B, and so on. For example, the data dimension may be user. After partitioning the data table according to the user dimension, one data partition may store only data related to user A, another data partition may store only data related to user B, and so on. Examples are not given here.

[0023] As mentioned above, different users or the same user at different times may have different data query requirements. If the data dimension targeted by subsequent users in their queries is different from the partition dimension of the data table, the data table needs to be repartitioned according to the new dimension.

[0024] For example, in existing data queries, it is often necessary to use a join-type query statement to query multiple data tables. Among them, a join-type query statement usually contains a join key. If the data table targeted by the query statement is not partitioned, or the data table is partitioned according to other data dimensions different from the join key (for example, the join key is the user, but the data table is partitioned according to the region dimension), then the join key needs to be used as the partition key to repartition the data table so that the query can be executed in each partition related to the join key to ensure query performance. However, the above-mentioned repartitioning will involve shuffling the data, that is, the storage location of a large amount of data in the data table will be moved, which will not only take a long time, but also put great pressure on the disk, thereby seriously affecting the query performance.

[0025] Based on this, this manual provides a technical solution that can actively collect users' past historical query records, analyze users' query needs, and effectively partition the data table in advance based on this, so that there is no need to repartition when executing queries later, greatly improving data query performance.

[0026] During implementation, the present application can obtain and parse multiple historical query statements for the target data table to obtain at least one data dimension included in the query conditions in the multiple historical query statements. Furthermore, the present application can determine the target data dimension from the at least one data dimension and partition the target data table based on the target data dimension to divide the target data table into multiple data partitions. Then, the present application can obtain the target query statement to be executed and execute the target query statement to perform query processing in the multiple data partitions obtained by the division.

[0027] In the above technical solution, the present application can obtain historical query statements for the target data table and parse the historical query statements to obtain at least one data dimension contained in the historical query statements, and then determine the target data dimension from the at least one data dimension. Furthermore, the present application can partition the target data table based on the target data dimension to divide the target data table into multiple data partitions, each of which stores part of the data in the target data table. Subsequently, after receiving the target query statement to be executed, since the present application has already partitioned the target data table based on the user's historical query needs in advance, it can directly perform query processing in the multiple data partitions obtained by the division, without the need to repartition the data table, which greatly improves the data query performance.

[0028] Please refer to Figure 1, which is a schematic diagram of a system architecture provided by an exemplary embodiment. One or more embodiments provided in this specification can be specifically implemented in the system architecture shown in Figure 1 or a similar system architecture. As shown in Figure 1, the system may include a storage system 100 and a data query system 200, wherein the storage system 100 may include multiple storage devices, for example, storage device 100a, storage device 100b, storage device 100c, etc. In an illustrated embodiment, the data query system 200 can establish a communication connection with the storage system 100 in any possible manner. For example, the data query system 200 can establish a communication connection with the storage system 100 via a wireless network, which is not specifically limited in this specification.

[0029] In one illustrated embodiment, storage system 100 may be a database system that may include multiple data tables. Storage devices 100a, 100b, and 100c in storage system 100 may be used to store data from these multiple data tables. Accordingly, a user may query data from these multiple data tables using data query system 200. For example, a query may be performed on a single data table, or a query may be performed on multiple data tables using a join-type SQL statement, etc. This specification does not specifically limit this.

[0030] In an illustrated embodiment, the data query system 200 may obtain historical query statements for a target data table and parse the multiple historical query statements to obtain data dimensions included in query conditions in the historical query statements.

[0031] It should be noted that this specification does not specifically limit the specific type of the above-mentioned historical query statement. In one embodiment shown, the historical query statement can be a group by type SQL statement, a join type SQL statement, an agg type SQL statement, etc., and this specification does not specifically limit this.

[0032] For example, the historical query statement may be "select xxx group by user", where "user" is the data dimension included in the query condition, that is, the group by key.

[0033] For example, the historical query statement may be "select xxx from a join b on a.user=b.user", where a and b may be the two data tables targeted by this joint query, and "user" is the data dimension included in the query condition, i.e., the join key.

[0034] It should be noted that this specification does not specifically limit how to parse SQL statements to obtain data dimensions. In one illustrated embodiment, the present application can use the Catalyst framework to recursively traverse the SQL syntax tree to obtain the keys contained in various SQL statements. Catalyst is a framework specifically used to parse SQL within the large-scale data computing engine Spark, and will not be described in detail here. In one illustrated embodiment, the present application can also use any other possible method to parse SQL statements to obtain the data dimensions contained therein.

[0035] For example, for the more complex nested query statement of the agg type shown below, where a join is nested under agg, the join contains two subqueries, and the first subquery also contains an agg, this application can first analyze the function tableName->alias, and then use alias->column to find which data column (i.e., data dimension) in which data table the query statement is targeting, etc. This specification does not make specific limitations on this.

[0036] *Agg

[0037] *--------Join

[0038] *------------SubQueryAlias1

[0039] *-------------Agg

[0040] *----------------Project

[0041] *-------------------Filter

[0042] *-----------------------Relation

[0043] *--------------SubQueryAlias2

[0044] *-------------------Project

[0045] *---------------------Filter

[0046] *-----------------------Relation

[0047] Based on multiple historical query statements of different query types (such as group by type, join type, agg type, distinct type, etc.), the data dimensions obtained by parsing may include group by key, join key, agg key, distinct key, etc., which are not specifically limited in this specification.

[0048] Furthermore, at least part of the at least one data dimension obtained by parsing the historical query statement can be used as a partition key for subsequent data partitioning.

[0049] Furthermore, in one illustrated embodiment, the data query system 200 can determine a target data dimension (i.e., a partition key) from the at least one acquired data dimension, and partition the target data table based on the target data dimension to divide the target data table into multiple data partitions. Each data partition obtained by the partitioning corresponds to a storage space, and each data partition stores a portion of the data in the target data table.

[0050] For example, before partitioning, all data in the target data table can be stored in storage device 1000a. If the target data dimension is time, then after partitioning the target data table according to time, the target data table can be divided into multiple data partitions. These multiple data partitions can respectively correspond to storage device 100a, storage device 100b, and storage device 100c shown in Figure 1. Specifically, storage device 100a can store multiple data items in the target data table with a date of 2023, storage device 100b can store multiple data items in the target data table with a date of 2022, storage device 100c can store multiple data items in the target data table with a date of 2021, and so on. This specification does not specifically limit this.

[0051] For example, if the target data dimension is city, then after partitioning the target data table by city, the target data table can be divided into multiple data partitions. These multiple data partitions can respectively correspond to storage device 100a, storage device 100b, and storage device 100c shown in FIG1 . Specifically, storage device 100a can store multiple data items related to city A in the target data table, storage device 100b can store multiple data items related to city B in the target data table, storage device 100c can store multiple data items related to city C in the target data table, and so on. This specification does not specifically limit this.

[0052] In one illustrated embodiment, the target data table may be an unpartitioned data table. Alternatively, the target data table may be a partitioned data table. In this case, the present application may optimize the partitioning of the target data table based on the target data dimensions obtained by parsing historical query statements.

[0053] In an illustrated embodiment, the present application may also partition the target data table based on the target data dimension obtained by parsing the historical query statement while constructing the target data table. For example, the data query system 200 may construct the target data table using UCS and partition the target data table based on the target data dimension obtained by parsing the historical query statement, etc. This specification does not specifically limit this.

[0054] Furthermore, after completing the partitioning processing of the target data table, the data query system 200 can obtain the target query statement to be executed for the target data table. The data dimension included in the query condition of the target query statement can be the above-mentioned target data dimension (such as the above-mentioned time or city, etc.). Accordingly, the data query system 200 executes the target query statement and can directly perform query processing within the multiple data partitions obtained by division without the need for re-partitioning, which greatly improves the data query efficiency.

[0055] In one illustrated embodiment, the data in the target data table may be original data related to the merchant knowledge graph, and this specification does not impose any specific limitation on this.

[0056] In one illustrated embodiment, storage device 100a, storage device 100b, storage device 100c, etc. can be independent storage devices, or different storage areas in the same device, such as different files, etc. This specification does not make specific limitations on this.

[0057] In one illustrated embodiment, the data query system 200 may be a system independent of the storage system 100, or the data query system 200 may be integrated into the storage system 200 as a functional module, etc. This specification does not specifically limit this.

[0058] In one illustrated embodiment, the data query system 200 may include an Autonomous Query Engine (AQE). AQE is a SQL optimization engine that can optimize subsequent query execution plans based on intermediate data during query execution, thereby improving overall query execution efficiency and enhancing data query performance.

[0059] As mentioned above, since the present application divides the data partitions based on the target data dimension obtained by parsing the user's historical query statements in advance when storing the data in the target data table, for AQE, if the data dimension (such as join key) contained in the target query statement to be executed is the same as the target data dimension of the above data partition, then it does not need to repartition when executing the target query statement (that is, it can choose to cancel the shuffle operator), and directly performs query processing based on the multiple data partitions obtained by division, which greatly reduces the amount of calculation and improves the query performance.

[0060] Please refer to Figure 2, which is a flowchart of a data query method provided by an exemplary embodiment. This method can be applied to the system architecture shown in Figure 1, and specifically to the data query system 200 in the system architecture shown in Figure 1. As shown in Figure 2, the method can specifically include the following steps S101-S103.

[0061] Step S101 : parsing a historical query statement for a target data table to obtain at least one data dimension included in a query condition in the historical query statement.

[0062] In an embodiment shown, the present application can obtain one or more historical query statements for the target data table. For example, the present application can uniformly collect the user's historical query statements for the target data table at the entrance of all queries.

[0063] It should be noted that this specification does not specifically limit the specific type of historical query statements. In one illustrated embodiment, the historical query statement can be a join-type SQL statement, an agg-type SQL statement, a group-by-type SQL statement, a distinct-type SQL statement, and so on. This specification does not specifically limit this. For details, please refer to the description of the embodiment corresponding to Figure 1 above.

[0064] Furthermore, the present application can parse the acquired historical query statements to obtain at least one data dimension included in the query conditions in the historical query statements.

[0065] In one illustrated embodiment, the present application may employ the Catalyst framework to recursively traverse the SQL syntax tree to obtain the data dimensions contained in the historical query statements. In some possible embodiments, the present application may also employ any other possible method to obtain the data dimensions contained in the historical query statements, which is not specifically limited in this specification.

[0066] For example, based on multiple historical query statements of different query types (such as the above-mentioned group by type, join type, agg type, distinct type, etc.), the data dimensions obtained by parsing may include group by key, join key, agg key, distinct key, etc., which is not specifically limited in this specification.

[0067] In addition, the present application may adopt different parsing methods for different types of historical query statements to obtain the data dimensions contained in different types of historical query statements, and this specification does not make specific limitations on this.

[0068] In an illustrated embodiment, the present application may obtain and parse the user's historical query statements for the target data table through AQE.

[0069] Furthermore, in order to improve the parsing efficiency of historical query statements and obtain the data dimensions contained therein more quickly and effectively, AQE can first pre-process the large number of historical query statements obtained to filter out historical query statements with the same pattern from the large number of historical query statements, and then parse these filtered historical query statements to obtain the data dimensions contained therein.

[0070] For example, if different historical query statements differ only in the time conditions of the where clause, they cannot be considered the same pattern. After obtaining a large number of historical query statements, AQE needs to filter out the influence of irrelevant expressions to ensure the efficiency of subsequent statement parsing.

[0071] Step S102 : determining a target data dimension from the at least one data dimension, and performing partition processing on the target data table based on the target data dimension to divide the target data table into a plurality of data partitions.

[0072] Furthermore, the present application can further obtain partition information for the target data table based on at least one data dimension obtained by the above analysis. In an illustrated embodiment, the partition information can include a partition key of the data partition and the number of partitions (partition size).

[0073] It is understandable that the at least one data dimension parsed from the historical query statement is not necessarily suitable as a partition key for data partitioning. Therefore, in one illustrated embodiment, the present application can determine the target data dimension (i.e., the partition key) from the at least one data dimension parsed.

[0074] In an embodiment shown, the target data dimension can be one or more, which is not specifically limited in this specification. In other words, the present application can partition the target data table based on a combination of one or more partition keys.

[0075] It should be noted that this specification does not particularly limit the specific method for determining the target data dimension from at least one data dimension.

[0076] In an illustrated embodiment, if the data column corresponding to a data dimension in the target data table is a high-cardinality column, the data dimension can be determined as the target data dimension. It should be noted that the repetition rate of data contained in the high-cardinality column is often small.

[0077] For example, the present application may first determine whether the data repetition rate in the data columns corresponding to each data dimension in the at least one data dimension in the target data table is less than a first preset value. Further, if the data repetition rate in the data columns corresponding to a data dimension is less than the first preset value, the data dimension may be determined to be the target data dimension.

[0078] In an embodiment shown, the first preset value can be set based on actual conditions, for example, 20%, 15%, or 10%, etc., which is not specifically limited in this specification.

[0079] For example, taking the user dimension as an example, the target data table includes a total of 100 rows of data, and these 100 rows of data are data for different users, that is, the data repetition rate in the data column corresponding to the user dimension is 0, then the user dimension can be determined as the target data dimension.

[0080] For example, let's take the city dimension as an example. The target data table contains 100 rows of data, and 80 of these rows are data for City A. This means that the data duplication rate in the data column corresponding to the city dimension is as high as 80%. Therefore, the city dimension is not suitable as a partition key. It should be understood that if the target data table is partitioned according to the city dimension, a large amount of data related to City A will still be stored in each data partition, and the data will not be evenly distributed across multiple data partitions, thus failing to improve the efficiency of subsequent data queries.

[0081] Furthermore, after determining the target data dimension, the present application may further determine a preset number of data partitions and a data range corresponding to each data partition.

[0082] In one illustrated embodiment, the present application can determine a preset number of more appropriate data partitions and a data range corresponding to each data partition based on the amount of data (or number of rows) in the data column corresponding to the target data dimension in the target data table.

[0083] Furthermore, based on the determined preset number of data partitions and the data range corresponding to each data partition, the present application can partition the target data table to divide the target data table into a preset number of multiple data partitions. Accordingly, each of the multiple data partitions can store data within a corresponding data range in the target data table.

[0084] It should be noted that the more partitions, the better. If the amount of data is small but there are too many partitions, it will lead to too many tasks being started during subsequent data queries, and each task will be very fragmented, which will increase the pressure on the driver and seriously affect data query performance.

[0085] In one illustrated embodiment, the preset number of data partitions may be proportional to the amount of data in the data column corresponding to the target data dimension, i.e., the greater the amount of data, the greater the number of data partitions, thereby reducing the data query burden within each data partition.

[0086] For example, taking the target data dimension as the user dimension, the data column corresponding to the user dimension may contain tens of thousands of rows of data. If each user corresponds to a data partition, tens of thousands of data partitions will be generated, which will affect the subsequent data query performance. If it is divided into only two data partitions, each data partition will contain data of tens of thousands of users, which will also affect the subsequent data query performance. Therefore, this application needs to determine a more appropriate number of partitions to divide the data of several users into the same data partition. For example, if the target data table contains data related to ten thousand users, the preset number of data partitions can be determined to be one hundred, and each data partition stores data related to one hundred users, etc. This specification does not make specific limitations on this.

[0087] In addition, in an illustrated embodiment, the present application can also obtain sort information by parsing historical query statements, that is, the above-mentioned partition information can also include sort information. Based on the sort information, the data in each data partition obtained by division can be stored in an orderly manner. Exemplarily, taking the target data dimension as user as an example, the data in each data partition can be stored in alphabetical order of the user name. Exemplarily, taking the target data dimension as time as an example, the data in each data partition can be stored in chronological order of date, etc., and this specification does not make specific limitations on this.

[0088] It should be noted that this specification does not specifically limit the specific type of partitioning. In one embodiment, the partitioning type is mainly any of the following: range partitioning, hash partitioning, predefined list partitioning, key partitioning, etc. This specification does not specifically limit this.

[0089] Exemplarily, taking the data partition as a hash partition as an example, the data range corresponding to each data partition may include a hash value corresponding to each hash partition.

[0090] In an illustrated embodiment, when the present application partitions the target data table based on the preset number of determined data partitions and the data range corresponding to each data partition, it can specifically include: dividing the target data table into a preset number of multiple hash partitions based on the preset number of determined hash partitions; then, determining the dimension value corresponding to the target data dimension contained in each data of the target data table (that is, the value corresponding to the key), calculating the hash value corresponding to the dimension value, and storing each data in the hash partition corresponding to the calculated hash value.

[0091] For example, the dimension values ​​corresponding to the user dimension may include user names such as Zhang San, Li Si, and Wang Wu. For example, the dimension values ​​corresponding to the city dimension may include city A, city B, and city C. The hash values ​​calculated for city A and city B may be the same, and the data related to city A and city B may be divided into the same data partition.

[0092] It should be noted that this specification does not specifically limit the specific algorithm used to calculate the hash value. In one embodiment shown, the MD5 value corresponding to each dimension value can be calculated by using the Message-Digest Algorithm 5 (MD5). In some possible implementations, the hash value corresponding to each dimension value can also be calculated by using the Secure Hash Algorithm (SHA) algorithm, the Data Encryption Standard (DES) algorithm, the Advanced Encryption Standard (AES) algorithm, etc., and this specification does not specifically limit this.

[0093] In one illustrated embodiment, the present application can also periodically obtain and parse historical query statements for the target data table within each time period according to a preset time period, thereby periodically obtaining partition information for the target data table, so as to continuously optimize the partitions of the target data table based on the user's latest query requirements and ensure long-term query performance.

[0094] It is understandable that if the partition information obtained in the current cycle is consistent with the partition information in the previous cycle, and the target data table has been partitioned based on the partition information obtained in the previous cycle, then the target data table may no longer be partitioned this time to save computing resources and temporary storage resources.

[0095] It should be noted that this specification does not specifically limit the specific method of comparing the partition information obtained in two adjacent cycles.

[0096] In an illustrated embodiment, the present application can compare one by one whether the partition key, number of partitions, and sort information contained in the partition information obtained in the previous cycle and the partition information obtained in this cycle are the same. If all the information are the same one by one, it indicates that the partition information obtained in the previous cycle and the partition information obtained in this cycle are the same.

[0097] In an illustrated embodiment, the present application may also first calculate the MD5 values ​​of the partition information obtained in two adjacent cycles. If the two MD5 values ​​are the same, it indicates that the partition information obtained in the previous cycle is the same as the partition information obtained in the current cycle.

[0098] In one illustrated embodiment, the present application may not parse historical query statements to obtain partition information according to a fixed time period, but may parse to obtain partition information each time a preset number of historical query statements are collected, and so on. This specification does not make specific limitations on this.

[0099] In one illustrated embodiment, since the collection and parsing of historical query statements in the early stage and the subsequent partitioning processing itself require a portion of computing resources and temporary storage resources, if the data volume of the target data table is small, data partitioning generally cannot bring more ideal benefits. Therefore, when the data volume of the target data table is small, this application may also choose not to collect and parse historical query statements to perform partitioning processing on them.

[0100] For example, the present application may first determine whether the amount of data in the target data table is greater than a second preset value; if so, then obtain and parse historical query statements for the target data table to obtain corresponding partition information and perform partition processing on the target data table, etc. It should be noted that this specification does not specifically limit the specific value of the second preset value, which can be set based on actual conditions or needs.

[0101] In addition, it should be noted that the data partitioning involved in this application is an optimization item for the target data table. In order not to interfere with the operation of online jobs, this application can partition the target data table when the cluster is idle. Furthermore, in one embodiment shown, the data partitioning optimization item can be a program application that detects the cluster idle rate and starts in the background.

[0102] Furthermore, in some possible implementations, in addition to obtaining partition information for the target data table by obtaining and parsing historical query statements for the target data table, the present application may also obtain partition information for the target data table by obtaining and parsing historical query statements for other data tables, and so on, which are not specifically limited in this specification. The aforementioned other data tables may have data associations with the target data table.

[0103] Step S103 : obtaining a target query statement to be executed, and executing the target query statement to perform query processing in the multiple data partitions respectively.

[0104] In one illustrated embodiment, after completing the partitioning process on the target data table, a target query statement to be executed can be obtained. The data dimension included in the query condition of the target query statement can be the target data dimension described above. Since the target data table has been partitioned in advance in this application, when executing the target query statement, this application can perform query processing on each of the multiple data partitions obtained by partitioning and obtain query results. This will not be further described here.

[0105] Furthermore, the present application can also output query results of the target query statement to the user, and so on.

[0106] In summary, the present application can obtain historical query statements for the target data table and parse the historical query statements to obtain at least one data dimension contained in the historical query statements, and then determine the target data dimension from the at least one data dimension. Furthermore, the present application can partition the target data table based on the target data dimension to divide the target data table into multiple data partitions, each of which stores part of the data in the target data table. Subsequently, after receiving the target query statement to be executed, since the present application has already partitioned the target data table based on the user's historical query requirements in advance, it can directly perform query processing in the multiple data partitions obtained by the division, without the need to repartition the data table, which greatly improves the data query performance.

[0107] Corresponding to the implementation of the above-mentioned method flow, the embodiment of this specification also provides a data query device. Please refer to Figure 3, which is a structural diagram of a data query device provided by an exemplary embodiment. The device 30 can be applied to the data query system 200 in the system architecture shown in Figure 1. As shown in Figure 3, the device 30 includes: a parsing unit 301, which is used to parse the historical query statement for the target data table and obtain at least one data dimension contained in the query condition in the historical query statement; a partition processing unit 302, which is used to determine the target data dimension from the at least one data dimension, and partition the target data table based on the target data dimension to divide the target data table into multiple data partitions; a data query unit 303, which is used to obtain the target query statement to be executed, and execute the target query statement to perform query processing in the multiple data partitions respectively.

[0108] In an illustrated embodiment, the partition processing unit 302 is specifically used to: determine whether the data repetition rate in the data columns corresponding to each data dimension in the at least one data dimension in the target data table is less than a first preset value; if the data repetition rate in the data columns corresponding to any data dimension is less than the first preset value, determine that the data dimension is the target data dimension.

[0109] In an illustrated embodiment, the partition processing unit 302 is specifically used to: determine a preset number of data partitions and a data range corresponding to each data partition based on the amount of data in the data column corresponding to the target data dimension in the target data table; based on the determined preset number of data partitions and the data range corresponding to each data partition, perform partition processing on the target data table to divide the target data table into a preset number of multiple data partitions; wherein each of the multiple data partitions stores data within the corresponding data range in the target data table.

[0110] In one illustrated embodiment, the data partition is a hash partition, and the data range corresponding to each data partition includes a hash value corresponding to each hash partition; the partition processing unit 302 is specifically used to: divide the target data table into a preset number of multiple hash partitions based on the determined preset number of hash partitions; determine the dimension value corresponding to the target data dimension contained in each data of the target data table, calculate the hash value corresponding to the dimension value, and store each data in the hash partition corresponding to the calculated hash value.

[0111] In an illustrated embodiment, the parsing unit 301 is specifically configured to periodically obtain historical query statements for the target data table within a preset time period, and parse the historical query statements.

[0112] In an illustrated embodiment, the parsing unit 301 is specifically used to: parse the historical query statements for the target data table, including: determining whether the amount of data in the target data table is greater than a second preset value; if so, parsing the historical query statements for the target data table to partition the target data table.

[0113] In an illustrated embodiment, the historical query statement and the target query statement are join-type query statements or agg-type query statements.

[0114] The implementation process of the functions and effects of each unit in the above-mentioned device 30 is specifically described in the description of the embodiments corresponding to Figures 1 and 2 above, and will not be repeated here. It should be understood that the above-mentioned device 30 can be implemented by software, or by hardware or a combination of software and hardware. Taking software implementation as an example, as a device in the logical sense, it is formed by the processor (CPU) of the device where it is located reading the corresponding computer program instructions into the memory and running them. From the hardware level, in addition to the CPU and memory, the device where the above-mentioned device is located usually also includes other hardware such as chips for wireless signal transmission and reception, and / or other hardware such as boards and cards for implementing network communication functions.

[0115] The device embodiments described above are merely illustrative, wherein the units described as separate components may or may not be physically separate, and the components shown as units may or may not be physical modules, that is, they may be located in one place or distributed across multiple network modules. Some or all of the units or modules may be selected according to actual needs to achieve the purpose of the scheme of this specification. Those of ordinary skill in the art can understand and implement the present invention without inventive effort.

[0116] The devices, units, and modules described in the above embodiments may be implemented by computer chips or entities, or by products having certain functions. A typical implementation device is a computer, which may be in the form of a personal computer, laptop computer, cellular phone, camera phone, smartphone, personal digital assistant, media player, navigation device, email transceiver, game console, tablet computer, wearable device, vehicle-mounted computer, or any combination of these devices.

[0117] Corresponding to the above-mentioned method embodiment, the embodiment of this specification also provides a computer device. Please refer to Figure 4, which is a structural diagram of a computer device provided by an exemplary embodiment. The computer device shown in Figure 4 can be the computer device in the data query system 200 in the system architecture shown in Figure 1 above. As shown in Figure 4, the computer device includes a processor 1001 and a memory 1002, and can further include an input device 1004 (such as a keyboard, etc.) and an output device 1005 (such as a display, etc.). The processor 1001, the memory 1002, the input device 1004 and the output device 1005 can be connected via a bus or other means. As shown in Figure 4, the memory 1002 includes a computer-readable storage medium 1003, which stores a computer program that can be run by the processor 1001. The processor 1001 can be a CPU, a microprocessor, or an integrated circuit for controlling the execution of the above method embodiment. When the processor 1001 runs the stored computer program, it can execute the various steps of the data query method in the embodiments of this specification, including: parsing historical query statements for the target data table to obtain at least one data dimension contained in the query conditions in the historical query statements; determining the target data dimension from the at least one data dimension, and partitioning the target data table based on the target data dimension to divide the target data table into multiple data partitions; obtaining the target query statement to be executed, and executing the target query statement to perform query processing in the multiple data partitions respectively, and so on.

[0118] For a detailed description of each step of the above data query method, please refer to the previous content and will not be repeated here.

[0119] Corresponding to the above method embodiments, embodiments of this specification also provide a computer-readable storage medium storing a computer program. When executed by a processor, these computer programs perform the steps of the data query method described in the embodiments of this specification. For details, please refer to the description of the embodiments corresponding to Figures 1 and 2 above, and will not be repeated here.

[0120] The above description is only a preferred embodiment of this specification and is not intended to limit this specification. Any modifications, equivalent replacements, improvements, etc. made within the spirit and principles of this specification should be included in the scope of protection of this specification.

[0121] In a typical configuration, a terminal device includes one or more CPUs, input / output interfaces, network interfaces, and memory.

[0122] Memory may include non-permanent storage in a computer-readable medium, random access memory (RAM) and / or non-volatile memory in the form of read-only memory (ROM) or flash RAM. Memory is an example of a computer-readable medium.

[0123] Computer-readable media include permanent and non-permanent, removable and non-removable media that can store information using any method or technology. The information can be computer-readable instructions, data structures, program modules or other data.

[0124] Examples of computer storage media include, but are not limited to, phase change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory or other memory technology, compact disc read-only memory (CD-ROM), digital versatile disc (DVD) or other optical storage, magnetic cassettes, magnetic tape, magnetic disk storage or other magnetic storage devices, or any other non-transmission media that can be used to store information that can be accessed by a computing device. As defined herein, computer-readable media does not include transitory media such as modulated data signals and carrier waves.

[0125] It should also be noted that the terms "comprises," "includes," or any other variations thereof are intended to encompass non-exclusive inclusion, such that a process, method, commodity, or apparatus that includes a series of elements includes not only those elements but also other elements not explicitly listed, or includes elements inherent to such process, method, commodity, or apparatus. In the absence of further limitations, an element defined by the phrase "comprises a ..." does not exclude the presence of other identical elements in the process, method, commodity, or apparatus that includes the element.

[0126] Those skilled in the art will appreciate that the embodiments of this specification may be provided as methods, systems, or computer program products. Thus, the embodiments of this specification may take the form of entirely hardware embodiments, entirely software embodiments, or embodiments combining software and hardware. Furthermore, the embodiments of this specification may take the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to magnetic disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

Claims

1. A data query method, characterized in that: include: Parsing a historical query statement for a target data table to obtain at least one data dimension included in a query condition in the historical query statement; Determine a target data dimension from the at least one data dimension, and perform partition processing on the target data table based on the target data dimension to divide the target data table into a plurality of data partitions; A target query statement to be executed is obtained, and the target query statement is executed to perform query processing in the multiple data partitions respectively.

2. The method according to claim 1, characterized in that The determining a target data dimension from the at least one data dimension comprises: Determine whether a data repetition rate in a data column corresponding to each data dimension in the at least one data dimension in the target data table is less than a first preset value; If the data repetition rate in the data column corresponding to any data dimension is less than the first preset value, the data dimension is determined to be the target data dimension.

3. The method according to claim 1, characterized in that The partitioning process of the target data table based on the target data dimension includes: Determine a preset number of data partitions and a data range corresponding to each data partition based on the amount of data in the data column corresponding to the target data dimension in the target data table; Based on the determined preset number of data partitions and the data range corresponding to each data partition, the target data table is partitioned to divide the target data table into a preset number of multiple data partitions; wherein each of the multiple data partitions stores data within a corresponding data range in the target data table.

4. The method according to claim 3, characterized in that The data partition is a hash partition, and the data range corresponding to each data partition includes a hash value corresponding to each hash partition; The partitioning process of the target data table based on the determined preset number of data partitions and the data range corresponding to each data partition includes: Based on the determined preset number of hash partitions, dividing the target data table into a preset number of hash partitions; Determine the dimension value corresponding to the target data dimension contained in each data in the target data table, calculate the hash value corresponding to the dimension value, and store each data in a hash partition corresponding to the calculated hash value.

5. The method according to claim 1, characterized in that: The parsing of historical query statements for the target data table includes: According to a preset time period, historical query statements for the target data table in each time period are periodically obtained, and the historical query statements are parsed.

6. The method according to claim 1, characterized in that The parsing of historical query statements for the target data table includes: Determine whether the amount of data in the target data table is greater than a second preset value; If yes, the historical query statements for the target data table are parsed to perform partition processing on the target data table.

7. The method according to any one of claims 1 to 6, characterized in that: The historical query statement and the target query statement are join type query statements or agg type query statements.

8. A data query device, characterized in that: include: A parsing unit, used to parse the historical query statements for the target data table to obtain multiple data dimensions included in the query conditions in the historical query statements; a partition processing unit, configured to determine at least one target data dimension from the multiple data dimensions, and perform partition processing on the target data table based on the at least one target data dimension, so as to divide the target data table into multiple data partitions; The data query unit is used to obtain a target query statement to be executed, and execute the target query statement to perform query processing in the multiple data partitions respectively.

9. A computer device, characterized in that: include: Memory and processor; The memory stores a computer program executable by the processor; When the processor runs the computer program, the processor performs the method according to any one of claims 1 to 7.

10. A computer-readable storage medium, characterized in that: A computer program is stored thereon, and when the computer program is executed by a processor, the method according to any one of claims 1 to 7 is implemented.

Images