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.
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.
-
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
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
What do I do if the "Table 'instancename.tablename' doesn't exist ?" error message is returned when I create a mapping table?
-
Possible cause
The table for which you want to create a mapping table does not exist.
-
Solution
You can execute the CREATE TABLE statement in SQL to create a mapping table only for an existing table or search index. Make sure that the table or search index for which you want to create a mapping table exists.
What do I do if the "Table 'instancename.tablename' already exist?" error message is returned when I create a mapping table?
-
Possible cause
A mapping table is created for the table.
-
The user manually created a mapping table for the table.
-
Tablestore automatically created a mapping table for the table. If you directly use SQL statements (such as DESCRIBE, SELECT, etc.) to query data in a data table without creating a mapping table for the data table, Tablestore automatically creates a mapping table for the data table. The automatically created mapping table only contains the primary key columns and predefined columns of the data table. You cannot update the attribute columns of the mapping table.
-
-
Solution
You can execute the
DESCRIBEstatement to query the schema of the mapping table. If the schema does not meet your business requirements, you can update the attribute columns of the mapping table. If the mapping table is automatically created by Tablestore, you cannot update the attribute columns. In this case, you can delete the mapping table and create a new one if you are sure that your business is not affected after you delete the mapping table.
For common errors and solutions of the SQL query feature, see Troubleshoot common SQL query errors.
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.