Data retrieval method and system based on data column

Abstract

The present invention discloses a data retrieval method and system based on data columns, which includes: extracting and distinguishing each field data of each row in the accessed source system database table, and placing the data in a newly added data column of Elasticsearch; filtering out index data from multiple fields of the data column; and utilizing the inverted index algorithm of Elasticsearch to fuzzily match the query content input by the user to the data column according to the index data to obtain the corresponding field data. By implementing the present invention, search performance is improved, and the exploration and innovation of the word segmentation application of fuzzy query is realized, supporting the demand conditions when searching with one click, meeting the business needs of users when performing queries, conforming to the search habits of users, achieving accurate hits in billions of data, no missed searches, and short time consumption, while achieving a second-level response to billions of data.

Description

Technical Field

[0001] The present invention relates to the field of computer development technology, and in particular to a data column-based data retrieval method and system. Background Art

[0002] Elasticsearch is suitable for fast retrieval of data volumes in the billions, mainly due to the inverted index, which can find corresponding documents by key. The determination of the word index relies on the automatic matching and parsing of the word segmenter, but the word segmenter is an Elasticsearch plug-in that needs to be downloaded separately before it can be used. When the resource environment is limited and the word segmenter cannot be installed and used, if Elasticsearch is abandoned, the existing system will need to connect to dozens of source systems. Some of these source systems have been running since the early days of the factory construction, and the amount of data is quite large, ranging from tens of millions to hundreds of millions, as shown in the attached file. Figure 1 As shown in the following table, some tables have hundreds of columns, and the number of columns is large and complex. Figure 2 As shown, existing technologies cannot meet current needs. However, using Elasticsearch itself is limited. As a result, one-click searches or queries can fail to find the required data based on the conditions, or the query results can be chaotic and the query performance can be substandard. Summary of the Invention

[0003] The technical problem to be solved by the present invention is to provide a data column-based data retrieval method and system to address at least one defect in the prior art.

[0004] The technical solution adopted by the present invention to solve the technical problem is to construct a data retrieval method based on data columns, including the following steps:

[0005] S10: Extract and distinguish the data of each field in each row of the source system database table, and place it in the newly added data column of Elasticsearch;

[0006] S20: filtering out index data from the multiple fields of the data column;

[0007] S30: Using the inverted index algorithm of Elasticsearch, fuzzy match the query content input by the user to the data column according to the index data to obtain the corresponding field data.

[0008] Preferably, in the data column-based data retrieval method of the present invention, before step S10, the method further includes:

[0009] S01: After performing data analysis on the connected source system database, the required data for query analysis is obtained from the database to form a source system database table.

[0010] Preferably, in the data column-based data retrieval method of the present invention, before step S10, the method further includes:

[0011] S02: Processing and converting the data in the source system database table and storing the data.

[0012] Preferably, in the data column-based data retrieval method of the present invention, the differentiation method of step S10 is: using preset identifiers for extraction and differentiation.

[0013] Preferably, in the data column-based data retrieval method of the present invention, step S20 includes:

[0014] S201: Selecting key fields from multiple fields of the data column according to preset business requirements;

[0015] S202: Filter out index data according to key fields, including index terms and data column positions corresponding to their indexes.

[0016] Preferably, in the data column-based data retrieval method of the present invention, step S30 includes:

[0017] S301: Matching corresponding index terms according to the query content input by the user;

[0018] S302: Using the inverted index algorithm of Elasticsearch, the query content entered by the user is matched to the data column position corresponding to the index, and the corresponding field data is output.

[0019] The present invention also constructs a data retrieval system based on data columns, including:

[0020] New data columns added in Elasticsearch;

[0021] The extraction module is used to extract and distinguish each field data of each row in the accessed source system database table and place it in the data column;

[0022] A filtering module, configured to filter out index data from a plurality of fields in the data column;

[0023] The retrieval module is used to use the inverted index algorithm of Elasticsearch to fuzzy match the query content entered by the user to the data column according to the index data, and obtain the corresponding field data.

[0024] Preferably, in the data column-based data retrieval system of the present invention, the system further includes:

[0025] The analysis module is used to analyze the data of the connected source system database, obtain the required data for query and analysis from the database, and form the source system database table;

[0026] A conversion module, used to process and convert the data in the source system database table;

[0027] The storage module is used to store the source system database table after processing and conversion.

[0028] Preferably, in the data column-based data retrieval system of the present invention, the filtering module includes:

[0029] A keyword selection unit, configured to select a key field from the plurality of fields in the data column according to preset business requirements;

[0030] The index data filtering unit is used to filter out index data according to key fields, including index terms and the data column positions corresponding to their indexes.

[0031] Preferably, in the data column-based data retrieval system of the present invention, the retrieval module includes:

[0032] An index term matching unit, configured to match corresponding index terms according to the query content input by the user;

[0033] The data matching unit is used to use the inverted index algorithm of Elasticsearch to match the query content entered by the user to the data column position corresponding to the index and output the corresponding field data.

[0034] By implementing the present invention, the following beneficial effects are achieved:

[0035] A data column is constructed through aggregation, similar to the row-to-column conversion method, in which all the original field data is spliced ​​into a large data column. Subsequently, index data is obtained through a quasi-word segmenter. When performing fuzzy queries, full-text indexing is performed on this column in combination with Elasticsearch's own inverted index algorithm. This solves the problem of only being able to perform single-word segmentation when Elasticsearch does not apply a word segmenter or configure a word segmentation file, which affects search performance. This improves search performance and realizes the exploration and innovation of word segmentation applications in fuzzy queries.

[0036] When performing a one-click search, there is no need to adapt each column in the source system database table. You only need to retrieve the data column. This is a typical application expansion that trades space for time. It solves the problems of a large number of complex field columns in the source database table, easy omissions when searching for large amounts of data, incorrect query results, long waiting times, low retrieval efficiency, and high memory usage. It supports the requirements for one-click search, meets the business needs of users when performing queries, conforms to users' search habits, and achieves accurate hits in billions of data, no omissions, and short time consumption. At the same time, it achieves a response of billions of data in seconds. BRIEF DESCRIPTION OF THE DRAWINGS

[0037] The present invention will be further described below with reference to the accompanying drawings and embodiments, in which:

[0038] Figure 1 It is a schematic diagram of the data volume of a source system;

[0039] Figure 2 It is a schematic diagram of the fields in a source system database table;

[0040] Figure 3 It is a source system database data representation scheme;

[0041] Figure 4 This is a schematic diagram after the field data is distinguished;

[0042] Figure 5 This is a schematic diagram of the data retrieval method based on data columns of the present invention. Figure 1 ;

[0043] Figure 6 This is a schematic diagram of the data retrieval method based on data columns of the present invention. Figure 2 ;

[0044] Figure 7 It is a module block diagram of the data retrieval system based on data columns of the present invention. DETAILED DESCRIPTION

[0045] In order to have a clearer understanding of the technical features, purposes and effects of the present invention, specific embodiments of the present invention are now described in detail with reference to the accompanying drawings.

[0046] It should be noted that the flowcharts shown in the accompanying drawings are for illustrative purposes only and do not necessarily include all content and operations / steps, nor must they be executed in the order described. For example, some operations / steps may be decomposed, while others may be combined or partially combined. Therefore, the actual execution order may vary depending on the actual situation.

[0047] The block diagrams shown in the accompanying drawings are merely functional entities and do not necessarily correspond to physically separate entities. That is, these functional entities may be implemented in software, in one or more hardware modules or integrated circuits, or in different networks and / or processor devices and / or microcontroller devices.

[0048] like Figure 5 As shown, this embodiment discloses a data retrieval method based on data columns, including the following steps:

[0049] Step S10: extract and distinguish the data of each field in each row of the source system database table, and place it in the newly added data column of Elasticsearch;

[0050] Step S20: filtering out index data from multiple fields of the data column;

[0051] Step S30: Using the inverted index algorithm of Elasticsearch, the query content entered by the user is fuzzy matched to the data column according to the index data, the corresponding field data is obtained, and the search is completed.

[0052] A data column is constructed through aggregation, similar to the row-to-column conversion method, in which all the original field data is spliced ​​into a large data column. Subsequently, index data is obtained through a quasi-word segmenter. When performing fuzzy queries, full-text indexing is performed on this column in combination with Elasticsearch's own inverted index algorithm. This solves the problem of only being able to perform single-word segmentation when Elasticsearch does not apply a word segmenter or configure a word segmentation file, which affects search performance. This improves search performance and realizes the exploration and innovation of word segmentation applications in fuzzy queries.

[0053] When performing a one-click search, there is no need to adapt each column in the source system database table. You only need to retrieve the data column. This is a typical application expansion that trades space for time. It solves the problems of a large number of complex field columns in the source database table, easy omissions when searching for large amounts of data, incorrect query results, long waiting times, low retrieval efficiency, and high memory usage. It supports the requirements for one-click search, meets the business needs of users when performing queries, conforms to users' search habits, and achieves accurate hits in billions of data, no omissions, and short time consumption. At the same time, it achieves a response of billions of data in seconds.

[0054] It should be noted that searching for corresponding records based on a certain word (incomplete condition) is called an inverted index.

[0055] In some embodiments, before step S10, the method further includes:

[0056] Step S01: After performing data analysis on the connected source system database, the required data for query analysis is obtained from the database to form a source system database table.

[0057] In some embodiments, before step S10, the method further includes:

[0058] Step S02: Process and convert the data in the source system database table and store it. For example, the data can be filtered to remove null values ​​and the filtered data can be stored in the DWD layer.

[0059] In some embodiments, the distinguishing method of step S10 is: using a preset identifier to extract and distinguish. For example, using ',' to extract and distinguish, such as Figure 4 shown.

[0060] In some embodiments, step S20 includes:

[0061] S201: Select key fields from multiple fields in the data column according to preset business requirements, such as power plant, unit, source system, etc.;

[0062] S202: Filter out index data according to key fields, including index terms and data column positions corresponding to their indexes.

[0063] Accordingly, step S30 includes:

[0064] S301: Match the corresponding index words according to the query content input by the user; Figure 6 As shown, it is determined whether the index words of the index data contain the query content. If so, step S302 is executed. If not, the data set is empty.

[0065] S302: Using the inverted index algorithm of Elasticsearch, the query content entered by the user is matched to the data column position corresponding to the index, and the corresponding field data is output.

[0066] like Figure 7 As shown, this embodiment discloses a data retrieval system based on data columns, including:

[0067] New data columns added in Elasticsearch;

[0068] The extraction module is used to extract and distinguish the data of each field in each row of the accessed source system database table and place it in the data column;

[0069] The filtering module is used to filter out index data from multiple fields of a data column;

[0070] The retrieval module is used to use the inverted index algorithm of Elasticsearch to fuzzy match the query content entered by the user to the data column based on the index data, obtain the corresponding field data, and complete the search.

[0071] It should be noted that searching for corresponding records based on a certain word (incomplete condition) is called an inverted index.

[0072] In some embodiments, the system further comprises:

[0073] The analysis module is used to analyze the data of the connected source system database, obtain the required data for query and analysis from the database, and form the source system database table.

[0074] In some embodiments, the system further comprises:

[0075] The conversion module is used to process and convert the data in the source system database table;

[0076] The storage module is used to store the source system database table after processing and conversion. For example, it can filter the data to remove null values ​​and store the filtered data in the DWD layer.

[0077] In some embodiments, the distinguishing method is: using a preset identifier to extract and distinguish. For example, using ',' to extract and distinguish, such as Figure 4 shown.

[0078] In some embodiments, the filtering module includes:

[0079] The keyword selection unit is used to select key fields from multiple fields in the data column according to preset business requirements, such as power plant, unit, source system, etc.

[0080] The index data filtering unit is used to filter out index data according to key fields, including index terms and the data column positions corresponding to their indexes.

[0081] Accordingly, the retrieval module includes:

[0082] The index word matching unit is used to match the corresponding index word according to the query content input by the user; Figure 6 As shown, it is determined whether the index words of the index data contain the query content. If so, step S302 is executed. If not, the data set is empty.

[0083] The data matching unit is used to use the inverted index algorithm of Elasticsearch to match the query content entered by the user to the data column position corresponding to the index and output the corresponding field data.

[0084] By implementing the present invention, the following beneficial effects are achieved:

[0085] A data column is constructed through aggregation, similar to the row-to-column conversion method, in which all the original field data is spliced ​​into a large data column. Subsequently, index data is obtained through a quasi-word segmenter. When performing fuzzy queries, full-text indexing is performed on this column in combination with Elasticsearch's own inverted index algorithm. This solves the problem of only being able to perform single-word segmentation when Elasticsearch does not apply a word segmenter or configure a word segmentation file, which affects search performance. This improves search performance and realizes the exploration and innovation of word segmentation applications in fuzzy queries.

[0086] When performing a one-click search, there is no need to adapt each column in the source system database table. You only need to retrieve the data column. This is a typical application expansion that trades space for time. It solves the problems of a large number of complex field columns in the source database table, easy omissions when searching for large amounts of data, incorrect query results, long waiting times, low retrieval efficiency, and high memory usage. It supports the requirements for one-click search, meets the business needs of users when performing queries, conforms to users' search habits, and achieves accurate hits in billions of data, no omissions, and short time consumption. At the same time, it achieves a response of billions of data in seconds.

[0087] It is understandable that the above embodiments only express the preferred implementation modes of the present invention, and the description thereof is relatively specific and detailed, but it cannot be understood as limiting the patent scope of the present invention. It should be pointed out that for ordinary technicians in this field, without departing from the concept of the present invention, the above technical features can be freely combined, and several deformations and improvements can be made, all of which fall within the scope of protection of the present invention. Therefore, all equivalent changes and modifications made to the scope of the claims of the present invention should fall within the scope of coverage of the claims of the present invention.

Claims

1. A data retrieval method based on data columns, characterized in that: The following steps are involved: S10: Extract and distinguish the data of each field in each row of the source system database table, and place it in the newly added data column of Elasticsearch; S20: filtering index data from the multiple fields of the data column; the Elasticsearch does not use a word segmenter; S30: Using the inverted index algorithm of Elasticsearch, fuzzy matching the query content entered by the user to the data column according to the index data to obtain the corresponding field data; Wherein, the step S10 includes: extracting and distinguishing each field data of each row using a preset identifier, and splicing the extracted and distinguished data into the data column; The step S20 includes: selecting a key field from multiple fields of the data column according to preset business requirements, and filtering index data according to the key field; the index data includes index terms and data column positions corresponding to the indexes.

2. The data column-based data retrieval method according to claim 1, characterized in that: Before step S10, the following steps are also included: S01: After performing data analysis on the connected source system database, the required data for query analysis is obtained from the database to form a source system database table.

3. The data column-based data retrieval method according to claim 2, characterized in that: Before step S10, the following steps are also included: S02: Processing and converting the data in the source system database table and storing the data.

4. The data retrieval method based on data columns according to claim 1, characterized in that: Step S30 includes: S301: Matching corresponding index terms according to the query content input by the user; S302: Using the inverted index algorithm of Elasticsearch, the query content entered by the user is matched to the data column position corresponding to the index, and the corresponding field data is output.

5. A data retrieval system based on data columns, characterized in that: include: New data columns added in Elasticsearch; Elasticsearch does not use a word segmenter; The extraction module is used to extract and distinguish each field data of each row in the accessed source system database table, and place it in the data column; specifically, the data of each field of each row is extracted and distinguished using a preset identifier, and the extracted and distinguished data is spliced ​​into the data column; A filtering module, configured to filter out index data from a plurality of fields in the data column; The retrieval module is used to use the inverted index algorithm of Elasticsearch to fuzzy match the query content entered by the user to the data column based on the index data and obtain the corresponding field data; The filtering module includes: A keyword selection unit, configured to select a key field from the plurality of fields in the data column according to preset business requirements; The index data filtering unit is used to filter out index data according to key fields, wherein the index data includes index words and data column positions corresponding to the indexes.

6. The data column-based data retrieval system according to claim 5, characterized in that: The system also includes: The analysis module is used to analyze the data of the connected source system database, obtain the required data for query and analysis from the database, and form the source system database table; A conversion module, used to process and convert the data in the source system database table; The storage module is used to store the source system database table after processing and conversion.

7. The data column-based data retrieval system according to claim 5, characterized in that: The retrieval module includes: An index term matching unit, configured to match corresponding index terms according to the query content input by the user; The data matching unit is used to use the inverted index algorithm of Elasticsearch to match the query content entered by the user to the data column position corresponding to the index and output the corresponding field data.

Images