techthiyanes
diff --git a/‎.gitignore
Copy file name to clipboardExpand all lines: .gitignore
+3-2Lines changed: 3 additions & 2 deletions b/‎.gitignore
Copy file name to clipboardExpand all lines: .gitignore
+3-2Lines changed: 3 additions & 2 deletions
diff --git a/‎python/00-setup-database.sql
Copy file name to clipboard
+22Lines changed: 22 additions & 0 deletions b/‎python/00-setup-database.sql
Copy file name to clipboard
+22Lines changed: 22 additions & 0 deletions
diff --git a/‎python/hybrid_search.py
Copy file name to clipboard
+103Lines changed: 103 additions & 0 deletions b/‎python/hybrid_search.py
Copy file name to clipboard
+103Lines changed: 103 additions & 0 deletions
diff --git a/‎python/requirements.txt
Copy file name to clipboard
+4Lines changed: 4 additions & 0 deletions b/‎python/requirements.txt
Copy file name to clipboard
+4Lines changed: 4 additions & 0 deletions
diff --git a/‎python/utilities.py
Copy file name to clipboard
+17Lines changed: 17 additions & 0 deletions b/‎python/utilities.py
Copy file name to clipboard
+17Lines changed: 17 additions & 0 deletions
@@ -397,6 +397,7 @@ FodyWeavers.xsd
 # JetBrains Rider
 *.sln.iml
 
-# Customer
+# Custom
 *.local.txt
-*.local.sql
+*.local.sql
+.venv/
@@ -0,0 +1,22 @@
+DROP TABLE IF EXISTS dbo.document_embeddings
+DROP TABLE IF EXISTS dbo.documents
+go
+
+CREATE TABLE dbo.documents (id INT CONSTRAINT pk__documents PRIMARY KEY IDENTITY, content NVARCHAR(MAX), embedding NVARCHAR(MAX))
+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')
+BEGIN
+    CREATE FULLTEXT CATALOG [FullTextCatalog] AS DEFAULT;
+END
+go
+
+CREATE FULLTEXT INDEX ON dbo.documents (content) KEY INDEX pk__documents;
+go
+
+ALTER FULLTEXT INDEX ON dbo.documents ENABLE; 
+go
@@ -0,0 +1,103 @@
+import os
+import pyodbc
+import logging
+import json
+from sentence_transformers import SentenceTransformer
+from dotenv import load_dotenv
+from utilities import get_mssql_connection
+
+load_dotenv()
+
+if __name__ == '__main__':
+    print('Initializing sample...')
+    print('Getting embeddings...')    
+    sentences = [
+        'The dog is barking',
+        'The cat is purring',
+        'The bear is growling'
+    ]
+    model = SentenceTransformer('multi-qa-MiniLM-L6-cos-v1')
+    embeddings = model.encode(sentences)
+
+    print('Cleaning up the database...')
+    try:
+        conn = get_mssql_connection()
+        conn.execute("DELETE FROM dbo.document_embeddings;")
+        conn.execute("DELETE FROM dbo.documents;")
+        conn.commit();        
+    finally:
+        conn.close()
+
+    print('Saving documents and embeddings in the database...')    
+    try:
+        conn = get_mssql_connection()
+        cursor = conn.cursor()  
+        
+        for content, embedding in zip(sentences, embeddings):
+            cursor.execute(f"""
+                INSERT INTO dbo.documents (content, embedding) VALUES (?, ?);
+                INSERT INTO dbo.document_embeddings SELECT SCOPE_IDENTITY(), CAST([key] AS INT), CAST([value] AS FLOAT) FROM OPENJSON(?);
+            """,
+            content, 
+            json.dumps(embedding.tolist()),
+            json.dumps(embedding.tolist())
+            )
+
+        cursor.close()
+        conn.commit()
+    finally:
+        conn.close()
+
+    print('Searching for similar documents...')
+    print('Getting embeddings...')    
+    query = 'growling bear'
+    embedding = model.encode(query)    
+    
+    print('Querying database...')  
+    k = 5  
+    try:
+        conn = get_mssql_connection()
+        cursor = conn.cursor()  
+        
+        results  = cursor.execute(f"""
+            DECLARE @k INT = ?;
+            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]
+            ),
+            semantic_search AS
+            (
+                SELECT 
+                    id, 
+                    rank        
+                FROM 
+                    dbo.similar_documents(?)
+            )
+            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) 
+            FROM
+                semantic_search ss
+            FULL OUTER JOIN
+                keyword_search ks ON ss.id = ks.id
+            ORDER BY 
+                score DESC
+            """,
+            k,
+            query, 
+            json.dumps(embedding.tolist()),        
+        )
+
+        for row in results:
+            print('document:', row[0], 'RRF score:', row[1])
+
+        cursor.close()
+        conn.commit()
+    finally:
+        conn.close()
@@ -0,0 +1,4 @@
+python-dotenv
+pyodbc
+azure-identity
+sentence-transformers
@@ -0,0 +1,17 @@
+import os
+import pyodbc
+import struct
+import logging
+from azure import identity
+
+def get_mssql_connection():
+    logging.info('Getting MSSQL connection')
+    logging.info(' - Getting EntraID credentials...')    
+    mssql_connection_string = os.environ["MSSQL"]    
+    credential = identity.DefaultAzureCredential(exclude_interactive_browser_credential=False)    
+    token_bytes = credential.get_token("https://database.windows.net/.default").token.encode("UTF-16-LE")    
+    token_struct = struct.pack(f'<I{len(token_bytes)}s', len(token_bytes), token_bytes)
+    SQL_COPT_SS_ACCESS_TOKEN = 1256  # This connection option is defined by microsoft in msodbcsql.h        
+    logging.info(' - Connecting to MSSQL...')    
+    conn = pyodbc.connect(mssql_connection_string, attrs_before={SQL_COPT_SS_ACCESS_TOKEN: token_struct})
+    return conn