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