Solved

JDBC writes to MySQL Decimal type field missing decimal places

D
Rank

source data: csv format data file

The data in the red box has two decimal places

MySQL DDL:

CREATE TABLE test."LINEITEM" (
    "L_ORDERKEY" BIGINT NOT NULL,
    "L_PARTKEY" INTEGER NOT NULL,
    "L_SUPPKEY" INTEGER NOT NULL,
    "L_LINENUMBER" INTEGER NOT NULL,
    "L_QUANTITY" DECIMAL(15, 2),
    "L_EXTENDEDPRICE" DECIMAL(15, 2),
    "L_DISCOUNT" DECIMAL(15, 2),
    "L_TAX" DECIMAL(15, 2),
    "L_RETURNFLAG" VARCHAR(1),
    "L_LINESTATUS" VARCHAR(1),
    "L_SHIPDATE" DATE,
    "L_COMMITDATE" DATE,
    "L_RECEIPTDATE" DATE,
    "L_SHIPINSTRUCT" VARCHAR(25),
    "L_SHIPMODE" VARCHAR(10),
    "L_COMMENT" VARCHAR(44)
);

I use  Directory read csv data file and use JDBC Producer write the data to MySQL

but the result missing decimal places:

 

icon

Best answer by Joaquín 9 May 2022, 09:47

View original

2 replies

D
Rank

Hello, this happens because you need to explicitly convert the field types from the CSV file to the types you are using. Try adding a Field Type Converter between both stages and converting the fields to the DECIMAL type.

Thank you for your reply! I will try.

Joaquín
Rank

Hello, this happens because you need to explicitly convert the field types from the CSV file to the types you are using. Try adding a Field Type Converter between both stages and converting the fields to the DECIMAL type.

Reply


Terms & Conditions