数据转换参数 - Amazon Redshift

数据转换参数

在加载表时,COPY 会尝试将源数据中的字符串隐式转换为目标列的数据类型。如果您需要指定不同于默认行为的转换,或者默认转换会产生错误,则可以通过指定以下参数来管理数据转换。有关这些参数语法的更多信息,请参阅 COPY 语法

数据转换参数
ACCEPTANYDATE

允许加载包括无效格式(如 00/00/00 00:00:00)在内的任何日期格式,而不生成错误。此参数仅适用于 TIMESTAMP 和 DATE 列。始终将 ACCEPTANYDATE 与 DATEFORMAT 参数结合使用。如果数据的日期格式与 DATEFORMAT 规范不匹配,则 Amazon Redshift 会将 NULL 值插入该字段中。

ACCEPTINVCHARS [AS] ['replacement_char']

允许将数据加载到 VARCHAR 列中,即使数据包含无效的 UTF-8 字符。如果指定 ACCEPTINVCHARS,则 COPY 会将每个无效的 UTF-8 字符替换为长度相等且包含由 replacement_char 指定的字符的字符串。例如,如果替换字符为“^”,则将使用“^^^”替换无效的三字节字符。

替换字符可以是除 NULL 之外的任何 ASCII 字符。默认值为一个问号 (?)。有关无效的 UTF-8 字符的信息,请参阅多字节字符加载错误

COPY 将返回包含无效 UTF-8 字符的行的数量,并将为每个受影响的行在 STL_REPLACEMENTS 系统表中添加一个条目,每个节点切片最多有 100 行。还将替换其他无效的 UTF-8 字符,但不会记录这些替换事件。

如果未指定 ACCEPTINVCHARS,则 COPY 在遇到无效 UTF-8 字符时将返回错误。

ACCEPTINVCHARS 仅对 VARCHAR 列有效。

BLANKSASNULL

将仅包含空格字符的空白字段作为 NULL 加载。此选项仅适用于 CHAR 和 VARCHAR 列。其他数据类型(如 INT)的空白字段始终作为 NULL 加载。例如,包含三个连续的空格字符(并且无其他字符)的字符串将作为 NULL 加载。如果不使用此选项,则默认行为是按原样加载空白字符。

DATEFORMAT [AS] {'dateformat_string' | 'auto' }

如果未指定 DATEFORMAT,则默认格式为 'YYYY-MM-DD'。例如,一种有效的替代格式为 'MM-DD-YYYY'

如果 COPY 命令未识别日期或时间值的格式,或者日期或时间值使用不同的格式,请将 'auto' 参数与 DATEFORMAT 或 TIMEFORMAT 参数结合使用。在使用 DATEFORMAT 和 TIMEFORMAT 字符串时,'auto' 参数将识别一些不受支持的格式。'auto' 的关键字区分大小写。有关更多信息,请参阅 在 DATEFORMAT 和 TIMEFORMAT 中使用自动识别

日期格式可包含时间信息(小时、分钟、秒),但此信息将被忽略。AS 关键字是可选的。有关更多信息,请参阅 DATEFORMAT 和 TIMEFORMAT 字符串

EMPTYASNULL

指示 Amazon Redshift 应将空 CHAR 和 VARCHAR 字段作为 NULL 加载。其他数据类型(如 INT)的空字段始终作为 NULL 加载。当数据包含两个连续的分隔符且分隔符之间没有字符时,将出现空字段。EMPTYASNULL 和 NULL AS ''(空字符串)将产生相同的行为。

ENCODING [AS] file_encoding

指定加载数据的编码类型。COPY 命令在加载过程中将数据从指定的编码转换为 UTF-8。

file_encoding 的有效值如下所示:

  • UTF8

  • UTF16

  • UTF16LE

  • UTF16BE

  • ISO88591

默认为 UTF8

源文件名必须使用 UTF-8 编码。

下列文件必须使用 UTF-8 编码,即使为加载数据指定了不同的编码:

  • 清单文件

  • JSONPaths 文件

随下列参数提供的参数字符串必须使用 UTF-8:

  • FIXEDWIDTH 'fixedwidth_spec'

  • ACCEPTINVCHARS 'replacement_char'

  • DATEFORMAT 'dateformat_string'

  • TIMEFORMAT 'timeformat_string'

  • NULL AS 'null_string'

固定宽度的数据文件必须使用 UTF-8 编码。字段宽度基于字符数,而不是字节数。

所有加载数据必须使用指定编码。如果 COPY 遇到不同的编码,将跳过文件并返回错误。

如果您指定 UTF16,则您的数据必须具有字节顺序标记 (BOM)。如果您知道您的 UTF-16 数据是否为 little-endian (LE) 或 big-endian (BE),则不管是否存在 BOM,均可使用 UTF16LEUTF16BE

要使用 ISO-8859-1 编码,请指定 ISO88591。有关更多信息,请参阅 Wikipedia 中的 ISO/IEC 8859-1

ESCAPE

指定此参数后,输入数据中的反斜杠字符 (\) 将被视为转义字符。紧跟在反斜杠字符后面的字符将作为当前列值的一部分加载到表中,即使它是通常用作特殊用途的字符。例如,您可使用此参数转义分隔符字符、引号、嵌入的换行符或转义字符本身,前提是这些字符中的任何字符是列值的合法部分。

如果您指定 ESCAPE 参数与 REMOVEQUOTES 参数的组合,则可转义并保留可能会被删除的引号('")。默认 null 字符串 \N 按原样工作,但也可在输入数据中转义为 \\N。只要您未使用 NULL AS 参数指定替换 null 字符串,\N\\N 就会产生相同的结果。

注意

控制字符 0x00 (NUL) 无法转义,应从输入数据中删除或进行转换。此字符将被视为记录结束 (EOR) 标记,并导致记录的剩余部分被截断。

您无法对 FIXEDWIDTH 加载使用 ESCAPE 参数,并且无法指定转义字符本身;转义字符始终为反斜杠字符。此外,您必须确保输入数据在合适的位置包含转义字符。

下面是在指定 ESCAPE 参数的情况下的输入数据和产生的加载数据的一些示例。第 4 行的结果假设还指定了 REMOVEQUOTES 参数。输入数据包含两个用竖线分隔的字段:

1|The quick brown fox\[newline] jumped over the lazy dog. 2| A\\B\\C 3| A \| B \| C 4| 'A Midsummer Night\'s Dream'

加载到第 2 列的数据看上去与下面类似:

The quick brown fox jumped over the lazy dog. A\B\C A|B|C A Midsummer Night's Dream
注意

对加载的输入数据应用转义字符是用户的责任。此要求的一个例外情况是在您重新加载之前使用 ESCAPE 参数卸载的数据时。在此情况下,数据将已经包含必需的转义字符。

ESCAPE 参数不会解释 octal、hex、Unicode 或其他转义序列表示法。例如,如果您的源数据包含 octal 换行符值 (\012) 并且您尝试使用 ESCAPE 参数加载此数据,则 Amazon Redshift 会将值 012 加载到表中并且不会将此值解释为要转义的换行符。

为了转义源自 Microsoft Windows 平台的数据中的换行符,您可能需要使用两个转义字符:一个用于回车,一个用于换行。您也可以在加载文件(例如,通过使用 dos2unix 实用工具)之前删除回车符。

EXPLICIT_IDS

如果要将表中自动生成的值替换为源数据文件中的显式值,请对具有 IDENTITY 列的表使用 EXPLICIT_IDS。如果命令包含一个列列表,则该列表必须包含 IDENTITY 列才能使用此参数。EXPLICIT_IDS 值的数据格式必须与 CREATE TABLE 定义指定的 IDENTITY 格式匹配。

在对表运行带 EXPLICIT_IDS 选项的 COPY 命令时,Amazon Redshift 不会检查表中 IDENTITY 列的唯一性。

如果某个列使用 GENERATED BY DEFAULT AS IDENTITY 进行定义,则可以复制该列。使用您提供的值生成或更新值。EXPLICIT_IDS 选项不是必需项。COPY 不会更新身份高级别水印。

有关使用 EXPLICIT_IDS 的 COPY 命令的示例,请参阅加载具有显式的 IDENTITY 列值的 VENUE

FILLRECORD

当一些记录的末尾缺少相邻列时,允许加载数据文件。将缺少的列加载为 NULL。对于文本和 CSV 格式,如果缺少的是 VARCHAR 列,则会加载零长度字符串而非 NULL。要从文本和 CSV 将 NULL 加载到 VARCHAR 列,请指定 EMPTYASNULL 关键字。仅当列定义允许 NULL 时,NULL 替换才会工作。

例如,如果表定义包含 4 个可以为 null 的 CHAR 列,并且记录包含值 apple, orange, banana, mango,则 COPY 命令可能加载并填充仅包含 apple, orange 值的记录。缺少的 CHAR 值将作为 NULL 值加载。

IGNOREBLANKLINES

忽略数据文件中仅包含换行符的空行并且不尝试加载它们。

IGNOREHEADER [ AS ] number_rows

将指定的 number_rows 视为文件标题并且不加载它们。使用 IGNOREHEADER 跳过并行加载的所有文件中的文件标题。

NULL AS 'null_string'

加载将 null_string 匹配为 NULL 的字段,其中 null_string 可以是任何字符串。如果您的数据包含 null 终止符(也称为 NUL (UTF-8 0000) 或二进制零 (0x000)),则 COPY 会将其视为任何其他字符。例如,包含 '1' || NUL || '2' 的记录被复制为长度为 3 个字节的字符串。如果字段仅包含 NUL,您可使用 NULL AS 通过指定 '\0''\000' 来将 null 终止符替换为 NULL,例如,NULL AS '\0'NULL AS '\000'。如果指定包含以 NUL 和 NULL AS 结尾的字符串的字段,则将在末尾处插入 NUL。请勿将“\n”(换行符)用于 null_string 值。Amazon Redshift 将保留“\n”以用作行分隔符。默认 null_string'\N'。

注意

如果您尝试将 null 加载到定义为 NOT NULL 的列中,则 COPY 命令将失败。

REMOVEQUOTES

删除传入数据中的字符串周围的引号。将保留引号中的所有字符(包括分隔符)。如果字符串具有开始单引号或双引号但没有对应的结束引号,则 COPY 命令将无法加载相应行并返回错误。下表显示了包含引号的字符串和最终加载值的一些简单示例。

输入字符串 使用 REMOVEQUOTES 选项加载的值
"The delimiter is a pipe (|) character" The delimiter is a pipe (|) character
'Black' Black
"White" White
Blue' Blue'
'Blue Value not loaded: error condition
"Blue Value not loaded: error condition
' ' 'Black' ' ' ' 'Black' '
' ' <空格>
ROUNDEC

当输入值的小数位数超出列的小数位数时,会将数值向上取整。默认情况下,COPY 将在必要时截断值以匹配列的小数位数。例如,如果将值 20.259 加载到 DECIMAL(8,2) 列中,则 COPY 默认情况下会将此值截断为 20.25。如果指定 ROUNDEC,则 COPY 会将值取整为 20.26。INSERT 命令始终在必要时将值取整以匹配列的小数位数,因此包含 ROUNDEC 参数的 COPY 命令的行为方式与 INSERT 命令相同。

TIMEFORMAT [AS] {'timeformat_string' | 'auto' | 'epochsecs' | 'epochmillisecs' }

指定时间格式。如果未指定 TIMEFORMAT,则默认格式为 YYYY-MM-DD HH:MI:SS(对于 TIMESTAMP 列)或 YYYY-MM-DD HH:MI:SSOF(对于 TIMESTAMPTZ 列),其中 OF 是与协调世界时 (UTC) 的时差。您不能在 timeformat_string 中包括时区标识符。要加载格式与默认格式不同的 TIMESTAMPTZ 数据,请指定“自动”;有关更多信息,请参阅 在 DATEFORMAT 和 TIMEFORMAT 中使用自动识别。有关 timeformat_string 的更多信息,请参阅 DATEFORMAT 和 TIMEFORMAT 字符串

在使用 DATEFORMAT 和 TIMEFORMAT 字符串时,'auto' 参数将识别一些不受支持的格式。如果 COPY 命令未识别日期或时间值的格式,或者日期和时间值使用不同的格式,请将 'auto' 参数与 DATEFORMAT 或 TIMEFORMAT 参数结合使用。有关更多信息,请参阅 在 DATEFORMAT 和 TIMEFORMAT 中使用自动识别

如果源数据以纪元时间(自 1970 年 1 月 1 日 00:00:00 UTC 以来的秒数或微秒数)表示,请指定 'epochsecs''epochmillisecs'

'auto''epochsecs''epochmillisecs' 关键字区分大小写。

AS 关键字是可选的。

TRIMBLANKS

删除 VARCHAR 字符串的尾部空格字符。此参数仅适用于具有 VARCHAR 数据类型的列。

TRUNCATECOLUMNS

将列中的数据截断为合适的字符数以符合列规范。仅适用于具有 VARCHAR 或 CHAR 数据类型的列以及大小为 4 MB 或以下的行。