IoT Platform:Step 2: Write an SQL analysis statement

Background information

The following types of data storage tables can be queried by SQL analysis statements.

• Platform system tables: For more information, see View platform system tables.

• Product storage tables: To use a product storage table as a query object, you must back up its data source. Otherwise, only the last 30 days of data is available for query.

  For more information, see View product storage tables and Back up device data sources.

• Custom storage tables: To use a custom storage table as a query object, you must configure it as a destination for data parsing or as the output storage table of another SQL analysis task. For more information, see Create and manage custom storage tables and Data parsing.

Usage notes

• Output field names must start with a letter or a dollar sign ($) and can contain only letters, digits, dollar signs ($), and underscores (_). The maximum length is 60 characters.

  If an original field name does not meet these requirements, define an alias for it.

• System fields and system functions are not supported as output fields.

  Type	Unsupported fields
  System field names	id, gmt_create, gmt_modified, creator, modifier, is_deleted, append_column_name, append_column_value, append_column_concat, scope_id, and __closure_context__.
  System functions	COUNT, DATE_FORMAT, AVG, MAX, MIN, and SUM.

• To query a system-reserved field, use an alias for the output field.

  For example, to query the value of the gmt_create field from the device table ${system.device}, specify an alias such as gmt_create_alias for the output field:

  select gmt_create as gmt_create_alias from ${system.device}

• If an output field's data type is incompatible with the source field's data type, perform a type conversion.

  For example, the gmt_create field in the device table ${system.device} is a DATE type. If the destination field requires a different type, cast it to a compatible type such as BIGINT:

  select CAST(EXTRACT(EPOCH FROM gmt_create) * 1000 AS BIGINT) as gmt_create_alias from  ${system.device}

• To get the current time for a scheduled task, use ${bizDate} instead of current_timestamp().

  • ${bizDate}: The scheduled time of the SQL task. This variable is replaced with the actual execution time at runtime. It is a 13-digit timestamp in milliseconds, such as 1699286400000. The value falls within the time range specified by the effective date of scheduling policy for the SQL task.

  • current_timestamp(): Returns the current system time as a TIMESTAMP value.

  Reasons to use ${bizDate}:

  • Current scheduled tasks: Both ${bizDate} and current_timestamp() can be used.

  • Backfill tasks: You must use ${bizDate} to process data based on historical trigger times. Using current_timestamp() would process data against the current time, preventing a true backfill.

• For a list of functions supported by SQL analysis tasks, see Function overview.

Procedure

1. On the analysis and insight page, find your SQL analysis task. In the Actions column, click Development Workbench to open the SQL data service workbench page.

   For more information about the SQL data service workbench, see Step 1: Create an SQL analysis task.

2. In the left-side navigation pane of the SQL data service workbench, select the data object that you want to query.

   Important

   SQL analysis tasks support using data storage tables as query objects. For more information, see Offline data storage.

   The following operations are available:

   • Double-click a table name to quickly generate a query statement.

   • Hover over the name of the table that you want to query and perform one of the following actions:

     • Click Copy and then paste the table name into the SQL editor.

     • Click Details to view the data structure of the table or preview its 20 most recent data entries.

3. In the SQL editor, write your SQL analysis statement.

   Important

   If you use an SQL analysis task to store data in a custom time-series table, each data entry must include the timestamp field ts. Otherwise, the data write operation fails. If the write operation fails, you can view the Run Log of the SQL analysis task on the Task Operations page. For more information, see Operate on SQL analysis tasks.

   You can click intelligent assistant at the top of the workbench to quickly generate an SQL analysis statement using SQL template.

   1. On the SQL template tab, select a function based on your data analysis needs.

      For information about how to use functions, see Supported SQL functions.

   2. After the system generates the SQL analysis statement, click Use Now or Copy to add the statement to the SQL editor.

   3. In the SQL editor, modify parameters such as the query object to complete the SQL analysis statement.

   The Expression Templates panel provides four categories: Time Expressions, String Expressions, Conditional Expressions, and Data Preprocessing Expressions. You can expand a category to view specific templates and SQL examples, such as SELECT FROM_UNIXTIME(123456789).

4. Optional: Above the editor, click the Validate SQL icon to check the syntax of your SQL analysis statement.

5. When you are satisfied with the statement, click the Run icon above the editor.

   • On success, view the run log tab for execution details and the Result tab for the output. Results can be visualized in various chart formats.

     You can also click Export Data to download the current results as a CSV file.

   • If the execution fails, check the error message on the run log tab, resolve the issue, and then run the statement again.