Build a store search system for hundreds of millions of records

Solution overview

A store search platform must run multi-condition queries across hundreds of millions of records. Typical use cases include finding stores of a specific type near a location, searching by name with results sorted by rating, and aggregating store counts and ratings by type. The core challenge is combining efficient geo-location retrieval with flexible multi-condition queries.

A single Tablestore table supports petabyte-scale data storage and tens of millions of TPS. Search indexes support GEO_POINT geo-location indexing, full-text indexing, multi-condition queries, and aggregations.

Solution design

This solution uses one data table for store records and a search index for geo-location and multi-condition retrieval. The following table describes the fields in the data table and search index.

Field type	Field	Data type	Index field type	Description
Primary key column	_id	STRING	—	The MD5 hash of the store ID. This value is used as the partition key to evenly distribute data.
Attribute columns	shop_id	STRING	KEYWORD	The store ID.
	type	STRING	KEYWORD	The store type, such as bubble tea, coffee, or hotpot.
	name	STRING	TEXT	The store name. The TEXT type supports full-text search.
	pos	STRING	GEO_POINT	The geographic location of the store, in the format of "latitude,longitude" (for example, "30.270000,120.150000").
	score	DOUBLE	DOUBLE	The store rating (1.0 to 5.0).
	avg_price	DOUBLE	DOUBLE	The average spending per person (CNY).

Implementation

Before you begin, activate Tablestore and create an instance. If you already have an instance, skip this step. For more information, see Activate Tablestore and create an instance.

Note

The following examples use Tablestore SDK for Java and Tablestore SDK for Python. Before you begin, install and configure Java SDK or Python SDK.

Step 1: Create a data table and a search index

When you create a data table, specify only the primary key columns. Attribute columns do not need to be predefined. Then create a search index that includes a GEO_POINT geo-location index and a TEXT full-text index.

Java

// Create a data table
private static void createTable(SyncClient client) {
    TableMeta tableMeta = new TableMeta("shop");
    tableMeta.addPrimaryKeyColumn(new PrimaryKeySchema("_id", PrimaryKeyType.STRING));

    TableOptions tableOptions = new TableOptions();
    tableOptions.setMaxVersions(1);
    tableOptions.setTimeToLive(-1);

    client.createTable(new CreateTableRequest(tableMeta, tableOptions));
}

// Create a search index
private static void createSearchIndex(SyncClient client) {
    CreateSearchIndexRequest request = new CreateSearchIndexRequest();
    request.setTableName("shop");
    request.setIndexName("shop_index");

    IndexSchema schema = new IndexSchema();
    schema.setFieldSchemas(Arrays.asList(
        new FieldSchema("shop_id", FieldType.KEYWORD).setIndex(true).setEnableSortAndAgg(true),
        new FieldSchema("type", FieldType.KEYWORD).setIndex(true).setEnableSortAndAgg(true),
        new FieldSchema("name", FieldType.TEXT).setIndex(true).setAnalyzer(FieldSchema.Analyzer.MaxWord),
        new FieldSchema("pos", FieldType.GEO_POINT).setIndex(true).setEnableSortAndAgg(true),
        new FieldSchema("score", FieldType.DOUBLE).setIndex(true).setEnableSortAndAgg(true),
        new FieldSchema("avg_price", FieldType.DOUBLE).setIndex(true).setEnableSortAndAgg(true)
    ));

    request.setIndexSchema(schema);
    client.createSearchIndex(request);
}

Python

# Create a data table
def create_table(client):
    table_meta = tablestore.TableMeta("shop", [("_id", "STRING")])
    table_options = tablestore.TableOptions(time_to_live=-1, max_version=1)
    reserved = tablestore.ReservedThroughput(tablestore.CapacityUnit(0, 0))
    client.create_table(table_meta, table_options, reserved)

# Create a search index
def create_search_index(client):
    fields = [
        tablestore.FieldSchema("shop_id", tablestore.FieldType.KEYWORD, index=True, enable_sort_and_agg=True),
        tablestore.FieldSchema("type", tablestore.FieldType.KEYWORD, index=True, enable_sort_and_agg=True),
        tablestore.FieldSchema("name", tablestore.FieldType.TEXT, index=True, analyzer="max_word"),
        tablestore.FieldSchema("pos", tablestore.FieldType.GEOPOINT, index=True, enable_sort_and_agg=True),
        tablestore.FieldSchema("score", tablestore.FieldType.DOUBLE, index=True, enable_sort_and_agg=True),
        tablestore.FieldSchema("avg_price", tablestore.FieldType.DOUBLE, index=True, enable_sort_and_agg=True),
    ]
    client.create_search_index("shop", "shop_index", tablestore.SearchIndexMeta(fields))

Step 2: Write test data

The following code uses BatchWriteRow to write 10,000 store records covering five store types. The geographic locations are distributed within the Hangzhou urban area (latitude 30.1° to 30.4°, longitude 120.0° to 120.3°). Modify the TOTAL_ROWS constant to adjust the data volume.

Java

private static final int TOTAL_ROWS = 10000;
private static final String[] TYPES = {"Milk Tea", "Coffee", "Hot Pot", "Japanese Food", "Bakery"};
private static final String[] PREFIXES = {"John's", "Jane's", "Bob's", "Alice's", "Fresh"};
private static final String[] SUFFIXES = {"Tea Shop", "Coffee House", "Hot Pot Place", "Cuisine House", "Bread House"};

private static void writeTestData(SyncClient client) throws Exception {
    Random rand = new Random(42);
    int written = 0;

    while (written < TOTAL_ROWS) {
        BatchWriteRowRequest batch = new BatchWriteRowRequest();
        int batchSize = Math.min(200, TOTAL_ROWS - written);

        for (int i = 0; i < batchSize; i++) {
            int idx = written + i;
            String shopId = String.format("shop-%06d", idx);
            String id = md5(shopId);
            String type = TYPES[rand.nextInt(TYPES.length)];
            String name = PREFIXES[rand.nextInt(PREFIXES.length)] + SUFFIXES[rand.nextInt(SUFFIXES.length)];

            // Random location within the Hangzhou urban area
            double lat = 30.1 + rand.nextDouble() * 0.3;
            double lon = 120.0 + rand.nextDouble() * 0.3;
            // GEO_POINT format: "latitude,longitude"
            String pos = String.format("%.6f,%.6f", lat, lon);
            double score = Math.round((3.0 + rand.nextDouble() * 2.0) * 10.0) / 10.0;
            double avgPrice = 10 + rand.nextInt(191);

            PrimaryKeyBuilder pkb = PrimaryKeyBuilder.createPrimaryKeyBuilder();
            pkb.addPrimaryKeyColumn("_id", PrimaryKeyValue.fromString(id));
            RowPutChange row = new RowPutChange("shop", pkb.build());
            row.addColumn("shop_id", ColumnValue.fromString(shopId));
            row.addColumn("type", ColumnValue.fromString(type));
            row.addColumn("name", ColumnValue.fromString(name));
            row.addColumn("pos", ColumnValue.fromString(pos));
            row.addColumn("score", ColumnValue.fromDouble(score));
            row.addColumn("avg_price", ColumnValue.fromDouble(avgPrice));
            batch.addRowChange(row);
        }
        client.batchWriteRow(batch);
        written += batchSize;
    }
    System.out.println("Successfully wrote " + TOTAL_ROWS + " rows of data.");
}

Python

import hashlib, random

TOTAL_ROWS = 10000
TYPES = ["Milk Tea", "Coffee", "Hot Pot", "Japanese Food", "Bakery"]
PREFIXES = ["John's", "Jane's", "Bob's", "Alice's", "Fresh"]
SUFFIXES = ["Tea Shop", "Coffee House", "Hot Pot Place", "Cuisine House", "Bread House"]

def write_test_data(client):
    rand = random.Random(42)
    written = 0

    while written < TOTAL_ROWS:
        batch_size = min(200, TOTAL_ROWS - written)
        put_rows = []

        for i in range(batch_size):
            idx = written + i
            shop_id = f"shop-{idx:06d}"
            _id = hashlib.md5(shop_id.encode()).hexdigest()
            shop_type = rand.choice(TYPES)
            name = rand.choice(PREFIXES) + rand.choice(SUFFIXES)

            lat = 30.1 + rand.random() * 0.3
            lon = 120.0 + rand.random() * 0.3
            pos = f"{lat:.6f},{lon:.6f}"
            score = round(3.0 + rand.random() * 2.0, 1)
            avg_price = float(10 + rand.randint(0, 190))

            pk = [("_id", _id)]
            cols = [
                ("shop_id", shop_id), ("type", shop_type), ("name", name),
                ("pos", pos), ("score", score), ("avg_price", avg_price),
            ]
            row = tablestore.Row(pk, cols)
            put_rows.append(tablestore.PutRowItem(row,
                tablestore.Condition(tablestore.RowExistenceExpectation.IGNORE)))

        request = tablestore.BatchWriteRowRequest()
        request.add(tablestore.TableInBatchWriteRowItem("shop", put_rows))
        client.batch_write_row(request)
        written += batch_size

    print(f"Successfully wrote {TOTAL_ROWS} rows of data.")

Step 3: Query data

After data is written, the search index syncs automatically, typically within seconds. The following examples cover three common store search use cases.

Use case 1: Search for nearby stores of a specific type

This use case combines GeoDistanceQuery (geo-distance query), TermQuery (exact match on store type), and RangeQuery (upper limit on average spending) to find bubble tea stores within 5 km of a specified location with average spending no more than CNY 50, sorted by rating in descending order.

Java

public static void queryNearbyShops(SyncClient client) {
    // Geo-distance query: within 5 km of the specified center point
    GeoDistanceQuery geoQuery = new GeoDistanceQuery();
    geoQuery.setFieldName("pos");
    geoQuery.setCenterPoint("30.270000,120.150000");
    geoQuery.setDistanceInMeter(5000);

    // Exact match on shop type
    TermQuery typeQuery = new TermQuery();
    typeQuery.setFieldName("type");
    typeQuery.setTerm(ColumnValue.fromString("Milk Tea"));

    // Average price per person no more than 50 CNY
    RangeQuery priceQuery = new RangeQuery();
    priceQuery.setFieldName("avg_price");
    priceQuery.lessThanOrEqual(ColumnValue.fromDouble(50));

    // Boolean query
    BoolQuery boolQuery = new BoolQuery();
    boolQuery.setMustQueries(Arrays.asList(geoQuery, typeQuery, priceQuery));

    SearchQuery searchQuery = new SearchQuery();
    searchQuery.setQuery(boolQuery);
    searchQuery.setGetTotalCount(true);
    searchQuery.setLimit(5);
    searchQuery.setSort(new Sort(Arrays.asList(new FieldSort("score", SortOrder.DESC))));

    SearchRequest searchRequest = new SearchRequest("shop", "shop_index", searchQuery);
    SearchRequest.ColumnsToGet columnsToGet = new SearchRequest.ColumnsToGet();
    columnsToGet.setReturnAll(true);
    searchRequest.setColumnsToGet(columnsToGet);

    SearchResponse response = client.search(searchRequest);
    System.out.println("Total matches: " + response.getTotalCount());
    for (Row row : response.getRows()) {
        System.out.println(row);
    }
}

Python

def query_nearby_shops(client):
    bool_query = tablestore.BoolQuery(must_queries=[
        tablestore.GeoDistanceQuery("pos", "30.270000,120.150000", 5000),
        tablestore.TermQuery("type", "bubble tea"),
        tablestore.RangeQuery("avg_price", range_to=50, include_upper=True),
    ])

    result = client.search("shop", "shop_index",
        tablestore.SearchQuery(bool_query, get_total_count=True, limit=5,
            sort=tablestore.Sort(sorters=[tablestore.FieldSort("score", tablestore.SortOrder.DESC)])),
        tablestore.ColumnsToGet(return_type=tablestore.ColumnReturnType.ALL))

    print(f"Total matches: {result.total_count}")
    for row in result.rows:
        print(row)

Use case 2: Search for stores by name

Use MatchQuery for a full-text search on the TEXT-type name field, with results sorted by rating in descending order.

Java

public static void queryByName(SyncClient client) {
    MatchQuery nameQuery = new MatchQuery();
    nameQuery.setFieldName("name");
    nameQuery.setText("Shen's");

    SearchQuery searchQuery = new SearchQuery();
    searchQuery.setQuery(nameQuery);
    searchQuery.setGetTotalCount(true);
    searchQuery.setLimit(5);
    searchQuery.setSort(new Sort(Arrays.asList(new FieldSort("score", SortOrder.DESC))));

    SearchRequest searchRequest = new SearchRequest("shop", "shop_index", searchQuery);
    SearchRequest.ColumnsToGet columnsToGet = new SearchRequest.ColumnsToGet();
    columnsToGet.setReturnAll(true);
    searchRequest.setColumnsToGet(columnsToGet);

    SearchResponse response = client.search(searchRequest);
    System.out.println("Total matches: " + response.getTotalCount());
    for (Row row : response.getRows()) {
        System.out.println(row);
    }
}

Python

def query_by_name(client):
    result = client.search("shop", "shop_index",
        tablestore.SearchQuery(tablestore.MatchQuery("name", "Shen's"),
            get_total_count=True, limit=5,
            sort=tablestore.Sort(sorters=[tablestore.FieldSort("score", tablestore.SortOrder.DESC)])),
        tablestore.ColumnsToGet(return_type=tablestore.ColumnReturnType.ALL))

    print(f"Total matches: {result.total_count}")
    for row in result.rows:
        print(row)

Use case 3: Aggregate statistics by store type

Use GroupByField to group stores by type, with Count and Avg sub-aggregations to calculate the number of stores and average rating for each type.

Java

public static void groupByType(SyncClient client) {
    SearchRequest searchRequest = SearchRequest.newBuilder()
        .tableName("shop").indexName("shop_index")
        .searchQuery(SearchQuery.newBuilder()
            .query(QueryBuilders.matchAll())
            .limit(0)
            .addGroupBy(GroupByBuilders.groupByField("shopType", "type")
                .size(20)
                .addSubAggregation(AggregationBuilders.count("shopCount", "shop_id"))
                .addSubAggregation(AggregationBuilders.avg("avgScore", "score"))
                .addGroupBySorter(GroupBySorter.subAggSortInDesc("shopCount"))
                .build())
            .build())
        .build();

    SearchResponse resp = client.search(searchRequest);
    GroupByFieldResult results = resp.getGroupByResults().getAsGroupByFieldResult("shopType");
    for (GroupByFieldResultItem item : results.getGroupByFieldResultItems()) {
        System.out.println("Type: " + item.getKey()
            + "\tShop count: " + item.getSubAggregationResults().getAsCountAggregationResult("shopCount").getValue()
            + "\tAvg score: " + String.format("%.1f",
                item.getSubAggregationResults().getAsAvgAggregationResult("avgScore").getValue()));
    }
}

Python

def group_by_type(client):
    group_by = tablestore.GroupByField("type", size=20,
        sub_aggs=[
            tablestore.Count("shop_id", name="shopCount"),
            tablestore.Avg("score", name="avgScore"),
        ],
        group_by_sort=[tablestore.SubAggSort(tablestore.SortOrder.DESC, "shopCount")],
        name="shopType")

    result = client.search("shop", "shop_index",
        tablestore.SearchQuery(tablestore.MatchAllQuery(), limit=0, group_bys=[group_by]),
        tablestore.ColumnsToGet(return_type=tablestore.ColumnReturnType.NONE))

    for item in result.group_by_results[0].items:
        aggs = {a.name: a.value for a in item.sub_aggs}
        print(f"Type: {item.key}\tStore count: {int(aggs['shopCount'])}\tAverage rating: {aggs['avgScore']:.1f}")

Clean up resources

Note

If you no longer need the resources created in this solution, delete them to avoid unnecessary charges.

Delete the search index first, then the data table. If you created the instance specifically for this solution, release the instance last.

Java

private static void cleanup(SyncClient client) {
    // 1. Delete the search index
    DeleteSearchIndexRequest deleteIndexReq = new DeleteSearchIndexRequest();
    deleteIndexReq.setTableName("shop");
    deleteIndexReq.setIndexName("shop_index");
    client.deleteSearchIndex(deleteIndexReq);

    // 2. Delete the data table
    client.deleteTable(new DeleteTableRequest("shop"));
}

Python

def cleanup(client):
    # 1. Delete the search index
    client.delete_search_index("shop", "shop_index")
    # 2. Delete the data table
    client.delete_table("shop")