Aw: Re: Problem with COPY INTO

Hi Martin, Thanks for the tip, but I get nothing (I also tried playing with different delimiters, just to be sure) % mclient -d some_db -s "COPY 15 RECORDS INTO errortable FROM STDIN USING DELIMITERS '\t','\n','\r','\'' best effort;" - select * from sys.rejects; +-------+-------+---------+-------+ | rowid | fldid | message | input | +=======+=======+=========+=======+ +-------+-------+---------+-------+ 0 tuples (1.779ms) sql>select * from some_db.errortable; +--------------+------+ | rejectreason | text | +==============+======+ +--------------+------+ 0 tuples (1.851ms) % mclient -d some_db -s "COPY 15 RECORDS INTO errortable FROM STDIN USING DELIMITERS '\t','\n','\'' best effort;" -

Hi

Did you try the 'best effort' approach, which should give you possible more insights in what is wrong with the file.

See sql test directory for hint:

create table t_int(i int);

-- If a numeric is enclosed as string we have to be careful -- It seems that convention is to check for it, turning -- the example below into one null value added to the table. copy 1 records into t_int from stdin USING DELIMITERS ',','\n','\"' NULL AS '' best effort; ""

select * from t_int; select * from sys.rejects; call sys.clearrejects(); delete from t_int;

-- A decimal value entered at a place where we expect an int -- should be recognized. It is the residu of dumping an int -- as decimal. copy 2 records into t_int from stdin USING DELIMITERS ',','\n','\"' NULL AS '' best effort; 3.00 -4.0000

select * from t_int; select * from sys.rejects; call sys.clearrejects(); delete from t_int;

copy 3 records into t_int from stdin USING DELIMITERS ',','\n','\"' NULL AS '' best effort; 0 5.1 9

On 14/04/16 22:54, Knezevic Nikola 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','\'';"

(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

_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list