Tablestore:Use Tablestore SDKs to use the SQL query feature

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), Singapore, Indonesia (Jakarta), Germany (Frankfurt), SAU (Riyadh - Partner Region), and US (Virginia) 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 Use a RAM policy to 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 Create an AccessKey pair.

• A data table is created.

• An OTSClient instance is initialized. For more information, see Initialize an OTSClient instance.

Procedure

You can use Tablestore SDK for Java, Tablestore SDK for Go, Tablestore SDK for Python, Tablestore SDK for Node.js, Tablestore SDK for .NET, or Tablestore SDK for PHP to query data by executing SQL statements. In this example, Tablestore SDK for Java is used.

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.

   The following sample code provides an example on how to execute the create table test_table (pk varchar(1024), long_value bigint(20), double_value double, string_value mediumtext, bool_value bool, primary key(pk)) statement to create a mapping table for a 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(20), double_value double, string_value mediumtext, bool_value bool, primary key(pk))");
   
       client.sqlQuery(request);
   }

2. Execute the SELECT statement to query data in the table.

   The following sample code provides an example on how to 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 return up to 20 rows of data. 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 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

FAQ

• How do I select a query method when I use SQL to query data?

• How do I troubleshoot common errors of SQL queries?

• What do I do if the "The sql scanned rows of main table exceeds the quota, main table rows quota is 100000" error message appears when I use SQL to query data?

References

• You can use the SQL query feature in the Tablestore console or Tablestore CLI, or by using Java Database Connectivity (JDBC) or the Tablestore driver for Go. For more information, see Use the SQL query feature in the Tablestore console, Use the SQL query feature in the Tablestore CLI, Use JDBC, Use the Tablestore driver for Go to query data by executing SQL statements.

• If you want to accelerate data queries and computing by executing SQL statements, you can create a secondary index or a search index. For more information, see Index selection policy and Computing pushdown.

• You can also use computing engines, such as MaxCompute, Spark, Hive, HadoopMR, Function Compute, Flink, and PrestoDB, to compute and analyze data in tables. For more information, see Overview.

• To visualize data, you can use Grafana. For example, you can use Grafana to display Tablestore data in charts. For more information, see Connect Tablestore to Grafana.