Create a mapping table

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

Prerequisites

Install Tablestore SDK for Go and initialize a client. SQL query requires version 1.7.4 or later. We recommend that you use the latest version.
Create the data table or search index for which you want to create a mapping table.

Description

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

func (client *TableStoreClient) SQLQuery(request *SQLQueryRequest) (*SQLQueryResponse, error)

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 SQL syntax, field types, and parameters of the two mapping types, see DDL operations.

The following example creates a mapping table for the example_table data table.

request := &tablestore.SQLQueryRequest{Query: `
CREATE TABLE example_table (
    tenant_id BIGINT,
    pk VARCHAR(1024),
    name MEDIUMTEXT,
    score BIGINT,
    PRIMARY KEY(tenant_id, pk)
)`}

if _, err := client.SQLQuery(request); err != nil {
    log.Fatalf("SQLQuery failed: %v", err)
}

Parameters

SQLQueryRequest contains the following parameters.

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.

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.

request := &tablestore.SQLQueryRequest{Query: `
CREATE TABLE example_search_table (
    tenant_id BIGINT,
    pk MEDIUMTEXT,
    name MEDIUMTEXT,
    score BIGINT
) ENGINE='searchindex'
ENGINE_ATTRIBUTE='{"index_name":"example_index","table_name":"example_table"}'`}

if _, err := client.SQLQuery(request); err != nil {
    log.Fatalf("SQLQuery failed: %v", err)
}

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.