SQL expressions in AWS IoT Analytics - AWS IoT Analytics

SQL expressions in AWS IoT Analytics

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.

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 doesn't 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:

AWS IoT Analytics and Amazon Athena don't support the following:

  • 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][,...]>

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

AWS IoT Analytics and Amazon Athena table, database and column names must contain only lower-case letters, digits and underscore. Use backticks to enclose table or column names that begin with an underscore, such as the following example.

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

Enclose table names that include numbers in quotation marks, such as the following example.

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