使用NaN将CSV数据加载到AWS Redshift中 [英] Loading CSV data with NaN into AWS Redshift

问题描述

我正在尝试将CS​​V文件从AWS S3加载到AWS Redshift中. CSV文件包含以下行:

I am trying to load a CSV file from AWS S3 into AWS Redshift. The CSV file contains a line like:

15，NaN，0

15,NaN,0

该表是通过以下方式创建的:

The table was created via:

CREATE TABLE foo(a INT，b DOUBLE PRECISION，c INT);

CREATE TABLE foo (a INT, b DOUBLE PRECISION, c INT);

我正在尝试使用COPY命令加载表:

And I am trying to load the table using the COPY command:

从"s3://"凭据... CSV中复制foo(a，b，c)；

COPY foo (a, b, c) FROM "s3://" CREDENTIALS ... CSV;

我收到一个抱怨的错误消息

And I get an error complaining:

无效的数字值"N"

Invalid digit value 'N'

尝试通过INSERT语句手动加载同一行就可以了:

Trying to load that same line via an INSERT statement manually works just fine:

将INSERT INTO foo(a，b，c)值(15，'NaN'，0);

INSERT INTO foo (a, b, c) VALUES (15, 'NaN', 0);

任何帮助将不胜感激！

推荐答案

如果您要这样做，则需要告诉Redshift将NaN加载为NULL.

You need to tell Redshift to load NaN as a NULL if that is what you want to do.

例如:

COPY foo from 's3://xxx' credentials 'xxxx' DELIMETER AS ',' NULL 'NaN';

这应该成功执行，并将NULL而不是NaN插入表中.

This should execute successfully and insert a NULL into the table instead of NaN.

这篇关于使用NaN将CSV数据加载到AWS Redshift中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！