如何在Talend中即时猜测Mysqlinput中的模式 [英] How to Guess schema in Mysqlinput on the fly in Talend

问题描述

我已经建立了一个将数据从mysql db表复制到b mysql表的作业. 表列是相同的，除了有时可以在表db中添加新列.

I've build a job that copy data from a mysql db table to b mysql table. The table columns are the same except sometimes a new column can be added in table a db.

我想检索从a到b的所有列，但只检索表b中存在的那些列.我能够放入表b中存在的特定于查询的select colume语句，例如:

i want to retrieve all the columns from a to b but only those that exists in table b. i was able to put in the query specific select colume statment that exists in table b like:

select coulmn1,column2,columns3... from table a

问题是，如果我在b中添加了与Mysqlinput中的talend作业模式匹配的新列，也应该进行更改，因为我使用内置类型.

the issue is if i add a new column in b that matches a the talend job schema in Mysqlinput should be changed as well cause i work with build in type.

有没有办法在作业运行期间强制模式列?

Is there a way to force the schema columns during the job running?

推荐答案

如果使用的是Talend订阅版本，则可以使用动态列类型.您可以为动态"类型的输入定义单个列，并将其映射到输出组件中相同类型的列.这将动态地从表a中获取列，并将它们映射到表b中的相同列.这是一个示例.
如果您使用的是Talend Open Studio，那么事情会变得有些棘手，因为Talend希望在设计时就定义输入和输出组件的列列表.

If you are using a subscription version of Talend, you can use the dynamic column type. You can define a single column for your input of type "Dynamic" and map it to a column of the same type in your output component. This will dynamically get columns from table a and map them to the same columns in table b. Here's an example.
If you are using Talend Open Studio, things get a little trickier as Talend expects a list of columns for the input and output components that need to be defined at design time.

这是我整理出的一种解决方案，可以解决此限制.

Here's a solution I put together to work around this limitation.

这个想法是列出表b中所有表a的列.然后将其转换为逗号分隔的列列表(在我的示例中为id,Theme,name)，并将其存储在全局变量COLUMN_LIST中. tMap的第二个输出构建相同的列列表，但是这次在列之间放置单引号(以便以后可以将它们用作CONCAT函数的参数)，然后将单引号添加到开头和结尾，例如因此:"'", id,"','",Theme,"','",name,"'"并将其存储在全局变量CONCAT_LIST中.

The idea is to list all table a's columns that are present in table b. Then convert it to a comma separated list of columns, in my example id,Theme,name and store it in a global variable COLUMN_LIST. A second output of the tMap builds the same list of columns, but this time putting single quotes between columns (so as they can be used as parameters to the CONCAT function later), then add single quotes to the beginning and end, like so: "'", id,"','",Theme,"','",name,"'" and store it in a global variable CONCAT_LIST.

在下一个子作业中，我使用CONCAT函数查询table a，为它提供要连接的列的列表CONCAT_LIST，从而像'value1', 'value2',..etc

On the next subjob, I query table a using the CONCAT function, giving it the list of columns to be concatenated CONCAT_LIST, thus retrieving each record in a single column like so 'value1', 'value2',..etc

然后最后，我通过指定全局变量COLUMN_LIST给定的列列表以及要插入的值作为CONCAT函数产生的单个字符串，对table b执行INSERT查询(row6.values).

Then at last I execute an INSERT query against table b, by specifying the list of columns given by the global variable COLUMN_LIST, and the values to be inserted as a single string resulting from the CONCAT function (row6.values).

此解决方案是通用的，如果您用上下文变量替换表名，则可以使用它来将数据从任何MySQL表复制到另一个表.

This solution is generic, if you replace your table names by context variables, you can use it to copy data from any MySQL table to another table.

这篇关于如何在Talend中即时猜测Mysqlinput中的模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！