Data query method and device, electronic equipment and storage medium

Abstract

The embodiment of the invention provides a data query method and device, electronic equipment and a storage medium, and relates to the technical field of databases. Query condition information assembled by a client side is obtained, the query condition information is tree structure information, the query condition information comprises multiple pieces of node information, all the node information is analyzed into sub-SQL, all the sub-SQL are assembled into an executable execution SQL statement, the execution SQL statement is input into a Clickhouse database for query, and a data query result is obtained. Therefore, rich screening conditions can be provided, required data can be queried through flexible and efficient real-time query, and then the real-time decision and response capability is improved.

Description

Technical Field

[0001] The present invention relates to the field of database technology, and in particular to a data query method, device, electronic equipment and storage medium. Background Art

[0002] As operations become increasingly sophisticated, data needs are no longer limited to conventional report data, such as active users, paying users, and user lifetime value, but are more focused on comprehensive data indicators of users who meet specific conditions or multiple conditions. To solve this problem, establishing a tag service system has become a solution, aiming to provide a variety of screening conditions to achieve detailed observation and analysis of users, and provide users with more accurate and personalized services.

[0003] The traditional tag service method is to correspond each attribute of the data object to a field in the database, and record the value corresponding to the object. By screening and filtering the field value, the data object is obtained from the database and applied to the business scenario.

[0004] However, the traditional method requires the expansion of fields in advance to collect and store information, and the data needs to be landed in advance, and the T+1 method is often used for data production. This results in low label production efficiency, a long production cycle, and the inability to update data in real time, which cannot guarantee the timeliness of data and limits real-time decision-making and response capabilities. Summary of the invention

[0005] In view of this, an object of embodiments of the present invention is to provide a data query method, device, electronic device and storage medium to at least partially improve the above-mentioned problem.

[0006] In order to achieve the above purpose, the technical solution adopted by the embodiment of the present invention is as follows:

[0007] In a first aspect, an embodiment of the present invention provides a data query method, the method comprising:

[0008] Acquire query condition information assembled by the client; the query condition information is tree structure information, and the query condition information includes a plurality of node information;

[0009] Parsing each of the node information into sub-SQL statements, and assembling each of the sub-SQL statements into an executable SQL statement;

[0010] The executed SQL statement is input into the Clickhouse database for query to obtain data query results.

[0011] Optionally, the node information includes a query title, a query condition and a query value, the type of the query title includes a field attribute, an SQL attribute and a label, the field attribute represents a field of a database table, the SQL attribute represents a pseudo SQL statement, and the label is composed of the field attribute and / or the SQL attribute; parsing each of the node information into sub-SQLs, and assembling each of the sub-SQLs into an executable SQL statement, includes:

[0012] Expand and flatten each of the node information to obtain a flat information set of each of the node information at the same level, and mark the parent-child node relationship of each of the node information;

[0013] Traversing the flat information set, constructing the corresponding sub-SQL for each node information according to the query title, query condition and query value of each node information, and adding each sub-SQL to the corresponding node information;

[0014] According to the relationship between the parent and child nodes of each node information, the flat information set is reassembled into a tree structure to obtain the child SQL tree structure information;

[0015] According to the structural relationship of the SQL tree structure information, the sub-SQL tree structure information is parsed and reassembled to generate an executable SQL statement.

[0016] Optionally, the label includes at least one label value; the step of expanding and flattening each of the node information to obtain a flat information set of each of the node information at the same level, and marking the parent-child node relationship of each of the node information, includes:

[0017] For any of the node information, when the type of the query title of the node information is a tag, and the tag includes multiple tag values, the node information is expanded, the node information is split into multiple node information, and the multiple node information are associated with each other in an "or" logical relationship;

[0018] An ID generator is used to add a unique ID number and a parent node ID number to each of the node information, and each of the node information is added to the flat information set.

[0019] Optionally, constructing the corresponding sub-SQL for each node information according to the query title, query condition and query value of each node information includes:

[0020] When the type of the query title of the node information is a field attribute, a corresponding sub-SQL is constructed according to the field, query condition and query value of the database table corresponding to the query title;

[0021] When the type of the query title of the node information is an SQL attribute, find out the pseudo SQL statement corresponding to the query title, replace the corresponding part in the pseudo SQL statement with the query condition and the query value, and construct the corresponding sub-SQL;

[0022] When the type of the query title of the node information is a label, the corresponding field attributes and / or SQL attributes are found, and the corresponding sub-SQL is constructed according to the found field attributes and / or SQL attributes, the query conditions and the query values.

[0023] Optionally, each node of the sub-SQL tree structure information is a logical node or a leaf node, and the sub-SQL tree structure information is parsed and reassembled according to the structural relationship of the SQL tree structure information to generate an executable SQL statement, including:

[0024] Recursively traverse the sub-SQL tree structure information, and when the node is a logical node, continue to parse the lower layer; when the node is a leaf node, parse the sub-SQL in the node to obtain the table alias used by each sub-SQL when actually executing the query;

[0025] Replace the table name of each sub-SQL with the corresponding table alias, and assemble each sub-SQL into an executable SQL statement;

[0026] When each sub-SQL in the execution SQL statement contains the same table name, the where condition parts of the sub-SQL with the same table name are merged;

[0027] When there are more than two sub-SQL statements containing the same table name, first merge two of them, and then merge with the next sub-SQL statement in sequence.

[0028] Optionally, the parsing of the sub-SQL in the node to obtain the table alias used by each sub-SQL when actually executing the query includes:

[0029] Analyze the sub-SQL using a database toolkit to obtain the actual table name used in the query;

[0030] When the sub-SQL has aggregation logic or a sub-query appears in an "or" relationship, a timestamp is added after the actual table name to obtain a table alias.

[0031] Optionally, the method further comprises:

[0032] Generate a query ID according to the SQL statement, and record the query ID and the SQL statement;

[0033] During the execution of the SQL statement, the obtained data query results are directly stored in the Clickhouse database.

[0034] In a second aspect, an embodiment of the present invention provides a data query device, the device comprising:

[0035] A query condition information acquisition unit, used to acquire query condition information assembled by the client; the query condition information is tree structure information, and the query condition information includes a plurality of node information;

[0036] An SQL statement generating unit, used for parsing each of the node information into sub-SQLs, and assembling each of the sub-SQLs into an executable SQL statement;

[0037] The data query unit is used to input the execution SQL statement into the Clickhouse database for query and obtain the data query result.

[0038] In a third aspect, an embodiment of the present invention provides an electronic device, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor implements any of the above-described methods when executing the program.

[0039] In a fourth aspect, an embodiment of the present invention provides a storage medium having a computer program stored thereon, wherein the computer program implements any of the above-described methods when executed by a processor.

[0040] The data query method, device, electronic device and storage medium provided by the embodiment of the present invention obtain query condition information assembled by the client, assemble the query condition information into executable SQL statements, and use the Clickhouse database to query data. Thus, it can provide a variety of screening conditions, query the required data through flexible and efficient real-time query capabilities, and thus improve real-time decision-making and response capabilities.

[0041] In order to make the above-mentioned objects, features and advantages of the present application more obvious and easy to understand, preferred embodiments are specifically cited below and described in detail with reference to the attached drawings. BRIEF DESCRIPTION OF THE DRAWINGS

[0042] In order to more clearly illustrate the technical solutions of the embodiments of the present invention, the following briefly introduces the drawings required for use in the embodiments. It should be understood that the following drawings only show certain embodiments of the present invention and therefore should not be regarded as limiting the scope. For ordinary technicians in this field, other related drawings can be obtained based on these drawings without creative work.

[0043] Figure 1A schematic structural block diagram of an electronic device provided by an embodiment of the present invention;

[0044] Figure 2 A flowchart of a data query method provided by an embodiment of the present invention;

[0045] Figure 3 A schematic diagram of a client query condition information assembly interface provided by an embodiment of the present invention;

[0046] Figure 4 Another schematic diagram of a flow chart of a data query method provided by an embodiment of the present invention;

[0047] Figure 5 A schematic structural block diagram of a data query device provided in an embodiment of the present invention.

[0048] Icon: 100 - electronic device; 101 - memory; 102 - communication interface; 103 - processor; 104 - bus; 300 - data query device; 310 - query condition information acquisition unit; 320 - SQL statement generation unit; 330 - data query unit. DETAILED DESCRIPTION

[0049] In order to make the purpose, technical solutions and advantages of the embodiments of the present invention clearer, the technical solutions in the embodiments of the present invention will be clearly and completely described below in conjunction with the drawings in the embodiments of the present invention. Obviously, the described embodiments are part of the embodiments of the present invention, not all of the embodiments. Generally, the components of the embodiments of the present invention described and shown in the drawings here can be arranged and designed in various different configurations.

[0050] Therefore, the following detailed description of the embodiments of the present invention provided in the accompanying drawings is not intended to limit the scope of the invention claimed for protection, but merely represents selected embodiments of the present invention. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.

[0051] It should be noted that similar reference numerals and letters represent similar items in the following drawings, so once an item is defined in one drawing, it does not need to be further defined and explained in the subsequent drawings. At the same time, in the description of the present invention, the terms "first", "second", etc. are only used to distinguish the description and cannot be understood as indicating or implying relative importance.

[0052] It should be noted that, in this article, relational terms such as first and second, etc. are only used to distinguish one entity or operation from another entity or operation, and do not necessarily require or imply any such actual relationship or order between these entities or operations. Moreover, the terms "include", "comprise" or any other variants thereof are intended to cover non-exclusive inclusion, so that a process, method, article or device including a series of elements includes not only those elements, but also other elements not explicitly listed, or also includes elements inherent to such process, method, article or device. In the absence of further restrictions, the elements defined by the sentence "comprise a ..." do not exclude the existence of other identical elements in the process, method, article or device including the elements.

[0053] As operations become increasingly sophisticated, data needs are no longer limited to conventional report data, such as active users, paying users, and user lifetime value, but are more focused on comprehensive data indicators of users who meet specific conditions or multiple conditions. To solve this problem, establishing a tag service system has become a solution, aiming to provide a variety of screening conditions to achieve detailed observation and analysis of users, and provide users with more accurate and personalized services.

[0054] The traditional tag service method is to correspond each attribute of the data object to a field in the database, and record the value corresponding to the object. By screening and filtering the field value, the data object is obtained from the database and applied to the business scenario.

[0055] There are two main ways to store tag data:

[0056] Wide table storage method: This method stores the tag data in the database in the form of a large wide table by horizontally expanding the table fields. Each attribute corresponds to a field in the database, which records the value of the corresponding tag. This storage method can easily perform screening and filtering operations based on field values, providing higher query efficiency.

[0057] Map storage method: vertical expansion storage is performed in the form of key-value pairs (Map). Tag data is stored in the database in the form of key-value pairs, where the key represents the attribute name and the value represents the value of the corresponding tag. This storage method is suitable for situations where there are a large number of attributes and they change dynamically. It can easily add, delete and modify attributes, providing high flexibility and scalability.

[0058] However, the traditional label service method has some disadvantages, mainly including:

[0059] 1. Storage space requirements: The traditional method requires data to be stored in advance, which takes up a lot of storage space. As the number of data objects and tags increases, more and more stored data accumulates, increasing storage costs and maintenance complexity.

[0060] 2. Inefficient label production: The traditional method requires the expansion of fields in advance to collect and store information, resulting in low label production efficiency. The entire production cycle is long and requires a lot of time and resources.

[0061] 3. Data latency: Since data needs to be delivered in advance, data production is often done in a T+1 manner. This means that data cannot be updated in real time, and the timeliness of data cannot be guaranteed, which limits real-time decision-making and response capabilities.

[0062] Based on the above situation, the embodiments of the present invention provide a data query method, device, electronic device and storage medium, which obtain query conditions, assemble the query conditions into a large SQL statement, and use the Clickhouse database to query and obtain data query results. The SQL direct query method effectively avoids the storage of too much label data, reduces the data storage capacity and development cost, and can query and update the data in real time.

[0063] To implement the process steps and functions of each example of the present invention, please refer to Figure 1 , Figure 1 A schematic structural block diagram of an electronic device provided in an embodiment of the present invention. The electronic device 100 includes a memory 101 and a processor 103, and the memory 101 and the processor 103 are directly or indirectly electrically connected to realize data transmission or interaction. For example, these elements can be electrically connected to each other through one or more communication buses 104 or signal lines. The memory 101 can be used to store software programs and modules, and the processor 103 executes various functional applications and data processing by executing the software programs and modules stored in the memory 101.

[0064] The electronic device 100 may be, but is not limited to, a personal computer (PC), a server, a distributed computer, etc. It is understandable that the electronic device 100 is not limited to a physical server, but may also be a virtual machine on a physical server, a virtual machine built on a cloud platform, etc., which can provide the same functions as the server or virtual machine. The operating system of the electronic device 100 may be, but is not limited to, a Windows system, a Linux system, etc.

[0065] Among them, the memory 101 can be, but is not limited to, a random access memory (RAM), a read only memory (ROM), a programmable read-only memory (PROM), an erasable programmable read-only memory (EPROM), an electrically erasable read-only memory (EEPROM), etc.

[0066] The communication connection between the electronic device 100 and an external device is achieved through at least one communication interface 102 (which can be wired or wireless).

[0067] The processor 103 may be an integrated circuit chip with signal processing capabilities. In the implementation process, each step of the embodiment of the present invention can be completed by the hardware integrated logic circuit in the processor 103 or the instructions in the form of software. The processor 103 can be a general-purpose processor, including a central processing unit (CPU), a network processor (NP), etc.; it can also be a digital signal processor (DSP), an application specific integrated circuit (ASIC), a field-programmable gate array (FPGA) or other programmable logic devices, discrete gates or transistor logic devices, discrete hardware components.

[0068] Understandably, Figure 1 The structure shown is for illustration only. The electronic device 100 may also include Figure 1 More or fewer components as shown, or with Figure 1 Different configurations are shown. Figure 1 Each component shown in the figure can be implemented by hardware, software or a combination thereof.

[0069] The following is an exemplary description of a data query method provided by the present invention. Specifically, Figure 2 A flowchart of a data query method provided by an embodiment of the present invention is shown in FIG. Figure 2 , the execution subject of this method can be the above Figure 1 The electronic device 100 shown in FIG. 1 includes: Figure 2 The following steps are described:

[0070] S210: Acquire query condition information assembled by the client, wherein the query condition information is tree structure information, and the query condition information includes a plurality of node information.

[0071] The client can be a front-end web interface, see Figure 3 , Figure 3 This is a schematic diagram of a client query condition information assembly interface provided by an embodiment of the present invention, and an operator can construct corresponding query condition information according to their own needs. The query condition information is sent to a backend service, and the method obtains the query condition information.

[0072] S220: Parse each node information into sub-SQLs, and assemble each sub-SQL into an executable SQL statement.

[0073] Each node information contains information that can construct a sub-SQL. Through this information, each node information is parsed into a sub-SQL. Then, through the relationship between each node information, each sub-SQL is assembled into an executable SQL statement.

[0074] S230: Input the executed SQL statement into the Clickhouse database for query to obtain data query results.

[0075] Usually, the data that operators need to query needs to meet a lot of conditions, which causes this type of query to execute SQL statements to put a lot of pressure on the database. For a normal amount of data, such as MySQL, it is difficult to execute this type of SQL statement, which may cause query timeouts, database table locks, and excessive CPU load. Therefore, business data can be stored in the Clickhouse database, and Clickhouse's powerful query capabilities can be used to execute SQL at one time, perform calculations in the database memory, query the required results, and return them to the client for use.

[0076] This method obtains the query condition information assembled by the client, assembles the query condition information into an executable SQL statement, and queries the Clickhouse database to obtain data query results, thereby providing rich screening conditions and querying the required data in real time and efficiently.

[0077] If this method uses an ordinary database, the query efficiency is not as high as the query efficiency of the pre-implemented label data. However, the query capability of Clickhouse can make up for this deficiency. It can greatly improve the efficiency of label generation and reduce storage costs while meeting the query efficiency.

[0078] Furthermore, the node information may include a query title, a query condition, and a query value. Figure 3 , the query title is date of birth, the query condition is equal to, and the query value is 2024-07-10, where the query title type includes field attributes, SQL attributes, and tags.

[0079] The field attribute represents a field in the database table. It comes from a field in the database table and can be a direct value stored directly or obtained by aggregation; for example, the recharge amount in the recharge table and the registered IP in the role table.

[0080] First, you need to create field attributes, which can include the name, the rules used, the corresponding database table, field, function, and special processing logic; among them, the table and field are displayed by scanning the database meta information drop-down, so that we can add any table\field on the DB and display it immediately, which improves the efficiency of label production.

[0081] SQL attributes represent a pseudo-SQL statement, define a pseudo-SQL statement, and embed screening variables in the pseudo-SQL; when used, the variable part is interpreted and replaced to form a complete SQL for screening. SQL attributes are mainly used to solve some complex scenario problems such as field attributes cannot be associated with data business across databases and cannot use aggregate values ​​based on existing data; for example, roles with a total recharge amount greater than a specified value within a time period, and all roles created by users whose real-name authentication location is Suzhou, are affected by multiple restrictions. In this case, simple field attributes cannot describe it.

[0082] Tags are composed of field attributes and / or SQL attributes. Tags can also be divided into attribute tags and SQL tags.

[0083] Attribute tags are tags composed of attributes (field attributes and SQL attributes). Attributes can form an infinite level of tree. Attributes can only be leaf nodes, and non-leaf nodes can only be logical nodes (AND, OR).

[0084] SQL tags provide tags that can be directly applied to SQL definitions in order to solve problems such as field attribute composition tags causing performance to fall short of expectations and business complexity leading to overly complex field attribute splicing.

[0085] Among them, a tag can include at least one tag value. The tag value is an executable logic formed after the tag determines the operator and fills in the specific value, corresponding to a clear result set. For example, a tag such as user value stratification will have tag values ​​such as high value, medium value, and low value. The tag value is only used as a shortcut to identify the business when the tag is finally used, that is, when the operator enters the query conditions on the client, the tag value is used quickly. The tag value must correspond to a specific tag, and the tag can have several tag values.

[0086] There are many ways to assemble and execute SQL statements. In order to quickly assemble SQL statements, see Figure 4 , Figure 4 Another flow chart of a data query method provided by an embodiment of the present invention, the above step S220 may include the following: Figure 4 The following steps are shown:

[0087] S221: Expand and flatten each node information to obtain a flat information set of each node information at the same level, and mark the relationship between the parent and child nodes of each node information.

[0088] After obtaining the query condition information in a tree structure, the tree structure is first expanded and flattened into a flat information set in a flat structure, such as a list, a one-dimensional array, etc.

[0089] Since there is a shortcut for label values, for any node information, if the type of the query title of the node information is label, and the label includes multiple label values, such as high value, medium value, and low value, the label value can be separated, the node information can be expanded, and the node information can be split into multiple node information and associated with each other with an "or" logical relationship.

[0090] To mark the parent-child node relationship of each node information, an ID generator can be used to add a unique ID number and a parent node ID number to each node information, and each node information is added to a flat information set.

[0091] S222: traverse the flat information set, construct corresponding sub-SQL for each node information according to the query title, query condition and query value of each node information, and add each sub-SQL to the corresponding node information.

[0092] The method of constructing sub-SQL can be processed separately according to the type of query title. Constructing the corresponding sub-SQL from each node information may include:

[0093] When the type of the query title of the node information is a field attribute, a corresponding sub-SQL is constructed according to the field of the database table corresponding to the query title, the query condition and the query value.

[0094] When the type of the query title of the node information is an SQL attribute, a pseudo SQL statement corresponding to the query title is found, and corresponding parts of the pseudo SQL statement are replaced with query conditions and query values ​​to construct a corresponding sub-SQL.

[0095] When the type of the query title of the node information is a label, the corresponding field attributes and / or SQL attributes are found, and the corresponding sub-SQL is constructed according to the found field attributes and / or SQL attributes, the query conditions and the query values.

[0096] When parsing field attributes, the definition information of the attributes is read, and the simplest filter statement can be formed by string concatenation, such as: Select ID From Table_A WHERE Field_X (query condition) (query value). There can also be snapshot time, function enhancement, field enhancement, desensitization and other businesses; all practices are based on the filter statement, and the complete SQL statement we expect is formed by string concatenation. For example:

[0097] Scenario 1: Direct conversion of common fields:

[0098] select roleid from roles where${create#date}.

[0099] Scenario 2: For processing snapshot tables, it is necessary to add a fixed snapshot date and provide variable maintenance:

[0100] select roleid from roles where date=${snapshotDate#snapshot date)andforce${force#character's latest combat power}.

[0101] Scenario 3: Aggregate functions:

[0102] select roleid from imprest group by roleid having sum(cash)${cash_all#accumulated recharge amount}.

[0103] Scenario 4: Function processing needs to be added to the field:

[0104] select roleid from roles where dateDiff(now(),create)${open_days#Number of days to open the service}.

[0105] In the above scenario, ${create#date} is a preset placeholder, which will eventually be replaced with the query condition and query value to form a standard SQL statement so that it can be executed by the database. For example, select roleid from roles where ${create#date} will be converted to select roleid from roles where create>'2024-01-01', which can be executed.

[0106] The SQL attribute is a pseudo SQL statement with filter variables embedded in it. When parsing, the filter variables can be directly replaced with query conditions and query values ​​to form a complete SQL statement.

[0107] A tag is composed of multiple field attributes and / or SQL attributes. When parsing a tag, the corresponding field attributes and / or SQL attributes are found, and the parsing is performed according to the above field attributes and SQL data parsing method to construct the corresponding sub-SQL.

[0108] S223: According to the relationship between the parent and child nodes of each node information, the flat information set is reassembled into a tree structure to obtain the child SQL tree structure information.

[0109] S224: According to the structural relationship of the SQL tree structure information, the sub-SQL tree structure information is parsed and reassembled to generate an executable SQL statement.

[0110] Each node of the sub-SQL tree structure information can be a logical node or a leaf node. The logical node contains two logical relationships of "and" and "or". It is the parent node of the leaf node, and the leaf node is the specific information of the sub-SQL. The assembly method can directly assemble the sub-SQL tree structure information into an executable SQL statement by recursive loop. Preferably, each sub-SQL can also be optimized and assembled. Step S224 may include:

[0111] S2241: traverse the sub-SQL tree structure information in a recursive manner, and when the node is a logical node, continue to parse the lower layer; when the node is a leaf node, parse the sub-SQL in the node to obtain the actual table used by each sub-SQL when actually executing the query.

[0112] The sub-SQL can be parsed using the Druid toolkit, or any database toolkit can be used to analyze the sub-SQL statement. After analysis, the actual table name used in the query can be obtained.

[0113] In order to distinguish the query table names and avoid incorrect table name merging events, the sub-SQL in the node is parsed to obtain the actual table names used by each sub-SQL when executing the query, which may include:

[0114] Use the database toolkit to analyze the sub-SQL and obtain the actual table name used in the query;

[0115] When the sub-SQL contains aggregation logic or a sub-query appears in an "or" relationship, a timestamp is added after the actual table name to obtain the table alias.

[0116] First, use the database tool to obtain the actual table name used in the sub-SQL query, and then further process the actual table name. In some special cases, the same actual table name needs to be processed. For example, in the sub-SQL, the "or" condition also includes the sub-query, and its table name is the same. In this case, the two table names cannot be merged. Therefore, the two need to be distinguished. You can add a timestamp after the actual table name to distinguish the actual table name of the query. The purpose is to generate a table alias to make it unique and not repeated with other tables; avoid some unnecessary SQL merge logic, thereby avoiding incorrect merge events.

[0117] S2242: Replace the table name of each sub-SQL with the corresponding actual table name, and assemble each sub-SQL into an executable SQL statement.

[0118] S2243: When each sub-SQL in the executed SQL statement contains the same table name, the where condition parts of the sub-SQL with the same table name are merged.

[0119] In order to improve data query efficiency, when each sub-SQL in the executed SQL statement contains the same table name, SQL merging can be performed to avoid excessive repeated table scanning, thereby improving query efficiency.

[0120] Specifically, process the sub-SQL statements with the same table alias, extract the where condition part of each SQL statement, and merge them. At the same time, enclose the where condition in parentheses to increase the priority of the syntax. Otherwise, due to the and\or priority strategy of SQL itself, the semantics of the query will change. For example:

[0121] SubSQL_X:select roleid from roles where sex='1'.

[0122] SubSQL_Y:select roleid from roles where age='20'.

[0123] SQL after merging: select roleid from roles where (sex='1'and age='20').

[0124] S2244: When there are more than two sub-SQLs containing the same table name, first merge two of the sub-SQLs, and then merge with the next sub-SQL in sequence.

[0125] Each time processing is performed, the two sub-SQL statements are merged and then merged with the third sub-SQL statement. This allows the where part to be modified with parentheses each time the statement is merged, thus maintaining the correct semantics.

[0126] After the sub-SQL is processed, the sub-SQL is assembled to generate an executable SQL statement. When the node information is in an "and" relationship, it is assembled through "inner join"; for example, level>10 and gender='male', in this case, the assembled SQL is:

[0127] "SELECT t2.roleid

[0128] FROM(

[0129] SELECT roleid

[0130] FROM roles

[0131] WHERE 1=1

[0132] AND sex='1'

[0133] )t2

[0134] INNER JOIN(

[0135] SELECT roleid

[0136] FROM roles

[0137] WHERE 1=1

[0138] AND toInt32(lv)>'10'

[0139] )t3 USING(roleid)".

[0140] Concatenate two SQL statements through inner join to form a new executable SQL statement.

[0141] When the node information is in an "or" relationship, the system assembles it through "union distinct"; for example, level>10 or gender='male', in this case, the assembled SQL is:

[0142] "SELECT roleid

[0143] FROM(

[0144] SELECT roleid

[0145] FROM roles

[0146] WHERE 1=1

[0147] AND sex='1'

[0148] UNION DISTINCT

[0149] SELECT roleid

[0150] FROM roles

[0151] WHERE 1=1

[0152] AND toInt32(lv)>'10'

[0153] )t2uniont3”.

[0154] Use union distinct to concatenate two SQL statements to form a new SQL statement.

[0155] The join of three tables takes a very long time. Therefore, the combination method can also be "first inner join the two tables, and then use them as subqueries to inner join with another SQL", which is more efficient.

[0156] Furthermore, after assembling SQL to query and obtain data, in order to facilitate the next query with the same query conditions, the query conditions and query results can be recorded and directly used in the next query. Therefore, the data query method can also include:

[0157] Generate queryId based on the executed SQL statement, and record the queryId and the executed SQL statement.

[0158] During the execution of the SQL statement, the obtained data query results are directly stored in the Clickhouse database.

[0159] After recording the queryId and the executed SQL statements, the data results are stored in the database during the query process. Specifically, insert into select xx limit xx is used to perform the insertion; the result set is directly stored in Clickhouse. In the conventional practice, select count() is used to count the number first, and then the result set is checked; because that actually needs to be executed twice, the database level needs to scan the table twice, and the query consumption is relatively high; the result set is directly inserted through insert intoselect id from table limit 0,10000000; during the execution process, the SQL query logic is really slow, while insert into select is inserted directly through memory, and the insertion speed is very fast; there is not much difference in execution efficiency, because our result set has a simple structure and CK writes very quickly; at the same time, one query is reduced, which also improves query efficiency.

[0160] Preferably, a layer of elimination strategy can be implemented on the result set landing data, for example, the result set is automatically cleared after 1 month; there is no need to occupy storage space for a long time.

[0161] Furthermore, the embodiment of the present invention also provides a data query device 300. For details, see Figure 5 , Figure 5 A schematic structural block diagram of a data query device provided by an embodiment of the present invention, the data query device 300 includes:

[0162] The query condition information acquisition unit 310 is used to acquire the query condition information assembled by the client; the query condition information is tree structure information, and the query condition information includes a plurality of node information.

[0163] The SQL statement generating unit 320 is used to parse each node information into sub-SQLs, and assemble each sub-SQL into an executable SQL statement.

[0164] The data query unit 330 is used to input the execution SQL statement into the Clickhouse database for query and obtain the data query result.

[0165] In summary, the data query method, device, electronic device and storage medium provided by the embodiment of the present invention obtain the query condition information of the tree structure assembled by the client, process the query condition information, obtain the sub-SQL and other information of each node information of the query condition information, merge and optimize the sub-SQL, assemble the sub-SQL into an executable SQL statement, and finally query the Clickhouse database to obtain the data query result. Thus, it can provide flexible and efficient query capabilities and screening conditions, query the required data in real time and efficiently, and thus improve the real-time decision-making and response capabilities.

[0166] In the embodiments provided by the present invention, it should be understood that the disclosed devices and methods can also be implemented in other ways. The device embodiments described above are merely schematic. For example, the flowcharts and block diagrams in the accompanying drawings show the possible architecture, functions and operations of the devices, methods and computer program products according to multiple embodiments of the present invention. In this regard, each box in the flowchart or block diagram can represent a module, a program segment or a part of the code, and a module, a program segment or a part of the code contains one or more executable instructions for implementing the specified logical functions. It should also be noted that in some alternative implementations, the functions marked in the box can also occur in a different order from the order marked in the accompanying drawings. For example, two consecutive boxes can actually be executed substantially in parallel, and they can sometimes be executed in the opposite order, depending on the functions involved. It should also be noted that each box in the block diagram and / or flowchart, and the combination of boxes in the block diagram and / or flowchart can be implemented with a dedicated hardware-based system that performs a specified function or action, or can be implemented with a combination of dedicated hardware and computer instructions.

[0167] In addition, the functional modules in the various embodiments of the present invention may be integrated together to form an independent part, or each module may exist independently, or two or more modules may be integrated to form an independent part.

[0168] If the function is implemented in the form of a software function module and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of the present invention, or the part that contributes to the prior art or the part of the technical solution, can be embodied in the form of a software product, which is stored in a computer-readable storage medium and includes several instructions for a computer device (which can be a personal computer, server, or network device, etc.) to perform all or part of the steps of the methods of each embodiment of the present invention. The aforementioned storage medium includes: U disk, mobile hard disk, read-only memory (ROM, Read-Only Memory), random access memory (RAM, Random Access Memory), disk or optical disk, etc. Various media that can store program codes.

[0169] The above are only preferred embodiments of the present invention and are not intended to limit the present invention. For those skilled in the art, the present invention may have various modifications and variations. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of the present invention shall be included in the protection scope of the present invention.

[0170] It will be apparent to those skilled in the art that the invention is not limited to the details of the exemplary embodiments described above and that the invention can be implemented in other specific forms without departing from the spirit or essential features of the invention. Therefore, the embodiments should be considered exemplary and non-limiting in all respects, and the scope of the invention is defined by the appended claims rather than the foregoing description, and it is intended that all variations falling within the meaning and scope of the equivalent elements of the claims be included in the invention. Any reference numeral in a claim should not be considered as limiting the claim to which it relates.

Claims

1. A data query method, characterized in that: The method comprises: Acquire query condition information assembled by the client; the query condition information is tree structure information, and the query condition information includes a plurality of node information; Parsing each of the node information into sub-SQL statements, and assembling each of the sub-SQL statements into an executable SQL statement; The executed SQL statement is input into the Clickhouse database for query to obtain data query results.

2. The method according to claim 1, characterized in that: The node information includes a query title, a query condition and a query value. The type of the query title includes a field attribute, an SQL attribute and a label. The field attribute represents a field of a database table. The SQL attribute represents a pseudo SQL statement. The label is composed of the field attribute and / or the SQL attribute. The parsing of each node information into sub-SQLs and assembling each sub-SQL into an executable SQL statement includes: Expand and flatten each of the node information to obtain a flat information set of each of the node information at the same level, and mark the parent-child node relationship of each of the node information; Traversing the flat information set, constructing the corresponding sub-SQL for each node information according to the query title, query condition and query value of each node information, and adding each sub-SQL to the corresponding node information; According to the relationship between the parent and child nodes of each node information, the flat information set is reassembled into a tree structure to obtain the child SQL tree structure information; According to the structural relationship of the SQL tree structure information, the sub-SQL tree structure information is parsed and reassembled to generate an executable SQL statement.

3. The method according to claim 2, characterized in that The label includes at least one label value; the node information is expanded and flattened to obtain a flat information set of the node information at the same level, and the parent-child node relationship of each node information is marked, including: For any of the node information, when the type of the query title of the node information is a tag, and the tag includes multiple tag values, the node information is expanded, the node information is split into multiple node information, and the multiple node information are associated with each other in an "or" logical relationship; An ID generator is used to add a unique ID number and a parent node ID number to each of the node information, and each of the node information is added to the flat information set.

4. The method according to claim 2, characterized in that: The step of constructing the corresponding sub-SQL for each node information according to the query title, query condition and query value of each node information includes: When the type of the query title of the node information is a field attribute, a corresponding sub-SQL is constructed according to the field, query condition and query value of the database table corresponding to the query title; When the type of the query title of the node information is an SQL attribute, find out the pseudo SQL statement corresponding to the query title, replace the corresponding part in the pseudo SQL statement with the query condition and the query value, and construct the corresponding sub-SQL; When the type of the query title of the node information is a label, the corresponding field attributes and / or SQL attributes are found, and the corresponding sub-SQL is constructed according to the found field attributes and / or SQL attributes, the query conditions and the query values.

5. The method according to claim 2, characterized in that: Each node of the sub-SQL tree structure information is a logical node or a leaf node. According to the structural relationship of the SQL tree structure information, the sub-SQL tree structure information is parsed and reassembled to generate an executable SQL statement, including: Recursively traverse the sub-SQL tree structure information, and when the node is a logical node, continue to parse the lower layer; when the node is a leaf node, parse the sub-SQL in the node to obtain the table alias used by each sub-SQL when actually executing the query; Replace the table name of each sub-SQL with the corresponding table alias, and assemble each sub-SQL into an executable SQL statement; When each sub-SQL in the execution SQL statement contains the same table name, the where condition parts of the sub-SQL with the same table name are merged; When there are more than two sub-SQL statements containing the same table name, first merge two of them, and then merge with the next sub-SQL statement in sequence.

6. The method according to claim 5, characterized in that The sub-SQL in the node is parsed to obtain the table alias used by each sub-SQL when actually executing the query, including: Analyze the sub-SQL using a database toolkit to obtain the actual table name used in the query; When the sub-SQL has aggregation logic or a sub-query in an "or" relationship, a timestamp is added after the actual table name to obtain a table alias.

7. The method according to claim 1, characterized in that The method further comprises: Generate a query ID according to the SQL statement, and record the query ID and the SQL statement; During the execution of the SQL statement, the obtained data query results are directly stored in the Clickhouse database.

8. A data query device, characterized in that: The device comprises: A query condition information acquisition unit, used to acquire query condition information assembled by the client; the query condition information is tree structure information, and the query condition information includes a plurality of node information; An SQL statement generating unit, used for parsing each of the node information into sub-SQLs, and assembling each of the sub-SQLs into an executable SQL statement; The data query unit is used to input the execution SQL statement into the Clickhouse database for query and obtain the data query result.

9. An electronic device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that: When the processor executes the program, the method according to any one of claims 1 to 7 is implemented.

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

Images