Enterprise full-text search, native to Teradata

-- Full BM25 ranked search — works on Teradata 17 and 20
-- Returns the top 10 products most relevant to 'wireless noise cancelling'
SELECT
    p.product_id,
    p.sku,
    p.name,
    p.description,
    scores.score
FROM (
    SELECT
        ti.document_id,
        SUM(
            fts_metadata.BM25_SCORE(
                ti.term_frequency,     -- how often the term appears in this document
                ds.doc_length,         -- length of this document
                idx.avg_doc_length,    -- average length across all documents
                ts.document_frequency, -- how many docs contain this term
                idx.document_count     -- total number of indexed documents
            )
        ) AS score
    FROM TABLE(fts_metadata.TOKENIZE_TEXT_C('wireless noise cancelling')) AS qt(token, pos)
    INNER JOIN fts_metadata.fts_term_index ti
        ON ti.term = qt.token AND ti.index_name = 'products_idx'
    INNER JOIN fts_metadata.fts_document_stats ds
        ON ds.index_name = ti.index_name AND ds.document_id = ti.document_id
    INNER JOIN fts_metadata.fts_term_stats ts
        ON ts.index_name = ti.index_name AND ts.term = ti.term
    CROSS JOIN fts_metadata.fts_indexes idx
        WHERE idx.index_name = 'products_idx'
    GROUP BY ti.document_id
) scores
INNER JOIN mydb.products p
    ON CAST(p.product_id AS VARCHAR(512)) = scores.document_id
ORDER BY scores.score DESC
SAMPLE 10;  -- efficient pagination — no full sort required

-- Teradata 20 only: SEARCH_FTS convenience procedure
-- One call replaces the full BM25 query above
CALL fts_metadata.SEARCH_FTS(
    'products_idx',                    -- index to search
    'wireless noise cancelling',       -- search query
    'mydb.products',                   -- fully-qualified source table
    'product_id',                      -- primary key column
    'product_id, sku, name',           -- columns to return
    10                                 -- maximum results
);

-- Results are written to the global temporary table fts_search_results
SELECT * FROM fts_search_results ORDER BY score DESC;

-- Unranked search (fastest — no BM25 calculation, just matching docs)
SELECT DISTINCT ti.document_id
FROM TABLE(fts_metadata.TOKENIZE_TEXT_C('wireless')) AS qt(token, pos)
INNER JOIN fts_metadata.fts_term_index ti
    ON ti.term = qt.token AND ti.index_name = 'products_idx';

-- Debug tokenisation — see how your query terms are split
SELECT token, pos
FROM TABLE(fts_metadata.TOKENIZE_TEXT_C('noise-cancelling headphones (BT)')) AS t(token, pos)
ORDER BY pos;

-- A new product was inserted — index just that row (no full rebuild)
CALL fts_metadata.INDEX_SINGLE_DOCUMENT_C(
    'products_idx',
    CAST(1042 AS VARCHAR(512)),  -- the new product_id
    result_msg
);

-- An existing product was updated — re-index it
CALL fts_metadata.UPDATE_SINGLE_DOCUMENT_C(
    'products_idx',
    CAST(1042 AS VARCHAR(512)),
    result_msg
);

-- A product was deleted — remove it from the index
CALL fts_metadata.DELETE_SINGLE_DOCUMENT(
    'products_idx',
    CAST(1042 AS VARCHAR(512)),
    result_msg
);

-- After a large batch of changes, do a full rebuild instead
-- (automatically replaces index contents and recalculates statistics)
CALL fts_metadata.REBUILD_FTS_INDEX_C('products_idx', result_msg);
SELECT :result_msg;  -- "Index products_idx rebuilt (12,850 documents)"

# 1. Unpack the distribution for your Teradata version
unzip teradata-fts-td20.zip   # or teradata-fts-td17.zip

# 2. Edit credentials in the BTEQ script
nano bteq/install.bteq        # set your hostname, username, password

# 3. Run the deployment (creates fts_metadata schema + all objects)
./deploy.sh                   # Linux / macOS
deploy.bat                    # Windows

# 4. Verify installation
bteq << EOF
.LOGON myhost/myuser,mypassword
SELECT * FROM fts_metadata.v_fts_indexes;
.LOGOFF
EOF

# What gets installed into fts_metadata:
#   3 UDFs        — TOKENIZE_TEXT_C, TOKENIZE_TEXT_V, BM25_SCORE
#   9 procedures  — CREATE, DROP, REBUILD, INDEX/UPDATE/DELETE_SINGLE, SEARCH_FTS
#   4 tables      — fts_indexes, fts_term_index, fts_document_stats, fts_term_stats
#   3 views       — v_fts_indexes, v_fts_db_indexes, v_fts_index_columns
#   2 GTTs        — Working space for CLOB and VARCHAR indexing operations

Requires CREATE DATABASE privileges on the target Teradata instance and approximately 1 GB of permanent space for the fts_metadata schema. Full prerequisites are detailed in the documentation.