SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

SQL Server 2012 のフルテキストインデックスで Office Open XML と PDF を検索

leave a comment

SQL Server のフルテキストインデックスは varbinary(max) に対してフルテキストインデックスを作成することができるのでファイル内の検索をすることが可能です。
varbinary(max) 列および xml 列のクエリ (フルテキスト検索)

sys.fulltext_document_typessp_help_fulltext_system_components を使用することで組み込まれているフィルタを確認することができるのですが、初期の状態では xls/doc/ppt といった 2003 形式のファイルに対してのフィルタは組み込まれているのですが、Office 2007 以降の Office Open XML の形式や PDF のフィルタは組み込まれていないため検索をすることはできません。

今回の投稿ではこれらのファイルをフルテキストインデックスで検索するための手順をまとめてみたいと思います。

■フィルタのインストール


フィルタの追加に関しては 登録済みのフィルターおよびワード ブレーカーの表示または変更 に記載されています。

Office Open XML と PDF のフィルタは以下から入手することができます。
Microsoft Office 2010 フィルタ パック
Adobe PDF iFilter 9 for 64-bit platforms

今回の環境は x64 の環境を使用していますので 64 ビットのフィルタをダウンロードしています。

PDF の iFilter に関しては、[システム環境変数] の [PATH] に [C:Program FilesAdobeAdobe PDF iFilter 9 for 64-bit platformsbin] を追加しておかないとフルテキストインデックスのフィルタデーモンのログ ([C:Program FilesMicrosoft SQL Server<インスタンス ID>MSSQLLog] の [SQLFTxxxxxx.LOG]) に以下のようなメッセージが出力され、PDF ファイルの内容をクローリングすることができないので注意が必要です。
フルテキスト作成 (クロール) で発生したエラーのトラブルシューティング

Error ‘0x8004fd02: フィルタ デーモン MSFTEFD がドキュメントの IFilter インターフェイスを読み込めなかったため、インデックスを作成できません。’ occurred during full-text index population for table or indexed view ‘[TEST].[dbo].[Table_1]’ (table or indexed view ID ‘581577110’, database ID ‘7’), full-text key value ‘5E8AE95D-26FC-4866-9079-D9FB063E9585’. Attempt will be made to reindex it.

(SQL Server 2008 64-bit) Full-text indexing PDF not working – can’t find iFilter
Adobe PDF iFilter 9 for 64-bit Platforms now available

両 iFIlter をダウンロードしてインストールし、PATH の設定が終わった後に以下の技術情報の内容を実行します。
How to register Microsoft Filter Pack IFilters with SQL Server

 

具体的には

  1. [sp_fulltext_service ‘load_os_resources’, 1] を実行
  2. 対象インスタンスの [SQL Full-text Filter Daemon Launcher] サービスを再起動
  3. 対象インスタンスの SQL Server のサービスを再起動

そうするとインストールした Office 2010 と PDF の iFilter が SQL Server に組み込まれます。
OS がロードしている iFilterが組み込まれますので数がかなり増えると思います。
# sp_fulltext_service (Transact-SQL) で [update_languages][restart_all_fdhosts] を使ってもよいのかもしれないですね。
また、SQL Server 2008 以降であればフルテキスト検索の動作変更で SQL Server のコアサービス側にフルテキスト検索が統合されているのでサービスの再起動は SQL Server だけでもよさそうです。
フルテキスト検索の動作の変更
フルテキスト検索のコンポーネントとアーキテクチャ

 

■フルテキストインデックスで検索できるかのテスト


今回は以下のようなテーブルを作成しています。
image

varbinary の列に対してフルテキストインデックスを作成する場合、[データ型列] を指定する必要があります。
image

この列は varbinary に格納しているファイルの拡張子を示すものになります。
CREATE FULLTEXT INDEX (Transact-SQL)

TYPE COLUMN type_column_name

varbinary(max) または image ドキュメントのドキュメント型の格納に使用される、テーブル列の名前 type_column_name を指定します。 型列と呼ばれるこの列には、ユーザー指定のファイル拡張子 (.doc、.pdf、.xls など) が格納されます。 型列の型は、char、nchar、varchar、または nvarchar であることが必要です。

TYPE COLUMN type_column_name を指定できるのは、column_name に、データがバイナリ データとして格納される varbinary(max) または image を指定した場合のみです。それ以外の場合、SQL Server はエラーを返します。

この列の値を使用してバイナリファイルをどの iFilter を使用してクロールするかが決まるようです。
このテーブルに対して以下のようなクエリを実行してファイルを格納します。

INSERT INTO Table_1
SELECT NEWID(), *, N’.txt’ FROM OPENROWSET(BULK N’C:tempDocument.txt’, SINGLE_BLOB) AS document

INSERT INTO Table_1
SELECT NEWID(), *, N’.doc’ FROM OPENROWSET(BULK N’C:tempDocument.doc’, SINGLE_BLOB) AS document

INSERT INTO Table_1
SELECT NEWID(), *, N’.docx’ FROM OPENROWSET(BULK N’C:tempDocument.docx’, SINGLE_BLOB) AS document

INSERT INTO Table_1
SELECT NEWID(), *, N’.pdf’ FROM OPENROWSET(BULK N’C:tempDocument.pdf’, SINGLE_BLOB) AS document

OPENROWSET を使用することでファイルを varbinary に格納することができますので、テストするときはこの方法を使用すると楽だと思います。

今回は各ファイルに [Microsoft SQL Server 2012] という文字列を入力してあります。

この状態で以下のクエリを実行してフルテキストインデックスを作成します。

USE [TEST]
GO

CREATE FULLTEXT CATALOG [TEST]WITH ACCENT_SENSITIVITY = OFF
GO
CREATE FULLTEXT INDEX ON [dbo].[Table_1] KEY INDEX [PK_Table_1] ON ([TEST]) WITH (CHANGE_TRACKING AUTO)
GO
ALTER FULLTEXT INDEX ON [dbo].[Table_1] ADD ([Col2] TYPE COLUMN [Col3] LANGUAGE [Japanese])
GO
ALTER FULLTEXT INDEX ON [dbo].[Table_1] ENABLE
GO

フルテキストインデックスの作成が終わったら検索をしてみるとすべてのファイルが検索できていることが確認できます。
image

フルテキストインデックスは勉強で軽く触ったぐらいであまり使ったことがなかったので、勉強を兼ねて少しまとめてみました。

Written by masayuki.ozawa

9月 26th, 2012 at 11:54 pm

Posted in SQL Server

Tagged with

Leave a Reply

*