MonetDB Blob Inserts
Hi Guys,
I don't seem to be able to INSERT into a blob field is there any information how to achieve this.
Regards,
Brian Hood
On 24/06/17 21:31, Brian Hood wrote:
Hi Guys,
I don't seem to be able to INSERT into a blob field is there any information how to achieve this.
Regards,
Brian Hood _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Normally that would be something like: INSERT INTO t VALUES ('0102030405060708090a0b0c0d0e0f'); where the value being inserted is no more than a list of hexadecimal digits with en even length (two digits per octet).
Hi Sjoerd,
Thank you for this is makes sense now.
However essentially to INSERT data into a BLOB you are going to have convert the data before inserting it into database, which could be expensive in compute time.
Regards,
Brian Hood
On Mon, Jun 26, 2017 at 9:19 AM, Sjoerd Mullender sjoerd@acm.org wrote:
On 24/06/17 21:31, Brian Hood wrote:
Hi Guys,
I don't seem to be able to INSERT into a blob field is there any information how to achieve this.
Regards,
Brian Hood _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Normally that would be something like: INSERT INTO t VALUES ('0102030405060708090a0b0c0d0e0f'); where the value being inserted is no more than a list of hexadecimal digits with en even length (two digits per octet).
-- Sjoerd Mullender
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Hi Sjoerd,
Does MonetDB support BINARY BULK LOAD into a blob column? And if not, is there a reason?
Thanks, Anton
On Sat, Jul 8, 2017 at 9:02 PM, Brian Hood brianh6854@googlemail.com wrote:
Hi Sjoerd,
Thank you for this is makes sense now.
However essentially to INSERT data into a BLOB you are going to have convert the data before inserting it into database, which could be expensive in compute time.
Regards,
Brian Hood
On Mon, Jun 26, 2017 at 9:19 AM, Sjoerd Mullender sjoerd@acm.org wrote:
On 24/06/17 21:31, Brian Hood wrote:
Hi Guys,
I don't seem to be able to INSERT into a blob field is there any information how to achieve this.
Regards,
Brian Hood _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Normally that would be something like: INSERT INTO t VALUES ('0102030405060708090a0b0c0d0e0f'); where the value being inserted is no more than a list of hexadecimal digits with en even length (two digits per octet).
-- Sjoerd Mullender
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
On 15/09/17 06:25, Anton Kravchenko wrote:
Hi Sjoerd,
Does MonetDB support BINARY BULK LOAD into a blob column? And if not, is there a reason?
Yes it does:
create table t (i int, b blob, s clob); copy 1 records into t from stdin; 10|0102030405060708090a0b0c0d0e0f|string select * from t;
You can of course use different delimiters, copy from an external file, etc.
Thanks, Anton
On Sat, Jul 8, 2017 at 9:02 PM, Brian Hood <brianh6854@googlemail.com mailto:brianh6854@googlemail.com> wrote:
Hi Sjoerd, Thank you for this is makes sense now. However essentially to INSERT data into a BLOB you are going to have convert the data before inserting it into database, which could be expensive in compute time. Regards, Brian Hood On Mon, Jun 26, 2017 at 9:19 AM, Sjoerd Mullender <sjoerd@acm.org <mailto:sjoerd@acm.org>> wrote: On 24/06/17 21:31, Brian Hood wrote: > Hi Guys, > > I don't seem to be able to INSERT into a blob field is there any > information how to achieve this. > > Regards, > > Brian Hood > _______________________________________________ > users-list mailing list > users-list@monetdb.org <mailto:users-list@monetdb.org> > https://www.monetdb.org/mailman/listinfo/users-list <https://www.monetdb.org/mailman/listinfo/users-list> > Normally that would be something like: INSERT INTO t VALUES ('0102030405060708090a0b0c0d0e0f'); where the value being inserted is no more than a list of hexadecimal digits with en even length (two digits per octet). -- Sjoerd Mullender _______________________________________________ users-list mailing list users-list@monetdb.org <mailto:users-list@monetdb.org> https://www.monetdb.org/mailman/listinfo/users-list <https://www.monetdb.org/mailman/listinfo/users-list> _______________________________________________ users-list mailing list users-list@monetdb.org <mailto:users-list@monetdb.org> https://www.monetdb.org/mailman/listinfo/users-list <https://www.monetdb.org/mailman/listinfo/users-list>
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
great, thanks!
On Fri, Sep 15, 2017 at 1:28 AM, Sjoerd Mullender sjoerd@acm.org wrote:
On 15/09/17 06:25, Anton Kravchenko wrote:
Hi Sjoerd,
Does MonetDB support BINARY BULK LOAD into a blob column? And if not, is there a reason?
Yes it does:
create table t (i int, b blob, s clob); copy 1 records into t from stdin; 10|0102030405060708090a0b0c0d0e0f|string select * from t;
You can of course use different delimiters, copy from an external file, etc.
Thanks, Anton
On Sat, Jul 8, 2017 at 9:02 PM, Brian Hood <brianh6854@googlemail.com mailto:brianh6854@googlemail.com> wrote:
Hi Sjoerd, Thank you for this is makes sense now. However essentially to INSERT data into a BLOB you are going to have convert the data before inserting it into database, which could be expensive in compute time. Regards, Brian Hood On Mon, Jun 26, 2017 at 9:19 AM, Sjoerd Mullender <sjoerd@acm.org <mailto:sjoerd@acm.org>> wrote: On 24/06/17 21:31, Brian Hood wrote: > Hi Guys, > > I don't seem to be able to INSERT into a blob field is thereany
> information how to achieve this. > > Regards, > > Brian Hood > _______________________________________________ > users-list mailing list > users-list@monetdb.org <mailto:users-list@monetdb.org> > https://www.monetdb.org/mailman/listinfo/users-list <https://www.monetdb.org/mailman/listinfo/users-list> > Normally that would be something like: INSERT INTO t VALUES ('0102030405060708090a0b0c0d0e0f'); where the value being inserted is no more than a list ofhexadecimal
digits with en even length (two digits per octet). -- Sjoerd Mullender _______________________________________________ users-list mailing list users-list@monetdb.org <mailto:users-list@monetdb.org> https://www.monetdb.org/mailman/listinfo/users-list <https://www.monetdb.org/mailman/listinfo/users-list> _______________________________________________ users-list mailing list users-list@monetdb.org <mailto:users-list@monetdb.org> https://www.monetdb.org/mailman/listinfo/users-list <https://www.monetdb.org/mailman/listinfo/users-list>
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
-- Sjoerd Mullender
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Hi Sjoerd,
I don't quite understand why do we need to convert original binary into hex string - that takes extra time for conversion and [even worse] appears to double the size of the original binary data...
p.s. I developed BLOBimport C UDF that takes as input already existing Monet table and outputs Monet blob table [by converting its original table columns from Monet binary to our in-house binary data format].
sql_column *col1 = mvc_bind_column(m, blob_tbl, "v_blob"); char *data; size_t blob_len = reclen; BAT *tmp1 = COLnew(0, TYPE_sqlblob, (BUN)nrow, TRANSIENT); blob *ele_blob; ele_blob = GDKmalloc(blobsize(reclen)); ele_blob->nitems = reclen; jda = 0;
for (irow=0; irow < nrow; irow++) { memcpy(ele_blob->data, &da[jda], blob_len); //char *da contains original binary data records [with fixed length] BUNappend(tmp1, ele_blob, FALSE); jda += reclen; } mvc_append_column(m->session->tr, col1, tmp1); mvc_commit(m, 0, tr->name); bat_destroy(tmp1);
Also I developed another C UDF BLOBexport that takes blob table as input and creates [in-house binary data format] output file.
Thanks, Anton
On Fri, Sep 15, 2017 at 8:00 AM, Anton Kravchenko < kravchenko.anton86@gmail.com> wrote:
great, thanks!
On Fri, Sep 15, 2017 at 1:28 AM, Sjoerd Mullender sjoerd@acm.org wrote:
On 15/09/17 06:25, Anton Kravchenko wrote:
Hi Sjoerd,
Does MonetDB support BINARY BULK LOAD into a blob column? And if not, is there a reason?
Yes it does:
create table t (i int, b blob, s clob); copy 1 records into t from stdin; 10|0102030405060708090a0b0c0d0e0f|string select * from t;
You can of course use different delimiters, copy from an external file, etc.
Thanks, Anton
On Sat, Jul 8, 2017 at 9:02 PM, Brian Hood <brianh6854@googlemail.com mailto:brianh6854@googlemail.com> wrote:
Hi Sjoerd, Thank you for this is makes sense now. However essentially to INSERT data into a BLOB you are going to have convert the data before inserting it into database, which could be expensive in compute time. Regards, Brian Hood On Mon, Jun 26, 2017 at 9:19 AM, Sjoerd Mullender <sjoerd@acm.org <mailto:sjoerd@acm.org>> wrote: On 24/06/17 21:31, Brian Hood wrote: > Hi Guys, > > I don't seem to be able to INSERT into a blob field is thereany
> information how to achieve this. > > Regards, > > Brian Hood > _______________________________________________ > users-list mailing list > users-list@monetdb.org <mailto:users-list@monetdb.org> > https://www.monetdb.org/mailman/listinfo/users-list <https://www.monetdb.org/mailman/listinfo/users-list> > Normally that would be something like: INSERT INTO t VALUES ('0102030405060708090a0b0c0d0e0f'); where the value being inserted is no more than a list ofhexadecimal
digits with en even length (two digits per octet). -- Sjoerd Mullender _______________________________________________ users-list mailing list users-list@monetdb.org <mailto:users-list@monetdb.org> https://www.monetdb.org/mailman/listinfo/users-list <https://www.monetdb.org/mailman/listinfo/users-list> _______________________________________________ users-list mailing list users-list@monetdb.org <mailto:users-list@monetdb.org> https://www.monetdb.org/mailman/listinfo/users-list <https://www.monetdb.org/mailman/listinfo/users-list>
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
-- Sjoerd Mullender
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
participants (3)
-
Anton Kravchenko -
Brian Hood -
Sjoerd Mullender