Pentaho

 View Only

 How to change field name to lower case when copying table data from Oracle to PostgreSQL?

  • Pentaho
  • Kettle
  • Pentaho
  • Pentaho Data Integration PDI
Data Conversion's profile image
Data Conversion posted 02-06-2018 16:24

I am using ETL Metadata Injection to provide table name to the template transformation as a parameter which is read from a list of names. My template transformation contains a single Table input connected to a single Table output.

Currently the transformation is failing as the column "SYSID" is quoted in the generated INSERT statement because it is a reserved word. As a result it cannot be found in the output table because in PostgreSQL the column name is lower case.


#PentahoDataIntegrationPDI
#Pentaho
#Kettle
Data Conversion's profile image
Data Conversion posted 02-06-2018 23:20

Calculator step has a function "Lowercase of String A" that will convert "SYSID" to "sysid", but that will need to happen before you inject into your template.

If you are doing a simple Table Input ("select * from tableA")  -> Table Output (expect matching columns) then this won't work.

John Craig's profile image
John Craig posted 02-08-2018 22:11

The Transformations / Select Values step allows you to easily change the names of columns; unfortunately, you'll have to list each one (but unless it's a really long list of columns, that's not too onerous if the list of columns is fairly static--or there's only a handful of columns whose names you need to force to lower-case).

Other options would be

  1. To use a Scripting / Execute SQL script step to invoke the SQL and use a pattern like this:select SYSID as "sysid", ...    from ...
  2. If you want to handle any column names (convert every one to lower case), I'd suggest a Scripting / User Defined Java Class which would retrieve the metadata for each column and use String.toLowerCase() on each column name.

Related Content