Problem with COPY INTO
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
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
- 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"
- 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@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
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
- 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"
- 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@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
A linefeed is not the same as '\n' !!
On 15/04/16 00:13, Knezevic Nikola wrote:
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).
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
- 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)
- 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@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
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
- 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)
- 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@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
On Fri, Apr 15, 2016 at 05:10:32PM +0200, Knezevic Nikola wrote:
Hi Martin,
I had it created, but this still fails
This feature (granting copy from/to) is only available in the Jun2016 version.
Niels
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
- 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)
- 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@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
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Hi Niels,
thanks for the pointer. I'm pretty sure I installed the latest thing from yum, so I went to repeat the process. Also, is Jun2016 some next release? (as we're still in April). If so, it would be great if documentation would mention which version it applies to (it now says "In the feature release following Jul2015", which for me is Jul2015-SP4).
BTW, if I follow the steps from: http://dev.monetdb.org/downloads/epel/ it fails after % yum install http://dev.monetdb.org/downloads/epel/MonetDB-release-epel-1.1-1.monetdb.noa... % yum update http://dev.monetdb.org/downloads/epel/7.2/x86_64/repodata/repomd.xml: [Errno 14] HTTP Error 404 - Not Found
because monetdb.repo has $releasever , which is 7.2 on my system, and server only exposes /7/.
Best, Nikola
On Fri, Apr 15, 2016 at 05:10:32PM +0200, Knezevic Nikola wrote:
Hi Martin,
I had it created, but this still fails
This feature (granting copy from/to) is only available in the Jun2016 version.
Niels
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
- 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)
- 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@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
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
-- Niels Nes, Manager ITF, Centrum Wiskunde & Informatica (CWI) Science Park 123, 1098 XG Amsterdam, The Netherlands room L3.14, phone ++31 20 592-4098 sip:4098@sip.cwi.nl url: https://www.cwi.nl/people/niels e-mail: Niels.Nes@cwi.nl _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
On Fri, Apr 15, 2016 at 05:49:09PM +0200, Knezevic Nikola wrote:
Hi Niels,
thanks for the pointer. I'm pretty sure I installed the latest thing from yum, so I went to repeat the process. Also, is Jun2016 some next release? (as we're still in April). If so, it would be great if documentation would mention which version it applies to (it now says "In the feature release following Jul2015", which for me is Jul2015-SP4).
SP releases aren't bringing new features. Just bug fixes. The Jun2016 will be the next feature release, which is expected in Jun.
Niels
BTW, if I follow the steps from: http://dev.monetdb.org/downloads/epel/ it fails after % yum install http://dev.monetdb.org/downloads/epel/MonetDB-release-epel-1.1-1.monetdb.noa... % yum update http://dev.monetdb.org/downloads/epel/7.2/x86_64/repodata/repomd.xml: [Errno 14] HTTP Error 404 - Not Found
because monetdb.repo has $releasever , which is 7.2 on my system, and server only exposes /7/.
Best, Nikola
On Fri, Apr 15, 2016 at 05:10:32PM +0200, Knezevic Nikola wrote:
Hi Martin,
I had it created, but this still fails
This feature (granting copy from/to) is only available in the Jun2016 version.
Niels
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
- 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)
- 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@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
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
-- Niels Nes, Manager ITF, Centrum Wiskunde & Informatica (CWI) Science Park 123, 1098 XG Amsterdam, The Netherlands room L3.14, phone ++31 20 592-4098 sip:4098@sip.cwi.nl url: https://www.cwi.nl/people/niels e-mail: Niels.Nes@cwi.nl _______________________________________________ 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
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@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
- 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"
- 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@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
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@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@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
- 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"
- 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@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
-- | Stefan.Manegold@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@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Hi Stefan,
thanks for your help, removing ';' helped.
I'm now struggling to load 100+ million of rows via COPY INTO FROM STDIN, but it just fails (no error printed, apart from "0 affected rows"). 10-20 mil rows is ok to load via this method, and I'll see if I can make something that could split up the input into chunks and feed it into mclient.
Cheers, Nikola
Gesendet: Freitag, 15. April 2016 um 00:39 Uhr Von: "Stefan Manegold" Stefan.Manegold@cwi.nl An: "Communication channel for MonetDB users" users-list@monetdb.org Betreff: Re: Problem with COPY INTO
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@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@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
- 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"
- 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@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
-- | Stefan.Manegold@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@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
-- | Stefan.Manegold@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@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Nikola,
You might want to look at Pentaho Data Integration, I've had good success using it to bulk load MonetDB. You can also "loop" within it so you could chunk the dataset.
Bryan
On Fri, Apr 15, 2016 at 11:36 AM, Knezevic Nikola nikkne@gmx.ch wrote:
Hi Stefan,
thanks for your help, removing ';' helped.
I'm now struggling to load 100+ million of rows via COPY INTO FROM STDIN, but it just fails (no error printed, apart from "0 affected rows"). 10-20 mil rows is ok to load via this method, and I'll see if I can make something that could split up the input into chunks and feed it into mclient.
Cheers, Nikola
Gesendet: Freitag, 15. April 2016 um 00:39 Uhr Von: "Stefan Manegold" Stefan.Manegold@cwi.nl An: "Communication channel for MonetDB users" users-list@monetdb.org Betreff: Re: Problem with COPY INTO
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@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@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
- 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"
- 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@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
-- | Stefan.Manegold@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@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
-- | Stefan.Manegold@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@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
participants (6)
-
Bryan Senseman
-
Knezevic Nikola
-
Martin Kersten
-
Martin Kersten
-
Niels Nes
-
Stefan Manegold