ApsaraDB for SelectDB:Inverted Index

At table creation (synchronous)

The index is ready as soon as the table is created.

Syntax

CREATE TABLE [IF NOT EXISTS] [db_name.]<table_name>
(
  <column_definition_list>,
  [<index_definition_list>]
)
table_properties;

index_definition syntax:

INDEX <index_name>(<column_name>) USING INVERTED [PROPERTIES("<key>" = "<value>")] [COMMENT '<comment>']

Parameters

`index_definition` required parameters

`PROPERTIES` (optional)

Use PROPERTIES to control tokenization behavior. Specify one or more key-value pairs in the format "<key>" = "<value>", separated by commas. To preview how a string will be tokenized before creating the index, use the TOKENIZE function. See Tokenization functions.

COMMENT (optional)

Example

-- Creates an inverted index on the comment column using English tokenization.
-- Omit PROPERTIES("parser" = "english") to index the full string without tokenization.
-- Also supports "chinese" and "unicode".
CREATE TABLE hackernews_1m
(
    `id`          BIGINT,
    `deleted`     TINYINT,
    `type`        String,
    `author`      String,
    `timestamp`   DateTimeV2,
    `comment`     String,
    `dead`        TINYINT,
    `parent`      BIGINT,
    `poll`        BIGINT,
    `children`    Array<BIGINT>,
    `url`         String,
    `score`       INT,
    `title`       String,
    `parts`       Array<INT>,
    `descendants` INT,
    INDEX idx_comment (`comment`) USING INVERTED PROPERTIES("parser" = "english") COMMENT 'inverted index for comment'
)
DUPLICATE KEY(`id`)
DISTRIBUTED BY HASH(`id`) BUCKETS 10;

Add an index to an existing table (asynchronous)

This is an asynchronous operation. Track progress with SHOW ALTER TABLE COLUMN.

Syntax

ALTER TABLE <table_name> ADD INDEX <index_name>(<column_name>) USING INVERTED [PROPERTIES("<key>" = "<value>")];

Parameters are the same as for table creation.

Examples

-- Add an index without tokenization (treats the full value as a single token)
ALTER TABLE user_tb ADD INDEX index_userId(user_id) USING INVERTED;

-- Add an index with English tokenization
ALTER TABLE user_tb ADD INDEX index_city(city) USING INVERTED PROPERTIES("parser" = "english");

View all indexes for a table

SHOW INDEXES FROM <table_name>;

Example:

SHOW INDEXES FROM user_tb;

Check async operation progress

ALTER TABLE ADD INDEX and DROP INDEX are asynchronous. Check progress with:

SHOW ALTER TABLE COLUMN;

Full-text search

SELECT * FROM <table_name> WHERE <column_name> <match_function> '<keywords>';

Examples

-- Rows where logmsg contains "keyword1"
SELECT * FROM log_tb WHERE logmsg MATCH_ANY 'keyword1';

-- Rows where logmsg contains "keyword1" or "keyword2"
SELECT * FROM log_tb WHERE logmsg MATCH_ANY 'keyword1 keyword2';

-- Rows where logmsg contains both "keyword1" and "keyword2"
SELECT * FROM log_tb WHERE logmsg MATCH_ALL 'keyword1 keyword2';

-- Rows where logmsg contains "keyword1" immediately followed by "keyword2"
SELECT * FROM log_tb WHERE logmsg MATCH_PHRASE 'keyword1 keyword2';

Equality and range queries

For numeric and date columns, use standard SQL syntax. SelectDB automatically uses the inverted index when one exists.

-- Equality query
SELECT * FROM user_tb WHERE id = 123;

-- Range query
SELECT * FROM user_tb WHERE ts > '2023-01-01 00:00:00';

-- IN query
SELECT * FROM user_tb WHERE op_type IN ('add', 'delete');

Set up the environment

Step 1: Create the table

CREATE DATABASE test_inverted_index;
USE test_inverted_index;

CREATE TABLE hackernews_1m
(
    `id`          BIGINT,
    `deleted`     TINYINT,
    `type`        String,
    `author`      String,
    `timestamp`   DateTimeV2,
    `comment`     String,
    `dead`        TINYINT,
    `parent`      BIGINT,
    `poll`        BIGINT,
    `children`    Array<BIGINT>,
    `url`         String,
    `score`       INT,
    `title`       String,
    `parts`       Array<INT>,
    `descendants` INT,
    INDEX idx_comment (`comment`) USING INVERTED PROPERTIES("parser" = "english") COMMENT 'inverted index for comment'
)
DUPLICATE KEY(`id`)
DISTRIBUTED BY HASH(`id`) BUCKETS 10;

Step 2: Import data

1. Download the dataset.

   wget https://qa-build.oss-cn-beijing.aliyuncs.com/regression/index/hacknernews_1m.csv.gz

2. Import using Stream Load. Find the connection host and port on the instance details page in the ApsaraDB for SelectDB console. For more information, see Stream Load.

   curl --location-trusted -u root: -H "compress_type:gz" \
     -T hacknernews_1m.csv.gz \
     http://<host>:<port>/api/test_inverted_index/hackernews_1m/_stream_load

   Expected output:

   {
       "TxnId": 2,
       "Label": "a8a3e802-2329-49e8-912b-04c800a461a6",
       "TwoPhaseCommit": "false",
       "Status": "Success",
       "Message": "OK",
       "NumberTotalRows": 1000000,
       "NumberLoadedRows": 1000000,
       "NumberFilteredRows": 0,
       "NumberUnselectedRows": 0,
       "LoadBytes": 130618406,
       "LoadTimeMs": 8988,
       "BeginTxnTimeMs": 23,
       "StreamLoadPutTimeMs": 113,
       "ReadDataTimeMs": 4788,
       "WriteDataTimeMs": 8811,
       "CommitAndPublishTimeMs": 38
   }

3. Verify the import.

   SELECT count() FROM hackernews_1m;
   +---------+
   | count() |
   +---------+
   | 1000000 |
   +---------+