A device and method for optimizing query performance based on Hbase

Abstract

The invention discloses a device and method for optimizing query performance based on Hbase, comprising ElasticSearch, Proxy, Appache phoenix and an application client. Hbase is used for synchronizing query condition data and a primary index to ElasticSearch. The application client is used for sending standard SQL to Proxy. Proxy implements interaction with ElasticSearch. ElasticSearch generates an index array according to the query condition and returns the index array to Proxy. Proxy triggers the primary index and the secondary index according to the returned rowkey array, translates and submits SQL to Appache phoenix for data parsing. Appache phoenix parses SQL into related statements and submits them to Hbase for execution. The generated results are combined into a result set. Hbase returns the result set to Proxy through Appache phoenix. Proxy encapsulates the result set and returns it to the application client. The invention can quickly respond to data that needs to be queried, supports secondary indexes, supports standard SQL, greatly facilitates business data query, supports any combination query, and greatly improves the freedom of query.

Description

Technical Field

[0001] The present invention relates to the field of network data query performance optimization, and in particular to a device and method for optimizing Hbase query performance. Background Art

[0002] At present, big data has been widely used in finance, e-commerce, logistics, corporate government affairs and other fields. In recent years, it has begun to be applied to industrial Internet. In the 5G era, as the process of Internet of Everything accelerates, the storage of massive data has brought challenges. Hbase is widely used in big data storage with its strong horizontal online expansion capability, easy maintenance, high availability and good write performance.

[0003] HBase is a sub-project of Apache Hadoop. HBase relies on Hadoop's HDFS as the most basic storage unit. By using Hadoop's DFS tool, you can see the structure of these data storage folders, and you can also operate HBase through the Map / Reduce framework (algorithm). HBase also includes Jetty in the product. When HBase starts, Jetty is started in an embedded way, so HBase can be managed and some current running states can be viewed through the web interface, which is very lightweight. However, Hbase also has its inherent shortcomings during use, and there are some technical points that need to be solved:

[0004] 1. Only primary key query can be supported to query unique records or obtain a batch of records by specifying conditions, and the query efficiency is relatively low;

[0005] 2. Hbase does not natively support secondary indexes, and unique primary keys are usually specially processed to avoid data skew, which causes trouble for queries;

[0006] 3. It does not support any combination of queries, which makes it difficult for users to retrieve data. Summary of the invention

[0007] In order to overcome the deficiencies of the prior art, the present invention provides a device and method for optimizing query performance based on Hbase, which supports secondary indexes and standard SQL, and greatly facilitates business data query.

[0008] The technical solution adopted by the present invention to solve the technical problem is: a method based on Hbase query performance optimization, the improvement of which is that it includes the following steps:

[0009] Step 1: Hbase synchronizes the query condition data and primary index to ElasticSearch;

[0010] Step 2: The application client sends standard SQL to the Proxy, which interacts with ElasticSearch;

[0011] Step 3: After Proxy interacts with ElasticSearch, it generates an index array and returns the index array. Porxy triggers the primary and secondary indexes, translates and submits SQL to Appache phoenix for data parsing.

[0012] Step 4: Appache Phoenix parses the SQL into related statements and submits them to Hbase for execution. The generated results are combined into a result set.

[0013] Step 5: Hbase returns the result set to Proxy through Appache phoenix, and Proxy encapsulates the result set and returns it to the application client.

[0014] As a further improvement of the above technical solution, in the step three, after the Proxy interacts with ElasticSearch, ElasticSearch generates an index array according to the query conditions.

[0015] As a further improvement of the above technical solution, the Proxy receives standard SQL and interacts with ElasticSearch. ElasticSearch generates an index array according to the query conditions and returns the index array to the Proxy in an asynchronous manner.

[0016] As a further improvement of the above technical solution, the specific steps of the asynchronous method are as follows:

[0017] S1: Proxy receives standard SQL and transmits it to multiple Handler processors for processing;

[0018] S2: Standard SQL processed by multiple Handler processors is transmitted to ElasticSearch for interaction;

[0019] S3: The ElasticSearch generates an index array according to the query conditions, and then returns the index array to the Proxy after the Responder Handler performs response processing.

[0020] As a further improvement of the above technical solution, the index array is a rowkey array.

[0021] As a further improvement of the above technical solution, the statement is an executable hsql statement.

[0022] A device based on Hbase query performance optimization, the improvement of which includes:

[0023] An application client, wherein the application client is used to send standard SQL;

[0024] Proxy, the Proxy is used to receive standard SQL, translate and submit the standard SQL;

[0025] ElasticSearch: ElasticSearch is used to receive query condition data and primary indexes synchronized from Hbase, generate index arrays based on query conditions, and interact with Proxy for data.

[0026] Appache phoenix, the Appache phoenix is ​​used to receive the SQL submitted by the Proxy, parse the SQL data, and submit the parsed statements to Hbase for execution. After the execution, Hbase returns the result set to the Proxy, and the Proxy encapsulates the data and returns it to the application client.

[0027] As a further improvement of the above technical solution, the Proxy includes multiple Handler processors for processing SQL.

[0028] As a further improvement of the above technical solution, the ElasticSearch includes a data processing unit for implementing data response processing for the ResponderHandler.

[0029] The beneficial effects of the present invention are: the present invention can quickly respond to data that needs to be queried, supports secondary indexes, supports standard SQL, greatly facilitates business data queries, and supports any combination of queries, greatly improving the freedom of queries. BRIEF DESCRIPTION OF THE DRAWINGS

[0030] The present invention is further described below in conjunction with the accompanying drawings and embodiments.

[0031] Figure 1 It is a schematic diagram of the structure of the present invention;

[0032] Figure 2 It is a schematic diagram of the architecture expansion of the present invention. DETAILED DESCRIPTION

[0033] The following will clearly and completely describe the concept, specific structure and technical effects of the present invention in combination with the embodiments and drawings, so as to fully understand the purpose, characteristics and effects of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of them. Based on the embodiments of the present invention, other embodiments obtained by technicians in this field without creative work are all within the scope of protection of the present invention. In addition, all the connection / connection relationships involved in the patent do not refer to the direct connection of components, but refer to the formation of a better connection structure by adding or reducing connection accessories according to the specific implementation situation. The various technical features in the invention can be combined interchangeably without conflicting with each other.

[0034] Reference Figure 1 The present invention discloses a device for optimizing query performance based on Hbase, comprising:

[0035] An application client, wherein the application client is used to send standard SQL;

[0036] Proxy, the Proxy is used to receive standard SQL, translate and submit the standard SQL;

[0037] ElasticSearch: ElasticSearch is used to receive query condition data and primary indexes synchronized from Hbase, generate index arrays based on query conditions, and interact with Proxy for data.

[0038] Appache phoenix, the Appache phoenix is ​​used to receive the SQL submitted by the Proxy, and perform data analysis on the SQL, submit the analyzed statements to Hbase for execution, and after Hbase executes, it will return the result set to the Proxy, and the Proxy will encapsulate the data and then return it to the application client. The present invention adopts the open source Appache Phoenix, which is a hadoop-based OLTP technology implemented on hbase, and has the characteristics of low latency, transactionality, SQL usage, and providing jdbc interface. In addition, Appache Phoenix also provides a solution for Hbase secondary index, enriches the diversity of Hbase queries, and inherits the characteristics of fast random query of Hbase massive data.

[0039] The Hbase of the present invention synchronizes the commonly used query fields and rowkey arrays to ElasticSearch, and uses the Relication mechanism of HBase. Each Region Server of HBase will have a WAL Log. When Put / Delete is performed, it will be written into the WAL Log first, and then a thread in the background of Hbase will randomly send the WAL Log to the Slave Region Server. The Slave Region Server will record the location to which it is synchronized on Zookeeper.

[0040] In the above embodiment, the present invention is a method for optimizing query performance based on Hbase, which synchronizes query condition data and primary index to ElasticSearch through Hbase, waits for the triggering of primary index and secondary index, and the user inputs instructions to the application client. The application client sends standard SQL to Proxy, Proxy interacts with ElasticSearch, triggers primary index and secondary index, Proxy translates and submits SQL to Appache phoenix for data parsing, Appache phoenix parses SQL into related statements and submits them to Hbase for execution, and the generated results are combined into a result set. Hbase returns the result set to Proxy through Appache phoenix, and Proxy encapsulates the result set and returns it to the application client. The present invention supports secondary indexing and standard SQL, which greatly facilitates business data query.

[0041] The Proxy includes multiple Handler processors for processing SQL. The ElasticSearch includes a data processing unit for implementing data response processing for the Responder Handler.

[0042] refer to Figure 1 , a method for optimizing query performance based on Hbase, comprising the following steps:

[0043] Step 1: Hbase synchronizes the query condition data and primary index to ElasticSearch;

[0044] Step 2: The application client sends standard SQL to Proxy, Proxy interacts with ElasticSearch, ElasticSearch generates an index array based on the query conditions, and returns the index array to Proxy, where the index array is a rowkey array;

[0045] Step 3: Proxy triggers the primary and secondary indexes based on the returned rowkey array, translates and submits SQL to Appache phoenix for data parsing;

[0046] Step 4: Appache phoenix parses the SQL into relevant statements and submits them to Hbase for execution. The generated results are combined into a result set. The statements are executable hsql statements.

[0047] Step 5: Hbase returns the result set to Proxy through Appache phoenix, and Proxy encapsulates the result set and returns it to the application client.

[0048] In the above embodiment, the present invention is a method for optimizing query performance based on Hbase, which synchronizes query condition data and primary index to ElasticSearch through Hbase, waits for the triggering of primary index and secondary index, and the user inputs instructions to the application client. The application client sends standard SQL to Proxy, Proxy interacts with ElasticSearch, triggers primary index and secondary index, Proxy translates and submits SQL to Appache phoenix for data parsing, Appache phoenix parses SQL into related statements and submits them to Hbase for execution, and the generated results are combined into a result set. Hbase returns the result set to Proxy through Appache phoenix, and Proxy encapsulates the result set and returns it to the application client. The present invention supports secondary index and standard SQL, which greatly facilitates business data query, and the present invention can support any combination of queries according to different query conditions, which greatly improves the freedom of query.

[0049] For further reference, Figure 2 , the ElasticSearch is referred to as ES, the Proxy receives standard SQL and interacts with ElasticSearch, ElasticSearch generates an index array according to the query conditions and returns the index array to the Proxy, the method adopted is asynchronous. The specific steps of the asynchronous method are as follows:

[0050] S1: Proxy receives standard SQL and transmits it to multiple Handler processors for processing;

[0051] S2: Standard SQL processed by multiple Handler processors is transmitted to ElasticSearch for interaction;

[0052] S3: The ElasticSearch generates an index array according to the query conditions, and then returns the index array to the Proxy after the Responder Handler performs response processing.

[0053] In the above embodiment, in order to improve query performance and prevent blocking, asynchronous mode is used for job submission and data response, thereby greatly improving the concurrent processing capability of the system. In addition, as the business develops, the number of Handler processors can be dynamically adjusted to improve processing capability, which can adapt to the development of the business very quickly.

[0054] The beneficial effects of the present invention are: the present invention can quickly respond to data that needs to be queried, supports secondary indexes, supports standard SQL, greatly facilitates business data queries, and supports any combination of queries, greatly improving the freedom of queries.

[0055] The above is a specific description of the preferred implementation of the present invention, but the invention is not limited to the embodiments. Those skilled in the art can make various equivalent modifications or substitutions without violating the spirit of the present invention. These equivalent modifications or substitutions are all included in the scope defined by the claims of this application.

Claims

1. A method for optimizing query performance based on Hbase, characterized in that: The following steps are involved: Step 1: Hbase synchronizes the query condition data and primary index to ElasticSearch; Step 2: The application client sends standard SQL to the Proxy, which interacts with ElasticSearch; Step 3: Proxy interacts with ElasticSearch to generate an index array and returns the index array. Porxy triggers the primary and secondary indexes, translates and submits SQL to Appache phoenix for data parsing. In the step 3, after the Proxy interacts with ElasticSearch, ElasticSearch generates an index array according to the query conditions; the Proxy receives the standard SQL and interacts with ElasticSearch, ElasticSearch generates an index array according to the query conditions, and returns the index array to the Proxy in an asynchronous manner; The specific steps of the asynchronous method are as follows: S1: Proxy receives standard SQL and transmits it to multiple Handler processors for processing; S2: Standard SQL processed by multiple Handler processors is transmitted to ElasticSearch for interaction; S3: The ElasticSearch generates an index array according to the query conditions, and then returns the index array to the Proxy after the Responder Handler performs response processing; The index array is a rowkey array; Step 4: Appache phoenix parses the SQL into relevant statements and submits them to Hbase for execution, and the generated results are combined into a result set; the statements are executable hsql statements; Step 5: Hbase returns the result set to Proxy through Appache phoenix, and Proxy encapsulates the result set and returns it to the application client.

2. A device for optimizing query performance based on Hbase, characterized in that: The device is used to implement a method for optimizing query performance based on Hbase as described in claim 1; include: An application client, wherein the application client is used to send standard SQL; Proxy, the Proxy is used to receive standard SQL, translate and submit the standard SQL; ElasticSearch: ElasticSearch is used to receive query condition data and primary indexes synchronized from Hbase, generate index arrays based on query conditions, and interact with Proxy for data. Appache phoenix, which is used to receive the SQL submitted by Proxy, parse the SQL data, and submit the parsed statements to Hbase for execution. After Hbase executes, it returns the result set to Proxy, which encapsulates the data and returns it to the application client; The Proxy includes multiple Handler processors for processing SQL; The ElasticSearch includes a data processing unit for implementing data response processing for the Responder Handler.

Images