Tablestore:SQL query

Enter the SQL mode

Run the sql command to enter the SQL mode.

Create a mapping table

To query data in a data table, you must create a mapping table for the data table.

Run the following command to create a mapping table for the data table named mytable:

CREATE TABLE mytable(
     `uid` VARCHAR(1024),
     `pid` BIGINT(20),
     `b` DOUBLE,
     `c` BOOL,
     `d` MEDIUMTEXT,
     PRIMARY KEY(`uid`,`pid`)
);

Query mapping tables

Query the mapping tables that are created for data tables in an instance.

Run the SHOW TABLES; command to query the mapping tables that are created for data tables in an instance.

+----------------------+
| Tables_in_myinstance |
+----------------------+
| mytable              |
+----------------------+
| mytstable            |
+----------------------+
| mytstable::meta      |
+----------------------+

In the preceding command output, mytable is a mapping table that is created for a data table, mytstable is a mapping table that is created for a time series data table, and mytstable::meta is a mapping table that is created for a time series metadata table.

Query information about a mapping table

Query information about a mapping table.

DESCRIBE table_name;

DESCRIBE mytable;

+-------+---------------+------+-----+-------+
| Field | Type          | Null | Key | Extra |
+-------+---------------+------+-----+-------+
| uid   | varchar(1024) | NO   | PRI |       |
+-------+---------------+------+-----+-------+
| pid   | bigint(20)    | NO   | PRI |       |
+-------+---------------+------+-----+-------+
| b     | double        | YES  |     |       |
+-------+---------------+------+-----+-------+
| c     | tinyint(1)    | YES  |     |       |
+-------+---------------+------+-----+-------+
| d     | mediumtext    | YES  |     |       |
+-------+---------------+------+-----+-------+

Query the data in a table

Query the data in a table by executing the SELECT statement.

SELECT * FROM mytable;

Delete a mapping table

If changes are made to the attribute columns of a data table, you can delete the mapping table of the data table and create a new mapping table.

DROP MAPPING TABLE table_name;

DROP MAPPING TABLE mytable;

Exit the SQL mode

Run the exit; command to exit the SQL mode.

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 by using the Tablestore console, Tablestore SDKs, JDBC, and JDBC driver for Go.

  • Use the SQL query feature in the Tablestore console

  • Use the SQL query feature in the Tablestore CLI

  • Use Tablestore SDKs to use the SQL query feature

  • Use JDBC

    • Use JDBC to access Tablestore

    • Use Hibernate to query data by executing SQL statements

    • Use MyBatis to query data by executing SQL statements

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