Hi Stefan,

thanks for your help, removing ';' helped.

I'm now struggling to load 100+ million of rows via COPY INTO FROM STDIN, but it just fails (no error printed, apart from "0 affected rows"). 10-20 mil rows is ok to load via this method, and I'll see if I can make something that could split up the input into chunks and feed it into mclient.

Cheers,
Nikola


> Gesendet: Freitag, 15. April 2016 um 00:39 Uhr
> Von: "Stefan Manegold" <Stefan.Manegold@cwi.nl>
> An: "Communication channel for MonetDB users" <users-list@monetdb.org>
> Betreff: Re: Problem with COPY INTO

>
>
> Mind the difference:
>
> $ mclient -d demo -s "COPY 15 RECORDS INTO errortable FROM STDIN USING DELIMITERS '\t','\n';" - < /tmp/x.txt
>                                                                                            ^
> Failed to import table Column value 1 missing
> syntax error, unexpected sqlINT in: "100"
>
> $ mclient -d demo -s "COPY 15 RECORDS INTO errortable FROM STDIN USING DELIMITERS '\t','\n'" - < /tmp/x.txt
>                                                                                           ^^
> 15 affected rows
>
>
> While the examples at
> https://www.monetdb.org/Documentation/mclient-man-page
> and
> https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/LoadingBulkData
> indeed omit the semicolon (';'), they admittedly do not seem to menion this explicitly ...
>
> Stefan
>
> ----- On Apr 15, 2016, at 12:28 AM, Stefan Manegold Stefan.Manegold@cwi.nl wrote:
>
> > Hi,
> >
> > one question I have is why you specify that your string delimiter is a single
> > quote,
> > while your data does not seem to have any string delimiters?
> >
> > As for the granting of premissions, you might want to file a bug report;
> > either the documentation of the implementation is wrong, or at least they do not
> > match
> > or are misleading.
> > (well, I assume you you replaced "copy_role" by the actual role's name that you
> > want to grant the permission to, right?)
> >
> > Best,
> > Stefan
> >
> > ----- On Apr 14, 2016, at 10:54 PM, Knezevic Nikola nikkne@gmx.ch wrote:
> >
> >> Hi all,
> >>
> >> I've lost a significant time trying to get data from mysql database into
> >> monetdb. Since I have a lot of data, I wanted to make it as fast and automated
> >> as possible, but to no avail.
> >>
> >> There are several issues I encountered, and I'd appreciate any help on any of
> >> them. I'm running the latest monetDB on CentOS:
> >> % mclient --version
> >> mclient, the MonetDB interactive terminal (Jul2015-SP4)
> >> support for command-line editing compiled-in
> >> character encoding: UTF-8
> >>
> >> 1. I tried following the last example on
> >> https://www.monetdb.org/Documentation/SQLreference/Permissions in order to get
> >> another user to be able to perform COPY INTO, but it fails:
> >>
> >> % mclient -u monetdb -d some_db
> >> password:
> >> Welcome to mclient, the MonetDB/SQL interactive terminal (Jul2015-SP4)
> >> Database: MonetDB v11.21.19 (Jul2015-SP4),
> >> 'mapi:monetdb://internal:50000/some_db'
> >> Type \q to quit, \? for a list of available commands
> >> auto commit mode: on
> >> sql>GRANT COPY FROM TO copy_role;
> >> syntax error, unexpected COPY in: "grant copy"
> >>
> >> 2. Ok, since I can't run COPY INTO FROM FILE, I decided to go with FROM STDIN
> >> version. To do so, I dump the mysql database and import that in monetdb. That
> >> also fails:
> >>
> >> % mysql -N -B -u reader -pxxx some_db -h some_host -e "SELECT * FROM
> >> errortable;"|mclient -d some_db -s "COPY 15 RECORDS INTO errortable FROM STDIN
> >> USING DELIMITERS '\t','\n','\'';" -
> >> Failed to import table Column value 1 missing
> >> syntax error, unexpected sqlINT in: "100"
> >>
> >> % mclient -u writer -d some_db
> >> password:
> >> Welcome to mclient, the MonetDB/SQL interactive terminal (Jul2015-SP4)
> >> Database: MonetDB v11.21.19 (Jul2015-SP4),
> >> 'mapi:monetdb://internal:50000/some_db'
> >> Type \q to quit, \? for a list of available commands
> >> auto commit mode: on
> >> sql>\d errortable
> >> CREATE TABLE "some_db"."errortable" (
> >>    "rejectreason" INTEGER       NOT NULL,
> >>    "text"         CHARACTER LARGE OBJECT
> >> );
> >>
> >> Another try also fails (still using STDIN):
> >>
> >> % mysql -N -B -u reader -pxxx some_db -h some_host -e "SELECT * FROM
> >> errortable;" >test.out
> >> % mclient -d some_db -s "COPY 15 RECORDS INTO errortable FROM STDIN USING
> >> DELIMITERS '\t','\n','\'';"  <test.out
> >> Failed to import table Column value 1 missing
> >> % mclient -d some_db -s "COPY 15 RECORDS INTO errortable FROM STDIN USING
> >> DELIMITERS '\t','\n','\'';" - <test.out
> >> Failed to import table Column value 1 missing
> >> syntax error, unexpected sqlINT in: "100"
> >>
> >> (note different error messages, and also note that 100 is a perfectly good
> >> sqlINT)
> >>
> >> If I try the following, this succeeds. However, this is something I'm trying to
> >> avoid, as I'd had to create huge files (100+GB), which is an absolute waste of
> >> space and time.
> >>
> >> % mysql -N -B -u reader -pxxx some_db -h some_host -e "SELECT * FROM
> >> errortable;" >test.out
> >> % mclient -d some_db -s "COPY 15 RECORDS INTO errortable FROM
> >> '/home/nikola/test.out' USING DELIMITERS '\t','\n','\'';"
> >> COPY INTO: insufficient privileges: COPY INTO from file(s) requires database
> >> administrator rights, use 'COPY INTO "errortable" FROM STDIN' instead
> >> # obviously, fails due to (1)
> >>
> >> % mclient -u monetdb -d some_db -s "COPY 15 RECORDS INTO some_db.errortable FROM
> >> '/home/nikola/test.out' USING DELIMITERS '\t','\n','\'';"
> >> password:
> >> 15 affected rows
> >>
> >> The file in question is super simple, a list of integers with textual
> >> description:
> >> 100        Some limit exceeded
> >> 101        Another limit exceeded
> >> 404        Service temporarily not available
> >> 405        Service not available
> >> 500        Server not available
> >> 700        End of world as we know it
> >> 701        User already logged in
> >> 10000      Book not found
> >> 10001      Price reasonability doubtfull
> >> 10002      Client is well known
> >> 20001      Activation in progress
> >> 20002      Pre-load limit exceeded
> >> 30000      Pre-load limit exceeded again
> >> 1  test
> >> 5  Invalid value 0 to divide with
> >>
> >> Any hints on how to solve this super annoying problem?
> >>
> >> Thanks,
> >> Nikola
> >> _______________________________________________
> >> users-list mailing list
> >> users-list@monetdb.org
> >> https://www.monetdb.org/mailman/listinfo/users-list
> >
> > --
> >| Stefan.Manegold@CWI.nl | DB Architectures   (DA) |
> >| www.CWI.nl/~manegold/  | Science Park 123 (L321) |
> >| +31 (0)20 592-4212     | 1098 XG Amsterdam  (NL) |
> > _______________________________________________
> > users-list mailing list
> > users-list@monetdb.org
> > https://www.monetdb.org/mailman/listinfo/users-list
>
> --
> | Stefan.Manegold@CWI.nl | DB Architectures   (DA) |
> | www.CWI.nl/~manegold/  | Science Park 123 (L321) |
> | +31 (0)20 592-4212     | 1098 XG Amsterdam  (NL) |
> _______________________________________________
> users-list mailing list
> users-list@monetdb.org
> https://www.monetdb.org/mailman/listinfo/users-list
>
_______________________________________________
users-list mailing list
users-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/users-list