Use Tablestore SDK for Node.js to create a mapping table by executing SQL statements-Tablestore(Tablestore)-阿里云帮助中心

You can execute the CREATE TABLE statement to create a mapping table for an existing table or search index. When you create a mapping table, specify the field names and types, primary key information, and the execution engine you want to use for data queries based on your business requirements.

Note

Tablestore does not automatically create SQL mapping tables for data tables. To access a data table by using SQL, you must first execute a CREATE TABLE statement to create a mapping table.

For more information, see Create a mapping table for a table and Create mapping tables for search indexes.

Prerequisites

Before you begin, make sure that:

A client is initialized. For more information, see Initialize a Tablestore client.
A data table is created.
To create a mapping table for a search index, first create the search index.

Parameters

Parameter	Description
query	The SQL statement used to create the mapping table. The column types in the statement must match the data types of the corresponding columns in the data table.

Examples

Note

This topic describes how to create a mapping table for a data table. To create a mapping table for a search index, replace the SQL statement in the sample code with the SQL statement for a search index mapping table.

Unlike CREATE TABLE in a relational database, this statement does not create a new table. It creates a SQL mapping over an existing Tablestore data table, enabling SQL-based queries on NoSQL data.

The following example creates a mapping table for test_table using the statement create table test_table (pk varchar(1024), long_value bigint, double_value double, string_value mediumtext, bool_value bool, primary key(pk)).

const params = {
  // The SQL statement to create a mapping table.
  // Column types must match the data types of the corresponding columns in the data table.
  // For SQL syntax details, see the SQL reference documentation.
  query: "create table test_table (pk varchar(1024), long_value bigint, double_value double, string_value mediumtext, bool_value bool, primary key(pk))",
}

client.sqlQuery(params, function (err, data) {
  if (err) {
    console.log('sqlQuery error:', err.toString());
  } else {
    console.log('sqlQuery success:', data);
  }
});

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 was created by a CREATE TABLE statement.

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, and then use an ALTER TABLE statement to modify the mapping table.

References

After creating a mapping table, you can update its attribute columns, query mapping table information, list mapping table names, query index information, and delete the mapping table. For more information, see Update attribute columns of a mapping table, Query information about a table, List the names of tables, Query index information about tables, or Delete a mapping table.
Use the SELECT statement to query and analyze data in a mapping table. For more information, see Query data.