Aw: Re: Problem with COPY INTO

Knezevic Nikola nikkne at gmx.ch
Fri Apr 15 00:13:19 CEST 2016


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;" - <test.out
syntax error, unexpected ',', expecting SCOLON in: "copy 15 records into errortable from stdin using delimiters '\t','\n','\r',"
syntax error, unexpected sqlINT in: "100"
% mclient -d some_db -s "COPY 15 RECORDS INTO errortable FROM STDIN USING DELIMITERS '\t','\n','\r' best effort;" - <test.out
14 affected rows
syntax error, unexpected sqlINT in: "5"
% mclient -d some_db -s "COPY 15 RECORDS INTO errortable FROM STDIN USING DELIMITERS '\t','\r','\'' best effort;" - <test.out
0 affected rows

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>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;" - <test.out
14 affected rows
syntax error, unexpected sqlINT in: "5"

Lines in the file are delimited by 0x0a (LF) and columns with 0x09 (TAB).

Thanks,
Nikola

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