SQL query

Overview

The SQL query feature is compatible with MySQL query syntax. It also provides basic SQL Data Definition Language (DDL) capabilities to create tables. For existing data tables, you can run the CREATE TABLE statement to automatically create a mapping. You can then use SQL to access the data in the table.

The SQL query feature supports search indexes to quickly query data that meets specified conditions. When you use the SQL query feature, the system automatically selects an appropriate method to accelerate SQL access.

The SQL query feature is suitable for online data access scenarios that involve massive datasets. The latency of SQL access ranges from milliseconds to minutes. This includes point queries based on the primary keys of data tables (GetRow operations), term queries based on search indexes (TermQuery), and queries that use the statistical aggregation capabilities of search indexes, such as counting the number of rows that meet a condition or calculating the sum of a column.

SQL examples

The following are SQL examples for querying data from a mapping table of a time series table that uses the multi-value model:

• Query the location coordinates of a vehicle over a period of time

  select gps from `vehicles_timeseriesdata::multi_model` where _m_name = 'vehiclesSeriesData' and _data_source = 'vin_id_1488'
  and _time > 1655189452942704 and _time < 1655189872942704;

• Analyze the average speed of a vehicle over a period of time

  select avg(speed) from `vehicles_timeseriesdata::multi_model` where _m_name = 'vehiclesSeriesData' and _data_source = 'vin_id_1488'
  and _time > 1655189452942704 and _time < 1655189872942704;

• Analyze the average emission value for a specific vehicle model over a period of time

  select avg(emission) from `vehicles_timeseriesdata::multi_model` where tag_value_at(_tags,"model") = 'model_0'
  and _time > 1655189452942704 and _time < 1655189872942704;

• Analyze the total mileage for vehicles from each manufacturer

  select  tag_value_at(_tags,"mfrs") as mfrs, sum(mileage) from `vehicles_timeseriesdata::multi_model` group by mfrs;

Use SQL query

For more information about using SQL to query time series data, see Use SQL to query time series data.

You can use the SQL query feature in the following ways:

• Console

• SDK

• Using JDBC

  • Use SQL query through a direct JDBC connection

  • Use SQL query through Hibernate

  • Use SQL query through MyBatis

• Go driver

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?