Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Binary Compatibility Using Database Lookup Step between PostgreSQL and MySQL

  1. #1
    Join Date
    Oct 2013
    Posts
    3

    Default Binary Compatibility Using Database Lookup Step between PostgreSQL and MySQL

    Has anyone here ever tried to use the Database Lookup Step in PDI where the lookup key is based on a binary value from PostgreSQL (bytea) and comparing it to a MySQL binary(16) key?

    The Database Lookup step seems not to be able to match the key values. The log also shows that the value is a little bit mangled:

    The value should be aaaabbbbccccddddeeeeffff00000000 but the Lookup Step has this:

    2013/12/10 15:39:32 - Lookup sourcetable.0 - Checking row: [πaabbbbccccddddeeeeffff00000000]
    2013/12/10 15:39:32 - Lookup sourcetable.0 - Field [binfieldx] has nr. 0

    Note the funny character at the front of the field?

    Any ideas welcome.

  2. #2
    Join Date
    Apr 2008
    Posts
    1,771

    Default

    What if you CAST them both as String first?
    -- Mick --

  3. #3
    Join Date
    Oct 2013
    Posts
    3

    Default

    Quote Originally Posted by Mick_data View Post
    What if you CAST them both as String first?
    OK, so i changed the query to PostgreSQL to:

    SELECT binfieldx::varchar binfieldx,
    FROM bininfo


    so that the binary value is a string.

    The log ended up being:

    2013/12/11 09:15:32 - Lookup sourcetable.0 - Checking row: [\xaaaabbbbccccddddeeeeffff00000000]
    2013/12/11 09:15:32 - Lookup sourcetable.0 - Field [binfieldx] has nr. 0
    2013/12/11 09:15:32 - Lookup sourcetable.0 - Got row from previous step: [\xaaaabbbbccccddddeeeeffff00000000]
    2013/12/11 09:15:32 - Lookup sourcetable.0 - Added 1 values to lookup row: [\xaaaabbbbccccddddeeeeffff00000000]
    2013/12/11 09:15:32 - Lookup sourcetable.0 - No result found after database lookup! (add defaults)


    So it still doesn't find it.

    How can one cast the compared key from the source table to a string in the Lookup Step?

    Even if that were possible, it would probably render the index mostly useless since it might cause a table or index scan to get all the binary values and then convert them to string and then do the compare.

  4. #4
    Join Date
    Apr 2008
    Posts
    1,771

    Default

    As I understand it, any index is useless because you compare 2 fields form 2 different databases.
    As far as I know, an Index is used only internally by one db to execute its operation in a more efficient way.

    My reasoning was as follow: the binary values of Postgre and MySQL are (according to your post) different.
    The next question would be: are they genuinely different (different records) or are they different because of the implementation of the 2 db?
    If the answer to the above question is the second part, then you need to transform those value and translate them so that it's possible to compare them.

    I'm not an expert of Binary, but my FIRST test would be to change those values into string and then check if those strings can be compared.

    Therefore if I were you:
    1. find 2 records that should be classified as a match
    2. use the lookup step and check if those records are a match
    3. if not, use a select value step and in the metadata tab change their value into string - now compare those values.
    4. if it does not work, google postgre and mysql BINARY data format and see how they store those values and if it's possible to "translate" them.
    -- Mick --

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.