Disclosure of Invention
The invention provides a distributed newSQL database system, which realizes complex business logic, meets the requirement of non-primary key query, optimizes transaction operation under the condition of high concurrency, and supports distributed transactions, semi-structured data, full-text retrieval and efficient picture storage.
In order to achieve the technical purpose, the invention adopts the following technical scheme:
a distributed newSQL database system comprises
The database interface is used for sending a request to the Master by a user and receiving a result returned by the Master;
the Master is used for accessing user requests in a JDBC and ODBC mode, coordinating data communication among a plurality of processors and managing the whole flow, and preferentially sending the user requests to the SQLPLaner;
the SQLPLaner is used for analyzing the user request, compiling and customizing an execution plan;
the distributed transaction manager is used for coordinating multiple parties in the plan to finish distributed transaction management;
and the parallel task executor is used for executing tasks in charge of the plan in parallel and merging and summarizing the data obtained from the database to return to the master.
Further improvements to the above scheme are as follows
And the parallel task executor acquires data from the database through the hbase and the search engine server.
The master is connected with a monitor and is used for being responsible for metadata management and monitoring the load of the underlying hbase regions, avoiding that the load of a specific Region is too high, and redistributing the regions by using the hbase subprocessor.
The invention also provides a method for generating an execution plan by utilizing the SQLPLaner of the distributed newSQL database system, which comprises the following steps
Inputting SQL sentences through the database interface;
judging whether the SQL already exists in the shared cache pool, if so, outputting an execution plan corresponding to the SQL;
otherwise, carrying out syntax check and semantic check on the SQL statement, and carrying out view and expression conversion on the SQL statement after the syntax check and the semantic check are passed;
carrying out optimizer selection according to the conversion result;
selecting a data connection mode and a connection sequence according to a selection result of the optimizer;
selecting a search path according to the connection mode and the connection sequence;
and generating an execution plan according to the search path and outputting the execution plan.
The invention also provides a method for establishing and querying a plurality of secondary indexes by using the distributed newSQL database system, which comprises the following steps
Generating an index table aiming at data by using a Coprocessor and a Filter of the hbase; the coprocessors write index data into the index table in parallel in a reverse index mode according to the index definition, and therefore a plurality of secondary indexes are established;
the Master dynamically calculates the cost of using the index according to the query condition; the coprocessors can firstly inquire the index table according to the index definition and the inquiry condition, and parallelly inquire the data table again through the inquiry result of the index table.
The invention also provides a method for realizing semi-structured data access by using the distributed newSQL database system, which comprises the following steps
The parallel task executor writes JSON data as a common character string type as a whole into the data table of the hbase as a field; the copessor in the hbase extracts data in the JSON according to the field description, writes the index data into another hbase index table in an inverted index mode, and completes the storage of semi-structured data;
the parallel task executor queries an index table in parallel by using a coprocessor according to query conditions; the index coprocessors in the hbase return the index ID of the index table to the parallel task executor; and the parallel task executor queries a data table by utilizing an API (application programming interface) of the hbase according to the index ID, returns a result and finishes obtaining the semi-structured data.
The invention also provides a method for realizing the picture data access by utilizing the distributed newSQL database system, which is characterized by comprising the following steps
The parallel task executor generates image data into an image data format encrypted by an information summary algorithm, and writes the encrypted image data into an original data table; the parallel task executor writes the encrypted picture data into a picture data table for independent storage;
the parallel task executor queries an original data table according to query conditions to obtain image data encrypted by an information abstract algorithm; and the parallel task executor queries a picture data table by using the API of the hbase according to the encrypted image data to acquire picture data.
As an improvement of the above scheme, the hbase bottom layer adds an LOB type, establishes an alternative index for the LOB type, stores large object picture data as a bitmap in the database, stores the picture data in an independent data table as the bitmap, and stores only an index ID in an original data table.
The invention also provides a method for realizing full-text retrieval by utilizing the distributed newSQL database system, which comprises the following steps
The parallel task executor writes fields needing full-text retrieval into a data table of the hbase as common character string types for storage, and a coprocessor in the hbase writes data into a search engine server for indexing according to the description of the fields;
the parallel task executor queries a specific index ID from the search engine server according to a query condition, the search engine server returns the index ID according to the query condition, and the parallel task executor queries a data table by using the API of the hbase according to the index ID to acquire query data.
Advantageous effects
The distributed NewSQL database system provided by the invention provides a brand new mode covering the large data storage and high-speed read-write capability of the hbase, and simultaneously solves the practical application problem that the hbase cannot be considered at the same time. The system supports SQL, supports JDBC/ODBC, supports SQL through an interactive analysis engine UrunSQL, and can realize complex business logic on the system by compiling SQL by a user, thereby greatly reducing the use cost; the JDBC/ODBC interface is supported, and the ETL process is greatly simplified.
The distributed NewSQL database system supports the secondary index, efficiently solves the non-primary key query requirement, allows a user to flexibly establish the secondary index according to specific service logic, often establishes a plurality of secondary indexes in practical application, dynamically calculates the cost of using the index according to the query condition, and automatically selects the most appropriate index.
The distributed NewSQL database system supports distributed transactions, cross-row and cross-table distributed transactions, supports complete ACID transaction semantics, optimizes transaction operation under high concurrency conditions and can meet most OLTP applications.
The distributed NewSQL database system supports semi-structured data and JSON data format, and a user can directly store the data in the JSON format in a database connected with the hbase of the system, inquire any field of the JSON, create indexes and delete and modify the data.
The distributed NewSQL database system supports full-text retrieval, supports the distributed full-text retrieval through the Solr, and can enable a user to create a full-text index for a table of the user and search in the SQL by using a full-text retrieval syntax.
The distributed NewSQL database system supports efficient picture storage, LOB storage is added on the hbase bottom layer of the system, the LOB can efficiently meet the binary storage requirement that the size of a single piece of data is hundreds of K to 10M, and a user can meet the picture storage requirement through the LOB.
The distributed NewSQL database system has high availability, allows a plurality of copies to be maintained for the region at the same time, and the region multi-copy mechanism can ensure that the reading service is not influenced at all when the region server is down, the second-level recovery of the writing service is realized, the availability is effectively improved, and the requirement of the online service is met.
Detailed Description
The technical solutions in the embodiments of the present invention will be clearly and completely described below with reference to the drawings in the embodiments of the present invention, and it is obvious that the described embodiments are only a part of the embodiments of the present invention, and not all of the embodiments. All other embodiments, which can be derived by a person skilled in the art from the embodiments given herein without making any creative effort, shall fall within the protection scope of the present invention.
Referring to fig. 1, it is a schematic structural diagram of a distributed newSQL database system, also called an unrubate database system, according to embodiment 1 of the present invention, and includes:
the database interface, namely JDBC \ ODBC, is used for sending a request to the Master by a user and receiving a result returned by the Master;
the Master is used for accessing user requests in a JDBC and ODBC mode, coordinating data communication among a plurality of processors and managing the whole flow, and preferentially sending the user requests to the SQLPLaner;
the SQLPLaner is used for analyzing the user request, compiling and customizing an execution plan;
the DTM, namely a distributed transaction manager, is used for coordinating multiple parties in the plan to finish distributed transaction management;
the Worker is a parallel task executor and is used for executing tasks of the plan in parallel and merging and summarizing data obtained from the database to return to the master;
the hbase and the search engine server Solr are respectively connected with different workers, and the workers acquire data from the database through the hbase and the search engine server Solr;
the master is connected with a monitor and is used for being responsible for metadata management and monitoring the load of the underlying hbase regions, avoiding that the load of a specific Region is too high, and redistributing the regions by using the hbase subprocessor.
The invention discloses a distributed NewSQL database based on hadoop big data technology. When a user requests to enter, the following processes are mainly executed:
s1, Master is mainly responsible for accessing user request in JDBC, ODBC mode and coordinating data communication among multiple roles, managing the whole flow, it will send the request to SQLPLaner at first;
s2, SQLPLaner is mainly used for analyzing user requests, compiling SQL and customizing execution plans through a cloud-lubricated self-developed high-speed SQL engine UrunSQL;
s3, DTM, mainly used for coordinating multiple parties to complete distributed transaction management when the execution plan relates to transactions. Realizing distributed transaction processing and transaction management by using Java transaction processing API (JTA);
s4, the Worker is that the parallel task executor is mainly responsible for executing the tasks of the execution plan in parallel, and data obtained from the database is merged and summarized to be returned to the master;
s5, the master returns the request result to the user.
The Coprocessor is a Coprocessor provided by the hbase, and developers can realize efficient parallel processing on data on the basis of the Coprocessor, and meanwhile, a region management extension interface of a master end is provided.
JTA, a Java Transaction API, allows an application to perform a distributed Transaction-accessing and updating data on two or more networked computer resources.
Solr is a high-performance Lucene-based full-text search server, and simultaneously expands the server, provides richer query languages than Lucene, simultaneously realizes configurability and expandability, optimizes the query performance, provides a perfect function management interface, and is a very excellent full-text search engine.
Referring to fig. 2, a flowchart of a method for generating an execution plan by an SQLPlaner in a distributed newSQL database system according to embodiment 2 of the present invention is shown, where the embodiment is based on embodiment 1, and the method for generating an execution plan by an SQLPlaner includes the following steps:
1) inputting an SQL statement;
2) judging whether the SQL already exists in the shared cache pool, if so, outputting an execution plan corresponding to the SQL, and if not, executing the next step;
3) syntax checking is carried out on the SQL statement, if the syntax is wrong, error information is returned to a user, and the syntax checking is passed, namely the next step is executed;
4) performing semantic check on the SQL statement, if the semantic is wrong, returning error information to a user, and if the semantic check is passed, executing the next step;
5) carrying out view and expression conversion on the SQL statement;
6) selecting an optimizer according to the conversion result of the previous step;
7) selecting a data connection mode and a connection sequence according to a selection result of the optimizer;
8) selecting a searched path according to the connection mode and the connection sequence;
9) generating an execution plan according to the search path;
10) output the execution plan.
And returning the execution plan to the master after the execution plan is customized, wherein the master judges whether the intervention of the distributed transaction manager is needed or not according to the content of the execution plan, if so, the step of S3 in the embodiment 1 is executed, otherwise, the step of S3 in the embodiment 1 is skipped, and the step of S4 in the embodiment 1 is executed.
Referring to fig. 3, which is a flowchart of a method for establishing and querying a plurality of secondary indexes by a distributed newSQL database system according to embodiment 3 of the present invention, the embodiment is based on embodiment 2, wherein the method for establishing and querying a plurality of secondary indexes by a distributed newSQL database system includes the following steps:
and (3) writing request:
11) a user initiates a write request;
12) the master processes the sql request and generates an execution plan in combination with the SQLPlaner;
13) writing the data field into a data table by the worker according to the execution plan;
14) a coprocessor mechanism inside the hbase is utilized to realize synchronization and write the data into an index table in a reverse index generation mode;
15) returning the processing result of the hbase to the master by the worker;
16) the master returns the results to the user.
And (3) reading request:
21) user initiated read request
22) Master processes sql requests and generates execution plans in conjunction with SQLPlaner
23) The worker firstly queries the index table according to the execution plan, and the coprocessors are used for improving the query parallelism.
24) The index coprocessor in hbase returns the index ID of the index table
25) And (5) the worker queries the data table by utilizing the hbase API according to the index ID and returns the data table to the master.
26) The master merges the query results and returns the result to the user
Secondary indexes are supported, and the non-primary key query requirement is efficiently solved: the UrunBase allows a user to flexibly establish secondary indexes according to specific service logic, in practical application, the user often establishes a plurality of secondary indexes, and when the UrunBase is used, the UrunBase dynamically calculates the cost of using the indexes according to query conditions and automatically selects the most appropriate index. The query of hbase for rowkey is extremely efficient, so the implementation manner of the secondary index is to generate an index table for data by using Coprocessor and Filter of hbase. When writing data, the coprocessors writes the index data into the index table in a reverse index mode according to the index definition, preferentially queries the index table in a query stage according to the index definition and query conditions, and queries the data table again through a query result of the index table. Meanwhile, the parallelism of the coprocessors is utilized to improve the overall query speed.
The UrunBase supports the refinement work of the semi-structured data, the picture data and the full-text retrieval aiming at different storage types by taking the above two-level index flow as a reference.
Referring to fig. 4, a flowchart of a method for implementing semi-structured data access by a distributed newSQL database system according to embodiment 4 of the present invention is shown, where the embodiment is based on embodiment 3, where the method for implementing semi-structured data access by a distributed newSQL database system includes a write request:
31) writing JSON data as a common character string type as a whole into a data table of hbase as a field by worker
32) The copessor in the hbase extracts the data in the JSON according to the field description, and writes the index data into another hbase index table in an inverted index mode.
And (3) reading request:
41) and (5) the worker queries the index table according to the query condition, wherein the coprocessors are utilized to improve the parallelism of the query.
42) The index coprocessor in hbase returns the index ID of the index table
43) The worker queries the data table by utilizing the hbase API according to the index ID and returns a result
The UrunBase supports a JSON data format, and a user can directly store the data in the JSON format in the UrunBase, inquire any field of the JSON, create an index and delete and modify the field. JSON type data is newly added to the hbase bottom layer by the UrunBase, the JSON data is integrally stored in the bottom layer HFile, and the JSON is used as a nested type to carry out indexing when a secondary index is constructed, so that any field query, index creation and deletion aiming at the JSON can be supported.
Referring to fig. 5, which is a flowchart of a method for implementing picture data access by a distributed newSQL database system according to embodiment 5 of the present invention, the embodiment is based on embodiment 3, wherein the method for implementing picture data access by a distributed newSQL database system includes the steps of
And (3) writing request:
51) and (3) generating the MD5 from the picture data by the worker, and writing the picture MD5 into the original data table.
52) And writing the picture data into a picture data table by the worker for independent storage.
And (3) reading request:
61) the worker queries the original data table according to the query condition to obtain the MD5 of the picture
62) And (5) the worker queries the picture data table by utilizing the hbase API according to the picture MD5 and returns a result.
The UrunBase provides LOB storage, the LOB can efficiently meet the binary storage requirement that the size of a single piece of data is hundreds of K to 10M, and a user can meet the picture storage requirement through the LOB. The method includes the steps that an LOB type is added to the hbase bottom layer by UrunBase, the LOB type refers to the implementation of a BLOB type in SQL, a large object is stored as a bitmap in a database, the LOB is implemented to establish another type of index aiming at the LOB type, picture data are stored in an independent data table in the bitmap mode, an original data table only stores an index ID, and therefore the size of the data table is reduced. Because the picture data can only be modified in an atomic coverage way and can be inquired independently, the retrieval speed can be greatly improved when the image data is inquired for a non-picture field.
Referring to fig. 6, which is a flowchart of a method for implementing full-text retrieval by a distributed newSQL database system according to embodiment 6 of the present invention, the embodiment is based on embodiment 3, wherein the method for implementing full-text retrieval by a distributed newSQL database system includes
And (3) writing request:
71) the worker writes the field needing full text retrieval as a common character string type into a data table 7 of hbase for storage
2) The coprocessors in the hbase write the data into the solr for indexing according to the field description
And (3) reading request:
81) the worker queries the specific index ID in the solr according to the query condition
82) The solr returns the index ID according to the query condition.
83) And (5) the worker queries the data table by utilizing the hbase API according to the index ID and returns a result.
The UrunBase supports distributed full-text retrieval through Solr, and a user can create a full-text index for a table of the user and search in SQL by using full-text retrieval syntax. The mode is a special extension of the secondary index, and is realized by using a coprocessor, and the index data is not stored in another index table but stored in the SOLR aiming at the field needing full-text retrieval, and the SOLR provides the full-text retrieval function. When data is queried, the query statement of the field which is indexed by the full text is converted from the SQL conditional statement into the query expression of the SOLR for further query, and the return result of the SOLR is converted into a universal data format for further return.
It will be understood by those skilled in the art that all or part of the processes of the methods of the embodiments described above can be implemented by a computer program, which can be stored in a computer-readable storage medium, and when executed, can include the processes of the embodiments of the methods described above. The storage medium may be a magnetic disk, an optical disk, a Read-Only Memory (ROM), a Random Access Memory (RAM), or the like.
While the foregoing is directed to the preferred embodiment of the present invention, it will be understood by those skilled in the art that various changes and modifications may be made without departing from the spirit and scope of the invention.