Hitachi Vantara Pentaho Community Forums
Results 1 to 8 of 8

Thread: Can't import Key=Value text file as a single row into target Db

  1. #1
    Join Date
    Jan 2014
    Posts
    3

    Default Can't import Key=Value text file as a single row into target Db

    Using pdi v 4.4.0 on RHEL 6, I cannot seem to import a text file that maps to a single database row in the target MySQL database table. The text file has 155 lines, with each line mapping to a column in the target table. Each line starts with a "key" name that maps to the name of the target database column and the value follows a fixed-place "equals" (=) sign. The units of that value follow after a fixed length, but they are optional to ingest. Each line is terminated with carriage return/line feed ("\r\n")
    I can "preview rows", but I can't get the records to split on the carriage return. Any ideas?

    Sample data: (when I paste this data into the forum box, it automatically trims the spaces between keys and "=". believe me, the "=" signs are all lined up at the 36th position)
    OBS_AVAILABLE =250
    OBS_USED =29
    RESIDUALS_ACCEPTED =100.0
    WEIGHTED_RMS =0.873
    AREA_PC =4.2082 [m**2]
    CD_AREA_OVER_MASS =0.009383 [m**2/kg]
    CR_AREA_OVER_MASS =0.000000 [m**2/kg]
    THRUST_ACCELERATION =0.00000E+00 [m/s**2]
    SEDR =2.84444E-05 [W/kg]
    X =-486.595819 [km]
    Y =-24.442056 [km]
    Z =7130.146031 [km]
    Attached Files Attached Files
    Last edited by wooldridger; 01-08-2014 at 07:34 PM.

  2. #2
    Join Date
    Nov 2008
    Posts
    777

    Default

    Can you attach to your post the text file or at least a portion of it? You will have to "Go Advanced" to do so.
    pdi-ce-4.4.0-stable
    Java 1.7 (64 bit)
    MySQL 5.6 (64 bit)
    Windows 7 (64 bit)

  3. #3
    Join Date
    Jan 2014
    Posts
    3

    Default

    Text file uploaded. I figured out that I need to use the denormalizer and am fumbling my way through that now. If anyhow has denormalizer examples, I would appreciate you uploading one here i can look at.
    Thanks!

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

    Default

    In your installation of PDI, there's a folder called /samples.
    There are transformations which show how to use steps.
    Have a look and see if there's one with a denormaliser step.
    -- Mick --

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

    Default

    Denormalizing should be the smallest problem, though.
    Attached Files Attached Files
    So long, and thanks for all the fish.

  6. #6
    Join Date
    Nov 2008
    Posts
    777

    Default

    Do you really need to denormalize? Is it possible to just update each field one-row-at-a-time with a carefully crafted Execute SQL Script step?
    pdi-ce-4.4.0-stable
    Java 1.7 (64 bit)
    MySQL 5.6 (64 bit)
    Windows 7 (64 bit)

  7. #7
    Join Date
    Jan 2014
    Posts
    3

    Default

    Thank you very much for the sample. I would not have been able to get this far without it. I'm still chugging through the 155 lines of the Denormalise step.

  8. #8
    Join Date
    Nov 2008
    Posts
    777

    Default

    I still think denormalizing is not necessarily the best way to go but I have a few questions/concerns to help the thought process along:
    1. You say you have 155 lines to denormalize but do you realize that you have duplicate key names in your list? Unless you rename some of them, they will just overwrite each other. I think this is what marabu was referring to when he said denormalizing is the least of your worries.
    2. In reality, your input can be divided into 3 groups: a header group (first 14 keys/values), OBJECT1 (70 keys/values), and OBJECT2 (repeat of the previous 70 keys with some different values).
    3. So what is the format of your database table? Does it indeed have 155 columns and only one row in it? Or does it actually contain ~70 columns and is thus a collection of object rows whereby the operation of writing your file to the database would actually produce two rows - one for each object?


    Let me know if I'm way off base here but attached is an attempt to generate SQL statements that update each field one-at-a-time, i.e., without denormalizing. Doing so would generate SQL statements like this:

    Code:
    UPDATE ${TABLE_NAME} SET OBJECT_DESIGNATOR=24949 WHERE OBJECT='OBJECT1';
    UPDATE ${TABLE_NAME} SET CATALOG_NAME='SATCAT' WHERE OBJECT='OBJECT1';
    UPDATE ${TABLE_NAME} SET OBJECT_NAME='IRIDIUM 30' WHERE OBJECT='OBJECT1';
    UPDATE ${TABLE_NAME} SET INTERNATIONAL_DESIGNATOR='1997-051F' WHERE OBJECT='OBJECT1';
    UPDATE ${TABLE_NAME} SET EPHEMERIS_NAME='NONE' WHERE OBJECT='OBJECT1';
    UPDATE ${TABLE_NAME} SET COVARIANCE_METHOD='CALCULATED' WHERE OBJECT='OBJECT1';
    UPDATE ${TABLE_NAME} SET MANEUVERABLE='N/A' WHERE OBJECT='OBJECT1';
    UPDATE ${TABLE_NAME} SET REF_FRAME='ITRF' WHERE OBJECT='OBJECT1';
    UPDATE ${TABLE_NAME} SET GRAVITY_MODEL='CUSTOM: 36D 36O' WHERE OBJECT='OBJECT1';
    UPDATE ${TABLE_NAME} SET ATMOSPHERIC_MODEL='JBH09' WHERE OBJECT='OBJECT1';
    ...
    UPDATE ${TABLE_NAME} SET OBJECT_DESIGNATOR=21529 WHERE OBJECT='OBJECT2';
    UPDATE ${TABLE_NAME} SET CATALOG_NAME='SATCAT' WHERE OBJECT='OBJECT2';
    UPDATE ${TABLE_NAME} SET OBJECT_NAME='SCOUT G-1 DEB' WHERE OBJECT='OBJECT2';
    UPDATE ${TABLE_NAME} SET INTERNATIONAL_DESIGNATOR='1991-045C' WHERE OBJECT='OBJECT2';
    UPDATE ${TABLE_NAME} SET EPHEMERIS_NAME='NONE' WHERE OBJECT='OBJECT2';
    UPDATE ${TABLE_NAME} SET COVARIANCE_METHOD='CALCULATED' WHERE OBJECT='OBJECT2';
    UPDATE ${TABLE_NAME} SET MANEUVERABLE='N/A' WHERE OBJECT='OBJECT2';
    UPDATE ${TABLE_NAME} SET REF_FRAME='ITRF' WHERE OBJECT='OBJECT2';
    UPDATE ${TABLE_NAME} SET GRAVITY_MODEL='CUSTOM: 36D 36O' WHERE OBJECT='OBJECT2';
    UPDATE ${TABLE_NAME} SET ATMOSPHERIC_MODEL='JBH09' WHERE OBJECT='OBJECT2';
    ...
    Attached Files Attached Files
    Last edited by darrell.nelson; 01-10-2014 at 11:49 AM.
    pdi-ce-4.4.0-stable
    Java 1.7 (64 bit)
    MySQL 5.6 (64 bit)
    Windows 7 (64 bit)

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.