Hitachi Vantara Pentaho Community Forums
Results 1 to 14 of 14

Thread: How can I replace null with empty string?

  1. #1
    Join Date
    Nov 2010
    Posts
    10

    Exclamation How can I replace null with empty string?

    I used the "If field value is null" step, with "Select value type" checked to replace all string fields to empty, if they are null. If I put some string value in the "Replace by vlaue", it worked fine. However, if I leave it blank, as for empty string, the null fields are not replaced at all. Is there some kind of escaped value for emtpy string?
    Last edited by vincewang; 12-02-2010 at 01:25 PM.

  2. #2

    Default

    Kettle treats empty strings as if they were NULL. In this situation, I cheat and just put in a space....but I feel so dirty doing so.

    I've read about setting the variable KETTLE_EMPTY_STRING_DIFFERS_FROM_NULL=Y, but I haven't been able to get that to work yet.

  3. #3
    Join Date
    Nov 2010
    Posts
    10

    Default

    Thank you very much for replying. Yes, after much research, I came to the conclusion that this is not something easy to be done. I am also using a space to bypass for now. If I have time before deadline, I will try to play with your suggestion of using "KETTLE_EMPTY_STRING_DIFFERS_FROM_NULL=Y" and let you know.
    Personally I think this is a bug that Kettle treats NULL and empty string the same, because when using Merge Rows and Synchronize after merge combo, it would not synchronize, but, keep adding addtional rows with NULL, every time the transfermation runs, if, one of the field to be compared is empty string. It inserts NULL, when the value is actually an empty string. Yet, when doing comparison, they are not equal.

  4. #4
    Join Date
    Nov 2010
    Posts
    10

    Default

    johndz, the KETTLE_EMPTY_STRING_DIFFERS_FROM_NULL=Y setting appears to be working for the 4.1.0. Thanks for the tip! You might want to download the 4.1.0 to try it out.

  5. #5
    Join Date
    Apr 2010
    Posts
    14

    Default

    I'm using the windows version of Kettel (4.1.0).

    I put KETTLE_EMPTY_STRING_DIFFERS_FROM_NULL=Y in
    C:\Program Files\pentaho\design-tools\data-integration\launcher\launcher.properties

    And restarted, but still got the "X can't be NULL" error when inserting an empty string into a MySQL database.

    Am I doing it right?

    For the moment I'm going to continue using a space. (puke)

  6. #6
    Join Date
    Aug 2008
    Posts
    563

    Default

    You have to set this in the kettle.properties NOT in the launcher.properties
    Best regards,
    Diethard
    ===============
    Visit my Pentaho blog which offers some tutorials mainly on Kettle, Report Designer and Mondrian
    ===============

  7. #7
    Join Date
    Apr 2010
    Posts
    14

    Default

    Thanks diddy.

    For anyone following in my footsteps, that file was located here:

    C:\.kettle\kettle.properties

    Just going to try that now...

  8. #8
    Join Date
    Apr 2010
    Posts
    14

    Default

    Hmm. Unfortunately that doesn't seem to have worked.

    Oh well, back to spaces. Thanks anyway.

  9. #9

    Default How to replace nulls with an empty string

    For me there were two pieces to this problem:

    1. Kettle by default interprets empty strings as NULL.
    2. Most of the input data steps seem to read in empty strings as NULL, without any option to do otherwise.

    Solution to part 1:
    _____________________________________
    per

    http://jira.pentaho.com/browse/PDI-2277

    there's a configuration option so Kettle will write empty strings as empty strings, instead of NULL.
    It's called KETTLE_EMPTY_STRING_DIFFERS_FROM_NULL.
    You find it either in $HOME/.kettle/kettle.properties
    Or (according to an earlier post) if you're on windows: at C:\.kettle\kettle.properties.

    You set KETTLE_EMPTY_STRING_DIFFERS_FROM_NULL=Y.

    Solution to Part 2:
    _____________________________________

    Create a variable that contains an empty string, and then replace nulls in your desired column with that variable. (My understanding of Kettle is somewhat limited, so this is a bit hackish. If anyone has a better way of doing this, comments are appreciated.)

    In Spoon interface, add a javascript step to add a variable, you can call it empty_string_whatever.
    Specifically, in the javascript step, add the following:

    setVariable("empty_string_whatever","", "s");

    This sets the variable to be available in the system namespace.

    Then add an "If Null" step after the input step causing you issues. Configure "If Null" as follows:
    Set "Select Value Type" to checked
    Under Value Types, select
    Type: String
    Replace By Value: ${empty_string_whatever}

    This replaces all null strings passing through this step with the contents of the variable empty_string_whatever, or "".

    Now, with those two items configured, you should be converting all your null strings to empty strings, and writing them out as empty strings without Kettle automagically transforming them into nulls.

  10. #10
    Join Date
    Nov 1999
    Posts
    459

    Default Edit the kettle.properties file

    Quote Originally Posted by student_developer View Post
    You find it either in $HOME/.kettle/kettle.properties
    Or (according to an earlier post) if you're on windows: at C:\.kettle\kettle.properties.
    On Windows it is normally also in the users home directory. Other locations are depending on the user that is running e.g. the service of the Data Integration Server.

    To be on the safe side you can edit the variable within Spoon in the menu Edit / Edit the kettle.properties file. Then check the right location of the file and eventually the users home directory of the user account running the service of the Data Integration Server.

    Another option: Also see setting the KETTLE_HOME variable (search the forum or http://kb.pentaho.com/kb/#kbroot/Doc..._home_dir.html ).

    HTH,
    Jens

  11. #11
    Join Date
    Sep 2011
    Posts
    1

    Default

    Did anyone get this to work?

    I have the set the property, KETTLE_EMPTY_STRING_DIFFERS_FROM_NULL = Y. My input is from an Oracle table:

    SELECT CLTCLIENTFIRSTNAME, NVL(CLTCLIENTMIDDLEINITIAL, '') As CLTCLIENTMIDDLEINITIAL
    FROM CLIENTS

    The target DB is MySQL Server - and the target field does not allow NULL values. When I run this simple transformation, I receive the following error:

    2011/12/08 14:38:43 - Entities output.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : Because of an error, this step can't continue:
    2011/12/08 14:38:43 - Entities output.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : org.pentaho.di.core.exception.KettleException:
    2011/12/08 14:38:43 - Entities output.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : Error inserting row into table [Entities] with values: [MARGARET], [null]
    2011/12/08 14:38:43 - Entities output.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) :
    2011/12/08 14:38:43 - Entities output.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : Error inserting/updating row
    2011/12/08 14:38:43 - Entities output.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the value NULL into column 'entMiddleInitial', table 'HCtoSAMStarter110711.dbo.Entities'; column does not allow nulls. INSERT fails.
    2011/12/08 14:38:43 - Entities output.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) :

    PLEASE HELP!!!

  12. #12

    Default

    Thanks, you pointed out the 2 issues :-)
    The first issue was resolved by the property KETTLE_EMPTY_STRING_DIFFERS_FROM_NULL.
    The second issue was addressed in PDI-7073 (http://jira.pentaho.com/browse/PDI-7073).

    The good new is that issue 2 was released in the coming PDI 4.3
    Last edited by shassan2; 12-10-2011 at 12:47 PM.
    Samatar

  13. #13
    Join Date
    Mar 2017
    Posts
    11

    Default

    I am stuck with a similar problem, where i need to store a empty string into a table field which is NOT NULLable... I can see the value i am passing is empty and not null but its rejected in the table output step with the error that field cannot store null values... I was searching for the kettle properties file to update "KETTLE_EMPTY_STRING_DIFFERS_FROM_NULL=Y" but i cannot find it...

    Is there a updated settings for this ?

  14. #14
    Join Date
    Nov 1999
    Posts
    459

    Default

    See within Spoon: Edit / Edit the kettle.properties file. You can also manually add the string to the file, it is located in your .kettle users home folder, see also
    https://help.pentaho.com/Documentati...ctory_Location

    HTH,
    Jens

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.