I am using Talend Open Studio for Data Integration v6.2.1.
We have a lot of Talend jobs built against our existing Data Warehouse which all work fine and do their jobs nicely.
We are now in the process of redesigning the Data Warehouse to improve overall data logic and reduce the complexity of user queries.
This means that we are changing table structures, renaming tables and renaming columns, etc.
In Talend this is currently proving very time consuming...
In all my jobs i have created Metadata DB Schema and i use these in all my jobs.
If i take an example table 'Products', our new design means the table name will change to 'LU_Product'.
I changed this in the Edit Schema menu within Talend and propagated the changes. However, when i look at existing jobs that used this, i see the Schema has updated but the queries in the Input components still show the old table name... Seems a little strange to me that it would update one and not the other...
Anyone know a way to get this working?
Similarly, if a rename a column, example the primary key column in the 'Products' table was 'products_id', now that we have changed the table to 'LU_Product' the primary key column will be called 'idProduct'. When i rename the column in the schema and propagate changes all components treat this as a new column and all items in SQL code of Input components and any logic in tMap components that referenced 'product_id' have either been left as they were (in the case of Input) or removed complete (in the case of tMap). This is very frustrating and means that for every table or column change i need to manually edit and reconfigure every job to reflect every change. It would nearly be as fast for me to rebuild everything from scratch as to do this - there must be a better way of doing this???
Any and all help is greatly appreciated!
As a shortcut, I suggest you to define views on the DB.
These should be defined with old names for tables and fields and give access the desired tables and columns with their new names.
As an example, you should have a view called "Products" with a field "products_id" mapped to the table "LU_Product" and the field "idProduct" (based on your example).
Also, if you want some naming rules, you can name the view "View_Products" (as an example) and create a synonym (public or private) on this view with the nae "Products".
Then, nothing to do with on Talend side if you accept to follow this guide line.
Remark: I think you should move this topic to another discussion group.
I appreciate the suggestion TRF but this seems like just as much work as editing the job.
It just seems like a way of overcoming a shortcoming of the product - it shouldn't be this hard to reflect database changes in the ETL Tool...
I understand, just have to know that if you still have the old database, you can easily generate the views using the information from the DB dictionary, that is not possible in Talend.
So, in a 1st time, you can proceed as suggested for a quick result, then change the jobs slowly to go to the new schema.
Introduction to Talend Open Studio for Data Integration.
Practical steps to developing your data integration strategy.
Create systems and workflow to manage clean data ingestion and data transformation.