Re: [SPAM] COPY command & binary format

[Nicolas Paris <niparisco@xxxxxxxxx>]

2016-05-10 14:47 GMT+02:00 Moreno Andreo <moreno.andreo@xxxxxxxxxx>:

Il 10/05/2016 13:38, Nicolas Paris ha scritto:

2016-05-10 13:04 GMT+02:00 Moreno Andreo <moreno.andreo@xxxxxxxxxx>:

Il 10/05/2016 12:56, Nicolas Paris ha scritto:

Hello,

What is the way to build a binary format (instead of a csv) ? Is there specification for this file ?
http://www.postgresql.org/docs/9.5/static/sql-copy.html

I always create binary files with
COPY table TO 'path/to/file' WITH BINARY

 

​ Fine, this works in this way :
postgresql -> binary

binary -> postgresql

The way I want is :

csv -> binary -> postgresql

Is this just to be quicker or are you going to add some business logic while converting CSV data?
As you mentioned ETL, I assume the second, as I don't think that converting CSV to binary and then loading it to PostgreSQL will be more convenient than loading directly from CSV... as quicker as it can be, you have anyway to load data from CSV.

​
Right, ETL process means huge business logic.

get the data (csv or other) -> transform it -> produce a binary -> copy from binary from stdin ​

Producing 100GO CSVs, is a waste of time.

 

Binary file format is briefly described in the last part of the doc you linked, under "Binary format", and there's also reference to source files.

And if possible, transforming csv to binary throught java​.

This is beyond my knowledge, ATM. I'm just starting with Java and JDBC is still in the TODO list, sorry... :-)

Cheers
Moreno.-

​Documentation explains a bit. Moreover, I have found a detailled answer here :

​http://stackoverflow.com/questions/14242117/java-library-to-write-binary-format-for-postgres-copy​

My ultimate goal is to encapsulate it in a Talend component. (talend is an open-source java based ETL software).

Thanks, I ll keep you aware.