如何从现有的一个表数据库中填充一个合理的多表MySQL数据库? [英] How do I populate a rational multi-table MySQL database from an existing one table database?
问题描述
基本上有很多很大的分隔文件，我知道我可以将它们导入为表格，但是我需要将该数据映射到现有的有理多表MySQL数据库.数据类型应该不会有任何冲突，但是我对此并不陌生，所以请指出我应该注意的任何事情.显然，除非我知道它可以工作，否则我也不会在生产中运行它.
Basically have many huge delimited files that I know I can import as a table, but I need to map that data to an existing rational multi-table MySQL database. There should not be any conflict with datatypes, but I'm super new to this, so please point out anything I should be watching for. Clearly I'm not going to run this in production either until I know it works.
不能100％地确定stackoverflow是询问数据库问题的正确位置，但是我找不到其他更合适的Stack Exchange.
Not 100% sure stackoverflow is the right place to ask a database question, but I couldn't find any other Stack Exchange that was a better fit.
发布了此问题在SuperUser上，它正在寻找GUI来执行此操作，但是我为此编写了代码，因为它可以完成工作.因此，没有目标语言，只是要求数据库为MySQL.
Posted this question on SuperUser looking for a GUI to do this, but I up for coding this is it gets the job done. As such there is no target language, just the requirement that the database be MySQL.
此外，还发现该堆栈溢出Q/A处理MS-SQL的SSIS(由于成本原因，我不打算使用它，但所面临的内容和问题具有相同的性质.)- 使用SSIS加载多个表并保持外键关系
Also, found this stackoverflow Q/A that deals with MS-SQL's SSIS (which I'm not planning on using due to cost, but the content and issues faced are of the same nature it appears.) -- Loading Multiple Tables using SSIS keeping foreign key relationships
推荐答案
我建议使用 Pentaho商业智能软件包.它有一些学习曲线，但可以完全满足您的需求.他们的ETL工具称为Kettle，一旦掌握了它，它就会非常强大.
I'd suggest using the ETL(extract translate load) tool from the Pentaho Business Intelligence package. It's got a bit of a learning curve but it'll do exactly what you're looking for. Their ETL tool is called Kettle and it's extremely powerful once you get the hang of it.
Pentaho有两个版本，一个是具有免费试用版的企业版，另一个是免费的社区版.社区版本功能强大，但您也可以对企业版本进行测试.
There are two versions of Pentaho, an enterprise version that has a free trial, and a free community version. The community version is more than capable but you might give the enterprise version a test ride too.
这里有一些链接
更新:多个表输出
转换中的关键步骤之一将是组合查找-更新.此步骤检查给定的表以查看数据流中的记录是否存在，如果不存在，则插入一条新记录.无论是新记录还是旧记录，都将把该记录中的关键字段附加到您的数据流中.继续操作时，将数据导入相关表时，将这些键用作外键.
One of the key steps in your transformation is going to be a combination lookup-update. This step checks a given table to see if a record from your data-stream exists and inserts a new record if it does not. Regardless of whether it's a new or old record it's going to append the key field from that record into your data-stream. As you keep going you'll use these keys as foreign keys as you import data into related tables.
这篇关于如何从现有的一个表数据库中填充一个合理的多表MySQL数据库?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
