question Hive ignoring data type declarations in create table statement in Archives of Support Questions (Read Only)

Hive ignoring data type declarations in create table statement

joshua_persinge — Thu, 15 Sep 2016 02:16:27 GMT

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?

Re: Hive ignoring data type declarations in create table statement

mqureshi — Thu, 15 Sep 2016 02:28:06 GMT

@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.

Re: Hive ignoring data type declarations in create table statement

joshua_persinge — Thu, 15 Sep 2016 02:50:39 GMT

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

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

Re: Hive ignoring data type declarations in create table statement

joshua_persinge — Thu, 15 Sep 2016 03:05:12 GMT

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