Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

Commit e897643

Browse filesBrowse files
yorekyorek
committed
updated scripts to be easier to use
1 parent d36608c commit e897643
Copy full SHA for e897643

9 files changed

+61
-37
lines changed

‎README.md

Copy file name to clipboardExpand all lines: README.md
+2-2Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -61,14 +61,14 @@ The third script `./vector-embeddings/03-find-similar-articles.sql` starts invok
6161

6262
Make sure to have an Azure OpenAI [embeddings model](https://learn.microsoft.com/azure/cognitive-services/openai/concepts/models#embeddings-models) deployed and make sure it is using the `text-embedding-ada-002` model.
6363

64-
Once the Azure OpenAI model is deployed, it can be called from Azure SQL database using [sp_invoke_external_rest_endpoint](https://learn.microsoft.com/sql/relational-databases/system-stored-procedures/sp-invoke-external-rest-endpoint-transact-sql), to get the embedding vector for the "the foundation series by isaac asimov", text, for example, using the following code (make sure to replace the <api-key> with yout Azure OpenAI deployment):
64+
Once the Azure OpenAI model is deployed, it can be called from Azure SQL database using [sp_invoke_external_rest_endpoint](https://learn.microsoft.com/sql/relational-databases/system-stored-procedures/sp-invoke-external-rest-endpoint-transact-sql), to get the embedding vector for the "the foundation series by isaac asimov", text, for example, using the following code (make sure to replace the `<your-api-name>` and `<api-key>` with yout Azure OpenAI deployment):
6565

6666
```sql
6767
declare @inputText nvarchar(max) = 'the foundation series by isaac asimov';
6868
declare @retval int, @response nvarchar(max);
6969
declare @payload nvarchar(max) = json_object('input': @inputText);
7070
exec @retval = sp_invoke_external_rest_endpoint
71-
@url = 'https://<your-app-name>.openai.azure.com/openai/deployments/<deployment-id>/embeddings?api-version=2023-03-15-preview',
71+
@url = 'https://<your-api-name>.openai.azure.com/openai/deployments/<deployment-id>/embeddings?api-version=2023-03-15-preview',
7272
@method = 'POST',
7373
@headers = '{"api-key":"<api-key>"}',
7474
@payload = @payload,

‎vector-embeddings/01-import-wikipedia.sql

Copy file name to clipboardExpand all lines: vector-embeddings/01-import-wikipedia.sql
+4Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -82,4 +82,8 @@ go
8282
select top (100) * from [dbo].[wikipedia_articles_embeddings]
8383
go
8484

85+
select * from [dbo].[wikipedia_articles_embeddings] where title = 'Alan Turing'
86+
go
87+
88+
8589

‎vector-embeddings/02-create-vectors-table.sql

Copy file name to clipboardExpand all lines: vector-embeddings/02-create-vectors-table.sql
+2-2Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -53,10 +53,10 @@ go
5353
*/
5454
create clustered columnstore index ixc
5555
on dbo.wikipedia_articles_embeddings_titles_vector
56-
order (article_id);
56+
order (article_id)
5757
go
5858

5959
create clustered columnstore index ixc
6060
on dbo.wikipedia_articles_embeddings_contents_vector
61-
order (article_id);
61+
order (article_id)
6262
go

‎vector-embeddings/03-store-openai-credentials.sql

Copy file name to clipboard
+10Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,10 @@
1+
/*
2+
Create database credentials to store API key
3+
*/
4+
if exists(select * from sys.[database_scoped_credentials] where name = 'https://<your-app-name>.openai.azure.com')
5+
begin
6+
drop database scoped credential [https://<your-app-name>.openai.azure.com];
7+
end
8+
create database scoped credential [https://<your-app-name>.openai.azure.com]
9+
with identity = 'HTTPEndpointHeaders', secret = '{"api-key": "<api-key>"}';
10+
go

‎vector-embeddings/04-create-get-embeddings-procedure.sql

Copy file name to clipboard
+28Lines changed: 28 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,28 @@
1+
/*
2+
Get the embeddings for the input text by calling the OpenAI API
3+
*/
4+
create or alter procedure dbo.get_embedding
5+
@deployedModelName nvarchar(1000),
6+
@inputText nvarchar(max),
7+
@embedding nvarchar(max) output
8+
as
9+
declare @retval int, @response nvarchar(max);
10+
declare @payload nvarchar(max) = json_object('input': @inputText);
11+
declare @url nvarchar(1000) = 'https://<your-app-name>.openai.azure.com/openai/deployments/' + @deployedModelName + '/embeddings?api-version=2023-03-15-preview'
12+
exec @retval = sp_invoke_external_rest_endpoint
13+
@url = @url,
14+
@method = 'POST',
15+
@credential = [https://<your-app-name>],
16+
@payload = @payload,
17+
@response = @response output;
18+
19+
declare @re nvarchar(max) = '[]';
20+
if (@retval = 0) begin
21+
set @re = json_query(@response, '$.result.data[0].embedding')
22+
end
23+
24+
set @embedding = @re;
25+
26+
return @retval
27+
go
28+

‎vector-embeddings/03-find-similar-articles.sql renamed to ‎vector-embeddings/05-find-similar-articles.sql

Copy file name to clipboardExpand all lines: vector-embeddings/05-find-similar-articles.sql
+5-19Lines changed: 5 additions & 19 deletions
Original file line numberDiff line numberDiff line change
@@ -1,31 +1,17 @@
1-
/*
2-
Create database credentials to store API key
3-
*/
4-
if exists(select * from sys.[database_scoped_credentials] where name = 'https://<your-app-name>.openai.azure.com')
5-
begin
6-
drop database scoped credential [https://<your-app-name>.openai.azure.com];
7-
end
8-
create database scoped credential [https://<your-app-name>.openai.azure.com]
9-
with identity = 'HTTPEndpointHeaders', secret = '{"api-key": "<api-key>"}';
10-
go
11-
121
/*
132
Get the embeddings for the input text by calling the OpenAI API
143
*/
154
declare @inputText nvarchar(max) = 'the foundation series by isaac asimov';
165
declare @retval int, @response nvarchar(max);
17-
declare @payload nvarchar(max) = json_object('input': @inputText);
18-
exec @retval = sp_invoke_external_rest_endpoint
19-
@url = 'https://<your-app-name>.openai.azure.com/openai/deployments/<deployment-id>?api-version=2023-03-15-preview',
20-
@method = 'POST',
21-
@credential = [https://<your-app-name>.openai.azure.com],
22-
@payload = @payload,
23-
@response = @response output;
6+
7+
exec @retval = dbo.get_embedding 'embeddings', @inputText, @response output;
8+
249
drop table if exists #response;
2510
select @response as [response] into #response;
2611
select * from #response;
2712
go
2813

14+
2915
/*
3016
Extract the title vectors from the JSON and store them in a table
3117
*/
@@ -81,7 +67,7 @@ go
8167

8268
/*
8369
Optimization: since vectors are normalized (as per OpenAI documentation: https://platform.openai.com/docs/guides/embeddings/which-distance-function-should-i-use),
84-
we can simplify the cosine distance calculation by removing magnitude calculation
70+
we can simplify the cosine distance calculation to a dot product
8571
*/
8672
drop table if exists #results;
8773
select top(50)

‎vector-embeddings/05-sample-function-usage.sql

Copy file name to clipboardExpand all lines: vector-embeddings/05-sample-function-usage.sql
-14Lines changed: 0 additions & 14 deletions
This file was deleted.

‎vector-embeddings/04-sample-function.sql renamed to ‎vector-embeddings/06-sample-function.sql

Copy file name to clipboard
File renamed without changes.

‎vector-embeddings/07-sample-function-usage.sql

Copy file name to clipboard
+10Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,10 @@
1+
/*
2+
Test the function
3+
*/
4+
declare @e nvarchar(max);
5+
declare @text nvarchar(max) = N'the foundation series by isaac asimov';
6+
7+
exec dbo.get_embedding 'embeddings', @text, @e output;
8+
9+
select * from dbo.SimilarContentArticles(@e) as r order by cosine_distance desc
10+
go

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.