SQLImporter:executeMapDCommand:472 - SQL Execute failed - err No negative number in type definition.)


#1

Hello,

I’m new with mapd and I want to import data from an oracle database table via jdbc and SQLImporter to a mapd table but it does not work. The problem seemed to be a negativ number in source table. I tried different data types such as INT, SMALLINT or INTEGER ENCODING FIXED(8) but nothing works.

Has anybody some idea?

Regards!


#2

Hello,

Thank you for trying out MapD.

Can you please tell us what error/exception do you get?

Can we please get the schema of both databases?

Feel free to obfuscate the table/column names.

Thank you.
Vraj


#3

Hi

Please also include the output from the SQLImporter run

you should see something like

java -cp mapd-1.0-SNAPSHOT-jar-with-dependencies.jar:/home/mysql/mysql-connector-java-5.1.39-bin.jar com.mapd.utility.SQLImporter -t test1 -sp rootbeer -su root -c jdbc:mysql://localhost -ss "select * from employees.employees"
2018-06-27 12:59:45 INFO  SQLImporter:executeQuery:200 - Connecting to database url :jdbc:mysql://localhost
Wed Jun 27 12:59:45 PDT 2018 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
2018-06-27 12:59:46 INFO  SQLImporter:executeMapDCommand:471 -  run comamnd :Create table test1(emp_no INTEGER,birth_date DATE,first_name TEXT ENCODING DICT,last_name TEXT ENCODING DICT,gender TEXT ENCODING DICT,hire_date DATE)
2018-06-27 12:59:46 INFO  SQLImporter:executeQuery:262 - Imported 100000 records
2018-06-27 12:59:47 INFO  SQLImporter:executeQuery:262 - Imported 200000 records
2018-06-27 12:59:47 INFO  SQLImporter:executeQuery:262 - Imported 300000 records
2018-06-27 12:59:47 INFO  SQLImporter:executeQuery:271 - result set count is 300024 read time is 1609ms

Based on your error SQLImporter:executeMapDCommand:472 - SQL Execute failed - err No negative number in type definition.) I suspect the create table has got some kind of bogus scale or precision in one of the columns form the metadata from oracle.

There may be a type in your oracle schema that we do not currently support that is included in your select statement

regards


#4

Hi,

here is the output:

2018-06-27 13:42:31 INFO SQLImporter:executeQuery:200 - Connecting to database url :jdbc:oracle:thin:@xxxxx.yyyyyyy.net:1521:
2018-06-27 13:42:32 INFO SQLImporter:executeMapDCommand:467 - run comamnd :Create table kundenpool(…,VG_NR NUMERIC(0,-127),…,UMZUG_ER NUMERIC(0,-127),SCHEIDUNG_HEIRAT_ER NUMERIC(0,-127),…)
2018-06-27 13:42:32 ERROR SQLImporter:executeMapDCommand:472 - SQL Execute failed - TMapDException(error_msg:Exception: No negative number in type definition.)

The problem is at these three columns above with some negative values like -1 for example. What datatype will be correct in mapd?

Achim


#5

Hi

@achim you will need to share your select statement to see if those columns are expressions or if they are direct columns.

What in the sql select statement creates the projected columns VG_NR, UMZUG_ER and SCHEIDUNG_HEIRAT_ER

To work around this for now you should dump your projection into a csv file and use regular COPY FROM to load the data after having created the the table with a corrected CREATE TABLE from the output of SQLImporter

regards


#6

Hi dwayneberry,

the source table is from oracle. The columns are direct columns wit oracle datatype number.

regards