vertica 最佳实践之 Data Loading in Vertica Using COPY

使用COPY语句批量加载

COPY语句是将大量数据加载到Vertica数据库中的最有效方法。您可以使用COPY命令将一个或多个文件复制到集群主机上。对于批量加载，最有用的COPY命令是：

1. COPY LOCAL：将数据文件或所有指定的文件从本地客户端系统加载到Vertica主机，由服务器处理这些文件。
2. COPY with source data in a Vertica cluster：在Vertica集群中，从不同的数据源(如JSON和CSV)将数据文件或所有指定文件加载到Vertica内部格式。
3. COPY using User Defined Load (UDL) functions with custom sources, filters, or parsers：通过控制数据加载设置，从自定义用户定义的源、解析器和过滤器加载数据文件或指定文件。

所有类型的COPY语句共享相同的方法和过程，但都有不同的限制。不管有什么区别，COPY语句始终有两个阶段：

1. 第一阶段（initiator）加载并解析文件，然后将文件分发到其他节点。
2. 第二阶段（executor）处理所有节点上的数据。

使用COPY，您可以使用许多Execution Engine运算符进行批量加载。用于加载一个或多个文件的某些Execution Engine运算符包括Load，Parse，Load Union，Segment，Sort / Merge和DataTarget。

如果目标表已分段，则COPY语句为每个投影创建分段。分段定义了如何在集群节点之间分布数据以实现查询性能和快速清除数据。

COPY语句如何加载数据

用于加载一个或多个文件的COPY语句工作流分为两个阶段：

1. 第一阶段

   Load operator加载数据到数据库中的源文件。在Parse operator解析数据库加载的数据。 在对数据分段之前，Load Union 将解析的数据合并到一个容器中。加载多个文件时Load Union处于活动状态，而加载一个文件时Load Union处于非活动状态。
   根据数据的大小，Segment operator 将解析的数据分割为一个或多个投影。此外，表分区隔离每个节点上的数据，以便将数据均匀地分布在多个数据库节点上。这样做可确保所有节点都参与执行查询。

2. 第二阶段

   Sort operator 对分段的数据和投影进行排序。Merge operator 适当地合并排序好的数据。Sort operator 和Merge operator 可处理聚合数据。
   DataTarget 将数据复制到磁盘上。

下图显示了分为两个阶段加载一个或多个文件的工作。浅蓝色和深蓝色框表示Execution Engine operators。

在具有分摊负载的Vertica 8.0中，如果所有节点都可以访问源数据，则阶段I发生在多个节点上。分摊的负载是可分割的负载，因此您可以在多个节点上加载单个数据文件。如果源在多个节点上可用并且可以分割，则 built-in delimited parser可以自动分配负载，如果分配的负载不可用，则阶段I仅在读取文件的节点上发生。

第二阶段使用附加的Execution Engine 以及pre-join projections 投影和live aggregate projections投影。下图pre-join projections 显示了维度表的其他执行引擎运算符JOIN和SCAN。下图live aggregate projections实时聚合投影显示了附加的GROUP BY / Top-K执行引擎运算符。

pre-join projections为维度表添加了其他执行引擎运算符JOIN和SCAN。

实时聚合投影会添加GROUP BY / Top-K执行引擎运算符。

加载方式

根据要加载的数据，COPY语句有几种加载方法。您可以从三种加载方法中进行选择：

1. COPY AUTO
2. COPY DIRECT
3. COPY TRICKLE

何时使用COPY AUTO

COPY AUTO方法将数据加载到WOS中。对于小批量加载，请使用默认的COPY AUTO。当您无法确定文件大小时，“ AUTO”选项最有用。一旦WOS已满，COPY将继续直接加载到磁盘上的ROS容器。ROS数据已经过排序和编码。

何时使用COPY DIRECT

COPY DIRECT方法将数据直接加载到ROS容器中。对于大批量加载（100 MB或更多），请使用COPY DIRECT。COPY DIRECT 避免了WOS并将数据加载到ROS容器中，从而提高了大文件的性能。使用DIRECT加载许多较小的数据集会导致产生许多ROS容器，这些容器随后必须合并。

何时使用COPY TRICKLE

COPY TRICKLE方法将数据直接加载到WOS中。完成初始批量加载后，请使用TRICKLE加载方法来增量加载数据。如果WOS已满，则会发生错误，并且将回滚整个数据负载。只有在经过精细调优的加载和moveout 过程，并且您确信WOS能够保存您正在加载的数据时，才可以使用此方法。将数据加载到分区表中时，此选项比AUTO更有效。

监视数据负载的Vertica系统表

Vertica提供了系统表，可用于监视数据库负载：

LOAD_STREAMS：监视每个节点上的负载流的活动和历史负载度量，并提供有关已加载和已拒绝记录的统计信息。
DC_LOAD_EVENTS：存储有关负载分析期间重要系统事件的信息。

• Batchbegin
• Sourcebegin
• Parsebegin
• Parsedone
• Sourcedone
• Batchdone

调优数据负载

资源池参数和配置参数会影响数据加载的性能。

资源池参数

以下参数指定资源池的特征，这些特征可帮助DBA管理用于加载数据的资源。

参数	描述
PLANNEDCONCURRENCY	定义每个COPY命令分配的内存量。表示资源池中同时执行查询的首选数量。
MAXCONCURRENCY	限制并发COPY作业的数量，并表示资源池可用的并发执行插槽的最大数量。
EXECUTIONPARALLELISM	限制用于处理在此资源池中发出并分配给负载的任何单个查询的线程数。Vertica根据内核数，可用内存和系统中的数据量来设置此值。除非内存有限或数据量很小，否则Vertica将此值设置为节点上的核心数。

查询预算

RESOURCE_POOL_STATUS系统表中的query_budget_kb列显示在相关资源池上执行的查询的目标内存。

要检查查询 budget_kb，请使用以下命令：

SELECT pool_name, query_budget_kb FROM resource_pool_status;

修改之前query budget_kb，请注意以下内存注意事项：

1. If MEMORYSIZE > 0 and MAXMEMORYSIZE is empty or equal to MEMORYSIZE,
   the query budget = MEMORYSIZE / Planned Concurrency
2. If MEMORYSIZE = 0 and MAXMEMORYSIZE > 0, the query budget =
   (MAXMEMORYSIZE * 0.95) / Planned Concurrency
3. If MEMORYSIZE = 0 and MAXMEMORYSIZE is empty, the query budget =
   [(General Pool * 0.95) – (sum(MEMORYSIZE of other pools) ] / Planned
   Concurrency

如何更改资源池参数

要更改资源池参数，请使用以下命令：

=> ALTER RESOURCE POOL <池名称> <参数> <新值>;

数据加载配置参数

以下配置参数可以帮助您提高数据加载的性能。

参数	描述
EnableCooperativeParse	在节点上实现多线程协作解析功能。您可以将此参数用于定界和定宽加载。合作解析并行化对于源数据的节点而言是本地的。
SortWorkerThreads	控制排序工作线程的数量。设置为0时，它将禁用后台线程。当瓶颈处于负载的解析/排序阶段时，可以提高负载性能。
ReuseDataConnections	尝试在查询执行之间重用TCP连接。
DataBufferDepth	控制为数据连接分配的缓冲区。
CompressNetworkData	压缩数据流量并减少数据带宽。
EnableApportionLoad	为负载定义可分配的源/解析器，并将数据拆分为适当的部分。在Vertica 8.0中，分配的负载与FilePortionSource源功能一起使用。
MultiLevelNetworkRoutingFactor	定义大型群集的网络路由并调整计数减少因子。

对负载方案进行故障排除

如果在将数据加载到数据库时无法解决问题，请联系Vertica支持。

加载大文件

用例：大文件需要很长时间才能加载。

建议：通过以下两种方法之一使每个节点上的负载并行：

使用EnableApportionLoad参数可以使工作负载在集群中不同节点之间并行。对于分摊的加载，请在加载文件的节点之间共享文件，然后使用以下语句设置FilePortionSource Source UDx参数：

COPY copy_test.store_sales_fact with source FilePortionSource(file='/data/test_copy/source_data5/
Store_Sales_Fact.tbl',nodes='v_vdb_node0001,v_vdb_node0002,v_vdb_node0003')direct;

在NFS挂载点中拆分和暂存文件，以便所有节点都可以访问任何节点上的文件。

这两个选项具有相似的加载性能。但是，第二个选项要求您手动拆分文件。

将多个小文件加载到同一目标表

用例：使用COPY DIRECT加载多个小文件会降低性能。带有小文件的多个语句生成多个ROS容器。大量的ROS容器会影响Vertica的性能，并且在加载完成后需要对Tuple Mover进行额外的工作。

建议：考虑以下使用COPY语句加载多个小文件的选项：

1. 控制COPY语句的数量以合并加载文件。较少的COPY语句减少了事务数量，并在一个事务中加载了更多数据。
2. 使用Linux管道合并加载文件。
3. 在同一COPY语句中合并文件可提供更好的性能。

加载宽表

用例：具有大型VARCHAR列的宽表是COPY命令在第二阶段executor的瓶颈。

建议：考虑以下选项来加载宽表：

1. 对于特定的加载，更改LoadMergeChunkSizeK 参数
2. 对于宽表和多个小表使用flex表。将宽表加载到flex表中需要加载一个字段，而不是多个字段。因此，它减少了目录的大小并提高了整个数据库的性能。初始加载非常快，用户可以快速获得数据。但是，与列存储相比，查询性能更低。
3. 使用GROUPED相关列加载宽表。GROUPED 字句 将两个或多个列分组到单个磁盘文件中。如果一列的值与另一列的值相关，则两列是相关的。

用于加载的executor节点

用例：executor节点仅用于计算，不包含任何数据段。专用节点使用全部CPU。与该问题有关的两个用例：

• 大文件必须在24小时内加载完。
• 解析操作将阻塞工作流程。

在这种用例中，通常存在以下条件：

1. 宽表每行具有数千字节。
2. GZIP文件被压缩以减少网络传输时间。将文件放置在Vertica本地存储中后，COPY命令将解压缩数据，从而增加CPU使用率。
3. 具有宽segmentation keys的表使用更多的CPU。

建议：在咨询Vertica支持后使用executor节点。资源池配置影响在executor节点和数据节点上正确使用资源。由于资源池参数适用于整个集群，Vertica 为模型提供了参数配置。使用CPU更多的服务器，并将单独的CPU分配给加载资源池，从而限制专用于加载操作的资源。不要使用executor节点来加载。