Dremio supports decimal to decimal mappings for external sources. That is, when connecting a new external source, the default is to map decimal to decimal.
|Relational databases: See Data Type Mappings for External Sources for the current list of supported databases.||Decimal-to-decimal|
|Parquet (AWS Glue), Hive (Parquet/ORC)||Decimal data types|
|Text (CSV), JSON files||Cast strings and integers to decimals with decimal precision and scale.|
Mixed Decimal Types
Verify whether the following statement is true. Based on Cloud docs, but need to verify this is the case for Hive datasets. Current posted sw doc states ORC does not support mixed decimal types: https://docs.dremio.com/software/sql-reference/data-types/#mixed-decimal-types
Dremio supports mixed decimal types for AWS Glue and Hive datasets that are either in Parquet files or Optimized Row Columnar (ORC) files.
A DECIMAL type has precision (
p) and scale (
DECIMAL(p,s). For example,
DECIMAL(9,2). Precision is the total number of digits. Scale is the number of digits to the right of the decimal point.
When you perform arithmetic on DECIMAL types that have different precision and/or scale, the return value will have sufficient precision and scale to hold the result of the operation.
The following are decimal limitations:
Decimal numeric literals in SQL queries cannot be larger than the maximum possible long value.
Queries that do arithmetic on a column and literal may cause errors.
SELECT CAST(12345 as DOUBLE) * CAST(A as DOUBLE)fails. The workaround 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.
CAST(123.23 as DECIMAL(2,0))returns the same number as the output since the given number cannot be represented using a precision of
When there is an overflow with the decimal arithmetic output, the returned result will overflow.