November 2011 - users-list

[MonetDB-users] General Questions on Columnar DB Design and Querying
by Sean McNamara 13 Nov '11

13 Nov '11

Hi all, I've managed to get MonetDB running and loaded with a chunk of data (thanks to some great help from the list) and am now trying to figure out the "right" way to make use of it. I don't have a whole lot of exposure to columnar stores, and am therefore not sure if I'm doing things in an idiomatically correct way. I have a couple general questions about columnar DBs, and hope its appropriate to ask them here even thought they aren't specific to MonetDB. If these questions aren't considered appropriate for this list I apologize in advance and will happily move them to a different forum (but would appreciate pointers to where such a forum might be.) Two questions are general, and one is fairly specific. First the general: 1. Does working with a columnar store fundamentally change the way one would structure the tables? I'm somewhat familiar with star-schema designs where a central fact table holds the entire attribute set in as rows of indexes with supporting lookup tables for each column type, but it seems like columnar stores achieve the same thing without actually requiring the developer to specifically build those structures. Tables are constructed in a de-normalized RDBMS manner and the underlying DB takes care of the vectorization. Is this a valid description, or am I fundamentally misunderstanding the basics? 2. If there are significant differences in the way columnar stores should be designed, can folks point me to any resources (web, book, article, etc.) to help educate me on the topic? Now the specific: I have several sets of time-series data modeled as follows: create table sensordata ( sensor varchar(32), epoch_ts bigint, reading double, CONSTRAINT SENSORDATA_PKEY PRIMARY KEY (sensor, epoch_ts) ); I've populated that data, and now want to query data for a subset of sensors and generate a table that shows the value of the sensors at each point in time. I'm not sure what the right way to do this is. My desired out:ut would be: epoch_ts sensor1 sensor2 delta -------------- ------------ ------------ ------ <time1> 23.5 24.1 0.6 <time2> 19.3 25.2 5.9 ... <timeN> 25.1 19.3 -5.8 I tried to build that query as follows but end up with NULL results (note: I'm in no way a SQL pro so it might be really ugly.) select a.epoch_ts as epoch, a.reading as sensor1_reading, b.reading as sensor2_reading, b.reading - a.reading as delta from (select epoch_ts, reading from sensordata where sensor='sensor1') a, (select epoch_ts, reading from sensordata where sensor='sensor2') b where b.epoch_ts = a.epoch_ts order by epoch asc; Is this a sane way to approach the problem, or am I completely off track here. Each subselect returns results, but they obviously aren't joining correctly and it feels like it might simply be the wrong way to try to build the result set. I appreciate any direction you might provide. Thanks!

2 1

[MonetDB-users] MonetDB Permission/COPY INTO Questions
by Sean McNamara 13 Nov '11

13 Nov '11

Hi All, I'm just staring to play with MonetDB, but am running into some trouble with a few basic tasks. I've managed to create a new DB and have added a table. I'd now like to bulk-load the table with a CSV file full of data. When I try to do so, I get the following: WS3:~ tank$ mclient -u testuser testdb Welcome to mclient, the MonetDB/SQL interactive terminal (Aug2011) Database: MonetDB v11.5.1, 'testdb' Type \q to quit, \? for a list of available commands auto commit mode: on sql>COPY 1800 records into testdata from 'testdata.csv'; COPY INTO: insufficient privileges: COPY INTO from file(s) requires administrator rights, use 'COPY INTO "testdata" FROM STDIN' instead I then try to copy into via STDIN: WS3:~ tank$ mclient -u testuser -s "COPY 1800 records into testdata from STDIN using delimiters ',';" testdb < testdata.csv WS3:~ tank$ mclient -u testuser testdb Welcome to mclient, the MonetDB/SQL interactive terminal (Aug2011) Database: MonetDB v11.5.1, 'testdb' Type \q to quit, \? for a list of available commands auto commit mode: on sql>select * testdb; +-----+-----+------+ | foo | bar | baz | +===+==+===+ +-----+-----+------+ 0 tuples (2.307ms) sql> It seems clear I have some sort of permission/privilege issue, but I'm not clear how to resolve it. I've looked at all the tables and see the auths, users, user_role, and other tables, but it's not clear what actually needs to be tweaked there or if I should be using other DB commands to set things up. I've tried signing on as the monetdb user to do the import, but the monetdb user doesn't appear to be able to see the table: WS3:~ tank$ mclient -u monetdb testdb password: Welcome to mclient, the MonetDB/SQL interactive terminal (Aug2011) Database: MonetDB v11.5.1, 'testdb' Type \q to quit, \? for a list of available commands auto commit mode: on sql>COPY 1800 records into testdata from 'testdata.csv'; COPY INTO: no such table 'testdata' I realize this should all be quite simple, but am not finding my way forward. I've been searching the archives and hunting on google but am coming up empty. Can anyone point the way? Thanks!

6 11

[MonetDB-users] General questions on columnar DB design and querying
by Sean McNamara 13 Nov '11

13 Nov '11

Hi all, I've managed to get MonetDB running and loaded with a chunk of data (thanks to some great help from the list) and am now trying to figure out the "right" way to make use of it. I don't have a whole lot of exposure to columnar stores, and am therefore not sure if I'm doing things in an idiomatically correct way. I have a couple general questions about columnar DBs, and hope its appropriate to ask them here even thought they aren't specific to MonetDB. If these questions aren't considered appropriate for this list I apologize in advance and will happily move them to a different forum (but would appreciate pointers to where such a forum might be.) Two questions are general, and one is fairly specific. First the general: 1. Does working with a columnar store fundamentally change the way one would structure the tables? I'm somewhat familiar with star-schema designs where a central fact table holds the entire attribute set in as rows of indexes with supporting lookup tables for each column type, but it seems like columnar stores achieve the same thing without actually requiring the developer to specifically build those structures. Tables are constructed in a de-normalized RDBMS manner and the underlying DB takes care of the vectorization. Is this a valid description, or am I fundamentally misunderstanding the basics? 2. If there are significant differences in the way columnar stores should be designed, can folks point me to any resources (web, book, article, etc.) to help educate me on the topic? Now the specific: I have several sets of time-series data modeled as follows: create table sensordata ( sensor varchar(32), epoch_ts bigint, reading double, CONSTRAINT SENSORDATA_PKEY PRIMARY KEY (sensor, epoch_ts) ); I've populated that data, and now want to query data for a subset of sensors and generate a table that shows the value of the sensors at each point in time. I'm not sure what the right way to do this is. My desired out:ut would be: epoch_ts sensor1 sensor2 delta -------------- ------------ ------------ ------ <time1> 23.5 24.1 0.6 <time2> 19.3 25.2 5.9 ... <timeN> 25.1 19.3 -5.8 I tried to build that query as follows but end up with NULL results (note: I'm in no way a SQL pro so it might be really ugly.) select a.epoch_ts as epoch, a.reading as sensor1_reading, b.reading as sensor2_reading, b.reading - a.reading as delta from (select epoch_ts, reading from sensordata where sensor='sensor1') a, (select epoch_ts, reading from sensordata where sensor='sensor2') b where b.epoch_ts = a.epoch_ts order by epoch asc; Is this a sane way to approach the problem, or am I completely off track here. Each subselect returns results, but they obviously aren't joining correctly and it feels like it might simply be the wrong way to try to build the result set. I appreciate any direction you might provide. Thanks!

1 0

Re: [MonetDB-users] MonetDB Permission/COPY INTO Questions
by Allen Zhang 12 Nov '11

12 Nov '11

Sean, Maybe here is why you did not spot the duplicate: cat /tmp/testdata2.csv | cut -d, -f1,5 | sort | uniq -d Regards Allen On 11/12/11 12:39 PM, "Sean McNamara" <tank(a)thursday.org> wrote: >Looks like I had too much faith in the "uniq" command (or >misunderstand its constraints.) There are definitely duplicates in my >import set. :-( > >Back to data scrubbing... > >On Sat, Nov 12, 2011 at 12:08 PM, Allen Zhang <azhang(a)pmci-usa.com> wrote: >> Sean, >> >> Use sql select to find out duplicates. >> >> Regards >> Allen >> >> On 11/12/11 12:01 PM, "Sean McNamara" <tank(a)thursday.org> wrote: >> >>>I tried that (as both the normal dbuser and as admin). Both fail the >>>same way but don't give an indicator of where it feels the constraint >>>is violated: >>> >>>$ mclient -u monetdb -s "alter table mduser.testdata add constraint >>>testdata_pkey primary key (foo,epoch);" testdb >>>password: >>>SQLException:assert:UPDATE: PRIMARY KEY constraint >>>'testdata.testdata_pkey' violated >>> >>> >>>On Sat, Nov 12, 2011 at 11:42 AM, Allen Zhang <azhang(a)pmci-usa.com> >>>wrote: >>>> How about add constraint after you import? I think it might tell you >>>>which >>>> one had problem. >>>> >>>> Regards >>>> Allen >>>> >>>> On 11/12/11 11:19 AM, "Sean McNamara" <tank(a)thursday.org> wrote: >>>> >>>>>Brilliant suggestion, and I think it exposed the problem. >>>>> >>>>>$ mclient -u monetdb -s "copy into mduser.testdata from >>>>>'/tmp/testdata2.csv' using delimiters ',','\n','\'';" testdb >>>>>password: >>>>>SQLException:importTable:value 'ONE_SECOND' from line 12734 field 3 >>>>>not inserted, expecting type varchar(8) >>>>>failed to import table >>>>> >>>>>It looks like the field I created is too small to handle some of the >>>>>values in the file. After expanding all the varchar fields to 32 and >>>>>re-running, I get a different error: >>>>> >>>>>$ mclient -u monetdb -s "copy into mduser.testdata from >>>>>'/tmp/testdata2.csv' using delimiters ',','\n','\'';" testdb >>>>>password: >>>>>SQLException:assert:INSERT INTO: PRIMARY KEY constraint >>>>>'testdata.testdata_pkey' violated >>>>> >>>>>That makes me think I have duplicate data. The PKEY is comprised of >>>>>fields #1 and #5, so I check for that but it looks fine: >>>>> >>>>>$ cat /tmp/testdata2.csv | cut -d, -f1,5 |uniq -d >>>>>$ >>>>> >>>>>However if I drop and re-add the table without the constraint, it >>>>>works: >>>>> >>>>>sql>create table testdata ( >>>>>more> foo varchar(32), >>>>>more> bar varchar(32), >>>>>more> baz varchar(32), >>>>>more> woz varchar(32), >>>>>more> epoch bigint >>>>>more>); >>>>>operation successful (1.477ms) >>>>>sql>\q >>>>>$ mclient -u monetdb -s "copy into mduser.testdata from >>>>>'/tmp/testdata2.csv' using delimiters ',','\n','\'';" testdb >>>>>password: >>>>>2519931 affected rows >>>>> >>>>> >>>>>Thanks Allen! This gets me able to import. Now I'm wondering what >>>>>might be wrong with the constraint. Any insight there? >>>>> >>>>> >>>>> >>>>>On Sat, Nov 12, 2011 at 10:58 AM, Allen Zhang <azhang(a)pmci-usa.com> >>>>>wrote: >>>>>> What about just issue the following: >>>>>> >>>>>> mclient -s "copy into testdata from '/tmp/testdata2.csv' using >>>>>>delimiters >>>>>> ',','\n','\'';" >>>>>> >>>>>> >>>>>> Regards >>>>>> Allen >>>>>> >>>>>> >>>>>> On 11/12/11 10:48 AM, "Sean McNamara" <tank(a)thursday.org> wrote: >>>>>> >>>>>>>Thanks Allen. Red-herring there. My first file had "SPY" in the >>>>>>>first column, and the second had "testdata1", so the difference >>>>>>>there >>>>>>>is just because of the contents. If I convert the original SPY file >>>>>>>and re-run, I get the same behavior. >>>>>>> >>>>>>>$ mclient -s "copy into testdata from STDIN using delimiters >>>>>>>',','\n','\'';" testdb - < /tmp/testdata2.csv >>>>>>>syntax error, unexpected STRING in: "'SPY'" >>>>>>> >>>>>>> >>>>>>>On Sat, Nov 12, 2011 at 10:43 AM, Allen Zhang <azhang(a)pmci-usa.com> >>>>>>>wrote: >>>>>>>> Hi Sean, >>>>>>>> >>>>>>>> You should inspect your data quality, it seems in the first 3 >>>>>>>>imports, >>>>>>>>it >>>>>>>> stopped at "spy", I suspect it might be some \t in that supposed >>>>>>>>to >>>>>>>>be >>>>>>>> string, and last import you put single-quote around string, then >>>>>>>>it >>>>>>>> stopped at "testvalue1", search your import file, look for that >>>>>>>> "testvalue1", the problem should around there, maybe an extra >>>>>>>>field >>>>>>>> because wrong replace by sed. >>>>>>>> >>>>>>>> Best Regards >>>>>>>> Allen >>>>>>>> >>>>>>>> On 11/12/11 10:26 AM, "Sean McNamara" <tank(a)thursday.org> wrote: >>>>>>>> >>>>>>>>>Thanks Viktor, but no dice. I also tried converting from CSV to >>>>>>>>>TAB-delim to match what you were doing, but it's still not >>>>>>>>>working... >>>>>>>>> >>>>>>>>>$ mclient -s "copy into testdata from STDIN using delimiters >>>>>>>>>',','\n','';" testdb - < /tmp/testdata.csv >>>>>>>>>syntax error, unexpected IDENT in: "spy" >>>>>>>>> >>>>>>>>>$ sed 's/,/\'$'\t''/g' /tmp/testdata.csv > /tmp/testdata.dat >>>>>>>>> >>>>>>>>>$ mclient -s "copy into testdata from STDIN;" testdb - < >>>>>>>>>/tmp/testdata.dat >>>>>>>>>syntax error, unexpected IDENT in: "spy" >>>>>>>>> >>>>>>>>>$ mclient -s "copy into testdata from STDIN using delimiters >>>>>>>>>'\t','\n','';" testdb - < /tmp/testdata.dat >>>>>>>>>syntax error, unexpected IDENT in: "spy" >>>>>>>>> >>>>>>>>>I'm understanding the format of the documentation quite a bit >>>>>>>>>better >>>>>>>>>now after spending a few days with it, but I'm still not clear on >>>>>>>>>why >>>>>>>>>this insert is failing. >>>>>>>>>I went one step further and converted the input file to >>>>>>>>>single-quote >>>>>>>>>the string fields, but this fails as well: >>>>>>>>> >>>>>>>>>$ mclient -s "copy into testdata from STDIN using delimiters >>>>>>>>>',','\n','\'';" testdb - < /tmp/testdata2.csv >>>>>>>>>syntax error, unexpected STRING in: "'testvalue1'" >>>>>>>>> >>>>>>>>> >>>>>>>>>The problem has to be something trivial, but I have no idea what >>>>>>>>>it >>>>>>>>>is... >>>>>>>>> >>>>>>>>>On Sat, Nov 12, 2011 at 5:30 AM, Viktor Rosenfeld >>>>>>>>><listuser36(a)googlemail.com> wrote: >>>>>>>>>> Hi Sean, >>>>>>>>>> >>>>>>>>>> Sean McNamara wrote: >>>>>>>>>> >>>>>>>>>>> $ mclient -s "copy 2519931 records into testdata from STDIN >>>>>>>>>>>using >>>>>>>>>>> delimiters ',';" testdb - < /tmp/testdata.csv >>>>>>>>>>> syntax error, unexpected IDENT in: "testvalue1" >>>>>>>>>>> >>>>>>>>>>> where testvalue1 is one of the actual values in the FOO column >>>>>>>>>>> (actually the only value in this particular file.) >>>>>>>>>>> >>>>>>>>>>> I'm wondering if this is telling me it's expecting the field to >>>>>>>>>>>be >>>>>>>>>>> quoted, but that seems unlikely as it would make working with >>>>>>>>>>>CSVs >>>>>>>>>>> quite a bit painful. >>>>>>>>>>> >>>>>>>>>>> Can you guys kindly help me take another step forward. I >>>>>>>>>>>really >>>>>>>>>>> appreciate the guidance. >>>>>>>>>> >>>>>>>>>> I'm using >>>>>>>>>> >>>>>>>>>> COPY ... USING DELIMITERS '\t','\n',''; >>>>>>>>>> >>>>>>>>>> to bulk-load tab-delimited unquoted data. The '\t' is the >>>>>>>>>>default >>>>>>>>>>field >>>>>>>>>> separator, the '\n' is the default record separator. The final >>>>>>>>>>'' >>>>>>>>>>is >>>>>>>>>>the >>>>>>>>>> quote string -- empty in my case. >>>>>>>>>> >>>>>>>>>> See: >>>>>>>>>>http://www.monetdb.org/Documentation/Manuals/SQLreference/CopyInt >>>>>>>>>>o >>>>>>>>>> >>>>>>>>>> Cheers, >>>>>>>>>> Viktor >>>>>>>>>> >>>>>>>>> >>>>>>>>>------------------------------------------------------------------ >>>>>>>>>-- >>>>>>>>>-- >>>>>>>>>-- >>>>>>>>>-- >>>>>>>>>---- >>>>>>>>>RSA(R) Conference 2012 >>>>>>>>>Save $700 by Nov 18 >>>>>>>>>Register now >>>>>>>>>http://p.sf.net/sfu/rsa-sfdev2dev1 >>>>>>>>>_______________________________________________ >>>>>>>>>MonetDB-users mailing list >>>>>>>>>MonetDB-users(a)lists.sourceforge.net >>>>>>>>>https://lists.sourceforge.net/lists/listinfo/monetdb-users >>>>>>>> >>>>>>>> >>>>>>>> >>>>>> >>>>>> >>>>>> >>>> >>>> >>>> >> >> >>

1 0

Re: [MonetDB-users] MonetDB Permission/COPY INTO Questions
by Allen Zhang 12 Nov '11

12 Nov '11

Sean, Use sql select to find out duplicates. Regards Allen On 11/12/11 12:01 PM, "Sean McNamara" <tank(a)thursday.org> wrote: >I tried that (as both the normal dbuser and as admin). Both fail the >same way but don't give an indicator of where it feels the constraint >is violated: > >$ mclient -u monetdb -s "alter table mduser.testdata add constraint >testdata_pkey primary key (foo,epoch);" testdb >password: >SQLException:assert:UPDATE: PRIMARY KEY constraint >'testdata.testdata_pkey' violated > > >On Sat, Nov 12, 2011 at 11:42 AM, Allen Zhang <azhang(a)pmci-usa.com> wrote: >> How about add constraint after you import? I think it might tell you >>which >> one had problem. >> >> Regards >> Allen >> >> On 11/12/11 11:19 AM, "Sean McNamara" <tank(a)thursday.org> wrote: >> >>>Brilliant suggestion, and I think it exposed the problem. >>> >>>$ mclient -u monetdb -s "copy into mduser.testdata from >>>'/tmp/testdata2.csv' using delimiters ',','\n','\'';" testdb >>>password: >>>SQLException:importTable:value 'ONE_SECOND' from line 12734 field 3 >>>not inserted, expecting type varchar(8) >>>failed to import table >>> >>>It looks like the field I created is too small to handle some of the >>>values in the file. After expanding all the varchar fields to 32 and >>>re-running, I get a different error: >>> >>>$ mclient -u monetdb -s "copy into mduser.testdata from >>>'/tmp/testdata2.csv' using delimiters ',','\n','\'';" testdb >>>password: >>>SQLException:assert:INSERT INTO: PRIMARY KEY constraint >>>'testdata.testdata_pkey' violated >>> >>>That makes me think I have duplicate data. The PKEY is comprised of >>>fields #1 and #5, so I check for that but it looks fine: >>> >>>$ cat /tmp/testdata2.csv | cut -d, -f1,5 |uniq -d >>>$ >>> >>>However if I drop and re-add the table without the constraint, it works: >>> >>>sql>create table testdata ( >>>more> foo varchar(32), >>>more> bar varchar(32), >>>more> baz varchar(32), >>>more> woz varchar(32), >>>more> epoch bigint >>>more>); >>>operation successful (1.477ms) >>>sql>\q >>>$ mclient -u monetdb -s "copy into mduser.testdata from >>>'/tmp/testdata2.csv' using delimiters ',','\n','\'';" testdb >>>password: >>>2519931 affected rows >>> >>> >>>Thanks Allen! This gets me able to import. Now I'm wondering what >>>might be wrong with the constraint. Any insight there? >>> >>> >>> >>>On Sat, Nov 12, 2011 at 10:58 AM, Allen Zhang <azhang(a)pmci-usa.com> >>>wrote: >>>> What about just issue the following: >>>> >>>> mclient -s "copy into testdata from '/tmp/testdata2.csv' using >>>>delimiters >>>> ',','\n','\'';" >>>> >>>> >>>> Regards >>>> Allen >>>> >>>> >>>> On 11/12/11 10:48 AM, "Sean McNamara" <tank(a)thursday.org> wrote: >>>> >>>>>Thanks Allen. Red-herring there. My first file had "SPY" in the >>>>>first column, and the second had "testdata1", so the difference there >>>>>is just because of the contents. If I convert the original SPY file >>>>>and re-run, I get the same behavior. >>>>> >>>>>$ mclient -s "copy into testdata from STDIN using delimiters >>>>>',','\n','\'';" testdb - < /tmp/testdata2.csv >>>>>syntax error, unexpected STRING in: "'SPY'" >>>>> >>>>> >>>>>On Sat, Nov 12, 2011 at 10:43 AM, Allen Zhang <azhang(a)pmci-usa.com> >>>>>wrote: >>>>>> Hi Sean, >>>>>> >>>>>> You should inspect your data quality, it seems in the first 3 >>>>>>imports, >>>>>>it >>>>>> stopped at "spy", I suspect it might be some \t in that supposed to >>>>>>be >>>>>> string, and last import you put single-quote around string, then it >>>>>> stopped at "testvalue1", search your import file, look for that >>>>>> "testvalue1", the problem should around there, maybe an extra field >>>>>> because wrong replace by sed. >>>>>> >>>>>> Best Regards >>>>>> Allen >>>>>> >>>>>> On 11/12/11 10:26 AM, "Sean McNamara" <tank(a)thursday.org> wrote: >>>>>> >>>>>>>Thanks Viktor, but no dice. I also tried converting from CSV to >>>>>>>TAB-delim to match what you were doing, but it's still not >>>>>>>working... >>>>>>> >>>>>>>$ mclient -s "copy into testdata from STDIN using delimiters >>>>>>>',','\n','';" testdb - < /tmp/testdata.csv >>>>>>>syntax error, unexpected IDENT in: "spy" >>>>>>> >>>>>>>$ sed 's/,/\'$'\t''/g' /tmp/testdata.csv > /tmp/testdata.dat >>>>>>> >>>>>>>$ mclient -s "copy into testdata from STDIN;" testdb - < >>>>>>>/tmp/testdata.dat >>>>>>>syntax error, unexpected IDENT in: "spy" >>>>>>> >>>>>>>$ mclient -s "copy into testdata from STDIN using delimiters >>>>>>>'\t','\n','';" testdb - < /tmp/testdata.dat >>>>>>>syntax error, unexpected IDENT in: "spy" >>>>>>> >>>>>>>I'm understanding the format of the documentation quite a bit better >>>>>>>now after spending a few days with it, but I'm still not clear on >>>>>>>why >>>>>>>this insert is failing. >>>>>>>I went one step further and converted the input file to single-quote >>>>>>>the string fields, but this fails as well: >>>>>>> >>>>>>>$ mclient -s "copy into testdata from STDIN using delimiters >>>>>>>',','\n','\'';" testdb - < /tmp/testdata2.csv >>>>>>>syntax error, unexpected STRING in: "'testvalue1'" >>>>>>> >>>>>>> >>>>>>>The problem has to be something trivial, but I have no idea what it >>>>>>>is... >>>>>>> >>>>>>>On Sat, Nov 12, 2011 at 5:30 AM, Viktor Rosenfeld >>>>>>><listuser36(a)googlemail.com> wrote: >>>>>>>> Hi Sean, >>>>>>>> >>>>>>>> Sean McNamara wrote: >>>>>>>> >>>>>>>>> $ mclient -s "copy 2519931 records into testdata from STDIN using >>>>>>>>> delimiters ',';" testdb - < /tmp/testdata.csv >>>>>>>>> syntax error, unexpected IDENT in: "testvalue1" >>>>>>>>> >>>>>>>>> where testvalue1 is one of the actual values in the FOO column >>>>>>>>> (actually the only value in this particular file.) >>>>>>>>> >>>>>>>>> I'm wondering if this is telling me it's expecting the field to >>>>>>>>>be >>>>>>>>> quoted, but that seems unlikely as it would make working with >>>>>>>>>CSVs >>>>>>>>> quite a bit painful. >>>>>>>>> >>>>>>>>> Can you guys kindly help me take another step forward. I really >>>>>>>>> appreciate the guidance. >>>>>>>> >>>>>>>> I'm using >>>>>>>> >>>>>>>> COPY ... USING DELIMITERS '\t','\n',''; >>>>>>>> >>>>>>>> to bulk-load tab-delimited unquoted data. The '\t' is the default >>>>>>>>field >>>>>>>> separator, the '\n' is the default record separator. The final '' >>>>>>>>is >>>>>>>>the >>>>>>>> quote string -- empty in my case. >>>>>>>> >>>>>>>> See: >>>>>>>>http://www.monetdb.org/Documentation/Manuals/SQLreference/CopyInto >>>>>>>> >>>>>>>> Cheers, >>>>>>>> Viktor >>>>>>>> >>>>>>> >>>>>>>-------------------------------------------------------------------- >>>>>>>-- >>>>>>>-- >>>>>>>-- >>>>>>>---- >>>>>>>RSA(R) Conference 2012 >>>>>>>Save $700 by Nov 18 >>>>>>>Register now >>>>>>>http://p.sf.net/sfu/rsa-sfdev2dev1 >>>>>>>_______________________________________________ >>>>>>>MonetDB-users mailing list >>>>>>>MonetDB-users(a)lists.sourceforge.net >>>>>>>https://lists.sourceforge.net/lists/listinfo/monetdb-users >>>>>> >>>>>> >>>>>> >>>> >>>> >>>> >> >> >>

1 0

Re: [MonetDB-users] MonetDB Permission/COPY INTO Questions
by Allen Zhang 12 Nov '11

12 Nov '11

What about just issue the following: mclient -s "copy into testdata from '/tmp/testdata2.csv' using delimiters ',','\n','\'';" Regards Allen On 11/12/11 10:48 AM, "Sean McNamara" <tank(a)thursday.org> wrote: >Thanks Allen. Red-herring there. My first file had "SPY" in the >first column, and the second had "testdata1", so the difference there >is just because of the contents. If I convert the original SPY file >and re-run, I get the same behavior. > >$ mclient -s "copy into testdata from STDIN using delimiters >',','\n','\'';" testdb - < /tmp/testdata2.csv >syntax error, unexpected STRING in: "'SPY'" > > >On Sat, Nov 12, 2011 at 10:43 AM, Allen Zhang <azhang(a)pmci-usa.com> wrote: >> Hi Sean, >> >> You should inspect your data quality, it seems in the first 3 imports, >>it >> stopped at "spy", I suspect it might be some \t in that supposed to be >> string, and last import you put single-quote around string, then it >> stopped at "testvalue1", search your import file, look for that >> "testvalue1", the problem should around there, maybe an extra field >> because wrong replace by sed. >> >> Best Regards >> Allen >> >> On 11/12/11 10:26 AM, "Sean McNamara" <tank(a)thursday.org> wrote: >> >>>Thanks Viktor, but no dice. I also tried converting from CSV to >>>TAB-delim to match what you were doing, but it's still not working... >>> >>>$ mclient -s "copy into testdata from STDIN using delimiters >>>',','\n','';" testdb - < /tmp/testdata.csv >>>syntax error, unexpected IDENT in: "spy" >>> >>>$ sed 's/,/\'$'\t''/g' /tmp/testdata.csv > /tmp/testdata.dat >>> >>>$ mclient -s "copy into testdata from STDIN;" testdb - < >>>/tmp/testdata.dat >>>syntax error, unexpected IDENT in: "spy" >>> >>>$ mclient -s "copy into testdata from STDIN using delimiters >>>'\t','\n','';" testdb - < /tmp/testdata.dat >>>syntax error, unexpected IDENT in: "spy" >>> >>>I'm understanding the format of the documentation quite a bit better >>>now after spending a few days with it, but I'm still not clear on why >>>this insert is failing. >>>I went one step further and converted the input file to single-quote >>>the string fields, but this fails as well: >>> >>>$ mclient -s "copy into testdata from STDIN using delimiters >>>',','\n','\'';" testdb - < /tmp/testdata2.csv >>>syntax error, unexpected STRING in: "'testvalue1'" >>> >>> >>>The problem has to be something trivial, but I have no idea what it >>>is... >>> >>>On Sat, Nov 12, 2011 at 5:30 AM, Viktor Rosenfeld >>><listuser36(a)googlemail.com> wrote: >>>> Hi Sean, >>>> >>>> Sean McNamara wrote: >>>> >>>>> $ mclient -s "copy 2519931 records into testdata from STDIN using >>>>> delimiters ',';" testdb - < /tmp/testdata.csv >>>>> syntax error, unexpected IDENT in: "testvalue1" >>>>> >>>>> where testvalue1 is one of the actual values in the FOO column >>>>> (actually the only value in this particular file.) >>>>> >>>>> I'm wondering if this is telling me it's expecting the field to be >>>>> quoted, but that seems unlikely as it would make working with CSVs >>>>> quite a bit painful. >>>>> >>>>> Can you guys kindly help me take another step forward. I really >>>>> appreciate the guidance. >>>> >>>> I'm using >>>> >>>> COPY ... USING DELIMITERS '\t','\n',''; >>>> >>>> to bulk-load tab-delimited unquoted data. The '\t' is the default >>>>field >>>> separator, the '\n' is the default record separator. The final '' is >>>>the >>>> quote string -- empty in my case. >>>> >>>> See: >>>>http://www.monetdb.org/Documentation/Manuals/SQLreference/CopyInto >>>> >>>> Cheers, >>>> Viktor >>>> >>> >>>------------------------------------------------------------------------ >>>-- >>>---- >>>RSA(R) Conference 2012 >>>Save $700 by Nov 18 >>>Register now >>>http://p.sf.net/sfu/rsa-sfdev2dev1 >>>_______________________________________________ >>>MonetDB-users mailing list >>>MonetDB-users(a)lists.sourceforge.net >>>https://lists.sourceforge.net/lists/listinfo/monetdb-users >> >> >>

1 0

Re: [MonetDB-users] Extended Fetch ODBC Error
by Jeremy O'Sullivan 10 Nov '11

10 Nov '11

Hi Sjoerd, Would it be possible to test your thesis ? The OBI guys reckon that for odbc 2 connections, they use the function SQLExtendedFetch. This has bee replaced by SQLFetchScroll in odbc 3.5. Not sure if that helps, I know little or nothing about odbc, other than many BI tools only use this interface and not jdbc. Regards, Jeremy It is unclear from this message what exactly fails. My guess (since I saw something like that recently) is that Oracle's OBI EE tries to set the statement option (or attribute) SQL_ATTR_CONCURRENCY to a value that is not supported. We only support setting the value to SQL_CONCUR_READ_ONLY. Attempting to set the attribute to any other value will return the above error (not implemented). Perhaps what we should do instead here is accept the value and return a message (in the ODBC way) that instead of the value that the application asked for we set it to SQL_CONCUR_READ_ONLY. Other possible statement attributes that could return the feature not implemented error are SQL_ATTR_ASYNC_ENABLE, SQL_ATTR_CURSOR_SENSITIVITY, SQL_ATTR_ENABLE_AUTO_IPD, SQL_ATTR_FETCH_BOOKMARK_PTR, SQL_ATTR_KEYSET_SIZE, SQL_ATTR_MAX_LENGTH, SQL_ATTR_MAX_ROWS, SQL_ATTR_QUERY_TIMEOUT, SQL_ATTR_ROW_NUMBER, SQL_ATTR_SIMULATE_CURSOR, and SQL_ATTR_USE_BOOKMARKS. On 9 Nov 2011, at 12:26, Jeremy O'Sullivan wrote: > Hello, > > When trying to connect to MonetDB using Oracle's OBI EE, I get the error below. Has anyone seen this before ? > Regards, > Jeremy > > [MonetDB][ODBC Driver 11.5.7]Optional feature not implemented. > > [nQSError: 16006] Setting the statement option for extended fetch failed. > >

2 1

[MonetDB-users] Extended Fetch ODBC Error
by Jeremy O'Sullivan 09 Nov '11

09 Nov '11

Hello, When trying to connect to MonetDB using Oracle's OBI EE, I get the error below. Has anyone seen this before ? Regards, Jeremy [MonetDB][ODBC Driver 11.5.7]Optional feature not implemented. [nQSError: 16006] Setting the statement option for extended fetch failed.

2 1

[MonetDB-users] Tableau Alias error
by Jeremy O'Sullivan 09 Nov '11

09 Nov '11

Hello, When connecting from Tableau, the following syntax generates and error on the command line, is this correct ? (The :w triggers the error.) Regards, Jeremy select count(context_key) as "context_key:w" from context_dim;

2 1

[MonetDB-users] How to check wether a record exists or not in stored procedure
by dengxk 08 Nov '11

08 Nov '11

I am writing a stored procedure to insert an record into my table. If the record exists, just update it. it is easier to acheive in microsoft sql server with the sql: if exists( select column from table where condition ...). As monetdb doesn't support select statment and cursor in stored procedure, is there other walkround?

1 0