Problem with COPY INTO

Stefan Manegold Stefan.Manegold at cwi.nl
Fri Apr 15 00:39:33 CEST 2016


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


More information about the users-list mailing list