SSIS—插入大量行(数亿行)的最佳方式

本文关键字：亿行 最佳 方式 插入 SSIS | 更新日期: 2023-09-27 18:04:41

场景如下:我有一个XML文件，大小为500GB，数据大约有6亿行(一次在一个数据库表上)。我正在使用SSIS进行操作，因为它消耗了大量的内存，如果我要使用SSIS组件(即:XML Source)，它可能会导致超时(如果我错了请纠正我，但据我所知，使用SSIS上的组件将XML的内容加载到内存中-具有那么大的文件，它肯定会导致错误)。我的方法是:

  • 使用一个Script Task使用XML Reader来解析XML数据(XML Reader是目前最好的方法，因为它以一种向前的、非缓存的方式解析XML)
  • 插入DataTable
  • 在DataTable上每500,000行，使用SqlBulkCopy将内容插入数据库，然后清除DataTable
    • 的内容。

我的问题是，目前，我试着解析另一个文件的大小为200GB，它运行在大约13.5米/1小时-我不知道它是否仍然与运行时间。这确实解决了我的问题——但它不太优雅，我的意思是，应该有其他方法。

我正在寻找其他方法，比如:

  • 将大XML文件划分为小块csv(约20GB)，然后使用SSIS Data Flow task
  • 每新增一行使用INSERT脚本
你能帮我决定哪个是最好的吗?或提出其他解决方案。

每一个答案都将非常感谢。

编辑

我忘了说——我的方法是动态的。我的意思是，有许多表将被大型XML文件填充。因此，使用Script Component作为源可能不是很有用，因为我仍然需要定义输出列。但我还是要试一试。

编辑2015-07-28

文件来自我们的客户，我们不能对他们想要发送给我们的来源做任何事情。XML，就是这样。下面是我正在使用的XML的一个示例:

<?xml version="1.0" encoding="UTF-8"?>
<MFADISDCP>
  <ROW>
    <INVESTMENT_CODE>DATA</INVESTMENT_CODE>
    <DATE_OF_RECORD>DATA</DATE_OF_RECORD>
    <CAPITAL_GAIN_DISTR_RATE>DATA</CAPITAL_GAIN_DISTR_RATE>
    <INCOME_DISTR_RATE>DATA</INCOME_DISTR_RATE>
    <DISTR_PAYMENT_DATE>DATA</DISTR_PAYMENT_DATE>
    <CURRENCY>DATA</CURRENCY>
    <CONFIRM>DATA</CONFIRM>
    <EXPECTED_DISTRIBUTION_AMOUNT>DATA</EXPECTED_DISTRIBUTION_AMOUNT>
    <KEYING_STATUS>DATA</KEYING_STATUS>
    <DAF_RATE>DATA</DAF_RATE>
    <INCOME_START_DATE>DATA</INCOME_START_DATE>
    <ALLOCABLE_END_DATE>DATA</ALLOCABLE_END_DATE>
    <TRADE_DATE>DATA</TRADE_DATE>
    <OVR_CAPITAL_GAIN_DISTR_OPTION>DATA</OVR_CAPITAL_GAIN_DISTR_OPTION>
    <OVR_INCOME_DISTR_OPTION>DATA</OVR_INCOME_DISTR_OPTION>
    <BACKDATED_DISTRIBUTION>DATA</BACKDATED_DISTRIBUTION>
    <DATE_MODIFIED>DATA</DATE_MODIFIED>
  </ROW>
<!--AROUND 49M+ OF THIS ROWS-->
</MFADISDCP>

SSIS—插入大量行(数亿行)的最佳方式

如果我要这样做，那么我会将其分解为以下任务:

  1. 将XML文件转换为(制表符或逗号)分隔的文件。如果您的服务器有快速磁盘(SSD)，那么这应该非常快。注意数据中的字符串可能包含可能破坏分隔符格式的特殊字符。不要使用DataTable对象，因为它很慢。你可以流式传输，这样你就不需要一次把整个文件放在内存中(除非你的服务器有几百gb的内存)
  2. 截断数据库中用于加载数据的阶段表。
  3. 使用SQL Server的bcp.exe将带分隔符的文件推入数据库的阶段表。这可能是将大量数据放入数据库的最快方法。这样做的一个问题是，如果失败了，那么很难找到导致失败的数据行。
  4. 删除带分隔符的文件，因为你不需要它们躺在周围占用大量空间。
  5. 创建一个SQL存储过程，将数据从阶段表移动到使用它的任何地方。

您可以使用SSIS脚本任务，或者您可以编写自己的独立服务。

注意，这些都是理论上的，可能有更好的方法，但这可能是一个很好的起点，可以找出你的瓶颈在哪里。

相关文章：