Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: Importing Null Values from SQL

  1. #1
    Join Date
    Sep 2012
    Posts
    3

    Default Importing Null Values from SQL

    Hello,

    I am extremely new to Pentaho and I am trying to transform a table from SQL and export it to another table to a SQL database. However, I get the error that a certain field cannot be null and my transformation does not work.

    I am using Kettle-Spoon 4.2.0-stable. Any help would be really appreciated.

    Thank you!

  2. #2

    Default

    This will normaly work fine. Are you shure that your target table does allow null values in those fields? And please send your log output.

  3. #3
    Join Date
    Sep 2012
    Posts
    3

    Default

    Hi Michael,

    Where would I go to determine if my target table allows null values?

    Here is my log file:


    2012/09/19 13:42:42 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Because of an error, this step can't continue:
    2012/09/19 13:42:42 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : org.pentaho.di.core.exception.KettleException:
    2012/09/19 13:42:42 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Error batch inserting rows into table [stage_mag_newsletter_subscriber].
    2012/09/19 13:42:42 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Errors encountered (first 10):
    2012/09/19 13:42:42 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Column 'subscriber_email' cannot be null
    2012/09/19 13:42:42 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) :
    2012/09/19 13:42:42 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) :
    2012/09/19 13:42:42 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Error updating batch
    2012/09/19 13:42:42 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Column 'subscriber_email' cannot be null
    2012/09/19 13:42:42 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) :
    2012/09/19 13:42:42 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) :
    2012/09/19 13:42:42 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.trans.steps.tableoutput.TableOutput.writeToTable(TableOutput.java:393)
    2012/09/19 13:42:42 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.trans.steps.tableoutput.TableOutput.processRow(TableOutput.java:117)
    2012/09/19 13:42:42 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.trans.step.RunThread.run(RunThread.java:40)
    2012/09/19 13:42:42 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at java.lang.Thread.run(Unknown Source)
    2012/09/19 13:42:42 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Caused by: org.pentaho.di.core.exception.KettleDatabaseBatchException:
    2012/09/19 13:42:42 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Error updating batch
    2012/09/19 13:42:42 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Column 'subscriber_email' cannot be null
    2012/09/19 13:42:42 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) :
    2012/09/19 13:42:42 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.trans.steps.tableoutput.TableOutput.writeToTable(TableOutput.java:309)
    2012/09/19 13:42:42 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : ... 3 more
    2012/09/19 13:42:42 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Caused by: java.sql.BatchUpdateException: Column 'subscriber_email' cannot be null
    2012/09/19 13:42:42 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at com.mysql.jdbc.ServerPreparedStatement.executeBatchSerially(ServerPreparedStatement.java:796)
    2012/09/19 13:42:42 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1449)
    2012/09/19 13:42:42 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.trans.steps.tableoutput.TableOutput.writeToTable(TableOutput.java:304)
    2012/09/19 13:42:42 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : ... 3 more
    2012/09/19 13:42:42 - Table output.0 - Finished processing (I=0, O=999, R=1000, W=0, U=0, E=1)
    2012/09/19 13:42:42 - Spoon - The transformation has finished!!
    2012/09/19 13:42:42 - trans_load_stag_newsletter_subscriber - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Errors detected!
    2012/09/19 13:42:42 - trans_load_stag_newsletter_subscriber - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Errors detected!
    2012/09/19 13:42:42 - trans_load_stag_newsletter_subscriber - trans_load_stag_newsletter_subscriber
    2012/09/19 13:42:42 - trans_load_stag_newsletter_subscriber - trans_load_stag_newsletter_subscriber

  4. #4
    Join Date
    Jun 2011
    Posts
    12

    Default

    As the errors says:

    The column 'subscriber_email' does not allow NULL values. (other columns in the table might allow NULL values, but this one doesn't)

    Apparently some rows in your data set do not have a subscriber_email, thus NULL. And the column subscriber_email in your target table does not allow that.

    You can check this by looking at the table definition in MySQL via the command: SHOW CREATE TABLE tablename , or ask your DBA about it.

    One way to solve this is by adding a dummy value to the column subscriber_email in your ETL for the rows it's empty, so it is no longer NULL.

    Or you can ask your DBA to allow NULL values for the column 'subscriber_email', since you only have read only rights (SELECT) and cannot edit it yourself.

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

    Default

    A subscriber without an email address doesn't really make sense to me.
    Most likely you forgot to include subscriber_email in your field mapping.

    If there is actually an input row missing a value for subscriber_email, that would be a mistake on behalf of the webservice implementing Self-Service Registration, I think. You should filter and log the rows without email, so your remaining rows get through to the output table.
    So long, and thanks for all the fish.

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.