[resolved] Input File not Found - tPostgresqlBulkExec

[Anonymous]

Any suggestions about any part of the job(s) is much appreciated! (screenshots attached)
Job
tpostgresqlinput -- tpostgresqloutputbulk -- tpostgresqlbulkexec
|
|----------------- tfileInputDelimited -- tpostgresqlOutput
Flow
read all rows from table and pass to outputbulk which creates a delimited file which is input to tpostgresbulkexec.
The file is created successfully, but tpostgresbulkexec throws error:
Exception in component tPostgresqlBulkExec_1
org.postgresql.util.PSQLException: ERROR: could not open file "D:/sandbox/projects/ETL/wjb_dw/stage1/holiday.txt" for reading: No such file or directory
The second set of components in job employ a tfileinputdelimited to read this delimited file and truncate/insert to the same table the bulk exec is targeting. It has no problem reading the file and inserting to the table.
I may be misunderstanding the use/intent of the outputBulk and bulkExec components.. ?
Thanks in advance..
Mark

[Anonymous]

I uploaded 5 PDF files but it doesn't look like they made it..
I'll convert them to jpeg and try again.. sorry about the delay..
Mark

[Anonymous]

Hi
You'd better create job like this.
tpostgresqlinput -- tpostgresqloutputbulk
|
|------------------tpostgresqlbulkexec
|
|----------------- tfileInputDelimited -- tpostgresqlOutput
Regards,
Pedro

[Anonymous]

Hi Pedro,
I'll give that a go and see if it changes whether the final bulk component sees the file created in the previous step..
Note that the tfileInputDelimited -- tpostgresqlOutput is only there to prove the input file to the final BULK component really is available. I would not likely use a component that is insert oriented as it's too inefficient for the number of rows in the restore file..
I'd really like to understand why the BULK components don't load the data! )
Thanks
Mark
UPDATE:
Hi Pedro..the suggested connection change(s) made no difference.. the BULK component still cannot see the file. I assume that you may have been giving me a best practice rather than a solution to my problem, which I appreciate, but without an explanation, it's unclear why I would make that change. yet, I appreciate your time!
Thanks again..
Mark

[Anonymous]

Ok.. so I've changed the approach. Rather than use the 2 components (tPostgresqlOutputBulk, tPostgresqlBuldExec), I've built another job using 1 component (tPostgresqlOutputBulkExec. The docs indicate that if there's no need for transformation (ie, tMap) of the input data prior to load, then use the single component.
The job now looks like this:
tPostgresInput -- tPostgresqlOutputBulkExec
tPostgresInput retrieves 50 rows, which is what I expect.
tPostgresOutputBuldExec creates the file in "D:/Talend-4.2.3/studio/Talend-All-r67267-V4.2.3/workspace/holiday.txt" and the data is what I expect.
..but throws this error:
Exception in component tPostgresqlOutputBulkExec_1_tPBE
org.postgresql.util.PSQLException: ERROR: could not open file "D:/Talend-4.2.3/studio/Talend-All-r67267-V4.2.3/workspace/holiday.txt" for reading: No such file or directory
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1592)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1327)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:192)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:451)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:336)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:328)
at sandbox_marksutfin_project.bi_holidays_0_2.bi_holidays.tPostgresqlInput_1Process(bi_holidays.java:1059)
at sandbox_marksutfin_project.bi_holidays_0_2.bi_holidays.runJobInTOS(bi_holidays.java:3873)
at sandbox_marksutfin_project.bi_holidays_0_2.bi_holidays.main(bi_holidays.java:3649)
How can the same component that creates the file not be able to see it to open / read it?
What am I missing?
Thanks
Mark

[Anonymous · Accepted solution]

Found this posted in a message from 2009...
"Files named in a COPY command are read or written directly by the server, not by the client application. Therefore, they must reside on or be accessible to the database server machine, not the client. They must be accessible to and readable or writable by the PostgreSQL user (the user ID the server runs as), not the client. COPY naming a file is only allowed to database superusers, since it allows reading or writing any file that the server has privileges to access. "
So to recap if you want to carry out a bulkexec on a remote database you must copy the file to the server hosting that database and then in the bulkexec component for the filename you must provide the path to the file as it appears on the server.

[Anonymous]

Found this posted in a message from 2009...
"Files named in a COPY command are read or written directly by the server, not by the client application. Therefore, they must reside on or be accessible to the database server machine, not the client. They must be accessible to and readable or writable by the PostgreSQL user (the user ID the server runs as), not the client. COPY naming a file is only allowed to database superusers, since it allows reading or writing any file that the server has privileges to access. "
So to recap if you want to carry out a bulkexec on a remote database you must copy the file to the server hosting that database and then in the bulkexec component for the filename you must provide the path to the file as it appears on the server.

Thanks .