问题描述
我正在使用以逗号作为小数点分隔符而不是句号(3,99而不是3.99)的原始数据.有没有一种方法可以直接在redshift copy命令中将其转换，而不必先上传然后再进行更改?
I'm working with raw data that has a comma as the decimal separator rather than fullstop (3,99 instead of 3.99). Is there a way to convert this directly in redshift copy command rather than having to upload then change afterwards?
推荐答案
要考虑两个问题:
- 字段分隔符
- 替换字符
Amazon Redshift中的默认分隔符 除非使用了CSV选项，否则COPY 命令是竖线字符(|)，在这种情况下，默认的分隔符是逗号(，).
The default delimiter in the Amazon Redshift COPY command is a pipe character ( | ), unless the CSV option is used, in which case the default delimiter is a comma ( , ).
因此，如果您的文件由非逗号字符(例如pip"|"符号)分隔，则数字中的逗号将不会跨字段分割.
Thus, if your file is delimited by a non-comma character (eg a pip "|" symbol), then the comma in a number will not split across fields.
关于将逗号转换为小数，这不可能.您将需要将字段作为字符串加载，然后运行UPDATE命令以将字符串复制到数字字段(也需要一些字符替换).
As to converting the comma into a decimal, this is not possible. You will need to load the field as a string, and then run an UPDATE command to copy the string into a numeric field (with a bit of character replacement too).
或者，尝试在文件加载到Redshift之前(例如通过sed)对其进行预处理，因此在加载到Redshift之前它是干净的.
Alternatively, try to pre-process the file before loading into Redshift (eg through sed), so it is clean before loading into Redshift.
这篇关于如何在Redshift Copy命令中将小数点分隔符从逗号更改为句号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！