平面文件到 SQL 服务器(批量插入)ODI 11g 知识模块仅从平面文件加载一半的记录

Question

我正在使用Oracle Data Integrator 11g接口将包含超过一百万条记录的平面文件连接到SQL Server表。鉴于使用常规知识模块时存在一些性能问题 - 我正在利用平面文件到 SQL Server(批量插入)LKM 的使用，这大大提高了接口性能，因为 ODI 代理使用 SQL Server 上的本机 BCP 实用工具批量加载记录。

知识模块为 ODI 代理创建一个非常基本的大容量插入查询，以便执行 BCP 实用工具。我拥有的平面文件来自Oracle PBCS - 我们使用ODI 11g通过接口进行映射。由于平面文件的格式，我们在 ODI 平面文件模型中使用 |(pipe) 和字段分隔符 " ，以确保包含多个被 " 包围的管道的字段成为一个数据元素，而不是被分隔符分解为子组件。

例如，一条记录看起来像

FY19|ContractX|FundX|"BOCX|ProgramX|VersionX|ScenarioX"

这将映射到 SQL Server 表中(用空格分隔的列)。

FY19 ContractX FundX BOCX|ProgramX|VersionX|ScenarioX 

而不是

FY 19 ContractX FundX BOCX ProgramX VersionX ScenarioX

因此，在适当的情况下，由于" 字段分隔符.

由管道分隔的没有 " 的所有其他字段都被视为分隔元素。在一般情况下(平面文件到SQL Server的情况下不使用SQL Serverbcp实用程序，而是使用ODI的本机接口技术)，这就像一个魅力，以确保如上所述发生这种情况。

但是，当我们执行接口时，批量插入会做两件奇怪的事情。

1. BCP实用程序仅加载平面文件的一半记录。平面文件编码为 UTF-8，并以竖线分隔。我已经阅读了一些关于此的内容(所有列和列值都匹配，因此文件本身很好) - 不确定是否有快速简单的脚本可以让我格式化文件的方式 - 格式化后 - SQL Server 批量插入将正确加载。成功加载到 SQL Server 表中的记录不按顺序和随机(甚至远程不基于平面文件的顺序)也无济于事。

   这显然是最大的问题——需要先解决这个问题。

2. 第二个问题是，通过 ODI LKM 的 BCP 实用程序似乎"非常强大"，因为它忽略了我在 ODI 平面文件模型中定义的字段分隔符 "，并解析出包含多个管道的字段都在 " 中。在不使用 BCP 的普通知识模块中，由"包围并包含多个管道的字段不会分隔为单独的组件。我注意到，即使 ODI 平面文件模型指定了字段分隔符" – 创建的批量插入 sql 查询也不会在 SQL 代码中包含此分隔符。几乎就像批量插入忽略了这个字段分隔符(它似乎识别了 | 字段分隔符)

我的印象是我将不得不编写自己的自定义批量插入 sql 语句——我真的完全没问题——以确保上述两个问题都得到纠正。我正在寻找是否有人对可以解决这两个问题的批量插入查询有任何见解。

我试图更改平面文件上的分隔符和编码 - 无济于事。有一些文献可以格式化文件 - 但我需要 ODI 分步执行，因为即使我们不使用 LKM，这仍然是自动化的。

我的想法是我需要

1. 我将插入到 ODI 中以执行的自定义批量插入脚本或

2. 我需要创建一个自定义知识模块。

我更喜欢做 1.但需要构建脚本。

这是 ODI 通过对应于平面文件到 SQL Server(批量)LKM 的声明性设计执行的内容。

BULK INSERT BFS_DM.ODI_Work.TC$_0Cld_Ess_SpendPln_Final     
FROM 'E:EPM_Cloud/Exports/BUD_PLN/Data/Export_SpendPlan_BI_BUD_PLN.txt'     
WITH
(
CODEPAGE  = 'ACP' 
, DATAFILETYPE  = 'char '
, FIELDTERMINATOR  ='|' 
, FIRSTROW =2
, TABLOCK 
)

正如您在上面的代码中看到的那样，该代码由接口生成 - 管道分隔符在那里，但它似乎没有选择" 字段分隔符作为要考虑的因素。因此，最终加载到表中的记录成功是每个字段加载到由 | 分隔的列中。

此外，此代码仅加载一半的记录 - 想知道我如何在上面的批量插入中使用一些额外的代码行来解决这个问题。

第一行 = 2，因为文件中的标头。

Answer 1

您需要将格式化文件与批量插入语句一起使用。这是必需的，因为 SQL BCP 不允许考虑数据中包含的引号来隐藏/屏蔽数据中显示的给定字段终止符。此外，您将需要自定义(单个)字段终止符(并非所有字段都将以相同的值终止...就像您当前使用的"|"一样)。

使用 BCP 格式文件，可以为传入文件指定自定义字段终止符。每个字段都在格式化文件中被调用，因此每个字段都定义了自己的终止符。例如，您的第三个字段将以"|\"而不是"|"结尾("\"将转义您在数据中包含的双引号，因此不会将其视为真正的 dbl-quote 以在格式化文件中包含字段终止符)。

同时，您的第 4 列将不再以"|"结尾，而是以"\"\r"终止(这假设您使用的是标准 crlf 终止符)。同样，这里的第一个和最后一个双引号是 bcp 将从格式化文件中读取的实际 dbl 引号，其中包含分隔符(现在是"行"分隔符，因为我们在最后一个字段)。您必须使用"\"字符转义内部的 dbl 引号。\r 是 CRLF 的标准 bcp representatino。