Thank you both Henry and Sjoerd. Both your suggestions were helpful in moving me forward a bit, but now I'm stuck on the next stage: Actually completing the data. Here's my testdata table: create table testdata ( foo varchar(32), bar varchar(8), baz varchar(8), epoch bigint, CONSTRAINT TESTDATA_PKEY PRIMARY KEY (foo, epoch) ); Format of my testdata.file is: FOO,BAR,BAZ,EPOCH with unquoted values for strings, e.g.: testvalue1,testvalue2,testvalue3,1320443996 I then try to bulk load (using both methods suggested): $ cat testdata.csv |wc -l 2519931 $ 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. On Fri, Nov 11, 2011 at 1:57 AM, Sjoerd Mullender <sjoerd@acm.org> wrote:

On 2011-11-11 06:33, Sean McNamara wrote:

...

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>

Try this: mclient -u testuser -s "COPY 1800 records into testdata from STDIN using delimiters ',';" testdb - < testdata.csv

Note the extra - on the command line.

...

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!

------------------------------------------------------------------------------ RSA(R) Conference 2012 Save $700 by Nov 18 Register now http://p.sf.net/sfu/rsa-sfdev2dev1 _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

-- Sjoerd Mullender

------------------------------------------------------------------------------ RSA(R) Conference 2012 Save $700 by Nov 18 Register now http://p.sf.net/sfu/rsa-sfdev2dev1 _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

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@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

On 2011-11-12 05:31, Sean McNamara wrote:

Thank you both Henry and Sjoerd. Both your suggestions were helpful in moving me forward a bit, but now I'm stuck on the next stage: Actually completing the data.

Here's my testdata table:

create table testdata ( foo varchar(32), bar varchar(8), baz varchar(8), epoch bigint, CONSTRAINT TESTDATA_PKEY PRIMARY KEY (foo, epoch) );

Format of my testdata.file is:

FOO,BAR,BAZ,EPOCH

with unquoted values for strings, e.g.:

testvalue1,testvalue2,testvalue3,1320443996

I then try to bulk load (using both methods suggested):

$ cat testdata.csv |wc -l 2519931

$ 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 told you the way for the next release, not the current release. I'm sorry for causing confusion. The way to import in the current (Aug2011) release and before is: mclient -s "copy ... into testdata from stdin ..." -i -d testdb < /tmp/testdata.csv or mclient -s "copy ... into testdata from '/tmp/testdata.csv' ..." -d testdb In both cases, the -d can be left out in the Aug2011 release (but not in older releases). The important thing of the first version is that it is actually quite slow (that'll be fixed in the Dec2011 release), but it should work. Do note the use of -i instead of plain - which I told you before. For the second version it is important to use an absolute path name, and you should realize that the server reads the file, so it should have the right permissions.

On Fri, Nov 11, 2011 at 1:57 AM, Sjoerd Mullender <sjoerd@acm.org> wrote:

...

On 2011-11-11 06:33, Sean McNamara wrote:

...

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>

Try this: mclient -u testuser -s "COPY 1800 records into testdata from STDIN using delimiters ',';" testdb - < testdata.csv

Note the extra - on the command line.

...

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!

------------------------------------------------------------------------------ RSA(R) Conference 2012 Save $700 by Nov 18 Register now http://p.sf.net/sfu/rsa-sfdev2dev1 _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

-- Sjoerd Mullender

------------------------------------------------------------------------------ RSA(R) Conference 2012 Save $700 by Nov 18 Register now http://p.sf.net/sfu/rsa-sfdev2dev1 _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------------ RSA(R) Conference 2012 Save $700 by Nov 18 Register now http://p.sf.net/sfu/rsa-sfdev2dev1 _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

-- Sjoerd Mullender

On 12-11-2011 12:07:46 -0600, Sean McNamara wrote:

Thanks Sjoerd. I'm running the August release on Mac OSX. Strangely, trying the new invocation you suggested give a different (and seemingly much worse) error:

$ mclient -s "copy 2519931 records into mduser.testdata from STDIN using delimiters ',','\n','';" -i testdb < /tmp/testdata.dat mclient(99300) malloc: *** error for object 0x100600f28: incorrect checksum for freed object - object was probably modified after being freed. *** set a breakpoint in malloc_error_break to debug Abort trap

Do you perhaps have a CrashReport (~/Library/Crashes ?) for this one? Perhaps it gives a clue where this happens with the stacktrace

On 12-11-2011 15:29:45 -0600, Sean McNamara wrote:

Hi Fabian,

I did a search but didn't find any crash report. Is there something I can do to force the generation?

Not that I know off the top of my head. It has changed over OS X releases, IIRC.