1

Pentaho-Kettle is lowercasing the table names included in a select-based sql query derived from an XML file I cannot manipulate. As I have to execute this sql query against a PostgreSQL database which contains uppercased table names, Pentaho-Kettle logs errors at the time of executing the corresponding job.

The select-based sql queries I have to deal with are in this way:

SELECT tblBAS.*, tblLTFU.FIELD1 FROM tblBAS INNER JOIN ... ON ... WHERE ...

And the PostgreSQL database I have to work with does contain a couple of tables named tblBAS and tblLTFU.

I know that if I manually modify the query to quote table names, Pentaho-kettle executes the query properly, but I cannot manipulate the whole set of queries within the XML file, as said before.

In the 'Database Connection window > Advanced' section of the Table Input step where I'm attempting to execute the query, I've checked 'Quote all in database' option but with no success. It seems that the PostgreSQL driver of Pentaho-Kettle automatically lowercases table names and fields of the queries.

Does any Pentaho-Kettle expert knows a way of instructing Pentaho (or Pentaho PostgreSQL driver) to keep the names of table names and fields of a sql query in a mixed of upper-lowercase letters? Any workaround will be appreciated too.

txapeldot
  • 71
  • 2
  • 10
  • 1
    Another good example on why using those dreaded quoted identifiers (`"tblBAS"` rather than `tblbas`) when creating tables is a bad idea –  Jun 05 '19 at 10:46
  • Yes, I know it. The problem is that it all comes from a migration from an Access database to a PosgreSQL one. Any workaround? – txapeldot Jun 05 '19 at 11:09
  • Is the query itself working ? And all you need is for the names of the Columns AFTER input to be a mix of lower and upper case ? Nothing else ? – Cristian Curti Jun 05 '19 at 16:39
  • @CristianCurti yes, it's working as long as its table names and fields are quoted. Sorry but I don't understand your second question. – txapeldot Jun 06 '19 at 07:24
  • It is not a pentaho thing, its postgres. As you said, postgresq lowercase any name by default and only will keep the exact name if you use double quotes. Maybe if you create a postgres function that returns the name of the table wrapped by double quotes it can do the trick... – jacktrade Jun 06 '19 at 07:34
  • @txapeldot What i mean is if the query is returning the data you need, and the fields in the pentaho stream are in a lower case. If all you need are the columns renamed inside the pentaho stream there is a very simple solution for that, but that is AFTER the query is run. – Cristian Curti Jun 06 '19 at 12:40
  • 1
    @CristianCurti The query is returning the expected data as long as I modify every sql query in the xml (input) file. This modification consists of quoting each reference to a table name and to a table field. And that's unaffordable on a file with 100+ queries. It's not a matter of having the columns/fields renamed, it's a matter of the query is not executed if the column names are kept without quoting them because table names (lowercased by PostgreSQL driver for Pentaho) doesn't actually exist. – txapeldot Jun 06 '19 at 12:56
  • Seems that this is a PostGRE RDMS issue, PostgreSQL folds all unquoted identifiers to lowercase when creating a table, those tables you're reffering to were possibly created with "tblLTFU" clauses, so you are now forced to use double quotes to reach them, [https://stackoverflow.com/questions/20878932/are-postgresql-column-names-case-sensitive]. I think you have to use a workaround for these cases, like breaking down the query and quoting the identifiers, this is going to be hell i think. – Cristian Curti Jun 06 '19 at 13:39
  • @CristianCurti: yes, I'm afraid so. Thanks anyway. – txapeldot Jun 06 '19 at 14:08
  • Create separated schema, make tables with the same name but use default column naming rules. Add a special user for which this new schema is the default. Add for each table a trigger that will copy data to the original table. Metadata is available for trigger so use this info. Trigger actually may one for all tables. Performance issues must be tested. – simar Jun 11 '19 at 11:00
  • Technically it is not necessary to create even new user, just change search_path for this session. – simar Jun 11 '19 at 11:02

0 Answers0