DMS loads last column as 0, Source CSV file in S3, Target Aurora

0

I am facing an issue while loading data from CSV file(s) in S3 -> Aurora MySQL.

While the load completes successfully with all rows, the last column (Total Profit) however is loaded as 0.
The column is decimal type and I have defined it as below in S3 table mapping:
“ColumnType”: “NUMERIC”,
“ColumnPrecision”: “10",
“ColumnScale”: “2"
In the table the column is defined as decimal(10,2)
I have other columns in the file of the same type and they load correctly.
I have used other methods to load the same file and it loads fine.

In the log I can only see one warning however it also doesn't make sense as OrderID is a Integer. In table is it defined as Int(11).
2021-06-25T17:14:33 [TARGET_LOAD ]W: Invalid BC timestamp was encountered in column 'OrderID'. The value will be truncated on the target to the timestamp: 874708545 (csv_target.c:173)

Below is the S3 transformation:
{
"TableCount": "1",
"Tables": [
{
"TableName": "orders",
"TablePath": "div/yyyy-mm-dd/",
"TableOwner": "div",
"TableColumns": [
{
"ColumnName": "OrderID",
"ColumnType": "INT4"
},
{
"ColumnName": "Country",
"ColumnType": "STRING",
"ColumnLength": "50"
},
{
"ColumnName": "Item Type",
"ColumnType": "STRING",
"ColumnLength": "30"
},
{
"ColumnName": "Sales Channel",
"ColumnType": "STRING",
"ColumnLength": "10"
},
{
"ColumnName": "Order Priority",
"ColumnType": "STRING",
"ColumnLength": "5"
},
{
"ColumnName": "Order Date",
"ColumnType": "DATE"
},
{
"ColumnName": "Region",
"ColumnType": "STRING",
"ColumnLength": "80"
},
{
"ColumnName": "Ship Date",
"ColumnType": "DATE"
},
{
"ColumnName": "Units Sold",
"ColumnType": "INT2"
},
{
"ColumnName": "Unit Price",
"ColumnType": "NUMERIC",
"ColumnPrecision": "5",
"ColumnScale": "2"
},
{
"ColumnName": "Unit Cost",
"ColumnType": "NUMERIC",
"ColumnPrecision": "5",
"ColumnScale": "2"
},
{
"ColumnName": "Total Revenue",
"ColumnType": "NUMERIC",
"ColumnPrecision": "10",
"ColumnScale": "2"
},
{
"ColumnName": "Total Cost",
"ColumnType": "NUMERIC",
"ColumnPrecision": "10",
"ColumnScale": "2"
},
{
"ColumnName": "Total Profit",
"ColumnType": "NUMERIC",
"ColumnPrecision": "10",
"ColumnScale": "2"
}
],
"TableColumnsTotal": "14"
}
]
}
Sample data:
535113847,Azerbaijan,Snacks,Online,C,2014-10-08,Middle East and North Africa,2014-10-23,934,152.58,97.44,142509.72,91008.96,51500.76

874708545,Panama,Cosmetics,Offline,L,2015-02-22,Central America and the Caribbean,2015-02-27,4551,437.2,263.33,1989697.2,1198414.83,791282.37

854349935,Sao Tome and Principe,Fruits,Offline,M,2015-12-09,Sub-Saharan Africa,2016-01-18,9986,9.33,6.92,93169.38,69103.12,24066.26

892836844,Sao Tome and Principe,Personal Care,Online,M,2014-09-17,Sub-Saharan Africa,2014-10-12,9118,81.73,56.67,745214.14,516717.06,228497.08

Table definition:

mysql> describe orders;
----------------------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
----------------------------------------------------------+
| OrderID | int(11) | NO | | NULL | |
| Country | varchar(50) | NO | | NULL | |
| Item Type | varchar(30) | NO | | NULL | |
| Sales Channel | varchar(10) | NO | | NULL | |
| Order Priority | varchar(5) | NO | | NULL | |
| Order Date | date | NO | | NULL | |
| Region | varchar(80) | NO | | NULL | |
| Ship Date | date | NO | | NULL | |
| Units Sold | smallint(6) | NO | | NULL | |
| Unit Price | decimal(5,2) | NO | | NULL | |
| Unit Cost | decimal(5,2) | NO | | NULL | |
| Total Revenue | decimal(10,2) | NO | | NULL | |
| Total Cost | decimal(10,2) | NO | | NULL | |
| Total Profit | decimal(10,2) | NO | | NULL | |
----------------------------------------------------------+

Post Load Output:

mysql> select * from orders limit 3;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| OrderID | Country | Item Type | Sales Channel | Order Priority | Order Date | Region | Ship Date | Units Sold | Unit Price | Unit Cost | Total Revenue | Total Cost | Total Profit |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 535113847 | Azerbaijan | Snacks | Online | C | 2014-10-08 | Middle East and North Africa | 2014-10-23 | 934 | 152.58 | 97.44 | 142509.72 | 91008.96 | 0.00 |
| 874708545 | Panama | Cosmetics | Offline | L | 2015-02-22 | Central America and the Caribbean | 2015-02-27 | 4551 | 437.20 | 263.33 | 1989697.20 | 1198414.83 | 0.00 |
| 854349935 | Sao Tome and Principe | Fruits | Offline | M | 2015-12-09 | Sub-Saharan Africa | 2016-01-18 | 9986 | 9.33 | 6.92 | 93169.38 | 69103.12 | 0.00 |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
Kindly suggest

Edited by: DivAWS on Jun 27, 2021 11:20 AM

asked 3 years ago421 views
1 Answer
0

I found the solution to the issue with the help of AWS support.
Use /r/n as your Row delimiter.
It really depends on where you created your data file. In Windows both a CR (/r) and LF(/n) are required to note the end of a line, whereas in Linux/UNIX a LF (/n) is only required.

answered 3 years ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions