FLOAT data type
A single precision floating-point data type used in CREATE TABLE
and
ALTER TABLE
statements.
Syntax:
In the column definition of a CREATE TABLE
statement:
column_name FLOAT
Range: 1.40129846432481707e-45 .. 3.40282346638528860e+38, positive or negative
Precision: 6 to 9 significant digits, depending on usage. The number of significant digits does not depend on the position of the decimal point.
Representation: The values are stored in 4 bytes, using the IEEE 754 Single Precision Binary Floating Point format.
Conversions: Impala automatically converts FLOAT
to more precise
DOUBLE
values, but not the other way around. You can use CAST()
to convert
FLOAT
values to TINYINT
, SMALLINT
, INT
,
BIGINT
, STRING
, TIMESTAMP
, or BOOLEAN
.
You can use exponential notation in FLOAT
literals or when casting from
STRING
, for example 1.0e6
to represent one million.
Casting an integer or floating-point
value N
to TIMESTAMP
produces a
value that is N
seconds past the start of the epoch
date (January 1, 1970). By default, the result value represents a date
and time in the UTC time zone. If the setting
‑‑use_local_tz_for_unix_timestamp_conversions=true
is in effect, the resulting TIMESTAMP
represents a
date and time in the local time zone.
Usage notes:
Impala does not evaluate NaN (not a number) as equal to any other numeric values,
including other NaN values. For example, the following statement, which evaluates equality
between two NaN values, returns false
:
SELECT CAST('nan' AS FLOAT)=CAST('nan' AS FLOAT);
Examples:
CREATE TABLE t1 (x FLOAT);
SELECT CAST(1000.5 AS FLOAT);
Partitioning: Because fractional values of this type are not always represented precisely, when this
type is used for a partition key column, the underlying HDFS directories might not be named exactly as you
expect. Prefer to partition on a DECIMAL
column instead.
HBase considerations: This data type is fully compatible with HBase tables.
Parquet considerations: This type is fully compatible with Parquet tables.
Text table considerations: Values of this type are potentially larger in text tables than in tables using Parquet or other binary formats.
Internal details: Represented in memory as a 4-byte value.
Column statistics considerations: Because this type has a fixed size, the maximum and average size
fields are always filled in for column statistics, even before you run the COMPUTE STATS
statement.
Restrictions:
Due to the way arithmetic on FLOAT
and DOUBLE
columns uses
high-performance hardware instructions, and distributed queries can perform these operations in different
order for each query, results can vary slightly for aggregate function calls such as SUM()
and AVG()
for FLOAT
and DOUBLE
columns, particularly on
large data sets where millions or billions of values are summed or averaged. For perfect consistency and
repeatability, use the DECIMAL
data type for such operations instead of
FLOAT
or DOUBLE
.
The inability to exactly represent certain floating-point values means that
DECIMAL
is sometimes a better choice than DOUBLE
or FLOAT
when precision is critical, particularly when
transferring data from other database systems that use different representations
or file formats.
Kudu considerations:
Currently, the data types BOOLEAN
,
FLOAT
, and DOUBLE
cannot be used for primary key columns
in Kudu tables.