Table of contents

Data Types (Azure Stream Analytics)

Mary McCready|Last Updated: 10/5/2018
|
1 Contributor

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

Supported data types

Below is the list of data types supported.

Data typeDescription
bigintIntegers in the range -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807).
floatFloating point numbers in the range - 1.79E+308 to -2.23E-308, 0, and 2.23E-308 to 1.79E+308.
nvarchar(max)Text values, comprised of Unicode characters. Note: A value other than max is not supported.
datetimeDefines a date that is combined with a time of day with fractional seconds that is based on a 24-hour clock and relative to UTC (time zone offset 0).
recordSet of name/value pairs. Values must be of supported data type.
arrayOrdered 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.

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

Casting data

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

Type mappings and serialization formats:

Data typeCSV inCSV outJSON inJSON outAvro inAvro out
bigintstring converted to 64 bit signed integer64 bit signed integer converted to string using job culturenumber: integer converted to 64 bit signed integer;

Boolean: false is converted to 0, true is converted to 1
numeric: integerlong and int converted to 64 bit signed integer;

Boolean: false is converted to 0, true is converted to 1
long
floatstring converted to 64 bit signed float point number64 bit signed float point number converted to string using job culturenumber: fraction converted to 64 bit signed float point numbernumber: fractiondouble and float converted to 64 bit signed float point numberdouble
nvarchar(max)stringstringstringstringstringstring
datetimestring converted to datetime following ISO 8601 standardstring using ISO 8601 standardstring converted to datetime following ISO 8601 standarddatetime converted to string using ISO 8601 standardstring converted to datetime following ISO 8601 standarddatetime converted to string using ISO 8601 standard
recordN/ANot supported, “Record” string is outputtedJSON objectJSON objectAvro record typeAvro record type
arrayN/ANot supported, “Array” string is outputtedJSON objectJSON objectAvro record typeAvro record type

Type mapping when writing to structured data stores:

Data typeSQLPower BIDocument DB
bigintbigint, int, smallint, tinyint, all string types (ntext, nvarchar, char, …)yesnumeric: integer
floatfloat, real, decimal, numeric, all string types ( ntext, nvarchar, char, …)yesnumber: fraction
nvarchar(max)All string types (ntext, nvarchar, char, uniqueidentifier…)yesstring
datetimedatetime, datetime2, datetimeoffset, all string types ( ntext, nvarchar, char, …)yesdatetime converted to string using ISO 8601 standard
recordNot supported, “Record” string is outputtedNot supported, "Record" string is outputtedJSON object
arrayNot supported, “Array” string is outputtedNot supported, “Array” string is outputtedJSON object
© 2018 Microsoft