Skip to main content
Solved

JDBC writes to MySQL Decimal type field missing decimal places

  • May 9, 2022
  • 2 replies
  • 94 views
D
Fan

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:

 

Best answer by Joaquín

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.

2 replies

Joaquín
StreamSets Employee
  • JoaquínStreamSets Employee
  • StreamSets Employee
  • Answer
  • May 9, 2022

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.

D
Fan

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.

Terms & ConditionsAccessibility statement