You can call the sqlQuery operation to access Tablestore by executing SQL statements.

Usage notes

The SQL query feature is available in the China (Hangzhou), China (Shanghai), China (Beijing), China (Zhangjiakou), China (Shenzhen),China (Hong Kong), Germany (Frankfurt), US (Virginia), India (Mumbai),and Singapore (Singapore) regions.

Prerequisites

  • If you want to use a RAM user to perform operations, a RAM user is created and all SQL operation permissions are granted to the RAM user. You can configure "Action": "ots:SQL*" in the custom policy to grant all SQL operation permissions to the RAM user. For more information, see Grant permissions to a RAM user.
  • An AccessKey pair that consists of an AccessKey ID and an AccessKey secret is obtained. For more information, see Obtain an AccessKey pair.
  • A data table is created.
  • A Tablestore client is initialized. For more information, see Initialization.

Use Tablestore SDKs

You can use the following Tablestore SDKs to perform a SQL query:

Parameters

Parameter Description
query The SQL statement. Configure the parameter based on the required feature.

Examples

After you execute the create table statement to create a mapping table for an existing table, you can execute the select statement to query data in the existing table.

  1. Execute the create table statement to create a mapping table for an existing table.

    Execute the create table test_table (pk varchar(1024), long_value bigint, double_value double, string_value mediumtext, bool_value bool, primary key(pk)) statement to create a mapping table for the table named test_table.

    private static void createTable(SyncClient client) {
        // Create a SQL request. 
        SQLQueryRequest request = new SQLQueryRequest("create table test_table (pk varchar(1024), long_value bigint, double_value double, string_value mediumtext, bool_value bool, primary key(pk))");
    
        // Obtain the response to the SQL request. 
        SQLQueryResponse response = client.sqlQuery(request);
    }
  2. Execute the select statement to query data in the table.

    Execute the select pk, long_value, double_value, string_value, bool_value from test_table limit 20 statement to query data in the table named test_table and set the maximum number of rows that you want to return to 20. The system returns the request type, the schema of the returned results, and the returned results of the query statement.

    private static void queryData(SyncClient client) {
        // Create a SQL request. 
        SQLQueryRequest request = new SQLQueryRequest("select pk, long_value, double_value, string_value, bool_value from test_table limit 20");
    
        // Obtain the response to the SQL request. 
        SQLQueryResponse response = client.sqlQuery(request);
    
        // Obtain the SQL request type. 
        System.out.println("response type: " + response.getSQLStatementType());
    
        // Obtain the schema of the returned results of the SQL request. 
        SQLTableMeta tableMeta = response.getSQLResultSet().getSQLTableMeta();
        System.out.println("response table meta: " + tableMeta.getSchema());
    
        // Obtain the returned results of the SQL request. 
        SQLResultSet resultSet = response.getSQLResultSet();
        System.out.println("response resultset:");
        while (resultSet.hasNext()) {
            SQLRow row = resultSet.next();
            System.out.println(row.getString(0) + ", " + row.getString("pk") + ", " +
                               row.getLong(1) + ", " + row.getLong("long_value") + ", " +
                               row.getDouble(2) + ", " + row.getDouble("double_value") + ", " +
                               row.getString(3) + ", " + row.getString("string_value") + ", " +
                               row.getBoolean(4) + ", " + row.getBoolean("bool_value"));
        }
    }
    Sample output:
    response type: SQL_SELECT
    response table meta: [pk:STRING, long_value:INTEGER, double_value:DOUBLE, string_value:STRING, bool_value:BOOLEAN]
    response resultset:
    binary_null, binary_null, 1, 1, 1.0, 1.0, a, a, false, false
    bool_null, bool_null, 1, 1, 1.0, 1.0, a, a, null, null
    double_null, double_null, 1, 1, null, null, a, a, true, true
    long_null, long_null, null, null, 1.0, 1.0, a, a, true, true
    string_null, string_null, 1, 1, 1.0, 1.0, null, null, false, false