Hitachi Vantara Pentaho Community Forums
Results 1 to 17 of 17

Thread: Postgresql Bulk Load throws the below error

  1. #1
    Join Date
    Dec 2010
    Posts
    193

    Default Postgresql Bulk Load throws the below error

    When I try to do a bulk load using postgresql Bulk Loader step, it throws the following error , Help me.

    INFO 16-08 16:35:36,723 - test - Transformation has allocated 2 threads and 1 rowsets.

    INFO 16-08 16:35:36,724 - PostgreSQL Bulk Loader - Starting to run...

    INFO 16-08 16:35:36,956 - PostgreSQL Bulk Loader - Executing command: /usr/local/greenplum-clients-4.1.1.0-build-4/bin/psql -U u1celmet1 -h 10.237.69.72 -p 6432 d1caap

    INFO 16-08 16:35:36,964 - PostgreSQL Bulk Loader - ERROR {0} /usr/local/greenplum-clients-4.1.1.0-build-4/bin/psql: error while loading shared libraries: libpq.so.5: cannot open shared object file: No such file or directory

    test.ktrINFO 16-08 16:35:36,970 - PostgreSQL Bulk Loader - Launching command: COPY s1celm01.ic_m_elm_f_mnth_tgt_pop_test ( prj_id, on_off_id, customer_id, practice_id, loc_id, month_id, desg_id, dept_id, grade_id, associate_id, associate_doj, associate_type, customer_ldesc, crnt_sbu1_desc, crnt_sbu2_desc, onsite_offshore, grade_group_desc, country, location_group, elm_practice_name, elm_practice_type, associate_name, project_mgr_id, project_mgr_name, supervisor_id, supervisor_name ) FROM STDIN WITH CSV DELIMITER AS ',' QUOTE AS '"';


    ERROR 16-08 16:35:37,008 - PostgreSQL Bulk Loader - Error in step

    ERROR 16-08 16:35:37,009 - PostgreSQL Bulk Loader - org.pentaho.di.core.exception.KettleException:

    Error serializing rows of data to the psql command

    Broken pipe


    at org.pentaho.di.trans.steps.pgbulkloader.PGBulkLoader.writeRowToPostgres(PGBulkLoader.java:428)

    at org.pentaho.di.trans.steps.pgbulkloader.PGBulkLoader.processRow(PGBulkLoader.java:298)

    at org.pentaho.di.trans.step.RunThread.run(RunThread.java:40)

    at java.lang.Thread.run(Unknown Source)

    Caused by: java.io.IOException: Broken pipe

    at java.io.FileOutputStream.writeBytes(Native Method)

    at java.io.FileOutputStream.write(Unknown Source)

    at java.io.BufferedOutputStream.flushBuffer(Unknown Source)

    at java.io.BufferedOutputStream.write(Unknown Source)

    at java.io.FilterOutputStream.write(Unknown Source)

    at org.pentaho.di.trans.steps.pgbulkloader.PGBulkLoader.writeRowToPostgres(PGBulkLoader.java:343)

    ... 3 more

  2. #2
    Join Date
    Jul 2009
    Posts
    476

    Default

    When you run /usr/local/greenplum-clients-4.1.1.0-build-4/bin/psql from the command line, as the same user that runs ETL, are you able to connect to the database, or do you get the same error about the missing libpq.so.5 shared object file?

  3. #3
    Join Date
    Dec 2010
    Posts
    193

    Default

    Robj,

    Lemme clear what I am doing. First the attached transformation is created in Local Machine(Windows), with all the paths mentioned will be Server paths(Unix System Server). Then we move the ktr file to the server and run the transformation via Scripts in the Putty tool. The script will look as below,

    export KETTLE_HOME=/1CAAP/1C_ELM/ELM/config/

    echo $KETTLE_HOME

    cd /home/pentaho/pentaho/design-tools/data-integration/

    pwd

    export Current_Date=`date +%Y%m%d`

    ./pan.sh -file="/1CAAP/1C_ELM/ELM/etl/test.ktr" -level=Detailed >>/1CAAP/1C_ELM/ELM/log/job_bulk$Current_Date.txt


     

  4. #4
    Join Date
    Jul 2009
    Posts
    476

    Default

    Satran,

    I understand what you're doing. I'm asking you to go to your Unix system server, log in as the same user that runs your transformation, and try to run /usr/local/greenplum-clients-4.1.1.0-build-4/bin/psql. If you get the same error message about libpq.so.5 when you run psql from the command line, then you need to fix your Postgresql installation on the Unix server, confirm that psql works from the command line, and then try the transformation again.

    So please let us know whether psql works on the Unix server from the command line.

    Rob

  5. #5
    Join Date
    Dec 2010
    Posts
    193

    Default

    Hi Robj,

    Sorry for the delay in response. Please find the screenshot . We are using the same user to run the PDI and in command line psql execution also.Name:  cmd execution of psql.jpg
Views: 158
Size:  27.2 KB

  6. #6
    Join Date
    Dec 2010
    Posts
    193

    Default

    Also, I have one more doubt. How bulk loading is being processed by Bulk Loader steps (postgresql Bulk loader, Greenplum Load, Green Plum Bulk Loader) ??? . I mean whether each time an external or temp table is created ? Any sort of memory concerns it will create ?

  7. #7
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    Good point: why on earth are you using the PostgreSQL bulk loader when you have two (2!) Greenplum bulk loader steps available?

  8. #8
    Join Date
    Dec 2010
    Posts
    193

    Default

    Matt ,

    Waiting for you only. In both cases , I am getting the same error now. That is .,

    PostgreSQL Bulk Loader - ERROR {0} /usr/local/greenplum-clients-4.1.1.0-build-4/bin/psql: error while loading shared libraries: libpq.so.5: cannot open shared object file: No such file or directory

    INFO 21-08 17:41:50,763 - Greenplum Load - ERROR>gpload was unable to import The PyGreSQL Python module (pg.py) - libpq.so.5: cannot open shared object file: No such file or directory

    How to rectify this ?

    And clarify me , how green plum load step works. Because there is a myth here, that it will create temporary external table which remains until the cache is cleared and in turn it will affect performance . Have your say .. Please


  9. #9
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    It's an installation or permissions problem, you need to fix this yourself. Perhaps you can contact Greenplum for assistance.

    http://wiki.pentaho.com/display/EAI/Greenplum

    EMC spent a long time writing documentation, it would really be a shame if nobody read it.

  10. #10
    Join Date
    Dec 2010
    Posts
    193

    Default

    We have read this document and other EMC documents on this more times Matt. Just working the error to rectify. But why no answer regarding the external table s ?
    For that also I need to go to EMC forums ??

  11. #11
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    Of-course, this is not a Greenplum support channel.

  12. #12
    Join Date
    Dec 2010
    Posts
    193

    Default

    Great Escape. But in Pentaho you have Green Plum Load Step and there are properties for that. Anyways I have posted the query in Green Plum Portal . Thanks Matt.

  13. #13
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    At the same time, Pentaho and Greenplum are partners so I'm sure you could also contact Pentaho support if you don't like the answers given over here for free. I'm sure they would be happy to help you out.

  14. #14
    Join Date
    Jul 2009
    Posts
    476

    Default

    Satran,

    Thank you for including the screenshot. Your psql version is 8.1.18, which is older than what I've used (8.4 and 9.0). I ran "yum whatprovides *libpq.so.5" and "yum whatprovides *libpq.so.4" on my server, and it told me that libpq.so.5 comes with 8.4 and 9.0, but in 8.1, you get libpq.so.4.

    So the PostgreSQL Bulk Loader step specifically needs libpq.so.5, but your version of psql has libpq.so.4. That bulk load step was moved out of "experimental" status into mainline status only a year or two ago, so I'm not surprised that it requires a more recent version of the PostgreSQL client.

  15. #15
    Join Date
    Dec 2010
    Posts
    193

    Default

    Thank you Rob and Matt. @Rob I will try to find out the version and try toclear the error.@Matt the issue is already with pentaho support

  16. #16
    Join Date
    Dec 2010
    Posts
    193

    Default

    Hi Rob and Matt,

    One more clarification here. In greenplum load step, the control file is generated automatically. How can I change the contents of the control file , say by default the header value is False, what if I want to make it as True????

  17. #17
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    Dear Sathish Kumar,

    If you're already in contact with Pentaho support I would like to ask you to keep the conversation over there, not on this forum.

    Thanks,

    Matt

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.