8000 updated scripts to be easier to use · yang-jiayi/azure-sql-db-openai@e897643 · GitHub
[go: up one dir, main page]
Skip to content

Commit e897643

Browse files
yorekyorek
committed
updated scripts to be easier to use
1 parent d36608c commit e897643

9 files changed

+61
-37
lines changed

‎README.md

Lines 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

Lines 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

Lines 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

Lines 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

Lines 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

Lines 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

Lines changed: 0 additions & 14 deletions
This file was deleted.

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

File renamed without changes.

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

Lines 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