A Method and Apparatus for Unstructured Data Query Based on PostgreSQL

Abstract

This invention proposes a method and apparatus for querying unstructured data based on PostgreSQL. The query method includes: S100, obtaining node feature vectors based on the query statement; S200, inputting the node feature vectors into a pre-trained graph convolutional network model integrated in PostgreSQL to obtain complex semantic vectors; S300, calculating similarity based on the complex semantic vectors using an HNSW index and outputting the query results. The graph convolutional network model is trained using a semantic relationship graph generated from an unstructured data sample set and the node feature vectors of that graph. This invention integrates a graph convolutional network model into PostgreSQL. This model preserves the topological structure information of the graph and can capture the relationships between nodes, making the similarity calculation between nodes more semantically meaningful. Furthermore, by combining these learned node representations with the HNSW index, the graph's topological structure can be better utilized for similarity calculation, resulting in more accurate and consistent calculation results.

Description

Technical Field

[0001] This invention relates to the field of data query technology, and in particular to a method and apparatus for querying unstructured data based on PostgreSQL. Background Technology

[0002] Traditional PostgreSQL query methods are primarily based on similarity matching. First, the data in the database is converted into vector form, where each vector represents a feature of a data point. Then, when a query vector is provided to the database, the database calculates the similarity between the query vector and all vectors in the database. Euclidean distance, cosine similarity, or other similarity metrics are typically used to calculate this similarity.

[0003] In this query method, the database returns the vector most similar to the query vector as the query result. This means that the returned result is usually the vector that is closest to the query vector in the feature space. However, this method may have some limitations.

[0004] First, similarity-based queries may have limitations when dealing with complex semantic relationships. For example, if the query is about fruits related to "apple," traditional PostgreSQL might only return results based on literal similarity, failing to understand the true intent of the query. This could result in returned results that do not fully meet the user's expectations.

[0005] Secondly, similarity-based query methods may have limited effectiveness when dealing with unstructured, complex data. Traditional PostgreSQL faces significant challenges with unstructured data such as text, images, and videos. This is because such data often possess diverse semantics and implicit high-level relationships, which similarity-based query methods struggle to capture.

[0006] In summary, PostgreSQL query methods may have certain limitations when handling complex unstructured data and queries with complex semantic relationships. Summary of the Invention

[0007] The technical problem to be solved by this invention is to improve the rationality of querying unstructured data and data with complex semantic relationships. This invention proposes an unstructured data query method and device based on PostgreSQL.

[0008] The unstructured data query method based on PostgreSQL according to embodiments of the present invention includes:

[0009] S100: Based on the query statement, obtain the node feature vector;

[0010] S200, the node feature vector is input into a pre-trained graph convolutional network model in PostgreSQL to obtain a complex semantic vector;

[0011] S300, based on the complex semantic vector, call the HNSW index to calculate the similarity and output the query results;

[0012] The graph convolutional network model is obtained by training a semantic relationship graph generated from an unstructured data sample set and the node feature vectors of the semantic relationship graph.

[0013] According to some embodiments of the present invention, in PostgreSQL, a node table for storing nodes of the semantic relationship graph and an edge table for storing edges are created. Step S100 specifically includes: obtaining the node feature vector from the node table according to the query statement.

[0014] In some embodiments of the present invention, step S200, the implementation method of the pre-trained graph convolutional network model integrated in PostgreSQL specifically includes:

[0015] S21, Obtain an unstructured data sample set and perform preprocessing operations, and construct a semantic relationship graph based on the preprocessed unstructured data sample set;

[0016] S22, Train the graph convolutional network model based on the semantic relation graph, and integrate the trained graph convolutional network model into PostgreSQL.

[0017] According to some embodiments of the present invention, when the unstructured data sample set is a text data sample set, step S21 specifically includes:

[0018] S211, Clean and preprocess the text data sample set by word segmentation;

[0019] S212, Construct a vocabulary based on the preprocessed text data sample set, and create a co-occurrence matrix corresponding to the vocabulary;

[0020] S213, Based on the frequency of word pairs in the text data sample set, determine the value of the corresponding element in the co-occurrence matrix, and create edges for the non-zero elements of the co-occurrence matrix;

[0021] S214, using words as nodes, weights are assigned to edges based on the values ​​of the corresponding elements in the co-occurrence matrix, and the semantic relationship graph is constructed.

[0022] In some embodiments of the present invention, step S22 specifically includes:

[0023] S221, Define the structure and parameters of the graph convolutional network model;

[0024] S222, using the semantic relationship graph and the feature vectors of the nodes as input to the graph convolutional network model, forward propagation is performed through multiple layers of graph convolution operations to update the node representation layer by layer;

[0025] S223, Calculate the gradient of the loss function of the graph convolutional network model with respect to the parameters, and update the parameters of the graph convolutional network model;

[0026] S224, Export the trained graph convolutional network model in a format for inference and integrate it into PostgreSQL.

[0027] According to some embodiments of the present invention, in step S222, the operations in each graph convolution layer specifically include:

[0028] Calculate the average or weighted sum of the neighbor features for each node, and aggregate the information of neighbor nodes;

[0029] Perform a linear transformation on the features of each node and the information of its neighboring nodes;

[0030] The representation of each node is updated based on the neighbor node information and the transformed features of the node.

[0031] In some embodiments of the present invention, in step S300, the complex semantic vector is used as the input of the HNSW index, the nearest neighbor search is performed through the HNSW index, the similarity score between nodes is calculated, and the most similar set of nodes is selected.

[0032] According to some embodiments of the present invention, in step S300, the query results are output by generating a report, a visualization chart, or in the form of an API.

[0033] In some embodiments of the present invention, the method further includes:

[0034] S400 allows you to add sorting conditions to your query statement and then sort and display the query results based on similarity scores.

[0035] According to an embodiment of the present invention, a PostgreSQL-based unstructured data query apparatus includes: a memory, a processor, and a computer program stored in the memory and executable on the processor. When the computer program is executed by the processor, it implements the steps of the PostgreSQL-based unstructured data query method as described above.

[0036] According to an embodiment of the present invention, a computer storage medium stores a computer program, which, when executed by a processor, implements the steps of the PostgreSQL-based unstructured data query method as described above.

[0037] The present invention has the following beneficial effects:

[0038] This invention integrates a graph convolutional network model into PostgreSQL. This model preserves the topological structure of the graph and captures the relationships between nodes, making the similarity calculation between nodes more semantically meaningful, rather than simply based on feature similarity. By combining these learned node representations with HNSW indexes, the graph's topological structure can be better utilized for similarity calculation, resulting in more accurate and consistent results. Attached Figure Description

[0039] Figure 1 This is an embodiment of the unstructured data query method based on PostgreSQL according to the present invention;

[0040] Figure 2 This is an embodiment of the unstructured data query method based on PostgreSQL according to the present invention;

[0041] Figure 3 This is a flowchart of a method for generating semantic relationship graphs from unstructured datasets according to an embodiment of the present invention;

[0042] Figure 4 This is a flowchart illustrating the method for training and integrating a graph convolutional network model into PostgreSQL according to an embodiment of the present invention.

[0043] Figure 5 A flowchart of a method for obtaining complex semantic vectors from a query statement according to an embodiment of the present invention;

[0044] Figure 6 This diagram illustrates the output of query results for calculating similarity using the HNSW index according to an embodiment of the present invention. Detailed Implementation

[0045] To further illustrate the technical means and effects of the present invention in achieving its intended purpose, the present invention will be described in detail below with reference to the accompanying drawings and preferred embodiments.

[0046] The steps described in the specification and the flowcharts in the accompanying drawings of this invention are not necessarily to be strictly followed according to the step numbers; the execution order of the steps can be changed. Furthermore, certain steps can be omitted, multiple steps can be combined into one step, and / or one step can be broken down into multiple steps.

[0047] In the current technological environment, PostgreSQL is widely used for high-dimensional and structured data, effectively storing and querying data. However, for complex unstructured data, such as text, images, and videos, PostgreSQL's support is relatively limited.

[0048] One challenge PostgreSQL faces is its adaptability to unstructured data, primarily relying on similarity matching for queries. However, this can be limiting for queries with complex semantic relationships. Traditional PostgreSQL may fail to accurately understand the query intent, returning only literal similarities. This can lead to inaccurate results in certain scenarios. Therefore, it's necessary to address this issue, enabling PostgreSQL to better understand the semantic relationships within queries and return more accurate results.

[0049] This invention proposes a PostgreSQL-based method for querying unstructured data, improving PostgreSQL's query capabilities and enabling it to better adapt to query requirements involving unstructured data and diverse semantic relationships. This method provides a database that better supports complex unstructured data, accurately understands and satisfies user query needs, and returns more accurate results. It offers an effective solution for handling queries with complex semantic relationships, enhancing PostgreSQL's adaptability and performance in the unstructured data domain.

[0050] The unstructured data query method based on PostgreSQL according to embodiments of the present invention includes:

[0051] S100: Based on the query statement, obtain the node feature vector;

[0052] It should be noted that the "query statement" here can be a user-written query statement for unstructured data. According to some embodiments of the present invention, in PostgreSQL, a node table for storing nodes of a semantic relationship graph and an edge table for storing edges are created. Step S100 specifically includes: obtaining node feature vectors from the node table according to the query statement.

[0053] S200 integrates node feature vectors into a pre-trained Graph Convolutional Network (GCN) model in PostgreSQL to obtain complex semantic vectors.

[0054] In other words, PostgreSQL integrates a pre-trained graph convolutional network model, which can output complex semantic vectors by taking the input node feature vectors as input.

[0055] S300, based on complex semantic vectors, calls the HNSW index to calculate similarity and outputs query results;

[0056] The graph convolutional network model is obtained by training a semantic relationship graph generated from an unstructured data sample set and the node feature vectors of the semantic relationship graph.

[0057] According to the PostgreSQL-based unstructured data query method of this invention, a Graph Convolutional Network (GCN) is trained using a semantic relationship graph generated from an unstructured data sample set, enabling the learning of semantic representations of nodes. This makes the similarity calculation between nodes more semantically meaningful, rather than merely based on feature-based similarity. Through local information aggregation and multi-layer iterative operations, the GCN can learn information at different scales and levels. This makes the model more invariant to the input, capable of extracting useful features from various dimensions and levels of the input, thus making the similarity calculation more robust and having better generalization performance.

[0058] Furthermore, combining the node representations learned by GCN with HNSW indexes enables efficient similarity calculation on large-scale datasets. HNSW indexes have low time and space complexity, effectively handling large datasets. Combining similarity calculation with GCN maintains high accuracy while offering good computational efficiency and scalability.

[0059] Furthermore, GCN preserves the graph's topological structure information during training and is able to capture the relationships between nodes. By combining these learned node representations with HNSW indexes, the graph's topological structure can be better utilized for similarity calculation, resulting in more accurate and consistent results.

[0060] In some embodiments of the present invention, step S200, the implementation method of the pre-trained graph convolutional network model integrated in PostgreSQL specifically includes:

[0061] S21, Obtain an unstructured data sample set and perform preprocessing operations, then construct a semantic relationship graph based on the preprocessed unstructured data sample set;

[0062] S22, train a graph convolutional network model based on semantic relation graphs, and integrate the trained graph convolutional network model into PostgreSQL.

[0063] According to some embodiments of the present invention, when the unstructured data sample set is a text data sample set, step S21 specifically includes:

[0064] S211, Cleaning and word segmentation preprocessing of the text data sample set;

[0065] S212, Construct a vocabulary based on the preprocessed text data sample set, and create a co-occurrence matrix corresponding to the vocabulary;

[0066] S213, Based on the frequency of word pairs in the text data sample set, determine the value of the corresponding element in the co-occurrence matrix, and create edges for the non-zero elements of the co-occurrence matrix;

[0067] S214 uses words as nodes and assigns weights to edges based on the values ​​of the corresponding elements in the co-occurrence matrix to construct a semantic relationship graph.

[0068] In some embodiments of the present invention, step S22 specifically includes:

[0069] S221, Define the structure and parameters of the graph convolutional network model;

[0070] S222 takes the semantic relation graph and the feature vectors of the nodes as input to the graph convolutional network model, and performs forward propagation through multiple layers of graph convolution operations to update the representation of the nodes layer by layer.

[0071] S223, calculate the gradient of the loss function of the graph convolutional network model with respect to the parameters, and update the parameters of the graph convolutional network model;

[0072] S224 exports the trained graph convolutional network model into a format for inference and integrates it into PostgreSQL.

[0073] According to some embodiments of the present invention, in step S222, the operations in each graph convolution layer specifically include:

[0074] Calculate the average or weighted sum of the neighbor features for each node, and aggregate the information of neighbor nodes;

[0075] Perform a linear transformation on the features of each node and the information of its neighboring nodes;

[0076] Update the representation of each node based on the neighbor node information and the transformed features of the node.

[0077] In some embodiments of the present invention, in step S300, a complex semantic vector is used as the input to the HNSW index, and the nearest neighbor search is performed through the HNSW index to calculate the similarity score between nodes and filter out the most similar set of nodes.

[0078] It should be noted that the HNSW index is a hierarchical index structure that can effectively support fast similarity calculation. Combining the complex semantic vectors learned by GCN with the HNSW index allows for efficient calculation of similarity on large-scale datasets.

[0079] According to some embodiments of the present invention, in step S300, the query results are generated as reports, visualizations, or output via API. This facilitates users in viewing the query results.

[0080] In some embodiments of the present invention, the method further includes:

[0081] S400 allows you to add sorting criteria to your query statement and then sort and display the results based on similarity scores. This improves the convenience and flexibility of your query operations.

[0082] According to an embodiment of the present invention, a PostgreSQL-based unstructured data query apparatus includes: a memory, a processor, and a computer program stored in the memory and executable on the processor. When the computer program is executed by the processor, it implements the steps of the PostgreSQL-based unstructured data query method as described above.

[0083] According to an embodiment of the present invention, a computer storage medium stores a computer program, which, when executed by a processor, implements the steps of the PostgreSQL-based unstructured data query method as described above.

[0084] The unstructured data query method based on PostgreSQL of the present invention will now be described in detail with reference to the accompanying drawings and a specific embodiment. It should be understood that the following description is merely exemplary and should not be construed as a specific limitation of the present invention.

[0085] like Figure 2 As shown, the PostgreSQL-based unstructured data query method of the present invention is as follows:

[0086] Step 1: Perform data cleaning and other preprocessing operations on the collected dataset D1, and construct a semantic relationship graph for the processed data D1 to capture semantic relationships using the graph structure.

[0087] Step 2: Use the constructed semantic relationship graph as input to train the graph convolutional network (GCN) to effectively utilize the semantic relationships between nodes, extract higher-level semantic representations, and integrate the trained graph convolutional network model into PostgreSQL.

[0088] Step 3: Use the feature representation of the query as input to the graph convolutional network (GCN) for inference to obtain the complex semantic representation of the query;

[0089] Step 4: Calculate the similarity using the Hierarchical Navigable Small Worlds (HNSW) index and provide the most relevant query results. The flowchart is shown below. Figure 1As shown.

[0090] like Figure 3 As shown, the specific method for step 1 is as follows:

[0091] Step 1.1: Perform preprocessing operations on dataset D1, such as cleaning and word segmentation, including removing stop words.

[0092] Step 1.2: Construct a vocabulary for the preprocessed dataset D1, containing all the words that appear; then, create an empty co-occurrence matrix with a size equal to the size of the vocabulary.

[0093] Step 1.3: Iterate through each text sample. For each word pair (w1, w2), if they co-occur in the same text, increment the value at the corresponding position in the co-occurrence matrix by one.

[0094] Step 1.4: Based on the non-zero elements of the co-occurrence matrix, create an edge for each non-zero element (i,j), where i and j are the indices of the corresponding words in the vocabulary.

[0095] Step 1.5: Based on the word co-occurrence matrix, transform it into a graph structure. Treat each word as a node in the graph, and assign weights to the edges based on the values ​​in the co-occurrence matrix.

[0096] Step 1.6: Define attributes for each node, which can be part-of-speech, word frequency, or other features. Adding attributes can provide more semantic information.

[0097] like Figure 4 As shown, the specific method for step 2 is as follows:

[0098] Step 2.1: Define the structure and hyperparameters of the graph convolutional network, including the number of graph convolutional layers, the output dimension of each layer, and the selection of activation functions.

[0099] Step 2.2: The graph structure data is used as the input to the graph convolutional network. The network is propagated forward through multiple layers of graph convolution operations to update the node representation layer by layer.

[0100] Step 2.3: For each layer, firstly, calculate the average or weighted sum of the neighbor features for each node to aggregate the information of neighbor nodes. Then, perform a linear transformation on the features of each node with the neighbor information and apply an activation function to generate a new node representation. Finally, update the representation of each node based on the aggregated neighbor features and the transformed features.

[0101] Step 2.4: Through multiple layers of convolutional operations, the graph convolutional network can gradually aggregate and integrate neighbor information from different levels, thereby extracting high-level semantic representations. Each layer of convolutional operations can be iterated multiple times to increase the model's ability to perceive local and global information about nodes, thus capturing a wider range of contextual information.

[0102] Step 2.5: Define the cross-entropy loss function and the softmax function as the loss function for model training. Calculate the gradient of the loss function with respect to the parameters using the backpropagation algorithm, and use Batch Gradient Descent (BGD) to update the parameters in the graph convolutional network.

[0103] Step 2.6: After training is complete, export the trained graph convolutional network model in a format suitable for inference. Integrate the exported model into PostgreSQL through an external extension, making it the core computation and auxiliary query engine of PostgreSQL.

[0104] like Figure 5 As shown, the specific method for step 3 is as follows:

[0105] Step 3.1: In PostgreSQL, the first step is to define a database schema suitable for storing graph data. Create two tables: one to store the nodes of the graph, and the other to store the edges. The node table and edge table are designed according to actual needs, ensuring that they can store the identifiers of nodes and edges, as well as other attribute information, including vector representations.

[0106] Step 3.2: Write the query statement and define the query conditions, including keywords, attributes, or other limiting conditions. The query statement can retrieve the feature vectors of nodes from the node table.

[0107] Step 3.3 uses the graph convolutional network model integrated in PostgreSQL for inference to obtain the semantic representation of the nodes. The feature vectors of the nodes serve as the input to the model, and the output is the corresponding semantic representation vector.

[0108] Step 3.4: Use the query results to update the semantic representation column in the node table. You can add an extra column.

[0109] Step 3.5: Execute the query statement in the database, calculate the semantic similarity between the query node and other nodes, sort the results according to the similarity and select the most relevant node, thereby finding the node most relevant to a query node in the semantic space.

[0110] like Figure 6 As shown, the specific method for step 4 is as follows:

[0111] Step 4.1: In the external extension, call the HNSW index building function and pass in the semantic representation of the node table as input to HNSW to build the index structure and accelerate similarity calculation.

[0112] Step 4.2: Use the HNSW index to perform a nearest neighbor search to find the node most similar to the given node. Utilize the functions and operators supported by PostgreSQL to calculate the similarity score between nodes and filter out the set of nodes most similar to the query node.

[0113] Step 4.3: Use the JOIN operation to associate the matched nodes with the result details table, retrieve other information or attributes associated with similar nodes, and obtain more detailed display data.

[0114] Step 4.4: Generate reports, generate visualizations, or output data via API in the application.

[0115] Step 4.5: By adding appropriate sorting conditions to the query statement, the matching results are sorted according to similarity scores, and the most relevant results are selected and displayed to the user.

[0116] The present invention has the following beneficial effects:

[0117] (1) This invention trains a Graph Convolutional Network (GCN) on a semantic relation graph, which can learn the semantic representation of nodes. This makes the similarity calculation between nodes more semantically meaningful, rather than just based on feature similarity.

[0118] (2) GCN can learn information at different scales and levels through local information aggregation and multi-level iterative operations. This makes the model more invariant to the input and can extract useful features from various dimensions and levels of the input, making similarity calculation more robust and generalizing.

[0119] (3) The HNSW index is a hierarchical index structure that can effectively support fast similarity calculation. By combining the node representations learned by GCN with the HNSW index, similarity can be calculated efficiently on large-scale datasets.

[0120] (4) HNSW indexes have low time and space complexity and can effectively handle large-scale datasets. Combining them with GCN for similarity calculation can maintain high accuracy while having good computational efficiency and scalability.

[0121] (5) GCN preserves the topological structure information of the graph during training and can capture the relationships between nodes. By combining these learned node representations with HNSW indexes, the topological structure of the graph can be better utilized for similarity calculation, making the calculation results more accurate and consistent.

[0122] Through the description of specific embodiments, a more in-depth and specific understanding should be gained of the technical means and effects adopted by the present invention to achieve the intended purpose. However, the accompanying drawings are only provided for reference and illustration and are not intended to limit the present invention.

Claims

1. A method for querying unstructured data based on PostgreSQL, characterized in that, The method comprises the following steps: S100, obtaining a node feature vector based on a query statement; S200, inputting the node feature vector into a graph convolution network model pre-trained in PostgreSQL to obtain a complex semantic vector; In step S200, the implementation method of the graph convolution network model pre-trained in PostgreSQL comprises the following steps: S21, obtaining an unstructured data sample set and performing a preprocessing operation, and constructing a semantic relation graph based on the preprocessed unstructured data sample set; S22, training the graph convolution network model based on the semantic relation graph, and integrating the trained graph convolution network model into PostgreSQL; When the unstructured data sample set is a text data sample set, step S21 comprises the following steps: S211, performing cleaning and word segmentation preprocessing on the text data sample set; S212, constructing a vocabulary table based on the preprocessed text data sample set, and creating a co-occurrence matrix corresponding to the vocabulary table; S213, determining the value of the corresponding position element in the co-occurrence matrix according to the appearance frequency of the word pair in the text data sample set, and creating an edge for the non-zero element of the co-occurrence matrix; S214, taking a word as a node, assigning a weight to an edge according to the value of the corresponding position element in the co-occurrence matrix, and constructing the semantic relation graph; S300, calculating the similarity by calling the HNSW index based on the complex semantic vector, and outputting the query result; The graph convolution network model is trained by a semantic relation graph generated by an unstructured data sample set and a node feature vector of the semantic relation graph.

2. The PostgreSQL-based unstructured data query method according to claim 1, wherein, In PostgreSQL, a node table for saving nodes of the semantic relation graph and an edge table for saving edges are created, and step S100 comprises the following steps: obtaining the node feature vector from the node table according to the query statement.

3. The unstructured data query method of claim 1, wherein, Step S22 comprises the following steps: S221, defining the structure and parameters of the graph convolution network model; S222, taking the semantic relation graph and the feature vector of the node as the input of the graph convolution network model, performing forward propagation through multi-layer graph convolution operation, and updating the representation of the node layer by layer; S223, calculating the gradient of the loss function of the graph convolution network model with respect to the parameter, and updating the parameter of the graph convolution network model; S224, exporting the trained graph convolution network model into a format for inference, and integrating it into PostgreSQL.

4. The unstructured data query method of claim 1, wherein, In step S300, the complex semantic vector is taken as the input of the HNSW index, the nearest neighbor search is performed through the HNSW index, the similarity score between nodes is calculated, and the most similar node set is selected.

5. The unstructured data query method of claim 1, wherein, In step S300, the query result data is output in the form of a report, a visualization chart or an API.

6. The unstructured data query method of claim 1, wherein, The method further comprises the following steps: S400, adding a sorting condition in the query statement, and sorting and displaying the query result according to the similarity score.

7. A PostgreSQL-based unstructured data query apparatus, characterized by comprising: The device comprises a memory, a processor and a computer program stored on the memory and executable on the processor, which, when executed by the processor, implements the steps of the PostgreSQL-based unstructured data query method according to any one of claims 1 to 6. 8.A computer storage medium, having stored thereon a computer program, which, when executed by a processor, implements the steps of the PostgreSQL-based unstructured data query method according to any one of claims 1 to 6.

Images