Introduction: SearchIndex is the secondary index of Lindorm wide table, which is mainly used to help businesses realize rapid retrieval and analysis. This article introduces how to operate SearchIndex through a simple SQL interface.

1, Introduction

The cloud native multi-mode database Lindorm supports low-cost storage of massive data and flexible on-demand payment. It provides a variety of data models such as wide tables, time series and files. It is compatible with a variety of open-source standard interfaces such as HBase, Cassandra and OpenTSDB. It is the preferred database for Internet, IoT, Internet of vehicles, advertising, social networking, monitoring, games, risk control and other scenarios, It is also one of the databases that provide key support for Alibaba's core business. For more information about Lindorm, please refer to the cloud native multimode database Lindorm.

Lindorm provides a unified standard SQL portal for developers to get started quickly and easily operate and maintain massive data. SearchIndex is the secondary index of lindorm wide table, which is mainly used to help businesses realize rapid retrieval and analysis. This article introduces how to operate SearchIndex through a simple SQL interface.

2, SQL operation SearchIndex

Open full-text index

After you purchase the Lindorm wide table engine, you can activate the "full text index" function on the database instance console, as shown in the following figure:

Download SQL tools

Enter the database instance console and download the SQL tool: lindorm cli in "database connection". At the same time, in order to enable the local machine to access the cluster, you need to "open the external network address" and add the local ip to the white list.

Connection cluster

After decompressing the lindorm cli downloaded in the previous step, directly execute the following command:

./lindorm-cli -url jdbc:lindorm:table:url=http://ld-xxxx-proxy-lindorm-pub.lindorm.rds.aliyuncs.com:30060 -username xxx -password xxx

The public network connection address, user name and password can be obtained in "database connection" on the database console. See the screenshot in the previous chapter.

Note: the white list needs to be configured in advance.

Execute SQL statement

• Create database

create schema testSchema;

• Table creation

use testSchema;
// Create a table with id as the primary key and name, age and address as the non primary key
create table if not exists testTable(id varchar, name varchar, age bigint, address varchar, constraint primary key (id));

• Create SearchIndex

// Index the non primary key column of the table, where address is a word segmentation field, using IK word splitter
create search index if not exists testIdx on testTable(name,age,address(type=text,analyzer=ik)) with  (indexState=ACTIVE);

• Write test data

upsert into  testTable(id,name,age,address) values ('1', 'Zhang San', 25, 'Beijing Chaoyang District Greenland Center c Seat');
upsert into  testTable(id,name,age,address) values ('2', 'Li Si', 30, 'Building 2, Zhangjiang artificial intelligence island, Shangke Road');
upsert into  testTable(id,name,age,address) values ('3', 'Wang Wu', 28, '3331 Keyuan South Road, Nanshan District, Shenzhen');
upsert into  testTable(id,name,age,address) values ('4', 'Zhao Liu', 36, 'No. 969, Wenyi West Road, Yuhang District, Hangzhou');

query

select name  from  testtable where address = 'Chaoyang District' limit 10;

select *  from  testtable where age > 30 and address = 'Hangzhou' limit 10;

select name  from  testtable where name = 'Wang Wu' limit 10;

Non primary key queries generally consume a lot of system resources. For system stability, such queries will be rejected by default. If a secondary index is created based on these non primary keys, it can be executed efficiently.

For more SQL syntax, please refer to the syntax manual.

3, Summary

SearchIndex is the secondary index of Lindorm wide table. When you want to speed up the retrieval and analysis, you only need to create an index on the table, the written data will automatically build the index, and the server will automatically route to the index during query. The whole use process will invade the business code 0.

SearchIndex has rich application scenarios within Alibaba and on the public cloud. If you are facing the business architecture design of order query, logistics trajectory, big data portrait, Internet of vehicles and other scenarios, you can try to apply Lindorm SearchIndex to the architecture, which will bring great benefits to the development and storage costs.

Original link
This article is the original content of Alibaba cloud and cannot be reproduced without permission.