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