Hitachi Vantara Pentaho Community Forums
Results 1 to 10 of 10

Thread: Changing mask used for MS SQL input

  1. #1
    Join Date
    Nov 2015
    Posts
    3

    Default Changing mask used for MS SQL input

    Hi,
    I'm relatively fluent with computers, but a complete novice to this software (and barely above novice with SQL in general). I'm trying to do an ETL between a database running on MSSQL and one running on MySQL. I set up a database connection for import using MS SQL and it connects successfully. I have a table input using that connection which works, but there's a problem - the output field mask is set wrong. On the server itself, when I view the field the type is float and the number has the form ##.####### or -##.####### . However, when I view the output fields in Kettle the type is number and the mask is #.#;-#.#. I need the entire floating-point value, not truncated to one decimal place.

    I've spent some time googling for answers and looking through the documentation, but I can't seem to find any information on how to change the mask. I realize this is probably a pretty basic question, but I'm stumped.

    Two other quick questions:
    Is there a comprehensive source for documentation, like a manual?
    What's the difference between "MS SQL" and "MS SQL (Native)" for the database type?

    Thanks!

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

    Default

    To change the "mask" (metadata or format) of a field, you can use Select Value step and check the metadata tab.
    -- Mick --

  3. #3
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Kettle being a Java application (mostly) relies on JDBC for database access.
    Native refers to the JDBC driver from Microsoft, while an open source driver is already installed with Kettle.
    With some input steps you can specify a Java NumberFormat as part of the field metadata, not so with Table-Input.
    Select-Values will help you out in that case, as Mick already told you.
    So long, and thanks for all the fish.

  4. #4
    Join Date
    Nov 2015
    Posts
    3

    Default

    Thanks for the info, however, I'm still having issues. I have a select values step after the table input. When I go to "select fields" for that step, it shows a length of 15 and a mask of #.#;-#.#. Under metadata, I have the input fields marked as string, length 15. It still previews the same. In fact, if I preview only the table input step, the numbers are already truncated. If the numbers are truncated in the table input step, I'm not sure how the select values step is going to restore the missing digits...

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

    Default

    Instead of changing it to a string(15), try changing it to a number with a format of "##.#############"

    Data Preview doesn't always show you the actual truth.

  6. #6
    Join Date
    Nov 2015
    Posts
    3

    Default

    OK, got it working now. Table input -> select fields -> text file output, using either string or number, with or without specifying the length and manually typing in the form ##.####### works. If I use -##.####### it adds another "-" onto the front.

    Thanks!

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

    Default

    If you are going DB to DB, you shouldn't really need the Select Values in the middle... but you'd have to try it to be sure.

  8. #8
    Join Date
    Jul 2016
    Posts
    4

    Default

    Hi. This is a old post, but I have a similar question. I have a transformation that has a table input. This is a simple select from the DB.

    SELECT top 1 crncy, RT
    -- , UPD_By, UPD_DT
    FROM [LocalPNLZamb].[dbo].[FX_RT]
    where crncy = 'ZMW'
    order by upd_dt desc

    It give me 2 values.
    Name:  Image1.PNG
Views: 200
Size:  9.8 KB

    The RT is masked to 1 decimal. I can see this if I look at Output Fields.
    Name:  Image2.jpg
Views: 208
Size:  9.6 KB

    So how to I change this mask? And why is it there to start with?

    So to follow to this conversation, I tried a Text file output, under fields I changed the Format to "#.###;-#.###", and injected that into the rest of my transformation.
    Name:  Image3.PNG
Views: 215
Size:  9.4 KB
    And I now get the unmasked value:
    Name:  Image4.PNG
Views: 204
Size:  2.0 KB

    But why does Pentaho add the mask (seemingly by itself)?
    Surely there should be a simpler way to rectify this?

    I'm on Pentaho Version 7.0.0.0-25

    Vissie
    Last edited by Vissie; 08-01-2017 at 05:40 AM.

  9. #9
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    For every non-String datatype there is a default conversion format in Kettle.
    That's because Kettle is metadata-centric - no data-type should ever go without a format.
    Sometimes you can specify your own format in an Input step.
    If not, there's always Select-Values metadata tab.
    You may think it's early enough to provide the format in your Text-File-Output, but Kettle doesn't look ahead and requires a format as soon as a datatype is given.
    That's all.
    So long, and thanks for all the fish.

  10. #10
    Join Date
    Jul 2016
    Posts
    4

    Default

    Ok, clear! Thx. Thx for explaining.

    Vissie

Tags for this Thread

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.