Edit

Data Types (Azure Stream Analytics)

  • Article

In Azure Stream Analytics, each record has a related data type. A data type describes (and constrains) the set of values that a record of that type can hold or an expression of that type can produce.

Please note that it is records that have a type and not columns. Each record of a column can have a different type. If this will be transparent for most applications, it allows straightforward handling of schema drift scenarios and other unusual typing patterns.

Supported data types

Below is the list of data types supported.

Data type Description
bigint Integers in the range -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807).
float Floating point numbers in the range - 1.79E+308 to -2.23E-308, 0, and 2.23E-308 to 1.79E+308. Floating-point decimal values generally do not have an exact binary representation. Loss of precision can be experienced. This is not specific to Azure Stream Analytics but occurs in all floating-point number implementations.
nvarchar(max) Text values, comprised of Unicode characters. Note: A value other than max is not supported.
datetime Defines a date that is combined with a time of day with fractional seconds (7 digits, 100 nanoseconds precision) that is based on a 24-hour clock and relative to UTC (time zone offset 0).
bit An integer that can take a value of 1, 0, or NULL. This is supported in compatibility level 1.2 and above.
record Set of name/value pairs. Values must be of supported data type.
array Ordered collection of values. Values must be of supported data type.

You may join on (or compare) a bigint and a float data type. It will work correctly in all cases except for the case of the very large bigint values that cannot be represented.

Note

When reading bigint datatypes outside the range, Azure Stream Analytics supports, your Stream Analytics job would fail to deserialize the events with the error message: InputDeserializationError. You can pre-process the data and convert it to string. One option is using Azure functions to pre-process the data and convert such large integers to string.

Type conversions

These are the rules governing data type conversions:

  • Conversion without precision loss during input read and output write operations is implicit and is always successful
  • Precision loss and overflow inside output write operations is handled by configured error policy (set to either Drop or Retry)
  • Type conversion errors happening during output write operations are handled by the error policy
  • Type conversion errors happening during input read operations cause the job to drop the event

A loss of precision may happen in converting values to float. It is not specific to Azure Stream Analytics but to the float data type in general. As such it is not considered an error. In the case where every digit needs to be conserved, the data should be read as string.

Casting data

There are four functions in the streaming SQL language that are useful for observing and adjusting the data type of your data.

  • CAST : cast a single column to a given type - will fail the job in case of conversion error
  • TRY_CAST : cast a single column to a given type - errors are let through as NULL. See input validation for how to best use TRY_CAST
  • CREATE TABLE : define a single explicit schema to an input. Rows with conversion errors are removed from the stream
  • GetType : return the type of a column

For most use cases, the recommended option is to use TRY_CAST. This function protects downstream processing by ensuring the output type, while preventing the loss of data by replacing the value in error by NULL. The row is not dropped, and that original value can still be projected in another column.

For strong guarantees, the recommended option is to use CREATE TABLE. This approach allows to inform the job of the schema of a given input, with no risk of deviation. The trade-off being that only a single schema can be defined on a given input, and non-compliant rows will be dropped.

If possible, all casting operations should be done explicitly via these functions, rather than implicitly (silently) in other functions. This avoids type mismatches, unexpected behaviors, and insertion errors for strongly typed outputs like SQL databases. See input validation for how to protect the main query logic from such errors.

Conversion to bit

Values will be converted between float and bit with the following rules:

From To
(BIT) 1 (FLOAT) 1.0
(BIT) 0 (FLOAT) 0.0
(BIT) NULL (FLOAT) NULL
(FLOAT) 0.0 (BIT) 0
(FLOAT) any other value (BIT) 1
(FLOAT) NULL (BIT) NULL

Type mappings and serialization formats

Data type CSV in CSV out JSON in JSON out Avro in Avro out
bigint string converted to 64 bit signed integer 64 bit signed integer converted to string using job culture number: integer converted to 64 bit signed integer;

Boolean: in compatibility level 1.1 and below "false" is converted to 0, "true" is converted to 1		 number: integer long and int converted to 64 bit signed integer;

Boolean: in compatibility level 1.1 and below false is converted to 0, true is converted to 1		 long
float string converted to 64 bit signed float point number 64 bit signed float point number converted to string using job culture number: fraction converted to 64 bit signed float point number number: fraction double and float converted to 64 bit signed float point number double
nvarchar(max) string string string string string string
datetime string converted to datetime following ISO 8601 standard string using ISO 8601 standard string converted to datetime following ISO 8601 standard datetime converted to string using ISO 8601 standard string converted to datetime following ISO 8601 standard datetime converted to string using ISO 8601 standard
bit (compatibility level 1.2 and above) string "true", "false", or "null" is converted to integer value 1, 0, or null correspondingly converted to string "true" or "false" Boolean: "false" is converted to 0, "true" is converted to 1 Boolean: boolean value Boolean: false is converted to 0, true is converted to 1 boolean
record N/A Not supported, "Record" string is outputted JSON object JSON object Avro record type Avro record type
array N/A Not supported, "Array" string is outputted JSON object JSON object Avro record type Avro record type

Note

No data type conversion is needed for Parquet.

Type mapping when writing to structured data stores

Data type SQL Power BI Azure Cosmos DB PostgreSQL Azure Data Explorer
bigint bigint, int, smallint, tinyint, all string types (ntext, nvarchar, char, …) yes numeric: integer bigint dynamic, int, long
float float, real, decimal, numeric, all string types ( ntext, nvarchar, char, …) yes number: fraction double precision, numeric. Limited to 1.78E+308 dynamic, real, double
nvarchar(max) All string types (ntext, nvarchar, char, uniqueidentifier…) yes string character varying, text dynamic, string
datetime datetime, datetime2, datetimeoffset, all string types ( ntext, nvarchar, char, …) yes datetime converted to string using ISO 8601 standard timestamp, time. Timezone option supported but no time zone will be provided dynamic, string, datetime
bit (compatibility level 1.2 and above) bigint, int, smallint, tinyint, bit, all string types (ntext, nvarchar, char, …) yes boolean: 1 is converted to true, 0 converted to false bit dynamic, bool
record Not supported, "Record" string is outputted Not supported, "Record" string is outputted JSON object Not supported dynamic, bool, long, datetime, byte array, real, double, string
array Not supported, "Array" string is outputted Not supported, "Array" string is outputted JSON object Not supported dynamic, string