Integrated data analysis and full-text retrieval method and system

Abstract

The invention discloses an integrated data analysis and full-text retrieval method and system.The method comprises the steps that a materialized view is created based on a data main table, and the materialized view specifies fields needing full-text retrieval and main key fields of the data main table; the materialized view uses a table of a URL (Uniform Resource Locator) engine as a base table of the materialized view, converts a received data insertion statement into an HTTP (Hyper Text Transport Protocol) request of the URL, automatically constructs an inverted index, and finally writes the index into a storage library of a search engine; customizing a UDF function, calling a search engine library by the UDF function, and retrieving data by utilizing the full-text retrieval capability of a search engine; the parameters of the UDF function comprise a retrieval field name and a retrieval keyword, and the return value of the UDF function is an array composed of hit record primary keys, and judging whether the primary key field value of the data primary table is in the returned UDF array or not. According to the method and the system, the data consistency is ensured, the query process is simplified, the query is accelerated through the primary key index, and the retrieval performance is remarkably improved.

Description

Technical Field

[0001] This invention relates to the field of data analysis and full-text retrieval, and in particular to an integrated data analysis and full-text retrieval method and system. Background Technology

[0002] ClickHouse excels as an analytical database for large-scale data analysis, but it has limitations in full-text search scenarios. Because it requires full table scans, its performance is insufficient for practical needs when handling large amounts of text data for full-text search.

[0003] The current common solution in the industry is to use a dual-storage architecture of ClickHouse and Elasticsearch:

[0004] (1) Data storage: ClickHouse stores complete data, while Elasticsearch stores the fields to be retrieved and the primary key ID.

[0005] (2) Challenges exist:

[0006] Data synchronization: It requires writing to two systems simultaneously, which increases the difficulty of maintaining data consistency.

[0007] The query process is complex: first, the ID is retrieved through Elasticsearch, and then detailed data is retrieved through ClickHouse, resulting in a lengthy query chain.

[0008] Limited scalability: The system is highly coupled and cannot flexibly respond to changes in business requirements. Summary of the Invention

[0009] To address the aforementioned problems in existing technologies, this invention provides an integrated method and system for data analysis and full-text retrieval. It combines materialized views with User-Defined Functions (UDFs), automatically synchronizing data retrieval through materialized views and integrating with search engines using URL engines. Simultaneously, it encapsulates full-text retrieval functionality using UDFs, achieving seamless integration of data analysis and full-text retrieval capabilities within analytical databases. This method and system not only ensure data consistency and simplify the query process but also accelerate queries through primary key indexes, significantly improving retrieval performance and providing an efficient and reliable solution for full-text retrieval needs in large-scale data analysis scenarios.

[0010] To achieve the above objectives, the present invention adopts the following technical solution:

[0011] In one embodiment of the present invention, a method for integrated data analysis and full-text retrieval is proposed, the method comprising:

[0012] A materialized view is created based on the master data table. This materialized view specifies the fields that need to be searched for full text and the primary key fields of the master data table. The materialized view uses the table of the URL engine as its base table, converts the received data insertion statements into HTTP requests for URLs, automatically builds inverted indexes, and finally writes these indexes into the search engine's repository.

[0013] A custom UDF function is defined that calls the search engine's repository and uses the search engine's full-text search capabilities to retrieve data. The parameters of the UDF function include the search field name and search keywords. The return value of the UDF function is an array consisting of the primary keys of the hit records, and it is determined whether the value of the primary key field of the main table is in the returned UDF array.

[0014] Furthermore, the search engine data import service is constructed through the following steps:

[0015] Design an HTTP / HTTPS service interface that will be exposed to receive external data;

[0016] Determine which search engine's repository to use, based on its characteristics and project requirements;

[0017] Develop the service using any backend technology stack, ensuring that the service can handle HTTP POST requests;

[0018] Parse data from HTTP POST requests and convert the received data into the format required by the search engine's repository;

[0019] The parsed and transformed data is written to the search engine via the search engine's repository API.

[0020] Further, create a materialized view by following these steps:

[0021] Create a master data table, which serves as the sole entry point for data import, querying, and analysis.

[0022] Create a materialized view by using a SELECT statement to select the fields used for full-text search and the primary key fields of the main data table, and specify the base table of the materialized view;

[0023] Materialized views use the URL engine's table as their base table. When data is being stored in the database, the HTTP / HTTPS service of the search engine is invoked to write the data into the search engine's repository, which can then be used for subsequent full-text search.

[0024] Furthermore, the fields in the base table of the materialized view are consistent with the fields selected in the SELECT statement when the materialized view is created, including field names, field order, and field data types;

[0025] The materialized view base table specifies two parameters: the HTTP / HTTPS service URL for the search engine data ingestion and the data format.

[0026] Furthermore, when full-text search filtering is required, a custom UDF function is called through the function has(arr,elem);

[0027] The function has(arr,elem) checks whether the array arr contains the element elem; if it does, it returns 1; otherwise, it returns 0.

[0028] The first parameter of the function has(arr,elem) is an array, which takes the return value of the custom UDF function as its first parameter. The second parameter of the function has(arr,elem) is the primary key field of the main table. That is, the function has(arr,elem) checks whether the value of the primary key field of the main table exists in the array returned by the custom UDF function. If it exists, it returns 1, indicating that a matching record was found; if it does not exist, it returns 0, indicating that no matching record was found.

[0029] In one embodiment of the present invention, an integrated data analysis and full-text retrieval system is also proposed, the system comprising:

[0030] The data ingestion module is used to create materialized views based on the main data table. The materialized view specifies the fields that need to be searched for full text and the primary key fields of the main data table. The materialized view uses the table of the URL engine as its base table, converts the received data insertion statement into an HTTP request of the URL, automatically builds an inverted index, and finally writes these indexes into the search engine's repository.

[0031] The data analysis query module is used to define custom UDF functions. These UDF functions call the search engine's repository and utilize the search engine's full-text search capabilities to retrieve data. The parameters of the UDF function include the search field name and search keywords. The return value of the UDF function is an array composed of the primary keys of the hit records, and it is determined whether the value of the primary key field of the main data table is in the returned UDF array.

[0032] Furthermore, the search engine data import service is constructed through the following steps:

[0033] Design an HTTP / HTTPS service interface that will be exposed to receive external data;

[0034] Determine which search engine's repository to use, based on its characteristics and project requirements;

[0035] Develop the service using any backend technology stack, ensuring that the service can handle HTTP POST requests;

[0036] Parse data from HTTP POST requests and convert the received data into the format required by the search engine's repository;

[0037] The parsed and transformed data is written to the search engine via the search engine's repository API.

[0038] Further, create a materialized view by following these steps:

[0039] Create a master data table, which serves as the sole entry point for data import, querying, and analysis.

[0040] Create a materialized view by using a SELECT statement to select the fields used for full-text search and the primary key fields of the main data table, and specify the base table of the materialized view;

[0041] Materialized views use the URL engine's table as their base table. When data is being stored in the database, the HTTP / HTTPS service of the search engine is invoked to write the data into the search engine's repository, which can then be used for subsequent full-text search.

[0042] Furthermore, the fields in the base table of the materialized view are consistent with the fields selected in the SELECT statement when the materialized view is created, including field names, field order, and field data types;

[0043] The materialized view base table specifies two parameters: the HTTP / HTTPS service URL for the search engine data ingestion and the data format.

[0044] Furthermore, when full-text search filtering is required, a custom UDF function is called through the function has(arr,elem);

[0045] The function has(arr,elem) checks whether the array arr contains the element elem; if it does, it returns 1; otherwise, it returns 0.

[0046] The first parameter of the function has(arr,elem) is an array, which takes the return value of the custom UDF function as its first parameter. The second parameter of the function has(arr,elem) is the primary key field of the main table. That is, the function has(arr,elem) checks whether the value of the primary key field of the main table exists in the array returned by the custom UDF function. If it exists, it returns 1, indicating that a matching record was found; if it does not exist, it returns 0, indicating that no matching record was found.

[0047] In one embodiment of the present invention, a computer device is also proposed, including a memory, a processor, and a computer program stored in the memory and executable on the processor. When the processor executes the computer program, the aforementioned integrated data analysis and full-text retrieval method is designed.

[0048] In one embodiment of the present invention, a computer-readable storage medium is also provided, which stores a computer program for performing an integrated data analysis and full-text retrieval method.

[0049] Beneficial effects:

[0050] 1. Automated data synchronization

[0051] This invention uses a materialized view mechanism to automatically synchronize data to a search engine without requiring additional synchronization programs, thus ensuring data consistency from the source.

[0052] 2. Simplify the query process

[0053] This invention directly returns the primary key array through a UDF function and uses the primary key index to locate the data, simplifying the traditional cross-system query into a single SQL call and improving query efficiency.

[0054] 3. Decoupled Architecture Design

[0055] This invention uses a URL engine as an intermediate layer, which decouples the analytical database from the search engine, improving the maintainability and scalability of the system.

[0056] 4. Unified query interface

[0057] This invention encapsulates full-text search capabilities into UDF functions that can be directly called from SQL, lowering the barrier to entry and improving development efficiency.

[0058] 5. Performance optimization and innovation

[0059] This invention combines the high-performance computing of analytical databases with the full-text retrieval advantages of search engines, and accelerates queries through primary key indexes, achieving efficient retrieval in large-scale data analysis scenarios. Attached Figure Description

[0060] Figure 1 This is an overall flowchart of the integrated data analysis and full-text retrieval method of the present invention;

[0061] Figure 2 This is a flowchart of the data entry process of this invention;

[0062] Figure 3 This is a logical diagram of the data analysis and query of this invention;

[0063] Figure 4This is a schematic diagram of the integrated data analysis and full-text retrieval system structure of the present invention;

[0064] Figure 5 This is a schematic diagram of the computer device structure of the present invention. Detailed Implementation

[0065] The principles and spirit of the present invention will now be described with reference to several exemplary embodiments. It should be understood that these embodiments are provided merely to enable those skilled in the art to better understand and design the present invention, and are not intended to limit the scope of the invention in any way. Rather, these embodiments are provided to make this disclosure more thorough and complete, and to fully convey the scope of the disclosure to those skilled in the art.

[0066] Those skilled in the art will recognize that embodiments of the present invention can be designed as a system, apparatus, method, or computer program product. Therefore, this disclosure can be specifically designed as entirely hardware, entirely software (including firmware, resident software, microcode, etc.), or a combination of hardware and software.

[0067] According to an embodiment of the present invention, an integrated data analysis and full-text search method is proposed. This method combines materialized views with UDF functions, automatically synchronizing data retrieval through materialized views and integrating with search engines using URL engines. Simultaneously, UDF functions encapsulate full-text search functionality, achieving seamless integration of data analysis and full-text search capabilities in analytical databases. This method not only ensures data consistency and simplifies the query process but also accelerates queries through primary key indexes, significantly improving retrieval performance. It provides an efficient and reliable solution for full-text search needs in large-scale data analysis scenarios.

[0068] The principles and spirit of the present invention will be explained in detail below with reference to several representative embodiments.

[0069] Figure 1 This is an overall flowchart of the integrated data analysis and full-text retrieval method of the present invention.

[0070] like Figure 1 As shown, the method includes:

[0071] 1. Data entry process:

[0072] like Figure 2As shown, a materialized view is created based on the master data table. This materialized view specifies the fields to be used for full-text search and the primary key field of the master data table. To achieve this functionality, the materialized view uses a table from the URL engine as its base table. The URL engine's corresponding URL implements the function of writing data to the search engine's repository. During the data writing process, the system will automatically write data to the URL engine's materialized view base table according to the fields defined in the materialized view. The materialized view base table converts the received insert data statements into HTTP requests for URLs, automatically builds inverted indexes, and finally writes these indexes to the search engine's repository.

[0073] 2. Data analysis query logic:

[0074] like Figure 3 As shown, a custom UDF (User-Defined Function) is defined. The function's parameters include the field name to be searched and the search keywords. The function returns an array consisting of the primary keys of the matched records. This UDF function will be defined using Python. It will call the search engine's repository and utilize the search engine's full-text search capabilities to retrieve data. Specifically, the UDF function will return the IDs of the matched data.

[0075] If the SQL statement for data analysis queries implements full-text search functionality, it calls a custom full-text search UDF function, passing in the search field name and search keywords. The custom UDF function returns an array composed of the primary keys of the matched records. Then, it checks whether the value of the primary key field in the main data table is in the returned array of the custom UDF function. Since the data in the primary key field is organized according to the index structure, this structure can quickly locate the physical location of the data storage, reducing the number of I / O operations during the query process. By checking whether the value of the primary key field is in the returned array of the custom UDF function, the efficiency of data query and retrieval can be greatly improved.

[0076] It should be noted that although the operation of the method of the present invention has been described in a specific order in the above embodiments and figures, this does not require or imply that the operations must be performed in that specific order, or that all the operations shown must be performed to achieve the desired result. Additionally or alternatively, certain steps may be omitted, multiple steps may be combined into one step, and / or one step may be broken down into multiple steps.

[0077] To provide a clearer explanation of the above-mentioned integrated data analysis and full-text retrieval method, a specific embodiment will be used for illustration below. However, it is worth noting that this embodiment is only for better illustrating the present invention and does not constitute an improper limitation of the present invention.

[0078] Detailed implementation

[0079] I. Data Import

[0080] 1. Build a search engine data import service

[0081] Build a stable, efficient, and secure search engine data ingestion service that exposes HTTP / HTTPS service interfaces to receive external data and write it into search engine libraries such as Elasticsearch or SonIC.

[0082] 1) Define the service interface: Design an HTTP / HTTPS service interface that will be exposed to the outside world to receive external data.

[0083] 2) Choose a search engine: Determine which search engine repository to use, such as Elasticsearch or SonIC, based on its features and project requirements.

[0084] 3) Develop HTTP / HTTPS services: Develop services using any backend technology stack (such as Python Flask, Java Spring Boot, etc.) and ensure that the services can handle HTTP POST requests.

[0085] 4) Parse request data: Parse data from HTTP POST requests and convert the received data into the format required by the search engine's repository.

[0086] 5) Perform write operation: Write the parsed and transformed data into the search engine through the search engine's repository API.

[0087] 2. Materialized View Definition

[0088] Based on materialized views, SELECT statements are used to select and transform fields in the main data table, automatically building search engine data while data is written to the main data table.

[0089] The specific steps are as follows:

[0090] (1) Create the master data table. The master data table is the sole entry point for data entry, querying, and analysis.

[0091] The following example creates a main data table t_main_table and defines 5 fields, with id as the primary key.

[0092]

[0093] (2) Create a materialized view

[0094] To create a materialized view, use a SELECT statement to select the fields used for full-text search and the primary key fields of the main data table. You also need to specify the base table for the materialized view.

[0095] The SELECT statement for a materialized view can select fields from the main table, or it can transform the data in the main table fields before using it as data for the search engine. For example, if you only want to perform a full-text search on the first 100 characters of the message field, you can use a string truncation function to extract the first 100 characters of the message and store them in the database.

[0096] Example of a table creation statement for a materialized view:

[0097] Example:

[0098] --Specify the name of the materialized view: t_materialized_view

[0099] CREATE MATERIALIZED VIEW t_materialized_view

[0100] --Specify the base table of the materialized view as t_materialized_base_table

[0101] TO t_materialized_base_table

[0102] AS

[0103] --Use a select statement to specify the fields for full-text search in the main data table t_main_table as address and message, with id as the primary key.

[0104] SELECT id, address, message

[0105] FROMt_main_table;

[0106] In the materialized view example above, a materialized view was created, named t_materialized_view, and the base table for the materialized view was specified as t_materialized_base_table using the TO statement. A SELECT statement was used to specify the fields address and message for full-text search from the main table t_main_table, with id as the primary key.

[0107] If you only need to perform a full-text search on the first 100 characters of the `message` field, you can use a string truncation function to extract the first 100 characters of the `message` field and store them in the database. The SQL would then be:

[0108] CREATE MATERIALIZEDVIEW t_materialized_view

[0109] TO t_materialized_base_table

[0110] AS

[0111] --Use the substring function to extract the first 100 characters of the message.

[0112] SELECT id,address,substring(message,0,100)as message

[0113] FROMt_main_table;

[0114] (3) Create a materialized view base table

[0115] The base table of a materialized view uses a URL table engine, which is used to call the search engine's HTTP / HTTPS service for data entry when data is being entered into the database, and write the data into the search engine's repository so that it can be used for subsequent full-text search.

[0116] The following constraints apply:

[0117] 1) The fields in the base table of the materialized view are consistent with the fields selected in the SELECT statement when creating the materialized view, including the field names, field order, and field data types.

[0118] 2) The engine of the materialized view's base table is represented by a URL, specifying two parameters: the HTTP / HTTPS service URL for the search engine data entry and the data format. The HTTP / HTTPS service URL for the search engine data entry is used to receive data, and the data format is specified by the second parameter. For example, in the example below, specifying the data format as JSONEachRow means that the data will be sent to the HTTP / HTTPS service for search engine data entry in JSON format for parsing and writing to the search engine database.

[0119] The statement used to create the base table for a materialized view:

[0120]

[0121] The following is an example of an HTTP service implementation:

[0122]

[0123]

[0124]

[0125]

[0126]

[0127] 3. Data writing

[0128] The data is first entered into the main data table, and then the data writing process to the search engine's repository is automatically triggered. The specific operation logic is as follows: the newly entered data in the main data table is processed and transformed according to the SELECT statement defined in the materialized view. Next, the system calls the HTTP service interface of the materialized view's base table to write the transformed data into the search engine's repository. This process ensures efficient data flow and accuracy.

[0129] II. Data Analysis and Query

[0130] 1. Custom UDF function

[0131] A custom UDF function (e.g., named `index_search`) performs a full-text search and returns an array containing the primary key IDs of records that match the search criteria. These IDs are used as part of the result set for subsequent data processing or filtering operations.

[0132] The specific implementation logic of the custom UDF function is as follows:

[0133] (1) Function parameters: the field name to be searched and the search keywords.

[0134] (2) Return value of the function: Array type. When the full-text search finds a match, it returns an array consisting of the primary keys of the matched records. Otherwise, it returns an empty array.

[0135] (3) Functionality: Based on the input search keywords, the function performs a full-text search of the data corresponding to the field name to be searched from the search engine's repository. Since the search engine's repository is built based on an inverted index, the full-text search is very efficient. If the full-text search finds a match, it returns an array consisting of the primary keys of the matched records. Otherwise, it returns an empty array.

[0136] Steps to implement a custom UDF function:

[0137] (1) In ClickHouse's configuration file, define the function name, function parameters, and the data type of the parameters.

[0138]

[0139]

[0140] (2) Implement custom functions

[0141] Create a new Python script file named index_search.py ​​in the path / var / lib / clickhouse / user_scripts / . Implement the full-text search logic using a custom function.

[0142] The Python script is the execution body of the custom UDF, and its parameters are identical to those of the custom UDF. There are two parameters: the field name to be searched and the search keywords. The specific steps include:

[0143] 1) Receive parameters. The parameters are in JSON format and are passed to the Python script via standard input stream.

[0144] 2) Parse the parameters: Parse the JSON format input parameters and obtain the two parameter values ​​of the custom UDF: the field name to be searched and the search keyword.

[0145] 3) Call the search engine's API, such as Elasticsearch's search API or SonIC's retrieval API, to obtain an array of primary key IDs of the hit records.

[0146] 4) Serialize the returned ID array, refresh the standard output stream, and use it as the return result of the custom UDF.

[0147]

[0148] 2. Calling a custom UDF

[0149] Custom UDF functions are called by the name of the custom UDF, passing in two parameters: the field name to be searched and the search keyword.

[0150] For example, the custom UDF name is `index_search`, the field to be searched is `message`, the search keyword is "network management", and the custom function call is: `index_search('message', 'network management')`. Here, `index_search` is the UDF name, `message` is the field name, and `'network management'` is the keyword to search for. This means the function performs a full-text search for the keyword "network management" in the `message` field, and returns the primary key ID if a match is found.

[0151] A UDF function returns a result after execution. The return result is an array, and the elements of the array can be strings, numbers, or other types. In this example, assuming the return type is numbers and the records with primary keys 1, 5, and 7 are matched, the returned array is array(1,5,7).

[0152] 3. Integration of data analysis queries and custom UDFs

[0153] When full-text search filtering is required, a custom UDF function is called using the function has(arr,elem).

[0154] The function has(arr, elem) checks whether the array arr contains the element elem. If it does, the function returns 1; otherwise, it returns 0.

[0155] The first parameter of the function `has(arr,elem)` is an array. Since the return value of a custom UDF call is also an array, the return result of the custom UDF function can be used as the first parameter of `has(arr,elem)`. The second parameter of `has(arr,elem)` is the primary key field of the main data table. That is, `has(arr,elem)` checks whether the value of the primary key field of the main data table exists in the array returned by the custom UDF function. If it exists, `has(arr,elem)` returns 1, indicating that a matching record was found; if it does not exist, it returns 0, indicating that no matching record was found. Because data is organized according to an index structure based on the primary key, this structure can quickly locate the physical location of the data storage, reducing the number of I / O operations during the query process. By checking whether the primary key is in the array returned by the custom UDF function, the efficiency of data query and retrieval can be greatly improved.

[0156] The complete calling expression is as follows:

[0157] has(index_search('message','Network Management'),id)=1.

[0158] The following is an example of a query SQL:

[0159] SELECT id, name, address

[0160] from t_main_table

[0161] where has(index_search('message','Network Management'),id)=1

[0162] and address like '%Tongcheng%'

[0163] The combined use of the `has` function and custom UDF functions can achieve efficient full-text search filtering, while leveraging database indexes to optimize query performance and ensure fast and accurate data processing.

[0164] Based on the same inventive concept, this invention also proposes an integrated data analysis and full-text retrieval system. The implementation of this system can refer to the implementation of the methods described above, and repeated details will not be repeated. The term "module" used below can refer to a combination of software and / or hardware designed with predetermined functions. Although the system described in the following embodiments is preferably designed in software, hardware design, or a combination of software and hardware, is also possible and contemplated.

[0165] Figure 4 This is a schematic diagram of the integrated data analysis and full-text retrieval system structure of the present invention. For example... Figure 4 As shown, the system includes:

[0166] The data ingestion module 101 is used to create materialized views based on the main data table. The materialized view specifies the fields that need to be searched for full text and the primary key fields of the main data table. The materialized view uses the table of the URL engine as its base table, converts the received data insertion statement into an HTTP request of the URL, automatically builds an inverted index, and finally writes these indexes into the search engine's repository.

[0167] Build a search engine data ingestion service using the following steps:

[0168] Design an HTTP / HTTPS service interface that will be exposed to receive external data;

[0169] Determine which search engine's repository to use, based on its characteristics and project requirements;

[0170] Develop the service using any backend technology stack, ensuring that the service can handle HTTP POST requests;

[0171] Parse data from HTTP POST requests and convert the received data into the format required by the search engine's repository;

[0172] The parsed and transformed data is written to the search engine via the search engine's repository API.

[0173] Create a materialized view using the following steps:

[0174] Create a master data table, which serves as the sole entry point for data import, querying, and analysis.

[0175] Create a materialized view by using a SELECT statement to select the fields used for full-text search and the primary key fields of the main data table, and specify the base table of the materialized view;

[0176] Materialized views use the URL engine's table as their base table. When data is being stored in the database, the HTTP / HTTPS service of the search engine is invoked to write the data into the search engine's repository, which can then be used for subsequent full-text search.

[0177] The fields in the base table of a materialized view must be consistent with the fields selected in the SELECT statement when creating the materialized view, including field names, field order, and field data types;

[0178] The materialized view base table specifies two parameters: the HTTP / HTTPS service URL for the search engine data ingestion and the data format.

[0179] The data analysis query module 102 is used to define custom UDF functions. These UDF functions call the search engine's repository and utilize the search engine's full-text search capabilities to retrieve data. The parameters of the UDF function include the search field name and search keywords. The return value of the UDF function is an array composed of the primary keys of the hit records, and it is determined whether the value of the primary key field of the main data table is in the returned UDF array.

[0180] When full-text search filtering is required, a custom UDF function is called using the function has(arr,elem);

[0181] The function has(arr,elem) checks whether the array arr contains the element elem; if it does, it returns 1; otherwise, it returns 0.

[0182] The first parameter of the function has(arr,elem) is an array, which takes the return value of the custom UDF function as its first parameter. The second parameter of the function has(arr,elem) is the primary key field of the main table. That is, the function has(arr,elem) checks whether the value of the primary key field of the main table exists in the array returned by the custom UDF function. If it exists, it returns 1, indicating that a matching record was found; if it does not exist, it returns 0, indicating that no matching record was found.

[0183] It should be noted that although several modules of the integrated data analysis and full-text retrieval system are mentioned in the detailed description above, this division is merely exemplary and not mandatory. In fact, according to embodiments of the present invention, the features and functions of two or more modules described above can be embodied in one module. Conversely, the features and functions of one module described above can be further divided and embodied by multiple modules.

[0184] Based on the aforementioned inventive concept, such as Figure 5 As shown, the present invention also proposes a computer device 200, including a memory 210, a processor 220, and a computer program 230 stored in the memory 210 and executable on the processor 220. When the processor 220 executes the computer program 230, it designs the aforementioned integrated data analysis and full-text retrieval method.

[0185] Based on the aforementioned inventive concept, the present invention also proposes a computer-readable storage medium storing a computer program that performs the aforementioned integrated data analysis and full-text retrieval method.

[0186] The integrated data analysis and full-text retrieval method and system proposed in this invention have the following advantages:

[0187] 1. Automated data synchronization

[0188] This invention uses a materialized view mechanism to automatically synchronize data to a search engine without requiring additional synchronization programs, thus ensuring data consistency from the source.

[0189] 2. Simplify the query process

[0190] This invention directly returns the primary key array through a UDF function and uses the primary key index to locate the data, simplifying the traditional cross-system query into a single SQL call and improving query efficiency.

[0191] 3. Decoupled Architecture Design

[0192] This invention uses a URL engine as an intermediate layer, which decouples the analytical database from the search engine, improving the maintainability and scalability of the system.

[0193] 4. Unified query interface

[0194] This invention encapsulates full-text search capabilities into UDF functions that can be directly called from SQL, lowering the barrier to entry and improving development efficiency.

[0195] 5. Performance optimization and innovation

[0196] This invention combines the high-performance computing of analytical databases with the full-text retrieval advantages of search engines, and accelerates queries through primary key indexes, achieving efficient retrieval in large-scale data analysis scenarios.

[0197] While the spirit and principles of the invention have been described with reference to several specific embodiments, it should be understood that the invention is not limited to the disclosed specific embodiments, and the division of aspects does not imply that features in these aspects cannot be combined for benefit; such division is merely for ease of description. The invention is intended to cover various modifications and equivalent arrangements included within the spirit and scope of the appended claims.

[0198] Regarding the limitation of the scope of protection of this invention, those skilled in the art should understand that various modifications or variations that can be made by those skilled in the art without creative effort based on the technical solution of this invention are still within the scope of protection of this invention.

Claims

1. A method of integrated data analysis and full-text search, characterized by, The method comprises: Creating a materialized view based on the data master table, the materialized view specifying the fields that need to be subjected to full-text search and the primary key field of the data master table; the materialized view using the table of the URL engine as its base table, converting the received data insertion statement into an HTTP request of the URL, automatically building an inverted index, and finally writing the indexes into the repository of the search engine; Customizing a UDF function, the UDF function calling the repository of the search engine and utilizing the full-text search capability of the search engine to retrieve data; the parameters of the UDF function including the search field name and the search keyword, and the return value of the UDF function being an array composed of the primary keys of the hit records, and the UDF function judging whether the value of the primary key field of the data master table is in the returned array of the UDF function.

2. The method of integrated data analysis and full-text search according to claim 1, wherein, The search engine data repository service is built through the following steps: Designing an HTTP / HTTPS service interface that will be exposed to the outside for receiving external data; Determining which repository of the search engine to use, based on its characteristics and project requirements; Developing the service using any backend technology stack, ensuring that the service can handle HTTP POST requests; Parsing the data from the HTTP POST request and converting the received data into the format required by the repository of the search engine; Writing the parsed and converted data into the search engine through the API of the repository of the search engine.

3. The method of integrated data analysis and full-text search according to claim 1, wherein, The materialized view is created through the following steps: Creating a data master table, which is the only entry table for data repository, query, and analysis; Creating a materialized view by selecting the full-text search fields of the data master table and the primary key fields of the data master table through a SELECT statement, and specifying the base table of the materialized view; The materialized view uses the table of the URL engine as its base table, which is used to call the HTTP / HTTPS service of the search engine data repository when data is stored, and write the data into the repository of the search engine, so that it can be used for subsequent full-text query and retrieval.

4. The method of integrated data analysis and full-text search according to claim 3, wherein, The fields of the materialized view base table are consistent with the fields selected by the SELECT statement when creating the materialized view, including field name, field order, and field data type; The materialized view base table specifies two parameters: the HTTP / HTTPS service URL of the search engine data repository and the data format.

5. The method of integrated data analysis and full-text search according to claim 1, wherein, When full-text search filtering is needed, a custom UDF function is called through the function has(arr, elem); The function has(arr, elem) is used to check whether the array arr contains the element elem; if it contains, it returns 1; if it does not contain, it returns 0; The first parameter of the function has (arr, elem) is an array type, and the return result of the call of the custom UDF function is taken as the first parameter of the function has; the second parameter of the function has (arr, elem) is the primary key field of the data master table, that is, the function has (arr, elem) checks whether the value of the primary key field of the data master table exists in the array returned by the custom UDF function; if it exists, 1 is returned, indicating that a matching record is found; if it does not exist, 0 is returned, indicating that no matching record is found.

6. A system for integrated data analysis and full-text search, characterized by The system comprises: a data warehousing module, configured to create a materialized view based on a data master table, the materialized view specifying a field requiring full-text search and a primary key field of the data master table; the materialized view using a table of a URL engine as a base table thereof, converting a received data insertion statement into an HTTP request of the URL, and automatically building an inverted index, and finally writing the index into a storage of a search engine; a data analysis query module, configured to customize a UDF function, the UDF function calling the storage of the search engine and utilizing full-text search capability of the search engine to search data; the UDF function having parameters including a search field name and a search keyword, and the UDF function having a return value being an array composed of primary keys of hit records, and judging whether a value of the primary key field of the data master table is in the array returned by the UDF.

7. The system for integrated data analysis and full-text search according to claim 6, characterized in that, The search engine data warehousing service is built through the following steps: designing an HTTP / HTTPS service interface, which is exposed to the outside, for receiving external data; determining which storage of a search engine to use, based on its characteristics and project requirements; developing the service using any back-end technology stack, ensuring that the service can handle HTTP POST requests; parsing data from the HTTP POST request, and converting the received data into a format required by the storage of the search engine; writing the parsed and converted data into the search engine through an API of the storage of the search engine.

8. The system for integrated data analysis and full-text search according to claim 6, characterized in that, The materialized view is created through the following steps: creating a data master table, which is the only entry table for data warehousing, querying and analyzing; creating a materialized view, selecting a full-text search field of the data master table and a primary key field of the data master table through a SELECT statement, and specifying a base table of the materialized view; the materialized view using a table of a URL engine as a base table thereof, for calling an HTTP / HTTPS service of the search engine data warehousing when data is warehoused, and writing data into the storage of the search engine, so as to be available for subsequent full-text query and search.

9. The system for integrated data analysis and full-text search according to claim 8, characterized in that, The fields of the materialized view base table are consistent with the fields selected by the SELECT statement when the materialized view is created, including field names, field orders and field data types; the materialized view base table specifying two parameters: an HTTP / HTTPS service URL of the search engine data warehousing and a data format.

10. The system for integrated data analysis and full-text search of claim 6, wherein, When full-text search filtering is required, a custom UDF function is called through the function has (arr, elem). The function has (arr, elem) is to check whether the array arr contains the element elem; if yes, return 1; if not, return 0; The first parameter of the function has (arr, elem) is an array type, which returns the result of the call of the custom UDF function as the first parameter of the function has; the second parameter of the function has (arr, elem) is the primary key field of the data master table, that is, the function has (arr, elem) will check whether the value of the primary key field of the data master table exists in the array returned by the custom UDF function; if yes, return 1, indicating that the matching record is found; if not, return 0, indicating that the matching record is not found.

11. A computer device comprising a memory, a processor, and a computer program stored on the memory and executable on the processor, characterized in that, The processor executes the computer program to design the method of any one of claims 1-5.

12. A computer-readable storage medium, characterized in that, The computer readable storage medium stores a computer program for executing the method of any one of claims 1-5.

Images