主页 / dba / 问题 / 250485
Accepted
pnairn
Asked: 2019-10-08 16:24:25 +0800 CST

为什么 Redshift UNLOAD 提高了输出中 float4 字段的精度？

  • 772

我在 AWS Redshift 中创建了一个表，例如

CREATE TABLE exampleTableName (
  id       int identity(1,1),
  accId    varchar(16) encode zstd,
  amount   float4,

  primary key(id)
)
distkey(accId)
interleaved sortkey(accId);

表中的示例记录的amount字段值为120.12。

但是，当我尝试通过执行 UNLOAD 导出数据时，生成的文件（本质上是 CSV）对字段值具有额外的精度。

卸载命令：

UNLOAD ('SELECT * from exampleTableName')
TO 's3://bucket/prefixFile_'
IAM_ROLE 'XXX'
HEADER
ADDQUOTES
PARALLEL OFF
MAXFILESIZE AS 5gb
DELIMITER AS ',' 
GZIP;

结果输出中的字段值：（120.120003即它增加了 4 个小数位，原始数据集中没有）。

为什么会发生这种情况，如何防止附加精度（即小数位）作为 UNLOAD 命令的一部分输出？

redshift

1 个回答

  1. Best Answer

    来自AWS 论坛的回答：

    this happens when you use FLOAT for the decimal data because it cannot store values
of arbitrary precision. https://en.wikipedia.org/wiki/IEEE_754

I would generally always recommend using the DECIMAL datatype unless you have an
existing application that has an unchangeable requirement for FLOAT, e.g., 
calculation expects FLOAT and output cannot change.

Additionally, by using DECIMAL you are able to use our new AZ64 compression encoding
which will reduce the amount of storage needed and improve your query performance.
https://aws.amazon.com/about-aws/whats-new/2019/10/amazon-redshift-introduces-az64-a-new-compression-encoding-for-optimized-storage-and-high-query-performance/
    • 0

相关问题