--- title: "Building a Graph RAG Application with TiDB" version: "v2" status: "DEPRECATED" description: "TiDB Vector Search allows you to search on the meanings of the data instead of the literal value of the data. It also allows you to build applications powered by generative AI, or implement semantic search or similarity search for texts, images, videos, audios or any type of data. Vector Search uses distances in the embedding space to represent similarity. After storing the vector embeddings in the TiDB, to find related data, you simply search for nearest neighbors of vector embeddings. As a relational database with Vector Search feature, TiDB also allows you to store the data and their vector embeddings altogether, in one database. You can store them in the same table, or separate into different tables to build a knowledge graph and later retrieve them using Vector Search queries and JOINs. " --- # Building a Graph RAG Application with TiDB ## Overview TiDB Vector Search allows you to search on the meanings of the data instead of the literal value of the data. It also allows you to build applications powered by generative AI, or implement semantic search or similarity search for texts, images, videos, audios or any type of data. Vector Search uses distances in the embedding space to represent similarity. After storing the vector embeddings in the TiDB, to find related data, you simply search for nearest neighbors of vector embeddings. As a relational database with Vector Search feature, TiDB also allows you to store the data and their vector embeddings altogether, in one database. You can store them in the same table, or separate into different tables to build a knowledge graph and later retrieve them using Vector Search queries and JOINs. ![52%](https://lab-static.pingcap.com/quick-demo/TiDBAsVectorStoreForRAG_aa_001_20240610.png) ### Duration + This lab should take you approximately 90 minutes to complete. ### Tasks + Connect to a TiDB Cloud Serverless cluster and work with vector data from MySQL Client. + Use vector functions in TiDB Cloud Serverless. + Create and use HNSW (Hierarchical Navigable Small World) indexes for fast approximate nearest neighbor searches. + Leverage TiDB as a vector store to empower the creation of AI applications, enabling them to respond to inquiries extending beyond the boundaries of LLM's knowledge base. These applications, are also known as Retrieval Augmented Generation (RAG) applications. + Use a Knowledge Graph approach to enhance your RAG application. ### Prerequisites + You must bring your own OpenAI API key for this hands-on lab. > [!TIP] > Click **Start** to launch the lab environment. > Once the environment is ready, select **Next** on the bottom toolbar to advance to **Module 1**. > And, follow the step-by-step lab guide to connect to the hands-on environment and begin your tasks. ## Module 1: Introduction To TiDB Vector Data Type And Vector Functions ![48%](https://lab-static.pingcap.com/quick-demo/TiDBVectors_aa_001_20240610.png) ### Step 1. Log into a Linux machine + Click here - [Terminal 1](ssh://monitor1-ssh#1) to open a new terminal connected to the virtual machine assigned for your hands-on lab. + The banner after you logged in: ```output Last login: Mon Jun 3 01:38:55 2024 from ... [rocky@ip-10-90-1-234 ~]$ ``` ### Step 2. Connect to TiDB Cloud Serverless database + The following operations are performed on a remote Linux. Connect to TiDB Cloud Serverless using the following command. (When you want to exit the MySQL client, use `EXIT`.) ```bash mysql \ --comments -u 'serverless_username' -p'serverless_password' \ -h serverless_host -P serverless_port \ --ssl-mode=VERIFY_IDENTITY --ssl-ca=/etc/pki/tls/certs/ca-bundle.crt ``` ```output mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2168969 Server version: 5.7.28-TiDB-Serverless TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible Copyright (c) 2000, 2024, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> ``` ### Step 3. Create and query vectors + Create a new schema and switch into the new schema. ```sql DROP DATABASE IF EXISTS vs_example; CREATE DATABASE vs_example; USE vs_example; ``` ```output mysql> DROP DATABASE IF EXISTS vs_example; Query OK, 0 rows affected (0.00 sec) mysql> CREATE DATABASE vs_example; Query OK, 0 rows affected (0.12 sec) mysql> USE vs_example; Database changed ``` + Create a table with a 3-dimensions vector column by using `VECTOR(3)` and insert 3 rows to it. ```sql CREATE TABLE vector_table ( id BIGINT AUTO_RANDOM PRIMARY KEY, doc TEXT, embedding VECTOR(3)); ``` ```output Query OK, 0 rows affected, 1 warning (0.13 sec) ``` ```sql INSERT INTO vector_table (doc, embedding) VALUES ('apple', '[1,1,1]'), ('banana', '[1,1,2]'), ('cat', '[2, 2, 3]'); ``` ```output Query OK, 3 rows affected (0.08 sec) Records: 3 Duplicates: 0 Warnings: 0 ``` ```sql SELECT doc, embedding FROM vector_table ORDER BY embedding; ``` ```output +--------+-----------+ | doc | embedding | +--------+-----------+ | apple | [1,1,1] | | banana | [1,1,2] | | cat | [2,2,3] | +--------+-----------+ 3 rows in set (0.00 sec) ``` ### Step 4. Use functions on vectors + Use supported vector functions on the vector data type. + Get the vector cosine distances between the vector fields and a particular vector `[3,2,1]`. ```sql SELECT doc, embedding, VEC_COSINE_DISTANCE(embedding, '[3,2,1]') AS distance_to_321 FROM vector_table ORDER BY distance_to_321; ``` ```output +--------+-----------+---------------------+ | doc | embedding | distance_to_321 | +--------+-----------+---------------------+ | apple | [1,1,1] | 0.07417990022744858 | | cat | [2,2,3] | 0.15733515938218623 | | banana | [1,1,2] | 0.23623738417402662 | +--------+-----------+---------------------+ 3 rows in set (0.01 sec) ``` + Test Cosine distance, L1 distance (Manhattan Distance), L2 distance (Squared Euclidean), and negative inner product on the vector column against `[3,2,1]`. ```sql SELECT embedding, VEC_COSINE_DISTANCE(embedding, '[3,2,1]') AS cos_to_321, VEC_L1_DISTANCE(embedding, '[3,2,1]') AS l1_to_321, VEC_L2_DISTANCE(embedding, '[3,2,1]') AS l2_to_321, VEC_NEGATIVE_INNER_PRODUCT(embedding, '[3,2,1]') AS nip_to_321 FROM vector_table; ``` ```output +-----------+---------------------+-----------+-------------------+------------+ | embedding | cos_to_321 | l1_to_321 | l2_to_321 | nip_to_321 | +-----------+---------------------+-----------+-------------------+------------+ | [1,1,1] | 0.07417990022744858 | 3 | 2.23606797749979 | -6 | | [1,1,2] | 0.23623738417402662 | 4 | 2.449489742783178 | -7 | | [2,2,3] | 0.15733515938218623 | 3 | 2.23606797749979 | -13 | +-----------+---------------------+-----------+-------------------+------------+ 3 rows in set (0.01 sec) ``` + Other supported vector functions, such as number of dimensions, conversion between vector and text, and Euclidean norm. ```sql SELECT embedding, VEC_DIMS(embedding) AS "#_of_dim", VEC_AS_TEXT(embedding) AS to_text, VEC_FROM_TEXT(VEC_AS_TEXT(embedding)) AS embedding_clone, VEC_L2_NORM(embedding) AS l2_norm FROM vector_table ORDER BY embedding; ``` ```output +-----------+----------+---------+-----------------+--------------------+ | embedding | #_of_dim | to_text | embedding_clone | l2_norm | +-----------+----------+---------+-----------------+--------------------+ | [1,1,1] | 3 | [1,1,1] | [1,1,1] | 1.7320508075688772 | | [1,1,2] | 3 | [1,1,2] | [1,1,2] | 2.449489742783178 | | [2,2,3] | 3 | [2,2,3] | [2,2,3] | 4.123105625617661 | +-----------+----------+---------+-----------------+--------------------+ 3 rows in set (0.00 sec) ``` + Exit to Linux OS prompt. ```sql EXIT ``` ```output Bye ``` ### Module 1 Summary + In this module, there are only three records in total. If there are many vector records that need to be sorted or searched, how can efficiency be ensured? The next module introduces the HNSW index, which can solve this problem. + You practiced creating a TiDB table with the vector data type, and successfully inserted records with vector data. + Following vector functions are supported in TiDB: `VEC_L1_DISTANCE`, `VEC_L2_DISTANCE`, `VEC_COSINE_DISTANCE`, `VEC_NEGATIVE_INNER_PRODUCT`, `VEC_DIMS`, `VEC_AS_TEXT`, `VEC_FROM_TEXT`, `VEC_L2_NORM`, etc. ## Module 2: Using HNSW Indexes To Improve The Performance For K-nearest Neighbor Query ![54%](https://lab-static.pingcap.com/quick-demo/TiDBVectorSimilaritySearch_aa_001_20240610.png) ### References + HNSW (Hierarchical Navigable Small World) index is a type of approximate nearest neighbor search algorithm. + HNSW index organizes high-dimensional data points in a graph structure where each node represents a data point, and edges connect nodes that are close to each other in the high-dimensional space. + HNSW index efficiently approximates nearest neighbor queries by performing a hierarchical search through this graph, allowing for fast retrieval of nearest neighbors even in high-dimensional spaces. It's commonly used in applications such as recommendation systems, similarity search, and information retrieval. ### Step 1. Connect to TiDB Cloud Serverless database + The following operations are performed on a remote Linux. Connect to TiDB Cloud Serverless using the following command. (When you want to exit the MySQL client, use `EXIT`.) ```bash mysql \ --comments -u 'serverless_username' -p'serverless_password' \ -h serverless_host -P serverless_port \ -D vs_example \ --ssl-mode=VERIFY_IDENTITY --ssl-ca=/etc/pki/tls/certs/ca-bundle.crt ``` ```output mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2168969 Server version: 5.7.28-TiDB-Serverless TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible Copyright (c) 2000, 2024, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> ``` ### Step 2. Create a test table with vector data type + Create and populate data (32768 rows) into an example table `vector_table_noidx`. ```sql USE vs_example; DROP SEQUENCE IF EXISTS vs_seq1; CREATE SEQUENCE vs_seq1; DROP TABLE IF EXISTS vector_table_noidx; CREATE TABLE vector_table_noidx ( id BIGINT AUTO_RANDOM PRIMARY KEY, doc TEXT, embedding VECTOR(3)); INSERT INTO vector_table_noidx (doc, embedding) VALUES ('dog', '[-1, -2, -1]'); INSERT INTO vector_table_noidx (doc, embedding) SELECT NEXTVAL(vs_seq1), VEC_FROM_TEXT(CONCAT('[',CONVERT(RAND(), CHAR),',',CONVERT(RAND(), CHAR),',',CONVERT(RAND(), CHAR),']')) FROM vector_table_noidx; INSERT INTO vector_table_noidx (doc, embedding) SELECT NEXTVAL(vs_seq1), VEC_FROM_TEXT(CONCAT('[',CONVERT(RAND(), CHAR),',',CONVERT(RAND(), CHAR),',',CONVERT(RAND(), CHAR),']')) FROM vector_table_noidx; INSERT INTO vector_table_noidx (doc, embedding) SELECT NEXTVAL(vs_seq1), VEC_FROM_TEXT(CONCAT('[',CONVERT(RAND(), CHAR),',',CONVERT(RAND(), CHAR),',',CONVERT(RAND(), CHAR),']')) FROM vector_table_noidx; INSERT INTO vector_table_noidx (doc, embedding) SELECT NEXTVAL(vs_seq1), VEC_FROM_TEXT(CONCAT('[',CONVERT(RAND(), CHAR),',',CONVERT(RAND(), CHAR),',',CONVERT(RAND(), CHAR),']')) FROM vector_table_noidx; INSERT INTO vector_table_noidx (doc, embedding) SELECT NEXTVAL(vs_seq1), VEC_FROM_TEXT(CONCAT('[',CONVERT(RAND(), CHAR),',',CONVERT(RAND(), CHAR),',',CONVERT(RAND(), CHAR),']')) FROM vector_table_noidx; INSERT INTO vector_table_noidx (doc, embedding) SELECT NEXTVAL(vs_seq1), VEC_FROM_TEXT(CONCAT('[',CONVERT(RAND(), CHAR),',',CONVERT(RAND(), CHAR),',',CONVERT(RAND(), CHAR),']')) FROM vector_table_noidx; INSERT INTO vector_table_noidx (doc, embedding) SELECT NEXTVAL(vs_seq1), VEC_FROM_TEXT(CONCAT('[',CONVERT(RAND(), CHAR),',',CONVERT(RAND(), CHAR),',',CONVERT(RAND(), CHAR),']')) FROM vector_table_noidx; INSERT INTO vector_table_noidx (doc, embedding) SELECT NEXTVAL(vs_seq1), VEC_FROM_TEXT(CONCAT('[',CONVERT(RAND(), CHAR),',',CONVERT(RAND(), CHAR),',',CONVERT(RAND(), CHAR),']')) FROM vector_table_noidx; INSERT INTO vector_table_noidx (doc, embedding) SELECT NEXTVAL(vs_seq1), VEC_FROM_TEXT(CONCAT('[',CONVERT(RAND(), CHAR),',',CONVERT(RAND(), CHAR),',',CONVERT(RAND(), CHAR),']')) FROM vector_table_noidx; INSERT INTO vector_table_noidx (doc, embedding) SELECT NEXTVAL(vs_seq1), VEC_FROM_TEXT(CONCAT('[',CONVERT(RAND(), CHAR),',',CONVERT(RAND(), CHAR),',',CONVERT(RAND(), CHAR),']')) FROM vector_table_noidx; INSERT INTO vector_table_noidx (doc, embedding) SELECT NEXTVAL(vs_seq1), VEC_FROM_TEXT(CONCAT('[',CONVERT(RAND(), CHAR),',',CONVERT(RAND(), CHAR),',',CONVERT(RAND(), CHAR),']')) FROM vector_table_noidx; INSERT INTO vector_table_noidx (doc, embedding) SELECT NEXTVAL(vs_seq1), VEC_FROM_TEXT(CONCAT('[',CONVERT(RAND(), CHAR),',',CONVERT(RAND(), CHAR),',',CONVERT(RAND(), CHAR),']')) FROM vector_table_noidx; INSERT INTO vector_table_noidx (doc, embedding) SELECT NEXTVAL(vs_seq1), VEC_FROM_TEXT(CONCAT('[',CONVERT(RAND(), CHAR),',',CONVERT(RAND(), CHAR),',',CONVERT(RAND(), CHAR),']')) FROM vector_table_noidx; INSERT INTO vector_table_noidx (doc, embedding) SELECT NEXTVAL(vs_seq1), VEC_FROM_TEXT(CONCAT('[',CONVERT(RAND(), CHAR),',',CONVERT(RAND(), CHAR),',',CONVERT(RAND(), CHAR),']')) FROM vector_table_noidx; INSERT INTO vector_table_noidx (doc, embedding) SELECT NEXTVAL(vs_seq1), VEC_FROM_TEXT(CONCAT('[',CONVERT(RAND(), CHAR),',',CONVERT(RAND(), CHAR),',',CONVERT(RAND(), CHAR),']')) FROM vector_table_noidx; ANALYZE TABLE vector_table_noidx; ``` ```output ... Query OK, 16384 rows affected (0.06 sec) Records: 16384 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.10 sec) ``` ### Step 3. Run a K-Nearest neighbor search and examine the execution plan + Review the following query. It shows a typical K-nearest neighbor search by using the cosine distance. + The algorithm seeks the nearest node to a reference node `[-1, -1, -1]` within the dataset - table `vector_table_noidx`. ```sql SELECT doc, embedding FROM vector_table_noidx ORDER BY VEC_COSINE_DISTANCE(embedding, '[-1, -1, -1]') LIMIT 1; ``` ```output +------+------------+ | doc | embedding | +------+------------+ | dog | [-1,-2,-1] | +------+------------+ 1 row in set (0.03 sec) ``` + Review the execution plan to verify that a `FullTableScan` is used in the query. Considering the table `vector_table_noidx` comprises only 32768 rows, the response time is notably swift, it's crucial to consider the potential performance ramifications, especially when dealing with billions of rows where the impact is substantial and cannot be overlooked. Take note of the operation at the bottom of the explain output, where the estimated number of rows (`estRows`) is a number much larger than `1.00`. ```sql EXPLAIN SELECT doc, embedding FROM vector_table_noidx ORDER BY VEC_COSINE_DISTANCE(embedding, '[-1, -1, -1]') LIMIT 1; ``` ```output +--------------------------------+----------+-----------+--------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +--------------------------------+----------+-----------+--------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Projection_13 | 1.00 | root | | vs_example.vector_table_noidx.doc, vs_example.vector_table_noidx.embedding | | └─TopN_7 | 1.00 | root | | Column#4, offset:0, count:1 | | └─Projection_14 | 1.00 | root | | vs_example.vector_table_noidx.doc, vs_example.vector_table_noidx.embedding, vec_cosine_distance(vs_example.vector_table_noidx.embedding, [3,2,1])->Column#4 | | └─TableReader_12 | 1.00 | root | | data:TopN_11 | | └─TopN_11 | 1.00 | cop[tikv] | | vec_cosine_distance(vs_example.vector_table_noidx.embedding, [3,2,1]), offset:0, count:1 | | └─TableFullScan_10 | 10000.00 | cop[tikv] | table:vector_table_noidx | keep order:false, stats:pseudo | +--------------------------------+----------+-----------+--------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+ 6 rows in set, 1 warning (0.01 sec) ``` ### Step 4. Create a table with HNSW index on the vector data type + Create a new table `vector_table_with_index_cos` with an HNSW index defined on column `embedding`, and populate it with the same data from table `vector_table_noidx`. The HNSW index provides a good performance with approximated accuracy (greater than 98% in most cases). + Create a table with `cosine distance` HNSW index. ```sql DROP TABLE IF EXISTS vector_table_with_index_cos; CREATE TABLE vector_table_with_index_cos ( id BIGINT AUTO_RANDOM PRIMARY KEY, doc TEXT, embedding VECTOR(3), VECTOR INDEX embedding_idx ((VEC_COSINE_DISTANCE(embedding))) ); INSERT INTO vector_table_with_index_cos (doc, embedding) SELECT doc, embedding FROM vector_table_noidx; ANALYZE TABLE vector_table_with_index_cos; ``` ```output ... Query OK, 32768 rows affected (0.03 sec) Records: 32768 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.08 sec) ``` + Since HNSW indexes rely on TiFlash replicas, check if the TiFlash replicas are in place. Note that the replicas are in place when you see the same output as shown, that is, when `AVAILABLE` and `PROGRESS` are both 1. If they are less than 1, you can execute the following command again to observe the progress. The replicas will be available very quickly. ```sql SELECT * FROM information_schema.tiflash_replica WHERE TABLE_SCHEMA = 'vs_example'; ``` ```output +--------------+-----------------------------+----------+---------------+-----------------+-----------+----------+ | TABLE_SCHEMA | TABLE_NAME | TABLE_ID | REPLICA_COUNT | LOCATION_LABELS | AVAILABLE | PROGRESS | +--------------+-----------------------------+----------+---------------+-----------------+-----------+----------+ | vs_example | vector_table_with_index_cos | 183 | 2 | | 1 | 1 | +--------------+-----------------------------+----------+---------------+-----------------+-----------+----------+ ``` ### Step 5. Run the K-Nearest neighbor query against the indexed table + Run the same k-nearest neighbor search against table `vector_table_with_index_cos`. ```sql SELECT doc, embedding FROM vector_table_with_index_cos ORDER BY VEC_COSINE_DISTANCE(embedding, '[-1, -1, -1]') LIMIT 1; ``` ```output +------+------------+ | doc | embedding | +------+------------+ | dog | [-1,-2,-1] | +------+------------+ 1 row in set (0.03 sec) ``` ### Step 6. Review the execution plan + Review the execution plan to verify that an approximate nearest neighbor index (`index:embedding_idx(embedding)` object under `access object` column and `annIndex:COSINE` operator under `operator info` column in the output) is used in the query. In a production environment operating at scale, the table `vector_table_with_index_cos` is expected to outperform `vector_table_noidx` when serving this query. Take note of the operation at the bottom of the explain output, where the estimated number of rows (`estRows`) is ONLY `1.00`, which is significantly more efficient than Step 2. In addition to the index optimization, you can also find the `mpp[tiflash]` tasks under `task` column in the output, it's another columnar storage query optimization implemented by TiDB. The HNSW index is built asynchronously. In our lab, you may have to wait for up to 1 minute before the query optimizer choose to use index for your query. ```sql EXPLAIN SELECT doc, embedding FROM vector_table_with_index_cos ORDER BY VEC_COSINE_DISTANCE(embedding, '[-1, -1, -1]') LIMIT 1; ``` ```output +--------------------------------------+---------+--------------+-------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +--------------------------------------+---------+--------------+-------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Projection_28 | 1.00 | root | | vs_example.vector_table_with_index_cos.doc, vs_example.vector_table_with_index_cos.embedding | | └─TopN_10 | 1.00 | root | | Column#5, offset:0, count:1 | | └─Projection_29 | 1.00 | root | | vs_example.vector_table_with_index_cos.doc, vs_example.vector_table_with_index_cos.embedding, vec_cosine_distance(vs_example.vector_table_with_index_cos.embedding, [-1,-1,-1])->Column#5 | | └─TableReader_25 | 1.00 | root | | MppVersion: 1, data:ExchangeSender_24 | | └─ExchangeSender_24 | 1.00 | mpp[tiflash] | | ExchangeType: PassThrough | | └─Projection_26 | 1.00 | mpp[tiflash] | | vs_example.vector_table_with_index_cos.doc, vs_example.vector_table_with_index_cos.embedding | | └─TopN_23 | 1.00 | mpp[tiflash] | | Column#4, offset:0, count:1 | | └─Projection_27 | 1.00 | mpp[tiflash] | | vs_example.vector_table_with_index_cos.doc, vs_example.vector_table_with_index_cos.embedding, vec_cosine_distance(vs_example.vector_table_with_index_cos.embedding, [-1,-1,-1])->Column#4 | | └─TableFullScan_22 | 1.00 | mpp[tiflash] | table:vector_table_with_index_cos, index:embedding_idx(embedding) | keep order:false, stats:pseudo, annIndex:COSINE(embedding..[-1,-1,-1], limit:1) | +--------------------------------------+---------+--------------+-------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 9 rows in set (0.01 sec) ``` ### Step 7. Try L2 distance index + In addition to `cosine distance`, `L2 distance` index is also supported. + Due to the search algorithm changes to `L2 distance` this time, review the execution plan to verify that an approximate nearest neighbor index (`annIndex`) is NOT used in the query against table `vector_table_with_index_cos`, a `TableFullScan` is used instead. ```sql EXPLAIN SELECT doc, embedding FROM vector_table_with_index_cos ORDER BY VEC_L2_DISTANCE(embedding, '[-1, -1, -1]') LIMIT 1; ``` ```output +--------------------------------------+----------+--------------+-----------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +--------------------------------------+----------+--------------+-----------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Projection_28 | 1.00 | root | | vs_example.vector_table_with_index_cos.doc, vs_example.vector_table_with_index_cos.embedding | | └─TopN_9 | 1.00 | root | | Column#5, offset:0, count:1 | | └─Projection_29 | 1.00 | root | | vs_example.vector_table_with_index_cos.doc, vs_example.vector_table_with_index_cos.embedding, vec_l2_distance(vs_example.vector_table_with_index_cos.embedding, [-1,-1,-1])->Column#5 | | └─TableReader_20 | 1.00 | root | | MppVersion: 1, data:ExchangeSender_19 | | └─ExchangeSender_19 | 1.00 | mpp[tiflash] | | ExchangeType: PassThrough | | └─Projection_26 | 1.00 | mpp[tiflash] | | vs_example.vector_table_with_index_cos.doc, vs_example.vector_table_with_index_cos.embedding | | └─TopN_18 | 1.00 | mpp[tiflash] | | Column#4, offset:0, count:1 | | └─Projection_27 | 10000.00 | mpp[tiflash] | | vs_example.vector_table_with_index_cos.doc, vs_example.vector_table_with_index_cos.embedding, vec_l2_distance(vs_example.vector_table_with_index_cos.embedding, [-1,-1,-1])->Column#4 | | └─TableFullScan_16 | 10000.00 | mpp[tiflash] | table:vector_table_with_index_cos | keep order:false, stats:pseudo | +--------------------------------------+----------+--------------+-----------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 9 rows in set (0.88 sec) ``` + Create a table with `L2 distance` HNSW index and populate it with the same data from table `vector_table_with_index_cos`. ```sql DROP TABLE IF EXISTS vector_table_with_index_l2; CREATE TABLE vector_table_with_index_l2 ( id BIGINT AUTO_RANDOM PRIMARY KEY, doc TEXT, embedding VECTOR(3), VECTOR INDEX embedding_idx ((VEC_L2_DISTANCE(embedding))) ); INSERT INTO vector_table_with_index_l2 (doc, embedding) SELECT doc, embedding FROM vector_table_with_index_cos; ANALYZE TABLE vector_table_with_index_l2; ``` ```output Query OK, 32768 rows affected (0.62 sec) Records: 32768 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.09 sec) ``` + Since HNSW indexes rely on TiFlash replicas, check if the TiFlash replica is in place. This time, pay attention to `AVAILABLE` and `PROGRESS` for table `vector_table_with_index_l2`. ```sql SELECT * FROM information_schema.tiflash_replica WHERE TABLE_SCHEMA = 'vs_example'; ``` ```output +--------------+-----------------------------+----------+---------------+-----------------+-----------+----------+ | TABLE_SCHEMA | TABLE_NAME | TABLE_ID | REPLICA_COUNT | LOCATION_LABELS | AVAILABLE | PROGRESS | +--------------+-----------------------------+----------+---------------+-----------------+-----------+----------+ | vs_example | vector_table_with_index_cos | 183 | 2 | | 1 | 1 | | vs_example | vector_table_with_index_l2 | 186 | 2 | | 1 | 1 | +--------------+-----------------------------+----------+---------------+-----------------+-----------+----------+ 2 rows in set (0.07 sec) ``` + Execute the query against table `vector_table_with_index_l2` and check the execution plan to confirm that the operator at the bottom of the explain output table now utilizes an approximate nearest neighbor index (`index:embedding_idx(embedding)`) for L2 distance. ```sql SELECT doc, embedding FROM vector_table_with_index_l2 ORDER BY VEC_L2_DISTANCE(embedding, '[-1, -1, -1]') LIMIT 1; ``` ```output +------+------------+ | doc | embedding | +------+------------+ | dog | [-1,-2,-1] | +------+------------+ 1 row in set (0.10 sec) ``` ```bash EXPLAIN SELECT doc, embedding FROM vector_table_with_index_l2 ORDER BY VEC_L2_DISTANCE(embedding, '[-1, -1, -1]') LIMIT 1; ``` ```output +--------------------------------------+---------+--------------+------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +--------------------------------------+---------+--------------+------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Projection_28 | 1.00 | root | | vs_example.vector_table_with_index_l2.doc, vs_example.vector_table_with_index_l2.embedding | | └─TopN_10 | 1.00 | root | | Column#5, offset:0, count:1 | | └─Projection_29 | 1.00 | root | | vs_example.vector_table_with_index_l2.doc, vs_example.vector_table_with_index_l2.embedding, vec_l2_distance(vs_example.vector_table_with_index_l2.embedding, [-1,-1,-1])->Column#5 | | └─TableReader_25 | 1.00 | root | | MppVersion: 1, data:ExchangeSender_24 | | └─ExchangeSender_24 | 1.00 | mpp[tiflash] | | ExchangeType: PassThrough | | └─Projection_26 | 1.00 | mpp[tiflash] | | vs_example.vector_table_with_index_l2.doc, vs_example.vector_table_with_index_l2.embedding | | └─TopN_23 | 1.00 | mpp[tiflash] | | Column#4, offset:0, count:1 | | └─Projection_27 | 1.00 | mpp[tiflash] | | vs_example.vector_table_with_index_l2.doc, vs_example.vector_table_with_index_l2.embedding, vec_l2_distance(vs_example.vector_table_with_index_l2.embedding, [-1,-1,-1])->Column#4 | | └─TableFullScan_22 | 1.00 | mpp[tiflash] | table:vector_table_with_index_l2, index:embedding_idx(embedding) | keep order:false, stats:pseudo, annIndex:L2(embedding..[-1,-1,-1], limit:1) | +--------------------------------------+---------+--------------+------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 9 rows in set (0.01 sec) ``` + Exit from the MySQL client, and return to the remote Linux OS prompt. ```sql EXIT ``` ```output Bye ``` ### Module 2 Summary + In this module, you practiced creating HNSW indexes on TiDB tables to ensure the K-nearest search performance on large data set. + Following HNSW are supported in TiDB: `Cosine Distance`, `Square Distance (L2)`. + Employing TiDB as the vector store provides immediate advantages, including: + Efficient approximates nearest neighbor searches. + Zero planned and unplanned downtime. (For TiDB's zero downtime ability, please go through the other hands-on labs to learn more.) + Unified storage for both relational and vector data, scalable to extreme large volumes. + For more information about the columnar storage TiFlash, please check the other hands-on lab - Getting Started with TiDB Cloud Serverless or enroll the self-paced e-learning [Introduction to TiDB](https://www.pingcap.com/education/course-introduction-to-tidb/). + In the next module, we will apply TiDB's vector search feature to solve a real-world problem. ## Module 3: Using TiDB To Support The Semantic Search For RAG Applications + With the concepts and skills mastered in the first two demos, you can now proceed to a practical example. Not like the 3-dimension vectors in previous demos, you will use 1536-dimension vectors this time for a RAG application. In this demo, the RAG application is backed by TiDB as the vector store and OpenAI provides the LLM. + In this module, an OpenAI API KEY is required. ![48%](https://lab-static.pingcap.com/quick-demo/TiDBAsVectorStoreForRAGDetails_aa_001_20240610.png) ### Tips + RAG serves as a prompt enrichment method, enhancing the ability of the Large Language Model (LLM) to generate user-friendly responses by incorporating additional context within the prompt. + The crucial step in enhancing the prompt with additional context lies in conducting a semantic search, which involves performing a K-nearest neighbor search in a vector store like TiDB. ### Step 1. Connect to TiDB Cloud Serverless database + Connect to the designated TiDB Cloud Serverless instance and confirm the schema `semantic_search_test` has been provisioned. ```bash mysql \ --comments -u 'serverless_username' -p'serverless_password' \ -h serverless_host -P serverless_port \ --ssl-mode=VERIFY_IDENTITY --ssl-ca=/etc/pki/tls/certs/ca-bundle.crt ``` ### Step 2. Review the existing schema + There is only one table `llama_index_rag_test` in the database. ```sql USE semantic_search_test; SHOW TABLES; ``` ```output +--------------------------------+ | Tables_in_semantic_search_test | +--------------------------------+ | llama_index_rag_test | +--------------------------------+ 1 row in set (0.00 sec) ``` + Verify the table schema. Note that column `embedding` is the vector embedding of column `document`, and there is a `Cosine Distance` index on column `embedding`. ```sql SHOW CREATE TABLE llama_index_rag_test\G ``` ```output *************************** 1. row *************************** Table: llama_index_rag_test Create Table: CREATE TABLE `llama_index_rag_test` ( `id` varchar(36) NOT NULL, `embedding` vector(1536) NOT NULL COMMENT 'hnsw(distance=cosine)', `document` text DEFAULT NULL, `meta` json DEFAULT NULL, `create_time` datetime DEFAULT CURRENT_TIMESTAMP, `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */ ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin 1 row in set (0.01 sec) ``` + Table `llama_index_rag_test` contains 99 documents crawled from `https://docs.pingcap.com` on June 2024, they cover following topics up to version TiDB version 8.1: TiDB Overview, TiDB Quick Start, TiDB HTAP Quick Start, Migrate to TiDB. Those records were created using the LlamaIndex framework and a custom-configured OpenAI API key. The PingCAP Training and Certification team handled this process and provisioned the data for you, but you'll have the capability to do it by yourself in subsequent steps, provided you have an OpenAI account. ```sql SELECT COUNT(DISTINCT document), COUNT(DISTINCT embedding) FROM llama_index_rag_test; ``` ```output +--------------------------+---------------------------+ | COUNT(DISTINCT document) | COUNT(DISTINCT embedding) | +--------------------------+---------------------------+ | 99 | 99 | +--------------------------+---------------------------+ 1 row in set (0.02 sec) ``` + Verify the average length (more than 3,000 bytes) of the `document` column and the number of dimensions (1536 dimensions for each embedding) of its embedded vector in the `embedding` column. ```sql SELECT AVG(LENGTH(document)) AS avg_document_length, MAX(VEC_DIMS(embedding)) AS max_no_of_dimensions, MIN(VEC_DIMS(embedding)) AS min_no_of_dimensions FROM llama_index_rag_test; ``` ```output +---------------------+----------------------+----------------------+ | avg_document_length | max_no_of_dimensions | min_no_of_dimensions | +---------------------+----------------------+----------------------+ | 3518.3333 | 1536 | 1536 | +---------------------+----------------------+----------------------+ 1 row in set (0.01 sec) ``` + Examine one of the records to gain some basic understanding. ```sql SELECT SUBSTR(document,1,57) AS CONTENT, ' ', embedding AS EMBEDDING FROM llama_index_rag_test ORDER BY create_time LIMIT 1\G ``` ```output *************************** 1. row *************************** CONTENT: Users can elastically scale TiDB to meet the requirements : EMBEDDING: [-0.0027229728,-0.016953643,0.023380065,-0.021632409,-0.009694677,0.020834267,-0.02741206,0.0009624152,-0.029558789,-0.036274195,-0.0010510021,0.023999315,0.0132725565,0.001702933,-0.004558356,0.017504087,0.0037292512,-0.003866862,-0.004056077,-0.022802101,-0.0114767365,0.0022619767,-0.0079951845,0.004035435,-0.021536082,0.008903416,0.047283053,-0.009667155,0.0038634217,-0.000537972,0.03498065,-0.02752215,-0.019237982,0.004045756,-0.010135031,-0.006708524,0.0098254075, . . . -0.0036398042,0.0026696485,0.023063561,-0.022760818,-0.006822053,-0.020820506,-0.047971107,-0.013912447,0.04571429,-0.0026042834,0.0021794103,-0.0511912,0.009082309,-0.026283653,-0.025636882,-0.019361833,-0.020297585,0.0021484478,-0.022636969,-0.024811218,0.011655631,0.00072159636,0.024687368,-0.004241851,-0.018976523,-0.014270235,0.014696828,0.0033301802,0.014724351,-0.015591298,-0.0116212275] 1 row in set (0.01 sec) ``` ### Step 3. Examine embedded vectors in RAG applications + If you possess an embedded vector (from a question about TiDB for example), you can perform a cosine similarity search by using k-nearest neighbor search similar to what we demonstrated in demo 2. The distinction lies in the dimensionality: in demo 2, we worked with three dimensions, whereas now we're working with 1536 dimensions. + For example, the following 1536-dimension vector is embedded from the question `How to migrate to TiDB from MySQL?` using OpenAI `text-embedding-3-small` model. Check this [OpenAI documentation](https://platform.openai.com/docs/guides/embeddings) for more information about how to perform the embedding. ```output [-0.0088212425,-0.02732351,0.008679073,-0.008949871,-0.0052805594,-0.043733865,-0.031520877,0.033985138,-0.037207633,-0.020973299,0.011414132,0.050287172,-0.014704327,-0.003625307,0.000286665,0.078910515,-0.0034797532,-0.0055479724,-0.008922791,0.052318156,0.0006689555,-0.012395775,0.0033528167,-0.015462561,0.0058323103,-0.04015933,-0.051262043,0.017371686,0.03133132,-0.040457208,0.028298382,-0.03625984,-0.013248788,-0.007196455,-0.028190063,0.0127952015,-0.012761352,0.034635052,-0.010574658,0.011373512,0.023843756,0.02859626,0.0006452607,0.017385226,0.051045407,-0.0156115,-0.040592607,-0.001942975,0.011265193,0.036503557,0.0024168715,-0.033091504,-0.0074063228,0.0336331,-0.017601864,-0.03601612,0.023721898,0.030708484,0.03466213,0.019957807,0.027594307,-0.059033945,0.031033441,0.030789724,-0.00779221,0.012653032,-0.061796084,0.001817731,0.032224953,0.017155048,-0.029300334,0.06222936,-0.053780466,0.010046602,0.039644815,0.038480382,-0.021812772,0.016234335,-0.0078396,-0.01170524,0.038480382,-0.008137478,-0.00388595,-0.037288874,0.042406954,-0.018157,-0.028894138,-0.041946597,0.010012753,-0.035528686,-0.019334972,-0.00009361569,0.0024862634,0.033335224,0.034337174,0.046143964,-0.0285421,0.004691574,-0.028271303,0.004918367,0.04909566,0.009328988,-0.008178097,0.011982808,-0.011244884,0.007392783,-0.008245797,-0.005581822,-0.03111468,0.007379243,0.0364494,-0.042027835,-0.008293186,0.024886329,-0.008211947,0.00781252,0.021474274,-0.0026335097,-0.05995466,-0.05451162,0.0012871363,-0.012801971,-0.00023144759,-0.049826816,-0.030356446,-0.030356446,-0.033362303,0.0016129401,-0.06509982,-0.03514957,0.037343033,0.0064517604,-0.011962498,-0.01167139,-0.020580642,-0.03260407,-0.0116104605,0.022164809,-0.045846086,0.072952956,-0.0010611893,0.020052586,0.038128346,-0.009809654,-0.0052128597,0.0077719,-0.00129983,-0.023830216,-0.0058932398,-0.0065194597,0.029056616,0.011556301,-0.054619938,-0.022340829,-0.012463474,-0.026822533,0.03203539,-0.00027756786,-0.009538856,-0.0009858736,-0.010669438,-0.014826186,-0.040348887,0.011089175,-0.039482336,0.037153475,-0.010703288,-0.026389256,-0.07279048,-0.01165785,-0.011881258,0.022963664,-0.05451162,-0.05605517,-0.025563324,-0.04259651,-0.034580894,0.004691574,-0.045575287,0.044762895,0.033281066,0.08595126,-0.035799485,-0.013553436,-0.014162731,0.033497702,0.0004028119,0.0026216623,-0.021799233,-0.033443544,0.029571133,0.061687764,0.003190338,0.028731659,0.022543926,-0.03639524,0.0035711476,0.009708106,-0.018617356,0.020905599,0.012625953,0.04024057,0.012544713,-0.021636754,-0.08719693,-0.008123937,-0.044979535,0.014582467,-0.009606556,-0.051641162,0.01553026,-0.0015477793,-0.0067902575,0.008245797,0.0070881355,0.010554349,0.01696549,0.05207444,-0.019971346,0.012686882,-0.0027689086,0.020716041,0.051099565,0.010581428,0.03777631,0.024046855,0.022801185,-0.04015933,0.026632976,-0.04403174,0.032252032,0.042081997,0.0023762516,-0.0014640013,-0.0008610527,0.06910763,0.00002308658,0.008083317,-0.0050469963,-0.0049149822,-0.050151773,-0.025265446,0.0018532733,-0.00008182117,0.024994647,-0.001661176,0.013336797,0.02986901,0.028894138,0.051207885,-0.0026436646,0.012145286,-0.0073047737,-0.023180302,-0.01704673,-0.043788023,-0.03739719,0.018238239,0.03544745,0.0061606527,-0.010425719,0.003875795,-0.019714087,-0.015435481,-0.043625545,0.013858084,0.035664085,0.03804711,0.04397758,0.029489893,0.023627117,0.039780214,0.002853533,0.018698595,-0.021826312,0.010994395,0.015340702,0.052561875,-0.07409031,0.0731696,-0.07604005,-0.053617988,0.00033595867,0.003170028,-0.0058018453,-0.013289408,0.005822155,-0.0045595597,-0.013478966,-0.01689779,0.013932553,0.03254991,0.021420116,0.001810961,-0.015191763,0.0068274923,0.0025895052,-0.047633354,0.050937086,0.006143728,0.063041754,0.055432335,0.026199698,-0.00583908,-0.012131747,0.0233157,0.01677593,-0.02479155,-0.0035948423,0.012504093,-0.015449021,-0.02079728,-0.0078396,-0.0036523868,-0.028135903,-0.038074188,0.0012236681,0.035474528,0.050178852,0.008076548,0.017222747,-0.042109076,-0.011116254,-0.011251654,-0.0129306,0.049041502,0.06645381,-0.005273789,-0.019077713,0.008123937,0.018820455,-0.051126648,0.0010730367,0.034418415,0.010127842,-0.04698344,0.003434056,-0.009315448,0.03774923,0.008550444,-0.023627117,-0.04925814,-0.057246678,-0.008022388,-0.007128755,-0.06407078,0.014176271,0.021068078,-0.014081491,0.020336924,-0.017764343,0.009863814,-0.00906496,0.025549782,-0.011353202,-0.0003590188,0.009403458,-0.018468417,-0.015746899,0.042136155,-0.03766799,-0.0015740129,0.0024388738,-0.031547956,0.0030312443,-0.0034865232,0.008855091,-0.03514957,-0.024926947,0.04010517,0.015706278,0.006350211,0.023613578,0.007413093,-0.028894138,-0.011217804,-0.048743624,0.021745073,0.060387935,-0.023613578,-0.036232762,-0.0008081625,-0.022543926,0.016450973,0.01821116,-0.03904906,0.021406576,0.04135084,0.009599786,-0.07214057,0.06878267,0.0011195801,0.017886203,-0.010459569,-0.072628,0.020905599,0.025603943,0.046441842,0.015394862,-0.0044444706,0.056705084,-0.013289408,-0.041946597,-0.044708736,-0.009579476,0.012517634,0.022774104,0.016586373,0.03864286,-0.012504093,0.0018498882,0.002464261,0.03238743,0.05459286,0.013397727,0.015977077,0.037586752,-0.011468291,-0.02062126,-0.02859626,0.017331066,-0.0059507843,0.00010414084,0.021081617,-0.02081082,0.056434285,0.01580106,-0.008773852,0.01691133,-0.08270168,0.007135525,0.037234712,-0.00909204,0.023938535,-0.0009926436,-0.0259289,-0.027133951,0.07159897,-0.00043454603,0.035257887,-0.026565276,-0.022963664,0.011285503,0.008841552,-0.013377417,0.0064348355,0.02740475,-0.023586499,-0.033443544,0.026592355,0.0043090717,0.0023864065,-0.038209587,-0.0066683986,0.031954154,-0.0019615924,-0.0057341456,0.005436268,-0.023654198,0.01559796,0.00228655,0.011881258,-0.0063231313,-0.02471031,0.04124252,-0.041432083,0.0079614585,-0.019037094,0.017791422,0.013289408,-0.042244475,0.05058505,0.012260376,0.044546258,-0.009755495,0.02716103,-0.009396688,-0.02213773,-0.004156748,-0.061579447,0.0019057404,0.0037268563,-0.04419422,0.0135669755,-0.026294477,0.1475307,-0.0010552657,0.02068896,-0.024155173,-0.013045689,0.04284023,0.007521412,-0.024954028,-0.016247874,-0.06233768,0.019037094,-0.00040027316,-0.025495624,0.010134612,-0.0649915,-0.01930789,-0.01171201,0.008469204,0.042000756,0.016803011,-0.0030583239,0.0061809625,-0.0035372977,-0.017222747,0.042325713,0.007650041,0.0070881355,-0.01691133,0.0077109705,-0.024141634,-0.0060624885,-0.048391588,0.009660715,-0.036097363,0.014243971,0.0031344858,0.01683009,-0.013377417,-0.023044903,0.030329367,-0.041052964,-0.008103628,-0.064016625,0.023031363,-0.031791676,0.049962215,-0.0017652638,-0.005466733,0.030546004,-0.009545626,0.017967442,0.021189937,0.0050097615,-0.011163644,0.012510864,0.022841804,0.0033426618,0.045169093,0.017520625,-0.0005221322,-0.021013917,0.011881258,0.0045629446,0.00907173,-0.008002078,-0.032143712,-0.0360432,-0.01047988,0.020025507,0.017439386,-0.027648468,0.008665533,0.0009638713,0.0339039,0.01692487,0.0060117138,-0.026700674,0.034987092,-0.014541848,-0.01039864,0.010073682,-0.008990491,0.0011940496,-0.010567889,-0.05589269,-0.015124064,0.027485989,-0.011001165,0.04576485,-0.0495831,0.010107532,0.015191763,0.017398765,0.04013225,-0.02596952,0.031656276,-0.008049468,-0.02716103,-0.032008313,-0.033741422,-0.015042824,0.039671894,-0.022679325,-0.02075666,-0.025265446,-0.003364664,-0.008090088,0.004901442,0.015949998,0.004414006,0.013072769,0.043679703,0.020120285,0.005036841,-0.00019495335,-0.0045054,-0.034418415,-0.000118685646,0.014216891,0.03999685,0.013790384,0.0029398499,0.020932678,-0.012937371,-0.02213773,-0.007250614,0.0031886455,-0.0033528167,-0.025265446,0.014988665,0.017520625,0.0047152685,-0.030248128,-0.050097615,-0.03241451,-0.06569558,0.007243844,0.058925625,0.014216891,0.0018414258,0.06455822,-0.031981234,-0.04560237,-0.009193589,0.0070407456,0.0022019255,0.004806663,0.02207003,-0.0546741,-0.025631022,0.018102841,0.004955602,-0.023654198,0.029246176,-0.021189937,0.016369734,-0.0051519303,0.015787518,-0.008956641,0.06114617,-0.0031988004,0.019605769,0.01697903,-0.0062892814,-0.007264154,-0.017263368,-0.03533913,0.0040958184,-0.02334278,0.03114176,0.0073318537,-0.046550162,-0.010770987,-0.022936583,-0.0151376035,0.010026293,0.050043456,-0.027012091,-0.020472322,-0.03238743,0.02335632,-0.014243971,-0.035880722,-0.0114005925,0.009802884,-0.022801185,0.0064212955,0.008218717,-0.011150104,-0.012131747,0.008117167,-0.02225959,-0.012185906,-0.025102967,-0.008462435,0.0008733233,-0.0009427152,-0.022354368,0.0038588704,0.01944329,-0.003500063,0.008888941,-0.00090971176,-0.008665533,0.030302286,-0.038372066,-0.018725676,0.05605517,-0.0043361518,0.030383525,-0.062770955,0.030627243,-0.023396939,-0.04262359,0.0031412558,0.02602368,-0.009762265,0.0039773444,-0.024263494,-0.012253606,-0.0021968482,0.020011965,0.01695195,0.020431703,-0.012449934,0.036936834,-0.028244222,0.011955728,-0.025942441,-0.018685056,0.038155425,-0.024954028,-0.0014656937,-0.011502141,-0.038236666,0.043733865,0.025563324,-0.004383541,-0.026267398,-0.0077177407,-0.04541281,-0.0141898105,0.009938284,0.039834373,0.037992947,-0.0031361782,0.004674649,-0.03766799,0.01172555,0.017669564,0.03366018,-0.039103217,-0.016302034,-0.025509164,0.027012091,0.0075755715,-0.052507717,0.042027835,0.02316676,-0.0025353455,0.030248128,0.007562032,-0.014000252,-0.042298634,0.0005335565,-0.016342655,0.01039187,0.02724227,-0.0033612791,-0.005327949,-0.012544713,-0.00906496,-0.029083695,0.0469022,0.03466213,0.012382234,-0.0072099944,-0.005070691,0.005981249,0.0036760818,0.012057277,0.03477045,-0.007243844,0.0064179106,0.01550318,-0.012741042,-0.0036997765,-0.072465524,-0.03804711,-0.00519255,-0.0079614585,-0.06542478,-0.028433781,-0.01952453,0.015083443,0.01027678,0.01167816,-0.0065465397,0.000062516236,-0.002117301,-0.019334972,-0.0025031883,-0.01677593,-0.0015367782,-0.03894074,-0.004928522,-0.022476226,-0.04137792,0.0005356721,0.013682065,-0.014216891,0.0011652773,-0.0983538,-0.03918446,0.015164684,-0.022801185,-0.005964324,0.028406702,-0.016383274,-0.0014309977,-0.033226904,-0.009843504,0.007392783,-0.00019474178,-0.041973677,0.040348887,-0.040457208,-0.018157,0.05207444,0.007907299,-0.03753259,-0.010486649,-0.06260848,-0.021393035,-0.008611374,0.044708736,0.0025556553,0.010053373,-0.0046103345,0.013973173,0.035582844,-0.027472448,0.04806663,0.0015807828,-0.021677373,-0.0065194597,-0.011867719,-0.011806789,0.0008369348,-0.019551609,0.042109076,0.014731406,-0.017926821,-0.044654574,0.01557088,0.025441464,-0.04419422,0.0030634014,0.00023504412,0.030925121,0.01565212,0.006340056,0.027567228,0.006725943,0.002520113,-0.032116633,0.0049826815,-0.004820203,-0.00094694644,0.012862901,0.037315954,-0.008916021,0.0040619685,0.017141508,-0.016315574,-0.019984886,-0.006895192,-0.02473739,-0.044627495,-0.014460608,0.011001165,0.0031226384,0.026416337,0.029787771,0.011387052,0.02335632,-0.017358147,-0.021189937,0.0025979674,-0.023464639,0.025319604,0.022652246,0.015963538,-0.0026572046,0.0063095912,0.025157126,0.021108698,-0.025278986,-0.023843756,-0.014934505,-0.009261289,-0.029029537,0.016356194,0.0009020956,0.014541848,0.023884377,0.0009334066,0.0049962215,-0.023762517,0.0053990334,-0.020364003,-0.010182002,-0.0004612027,-0.008211947,0.02984193,-0.016803011,-0.011102715,-0.0079614585,0.019118333,-0.02466969,0.016112477,0.021284716,0.021501355,-0.006979816,0.031223,0.014880345,-0.025590403,0.022354368,-0.009362838,-0.019930726,-0.021731533,-0.009342528,0.005625827,-0.016031237,-0.0011864334,0.0019277426,-0.0040958184,0.00645853,-0.012294225,-0.005463348,0.006624394,-0.0124093145,0.035718244,0.05042257,-0.0007078827,-0.03341646,0.068457715,-0.032143712,0.025048807,0.0126462625,-0.04273191,0.020011965,0.0077109705,-0.02323446,-0.0011627385,-0.03249575,0.006377291,0.019808868,-0.0017347991,0.027662007,-0.021081617,-0.011387052,-0.011475062,0.0012177444,0.013946093,-0.018833995,-0.03918446,0.01944329,-0.052561875,-0.03793879,-0.0036049972,0.00050901546,0.0059981737,-0.00020669497,-0.024304112,-0.015828138,0.022232508,0.017317526,0.009396688,-0.017127968,0.004254912,-0.022422068,0.030708484,0.008103628,0.012341615,-0.020066125,0.022828264,-0.0031514107,0.036476478,-0.030491846,0.040565528,-0.020106746,0.055730212,0.027810946,0.006658244,-0.0042582974,0.0051146955,0.009552397,0.03533913,-0.035501607,0.0025218055,-0.0014233815,0.033064425,-0.0060218684,0.013194628,-0.005490428,0.012206216,0.0076094214,0.013330027,-0.02976069,-0.024371812,0.014731406,0.06807859,0.02062126,0.011495371,-0.0060015586,0.0044580107,0.045575287,-0.015313622,-0.030058568,0.04812079,-0.00010831917,-0.019768247,-0.013722684,0.00077473593,0.014406449,-0.010804837,-0.006208042,-0.048743624,0.007853139,-0.027012091,-0.04067385,0.026646515,-0.03406638,-0.009288369,0.007954689,-0.0054091886,0.009349298,0.031656276,-0.00012630183,-0.016721772,-0.001428459,-0.009796115,-0.0025048808,0.020025507,0.025265446,-0.010818377,0.013573745,-0.0018685056,-0.014799106,-0.010154922,0.0017119505,0.013174319,0.022503307,-0.009626866,0.0028873829,0.003889335,0.00083566544,0.018305939,0.007914069,0.022543926,0.021636754,-0.026010139,-0.0073047737,-0.019998426,-0.012727502,-0.015205303,0.021785691,0.023044903,0.008096858,-0.007886989,0.0094440775,0.026308017,0.014555388,0.022300208,0.0070881355,-0.031656276,-0.018915234,0.00095964014,0.02343756,0.021230556,0.009647176,0.062554315,0.033091504,0.022449147,0.030762643,0.024344733,0.057625797,0.013986712,-0.01292383,0.008807702,0.0022560852,-0.009721645,-0.0027435215,0.014054412,-0.0180893,-0.021555513,-0.022868885,-0.0060421783,0.0032817323,0.021081617,0.00051832414,0.008442125,-0.0052636345,0.036070284,0.021433655,-0.027012091,-0.024480132,-0.0059237042,-0.0012499016,0.02204295,-0.034526736,0.032739468,0.043463066,0.008448895,-0.0038317905,0.0070813657,0.022598086,0.0067360983,0.01031063,0.010588199,-0.0010561119,0.03929278,0.0015333932,-0.01948391,0.029679451,0.007264154,-0.0029838546,0.007521412,0.018725676,-0.022246048,0.025021728,-0.014406449,0.017452925,-0.010506959,-0.012097897,-0.014920965,0.009796115,-0.016423894,-0.014406449,-0.0006689555,-0.009667486,0.014839726,0.001143275,0.010324171,0.026362177,0.034987092,0.02458845,-0.005327949,0.012328075,0.01033771,-0.00903788,-0.020418163,-0.0052263997,0.008293186,0.01167139,-0.011197493,0.007501102,-0.023654198,0.0002968199,-0.0013429885,0.0005703681,0.10311984,-0.007534952,-0.004813433,0.027865106,0.008313496,-0.00054201897,0.027120411,-0.03986145,-0.002091914,-0.005405803,-0.03625984,0.018536117,0.021948172,0.016992569,0.011177184,0.026632976,-0.008205177,-0.008828012,-0.0050943857,0.0029144625,0.0012499016,0.025184207,-0.018793376,0.012849361,0.024317652,-0.0019548223,-0.014162731,-0.016261416,0.027580768,0.019186033,-0.0010501882,-0.02878582,0.0046137194,-0.044898294,-0.034391336,0.014636627,0.018861074,0.01683009,0.032766547,-0.03106052,0.0022476227,-0.012659802,0.029923169,0.0038588704,-0.021081617,-0.004133053,0.005290714,0.0013302948,0.005311024,0.010554349,-0.013749764,-0.005273789,-0.007954689,-0.00035499915,-0.032874867,0.010798067,-0.023478178,-0.03999685,0.023329241,0.021812772,-0.025414385,-0.014772026,0.026917312,0.008503054,0.06120033,0.0028586106,-0.029164935,-0.005585207,0.015949998,0.013776843,0.002015752,0.008909252,-0.016748851,0.014149191,0.01429813,0.0129306,-0.008597834,0.0016738696,-0.01432521,0.0062994366,0.024046855,0.017006109,0.03249575,-0.018251779,0.03918446,-0.05345551,0.01158338,0.023694817,-0.0055005825,0.005321179,0.00649915,-0.0065905442,0.0033917439,0.029354494,0.0041905977,0.004705114,-0.0126191825,-0.019876568,0.001073883,0.0040551987,0.018549656,-0.007927609,0.010852226,0.028839977,-0.00086528395,-0.03219787,0.0006232583,0.00052001665,-0.031520877,0.026443416,0.013919013,-0.014975125,-0.0035643775,-0.0047118836,0.0001875487,-0.010182002,-0.02207003,-0.04411298,-0.046252284,0.0129983,-0.009437308,-0.031845834,0.015814599,0.0014174578,-0.008577524,-0.011326123,0.042325713,0.012605643,0.025102967,0.010818377,-0.010242932,-0.014162731,0.023708357,-0.0088821715,0.032143712,-0.032252032,-0.00095710135,-0.0014436914,0.03742427,0.031629197,0.011258423,-0.016383274,-0.017561244,-0.012666573,0.03379558,0.046793878,0.012280686,0.024222873,-0.049528938,-0.0337685,-0.01695195,0.017601864,-0.021731533,-0.017222747,0.04015933,-0.018617356,-0.0014267665,-0.026619434,0.0035203728,0.0019954422,0.02355942,0.0011068864,0.0013700682,0.012829051,-0.012118206,0.0131810885,0.033389382,-0.021365955,-0.018427799,0.011292273,-0.01288998,0.021040998,-0.011894799,0.014758486,0.010798067,0.013390956,0.002836608,0.022638706,-0.024006234,-0.0412696,-0.032901946,-0.03988853,-0.027702626,0.016464513,-0.016518673,0.028487941,-0.014704327,-0.015963538,0.007270924,-0.0004265067,0.04684804,-0.0012245143,0.021122238,0.01041895,-0.01165785,-0.014541848,-0.013878393,-0.035745326,0.0073521635,-0.014731406,0.007000126,0.013458656,-0.0009782575,0.03406638,0.038128346,-0.023708357,0.017520625,-0.034499653,-0.008259336,0.018481957,-0.009559167,0.019050634,-0.0019395901,0.002335632,0.0088821715,0.022963664,-0.008455665,0.036638957,0.0018820455,0.006749638,-0.04316519,-0.036205683,0.003774246,-0.010087222,0.010459569,-0.003347739,-0.018874615,-0.017926821,0.02487279,0.010974086,0.011529221,-0.030627243,0.017452925,-0.001890508,0.02859626,-0.026321556,-0.0051350053,-0.015421941,-0.008949871,-0.011129795,-0.019362051,0.007947919,0.010093993,-0.018779835,-0.008651993,0.05968386,0.019064173,-0.04441086,0.018035142,0.027106872,0.0140408715,-0.014663707,0.005876315,-0.0036930065,-0.0033731265,-0.0039468794,0.015462561,-0.033091504,-0.006993356,0.0042278324,0.03639524,0.026186159,-0.010676208,-0.00015539146,0.022232508,0.030898042,-0.02736413,0.025116507,0.015083443,-0.022218969,-0.01175263,-0.0026233548,-0.016803011,0.024249954,0.012463474,0.013763304,-0.040836327,-0.034851693,-0.016220795,0.002655512,-0.0068241074,0.023261542,-0.0064179106,0.0084353555,0.041161284,0.019835947,0.01423043,0.040782165,0.020851439,-0.020364003,-0.011299043,0.023816677,-0.01807576,0.0035508377,0.0151376035,0.01171878,-0.013946093,0.0018431183,-0.000030279649,-0.017290447,-0.0056833713,0.014758486,0.016071856,-0.008814472,0.004437701,-0.0035339128,-0.008056238,0.044058822,0.008550444,-0.00389949,0.028948298,-0.016694691,-0.031277157,0.0042312173,-0.0050266865,0.0005856005,0.010892846,-0.0019683624,0.0022053106,-0.019714087,0.031737514,-0.014975125,0.014352289,0.010513729,0.002679207,0.0011737397,-0.0017432616,0.0015054671,0.023681277,0.03406638,0.025251906,0.010730367,-0.019998426,-0.013099849,0.024899868,0.008536904,0.009484697,0.019334972,0.0233834,-0.019863026,0.030681403,-0.008252567,-0.015096984,0.014122111,0.047200076,0.03382266,0.045006614,-0.018576737,-0.027580768,0.004786353,-0.022882424,-0.0091191195,-0.0336331,-0.023518799,-0.01041895,0.004502015,0.029246176,0.015029284,0.014216891,-0.016450973,-0.024304112,-0.011732319,0.024859248,0.0010975777,0.01292383,0.017588325,-0.018630896,-0.010899616,0.008699383,0.040809244,-0.022747025,-0.020120285,0.007683891,0.011556301,-0.0073724734,0.0012168981,-0.02079728,-0.0064280657,-0.0075552617,-0.0046949587,0.037315954,0.010425719,0.022394987,-0.0020360618,0.0103174,-0.024994647,-0.036097363,-0.008570754,0.028135903] ``` ### Step 4. Perform a semantic similarity search based on the embedded vectors + Perform a semantic similarity search to get the 3 nearest `document` against the question `How to migrate to TiDB from MySQL?`. Review the retrieved documents to ensure their relevance to the topic of "Migration to TiDB from MySQL". Supplying them as additional contextual prompts for your LLM can enhance its ability to provide a more precise response to your question. You can tell they are coming from the `TiDB Data Migration` section. To ensure conciseness, the output has been truncated. ```sql PAGER less -S SELECT document FROM llama_index_rag_test ORDER BY VEC_COSINE_DISTANCE(embedding, '[-0.0088212425,-0.02732351,0.008679073,-0.008949871,-0.0052805594,-0.043733865,-0.031520877,0.033985138,-0.037207633,-0.020973299,0.011414132,0.050287172,-0.014704327,-0.003625307,0.000286665,0.078910515,-0.0034797532,-0.0055479724,-0.008922791,0.052318156,0.0006689555,-0.012395775,0.0033528167,-0.015462561,0.0058323103,-0.04015933,-0.051262043,0.017371686,0.03133132,-0.040457208,0.028298382,-0.03625984,-0.013248788,-0.007196455,-0.028190063,0.0127952015,-0.012761352,0.034635052,-0.010574658,0.011373512,0.023843756,0.02859626,0.0006452607,0.017385226,0.051045407,-0.0156115,-0.040592607,-0.001942975,0.011265193,0.036503557,0.0024168715,-0.033091504,-0.0074063228,0.0336331,-0.017601864,-0.03601612,0.023721898,0.030708484,0.03466213,0.019957807,0.027594307,-0.059033945,0.031033441,0.030789724,-0.00779221,0.012653032,-0.061796084,0.001817731,0.032224953,0.017155048,-0.029300334,0.06222936,-0.053780466,0.010046602,0.039644815,0.038480382,-0.021812772,0.016234335,-0.0078396,-0.01170524,0.038480382,-0.008137478,-0.00388595,-0.037288874,0.042406954,-0.018157,-0.028894138,-0.041946597,0.010012753,-0.035528686,-0.019334972,-0.00009361569,0.0024862634,0.033335224,0.034337174,0.046143964,-0.0285421,0.004691574,-0.028271303,0.004918367,0.04909566,0.009328988,-0.008178097,0.011982808,-0.011244884,0.007392783,-0.008245797,-0.005581822,-0.03111468,0.007379243,0.0364494,-0.042027835,-0.008293186,0.024886329,-0.008211947,0.00781252,0.021474274,-0.0026335097,-0.05995466,-0.05451162,0.0012871363,-0.012801971,-0.00023144759,-0.049826816,-0.030356446,-0.030356446,-0.033362303,0.0016129401,-0.06509982,-0.03514957,0.037343033,0.0064517604,-0.011962498,-0.01167139,-0.020580642,-0.03260407,-0.0116104605,0.022164809,-0.045846086,0.072952956,-0.0010611893,0.020052586,0.038128346,-0.009809654,-0.0052128597,0.0077719,-0.00129983,-0.023830216,-0.0058932398,-0.0065194597,0.029056616,0.011556301,-0.054619938,-0.022340829,-0.012463474,-0.026822533,0.03203539,-0.00027756786,-0.009538856,-0.0009858736,-0.010669438,-0.014826186,-0.040348887,0.011089175,-0.039482336,0.037153475,-0.010703288,-0.026389256,-0.07279048,-0.01165785,-0.011881258,0.022963664,-0.05451162,-0.05605517,-0.025563324,-0.04259651,-0.034580894,0.004691574,-0.045575287,0.044762895,0.033281066,0.08595126,-0.035799485,-0.013553436,-0.014162731,0.033497702,0.0004028119,0.0026216623,-0.021799233,-0.033443544,0.029571133,0.061687764,0.003190338,0.028731659,0.022543926,-0.03639524,0.0035711476,0.009708106,-0.018617356,0.020905599,0.012625953,0.04024057,0.012544713,-0.021636754,-0.08719693,-0.008123937,-0.044979535,0.014582467,-0.009606556,-0.051641162,0.01553026,-0.0015477793,-0.0067902575,0.008245797,0.0070881355,0.010554349,0.01696549,0.05207444,-0.019971346,0.012686882,-0.0027689086,0.020716041,0.051099565,0.010581428,0.03777631,0.024046855,0.022801185,-0.04015933,0.026632976,-0.04403174,0.032252032,0.042081997,0.0023762516,-0.0014640013,-0.0008610527,0.06910763,0.00002308658,0.008083317,-0.0050469963,-0.0049149822,-0.050151773,-0.025265446,0.0018532733,-0.00008182117,0.024994647,-0.001661176,0.013336797,0.02986901,0.028894138,0.051207885,-0.0026436646,0.012145286,-0.0073047737,-0.023180302,-0.01704673,-0.043788023,-0.03739719,0.018238239,0.03544745,0.0061606527,-0.010425719,0.003875795,-0.019714087,-0.015435481,-0.043625545,0.013858084,0.035664085,0.03804711,0.04397758,0.029489893,0.023627117,0.039780214,0.002853533,0.018698595,-0.021826312,0.010994395,0.015340702,0.052561875,-0.07409031,0.0731696,-0.07604005,-0.053617988,0.00033595867,0.003170028,-0.0058018453,-0.013289408,0.005822155,-0.0045595597,-0.013478966,-0.01689779,0.013932553,0.03254991,0.021420116,0.001810961,-0.015191763,0.0068274923,0.0025895052,-0.047633354,0.050937086,0.006143728,0.063041754,0.055432335,0.026199698,-0.00583908,-0.012131747,0.0233157,0.01677593,-0.02479155,-0.0035948423,0.012504093,-0.015449021,-0.02079728,-0.0078396,-0.0036523868,-0.028135903,-0.038074188,0.0012236681,0.035474528,0.050178852,0.008076548,0.017222747,-0.042109076,-0.011116254,-0.011251654,-0.0129306,0.049041502,0.06645381,-0.005273789,-0.019077713,0.008123937,0.018820455,-0.051126648,0.0010730367,0.034418415,0.010127842,-0.04698344,0.003434056,-0.009315448,0.03774923,0.008550444,-0.023627117,-0.04925814,-0.057246678,-0.008022388,-0.007128755,-0.06407078,0.014176271,0.021068078,-0.014081491,0.020336924,-0.017764343,0.009863814,-0.00906496,0.025549782,-0.011353202,-0.0003590188,0.009403458,-0.018468417,-0.015746899,0.042136155,-0.03766799,-0.0015740129,0.0024388738,-0.031547956,0.0030312443,-0.0034865232,0.008855091,-0.03514957,-0.024926947,0.04010517,0.015706278,0.006350211,0.023613578,0.007413093,-0.028894138,-0.011217804,-0.048743624,0.021745073,0.060387935,-0.023613578,-0.036232762,-0.0008081625,-0.022543926,0.016450973,0.01821116,-0.03904906,0.021406576,0.04135084,0.009599786,-0.07214057,0.06878267,0.0011195801,0.017886203,-0.010459569,-0.072628,0.020905599,0.025603943,0.046441842,0.015394862,-0.0044444706,0.056705084,-0.013289408,-0.041946597,-0.044708736,-0.009579476,0.012517634,0.022774104,0.016586373,0.03864286,-0.012504093,0.0018498882,0.002464261,0.03238743,0.05459286,0.013397727,0.015977077,0.037586752,-0.011468291,-0.02062126,-0.02859626,0.017331066,-0.0059507843,0.00010414084,0.021081617,-0.02081082,0.056434285,0.01580106,-0.008773852,0.01691133,-0.08270168,0.007135525,0.037234712,-0.00909204,0.023938535,-0.0009926436,-0.0259289,-0.027133951,0.07159897,-0.00043454603,0.035257887,-0.026565276,-0.022963664,0.011285503,0.008841552,-0.013377417,0.0064348355,0.02740475,-0.023586499,-0.033443544,0.026592355,0.0043090717,0.0023864065,-0.038209587,-0.0066683986,0.031954154,-0.0019615924,-0.0057341456,0.005436268,-0.023654198,0.01559796,0.00228655,0.011881258,-0.0063231313,-0.02471031,0.04124252,-0.041432083,0.0079614585,-0.019037094,0.017791422,0.013289408,-0.042244475,0.05058505,0.012260376,0.044546258,-0.009755495,0.02716103,-0.009396688,-0.02213773,-0.004156748,-0.061579447,0.0019057404,0.0037268563,-0.04419422,0.0135669755,-0.026294477,0.1475307,-0.0010552657,0.02068896,-0.024155173,-0.013045689,0.04284023,0.007521412,-0.024954028,-0.016247874,-0.06233768,0.019037094,-0.00040027316,-0.025495624,0.010134612,-0.0649915,-0.01930789,-0.01171201,0.008469204,0.042000756,0.016803011,-0.0030583239,0.0061809625,-0.0035372977,-0.017222747,0.042325713,0.007650041,0.0070881355,-0.01691133,0.0077109705,-0.024141634,-0.0060624885,-0.048391588,0.009660715,-0.036097363,0.014243971,0.0031344858,0.01683009,-0.013377417,-0.023044903,0.030329367,-0.041052964,-0.008103628,-0.064016625,0.023031363,-0.031791676,0.049962215,-0.0017652638,-0.005466733,0.030546004,-0.009545626,0.017967442,0.021189937,0.0050097615,-0.011163644,0.012510864,0.022841804,0.0033426618,0.045169093,0.017520625,-0.0005221322,-0.021013917,0.011881258,0.0045629446,0.00907173,-0.008002078,-0.032143712,-0.0360432,-0.01047988,0.020025507,0.017439386,-0.027648468,0.008665533,0.0009638713,0.0339039,0.01692487,0.0060117138,-0.026700674,0.034987092,-0.014541848,-0.01039864,0.010073682,-0.008990491,0.0011940496,-0.010567889,-0.05589269,-0.015124064,0.027485989,-0.011001165,0.04576485,-0.0495831,0.010107532,0.015191763,0.017398765,0.04013225,-0.02596952,0.031656276,-0.008049468,-0.02716103,-0.032008313,-0.033741422,-0.015042824,0.039671894,-0.022679325,-0.02075666,-0.025265446,-0.003364664,-0.008090088,0.004901442,0.015949998,0.004414006,0.013072769,0.043679703,0.020120285,0.005036841,-0.00019495335,-0.0045054,-0.034418415,-0.000118685646,0.014216891,0.03999685,0.013790384,0.0029398499,0.020932678,-0.012937371,-0.02213773,-0.007250614,0.0031886455,-0.0033528167,-0.025265446,0.014988665,0.017520625,0.0047152685,-0.030248128,-0.050097615,-0.03241451,-0.06569558,0.007243844,0.058925625,0.014216891,0.0018414258,0.06455822,-0.031981234,-0.04560237,-0.009193589,0.0070407456,0.0022019255,0.004806663,0.02207003,-0.0546741,-0.025631022,0.018102841,0.004955602,-0.023654198,0.029246176,-0.021189937,0.016369734,-0.0051519303,0.015787518,-0.008956641,0.06114617,-0.0031988004,0.019605769,0.01697903,-0.0062892814,-0.007264154,-0.017263368,-0.03533913,0.0040958184,-0.02334278,0.03114176,0.0073318537,-0.046550162,-0.010770987,-0.022936583,-0.0151376035,0.010026293,0.050043456,-0.027012091,-0.020472322,-0.03238743,0.02335632,-0.014243971,-0.035880722,-0.0114005925,0.009802884,-0.022801185,0.0064212955,0.008218717,-0.011150104,-0.012131747,0.008117167,-0.02225959,-0.012185906,-0.025102967,-0.008462435,0.0008733233,-0.0009427152,-0.022354368,0.0038588704,0.01944329,-0.003500063,0.008888941,-0.00090971176,-0.008665533,0.030302286,-0.038372066,-0.018725676,0.05605517,-0.0043361518,0.030383525,-0.062770955,0.030627243,-0.023396939,-0.04262359,0.0031412558,0.02602368,-0.009762265,0.0039773444,-0.024263494,-0.012253606,-0.0021968482,0.020011965,0.01695195,0.020431703,-0.012449934,0.036936834,-0.028244222,0.011955728,-0.025942441,-0.018685056,0.038155425,-0.024954028,-0.0014656937,-0.011502141,-0.038236666,0.043733865,0.025563324,-0.004383541,-0.026267398,-0.0077177407,-0.04541281,-0.0141898105,0.009938284,0.039834373,0.037992947,-0.0031361782,0.004674649,-0.03766799,0.01172555,0.017669564,0.03366018,-0.039103217,-0.016302034,-0.025509164,0.027012091,0.0075755715,-0.052507717,0.042027835,0.02316676,-0.0025353455,0.030248128,0.007562032,-0.014000252,-0.042298634,0.0005335565,-0.016342655,0.01039187,0.02724227,-0.0033612791,-0.005327949,-0.012544713,-0.00906496,-0.029083695,0.0469022,0.03466213,0.012382234,-0.0072099944,-0.005070691,0.005981249,0.0036760818,0.012057277,0.03477045,-0.007243844,0.0064179106,0.01550318,-0.012741042,-0.0036997765,-0.072465524,-0.03804711,-0.00519255,-0.0079614585,-0.06542478,-0.028433781,-0.01952453,0.015083443,0.01027678,0.01167816,-0.0065465397,0.000062516236,-0.002117301,-0.019334972,-0.0025031883,-0.01677593,-0.0015367782,-0.03894074,-0.004928522,-0.022476226,-0.04137792,0.0005356721,0.013682065,-0.014216891,0.0011652773,-0.0983538,-0.03918446,0.015164684,-0.022801185,-0.005964324,0.028406702,-0.016383274,-0.0014309977,-0.033226904,-0.009843504,0.007392783,-0.00019474178,-0.041973677,0.040348887,-0.040457208,-0.018157,0.05207444,0.007907299,-0.03753259,-0.010486649,-0.06260848,-0.021393035,-0.008611374,0.044708736,0.0025556553,0.010053373,-0.0046103345,0.013973173,0.035582844,-0.027472448,0.04806663,0.0015807828,-0.021677373,-0.0065194597,-0.011867719,-0.011806789,0.0008369348,-0.019551609,0.042109076,0.014731406,-0.017926821,-0.044654574,0.01557088,0.025441464,-0.04419422,0.0030634014,0.00023504412,0.030925121,0.01565212,0.006340056,0.027567228,0.006725943,0.002520113,-0.032116633,0.0049826815,-0.004820203,-0.00094694644,0.012862901,0.037315954,-0.008916021,0.0040619685,0.017141508,-0.016315574,-0.019984886,-0.006895192,-0.02473739,-0.044627495,-0.014460608,0.011001165,0.0031226384,0.026416337,0.029787771,0.011387052,0.02335632,-0.017358147,-0.021189937,0.0025979674,-0.023464639,0.025319604,0.022652246,0.015963538,-0.0026572046,0.0063095912,0.025157126,0.021108698,-0.025278986,-0.023843756,-0.014934505,-0.009261289,-0.029029537,0.016356194,0.0009020956,0.014541848,0.023884377,0.0009334066,0.0049962215,-0.023762517,0.0053990334,-0.020364003,-0.010182002,-0.0004612027,-0.008211947,0.02984193,-0.016803011,-0.011102715,-0.0079614585,0.019118333,-0.02466969,0.016112477,0.021284716,0.021501355,-0.006979816,0.031223,0.014880345,-0.025590403,0.022354368,-0.009362838,-0.019930726,-0.021731533,-0.009342528,0.005625827,-0.016031237,-0.0011864334,0.0019277426,-0.0040958184,0.00645853,-0.012294225,-0.005463348,0.006624394,-0.0124093145,0.035718244,0.05042257,-0.0007078827,-0.03341646,0.068457715,-0.032143712,0.025048807,0.0126462625,-0.04273191,0.020011965,0.0077109705,-0.02323446,-0.0011627385,-0.03249575,0.006377291,0.019808868,-0.0017347991,0.027662007,-0.021081617,-0.011387052,-0.011475062,0.0012177444,0.013946093,-0.018833995,-0.03918446,0.01944329,-0.052561875,-0.03793879,-0.0036049972,0.00050901546,0.0059981737,-0.00020669497,-0.024304112,-0.015828138,0.022232508,0.017317526,0.009396688,-0.017127968,0.004254912,-0.022422068,0.030708484,0.008103628,0.012341615,-0.020066125,0.022828264,-0.0031514107,0.036476478,-0.030491846,0.040565528,-0.020106746,0.055730212,0.027810946,0.006658244,-0.0042582974,0.0051146955,0.009552397,0.03533913,-0.035501607,0.0025218055,-0.0014233815,0.033064425,-0.0060218684,0.013194628,-0.005490428,0.012206216,0.0076094214,0.013330027,-0.02976069,-0.024371812,0.014731406,0.06807859,0.02062126,0.011495371,-0.0060015586,0.0044580107,0.045575287,-0.015313622,-0.030058568,0.04812079,-0.00010831917,-0.019768247,-0.013722684,0.00077473593,0.014406449,-0.010804837,-0.006208042,-0.048743624,0.007853139,-0.027012091,-0.04067385,0.026646515,-0.03406638,-0.009288369,0.007954689,-0.0054091886,0.009349298,0.031656276,-0.00012630183,-0.016721772,-0.001428459,-0.009796115,-0.0025048808,0.020025507,0.025265446,-0.010818377,0.013573745,-0.0018685056,-0.014799106,-0.010154922,0.0017119505,0.013174319,0.022503307,-0.009626866,0.0028873829,0.003889335,0.00083566544,0.018305939,0.007914069,0.022543926,0.021636754,-0.026010139,-0.0073047737,-0.019998426,-0.012727502,-0.015205303,0.021785691,0.023044903,0.008096858,-0.007886989,0.0094440775,0.026308017,0.014555388,0.022300208,0.0070881355,-0.031656276,-0.018915234,0.00095964014,0.02343756,0.021230556,0.009647176,0.062554315,0.033091504,0.022449147,0.030762643,0.024344733,0.057625797,0.013986712,-0.01292383,0.008807702,0.0022560852,-0.009721645,-0.0027435215,0.014054412,-0.0180893,-0.021555513,-0.022868885,-0.0060421783,0.0032817323,0.021081617,0.00051832414,0.008442125,-0.0052636345,0.036070284,0.021433655,-0.027012091,-0.024480132,-0.0059237042,-0.0012499016,0.02204295,-0.034526736,0.032739468,0.043463066,0.008448895,-0.0038317905,0.0070813657,0.022598086,0.0067360983,0.01031063,0.010588199,-0.0010561119,0.03929278,0.0015333932,-0.01948391,0.029679451,0.007264154,-0.0029838546,0.007521412,0.018725676,-0.022246048,0.025021728,-0.014406449,0.017452925,-0.010506959,-0.012097897,-0.014920965,0.009796115,-0.016423894,-0.014406449,-0.0006689555,-0.009667486,0.014839726,0.001143275,0.010324171,0.026362177,0.034987092,0.02458845,-0.005327949,0.012328075,0.01033771,-0.00903788,-0.020418163,-0.0052263997,0.008293186,0.01167139,-0.011197493,0.007501102,-0.023654198,0.0002968199,-0.0013429885,0.0005703681,0.10311984,-0.007534952,-0.004813433,0.027865106,0.008313496,-0.00054201897,0.027120411,-0.03986145,-0.002091914,-0.005405803,-0.03625984,0.018536117,0.021948172,0.016992569,0.011177184,0.026632976,-0.008205177,-0.008828012,-0.0050943857,0.0029144625,0.0012499016,0.025184207,-0.018793376,0.012849361,0.024317652,-0.0019548223,-0.014162731,-0.016261416,0.027580768,0.019186033,-0.0010501882,-0.02878582,0.0046137194,-0.044898294,-0.034391336,0.014636627,0.018861074,0.01683009,0.032766547,-0.03106052,0.0022476227,-0.012659802,0.029923169,0.0038588704,-0.021081617,-0.004133053,0.005290714,0.0013302948,0.005311024,0.010554349,-0.013749764,-0.005273789,-0.007954689,-0.00035499915,-0.032874867,0.010798067,-0.023478178,-0.03999685,0.023329241,0.021812772,-0.025414385,-0.014772026,0.026917312,0.008503054,0.06120033,0.0028586106,-0.029164935,-0.005585207,0.015949998,0.013776843,0.002015752,0.008909252,-0.016748851,0.014149191,0.01429813,0.0129306,-0.008597834,0.0016738696,-0.01432521,0.0062994366,0.024046855,0.017006109,0.03249575,-0.018251779,0.03918446,-0.05345551,0.01158338,0.023694817,-0.0055005825,0.005321179,0.00649915,-0.0065905442,0.0033917439,0.029354494,0.0041905977,0.004705114,-0.0126191825,-0.019876568,0.001073883,0.0040551987,0.018549656,-0.007927609,0.010852226,0.028839977,-0.00086528395,-0.03219787,0.0006232583,0.00052001665,-0.031520877,0.026443416,0.013919013,-0.014975125,-0.0035643775,-0.0047118836,0.0001875487,-0.010182002,-0.02207003,-0.04411298,-0.046252284,0.0129983,-0.009437308,-0.031845834,0.015814599,0.0014174578,-0.008577524,-0.011326123,0.042325713,0.012605643,0.025102967,0.010818377,-0.010242932,-0.014162731,0.023708357,-0.0088821715,0.032143712,-0.032252032,-0.00095710135,-0.0014436914,0.03742427,0.031629197,0.011258423,-0.016383274,-0.017561244,-0.012666573,0.03379558,0.046793878,0.012280686,0.024222873,-0.049528938,-0.0337685,-0.01695195,0.017601864,-0.021731533,-0.017222747,0.04015933,-0.018617356,-0.0014267665,-0.026619434,0.0035203728,0.0019954422,0.02355942,0.0011068864,0.0013700682,0.012829051,-0.012118206,0.0131810885,0.033389382,-0.021365955,-0.018427799,0.011292273,-0.01288998,0.021040998,-0.011894799,0.014758486,0.010798067,0.013390956,0.002836608,0.022638706,-0.024006234,-0.0412696,-0.032901946,-0.03988853,-0.027702626,0.016464513,-0.016518673,0.028487941,-0.014704327,-0.015963538,0.007270924,-0.0004265067,0.04684804,-0.0012245143,0.021122238,0.01041895,-0.01165785,-0.014541848,-0.013878393,-0.035745326,0.0073521635,-0.014731406,0.007000126,0.013458656,-0.0009782575,0.03406638,0.038128346,-0.023708357,0.017520625,-0.034499653,-0.008259336,0.018481957,-0.009559167,0.019050634,-0.0019395901,0.002335632,0.0088821715,0.022963664,-0.008455665,0.036638957,0.0018820455,0.006749638,-0.04316519,-0.036205683,0.003774246,-0.010087222,0.010459569,-0.003347739,-0.018874615,-0.017926821,0.02487279,0.010974086,0.011529221,-0.030627243,0.017452925,-0.001890508,0.02859626,-0.026321556,-0.0051350053,-0.015421941,-0.008949871,-0.011129795,-0.019362051,0.007947919,0.010093993,-0.018779835,-0.008651993,0.05968386,0.019064173,-0.04441086,0.018035142,0.027106872,0.0140408715,-0.014663707,0.005876315,-0.0036930065,-0.0033731265,-0.0039468794,0.015462561,-0.033091504,-0.006993356,0.0042278324,0.03639524,0.026186159,-0.010676208,-0.00015539146,0.022232508,0.030898042,-0.02736413,0.025116507,0.015083443,-0.022218969,-0.01175263,-0.0026233548,-0.016803011,0.024249954,0.012463474,0.013763304,-0.040836327,-0.034851693,-0.016220795,0.002655512,-0.0068241074,0.023261542,-0.0064179106,0.0084353555,0.041161284,0.019835947,0.01423043,0.040782165,0.020851439,-0.020364003,-0.011299043,0.023816677,-0.01807576,0.0035508377,0.0151376035,0.01171878,-0.013946093,0.0018431183,-0.000030279649,-0.017290447,-0.0056833713,0.014758486,0.016071856,-0.008814472,0.004437701,-0.0035339128,-0.008056238,0.044058822,0.008550444,-0.00389949,0.028948298,-0.016694691,-0.031277157,0.0042312173,-0.0050266865,0.0005856005,0.010892846,-0.0019683624,0.0022053106,-0.019714087,0.031737514,-0.014975125,0.014352289,0.010513729,0.002679207,0.0011737397,-0.0017432616,0.0015054671,0.023681277,0.03406638,0.025251906,0.010730367,-0.019998426,-0.013099849,0.024899868,0.008536904,0.009484697,0.019334972,0.0233834,-0.019863026,0.030681403,-0.008252567,-0.015096984,0.014122111,0.047200076,0.03382266,0.045006614,-0.018576737,-0.027580768,0.004786353,-0.022882424,-0.0091191195,-0.0336331,-0.023518799,-0.01041895,0.004502015,0.029246176,0.015029284,0.014216891,-0.016450973,-0.024304112,-0.011732319,0.024859248,0.0010975777,0.01292383,0.017588325,-0.018630896,-0.010899616,0.008699383,0.040809244,-0.022747025,-0.020120285,0.007683891,0.011556301,-0.0073724734,0.0012168981,-0.02079728,-0.0064280657,-0.0075552617,-0.0046949587,0.037315954,0.010425719,0.022394987,-0.0020360618,0.0103174,-0.024994647,-0.036097363,-0.008570754,0.028135903]') LIMIT 3\G ``` + Press Up and Down to scroll through the output. Content related to `dmctl`, `TiDB Data Migration`, `dm-task.yaml` are expected. Press Q to go back to `mysql>` prompt. ### Step 5. Exit from the TiDB Cloud Serverless connection + Now you had grasped all the prerequisite knowledge about the vector database and the similarity search, in the next step we will put everything together to build an simple Python RAG QA application backed by TiDB as the vector store and OpenAI Agent as the LLM. Exit to the OS prompt on the remote Linux. ```sql EXIT; ``` ```output mysql> EXIT; Bye ``` ### Step 6. Prepare the environment for the RAG application + Start from this step, you need to setup your own OpenAI API KEY to an environment variable on the remote Linux in order to make the LlamaIndex Query Engine functional. If you're unable to supply your own OpenAI API KEY, the hands-on lab concludes here. However, feel free to continue scrolling down to explore the output provided. + Setup an [OpenAI API key](https://help.openai.com/en/articles/4936850-where-do-i-find-my-openai-api-key). Execute the following command on the remote Linux, replace the `${OPENAI_API_KEY}` with the actual value. ```bash export OPENAI_API_KEY=${OPENAI_API_KEY} ``` + To utilize TiDB as a vector store and retrieve vectors by efficient vector indexes, you don't have to use SQL directly, TiDB supports Python and following frameworks: TiDB Vector Client, SQLAlchemy, Peewee, Django, LangChain, and LlamaIndex In this demo, we use [TiDB as a vector store for LlamaIndex](https://docs.llamaindex.ai/en/stable/examples/vector_stores/TiDBVector/). + Install the required libraries on the remote Linux. ```bash pip install --upgrade pip pip install \ "joblib>=1.3.0" \ "click==8.1.7" \ "PyMySQL==1.1.0" \ "SQLAlchemy==2.0.30" \ "tidb-vector==0.0.9" \ "openai==1.27.0" \ "llama-index==0.10.68" \ "llama-index-readers-web==0.1.23" \ "llama-index-vector-stores-tidbvector==0.1.2" \ "httpx==0.27.2" ``` ```output ... Successfully installed PyYAML-6.0.1 aiohttp-3.9.5 aiosignal-1.3.1 annotated-types-0.7.0 anyio-4.4.0 async-timeout-4.0.3 attrs-23.2.0 beautifulsoup4-4.12.3 certifi-2024.6.2 charset-normalizer-3.3.2 chromedriver-autoinstaller-0.6.4 click-8.1.7 cssselect-1.2.0 dataclasses-json-0.6.6 deprecated-1.2.14 dirtyjson-1.0.8 distro-1.9.0 exceptiongroup-1.2.1 feedfinder2-0.0.4 feedparser-6.0.11 filelock-3.14.0 frozenlist-1.4.1 fsspec-2024.6.0 greenlet-3.0.3 h11-0.14.0 html2text-2020.1.16 httpcore-1.0.5 httpx-0.27.0 jieba3k-0.35.1 joblib-1.4.2 llama-index-0.10.43 llama-index-agent-openai-0.2.7 llama-index-cli-0.1.12 llama-index-core-0.10.43 llama-index-embeddings-openai-0.1.10 llama-index-indices-managed-llama-cloud-0.1.6 llama-index-legacy-0.9.48 llama-index-llms-openai-0.1.22 llama-index-multi-modal-llms-openai-0.1.6 llama-index-program-openai-0.1.6 llama-index-question-gen-openai-0.1.3 llama-index-readers-file-0.1.23 llama-index-readers-llama-parse-0.1.4 llama-index-readers-web-0.1.18 llama-index-vector-stores-tidbvector-0.1.2 llama-parse-0.4.4 llamaindex-py-client-0.1.19 lxml-5.2.2 marshmallow-3.21.3 multidict-6.0.5 mypy-extensions-1.0.0 nest-asyncio-1.6.0 networkx-3.2.1 newspaper3k-0.2.8 nltk-3.8.1 numpy-1.26.4 openai-1.33.0 outcome-1.3.0.post0 packaging-24.0 pandas-2.2.2 peewee-3.17.5 pillow-10.3.0 playwright-1.44.0 pydantic-2.7.3 pydantic-core-2.18.4 pyee-11.1.0 pymysql-1.1.1 pypdf-4.2.0 python-dateutil-2.9.0.post0 regex-2024.5.15 requests-2.32.3 requests-file-2.1.0 selenium-4.21.0 sgmllib3k-1.0.0 sniffio-1.3.1 sortedcontainers-2.4.0 soupsieve-2.5 spider-client-0.0.11 sqlalchemy-2.0.30 striprtf-0.0.26 tenacity-8.3.0 tidb-vector-0.0.9 tiktoken-0.7.0 tinysegmenter-0.3 tldextract-5.1.2 tqdm-4.66.4 trio-0.25.1 trio-websocket-0.11.1 typing-extensions-4.12.2 typing-inspect-0.9.0 tzdata-2024.1 wrapt-1.16.0 wsproto-1.2.0 yarl-1.9.4 ``` ### Step 7. Create the simple RAG chatbot application + Create the `tidb-chat` program. Note that it connects to TiDB and use table `semantic_search_test.llama_index_rag_test` as the vector store. And it requires the successful completion of Step 2. ```bash cat > tidb-chat.py <<'EOF' import os import click from sqlalchemy import URL from llama_index.core import VectorStoreIndex, StorageContext from llama_index.vector_stores.tidbvector import TiDBVectorStore from llama_index.readers.web import SimpleWebPageReader tidb_connection_url = URL( "mysql+pymysql", username='serverless_username', password='serverless_password', host='serverless_host', port=serverless_port, database="semantic_search_test", query={"ssl_verify_cert": True, "ssl_verify_identity": True}, ) tidbvec = TiDBVectorStore( connection_string=tidb_connection_url, table_name="llama_index_rag_test", distance_strategy="cosine", vector_dimension=1536, drop_existing_table=False, ) tidb_vec_index = VectorStoreIndex.from_vector_store(tidbvec) storage_context = StorageContext.from_defaults(vector_store=tidbvec) query_engine = tidb_vec_index.as_query_engine(streaming=True) @click.command() def start_chat(): while True: question = click.prompt("Enter your question") response = query_engine.query(question) click.echo(response) if __name__ == '__main__': start_chat() EOF ls -l tidb-chat.py ``` ```output -rw-r--r--. 1 rocky rocky 1157 Jun 20 14:57 tidb-chat.py ``` ### Step 8. Run the simple RAG chatbot application + Run `tidb-chat` program to have fun. ```bash python tidb-chat.py ``` ```output Enter your question: ``` + Try following questions on `tidb-chat` program you just built. ```bash what is a dm-task.yaml file? ``` ```output Enter your question: what is a dm-task.yaml file? A `dm-task.yaml` file is a configuration file used for defining the settings and parameters required for a migration task in a DM (Data Migration) cluster. This file contains the necessary information such as the data source, target database, replication rules, filtering criteria, and other configurations essential for executing a migration task. It serves as a blueprint for DM to understand how to handle the data migration process effectively. ``` ```bash How to migrate to TiDB from MySQL? ``` ```output Enter your question: How to migrate to TiDB from MySQL? To migrate to TiDB from MySQL, you can follow these steps: 1. Export all data from MySQL. 2. Import full data to TiDB. 3. Replicate incremental data to TiDB by adding the data source, adding a replication task, checking the migration task status, monitoring the task, and viewing logs. ``` + Try more questions if you have time. Press Ctrl + C to exit to OS prompt. ### Step 9. Start over again with an empty database + Start it all over again in one step. In this step, you initiate the process by creating an empty database within your TiDB environment. Subsequently, you leverage the LlamaIndex framework to autonomously construct the vector store directly from the supplied URLs. This automation streamlines the creation of the vector store. + On the remote Linux, provision a new database named `chatdb` in TiDB to serve the purpose of a vector store. Then, exit to the OS prompt. ```bash mysql \ --comments -u 'serverless_username' -p'serverless_password' \ -h serverless_host -P serverless_port \ --ssl-mode=VERIFY_IDENTITY --ssl-ca=/etc/pki/tls/certs/ca-bundle.crt ``` ```sql DROP DATABASE IF EXISTS chatdb; CREATE DATABASE chatdb; EXIT; ``` + Create a new Python chat program `tidb-chat-v2.py` to use `chatdb` as the vector store. Review the code to confirm that the database used in the `URL()` constructor is `chatdb`. During execution, the program will create table `llama_index_rag_test` as a vector store and populate documents and corresponding embeddings to it. ```bash cat > tidb-chat-v2.py <<'EOF' import os import click from sqlalchemy import URL from llama_index.core import VectorStoreIndex, StorageContext from llama_index.vector_stores.tidbvector import TiDBVectorStore from llama_index.readers.web import SimpleWebPageReader tidb_connection_url = URL( "mysql+pymysql", username='serverless_username', password='serverless_password', host='serverless_host', port=serverless_port, database='chatdb', query={"ssl_verify_cert": True, "ssl_verify_identity": True}, ) tidbvec = TiDBVectorStore( connection_string=tidb_connection_url, table_name="llama_index_rag_test", distance_strategy="cosine", vector_dimension=1536, drop_existing_table=True, ) tidb_vec_index = VectorStoreIndex.from_vector_store(tidbvec) storage_context = StorageContext.from_defaults(vector_store=tidbvec) query_engine = tidb_vec_index.as_query_engine(streaming=True) def prepare_data(url): documents = SimpleWebPageReader(html_to_text=True).load_data([url,]) tidb_vec_index.from_documents(documents, storage_context=storage_context, show_progress=True) urls = [ 'https://docs.pingcap.com/tidb/stable/overview', 'https://docs.pingcap.com/tidb/stable/quick-start-with-tidb', 'https://docs.pingcap.com/tidb/stable/quick-start-with-htap', 'https://docs.pingcap.com/tidb/stable/basic-sql-operations', 'https://docs.pingcap.com/tidb/stable/explore-htap', 'https://docs.pingcap.com/tidb/stable/migration-overview', 'https://docs.pingcap.com/tidb/stable/migration-tools', 'https://docs.pingcap.com/tidb/stable/data-import-best-practices', 'https://docs.pingcap.com/tidb/stable/migrate-aurora-to-tidb', 'https://docs.pingcap.com/tidb/stable/migrate-small-mysql-to-tidb', 'https://docs.pingcap.com/tidb/stable/migrate-large-mysql-to-tidb', 'https://docs.pingcap.com/tidb/stable/migrate-small-mysql-shards-to-tidb', 'https://docs.pingcap.com/tidb/stable/migrate-large-mysql-shards-to-tidb', 'https://docs.pingcap.com/tidb/stable/migrate-from-mariadb', 'https://docs.pingcap.com/tidb/stable/migrate-from-csv-files-to-tidb', 'https://docs.pingcap.com/tidb/stable/migrate-from-sql-files-to-tidb', 'https://docs.pingcap.com/tidb/stable/migrate-from-parquet-files-to-tidb', 'https://docs.pingcap.com/tidb/stable/migrate-from-tidb-to-tidb', 'https://docs.pingcap.com/tidb/stable/migrate-from-tidb-to-mysql', ] def start_chat(urls): for url in urls: prepare_data(url) while True: question = click.prompt("Enter your question") response = query_engine.query(question) click.echo(response) if __name__ == '__main__': start_chat(urls) EOF ``` + Run the new version chatbot application. ```bash python tidb-chat-v2.py ``` ```output Parsing nodes: 100%|██████████████████████████████████████████████████████████████████████| 1/1 [00:00<00:00, 128.14it/s] Generating embeddings: 100%|███████████████████████████████████████████████████████████████| 3/3 [00:00<00:00, 5.69it/s] Parsing nodes: 100%|███████████████████████████████████████████████████████████████████████| 1/1 [00:00<00:00, 64.40it/s] Generating embeddings: 100%|███████████████████████████████████████████████████████████████| 7/7 [00:00<00:00, 12.48it/s] Parsing nodes: 100%|███████████████████████████████████████████████████████████████████████| 1/1 [00:00<00:00, 98.69it/s] Generating embeddings: 100%|███████████████████████████████████████████████████████████████| 5/5 [00:00<00:00, 12.08it/s] Parsing nodes: 100%|██████████████████████████████████████████████████████████████████████| 1/1 [00:00<00:00, 159.58it/s] Generating embeddings: 100%|███████████████████████████████████████████████████████████████| 3/3 [00:00<00:00, 4.51it/s] Parsing nodes: 100%|███████████████████████████████████████████████████████████████████████| 1/1 [00:00<00:00, 99.84it/s] Generating embeddings: 100%|███████████████████████████████████████████████████████████████| 4/4 [00:00<00:00, 5.01it/s] Parsing nodes: 100%|██████████████████████████████████████████████████████████████████████| 1/1 [00:00<00:00, 176.32it/s] Generating embeddings: 100%|███████████████████████████████████████████████████████████████| 3/3 [00:00<00:00, 7.16it/s] Parsing nodes: 100%|██████████████████████████████████████████████████████████████████████| 1/1 [00:00<00:00, 140.53it/s] Generating embeddings: 100%|███████████████████████████████████████████████████████████████| 4/4 [00:00<00:00, 10.30it/s] Parsing nodes: 100%|███████████████████████████████████████████████████████████████████████| 1/1 [00:00<00:00, 84.42it/s] Generating embeddings: 100%|███████████████████████████████████████████████████████████████| 5/5 [00:00<00:00, 8.75it/s] Parsing nodes: 100%|███████████████████████████████████████████████████████████████████████| 1/1 [00:00<00:00, 59.69it/s] Generating embeddings: 100%|███████████████████████████████████████████████████████████████| 7/7 [00:00<00:00, 11.87it/s] Parsing nodes: 100%|██████████████████████████████████████████████████████████████████████| 1/1 [00:00<00:00, 120.75it/s] Generating embeddings: 100%|███████████████████████████████████████████████████████████████| 4/4 [00:00<00:00, 9.85it/s] Parsing nodes: 100%|███████████████████████████████████████████████████████████████████████| 1/1 [00:00<00:00, 58.71it/s] Generating embeddings: 100%|███████████████████████████████████████████████████████████████| 8/8 [00:00<00:00, 10.82it/s] Parsing nodes: 100%|███████████████████████████████████████████████████████████████████████| 1/1 [00:00<00:00, 78.74it/s] Generating embeddings: 100%|███████████████████████████████████████████████████████████████| 6/6 [00:00<00:00, 9.22it/s] Parsing nodes: 100%|███████████████████████████████████████████████████████████████████████| 1/1 [00:00<00:00, 45.15it/s] Generating embeddings: 100%|███████████████████████████████████████████████████████████████| 9/9 [00:00<00:00, 15.58it/s] Parsing nodes: 100%|███████████████████████████████████████████████████████████████████████| 1/1 [00:00<00:00, 72.00it/s] Generating embeddings: 100%|███████████████████████████████████████████████████████████████| 7/7 [00:00<00:00, 14.08it/s] Parsing nodes: 100%|███████████████████████████████████████████████████████████████████████| 1/1 [00:00<00:00, 94.23it/s] Generating embeddings: 100%|███████████████████████████████████████████████████████████████| 5/5 [00:00<00:00, 11.66it/s] Parsing nodes: 100%|██████████████████████████████████████████████████████████████████████| 1/1 [00:00<00:00, 130.31it/s] Generating embeddings: 100%|███████████████████████████████████████████████████████████████| 4/4 [00:00<00:00, 9.57it/s] Parsing nodes: 100%|██████████████████████████████████████████████████████████████████████| 1/1 [00:00<00:00, 108.44it/s] Generating embeddings: 100%|███████████████████████████████████████████████████████████████| 4/4 [00:00<00:00, 9.51it/s] Parsing nodes: 100%|███████████████████████████████████████████████████████████████████████| 1/1 [00:00<00:00, 76.71it/s] Generating embeddings: 100%|███████████████████████████████████████████████████████████████| 6/6 [00:00<00:00, 10.05it/s] Parsing nodes: 100%|███████████████████████████████████████████████████████████████████████| 1/1 [00:00<00:00, 95.09it/s] Generating embeddings: 100%|███████████████████████████████████████████████████████████████| 5/5 [00:00<00:00, 11.96it/s] ``` ```output Enter your question: ``` + Try some questions if you have time. Press Ctrl + C to exit to OS prompt when you've completed your tests. ### Step 10. Review the populated data in the database + Connect to the designated TiDB Cloud Serverless instance and confirm the table `llama_index_rag_test` had been created under database `chatdb`. ```bash mysql \ --comments -u 'serverless_username' -p'serverless_password' \ -h serverless_host -P serverless_port \ --ssl-mode=VERIFY_IDENTITY --ssl-ca=/etc/pki/tls/certs/ca-bundle.crt \ -D chatdb ``` ```sql USE chatdb; SHOW TABLES; ``` ```output +----------------------+ | Tables_in_chatdb | +----------------------+ | llama_index_rag_test | +----------------------+ 1 row in set (0.00 sec) ``` + Verify the table schema. Note that column `embedding` is the vector embedding of column `document`, and there is a `Cosine Distance` index on column `embedding`. ```sql SHOW CREATE TABLE llama_index_rag_test\G ``` ```output *************************** 1. row *************************** Table: llama_index_rag_test Create Table: CREATE TABLE `llama_index_rag_test` ( `id` varchar(36) NOT NULL, `embedding` vector(1536) NOT NULL COMMENT 'hnsw(distance=cosine)', `document` text DEFAULT NULL, `meta` json DEFAULT NULL, `create_time` datetime DEFAULT CURRENT_TIMESTAMP, `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */ ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin 1 row in set (0.01 sec) ``` + Verify columns `document` and `embedding` have been populated for table `llama_index_rag_test`. The length of the documentation may change overtime. Thus, the number of documents shown here might be different from your results. ```sql SELECT COUNT(DISTINCT document), COUNT(DISTINCT embedding) FROM llama_index_rag_test; ``` ```output +--------------------------+---------------------------+ | COUNT(DISTINCT document) | COUNT(DISTINCT embedding) | +--------------------------+---------------------------+ | 99 | 99 | +--------------------------+---------------------------+ 1 row in set (0.02 sec) ``` + Exit to OS prompt. ```sql EXIT ``` ```output Bye ``` ### Module 3 Summary We are not done yet. In the upcoming practice session, you will utilize LLM to assist in constructing a Knowledge Graph. Subsequently, you will leverage this Knowledge Graph to enhance the chat program, transitioning from a basic RAG to a Graph RAG. ## Module 4: Building A Simple GraphRAG Chatbot Through previous practices, you have learned how using RAG to retrieve relevant information from external knowledge sources empowers large language models to answer questions from previously unseen document collections. However, RAG falls short when handling broad questions directed at an entire text corpus. Use a knowledge graph can upgrade your RAG application from naive RAG to Graph RAG to get the most relevant knowledge instead of finding the most similar text for LLM prompt enhancement. In this practice, an OpenAI API KEY supports `gpt-4o` is required. ![48%](https://lab-static.pingcap.com/quick-demo/TiDBAsGraphStoreForRAGDetails_aa_001_20240610.png) ### Tips + In this practice you will use an open-source, Python-based implementation of Graph RAG approach to build the knowledge Graph about "TiDB" from Wiki pages in TiDB database `chatdb`. + The Python-based implementation uses two tables in TiDB to represent the Graph, `entities` and `relationships`. The knowledge documents and their vector embeddings are stored in the `entities` table. + An embedding-based vector search serves as the entry point into the graph, while `JOIN` operations traverse within the graph to retrieve the most relevant knowledge nodes. ### Step 1. Connect to TiDB Cloud Serverless database + Connect to the designated TiDB Cloud Serverless instance and make sure it has a database called `chatdb`. ```bash mysql \ --comments -u 'serverless_username' -p'serverless_password' \ -h serverless_host -P serverless_port \ --ssl-mode=VERIFY_IDENTITY --ssl-ca=/etc/pki/tls/certs/ca-bundle.crt ``` ```sql DROP DATABASE IF EXISTS chatdb; CREATE DATABASE chatdb; EXIT ``` ### Step 2. Prepare the application environment + Install the required Python modules. ```bash pip install \ click==8.1.7 \ PyMySQL==1.1.0 \ SQLAlchemy==2.0.30 \ tidb-vector==0.0.14 \ pydantic==2.7.1 pydantic_core==2.18.2 \ dspy-ai==2.4.9 \ langchain-community==0.2.0 \ wikipedia==1.4.0 \ pyvis==0.3.1 \ openai==1.27.0 \ httpx==0.27.2 ``` ```output ... Successfully installed Mako-1.3.6 PyMySQL-1.1.0 SQLAlchemy-2.0.30 aiohappyeyeballs-2.4.3 aiohttp-3.11.0 aiosignal-1.3.1 alembic-1.14.0 annotated-types-0.7.0 anyio-4.6.2.post1 asttokens-2.4.1 async-timeout-4.0.3 backoff-2.2.1 beautifulsoup4-4.12.3 certifi-2024.8.30 click-8.1.7 colorlog-6.9.0 dataclasses-json-0.6.7 datasets-2.14.7 decorator-5.1.1 dill-0.3.7 distro-1.9.0 dspy-ai-2.4.9 exceptiongroup-1.2.2 executing-2.1.0 filelock-3.16.1 frozenlist-1.5.0 fsspec-2023.10.0 greenlet-3.1.1 h11-0.14.0 httpcore-1.0.6 httpx-0.27.2 huggingface-hub-0.26.2 ipython-8.18.1 jedi-0.19.2 joblib-1.3.2 jsonpatch-1.33 jsonpickle-4.0.0 langchain-0.2.17 langchain-community-0.2.0 langchain-core-0.2.43 langchain-text-splitters-0.2.4 langsmith-0.1.143 marshmallow-3.23.1 matplotlib-inline-0.1.7 multidict-6.1.0 multiprocess-0.70.15 mypy-extensions-1.0.0 networkx-3.2.1 numpy-1.26.4 openai-1.27.0 optuna-4.1.0 orjson-3.10.11 packaging-24.2 pandas-2.2.3 parso-0.8.4 pexpect-4.9.0 prompt-toolkit-3.0.48 propcache-0.2.0 ptyprocess-0.7.0 pure-eval-0.2.3 pyarrow-18.0.0 pyarrow-hotfix-0.6 pydantic-2.7.1 pydantic-core-2.18.2 pygments-2.18.0 python-dateutil-2.9.0.post0 pyvis-0.3.1 regex-2024.11.6 requests-toolbelt-1.0.0 sniffio-1.3.1 soupsieve-2.6 stack-data-0.6.3 structlog-24.4.0 tenacity-8.5.0 tidb-vector-0.0.14 tqdm-4.67.0 traitlets-5.14.3 typing-extensions-4.12.2 typing-inspect-0.9.0 tzdata-2024.2 ujson-5.10.0 wcwidth-0.2.13 wikipedia-1.4.0 xxhash-3.5.0 yarl-1.17.1 ``` + Setup an [OpenAI API key](https://help.openai.com/en/articles/4936850-where-do-i-find-my-openai-api-key) in the environemnt. Replace the `${OPENAI_API_KEY}` with the actual value, and execute the following command on the remote Linux. You can skip this step if you had completed the setting in the previous practice. ```bash export OPENAI_API_KEY=${OPENAI_API_KEY} echo $OPENAI_API_KEY ``` ### Step 3. Build the knowledge graph for the chatbot + Build the Knowledge Graph from the Wikipedia Loader query of "TiDB". + To create a Python script named `build-graph.py`, execute the following command. You can click on the code area below to copy the entire `cat` command effortlessly. ```bash cat > build-graph.py <<'EOF' import os import pymysql import dspy import enum import openai import tidb_vector from pymysql import Connection from pymysql.cursors import DictCursor from dspy.functional import TypedPredictor from pydantic import BaseModel, Field from typing import Mapping, Any, Optional, List from langchain_community.document_loaders import WikipediaLoader from pyvis.network import Network from IPython.display import HTML from sqlalchemy import ( Column, Integer, String, Text, JSON, ForeignKey, BLOB, Enum as SQLEnum, DateTime, URL, create_engine, or_, ) from sqlalchemy.orm import relationship, Session, declarative_base, joinedload from tidb_vector.sqlalchemy import VectorType, VectorAdaptor class Entity(BaseModel): """List of entities extracted from the text to form the knowledge graph""" name: str = Field( description="Name of the entity, it should be a clear and concise term" ) description: str = Field( description=( "Description of the entity, it should be a complete and comprehensive sentence, not few words. " "Sample description of entity 'TiDB in-place upgrade': " "'Upgrade TiDB component binary files to achieve upgrade, generally use rolling upgrade method'" ) ) class Relationship(BaseModel): """List of relationships extracted from the text to form the knowledge graph""" source_entity: str = Field( description="Source entity name of the relationship, it should an existing entity in the Entity list" ) target_entity: str = Field( description="Target entity name of the relationship, it should an existing entity in the Entity list" ) relationship_desc: str = Field( description=( "Description of the relationship, it should be a complete and comprehensive sentence, not few words. " "Sample relationship description: 'TiDB will release a new LTS version every 6 months.'" ) ) class KnowledgeGraph(BaseModel): """Graph representation of the knowledge for text.""" entities: List[Entity] = Field( description="List of entities in the knowledge graph" ) relationships: List[Relationship] = Field( description="List of relationships in the knowledge graph" ) class ExtractGraphTriplet(dspy.Signature): text = dspy.InputField( desc="a paragraph of text to extract entities and relationships to form a knowledge graph" ) knowledge: KnowledgeGraph = dspy.OutputField( desc="Graph representation of the knowledge extracted from the text." ) class Extractor(dspy.Module): def __init__(self): super().__init__() self.prog_graph = TypedPredictor(ExtractGraphTriplet) def forward(self, text): return self.prog_graph( text=text, config={ "response_format": {"type": "json_object"}, }, ) def interactive_graph(kg: KnowledgeGraph) -> str: net = Network(notebook=True, cdn_resources="remote") node_map = {} for index in range(len(kg.entities)): node_map[kg.entities[index].name] = index net.add_node( index, label=kg.entities[index].name, title=kg.entities[index].description ) for index in range(len(kg.relationships)): relation = kg.relationships[index] src_index = node_map[relation.source_entity] target_index = node_map[relation.target_entity] net.add_edge(src_index, target_index) filename = "kg.html" net.save_graph(filename) return filename def get_query_embedding(query: str): open_ai_client = openai.OpenAI(api_key=os.getenv("OPENAI_API_KEY")) response = open_ai_client.embeddings.create( input=[query], model="text-embedding-3-small" ) return response.data[0].embedding def generate_result(query: str, entities, relationships): open_ai_client = openai.OpenAI(api_key=os.getenv("OPENAI_API_KEY")) entities_prompt = "\n".join( map(lambda e: f'(Name: "{e.name}", Description: "{e.description}")', entities) ) relationships_prompt = "\n".join( map(lambda r: f'"{r.relationship_desc}"', relationships) ) response = open_ai_client.chat.completions.create( model="gpt-4o", messages=[ { "role": "system", "content": "Please carefully think the user's " + "question and ONLY use the content below to generate answer:\n" + f"Entities: {entities_prompt}, Relationships: {relationships_prompt}", }, {"role": "user", "content": query}, ], ) return response.choices[0].message.content def get_db_url(): return URL( drivername="mysql+pymysql", username="serverless_username", password="serverless_password", host="serverless_host", port=serverless_port, database="chatdb", query={"ssl_verify_cert": True, "ssl_verify_identity": True}, ) engine = create_engine(get_db_url(), pool_recycle=300) Base = declarative_base() class DatabaseEntity(Base): id = Column(Integer, primary_key=True) name = Column(String(512)) description = Column(Text) description_vec = Column(VectorType(1536)) __tablename__ = "entities" class DatabaseRelationship(Base): id = Column(Integer, primary_key=True) source_entity_id = Column(Integer, ForeignKey("entities.id")) target_entity_id = Column(Integer, ForeignKey("entities.id")) relationship_desc = Column(Text) source_entity = relationship("DatabaseEntity", foreign_keys=[source_entity_id]) target_entity = relationship("DatabaseEntity", foreign_keys=[target_entity_id]) __tablename__ = "relationships" def clean_knowledge_graph(kg: KnowledgeGraph) -> KnowledgeGraph: entity_name_set = set(map(lambda e: e.name, kg.entities)) kg.relationships = list( filter( lambda r: r.source_entity in entity_name_set and r.target_entity in entity_name_set, kg.relationships, ) ) return kg def save_knowledge_graph(kg: KnowledgeGraph): data_entities = list( map( lambda e: DatabaseEntity( name=e.name, description=e.description, description_vec=get_query_embedding(e.description), ), kg.entities, ) ) with Session(engine) as session: session.add_all(data_entities) session.flush() entity_id_map = dict(map(lambda e: (e.name, e.id), data_entities)) print(entity_id_map) data_relationships = list( map( lambda r: DatabaseRelationship( source_entity_id=entity_id_map[r.source_entity], target_entity_id=entity_id_map[r.target_entity], relationship_desc=r.relationship_desc, ), kg.relationships, ) ) session.add_all(data_relationships) session.commit() def retrieve_entities_relationships(question_embedding) -> (List[DatabaseEntity], List[DatabaseRelationship]): with Session(engine) as session: entity = ( session.query(DatabaseEntity) .order_by( DatabaseEntity.description_vec.cosine_distance(question_embedding) ) .limit(1) .first() ) entities = {entity.id: entity} relationships = ( session.query(DatabaseRelationship) .options( joinedload(DatabaseRelationship.source_entity), joinedload(DatabaseRelationship.target_entity), ) .filter( or_( DatabaseRelationship.source_entity == entity, DatabaseRelationship.target_entity == entity, ) ) ) for r in relationships: entities.update( { r.source_entity.id: r.source_entity, r.target_entity.id: r.target_entity, } ) return entities.values(), relationships extractor = Extractor() Base.metadata.create_all(engine) # Create Index VectorAdaptor(engine).create_vector_index( DatabaseEntity.description_vec, tidb_vector.DistanceMetric.COSINE, skip_existing=True # For L2 distance, use tidb_vector.DistanceMetric.L2 ) open_ai_client = dspy.OpenAI( model="gpt-4o", api_key=os.getenv("OPENAI_API_KEY"), max_tokens=4096 ) dspy.settings.configure(lm=open_ai_client) wiki = WikipediaLoader(query="TiDB").load() pred = extractor(text=wiki[0].page_content) knowledge_graph = clean_knowledge_graph(pred.knowledge) interactive_graph(knowledge_graph) save_knowledge_graph(knowledge_graph) EOF ls -l build-graph.py ``` ```output -rw-r--r--. 1 rocky rocky 8777 Jun 20 15:02 build-graph.py ``` + Run the Python script `build-graph.py` to build the Graph. It will create `entities` and `relationships` tables under database `chatdb`. Wait for the script to complete. It should take about 2 minutes. Your output may not necessarily be identical to the displayed output. ```bash python build-graph.py ``` ```output {'TiDB': 90001, 'PingCAP': 90002, 'Apache 2.0': 90003, "Google's Spanner and F1 papers": 90004, 'TiDB 7.5 GA': 90005, 'TiDB 7.1 GA': 90006, 'TiDB 6.0 GA': 90007, 'TiDB 5.0 GA': 90008, 'TiDB 4.0 GA': 90009, 'TiDB 3.0 GA': 90010, 'TiDB 2.0 GA': 90011, 'TiDB 1.0 GA': 90012, 'Horizontal scalability': 90013, 'MySQL compatibility': 90014, 'Distributed transactions with strong consistency': 90015, 'Cloud native': 90016, 'TiKV': 90017, 'Real-time HTAP': 90018, 'TiFlash': 90019, 'High availability': 90020, 'Raft consensus algorithm': 90021, 'Kubernetes with Operator': 90022, 'TiDB Operator': 90023, 'TiUP': 90024, 'TiDB Ansible': 90025, 'Docker': 90026, 'TiDB Data Migration (DM)': 90027, 'Backup & Restore (BR)': 90028, 'Dumpling': 90029} ``` ### Step 4. Check the knowledge graph you have built + Connect to `chatdb` database. ```bash mysql \ --comments -u 'serverless_username' -p'serverless_password' \ -h serverless_host -P serverless_port \ --ssl-mode=VERIFY_IDENTITY --ssl-ca=/etc/pki/tls/certs/ca-bundle.crt \ -D chatdb ``` ```output mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2168969 Server version: 5.7.28-TiDB-Serverless TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible Copyright (c) 2000, 2024, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> ``` + Review the table `chatdb.entities` populated by the Python script. Note that the HNSW index was declared on column `description_vec`. Note that your output may not necessarily be identical to the displayed output. ```sql SHOW CREATE TABLE entities\G ``` ```output *************************** 1. row *************************** Table: entities Create Table: CREATE TABLE `entities` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(512) DEFAULT NULL, `description` text DEFAULT NULL, `description_vec` vector(1536) DEFAULT NULL COMMENT 'HNSW(distance=cosine)', PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */, VECTOR INDEX `vec_idx_description_vec` ((VEC_COSINE_DISTANCE(`description_vec`))) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=60001 1 row in set (0.02 sec) ``` ```sql SELECT id, name, SUBSTR(description, 1,30) AS desc_head, VEC_DIMS(description_vec) FROM entities ORDER BY name; ``` ```output +----+--------------------------------------------------+--------------------------------+---------------------------+ | id | name | desc_head | VEC_DIMS(description_vec) | +----+--------------------------------------------------+--------------------------------+---------------------------+ | 3 | Apache 2.0 | Apache 2.0 is the license unde | 1536 | | 26 | Backup & Restore (BR) | Backup & Restore (BR) is a dis | 1536 | | 31 | Cloud Native Computing Foundation (CNCF) | The Cloud Native Computing Fou | 1536 | | 18 | Cloud native | TiDB is designed to work in th | 1536 | | 17 | Distributed transactions with strong consistency | TiDB internally shards a table | 1536 | | 24 | Docker | Docker can be used to deploy T | 1536 | | 4 | Google's Spanner and F1 papers | Google's Spanner and F1 papers | 1536 | | 20 | High availability | TiDB uses the Raft consensus a | 1536 | | 15 | Horizontal scalability | TiDB can expand both SQL proce | 1536 | | 21 | Kubernetes with Operator | TiDB can be deployed in a Kube | 1536 | | 16 | MySQL compatibility | TiDB acts like it is a MySQL 8 | 1536 | | 2 | PingCAP | PingCAP is the primary develop | 1536 | | 29 | Raft consensus algorithm | The Raft consensus algorithm i | 1536 | | 19 | Real-time HTAP | TiDB supports both online tran | 1536 | | 1 | TiDB | TiDB is an open-source NewSQL | 1536 | | 14 | TiDB 1.0 GA | TiDB 1.0 GA was released on Oc | 1536 | | 13 | TiDB 2.0 GA | TiDB 2.0 GA was released on Ap | 1536 | | 12 | TiDB 3.0 GA | TiDB 3.0 GA was released on Ju | 1536 | | 11 | TiDB 4.0 GA | TiDB 4.0 GA was released on Ma | 1536 | | 10 | TiDB 5.0 GA | TiDB 5.0 GA was released on Ap | 1536 | | 9 | TiDB 6.0 GA | TiDB 6.0 GA was released on Ap | 1536 | | 8 | TiDB 7.1 GA | TiDB 7.1 GA was released on Ma | 1536 | | 7 | TiDB 7.5 GA | TiDB 7.5 GA was released on De | 1536 | | 6 | TiDB 8.1 GA | TiDB 8.1 GA was released on Ma | 1536 | | 5 | TiDB 8.5 GA | TiDB 8.5 GA was released on De | 1536 | | 23 | TiDB Ansible | TiDB can be deployed using Ans | 1536 | | 25 | TiDB Data Migration (DM) | TiDB Data Migration (DM) is su | 1536 | | 30 | TiDB Operator | TiDB Operator is a method of p | 1536 | | 28 | TiFlash | TiFlash is a columnstore stora | 1536 | | 27 | TiKV | TiKV is the storage layer of T | 1536 | | 22 | TiUP | TiDB 4.0 introduces TiUP, a cl | 1536 | +----+--------------------------------------------------+--------------------------------+---------------------------+ 31 rows in set (0.34 sec) ``` + Review the table `chatdb.relationships` schema, and fetch relationships from table `entities` and `relationshipts` joins. Note that your output may not necessarily be identical to the displayed output. ```sql SHOW CREATE TABLE relationships\G ``` ```output *************************** 1. row *************************** Table: relationships Create Table: CREATE TABLE `relationships` ( `id` int(11) NOT NULL AUTO_INCREMENT, `source_entity_id` int(11) DEFAULT NULL, `target_entity_id` int(11) DEFAULT NULL, `relationship_desc` text DEFAULT NULL, PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */, KEY `fk_1` (`source_entity_id`), KEY `fk_2` (`target_entity_id`), CONSTRAINT `fk_1` FOREIGN KEY (`source_entity_id`) REFERENCES `chatdb`.`entities` (`id`), CONSTRAINT `fk_2` FOREIGN KEY (`target_entity_id`) REFERENCES `chatdb`.`entities` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=60001 1 row in set (0.00 sec) ``` ```sql SELECT source.name AS src_name, SUBSTR(r.relationship_desc, 1, 50) relation_head, target.name AS tgt_name FROM entities AS source, entities AS target, relationships as r WHERE source.id = r.source_entity_id AND target.id = r.target_entity_id ORDER BY src_name; ``` ```output +--------------------------+----------------------------------------------------+--------------------------------------------------+ | src_name | relation_head | tgt_name | +--------------------------+----------------------------------------------------+--------------------------------------------------+ | Raft consensus algorithm | The Raft consensus algorithm is used by TiDB to en | High availability | | TiDB | TiDB acts like it is a MySQL 8.0 server to applica | MySQL compatibility | | TiDB | TiDB supports both online transaction processing ( | Real-time HTAP | | TiDB | Dumpling is a data export tool for TiDB. | Dumpling | | TiDB | Backup & Restore (BR) is a distributed backup and | Backup & Restore (BR) | | TiDB | TiDB Data Migration (DM) is a tool for replicating | TiDB Data Migration (DM) | | TiDB | Docker can be used to deploy TiDB in a containeriz | Docker | | TiDB | TiDB can be deployed using Ansible with a TiDB Ans | TiDB Ansible | | TiDB | TiUP is a cluster operation and maintenance tool i | TiUP | | TiDB | TiDB can be deployed in a Kubernetes-enabled cloud | Kubernetes with Operator | | TiDB | TiDB uses the Raft consensus algorithm to ensure d | High availability | | TiDB | TiDB can expand both SQL processing and storage ca | Horizontal scalability | | TiDB | TiDB acts like it is a MySQL 8.0 server to applica | MySQL compatibility | ... | TiDB 5.0 GA | TiDB 5.0 GA was released on April 7, 2021. | TiDB | | TiDB 5.0 GA | TiDB 5.0 GA was released on April 7, 2021. | TiDB | | TiDB 6.0 GA | TiDB 6.0 GA was released on April 7, 2022. | TiDB | | TiDB 6.0 GA | TiDB 6.0 GA was released on April 7, 2022. | TiDB | | TiDB 6.0 GA | TiDB 6.0 GA was released on April 7, 2022. | TiDB | | TiDB 7.1 GA | TiDB 7.1 GA was released on May 31, 2023. | TiDB | | TiDB 7.1 GA | TiDB 7.1 GA was released on May 31, 2023. | TiDB | | TiDB 7.1 GA | TiDB 7.1 GA was released on May 31, 2023. | TiDB | | TiDB 7.5 GA | TiDB 7.5 GA was released on December 1, 2023. | TiDB | | TiDB 7.5 GA | TiDB 7.5 GA was released on December 1, 2023. | TiDB | | TiDB 7.5 GA | TiDB 7.5 GA was released on December 1, 2023. | TiDB | | TiDB Operator | TiDB Operator was originally developed by PingCAP | PingCAP | | TiDB Operator | TiDB Operator was originally developed by PingCAP | PingCAP | | TiDB Operator | TiDB Operator is a method of packaging, deploying, | Kubernetes with Operator | | TiFlash | TiFlash is a columnstore storage engine used by Ti | Real-time HTAP | | TiKV | TiKV, the storage layer of TiDB, became a Cloud Na | Cloud native | +--------------------------+----------------------------------------------------+--------------------------------------------------+ 81 rows in set (0.01 sec) ``` + Exit to OS prompt on remote Linux. ```sql EXIT ``` ### Step 5. Create the GraphRAG chatbot program + Create the Python script `test-graph.py`. ```bash cat > test-graph.py <<'EOF' import openai import os import click from sqlalchemy import ( Column, Integer, String, Text, ForeignKey, URL, create_engine, or_, ) from typing import Mapping, Any, Optional, List from sqlalchemy.orm import relationship, Session, declarative_base, joinedload from tidb_vector.sqlalchemy import VectorType def get_db_url(): return URL( drivername="mysql+pymysql", username="serverless_username", password="serverless_password", host="serverless_host", port=serverless_port, database="chatdb", query={"ssl_verify_cert": True, "ssl_verify_identity": True}, ) engine = create_engine(get_db_url(), pool_recycle=300) Base = declarative_base() class DatabaseEntity(Base): id = Column(Integer, primary_key=True) name = Column(String(512)) description = Column(Text) description_vec = Column(VectorType(1536)) __tablename__ = "entities" class DatabaseRelationship(Base): id = Column(Integer, primary_key=True) source_entity_id = Column(Integer, ForeignKey("entities.id")) target_entity_id = Column(Integer, ForeignKey("entities.id")) relationship_desc = Column(Text) source_entity = relationship("DatabaseEntity", foreign_keys=[source_entity_id]) target_entity = relationship("DatabaseEntity", foreign_keys=[target_entity_id]) __tablename__ = "relationships" def get_query_embedding(query: str): open_ai_client = openai.OpenAI(api_key=os.getenv("OPENAI_API_KEY")) response = open_ai_client.embeddings.create( input=[query], model="text-embedding-3-small" ) return response.data[0].embedding def retrieve_entities_relationships( question_embedding, ) -> (List[DatabaseEntity], List[DatabaseRelationship]): with Session(engine) as session: entity = ( session.query(DatabaseEntity) .order_by( DatabaseEntity.description_vec.cosine_distance(question_embedding) ) .limit(1) .first() ) entities = {entity.id: entity} relationships = ( session.query(DatabaseRelationship) .options( joinedload(DatabaseRelationship.source_entity), joinedload(DatabaseRelationship.target_entity), ) .filter( or_( DatabaseRelationship.source_entity == entity, DatabaseRelationship.target_entity == entity, ) ) ) for r in relationships: entities.update( { r.source_entity.id: r.source_entity, r.target_entity.id: r.target_entity, } ) return entities.values(), relationships def generate_result(query: str, entities, relationships): open_ai_client = openai.OpenAI(api_key=os.getenv("OPENAI_API_KEY")) entities_prompt = "\n".join( map(lambda e: f'(Name: "{e.name}", Description: "{e.description}")', entities) ) relationships_prompt = "\n".join( map(lambda r: f'"{r.relationship_desc}"', relationships) ) response = open_ai_client.chat.completions.create( model="gpt-4o", messages=[ { "role": "system", "content": "Please carefully think the user's " + "question and ONLY use the content below to generate answer:\n" + f"Entities: {entities_prompt}, Relationships: {relationships_prompt}", }, {"role": "user", "content": query}, ], ) return response.choices[0].message.content @click.command() def start_chat(): while True: question = click.prompt("Enter your question") question_embedding = get_query_embedding(question) entities, relationships = retrieve_entities_relationships(question_embedding) result = generate_result(question, entities, relationships) click.echo(result) if __name__ == '__main__': start_chat() EOF ls -l test-graph.py ``` ```output -rw-r--r--. 1 rocky rocky 4154 Jun 20 15:14 test-graph.py ``` ### Step 6. Run the GraphRAG chatbot program + Run the script `test-graph.py`. ```bash python test-graph.py ``` ```output Enter your question: ``` + Try question 1, the knowledge is NOT in our TiDB Knowledge Graph: "What is the relationship between raccoon and umbrella?" ```bash What is the relationship between raccoon and umbrella? ``` ```output Enter your question: What is the relationship between raccoon and umbrella? The provided information does not contain any details about raccoons or umbrellas. Therefore, I can't provide a relationship between these two based on the given content. ``` + Try question 2, the knowledge is covered in our TiDB Knowledge Graph: "What is the relationship between PD and Raft?" ```bash What is the relationship between PD and Raft? ``` ```output Enter your question: What is the relationship between PD and Raft? The Raft consensus algorithm is used by TiDB to ensure data availability and replication. However, the relationship specifically between PD and Raft is not detailed in the provided content. Generally speaking, in TiDB, PD (Placement Driver) handles metadata management and scheduling, and works in conjunction with the Raft consensus algorithm to maintain high availability and data consistency through leader election and automatic failover mechanisms. ``` + Try question 3, the knowledge is covered in our TiDB Knowledge Graph: "Which tools or solutions are most relevant for migrating to TiDB?" ```bash Which tools or solutions are most relevant for migrating to TiDB? ``` ```output Enter your question: Which tools or solutions are most relevant for migrating to TiDB? The most relevant tool for migrating to TiDB is **TiDB Data Migration (DM)**. This tool is specifically designed for replicating data from sharded MySQL or MariaDB tables to TiDB, facilitating near real-time analytical workloads. ``` + Try the SAME question you asked in Module 3. This time you are using Graph RAG instead of naive RAG, compare two anwsers, which one is better for you? Note that your output may not necessarily be identical to the displayed output. ```bash How to migrate to TiDB from MySQL? ``` ```output Enter your question: How to migrate to TiDB from MySQL? To migrate to TiDB from MySQL, you can use TiDB Data Migration (DM). TiDB DM is a tool designed specifically for replicating data from sharded MySQL or MariaDB tables to TiDB, allowing near real-time analytical workloads. Here are the general steps to perform the migration: 1. **Install TiDB DM**: Set up TiDB Data Migration by following the official installation guide provided by PingCAP. 2. **Configure Source Database**: Prepare your MySQL or MariaDB databases with the necessary configurations and privileges required for data replication. 3. **Deploy and Configure DM Workers and Master**: Deploy TiDB DM components, including DM workers and the DM master. Configure these components by defining the source database configurations, replication tasks, and target TiDB cluster details. 4. **Create Data Migration Tasks**: Define and create data migration tasks that detail what data from the source databases should be replicated to TiDB. This includes sharding rules if applicable. 5. **Start Data Migration**: Initiate the data migration tasks to begin replicating data from the MySQL or MariaDB source tables to TiDB. 6. **Monitor and Manage**: Monitor the migration process using provided tools and logs to ensure it progresses as expected. Make adjustments and handle any issues that arise. By following these steps with TiDB Data Migration (DM), you can efficiently migrate your data from MySQL to TiDB while maintaining data integrity and allowing for real-time workload processing on TiDB. For detailed instructions and best practices, refer to the official TiDB documentation. ``` + Try more questions if you have time. Press Ctrl + C to exit to OS prompt. + Log out from the remote Linux. ```bash exit ``` ## Conclusion In TiDB, you can create and utilize Vector Search Indexes for such approximate nearest neighbor (ANN) searches. By using Vector Search Indexes, vector search queries could be finished in milliseconds, to empower your RAG or Graph RAG applications. Furthermore, opting for the distributed SQL database TiDB as your vector store grants you instant access to its inherent advantages: limitless scalability for accommodating unlimited vectors (up to a maximum dimension of 2048, in our example the embedded documents are 1536 dimensions), ensuring continuous availability and uninterrupted performance. Please check the other labs for more details. See you in the next lab, Goodbye! ```hbsptform { "ui": { "titleLevel": 3 }, "portalId": "4466002", "formId": "89fa8368-eba1-4b84-b91e-eb9eb6613ddf", "region": "na1", "sfdcCampaignId": "701RC00000VqCODYA3" } ```