Sqoop export to teradata rouding off decimal numbers

[#1]

Hello All,

I am trying to sqoop export a table into Teradata which I had earlier imported into HDFS. On exporting looks like the columns which are defined as decimal (18,2) are getting rounded off. For eg. value 82.5 is exported as 83.

I checked on hdfs files, the value is 82.5 only and also the target table structure on Teradata is same and the column is defined as decimal (18,2).

I am using the usual sqoop export command without any addional options :

sqoop export --connect jdbc:teradata://XXXX --username XXX --password XXX --exportdir XXXXX --table XXX --input-fields-terminated-by '|' --input-null-string '\\N' --input-null-non-string '\\N' -- --output-method batch.insert

Any help would be appreciated.

Thanks.

[#2]

Hi,

Can you double-check that the target table in Teradata DB is defined as decimal(18,2)?

I could reproduce the behavior you see only if the table is defined simply as decimal without scale.

MYUSER=sqoop;
MYPSWD=sqoop;
MYCONN="jdbc:teradata://tdhost/DATABASE=MYDB";

sqoop list-tables --connect $MYCONN --username $MYUSER --password $MYPSWD

sqoop eval --connect $MYCONN --username $MYUSER --password $MYPSWD --query "create table t1_decimal (col1 varchar(20), col2 decimal)"

sqoop eval --connect $MYCONN --username $MYUSER --password $MYPSWD --query "insert into t1_decimal values ('SAMPLE', 1.234)"

sqoop eval --connect $MYCONN --username $MYUSER --password $MYPSWD --query "select * from t1_decimal"

This returns:

-----------------------------------------------
| col1 | col2 | 
-----------------------------------------------
| SAMPLE | 1 | 
-----------------------------------------------

But if I define the table as 

sqoop eval --connect $MYCONN --username $MYUSER --password $MYPSWD --query "drop table t1_decimal"

sqoop eval --connect $MYCONN --username $MYUSER --password $MYPSWD --query "create table t1_decimal (col1 varchar(20), col2 decimal(18,2))"

sqoop eval --connect $MYCONN --username $MYUSER --password $MYPSWD --query "insert into t1_decimal values ('SAMPLE', 1.234)"

sqoop eval --connect $MYCONN --username $MYUSER --password $MYPSWD --query "select * from t1_decimal"

sqoop import --connect $MYCONN --username $MYUSER --password $MYPSWD --table t1_decimal --target-dir t1_decimal --delete-target-dir -m 1 --split-by col1

sqoop eval --connect $MYCONN --username $MYUSER --password $MYPSWD --query "delete from t1_decimal"

hdfs dfs -text t1_decimal/*

This returns:

SAMPLE,1.23

If I export it to Teradata DB table

sqoop export --connect $MYCONN --username $MYUSER --password $MYPSWD --table t1_decimal --export-dir t1_decimal -m 1

sqoop eval --connect $MYCONN --username $MYUSER --password $MYPSWD --query "select * from t1_decimal"

It returns the expected, same value

-----------------------------------------------
| col1 | col2 | 
-----------------------------------------------
| SAMPLE | 1.23 | 
-----------------------------------------------

It maintans the precision with bigger numbers, like 12.2345 -> will be exported like 12.23

My environment is CDH 5.5, TD 14.00

 Miklos Szurap

Customer Operations Engineer

[#3]

Thanks for your response.

Data type was the first thing I checked in the target table. But it is still defined as decimal(18,2). We didn't change anything on the table structure.

We are on CDH 5.4 and TD 15.0

[#4]

Looks like we did hive import on the table initially and the same table when exported back had decimal numbers rounded off.

Apparently, its working fine in case we had a hive table created from HUE on top of imported data.

Not sure why that's happening I am guessing it could be becuase its using metadata as it exports back to TD and in the hive import the data type precision is changed. But we have a temporary solution for now.

[#5]

Hi All,

Just trying to get back to this question and see if there is a solution. Any time we export the data stored in a hive warehouse within the hive table and export it back to TD, it converts the decimal values and rounds it off. Eg. 150.10 would be exported back as 150.00 on the target table in Teradata.

If we just store the file in an HDFS directory and export it back to teradata the values remain the same and there is no desrepancy.

I checked the data type both sides and it is decimal(18,2). 

Let me know of any suggestion or solution.

[#6]

Hello,

I am just posting our solution to this problem. We upgraded our Teradata connector parcel from 1.3c5 to 1.6c5 and it resolved our issues. Recommend upgrading to the latest for avoiding some of these bugs.

Thanks.

[#7]

Thank you for sharing your solution, I'm glad it is resolved. Yes, it is usually advised to check and go with the latest versions - and this is true for the Teradata connector too.