Hitachi Vantara Pentaho Community Forums
Results 1 to 9 of 9

Thread: PostgreSQL Bulk Loader

  1. #1
    Join Date
    Mar 2014
    Posts
    5

    Default PostgreSQL Bulk Loader

    Hi,

    I cant find any doc/information how to proper use Postgresql Bulk Loader. My question is how could i feed it with rows which are strings direct for copy command.

    for example this is what i need:
    COPY lead_tool.profcopy
    FROM stdin;
    900 abcd 10 f abasd adasd {a} {c,d} fb
    \.


    But the Bulk loader adds: (WITH CSV DELIMITER AS '\t' QUOTE AS 'null'
    COPY lead_tool.profcopy ( profile_identifier, title, fan_count, is_in_analytics, url_key, profile_countries, profile_tags, profile_type ) FROM STDIN WITH CSV DELIMITER AS '\t' QUOTE AS 'null';

    I dont wanna use that in copy command, why CSV? why Quote? I want to pass rows/lines into Loader as tab seperated data.

    Any idea/ suggestions/ ideas?


    thanks

  2. #2
    Join Date
    Mar 2014
    Posts
    5

    Default err

    I make simple transformation using Text file input -> Postgre Bulk Loader, here is the log



    2014/03/21 13:14:18 - PostgreSQL Bulk Loader.0 - ERROR {0} /usr/lib/postgresql/9.1/bin/psql: relocation error: /usr/lib/postgresql/9.1/bin/psql: symbol SSL_CIPHER_get_name, version OPENSSL_1.0.0 not defined in file libssl.so.1.0.0 with link time reference
    2014/03/21 13:14:18 - PostgreSQL Bulk Loader.0 - ERROR (version 5.0.1, build 1 from 2013-10-30_19-53-32 by buildguy) : Error in step
    2014/03/21 13:14:18 - PostgreSQL Bulk Loader.0 - ERROR (version 5.0.1, build 1 from 2013-10-30_19-53-32 by buildguy) : org.pentaho.di.core.exception.KettleException:
    2014/03/21 13:14:18 - PostgreSQL Bulk Loader.0 - Error serializing rows of data to the psql command
    2014/03/21 13:14:18 - PostgreSQL Bulk Loader.0 - Broken pipe
    2014/03/21 13:14:18 - PostgreSQL Bulk Loader.0 -

  3. #3
    Join Date
    Jul 2009
    Posts
    476

    Default

    In your first post, you asked about the WITH CSV_DELIMITER... clause, which is added by the Bulk Loader. I don't think that's an issue, because the Bulk Loader step formats the rows in the transformation stream to that kind of text input.

    In your second post, it looks like a problem with your Postgres client installation. Are you able to connect to the database with psql directly and execute regular commands (select/insert/update/delete)?

    I see that you found the documentation page, which is http://wiki.pentaho.com/display/EAI/...QL+Bulk+Loader.

  4. #4
    Join Date
    Mar 2014
    Posts
    5

    Default

    Quote Originally Posted by robj View Post
    In your first post, you asked about the WITH CSV_DELIMITER... clause, which is added by the Bulk Loader. I don't think that's an issue, because the Bulk Loader step formats the rows in the transformation stream to that kind of text input.

    In your second post, it looks like a problem with your Postgres client installation. Are you able to connect to the database with psql directly and execute regular commands (select/insert/update/delete)?

    I see that you found the documentation page, which is http://wiki.pentaho.com/display/EAI/...QL+Bulk+Loader.

    Hi,

    i can connect with psql and do copy from command line of the system, but when PDI execute psql its error attached before. So I found out that there are two libssl.so.1.0.0 - one is system one - /lib .... another one is placed in /home/pentaho/pentaho/common/lib/libssl.so.1.0.0

    So how could i update pentaho lib or set it as use the system one?


  5. #5
    Join Date
    Jul 2009
    Posts
    476

    Default

    Where did /home/pentaho/pentaho/common/lib/libssl.so.1.0.0 come from? Why is it there? PDI is a Java-based application that has Java libraries in *.jar files. I don't see any native Linux libraries in my PDI application folders, so I don't know why you have one there.

  6. #6
    Join Date
    Mar 2014
    Posts
    5

    Default

    Quote Originally Posted by robj View Post
    Where did /home/pentaho/pentaho/common/lib/libssl.so.1.0.0 come from? Why is it there? PDI is a Java-based application that has Java libraries in *.jar files. I don't see any native Linux libraries in my PDI application folders, so I don't know why you have one there.

    I dont know everything is inside pentaho dir (after install). I found another psql in the dir ~/pentaho/postgresql/bin/psql which looks like work. I dont know why there is own psql and so on .. like those openssllibs...

    whats another problem that http://wiki.pentaho.com/display/EAI/...QL+Bulk+Loader - PGPASS version definitely doesnt work.

  7. #7
    Join Date
    Jul 2009
    Posts
    476

    Default

    If you're having problems with the .pgpass file, you should make sure that it works through psql directly before trying to use it with your transformation. Documentation for .pgpass is here: http://www.postgresql.org/docs/9.3/s...pq-pgpass.html. One "gotcha" on Linux is that the file's permissions need to be set to 600 (rw-------), otherwise it won't work.

    I don't know why everything else was put under /home/pentaho for you. I use the Community Edition of PDI, and installed it by downloading the zip file (or tar bundle, don't remember which) to a shared folder, then extracting it there. There weren't any *.so files, or any other native files in the program directory structure, other than some BASH scripts/Windows batch files.

  8. #8
    Join Date
    Mar 2014
    Posts
    5

    Default

    Quote Originally Posted by robj View Post
    If you're having problems with the .pgpass file, you should make sure that it works through psql directly before trying to use it with your transformation. Documentation for .pgpass is here: http://www.postgresql.org/docs/9.3/s...pq-pgpass.html. One "gotcha" on Linux is that the file's permissions need to be set to 600 (rw-------), otherwise it won't work.

    I don't know why everything else was put under /home/pentaho for you. I use the Community Edition of PDI, and installed it by downloading the zip file (or tar bundle, don't remember which) to a shared folder, then extracting it there. There weren't any *.so files, or any other native files in the program directory structure, other than some BASH scripts/Windows batch files.

    well yes, everything works fine from terminal with .pgpass ... login w/out password. The problem is that it doesnt work from PDI. ;/

  9. #9
    Join Date
    Jul 2009
    Posts
    476

    Default

    If you're able to connect with psql without a password, using the .pgpass file, and that connection is over an IP address, not a Unix socket, then I wonder if those two versions of psql are fighting with each other, that is, they are different versions. You might be using one of them in PDI, but it's loading shared libraries of the other version, and failing. That's just a guess. You can check both versions by running

    <path-to-first-psql>/psql --version
    <path-to-second-psql>/psql --version

    and comparing the output. For example, on one of my machines I get this:

    $ /usr/local/bin/psql --version
    psql (PostgreSQL) 9.3.2

    If they are different versions, try to avoid the one that you are not using when you run psql from the command line (maybe uninstall it?), and be sure to specify the full path to the psql that works in the Bulk Loader step.

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.