Loading blob (bytea) data

[assafm@vision.bi]

Hi,
I'm loading data from mysql to GP.
I have this table in mysql:
CREATE TABLE images
(id int,
image blob)

and would like to load this data into GP.

CREATE TABLE images
(id integer,
image bytea)


For non-blob tables I usually do:
export mysql data to csv file --> load to gp from csv file using COPY or EXTERNAL TABLE

obviously, it doesn't work with binary data.

Please advise

[ads]

How does the mysql csv export look like? Still every binary object in a single line?

[assafm@vision.bi]

the csv is messed up - the binary object breaks into multiple lines

[ads]

Bad. You probably need a small script to transfer the data.

[carlo.ascani@2ndquadrant.it]

Bad. You probably need a small script to transfer the data.

I agree with that.

Take a look at External Web Tables, they are explained on chapter 12 of the Greenplum Admin Guide.

Cheers,

[carlo.ascani@2ndquadrant.it]

FYI: I have written a blog article about Web External Tables:
http://blog.2ndquadrant.com/en/2012/...eb-tables.html

That's a very simple example, you would be able to do the same with mysql and BLOBs (...I am thinking about mysqldump...)

Cheers,

[ads]

For inserting binary data (or any kind of data which is not exactly split by lineends) you need to escape the input data, right?