CODEWITHSUNDEEP
floating-pointsnowflake-cloud-data-platformprecision
zjam
zjam

Reputation: 13

Snowflake Float value with 16 precision loaded as exponential

Data file has value: 2014050600001253

Float column in Snowflake is loaded with: 2.01405060000125e+15

Is there a way to prevent the exponential format and keep the value as it is in the file with float data type?

File format used:

CREATE or Replace FILE FORMAT test_fmt
   TYPE = 'CSV'
   COMPRESSION = 'GZIP'
   FILE_EXTENSION= 'txt'
   FIELD_DELIMITER = '|'
   NULL_IF = ('NULL','null','')
   FIELD_OPTIONALLY_ENCLOSED_BY = '"'
   DATE_FORMAT = 'MM/DD/YYYY'
   TIMESTAMP_FORMAT = 'MM/DD/YYYY HH12:MI:SS AM'
   EMPTY_FIELD_AS_NULL = TRUE;

Upvotes: 1

Views: 1530

Answers (2)

chux
chux

Reputation: 153338

2014050600001253 (a 51-bit value) is exactly representable as a 64-bit floating point type.

"Float column in Snowflake is loaded with: 2.01405060000125e+15" is mistaken, the value is 2.014050600001253e+15.

Is there a way to prevent the exponential format and keep the value as it is in the file with float data type?

The value in the file is what is is regardless of how your choose to print it. Print with at least 17 significant decimal digits to get a better idea of its true value.

Even better, since floating point values are encoded with a binary significant, print with a binary/hexadecimal output to avoid decimal artifacts.

Upvotes: 1

phuclv
phuclv

Reputation: 41754

Is there a way to prevent the exponential format and keep the value as it is in the file with float data type?

Yes there are ways to force printing the values without exponential format, but you can't keep the value as it is because Snowflake uses IEEE-754 double precision, and such large values when converted to double will be rounded to the closest representable value in double precision. Most of the time the result won't be equal to the original value anymore. For example 20140506000001253 will be printed as 20140506000001252, and 9014050600001253 is also rounded to 9014050600001252

You must use string if you want to store a floating-point value that huge, or if the values are integers smaller than 1038 then use an integer type

Precision is approximately 15 digits. For example, for integers, the range is from -9007199254740991 to +9007199254740991 (-253 + 1 to +253 - 1). Floating-point values can range from approximately 10-308 to 10+308. (More extreme values between approximately 10-324 and 10-308 can be represented with less precision.) For more details, see the Wikipedia article on double-precision numbers.

Numeric Data Types - FLOAT , FLOAT4 , FLOAT8

You can

Upvotes: 4

Related Questions