在应用程序之间进行数据转换的最佳实践是什么 [英] What is the best practice for data conversion between applications

问题描述

我想知道这是否对于stackoverflow来说是一个过于主观的问题，但无论如何都不能解决。

I wonder if this might be a too subjective question for stackoverflow but ill give it a go anyway.

在应用程序之间进行数据迁移是否有通用/最佳实践？可以说我有用Java / J2EE编写的Application A和PostgreSQL数据库连接，而用Ruby / Rails编写的Application B和MySQL数据库连接。

Is there a common/best practice for data migration between applications? Lets say I have Application A written in Java/J2EE and connected to a PostgreSQL database and Application B written in Ruby/Rails and connected to a MySQL database.

我想将数据从应用程序A迁移到应用程序B，因此表结构以及A的数据模型与B完全不同。所以我想从中提取信息

I want to migrate my data from Application A to Application B, the table structure and thus the datamodel of A is completely different from B. So I want to extract information from A, change its structure and insert it into B.

我也有应用程序B中的现有信息，该信息与应用程序A中的信息有关联，例如，基于ID在这两个应用程序中都很常见

Also I have existing information in application B which has a relation with information from application A, based for example on a ID common in both applications

我尝试编写一些精美的sql脚本，但是这种方法并没有很快。

I tried writing a few fancy sql scripts but that goes nowhere fast.

上次我遇到这样的项目时，我只是写了很多代码来处理迁移。我想知道是否有最佳实践？我认为这是开发人员经常完成的工作。也许有可用的工具或框架？

The last time I faced a project like this I just wrote big chuck of code to handle the migration. Is there maybe a best practice for this I wonder? I reckon this is a job done quite often by developers. Maybe there are tools or frameworks available?

推荐答案

也许不是一个最佳实践，但是一旦您选择了一种方法，就可以最佳实践。

Probably not a single best practice, but once you pick an approach, a collection of best practices.

一种策略是在目标平台中以相同模型（或非常接近）引入数据，然后在目标平台内进行转换。

One strategy is to bring the data in the same model (or very close) in the destination platform and then transform within the destination platform.

例如，如果目标是SQL Server，我将在目标服务器上创建另一个数据库，该数据库具有从表到表的直接数据副本（数据类型是您要注意的主要内容）在那里），只需使用针对database2.user.table_names的查询即可填充目标数据模型。

For instance, if the destination was SQL Server, I would create another database on the destination server with straight data copies from tables to tables (data types are the main thing you are watching out for there) and simply use queries against database2.user.table_names in order to populate the destination data model.

这消除了在任何ETL工具选择中源/目标异类的问题您可能正在使用并允许您在数据库2上添加一些其他索引，这些索引可能最适合您的转换。

This eliminates issues with heterogenous source/destination in whatever choice of ETL tool you might be using and allows you to make some additional indexes on the database2 which might be optimal for your conversion.

此外，您的转换将采用直接SQL ，允许同时连接到源和目标，而无需任何服务器间延迟或带宽。

Also, your conversion would be in straight SQL, allowing a joins to both source and destination simultaneously without any inter-server latency or bandwidth.

如果表中有二进制数据或类似的东西，显然事情会发生

If you have binary data in your tables or anything like that, obviously things get a lot more complicated.

这篇关于在应用程序之间进行数据转换的最佳实践是什么的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！