Tablestore:Use Tablestore SDKs

Usage notes

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

Prerequisites

• If you want to query data as a Resource Access Management (RAM) user, a RAM user is created and all SQL operation permissions are granted to the RAM user. You can configure "Action": "ots:SQL*" in a custom policy to grant all SQL operation permissions to the RAM user. For more information, see Configure user permissions.

• 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 Initialize an OTSClient instance.

Use Tablestore SDKs

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

• Tablestore SDK for Java: SQL query

• Tablestore SDK for Go: SQL query

• Tablestore SDK for Python: SQL query

• Tablestore SDK for Node.js: SQL query

• Tablestore SDK for .NET: SQL query

• Tablestore SDK for PHP: SQL query

Parameters

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 an 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, response schema, and response results of the SQL statement.

   private static void queryData(SyncClient client) {
       // Create an 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 response:

   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