AWS IoT Analytics
AWS IoT Analytics User Guide

SQL Support

Data sets are generated using SQL expressions on data in a data store. AWS IoT Analytics uses the same SQL queries, functions and operators as Amazon Athena

This means that AWS IoT Analytics supports a subset of ANSI standard SQL syntax as follows:

SELECT [ ALL | DISTINCT ] select_expression [, ...] [ FROM from_item [, ...] ] [ WHERE condition ] [ GROUP BY [ ALL | DISTINCT ] grouping_element [, ...] ] [ HAVING condition ] [ UNION [ ALL | DISTINCT ] union_query ] [ ORDER BY expression [ ASC | DESC ] [ NULLS FIRST | NULLS LAST] [, ...] ] [ LIMIT [ count | ALL ] ]

However, AWS IoT Analytics does not support JOIN or WITH clauses.

Amazon Athena and AWS IoT Analytics SQL functionality are based on Presto 0.172, so they support the following functions:

However, AWS IoT Analytics and Amazon Athena do not support:

  • User-defined functions (UDFs or UDAFs).

  • Stored procedures.

  • Some data types.

  • CREATE TABLE AS SELECT statements.

  • INSERT INTO statements.

  • Prepared statements. You cannot run EXECUTE with USING.

  • CREATE TABLE LIKE.

  • DESCRIBE INPUT and DESCRIBE OUTPUT.

  • EXPLAIN statements.

  • Federated connectors.

These are the supported data types:

  • primitive_type

    • TINYINT

    • SMALLINT

    • INT

    • BIGINT

    • BOOLEAN

    • DOUBLE

    • FLOAT

    • STRING

    • TIMESTAMP

    • DECIMAL[(precision, scale)]

    • DATE

    • CHAR (fixed-length character data with a specified length)

    • VARCHAR (variable-length character data with a specified length)

  • array_type

    • ARRAY<data_type>

  • map_type

    • MAP<primitive_type, data_type>

  • struct_type

    • STRUCT<col_name:data_type[COMMENT col_comment][,...]>

IoT Analytics entity names (channel, data set, data store, and pipeline names) must begin with a lower-case letter and can contain only lower-case letters, digits and underscores.

Athena table, database and column names must contain only lower-case letters, digits and underscore. Use backtics to enclose table or column names that begin with an underscore. For example:

CREATE_TABLE `_myunderscoretable`( `_id` string, `_index` string, ...

Enclose table names that include numbers in quotation marks. For example:

CREATE_TABLE "table123"( `_id` string, `_index` string, ...