Vectors and Embeddings<\/h2>\n
Vector databases are gaining quite a lot of interest lately. Using text embeddings and vector operations makes extremely easy to find similar \u201cthings\u201d. Things can be articles, photos, products…everything. As one can easily imagine, this ability is great to easily implement suggestions in applications. From providing suggestions on similar articles or other products that may be of interest, to quickly finding and grouping similar items, the applications are many.<\/p>\n
A great article to understand how embeddings work, is the following: Introducing text and code embeddings<\/a>.<\/p>\n Reading the mentioned articles, you can learn that “embeddings are numerical representations of concepts converted to number sequences, which make it easy for computers to understand the relationships between those concepts.”<\/p>\n More specifically, embeddings are vectors<\/em>…hence the great interest for vector databases.<\/p>\n But are vector databases really needed? At the end of the day a vector is just a list of numbers and finding if two vectors represent similar object is as easy as calculating the distance<\/em> between the vectors. One of the most common and useful distance metric is the cosine distance<\/a> and, even better, the related cosine similarity<\/a><\/p>\n The real complex part is calculating the embeddings, but thanks to Azure OpenAI, everyone has an easily accessible REST service that can used to get the embeddings using pre-trained ML models. In this article we will use OpenAI to generate vectors for doing similarity search<\/a> and then use Azure SQL database to store and search for similar vectors.<\/p>\n In this article we’ll build a sample solution to find Wikipedia articles that are related to any topic we may be interested in. As usual all the code is available in GitHub:<\/p>\n https:\/\/github.com\/Azure-Samples\/azure-sql-db-openai<\/a><\/p>\n The pre-calculated embeddings, both for the title and the body, of a selection of Wikipedia articles, is made available by OpenAI here:<\/p>\n https:\/\/cdn.openai.com\/API\/examples\/data\/vector_database_wikipedia_articles_embedded.zip<\/a><\/p>\n Vectors can be efficiently stored in Azure SQL database by columnstore indexes<\/a>. There is no specific data type available to store a vector in Azure SQL database, but we can use some human ingenuity to realize that a vector is just a list of numbers. As a result, we can store a vector in a table very easily by creating a column to contain vector data. One row per vector element. We can then use a columnstore index to efficiently store and search for vectors.<\/p>\n Using this Wikipedia article as starting point, you can see that there are two vectors, one to store title embeddings and one to store article embeddings:<\/p>\n The vectors can be more efficiently stored into a table like this:<\/p>\n On that table we can create a column store index to efficiently store and search for vectors. Then it is just a matter of calculating the distance between vectors to find the closest. Thanks to the internal optimization of the columnstore (that uses SIMD<\/a> AVX-512 instructions<\/a> to speed up vector operations) the distance calculation is extremely fast.<\/p>\n The most common distance is the cosine similarity, which can be calculated quite easily in SQL.<\/p>\n Cosine similarity<\/a> can be calculated in SQL using the following formula, given two vectors Really easy. What is now left to do is to query the Azure OpenAI REST service so that, given any text, we can get the vector representation of that text. Then we can use that vector to calculate the cosine distance against all<\/em> the wikipedia articles stored in the database and take only the closest ones which will return the article most likely connect to the topic we are interested in.<\/p>\n Create an Azure OpenAI resource via the Azure portal. For this specific sample you have to deploy an Embedding model using the The response is a vector of 1536 elements in JSON format. Vector values can be easily extracted using the following T-SQL code:<\/p>\n If you are interested in trying this amazing capability by yourself, you can find the source code here:<\/p>\n![\"text](\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/06\/text-embeddings.png\")
<\/p>\n![\"cosine](\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/06\/cosine-distance.png\")
<\/p>\nVectors in Azure SQL database<\/h2>\n
![\"Image](\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/06\/Screenshot-2023-06-03-164341.png\")
<\/a><\/p>\nCREATE TABLE [dbo].[wikipedia_articles_embeddings_titles_vector]\r\n(\r\n [article_id] [int] NOT NULL,\r\n [vector_value_id] [int] NOT NULL,\r\n [vector_value] [float] NOT NULL\r\n) \r\nGO<\/code><\/pre>\nCalculating cosine similarity<\/h2>\n
a<\/code> and b<\/code>:<\/p>\nSELECT \r\n SUM(a.value * b.value) \/ ( \r\n SQRT(SUM(a.value * a.value)) * SQRT(SUM(b.value * b.value)) \r\n ) AS cosine_similarity\r\nFROM\r\n vectors_values<\/code><\/pre>\nQuerying OpenAI<\/h2>\n
text-embedding-ada-002<\/code> model, the same used for the Wikipedia articles source we are using in this sample. Once that is done, you need to get the API KEY and the URL of the deployed model (read the Embeddings REST API<\/a> documentation) and then you can use sp_invoke_external_rest_endpoint<\/a> to call the REST API from Azure SQL database.<\/p>\ndeclare @retval int, @response nvarchar(max);\r\ndeclare @payload nvarchar(max) = json_object('input': 'Isaac Asimov');\r\n\r\nexec @retval = sp_invoke_external_rest_endpoint\r\n @url = 'https:\/\/<your-app-name>.openai.azure.com\/openai\/deployments\/<deployment-id>\/embeddings?api-version=2023-03-15-preview',\r\n @method = 'POST',\r\n @headers = '{\"api-key\":\"<your api key>\"}',\r\n @payload = @payload,\r\n @response = @response output;<\/code><\/pre>\nselect [key] as value_id, [value] from openjson(@response, '$.result.data[0].embedding')<\/code><\/pre>\nSource code<\/h2>\n
![\"\"](\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2023\/06\/azure-sql-cosine-similarity.gif\")
<\/p>\n","protected":false},"excerpt":{"rendered":"