Support Questions
Find answers, ask questions, and share your expertise

Hive ignoring data type declarations in create table statement

Contributor

I am creating an external table with some fields that are numeric decimals. I have tried declaring them as floats, doubles, and decimal formats, but hive is casting them as strings regardless.

Here is a sample of the query:

create external table sapbw.copa_ods(currency_type string, cost_for_fi decimal, gross_margin_fi decimal, sales_for_fi decimal)

ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'

stored as textfile

location '/data_LZ/BW/COPA_ODS';

Why is this happening?

1 ACCEPTED SOLUTION

Accepted Solutions

Contributor

Found the culprit, it's the CSVSerde, it casts all fields as strings.

View solution in original post

3 REPLIES 3

Super Guru
@Josh Persinger

May be someone else can confirm but I think you are storing the data as text format so it is storing it as a string. When loading the data in memory it will be loaded as decimal. The on disk representation varies based on file format.

Try creating data in Avro or ORC format and see if it retains the data type as Decimal.

Contributor

I just tried following the method shown here: https://docs.hortonworks.com/HDPDocuments/HDP2/HDP-2.3.0/bk_dataintegration/content/moving_data_from...

I created the ORC table and it still stores everything as a string

Contributor

Found the culprit, it's the CSVSerde, it casts all fields as strings.

View solution in original post