How to change the column name from UPPER case to lower case without using tMap

Five Stars

How to change the column name from UPPER case to lower case without using tMap

I'm trying to import data from MySQL to PostgreSQL but seems there is a case sensitive issue with column names.
MySQL has:
Id | OrgId | EmailID

PostgreSQL has:
id | orgid | emailid

To make it easy for Talend, I selected column names renaming them to lowercase in the tDBInput and then added them to tDBOutput
" select Id as id,OrgId as orgid,EmailId as emailid from employee "

However, it still fails with below error
ERROR: column "Id" of relation "employee" does not exist

 

I was dumping data from 75 tables and do not want to create views or tMap(this components seems staging data and takes longer time).
Is there any other way to achieve this using Talend Open Studio 7.0.1?


Accepted Solutions
Community Manager

Re: How to change the column name from UPPER case to lower case without using tMap

Your change earlier where you noticed that after casting the value the error moved on to the next column, seems to suggest that your source types are different to your target types. Can you add a tMap and see if this solves this?

View solution in original post


All Replies
Twelve Stars

Re: How to change the column name from UPPER case to lower case without using tMap

your problem is on existence of column not on name.
check your select query. did you request n multi table or on view…..

Francois Denis

Tag as "solved" for others! Kudos to thanks!

Community Manager

Re: How to change the column name from UPPER case to lower case without using tMap

Can you give us a screenshot of your job and your db component configurations? This doesn't quite give enough information.

 

FYI the tMap does not stage the data anywhere and will not add a great deal of time to the processing at all. 

Five Stars

Re: How to change the column name from UPPER case to lower case without using tMap

I guess there is not an issue with existence of column.
When I cast the column as below it throws error on the "OrgId" column that means the "id" column has been accepted.
" select cast(Id as unsigned)as id,OrgId as orgid,EmailId as emailid from employee "

I also checked this in the SQL Builder using only one column which doubles my doubt on the case sensitive of the column name.

Five Stars

Re: How to change the column name from UPPER case to lower case without using tMap

@rhall_2_0

I have attached the screenshots in a document.

 

To illustrate, I'm not using the actual data tables and only playing with dummy tables.

Let me know if something is not clear.

 

Thank you.

Community Manager

Re: How to change the column name from UPPER case to lower case without using tMap

It is not clear where your error is originating (the input or output component) but your response to @fdenis hints that actually your column types between your databases are precisely the same. Can you disconnect your DBOutput component and add a tLogRow. Try running that.

 

Also, how did you get your database schemas (both sides)? Did you allow Talend to do this or did you create them manually?

Five Stars

Re: How to change the column name from UPPER case to lower case without using tMap

@rhall_2_0

Error seems to be generating at destination because when I add tLogRow it succeeded without errors and gave me expected result.

I think schema is retrieved from connection because I explicitly asked the Talend to retrieve.

 

To give more background on this error:

Yes, the source and destination tables exists already with similar datatypes and same column names. The only issue is with the column name case, at source table all columns have mixed case but at destination all columns are created in lowercase.

 

Thank you.

Highlighted
Twelve Stars

Re: How to change the column name from UPPER case to lower case without using tMap

you can get columns to dynamic and then rename all column to lower case.
you have to use tJavaRow or tJavaFlex.
dynamic types is allowed only on enterprise version.

Francois Denis

Tag as "solved" for others! Kudos to thanks!

Five Stars

Re: How to change the column name from UPPER case to lower case without using tMap

@fdenis

Yes but I'm using Talend Open Studio 7.0.1

 

Thank you.

Twelve Stars

Re: How to change the column name from UPPER case to lower case without using tMap

so you can generate insert statement "insert into xxx (xxx,xxx,...) values (yyy,yy,...)"
and run it.

Francois Denis

Tag as "solved" for others! Kudos to thanks!

Community Manager

Re: How to change the column name from UPPER case to lower case without using tMap

Your change earlier where you noticed that after casting the value the error moved on to the next column, seems to suggest that your source types are different to your target types. Can you add a tMap and see if this solves this?

View solution in original post

Five Stars

Re: How to change the column name from UPPER case to lower case without using tMap

@rhall_2_0

Yes, adding a tMap solves the issues. Doesn't tMap stage the data before loading to destination which may be a performance hiccup?

Community Manager

Re: How to change the column name from UPPER case to lower case without using tMap

No, the tMap doesn't stage the data. Everything is done in memory by default. You CAN store it, but unless you are working with lots of lookups over millions of rows, there is no point. The scenario you have described here doesn't sound like you would need to do that.

 

What performance issues are you seeing? I have never seen performance issues caused by a simple pass-through tMap 

Five Stars

Re: How to change the column name from UPPER case to lower case without using tMap

@rhall_2_0

I have no evidence on performance issues. Its just my assumption.

Community Manager

Re: How to change the column name from UPPER case to lower case without using tMap

OK. Just use the tMap solution then. You will not see any performance problems doing it this way

Five Stars

Re: How to change the column name from UPPER case to lower case without using tMap

@rhall_2_0

Thank you

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog