Create a mapping table - Tablestore SDK for Java - Tablestore

Tablestore SDK for Java creates a mapping table for an existing table or search index to enable SQL access to data.

Prerequisites

Install the Tablestore SDK for Java and initialize a client.
Use Tablestore SDK for Java 5.13.0 or later.
Create the data table for which you want to create a mapping table. To create a mapping table for a search index, first create the search index.

Description

Call the sqlQuery method to execute a CREATE TABLE statement and create a mapping table for an existing table or search index.

SQLQueryResponse sqlQuery(SQLQueryRequest request)

For a data table, the mapping table must have the same name as the data table. Field names are case-insensitive, and field types must correspond to the data table field types. For a search index, you can customize the mapping table name and use ENGINE and ENGINE_ATTRIBUTE to specify the data table and search index.

For the syntax, field types, and parameters of the CREATE TABLE statement, see DDL operations.

The following example creates a mapping table for the example_table data table, which contains a string primary key column and four attribute columns.

String query = "CREATE TABLE example_table ("
        + "pk VARCHAR(1024), "
        + "long_value BIGINT, "
        + "double_value DOUBLE, "
        + "string_value MEDIUMTEXT, "
        + "bool_value BOOL, "
        + "PRIMARY KEY(pk))";
SQLQueryRequest request = new SQLQueryRequest(query);

client.sqlQuery(request);

Parameters

SQLQueryRequest contains the following parameter.

Name	Type	Description
query (required)	String	The `CREATE TABLE` statement that defines the mapping table name, fields, primary key, and query engine. Data table mappings and search index mappings use different syntax.

Scenario examples

Create a mapping table for a search index

To create a mapping table for a search index, set ENGINE to searchindex and specify the data table and search index in ENGINE_ATTRIBUTE. The following example creates a mapping table named example_search_table for the example_index search index of the example_table data table.

String tableName = "example_table";
String indexName = "example_index";
String mappingTableName = "example_search_table";
String query = String.format(
        "CREATE TABLE %s (pk MEDIUMTEXT, long_value BIGINT, "
                + "double_value DOUBLE, string_value MEDIUMTEXT, "
                + "bool_value BOOL) ENGINE='searchindex' "
                + "ENGINE_ATTRIBUTE='{\"index_name\":\"%s\","
                + "\"table_name\":\"%s\"}'",
        mappingTableName, indexName, tableName);
SQLQueryRequest request = new SQLQueryRequest(query);

client.sqlQuery(request);

FAQ

The `Table 'instance.table' doesn't exist` error is returned when you create a mapping table

Cause: The specified data table or search index does not exist. The CREATE TABLE statement creates only a mapping table for an existing data table or search index. It does not create the data table or search index.

Solution: Check whether the data table or search index name is correct and make sure that the data table or search index exists. Then, execute the CREATE TABLE statement again.

The `Table 'instance.table' already exists` error is returned when you create a mapping table

Cause: The mapping table already exists. It may have been created by a CREATE TABLE statement or automatically created when a DESCRIBE or SELECT statement was executed without an explicit mapping table. An automatically created mapping table contains only the primary key columns and predefined columns of the data table.

Solution:

If you want to prevent an error when the mapping table already exists, add IF NOT EXISTS to the CREATE TABLE statement.
If you want to change the schema of the mapping table, first execute a DESCRIBE statement to view the schema. You can use an ALTER TABLE statement to modify a mapping table created by a CREATE TABLE statement. An automatically created mapping table cannot be modified. After you assess the impact on your workloads, execute a DROP MAPPING TABLE statement to delete the mapping table and then recreate it.