Building an RAG Framework Using DWS to Generate Industry Research Reports

Basic Concepts

Before you start, learn the following concepts first:

• RAG

  It is a technical paradigm that combines information retrieval with LLM generation. It retrieves task-related information from an external knowledge base before an LLM generates text, and then injects the obtained information as context into the generation process. This mitigates LLM hallucinations and knowledge lag.

• LLM

  It is a large-scale pre-trained language model based on deep learning and can generate coherent natural language content based on input prompts. In this solution, it is used to generate a survey report based on text blocks.

• Vector

  A vector maps unstructured data such as text and images to numerical arrays with fixed dimensions (for example, [0.12, 0.34, -0.21, ...]) through mathematical methods. This allows computers to measure the semantic similarity between data through numerical calculations. For example, a three-dimensional vector [30, 175, 70] can be used to describe a person's age, height, and weight, allowing the computer to understand the semantic features.

• Embedding (vectorization)

  It refers to the process of converting unstructured data (such as text) into vectors, typically using LLMs (such as BERT and GPT). The vectors retain the semantic information of the raw data. Text with similar semantics is also close in the vector space, allowing for semantic-level text retrieval through vector similarity comparison.

• Chunk

  It refers to the process of dividing a long text into short text segments with complete semantics based on a certain granularity. Chunking facilitates vectorization and precise retrieval, preventing semantic ambiguity caused by the vector of a long text.

• Approximate nearest neighbor (ANN)

  It is an algorithm used to quickly find the top-k vectors that are most similar to a target vector in a high-dimensional vector space. Compared with exact search, ANN provides much better performance at the cost of minimal precision loss, making it suitable for large-scale vector retrieval.

• pgai is an AI plugin integrated into DWS. It allows you to call LLMs and embedding models within a database and provides AI functions such as text chunking (chunk_text_recursively), text ranking (rank), text generation (openai_chat_complete), and vectorization (openai_embed).
• pgvector is a vector storage and retrieval plugin integrated into DWS. It provides the vector data type and vector indexes based on the HNSW algorithm, and supports the efficient ANN.

Notes and Constraints

• The DWS version must be 9.1.1.200 or later. Contact technical support to set enable_pgvector and enable_pgai_extension for the GUC parameter feature_support_options.
• In clusters with decoupled storage and compute, the pgvector extension function (CREATE EXTENSION statement) can be executed only on primary VWs.

Prerequisites

• A DWS cluster has been created and is available. For details about how to create a cluster, see Creating a DWS Cluster.
• Before using this function, contact technical support to install the pgai plugin.
• Model API: Prepare a large model API service (compatible with OpenAI API specifications) that can be accessed in advance and obtain a valid base URL and API key. The API service must have the following model capabilities:
  • Embedding model: used for text vectorization (such as text-embedding-ada-002)
  • Chat model: used for text generation and ranking (such as GPT-4 and GPT-3.5-turbo)
• Network connectivity: The DWS cluster must be able to access the URL of the model API service. If the API service is deployed on a public network, ensure that the cluster can access the public network. If services such as Huawei Cloud ModelArts are used, ensure that the VPC of the cluster can communicate with that of the API service.

RAG Architecture Built Using DWS

Naive RAG

The Naive RAG framework obtains highly relevant information from an external knowledge base before an LLM generates text, and injects the information as context into the generation process. This mechanism effectively mitigates issues such as LLM hallucinations, knowledge lag, and poor domain adaptability. It is particularly suitable for complex tasks that rely on specific domain knowledge, such as professional Q&A, long document summarization, and industry survey report generation.

• Retrieval: Based on the user's input question or prompt, relevant text fragments (such as paragraphs or document blocks) are retrieved from the pre-built knowledge base.
• Generation: The retrieval result and the raw query are combined into a structured prompt, which is then forwarded to the LLM to generate an output. This ensures that the answer is reliable and logically sound.

During system initialization, the raw text data sources are preprocessed and split into text chunks with complete semantics. Each chunk is converted into a high-dimensional vector using an embedding model and then stored persistently in DWS (based on the vector storage provided by pgvector and indexing capabilities provided by HNSW).

When receiving a user query request, the system synchronously encodes the query text into a vector and performs an ANN search in DWS to retrieve the top-k text chunks that are most relevant semantically. These retrieval results, together with the user's original query, form an enhanced prompt, which is then input into the LLM to generate an accurate and traceable response.

Step 1: Create Extensions and Storage Tables

Create pgai and pgvector extensions and create data tables for storing long text corpora, chunked text vectors, and the survey report. This provides the data storage required for the subsequent RAG process.

1. Ensure that enable_pgvector and enable_pgai_extension are set for the GUC parameter feature_support_options. Contact technical support to set the parameter.
2. Contact technical support to deploy the pgai plugin.
3. Connect to the DWS database and create the required extensions.

   1 2	CREATE EXTENSION ai; CREATE EXTENSION pgvector;

   If the following errors are displayed, the extensions are disabled in the GUC parameter. Contact technical support to enable them.

   

   If the following error is displayed after the extensions have been enabled in the GUC parameter, the pgai plugin is not installed. Contact technical support to install it.

   

4. Create a long text corpus table. Each row in the documents table represents a long text corpus. id indicates the primary key that distinguishes rows, topic indicates the topic of the long text corpus, and content indicates the content of the long text corpus.

   1 2 3 4 5

   CREATE TABLE documents( id SERIAL PRIMARY KEY, topic text, content text );

5. Create a long text vectorization table. The chunk_text table records all long text content after chunking. chunk indicates the text content after chunking, and embedding indicates the vector representation of the text after chunking. The hnsw index is used to accelerate approximate vector retrieval.

   1 2 3 4 5 6

   CREATE TABLE chunk_text( id SERIAL PRIMARY KEY, chunk text, embedding vector ); CREATE INDEX ON chunk_text USING hnsw(embedding vector_cosine_ops);

6. Create a survey report result table. Each row in the reports table represents a survey report, and content indicates the text content of a report.

   1 2 3 4

   CREATE TABLE reports( id SERIAL PRIMARY KEY, content text );

Step 2: Configure Model APIs

Configure the connection information and model name of the model API service for the pgai functions so that DWS can call external large model services.

1. Prepare a model that can be called, for example, Huawei Cloud MaaS. For details about how to purchase models, see the MaaS Documentation.
2. Set the base URL and API key of the API service.

   Replace baseurl with the URL of the model API service (for example, https://api.openai.com/v1) and replace apikey with a valid API key. The API key is encrypted and stored in DWS.

   1	SELECT ai.dws_pgai_encrypt_info('baseurl', 'apikey');

3. Set the name of the model used by each AI function.

   1 2 3 4 5 6

   -- Set the embedding model (used for text vectorization). SELECT ai.set_func_model('openai_embed', 'text-embedding-ada-002'); -- Set the chat model (used for text generation). SELECT ai.set_func_model('openai_chat_complete', 'gpt-4'); -- Set the ranking model (used for document coarse screening). SELECT ai.set_func_model('rank', 'gpt-4');

Step 3: Import Test Data

1. Import long text corpora. Import the raw long text corpora to the documents table as the knowledge base for RAG retrieval. You can use the INSERT statement to import the data piece by piece, or use the batch import tool of DWS (such as the \copy command or GDS) to load the data in batches from files.

   1 2 3 4

   INSERT INTO documents(topic, content) VALUES ('Database Market Demand', 'As digital transformation accelerates, the global demand for databases is increasing. Enterprises' needs for data-driven decision-making, efficiency improvement, and innovation have driven the widespread application of database technologies. From traditional relational databases (RDBMS) to emerging non-relational databases (NoSQL), various types of databases have been applied in different industries, especially in finance, e-commerce, healthcare, and IoT. Big data analytics makes it necessary for enterprises to process massive amounts of data and quickly extract valuable information from it. This has driven the demand for distributed and high-performance databases. In addition, the popularity of cloud computing is also a significant driving force of the demand for databases. An increasing number of enterprises are choosing to migrate their databases to the cloud, leveraging the elasticity and scalability of cloud databases to meet rapidly growing business needs. According to data from the market research firm Gartner, the global cloud database market will reach approximately $6 billion in 2023 and is expected to continue growing in the coming years. Especially in the context of enterprises' increasing demands for fast data access and analysis, database technologies are playing an even more important role. In terms of data security, as data breaches occur more frequently, database security is an issue that enterprises cannot ignore. An increasing number of enterprises are attaching importance to security features such as data encryption, identity authentication, and access control. This poses higher technical requirements for database vendors. Enterprises require efficient data processing of databases while also demanding security.'); INSERT INTO documents(topic, content) VALUES ('Popular Database Technologies', 'In recent years, database technologies have been iterating, and some significant trends have emerged. First, distributed databases are gradually becoming the mainstream of technological development. As cloud computing, big data, and artificial intelligence (AI) continue to advance, traditional single-node databases struggle to process massive amounts of data. Distributed databases distribute data storage and computing tasks across multiple nodes, offering better scalability and fault tolerance. Second, multimodal databases and graph databases are leading another trend in database technologies. Multimodal databases support various data models (such as document, relational, graph, and column-based storage), enabling enterprises to process different types of data on the same platform. Graph databases are gaining more attention from more and more enterprises due to their advantages in fields like social networking, recommendation systems, and path analysis. Additionally, with the development of AI and machine learning (ML), intelligent databases have emerged as a new trend. They integrate AI capabilities and automate data processing, including automatic index optimization, query optimization, and anomaly detection. This not only improves database efficiency but also helps users reduce maintenance costs. Serverless databases are also among the popular technologies, especially in the cloud database field. This architecture allows users to pay only for the computing resources they use, without having to manage or maintain database instances. The serverless architecture simplifies database management and is particularly suitable for short-term workloads with considerable fluctuations.'); INSERT INTO documents(topic, content) VALUES ('Trends of Databases', 'In the future, the database industry will move towards greater efficiency, intelligence, and security. On one hand, as the volume of data increases dramatically, automation and intelligence will become key to database technologies. The integration of AI and ML will enable databases to autonomously learn and optimize performance. For example, they will be able to predict query loads and automatically adjust indexes to improve efficiency. Automated database management will also significantly reduce the workload of developers and O&M personnel, enhancing enterprise productivity. Cloud databases and the serverless architecture will be more widely applied. As more enterprises adopt hybrid cloud and multi-cloud architectures, they will consider the elasticity, scalability, and cost-effectiveness of cloud databases when making choices. Cloud databases not only support efficient data storage but also provide enterprises with high availability, fault tolerance, and disaster recovery capabilities. In addition, as data privacy protection and compliance requirements continue to increase, database vendors will continuously enhance functions such as data encryption, access control, and auditing. The blockchain technology is also expected in the database field, improving data security and transparency through decentralization, especially in scenarios involving sensitive data and financial data. The rise of edge computing will influence the development of database technologies. In the future, databases will not only run in data centers but also on network edge devices, supporting real-time data processing and low latency. Especially in IoT and smart devices, databases will provide enhanced edge computing.'); INSERT INTO documents(topic, content) VALUES ('Database Revenue','In recent years, the overall revenue of the database market has been growing steadily. According to IDC and Gartner, the overall scale of the global database market reached nearly $50 billion USD in 2022. With digital transformation, enterprises' requirements for databases are increasing. It is estimated that the market will continue to expand in the next few years, especially owing to the popularity of cloud databases. Many large cloud service providers have seen an increasing market share of their cloud database products, which have become an important source of revenue growth. As an important branch of cloud computing, Database as a Service (DBaaS) is becoming one of the most promising markets. DBaaS provides plug-and-play database services for enterprises, reducing their investment in hardware, software, and O&M. DBaaS has become the main revenue source of some database vendors (such as MongoDB and Snowflake). Traditional database vendors, such as Oracle and Microsoft SQL Server, still take the lead in the global market, but they face a slow growth rate. To cope with the challenges of cloud databases, these traditional vendors are moving their database products to the cloud to provide hybrid cloud databases and cloud database services. In addition, as more enterprises migrate data storage and computing to the cloud, NoSQL and NewSQL databases provide new growth momentum for the market. With the increase of big data and AI application scenarios, demands for these databases are also increasing, further driving the revenue growth of the database industry.');

2. Coarsely filter related documents. Use the rank function to roughly match the topics in the documents table with the user's question, and return the five long text corpora that are most relevant to the question. Then, use the chunk_text_recursively function to divide the text into chunks and store them in the chunk_text table.

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

   WITH topics AS ( SELECT array_agg(topic) AS topics_array FROM documents ), rank_result AS ( SELECT ai.rank('Generate a survey report on the development of the database industry from 2020 to 2025, focusing on the market demand, popular technologies, and future trends.', topics_array) AS result FROM topics ), rank_score AS ( SELECT (jsonb_each_text(result)).key AS content, (jsonb_each_text(result)).value AS score FROM rank_result ), related_documents AS ( SELECT content, score FROM rank_score ORDER BY score DESC LIMIT 5 ) INSERT INTO chunk_text (chunk) SELECT unnest(ai.chunk_text_recursively(rd.content, 300)) AS chunk FROM related_documents rd;

3. Vectorize the text chunks. Convert each chunk into a high-dimensional vector using the embedding model and store it persistently in DWS.

   1 2	UPDATE chunk_text SET embedding = ai.openai_embed(chunk)::VECTOR;

Step 4: Generate a Survey Report

1. Search for similar chunks and generate a survey report.

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34

   WITH query_embedding AS ( SELECT ai.openai_embed('Generate a survey report on the development of the database industry from 2020 to 2025, focusing on the market demand, popular technologies, and future trends.')::VECTOR AS embedding ), similarity_chunks AS ( SELECT ct.id, ct.chunk, (ct.embedding <=> qe.embedding) AS similarity FROM chunk_text ct, query_embedding qe ORDER BY similarity LIMIT 10 ), chunks AS ( SELECT string_agg(chunk, ' ') AS concatenated_chunks FROM similarity_chunks ), report AS ( SELECT ai.openai_chat_complete( jsonb_build_array( jsonb_build_object( 'role', 'system', 'content', 'Generate a survey report based on my question and the text content.' ), jsonb_build_object( 'role', 'user', 'content', 'Generate a survey report on the development of the database industry from 2020 to 2025, focusing on the market demand, popular technologies, and future trends. ' || concatenated_chunks ) ) ) AS report_content FROM chunks ) INSERT INTO reports (content) SELECT report_content FROM report;

2. View the generated result. In this example, the report in Markdown format is stored in the reports table as plain text. You can directly export the report and save it as an .md file.

   1	SELECT * FROM reports;