Полнотекстовый поиск по файловой системе из SQL Server


Как известно, SQL Server еще с семерки умеет заниматься полнотекстовым поиском по своим строковым и текстовым полям. Бывают задачи, где целевые данные находятся вне базы. Например, понадобилось прошерстить файловую папку со сканами статей из периодики, отобрав те из них, в которых, грубо говоря, объект А упоминается в непосредственной близости от объекта Б, и навести по ним некоторую статистику. Удобно это делать из SQL Server, чтобы тут же на лету распихивать результаты по разным таблицам; сортировать, группировать, агрегировать и т.д. Конечно, можно, не долго думая, затащить все файлы в базу (см. "Импорт/экспорт блобов в файлы", "Импорт/экспорт блобовских полей в файлы – CLR", "Загрузка данных в filestream") и натравить на них полнотекстовый поиск изнутри SQL Server, благо по файлстриму он тоже работает, но это будет означать удвоенный расход дискового пространства, который может оказаться чувствительным, если папка с периодикой весома. Чтобы избежать лишних трат, было бы оптимально проводить поиск непосредственно по месту нахождения информации, то есть в файловых каталогах Windows, тем более, что там всяких технологий поиска развелось, как грязи: Windows Search, Windows Desktop Service, Instant Search, Windows Indexing Service, Indexed Search, MSN Desktop Search. Аж глаза разбегаются. Куда крестьянину податься? Если бы я, к примеру, знал, как позвать Windows Search из SQL Server, я бы, конечно, обратился к этой замечательной передовой и прогрессивной технологии. Однако этого невзирая на http://blogs.msdn.com/cheller/archive/2006/06/21/642220.aspx и https://channel9.msdn.com/posts/jmazner/How-to-Query-Vista-Search-From-Your-App/ по сей день не знает никто: http://social.msdn.microsoft.com/Forums/en-US/windowsgeneraldevelopmentissues/thread/5011856a-5e52-4775-972d-212637b0fa66,  http://social.msdn.microsoft.com/Forums/en-US/windowsdesktopsearchdevelopment/thread/c460515c-2446-465d-bd30-92d632579b39, http://codebetter.com/blogs/kyle.baley/archive/2008/07/07/trying-to-access-windows-search-from-sql-server-an-appeal.aspx. «Alas, all efforts to connect to Windows Search via SQL Server have failed. The nearest I've come is to find a couple of lost souls to commiserate with who are having the same problem. I've tried every possible combination of provider, datasource, and provstr I can fathom with the connection string, Provider=Search.CollatorDSO;Extended Properties=\"Application=Windows\", and have come up empty». Патпесалсо.

 

В итоге я подался в привычную степь старого доброго Index Server aka Monarch. На Висте он по умолчанию отключен, надо пойти в Control Panel -> Programs and Features -> Turn Windows features on or off и включить его:

 

image001

рис.1

 

Включался он не менее 10 мин., но, в итоге, включился. В сервисах появился Indexing Service (C:\Windows\system32\CISVC.EXE), а в mmc.exe – новый snap-in. 

 

image003

рис.2

 

Добавляем в индексацию новый каталог

 

image005

рис.3

 

image007

рис.4

 

и фолдеры, которые будут полнотекстно индексированы.

 

image009

рис.5

 

image011

рис.6

 

Согласно глоссарию (http://www.microsoft.com/technet/prodtechnol/sql/70/reskit/part12/sqcglo.mspx?mfr=true) full-text catalog – это entity holding a collection of full-text indexes. Platform SDK поясняет ( http://msdn.microsoft.com/en-us/library/ms689718.aspx): catalog contains a master index (and any temporary word lists and shadow indexes) storing words and their locations within a set of indexed documents. По поводу выбора места для каталога (http://www.windowsnetworking.com/articles_tutorials/Working-With-Windows-Server-2003-Indexing-Service.html ): you should never place a catalog file into a folder that’s being indexed. The reason is because Windows monitors indexed folders for changes. If you were to add a file to the folder, Windows would update the catalog. Windows would then see that the catalog has been updated, and reindex the folder again. It’s a pretty vicious cycle, so it’s best to just choose a non indexed location.

 

Отлично. Теперь создаем прилинкованный сервер на индексный сервер, как написано (или уже не написано?) в примерах на sp_addlinkedserver в BOL:

 

if exists (select 1 from sys.servers where name = 'FileSystem') exec sp_dropserver 'FileSystem', 'droplogins'

exec sp_addlinkedserver

   @server = 'FileSystem',

   @srvproduct = 'Indexing Service',

   @provider = 'MSIDXS',

   @datasrc = 'Test',

   @provstr = 'Locale Identifier=1049' -- LCID нужно оговорить, чтобы работал поиск русских словоформ

 

Давайте сделаем к нему какой-никакой запрос ради хохмы

 

select *

from openquery(FileSystem,

   'SELECT Directory, FileName, Size

    FROM SCOPE('' "c:\Trainings\SQLClub" '')

    WHERE FileName LIKE ''%.ps1''

')

 

image013

рис.7

 

Вы будете сильно смеяться, но старый конь, на удивление, все еще пашет:

 

select *

from openquery(FileSystem,

   'SELECT Path, Rank

    FROM SCOPE(''SHALLOW TRAVERSAL OF "c:\Trainings\SQLClub" '')

    WHERE CONTAINS(''FORMSOF(INFLECTIONAL, "запрос")'')

    ORDER BY Rank DESC

') -- По умолчанию предполагается CONTAINS(CONTENTS, ''FORMSOF... и CONTAINS(FileName, ''FORMSOF...

 

image015

рис.8

 

Можно даже так:

 

select *

from openquery(FileSystem,

   'SELECT Path, Rank, Write

    FROM SCOPE(''SHALLOW TRAVERSAL OF "c:\Trainings\SQLClub" '')

    WHERE FREETEXT(''полнотекст поиск'')

    ORDER BY FileName

') -- Атрибут Write означает Date Modified

 

 

image017

рис.9

 

И по-французски

 

select *

from openquery(FileSystem,

   'SELECT Path, Rank, Write

    FROM SCOPE(''SHALLOW TRAVERSAL OF "c:\Trainings\SQLClub" '')

    WHERE contains(''"SQL" near "Server"'')

    ORDER BY FileName

')

 

и на рояле

 

select *

from openquery(FileSystem,

   'SELECT Path, Rank, Write

    FROM SCOPE(''SHALLOW TRAVERSAL OF "c:\Trainings\SQLClub" '')

    WHERE contains(''isabout("ради" weight(0.2), "хохма" weight(0.8))'')

    ORDER BY FileName

')

 

и еще по-всякому,  как описывалось еще в далеком январе 2000 г. - http://www.osp.ru/win2000/2000/01/174147/.

 

Список полей, доступных для употребления в запросе, можно посмотреть на рис.5, в папке Properties:

 

 

image019 

рис.10

 

Из непоняток был замечен странный уход в себя Indexing Service, когда запрос к ней из SQL Server может выполняться бесконечно долго. Рестарт службы, рестарт SQL Server, протирка экрана ничего не дают, т.к. когда начинаем его по-новой выполнять, поведение повторяется. В некоторых случаях Indexing Service вообще не стопится, зависает в бесконечном состоянии Stopping. Можно пойти в Task Manager и грубо пристрелить cisvc.exe, но это не спасает, потому что как только службу стартовать по-новой, все будет ровно так же. Лечит только перестарт машины. Я не знаю, с чем это связано. От запроса это точно не зависит. Любое обращение к прилинкованному Index Server приводит к тому, что он уходит в себя. По-моему, я с таким поведением сталкиваюсь только на Висте. Возможно, оно возникает, если при работе с прилинкованным сервером открыть snap-in индекс сервера в ММС. Во всяком случае пока этот глюк проявлялся случайно пару раз и еще не достал настолько, чтобы с ним париться.


Comments (2)
  1. Смирнов Вячеслав says:

    Windows 7. Тшется пытаюсь понять по каким признакам формируется Rank. Точнее как повлиять на его формирование. Ситуация: 100 документов, для каждого знаю количество слов "Волгоград" в тексте документа (количестов отражено в названии).

    1. Выполняю поиск.

    select * from  openquery(FileStorage,

       'select Path, Rank from scope() where CONTAINS(''Волгоград'')')

    order by Rank DESC

    – Смотрю – порядок выдачи нормальный, максимальный ранг у файла с 49-ю словами в тексте, ранг равен 5.

    2. Открываю файл с 17-ю словами на редактирование, добавляю туда пробел, закрываю.

    3. Снова делаю запрос.

    – Теперь уже файл с 17-ю словами обогнал файл с 49-ю словами (видать из-за даты последнего редактирования/обращения). У файла с 17-ю словами ранг 143, у файла с 49-ю словами- по прежнему 5.

    Так как заставить считаться ранг только по тексту?

    Пробовал так:

    select * from  openquery(FileStorage,

       'select Path, Rank from scope() where CONTAINS(Text, ''Волгоград'')')

    order by Rank DESC

    Но нет такого свойства – Text. Надо как-то отсечь даты, названия, названия папок, примечания, … чтобы они не влияли на ранг.

Comments are closed.

Skip to main content