All Products
Search

This Product

    Tablestore:Secondary index

    Document Center

    Tablestore:Secondary index

    Last Updated:Aug 27, 2024

    If you want to use one or more attribute columns of a data table as query conditions to query data, you can specify the attribute columns as the primary key columns of a secondary index that is created for the data table. This way, you can quickly query data based on the attribute columns. After you create a secondary index, you need to only write data to the data table and then use the secondary index to query data. This topic describes how to create a secondary index, query information about an index table, use an index table to query and export data, and delete an index table in the Tablestore CLI.

    Prerequisites

    Create a secondary index

    Note

    Secondary indexes are classified into 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.

      Tablestore automatically synchronizes data from the indexed columns and primary key columns of the data table to the columns of the index table that you want to create in asynchronous mode. The synchronization latency is within a few milliseconds.

    • local: local secondary index.

      Tablestore automatically synchronizes data from the indexed columns and primary key columns of the data table to the columns of the index table that you want to create in synchronous mode. After data is written to the data table, you can immediately query the data in 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 sample code provides an example on how to create a global secondary index named index0 for the mytable data table. In this example, the global secondary index includes the existing data of the data table. 

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

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

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

    The following sample code provides an example on how to create a local secondary index named index2 for the mytable data table. In this example, the local 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 a table

    Select the 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 can configure to use a 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 sample code provides an example on how to use an index table named index0: 

    use -t index0

    Query information about a secondary index

    Query information about a table and then save the information to a JSON file on your local computer.

    Command syntax

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

    The following table describes the parameters that you can configure to query information about a 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 provides an example on how to query information about the current table: 

    desc

    The following sample code provides an example on how to query information about the current table and save the information to a local file named describe_table_meta.json: 

    desc -o  /tmp/describe_table_meta.json

    Query data by using a secondary index

    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 of the command.

    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 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 columns that you want to read. You can specify the names of primary key columns or attribute columns. If you do not specify a column, all data in the row is returned.

    --max_version

    No

    1

    The maximum number of data versions that you can 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.

    Examples

    The following sample command provides an example on how to read a row of data whose value of the first primary key column is "86" and 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 the parameter of the command.

    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.

    Examples

    The following example provides an example on how to scan up to 10 rows of data in a data table. 

    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 of the command.

    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

    '["86", 6771]'

    The value range of data that you want to export. The range of the primary key is a left-closed, right-open interval.

    -e, --end

    No

    '["86", 6775]'

    Examples

    • Example 1

      The following sample command provides an example on how to export all data from the current table to the mydata.json local file. 

      scan -o /tmp/mydata.json

    • Example 2

      The following sample command provides an example on how to export the data in the uid and name columns of the current table to the mydata.json local file. 

      scan -o /tmp/mydata.json -c uid,name

    • Example 3

      The following sample command provides an example on how to export the data with the specified primary key of the current table to the mydata.json local file. The value of the first primary key column where the export starts is "86". The value of the second primary key column where the export starts is 6771. The value of the first primary key column where the export ends is "86". The value of the second primary key column where the export ends is 6775. 

      scan -o D:\\0testreport\\myh3.json -b '["86", 6771]' -e '["86", 6775]'

    Delete a secondary index

    Delete an index table that you no longer require.

    Command syntax

    drop_index -t <tableName> -i <indexName> -y

    The following table describes the parameters that you can configure to use a 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 that you want to delete.

    -y,--yes

    Yes

    N/A

    Specifies that the confirmation information is displayed. This parameter is required.

    Examples

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

    drop_index -i index0 -y

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

    drop_index -t mytable -i index0 -y