전문검색(Full Text Search) - 2. 전문검색 서비스의 구축

전문검색 서비스의 구현입니다.

늘 그랬듯이. -_-;; 엔터프라이즈 관리자로 구현하는 부분을 먼저 설명 드린후

쿼리로 구성해 스크립트로 돌릴 수 있도록 하는 방법 역시 설명해 드리도록 하겠습니다.

전문검색을 구현하기 위한 단계는 크게 아래처럼 나뉘어 집니다.

　

1. 전문검색 서비스 설치

2. 전문검색 카탈로그 구성

3. 풀 파퓰레이션 수행

4. 필요할 경우 스케쥴을 이용한 풀 파퓰레이션 또는 증분 파퓰레이션 수행

　

이렇게 나뉘어 집니다. 구현부는 역시나 그다지 어렵지 않으니 빠르게 보실 수 있으실

겁니다.

엔터프라이즈 관리자를 이용한 전문 검색 구현.

먼저 엔터프라이즈 관리자의

지원 서비스 항목의 전체 텍스트 검색 항목이 시작 되어 있는지 확인 하셔야 합니다.

만약 항목이 없다면 설치를 안하신 것이며(디폴트로 설치시 자동 설치 됩니다.)

다시 설치 디스크를 넣고 엔진 항목의 전체 텍스트 검색 항목을 설치 하시면 됩니다.

　

혹시 실제로 테스트를 하고 싶으신 분들은?

테스트 데이터 다운로드(EXE화일입니다. 마우스 우측버튼 클릭 -> 다른 이름으로 저장)

SFX압축을 해제 하시면 tMSSQLQnA.mdb 항목이 나오며 이 mdb를 SQL서버로

DTS하신후 테스트 하시면 됩니다. (SQL2000강좌의 DTS강좌 참조)

　

코난이의 경우 이렇게 tMSSQLQnA라는 테이블로 SQL서버측에 구성 했습니다.

　

그후 DB에서 PK를 추가 했으며 이어서 Timestamp 컬럼 역시 추가 했습니다.

(증분 채우기 테스트용)

--PK확인.- 없으면 생성 합니다. ALTER TABLE tMSSQLQnA WITH NOCHECK ADD CONSTRAINT PKtMSSQLQnA PRIMARY KEY CLUSTERED ( idx ) GO --Timestamp 컬럼을 테이블에 추가. ALTER TABLE tMSSQLQnA ADD TStamp timestamp NOT NULL GO

물론 이메일 비슷한 것이 들어가는 @ 마크만 들어가도 샘플에 넣지 않았으며 가급적 사적인

데이터는 나름대로 제거한 샘플입니다. 참고 하시길 바랍니다.

　

자~~ 이제 전문검색을 구성해 보지요.

이렇게 전체텍스트 인덱스를 구성하려 하는 테이블에서 마우스 우버튼 -> 전체 텍스트

인덱스 테이블 -> 테이블에서 전체 텍스트 인덱싱 정의 를 선택하고 구성을 시작합니다.

　

이렇게 고유 인덱스를 선택 합니다. 반드시 PK 컬럼이나 Unique인덱스 컬럼이 필요하며

특이할 부분으로 두개의 컬럼의 값의 조합으로 고유값을 가지는 구성인 Composite 인덱스는

사용이 불가 합니다. - 만약 Composite 인덱스 사용중일 경우엔 하나의 컬럼을 고유값을

가지도록 identity로 컬럼을 추가해 구성하는 방법을 사용하면 됩니다.

　

전문검색을 수행할 열을 결정합니다. 테스트 테이블의 경우 title컬럼과 content 컬럼을

전문검색으로 사용 합니다. 아울러 단어 분리기(Word Breaker)는 한국어를 지정 해야만

한글 처리를 수행 할 수 있습니다. 자세한 이야기는 다음 장에서 풀도록 하지요.

　

전문검색의 인덱스가 저장되는 곳은? - Windows NT의 화일 시스템상에 독립적인 카탈로그로

저장 됩니다. 이 위치를 지정하는 것이며 추후 백업과 전문검색 카탈로그 이동을 고려해

적절한 위치에 잡으시면 됩니다. 전문검색의 인덱싱 데이터는 대부분의 분들이 별로 신경을

안쓰시는듯 합니다. SQL7부터 자동으로 인덱스의 데이터를 구성하고 활성화 시키고 자동으로

갱신해 주기 때문에 인덱스 데이터에 대한 많은 고려가 없고 신경쓸 일이 별로 없기 때문에

많은 분들이 크게 고려하지 않으시지만 인덱싱 데이터도 중요한 데이터이며 최적의 상태로

유지시켜 줘야 하는 녀석입니다. 전문검색 인덱스 데이터인 카탈로그 역시 무척이나 중요하고

꽤 큰 용량이니 잘 관리 하시길...

　

필요할 경우 이렇게 채우기 일정(스케쥴)을 구성합니다.

저의 경우는 간단히 테스트로 증분(Incremental) 채우기를 구성했습니다.

증분 채우기를 위해서는 반드시 전체 채우기가 필요하며 해당하는 테이블에 하나의

timestamp 컬럼이 필요 합니다.

　

자. 구성이 완료 되었군요. 마침을 누르면 전문검색 "구성만" 완료 됩니다.

　

꽤나 중요한 화면으로 인덱싱 정의는 완료 되었으나 인덱스는 채워지지 않았다고 나옵니다.

반드시 채우기를 수행 해야만 원하시는 검색을 수행 할 수 있지요.

　

이렇게 전체 텍스트 카탈로그 항목을 보면 구성한 전체 텍스트 구성이 있으며 해당 항목에서

전체 채우기 시작을 선택 합니다. 그후 다시 전체 텍스트 카탈로그 항목에소 새로고침을

수행하면 상태 항목이

이렇게 채우기 진행 중으로 변화하는 것을 볼 수 있습니다.

어느정도 시간이 지난후 다시 리프레시해 채우기가 완료되면 이제 테스트를 해 보실 수 있지요.

어렵지 않게 구성 항목을 보셨습니다. 얼마 안되는 구성이지만 실제 전문검색을 운영하실

경우엔 이렇게 EM으로 구성할 일은 거의 없으며 하지도 않습니다. 실수나 시간이 오래

걸리기 때문이지요. 실제로는 아래와 같은 구성 쿼리를 보통 사용하게 되며 아래 항목을

역시나 템플릿으로 구성해 여러 항목에 대한 정의를 스크립트로 처리할 수 있습니다.

그럼 SQL구문으로 전문검색을 구현해 보도록 하지요.

　

SQL구문을 이용한 전문검색 구현

--TEST 조회 select top 10 idx, title, writeday, content from tMSSQLQnA --전체 건수 select count(idx) from tMSSQLQnA --title 컬럼의 용량 select sum(datalength(title)) from tMSSQLQnA 대략 1M --content 컬럼의 용량 select sum(datalength(content)) from tMSSQLQnA 대략 27M --전문검색을 활성화 시킵니다. if (select DATABASEPROPERTY(DB_NAME(), N'IsFullTextEnabled')) <> 1 exec sp_fulltext_database N'enable' GO --전문검색 카탈로그가 만약 존재 한다면 삭제 합니다. if exists (select * from dbo.sysfulltextcatalogs where name = N'ftMSSQLQnA') exec sp_fulltext_catalog N'ftMSSQLQnA', N'drop' GO --전문검색 카탈로그를 생성합니다. if not exists (select * from dbo.sysfulltextcatalogs where name = N'ftMSSQLQnA') exec sp_fulltext_catalog N'ftMSSQLQnA', N'create' GO --PK확인.- 없으면 생성 합니다. ALTER TABLE tMSSQLQnA WITH NOCHECK ADD CONSTRAINT PKtMSSQLQnA PRIMARY KEY CLUSTERED ( idx ) GO --전문검색에 키값을 지정합니다. exec sp_fulltext_table N'tMSSQLQnA', N'create', N'ftMSSQLQnA', N'PK_tMSSQLQnA' --전문검색에 컬럼추가 - content 0x0412 : 한글 0x0409 : 영어(미국) exec sp_fulltext_column N'tMSSQLQnA', N'content', N'add', 0x0412 --전문검색에 컬럼추가 - title exec sp_fulltext_column N'tMSSQLQnA', N'title', N'add', 0x0412 --전문검색 활성화를 시작합니다. exec sp_fulltext_table N'tMSSQLQnA', N'activate' --풀파퓰레이션 시작 EXEC sp_fulltext_catalog 'ftMSSQLQnA', 'start_full' --증분 파퓰레이션 시작 - Timestamp 컬럼이 없을 경우 EXEC sp_fulltext_catalog 'ftMSSQLQnA', 'start_incremental' --Timestamp 컬럼을 테이블에 추가. ALTER TABLE tMSSQLQnA ADD TStamp timestamp NOT NULL GO --증분 파퓰레이션 시작 EXEC sp_fulltext_catalog 'ftMSSQLQnA', 'start_incremental' --필요에 의해 스케줄 구성.

쿼리쪽을 정확히 알아 두시고 필요시마다 사용하시면 되겠지요. 당연히 코난이도 위의 스크립만

쓰고 EM으로는 거의 하지 않는답니다. ^_^;;

　

노이즈 단어 제거

이 전문검색 인덱싱시 노이즈 단어로 정의된 녀석들이 있으며 이 노이즈 단어들은

인덱싱에서 제거 되며 쿼리시에서도 수행되지 않습니다.

노이즈 화일은 보통

SQL서버설치폴더\MSSQL\FTDATA\SQLServer\Config

경로에 포함되어 있습니다. 한국어 처리시 사용되는 노이즈 화일이 noise.kor 화일 입니다.

　

다음으로 전문검색을 구축했으니 어플리케이션에서 이용을 해야 겠지요. 간략히

몇몇 샘플을 알아 보도록 하지요.

전문검색과 관련해 주의하셔야 하는 키워드는 다음과 같습니다.

CONTAINS

CONTAINSTABLE

FREETEXT

FREETEXTTABLE

이렇게 네가지 입니다. 그중에서 CONTAINS 항목을 제외하면 한글 처리에서는

거의 쓰일일이 없기 때문에 CONTAINS 하나정도가 유력해지며 CONTAINS 항목의

여러 검색 방식 중에서도 한글 처리를 어느정도 지원하기 위해 사용 가능한 모듈 역시

몇가지를 제외하면 불가하니 실제 업무에 적용 하실 경우 사용 가능한 펑션은 많지 않습니다.

중요한 부분은 정확히 말씀 드리고 그외적인 부분은 언급 정도만 하도록 하지요.

　

CONTAINS

특정 키워드를 이용해 전문검색 카탈로그를 검색 합니다. 한글 처리에 적절한 부분은 역시나

키워드 검색 뿐이며 그외 근접어, 가중어, 유사단어에 대한 내용은 다음 장에서 조금

이야기해 보도록 하지요.

대략적인 예시 구문은 다음과 같습니다.

--백업 키워드로 제목 검색 SELECT idx, title, writeday, content FROM tMSSQLQnA WHERE contains(title, '"백업"') --백업 키워드로 글내용 검색 SELECT idx, title, writeday, content FROM tMSSQLQnA WHERE contains(content, '"백업"') --백* 키워드로 제목 검색 SELECT idx, title, writeday, content FROM tMSSQLQnA WHERE contains(title, '"백*"') --백업 or 리스토어 키워드로 글내용 검색 SELECT idx, title, writeday, content FROM tMSSQLQnA WHERE contains(content, '"백업" or "리스토어"') --백* or 리스토어 키워드로 글내용 검색 SELECT idx, title, writeday, content FROM tMSSQLQnA WHERE contains(content, '"백*" or "리스토어"') --백업 near 에러 키워드로 글내용 검색 SELECT idx, title, writeday, content FROM tMSSQLQnA WHERE contains(content, '백업 near 에러')

보시는 바와 같이 contains 항목중 위의 항목들은 비교적 동작하는 편입니다.

WHERE 구문의 LIKE 검색과 어느정도 비슷해 보이시는지요? 작은 따옴표와 큰 따옴표를

적절히 잘 확인 하셔야만 하며 가급적 메모장이나 쿼리 분석기에 복사하신후

구문정보를 잘 봐 두시는 것도 도움 되실 겁니다.

　

이하 항목은 그외의 contains 항목의 처리들로 참고 하시길 바랍니다.

--파생어 처리 USE Northwind GO SELECT ProductName FROM Products WHERE CONTAINS(ProductName, ' FORMSOF (INFLECTIONAL, dry) ') GO --가중치 처리 USE Northwind GO SELECT CategoryName, Description FROM Categories WHERE CONTAINS(Description, 'ISABOUT (spread weight (.8), sauces weight (.4), relishes weight (.2) )' ) GO

　

CONTAINSTABLE

정확도 검색(Ranking Search)를 수행 가능하게 합니다.

SQLER에서 예전에 게시판 검색 결과로 사용하던 방식으로 자동으로 Rank 컬럼이

구성되며 CONTAINSTABLE에서 weight 값을 이용해 단어별로 처리 할 수 있습니다.

실제로 하나의 검색어가 넘어올 경우(보통 게시판...) 이라면 별 의미가 없지만

좀더 상세한 전문검색의 세분화된 결과를 원하신다면 유용하실겁니다.

USE Northwind GO SELECT FT_TBL.CategoryName, FT_TBL.Description, KEY_TBL.RANK FROM Categories AS FT_TBL INNER JOIN CONTAINSTABLE(Categories, Description, 'ISABOUT (breads weight (.8), fish weight (.4), beers weight (.2) )' ) AS KEY_TBL ON FT_TBL.CategoryID = KEY_TBL.[KEY] ORDER BY KEY_TBL.RANK DESC GO

　

FREETEXT, FREETEXTTABLE

이름도 멋진 의미기반 검색입니다만. SQL2000의 한글 전문검색에서는 사용이 불가 합니다.

정확히 유의어 검색에 사용되며 전혀 틀린 단어라 할지라도 내부 사전의 유의 수준에 따라

결과를 보여주게 됩니다. FREETEXTTABLE의 경우 RANK 값을 또한 리턴 합니다.

사실상 한글 처리에서는 전혀 쓰이지 않습니다.

USE Northwind GO SELECT CategoryName FROM Categories WHERE FREETEXT (Description, 'sweetest candy bread and dry meat' ) GO 　 USE Northwind SELECT FT_TBL.CategoryName, FT_TBL.Description, KEY_TBL.RANK FROM Categories AS FT_TBL INNER JOIN FREETEXTTABLE(Categories, Description, 'sweetest candy bread and dry meat') AS KEY_TBL ON FT_TBL.CategoryID = KEY_TBL.[KEY] GO

　

약간의 샘플을 보셨습니다. 실제 사용되는 CONTAINS 정도만 주의하셔서 잘 봐두시면

전문검색은 다 보셨다고 해도 무방합니다.

이렇게 전문검색에 대해서 어느정도 알아 보셨습니다. 대부분의 책에서 소개되는 이 정도의

내용은 이제 어느정도 감이 잡히셨으리라 생각하고 있으며 이하 코난이가 생각하는

전문검색입니다.

　

자. 그럼 구현에 대한 이야기는 이정도로 마치기로 하지요.