Denali CTP3 - Semantic Search features

Published 18 October 11 02:59 AM | sqlartist

Long time since I posted but the last post I did mentioned the Semantic Search feature potentially coming in SQL Server. Well its finally here and I finally got to test it out on the Denali CTP3 build. It is not feature complete and only allows Key terms to be analysed - the holy grail for text indexing or search engine developers would be key terms and key phrases - that really then opens up the world of text indexing and un structured information analysis.

I followed the tutorial included in the recently released SQL Server 2012 Developer Training Kit but to save anyone the time of copy and pasting I have included the SQL code from that tutorial. I hope to test this out on some decent document collections very soon.

EXECUTE xp_cmdshell 'md C:\SQLServerArticles', no_output
GO

SELECT * FROM sys.fulltext_semantic_languages
GO

create database SQLServerArticles
    on primary (name = SQLServerArticles_File, filename = N'C:\SQLServerArticles\SQLServerArticles_File.mdf'),
    filegroup SQLStorage
        contains filestream
            (name = SQLServerArticles_FS_File, filename = N'C:\SQLServerArticles\SQLServerArticles_FS')
        with filestream
            (non_transacted_access = full, directory_name = N'SQLServer Articles')
GO

CREATE TABLE SQLServerArticles..Documents AS FileTable WITH (filetable_directory = N'Document Library')
GO

-- Then Load Office 2010 Filters

USE SQLServerArticles
GO
EXEC sp_fulltext_service 'load_os_resources',1
EXEC sp_fulltext_service 'restart_all_fdhosts'
CREATE FULLTEXT CATALOG ft AS DEFAULT
GO
CREATE UNIQUE INDEX DocumentsFt ON SQLServerArticles..Documents(stream_id)
GO
CREATE SEARCH PROPERTY LIST DocumentProperties;
GO
ALTER SEARCH PROPERTY LIST DocumentProperties
   ADD 'Title'
   WITH ( PROPERTY_SET_GUID = 'F29F85E0-4FF9-1068-AB91-08002B27B3D9', PROPERTY_INT_ID = 2,
      PROPERTY_DESCRIPTION = 'System.Title - Title of the item.');
ALTER SEARCH PROPERTY LIST DocumentProperties
    ADD 'Author'
   WITH ( PROPERTY_SET_GUID = 'F29F85E0-4FF9-1068-AB91-08002B27B3D9', PROPERTY_INT_ID = 4,
      PROPERTY_DESCRIPTION = 'System.Author - Author or authors of the item.');
ALTER SEARCH PROPERTY LIST DocumentProperties
    ADD 'Tags'
   WITH ( PROPERTY_SET_GUID = 'F29F85E0-4FF9-1068-AB91-08002B27B3D9', PROPERTY_INT_ID = 5,
      PROPERTY_DESCRIPTION = 'System.Keywords - Keywords (Tags) of the item.');
GO
CREATE FULLTEXT INDEX ON SQLServerArticles..Documents
    (file_stream TYPE COLUMN file_type LANGUAGE 1033 statistical_semantics)
    KEY INDEX DocumentsFt ON ft
    WITH SEARCH PROPERTY LIST = DocumentProperties
GO

-- Find path of Documents

USE SQLServerArticles
GO
SELECT FileTableRootPath() AS 'FileTable Root Path'
GO

-- Right-click the first returned row and select Copy.
/*
4.  Open a Windows Explorer window, paste the Document Library path to the address bar, and press Enter. Then double-click the Document Library folder.
5.  Open another Windows Explorer window, browse to the Assets folder of this lab and enter the SQLServerArticles folder.
6.  Select all files and folders in the SQLServerArticles folder, and drag them to the Document Library folder, while pressing Ctrl.
*/


-- \\KIWI\MSSQLSERVER\SQLServer Articles
-- Check all files are in the table

SELECT stream_id, name, file_type,
(SELECT REPLACE(N'File:\\'+FileTableRootPath()+file_stream.GetFileNamespacePath(), N' ', N'%20')
FROM SQLServerArticles..Documents T2
WHERE T2.stream_id = T1.stream_id FOR XML PATH(''), TYPE) AS FilePath,
    creation_Time, last_write_time, last_access_time, is_directory, is_offline,
    is_hidden, is_readonly, is_archive, is_system, is_temporary
        FROM SQLServerArticles..Documents T1
        ORDER BY name ASC
GO


-- Querying table
/*
SELECT column_name FROM table_name
WHERE CONTAINS (PROPERTY (column_name,'property_name'),'')
*/


USE SQLServerArticles
GO
SELECT name DocumentName, file_stream.GetFileNamespacePath() Path FROM Documents
    WHERE CONTAINS(PROPERTY(file_stream, 'Title'), 'data OR SQL')
GO

SELECT name DocumentName, file_stream.GetFileNamespacePath() Path FROM Documents
    WHERE CONTAINS(file_stream, 'NEAR(("data", "SQL"), 5, FALSE)')
GO

SELECT name, document_key, keyphrase, score
    FROM semantickeyphrasetable(Documents, *)
  INNER JOIN Documents ON stream_id = document_key
    ORDER BY name, score DESC

GO

SELECT KeyPhrase, AVG(score) AS score FROM semantickeyphrasetable(Documents, *)
    INNER JOIN Documents ON stream_id = document_key
    GROUP BY KeyPhrase ORDER BY score DESC
GO

DECLARE @Title as NVARCHAR(1000)
DECLARE @DocID as UNIQUEIDENTIFIER

SET @Title = 'InsideSQLAzure.docx'

SELECT @DocID = stream_id FROM Documents WHERE name = @Title

SELECT name, document_key, keyphrase, score
    FROM semantickeyphrasetable(Documents, *, @DocID)
    INNER JOIN Documents ON stream_id = document_key
    ORDER BY name, score DESC
GO

DECLARE @Title as NVARCHAR(1000)
DECLARE @DocID as UNIQUEIDENTIFIER

SET @Title = 'ConsolidationPrescriptiveGuidance.docx'

SELECT @DocID = stream_id FROM Documents WHERE name = @Title

SELECT @Title AS SourceTitle, name AS MatchedTitle, stream_id, score
    FROM semanticsimilaritytable(Documents, *, @DocID)
    INNER JOIN Documents ON stream_id = matched_document_key
    ORDER BY score DESC
GO

USE SQLServerArticles
DECLARE @SourceTitle as NVARCHAR(1000)
DECLARE @MatchedTitle as NVARCHAR(1000)
DECLARE @SourceDocID as UNIQUEIDENTIFIER
DECLARE @MatchedDocID as UNIQUEIDENTIFIER

SET @SourceTitle = 'ConsolidationPrescriptiveGuidance.docx'
SET @MatchedTitle = 'SS08R2Virtualization.docx'

SELECT @SourceDocID = stream_id FROM Documents WHERE name = @SourceTitle
SELECT @MatchedDocID = stream_id FROM Documents WHERE name = @MatchedTitle

SELECT @SourceTitle AS SourceTitle, @MatchedTitle AS MatchedTitle, keyphrase, score
    FROM SEMANTICSIMILARITYDETAILSTABLE(Documents, file_stream, @SourceDocID, file_stream, @MatchedDocID)
    ORDER BY score DESC
GO



-- Testing Semantic Search capabilities

USE AdventureWorks2008R2
GO
CREATE FULLTEXT INDEX ON Production.Document
    (Document
        TYPE COLUMN FileExtension
        LANGUAGE 1033
        statistical_semantics,

        DocumentSummary
        LANGUAGE 1033
    )
    KEY INDEX PK_Document_DocumentNode
    ON AW2008FullTextCatalog
    WITH (CHANGE_TRACKING = AUTO)
GO

ALTER FULLTEXT INDEX ON HumanResources.JobCandidate
    ALTER COLUMN Resume
    ADD statistical_semantics
GO

SELECT OBJECT_NAME(object_id) TableName, COL_NAME(object_id, column_id) IndexedColumn, *
    FROM sys.fulltext_index_columns
GO

SELECT Title, document_key, keyphrase, score
   FROM semantickeyphrasetable(Production.Document, *)
   INNER JOIN Production.Document ON DocumentNode = document_key
   ORDER BY Title, score DESC
GO

DECLARE @Title as NVARCHAR(1000)
DECLARE @DocID as HierarchyID

SET    @Title = 'Seat Assembly'
SELECT @DocID = DocumentNode FROM Production.Document WHERE Title = @Title

SELECT @DocID AS ManualID, @Title AS 'Manual', keyphrase, score
    FROM semantickeyphrasetable(Production.Document, *, @DocID)
    ORDER BY score DESC
GO

DECLARE @Title as NVARCHAR(1000)
DECLARE @DocID as HierarchyID

SET    @Title = 'Seat Assembly'
SELECT @DocID = DocumentNode FROM Production.Document WHERE Title = @Title
SELECT @Title AS 'Original Document', Title AS 'Similar Document', score
    FROM semanticsimilaritytable(Production.Document, *, @DocID)
    INNER JOIN Production.Document ON DocumentNode = matched_document_key
    ORDER BY score DESC
GO

DECLARE @SourceTitle as NVARCHAR(1000)
DECLARE @MatchedTitle as NVARCHAR(1000)
DECLARE @SourceDocID as HierarchyID
DECLARE @MatchedDocID as HierarchyID

SET @SourceTitle = 'Seat Assembly'
SET @MatchedTitle = 'Front Reflector Bracket and Reflector Assembly 3'

SELECT @SourceDocID = DocumentNode FROM Production.Document WHERE Title = @SourceTitle
SELECT @MatchedDocID = DocumentNode FROM Production.Document WHERE Title = @MatchedTitle

SELECT @SourceTitle AS 'Original Document', @MatchedTitle AS 'Related Document', keyphrase, score
    FROM semanticsimilaritydetailstable(Production.Document, Document, @SourceDocID, Document, @MatchedDocID)
    ORDER BY score DESC
GO

Comments

# Dew Drop – October 18, 2011 | Alvin Ashcraft's Morning Dew said on October 18, 2011 12:48 PM:

Pingback from  Dew Drop – October 18, 2011 | Alvin Ashcraft's Morning Dew