Bubby2015
diff --git a/‎python/00-setup-database.sql
Lines changed: 7 additions & 58 deletions b/‎python/00-setup-database.sql
Lines changed: 7 additions & 58 deletions
diff --git a/‎python/hybrid_search.py
Lines changed: 45 additions & 19 deletions b/‎python/hybrid_search.py
Lines changed: 45 additions & 19 deletions
diff --git a/‎vector-embeddings/02-create-vectors-table.sql
Lines changed: 0 additions & 62 deletions b/‎vector-embeddings/02-create-vectors-table.sql
Lines changed: 0 additions & 62 deletions
diff --git a/‎vector-embeddings/02-use-native-vectors.sql
Lines changed: 44 additions & 0 deletions b/‎vector-embeddings/02-use-native-vectors.sql
Lines changed: 44 additions & 0 deletions
diff --git a/‎vector-embeddings/04-create-get-embeddings-procedure.sql
Lines changed: 5 additions & 3 deletions b/‎vector-embeddings/04-create-get-embeddings-procedure.sql
Lines changed: 5 additions & 3 deletions
@@ -1,72 +1,21 @@
-drop table if exists dbo.document_embeddings
-drop table if exists dbo.documents
+drop table if exists dbo.sample_documents
 go
 
-create table dbo.documents
+create table dbo.sample_documents
 (
     id int constraint pk__documents primary key,
     content nvarchar(max),
-    embedding nvarchar(max)
+    embedding varbinary(8000)
 )
-create table dbo.document_embeddings
-(
-    id int references dbo.documents(id),
-    vector_value_id int,
-    vector_value float
-)
-go
-
-create clustered columnstore index csi__document_embeddings 
-    on dbo.document_embeddings order (id)
-go
 
-if not exists(select * from sys.fulltext_catalogs where [name] = 'FullTextCatalog')
+if not exists(select * from sys.fulltext_catalogs where [name] = 'main_ft_catalog')
 begin
-    create fulltext catalog [FullTextCatalog] as default;
+    create fulltext catalog [main_ft_catalog] as default;
 end
 go
 
-create fulltext index on dbo.documents (content) key index pk__documents;
+create fulltext index on dbo.sample_documents (content) key index pk__documents;
 go
 
-alter fulltext index on dbo.documents enable; 
-go
-
-create or alter function dbo.similar_documents(@vector nvarchar(max))
-returns table
-as
-return 
-with cteVector as
-(
-    select
-        cast([key] as int) as [vector_value_id],
-        cast([value] as float) as [vector_value]
-    from
-        openjson(@vector)
-),
-cteSimilar as
-(
-    select top (50)
-        v2.id,
-        1-sum(v1.[vector_value] * v2.[vector_value]) / 
-        (
-            sqrt(sum(v1.[vector_value] * v1.[vector_value])) 
-            * 
-            sqrt(sum(v2.[vector_value] * v2.[vector_value]))
-        ) as cosine_distance
-    from
-        cteVector v1
-    inner join
-        dbo.document_embeddings v2 on v1.vector_value_id = v2.vector_value_id
-    group by
-        v2.id
-    order by
-        cosine_distance
-)
-select
-    rank() over (order by r.cosine_distance) as rank,
-    r.id,
-    r.cosine_distance
-from
-    cteSimilar r
+alter fulltext index on dbo.sample_documents enable; 
 go
@@ -14,7 +14,8 @@
     sentences = [
         'The dog is barking',
         'The cat is purring',
-        'The bear is growling'
+        'The bear is growling',
+        'A bear growling to a cat'
     ]
     model = SentenceTransformer('multi-qa-MiniLM-L6-cos-v1')
     embeddings = model.encode(sentences)
@@ -24,8 +25,7 @@
     print('Cleaning up the database...')
     try:
         cursor = conn.cursor()    
-        cursor.execute("DELETE FROM dbo.document_embeddings;")
-        cursor.execute("DELETE FROM dbo.documents;")
+        cursor.execute("DELETE FROM dbo.sample_documents;")
         cursor.commit();        
     finally:
         cursor.close()
@@ -39,8 +39,7 @@
                 DECLARE @id INT = ?;
                 DECLARE @content NVARCHAR(MAX) = ?;
                 DECLARE @embedding NVARCHAR(MAX) = ?;
-                INSERT INTO dbo.documents (id, content, embedding) VALUES (@id, @content, @embedding);
-                INSERT INTO dbo.document_embeddings SELECT @id, CAST([key] AS INT), CAST([value] AS FLOAT) FROM OPENJSON(@embedding);
+                INSERT INTO dbo.sample_documents (id, content, embedding) VALUES (@id, @content, JSON_ARRAY_TO_VECTOR(@embedding));
             """,
             id,
             content, 
@@ -53,37 +52,64 @@
 
     print('Searching for similar documents...')
     print('Getting embeddings...')    
-    query = 'growling bear'
+    query = 'a growling bear'
     embedding = model.encode(query)    
 
-    print('Querying database...')  
+    print(f'Querying database for "{query}"...')  
     k = 5  
     try:
         cursor = conn.cursor()  
 
         results  = cursor.execute(f"""
             DECLARE @k INT = ?;
+            DECLARE @q NVARCHAR(4000) = ?;
+            DECLARE @e VARBINARY(8000) = JSON_ARRAY_TO_VECTOR(CAST(? AS NVARCHAR(MAX)));
             WITH keyword_search AS (
                 SELECT TOP(@k)
                     id,
-                    ftt.[RANK] AS rank
-                FROM 
-                    dbo.documents 
-                INNER JOIN 
-                    FREETEXTTABLE(dbo.documents, *, ?) AS ftt ON dbo.documents.id = ftt.[KEY]
+                    RANK() OVER (ORDER BY rank) AS rank,
+                    content
+                FROM
+                    (
+                        SELECT TOP(@k)
+                            sd.id,
+                            ftt.[RANK] AS rank,
+                            sd.content
+                        FROM 
+                            dbo.sample_documents AS sd
+                        INNER JOIN 
+                            FREETEXTTABLE(dbo.sample_documents, *, @q) AS ftt ON sd.id = ftt.[KEY]
+                    ) AS t
+                ORDER BY
+                    rank
             ),
             semantic_search AS
             (
-                SELECT 
-                    id, 
-                    rank        
-                FROM 
-                    dbo.similar_documents(?)
+                SELECT TOP(@k)
+                    id,
+                    RANK() OVER (ORDER BY distance) AS rank,
+                    content
+                FROM
+                    (
+                        SELECT TOP(@k)
+                            id, 
+                            VECTOR_DISTANCE('cosine', embedding, @e) AS distance,
+                            content
+                        FROM 
+                            dbo.sample_documents
+                        ORDER BY
+                            distance
+                    ) AS t
+                ORDER BY
+                    rank
             )
             SELECT TOP(@k)
                 COALESCE(ss.id, ks.id) AS id,
                 COALESCE(1.0 / (@k + ss.rank), 0.0) +
-                COALESCE(1.0 / (@k + ks.rank), 0.0) AS score -- Reciprocal Rank Fusion (RRF) 
+                COALESCE(1.0 / (@k + ks.rank), 0.0) AS score, -- Reciprocal Rank Fusion (RRF)
+                COALESCE(ss.content, ks.content) AS content,
+                ss.rank AS semantic_rank,
+                ks.rank AS keyword_rank
             FROM
                 semantic_search ss
             FULL OUTER JOIN
@@ -97,7 +123,7 @@
         )
 
         for row in results:
-            print(f'Document: {row[0]} -> RRF score: {row[1]:0.4}')
+            print(f'Document: "{row[2]}", Id: {row[0]} -> RRF score: {row[1]:0.4} (Semantic Rank: {row[3]}, Keyword Rank: {row[4]})')
 
     finally:
         cursor.close()
@@ -0,0 +1,44 @@
+/*
+    Add columns to store the native vectors
+*/
+alter table wikipedia_articles_embeddings
+add title_vector_native varbinary(8000);
+
+alter table wikipedia_articles_embeddings
+add content_vector_native varbinary(8000);
+
+/*
+    Update the native vectors
+*/
+update 
+    wikipedia_articles_embeddings
+set 
+    title_vector_native = json_array_to_vector(title_vector),
+    content_vector_native = json_array_to_vector(content_vector);
+go
+
+/*
+    Remove old columns
+*/
+alter table wikipedia_articles_embeddings
+drop column title_vector;
+go
+
+alter table wikipedia_articles_embeddings
+drop column content_vector;
+go
+
+/*
+    Rename the columns
+*/
+EXEC sp_rename 'dbo.wikipedia_articles_embeddings.title_vector_native', 'title_vector_ada2', 'COLUMN';
+EXEC sp_rename 'dbo.wikipedia_articles_embeddings.content_vector_native', 'content_vector_ada2', 'COLUMN';
+
+/*
+	Verify data
+*/
+select top (100) * from [dbo].[wikipedia_articles_embeddings]
+go
+
+select * from [dbo].[wikipedia_articles_embeddings] where title = 'Alan Turing'
+go
@@ -4,7 +4,7 @@
 create or alter procedure dbo.get_embedding
 @deployedModelName nvarchar(1000),
 @inputText nvarchar(max),
-@embedding nvarchar(max) output
+@embedding varbinary(8000) output
 as
 declare @retval int, @response nvarchar(max);
 declare @payload nvarchar(max) = json_object('input': @inputText);
@@ -16,12 +16,14 @@ exec @retval = sp_invoke_external_rest_endpoint
     @payload = @payload,
     @response = @response output;
 
-declare @re nvarchar(max) = '[]';
+declare @re nvarchar(max) = null;
 if (@retval = 0) begin
     set @re = json_query(@response, '$.result.data[0].embedding')
+end else begin
+    select @response as 'Error message from OpenAI API';
 end
 
-set @embedding = @re;
+set @embedding = json_array_to_vector(@re);
 
 return @retval
 go