Data query method, device, equipment and storage medium

Abstract

The present application provides a data query method, apparatus, device, and storage medium, the method comprising: determining query condition information for querying a target database, the query condition information indicating a target table to be queried, a query condition, and an output field to be output, the query condition including conditions that must be satisfied by the query field to be matched; determining a target field of array type among the query fields and the output field; generating a join relationship for left-joining the subtable to the main table with the target field as a subtable and the target table as a main table; using the join table represented by the join relationship as a table to be queried in an SQL query statement, combining the query condition and the output field, and constructing an SQL query statement according to SQL syntax; and querying the target database based on the SQL query statement to retrieve data from the target table in the target database. This solution can improve the convenience of querying data in a database when the database adopts a columnar storage format that supports nested structures.

Description

Technical Field

[0001] The present application relates to the field of data processing technology, and in particular to a data query method, apparatus, device and storage medium. Background Art

[0002] In the era of big data, columnar storage formats that support nested structures are becoming increasingly common in databases. For example, Parquet is a commonly used columnar storage format that supports nested structures.

[0003] When a database uses a columnar storage format that supports nested structures, such as Parquet, the database supports query statements using Structured Query Language (SQL). However, if users do not understand or are unfamiliar with SQL, it is difficult to query data in a database that uses a columnar storage format that supports nested structures. Therefore, how to improve the convenience of data querying in such databases is a technical problem that needs to be solved by those skilled in the art. Summary of the Invention

[0004] In view of this, the present application provides a data query method, apparatus, device and storage medium, which improve the convenience of querying data in a database when the database adopts a columnar storage format that supports nested structures.

[0005] To achieve the above objectives, this application provides a data query method, including:

[0006] Determining query condition information for querying a target database, the query condition information indicating a target table to be queried, a query condition, and output fields to be output, the query condition including conditions that must be satisfied by the query fields to be matched, and the target database storing data in a columnar storage format that supports nested structures;

[0007] Determine a target field of the query field and the output field that is an array type;

[0008] With the target field as a sub-table and the target table as a main table, generating a join relationship for left-joining the sub-table with the main table;

[0009] Using the connection table represented by the connection relationship as a table to be queried in a structured query language SQL query statement, combining the query condition and the output field, and constructing the SQL query statement according to the SQL grammar;

[0010] The target database is queried based on the SQL query statement to retrieve data matching the SQL query statement from the target table in the target database.

[0011] In a possible implementation, the query condition information includes the name of the output field to be output, and the query condition indicates the name of the query field to be queried and the condition that the query field needs to meet;

[0012] The step of taking the target field as a sub-table and the target table as a main table and generating a connection relationship for left-joining the sub-table with the main table includes:

[0013] determining a first replacement name for replacing the name of the target field;

[0014] Using the first replacement name of the target field as the name of the sub-table and the target table as the main table, generating a join relationship for left-joining the sub-table having the first replacement name with the main table;

[0015] The step of using the connection table represented by the connection relationship as a table to be queried in a structured query language SQL query statement, combining the query conditions and output fields, and constructing the SQL query statement according to SQL syntax includes:

[0016] Replacing the name of the target field in the query condition and the output field with the first replacement name to obtain a replaced query condition and a replaced output field;

[0017] The connection table represented by the connection relationship is used as a table to be queried in a structured query language SQL query statement, and the replaced query conditions and replaced output fields are combined to construct an SQL query statement according to SQL syntax.

[0018] In another possible implementation, the query condition information includes a table name of a target table to be queried;

[0019] Before generating a connection relationship for left-connecting the sub-table of the first replacement name with the main table, the method further includes:

[0020] determining a second alternative name for replacing the table name of the target table, wherein the second alternative name contains fewer characters than the table name of the target table;

[0021] The step of using the first replacement name of the target field as the name of the sub-table and the target table as the main table to generate a connection relationship for left-joining the sub-table with the first replacement name and the main table includes:

[0022] The first alternative name of the target field is used as the name of the sub-table, and the second alternative name of the target table is used as the name of the main table, to generate a join relationship for left-joining the sub-table of the first alternative name with the main table of the second alternative name.

[0023] In yet another possible implementation, determining a target field of the query field and the output field that is an array type includes:

[0024] Determining a hierarchical relationship between the query field and at least one target field of array type in the output field and the at least one target field;

[0025] Before generating a connection relationship for left-connecting the sub-table with the main table, the method further includes:

[0026] determining a connection order of the at least one target field based on a hierarchical relationship between the at least one target field, wherein the target field has an earlier hierarchy and an earlier connection order;

[0027] The step of taking the target field as a sub-table and the target table as a main table and generating a connection relationship for left-joining the sub-table with the main table includes:

[0028] With the target table as the main table, a connection relationship is generated in accordance with the connection order, for sequentially connecting each target field as a sub-table with the main table as a left connection.

[0029] In another possible implementation, the query condition information indicates at least one query condition group and a logical relationship between the query condition groups, and the query condition group includes at least one query condition and a logical relationship between the at least one query condition;

[0030] The step of using the connection table represented by the connection relationship as a table to be queried in a structured query language SQL query statement, combining the query conditions and output fields, and constructing the SQL query statement according to SQL syntax includes:

[0031] The connection table represented by the connection relationship is used as the table to be queried in the structured query language SQL query statement, combined with the logical relationship between the query condition groups, the logical relationship between at least one query condition and at least one query condition in the query condition group, and the output field, and the SQL query statement is constructed according to the SQL grammar.

[0032] In yet another possible implementation, determining query condition information for querying the target database includes:

[0033] Display the condition configuration interface;

[0034] Based on the configuration operation of the user in the condition configuration interface, the to-be-queried condition information configured by the user for the target database is obtained.

[0035] In yet another possible implementation, before determining the query condition information for querying the target database, the method further includes:

[0036] Determine the HBase database table to be converted;

[0037] According to the data structure description specification of the data structure description language protobuf, the HBase database table is converted into a data set that conforms to the corresponding data structure of the data structure description language protobuf;

[0038] According to a mapping specification between a data structure of the data structure description language protobuf and a data structure of a column storage format supporting a nested structure, the data set is converted into a database table using a column storage format supporting a nested structure.

[0039] In another aspect, the present application further provides a data query device, comprising:

[0040] an information determination unit, configured to determine query condition information for querying a target database, wherein the query condition information indicates a target table to be queried, a query condition, and output fields to be output, wherein the query condition includes conditions that must be satisfied by the query fields to be matched, and the target database stores data in a columnar storage format that supports nested structures;

[0041] a field determination unit, configured to determine a target field of an array type among the query field and the output field;

[0042] a relationship generating unit, configured to generate a connection relationship for left-joining the sub-table to the main table, with the target field as the sub-table and the target table as the main table;

[0043] A statement construction unit, configured to use the connection table represented by the connection relationship as a table to be queried in a structured query language SQL query statement, combine the query condition and the output field, and construct the SQL query statement according to the SQL grammar;

[0044] A data query unit is used to query the target database based on the SQL query statement to query the target table in the target database for data matching the SQL query statement.

[0045] In another aspect, the present application further provides a computer device, comprising a memory and a processor;

[0046] Wherein, the memory is used to store programs;

[0047] The processor is used to execute the program, and when the program is executed, it is specifically used to implement any one of the data query methods described above.

[0048] On the other hand, the present application also provides a computer-readable storage medium for storing a program, which, when executed, is used to implement any of the data query methods described above.

[0049] As can be seen from the above, in this application, considering that the column storage structure supporting nested structures supports array types, but the database cannot process SQL query statements containing array type fields, after determining the query field in the query condition information and the target field in the output field that belongs to the array type, the target field will be used as a subtable and the target table to be queried in the query condition information will be used as the main table to generate a connection relationship for left-joining the subtable with the main table. On this basis, this application will use the connection table represented by the connection relationship as the table to be queried in the SQL query statement, combine the query conditions and output fields in the query condition information, and construct the corresponding SQL query statement according to the SQL grammar, so that the database can first execute the operation of left-joining the target field of the array type as a subtable with the target table based on the SQL query statement, and then perform the corresponding SQL query on the connected connection table, thereby solving the problem that the database using the column storage structure supporting nested structures cannot process SQL queries containing array type fields, and also realizes that the query operation of data in this type of database can be realized without the user entering an SQL statement, thereby improving the convenience of data query in this type of database. BRIEF DESCRIPTION OF THE DRAWINGS

[0050] In order to more clearly illustrate the technical solutions in the embodiments of the present application, the following briefly introduces the drawings required for use in the description of the embodiments. Obviously, the drawings described below are merely embodiments of the present application. For ordinary technicians in this field, other drawings can be obtained based on the provided drawings without any creative work.

[0051] Figure 1 A schematic diagram of a process flow of a data query method provided in an embodiment of the present application is shown;

[0052] Figure 2 Another flow chart of the data query method provided in the embodiment of the present application is shown;

[0053] Figure 3 A schematic diagram of a process for converting an HBase table into data in a storage format that supports nested structures is shown in an embodiment of the present application;

[0054] Figure 4 A schematic diagram of the structure of a data query device provided in an embodiment of the present application is shown. DETAILED DESCRIPTION

[0055] The solution of the present application is applicable to data queries on databases that use a columnar storage format that supports data structures, such as data queries on databases that use Parquet.

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

[0057] like Figure 1 , which shows a flow chart of an embodiment of the data query method provided by the present application. The method of this embodiment can be applied to any computer device. The method of this embodiment may include:

[0058] S101: Determine query condition information for querying a target database.

[0059] The target database stores data in a columnar storage format that supports nested structures. For example, the target database may be a database that uses Parquet, without any specific limitation.

[0060] The query condition information indicates the target table to be queried, the query condition, and the output fields required to be output.

[0061] The target table is a database table that needs to be queried in the target database. For the sake of distinction, the database table to be queried is referred to as the target table.

[0062] The query condition includes the conditions that the query field to be matched must meet. For example, the query condition may be that the value of field a is greater than a set value, and field a is the query field.

[0063] It is understood that in actual applications, the query condition information may include one or more query conditions. When the query condition information includes multiple query conditions, the query condition information may also indicate the logical relationship between the multiple query conditions. The logical relationship between the query conditions may be an "and" or "or" logical relationship, without limitation.

[0064] The output field in the query condition information refers to the field in the target table to which the results are to be retrieved. For example, to retrieve all "student names" whose "Chinese score" field has a value greater than 90, the output field would be "student name."

[0065] In the present application, the query condition information can be configured by the user as needed. Compared with using Structured Query Language (SQL) to write query statements, the complexity of directly configuring the corresponding query condition information by the user is lower.

[0066] For example, in one possible implementation, the present application may display a condition configuration interface, where the user can configure query condition information for the target database. Accordingly, the query condition information configured by the user in the condition configuration interface can be obtained. This scenario will be described in detail later, and will not be further elaborated here.

[0067] S102: Determine a target field of array type among the query field and the output field.

[0068] An array type field can have multiple values, while a non-array type field can only have one value or none.

[0069] For example, a movie can have multiple aliases. Therefore, the "Alias" field can contain multiple aliases corresponding to a movie. Therefore, "Alias" is an array-type field. Meanwhile, the duration of a movie is fixed, so the duration field must be a non-array-type field.

[0070] For easy distinction, the array type fields in query fields and output fields are called target fields.

[0071] Whether a field is an array type field can be determined by obtaining the field attributes of the field. The field attributes of the field can record the relevant characteristic information of the field. In this application, the field attributes of the field at least record the information of whether the field is an array type field.

[0072] For example, the field attributes of the query field and the field attributes of the output field can be queried from the target database. If the field attributes of a field in the query field and the output field indicate that the field is an array type field, then the field is determined as the target field.

[0073] S103 , with the target field as the sub-table and the target table as the main table, generating a join relationship for left-joining the sub-table with the main table.

[0074] In a left join between a subtable and a main table, the main table is the left table and the subtable is the right table. The result of the left join is to return all records in the left table that have the same join field as the right table. The join field is the field in the set join condition, generally the record identifier. In this application, since the subtable is essentially a field of the main table, the join condition does not need to be set, and the join field is the record identifier in the target table.

[0075] It's understandable that join operations can only be performed on tables. Furthermore, since the target field is an array type field and can contain multiple values, it can be considered a table. Based on this, this application uses the target field as the subtable in the join operation and the target table as the primary table in the join operation, thus constructing a left-join relationship between the subtable and the primary table.

[0076] The connection relationship may be a statement or function used to represent the establishment of a left connection between the sub-table and the main table.

[0077] It is understandable that columnar storage formats that support nested structures, such as Parquet, support array types, but the database cannot directly process the query processing of SQL query statements containing fields in array form. Therefore, this application left-joins the target field as a sub-table with the target table, so that the table after the left join can contain all the contents of the target table, and splits the value in the target field into multiple records, so that multiple values ​​under the target field are in different records in the table after the left join, thereby connecting the target field and the target table into a table that does not contain an array form, so that the database can query the connected table.

[0078] It is understood that the above description uses the case where there is only one target field as an example. In actual applications, there may be multiple target fields of array type in the query field and the output field. In this case, the present application can simultaneously determine the hierarchical relationship between the at least one target field of array type in the query field and the output field while determining the at least one target field of array type.

[0079] The hierarchical relationship between target fields can represent the subordinate relationship between the fields. For example, if target field B is a secondary field under target field A, then target field A is at a higher level than target field B, and target field B is at the next level below target field A. Of course, if there is no subordinate relationship between two target fields, then the two target fields can be at the same level.

[0080] Based on the above, the join order of the at least one target field can be determined based on the hierarchical relationship between the at least one target field. The higher the hierarchical level of the target field, the higher the join order of the target field. Accordingly, with the target table as the primary table, a join relationship can be generated according to the join order, sequentially left-joining each target field as a sub-table with the primary table. This join relationship represents a multi-table left join between the at least one target field and the primary table.

[0081] The connection relationship of left-joining each target field as a sub-table with the main table will eventually left-join each target field as a sub-table with the main table to form a single table. Specifically, for each target field, the target field as a sub-table needs to be left-joined with the other target fields that precede the target field in the join order as sub-tables and the connection table after the main table is left-joined. This connection relationship enables each target field to be left-joined with the main table in sequence, ultimately forming a single table.

[0082] S104: Use the connection table represented by the connection relationship as a table to be queried in a structured query language SQL query statement, combine the query conditions and output fields, and construct the SQL query statement according to the SQL syntax.

[0083] It can be understood that since the connection relationship between the sub-table and the main table actually represents the connection table after the sub-table and the main table are left-connected, the SQL query statement constructed in this application actually uses the SQL language to describe the query conditions and output fields, and indicates that the table to be queried is the connection table corresponding to the connection relationship.

[0084] For example, you can first convert the query conditions into query conditions that conform to the SQL language syntax. Then, according to the grammatical structure of the SQL language, construct an SQL query statement by combining the output fields, the connection table to be queried, and the query conditions that conform to the SQL syntax.

[0085] As mentioned above, the query condition indication information may include multiple query conditions. In this case, the query condition information will include the logical relationship between the multiple query conditions. Based on this, when constructing an SQL query statement, it is necessary to combine multiple query conditions and their logical relationships, and construct the SQL query statement according to the SQL syntax.

[0086] In one possible implementation, the query condition information may indicate at least one query condition group and the logical relationship between the query condition groups. Each query condition group includes at least one query condition and the logical relationship between the at least one query condition. Accordingly, the connection table represented by the connection relationship can be used as the table to be queried in a structured query language (SQL) query statement. The SQL query statement is constructed according to SQL syntax, combining the logical relationship between the query condition groups, the logical relationship between at least one query condition in each query condition group and at least one query condition, and the output field.

[0087] S105 , querying the target database based on the SQL query statement to retrieve data matching the SQL query statement from a target table in the target database.

[0088] For example, the SQL query statement is sent to the target database. Based on this, the target database will, based on the join relationship contained in the SQL query statement, left-join the target field as a sub-table with the target table to form a single table (i.e., a joined table). Then, based on the query conditions in the SQL query statement, the target database will query the joined table obtained by the left join to obtain data related to the output fields that meet the query conditions.

[0089] As can be seen from the above, in this application, considering that the column storage structure supporting nested structures supports array types, but the database cannot process SQL query statements containing array type fields, after determining the query field in the query condition information and the target field in the output field that belongs to the array type, the target field will be used as a sub-table and the target table to be queried in the query condition information will be used as the main table, and a connection relationship for left-joining the sub-table with the main table will be generated. On this basis, this application will use the connection table represented by the connection relationship as the table to be queried in the SQL query statement, combine the query conditions and output fields in the query condition information, and construct a corresponding SQL query statement according to the SQL grammar, so that the database can first perform a left join operation between the target field of the array type and the target table based on the SQL query statement, and then perform a corresponding SQL query on the connection table after the left join, thereby solving the problem that the database using the column storage structure supporting nested structures cannot process SQL queries on numeric type fields, and also realizes that the query operation of data in this type of database can be performed without the user entering an SQL statement, thereby improving the convenience of querying this type of database.

[0090] It can be understood that since the target field of the array type is essentially still a field, and the object of the join operation is a table, in order to enable the database to perform join on the field as a table and distinguish it from the name of the field, this application can also first replace the name of the target field with another name, so that in the subsequently constructed SQL statement, it can be indicated that the replacement name of the target field actually represents the name of a table.

[0091] Specifically, a first alternative name can be determined to replace the name of the target field. To distinguish it from the name of the target field and the name subsequently used to replace the target table, the alternative name used to replace the target field name is referred to as the first alternative name. Accordingly, the first alternative name of the target field is used as the name of the sub-table, and with the target table as the main table, a join relationship is generated to left-join the sub-table with the first alternative name to the main table.

[0092] Based on the above, in order to make the name of the target field in the query conditions and output conditions consistent with the first replacement name, so that the database can identify the table corresponding to the target field with the first replacement name, this application also needs to replace the name of the target field contained in the query conditions and output fields with the first replacement name to obtain the replaced query conditions and replaced output fields.

[0093] Accordingly, when constructing an SQL statement, the replaced query conditions and the replaced output fields may be combined and the SQL query statement may be constructed according to the SQL syntax.

[0094] Furthermore, in actual applications, considering that the name of the target table may be long, in order to simplify the connection relationship of the join operation, the present application can also replace the name of the target table with a simpler name. By replacing the name of the target table with a simpler name, the number of characters in the subsequently generated SQL query statement can also be reduced, thereby simplifying the SQL query statement.

[0095] To facilitate understanding, a possible implementation is introduced below.

[0096] like Figure 2 As shown, it shows a flow chart of another embodiment of the data query method provided by the present application. The method of this embodiment can be applied to any computer device. The method of this embodiment may include:

[0097] S201, displaying a condition configuration interface.

[0098] The condition configuration interface is an operation interface for providing users with query condition information corresponding to the target database. In this application, the target database adopts a columnar storage format that supports nested structures. For example, the target database is a database that stores data in the Parquet storage format.

[0099] Of course, in actual applications, if the target database is not the default database, the target database information of the required operation can also be configured in the condition configuration interface.

[0100] For example, the condition configuration interface may include a condition group configuration area, each of which may include at least one condition configuration area. The condition configuration area is used to configure query conditions. This condition configuration area may include a configuration box for fields in the query condition, a configuration box for operators in the query condition, and a configuration box for field values ​​in the query condition.

[0101] The operators in the query conditions may include operators such as equal to, not equal to, greater than, and less than, which are used to limit the conditions that fields must meet, and there is no restriction on this.

[0102] Among them, the configuration box can present a corresponding drop-down selection menu for the user to select corresponding information from the drop-down menu. For example, when the user clicks the configuration box of the field, a drop-down menu of the field can be presented in the configuration box of the field, so that the user can select the field required to construct the query condition from the drop-down menu of the field.

[0103] The configuration box can also be an information input box. For example, the field value configuration box can be used to input the conditional value that the field in the query condition needs to meet.

[0104] It is understandable that after configuring the fields to be matched, operators, and field values ​​in the query condition in the condition configuration area, a query condition can be obtained.

[0105] In actual applications, different query conditions may be configured in different condition configuration areas or multiple configurations may be performed in the same condition configuration area to obtain one or more query conditions within the same query condition group.

[0106] At the same time, in the present application, one or more query condition groups can be obtained by configuring query condition groups in different condition configuration areas or configuring multiple query condition groups in the same condition configuration area.

[0107] Furthermore, the user can also configure the logical relationship between different query conditions and the logical relationship between different condition groups in the configuration interface, and there is no restriction on this.

[0108] Accordingly, each configured query condition group may include at least one query condition and a logical relationship between the at least one query condition. Furthermore, different query condition groups may also have configured logical relationships. For example, the logical relationship may be an "and" or "or" relationship.

[0109] S202: Based on the configuration operation of the user in the condition configuration interface, obtain the query condition information configured by the user for the target database.

[0110] The query condition information indicates the target table to be queried (eg, including the name of the target table, etc.), at least one query condition group, the logical relationship between the query condition groups, and the output fields required to be output.

[0111] The query condition information indicating the target table to be queried may include the name of the target table to be queried. Correspondingly, the query condition information indicating the output field to be output may include the name of the output field.

[0112] Each query condition group includes at least one query condition and at least one logical relationship between query conditions. Each query condition includes the name of a query field to be matched and a condition that the query field to be matched needs to meet.

[0113] The number of output fields in the query condition information can be one or more, and there is no restriction on this.

[0114] For example: Take the example of querying the names and scores of students whose Chinese scores are less than 60 points from the student score table:

[0115] The query condition information may include the following:

[0116] The target table is the student score table, i.e. studentScore;

[0117] The output fields are: studentName (student name), score.scoreNumber (subject score)

[0118] The query condition group includes query condition 1 and query condition 2, and the logical relationship between query condition 1 and query condition 2 is: AND;

[0119] Query condition 1: score.courseName (subject name) is equal to Chinese;

[0120] Query condition 2: score.scoreNumber is less than 60.

[0121] In the above example, score (subject score) can include multiple second-level fields. For example, scoreNumber (subject score) and courseName (subject name) are both the next-level fields of score (subject score). Therefore, when describing the second-level field, you can use a period before the second-level field to write the field name of the previous level field.

[0122] It should be noted that the above steps S202 and S203 are described by taking an example of an implementation method of determining query condition information for querying the target database. Other methods of determining query condition information are also applicable to the present application and will not be described in detail.

[0123] S203: Query the target database for each output field and the field attributes of the query field in each query condition.

[0124] The field attribute of a field is used to indicate whether the field is an array type field.

[0125] S204: Determine at least one target field of array type in the query field and the output field and a hierarchical relationship of the at least one target field.

[0126] It is understandable that there may be one or more fields of array type in the query field and the output field, so that at least one target field can be obtained.

[0127] Among them, the value of the array type field can be multiple, and the value in the array type field can be a basic type or a structure.

[0128] To make it easier to understand, let’s take an example:

[0129] There is a student score table with the following structure:

[0130]

[0131] In the table structure above, repeated indicates that the score field is an array type field.

[0132] For example, in the table structure, each value in score is a structure. Using JSON format as an example, a database record can be as follows:

[0133]

[0134]

[0135] In the example above, score is an array type field with a length of 4. The brackets "[]" below score are the four structured objects within score (each object represents a value). Each object is enclosed in curly braces within the brackets "[]".

[0136] Of course, the above description is based on the example of a structure as the value in an array type field, but it can be understood that the value of an array type field is a basic type or other types, which is similar and will not be repeated here.

[0137] The hierarchical relationship can be derived by combining the associations between fields. For example, in the example of step S203, the scoreNumber field is a secondary field of the score field, and therefore, scoreNumber is located one level below score. Both scoreNumber and courseName are secondary fields of score, and therefore, scoreNumber and courseName can both be fields one level below score.

[0138] S205: Determine the first replacement name of each target field respectively.

[0139] In order to facilitate distinction, the alternative name of the target field is called the first alternative name, but the first alternative names of different target fields are different and can be set according to needs without limitation.

[0140] For example, a corresponding number of first alternative names can be determined based on the number of target fields, sequentially named t1, t2, and so on, where n is the total number of target fields. Accordingly, the target fields are prioritized according to their hierarchical relationship, and the n first alternative names are then assigned to the at least one target field in that order. For example, the first alternative name of the first target field is t1, the first alternative name of the second target field is t2, and so on.

[0141] S206: Determine a second alternative name for replacing the table name of the target table.

[0142] The second alternative name contains fewer characters than the target table name.

[0143] For example, taking the query condition information in the previous S202 as an example, the target table is studentScore. Assuming that the second alternative name is t, then "studentScore" needs to be replaced with "t" later.

[0144] S207: Determine a connection order of the at least one target field based on a hierarchical relationship between the at least one target field.

[0145] The higher the level of the target field is, the higher the connection order of the target field is.

[0146] For example, using the query condition information example in S202 above, scoreNumber and courseName may be located at the same level, and score is located at the upper level of scoreNumber and courseName. In this case, the connection order of score may be the first, while the connection order of scoreNumber and courseName may be randomly determined.

[0147] S208, using the first alternative name of the target field as the name of the sub-table and the second alternative name of the target table as the name of the main table, and generating a connection relationship for sequentially connecting each target field as a sub-table with the main table as a left connection according to the connection order.

[0148] In this application, according to the connection order, the target field to be processed is selected from at least one target field, and a left connection relationship is constructed with the main table each time a target field is selected, until a connection relationship is constructed in which the sub-tables of all target fields are left-connected to the main table.

[0149] For example, assuming that the target fields include field a and field b, and the connection order is field a, field b, and the first alternative name of field a is t1, and the first alternative name of field b is t2, then it is necessary to first establish a left join between t1 and the primary table t (the primary table represented by the second alternative name t corresponding to the target table), and then construct a left join between t2 and the connection table after t1 is left joined with the primary table t.

[0150] It should be understood that this embodiment uses the example of a situation where there may be multiple target fields. If there is only one target field, the first alternative name of the target field is used as the name of the sub-table, and the second alternative name of the target table is used as the name of the main table. A join relationship is directly generated to left-join the sub-table of the first alternative name with the main table of the second alternative name.

[0151] S209 , for each target field, replacing the name of the target field contained in each query condition and each output field with a first replacement name corresponding to the target field, to obtain a replaced query condition and a replaced output field.

[0152] For each target field, if the query condition in the query condition group includes the name of the target field, the name of the target field needs to be replaced with the first replacement name corresponding to the target field.

[0153] It is understandable that if the target field does not exist in the query condition or output field, the query condition and output field are not actually replaced by the field name. Therefore, the query condition or output field after replacement is the same as before replacement.

[0154] It can be understood that if the field in the query condition information is represented by the name of the target table plus the field name of the field, then it is also necessary to replace the name corresponding to the target table contained in the query condition information with the second alternative name corresponding to the target table.

[0155] For example, the output field: studentName is essentially the field studentName under the target table studentScore. Therefore, this field is actually also expressed as studentScore.studentName. Replace the name of the target table studentScore with t. After the name of the target table in this field is replaced with t, the field can be expressed as: t.studentName.

[0156] S210: Use the connection table represented by the connection relationship as a table to be queried in a structured query language SQL query statement, combine the logical relationship between the query condition groups, the logical relationship between at least one replaced query condition in each query condition group and the at least one replaced query condition, and the replaced output field, and construct an SQL query statement according to SQL syntax.

[0157] Among them, the logical relationship between the query conditions will not change before and after the target field in the query condition is replaced.

[0158] It can be understood that based on the replaced query conditions and the logical relationship between different query conditions, SQL syntax can be used to construct a statement that represents each query condition and its logical relationship. On this basis, based on the table to be queried being a table representing a connection relationship, and the replaced output fields, etc., an SQL query statement can finally be constructed based on SQL syntax.

[0159] The SQL symbols used to represent logical relationships in SQL syntax are fixed. For example, in SQL syntax, the logical relationship "and" can be represented as "and", and the logical relationship "or" can be represented as "or". Other logical relationships also have similar corresponding relationships, which will not be detailed here.

[0160] To facilitate understanding of the process of constructing an SQL query statement in this embodiment, the following example is used to illustrate the query condition information in the previous S202:

[0161] Assume that the score field is named t1 as an alternative name for the table.

[0162] On this basis, the output field score.scoreNumber can be replaced with t1.scoreNumber;

[0163] In query condition 1, score.courseName is replaced with t1.courseName. Correspondingly, the replaced query condition 1 is: t1.scoreNumber = 'Chinese';

[0164] The score.scoreNumber in query condition 2 is replaced with t1.scoreNumber. Accordingly, the replaced query condition 2 is t1.scoreNumber<60.

[0165] As we can see from the previous example, the logical relationship between query condition 1 and query condition 2 is "AND". Based on the replaced query conditions corresponding to these two query conditions and their logical relationship, we can convert them into the following SQL statement:

[0166] (t1.scoreNumber='Chinese')and(t1.scoreNumber<60);

[0167] Based on this, assuming the output field studentName is studentScoret.studentName, then replacing the target table name with t in the output field yields the output field t.scorename. Accordingly, based on the output field t.scoreName, the replaced output field t1.scoreNumber and the constructed join relationship can generate the following SQL query:

[0168] select t.studentName,t1.scoreNumber from studentScore,t.score t1where((t1.scoreNumber='Chinese')and(t1.scoreNumber<60));

[0169] In the above SQL statement, "studentScore, t.score t1" is the connection relationship constructed previously. This connection relationship corresponds to a database connection operation. The result of the connection operation is the connection table obtained by left-joining each target field as a sub-table with the target table as the main table t.

[0170] S211 , querying the target database based on the SQL query statement to retrieve data matching the SQL query statement from a target table in the target database.

[0171] The step S211 can be referred to the relevant description of the previous embodiment and will not be repeated here.

[0172] It is understandable that since Parquet and other columnar storage formats support nested structures, unnecessary data scans can be reduced, which is beneficial for improving data query performance. Therefore, for some databases that do not have efficient data query performance, such as HBase databases, this application can also convert the data in such databases to Parquet and other columnar storage formats that support nested structures.

[0173] HBase is a distributed, column-oriented storage system built on HDFS (Hadoop Distributed File System). HBase stores data in tables. Tables consist of rows and columns, and columns are divided into several column families.

[0174] like Figure 3 As shown, it shows a schematic diagram of a process of converting an HBase table into data in a storage format that supports nested structures. This embodiment may include:

[0175] S301: Determine the HBase database table to be converted.

[0176] The HBase database table includes: at least one row key and at least one column cluster, wherein each column cluster includes at least one column.

[0177] It's understandable that some HBase fields (or columns) contain arrays of multiple values. To identify array-type fields (or columns) in HBase, you can prefix them with a prefix that represents the array type, as defined in the protobuf data structure specification.

[0178] Table 1 below shows an example of an HBase table:

[0179] Table 1

[0180]

[0181] In Table 1, "c1", "c2_n_a", and "c2_n_b" are all column names.

[0182] Among them, "c2_n_a" and "c2_n_b" have the same prefix c2_n. c2_n indicates that the field is an array field. The suffixes "a" and "b" in these two fields come from the "primary key" field value under the array.

[0183] S302 , according to the data structure description specification of the data structure description language protobuf, convert the HBase database table into a data set that conforms to the corresponding data structure of the data structure description language protobuf.

[0184] Among them, protobuf (Google Protocol Buffers) is a tool library provided by Google with an efficient protocol data exchange format.

[0185] It is understandable that the protobuf data structure description specification has a specific description method for fields. Therefore, for the fields in the HBase table, protobuf can be used to describe these fields according to the protobuf data structure description specification.

[0186] Specifically, the protobuf data structure description specification can include descriptions of non-array type columns and array type columns. Therefore, the protobuf language can be used to describe non-array type columns and array type columns in HBase tables.

[0187] For example, column c1 of column cluster d is a non-array column and is described in protobuf as optional Message d_c1.

[0188] The c2_n_a and c2_n_b columns of column cluster d are array columns and are described as repeated message d_c2 using protobuf.

[0189] Since the primary key in HBase is not a field and Protobuf does not have a description method for the primary key, this application can construct a mapping relationship between the primary key in HBase and the Protobuf description method. For example, the rowkey is used as the primary key ID and the Protobuf description is an optional long ID.

[0190] S303 , according to a mapping specification between a data structure of the data structure description language protobuf and a data structure of a storage format supporting nested structures, converting a data set described in the data structure description language protobuf into a database table in a columnar storage format supporting nested structures.

[0191] It can be understood that based on the mapping specification between protobuf and the data structure of the storage format supporting nested structures, the conversion method of converting protobuf data into the data structure of the columnar storage format supporting nested structures can be determined, thereby completing the corresponding data conversion.

[0192] For example, using Parquet as an example, the specific conversion algorithm can be as follows:

[0193] Get the fields and field values ​​in the dataset described by protobuf;

[0194] For fields of basic types (such as numbers or strings) or enumeration types, the field values ​​can be directly mapped to fields in the Parquet format.

[0195] For fields of structure type, it is necessary to continuously recurse each field under the field, and finally process the mapping conversion of the included basic types or fields of each sentence type layer by layer.

[0196] It is understandable that HBase, due to its inherent storage engine design, cannot provide efficient statistical analysis and has low query performance. However, the solution of this application converts HBase database tables into database tables using a columnar storage format such as Parquet that supports nested structures. This allows data query to be implemented by leveraging the efficient storage structure and good scanning performance of the columnar storage format that supports nested structures.

[0197] After the HBase database table is converted to a database table in a storage format such as Parquet, data query and other related processing can be performed in combination with the solutions of the previous embodiments of this application, which will not be repeated here.

[0198] Corresponding to a data query method of the present application, the present application also provides a data query device.

[0199] like Figure 4 , which shows a schematic diagram of the structure of a data query device provided in an embodiment of the present application. The device of this embodiment may include:

[0200] An information determination unit 401 is configured to determine query condition information for querying a target database, wherein the query condition information indicates a target table to be queried, a query condition, and output fields to be output. The query condition includes conditions that must be satisfied by the query fields to be matched. The target database stores data in a columnar storage format that supports nested structures.

[0201] A field determination unit 402 is configured to determine a target field of an array type among the query field and the output field;

[0202] A relationship generating unit 403 is configured to generate a join relationship for left-joining the sub-table to the main table, with the target field as the sub-table and the target table as the main table;

[0203] A statement construction unit 404 is configured to use the connection table represented by the connection relationship as a table to be queried in a structured query language SQL query statement, combine the query conditions and output fields, and construct an SQL query statement according to SQL syntax;

[0204] The data query unit 405 is configured to query the target database based on the SQL query statement to retrieve data matching the SQL query statement from the target table in the target database.

[0205] In a possible implementation, the information determining unit determines that the query condition information includes the name of the output field to be output, where the query condition indicates the name of the query field to be queried and the condition that the query field needs to satisfy;

[0206] The association generation unit includes:

[0207] a first replacing unit, configured to determine a first replacement name for replacing the name of the target field;

[0208] an association generating subunit, configured to use the first replacement name of the target field as the name of the sub-table and the target table as the main table, and to generate a connection relationship for left-joining the sub-table having the first replacement name with the main table;

[0209] Statement building blocks, including:

[0210] a field replacement subunit, configured to replace the name of the target field in the query condition and the output field with the first replacement name, to obtain a replaced query condition and a replaced output field;

[0211] The statement construction subunit is used to use the connection table represented by the connection relationship as the table to be queried in the structured query language SQL query statement, combine the replaced query conditions and the replaced output fields, and construct the SQL query statement according to the SQL grammar.

[0212] In another possible implementation, the query condition information determined by the information determination unit includes a table name of a target table to be queried;

[0213] The device also includes:

[0214] a second replacing unit configured to determine, before the association generating subunit generates a join relationship for left-joining the subtable having the first replacement name with the main table, a second replacement name for replacing the table name of the target table, wherein the second replacement name contains fewer characters than the table name of the target table;

[0215] The association generation subunit is used to use the first alternative name of the target field as the name of the sub-table and the second alternative name of the target table as the name of the main table, and to generate a connection relationship for left-connecting the sub-table of the first alternative name with the main table of the second alternative name.

[0216] In another possible implementation, the field determining unit is specifically configured to determine a hierarchical relationship between the query field and at least one target field of array type in the output field and the at least one target field;

[0217] The device also includes:

[0218] a join order determining unit, configured to determine, before the association generating unit generates a join relationship for left-joining the sub-table to the main table, a join order of the at least one target field based on a hierarchical relationship between the at least one target field, wherein the target field has an earlier hierarchical level and an earlier join order;

[0219] The association generating unit is specifically configured to use the target table as the main table and generate a connection relationship for sequentially connecting each target field as a sub-table with the main table in a left connection manner according to the connection sequence.

[0220] In a possible implementation, the query condition information determined by the information determination unit indicates at least one query condition group and a logical relationship between the query condition groups, and the query condition group includes at least one query condition and a logical relationship between the at least one query condition;

[0221] The statement construction unit is specifically configured to use the connection table represented by the connection relationship as a table to be queried in a structured query language SQL query statement, combine the logical relationship between the query condition groups, the logical relationship between at least one query condition in the query condition group and at least one query condition, and the output field, and construct an SQL query statement according to SQL grammar.

[0222] In yet another possible implementation, the information determining unit includes:

[0223] Interface display unit, used to display the condition configuration interface;

[0224] The information obtaining unit is used to obtain the to-be-queried condition information configured by the user for the target database based on the configuration operation of the user in the condition configuration interface.

[0225] In yet another possible implementation, the apparatus further includes:

[0226] a database table determination unit, configured to determine an HBase database table to be converted before the information determination unit determines query condition information for querying the target database;

[0227] A data structure conversion unit, configured to convert the HBase database table into a data set conforming to a corresponding data structure of the data structure description language protobuf according to a data structure description specification of the data structure description language protobuf;

[0228] A storage structure conversion unit is used to convert the data set into a database table using a column storage format supporting a nested structure according to a mapping specification between the data structure of the data structure description language protobuf and the data structure of the column storage format supporting a nested structure.

[0229] In another aspect, the present application further provides a computer device, comprising a memory and a processor;

[0230] Wherein, the memory is used to store programs;

[0231] The processor is used to execute the program, and when the program is executed, it is specifically used to implement the data query method described in any one of the above embodiments.

[0232] On the other hand, the present application also provides a computer-readable storage medium for storing a program, which, when executed, is used to implement the data query method described in any one of the above embodiments.

[0233] It should be noted that the various embodiments in this specification are described in a progressive manner, and each embodiment focuses on the differences from other embodiments. The same or similar parts between the various embodiments can be referred to each other. At the same time, the features described in the various embodiments in this specification can be replaced or combined with each other, so that professionals in this field can implement or use this application. For device embodiments, since they are basically similar to method embodiments, the description is relatively simple, and the relevant parts can be referred to the partial description of the method embodiment.

[0234] Finally, it should be noted that, in this document, relational terms such as first and second, etc., are used only to distinguish one entity or operation from another entity or operation, and do not necessarily require or imply any actual relationship or order between these entities or operations. Moreover, the terms "comprises," "comprising," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or device comprising a series of elements includes not only those elements, but also other elements not explicitly listed, or elements inherent to such process, method, article, or device. In the absence of further limitations, an element defined by the phrase "comprising a ..." does not exclude the presence of additional identical elements in the process, method, article, or device comprising the element.

[0235] The above description of the disclosed embodiments will enable one skilled in the art to implement or use the present application. Various modifications to these embodiments will be readily apparent to one 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 present application. Therefore, the present application is not limited to the embodiments shown herein, but is intended to conform to the widest scope consistent with the principles and novel features disclosed herein.

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

Claims

1. A data query method, characterized in that: include: Determining query condition information for querying a target database, the query condition information indicating a target table to be queried, a query condition, and output fields to be output, the query condition including conditions that must be satisfied by the query fields to be matched, and the target database storing data in a columnar storage format that supports nested structures; Determine a target field of the query field and the output field that is an array type; With the target field as a sub-table and the target table as a main table, generating a join relationship for left-joining the sub-table with the main table; Using the connection table represented by the connection relationship as a table to be queried in a structured query language SQL query statement, combining the query condition and the output field, and constructing the SQL query statement according to the SQL grammar; The target database is queried based on the SQL query statement to retrieve data matching the SQL query statement from the target table in the target database.

2. The method according to claim 1, characterized in that The query condition information includes the name of the output field to be output, and the query condition indicates the name of the query field to be queried and the condition that the query field needs to meet; The step of taking the target field as a sub-table and the target table as a main table and generating a connection relationship for left-joining the sub-table with the main table includes: determining a first replacement name for replacing the name of the target field; Using the first replacement name of the target field as the name of the sub-table and the target table as the main table, generating a join relationship for left-joining the sub-table having the first replacement name with the main table; The step of using the connection table represented by the connection relationship as a table to be queried in a structured query language SQL query statement, combining the query conditions and output fields, and constructing the SQL query statement according to SQL syntax includes: Replacing the name of the target field in the query condition and the output field with the first replacement name to obtain a replaced query condition and a replaced output field; The connection table represented by the connection relationship is used as a table to be queried in a structured query language SQL query statement, and the replaced query conditions and replaced output fields are combined to construct an SQL query statement according to SQL syntax.

3. The method according to claim 2, characterized in that The query condition information includes the table name of the target table to be queried; Before generating a connection relationship for left-connecting the sub-table of the first replacement name with the main table, the method further includes: determining a second alternative name for replacing the table name of the target table, wherein the second alternative name contains fewer characters than the table name of the target table; The step of using the first replacement name of the target field as the name of the sub-table and the target table as the main table to generate a connection relationship for left-joining the sub-table with the first replacement name and the main table includes: The first alternative name of the target field is used as the name of the sub-table, and the second alternative name of the target table is used as the name of the main table, to generate a join relationship for left-joining the sub-table of the first alternative name with the main table of the second alternative name.

4. The method according to claim 1, wherein The determining of a target field of the query field and the output field that is an array type includes: Determining a hierarchical relationship between the query field and at least one target field of array type in the output field and the at least one target field; Before generating a connection relationship for left-connecting the sub-table with the main table, the method further includes: determining a connection order of the at least one target field based on a hierarchical relationship between the at least one target field, wherein the target field has an earlier hierarchy and an earlier connection order; The step of taking the target field as a sub-table and the target table as a main table and generating a connection relationship for left-joining the sub-table with the main table includes: With the target table as the main table, a connection relationship is generated in accordance with the connection order, for sequentially connecting each target field as a sub-table with the main table as a left connection.

5. The method according to claim 4, characterized in that The query condition information indicates at least one query condition group and a logical relationship between the query condition groups, wherein the query condition group includes at least one query condition and a logical relationship between the at least one query condition; The step of using the connection table represented by the connection relationship as a table to be queried in a structured query language SQL query statement, combining the query conditions and output fields, and constructing the SQL query statement according to SQL syntax includes: The connection table represented by the connection relationship is used as the table to be queried in the structured query language SQL query statement, combined with the logical relationship between the query condition groups, the logical relationship between at least one query condition and at least one query condition in the query condition group, and the output field, and the SQL query statement is constructed according to the SQL grammar.

6. The method according to claim 1, characterized in that The step of determining query condition information for querying the target database includes: Display the condition configuration interface; Based on the configuration operation of the user in the condition configuration interface, the to-be-queried condition information configured by the user for the target database is obtained.

7. The method according to claim 1, characterized in that Before determining the query condition information for querying the target database, the method further includes: Determine the HBase database table to be converted; According to the data structure description specification of the data structure description language protobuf, the HBase database table is converted into a data set that conforms to the corresponding data structure of the data structure description language protobuf; According to a mapping specification between a data structure of the data structure description language protobuf and a data structure of a column storage format supporting a nested structure, the data set is converted into a database table using a column storage format supporting a nested structure.

8. A data query device, characterized in that: include: an information determination unit, configured to determine query condition information for querying a target database, wherein the query condition information indicates a target table to be queried, a query condition, and output fields to be output, wherein the query condition includes conditions that must be satisfied by the query fields to be matched, and the target database stores data in a columnar storage format that supports nested structures; a field determination unit, configured to determine a target field of an array type among the query field and the output field; a relationship generating unit, configured to generate a connection relationship for left-joining the sub-table to the main table, with the target field as the sub-table and the target table as the main table; A statement construction unit, configured to use the connection table represented by the connection relationship as a table to be queried in a structured query language SQL query statement, combine the query condition and the output field, and construct the SQL query statement according to the SQL grammar; A data query unit is used to query the target database based on the SQL query statement to query the target table in the target database for data matching the SQL query statement.

9. A computer device, characterized in that: including memory and processor; Wherein, the memory is used to store programs; The processor is used to execute the program, and when the program is executed, it is specifically used to implement the data query method according to any one of claims 1 to 7.

10. A computer-readable storage medium, characterized in that Used to store a program, which, when executed, is used to implement the data query method as described in any one of claims 1 to 7.

Images