CN113760949B - Data query method and device - Google Patents
Data query method and device Download PDFInfo
- Publication number
- CN113760949B CN113760949B CN202110251343.5A CN202110251343A CN113760949B CN 113760949 B CN113760949 B CN 113760949B CN 202110251343 A CN202110251343 A CN 202110251343A CN 113760949 B CN113760949 B CN 113760949B
- Authority
- CN
- China
- Prior art keywords
- instruction
- hit
- sql
- template file
- rule
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Active
Links
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/242—Query formulation
- G06F16/2433—Query languages
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/27—Replication, distribution or synchronisation of data between databases or within a distributed database system; Distributed database system architectures therefor
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Physics & Mathematics (AREA)
- Databases & Information Systems (AREA)
- Data Mining & Analysis (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Mathematical Physics (AREA)
- Computational Linguistics (AREA)
- Computing Systems (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention discloses a data query method and a data query device, and relates to the technical field of computers. One embodiment of the method comprises the following steps: generating an SQL statement with a hint instruction according to a predefined hint instruction rule; the hit instruction rule includes: the instruction identification and the corresponding template file; the hit instruction comprises an instruction identification; extracting a hit instruction from the SQL sentence, acquiring a template file corresponding to the hit instruction according to an instruction identifier of the hit instruction, and rewriting the SQL sentence according to the template file; and inquiring data according to the rewritten sentence. According to the embodiment, different hit instructions can be written according to the predefined hit instruction rule, declarative codes can be used for replacing instruction codes in the program coding process, the codes are decoupled, query performance is improved, grammar supported by an SQL query mode is expanded, and expandability of the data query mode is improved.
Description
Technical Field
The present invention relates to the field of computer technologies, and in particular, to a method and an apparatus for querying data.
Background
The elastic search (hereinafter referred to as ES) is an efficient distributed storage engine, supports near real-time query capability for mass data, and is widely used in a big data real-time OLAP (Online Analytical Processing, online analysis processing) scenario. ES authorities support query approaches based on DSL (Domain Specific Language ) and SQL (Structured Query Language, structured query language) grammars.
The existing ES-SQL component has the following disadvantages: the dimension index query is not supported, dimension switching is needed to be realized in the program coding, and the code expandability is poor; control instructions that do not support the query phase: such as conditional routing queries, cache control, whether the query results need to be cached, etc.
Disclosure of Invention
In view of this, the embodiments of the present invention provide a method and apparatus for querying data, which write different hit instructions according to predefined hit instruction rules, so that declarative encoding can be used to replace instruction encoding in the program encoding process, and the code can be decoupled, thereby improving the querying performance, expanding the grammar supported by the SQL querying mode, and improving the expandability of the data querying mode.
To achieve the above object, according to an aspect of an embodiment of the present invention, there is provided a data query method, including:
generating an SQL statement with a hit instruction according to a predefined hit instruction rule; the hit instruction rule includes: the instruction identification and the corresponding template file; the hit instruction comprises an instruction identification;
extracting a hit instruction from the SQL sentence, acquiring a template file corresponding to the hit instruction according to an instruction identifier of the hit instruction, and rewriting the SQL sentence according to the template file;
and inquiring data according to the rewritten sentence.
Optionally, after extracting the hit instruction from the SQL statement, the method further includes: determining an instruction type of the hit instruction; the instruction type is a routing instruction or a control instruction;
rewriting the SQL sentence according to the template file, including: if the hit instruction is a routing instruction, rewriting the SQL statement according to the template file; if the hit instruction is a control instruction of a query class, generating a DSL statement according to the template file; and if the hit instruction is a control instruction of a result type, acquiring a control rule of the control instruction according to the template file, and binding the control rule with the SQL statement.
Optionally, the hit instruction includes an instruction value; rewriting the SQL sentence according to the template file, including:
positioning a target index in the SQL sentence according to the template file, and replacing the target index with the instruction value to obtain the rewritten SQL sentence; and/or the number of the groups of groups,
and determining the query conditions in the SQL sentence, and merging the query conditions in the SQL sentence or adjusting the sequence of the query conditions according to the instruction values and the template file to obtain the rewritten SQL sentence.
Optionally, the hit instruction rule includes: an instruction format rule;
after extracting the hit instruction from the SQL statement, the method further comprises the following steps: checking the SQL sentence to determine that the grammar of the SQL sentence is correct; and/or checking the hit instruction according to the instruction format rule to determine that the hit instruction is correctly formatted.
Optionally, the hit instruction rule includes: instruction parameter rules;
before the template file corresponding to the hit instruction is obtained from the template configuration file according to the instruction identification of the hit instruction, the method further comprises: and checking the hit instruction according to the instruction parameter rule to determine that the hit instruction parameter is correct.
Optionally, before generating the SQL statement with the hit instruction according to the predefined hit instruction rule, the method further includes: writing the hit instruction rule into the etcd or zookeeper cluster.
Optionally, after writing the hit instruction rule into the etcd or zookeeper cluster, the method further includes: the hit instruction rule is updated based on a registration mechanism.
According to still another aspect of an embodiment of the present invention, there is provided a data query apparatus including:
the query statement generation module generates an SQL statement with a hit instruction according to a predefined hit instruction rule; the hit instruction rule includes: the instruction identification and the corresponding template file; the hit instruction comprises an instruction identification;
the query statement rewriting module extracts a hit instruction from the SQL statement, acquires a template file corresponding to the hit instruction according to an instruction identifier of the hit instruction, and rewrites the SQL statement according to the template file;
and the query statement execution module queries data according to the rewritten statement.
Optionally, the query statement rewrite module is further configured to: after extracting a hit instruction from the SQL statement, determining the instruction type of the hit instruction; the instruction type is a routing instruction or a control instruction;
the query statement rewriting module rewrites the SQL statement according to the template file, and the query statement rewriting module comprises: if the hit instruction is a routing instruction, rewriting the SQL statement according to the template file; if the hit instruction is a control instruction of a query class, generating a DSL statement according to the template file; and if the hit instruction is a control instruction of a result type, acquiring a control rule of the control instruction according to the template file, and binding the control rule with the SQL statement.
Optionally, the hit instruction includes an instruction value; the query statement rewriting module rewrites the SQL statement according to the template file, and the query statement rewriting module comprises:
positioning a target index in the SQL sentence according to the template file, and replacing the target index with the instruction value to obtain the rewritten SQL sentence; and/or the number of the groups of groups,
and determining the query conditions in the SQL sentence, and merging the query conditions in the SQL sentence or adjusting the sequence of the query conditions according to the instruction values and the template file to obtain the rewritten SQL sentence.
Optionally, the hit instruction rule includes: an instruction format rule; the query statement rewrite module is further configured to: after extracting a hit instruction from the SQL statement, checking the SQL statement to determine that the grammar of the SQL statement is correct; and/or checking the hit instruction according to the instruction format rule to determine that the hit instruction is correctly formatted.
Optionally, the hit instruction rule includes: instruction parameter rules;
the query statement rewrite module is further configured to: and before the template file corresponding to the hit instruction is obtained from the template configuration file according to the instruction identification of the hit instruction, checking the hit instruction according to the instruction parameter rule to determine that the hit instruction parameter is correct.
Optionally, the apparatus further includes an instruction rule configuration module for: and writing the hit instruction rule into the etcd or zookeeper cluster before the query statement rewrite module generates the SQL statement with the hit instruction according to the predefined hit instruction rule.
Optionally, the instruction rule configuration module is further configured to: after writing the hit instruction rule into the etcd or zookeeper cluster, updating the hit instruction rule based on a registration mechanism.
According to another aspect of an embodiment of the present invention, there is provided an electronic device for data query, including:
one or more processors;
storage means for storing one or more programs,
the one or more programs, when executed by the one or more processors, cause the one or more processors to implement the method provided by the first aspect of the embodiments of the present invention.
According to a further aspect of an embodiment of the present invention, there is provided a computer readable medium having stored thereon a computer program which, when executed by a processor, implements the method provided by the first aspect of an embodiment of the present invention.
One embodiment of the above invention has the following advantages or benefits: different hit instructions can be written according to predefined hit instruction rules, declarative codes can be used for replacing instruction codes in the program coding process, codes are decoupled, query performance is improved, grammar supported by an SQL query mode is expanded, and expandability of the data query mode is improved.
Further effects of the above-described non-conventional alternatives are described below in connection with the embodiments.
Drawings
The drawings are included to provide a better understanding of the invention and are not to be construed as unduly limiting the invention. Wherein:
FIG. 1 is a schematic diagram of the main flow of a method of data querying according to an embodiment of the present invention;
FIG. 2 is a flow chart of a method of data polling in some embodiments of the invention;
FIG. 3 is a flow diagram of a rewrite SQL statement according to some embodiments of the invention;
FIG. 4 is a schematic diagram of the main modules of an apparatus for data querying according to an embodiment of the present invention;
FIG. 5 is an exemplary system architecture diagram in which embodiments of the present invention may be applied;
fig. 6 is a schematic diagram of a computer system suitable for use in implementing an embodiment of the invention.
Detailed Description
Exemplary embodiments of the present invention will now be described with reference to the accompanying drawings, in which various details of the embodiments of the present invention are included to facilitate understanding, and are to be considered merely exemplary. Accordingly, those of ordinary skill in the art will recognize that various changes and modifications of the embodiments described herein can be made without departing from the scope and spirit of the invention. Also, descriptions of well-known functions and constructions are omitted in the following description for clarity and conciseness.
According to one aspect of an embodiment of the present invention, a data query method is provided. Fig. 1 is a schematic diagram of main flow of a data query method according to an embodiment of the present invention, and as shown in fig. 1, the data query method includes:
step S101, generating an SQL sentence with a hit instruction according to a predefined hit instruction rule; the hit instruction rule includes: the instruction identification and the corresponding template file; the hit instruction comprises an instruction identification;
step S102, extracting a hit instruction from the SQL sentence, acquiring a template file corresponding to the hit instruction according to an instruction identifier of the hit instruction, and rewriting the SQL sentence according to the template file;
step S103, inquiring data according to the rewritten sentence.
Instruction identification is a unique representation of an instruction, such as an instruction name, instruction encoding, etc. The template file refers to a specific implementation of an instruction, in which an implementation function of a rewrite SQL statement is configured.
ES supports query approaches based on DSL and SQL syntax, etc. DSL supports the query characteristics of all ES, and is assembled in JSON format, thus being deep in Json nesting for complex query, easy to make mistakes and inconvenient to check. SQL supports the query feature of part ES, based on SQL grammar, writing is simple, and readability is strong. In the data visualization and business BI scenarios, query of ESs based on SQL is widely used due to the need to count a large number of data indexes and to consider maintainability and ease of use. The existing ES-SQL components have two types of official release and gitHub (which is a managed platform facing open source and private software projects), and basically support SQL queries under the scenes of common aggregation, non-aggregation and the like. But there are problems in the following scenarios: (1) does not support dimension index queries: assuming that indexes are created with time as dimensions, such as 202012_ORDER and 202011_ORDER, switching of date dimensions is required to be realized in program coding, and code scalability is poor; (2) control instructions for the query phase are not supported: conditional routing queries, such as ES supported, are supported in DSL but cannot be used in SQL; there are also e.g. cache control, such as whether the query results need to be cached, etc.
The hit instruction is a rule constraint, and is used as an SQL supplementary grammar, so that a user is allowed to perform special optimization on SQL by inserting the hit instruction into an SQL statement to influence the execution mode of the SQL. In the invention, by defining and writing different rint instructions, a rule engine (e.g. Drools, an open source business rule engine) can be adopted in the actual application process to realize the step S102 so as to execute the rint instructions. The method and the device can solve the defects in the SQL query mode, write different hit instructions according to the predefined hit instruction rule, replace instruction type codes with declarative type codes in the program coding process, decouple codes, improve query performance, expand grammar supported by the SQL query mode and improve the expandability of the data query mode.
The instruction types include routing instructions and control instructions. Routing instructions are used to support the storage of indices in the form of a dimension table, such as by date, merchant, region, etc. The control instruction is used for controlling query cache, optimizing SQL-DSL process, batch processing operation of query result, etc. Each hit instruction corresponds to a template file. In the actual application process, the hit instruction set can be maintained in advance, and the instruction set can be expanded according to different service scenes. Table 1 below shows the correspondence of part of the instructions and the template file. If a new instruction is needed subsequently, only the instruction name and the corresponding template file are defined.
Table 1 correspondence of instructions and templates
The control instructions can be divided into result-class control instructions and query-class control instructions according to the function of the instructions. The control instruction of the query class refers to an instruction affecting the execution mode of the query, for example, record-limit, scrolled, routing in table 1. The control instruction of the result class is an instruction affecting the query result, for example, cach, timeout, null-overt in table 1. The method for extracting the hit instruction from the SQL statement can further comprise the following steps: the instruction type of the hit instruction is determined. When the SQL sentence is rewritten according to the template file, if the hit instruction is a routing instruction, the SQL sentence is rewritten according to the template file; if the hit instruction is a control instruction of the query class, generating DSL statements according to the template file; if the hit instruction is a control instruction of a result class, a control rule of the control instruction is obtained according to the template file, and then the control rule is bound with the SQL statement. For the control instruction of the query class, a corresponding DSL segment is generated according to the control instruction of each query class in the SQL sentence, and when the query is executed, all DSL segments are combined, and then a query request is sent to the ES. By setting various control instructions, the query performance of the SQL query mode can be expanded.
Optionally, the hit instruction includes an instruction value. When the SQL statement is rewritten according to the template file, the method can comprise the following steps: locating a target index in the SQL statement according to the template file, and replacing the target index with the rewritten SQL statement of the instruction value; and/or determining the query conditions in the SQL statement, merging the query conditions in the SQL statement or adjusting the sequence of the query conditions according to the instruction values and the template file, and obtaining the rewritten SQL statement. The realization function (i.e. the routing rule) of the routing instruction is loaded in the template file, and the SQL sentence is rewritten according to the routing rule, so that the query efficiency can be improved on the basis of expanding the query performance of the SQL query mode, and the realization is easy.
The hit instruction rules may include instruction format rules. After the hit instruction is extracted from the SQL statement, the SQL statement may be checked to determine that the SQL statement is correctly parsed and/or the hit instruction may be checked according to the instruction format rules to determine that the hit instruction is correctly formatted. The content of the instruction format rule can be selectively set according to the actual situation.
For example, the prefix tag of the predefined hit instruction is "/" eshit:/", the instruction adopts a key-value pair format, the key-value pair is key=value, the key is the instruction identifier, the value is the instruction value, and when the SQL statement contains multiple hit instructions, the key-value pairs of the multiple hit instructions are separated by #. An index contains prefix and key value pairs inside, and when checking, the correctness of the instruction definition is verified, including: whether the prefix is correct, whether the key-value pair format in the instruction is correct.
The manner of verifying whether the grammar of the SQL sentence is correct can be selectively set according to the actual situation, and is not particularly limited herein. Optionally, the SQL statement is parsed by a device (a JDBC connection pool component) and the correctness of the SQL statement is verified according to an AST syntax tree (abstract syntax tree). For example, ES-SQL does not support JOIN (putting all elements in the array into a string), sub-query queries, and ES-SQL only supports select queries. If the SQL statement contains the query, the grammar is incorrect.
By checking the SQL statement and the hit instruction format, the calculation resource waste caused by the fact that the follow-up logic processing is still carried out when the SQL statement or the hit instruction format is incorrect can be avoided, and the query efficiency is improved.
Optionally, the hit instruction rule includes an instruction parameter rule. Before the template file corresponding to the hit instruction is obtained from the template configuration file according to the instruction identification of the hit instruction, the method further comprises: and checking the hit instruction according to the instruction parameter rule to determine that the hit instruction parameter is correct. For example, the instruction value of instruction record-limit in table 1 is an integer greater than 0, and if the instruction value in the extracted hit instruction is 0, the hit instruction parameter is incorrect and the hit instruction is illegal. By checking the SQL statement and the hit instruction format, the calculation resource waste caused by the follow-up logic processing still performed when the hit instruction is illegal can be avoided, and the query efficiency is improved.
According to the predefined hit instruction rule, before generating the SQL statement with the hit instruction, the method may further include: the hit instruction rule is written to the etcd or zookeeper cluster. etcd is a highly available distributed key-value database that employs a consistency algorithm. zookeeper is a distributed application coordination service that can provide a consistent service for distributed applications. Illustratively, the method of the embodiment of the invention is realized based on the open source rule engine Drools as the bottom layer component, and when the component is initialized, the rule engine configuration is written into the etcd or zookeeper cluster, so that unified maintenance of rule metadata can be realized.
After writing the hit instruction rule into the etcd or zookeeper cluster, the method may further include: the hit instruction rule is updated based on the registration mechanism. Based on the registry, a modified hot update to the rules can be implemented.
The rule engine for implementing the embodiment of the invention can be integrated into projects in jar (a software package file format) package form through maven (project object model), and is matched into the existing SQL query components (official and open source) through the component query adaptation interface.
The following describes an exemplary data query method according to an embodiment of the present invention with reference to fig. 2. In this embodiment, the hit instruction rule is preconfigured. The embodiment mainly comprises the following steps:
1. SQL parsing
When the SQL statement is submitted to the rule engine, the rule engine firstly verifies the grammar of the SQL statement through SQL analysis, if the hit instruction exists in the SQL statement, the hit instruction is extracted from the SQL statement and verified to be correct in format in the step, and then the hit instruction analysis is carried out. If the hit instruction does not exist in the SQL statement, query execution is directly performed according to the SQL statement.
2. Hint instruction parsing
When a query component based on a rules engine is started, the component reads the Drools configuration file and initializes a verifier object that instructs the verifier.
If the hit instruction exists, verifying whether the hit instruction is legal or not, acquiring a corresponding template file according to the hit name after verification is passed, analyzing and executing the template file by Drools, returning a result HintProcessInfo object, and transmitting the HintProcessInfo object as a parameter to a component context object for rewriting an SQL statement or optimizing a DSL statement.
3. SQL statement rewrite
For the routing instructions, the SQL statement is modified according to the routing instruction rule positioning to the target index. The sphere condition is rewritten, and the repeated query condition and the condition sequence are combined. And for the control instruction, optimizing the SQL statement according to the control rule of the control instruction, wherein the optimization comprises the steps of converting the SQL statement into a DSL statement and binding the control rule into the SQL statement.
The specific logic is as follows:
1. returning to HintProcessInfo object after Drools processing, and obtaining three parameters of instruction name, instruction value (which is the parameter input in overwriting or optimizing or the function actually called) and instruction type from the object.
2. If the instruction type is route type, the SQL statement rewrite rules are as follows in Table 2:
table 2 SQL statement rewrite rules
3. If the instruction type is control type, the optimization rule of the control instruction is as follows in table 3:
TABLE 3 optimization rules for control instructions
The control instruction of the query class refers to an instruction affecting the execution mode of the query, for example, record-limit, scrolled, routing in table 1. The control instruction of the result class is an instruction affecting the query result, for example, cach, timeout, null-overt in table 1. The method for extracting the hit instruction from the SQL statement can further comprise the following steps: the instruction type of the hit instruction is determined.
If the hit instruction is record-limit, scrolled, routing, generating DSL statements according to the template file; if the hit instruction is cach, timeout, null-overt, the corresponding control rule is bound with the SQL statement.
Illustratively, let/# esHint: schema=202012_ORDER/select from ORDER, and/# esHint: schema=202012_ORDER/be the hit instruction, select from ORDER is the query statement, where ORDER is the alias of the ES index. After the hit instruction parsing process, the rewritten SQL is select from 202012_order.
4. Query execution
And querying data according to the rewritten SQL statement, or the optimized SQL statement, or the generated DSL statement. The component can abstract and define a query adaptation interface, supports adaptation of the underlying SQL query component in an SPI (Serial Peripheral Interface ) mode, supports three versions of official, open source and self-implementation, and defaults to self-implementation. When the interfacing authorities and communities are open, the routing instructions are mainly used, and for custom implementation, the routing instructions and the control instructions are simultaneously supported. The self-implemented SQL query component has the following characteristics: SQL statement level caching; executing a plurality of SQL in batches, and simultaneously carrying out operations such as merging UNION, splicing JOIN and the like on a plurality of query result sets; performing distict de-duplication processing on a single result set; a pool of data source connections is supported.
After the SQL statement is rewritten, or the SQL is optimized, or the DSL process is generated through the steps, the query operation is carried out by the SQL query component at the bottom layer according to the rewritten SQL, or the optimized SQL, or the DSL process.
FIG. 3 is a flow diagram of a rewrite SQL statement according to some embodiments of the invention. In this embodiment, after step S301 receives the SQL statement with the hit instruction, it is checked in step S302 whether the SQL statement is correct according to the AST syntax tree. If yes, go to step S303, extract the hit instruction from the SQL statement, otherwise, the flow ends. After the hit instruction is fetched, it is checked in step S304 whether the hit instruction is correct. If yes, go to step S305, otherwise the flow ends. In step S305, it is determined that the hit instruction type is a routing instruction, and if yes, the step goes to step S306 to rewrite SQL, otherwise the step goes to step S307 to generate DSL or binding control rules. In the actual application process, the execution sequence of steps S302, S303 and S304 may be adjusted as required.
According to a further aspect of the embodiments of the present invention, there is provided an apparatus for implementing the above method.
Fig. 4 is a schematic diagram of main modules of an apparatus for data query according to an embodiment of the present invention. As shown in fig. 4, the data query apparatus 400 includes:
the query statement generation module 401 generates an SQL statement with a hit instruction according to a predefined hit instruction rule; the hit instruction rule includes: the instruction identification and the corresponding template file; the hit instruction comprises an instruction identification;
a query statement rewrite module 402, configured to extract a hit instruction from the SQL statement, obtain a template file corresponding to the hit instruction according to an instruction identifier of the hit instruction, and rewrite the SQL statement according to the template file;
the query term execution module 403 queries data according to the rewritten term.
Optionally, the query statement rewrite module is further configured to: after extracting a hit instruction from the SQL statement, determining the instruction type of the hit instruction; the instruction type is a routing instruction or a control instruction;
the query statement rewriting module rewrites the SQL statement according to the template file, and the query statement rewriting module comprises: if the hit instruction is a routing instruction, rewriting the SQL statement according to the template file; if the hit instruction is a control instruction of a query class, generating a DSL statement according to the template file; and if the hit instruction is a control instruction of a result type, acquiring a control rule of the control instruction according to the template file, and binding the control rule with the SQL statement.
Optionally, the hit instruction includes an instruction value; the query statement rewriting module rewrites the SQL statement according to the template file, and the query statement rewriting module comprises:
positioning a target index in the SQL sentence according to the template file, and replacing the target index with the instruction value to obtain the rewritten SQL sentence; and/or the number of the groups of groups,
and determining the query conditions in the SQL sentence, and merging the query conditions in the SQL sentence or adjusting the sequence of the query conditions according to the instruction values and the template file to obtain the rewritten SQL sentence.
Optionally, the hit instruction rule includes: an instruction format rule;
the query statement rewrite module is further configured to: after extracting a hit instruction from the SQL statement, checking the SQL statement to determine that the grammar of the SQL statement is correct; and/or checking the hit instruction according to the instruction format rule to determine that the hit instruction is correctly formatted.
Optionally, the hit instruction rule includes: instruction parameter rules;
the query statement rewrite module is further configured to: and before the template file corresponding to the hit instruction is obtained from the template configuration file according to the instruction identification of the hit instruction, checking the hit instruction according to the instruction parameter rule to determine that the hit instruction parameter is correct.
Optionally, the apparatus further includes an instruction rule configuration module for: and writing the hit instruction rule into the etcd or zookeeper cluster before the query statement rewrite module generates the SQL statement with the hit instruction according to the predefined hit instruction rule.
Optionally, the instruction rule configuration module is further configured to: after writing the hit instruction rule into the etcd or zookeeper cluster, updating the hit instruction rule based on a registration mechanism.
According to another aspect of an embodiment of the present invention, there is provided an electronic device for data query, including:
one or more processors;
storage means for storing one or more programs,
the one or more programs, when executed by the one or more processors, cause the one or more processors to implement the method provided by the first aspect of the embodiments of the present invention.
According to a further aspect of an embodiment of the present invention, there is provided a computer readable medium having stored thereon a computer program which, when executed by a processor, implements the method provided by the first aspect of an embodiment of the present invention.
Fig. 5 illustrates an exemplary system architecture 500 of a data querying method or device to which embodiments of the present invention may be applied.
As shown in fig. 5, the system architecture 500 may include terminal devices 501, 502, 503, a network 504, and a server 505. The network 504 is used as a medium to provide communication links between the terminal devices 501, 502, 503 and the server 505. The network 504 may include various connection types, such as wired, wireless communication links, or fiber optic cables, among others.
A user may interact with the server 505 via the network 504 using the terminal devices 501, 502, 503 to receive or send messages or the like. Various communication client applications may be installed on the terminal devices 501, 502, 503, such as shopping class applications, web browser applications, search class applications, instant messaging tools, mailbox clients, social platform software, etc. (by way of example only).
The terminal devices 501, 502, 503 may be a variety of electronic devices having a display screen and supporting web browsing, including but not limited to smartphones, tablets, laptop and desktop computers, and the like.
The server 505 may be a server providing various services, such as a background management server (by way of example only) providing support for shopping-type websites browsed by users using the terminal devices 501, 502, 503. The background management server may analyze and process the received data such as the product information query request, and feedback the processing result (e.g., the target push information, the product information—only an example) to the terminal device.
It should be noted that, the method for querying data provided by the embodiment of the present invention is generally executed by the server 505, and accordingly, the device for querying data is generally disposed in the server 505.
It should be understood that the number of terminal devices, networks and servers in fig. 5 is merely illustrative. There may be any number of terminal devices, networks, and servers, as desired for implementation.
Referring now to FIG. 6, there is illustrated a schematic diagram of a computer system 600 suitable for use in implementing an embodiment of the present invention. The terminal device shown in fig. 6 is only an example, and should not impose any limitation on the functions and the scope of use of the embodiment of the present invention.
As shown in fig. 6, the computer system 600 includes a Central Processing Unit (CPU) 601, which can perform various appropriate actions and processes according to a program stored in a Read Only Memory (ROM) 602 or a program loaded from a storage section 608 into a Random Access Memory (RAM) 603. In the RAM 603, various programs and data required for the operation of the system 600 are also stored. The CPU 601, ROM 602, and RAM 603 are connected to each other through a bus 604. An input/output (I/O) interface 605 is also connected to bus 604.
The following components are connected to the I/O interface 605: an input portion 606 including a keyboard, mouse, etc.; an output portion 607 including a Cathode Ray Tube (CRT), a Liquid Crystal Display (LCD), and the like, a speaker, and the like; a storage section 608 including a hard disk and the like; and a communication section 609 including a network interface card such as a LAN card, a modem, or the like. The communication section 609 performs communication processing via a network such as the internet. The drive 610 is also connected to the I/O interface 605 as needed. Removable media 611 such as a magnetic disk, an optical disk, a magneto-optical disk, a semiconductor memory, or the like is installed as needed on drive 610 so that a computer program read therefrom is installed as needed into storage section 608.
In particular, according to embodiments of the present disclosure, the processes described above with reference to flowcharts may be implemented as computer software programs. For example, embodiments of the present disclosure include a computer program product comprising a computer program embodied on a computer readable medium, the computer program comprising program code for performing the method shown in the flow chart. In such an embodiment, the computer program may be downloaded and installed from a network through the communication portion 609, and/or installed from the removable medium 611. The above-described functions defined in the system of the present invention are performed when the computer program is executed by a Central Processing Unit (CPU) 601.
The computer readable medium shown in the present invention may be a computer readable signal medium or a computer readable storage medium, or any combination of the two. The computer readable storage medium can be, for example, but not limited to, an electronic, magnetic, optical, electromagnetic, infrared, or semiconductor system, apparatus, or device, or a combination of any of the foregoing. More specific examples of the computer-readable storage medium may include, but are not limited to: an electrical connection having one or more wires, a portable computer diskette, a hard disk, a Random Access Memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber, a portable compact disc read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the foregoing. In the context of this document, a computer readable storage medium may be any tangible medium that can contain, or store a program for use by or in connection with an instruction execution system, apparatus, or device. In the present invention, however, the computer-readable signal medium may include a data signal propagated in baseband or as part of a carrier wave, with the computer-readable program code embodied therein. Such a propagated data signal may take any of a variety of forms, including, but not limited to, electro-magnetic, optical, or any suitable combination of the foregoing. A computer readable signal medium may also be any computer readable medium that is not a computer readable storage medium and that can communicate, propagate, or transport a program for use by or in connection with an instruction execution system, apparatus, or device. Program code embodied on a computer readable medium may be transmitted using any appropriate medium, including but not limited to: wireless, wire, fiber optic cable, RF, etc., or any suitable combination of the foregoing.
The flowcharts and block diagrams in the figures illustrate the architecture, functionality, and operation of possible implementations of systems, methods and computer program products according to various embodiments of the present invention. In this regard, each block in the flowchart or block diagrams may represent a module, segment, or portion of code, which comprises one or more executable instructions for implementing the specified logical function(s). It should also be noted that, in some alternative implementations, the functions noted in the block may occur out of the order noted in the figures. For example, two blocks shown in succession may, in fact, be executed substantially concurrently, or the blocks may sometimes be executed in the reverse order, depending upon the functionality involved. It will also be noted that each block of the block diagrams or flowchart illustration, and combinations of blocks in the block diagrams or flowchart illustration, can be implemented by special purpose hardware-based systems which perform the specified functions or acts, or combinations of special purpose hardware and computer instructions.
The modules involved in the embodiments of the present invention may be implemented in software or in hardware. The described modules may also be provided in a processor, for example, as: a processor includes a query statement generation module, a query statement rewrite module, and a query statement execution module. The names of these modules do not constitute limitations on the module itself in some cases, and for example, a query statement generation module may also be described as "a module that rewrites the SQL statement from the template file".
As another aspect, the present invention also provides a computer-readable medium that may be contained in the apparatus described in the above embodiments; or may be present alone without being fitted into the device. The computer readable medium carries one or more programs which, when executed by a device, cause the device to include: generating an SQL statement with a hit instruction according to a predefined hit instruction rule; the hit instruction rule includes: the instruction identification and the corresponding template file; the hit instruction comprises an instruction identification; extracting a hit instruction from the SQL sentence, acquiring a template file corresponding to the hit instruction according to an instruction identifier of the hit instruction, and rewriting the SQL sentence according to the template file; and inquiring data according to the rewritten sentence.
According to the technical scheme provided by the embodiment of the invention, different hit instructions can be written according to the predefined hit instruction rule, declarative codes can be used for replacing instruction codes in the program coding process, the codes are decoupled, the query performance is improved, the grammar supported by the SQL query mode is expanded, and the expandability of the data query mode is improved.
The above embodiments do not limit the scope of the present invention. It will be apparent to those skilled in the art that various modifications, combinations, sub-combinations and alternatives can occur depending upon design requirements and other factors. Any modifications, equivalent substitutions and improvements made within the spirit and principles of the present invention should be included in the scope of the present invention.
Claims (9)
1. A method of querying data, comprising:
generating an SQL statement with a hit instruction according to a predefined hit instruction rule; the hit instruction rule includes: the instruction identification and the corresponding template file; the hit instruction comprises an instruction identification;
extracting a hit instruction from the SQL sentence, acquiring a template file corresponding to the hit instruction according to an instruction identifier of the hit instruction, and rewriting the SQL sentence according to the template file;
inquiring data according to the rewritten sentence;
after extracting the hit instruction from the SQL statement, the method further comprises the following steps: determining an instruction type of the hit instruction; the instruction type is a routing instruction or a control instruction, and the routing instruction is used for supporting the storage of indexes in a form of a dimension table; rewriting the SQL sentence according to the template file, including: if the hit instruction is a routing instruction, rewriting the SQL statement according to the template file; if the hit instruction is a control instruction of a query class, generating a DSL statement according to the template file; if the hit instruction is a control instruction of a result type, acquiring a control rule of the control instruction according to the template file, and binding the control rule with the SQL statement; the instruction identification is a unique representation of an instruction; the template file refers to a specific implementation of an instruction, wherein an implementation function for rewriting an SQL statement is configured.
2. The method of claim 1, wherein the hit instruction comprises an instruction value; rewriting the SQL sentence according to the template file, including:
positioning a target index in the SQL sentence according to the template file, and replacing the target index with the instruction value to obtain the rewritten SQL sentence; and/or the number of the groups of groups,
and determining the query conditions in the SQL sentence, and merging the query conditions in the SQL sentence or adjusting the sequence of the query conditions according to the instruction values and the template file to obtain the rewritten SQL sentence.
3. The method of claim 1, wherein the hit instruction rule comprises: an instruction format rule;
after extracting the hit instruction from the SQL statement, the method further comprises the following steps: checking the SQL sentence to determine that the grammar of the SQL sentence is correct; and/or checking the hit instruction according to the instruction format rule to determine that the hit instruction is correctly formatted.
4. The method of claim 1, wherein the hit instruction rule comprises: instruction parameter rules;
before the template file corresponding to the hit instruction is obtained from the template configuration file according to the instruction identification of the hit instruction, the method further comprises: and checking the hit instruction according to the instruction parameter rule to determine that the hit instruction parameter is correct.
5. The method of claim 1, wherein prior to generating the SQL statement with the hit instruction according to the predefined hit instruction rule, further comprising: writing the hit instruction rule into the etcd or zookeeper cluster.
6. The method of claim 5, wherein after writing the hit instruction rule to etcd or zookeeper cluster, further comprising: the hit instruction rule is updated based on a registration mechanism.
7. A data query device, comprising:
the query statement generation module generates an SQL statement with a hit instruction according to a predefined hit instruction rule; the hit instruction rule includes: the instruction identification and the corresponding template file; the hit instruction comprises an instruction identification;
the query statement rewriting module extracts a hit instruction from the SQL statement, acquires a template file corresponding to the hit instruction according to an instruction identifier of the hit instruction, and rewrites the SQL statement according to the template file;
the query statement execution module queries data according to the rewritten statement;
the query statement rewrite module is further configured to: after extracting a hit instruction from the SQL statement, determining the instruction type of the hit instruction; the instruction type is a routing instruction or a control instruction, and the routing instruction is used for supporting the storage of indexes in a form of a dimension table; the query statement rewriting module rewrites the SQL statement according to the template file, and the query statement rewriting module comprises: if the hit instruction is a routing instruction, rewriting the SQL statement according to the template file; if the hit instruction is a control instruction of a query class, generating a DSL statement according to the template file; if the hit instruction is a control instruction of a result type, acquiring a control rule of the control instruction according to the template file, and binding the control rule with the SQL statement; the instruction identification is a unique representation of an instruction; the template file refers to a specific implementation of an instruction, wherein an implementation function for rewriting an SQL statement is configured.
8. An electronic device for data querying, comprising:
one or more processors;
storage means for storing one or more programs,
when executed by the one or more processors, causes the one or more processors to implement the method of any of claims 1-6.
9. A computer readable medium, on which a computer program is stored, characterized in that the program, when being executed by a processor, implements the method according to any of claims 1-6.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202110251343.5A CN113760949B (en) | 2021-03-08 | 2021-03-08 | Data query method and device |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202110251343.5A CN113760949B (en) | 2021-03-08 | 2021-03-08 | Data query method and device |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN113760949A CN113760949A (en) | 2021-12-07 |
| CN113760949B true CN113760949B (en) | 2023-09-22 |
Family
ID=78786708
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202110251343.5A Active CN113760949B (en) | 2021-03-08 | 2021-03-08 | Data query method and device |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN113760949B (en) |
Citations (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN106294468A (en) * | 2015-06-02 | 2017-01-04 | 阿里巴巴集团控股有限公司 | The method and apparatus processing business datum |
Family Cites Families (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US7801913B2 (en) * | 1998-12-07 | 2010-09-21 | Oracle International Corporation | System and method for querying data for implicit hierarchies |
-
2021
- 2021-03-08 CN CN202110251343.5A patent/CN113760949B/en active Active
Patent Citations (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN106294468A (en) * | 2015-06-02 | 2017-01-04 | 阿里巴巴集团控股有限公司 | The method and apparatus processing business datum |
Non-Patent Citations (1)
| Title |
|---|
| ORACLE SQL语句执行效率研究;魏彬;;内蒙古科技与经济(11);全文 * |
Also Published As
| Publication number | Publication date |
|---|---|
| CN113760949A (en) | 2021-12-07 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN110019350B (en) | Data query method and device based on configuration information | |
| CN110023923B (en) | Generating a data transformation workflow | |
| AU2021212135B2 (en) | Building and managing data-processing attributes for modelled data sources | |
| US8346747B2 (en) | Extending database tables in a multi-tenant environment | |
| US10901804B2 (en) | Apparatus and method to select services for executing a user program based on a code pattern included therein | |
| CN108846753B (en) | Method and apparatus for processing data | |
| US20130304713A1 (en) | System and method for metadata level validation of custom setup objects | |
| WO2017156916A1 (en) | Data access method and device | |
| CN107534671A (en) | The polymerization of Distributed Services entity and association is with combining | |
| CN109522341A (en) | Realize method, apparatus, the equipment of the stream data processing engine based on SQL | |
| US20170206326A1 (en) | Systems and methods for the classification and indexing of contract documentation | |
| CN111125064B (en) | Method and device for generating database schema definition statement | |
| US11914560B2 (en) | Systems and methods for creating a reorganization-immune blockchain index using mono-increasing sequence records | |
| US20230116336A1 (en) | API Governance Enforcement Architecture | |
| CN113760948A (en) | Data query method and device | |
| US20180018367A1 (en) | Remote query optimization in multi data sources | |
| US11341154B2 (en) | Normalizing encodings of requested data from a common data schema to a target data schema | |
| US10291746B2 (en) | Context switch of database connections | |
| CN108694172B (en) | Information output method and device | |
| CN113760949B (en) | Data query method and device | |
| US10235224B2 (en) | Validation and parsing performance using subtree caching | |
| CN112988778B (en) | Method and device for processing database query script | |
| US11971878B2 (en) | Systems and methods for supporting both batch processing and streaming data applications based on raw blockchain data | |
| US11954114B2 (en) | Systems and methods for improved blockchain data indexing by decoupling compute and storage layers | |
| US20240007525A1 (en) | Systems and methods for improved blockchain data indexing by avoiding throughput bottlenecks |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |