Problem with COPY INTO

Bryan Senseman monetdb at openbi.com
Fri Apr 15 18:06:21 CEST 2016


Nikola,

You might want to look at Pentaho Data Integration, I've had good success
using it to bulk load MonetDB.  You can also "loop" within it so you could
chunk the dataset.

Bryan

On Fri, Apr 15, 2016 at 11:36 AM, Knezevic Nikola <nikkne at gmx.ch> wrote:

> 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 at cwi.nl>
> > An: "Communication channel for MonetDB users" <users-list at 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 at 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 at 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 at monetdb.org
> > >> https://www.monetdb.org/mailman/listinfo/users-list
> > >
> > > --
> > >| Stefan.Manegold at 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 at monetdb.org
> > > https://www.monetdb.org/mailman/listinfo/users-list
> >
> > --
> > | Stefan.Manegold at 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 at monetdb.org
> > https://www.monetdb.org/mailman/listinfo/users-list
> >
> _______________________________________________
> users-list mailing list
> users-list at monetdb.org
> https://www.monetdb.org/mailman/listinfo/users-list
>

-- 

------------------------------

This transmission is confidential and intended solely for the use of the 
recipient named above. It may contain confidential, proprietary, or legally 
privileged information. If you are not the intended recipient, you are 
hereby notified that any unauthorized review, use, disclosure or 
distribution is strictly prohibited. If you have received this transmission 
in error, please contact the sender by reply e-mail and delete the original 
transmission and all copies from your system.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.monetdb.org/pipermail/users-list/attachments/20160415/0cbfb3e3/attachment-0001.html>


More information about the users-list mailing list