Fusion method of analytical database and full-text search engine and related device

Abstract

The present application discloses a method for integrating an analytical database and a full-text search engine and a related device, the method comprising: establishing an ES external table in the analytical database; wherein the ES external table includes at least one index specified by a client, for storing relevant configuration information of the full-text search engine; for each index in the ES external table, in response to verifying that there is no problem with the relevant attributes of the index and in response to the automatic creation function of the index being turned on, creating an index in the full-text search engine, and using the index to establish an association relationship between the analytical database and the full-text search engine in the ES external table; in response to the successful establishment of the association relationship, connecting to the full-text search engine and obtaining the association information of the index from the full-text search engine; in response to no abnormality in the association information, saving the association information to the ES external table. Data in the full-text search engine can be accessed using only JDBC, improving query efficiency.

Description

Technical Field

[0001] The present application relates to the technical field of distributed analytical databases, and in particular to a method for integrating an analytical database and a full-text search engine and related devices. Background Art

[0002] With the popularization of the Internet and the widespread application of the Internet of Things, the amount of data in various business systems has also increased rapidly. The technology that could be handled by the original single-machine analytical database system has become difficult to handle with such a huge amount of data. Since the single-machine analytical database cannot be horizontally expanded, the distributed analytical database came into being. However, while the distributed analytical database solves one problem, it also introduces a series of new problems. There are many distributed analytical databases, but no distributed analytical database can combine the strengths of all, that is, it is impossible to achieve the same performance for large-scale data and small-scale data on a single machine based on the same analytical database while increasing the number of servers.

[0003] Take mathematical formulas as an example (DB1 represents a stand-alone analytical database, DB2 and DB3 represent different types of distributed analytical databases, Data represents a certain amount of data within the processing range of a stand-alone analytical database, and Node represents a server node): DB1*Data / Node≠DB2*N*Data / N*Node; DB1*Data / Node≈DB2*N*Data / N*Node+DB2*M*Data / M*Node+…; The analytical database system mainly provides data storage and query capabilities, involving all kinds of technical types. For example, for the same indexing function, there are many algorithms and data structures. Different algorithms and data structures have different advantages. For example, a good update performance may not necessarily have good insertion performance. Similarly, storage compression algorithms vary greatly. Some compression algorithms have a higher compression ratio, but the CPU and other computing resources required for compression will also be higher.

[0004] Therefore, a new method of integrating analytical databases and full-text retrieval engines is urgently needed to solve the above problems. Summary of the invention

[0005] The main technical problem solved by the present application is to provide a method and related device for integrating an analytical database and a full-text search engine, so that the database itself or the full-text search engine can be accessed by simply using JDBC.

[0006] To solve the above technical problems, a technical solution adopted in the present application is: to provide a method for integrating an analytical database and a full-text search engine, comprising: establishing an ES external table in the analytical database; wherein the ES external table includes at least one index specified by the client, for storing relevant configuration information of the full-text search engine, and the relevant configuration information includes relevant attributes of the at least one index; for each index in the ES external table, in response to verifying that there is no problem with the relevant attributes of the index and in response to the automatic creation function of the index being turned on, the index is created in the full-text search engine, and the index is used to establish an association relationship between the analytical database and the full-text search engine in the ES external table; in response to the successful establishment of the association relationship, the full-text search engine is connected and the association information of the index is obtained from the full-text search engine; in response to the association information having no abnormalities, the association information is saved to the ES external table.

[0007] Among them, after the step of saving the associated information to the ES external table in response to there being no abnormality in the associated information, the step includes: waking up the ES external table at preset time intervals, and connecting the ES external table to the full-text retrieval engine to periodically synchronize the associated associated information in the ES external table.

[0008] Among them, after the step of saving the associated information to the ES external table in response to the association information having no abnormality, the following steps are included: resetting the object builder and reading the next tuple to be inserted from the analytical database; wherein the object builder is used to convert the tuple to be inserted into a corresponding string object, which can be recognized by the full-text retrieval engine; in response to the fact that there are still tuples to be inserted in the analytical database, using the object builder to build a batch insert request body; in response to the number of tuples to be inserted contained in the batch insert request body reaching a first threshold, connecting a master node obtained from the full-text retrieval engine, and writing the tuples to be inserted in batches to the corresponding index in the master node; in response to successfully writing the tuples to be inserted in batches to the corresponding index in the master node, obtaining the returned first parsing http feedback information; in response to the fact that there is no error in the first parsing http feedback information, returning to the step of resetting the object builder and reading the next tuple to be inserted from the analytical database.

[0009] Among them, after the step of resetting the object builder and reading the next tuple to be inserted from the analytical database, it also includes: in response to the tuple to be inserted not existing in the analytical database and in response to the object builder being in a non-empty state, entering into a master control node obtained from the full-text retrieval engine, and writing the tuples to be inserted in batches into the corresponding index in the master control node.

[0010] Among them, after the step of saving the associated information to the ES external table in response to the associated information having no abnormalities, the step also includes: in response to the analytical database receiving the structured query language sent by the client, parsing the structured query language, and sending the parsed structured query language to the index fragment corresponding to the structured query language in the full-text retrieval engine.

[0011] Among them, the full-text search engine includes at least one index fragment, the at least one index fragment corresponds to an execution node in the analytical database, and one of the index fragments includes at least one file; the step of parsing the structured query language and sending the parsed structured query language to the index fragment corresponding to the structured query language in the full-text search engine includes: for each execution node of the analytical database, traversing all filter conditions in the structured query language, and constructing a domain-specific language that can be pushed down to the full-text search engine according to the filter conditions; reading the index fragment corresponding to the execution node, and reconstructing the cursor query request in combination with the domain-specific language or cursor parameters; in response to connecting the index fragment and successfully associating the domain-specific language or the cursor parameter with the index fragment, obtaining a returned second parsing http feedback information; in response to the second parsing http feedback information having a file returned from the index fragment and in response to the successful parsing of the file, converting the parsed file into a corresponding tuple in the analytical database.

[0012] Among them, in response to the second analysis http feedback information containing a file returned from the index fragment, and in response to the successful parsing of the file, after the step of converting the parsed file into a corresponding tuple in the analytical database, it includes: filtering out the first tuple that meets the remaining filtering conditions from the tuple and returning it to the step of reconstructing the cursor query request in combination with the domain specific language or cursor parameters; wherein the remaining filtering conditions are filtering conditions that cannot be pushed down to the full-text retrieval engine.

[0013] Among them, after the step of establishing the ES external table in the analytical database, it also includes: for each index in the ES external table, in response to verifying that there is no problem with the relevant attributes of the index and in response to the automatic creation function of the index being turned off, using the index to establish an association relationship between the analytical database and the full-text search engine in the ES external table, and entering the step of connecting to the full-text search engine and obtaining the association information of the index from the full-text search engine in response to the successful establishment of the association relationship.

[0014] To solve the above technical problems, another technical solution adopted in the present application is: to provide an electronic device, comprising a memory and a processor coupled to each other, wherein the memory stores program instructions, and the processor is used to execute the program instructions to implement the fusion method of the analytical database and the full-text retrieval engine mentioned in any of the above embodiments.

[0015] To solve the above technical problems, another technical solution adopted in the present application is: providing a computer-readable storage medium, wherein the computer-readable storage medium stores a computer program, and the computer program is used to implement the fusion method of the analytical database and the full-text retrieval engine mentioned in any of the above embodiments.

[0016] Different from the prior art, the beneficial effect of the present application is that the fusion method of the analytical database and the full-text search engine provided by the present application includes: establishing an ES external table in the analytical database; wherein the ES external table includes at least one index specified by the client, which is used to save the relevant configuration information of the full-text search engine, and the relevant configuration information includes the relevant attributes of at least one index; for each index in the ES external table, when it is verified that there is no problem with the relevant attributes of the index, and when the automatic creation function of the index is turned on, the index is created in the full-text search engine, and the index is used to establish the association relationship between the analytical database and the full-text search engine in the ES external table; in response to the successful establishment of the association relationship, the full-text search engine is connected and the association information of the index is obtained from the full-text search engine; in response to the association information being normal, the association information is saved in the ES external table. Through this design method, the user only needs to connect to the analytical database, and only needs to use JDBC (using standard SQL) to access the database itself or the full-text search engine, and the interaction between the analytical database and the full-text search engine does not need to be transferred by the user, and the distributed analytical database can automatically and concurrently access the data in the full-text search engine, thereby improving the query efficiency. BRIEF DESCRIPTION OF THE DRAWINGS

[0017] In order to more clearly illustrate the technical solutions in the embodiments of the present application, the following briefly introduces the drawings required for use in the description of the embodiments. Obviously, the drawings described below are only some embodiments of the present application. For ordinary technicians in this field, other drawings can be obtained based on these drawings without creative work. Among them:

[0018] Figure 1 It is a schematic diagram of the business layer directly connecting to different types of underlying analytical databases in the prior art;

[0019] Figure 2 yes Figure 1 Schematic diagram of the corresponding usage scenario;

[0020] Figure 3 It is a schematic diagram of providing an adaptation layer on the upper layer of the analytical database to shield the underlying analytical database in the prior art;

[0021] Figure 4 yes Figure 2 Schematic diagram of the corresponding usage scenario;

[0022] Figure 5 It is a schematic diagram of a usage scenario of the fusion method provided in this application;

[0023] Figure 6 It is a flowchart of an implementation method of a fusion method of an analytical database and a full-text search engine of the present application;

[0024] Figure 7 yes Figure 6 A schematic diagram of a flow chart of an implementation method after step S11;

[0025] Figure 8 It is a schematic diagram of the execution of analytical database and full-text search engine;

[0026] Fig. 9 yes Figure 6 A schematic flow chart of another embodiment after step S11;

[0027] Fig.10 It is a structural diagram of an implementation method of a fusion system of an analytical database and a full-text search engine of the present application;

[0028] Fig.11 It is a schematic diagram of a framework of an implementation method of an electronic device of the present application;

[0029] Fig.12 It is a schematic diagram of a framework of an implementation scheme of a computer-readable storage medium of the present application. DETAILED DESCRIPTION

[0030] The following will be combined with the drawings in the embodiments of the present application to clearly and completely describe the technical solutions in the embodiments of the present application. Obviously, the described embodiments are only part of the embodiments of the present application, not all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of this application.

[0031] Different types of analytical databases have their own trade-offs when making technical selections, which has led to a wide variety of analytical databases, including OLAP analytical databases, OLTP analytical databases, full-text retrieval analytical databases, and so on. Specifically, at the business level, for example, analytical databases with strong analytical capabilities will lack transaction capabilities or concurrency capabilities; analytical databases with good single-table query performance will not be satisfactory in multi-table association or aggregation capabilities. Therefore, the business scenarios that different types of analytical databases can meet are relatively simple and limited, and no analytical database can achieve excellent performance in all scenarios. In this case, a business system often needs to adopt multiple different types of analytical databases to cover all business needs and ensure that each business need can achieve good performance and stability during the implementation process.

[0032] In order to solve the problem that a business system often needs to use multiple different analytical database types to cover all business needs, the following two implementation solutions are usually used. Please refer to Figure 1-Figure 4 , Figure 1 It is a schematic diagram of the business layer directly connecting to different types of underlying analytical databases in the prior art. Figure 2 yes Figure 1 Schematic diagram of the corresponding usage scenario, Figure 3 It is a schematic diagram of providing an adaptation layer on the upper layer of the analytical database to shield the underlying analytical database in the prior art. Figure 4 yes Figure 2 Schematic diagram of the corresponding usage scenario. Figure 1As shown, in this solution, data will be stored in multiple copies, that is, as many copies of data as several analytical databases are used. Faced with different business scenarios, users can judge for themselves which type of analytical database is more suitable, including the performance of queries and the pressure impact of the operation on the analytical database service, and develop corresponding codes to perform operations. Different types of analytical databases are often used in different ways, so it is necessary to develop multiple sets of codes to adapt to different types of analytical databases. When data changes, such as adding, deleting, and modifying data, multiple analytical databases need to be operated at the same time. Different types of analytical databases have different constraints on data formats, which may cause data in one analytical database to be stored, while data in another analytical database cannot be stored. Since users directly operate the analytical databases, users at the business layer need to be familiar with various types of analytical databases. Figure 2 As shown in the figure, one use scenario of the full-text search engine (Elasticsearch, ES) is to use it in combination with the analytical database, using ES for fast single-table retrieval and using the analytical database for complex multi-table joins. However, since the analytical database often uses the JDBC API, and ES uses the REST API, users need to understand and operate these two interfaces. Figure 3 As shown in the figure, this solution is also often used in system design. Figure 1 The difference between the solutions shown is that an adapter service is added between the business layer and the analytical database layer, but its core logic has not changed. It is mainly to solve the need to use different types of analytical databases in the same system. The adapter service belongs to the PaaS layer service. It is an independent service separated from the business layer and the analytical database layer, and has no intrusion to the underlying analytical database layer. The adapter service layer often needs to have a certain structured query language SQL parsing capability to forward different types of query requirements to the corresponding analytical database for query requests. When parsing SQL statements, it is necessary to identify which type of query it belongs to. For example, for a single table filtering query based on the attribute field, which does not involve aggregation operations such as grouping statistics, the query request can be sent to the full-text search engine; while for complex multi-table associations or query requests for aggregation operations such as grouping statistics, they should be forwarded to the OLAP type analytical database for processing. With this adapter service, upper-level business developers do not need to be aware of the different types of underlying analytical databases, and do not need to worry about which type of analytical database should be used to perform query operations in the corresponding business scenarios, such as Figure 4As shown, the system only needs to use a set of JDBC APIs to operate the analytical database or the full-text search engine. For the client, it does not need to understand the REST API provided by the full-text search engine, but can only see a single type of analytical database, and the usage method also needs to be unified.

[0033] Please also read Figure 5-Figure 6 , Figure 5 is a schematic diagram of a usage scenario of the fusion method provided in this application, Figure 6 This is a flow chart of an implementation method of the fusion method of the analytical database and the full-text search engine of the present application. The fusion method includes:

[0034] S1: Create an ES external table in Analytic DB.

[0035] In this embodiment, a CREATE TABLE execution plan generated by parsing SQL is given, and the CREATE TABLE (ES) execution process is entered. Specifically, the ES external table includes at least one index specified by the client, which is used to save relevant configuration information of the full-text retrieval engine ES, and the relevant configuration information includes relevant attributes of at least one index. Specifically, in this embodiment, the above-mentioned ES external table is different from an ordinary analytical database table. It does not store any data in the analytical database itself, and is only used to save configuration-related information of the full-text retrieval engine ES. The ES external table is established through CREATE TABLE, but allows a variety of additional attributes to be provided. For example, CREATE TABLE is shown in the following table.

[0036]

[0037] When creating a table, specify ELASTICSEARCH (case-insensitive) in Engine to create an ES external table, and PROPERTIES to specify additional properties, such as the name of the necessary ES relationship (ES index / type name), the address of the ES master node, and the automatic creation function enable_auto_create to indicate whether to automatically create the ES index / type relationship or associate with an existing ES index / type relationship, and the bulk_insert_num to indicate the maximum number of documents included in each batch of data when importing data to the external table. Of course, it also includes other ES properties such as the number of fragments number_of_shards, the number of clones number_of_replicas, etc.

[0038] Specifically, in this embodiment, the relevant attributes of each field in the index can be set separately through "field name.attribute name", and the attribute information of the index field in the full-text search engine of the associated ES external table can be specified. For example, the "c3.index" in the above text will set or expect the index attribute of the c3 field in the mapping relationship of the ES index test_abc / type_doc to be not_analyzed. Specifically, c3 here is the field name, index is the word segmentation type attribute, and setting it to not_analyzed means that no word segmentation processing is performed.

[0039] However, not all relationship index / type attributes can be specified through CREATE TABLE, because some attributes cannot be simply set through "key" = "value". If there is such a requirement, you can directly connect to the full-text search engine and manually establish the relationship, and then associate it with CREATE TABLE, that is, specify "enable_auto_create" = "false".

[0040] S2: For each index in the ES external table, determine whether there are any problems with the relevant attributes of the index.

[0041] Specifically, in the fusion method provided in this application, a necessary step is to verify each relevant property specified in PROPERTIES(), but some relevant properties must be provided, and some relevant properties are optional, and this application does not limit this.

[0042] S3: If yes, determine whether the automatic index creation function is enabled.

[0043] Specifically, for each index in the ES external table, if there is no problem with the relevant attributes of the index, determine whether the attribute value of the index's automatic creation function "enable_auto_create" is true.

[0044] S4: Otherwise, throw an exception.

[0045] Specifically, for each index in the ES external table, if there is a problem with the relevant attributes of the index, that is, the relevant attributes are undefined or misspelled, indicating that the creation failed, an exception is thrown and the process ends.

[0046] S5: If yes, create an index in the full-text search engine, and use the index to establish an association between the analytical database and the full-text search engine in the ES external table.

[0047] Specifically, if the automatic creation function of the index is turned on, that is, the attribute value of "enable_auto_create" is true, an index is created in the full-text search engine, and the index is used to establish an association relationship between the analytical database and the full-text search engine in the ES external table. In this embodiment, the mapping relationship between the default analytical database table field type and the field type of the full-text search engine is shown in Table 1.

[0048] Specifically, in this embodiment, the default type mapping relationship in Table 1 can be overwritten with the "field name.attribute name" described above, such as "c3.type" = "keyword", which is an example of another scenario of the function of "users can customize some attribute information". For example, if the type attribute of the c3 field is originally other values, it can be overwritten by setting it to keyword here. The keyword shall prevail.

[0049] Table 1 Mapping relationship between Analytic Database table field types and ES field types

[0050] Analytical Database ES tinyint byte smallint short int integer bigint long float float double double varchar text+keyword(fields) char text+keyword(fields) date date datetime date

[0051] In addition, in this embodiment, the reason for defaulting to such a mapping relationship is that the field is expected to maintain the word segmentation search capability and provide full word matching. The "text+keyword" described in Table 1 above specifically refers to the ES fields feature, that is, the same field can be of both text type and keyword type, that is:

[0052]

[0053]

[0054] S6: Otherwise, proceed to step S8.

[0055] Specifically, if the automatic creation function of the index is turned off, that is, the attribute value of "enable_auto_create" is false, it means that the specified association relationship already exists, and the full-text search engine is directly connected to obtain the association information of the index from the full-text search engine.

[0056] S7: Determine whether the association relationship is established successfully.

[0057] S8: If yes, connect to the full-text search engine and obtain the associated information of the index from the full-text search engine.

[0058] Specifically, if the association is established successfully, the full-text search engine is connected and the index association information is obtained from the full-text search engine. Before the table is built, the full-text search engine will be connected to obtain the association information, which can be field type mapping, field index / doc_values ​​on and off, and the shards distribution information where it is located. This information will be used when reading and writing the ES external table later. In this way, the data in the full-text search engine can be presented in the analytical database as a table, so that fast retrieval and analytical query can be taken into account at the same time to improve query efficiency.

[0059] S9: Otherwise, throw an exception.

[0060] Specifically, if the association fails to be established or the specified association already exists, indicating that the creation failed, an exception is thrown and the process ends.

[0061] S10: Determine whether there is any abnormality in the associated information.

[0062] S11: If yes, save the related information to the ES external table.

[0063] Specifically, if there is no abnormality in the related information, the related information will be saved in the ES external table.

[0064] S12: Otherwise, throw an exception.

[0065] Specifically, if there is an exception in the associated information, indicating that the creation failed, an exception is thrown and the process ends.

[0066] Specifically, in this embodiment, the association information on the ES side may change at any time, such as manual modification, such as shard migration, etc., so in addition to the table building stage, it is necessary to periodically synchronize the association information associated with the ES external table. In this embodiment, after each ES external table is established, including after the database is restarted, a background synchronization thread will be pulled up for it. Therefore, the fusion method provided in this application includes after step S11: wake up the ES external table every preset time interval (sleepsync_interval seconds), and connect the ES external table to the full-text search engine to periodically synchronize the association information associated with the ES external table. Specifically, the synchronization thread wakes up and connects to ES every sync_interval seconds to obtain the association information corresponding to the synchronization. In this embodiment, the configuration sync_interval defaults to 20s. Of course, in other embodiments, sync_interval can also be set to other values, which are not limited in this application. Specifically, each ES external table can re-specify the configuration in PROPERTIES() during the table building stage, such as "sync_interval" = "60" to adjust the synchronization period to 1 minute. After the ES external table is established, the insert / read operation can be performed.

[0067] With this design, users only need to connect to the analytical database and use JDBC (using standard SQL) to access the database itself or the full-text search engine. In addition, the interaction between the analytical database and the full-text search engine does not need to be intermediated by the user. The distributed analytical database can automatically and concurrently access the data in the full-text search engine, thereby improving query efficiency.

[0068] The following will introduce the insert operation in detail. ES external tables support insert operations. The data written to ES external tables will actually be inserted into the associated relationship. The SQL is shown in the following table.

[0069]

[0070] In the Analytic DB executor, it will be converted into an http request similar to the following, as shown in the following table.

[0071]

[0072] Specifically, in this embodiment, the maximum number of tuples contained in a _bulk operation (batch operation) is determined by the bulk insert parameter bulk_insert_num specified when the table is created, and the default value is 10000. Of course, if you need to improve the insertion efficiency, you can increase this value in PROPERTIES() during the table creation stage, and this application does not limit it here.

[0073] Specifically, in this embodiment, the entire insertion process is as follows, see Figure 7 , Figure 7 yes Figure 6 Schematic diagram of a flow chart of an implementation method after step S11. Specifically, after step S11, it includes:

[0074] S20: Reset the object builder and read the next tuple to be inserted from the analytical database.

[0075] In this embodiment, the INSERT execution plan generated by the parsed SQL is given, and the INSERT ESTABLE execution process is entered. Specifically, the object builder json builder is used to convert each row of tuples to be inserted into the corresponding string object json object, which requires the conversion of each column data (analytic database type to ESfield type of the full-text search engine). The specific type mapping relationship can be seen in Table 1. In addition, in this embodiment, the string object jsonobject can be recognized by the full-text search engine ES.

[0076] S21: Determine whether there are still tuples to be inserted in the analytical database.

[0077] S22: If yes, construct a batch insert request body using the object builder.

[0078] Specifically, if there are still tuples to be inserted in the analytical database, the object builder jsonbuilder is used to build a bulk insert request body (bulkinsert request body).

[0079] S23: Otherwise, determine whether the object builder is in a non-empty state.

[0080] Specifically, if the tuple to be inserted does not exist in the analytical database, it is determined whether the object builder jsonbuilder is in a non-empty state.

[0081] S24: If yes, proceed to step S27.

[0082] Specifically, if the object builder json builder is not in a non-empty state, the process directly proceeds to a step of connecting to a master node obtained from the full-text search engine and writing the tuples to be inserted into a batch into a corresponding index in the master node.

[0083] S25: Otherwise, end.

[0084] Specifically, if the object builder json builder is in a non-empty state, the entire insert operation is terminated.

[0085] S26: Determine whether the number of tuples to be inserted contained in the batch insert request body reaches a first threshold.

[0086] Specifically, it is determined whether the number of tuples to be inserted contained in the bulk insert request body reaches a first threshold, where the first threshold is the bulk insert parameter bulk_insert_num.

[0087] S27: If yes, connect to a master node obtained from the full-text search engine, and write the tuples to be inserted into the corresponding index in the master node in batches.

[0088] Specifically, if the number of tuples to be inserted contained in the bulk insert request body reaches the batch insert parameter bulk_insert_num, a master node obtained from the full-text search engine is connected, and the tuples to be inserted are written in batches to the corresponding index in the master node.

[0089] S28: Otherwise, return to the step of reading the next tuple to be inserted from the analytical database.

[0090] Specifically, if the number of tuples to be inserted contained in the bulk insert request body does not reach the batch insert parameter bulk_insert_num, the process returns to the step S20 in which the next tuple to be inserted is read from the analytical database.

[0091] S29: Determine whether the tuples to be inserted are successfully written into the corresponding indexes in the master control node in batches.

[0092] S30: If yes, obtain the returned first parsed http feedback information.

[0093] Specifically, in this embodiment, _bulk insert will contain multiple ESdocuments at a time, but even if there is an ESdocument insertion failure, the status returned by the overall _bulk http request is still 200, so we still need to detect the first parsed http response message it returns. Therefore, if the tuples to be inserted are successfully written to the corresponding index in the master node, the first parsed http feedback message returned is obtained.

[0094] S31: Otherwise, throw an exception.

[0095] Specifically, if the tuples to be inserted are not successfully written in batches to the corresponding indexes in the master node, indicating that the creation has failed, an exception is thrown and the process ends.

[0096] S32: Determine whether there is no error in the first parsed http feedback information.

[0097] Specifically, the first parsed http feedback information is also a message in json format. It is necessary to determine whether there is an error in the first parsed http feedback information. If there is an error, its format is as shown in the following table;

[0098]

[0099] In this embodiment, it is possible to detect whether the "errors" field in the first parsed http feedback information is true, so as to confirm whether there is an insertion failure.

[0100] S33: If yes, return to step S20.

[0101] Specifically, if there is no error in the first parsed http feedback information, the process returns to the step of resetting the object builder and reading the next tuple to be inserted from the analytical database, and continues the insertion operation.

[0102] S34: Otherwise, throw an exception.

[0103] Specifically, if there is an error in the first parsing http feedback information, indicating that the entire insert operation will be terminated with an error, an exception is thrown and the operation ends. In addition, in the present embodiment, if the error is terminated, the data previously inserted into the full-text search engine will still exist.

[0104] With this design approach, the entire system supports insert operations, making it easier to synchronize data between the analytical database and the full-text search engine.

[0105] For a distributed analytical database, there are multiple execution instances. The ES external table insertion of all execution instances follows the above process description, except that the data to be imported is different.

[0106] The following is a detailed introduction to the read operation. Similar to the insert operation mentioned above, the read operation also converts the structured query language SQL into the corresponding REST http query request of the full-text search engine. An example is shown in the following table.

[0107]

[0108] Specifically, in this embodiment, the application can use ES external tables like ordinary tables. ES external tables themselves do not store data in the analytical database. The database executor actually pulls relevant data from the full-text search engine. The filter conditions in the SELECT SQL will be pushed down to the full-text search engine as needed, which involves the automatic conversion between SQL filter conditions and ES's domain-specific language DSL. Please refer to Figure 8 , Figure 8 It is an execution diagram of the analytical database and the full-text search engine. At the same time, because the data fragments (shards) in the full-text search engine are distributed in multiple data nodes DataNode, and the distributed database also has multiple execution instances, it can be read in parallel in a many-to-many manner, and the full-text search engine supports direct access to the data node DataNode. Thereby achieving the purpose of many-to-many concurrent access between the analytical database and the full-text search engine. The fragment distribution information of the association relationship in the above text has been periodically synchronized to the ES external table. For details, please refer to steps S1-S12, which will not be repeated here.

[0109] After step S11, the method further includes: when the analytical database receives the structured query language sent by the client, parsing the structured query language, and sending the parsed structured query language to the index fragment corresponding to the structured query language in the full-text search engine.

[0110] Specifically, in this embodiment, the full-text search engine includes at least one index fragment, at least one index fragment corresponds to an execution node in the analytical database, and one index fragment includes at least one file. Fig. 9 , Fig. 9 yes Figure 6 Schematic diagram of another implementation process after step S11. After the above step S11, the steps of parsing the structured query language and sending the parsed structured query language to the index fragments corresponding to the structured query language in the full-text search engine include:

[0111] S40: For each execution node of the analytical database, all filter conditions in the structured query language are traversed, and a domain-specific language that can be pushed down to the full-text search engine is constructed based on the filter conditions.

[0112] In this embodiment, a SELECT execution plan generated by parsing SQL is given, and the SELECT ESTABLE execution process is entered. Then, for each execution node of the analytical database, all filter conditions in the structured query language are traversed, and a domain-specific language that can be pushed down to the full-text search engine is constructed based on the filter conditions.

[0113] S41: Read the index fragments corresponding to the execution node, and reconstruct the cursor query request in combination with the domain specific language or cursor parameters.

[0114] Specifically, to support large data volume reading, the full-text search engine ES cursor Scroll query interface API is adopted, which is similar to the database cursor query and can pull data in batches. In this embodiment, the cursor query request is rebuilt in combination with the domain-specific language DSL or cursor parameters, and the SELECT execution plan has allocated corresponding fragmentation information to each database execution node. The fragmentation information is used in the http request URL to directly access the index fragment (ES indexshard) of the full-text search engine.

[0115] S42: Determine whether the index fragment is connected and whether the domain specific language or cursor parameter is successfully associated with the index fragment.

[0116] S43: If yes, obtain the returned second parsing http feedback information.

[0117] Specifically, if the index fragment is connected and the domain specific language or cursor parameter is successfully associated with the index fragment, the returned second parsing http feedback information is obtained.

[0118] S44: Otherwise, throw an exception.

[0119] Specifically, if the index fragment is not successfully connected and the domain-specific language or cursor parameter is not successfully associated with the index fragment, indicating that the creation fails, an exception is thrown and the process ends.

[0120] S45: Determine whether there is a file returned from the index fragment in the second parsed http feedback information.

[0121] Specifically, after obtaining the second parsing http feedback information in the above step S43, it is determined whether there is a file document returned from the index fragment in the second parsing http feedback information.

[0122] S46: If yes, determine whether the file is parsed successfully.

[0123] Specifically, if there is a file returned from the index fragment in the second parsing http feedback information, the file is parsed, and it is determined whether the file is parsed successfully.

[0124] S47: Otherwise, end.

[0125] Specifically, if there is no file returned from the index fragment in the second parsing http feedback information, the entire reading operation is terminated.

[0126] S48: If yes, convert the parsed file into corresponding tuples in the analytical database.

[0127] Specifically, if the above file is parsed successfully, the parsed file is converted into the corresponding tuple truple in the analytical database.

[0128] S49: Otherwise, throw an exception.

[0129] Specifically, if the above file is not parsed successfully, it means that the creation fails, then an exception is thrown and the process ends.

[0130] Table 2 Conversion relationship between SQL and ES DSL

[0131] SQL ES DSL ＝ term IN terms LIKE wildcard ><>＝<＝ range AND / OR / NOT bool / must / should / must_not ... ...

[0132] Specifically, in this embodiment, the key point of converting the structured query language SQL in the database into ES query is that the executed filtering conditions need to remain equivalent, otherwise the results may be inconsistent. Most commonly used filtering operations can be converted into ESDSL, as shown in Table 2.

[0133] However, some conditions in the database cannot be pushed down to the full-text search engine ES, such as special functions:

[0134] SELECT*FROM es_test_abc WHERE c1<100AND c3 IN('aaa','bbb')AND foo(c2)! =1;

[0135] As shown above, the query condition "foo(c2)!=1" mentioned above cannot be pushed down to the full-text search engine ES, and a secondary filtering is required on the data result set returned by the full-text search engine ES. Therefore, after step S48, it also includes: filtering out the first tuple that meets the remaining filtering conditions from the tuple and returning to the step S41 to reconstruct the cursor query request in combination with the domain-specific language or cursor parameters. Specifically, the remaining filtering conditions are filtering conditions that cannot be pushed down to the full-text search engine ES. Specifically, in this embodiment, the remaining filtering conditions are query conditions that cannot be pushed down to the full-text search engine ES. For this type of situation, assuming that the foo() function does not exist in the full-text search engine ES, we can only push down the c1<100AND c3 IN('aaa','bbb') condition part to the full-text search engine ES. Thereafter, the tuple tuple obtained from the full-text search engine ES needs to be filtered twice (foo(c2)!=1). With this design, users do not need to be aware of the conversion shown in Table 2, nor do they need to understand the DSL syntax. They can use ES external tables just like ordinary database tables. In SQL usage, this type of ES external table can be used in conjunction with ordinary tables in Analytic Database, such as JOIN queries.

[0136] See also Fig.10 , Fig.10 This is a schematic diagram of the structure of an implementation of a fusion system of an analytical database and a full-text search engine of the present application. The fusion system specifically includes:

[0137] The establishment module 10 is used to establish an ES external table in the analytical database. The ES external table includes at least one index specified by the client, which is used to store relevant configuration information of the full-text search engine, and the relevant configuration information includes relevant attributes of at least one index.

[0138] The processing module 12 is coupled to the establishment module 10, and is used to create an index in the full-text search engine for each index in the ES external table in response to verifying that there is no problem with the relevant attributes of the index and in response to the automatic creation function of the index being turned on, and to use the index to establish an association relationship between the analytical database and the full-text search engine in the ES external table. In addition, the processing module 12 is also used to connect to the full-text search engine and obtain the association information of the index from the full-text search engine in response to the successful establishment of the association relationship. In addition, the processing module 12 is also used to save the association information to the ES external table in response to the association information being normal.

[0139] See also Fig.11 , Fig.111 is a schematic diagram of a framework of an embodiment of an electronic device of the present application. The electronic device includes a memory 20 and a processor 22 coupled to each other. Specifically, in this embodiment, the memory 20 stores program instructions, and the processor 22 is used to execute the program instructions to implement the fusion method of the analytical database and the full-text retrieval engine mentioned in any of the above embodiments.

[0140] Specifically, the processor 22 may also be referred to as a CPU (Central Processing Unit). The processor 22 may be an integrated circuit chip having the ability to process signals. The processor 22 may also be a general-purpose processor, a digital signal processor (DSP), an application-specific integrated circuit (ASIC), a field-programmable gate array (FPGA) or other programmable logic devices, discrete gates or transistor logic devices, discrete hardware components. A general-purpose processor may be a microprocessor or the processor may also be any conventional processor, etc. In addition, the processor 22 may be implemented by a plurality of integrated circuit chips.

[0141] See also Fig.12 , Fig.12 It is a schematic diagram of a framework of an embodiment of a computer-readable storage medium of the present application. The computer-readable storage medium 30 stores a computer program 300, which can be read by a computer, and the computer program 300 can be executed by a processor to implement the fusion method of the analytical database and the full-text search engine mentioned in any of the above embodiments. Among them, the computer program 300 can be stored in the above-mentioned computer-readable storage medium 30 in the form of a software product, including a number of instructions to enable a computer device (which can be a personal computer, a server, or a network device, etc.) or a processor (processor) to perform all or part of the steps of the method described in each embodiment of the present application. The computer-readable storage medium 30 with a storage function can be a U disk, a mobile hard disk, a read-only memory (ROM, Read-Only Memory), a random access memory (RAM, Random Access Memory), a disk or an optical disk, etc., which can store program codes, or a terminal device such as a computer, a server, a mobile phone, and a tablet.

[0142] In summary, different from the prior art, the fusion method of the analytical database and the full-text search engine provided by the present application includes: establishing an ES external table in the analytical database; wherein the ES external table includes at least one index specified by the client, which is used to save the relevant configuration information of the full-text search engine, and the relevant configuration information includes the relevant attributes of at least one index; for each index in the ES external table, when it is verified that there is no problem with the relevant attributes of the index, and when the automatic creation function of the index is turned on, the index is created in the full-text search engine, and the index is used to establish the association relationship between the analytical database and the full-text search engine in the ES external table; in response to the successful establishment of the association relationship, the full-text search engine is connected and the associated information of the index is obtained from the full-text search engine; in response to the association information being normal, the associated information is saved in the ES external table. Through this design method, the user only needs to connect to the analytical database, and only needs to use JDBC (using standard SQL) to access the database itself or the full-text search engine, and the interaction between the analytical database and the full-text search engine does not need to be transferred by the user, and the distributed analytical database can automatically and concurrently access the data in the full-text search engine, thereby improving query efficiency.

[0143] The above description is only an implementation method of the present application, and does not limit the patent scope of the present application. Any equivalent structure or equivalent process transformation made using the contents of the present application specification and drawings, or directly or indirectly used in other related technical fields, are also included in the patent protection scope of the present application.

Claims

1. A method for integrating an analytical database and a full-text search engine, characterized in that: include: An ES external table is established in the analytical database; wherein the ES external table includes at least one index specified by the client, and is used to store relevant configuration information of the full-text search engine, and the relevant configuration information includes relevant attributes of the at least one index; For each index in the ES external table, in response to verifying that there is no problem with the relevant attributes of the index and in response to the automatic creation function of the index being turned on, create the index in the full-text search engine, and use the index to establish an association relationship between the analytical database and the full-text search engine in the ES external table; In response to the association relationship being successfully established, connecting to the full-text search engine and acquiring association information of the index from the full-text search engine; In response to there being no abnormality in the associated information, the associated information is saved in the ES external table.

2. The fusion method according to claim 1, characterized in that: After the step of saving the association information to the ES external table in response to the association information being normal, the method further includes: The ES external table is awakened at preset time intervals, and the ES external table is connected to the full-text search engine to periodically synchronize the associated information associated in the ES external table.

3. The fusion method according to claim 1, characterized in that: After the step of saving the association information to the ES external table in response to the association information being normal, the method further includes: Resetting the object builder and reading the next tuple to be inserted from the analytical database; wherein the object builder is used to convert the tuple to be inserted into a corresponding string object, and the string object can be recognized by the full-text search engine; In response to the fact that there are still tuples to be inserted in the analytical database, constructing a batch insert request body using the object builder; In response to the number of tuples to be inserted contained in the batch insert request body reaching a first threshold, connecting to a master control node obtained from the full-text search engine, and writing the tuples to be inserted in batches into a corresponding index in the master control node; In response to successfully writing the tuples to be inserted into the corresponding indexes in the master control node in batches, obtaining the returned first parsing http feedback information; In response to there being no error in the first parsed http feedback information, the process returns to the reset object builder and reads the next tuple to be inserted from the analytical database.

4. The fusion method according to claim 3, characterized in that: After the step of resetting the object builder and reading the next tuple to be inserted from the analytical database, the method further includes: In response to the absence of the tuple to be inserted in the analytical database and in response to the non-empty state in the object builder, enter the step of connecting to a master node obtained from the full-text retrieval engine and batch write the tuple to be inserted into the corresponding index in the master node.

5. The fusion method according to claim 1, characterized in that: After the step of saving the association information to the ES external table in response to the association information being normal, the method further includes: In response to the analytical database receiving the structured query language sent by the client, the structured query language is parsed, and the parsed structured query language is sent to the index fragment corresponding to the structured query language in the full-text search engine.

6. The fusion method according to claim 5, characterized in that: The full-text search engine includes at least one index fragment, the at least one index fragment corresponds to an execution node in the analytical database, and one index fragment includes at least one file; the step of parsing the structured query language and sending the parsed structured query language to the index fragment corresponding to the structured query language in the full-text search engine includes: For each execution node of the analytical database, all filter conditions in the structured query language are traversed, and a domain specific language that can be pushed down to the full-text search engine is constructed according to the filter conditions; Read the index fragment corresponding to the execution node, and reconstruct the cursor query request in combination with the domain specific language or cursor parameters; In response to connecting the index fragment and successfully associating the domain specific language or the cursor parameter with the index fragment, obtaining returned second parsing http feedback information; In response to the second parsing http feedback information containing a file returned from the index fragment, and in response to the file being parsed successfully, the parsed file is converted into a corresponding tuple in the analytical database.

7. The fusion method according to claim 6, characterized in that: After the step of converting the parsed file into a corresponding tuple in the analytical database in response to the second parsing http feedback information having a file returned from the index fragment and in response to the file being parsed successfully, the method includes: The first tuple satisfying the remaining filter conditions is selected from the tuples and returned to the step of reconstructing the cursor query request in combination with the domain specific language or cursor parameters; wherein the remaining filter conditions are filter conditions that cannot be pushed down to the full-text search engine.

8. The fusion method according to claim 1, characterized in that: After the step of creating an ES external table in the analytical database, the following steps are also included: For each index in the ES external table, in response to verifying that there is no problem with the relevant attributes of the index and in response to the automatic creation function of the index being turned off, an association relationship between the analytical database and the full-text search engine is established in the ES external table using the index, and the process proceeds to the step of connecting to the full-text search engine and obtaining the association information of the index from the full-text search engine in response to the successful establishment of the association relationship.

9. An electronic device, characterized in that: It comprises a memory and a processor coupled to each other, wherein the memory stores program instructions, and the processor is used to execute the program instructions to implement the fusion method of the analytical database and the full-text retrieval engine as described in any one of claims 1 to 8.

10. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores a computer program, and the computer program is used to implement the method for integrating the analytical database and the full-text search engine as described in any one of claims 1 to 8.

Images