Problem with COPY INTO

Knezevic Nikola nikkne at gmx.ch
Fri Apr 15 17:10:32 CEST 2016


Hi Martin,

I had it created, but this still fails

mclient -d some_db -u monetdb
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>CREATE ROLE copy_role;
CREATE ROLE: role 'copy_role' already exists
sql>GRANT COPY FROM TO copy_role;
syntax error, unexpected COPY in: "grant copy"
sql>drop role copy_role;
operation successful (2.564ms)
sql>CREATE ROLE copy_role;
operation successful (3.659ms)
sql>GRANT COPY FROM TO copy_role;
syntax error, unexpected COPY in: "grant copy"

Cheers,
Nikola


> 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"
> 
> You forgot to first create the role.
> 
> CREATE ROLE copy_role;
> operation successful (34.549ms)
> GRANT COPY FROM TO copy_role;
> operation successful (15.332ms)
> CREATE SCHEMA copy_schema AUTHORIZATION copy_role;
> operation successful (15.921ms)
> CREATE USER user1 WITH PASSWORD 'user1' NAME 'copy user' SCHEMA copy_schema;
> operation successful (233.594ms)
> CREATE USER user2 WITH PASSWORD 'user2' NAME 'copy user' SCHEMA copy_schema;
> operation successful (207.331ms)
> GRANT copy_role TO user1;
> operation successful (8.123ms)
> GRANT copy_role TO user2;
> operation successful (8.272ms)
> 
> >
> > 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
> 
> You mix here different notions for STDIN
> The single statement is not a prelude for reading from 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?
> There are about 100 examples of copy into in the SQL test directory
> for further inspiration.
> 
> >
> > 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