Full-Text Search (MS SQL Server) pro internetové obchody a jiné aplikace.

MichalJezek — Thu, 25 Mar 2010 11:44:00 GMT

    Úvod
    Co očekává uživatel od hledání?
    Karsa Search Manager
    Testovací databáze
    Vytvoření fulltextového katalogu
    Kvalita hledání
    Váha sloupců
    iFTS Predicates and Functions
    Vyhledávací vzory
    Personalizované hledání
    Přímé volání výsledné uložené procedury
    Závěr

Úvod
Cílem článku je příblížit problematiku vytvoření a správu relevantního full-textového hledání ve webových projektech - internetový obchodech. Informace zde uvedené vycházejí ze zkušeností při tvorbě aplikace Karsa SQL fulltext search manager a následných implementacích hledání. V článku se zaměřujeme na problematiku hledání v internetových obchodech, ale postup lze samozřejmě aplikovat na libovolnou webovou, desktopovou aplikaci.

Co očekává uživatel od hledání?

Uživatel očekává, že pokud zadá hledáný výraz, ihned se zobrazí relevantní výsledky. Nepřemýšlí nad tím, že Vaše aplikace není google. Pokud se nezobrazí relevantní výsledky zpravidla odchází. Tím klesá konverzní poměr – nákupu.
Obecně uživatelé nedůvěřují hledání na stránkách mimo hlavní portály, právě z důvodu nízké relevantnosti výsledků. Cílem by tedy mělo být vytvořit co nejvíce relevantní hledaní a s MS SQL Server iFTS (Integrated Full Text Search) tato možnost rozhodně je.

Karsa Search Manager

Karsa Fulltext Search Manager je desktopová aplikace, díky které se zvýšuje efektivita práce a redukují se chyby při tvorbě fulltextového hledání. Desktopový manager slouží k nastavení
hledání a následnému vygenerování uložených procedur na MS SQL Server.Není tedy potřeba registrace žádných dll ani vkládání .net kódu na SQL Server, vždy se spouští standardní T-SQL script a využívají se široké možnosti SQL Server iFTS.

Vytvoření několik set řádkového scriptu je velice časově náročné, nehledě na jeho následné úpravy dle a/b testování a vyhodnocení logů a právě v tomto aplikace významně vývojáři usnadňuje život. Aplikace neomezuje vývojáře při tvorbě vlastních vyhledávacích scriptů a odvádí “černou práci” při generování pomocných scriptů, výtvoření katalogu, propojení, profilování, aktualizacích.

Testovací databáze

Pro ukázky hledání budeme používat toto zjednodušené schéma imaginárního internetového obchodu.

Tabulka eshop_product obsahuje jen několik záznamů, na kterých budeme demostrovat hledání

Vytvoření fulltextového katalogu

K vytvoření Fulltextového katalogu můžeme využít více možností.

1. SQL Server Managment studio
2. T-SQL
3. Karsa Search manager

Zde si uvedeme možnosti 1 a 3.

SQL Server Managment studio
1. V objekt exploreru, otevřete server, rozbalte databáze a otevřete databázi v které chcete vytvořit full-text katalog.
2. Rozbalte Storege, klikněte pravým tlačítkem na Full Text Catalogs
3. Vyberte New Full-Text Catalog
4. V New Full-Text Catalog dialogu uveďte informace o katalogu, který chcete vytvořit.

Nový full-text katalog

Výběr tabulek a indexu

Při výběru indexu dejte pozor, aby byl vybrán Clustered index. Clustrovaný index je výrazně rychlejší

Karsa Search manager

Výhodou použití je svázanost návrhu full-textového hledání s katalogem. Pokud přidáte nový sloupec ve kterém chcete hledat a tento sloupec není definován ve full-textovém katalogu je tento sloupce označen červeně.

Po stisknutí tlačítka Generování pomocných scriptů se na databázi nebo do souboru vygeneruje pomocný script, který sloupce, přidá do full-textového katalogu

Při návrhu hledaní se těchto změn provádějí desítky a tato funkcionalita šetří velice čas. Pokud není vytvořen full-text katalog je také automaticky vytvořen.

Kvalita hledání

Kvalitou hledání rozumíme počet výsledků, které jsou relevantní k hledanému výrazu. Nejčastější chybou hledání v aplikacích je přiliš nízká míra relavantních výsledků v hledání. Výsledk obsahuje příliš irelevantních záznamů, nebo neobsahuje žádné záznamy. To je způsobeno přiliš striktním full-textovým dotazem. Například hledaná fráze: Mountains bike pedals

select  * from eshop_product where  contains([Description], ' "mountains" and "bike" and "pedals"')

Výsledkem je nalezený jeden záznam

Pokud by jsme použili tento dotaz ve výsledku se nezobrazí záznam, který
v popisu obsahuje jen slova bike and pedals

Mužeme změnit dotaz například takto

select  * from eshop_product where  contains([Description], ' ("mountains" and "bike" and "pedals") or ("bike" and "pedals")')

Výsledkem jsou dva záznamy

tak, aby vyhledal dvou i tříslovný výraz.
Pokud bude tabulka eshop_product obsahovat dva zaznamy jeden v popisu obsahující všechna tři slova a druhý jen dvě, musíme toto zohlednit ve výsledku hledání, kdy první záznam obsahující všechna tři slova dostane výšší rank. Stejně by jsme měli zohlednit to pokud slova jsou nalezena v jiném tvaru při použítí

FORMSOF(INFLECTIONAL, bikes near mountains )

nebo v jiném pořadí.

Duležité
Obdobně by záznam měl dostat vyšší rank pokud se hledaný výraz vyskytuje v názvu produktu v našem případě sloupce Name něž pokud se nalézne shoda v popisu.

Karsa full-text manager toto řeší pomocí vyhledávacích vzorů, které lze přiřadit ke každému sloupci a vyhledávacímu vzoru lze nastavit váhu, která se započte do výsledného ranku záznamu. Také je možno nastavit váhu celého sloupce. Vyhledávací vzory jsou základním nástrojem, kterým je možné detailně vyladit váhy hledání a docílit tak relevantnějších výsledků hledání.

Nastavení váhy sloupce a vyhledávacích vzorů

iFTS Predikáty a funkce (Predicates and Functions)

Pomocí predikátů T-SQL, které se používají v klauzulích WHERE, HAVING nebo ve spojovacích podmínkách můžeme prohledávat nestrukturovaná textová data. V textových datech můžeme hledat gramatické tvary slov, synonyma, přesnou shodu. Hledání vrací relevantnější výsledky a je rychlejší než použítí LIKE, protože se textová data prohledávají podle významu a ne podle shody s hledaným výrazem.

FREETEXT - FREETEXTTABLE

Predikát FREETEXT automaticky hledá shodu s gramatickými tvary slov, přesné shody a synonyma. Automaticky také do ranku započítává blízkost hledaných slov odsebe.

select  * from eshop_product where  freetext([Description], ' bike pedals')

Výsledkem jsou tři záznamy

FREETEXTTABLE

Syntaxe a funkcionalita predikátů FREETEXT a FREETEXTTABLE je velmi podobná

select id, f.rank, Name, Description   from eshop_product inner join  freetextTable(eshop_product,Description, ' bike pedals' ) f on eshop_product.id = f.KEY

Zobrazení ranku záznamů

FREETEXTTABLE přidává možnost definovat arumenty, název tabulky, sloupce, které se mají prohledávat. Vrací také rank, podle kterého lze omezit počet výsledků.

FREETEXT – FREETEXTTABLE jsou méně precizní způsoby hledání než predikát CONTAINS. Právě pro svoji “automatičnost„ si uživatelé často stěžují na nerelevantní výsledky. Z tohoto důvodu se v karsa full-text manageru predikát FREETEXT nepoužívá.

CONTAINS - CONTAINSTABLE

Nabízí rozšířené možnosti oproti predikátu FREETEXT. CONTAINSTABLE se používá jako základní predikát při vytváření vyhledávacích vzorů v Karsa full-text search manageru.

Můžeme použít hledání pomocí:

Gramatických tvarů
Fráze
Operátorů AND OR NOT
Blízkosti slov (proximity)
Váhy slov (Weighted terms)
Prefixů

Gramatických tvarů

Pokud chceme najít slovo bike i v jiném tavaru použijeme predikát FORMSOF(INFLECTIONAL, bikes) v ukázce doplněno o hledání v THESAURUS souboru

SELECT * FROM eshop_product  WHERE contains([Description], ' FORMSOF(INFLECTIONAL, bikes) or  FORMSOF(THESAURUS, bicycle)' )

Fráze

Hledání přesné fráze

SELECT * FROM eshop_product  WHERE CONTAINS ([Description], '"mountains  bike pedals"');

Operátor AND OR NOT

Hledáme slova bike a pedals a nechceme ve výsledku zobrazit silniční kola

SELECT * FROM eshop_product  WHERE contains([Description], '"bike" and "pedals" and not (FORMSOF(INFLECTIONAL, "road") or "road bicycles") ' )

Blízkost slov

NEAR zohledňuje vzdálenost slov v hledaném textu od sebe. Bližším slovům přiděluje vyšší rank.

SELECT * FROM eshop_product  WHERE contains([Description], 'bike NEAR mountains ')

Váha slov

Pomocí ISABOUT lze pridělit slovům výšší váhu. Tato váha se promítne u nalezených záznamů do výsledného ranku.

select id, f.rank, Name, Description   from eshop_product inner join  containstable(eshop_product,Description, 'ISABOUT(bike weight(.9), pedals weight(.3) )  ' ) f on eshop_product.id = f.KEY

Prefixy

Použitím zástupného symbolu hvězdička *, který prezentuje nula nebo více znaků
lze docílit obdobného hledání jako při použití LIKE. Zde, ale těžíme z výšší rychlosti full-textového hledání. Toto hledání se často používá při hledání kódů produktů.

SELECT * FROM eshop_product  WHERE CONTAINS ([Description], '"moun*"');

Vyhledávací vzory

Vyhledávací vzory umožňují přesné vyladění vah hledání. Předdefinováno je celkem 9 vzorů hledání. Vzory je možné také vytvářet vlastní

U každého vzoru je možné nastavit jeho název, defaultní váhu a samotný obsah vzoru. Vyhledávací vzory je možné aplikovat také na vazební tabulky a tím docílit vyšší relevance hledání. V naši ukázce například prohledávaním tabulky eshop_forum. Obsah vzoru je definován pomocí těla C# metody (vb.net se připravuje), který je možno editovat. Například

public string Test(string text, string[] words){
//Tento obsah je možné editovat
}
//String text  vyhladávaný text
//string[]    words  - pole s vyhledávaným textem, rozděleným na slova.

Zjednodušeně platí: words = text.Split(" ")
Tělo metody musí vrátit validní SQL fulltextový vzor vhodný pro zadání vyhladávaných slov. V těle metody lze použít jakýkoliv validní C# výraz. Můžete například odstranit nechtěné znaky, přeházet slova atd. Tato funkce se ukládá na SQL server jako text do pomocné tabulky a dodávaná knihovna dll filtering provider ji načíta. Není tedy využíván .Net kód na straně SQL Serveru. Pokud se vyhledávání neaplikuje, není odesíláné na SQL server a nesnižuje se tak rychlost

hledání. Ve výsledném SQL dotazů může být použit jakýkoliv validní SQL výraz, například FORMSOF INFLECTIONAL

Ukázeme si krátký postup jak vytvořit vlastní vzor. Budeme vytvářet nový vzor hledání, který bude zohledňovat váhu pořadí slov v hledaném výrazu, pomocí predikátu ISABOUT. Tento vzor není standardně definován.
Vzor nazveme IsAboutMoreWords. Vzor bude aplikován pokud v hledaném výrazu je použito tři a více slov. Dle pořadí slov v hledaném výrazu přiřadí těmto slovům váhu.

if (words.Length < 3)
{
    return string.Empty;
}
System.Text.StringBuilder result = new System.Text.StringBuilder();
result.Append("'ISABOUT(");
double weight = 1.0;
for (int i = 0; i < words.Length; i++)
{
    string weightText = weight.ToString().Replace(",", ".");
    result.AppendFormat("\"{0}\" weight({1})", words, weightText);
    if (i < words.Length - 1)
    {
        result.Append(", ");
    }
    weight -= 0.1;
    if (weight < 0.1)
    {
        weight = 0.1;
    }
}
result.Append(")'");
return result.ToString();

Pokud budeme hledat výraz „mountains bike pedals“ výsledkem je tento string 'ISABOUT("mountains" weight(1), "bike" weight(0.9), "pedals" weight(0.8))' , který se použije v predikatu CONTAINSTABLE. Vzor stačí uložit a můžete jej přiřadit k libovolným sloupcům, kde jej chcete použít.

Editace vyhledávacího vzoru. Editor kontroluje validnost kódu.

Personalizované hledání

Pomocí rersonalizovaného hledání můžeme uživateli nabídnout ješte více relevantnější výsledky. Můžeme měnit váhu sloupců a vyhledávacích vzorů při každém hledání a to pro konkrétního uživatele.
Tím se nám nabízejí šíroké možnosti použítí. Můžeme například logovat pohyb uživatele po obchodu a dle stráveného času v určitých kategoriích preferovat určité sloupce, vyhledávací vzory nebo modifikovat hledaný výraz.
Personalizované hledání může být velice silným nástrojem.

Ukázka nastavení váhy sloupce Name na 100

FilterService service = new
FilterService(@"Server=.\SQLEXPRESS;Database=KarsaFulltextSample;Trusted_Connection=True");

FilterCriteria filterCriteria = new FilterCriteria();
filterCriteria.FulltextSearchText = "bike";

IEnumerable<FulltextColumnsPattern> fulltextPatterns;
fulltextPatterns = service.GetDefaultFulltextColumnsPatterns();

foreach (FulltextColumnsPattern fulltextPattern in fulltextPatterns)
{
    if ((fulltextPattern.NodeName == "eshop_product") &&
        (fulltextPattern.Columns.Contains("Name")))
    {
        fulltextPattern.Weight = 100;
    }
}

FilterResult result = service.GetItems(1, 10,
                        filterCriteria, fulltextPatterns);
foreach (ResultItem item in result.ItemsOnPage)
{
    Console.WriteLine(item.Identification["ID"]);
}












FilterService service = new
FilterService(@"Server=.\SQLEXPRESS;Database=KarsaFulltextSample;Trusted_Connection=True");

FilterCriteria filterCriteria = new FilterCriteria();
filterCriteria.FulltextSearchText = "bike";

IEnumerable<FulltextColumnsPattern> fulltextPatterns;
fulltextPatterns = service.GetDefaultFulltextColumnsPatterns();

foreach (FulltextColumnsPattern fulltextPattern in fulltextPatterns)
{
    if ((fulltextPattern.NodeName == "eshop_product") &&
        (fulltextPattern.Columns.Contains("Name")))
    {
        fulltextPattern.Weight = 100;
    }
}

FilterResult result = service.GetItems(1, 10,
                        filterCriteria, fulltextPatterns);
foreach (ResultItem item in result.ItemsOnPage)
{
    Console.WriteLine(item.Identification["ID"]);
}

Přímé volání výsledné uložené procedury

Možnost volat výslednou uloženou procedůru z karsa fulltext search manageru především ocení vývojáři, kteří nepoužívají C#, vb.net, ale třeba PHP, Java, Python, C++ a chtějí vytvořit relevantnější hledání.

Ukázka přímého volaní.

EXEC karsa_generated_list_selection
    -- limit result to only first 100 items
    @minIndex = 0,  -- items with smaller index are skipped
    @maxIndex = 99, -- items with bigger index are skipped
    
    -- text to search for
    @fulltextSearchText = 'sony',
    -- table with fulltext patterns associated with columns
    @fulltextColumnPatternTable = '#inputFulltextColumnPattern',    
    -- minimal required rank
    @fulltextMinRank = 1,        
    -- If not null, this table will contain debug data. If null, debug data are not provided.    
    @fulltextDebugTable = '#outputFulltextDebugData',
    
    -- Sorters are comma-separated and prefix with "+" (ascending) or "-" (descending)
    @sorters = '+Name,-Manufacturer',
    -- This table will be filled with result items
    @resultTable = '#outputResult',
    -- If not null, this table will contain profile data. If null, profile data are not provided.
    @profileTable = '#outputProfileData'

Závěr

Karsa fulltext manager je ke stažení v české i anglické verzi.
V článku nejsou uvedený další možnosti zvyšování relevance, jako jsou například.

Vyhledávání v relačních tabulkách
Stoplist
Thesaurus file
Zvýraznění výsledků hledání / Highlighting
Našeptávač
Historie hledání
Logování hledáných výrazů
Cachování hledaných výrazů
Logování konverze | A/B testování
Parametrické hledání

o těchto možnostech případně přístě.

Michal Ježek

Full-Text Search (MS SQL Server) pro internetové obchody a jiné aplikace.