Status

Current state: [One of "Under Discussion", "Accepted", "Rejected"]

Discussion thread: 

JIRA or Github Issue: 

Released: <Doris Version>

Google Doc: <If the design in question is unclear or needs to be discussed and reviewed, a Google Doc can be used first to facilitate comments from others.>

Motivation

For text data, such as log, commodity description, OLAP users need to search in the text. For example search error logs that contains 'ERROR', 'Exception' keywords.

Currently, users use LIKE SQL function for text pattern matching in Doris and most OLAP databases. But LIKE is not slow since all rows need to be checked against the search pattern.

Related Research

In information retrieve area, fulltext search is a mature solution to find the content that match a given query. It's widely used by web search engine.

Elasticsearch support distributed fulltext search capability based on the open source search library lucene.

Some database, eg. MySQL, PostgreSQL, also add inverted index to support fulltext search.

In fact, the BITMAP index in Doris is a simple inverted index. But it lack text tokenization, efficient dictionary, search query syntax to support mature fulltext search.

Detailed Design

Functionality

1. add a new index type INVERTED index
2. support create INVERTED index with tokenizer and fast fulltext search on text column with type char/varchar/string.
3. support create INVERTED index without tokenizer and fast equal, range operators on text column with type char/varchar/string.
4. support create INVERTED index without tokenizer and fast equal, range operators on numeric column with type int*/float*/date/datetime.

User interface

• create table with INVERTED index 

CREATE TABLE httplogs (
  ts datetime,
  clientip varchar(20),
  request string,
  status smallint,
  size int,
  INDEX idx_size (size) USING INVERTED,
  INDEX idx_status (status) USING INVERTED,
  INDEX idx_clientip (clientip) USING INVERTED PROPERTIES("tokenizer"="none"),
) ENGINE=OLAP
DUPLICATE KEY(ts);

• add an INVERTED index  to a table

CREATE INDEX idx_request ON httplogs(request) USING INVERTED PROPERTIES("tokenizer"="english")

• fulltext search query

SELECT * FROM httplogs WHERE request MATCH 'login';

• equal query

SELECT * FROM httplogs WHERE status = 404;

• range query

SELECT COUNT() FROM httplogs WHERE size > 1024;

Implementation

Scheduling

specific implementation steps and approximate scheduling.