Hive中字符串转Decimal(10,3)：指定小数点位置问题求助

Got it, let's tackle this conversion issue you're facing. The problem here is that when you directly cast the string entry2 to DECIMAL(10,3), Hive treats the entire string as an integer—so 00050000 becomes 50000 instead of 50.000. We need to explicitly tell Hive where to place that decimal point before casting.

Here are two reliable solutions to get the desired result:

Solution 1: Numeric Calculation (Simplest Approach) #

Since you want the decimal point 3 places from the end, this is equivalent to dividing the numeric value of the string by 1000. Using BIGINT for the initial cast avoids floating-point precision issues with longer strings:

INSERT INTO TABLE my_orc_table 
SELECT 
    entry1,
    -- Convert string to BIGINT, divide by 1000.0 to get decimal, then cast to target type
    cast(cast(entry2 as bigint) / 1000.0 as decimal(10,3)) as entry2
FROM my_text_table;

This works seamlessly even for shorter strings—for example, if entry2 is 123, it will convert to 0.123, which aligns with your requirement of placing the decimal 3 positions from the end.

Solution 2: String Manipulation (Explicit Control) #

If you prefer to handle the string directly to visualize the decimal placement, you can split the string at the 3rd character from the end, add a decimal point, then cast to DECIMAL:

INSERT INTO TABLE my_orc_table 
SELECT 
    entry1,
    cast(
        concat(
            substr(entry2, 1, length(entry2)-3),  -- Part before decimal
            '.',
            substr(entry2, length(entry2)-2)      -- Last 3 characters (decimal part)
        ) as decimal(10,3)
    ) as entry2
FROM my_text_table;

Notes to Keep in Mind #

• Clean Invalid Data: If your entry2 string has non-numeric characters, add a cleanup step first (e.g., regexp_replace(entry2, '[^0-9]', '') to remove non-digits).
• Precision Check: Ensure your original string values don't exceed the capacity of DECIMAL(10,3)—this type supports up to 7 digits before the decimal point (since 10 total digits minus 3 decimal places).

内容的提问来源于stack exchange，提问作者Louis