전체 텍스트 검색 익스텐션

전체 텍스트 검색 익스텐션 (Full-Text Search Extension)

Full-Text Search는 문자열 검색을 가능하게 하는 DuckDB 익스텐션이에요. SQLite의 FTS5 익스텐션과 비슷해요. 대규모 텍스트에서 단어를 빠르게 찾고 싶을 때 딱 맞아요.

출처: 문서

본문

Full-Text Search는 SQLite의 FTS5 익스텐션과 비슷하게 문자열 검색을 가능하게 하는 DuckDB 익스텐션이에요.

설치와 로드 (Installing and Loading)

fts 익스텐션은 공식 익스텐션 저장소에서 처음 사용할 때 투명하게 자동 로드돼요. 수동으로 설치하고 로드하려면:

INSTALL fts;
LOAD fts;

사용법 (Usage)

이 익스텐션은 DuckDB에 두 개의 PRAGMA 문을 추가해요: 하나는 인덱스를 만들고, 하나는 인덱스를 삭제하는 것이에요. 또한 익스텐션이 내부적으로 사용하는 스칼라 매크로 stem도 추가해요.

PRAGMA create_fts_index

create_fts_index(input_table, input_id, *input_values, stemmer = 'porter',
                 stopwords = 'english', ignore = '(\\.|[^a-z])+',
                 strip_accents = 1, lower = 1, overwrite = 0)

지정된 테이블에 대한 FTS 인덱스를 만드는 PRAGMA예요.

이름 타입 설명
input_table VARCHAR 지정 테이블의 정규화된 이름. 예: 'table_name' 또는 'main.table_name'
input_id VARCHAR 문서 식별자 컬럼 이름. 예: 'document_identifier'
input_values... VARCHAR 인덱싱할 텍스트 필드 컬럼 이름(vararg). 예: 'text_field_1', 'text_field_2', ..., 'text_field_N', 또는 input_table의 VARCHAR 타입 모든 컬럼에 대해 '\*'
stemmer VARCHAR 사용할 스테머(stemmer) 타입. 'arabic', 'basque', 'catalan', 'danish', 'dutch', 'english', 'finnish', 'french', 'german', 'greek', 'hindi', 'hungarian', 'indonesian', 'irish', 'italian', 'lithuanian', 'nepali', 'norwegian', 'porter', 'portuguese', 'romanian', 'russian', 'serbian', 'spanish', 'swedish', 'tamil', 'turkish' 중 하나, 또는 스테밍을 사용하지 않으면 'none'. 기본값은 'porter'
stopwords VARCHAR 원하는 스톱워드를 담은 단일 VARCHAR 컬럼을 가진 테이블의 정규화된 이름, 또는 스톱워드를 사용하지 않으면 'none'. 기본값은 571개의 영어 스톱워드 사전 정의 목록인 'english'
ignore VARCHAR 무시할 패턴의 정규 표현식. 기본값은 `'(\.
strip_accents BOOLEAN 악센트를 제거할지 여부(예: á를 a로 변환). 기본값 1
lower BOOLEAN 모든 텍스트를 소문자로 변환할지 여부. 기본값 1
overwrite BOOLEAN 테이블의 기존 인덱스를 덮어쓸지 여부. 기본값 0

이 PRAGMA는 새로 생성된 스키마 아래에 인덱스를 만든다. 스키마는 입력 테이블의 이름을 따서 만들어져요: 테이블 'main.table_name'에 인덱스가 만들어지면 스키마는 'fts_main_table_name'으로 이름 지어져요.

PRAGMA drop_fts_index

drop_fts_index(input_table)

지정된 테이블에 대한 FTS 인덱스를 삭제해요.

이름 타입 설명
input_table VARCHAR 입력 테이블의 정규화된 이름. 예: 'table_name' 또는 'main.table_name'

match_bm25 함수

match_bm25(input_id, query_string, fields := NULL, k := 1.2, b := 0.75, conjunctive := 0)

인덱스가 만들어지면 인덱스를 검색하는 데 사용할 수 있는 이 검색 매크로가 생성돼요.

이름 타입 설명
input_id VARCHAR 문서 식별자 컬럼 이름. 예: 'document_identifier'
query_string VARCHAR 인덱스에서 검색할 문자열
fields VARCHAR 검색할 필드의 콤마로 구분된 목록. 예: 'text_field_2, text_field_N'. 기본 NULL은 인덱싱된 모든 필드 검색
k DOUBLE Okapi BM25 검색 모델의 파라미터 k1. 기본값 1.2
b DOUBLE Okapi BM25 검색 모델의 파라미터 b. 기본값 0.75
conjunctive BOOLEAN 쿼리를 결합(conjunctive)으로 만들지 여부 — 문서가 검색되려면 쿼리 문자열의 모든 용어가 있어야 함

stem 함수

stem(input_string, stemmer)

단어를 어간(base)으로 줄여요. 익스텐션이 내부적으로 사용해요.

이름 타입 설명
input_string VARCHAR 스테밍할 컬럼 또는 상수.
stemmer VARCHAR 사용할 스테머 타입. 'arabic', 'basque', 'catalan', 'danish', 'dutch', 'english', 'finnish', 'french', 'german', 'greek', 'hindi', 'hungarian', 'indonesian', 'irish', 'italian', 'lithuanian', 'nepali', 'norwegian', 'porter', 'portuguese', 'romanian', 'russian', 'serbian', 'spanish', 'swedish', 'tamil', 'turkish' 중 하나, 또는 스테밍을 사용하지 않으면 'none'.

사용 예시 (Example Usage)

테이블을 만들고 텍스트 데이터로 채워요:

CREATE TABLE documents (
    document_identifier VARCHAR,
    text_content VARCHAR,
    author VARCHAR,
    doc_version INTEGER
);
INSERT INTO documents
    VALUES ('doc1',
            'The mallard is a dabbling duck that breeds throughout the temperate.',
            'Hannes Mühleisen',
            3),
           ('doc2',
            'The cat is a domestic species of small carnivorous mammal.',
            'Laurens Kuiper',
            2
           );

인덱스를 만들고 text_content와 author 컬럼 모두 검색 가능하게 해요.

PRAGMA create_fts_index(
    'documents', 'document_identifier', 'text_content', 'author'
);

author 필드 인덱스에서 Muhleisen이 작성한 문서를 검색해요. doc1을 가져와요:

SELECT document_identifier, text_content, score
FROM (
    SELECT *, fts_main_documents.match_bm25(
        document_identifier,
        'Muhleisen',
        fields := 'author'
    ) AS score
    FROM documents
) sq
WHERE score IS NOT NULL
  AND doc_version > 2
ORDER BY score DESC;
document_identifier text_content score
doc1 The mallard is a dabbling duck that breeds throughout the temperate. 0.0

small cats에 관한 문서를 검색해요. doc2를 가져와요:

SELECT document_identifier, text_content, score
FROM (
    SELECT *, fts_main_documents.match_bm25(
        document_identifier,
        'small cats'
    ) AS score
    FROM documents
) sq
WHERE score IS NOT NULL
ORDER BY score DESC;
document_identifier text_content score
doc2 The cat is a domestic species of small carnivorous mammal. 0.0

경고 (Warning) FTS 인덱스는 입력 테이블이 변경되어도 자동으로 갱신되지 않아요. 이 제한의 해결 방법으로는 인덱스를 다시 만들어 새로 고침하는 것이 있어요.

더 알아보기 (Learn more)