TextDB

TextDB provides a list of functions for text search, outlined in the table below. For detailed usage, see the Usage Example section.

A text index will automatically speed up queries using = or IN operators on text-indexed columns if both of the following conditions are met:

• The text index is built without tokenization - setting parser as ‘none’.
• The value on the right side of the = or IN query is a constant.

select textCol from pt where textCol = "apple";
select textCol from pt where textCol in ("apple", "banana");

Database and Table Creation

• TextDB is built based on PKEY database, so you must specify engine="PKEY" when creating a database.
• Text indexes can only be created on STRING columns.
• Multiple indexes can be created on several individual columns of a single table, but a single index that spans multiple columns is not supported.

Data Size and Character Limits

• TextDB only supports text indexes on STRING columns, which have a maximum length limit of 65,535 bytes. Text exceeding this limit will be truncated.
• Only UTF-8 encoded strings are supported. If an invalid UTF-8 character (garbled text) is encountered, the string will be truncated up to that point.

Queries

• TextDB only supports query acceleration on columns with a text index.
• Queries must be applied directly to tables that have text indexing set. The text search functions cannot be applied to joined tables.
• Text search functions must be used in the where clause and must be positioned outermost, without being nested within other conditions.
• Only and operator can be used to connect text search functions with other conditions. The or operator is not supported.

Create a database and a table.

// create a PKEY database
dbName = "dfs://test"
if (existsDatabase(dbName)){
        dropDatabase(dbName)
}

// use the database function
db = database(dbName, HASH, [INT, 3], engine=`PKEY)

// or use the SQL CREATE DATABASE statement
create database "dfs://test" 
partitioned by HASH([INT, 3]),engine="PKEY"

// create a partitioned table with text indexing on textCol
// use the createPartitionedTable function 
schematb = table(5:5, `col0`col1`textCol, [INT,INT,STRING])
pt = createPartitionedTable(dbHandle=db,table=schematb,tableName=`pt, partitionColumns=`col0, primaryKey=`col0`col1, indexes={"textCol":"textindex(parser=english,full=false,lowercase=true,stem=false)"})

// or use the SQL CREATE TABLE statement
CREATE TABLE "dfs://test"."pt"(
    col0 INT,
    col1 INT,
    textCol STRING [indexes="textIndex(parser=english,full=true,lowercase=true,stem=false)"]
)
PARTITIONED BY col0,
primaryKey=`col0`col1

Append data to pt.

textData = [
        "The sun was shining brightly as I walked down the street, enjoying the warmth of the summer day.",
        "I enjoy a refreshing smoothie made with apple every morning.",
        "The sound of the waves crashing against the shore was a soothing melody that helped me relax after a long day.",
        "The city skyline looked stunning from the top of the mountain, with the lights twinkling like stars in the night sky.",
        "The picnic basket was filled with juicy apple slices and ripe banana for a healthy snack."
    ]
data = table(1..5 as col1, 1..5 as col2, textData)

pt.append!(data)

Perform queries.

// Search for rows containing "apple" or "banana"
select textCol from pt where matchAny(textCol, "apple banana");

// Search for rows containing "apple" and "banana"
select textCol from pt where matchAll(textCol, "apple banana");

// Search for rows containing phrase "juicy apple"
select textCol from pt where matchPhrase(textCol, "juicy apple");

// Search for rows containing words prefixed with "ap"
select textCol from pt where matchPrefix(textCol, "ap");

// Search for rows containing words suffixed with "ana"
select textCol from pt where matchSuffix(textCol, "ana");

// Search for rows containing words prefixed with "ap" and suffixed with "le"
select textCol from pt where matchPrefixSuffix(textCol, "ap", "le");

// Search for rows containing phrase prefixed with "filled with juicy ap"
select textCol from pt where matchPhrasePrefix(textCol, "filled with juicy", "ap");

// Search for rows containing phrase suffixed with "th of the summer day"
select textCol from pt where matchPhraseSuffix(textCol, "th", "of the summer day");

// Search for rows containing phrase "twinkling like" that is preceded by a word prefixed with "hts" and followed by a word suffixed with "sta"
select textCol from pt where matchPhraseInfix(textCol, "hts", "twinkling like", "sta");

/**
  Search for rows containing phrase "enjoying the summer day", 
  allowing up to 2 extra words (excluding stop words) before, after, or within the phrase
*/
select textCol from pt where matchSpan(textCol, "enjoying the summer day", 2)

/**
  Search for rows containing phrase "day summer the enjoying" (order-insensitive), 
  allowing up to 2 extra words before, after, or within the phrase
*/
select textCol from pt where matchUnorderedSpan(textCol, "day summer the enjoying", 2)