SQL query method and device based on HBase
By analyzing and decomposing SQL statements, HBase multi-table query or cross-database query method is used to solve the problem of large resource occupancy and inability to cross-database query in SQL on HBase technology, and an efficient and low-resource query process is achieved.
Patent Information
- Application Number
- CN202411033001.6
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2024-07-30
- Publication Date
- 2024-11-15
- Estimated Expiration
- Not applicable · inactive patent
AI Technical Summary
The existing SQL on HBase technology has the problem of large resource occupancy and inability to associate queries across databases, resulting in heavyweight and inefficient systems.
By parsing SQL statements, determining the query type and decomposing the data source, using HBase multi-table query or cross-database query method, using API query and local file secondary query, optimize the query process to reduce resource usage and improve efficiency.
It realizes basically no additional resource usage, supports cross-database queries, improves query efficiency, and can obtain accurate query results without increasing resource consumption.
Smart Images

Figure CN118964498A_ABST
Abstract
Description
Technical Field
[0001] The present invention relates to the technical field of database query, and in particular to an HBase-based SQL query method and device. Background Art
[0002] Today's society is a rapidly developing society with advanced technology, information flow, closer communication between people, and more convenient life. Big data is the product of this high-tech era. The growth of data urgently requires the expansion of database storage capacity. Existing relational databases are difficult to meet the needs of crazy data growth in hardware. Apache HBase is a high-performance, distributed, column-oriented, scalable open source distributed NoSQL database. It is different from general relational databases and is a database suitable for storing unstructured data.
[0003] HBase is a very useful big data storage, but its access mechanism is very primitive and can only be accessed through the client API, Map / Reduce interface and interactive shell. SQL access to HBase data can be done through Map / Reduce or interface mechanism and "local SQL" technology. However, there are two problems with using these two technologies:
[0004] 1) Large resource usage:
[0005] Systems that use Map / Reduce or interface mechanisms for data access include Spark, Hive, Impala, etc. Such systems require a large amount of memory resources to analyze and calculate data. "Local SQL technology" represented by Phoenix requires building data views in advance based on the data in Hbase to respond to various query requirements. Data views take up a lot of disk space. Overall, the current SQL on Hbase is a heavyweight system and requires a large amount of additional host resources.
[0006] 2) Unable to cross database
[0007] Currently, SQL on Hbase is basically a single-database data analysis, and it is rare to complete multi-table association queries in multiple databases. When cross-database multi-table queries are required, the data needs to be completely copied to a type of database before the association query can be performed. Summary of the invention
[0008] The present invention provides an HBase-based SQL query method and device to solve at least one of the above technical problems.
[0009] On the one hand, the present invention provides a SQL query method based on HBase, the method comprising: performing SQL parsing on an SQL statement to obtain a data source and a query condition corresponding to the SQL statement; determining a query type corresponding to the SQL statement according to the data source; based on the query type, decomposing the data source through the query condition; and querying the decomposed data source to obtain query data corresponding to the SQL statement.
[0010] In a possible implementation of the present invention, SQL parsing is performed on an SQL statement, including: parsing the SQL statement to obtain a table to be queried and a query condition of the corresponding table; and constituting the data source through the table to be queried.
[0011] In a possible implementation of the present invention, the query type corresponding to the SQL statement is determined according to the data source, including: if the tables to be queried all belong to the HBase database, the query type is the HBase multi-table query type; if the tables to be queried belong to the HBase database and the relational database, the query type is the cross-database query type.
[0012] In a possible implementation of the present invention, if the query type is an HBase multi-table query type, the method further includes: dividing the data source into an explicit condition table and an implicit condition table according to the query condition; querying the explicit condition table to obtain a first query result; applying the first query result to the implicit condition table, and querying the implicit condition table to obtain a second query result; and obtaining query data corresponding to the SQL statement based on the first query result and the second query result.
[0013] In a possible implementation of the present invention, query data corresponding to the SQL statement is obtained, including: merging the first query result and the second query result into a data collection, and storing the data in a local file; and performing a secondary query on the local file using the SQL statement to obtain the query data.
[0014] In a possible implementation of the present invention, if the query type is a cross-database query type, the method also includes: dividing the data source according to the database to which it belongs, and obtaining a table to be queried of a single database; querying the table to be queried belonging to the relational database through the query condition to obtain a first query result; processing the query condition according to a preset screening rule, and querying the table to be queried belonging to the HBase database through the processed query condition to obtain a second query result; and obtaining query data corresponding to the SQL statement based on the first query result and the second query result.
[0015] In a possible implementation of the present invention, query data corresponding to the SQL statement is obtained, including: merging the first query result and the second query result into a data collection, and storing the data in a local file; and performing a secondary query on the local file using the SQL statement to obtain the query data.
[0016] In a possible implementation of the present invention, the preset screening condition includes: reducing the query results of the table to be queried belonging to the HBase database, and / or reducing the query range of the table to be queried belonging to the HBase database.
[0017] In a possible implementation of the present invention, when the data source is queried using the SQL statement, the method further includes: if the data source belongs to an HBase database, parsing the SQL statement into an API query statement before querying.
[0018] On the other hand, the present invention also provides an HBase-based SQL query device, the device comprising: at least one processor; and a memory communicatively connected to the at least one processor; wherein the memory stores instructions that can be executed by the at least one processor so that the at least one processor can perform SQL parsing on SQL statements to obtain data sources and query conditions corresponding to the SQL statements; determine the query type corresponding to the SQL statement according to the data source; based on the query type, decompose the data source through the query conditions; and query the decomposed data source to obtain query data corresponding to the SQL statement.
[0019] The present invention provides an HBase-based SQL query method and device, which have the following beneficial effects:
[0020] 1) There is basically no additional resource occupation. In the entire query process, only the files of the intermediate result sets of each table occupy a part of the resources. This data is not much different from the final result data and can be understood as a superset of the final result. Apart from this, no other resources are occupied. This ensures that no additional resources are occupied while ensuring the query results.
[0021] 2) It can realize cross-database query and "join" table data of multiple types of databases together without additional operations, and realize associated query with relational database and HBase database.
[0022] 3) The query efficiency is high. The query of each table maximizes the clear data range to obtain the most accurate query results, thereby reducing the amount of data for secondary queries and improving the overall query efficiency. BRIEF DESCRIPTION OF THE DRAWINGS
[0023] The drawings described herein are used to provide a further understanding of the present invention and constitute a part of the present invention. The exemplary embodiments of the present invention and their descriptions are used to explain the present invention and do not constitute an improper limitation of the present invention. In the drawings:
[0024] Figure 1 A flow chart of a SQL query method based on HBase provided by the present invention;
[0025] Figure 2 A cross-database query process diagram provided by the present invention;
[0026] Figure 3 A diagram of the HBase multi-table query process provided by the present invention;
[0027] Figure 4 A structural diagram of an HBase-based SQL query device provided by the present invention. DETAILED DESCRIPTION
[0028] In order to make the purpose, technical solution and advantages of the present invention clearer, the technical solution of the present invention will be clearly and completely described below in conjunction with the specific embodiments of the present invention and the corresponding drawings. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without making creative work are within the scope of protection of the present invention.
[0029] The technical solution proposed by the present invention is described in detail below with reference to the accompanying drawings.
[0030] Figure 1 The present invention provides a flow chart of a SQL query method based on HBase. Figure 1 As shown, the query method proposed in the present invention at least includes the following execution steps:
[0031] Step 101: perform SQL parsing on the SQL statement to obtain the data source and query conditions corresponding to the SQL statement.
[0032] The database query in the present invention is implemented by using SQL statements, so the SQL statements need to be SQL parsed before querying. SQL parsing is an existing method for parsing SQL statements, and its parsing process can be implemented by existing methods or algorithms, which will not be elaborated in the present invention.
[0033] Furthermore, after the SQL statement is parsed, the query conditions of the corresponding table of the table to be queried are obtained. Here, the table to be queried is the data source, indicating which table the data to be queried comes from. The query conditions of all corresponding tables constitute the aforementioned query conditions.
[0034] Step 102: Determine the query type corresponding to the SQL statement according to the data source.
[0035] The query type corresponding to the SQL statement is determined based on whether the databases to which the tables to be queried obtained by parsing the SQL statement are the same. In a possible implementation of the present invention, if all the tables to be queried come from or all belong to the HBase database, the query type is determined to be the HBase multi-table query type, that is, it is necessary to query multiple tables in the HBase database, and if some of the tables to be queried belong to the HBase database and some belong to the relational database, the query type is determined to be the cross-database query type, that is, a cross-database query is required.
[0036] Step 103: Based on the query type, the data sources are decomposed by query conditions.
[0037] Decomposition here refers to distinguishing or dividing the tables to be queried according to the conditions. When the query type is HBase multi-table query, decomposition here refers to dividing the tables to be queried into explicit condition tables and non-explicit condition tables. When the query type is a cross-database query type, decomposition here means dividing the tables to be queried according to a single database.
[0038] Step 104: query the decomposed data source to obtain query data corresponding to the SQL statement.
[0039] When querying the table to be queried after the division is completed, there are the following rules, and the priority of the query order is: data table of relational database > HBase explicit condition table > HBase non-explicit condition table. This is because the amount of data in the relational database is small, the SQL support is good and the query efficiency is high, the query intermediate results can be obtained quickly and conveniently, and its results can be used as conditions to make the intermediate result set of the query in HBase more accurate. HBase is a NoSQL database that stores large amounts of data, and HBase uses bitmap index technology to improve the query speed. Accurate ROWKEY can be found for various limiting conditions. Therefore, by clarifying the query conditions, the query efficiency can be greatly improved. If there are no query conditions, the entire HBase table needs to be scanned, which is inefficient. Therefore, when querying HBase data, try to clarify the conditions as much as possible to obtain more accurate data, so the query of the data table with non-explicit conditions should be performed last.
[0040] In a possible implementation of the present invention, SQL conversion + two-stage query method is used to implement SQL on HBase query, and its main application scenarios are cross-database query and HBase multi-table query application. Cross-database query refers to the query method of multi-table association between relational databases (Oracle, MySQL, etc.) and HBase databases, and HBase multi-table query refers to the query method of single table or multi-table association in HBase.
[0041] Therefore, after determining the query type, different query types use different query processes, as follows:
[0042] 1) When the query type is cross-database query:
[0043] like Figure 2 As shown, the SQL statement is first parsed to obtain one or more tables to be queried, that is, data sources and query conditions, and then the multi-table association query is decomposed into a query of a data table with conditions in a single database.
[0044] In the process of querying by database, the data table in the relational database is queried first. Figure 2 Qra_Tab in the query results, that is, Figure 2 Then, according to the table association, find the limiting conditions that can reduce the query results and / or query range of the HBase table, and then query the tables in HBase according to the limiting conditions. Figure 2 Query the NBase_Tab in the query to get the second query result, that is, Figure 2 In this process, because HBase itself does not have a related SQL interface, it is also necessary to parse the SQL statements in HBase into API query statements or methods supported by HBase for query.
[0045] Furthermore, the query results of different databases, i.e. Figure 2 The Qra_Tab result set and the HBase_Tab result set in the table are merged and output to the local file, integrated into a multi-data collection of the local file, and finally the SQL statement is used again for the local file to perform a secondary query to form the final result output to obtain the query data.
[0046] 2) When the query type is HBase multi-table query:
[0047] like Figure 3 As shown, the SQL statement is parsed to obtain the table to be queried, that is, the data source and the query conditions of the corresponding table, and the table to be queried is divided into explicit condition tables, that is, Figure 3 HBase_Tab1 and non-explicit condition tables in Figure 3 According to the above query table priority rules, and in order to reduce the size of the intermediate result set of data, the table with clear conditions is prioritized. Figure 3 Query HBase_Tab1 in the table, and obtain the first query result, that is, Figure 3 The HBase_Tab1 result set in the table is applied to the non-explicit condition table, that is, Figure 3 In HBase_Tab2 in the above example, the non-explicit condition table is queried to obtain the second query result, that is, Figure 3 In this process, the query of HBase_Tab1 and HBase_Tab2 requires the SQL statement to be parsed into the API query statement or method supported by HBase for query.
[0048] Furthermore, the query results of different databases, i.e. Figure 3 The HBase_Tab1 result set and the HBase_Tab2 result set in the table are merged and output to the local file, integrated into a multi-data collection of the local file, and finally the SQL statement is used again for the local file to perform a secondary query to form the final result output to obtain the query data.
[0049] Based on the same inventive concept, the present invention also provides a SQL query device based on HBase, whose structure is as follows: Figure 4 shown.
[0050] Figure 4 This is a structural diagram of an HBase-based SQL query device provided by the present invention. Figure 4 As shown, the HBase-based SQL query device 400 in the present invention specifically includes: at least one processor 401; and a memory 403 that is communicatively connected to the at least one processor 401 (connected via a bus 402); wherein the memory 403 stores instructions that can be executed by the at least one processor 401, so that the at least one processor 401 can execute an HBase-based SQL query method as described in the above embodiment.
[0051] In a possible implementation of the present invention, the aforementioned processor 401 is used to execute, perform SQL parsing on an SQL statement to obtain a data source and query conditions corresponding to the SQL statement; determine a query type corresponding to the SQL statement according to the data source; based on the query type, decompose the data source through the query conditions; and query the decomposed data source to obtain query data corresponding to the SQL statement.
[0052] The present invention realizes the SQL on HBase function under the condition of minimizing resource occupation through the above method, and can realize cross-database query. Specifically, it is realized that there is basically no additional resource occupation, and only the files of the intermediate result set of each table in the entire query process occupy a part of the resources, and this data is not much different from the final result data, which can be understood as a superset of the final result. In addition, there is no other resource occupation, which realizes that no additional resource occupation is added under the premise of ensuring the query results; cross-database query can be realized, and the table data of multiple types of databases can be "spliced" together, and no additional operation is required to realize the associated query with the relational database and the HBase database; the query efficiency is high, and the query of each table maximizes the clear data range to obtain the most accurate query result, thereby reducing the amount of data for secondary query and improving the overall query efficiency.
[0053] The various embodiments of the present invention are described in a progressive manner, and the same or similar parts between the various embodiments can be referred to each other, and each embodiment focuses on the differences from other embodiments. In particular, for the device embodiment, since it is basically similar to the method embodiment, the description is relatively simple, and the relevant parts can be referred to the partial description of the method embodiment.
[0054] The device and method provided by the present invention correspond one to one, and therefore, the device embodiment also has similar beneficial technical effects as the corresponding method. Since the beneficial technical effects of the method have been described in detail above, the beneficial technical effects of the device embodiment will not be repeated here.
[0055] Those skilled in the art will appreciate that embodiments of the present invention may be provided as methods, systems, or computer program products. Therefore, the present invention may take the form of a complete hardware embodiment, a complete software embodiment, or an embodiment combining software and hardware. Moreover, the present invention may take the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.
[0056] The present invention is described with reference to flowcharts and / or block diagrams of methods, devices (systems), and computer program products according to the present invention. It should be understood that each process and / or block in the flowchart and / or block diagram, as well as the combination of processes and / or blocks in the flowchart and / or block diagram, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, a special-purpose computer, an embedded processor, or other programmable data processing device to produce a machine, so that the instructions executed by the processor of the computer or other programmable data processing device generate instructions for implementing the processes in the flowchart and / or block diagram. Figure 1 A process or multiple processes and / or boxes Figure 1 A device that provides the functions specified in a block or multiple blocks.
[0057] These computer program instructions may also be stored in a computer-readable memory capable of directing a computer or other programmable data processing device to operate in a specific manner, so that the instructions stored in the computer-readable memory produce an article of manufacture comprising an instruction device, which implements the process Figure 1 A process or multiple processes and / or boxes Figure 1 A function specified in one or more boxes.
[0058] These computer program instructions can also be loaded onto a computer or other programmable data processing device so that a series of operating steps are executed on the computer or other programmable device to produce a computer-implemented process, thereby providing instructions for implementing the process. Figure 1 A process or multiple processes and / or boxes Figure 1 The steps for the functions specified in one or more boxes.
[0059] In a typical configuration, a computing device includes one or more processors (CPU), input / output interfaces, network interfaces, and memory. Memory may include non-permanent memory in a computer-readable medium, random access memory (RAM) and / or non-volatile memory in the form of read-only memory (ROM) or flash RAM. Memory is an example of a computer-readable medium. Computer-readable media include permanent and non-permanent, removable and non-removable media that can be implemented by any method or technology to store information. Information can be computer-readable instructions, data structures, modules of programs, or other data. Examples of computer storage media include, but are not limited to, phase change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory or other memory technology, compact disk read-only memory (CD-ROM), digital versatile disk (DVD) or other optical storage, magnetic cassettes, magnetic tape magnetic disk storage or other magnetic storage devices or any other non-transmission media that can be used to store information that can be accessed by a computing device. According to the definition in this article, computer-readable media does not include temporary computer-readable media (transitory media), such as modulated data signals and carrier waves.
[0060] It should also be noted that the terms "include", "comprises" or any other variations thereof are intended to cover non-exclusive inclusion, so that a process, method, commodity 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, commodity or device. In the absence of more restrictions, the elements defined by the sentence "comprises a ..." do not exclude the existence of other identical elements in the process, method, commodity or device including the elements.
[0061] The above description is only an embodiment of the present invention and is 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 should be included in the protection scope of the present invention.
Claims
1. A SQL query method based on HBase, characterized in that: The method comprises: Perform SQL parsing on the SQL statement to obtain the data source and query conditions corresponding to the SQL statement; Determine the query type corresponding to the SQL statement according to the data source; Based on the query type, decomposing the data source by the query condition; The decomposed data source is queried to obtain query data corresponding to the SQL statement.
2. The HBase-based SQL query method according to claim 1, characterized in that: Perform SQL parsing on SQL statements, including: Parsing the SQL statement to obtain the table to be queried and the query conditions of the corresponding table; The data source is formed by the table to be queried.
3. The HBase-based SQL query method according to claim 2, characterized in that: Determining the query type corresponding to the SQL statement according to the data source includes: If the tables to be queried all belong to the HBase database, the query type is the HBase multi-table query type; If the table to be queried belongs to an HBase database and a relational database, the query type is a cross-database query type.
4. The HBase-based SQL query method according to claim 1, characterized in that: If the query type is an HBase multi-table query type, the method further includes: Dividing the data sources into explicit condition tables and non-explicit condition tables according to the query conditions; Query the explicit condition table to obtain a first query result; Applying the first query result to the non-ambiguous condition table, and querying the non-ambiguous condition table to obtain a second query result; According to the first query result and the second query result, query data corresponding to the SQL statement is obtained.
5. The HBase-based SQL query method according to claim 4, characterized in that: Obtaining query data corresponding to the SQL statement includes: Merging the first query result and the second query result into a data collection, and storing the data collection in a local file; The local file is queried again using the SQL statement to obtain the query data.
6. The HBase-based SQL query method according to claim 1, characterized in that: If the query type is a cross-database query type, the method further includes: Divide the data sources according to the databases to which they belong, and obtain a table to be queried of a single database; According to the query condition, a query is performed on a table to be queried belonging to the relational database to obtain a first query result; The query condition is processed according to a preset screening rule, and a query is performed on the to-be-queried table belonging to the HBase database using the processed query condition to obtain a second query result; According to the first query result and the second query result, query data corresponding to the SQL statement is obtained.
7. The HBase-based SQL query method according to claim 6, characterized in that: Obtain query data corresponding to the SQL statement, including: Merging the first query result and the second query result into a data collection, and storing the data collection in a local file; The local file is queried again using the SQL statement to obtain the query data.
8. The HBase-based SQL query method according to claim 6, characterized in that: The preset screening conditions include: Reduce the query results of the table to be queried belonging to the HBase database, and / or reduce the query range of the table to be queried belonging to the HBase database.
9. The HBase-based SQL query method according to claim 1, characterized in that: When the SQL statement is used to query the data source, the method further includes: If the data source belongs to an HBase database, the SQL statement is parsed into an API query statement before querying.
10. An HBase-based SQL query device, characterized in that: The device comprises: at least one processor; and, a memory communicatively connected to the at least one processor; wherein, The memory stores instructions that can be executed by the at least one processor, so that the at least one processor can execute, perform SQL parsing on the SQL statement to obtain the data source and query conditions corresponding to the SQL statement; determine the query type corresponding to the SQL statement according to the data source; based on the query type, decompose the data source through the query conditions; query the decomposed data source to obtain the query data corresponding to the SQL statement.
Citation Information
Patent Citations
Method and equipment for querying data in database
CN106294499A
SQL query method and system based on Hbase and correlative device
CN108009296A
Data query method and device
CN113177062A
