Hitachi Vantara Pentaho Community Forums
Results 1 to 9 of 9

Thread: Adding query parameter ruins blob type

  1. #1
    Join Date
    Feb 2016
    Posts
    16

    Default Adding query parameter ruins blob type

    Sorry for the oblique thread title, but I don't know how else to exaplin it.

    I have a simple transformation: get data out of table 1, parse the json string, stick the parsed data into table 2.

    Query for the table input step is just: "select * from tickets where year(al_last_update) = 2017". When I preview the step, this is what I get:

    Name:  pdi_valid_json.jpg
Views: 65
Size:  52.9 KB

    As you can see, that is valid JSON in the json field. This is fine for an initial load of table 2. However, in the interest of efficiency, I want to first find the max last_updated value from table 2 and use that as a parameter for the query. The parameterized query is: select * from tickets where al_last_update >= ?". I have the upstream steps set up to the point where the transformation at least run. From the logs, I can see that "2017/10/02 05:49:47.000000000" is a value that gets put into the query. However, when I try to preview the data, I get this:

    Name:  pdi_not_json.jpg
Views: 59
Size:  40.7 KB

    What is that in the json field? Can someone explain what is going on? Why does adding a data parameter change the output?

    FYI, the json field in table 1 is defined as a BLOB, and the transformation considers it a String.

    Thanks.

  2. #2
    Join Date
    Sep 2011
    Posts
    152

    Default

    could you please send screenshots of ktr steps defined ?

  3. #3
    Join Date
    May 2016
    Posts
    282

    Default

    Does spoon log show an error? Have you tried specifying the columns to retrieve instead of using a SELECT *?
    OS: Ubuntu 16.04 64 bits
    Java: Openjdk 1.8.0_131
    Pentaho 6.1 CE

  4. #4
    Join Date
    Feb 2016
    Posts
    16

    Default

    The step that gets the current max date:

    Name:  MaxDate.jpg
Views: 62
Size:  17.2 KB

    ...immediately preceeds the main table input step:

    Name:  TableInput.jpg
Views: 58
Size:  19.5 KB

    Thanks!

  5. #5
    Join Date
    Sep 2011
    Posts
    152

    Default

    screenshot looks correct, seems like some datatype problem.

    Does preview is working fine if you put static max date in second table input step?

  6. #6
    Join Date
    Feb 2016
    Posts
    16

    Default

    That was a good idea. Unfortunately, there is no change. I changed the sql in the "Get Max Date" step to "select '2017/10/01 04:41:25.000000000'" and then tried again using a more "normal"-looking MySql date: "select '2017-10-01'". Both attempts produced the odd values you can see in the second screenshot in the prior post.

  7. #7
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Quote Originally Posted by kc1234 View Post
    What is that in the json field?
    It appears to be a java binary reference (Similar to what you get with a Lazy Conversion)
    You might be able to fix it by running it through a "Select Values" step , Meta-Data tab, and selecting the "Binary to Normal" to Y

    Something really odd is going on though, in your first screenshot (where it should only be showing year(al_last_update)=2017), you have rows from 2015.
    Is it possible that someone really goofed, and loaded the text "[B@7da99bb" as the JSON for rows in 2017?

  8. #8
    Join Date
    Feb 2016
    Posts
    16

    Default

    Well, thanks for trying guys, but I eliminated that first table input step and replaced the sql in the second on with:

    Code:
    SELECT
      id
    , al_support_case_id
    , al_last_update
    , json
    , last_updated
    FROM tickets
    where al_last_update is not null
           and al_last_update >= (SELECT max(updated_date) FROM al_tickets)
        and length(json) > 0;
    I really should've thought of that sooner.

  9. #9
    Join Date
    Nov 2013
    Posts
    382

    Default

    Just curious ... shouldn't "Execute for each row" be ticked on your original logic? Replace variables is useless as you don't use any variable, ? is a placeholder for the previous step values, not a variable.

    Edited: also, I would use cast('?' as date)

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.