Tablestore:Query data

Prerequisites

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

• A mapping table is created. For more information, see Create mapping tables for tables.

Usage notes

Tablestore SDK for Java V5.13.0 and later support the SQL query feature. To use the SQL query feature, make sure that you use a version of Tablestore Java SDK that supports the SQL query feature. For more information about the version history of Tablestore SDK for Java, see Version history of Tablestore SDK for Java.

Parameters

Examples

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"));
    }
}

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 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

• 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.

• If an attribute column is added to or deleted from a data table, you can execute the ALTER TABLE statement to modify the mapping table that is created for the data table. For more information, see Update attribute columns of mapping tables.

• If you want to query the description of a table, you can execute the DESCRIBE statement. For more information, see Query information about tables.

• If you no longer require a mapping table that is created for a table or a search index, you can execute the DROP MAPPING TABLE statement to delete the mapping table. For more information, see Delete mapping tables.

• If you want to view the index information about a table, you can execute the SHOW INDEX statement. For more information, see Query the index information about tables.

• If you want to list the names of mapping tables in the current database, you can execute the SHOW TABLES statement. For more information, see List mapping table names.

• 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.