SQL queries

Prerequisites

• The Tablestore CLI is installed with access credentials configured. For more information, see Tablestore CLI.

• A data table or timeseries table created. For more information, see Data table operations or Time series table operations.

Supported SQL statements

The Tablestore CLI supports the following SQL statements. For the complete syntax, see SQL query.

Enter and exit SQL mode

Run sql to enter SQL mode and exit; to return to command mode.

sql

After you enter SQL mode, the CLI displays:

Enter 'SQL' mode, input 'exit;' if you want to quit into 'COMMAND' mode.

Create a mapping

Data tables require a mapping for SQL queries.

CREATE TABLE mytable(
    `uid` VARCHAR(1024),
    `pid` BIGINT(20),
    `col1` BIGINT(20),
    `col2` MEDIUMTEXT,
    PRIMARY KEY(`uid`,`pid`)
);

View mapping tables

SHOW TABLES lists all mapping tables in the current instance.

SHOW TABLES;

Sample output:

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

mytable is the mapping table of a data table. mytstable is a timeseries data table, and mytstable::meta is the timeseries metadata table. Both timeseries tables and their metadata tables support SQL queries.

View mapping table details

To inspect column details, run DESCRIBE on a mapping table.

DESCRIBE mytable;

Sample output:

+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| uid   | varchar(1024) | NO | PRI | null    |       |
+-------+------------+------+-----+---------+-------+
| pid   | bigint(20) | NO   | PRI | null    |       |
+-------+------------+------+-----+---------+-------+
| col1  | bigint(20) | YES  |     | null    |       |
+-------+------------+------+-----+---------+-------+
| col2  | mediumtext | YES  |     | null    |       |
+-------+------------+------+-----+---------+-------+

Timeseries tables use a fixed single-value schema:

DESCRIBE mytstable;

+-------------------+---------------+------+-----+---------+-------+
| Field             | Type          | Null | Key | Default | Extra |
+-------------------+---------------+------+-----+---------+-------+
| _m_name           | varchar(1000) | NO   | PRI | null    |       |
+-------------------+---------------+------+-----+---------+-------+
| _data_source      | varchar(1000) | NO   | PRI | null    |       |
+-------------------+---------------+------+-----+---------+-------+
| _tags             | varchar(1000) | NO   | PRI | null    |       |
+-------------------+---------------+------+-----+---------+-------+
| _time             | bigint(20)    | NO   | PRI | null    |       |
+-------------------+---------------+------+-----+---------+-------+
| _field_name       | varchar(1000) | YES  |     | null    |       |
+-------------------+---------------+------+-----+---------+-------+
| _long_value       | bigint(20)    | YES  |     | null    |       |
+-------------------+---------------+------+-----+---------+-------+
| _double_value     | double        | YES  |     | null    |       |
+-------------------+---------------+------+-----+---------+-------+
| _bool_value       | tinyint(1)    | YES  |     | null    |       |
+-------------------+---------------+------+-----+---------+-------+
| _string_value     | varchar(1000) | YES  |     | null    |       |
+-------------------+---------------+------+-----+---------+-------+
| _binary_value     | mediumblob    | YES  |     | null    |       |
+-------------------+---------------+------+-----+---------+-------+
| _attributes       | varchar(1000) | YES  |     | null    |       |
+-------------------+---------------+------+-----+---------+-------+
| _meta_update_time | bigint(20)    | YES  |     | null    |       |
+-------------------+---------------+------+-----+---------+-------+

View index information

SHOW INDEX displays the indexes associated with a mapping table and their column details.

SHOW INDEX FROM mytable;

Query data

Use a SELECT statement to query data from a mapping table.

SELECT * FROM mytable;

Examples

• Filter by specific conditions.

  SELECT * FROM mytable WHERE uid = 'user001' AND pid = 1;

• Count the number of rows.

  SELECT COUNT(*) FROM mytable;

• Limit the number of returned rows.

  SELECT * FROM mytable LIMIT 10;

• Query timeseries table data.

  SELECT * FROM mytstable LIMIT 10;

Write and modify data

In SQL mode, run INSERT, UPDATE, and DELETE statements on non-transaction tables. For the complete syntax and constraints, see DML operations.

Examples

• Insert a row.

  INSERT INTO mytable (uid, pid, col1) VALUES ('user001', 1, 100);

• Update a specific row.

  UPDATE mytable SET col1 = 200 WHERE uid = 'user001' AND pid = 1;

• Delete a specific row.

  DELETE FROM mytable WHERE uid = 'user001' AND pid = 1;

Update mapping table columns

If you add or remove columns from a data table, use ALTER TABLE to update the mapping.

ALTER TABLE mytable ADD COLUMN col3 MEDIUMTEXT;

Delete a mapping

When the attribute columns of a data table change, delete the mapping and recreate it.

DROP MAPPING TABLE mytable;