Re: [MonetDB-users] MonetDB Permission/COPY INTO Questions

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 On Sat, Nov 12, 2011 at 11:43 AM, Sjoerd Mullender wrote:

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

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