Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: Newbie Help. simple ETL doesn't work

  1. #1
    Join Date
    Jan 2008
    Posts
    3

    Angry Newbie Help. simple ETL doesn't work

    Hi,

    my basic requiremnt is to be able to load text files into an oracle database.

    I have downloaded and installed the windows version of Kettle. All is fine and it loads OK. I read the manual (quickly).

    My first test was to use kettle to simply create a CSV file from an Oracle database table. This worked.

    I then wanted to reload the CSV file back into the Oracle table.

    To do this, I used the csv file i had extracted - TEST.CSV. The data is all there and fine.

    I set up a simple two step transformation
    1) input from CSV
    2) Table output.
    and run the process.

    First, Kettle can't recognise a lot of the fields correctly, even though it created them. I have been and reset all of the fileds to their correct format (string, integer, etc..).

    I run the process.

    The output table cannot be updated and the run has errors. This seems to be in the update of the table:

    2008/01/05 14:39:15 - Table output.0 - Prepared statement : INSERT INTO BANK_DATA.UNREALISED_LEADS_HISTORY(LEAD_ID, ACCOUNT_ID, CUSTOMER_ID, EVENT_ID, DATE_, AMOUNT, RELATIVE_VALUE, CHANNEL_INFO, STATUS, COMMENT_, OPTIMISING_STEP, RESULT, RESPONSE_CODE, BANK_CODE, ACCOUNT_CODE, END_DATE) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    2008/01/05 14:39:15 - Table output.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : Because of an error, this step can't continue:
    2008/01/05 14:39:15 - Table output.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : Error inserting row into table [UNREALISED_LEADS_HISTORY] with values: [ 41615775], [020004600000000113050003], [ 002878917], [ 101], [2004/06/09 00:00:00.000], [5,985.00], [299.25], [], [E], [Grandi Depositi : 5985.00], [ 1], [], [null], [], [], [2004/06/10 00:00:00.000]
    2008/01/05 14:39:15 - Table output.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) :
    2008/01/05 14:39:15 - Table output.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : offending row : [LEAD_ID Integer(8)], [ACCOUNT_ID String(24)], [CUSTOMER_ID Integer(9)], [EVENT_ID Integer(3)], [DATE_ Date], [AMOUNT Number(18, 2)], [RELATIVE_VALUE Number(18, 2)], [CHANNEL_INFO String(15)], [STATUS String(1)], [COMMENT_ String(100)], [OPTIMISING_STEP Integer(1)], [RESULT String(1)], [RESPONSE_CODE String(1)], [BANK_CODE String], [ACCOUNT_CODE String], [END_DATE Date]
    2008/01/05 14:39:15 - Table output.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) :
    2008/01/05 14:39:15 - Table output.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : Error setting value #1 [LEAD_ID Integer(8)] on prepared statement (Integer)
    2008/01/05 14:39:15 - Table output.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : java.sql.SQLException: General error

    a) there are no constraints on the table.
    b) i have full rights

    Next I removed the column LEAD_ID which gave the error.

    Then I get an error where kettle tries to update a numeric field with a null value (this is allowed in the column definition).

    All in all, I have tried fixed format, csv, text and can't get ANY kind of file input to update an Oracle table. As this is a basic ETL function, I am not sure what I am doing wrong.

    Help.


    Mark

  2. #2
    Join Date
    May 2006
    Posts
    4,882

    Default

    Attach your .ktrs and your input/output files. + DDL of the 1 oracle table.

    Regards,
    Sven

  3. #3
    Join Date
    Jan 2008
    Posts
    3

    Default Files

    Hi,

    here are the files requested.

    The output test produced an output leads3.txt (OK).

    The input test cannot read leads3 back into the unrealised_leads table.

    I am sure I am doing something dumb, but just can't see it. The problem seesm to come when inserting records into the table (can't insert....).

    Cheers,


    Mark
    Attached Files Attached Files
    Last edited by kingbeard; 01-05-2008 at 02:40 PM.

  4. #4
    Join Date
    May 2006
    Posts
    4,882

    Default

    First try switching from ODBC to "Natiive (JDBC)"... ODBC is known to have some problems

    If it still fails can you provide the real DDL from oracle, not the DDL made by PDI.

    With your DDL, input file, and input.ktr all works fine for me (using JDBC).

    Regards,
    Sven

  5. #5
    Join Date
    Jan 2008
    Posts
    3

    Default

    Dear Sen,

    first of all thanks for your time on this.

    I tried using JDBC. I made the connection OK and it tested OK, but when I tried to run I get

    2008/01/05 19:45:53 - Table output.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : An error occurred intialising this step:
    2008/01/05 19:45:53 - Table output.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : Error occured while trying to connect to the database
    2008/01/05 19:45:53 - Table output.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) :
    2008/01/05 19:45:53 - Table output.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : Error connecting to database: (using class oracle.jdbc.driver.OracleDriver)
    2008/01/05 19:45:53 - Table output.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : Io exception: The Network Adapter could not establish the connection
    2008/01/05 19:45:53 - Table output - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : Error initializing step [Table output]
    2008/01/05 19:45:53 - input test - Step [Text file input.0] initialized flawlessly.

    It did this, even though the test connection works (and get schemas, etc..). This is why I was using ODBC.

    I have changed the java drivers (as suggested in the manual). This also gives the same problem.

    Any ideas?

    Maybe the problem is with my Oracel setup (access rights, etc??). In any case, maybe I'll try with MySQL and see if this is different.

    Cheers,


    Mark
    Last edited by kingbeard; 01-05-2008 at 04:27 PM.

  6. #6
    Join Date
    May 2006
    Posts
    4,882

    Default

    strange, the test and the actual opening should have the same effect. Open the connection from the table output and check whether the connection in there is ok.

    Regards,
    Sven

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.