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

使用外部数据

1: 外部表与原生表有何差异
2: 创建外部表
3: 分区文件路径
4: 查询外部表
5: 监控外部表
6: 外部表故障排除

将数据导入 Vertica 的另一种方法是就地查询。在某些情况下，查询外部数据而不是导入它可能是有利的：

在选择要加载到 Vertica 中的数据之前，如果想浏览诸如数据湖中的数据。
如果您是共享诸如数据湖中的相同数据的多个使用者之一，则就地读取数据可以消除对查询结果是否是最新的顾虑。副本只有一份，因此所有使用者看到的数据都相同。
如果您的数据变化迅速，但您不想将其流式传输到 Vertica，则可以改为自动查询最新更新。
如果您有大量数据并且不想增加许可证容量。
如果 Vertica 中拥有您仍希望能够查询的低优先级数据。

要查询外部数据，必须将您的数据描述为外部表。和原生表一样，外部表也有表定义，可以被查询。与原生表不同，外部表没有编录，Vertica 根据需要从外部源加载选定数据。对于某些格式，查询计划器可以利用数据中的分区和排序，因此查询外部表并不意味着您在查询时加载所有数据。（有关原生表的详细信息，请参阅使用原生表。）

本节未涉及一种特殊类型的外部数据。如果您正在从 Hadoop 中读取数据，特别是从 Hive 数据仓库中读取，则可以从 Hive 中读取架构信息，而不是定义您自己的外部表。有关详细信息，请参阅使用 HCatalog 连接器。

1 - 外部表与原生表有何差异

您可以像使用 Vertica 原生表一样使用外部表。但是，由于数据在数据库外部，因此外部表的操作方式存在一些差异。

数据

外部表的数据可以驻留在任何地方，前提是所有数据库节点都可以访问它。S3、HDFS 和 NFS 挂载点是查找外部数据的常见位置。与查询本地存储的 ROS 数据相比，查询外部数据自然会产生一些延迟，但 Vertica 的优化可以减少影响。例如，Vertica 可以利用 HDFS 数据的节点和机架位置。

由于数据是外部数据，因此 Vertica 会在您每次查询时加载外部数据。Vertica 经过优化以减少读取数据量，包括支持分区的格式的谓词下推和分区修剪。ORC 和 Parquet 格式支持这些优化。

由于数据是在查询时读取的，因此您必须确保您的用户拥有并保留在其原始位置读取数据的权限。根据数据的存储位置，您可能需要采取额外的步骤来管理访问权限，例如在 S3 上创建 AWS IAM 角色。

由于数据未存储在 Vertica 中，因此外部表不使用超投影和伙伴实例投影。

资源使用

外部表对 Vertica 编录的添加很少，从而减少了查询使用的资源。由于数据不存储在 Vertica 中，因此外部表不受 Tuple Mover 的影响，也不会导致 ROS 回推。在读取外部表数据时，Vertica 将使用少量内存，因为表内容不是数据库的一部分，每次使用外部表时都会进行解析。

备份和还原

由于外部表中的数据在 Vertica 以外进行管理，因此数据库备份中仅包含外部表定义（而非数据文件）。为外部表数据安排单独的备份过程。

DML 支持

外部表允许您读取外部数据。不允许您进行修改。因此，某些 DML 操作不适用于外部表，包括：

DELETE FROM
INSERT INTO
SELECT...FOR UPDATE

序列和标识列

外部表的 COPY 语句定义可以包含标识列和序列。每当 select 语句查询外部表时，都会重新评估序列和标识列。这会导致外部表列的值发生变化，即使基础外部表数据保持不变。

2 - 创建外部表

要创建外部表，请使用 CREATE EXTERNAL TABLE AS COPY 语句将表定义与复制语句结合起来。CREATE EXTERNAL TABLE AS COPY 使用来自 CREATE TABLE 和 COPY 的参数子集。

您可以使用 CREATE TABLE 像定义 Vertica 原生表一样定义表列。您还可以指定 COPY FROM 子句以描述如何读取数据，就像加载数据一样。如何指定 FROM 路径取决于文件所在的位置和数据格式。请参阅指定加载数据的位置和数据格式。

与原生表一样，您可以使用 INFER_TABLE_DDL 函数从支持格式的数据文件中派生列定义。

以下示例为存储在 HDFS 中的分隔数据定义了一个外部表：

=> CREATE EXTERNAL TABLE sales (itemID INT, date DATE, price FLOAT)
    AS COPY FROM 'hdfs:///data/ext1.csv' DELIMITER ',';

以下示例使用存储在 S3 中的 ORC 格式的数据。数据有两个分区列。有关分区的详细信息，请参阅分区文件路径。

=> CREATE EXTERNAL TABLE records (id int, name varchar(50), created date, region varchar(50))
   AS COPY FROM 's3://datalake/sales/*/*/*'
   PARTITION COLUMNS created, region;

下面的示例演示了，如何才能从本地目录（没有分区和 glob）中的所有 Parquet 文件读取数据：

=> CREATE EXTERNAL TABLE sales (itemID INT, date DATE, price FLOAT)
    AS COPY FROM '/data/sales/*.parquet' PARQUET;

创建外部表时，数据不会添加到数据库中，也不会创建投影。相反，Vertica 会对 CREATE EXTERNAL TABLE AS COPY 语句执行语法检查并在编录中存储表名称和 COPY 语句定义。每次 SELECT 查询引用外部表时，Vertica 将解析并执行存储的 COPY 语句以获取引用的数据。只有通过查询表才能发现表定义中的任何问题（例如不正确的列类型）。

若要从外部表成功返回数据，COPY 定义必须正确，其他依赖项（如文件、节点和其他资源）必须可供访问且在查询时可用。如果表定义使用 glob（通配符）并添加或删除文件，则外部表中的数据可以在查询之间更改。

ORC 和 Parquet 格式

为 ORC 或 Parquet 数据定义外部表时，您必须定义文件中的所有数据列。如果省略数据列，则使用该表的查询会因错误而中止。

如果您在同一 COPY 语句中从多个 ORC 或 Parquet 文件加载，并且其中任何一个被中止，则整个加载中止。这种行为与分隔文件的行为不同，分隔文件中的 COPY 语句会加载其可以加载的数据并忽略剩余数据。

外部表的特殊注意事项

如果列的最大长度小于实际数据（如过短的 VARCHAR），Vertica 会将数据截断并记录该事件。

如果列上的约束导致值被拒绝，您可能会看到意外的查询结果：

如果您指定 NOT NULL 列约束并且数据包含 null 值，则这些行将被拒绝。
如果您使用 ENFORCELENGTH，太长的值将被拒绝而不是被截断。
读取 ORC 数据时，如果声明了标量精度并且某些数据不适合，则该行将被拒绝。例如，如果将列指定为 Decimal(6,5)，则拒绝值 123.456。

了解列约束是否导致数据被拒绝的一种方法是，列上的 COUNT 返回的值是否与 COUNT(*) 不同。

使用 COPY 参数 ON ANY NODE 时，请确认源文件定义在所有节点上均相同。指定不同的外部文件会导致生成的结果不一致。

可以利用分区来限制 Vertica 读取的数据量。有关使用分区数据的详细信息，请参阅分区文件路径。

取消 CREATE EXTERNAL TABLE AS COPY 语句会导致不可预测的结果。如果您在开始操作后发现表定义不正确（例如，无意中指定了错误的外部位置），请等待查询完成。当外部表存在时，使用 DROP TABLE 移除其定义。

提示

使用新的外部数据源时，请考虑将 REJECTMAX 设置为 1 以使数据中的问题明显。以这种方式进行测试可以让您在针对数据运行生产查询之前发现数据中的问题。

创建外部表后，分析其行数以提高查询性能。请参阅提高外部表的查询性能。

必需的权限

除了在 Vertica 中拥有权限外，用户还必须拥有对外部数据的读取访问权限。

对于本地磁盘上的数据，此访问权限由本地文件权限控制。
对于 HDFS 中的数据，访问权限可能受 Kerberos 身份验证控制。请参阅访问已进行 Kerberize 的 HDFS 数据。
对于 S3 中的数据，您需要通过 AWS IAM 角色进行访问。请参阅S3 对象存储。

对于 GCS 中的数据，您必须在读取数据之前启用 S3 兼容性。请参阅Google Cloud Storage (GCS) 对象存储。

默认情况下，您还必须是数据库超级用户才能通过 SELECT 语句访问外部表。

在大多数情况下，要允许没有超级用户访问权限的用户查询外部表，则管理员必须创建一个 USER 存储位置，并为这些用户授予对该位置的读取访问权限。请参阅 CREATE LOCATION 和 GRANT（存储位置）。该位置必须是在创建外部表时 COPY 语句中使用的路径的父项。此要求不适用于 HDFS 中存储的外部表。以下示例显示了为一个名为 Bob 的用户授予对数据位于 /tmp（包含任何深度的子目录）之下的所有外部表的访问权限：

=> CREATE LOCATION '/tmp' ALL NODES USAGE 'user';
=> GRANT ALL ON LOCATION '/tmp' to Bob;

组织外部表数据

如果存储在外部表中的数据定期更改（例如每月存储最近的历史数据），您可以结合使用分区和通配符 (globs)，更加动态地解析存储的 COPY 语句定义。例如，如果将每月数据存储在 NFS 挂载上，您可以在日历年的顶层目录中按月组织文件，例如：

/year=2018/month=01/

然后，您可以从 COPY 语句的目录名称中读取年份和月份值：

=> CREATE EXTERNAL TABLE archive (...) AS COPY FROM '/nfs_name/*/*/*' PARTITION COLUMNS year, month;

每当 Vertica 查询引用外部表 archive 并且 Vertica 解析 COPY 语句时，查询都可以访问顶级 monthly 目录中存储的所有数据。如果查询按年份或月份筛选（例如在 WHERE 子句中），则 Vertica 在评估 glob 时会跳过不相关的目录。有关详细信息，请参阅分区文件路径。

验证表定义

创建外部表时，Vertica 会验证 CREATE EXTERNAL TABLE AS COPY FROM 语句的语法。例如，如果在语句中忽略必需的关键字，则创建外部表会失败：

=> CREATE EXTERNAL TABLE ext (ts timestamp, d varchar)
    AS COPY '/home/dbadmin/designer.log';
ERROR 2778:  COPY requires a data source; either a FROM clause or a WITH SOURCE for a user-defined source

系统不会检查 COPY 定义的其他组件（例如路径语句和节点可用性），直到 SELECT 查询引用外部表。

若要验证外部表定义，请运行引用外部表的 SELECT 查询。检查返回的查询数据是否符合预期。如果查询没有正确地返回数据，请检查 COPY 异常以及拒绝数据日志文件。

由于 COPY 定义确定了查询外部表时发生的内容，因此 COPY 语句错误可以揭示潜在问题。有关 COPY 异常和拒绝的详细信息，请参阅处理杂乱的数据。

查看外部表定义

创建外部表时，Vertica 将在 TABLES 系统表的 table_definition 列中存储 COPY 定义语句。

要列出所有表，请使用 SELECT * 查询，如下所示：

=> SELECT * FROM TABLES WHERE table_definition <> '';

使用如下所示的查询列出外部表定义：

=> SELECT table_name, table_definition FROM TABLES;
 table_name |                                table_definition
------------+----------------------------------------------------------------------
 t1         | COPY            FROM 'TMPDIR/external_table.dat'  DELIMITER ','
 t1_copy    | COPY            FROM 'TMPDIR/external_table.dat'  DELIMITER ','
 t2         | COPY FROM 'TMPDIR/external_table2.dat' DELIMITER ','
(3 rows)

3 - 分区文件路径

数据文件有时在文件系统中使用目录结构进行分区。分区将值从原始数据中移出（在原始数据中，它们必须包含在每一行中），然后移入目录结构，从而节省磁盘空间。分区还可以通过允许跳过整个目录来提高查询性能。

一种常见使用情况是按日期分区：

/data/created=2016-11-01/*
/data/created=2016-11-02/*
/data/created=2016-11-03/*
/data/...

glob 中的文件不包含 created 列，因为此信息是通过文件系统表示的。Vertica 可以将分区值（在本例中为日期）读入表列（在本例中为 created）。

数据可以按多个值分区：

/data/created=2016-11-01/region=northeast/*
/data/created=2016-11-01/region=central/*
/data/created=2016-11-01/region=southeast/*
/data/created=2016-11-01/...
/data/created=2016-11-02/region=northeast/*
/data/created=2016-11-02/region=central/*
/data/created=2016-11-02/region=southeast/*
/data/created=2016-11-02/...
/data/created=2016-11-03/...
/data/...

如果数据按多个值分区，则分区必须以与所有目录路径中相同的顺序显示在 glob 中。在此示例中，创建日期在区域之前，并且必须始终如此。

COPY 语法

要从分区文件中读取值，请使用 COPY PARTITION COLUMNS 选项：

=> CREATE EXTERNAL TABLE records (id int, name varchar(50), created date, region varchar(50))
   AS COPY FROM 'webhdfs:////data/*/*/*'
   PARTITION COLUMNS created, region;

该路径针对每一级目录分区包含一个通配符 ()，针对文件再包含一个通配符 ()。通配符的数量必须至少比分区列的数量多 1。数据文件必须至少包含一个实际列；您不能完全通过目录结构来表示数据。

每个分区目录名的第一部分必须与表定义中的列名相匹配。COPY 解析 = 之后的字符串以获取值。空值（例如名为 created= 的目录）被视为 null 值。为了向后兼容，值 __HIVE_DEFAULT_PARTITION__ 也意味着 null。

无法强制转换为正确类型的值将被拒绝，拒绝方式与拒绝数据中的非强制值相同。

查询执行

使用谓词执行查询时，Vertica 会跳过不满足谓词的子目录。此过程称为分区修剪，它可以显着提高查询性能。以下示例仅读取指定区域的所有日期的分区。虽然数据也按日期分区，但查询不限制日期。

=> SELECT * FROM t WHERE region='northeast';

要验证 Vertica 是否正在修剪分区，请在说明计划中查找类似于以下内容的消息：

files with unmatched partition have been pruned

创建分区结构

要创建分区文件结构，可以使用 Hive 或文件导出器。有关使用 Hive 的信息，请参阅适用于 Vertica 集成的 Hive 入门知识。

您可以为任何简单数据类型的列创建分区。但是，作为最佳实践，您应当避免使用 BOOLEAN、FLOAT 和 NUMERIC 类型对列进行分区。

4 - 查询外部表

创建外部表后，可以像查询任何其他表一样查询它。假设您创建了以下外部表：

=> CREATE EXTERNAL TABLE catalog (id INT, description VARCHAR, category VARCHAR)
    AS COPY FROM 'hdfs:///dat/catalog.csv' DELIMITER ',';
CREATE TABLE
=> CREATE EXTERNAL TABLE inventory(storeID INT, prodID INT, quantity INT)
    AS COPY FROM 'hdfs:///dat/inventory.csv' DELIMITER ',';
CREATE TABLE

您现在可以针对这些表编写查询，如下所示：

=> SELECT * FROM catalog;
 id |     description      |  category
----+----------------------+-------------
 10 | 24in monitor         | computers
 11 | 27in monitor         | computers
 12 | 24in IPS monitor     | computers
 20 | 1TB USB drive        | computers
 21 | 2TB USB drive        | computers
 22 | 32GB USB thumb drive | computers
 30 | 40in LED TV          | electronics
 31 | 50in LED TV          | electronics
 32 | 60in plasma TV       | electronics
(9 rows)

=> SELECT * FROM inventory;
 storeID | prodID | quantity
---------+--------+----------
     502 |     10 |       17
     502 |     11 |        2
     517 |     10 |        1
     517 |     12 |        2
     517 |     12 |        4
     542 |     10 |        3
     542 |     11 |       11
     542 |     12 |        1
(8 rows)

=> SELECT inventory.storeID,catalog.description,inventory.quantity
    FROM inventory JOIN catalog ON inventory.prodID = catalog.id;
 storeID |   description    | quantity
---------+------------------+----------
     502 | 24in monitor     |       17
     517 | 24in monitor     |        1
     542 | 24in monitor     |        3
     502 | 27in monitor     |        2
     542 | 27in monitor     |       11
     517 | 24in IPS monitor |        2
     517 | 24in IPS monitor |        4
     542 | 24in IPS monitor |        1
(8 rows)

外部表和 Vertica 原生表之间的一个重要区别是，查询外部表每次都会读取外部数据。（请参阅外部表与原生表有何差异。）具体来说，选择查询每次引用外部表时，Vertica 会重新解析 COPY 语句定义以访问数据。在运行查询之前，您的表定义或数据中的某些错误不会很明显，因此请在将外部表部署到生产环境之前测试外部表。

处理错误

使用不正确的 COPY FROM 语句定义查询外部表数据可能会导致多个拒绝行。为了限制拒绝数量，Vertica 使用 ExternalTablesExceptionsLimit 配置参数设置保留的最大拒绝数。默认值为 100。将 ExternalTablesExceptionsLimit 设置为 –1 可移除此限制，但不建议这样做。

如果 COPY 错误达到最大拒绝数，外部表查询会继续，但 COPY 会在 vertica.log 文件中生成一条警告，且不会报告后续拒绝行。

设置拒绝阈值下限时，使用 ExternalTablesExceptionsLimit 配置参数不同于使用 COPY 语句 REJECTMAX 参数。REJECTMAX 值控制在导致加载失败前所允许的拒绝行数量。如果 COPY 遇到等于或大于 REJECTMAX 的拒绝行数，则 COPY 中止执行，而不是在 vertica.log 中记录警告。

提高外部表的查询性能。

如果较小的表是内部表，则包含联接的查询会执行得更好。对于原生表，查询优化器使用基数来选择内部表。对于外部表，查询优化器使用行计数（如果可用）。

创建外部表后，使用 ANALYZE_EXTERNAL_ROW_COUNT 收集此信息。调用此函数可能成本很高，因为它必须实体化表的一列才能计算行数，因此请在数据库不忙于关键查询时进行此分析。（这就是 Vertica 在您创建表时不会自动执行此操作的原因。）

查询优化器在计划查询时使用您最近调用此函数的结果。因此，如果数据量发生显著变化，您应当再次运行它以提供更新的统计信息。几个百分点的差异无关紧要，但如果您的数据量增长了 20% 或更多，则应当在可能的情况下重复此操作。

如果您的数据已分区，Vertica 会自动修剪不会影响查询结果的分区，从而减少加载的数据。

对于使用 Hive 0.14 及更高版本写入的 ORC 和 Parquet 数据，Vertica 自动使用谓词下推以进一步提高查询性能。谓词下推可将部分查询移动到更接近数据的位置执行，从而减少了必须从磁盘或通过网络读取的数据量。使用早期版本的 Hive 写入的 ORC 文件可能不包含执行此优化所需的统计信息。对缺少上述统计信息的文件执行查询时，Vertica 会在 QUERY_EVENTS 系统表中记录一个 EXTERNAL_PREDICATE_PUSHDOWN_NOT_SUPPORTED 事件。如果您发现查询出现性能方面的问题，请检查此表中是否有这些事件。

将外部表与用户定义的加载 (UDL) 函数结合使用

您可以将外部表与自己创建的 UDL 函数结合使用。有关使用 UDL 的详细信息，请参阅用户定义的加载 (UDL)。

5 - 监控外部表

Vertica 在系统表中记录有关外部表的信息。您可以使用这些表来跟踪外部数据和针对它的查询。

TABLES 系统表包含有关所有表（包括本地表和外部表）的数据。TABLE_DEFINITION 列特定于外部表。您可以查询此列以查看当前正在使用的所有外部数据源，如下例所示：

=> SELECT table_name, create_time, table_definition FROM tables WHERE table_definition != '';
table_name   |          create_time          |                     table_definition
---------------+-------------------------------+-----------------------------------------------------------
customers_orc | 2018-03-21 11:07:30.159442-04 | COPY from '/home/dbadmin/sample_orc_files/0*' ORC
miscprod      | 2018-06-26 17:40:04.012121-04 | copy from '/home/dbadmin/data/prod.csv'
students      | 2018-06-26 17:46:50.695024-04 | copy from '/home/dbadmin/students.csv'
numbers       | 2018-06-26 17:53:52.407441-04 | copy from '/home/dbadmin/tt.dat'
catalog       | 2018-06-26 18:12:28.598519-04 | copy from '/home/dbadmin/data/prod.csv' delimiter ','
inventory     | 2018-06-26 18:13:06.951802-04 | copy from '/home/dbadmin/data/stores.csv' delimiter ','
test          | 2018-06-27 16:31:39.170866-04 | copy from '/home/dbadmin/data/stores.csv' delimiter ','
                (7 rows)

EXTERNAL_TABLE_DETAILS 表提供了更多详细信息，包括文件大小。Vertica 在查询时计算此表中的值，这可能成本很高，因此请考虑按架构或表限制查询。

=> SELECT table_name, source_format, total_file_size_bytes FROM external_table_details;
table_name   | source_format | total_file_size_bytes
---------------+---------------+-----------------------
customers_orc | ORC           |             619080883
miscprod      | DELIMITED     |                   254
students      | DELIMITED     |                   763
numbers       | DELIMITED     |                    30
catalog       | DELIMITED     |                   254
inventory     | DELIMITED     |                    74
test          | DELIMITED     |                    74
(7 rows)

如果外部表的大小随时间发生显著变化，则应重新运行 ANALYZE_EXTERNAL_ROW_COUNT() 以收集更新的统计信息。请参阅提高外部表的查询性能。

LOAD_SOURCES 表显示当前正在进行的加载的信息。此表不记录有关加载 ORC 或 Parquet 数据的信息。

6 - 外部表故障排除

在创建或查询外部表时，可能会遇到以下问题。有关一般数据加载故障排除，请参阅对数据加载进行故障排除。

找不到文件或权限被拒绝

如果针对外部表的查询出现文件或权限错误，请确保执行查询的用户在 Vertica 和文件系统中都具有必要的权限。请参阅创建外部表中的权限部分。

错误 7226：找不到分区列

查询分区数据支持的外部表时，您可能会看到一条错误消息，指出缺少分区列：

ERROR 7226: Cannot find partition column [region] in parquet source
[/data/table_int/int_original/000000_0]

如果您创建外部表，然后更改分区结构（例如通过重命名列进行更改），则必须重新创建外部表。如果您看到此错误，请更新您的表以匹配磁盘上的分区。

有关分区结构的详细信息，请参阅分区文件路径。

错误 6766：是一个目录

查询数据时，您可能会看到一条错误消息，指出输入文件是一个目录：

ERROR 6766: Error reading from orc parser input stream
[/tmp/orc_glob/more_nations]: Is a directory

如果表的 COPY FROM 子句中的 glob 与空目录匹配，则会发生此错误。此错误仅针对 Linux 文件系统中的文件发生；HDFS 中的空目录将被忽略。

要更正错误，请使 glob 更加具体。例如，使用 *.orc 代替 *。