Problem with COPY INTO

Martin Kersten martin at monetdb.org
Thu Apr 14 23:42:00 CEST 2016


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','\'';"  <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
>



More information about the users-list mailing list