Casting String->Fixed precision Decimal

Question

I have the following problem in Hive: I have a table stored as a Textfile with all the fields being of STRING type. I want to convert this table in an ORC table, but some of the STRING fields must be cast to decimal with precision = 3. Th problem is that the comma is not already there in the initial string field, so I am looking to see if there is a way to tell Hive to put this decimal 3 positions before the end of the string :-).

So my HiveSql commands look like this:

CREATE my_orc_table(entry1 STRING, entry2 DECIMAL(10,3)) STORED AS ORC;
INSERT INTO TABLE my_orc_table SELECT * FROM my_text_table;

So the problem is that if I have 00050000 in entry2 of my TextTable, I want to obtain 50.0 in my ORC table. For the moment I have 50000 (I suppose that Hive put the comma at the end of my string, which is quite logic, but not what I am looking for).

I tried to google a bit but I did not really find the solution.

Thank you :-) !

Answer 1

What about..

select cast(entry2 AS DECIMAL)/1000.0