Dremio Community

Data Types

Supported Data Types

Data Type UI Symbol Maximum Size
BOOLEAN NA
VARBINARY 32000 bytes
DATE NA
FLOAT NA
DECIMAL* NA
DOUBLE NA
INTERVAL (day to seconds) NA
INTERVAL (years to months) NA
INT NA
BIGINT NA
TIME NA
TIMESTAMP NA
VARCHAR** 32000 bytes
STRUCT NA
LIST NA

* Partially supported
** _VARCHAR type only supports UTF-8 encoded values_

Decimal Support

As of Dremio 4.0, decimal to decimal mappings are supported for relational database sources. That is, when creating a new relational database data source, the default is to map decimal to decimal. If you do not want the default decimal-to-decimal mapping, you can enable decimal-to-double mappings through the data source Advanced Options tab.

Upgrade Impact

When you upgrade a relational data source to Dremio 4.0 with decimal usage, the existing (pre-4.0) behavior of mapping decimals to doubles is retained.

For more information, see Installing and Upgrade.

Dremio fully supports Decimal data type for Parquet, Hive(Parquet/ORC) sources. Text/Json sources can cast strings/integers to decimals for decimal precision and scale. Other sources treat decimals as doubles same as prior to 3.3.5.

Return Type Inference

The return type inference provides the return type for functions and operators that natively handle decimal types. Functions and operators that downcast instead return the data type in the existing function (double/float/int).

Dremio supports the following return type inference rules:

Arithmetic Operators
Function/Operator Native Decimal Support
+ (Addition)
- (Subtraction)		 s = max(s1, s2)
p = max(s1, s2) + max(p1 - s1, p2 - s2) + 1

if (p > 38) { minS = min(s, 6) s = s - (p - 38) s = max(minS, s) P = 38 }
* (Multiplication) s = s1 + s2
p = p1 + p2 + 1

if (p > 38) { minS = min(s, 6) s = s - (p - 38) s = max(minS, s) p = 38 }
/ (Division) s = max(6, s1 + p2 + 1)
p = p1 -s1 + s2 + s

if (p > 38) { minS = min(s, 6) s = s - (p - 38) s = max(minS, s) p = 38 }
% (Modulus) s = max (s1, s2)
p = min (p1 - s1, p2 - s2) + s

if (p > 38) { minS = min(s, 6) s = s - (p - 38) s = max(minS, s) p = 38 }
Mixed Types
Function/Operator Native Decimal Support
Mixed Types s = max(s1, s2)
p = max(s1, s2) + max(p1 - s1, p2 - s2)

if (p > 38) { Error out and reject. }
SQL Clauses
Function/Operator Native Decimal Support
ORDER BY Same as input precision and scale
GROUP BY Same as input precision and scale
Math Functions
Function/Operator Native Decimal Support
ABS() Same as input precision and scale
CEILING() Same as input precision and scale
FLOOR() Same as input precision and scale
MOD() Same as Modulus above
ROUND() Same as input precision and scale
TRUNC() Same as input precision and scale
Aggregate Functions
Function/Operator Native Decimal Support
AVG() Decimal(38,s) / Decimal(18,0)
MAX() Same as input precision and scale
MIN() Same as input precision and scale
SUM() Decimal(38, input Scale)
Analytic Functions
Function/Operator Native Decimal Support
LAG() Same as input precision and scale
LEAD() Same as input precision and scale
Type Conversion Functions
Function/Operator Native Decimal Support
CAST() Provided in cast type
CONVERT_FROM()) Provided in cast type
CONVERT_TO() Provided in cast type
Set Operators
Function/Operator Native Decimal Support
UNION() TBD
UNION ALL() TBD
INTERSET() TBD
MINUS() TBD

Mixed Decimal Types

The following decimal union types specifies when mixed decimal types are supported:

  • Parquet – Mixed decimal types in a data set are not supported. For example, the same column cannot have differing precision/scale across files in the same dataset.
  • ORC – Mixed decimal types in a data set are supported.

Decimal Limitations

The following are decimal limitations:

  • Decimal numeric literals in SQL queries cannot be larger than maximum possible long value.

  • Queries that do arithmetic on a column and literal have issues.

    For example, select cast(12345 as double) * CAST(A as double) fails. The work around is to use a string value such as select cast('12345' as double) * CAST(A as double).

  • Queries casting numeric literals to decimal should use specific precision. Precision of literal cannot be lowered.

    For example, cast(123.23 as decimal(2,0)) returns the same number as output since given number cannot be represented using precision 2.

  • In general, decimal overflow is not handled; output when decimal arithmetic overflows is undefined. This is the same as other data types in Dremio.

Timestamp Queries

When Dremio retrieves the timestamp value entry in a table, the returned timestamp entry is displayed as it was written to the file system. That is, Dremio does not apply any conversions to the timestamp entry.

For example:

  • If the timestamp value entry is written to the file system in UTC, then Dremio displays the entry in UTC.
  • With Hive, timestamp value entries are converted and stored as UTC in the file system.
    Subsequently, when Dremio returns the timestamp value entry, it is displayed in UTC.
  • With some other application, if the timestamp value entry is written and stored as PST (local time) in the file system, then when Dremio returns the timestamp value entry, it is displayed in PST.

Source Data Type Mappings

The following topics provide Dremio data type mappings to source database types: