I am trying to load a CSV file from AWS S3 into AWS Redshift. The CSV file contains a line like:
我正在尝试将AWS S3中的CSV文件加载到AWS Redshift中。 CSV文件包含如下行：
15,NaN,0
15，NaN时，0
The table was created via:
该表通过以下方式创建：
CREATE TABLE foo (a INT, b DOUBLE PRECISION, c INT);
CREATE TABLE foo（INT，b DOUBLE PRECISION，c INT）;
And I am trying to load the table using the COPY command:
我正在尝试使用COPY命令加载表：
COPY foo (a, b, c) FROM "s3://" CREDENTIALS ... CSV;
COPY foo（a，b，c）FROM“s3：//”CREDENTIALS ... CSV;
And I get an error complaining:
我抱怨错误：
Invalid digit value 'N'
无效的数字值'N'
Trying to load that same line via an INSERT statement manually works just fine:
试图通过手动INSERT语句加载相同的行正常工作：
INSERT INTO foo (a, b, c) VALUES (15, 'NaN', 0);
INSERT INTO foo（a，b，c）VALUES（15，'NaN'，0）;
Any help would be much appreciated!
任何帮助将非常感激！
1 个解决方案
#1
5
You need to tell Redshift to load NaN as a NULL if that is what you want to do.
如果这是您想要做的，您需要告诉Redshift将NaN加载为NULL。
For example:
例如：
COPY foo from 's3://xxx' credentials 'xxxx' DELIMETER AS ',' NULL 'NaN';
This should execute successfully and insert a NULL into the table instead of NaN.
这应该成功执行并在表中插入NULL而不是NaN。
#1
5
You need to tell Redshift to load NaN as a NULL if that is what you want to do.
如果这是您想要做的，您需要告诉Redshift将NaN加载为NULL。
For example:
例如：
COPY foo from 's3://xxx' credentials 'xxxx' DELIMETER AS ',' NULL 'NaN';
This should execute successfully and insert a NULL into the table instead of NaN.
这应该成功执行并在表中插入NULL而不是NaN。