After you create a secondary index for a data table, you can use the index table, view information about the index table, query data by using the index table, and delete the index table.

Prerequisites

  • A data table for which the max versions parameter is set to 1 is created.
  • Predefined columns are specified for the data table.

Create a secondary index

Note Secondary indexes include global secondary indexes and local secondary indexes. For more information, see Overview.
  • Command syntax
    create_index -t <tableName> -n <indexName> -i <indexType> --pk <primaryKeyName,primaryKeyName> --attr <definedColumn,definedColumn>

    The following table describes the parameters that you can configure to create a secondary index.

    Parameter Required Example Description
    -t,--table No mytable The name of the data table.
    -n,--name Yes index0 The name of the secondary index that you want to create.
    -i,--index_type No global The type of the secondary index. Valid values:
    • global: global secondary index. This is the default value.

      If you use a global secondary index, Tablestore automatically synchronizes the data from the indexed columns and primary key columns of a data table to the columns of an index table in asynchronous mode. The synchronization latency is within a few milliseconds.

    • local: local secondary index.

      If you use a local secondary index, Tablestore automatically synchronizes the data from the indexed columns and primary key columns of a data table to the columns of an index table in synchronous mode. After data is written to the data table, you can query the data from the index table.

    -k,--pk Yes uid,pid The index columns of the index table. The index columns are a combination of primary key columns and predefined columns of the data table.

    If you use a local secondary index, the first primary key column of the index table must be the same as the first primary key column of the data table for which the index table is created.

    -a,--attr Yes col0,col1 The attribute columns of the index table. The attribute columns are a combination of predefined columns of the data table.
    -b,--without_base_data No N/A Specifies that the secondary index that you want to create does not include the existing data in the data table.
  • Examples

    The following code provides an example on how to create a global secondary index named index0 for the data table named mytable. In this example, the secondary index includes existing data of the data table. 

     create_index -t mytable -n index0 -i global --pk pid,uid -a name,col0

    The following code provides an example on how to create a global secondary index named index1 for the data table named mytable. In this example, the secondary index does not include the existing data of the data table. 

     create_index -t mytable -n index1 -i global --pk pid,uid -a name,col0 -b

    The following code provides an example on how to create a local secondary index named index2 for the data table named mytable. In this example, the secondary index includes the existing data of the data table. 

     create_index -t mytable -n index2 -i local -k uid,name -a col0,col1

Use tables

Select the data table on which you want to perform table operations or data operations.

  • Command syntax
    use --wc -t <tableName>

    The following table describes the parameters that you must configure to use a data table.

    Parameter Required Example Description
    --wc No N/A Specifies that the table on which you want to perform operations is a data table or an index table.
    -t,--table Yes index0 The name of the index table.
  • Examples

    The following code provides an example on how to use the index table named index0: 

    use -t index0

View information about secondary indexes

You can query information about a data table and then save the information to a JSON file on your local PC.

  • Command syntax
    desc -t <tableName> -o /localpath/filename.json

    The following table describes the parameters that you can configure to query the information about a data table.

    Parameter Required Example Description
    -t,--table No index0 The name of the data table or index table.
    -f,--print_format No json The output format of the information about the table. Default value: json. Valid values: json and table.
    -o,--output No /tmp/describe_table_meta.json The path of the local JSON file in which the information about the table is stored.
  • Examples

    The following sample code shows how to query information about the current table. 

    desc
    The following sample code shows how to query information about the current data table and save the information to the local file named describe_table_meta.json: 
    desc -o  /tmp/describe_table_meta.json

Query data by using secondary indexes

Read a single row of data

You can read data from a data table and export the data to a local JSON file.

Note If the row that you want to read does not exist, an empty result is returned.
  • Command syntax
    get --pk '[primaryKeyValue,primaryKeyValue]'

    The following table describes the parameters you can configure to read a row of data.

    Parameter Required Example Description
    -p, --pk Yes ["86",6771] The values of the primary key columns of the data table. The values are included in an array.
    Important The number and types of the primary key columns that you specify must be the same as the actual number and types of primary key columns in the data table.
    --columns No name,uid The set of columns that you want to read. You can specify the name of a primary key column or an attribute column. If you do not specify a column name, all data in the row is returned.
    --max_version No 1 The maximum number of data versions that you want to read.
    --time_range_start No 1626860469000 The version range of data that you want to read. time_range_start specifies the start timestamp and time_range_end specifies the end timestamp. The range includes the start value and excludes the end value.
    --time_range_end No 1626865270000
    --time_range_specific No 1626862870000 The specific version of data that you want to read.
    -o, --output No /tmp/querydata.json The local path of the JSON file to which the query results are exported.
  • Example
    Read a row of data in which the value of the first primary key column is "86" and the value of the second primary key column is 6771. 
    get --pk '["86",6771]'

Scan data

You can scan a data table to obtain all data or a specified number of rows of data in the data table.

  • Command syntax
    scan --limit limit
    The following table describes parameters that you can configure to scan data in a data table.
    Parameter Required Example Description
    --limit No 10 Optional. The maximum number of rows that you want to return. If you do not configure this parameter, all data in the data table is scanned.
  • Example
    The following sample code shows how to scan data in a data table to return up to 10 rows of data: 
    scan --limit 10

Export data

You can export data from a data table to a local JSON file.

  • Command syntax
    scan -o /localpath/filename.json -c attributeColumnName,attributeColumnName,attributeColumnName

    The following table describes the parameters you can configure to export data from a data table to a local file.

    Parameter Required Example Description
    -c, --columns Yes uid,name The set of columns that you want to export. You can specify the name of a primary key column or an attribute column. If you do not specify a column name, all data in the row is exported.
    --max_version No 1 The maximum number of data versions that can be exported.
    --time_range_start No 1626865596000 The version range of data that you want to export. time_range_start specifies the start timestamp and time_range_end specifies the end timestamp. The range includes the start value and excludes the end value.
    --time_range_end No 1626869196000
    --time_range_specific No 1626867396000 The specific version of data that you want to export.
    --backward No N/A Specifies that the system sorts the exported data in descending order of primary keys.
    -o, --output Yes /tmp/mydata.json The local path of the JSON file to which the query results are exported.
    -l, --limit No 10 The maximum number of rows that you want to return in the query.
    -b, --begin No 1000 The value range of data that you want to export.
    -e, --end No 2000
  • Examples
    The following sample code shows how to export all data from the current table to the mydata.json local file: 
    scan -o /tmp/mydata.json
    The following sample code shows how to export data from the uid and name columns of the current table to the mydata.json local file: 
    scan -o /tmp/mydata.json -c uid,name

Delete secondary indexes

This section describes how to delete index tables that you no longer need.

  • Command syntax
    drop_index -t <tableName> -i <indexName> -y

    The following table describes the parameters that you must configure to use a data table.

    Parameter Required Example Description
    -t,--table No mytable The name of the data table.
    -i,--index Yes index0 The name of the secondary index.
    -y,--yes Yes N/A Specifies that the confirmation information must appear. This parameter is required.
  • Examples

    The following code provides an example on how to delete the index table named index0 for the current data table: 

    drop_index -i index0 -y

    The following code provides an example on how to delete the index table named index0 for the data table named mytable: 

    drop_index -t mytable -i index0 -y