Wyszukiwanie pełnotekstowe(Full-Text Search)

w SQL Server

Kamil Nowiński

kamil@nowinski.net

Kamil Nowiński

• Konsultant, freelancer, SQL Server expert

• Ostatnio jako architekt w projekcie hurtowni danych „Ari@dna2” programu e-Cło dla Ministerstwa Finansów

• Programista > 20 lat (VB6, VB.NET, C#, .NET Framework)

• Ponad 10-letnie doświadczenie w SQL Server (DEV/DBA)

• Bazy danych: Microsoft SQL 2000 – 2012+

• Członek komisji rewizyjnej Stowarzyszenia PLSSUG,

• Współorganizator konferencji SQLDay

• Certyfikaty SQL Server: MCITP, MCP, MCTS, MCSA, MCSE

• Zainteresowania:

• Optymalizacja baz danych, Hurtownie danych, SSIS

• Bieganie, Fotografia cyfrowa (Nikon D-90, Adobe Lightroom)

Agenda

Wprowadzenie

Full-Text Search Components

Creating and Managing Full-Text Catalogs & Indexes

Using the CONTAINS & FREETEXT Predicates

Wyszukiwarka Google

Możliwości wyszukiwania

• Simple terms – one or more specific words or phrases

• Perfix terms, which are terms the words of phrases begin with

• Generation terms, meaning inflectional forms of words

• Proximity terms (or words or phrases) close to another word/phrase

• Thesaurus terms, or synonyms of a word

• Weighted terms, which are words or phrases that use values with yourcustom weight

• Statistical semantic search, or key phrases in a document

• Similar documents, where similarity is defined by semantic key phrases

Możliwości wyszukiwania

Kolumny typu:

• CHAR

• VARCHAR

• NCHAR

• NVARCHAR

• TEXT

• NTEXT

Kolumny typu:

• IMAGE *

• XML *

• VARBINARY(MAX) *

* Pozwalają przechowywać całe dokumenty.

Wymagana do tego dodatkowa kolumna z rozszerzeniem pliku (typ)

Full-Text Search Components

• Wymagana instalacja komponentu na instancji SQL Server

• Stosowanie filtrów (ifilters) dla dokumentów

– Zaczerpnięcie tylko informacji tekstowych

– Usunięcie formatowania z dokumentu(ów)

• Zainstalowane filtry:

– Wiele popularnych formatów jest zainstalowanych domyślnie

– Filtry dla dokumentów w formacie MS Office 2010 i nowszych:http://www.microsoft.com/en-us/download/details.aspx?id=17062

– Po instalacji nowe filtry wymagają rejestracji w SQL Server:

Linguistic analysis

• Dane w indeksach Full-Text podlegają analizie językowej

• Analiza językowa wymaga znalezienia granicy słowa (word-breaking)oraz znajomości odmian czasowników (stemming)

• Word breakers & stemmers są specyficzne dla każdego języka

• Zatem reguły analizy językowej również

• Default Full-Text language:

– Setup sets to the language of your instance

– EnglishIf language is not supported or you use a nonlocalized version of SQL Server

Stoplists & stopwords

• Stoplist-a może zawierać wiele stopword’s

• Pozwala zapobiegać indeksowaniu (a dokładniej wyszukiwaniu)

• Przykłady:

– łączniki: i, lub, oraz,

– Słowa często występujące w danej dziedzinie (np. SQL)

• Jak sprawdzić:

Synonimy i plik Tezaurus (thesaurus)

• Wyszukiwanie odbywa się wg podanych słów

• Wyszukiwanie odbywa się również wśród synonimów

• SQL Server korzysta z bazy synonimów zdefiniowanych w plikach XML(thesaurus files)

• Każdy język posiada swój odrębny plik Tezaurus

• Edycja pliku pozwala na konfigurację:

– Diacritics_sensitive

– Expansion (autor, pisarz)

– Replacement (Win 2k8 -> Windows 2008)

• Po edycji pliku musi zostać on załadowany przez SQL Server:

Creating Full-Text Catalogs

CREATE FULLTEXT CATALOG catalog_name

[ON FILEGROUP filegroup]

[IN PATH 'rootpath']

[WITH <catalog_option>]

[AS DEFAULT]

[AUTHORIZATION owner_name]

<catalog_option>::=

ACCENT_SENSITIVITY = {ON|OFF}

• ON FILEGROUP, IN PATH

zostały zachowane dla kompatybilności wstecznej i nie mają żadnego znaczenia w SQL Server 2012

Creating Full-Text Indexes

CREATE FULLTEXT INDEX ON dbo.Documents

(

docexcerpt Language 1033,

doccontent TYPE COLUMN doctype Language 1033

STATISTICAL_SEMANTICS

)

KEY INDEX PK_Documents ON DocumentsFtCatalog

WITH STOPLIST = SQLStopList,

SEARCH PROPERTY LIST =WordSearchPropertyList,

CHANGE_TRACKING AUTO;

Creating Full-Text Indexes

• KEY INDEX index_name

Unique key index on a table

• CHANGE_TRACKING [=] { MANUAL |AUTO | OFF [, NO POPULATION]}Konfiguruje czy SQL Server powinien aktualizować full-textindex automatycznie

• STATISTICAL_SEMANTICS

Tworzy dodatkową frazę kluczową i dokument podobieństwa indeksów, które są częścią statystycznego indeksowania semantycznego

Wyszukiwanie semantyczne

• Pozwala na głębsze wniknięcie do dokumentów:

– Indeksowanie statystyczne zależnych fraz kluczowych

• Wykorzystanie fraz kluczowych służy:

– Wyszukiwaniu dokumentów podobnych lub powiązanych

• Semantic search rozszerza możliwości wyszukiwania pełnotekstowego

• Wymagania:

– Zainstalowana baza danych Semantic Language StatisticsDostępna na płycie z instalatorem SQL Server w folderze:\x64\Setup\SemanticLanguageDatabase.msi

DEMO #1CREATING FULL-TEXT CATALOGS AND INDEXES

Using the CONTAINS Predicate

Używając predykatu CONTAINS można wyszukiwać:

• Słowa i frazy w tekście

• Dokładne lub przybliżone dopasowania

• Formy fleksyjne wyrazów

• Tekst, w którym dwa wyrazy są blisko siebie

• Synonimy poszukiwanych wyrazów

• Przedrostków wyrazów

Można również stosować „wagi” dla poszczególnych słów.

Predykat CONTAINS jest stosowany w klauzuli WHERE.

Using the FREETEXT Predicate

• Mniej precyzyjny, zwraca więcej wyników (wierszy)

• Dużo prostsza forma zapytania

• Zwracane wartości:

– Pasujące do znaczenia frazy

– Nie tylko dokładne słowa

Silnik SQL Server:

1) Wykonuje dzielenie wyrazów (word breaking) frazy

2) Generuje formy fleksyjne

3) Identyfikuje listę rozszerzeń i zastąpień (thesaurus)

4) Znajduje wszystkie powyższe wyrazy

DEMO #2USING THE CONTAINS AND FREETEXT PREDICATES

DEMO #3WYSZUKIWARKA SQLOOGLE

TEST

Review #1

• Które elementy wyszukiwania pełnotekstowegopozwalają uniknąć indeksowania niechcianych wyrazów?

A. Stopwords

B. Thesaurus

C. Stemmer

D. Stoplists

Review #2

• Jaką bazę danych trzeba zainstalować aby włączyć wyszukiwanie semantyczne?

A. msdb

B. distribution

C. semanticsdb

D. tempdb

Review #3

• Jak można utworzyć synonimy dla wyszukiwanych słów?

A. Edytując plik Tezaurus

B. Tworząc tabelę Tezaurus

C. Używając stopwords dla synonimów

D. Wyszukiwanie pełnotekstowe nie wspiera synonimów

Answers

• Review #1

–Correct Answers: A and D

• Review #2

– Correct Answer: C

• Review #3

– Correct Answer: A

Podsumowanie (hasłowo)

• Full-Text Catalog (in Storage folder)

• Full-Text Indexes

• Reguły analizy językowej specyficzne dla języka

• StopLists & StopWords

• Plik Tezaurus (synonimy i zastąpienia)

• CONTAINS Predicate

• FREETEXT Predicate

Q&A

ANY QUESTIONS

Materiały

• Exam 70-461: „Querying Microsoft SQL Server 2012”Itzik Ben-Gan, Dejan Sarka, Ron Talmage

• http://msdn.microsoft.com/en-us/library/ms142571(v=sql.110).aspx

DZIĘKUJĘ ZA UWAGĘ

Zapraszam do kontaktu

kamil@nowinski.net

kamil.nowinski

http://pl.linkedin.com/in/kamilnowinski/

@NowinskiK