Tablestore:What do I do if data latency exists when I query data by executing SQL statements?

Problem description

After new data is added to a data table or data in a data table is updated, data latency exists and I cannot obtain the latest data by executing SQL statements.

Possible cause

After you create a search index for a data table, the SQL engine automatically selects the search index to query data if the search index contains all data columns involved in the SQL statement that you execute to query data. For more information, see Index selection policy.

Synchronization latency may exist for the data in the search index. Therefore, the latest data may not return after you execute SQL statements even if the data is written to the data table.

Solution

• Explicitly specify that search indexes are not preferentially used

  When you query data by executing SQL statements, include the use index() command in the SQL statements to explicitly specify that search indexes are not preferentially used.

  For example, a data table named exampletable contains the pk, name, and context data columns, and a search index that contains the preceding data columns is created for the data table. You can execute the following sample SQL statement in which no search index is specified to query data:

  SELECT pk,name,context FROM exampletable use index();  -- Specify that search indexes are not preferentially used when you query data from the data table.

• Use data read operations to query data

  If you want to read data based on the primary key, you can use data read operations. Tablestore provides the GetRow operation to allow you to read a single row of data and provides operations such as BatchGetRow and GetRange to allow you to read multiple rows of data in a batch. For more information, see Read data.

  Operation	Description	Scenario
  GetRow	Reads data in a single row based on the specified primary key.	This operation is suitable for scenarios in which the values of all primary key columns of the row that you want to read can be determined and the number of rows that you want to read is small.
  BatchGetRow	Reads multiple rows of data from one or more tables at a time.	This operation is suitable for scenarios in which the values of all primary key columns of the rows that you want to read can be determined and the number of rows that you want to read is large or you want to read data from multiple tables.
  GetRange	Reads data whose primary key values are within a specific range.	This operation is suitable for scenarios in which the range of primary key values or the prefix of primary key values of the rows that you want to read can be determined.

• Use a secondary index

  Important

  You are charged storage fees when you use the secondary index feature. When you write data to a data table, computing resources are consumed to create a search index and read data.

  You can use a secondary index to query data by executing SQL statements. Perform the following steps.

  1. Create a secondary index for the data table. For more information, see Use secondary indexes in the Tablestore console.

     We recommend that you create a local secondary index to meet the strong consistency requirements.

  2. You can execute SQL statements to query data based on a secondary index by using one of the following methods:

     For example, a data table named exampletable contains the pk, name, and context data columns, a secondary index named exampletable_index is created for the data table, and the secondary index contains the preceding data columns.

     • Method 1: Explicitly specify a secondary index to query data from a data table

       If you do not explicitly specify an index when you execute an SQL statement to query data, Tablestore automatically determines whether to use an index based on the index selection policy.

       You can execute the following sample SQL statement in which a secondary index is explicitly specified by using the use index() command to query data:

       SELECT pk,name,context FROM exampletable use index(exampletable_index);  -- Explicitly specify that a secondary index is used to query data from a data table.

     • Method 2: Query data by using a mapping table that is created for a secondary index

       1. Execute the CREATE TABLE statement to create a mapping table named exampletable_index for the secondary index. For more information, see Create a mapping table for a table.

       2. Execute the following sample SELECT SQL statement to query data by using the mapping table that is created for the secondary index:

          SELECT pk,name,context FROM exampletable_index;  -- Query data by using the mapping table that is created for the secondary index.