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.

Parameters

Examples

The following sample code provides an example on how to query all data in the table named test_table:

const client = require('./client');

const params = {
    query: "select * from test_table",
}

client.sqlQuery(params, function (err, resp) {
    if (err) {
        console.log('sqlQuery error:', err.toString());
    } else {
        console.log('sqlQuery success:', resp);
        console.log(resp.sqlRows.rowCount.toFloat64());
        console.log(resp.sqlRows.columnCount);
        console.log(resp.sqlRows.sqlTableMeta)
        for (let i = 0; i < resp.sqlRows.rowCount.toFloat64(); i++) {
            for (let j = 0; j < resp.sqlRows.columnCount; j++) {
                let data = resp.sqlRows.get(i, j);
                // Process data of the BINARY type.
                if (resp.sqlRows.sqlTableMeta.schemas[j].typeName === "BINARY") {
                    let int8Array = data.valueArray();
                    console.log(int8Array);
                }
                // Process data of the LONG type.
                if (resp.sqlRows.sqlTableMeta.schemas[j].typeName === "LONG") {
                    console.log(data.toFloat64());
                }
                console.log("i:" + i, ", j:" + j + ":" + data);
            }
        }
    }
});

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 removed 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 index information about tables.

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

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

• If your business requires multi-dimensional queries and data analysis, you can create a search index and specify the required attribute columns as the fields of the search index. Then, you can query and analyze data by using the search index. For example, you can use a search index to perform queries based on non-primary key columns, Boolean queries, and fuzzy queries. You can also use a search index to obtain the maximum and minimum values, collect statistics about the number of rows, and group query results. For more information, see Search index.