Полнотекстовый поиск по файловой системе из 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, я бы, конечно, обратился к этой замечательной передовой и прогрессивной технологии. Однако этого невзирая на https://blogs.msdn.com/cheller/archive/2006/06/21/642220.aspx и https://channel9.msdn.com/posts/jmazner/How-to-Query-Vista-Search-From-Your-App/ по сей день не знает никто: https://social.msdn.microsoft.com/Forums/en-US/windowsgeneraldevelopmentissues/thread/5011856a-5e52-4775-972d-212637b0fa66, https://social.msdn.microsoft.com/Forums/en-US/windowsdesktopsearchdevelopment/thread/c460515c-2446-465d-bd30-92d632579b39, https://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 и включить его:

рис.1

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

рис.2

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

рис.3

рис.4

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

рис.5

рис.6

 

Согласно глоссарию (https://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 поясняет ( https://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. По поводу выбора места для каталога (https://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''

')

рис.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...

 

рис.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

 

 

рис.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 г. - https://www.osp.ru/win2000/2000/01/174147/.

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

 

 

рис.10

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

Comments

• Anonymous
  June 12, 2009
  PingBack from http://blog.a-foton.ru/index.php/2009/06/12/%d0%bf%d0%be%d0%bb%d0%bd%d0%be%d1%82%d0%b5%d0%ba%d1%81%d1%82%d0%be%d0%b2%d1%8b%d0%b9-%d0%bf%d0%be%d0%b8%d1%81%d0%ba-%d0%bf%d0%be-%d1%84%d0%b0%d0%b9%d0%bb%d0%be%d0%b2%d0%be%d0%b9-%d1%81%d0%b8%d1%81/

• Anonymous
  September 17, 2010
  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. Надо как-то отсечь даты, названия, названия папок, примечания, ... чтобы они не влияли на ранг.