how to write binary data into BAT tail?
i want to create a monetdb function to help write my astronomical *binary*processing result into database,this function will load the binary result from memory directly into monetdb to save time, so i defined a UDF binaryCopy() , svom.c int binaryCopy(str columnname, str buffer, int count) { mvc *sql = NULL; sql_table *tmatch = NULL; /*BATs in which the data of the columns of the output table will be stored*/ BAT *tmatch_column = NULL; BUN nr_rows; /* pointer to the tail of above BAT, which is just a normal C-array */ lng *tmatch_column_lng_t = NULL; flt *tmatch_column_flt_t = NULL; /*_bind_table is just a wrapper that tries to see if it can find the table match_table under three default schemas*/ if(!(tmatch = _bind_table(sql, NULL, "testtable"))) //return sql_message("42S02!BINARYLOAD():no such table test"); return 0; //nr_match = 20000; nr_rows = count; /* create BATs for all columns of the output table 'tmatch' */ if(strcmp(columnname,"starid")==0 || strcmp(columnname,"crossid")==0 ||strcmp(columnname,"catid")==0 ) { tmatch_column = BATnew(TYPE_void, TYPE_lng,nr_rows); }else { tmatch_column = BATnew(TYPE_void,TYPE_flt,nr_rows); } /* now tmatch_starid_t points to the first element of the BAT's tails */ tmatch_column_lng_t = (lng*)Tloc(tmatch_column,BUNfirst(tmatch_column)); /* now we can write data into the BAT tail */ if((strcmp(columnname,"starid")==0) || (strcmp(columnname,"crossid")==0) ||(strcmp(columnname,"catid")==0) ) { *memcpy(tmatch_column_lng_t,buffer,nr_rows*sizeof(lng)); //is it right to write binary data into BAT???* }else { memcpy(tmatch_column_flt_t, buffer, nr_rows*sizeof(flt)); } /* Set proper properties for all BATs */ BATsetcount(tmatch_column,nr_rows); BATseqbase(tmatch_column,0); /* sorted:1, column is sorted in ascending order */ tmatch_column->tsorted = 0; /* if the tail is reverse sorted. */ tmatch_column->trevsorted = 0; /* if the tail contains NIL values */ tmatch_column->T->nil = 0; /* if the tail doesn't contain NIL values */ tmatch_column->T->nonil = 0; /* if the values in the tail are unique.Since BATkey only works with BAT heads, * BATmirror is used to switch the head and tail of the BATs */ if(strcmp(columnname,"starid")==0) { BATkey(BATmirror(tmatch_column),1); //only starid is 1 }else { BATkey(BATmirror(tmatch_column),0); } /* finally, append the result BATs to the result table * ignore errors, since we are finishing */ _append_bat(sql,tmatch,columnname,tmatch_column); /* The BATs now contain the data for the columns of the output tables, * append_bat registers in the system where to find the BAT for those columns. */ /* To avoid memory leak, BAT reference counter must be decreased after use. * That can be done using BBPunfix. */ if(tmatch_column) BBPunfix(tmatch_column->batCacheid); return 1; //success } Thanks!
Hi On 6/28/13 7:25 AM, hlfwm hlfwm wrote:
i want to create a monetdb function to help write my astronomical *binary* processing result into database,this function will load the binary result from memory directly into monetdb to save time, so i defined a UDF binaryCopy() ,
I am not sure such a function would improve your system much, because it depends on the relative time compared to your astronomical processing. Furthermore, to make sense you have to go all the way to link your astro library as UDF as well. Another issue is that you bypass the transaction management layers, which makes the system sensitive for incorrectness. In general, it is better to first identify 'how' bad the (BINARY) COPY into would be in the total setup. Martin
svom.c
int binaryCopy(str columnname, str buffer, int count) { mvc *sql = NULL; sql_table *tmatch = NULL; /*BATs in which the data of the columns of the output table will be stored*/ BAT *tmatch_column = NULL; BUN nr_rows; /* pointer to the tail of above BAT, which is just a normal C-array */ lng *tmatch_column_lng_t = NULL; flt *tmatch_column_flt_t = NULL; /*_bind_table is just a wrapper that tries to see if it can find the table match_table under three default schemas*/ if(!(tmatch = _bind_table(sql, NULL, "testtable"))) //return sql_message("42S02!BINARYLOAD():no such table test"); return 0; //nr_match = 20000; nr_rows = count; /* create BATs for all columns of the output table 'tmatch' */ if(strcmp(columnname,"starid")==0 || strcmp(columnname,"crossid")==0 ||strcmp(columnname,"catid")==0 ) { tmatch_column = BATnew(TYPE_void, TYPE_lng,nr_rows); }else { tmatch_column = BATnew(TYPE_void,TYPE_flt,nr_rows); }
/* now tmatch_starid_t points to the first element of the BAT's tails */ tmatch_column_lng_t = (lng*)Tloc(tmatch_column,BUNfirst(tmatch_column)); /* now we can write data into the BAT tail */ if((strcmp(columnname,"starid")==0) || (strcmp(columnname,"crossid")==0) ||(strcmp(columnname,"catid")==0) ) { *memcpy(tmatch_column_lng_t,buffer,nr_rows*sizeof(lng)); //is it right to write binary data into BAT???* }else { memcpy(tmatch_column_flt_t, buffer, nr_rows*sizeof(flt)); }
/* Set proper properties for all BATs */ BATsetcount(tmatch_column,nr_rows);
BATseqbase(tmatch_column,0); /* sorted:1,column is sorted in ascending order */ tmatch_column->tsorted = 0;
/* if the tail is reverse sorted. */ tmatch_column->trevsorted = 0;
/* if the tail contains NIL values */ tmatch_column->T->nil = 0; /* if the tail doesn't contain NIL values */ tmatch_column->T->nonil = 0;
/* if the values in the tail are unique.Since BATkey only works with BAT heads, * BATmirror is used to switch the head and tail of the BATs */ if(strcmp(columnname,"starid")==0) { BATkey(BATmirror(tmatch_column),1); //only starid is 1 }else { BATkey(BATmirror(tmatch_column),0); } /* finally, append the result BATs to the result table * ignore errors, since we are finishing */ _append_bat(sql,tmatch,columnname,tmatch_column); /* The BATs now contain the data for the columns of the output tables, * append_bat registers in the system where to find the BAT for those columns. */ /* To avoid memory leak, BAT reference counter must be decreased after use. * That can be done using BBPunfix. */ if(tmatch_column) BBPunfix(tmatch_column->batCacheid);
return 1; //success }
Thanks!
_______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list
hi Martin,
I know the binary copy utility in postgresql there is a similar
function *PQputCopyData,
it can reduce time by 20 times .*
the following is the function i write my result into database* PostgreSQL:*
*
*
void *writeToDBBinary*(struct SAMPLE *points, char *fileName, int fileType)
{ //my process result is in input parameter struct SAMPLE
PGconn *conn = NULL;
PGresult *pgrst = NULL;
conn = PQsetdbLogin(host, port, options, tty, dbname, user, password);
char *buf = (char*) malloc(1024 * sizeof (char));
unsigned short fieldNum = 21;
char *sendHeader = "PGCOPY\n\377\r\n\0";
unsigned int zero = '\0';
struct strBuffer strBuf;
strBuf.data = buf;
strBuf.len = MAX_BUFFER;
int i = 0;
int j = 0;
int k = 0;
struct SAMPLE *tSample;
if (fileType) { //insert reference file
//total 19 column, not include cid
sprintf(sqlBuf, "*COPY %s(\
*
*
starid,crossid,catid,magnorm,ra,dec,background,classstar,ellipticity,flags,mag,mage,fwhm,pixx,pixy,thetaimage,vignet,\
*
* magcalib,magcalibe,pixx1,pixy1 \*
* )FROM STDIN WITH BINARY", match_table);*
pgrst = PQexec(conn, sqlBuf);
if (PQresultStatus(pgrst) == PGRES_COPY_IN) {
strBuf.cursor = 0;
memcpy(strBuf.data, sendHeader, 11);
strBuf.cursor += 11;
memcpy(strBuf.data + strBuf.cursor, (char*) &zero, 4);
strBuf.cursor += 4;
memcpy(strBuf.data + strBuf.cursor, (char*) &zero, 4);
strBuf.cursor += 4;
tSample = points->next;
while (tSample) {
addInt16(&strBuf, fieldNum); //column number, when add or
delete colume, must change this number
addInt64(&strBuf, tSample->id);
addInt64(&strBuf, tSample->crossid);
addInt64(&strBuf, catid);
addFloat8(&strBuf, -1.0);
addFloat8(&strBuf, tSample->alpha);
addFloat8(&strBuf, tSample->delta);
addFloat8(&strBuf, tSample->background);
addFloat8(&strBuf, tSample->classstar);
addFloat8(&strBuf, tSample->ellipticity);
addFloat8(&strBuf, tSample->flags);
addFloat8(&strBuf, tSample->mag);
addFloat8(&strBuf, tSample->mage);
addFloat8(&strBuf, tSample->fwhm);
addFloat8(&strBuf, tSample->pixx);
addFloat8(&strBuf, tSample->pixy);
addFloat8(&strBuf, tSample->thetaimage);
addFloat8(&strBuf, tSample->vignet);
addFloat8(&strBuf, tSample->magcalib);
addFloat8(&strBuf, tSample->magcalibe);
addFloat8(&strBuf, tSample->pixx);
addFloat8(&strBuf, tSample->pixy);
int copydatares = *PQputCopyData*(conn, strBuf.data,
strBuf.cursor);
//if(i >5) break;
i++;
tSample = tSample->next;
strBuf.cursor = 0;
}
* PQputCopyEnd(conn, NULL);*
} else {
printf("can not copy in!\n");
}
PQclear(pgrst);
so i am sure it's worthy to implement binarycopy in monetdb.
2013/6/28 Martin Kersten
Hi
On 6/28/13 7:25 AM, hlfwm hlfwm wrote:
i want to create a monetdb function to help write my astronomical *binary* processing result into database,this function will load the binary result from memory directly into monetdb to save time, so i defined a UDF binaryCopy() ,
I am not sure such a function would improve your system much, because it depends on the relative time compared to your astronomical processing. Furthermore, to make sense you have to go all the way to link your astro library as UDF as well. Another issue is that you bypass the transaction management layers, which makes the system sensitive for incorrectness.
In general, it is better to first identify 'how' bad the (BINARY) COPY into would be in the total setup.
Martin
svom.c
int binaryCopy(str columnname, str buffer, int count) { mvc *sql = NULL; sql_table *tmatch = NULL; /*BATs in which the data of the columns of the output table will be stored*/ BAT *tmatch_column = NULL; BUN nr_rows; /* pointer to the tail of above BAT, which is just a normal C-array */ lng *tmatch_column_lng_t = NULL; flt *tmatch_column_flt_t = NULL; /*_bind_table is just a wrapper that tries to see if it can find the table match_table under three default schemas*/ if(!(tmatch = _bind_table(sql, NULL, "testtable"))) //return sql_message("42S02!BINARYLOAD(**):no such table test"); return 0; //nr_match = 20000; nr_rows = count; /* create BATs for all columns of the output table 'tmatch' */ if(strcmp(columnname,"starid")**==0 || strcmp(columnname,"crossid")== **0 ||strcmp(columnname,"catid")==**0 ) { tmatch_column = BATnew(TYPE_void, TYPE_lng,nr_rows); }else { tmatch_column = BATnew(TYPE_void,TYPE_flt,nr_**rows); }
/* now tmatch_starid_t points to the first element of the BAT's tails */ tmatch_column_lng_t = (lng*)Tloc(tmatch_column,** BUNfirst(tmatch_column)); /* now we can write data into the BAT tail */ if((strcmp(columnname,"starid"**)==0) || (strcmp(columnname,"crossid")=**=0) ||(strcmp(columnname,"catid")=**=0) ) { *memcpy(tmatch_column_lng_t,**buffer,nr_rows*sizeof(lng)); //is it right to write binary data into BAT???*
}else { memcpy(tmatch_column_flt_t, buffer, nr_rows*sizeof(flt)); }
/* Set proper properties for all BATs */ BATsetcount(tmatch_column,nr_**rows);
BATseqbase(tmatch_column,0); /* sorted:1,column is sorted in ascending order
*/ tmatch_column->tsorted = 0;
/* if the tail is reverse sorted. */ tmatch_column->trevsorted = 0;
/* if the tail contains NIL values */ tmatch_column->T->nil = 0; /* if the tail doesn't contain NIL values */ tmatch_column->T->nonil = 0;
/* if the values in the tail are unique.Since BATkey only works with BAT heads, * BATmirror is used to switch the head and tail of the BATs */ if(strcmp(columnname,"starid")**==0) { BATkey(BATmirror(tmatch_**column),1); //only starid is 1 }else { BATkey(BATmirror(tmatch_**column),0); } /* finally, append the result BATs to the result table * ignore errors, since we are finishing */ _append_bat(sql,tmatch,**columnname,tmatch_column); /* The BATs now contain the data for the columns of the output tables, * append_bat registers in the system where to find the BAT for those columns. */ /* To avoid memory leak, BAT reference counter must be decreased after use. * That can be done using BBPunfix. */ if(tmatch_column) BBPunfix(tmatch_column->**batCacheid);
return 1; //success }
Thanks!
______________________________**_________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/**mailman/listinfo/users-listhttp://mail.monetdb.org/mailman/listinfo/users-list
______________________________**_________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/**mailman/listinfo/users-listhttp://mail.monetdb.org/mailman/listinfo/users-list
participants (2)
-
hlfwm hlfwm -
Martin Kersten