Re: [SPAM] COPY command & binary format

2016-05-10 14:47 GMT+02:00 Moreno Andreo <moreno(dot)andreo(at)evolu-s(dot)it>:

> Il 10/05/2016 13:38, Nicolas Paris ha scritto:
>
> 2016-05-10 13:04 GMT+02:00 Moreno Andreo <moreno(dot)andreo(at)evolu-s(dot)it>:
>
>> 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.