如何使用 ETL 管理身份？
我需要帮助确定工作流程，但不知道如何进行...假设我正在将（ETL？）数据从表 A 转换到表 B。表 A 有一个复合主键 A.a+ A.b+Ac，而表 B 只有一个自动填充的标识列。 如何将复合键从 A 映射回插入 B 时创建的身份？
最好我希望表 B 中没有任何与 A 的组合键相关的列，因为还有许多其他表需要进行相同的操作，但不具有相同的组合键结构。
I need help figuring out a workflow and I'm not sure how to go about it... Let's say I'm transforming (ETL?) data from Table A to Table B. Table A has a composite primary key A.a+A.b+A.c, while Table B has just an automatically populated identity column. How can I map the composite keys from A back to the identities created when inserting into B?
Preferably I would like to not have any columns in table B related to A's composite key because there are many other tables that need to undergo the same operation but don't have the same composite key structure.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
如果我理解正确的话，您无法在转换后将表 B 中的记录关联回表 A 的记录，除非您在转换期间以某种方式捕获 A 的组合键和 B 的标识符之间的映射。
您可以向 A 添加一列，并预先计算插入 B 时要使用的标识符。然后您将获得一个映射。 如果您不想向 A 添加列，也可以使用单独的映射表来完成此操作。
如果您不想覆盖标识符的默认分配，则必须在加载期间捕获它们。 为此，Oracle 在 PL/SQL 中为
insert提供了returning子句。 我不确定 SQL Server。 也可以通过在 B 上使用触发器插入到单独的映射表或更新 A 中的列来实现此目的。尽管这可能会大大减慢您的负载。如果没有别的事，您可以在 B 中创建额外的列来在加载期间保存 A 的键，然后将映射查询到单独的表中，然后删除额外的列。
我希望这有帮助。
If I understand you correctly, you can't relate records from table B back to the records of table A after the transformation unless you somehow capture a mapping between A's composite key and B's identifier during the transformation.
You could add a column to A and pre-compute the identifiers to be used when inserting into B. Then you would have a mapping. This could also be done using a separate mapping table, if you don't want to add a column to A.
If you don't want to override the default assignment of identifiers, then you will have to capture them during the load. Oracle provides the
returningclause forinsertin PL/SQL for this purpose. I'm not sure about SQL Server. It may also be possible to accomplish this by using a trigger on B to insert into a separate mapping table or update a column in A. Though that's likely to slow down your load considerably.If nothing else, you could create additional columns in B to hold the keys of A during the load, query out the mappings into a separate table afterwards, and then drop the extra columns.
I hope that helps.
问问自己到底需要原始钥匙做什么。 答案可能因源系统而异。 这可能会导致您维护“源系统”列和“原始源密钥”列。 后者可能需要是原始键的逗号分隔列表。
或者，您可能会发现您实际上从未需要映射回来，因此不需要保留任何内容。
Ask yourself exactly what you need the original keys for. The answer may vary depending on the source system. This may lead you to maintain a "source system" column and a "original source keys" column. The latter may need to be a comma-delimited list of the original keys.
Or, you may find that you never actually need to map back, so don't need to keep anything.