Create a mapping table

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

Prerequisites

Install Tablestore SDK for Python and initialize a client.
SQL query requires version 5.4.2 or later. We recommend that you use the latest version.

Description

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

def exe_sql_query(self, query)

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.

query = """
CREATE TABLE example_table (
    pk VARCHAR(1024), long_value BIGINT, double_value DOUBLE,
    string_value MEDIUMTEXT, bool_value BOOL, PRIMARY KEY(pk)
)
"""
client.exe_sql_query(query)

Parameters

The exe_sql_query method contains the following parameter.

Name	Type	Description
query (required)	String	The `CREATE TABLE` statement to execute. The statement 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

Set ENGINE to searchindex and specify the data table and search index in ENGINE_ATTRIBUTE. The following example creates the example_search_table mapping table for the example_index search index of the example_table data table.

query = """
CREATE TABLE example_search_table (
    pk MEDIUMTEXT, long_value BIGINT, string_value MEDIUMTEXT
) ENGINE='searchindex'
ENGINE_ATTRIBUTE='{"index_name":"example_index","table_name":"example_table"}'
"""
client.exe_sql_query(query)

FAQ

The `Table 'instance.table' doesn't exist` error is returned

Cause: The specified data table or search index does not exist. The CREATE TABLE statement creates only the mapping table.

Solution: Check the names, make sure that the data table or search index exists, and execute the statement again.

The `Table 'instance.table' already exists` error is returned

Cause: The mapping table already exists. A DESCRIBE or SELECT statement can also automatically create a mapping table that contains only primary key columns and predefined columns.

Solution: To prevent duplicate-creation errors, use IF NOT EXISTS. To change an automatically created mapping table, assess the impact, delete the mapping table, and recreate it.