Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: DI - Write to DB doesn't

  1. #1

    Default DI - Write to DB doesn't

    Hi

    I am working through the examples in order to get familiar with the Pentaho product suite. In general I'm pretty happy with what I see.

    Right now I'm exploring DI. I am working through the first example where a csv file that is missing some zip codes has the missing zip codes added and the results are written out to a DB.

    In the example the H2 DB is used - I tried this, everything appeared to work, except the data was not written to the DB. I checked the data and by and large it was OK (a few string values were longer than was created in the DB).

    In order to get around this problem,I set up the Write To DB step again, this time writing to a table in a new PostgreSQL DB. I checked that the table was correctly created in the DB, but again, the write to the DB failed. I tried reducing the batch size, stopping truncation, but to no avail.

    So, can anybody suggest a way around this, or even the best way of seeing what is causing the error, because I can't identify that.

    Thanks (and Happy Thanksgiving!)

    R+C

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

    Default

    What's the error message?
    Have you connected successfully to the DB?
    -- Mick --

  3. #3

    Default

    I am happy that the connection with the DB is OK. DI created the table in the DB as I worked through the problem, it just doesn't write data to it. I have previewed the data and it looks OK

    Here is the end of the unhelpful error report

    2013/11/29 15:50:30 - Write To Database.0 - at java.lang.Thread.run(Unknown Source)
    2013/11/29 15:50:30 - Write To Database.0 - Caused by: org.pentaho.di.core.exception.KettleDatabaseException:
    2013/11/29 15:50:30 - Write To Database.0 - Error inserting/updating row
    2013/11/29 15:50:30 - Write To Database.0 - ERROR: value too long for type character varying(30)
    2013/11/29 15:50:30 - Write To Database.0 -
    2013/11/29 15:50:30 - Write To Database.0 - at org.pentaho.di.core.database.Database.insertRow(Database.java:1193)
    2013/11/29 15:50:30 - Write To Database.0 - at org.pentaho.di.trans.steps.tableoutput.TableOutput.writeToTable(TableOutput.java:284)
    2013/11/29 15:50:30 - Write To Database.0 - ... 3 more
    2013/11/29 15:50:30 - Write To Database.0 - Caused by: org.postgresql.util.PSQLException: ERROR: value too long for type character varying(30)
    2013/11/29 15:50:30 - Write To Database.0 - at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2101)
    2013/11/29 15:50:30 - Write To Database.0 - at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1834)
    2013/11/29 15:50:30 - Write To Database.0 - at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
    2013/11/29 15:50:30 - Write To Database.0 - at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:510)
    2013/11/29 15:50:30 - Write To Database.0 - at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:386)
    2013/11/29 15:50:30 - Write To Database.0 - at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:332)
    2013/11/29 15:50:30 - Write To Database.0 - at org.pentaho.di.core.database.Database.insertRow(Database.java:1153)
    2013/11/29 15:50:30 - Write To Database.0 - ... 4 more
    2013/11/29 15:50:30 - Write To Database.0 - Finished processing (I=0, O=111, R=112, W=111, U=0, E=1)
    2013/11/29 15:50:30 - Getting Started Transformation - Getting Started Transformation
    2013/11/29 15:50:30 - Getting Started Transformation - Getting Started Transformation
    2013/11/29 15:50:30 - Lookup Missing Zips.0 - Finished processing (I=0, O=0, R=21379, W=0, U=0, E=0)
    2013/11/29 15:50:30 - Getting Started Transformation - ERROR (version 5.0.1, build 1 from 2013-10-30_19-53-32 by buildguy) : Errors detected!
    2013/11/29 15:50:30 - Spoon - The transformation has finished!!
    2013/11/29 15:50:30 - Getting Started Transformation - ERROR (version 5.0.1, build 1 from 2013-10-30_19-53-32 by buildguy) : Errors detected!
    2013/11/29 15:50:30 - Getting Started Transformation - ERROR (version 5.0.1, build 1 from 2013-10-30_19-53-32 by buildguy) : Errors detected!

  4. #4
    Join Date
    Nov 2013
    Posts
    19

    Default

    Hi,

    If you're using a 'table output component', is "Use batch update for inserts" option checked ?? Usually you get more precise logs when it is not.

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

    Default

    Quote Originally Posted by uberseehandel View Post
    2013/11/29 15:50:30 - Write To Database.0 - ERROR: value too long for type character varying(30)
    PDI is actually very polite.
    Since you've provided data that is tripping an error in the DB, it does a rollback!

    Fix your data so that you don't have any data longer than 30 characters (Sorry - I can't tell you from your log which column you need to fix...) and it should write correctly.
    **THIS IS A SIGNATURE - IT GETS POSTED ON (ALMOST) EVERY POST**
    I'm no expert.
    Take my comments at your own risk.

    PDI user since PDI 3.1
    PDI on Windows 7 & Linux

    Please keep in mind (and this may not apply to this thread):
    No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.
    Signature Updated: 2014-06-30

  6. #6
    Join Date
    Nov 2013
    Posts
    19

    Default

    You seem to use a csv component, if your preview seem right maybe your data need to be trimmed. In PDI, at least in the version I'm using, when dealing with strings in particular, there are extra spaces added to the field's value and don't ask me why but you can trim directly from the csv or text output component.

  7. #7

    Default

    Thank you for your reply.

    I went back and reworked the exercise.

    This time, however, I didn't just sample the first 1,000 records, I sampled the entire population. Then I created the table in the DB and the write proceeded correctly, as may be seen below -


    2013/11/29 17:33:16 - Spoon - Transformation opened.
    2013/11/29 17:33:16 - Spoon - Launching transformation [Getting Started Transformation]...
    2013/11/29 17:33:16 - Spoon - Started the transformation execution.
    2013/11/29 17:33:16 - Getting Started Transformation - Dispatching started for transformation [Getting Started Transformation]
    2013/11/29 17:33:16 - Write To Database.0 - Connected to database [sample data] (commit=1000)
    2013/11/29 17:33:16 - Read Postal Codes.0 - Opening file: file:///C:/Program Files/pentaho/design-tools/data-integration/samples/transformations/files/Zipssortedbycitystate.csv
    2013/11/29 17:33:16 - Read Sales Data.0 - Opening file: file:///C:/Program Files/pentaho/design-tools/data-integration/samples/transformations/files/sales_data.csv
    2013/11/29 17:33:28 - Read Sales Data.0 - Finished processing (I=2824, O=0, R=0, W=2823, U=1, E=0)
    2013/11/29 17:33:28 - Filter Missing Zips.0 - Finished processing (I=0, O=0, R=2823, W=2823, U=0, E=0)
    2013/11/29 17:33:28 - Read Postal Codes.0 - Finished processing (I=21380, O=0, R=0, W=21379, U=1, E=0)
    2013/11/29 17:33:28 - Lookup Missing Zips.0 - Finished processing (I=0, O=0, R=21455, W=76, U=0, E=0)
    2013/11/29 17:33:28 - Prepare Field Layout.0 - Finished processing (I=0, O=0, R=76, W=76, U=0, E=0)
    2013/11/29 17:33:28 - Write To Database.0 - Finished processing (I=0, O=2823, R=2823, W=2823, U=0, E=0)
    2013/11/29 17:33:28 - Spoon - The transformation has finished!!

    I'm still impressed.

    and thank you everybody who replied on a day when you are struggling with turkey and....stuff

    R+C

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.