这是本节的多页打印视图。点击此处打印.

数据格式

1: 分隔数据
2: 二进制（原生）数据
3: 原生 varchar 数据
4: 固定宽度格式数据
5: ORC 数据
6: Parquet 数据
7: JSON 数据
8: Avro 数据
9: 正则表达式中的匹配项
10: 通用事件格式 (CEF) 数据

COPY 支持多种数据格式，详细信息将在后面的章节中介绍。可以通过指定解析器来指定数据格式。

默认情况下， COPY 使用 DELIMITED 解析器 (分隔数据) 将原始数据加载到数据库。原始输入数据必须采用 UTF-8 分隔文本格式。其他解析器支持其他数据格式。

指定使用哪个解析器的语法各不相同。每个解析器的描述都包含此信息。

同一 COPY 语句不能将需要不同解析器的原始数据类型（例如 NATIVE 和 FIXEDWIDTH）进行混合。不过，您可以借助不同的解析器，使用不同的 COPY 语句将不同格式的数据加载到同一个表中。

有关验证输入数据格式的信息，请参阅处理非 UTF-8 输入。

本节中描述的所有解析器都可以与常规表（使用 CREATE TABLE 或 CREATE EXTERNAL TABLE 创建的表）一起使用。有些解析器还支持 Flex 表 (CREATE FLEX TABLE)。有关 Flex 表的详细信息，请参阅使用 Flex 表解析器。

所有解析器都支持所有基元数据类型，一些解析器支持基元类型的一维数组。一些解析器支持其他复杂类型。有关受支持的类型的信息，请参阅各个解析器的文档。

1 - 分隔数据

如果您未指定其他解析器，则 Vertica 默认使用 DELIMITED 解析器。您可以在 COPY 语句中指定分隔符、转义字符、处理 null 值的方式以及其他参数。

以下示例显示分隔符为 '|' 时的默认行为

=> CREATE TABLE employees (id INT, name VARCHAR(50), department VARCHAR(50));
CREATE TABLE

=> COPY employees FROM STDIN;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 42|Sheldon Cooper|Physics
>> 17|Howard Wolowitz|Astronomy
>> \.

=> SELECT * FROM employees;
 id |      name       |  department
----+-----------------+--------------
 17 | Howard Wolowitz | Astrophysics
 42 | Sheldon Cooper  | Physics
(2 rows)

默认情况下，集合值用括号分隔，元素用逗号分隔。集合必须是一维数组或标量类型集。

=> CREATE TABLE researchers (id INT, name VARCHAR, grants ARRAY[VARCHAR], values ARRAY[INT]);
CREATE TABLE

=> COPY researchers FROM STDIN;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 42|Sheldon Cooper|[US-7376,DARPA-1567]|[65000,135000]
>> 17|Howard Wolowitz|[NASA-1683,NASA-7867,SPX-76]|[16700,85000,45000]
>> \.

=> SELECT * FROM researchers;
 id |      name       |               grants               |       values
----+-----------------+------------------------------------+---------------------
 17 | Howard Wolowitz | ["NASA-1683","NASA-7867","SPX-76"] | [16700,85000,45000]
 42 | Sheldon Cooper  | ["US-7376","DARPA-1567"]           | [65000,135000]
(2 rows)

若要将某个特殊字符用作字面量，请在其前面添加一个转义字符。例如，要在加载的数据（例如当包含文件路径时）中包含一个字面量反斜线 (\)，请使用两个反斜线 (\)。COPY 会在加载转义的字符时从输入中删除转义字符。

加载分隔数据时，两个连续的分隔符表示一个 null 值，除非另外设置了 NULL 参数。最后的分隔符是可选的。例如，以下输入对上一个表有效：


=> COPY employees FROM STDIN;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 45|Raj|
>> 21|Leonard||
>> \.

=> SELECT * FROM employees;
 id |  name   |  department
----+---------+--------------
 21 | Leonard |
 42 | Raj     |
(2 rows)

默认情况下，如果数据包含的值太少，则加载失败。您可以使用 TRAILING NULLCOLS 选项接受任意数量的缺失列，并将其值视为 null。

Vertica 假设数据采用 UTF-8 编码。

特定于 DELIMITED 解析器的选项及其默认值为：

要将分隔数据加载到 Flex 表中，请使用 FDELIMITEDPARSER 解析器。

更改列分隔符 (DELIMITER)

默认 COPY 分隔符为竖线 ('|')。DELIMITER 是一个 ASCII 字符，用于分隔输入源中每条记录内的各个列。在两个分隔符之间，COPY 会将输入中的所有字符串数据解释为字符。请勿在字符串两侧加引号，因为引号字符也会被视为两个分隔符之间的字面量。

可以使用 E'\000' 至 E'\177'（包含）范围内的任何 ASCII 值来定义不同的分隔符。例如，如果加载的是 CSV 数据文件，并且这些文件使用逗号 (',') 字符作为分隔符，则可以将默认分隔符更改为逗号。不能对 DELIMITER 和 NULL 选项使用相同的字符。

如果分隔符位于一串数据值之间，请使用 ESCAPE AS 字符（默认值为 '\'）来指明应将分隔符视为字面量。

COPY 语句可接受空值（即两个连续分隔符）作为 CHAR 和 VARCHAR 数据类型的有效输入数据。COPY 会将空列存储为一个空字符串 ('')。空字符串不等同于 NULL 字符串。

要指示一个不可打印的分隔符（例如 Tab 键），请采用扩展字符串语法 (E'...') 来指定该字符。如果数据库启用了 StandardConformingStrings，请使用 Unicode 字符串字面量 (U&'...')。例如，使用 E'\t' 或 U&'\0009' 将 Tab 键指定为分隔符。

以下示例将从逗号分隔文件加载数据：

=> COPY employees FROM ... DELIMITER ',';

在以下示例中，第一列具有特定于列的分隔符：

=> COPY employees(id DELIMITER ':', name, department) FROM ... DELIMITER ',';

更改集合分隔符（COLLECTIONDELIMITER、COLLECTIONOPEN、COLLECTIONCLOSE）

DELIMITER 选项可指定在输入中分隔列的值。对于具有集合类型（ARRAY 或 SET）的列，集合的各元素之间还需要一个分隔符。此外，集合本身具有开始和结束标记。默认情况下，集合会用括号括起来，元素则用逗号进行分隔，但您可以更改这些值。

在以下示例中，集合用大括号括起来且用句点进行分隔。

=> COPY researchers FROM STDIN COLLECTIONOPEN '{' COLLECTIONCLOSE '}' COLLECTIONDELIMITER '.';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 19|Leonard|{"us-1672"."darpa-1963"}|{16200.16700}
>> \.

=> SELECT * FROM researchers;
 id |      name       |               grants               |       values
----+-----------------+------------------------------------+---------------------
 17 | Howard Wolowitz | ["NASA-1683","NASA-7867","SPX-76"] | [16700,85000,45000]
 42 | Sheldon Cooper  | ["US-7376","DARPA-1567"]           | [65000,135000]
 19 | Leonard         | ["us-1672","darpa-1963"]           | [16200,16700]
(3 rows)

更改将列或集合值括起来的字符（ENCLOSED BY、COLLECTIONENCLOSE）

使用 ENCLOSED BY 参数可以设置 ASCII 字符来分隔嵌入在字符串值中的各个字符。当且仅当括号字符是输入的第一个和最后一个字符时，括号字符才不会被视为是数据的一部分。可以将 E'\001' 至 E'\177'（包含）范围内的任何 ASCII 值（除 NULL: E'\000' 以外的任何 ASCII 字符）用于 ENCLOSED BY 值。使用双引号 (") 很常见，如以下示例所示。

=> COPY employees FROM STDIN ENCLOSED BY '"';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 21|Leonard|Physics
>> 42|"Sheldon"|"Physics"
>> 17|Rajesh "Raj" K|Astronomy
>> \.

=> SELECT * FROM employees;
 id |     name       |  department
----+----------------+--------------
 17 | Rajesh "Raj" K | Astronomy
 21 | Leonard        | Physics
 42 | Sheldon        | Physics
(3 rows)

请注意，虽然 ENCLOSED BY 是双引号，但 Rajesh 姓名中嵌入的引号被视为数据的一部分，因为它们不是列中的第一个字符和最后一个字符。出于所处位置原因，已删除将 "Sheldon" 和 "Physics" 括起来的引号。

在集合值中，COLLECTIONENCLOSE 参数类似于集合中各个元素的 ENCLOSED BY。

更改 null 指示符 (NULL)

默认情况下，列值的空字符串 ('') 表示 NULL。您可以在 E'\001' 至 E'\177'（包含）范围内指定其他 ASCII 值（除 NUL: E'\000' 之外的任何 ASCII 字符）作为 NULL 指示符。不能对 DELIMITER 和 NULL 选项使用相同的字符。

包含一个或多个空格字符的列不是 NULL，除非空格的顺序与 NULL 字符串完全匹配。

NULL 不区分大小写，但必须是数据字段分隔符之间的唯一值。例如，如果 NULL 字符串为 NULL 且分隔符为默认的竖线 (|)：

|NULL| 表示 null 值。

| NULL | 不表示 null 值。

在脚本中使用 COPY 语句时，必须将包含反斜线的每个 NULL 字符串替换为双反斜线。例如，用于加载示例数据库的脚本包含：

COPY ... NULL E'\\n' ...

更改集合值的 null 指示符 (COLLECTIONNULLELEMENT)

NULL 选项指定要将列值视为 null 的值。对于具有集合类型（ARRAY 或 SET）的列，单独的选项将指定如何解释 null 元素。默认情况下，"null" 表示 null 值。表示两个连续的元素分隔符的空值不表示 null：

=> COPY researchers FROM STDIN;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 17|Howard|["nasa-143",,"nasa-6262"]|[10000,1650,15367]
>> 19|Leonard|["us-177",null,"us-6327"]|[16200,64000,26500]
>> \.

=> SELECT * FROM researchers;
 id |  name   |           grants            |       values
----+---------+-----------------------------+---------------------
 17 | Howard  | ["nasa-143","","nasa-6262"] | [10000,1650,15367]
 19 | Leonard | ["us-177",null,"us-6327"]   | [16200,64000,26500]
(2 rows)

使用 COLLECTIONNULLELEMENT 指定不同的值，如以下示例所示。

=> COPY researchers from STDIN COLLECTIONNULLELEMENT 'x';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 42|Sheldon|[x,"us-1672"]|[x,165000]
>> \.

=> SELECT * FROM researchers;
 id |  name   |           grants            |       values
----+---------+-----------------------------+---------------------
 17 | Howard  | ["nasa-143","","nasa-6262"] | [10000,1650,15367]
 19 | Leonard | ["us-177",null,"us-6327"]   | [16200,64000,26500]
 42 | Sheldon | [null, "us-1672"]           | [null,165000]
(3 rows)

填充缺失的列 (TRAILING NULLCOLS)

默认情况下，如果输入不包含足够的列，则 COPY 会失败。使用 TRAILING NULLCOLS 选项为缺少数据的任何列插入 NULL 值。此选项不能用于具有 NOT NULL 约束的列。

以下示例演示了如何使用此选项。

=> CREATE TABLE z (a INT, b INT, c INT );

--- insert with enough data:
=> INSERT INTO z VALUES (1, 2, 3);

=> SELECT * FROM z;
 a | b | c
---+---+---
 1 | 2 | 3
(1 row)

--- insert deficient data:
=> COPY z FROM STDIN TRAILING NULLCOLS;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 4 | 5 | 6
>> 7 | 8
>> \.

=> SELECT * FROM z;
 a | b | c
---+---+---
 1 | 2 | 3
 4 | 5 | 6
 7 | 8 |
(3 rows)

更改转义字符（ESCAPE AS、NO ESCAPE）

您可以指定一个转义字符，它可以将任何特殊字符视为数据的一部分。例如，如果 CSV 文件中的元素应包含逗号，您可以通过在数据中的逗号前面附加转义字符来加以表示。默认转义字符为反斜线 (\)。

要将默认字符更改为其他字符，请使用 ESCAPE AS 选项。可以将转义字符设置为 E'\001' 至 E'\177'（包含）范围内的任何 ASCII 值。

如果您不需要任何转义字符且希望阻止任何字符被解释为转义序列，请使用 NO ESCAPE 选项。

ESCAPE AS 和 NO ESCAPE 可以在列和全局级别进行设置。

更改行尾字符 (RECORD TERMINATOR)

要指定表示数据文件记录结尾的字面量字符串，请使用 RECORD TERMINATOR 参数，后跟要使用的字符串。如果不指定值，则 Vertica 会尝试确定正确的行结束符，即仅接受换行符 (E'\n')（通常在 UNIX 系统上）或接受回车键和换行符 (E'\r\n')（通常在 Windows 平台上）。

例如，如果您的文件包含以换行符终止的逗号分隔值且您希望保留换行符，请使用 RECORD TERMINATOR 选项指定替代值：

=>  COPY mytable FROM STDIN DELIMITER ',' RECORD TERMINATOR E'\n';

要将 RECORD TERMINATOR 指定为不可打印的字符，请使用扩展字符串语法或 Unicode 字符串字面量。下表列出了一些常见的记录终止符。有关字面量字符串格式的说明，请参阅字符串字面量。

如果使用 RECORD TERMINATOR 选项指定自定义值，请确保输入文件与该值匹配，否则可能会得到不一致的数据加载。

注意

记录终止符不能与 DELIMITER、NULL、ESCAPE 或 ENCLOSED BY 相同。

使用 JDBC 时，Vertica 建议将以下值用于 RECORD TERMINATOR：

System.getProperty("line.separator")

2 - 二进制（原生）数据

可使用 NATIVE 解析器选项加载二进制数据，不支持此选项的 COPY LOCAL 除外。由于二进制格式数据不需要使用和处理分隔符，因此无需将整数、日期和时间戳从文本转换成本机存储格式，与分隔的数据相比，其加载性能更佳。所有二进制格式文件都必须符合附录：创建原生二进制格式文件中描述的格式规范。

本机二进制格式数据文件通常比其分隔文本格式的数据文件要大，所以在加载之前需先压缩数据。NATIVE 解析器不支持串联的压缩二进制文件。开发 ETL 应用程序插件时，您可以加载本机（二进制）格式文件。

逐字节加载二进制数据时无格式复制，因为数据中的列和记录分隔符必须进行转义。二进制数据类型值会填充到输入、受支持的函数、操作符以及类型转换中并进行转换。

加载十六进制、八进制和位字符串数据

可以仅使用十六进制、八进制和位字符串格式来加载二进制列。若要指定这些列格式，请使用 COPY 语句的 FORMAT 选项：

十六进制
八进制
位字符串

以下示例说明如何使用 FORMAT 选项。

创建一个表：

=> CREATE TABLE t(oct VARBINARY(5),
     hex VARBINARY(5),
     bitstring VARBINARY(5) );

创建投影：

=> CREATE PROJECTION t_p(oct, hex, bitstring) AS SELECT * FROM t;

使用带有 STDIN 子句的 COPY 语句并指定每个格式：

=> COPY t (oct FORMAT 'octal', hex FORMAT 'hex',
           bitstring FORMAT 'bitstring')
   FROM STDIN DELIMITER ',';

在单独一行中输入要加载的数据，以反斜杠 () 和句点 (.) 结束语句：

Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 141142143144145,0x6162636465,0110000101100010011000110110010001100101
>> \.

对表 t 使用选择查询以查看输入值结果：

=> SELECT * FROM t;
 oct   | hex   | bitstring
-------+-------+-----------
abcde  | abcde | abcde
(1 row)

COPY 使用与输入二进制数据所用格式相同的默认格式来加载二进制数据。由于反斜杠字符 ('\\') 为默认转义字符，因此必须对八进制输入值进行转义。例如，将字节 '\141' 输入为 '\\\141'。

注意

如果输入的转义字符后跟无效的八进制数字，或转义字符已被转义，COPY 将返回错误。

在输入中，COPY 按如下方式转换字符串数据：

使用 HEX_TO_BINARY 函数从十六进制表示转换成二进制。
使用 BITSTRING_TO_BINARY 函数从位字符串表示转换成二进制。

这两个函数都使用 VARCHAR 实参并返回 VARBINARY 值。

也可以使用转义字符通过转义两次来表示（十进制）字节 92；例如 '\\\\\\'。请注意，vsql 以四个反斜杠输入转义的反斜杠。等效输入为十六进制值 '0x5c' 和八进制值 '\134' (134 = 1 x 8^2 + 3 x 8^1 + 4 x 8^0 = 92)。

您可以加载使用反斜杠转义后的分隔符值。例如，假定分隔符为 '|'，'\\\001\\|\\\002' 则被加载为 {1,124,2}，也可以采用八进制格式将其表示成 '\\\001\\\174\\\002'。

如果插入值的字节数太多，以致于目标列容纳不下，COPY 将会返回错误。例如，如果列 c1 为 VARBINARY(1)：

=> INSERT INTO t (c1) values ('ab');   ERROR: 2-byte value too long for type Varbinary(1)

如果您对字节数过多以致于目标数据类型容纳不下的值进行隐式或显式类型转换，COPY 则会截断该数据而不进行提示。例如：

=> SELECT 'abcd'::binary(2);
 binary
--------
 ab
(1 row)

十六进制数据

可选的 '0x' 前缀指示值为十六进制而非十进制，但并非所有十六进制值都使用 A-F；例如 5396。在加载输入数据时，COPY 将忽略 0x 前缀。

如果十六进制值中存在奇数个字符，则将第一个字符处理为第一个（最左边）字节的低效半字节。

八进制数据

加载八进制格式数据要求使用三个数字的八进制代码表示每个字节。第一个数字必须在 [0,3] 范围内，第二个和第三个数字必须同时在 [0,7] 范围内。

如果某个八进制值的长度不是三的倍数，或者三个数字中有一个不在正确范围内，该值则无效，COPY 将拒绝该值所在的行。如果提供的八进制值无效，COPY 将会返回错误。例如：

=> SELECT '\\000\\387'::binary(8);
ERROR: invalid input syntax for type binary

如果行包含具有无效八进制表示的二进制值，则也会被拒绝。例如，COPY 会拒绝 '\\\008'，因为 '\\\ 008' 不是有效的八进制数字。

位字符串数据

加载位字符串数据要求每个字符必须是零 (0) 或一 (1)，值必须是八个字符的倍数。如果位字符串值不是八个字符的倍数，COPY 则会将前 n 个字符视为第一个（最左端）字节的低位，其中 n 为值长度除以八之后的余数。

示例

以下示例显示了 VARBINARY HEX_TO_BINARY(VARCHAR) 和 VARCHAR TO_HEX(VARBINARY) 的用法。

创建带有二进制列的表 t 及其投影：

=> CREATE TABLE t (c BINARY(1));
=> CREATE PROJECTION t_p (c) AS SELECT c FROM t;

插入最小和最大字节值，包括以字符串表示的 IP 地址：

=> INSERT INTO t values(HEX_TO_BINARY('0x00'));
=> INSERT INTO t values(HEX_TO_BINARY('0xFF'));
=> INSERT INTO t values (V6_ATON('2001:DB8::8:800:200C:417A'));

使用 TO_HEX 函数在输出中以十六进制设置二进制值的格式：

=> SELECT TO_HEX(c) FROM t;
 to_hex
--------
 00
 ff
 20
(3 rows)

另请参阅

3 - 原生 varchar 数据

当原始数据主要包含 CHAR 或 VARCHAR 数据时，请使用 NATIVE VARCHAR 解析器选项。 COPY 将在数据库服务器上执行转换为实际表数据类型的操作。COPY LOCAL 不支持此解析器选项。

使用 NATIVE VARCHAR 与 NATIVE 的效率不同。但 NATIVE VARCHAR 无需使用分隔符或转义特殊字符（例如引号），这样可以更方便地使用客户端应用程序。

注意

NATIVE VARCHAR 不支持串联的压缩文件。

通过 Vertica ODBC 和 JDBC 驱动程序执行的批量数据插入操作将自动使用 NATIVE VARCHAR 格式。

4 - 固定宽度格式数据

使用 FIXEDWIDTH 解析器选项可批量加载固定宽度数据。必须指定 COLSIZES 选项值以指定每一列的字节数。所加载表的定义 (COPY table f (x, y, z)) 确定要声明的 COLSIZES 值的数量。

若要加载固定宽度数据，请使用 COLSIZES 选项指定每个输入列的字节数。如果任何记录都没有值，COPY 会插入一个或多个 null 字符以补齐指定字节数。固定宽度数据文件中的最后一条记录必须包含记录终止符，以确定加载数据的结尾。

以下 COPY 选项不受支持：

DELIMITER
ENCLOSED BY
ESCAPE AS
TRAILING NULLCOLS

在固定宽度数据中使用 null 值

用于固定宽度加载的默认 NULL 字符串不能为空字符串，应改用全部由空格组成的字符串。空格数取决于使用 COLSIZES (integer, [,...]) 选项声明的列宽度。

对于固定宽度加载，NULL 定义取决于是在列级别还是在语句级别指定 NULL：

语句级别：必须将 NULL 定义为单字符。整个列宽度重复使用默认（或自定义）的 NULL 字符。
列级别：必须将 NULL 定义为字符串，其长度与列宽度相匹配。

对于固定宽度加载，如果输入数据列的值数量少于指定列大小，COPY 会插入 NULL 字符。NULL 数量必须与声明的列宽度相匹配。如果您在列级别指定 NULL 字符串，COPY 会按照列宽度匹配字符串。

注意

若要关闭 NULL，请使用 NULL AS 选项并指定 NULL AS ''。

定义 null 字符（语句级别）

创建包含两个列的表 (fw)：

=> CREATE TABLE fw(co int, ci int);
CREATE TABLE

复制该表并将 NULL 指定为 'N'，然后输入一些数据：

=> COPY fw FROM STDIN FIXEDWIDTH colsizes(2,2) null AS 'N' NO COMMIT;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> NN12
>> 23NN
>> NNNN
>> nnnn
>> \.

从表中选择所有 (*)：

=> SELECT * FROM fw;
 co | ci
----+----
    | 12
 23 |
(2 rows)

定义自定义的记录终止符

要定义不同于加载固定宽度数据时的 COPY 默认值的记录终止符，请执行以下步骤：

创建包含两列（co 和 ci）的表 fw：

=> CREATE TABLE fw(co int, ci int);
CREATE TABLE

复制该表 fw，并指定列大小为 2 个字节，指定逗号 (,) 作为记录终止符：

=> COPY fw FROM STDIN FIXEDWIDTH colsizes(2,2) RECORD TERMINATOR ',';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1234,1444,6666
>> \.

查询表 fw 中的所有数据：

=> SELECT * FROM fw;
 co | ci
----+----
 12 | 34
 14 | 44
(2 rows)

SELECT 输出仅指示两个值。 COPY 拒绝了第三个值 (6666)，因为其后无逗号 (,) 记录终止符。仅在显式指定记录终止符时，固定宽度数据才需要尾随记录终止符。

复制固定宽度的数据

使用 COPY FIXEDWIDTH COLSIZES (n [,...) 将文件加载到 Vertica 数据库中。默认情况下，所有空格均为 NULL。例如：

=> CREATE TABLE mytest(co int, ci int);
=> CREATE PROJECTION mytest_p1 AS SELECT * FROM mytest SEGMENTED BY HASH(co) ALL NODES;
=> COPY mytest(co,ci) FROM STDIN FIXEDWIDTH colsizes(6,4) NO COMMIT;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> \.
=> SELECT * FROM mytest ORDER BY co;
 co | ci
----+----
(0 rows)

跳过固定宽度数据中的内容

COPY 语句有两个用于跳过输入数据的选项。SKIP BYTES 选项仅用于固定宽度数据加载：

以下示例将使用 SKIP BYTES 在加载包含两列（4 和 6 个字节）的固定宽度表时跳过 11 个字节：

使用 SKIP BYTES 复制表：

=> COPY fw FROM STDIN FIXEDWIDTH colsizes (4,6) SKIP BYTES 11;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 2222666666
>> 1111999999
>> 1632641282
>> \.

查询表 fw 中的所有数据：

=> SELECT * FROM fw ORDER BY co;
  co  |   ci
------+--------
 1111 | 999999
 1632 | 641282
(2 rows)

输出会确认 COPY 跳过了已加载数据的前 11 个字节。

以下示例会在加载固定宽度 (4、6) 表时使用 SKIP：

复制表并使用 SKIP 跳过输入数据的两条记录：

=> COPY fw FROM STDIN FIXEDWIDTH colsizes (4,6) SKIP 2;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 2222666666
>> 1111999999
>> 1632641282
>> 3333888888
>> \.

查询表 fw 中的所有数据：

=> SELECT * FROM fw ORDER BY co;
  co  |   ci
------+--------
 1632 | 641282
 3333 | 888888
(2 rows)

输出会确认 COPY 跳过了已加载数据的前两条记录。

在固定宽度的数据加载中修剪字符

使用 TRIM 选项可修剪字符。 TRIM 接受在数据开头和结尾处修剪的单字节字符。对于固定宽度数据加载，当您指定 TRIM 字符时，COPY 会先检查行是否为 NULL。如果行不为 NULL，COPY 会修剪字符。下一个示例指示 COPY 修剪字符 A，并显示结果：

复制表 fw 并指定 TRIM 字符 A：

=> COPY fw FROM STDIN FIXEDWIDTH colsizes(4,6) TRIM 'A';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> A22A444444
>> A22AA4444A
>> \.

查询表 fw 中的所有数据：

=> SELECT * FROM fw ORDER BY co;
 co |   ci
----+--------
 22 |   4444
 22 | 444444
(2 rows)

在固定宽度的数据加载中使用填充

默认情况下，填充字符为“ ”（单个空格）。固定宽度数据加载的填充行为类似于如何以其他格式处理空格，处理方式因数据类型而异，具体如下：

5 - ORC 数据

ORC（优化的行列）格式是一种面向列的文件格式。Vertica 拥有一个可处理此格式且可利用列式布局的解析器。

在 COPY 语句中，请按如下所示指定解析器。请注意，这不是通常的 PARSER parser-name 语法；COPY 直接支持 ORC：

=> COPY tableName FROM path ORC[(...)];

该解析器会使用多个可选参数；请参阅 ORC 参考页面。

请注意，如果您在同一个 COPY 语句中从多个文件加载数据，而其中任何一个加载被中止，那么整个加载都会中止。这种行为与分隔文件的行为不同，分隔文件中的 COPY 语句会加载其可以加载的数据并忽略剩余数据。

支持的数据类型

Vertica 可以本机读取所有 Hive 原始数据类型的列。有关完整列表，请参阅 HIVE 数据类型（特别是数字、日期/时间、字符串和其他列表）。Vertica 还可以加载 UUID 和复杂类型（任意组合的数组和结构）。

您为 COPY 或 CREATE EXTERNAL TABLE AS COPY 指定的数据类型必须与数据中的类型完全匹配，但 Vertica 允许在兼容的数字类型之间进行隐式转换。

时间戳和时区

要正确报告时间戳，Vertica 必须知道将数据写入的时区。早期版本的 ORC 格式不会记录该时区。

Hive 版本 1.2.0 及更高版本会在条带页脚中记录写入者所在的时区。Vertica 会使用该时区来确保读入到数据库中的时间戳值与写入到源文件的时间戳值相匹配。对于缺少此时区信息的 ORC 文件，Vertica 假设这些值已写入到本地时区，并在 QUERY_EVENTS 系统表中记录 ORC_FILE_INFO 事件。在第一次查询后检查此类型的事件，以验证时间戳是否按预期予以处理。

6 - Parquet 数据

Parquet 是一种面向列的文件格式。Vertica 拥有一个可以利用列式布局的解析器。

在 COPY 语句中，请按如下所示指定解析器。请注意，这不是通常的 PARSER parser-name 语法；COPY 直接支持 Parquet：


=> COPY tableName FROM path PARQUET[(...)];

该解析器会使用多个可选参数；请参阅 PARQUET 参考页面。

架构匹配

默认情况下，Parquet 解析器使用强架构匹配。这意味着加载必须按照列在数据中出现的顺序来使用 Parquet 数据中的所有列。但是，您可以改为使用弱架构匹配，以便选择所需的列而忽略剩余列。弱架构匹配取决于数据中列的名称而非其顺序，因此表中的列名称必须与数据中的列名称匹配。类型必须匹配或可进行强制设置。有关如何使用宽泛的架构匹配的详细信息，请参阅 PARQUET 参考页面上的弱架构匹配。

您可以对基元类型的列和基元类型的一维数组使用弱架构匹配。对于其他复杂类型，请改用可变复杂类型。请参阅可变复杂类型。

使用 do_soft_schema_match_by_name 参数指定弱架构匹配。在以下示例中，Parquet 数据包含的列多于表中使用的列。

=> CREATE EXTERNAL TABLE restaurants(name VARCHAR, cuisine VARCHAR)
    AS COPY FROM '/data/rest*.parquet'
    PARQUET(do_soft_schema_match_by_name='True');

=> SELECT * from restaurant;
       name        | cuisine
-------------------+----------
 Bob's pizzeria    | Italian
 Bakersfield Tacos | Mexican
(2 rows)

元数据缓存

Parquet 文件包含 Vertica 在加载数据时使用的元数据。为避免重复获提取此数据，尤其是从远程源或 API 调用会产生财务成本的情况下，Vertica 会在计划阶段将此元数据缓存在每个参与节点上，以供在执行阶段使用。

仅当 TEMP 存储位于本地文件系统上时，Vertica 才会将 TEMP 存储用于缓存。

您可以通过设置 ParquetMetadataCacheSizeMB 配置参数来限制缓存的大小。默认值为 4GB。

支持的数据类型

您为 COPY 或 CREATE EXTERNAL TABLE AS COPY 指定的数据类型必须与数据中的类型完全匹配，但 Vertica 允许在兼容的数字类型之间进行隐式转换。

仅对于 Parquet 格式，您可以使用可变复杂类型，而不是完全指定复杂类型的架构。请参阅可变复杂类型。

时间戳和时区

要正确报告时间戳，Vertica 必须知道将数据写入的时区。Hive 不会记录写入者所在的时区。Vertica 假设时间戳值是在本地时区写入的，并在查询时报告警告。

在写入 Parquet 文件时，Hive 提供了用于在本地时区中记录时间戳的选项。如果您使用以这种方式记录时间的 Parquet 文件，请将 UseLocalTzForParquetTimestampConversion 配置参数设置为 0 以禁用由 Vertica 完成的转换。（请参阅常规参数。）

7 - JSON 数据

使用 FJSONPARSER 加载 JSON 格式的数据。

JSON 数据的架构是属性/值对中的一组属性名称。将 JSON 数据加载到列式表或 Flex 表中的实体化列中时，数据中的属性名称必须与表中的列名称匹配。您不需要加载数据中的所有列。

JSON 解析器可以将数据加载到任何标量类型、强类型复杂类型或可变复杂类型的列中。可变复杂类型意味着您没有完全指定该列的架构。可以将表中的这些列定义为 LONG VARBINARY，并且可以使用 Flex 函数从中提取值。

在 COPY 语句中，使用 PARSER 参数可指定 JSON 解析器，如以下示例所示：

=> CREATE EXTERNAL TABLE customers(id INT, address VARCHAR, transactions ARRAY[INT,10])
    AS COPY FROM 'cust.json' PARSER FJSONPARSER();

此解析器有几个可选参数，其中一些专门用于 Flex 表和可变复杂类型。

在加载 JSON 数据之前，请考虑使用 JSONLint 等工具来验证数据是否有效。

如果将 JSON 数据加载到 Flex 表中，Vertica 会将所有数据加载到 __raw__ (VMap) 列中，包括在数据中找到的复杂类型。可以使用 Flex 函数来提取值。

强类型复杂类型

JSON 数据可能包含数组、结构体以及这两者的组合。可以将这些数据作为可变 (VMap) 列或具有强类型的可变列来加载。强类型允许您直接查询值，而无需使用函数来解压 VMap 列。

像往常一样，在表定义中使用 ARRAY 和 ROW 类型：

=> CREATE EXTERNAL TABLE rest
  (name VARCHAR, cuisine VARCHAR,
   location_city ARRAY[VARCHAR(80),50],
   menu ARRAY[ ROW(item VARCHAR(80), price FLOAT), 100 ]
  )
 AS COPY FROM :restdata PARSER FJSONPARSER();

=> SELECT name, location_city, menu FROM rest;
       name        |       location_city        |                                     menu
-------------------+----------------------------+------------------------------------------------------------------------------
 Bob's pizzeria    | ["Cambridge","Pittsburgh"] | [{"item":"cheese pizza","price":8.25},{"item":"spinach pizza","price":10.5}]
 Bakersfield Tacos | ["Pittsburgh"]             | [{"item":"veggie taco","price":9.95},{"item":"steak taco","price":10.95}]
(2 rows)

将 JSON 数据加载到具有复杂类型的强类型的表中时，Vertica 会忽略解析器的 flatten_maps 和 flatten_arrays 参数。

强复杂类型和可变复杂类型

强类型的优点是在查询中访问数据更容易（且更高效）。缺点是会忽略在数据中找到但未包含在列定义中的其他值。如果此数据中的菜单结构体包含更多属性（例如卡路里），则不会加载它们，因为列的定义仅指定了选项和价格。以下示例使用可变复杂类型来显示额外的属性：

=> CREATE EXTERNAL TABLE rest
  (name VARCHAR, cuisine VARCHAR,
   location_city LONG VARBINARY, menu LONG VARBINARY)
 AS COPY FROM :restdata
 PARSER FJSONPARSER(flatten_maps=false);

=> SELECT name, MAPTOSTRING(location_city) as location_city, MAPTOSTRING(menu) AS menu FROM rest;
       name        |       location_city      |                     menu
-------------------+--------------------------+---------------------------------------------------
 Bob's pizzeria    | {
    "0": "Cambridge",
    "1": "Pittsburgh"
} | {
    "0": {
        "calories": "1200",
        "item": "cheese pizza",
        "price": "8.25"
    },
    "1": {
        "calories": "900",
        "item": "spinach pizza",
        "price": "10.50"
    }
}
 Bakersfield Tacos | {
    "0": "Pittsburgh"
}                    | {
    "0": {
        "item": "veggie taco",
        "price": "9.95",
        "vegetarian": "true"
    },
    "1": {
        "item": "steak taco",
        "price": "10.95"
    }
}
(2 rows)

从特定的起点加载

您无需加载整个 JSON 文件。您可以使用 start_point 参数在特定键而非文件开头处加载数据。数据解析始于 start_point 键后，直到文件的末尾或首个 start_point 值的末尾。解析器将忽略 start_point 的任何后续实例，即使该键在输入文件中多次出现也是如此。如果输入数据只包含 start_point 键的一个副本，并且该值为 JSON 元素的列表，解析器会将列表中的各个元素加载为行。

如果 start_point 值在 JSON 数据中出现多次，您可以使用 start_point_occurrence 整数参数来指定在哪次出现时开始解析。

此示例使用以下 JSON 数据，这些数据已保存到名为 alphanums.json 的文件中：

 { "A": { "B": { "C": [ { "d": 1, "e": 2, "f": 3 }, { "g": 4, "h": 5, "i": 6 },
{ "j": 7, "k": 8, "l": 9 } ] } } }

将此数据加载到 Flex 表中会产生以下结果：

=> CREATE FLEX TABLE start_json;
CREATE TABLE

=> COPY start_json FROM '/home/dbadmin/data/alphanums.json' PARSER FJSONPARSER();
 Rows Loaded
-------------
           1
(1 row)

=> SELECT maptostring(__raw__) FROM start_json;
                        maptostring
-------------------------------------------------------------------------------
 {
   "A.B.C" : {
      "0.d" : "1",
      "0.e" : "2",
      "0.f" : "3",
      "1.g" : "4",
      "1.h" : "5",
      "1.i" : "6",
      "2.j" : "7",
      "2.k" : "8",
      "2.l" : "9"
   }
}

(1 row)

以下加载指定了起点：

=> TRUNCATE TABLE start_json;
TRUNCATE TABLE

=>  COPY start_json FROM '/home/dbadmin/data/alphanums.json' PARSER FJSONPARSER(start_point='B');
 Rows Loaded
-------------
           1
(1 row)

=> SELECT maptostring(__raw__) FROM start_json;
                         maptostring
--------------------------------------------------------------------------------
 {
   "C" : {
      "0.d" : "1",
      "0.e" : "2",
      "0.f" : "3",
      "1.g" : "4",
      "1.h" : "5",
      "1.i" : "6",
      "2.j" : "7",
      "2.k" : "8",
      "2.l" : "9"
   }
}
(1 row)

处理无效的 JSON 记录

如果 JSON 数据包含语法错误，您的加载可能会因无效记录而失败。如果 JSON 记录始终用换行符等字符进行分隔，则可以在 COPY 语句中使用 RECORD_TERMINATOR 选项来跳过这些无效的记录。设置记录终止符允许解析器跳过无效记录并继续解析剩余数据。

如果标记记录所用的字符不一致，则可以使用 ERROR TOLERANCE 选项。ERROR TOLERANCE 会跳过包含无效 JSON 记录的整个源文件，而 RECORD_TERMINATOR 会跳过个别格式错误的 JSON 记录。您可以同时使用这两个选项。

以下示例将使用无效记录：

=> => CREATE FLEX TABLE fruits();
CREATE TABLE

=> COPY fruits FROM STDIN PARSER FJSONPARSER(RECORD_TERMINATOR=E'\n');
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself
>> {"name": "orange", "type": "fruit", "color": "orange", "rating": 5 }
>> {"name": "apple", "type": "fruit", "color": "green" }
>> {"name": "blueberry", "type": "fruit", "color": "blue", "rating": 10 }
>> "type": "fruit", "rating": 7 }
>> {"name": "banana", "type" : "fruit", "color": "yellow", "rating": 3 }
>> \.

使用 MAPTOSTRING 查看 Flex 表，以确认在成功加载剩余记录时跳过了无效记录：

=> SELECT MAPTOSTRING(__raw__) FROM fruits;
maptostring
--------------------------------------------------------------------------------------------
{
"color" : "orange",
"name" : "orange",
"rating" : "5",
"type" : "fruit"
}

{
"color" : "green",
"name" : "apple",
"type" : "fruit"
}

{
"color" : "blue",
"name" : "blueberry",
"rating" : "10",
"type" : "fruit"
}

{
"color" : "yellow",
"name" : "banana",
"rating" : "3",
"type" : "fruit"
}
(4 rows)

拒绝包含实体化列类型错误的数据

默认情况下，如果 FJSONPARSER 无法将数据值强制转换为与列定义匹配的类型，它会将值设置为 NULL。您可以选择使用 reject_on_materialized_type_error 参数来拒绝这些值。如果此参数为 true，则 COPY 拒绝此类行并报告错误。

如果该列是强类型复杂类型，而不是可变复杂类型，则复杂类型中的类型不匹配将导致整个列被视为不匹配。解析器不会部分加载复杂类型；如果无法强制使用任何 ROW 字段或 ARRAY 元素，则会为列加载 NULL。

以下示例会尝试加载无效数据。请注意，查询结果中缺少无效行：

=> CREATE TABLE test(one VARCHAR, two INT);
CREATE TABLE

=> COPY test FROM stdin
   PARSER FJSONPARSER(reject_on_materialized_type_error=true);
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> {"one": 1, "two": 2}
>> {"one": "one", "two": "two"}
>> {"one": "one", "two": 2}
>> \.

=> SELECT one, two FROM test;
 one | two
-----+-----
 1   |   2
 one |   2
(2 rows)

拒绝或忽略 Flex 表中的空键

有效的 JSON 文件可能包含空键值对。默认情况下，对于 Flex 表，FJSONPARSER 会加载空键值对，如以下示例所示：

=> CREATE FLEX TABLE fruits();
CREATE TABLE

=> COPY fruits FROM STDIN PARSER FJSONPARSER();
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> {"name": "orange", "rating": 5}
>> {"name": "apple", "rating" : 10}
>> {"": "banana", "rating" : 2}
>> \.

=> SELECT MAPTOSTRING(__raw__) FROM fruits;
              maptostring
---------------------------------------
 {
        "name": "orange",
        "rating": "5"
}
 {
        "name": "apple",
        "rating": "10"
}
 {
        "": "banana",
        "rating": "2"
}
(3 rows)

要忽略具有空白键的字段，请使用 omit_empty_keys 参数：

=> COPY fruits FROM STDIN PARSER FJSONPARSER(omit_empty_keys=true);
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> {"name": "apple", "rating": 5}
>> {"": "missing", "rating" : 1}
>> {"name": "", "rating" : 3}
>> \.
=> SELECT MAPTOSTRING(__raw__) FROM fruits;
             maptostring
--------------------------------------
 {
        "name": "apple",
        "rating": "5"
}
 {
        "rating": "1"
}
 {
        "name": "",
        "rating": "3"
}
(3 rows)

请注意，缺少名称的第二个值仍会与另一个（非空）字段一起加载。要完全拒绝该行，请使用 reject_on_empty_key 参数：

=> COPY fruits FROM STDIN PARSER FJSONPARSER(reject_on_empty_key=true);
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> {"name" : "apple", "rating" : 5}
>> {"" : "missing", "rating" : 1}
>> {"name" : "", "rating" : 3}
>> \.
=> SELECT MAPTOSTRING(__raw__) FROM fruits;
             maptostring
--------------------------------------
 {
        "name": "apple",
        "rating": "5"
}
 {
        "name": "",
        "rating": "3"
}
(2 rows)

8 - Avro 数据

使用 FAVROPARSER 可加载 Avro 数据文件。此解析器支持列式表和 Flex 表。

列可以是任何标量类型、强类型复杂类型或可变复杂类型。可变复杂类型意味着您没有完全指定该列的架构。您将表中的这些列定义为 LONG VARBINARY，并且可以使用 Flex 函数从中提取值。

需要满足以下要求：

Avro 文件必须以 Avro 二进制序列化编码格式进行编码，如 Apache Avro 标准中所述。解析器还支持 Snappy 和 deflate 压缩。
Avro 文件必须在正在加载的文件中包含其相关架构；FAVROPARSER 不支持具有单独架构文件的 Avro 文件。

在 COPY 语句中，使用 PARSER 参数可指定 Avro 解析器，如下例所示：

=> COPY weather FROM '/home/dbadmin/data/weather.avro' PARSER FAVROPARSER()

此解析器有几个可选参数，其中一些专门用于 Flex 表和可变复杂类型。

Avro 架构和列式表

Avro 将架构包含在数据中。将 Avro 数据加载到列式表中时，数据中架构中的列名称必须与表中的列名称匹配。您不需要加载数据中的所有列。

例如，以下 Avro 架构使用 Avro 记录类型来表示用户配置文件：

{
  "type": "record",
  "name": "Profile",
  "fields" : [
      {"name": "UserName", "type": "string"},
      {"name": "Email", "type": "string"},
      {"name": "Address", "type": "string"}
   ]
}

要使用此架构成功地将数据加载到列式表中，每个目标列名称必须与架构中的“名称”值匹配。在以下示例中，profiles 表不会加载与架构的 Email 字段对应的值，因为目标列名为 EmailAddr：

=> COPY profiles FROM '/home/dbadmin/data/user_profile.avro' PARSER FAVROPARSER();

=> SELECT * FROM profiles;
     UserName    |     EmailAddr      |       Address
-----------------+--------------------+---------------------
    dbadmin      |                    |    123 Main St.

强类型复杂类型

Avro 数据可以包含数组、结构和两者的组合。可以将这些数据作为可变 (VMap) 列或强类型来读取。强类型允许您直接查询值，而无需使用函数来解压 VMap 列。

像往常一样，在表定义中使用 ARRAY 和 ROW 类型：

=> CREATE EXTERNAL TABLE rest
  (name VARCHAR, cuisine VARCHAR,
   location_city ARRAY[VARCHAR(80)],
   menu ARRAY[ ROW(item VARCHAR(80), price FLOAT) ]
  )
AS COPY FROM :avro_file PARSER FAVROPARSER();

您可以在外部表和原生表中使用强类型。

可变复杂类型可以替代复杂类型的强类型。

拒绝包含实体化列类型错误的数据

默认情况下，如果 FAVROPARSER 无法将数据值强制转换为与列定义匹配的类型，它会将值设置为 NULL。您可以选择使用 reject_on_materialized_type_error 参数来拒绝这些值。如果此参数为 true，则 COPY 拒绝此类行并报告错误。

如果该列是强类型复杂类型，而不是可变复杂类型，则复杂类型中的类型不匹配将导致整个列被视为不匹配。解析器不会部分加载复杂类型。

如果 Flex 表具有实体化列，则加载的数据必须强制转换为该列的类型。例如，如果实体化列声明为 FLOAT 而您尝试为该键加载 VARCHAR 值，则 FAVROPARSER 拒绝该数据行。

另请参阅

手动使用来自 Kafka 的数据

9 - 正则表达式中的匹配项

您可以使用 fregexparser 将正则表达式的匹配结果加载到 Flex 表或列式表中。本节将介绍 Flex 解析器支持选项的一些使用示例。

示例正则表达式

这些示例将使用以下正则表达式，它们会搜索包括 timestamp、date、thread_name 和 thread_id 字符串在内的信息。

当心

出于显示目的，此示例正则表达式添加了换行符以拆分长文本行。要在查询中使用此表达式，请先复制并编辑示例，以移除任何换行符。

此示例表达式会加载任何 thread_id 十六进制值，而无论这些值是否具有 0x 前缀 (<thread_id>(?:0x)?[0-9a-f]+).

'^(?<time>\d\d\d\d-\d\d-\d\d \d\d:\d\d:\d\d\.\d+)
 (?<thread_name>[A-Za-z ]+):(?<thread_id>(?:0x)?[0-9a-f]+)
-?(?<transaction_id>[0-9a-f])?(?:[(?<component>\w+)]
\<(?<level>\w+)\> )?(?:<(?<elevel>\w+)> @[?(?<enode>\w+)]?: )
?(?<text>.*)'

将正则表达式匹配项用于 Flex 表

您可以使用 fregexparser 将正则表达式结果加载到 Flex 表中。。有关执行此操作的完整示例，请参阅 FREGEXPARSER。

针对列式表使用 fregexparser

此部分说明了如何加载针对 Vertica 数据库示例日志文件使用的正则表达式的结果。通过使用外部表定义，介绍了使用 fregexparser 将数据加载到列式表中的示例。将 Flex 表解析器用于列式表使您能够在一个表中混合数据加载。例如，您可以在一个会话中加载正则表达式的结果，在另一个会话中加载 JSON 数据。

以下基本示例说明了这种用法。

创建一个列式表 vlog，带有以下列：

=> CREATE TABLE vlog (
    "text"             varchar(2322),
    thread_id         varchar(28),
    thread_name     varchar(44),
    "time"             varchar(46),
    component         varchar(30),
    level             varchar(20),
    transaction_id     varchar(32),
    elevel             varchar(20),
    enode             varchar(34)
);

借助 fregexparser 使用 COPY 加载采用上述示例正则表达式的日志文件的多个部分：在您自己尝试之前，请务必从该表达式示例中移除任何行字符：

=> COPY v_log FROM '/home/dbadmin/data/flex/vertica.log' PARSER
FRegexParser(pattern='^(?<time>\d\d\d\d-\d\d-\d\d \d\d:\d\d:\d\d\.\d+)
 (?<thread_name>[A-Za-z ]+):(?<thread_id>(?:0x)?[0-9a-f]+)
-?(?<transaction_id>[0-9a-f])?(?:[(?<component>\w+)]
\<(?<level>\w+)\> )?(?:<(?<elevel>\w+)> @[?(?<enode>\w+)]?: )
?(?<text>.*)'


) rejected data as table fregex_reject;

查询 time 列：

=> SELECT time FROM flogs limit 10;
          time
-------------------------
 2014-04-02 04:02:02.613
 2014-04-02 04:02:02.613
 2014-04-02 04:02:02.614
 2014-04-02 04:02:51.008
 2014-04-02 04:02:51.010
 2014-04-02 04:02:51.012
 2014-04-02 04:02:51.012
 2014-04-02 04:02:51.013
 2014-04-02 04:02:51.014
 2014-04-02 04:02:51.017
(10 rows)

借助 fregexparser 使用外部表

通过为 Vertica 日志文件创建外部列式表，查询该表将返回更新后的日志信息。以下基本示例说明了这种用法。

创建列式表 vertica_log，使用 AS COPY 子句和 fregexparser 加载正则表达式的匹配结果。为了便于说明，此正则表达式具有用于拆分长文本行的换行符。在使用此表达式进行测试之前，请移除所有换行符：

=> CREATE EXTERNAL TABLE public.vertica_log
(
    "text" varchar(2322),
    thread_id varchar(28),
    thread_name varchar(44),
    "time" varchar(46),
    component varchar(30),
    level varchar(20),
    transaction_id varchar(32),
    elevel varchar(20),
    enode varchar(34)
)
AS COPY
FROM '/home/dbadmin/data/vertica.log'
PARSER FRegexParser(pattern='^(?<time>\d\d\d\d-\d\d-\d\d \d\d:\d\d:\d\d\.\d+)
 (?<thread_name>[A-Za-z ]+):(?<thread_id>(?:0x)?[0-9a-f]+)
-?(?<transaction_id>[0-9a-f])?(?:[(?<component>\w+)]
\<(?<level>\w+)\> )?(?:<(?<elevel>\w+)> @[?(?<enode>\w+)]?: )
?(?<text>.*)'


);

从外部表查询以获取更新后的结果：

=> SELECT component, thread_id, time FROM vertica_log limit 10;
 component | thread_id  |          time
-----------+------------+-------------------------
 Init      | 0x16321430 | 2014-04-02 04:02:02.613
 Init      | 0x16321430 | 2014-04-02 04:02:02.613
 Init      | 0x16321430 | 2014-04-02 04:02:02.613
 Init      | 0x16321430 | 2014-04-02 04:02:02.613
 Init      | 0x16321430 | 2014-04-02 04:02:02.613
 Init      | 0x16321430 | 2014-04-02 04:02:02.613
 Init      | 0x16321430 | 2014-04-02 04:02:02.613
           | 0x16321430 | 2014-04-02 04:02:02.614
           | 0x16321430 | 2014-04-02 04:02:02.614
           | 0x16321430 | 2014-04-02 04:02:02.614
(10 rows)

10 - 通用事件格式 (CEF) 数据

使用 Flex 解析器 fcefparser 将 Micro Focus ArcSight 或其他通用事件格式 (CEF) 日志文件数据加载到列式表和 Flex 表中。

当您使用该解析器加载任意 CEF 格式文件时，它会将数据中的键名解释为 Flex 表中的虚拟列。加载后，无论每行中存在何种键集，您均可直接查询 CEF 数据。您还可以使用关联 Flex 表数据和映射函数来管理对 CEF 数据的访问。

创建 Flex 表并加载 CEF 数据

此部分使用了一个示例 CEF 数据集。所有 IP 地址均已被有意更改为不准确的地址，并为了便于说明而添加了换行符。

要使用此示例数据，请复制下面的文本并删除所有换行符。将文件另存为 CEF_sample.cef，该名称将被用于所有这些示例。

CEF:0|ArcSight|ArcSight|6.0.3.6664.0|agent:030|Agent [test] type [testalertng] started|Low|
eventId=1 mrt=1396328238973 categorySignificance=/Normal categoryBehavior=/Execute/Start
categoryDeviceGroup=/Application catdt=Security Mangement categoryOutcome=/Success
categoryObject=/Host/Application/Service art=1396328241038 cat=/Agent/Started
deviceSeverity=Warning rt=1396328238937 fileType=Agent
cs2=<Resource ID\="3DxKlG0UBABCAA0cXXAZIwA\=\="/> c6a4=fe80:0:0:0:495d:cc3c:db1a:de71
cs2Label=Configuration Resource c6a4Label=Agent
IPv6 Address ahost=SKEELES10 agt=888.99.100.1 agentZoneURI=/All Zones/ArcSight
System/Private Address Space
Zones/RFC1918: 888.99.0.0-888.200.255.255 av=6.0.3.6664.0 atz=Australia/Sydney
aid=3DxKlG0UBABCAA0cXXAZIwA\=\= at=testalertng dvchost=SKEELES10 dvc=888.99.100.1
deviceZoneURI=/All Zones/ArcSight System/Private Address Space Zones/RFC1918:
888.99.0.0-888.200.255.255 dtz=Australia/Sydney _cefVer=0.1

创建 Flex 表 logs：

=> CREATE FLEX TABLE logs();
CREATE TABLE

使用 Flex 解析器 fcefparser 加载示例 CEF 文件：

=> COPY logs FROM '/home/dbadmin/data/CEF_sample.cef' PARSER fcefparser();
 Rows Loaded
-------------
           1
(1 row)

使用 maptostring() 函数查看 logs Flex 表的内容：

=> SELECT maptostring(__raw__) FROM logs;
                                  maptostring
-------------------------------------------------------------------------------------
  {
   "_cefver" : "0.1",
   "agentzoneuri" : "/All Zones/ArcSight System/Private Address
    Space Zones/RFC1918: 888.99.0.0-888.200.255.255",
   "agt" : "888.99.100.1",
   "ahost" : "SKEELES10",
   "aid" : "3DxKlG0UBABCAA0cXXAZIwA==",
   "art" : "1396328241038",
   "at" : "testalertng",
   "atz" : "Australia/Sydney",
   "av" : "6.0.3.6664.0",
   "c6a4" : "fe80:0:0:0:495d:cc3c:db1a:de71",
   "c6a4label" : "Agent IPv6 Address",
   "cat" : "/Agent/Started",
   "catdt" : "Security Mangement",
   "categorybehavior" : "/Execute/Start",
   "categorydevicegroup" : "/Application",
   "categoryobject" : "/Host/Application/Service",
   "categoryoutcome" : "/Success",
   "categorysignificance" : "/Normal",
   "cs2" : "<Resource ID=\"3DxKlG0UBABCAA0cXXAZIwA==\"/>",
   "cs2label" : "Configuration Resource",
   "deviceproduct" : "ArcSight",
   "deviceseverity" : "Warning",
   "devicevendor" : "ArcSight",
   "deviceversion" : "6.0.3.6664.0",
   "devicezoneuri" : "/All Zones/ArcSight System/Private Address Space
    Zones/RFC1918: 888.99.0.0-888.200.255.255",
   "dtz" : "Australia/Sydney",
   "dvc" : "888.99.100.1",
   "dvchost" : "SKEELES10",
   "eventid" : "1",
   "filetype" : "Agent",
   "mrt" : "1396328238973",
   "name" : "Agent [test] type [testalertng] started",
   "rt" : "1396328238937",
   "severity" : "Low",
   "signatureid" : "agent:030",
   "version" : "0"
}

(1 row)

创建列式表并加载 CEF 数据

此示例可让您针对 CEF 数据比较 Flex 表与列式表。要实现这一目标，您应该创建一个新表，并加载在前面 Flex 表示例中使用的同一个 CEF_sample.cef 文件。

创建一个列式表 col_logs，并定义在 fcefparser 中硬编码的前缀名：

=> CREATE TABLE col_logs(version INT,
  devicevendor VARCHAR,
  deviceproduct VARCHAR,
  deviceversion VARCHAR,
  signatureid VARCHAR,
  name VARCHAR,
  severity VARCHAR);
CREATE TABLE

将示例文件加载到 col_logs 中，就像您之前对 Flex 表所做的那样：

=> COPY col_logs FROM '/home/dbadmin/data/CEF_sample.cef' PARSER fcefparser();
 Rows Loaded
-------------
           1
(1 row)

查询该表。您可以在 Flex 表输出中找到完全相同的信息。

=> \x
Expanded display is on.
VMart=> SELECT * FROM col_logs;
-[ RECORD 1 ]-+----------------------------------------
version       | 0
devicevendor  | ArcSight
deviceproduct | ArcSight
deviceversion | 6.0.3.6664.0
signatureid   | agent:030
name          | Agent [test] type [testalertng] started
severity      | Low

计算键并生成 Flex 表视图

在此示例中，您使用 Flex 的 Helper 函数来为 logs Flex 表计算键并构建视图。

使用 compute_flextable_keys_and_build_view 函数计算键并填充从 logs Flex 表中生成的视图：

=> SELECT compute_flextable_keys_and_build_view('logs');
                            compute_flextable_keys_and_build_view
-------------------------------------------------------------------------------------
 Please see public.logs_keys for updated keys
The view public.logs_view is ready for querying
(1 row)

查询 logs_keys 表以了解函数从示例 CEF 数据中计算的内容：

=> SELECT * FROM logs_keys;
       key_name       | frequency | data_type_guess
----------------------+-----------+-----------------
 c6a4                 |         1 | varchar(60)
 c6a4label            |         1 | varchar(36)
 categoryobject       |         1 | varchar(50)
 categoryoutcome      |         1 | varchar(20)
 categorysignificance |         1 | varchar(20)
 cs2                  |         1 | varchar(84)
 cs2label             |         1 | varchar(44)
 deviceproduct        |         1 | varchar(20)
 deviceversion        |         1 | varchar(24)
 devicezoneuri        |         1 | varchar(180)
 dvchost              |         1 | varchar(20)
 version              |         1 | varchar(20)
 ahost                |         1 | varchar(20)
 art                  |         1 | varchar(26)
 at                   |         1 | varchar(22)
 cat                  |         1 | varchar(28)
 catdt                |         1 | varchar(36)
 devicevendor         |         1 | varchar(20)
 dtz                  |         1 | varchar(32)
 dvc                  |         1 | varchar(24)
 filetype             |         1 | varchar(20)
 mrt                  |         1 | varchar(26)
 _cefver              |         1 | varchar(20)
 agentzoneuri         |         1 | varchar(180)
 agt                  |         1 | varchar(24)
 aid                  |         1 | varchar(50)
 atz                  |         1 | varchar(32)
 av                   |         1 | varchar(24)
 categorybehavior     |         1 | varchar(28)
 categorydevicegroup  |         1 | varchar(24)
 deviceseverity       |         1 | varchar(20)
 eventid              |         1 | varchar(20)
 name                 |         1 | varchar(78)
 rt                   |         1 | varchar(26)
 severity             |         1 | varchar(20)
 signatureid          |         1 | varchar(20)
(36 rows)

查询来自 logs_view 的几列：

=> \x
Expanded display is on.
VMart=> select version, devicevendor, deviceversion, name, severity, signatureid
  from logs_view;
-[ RECORD 1 ]-+----------------------------------------
version       | 0
devicevendor  | ArcSight
deviceversion | 6.0.3.6664.0
name          | Agent [test] type [testalertng] started
severity      | Low
signatureid   | agent:030

使用 fcefparser 的 delimiter 参数

在此示例中，您使用 fcefparser delimiter 参数查询加利福尼亚州、新墨西哥州和亚利桑那州的事件。

创建一个新的列式表 CEFData3：

=> CREATE TABLE CEFData3(eventId INT, location VARCHAR(20));
CREATE TABLE

使用 delimiter=',' 参数，将一些 CEF 数据加载到表中：

=> COPY CEFData3 FROM stdin PARSER fcefparser(delimiter=',');
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> eventId=1,location=California
>> eventId=2,location=New Mexico
>> eventId=3,location=Arizona
>> \.

查询表：


=> SELECT eventId, location FROM CEFData3;
 eventId |  location
---------+------------
       1 | California
       2 | New Mexico
       3 | Arizona
(3 rows)