CODEWITHSUNDEEP
hive
M80
M80

Reputation: 994

How to create an Hive External on & delimited Key-Value pair

I have a simple requirement of creating an "Hive external table" on a text file which has data in the format of

colAAA=2&colDDD=1065985&colBBB=valueBB&colCCC=875 COL_NAME=VALUE&COL_NAME=VALUE&COL_NAME=VALUE

I cannot use RegEx Serde as the column names don't come in a defined order. Is there a way to create external table with out writing a new CustomSerde ??

create external table if not exists custom_table_name( colAAA int, colBBB int, colCCC string, colDDD int) row format delimited fields terminated by '&' ????????????? How to make it read the Key-Value ??

I would like to avoid writing CustomSerde unless there is no open-source SERDE available ... Thanks.

Upvotes: 0

Views: 233

Answers (1)

serge_k
serge_k

Reputation: 1782

First, create external table with one map column to parse your data

create external table some_table
(map_col map<string, string>)
row format 
   COLLECTION ITEMS TERMINATED BY '&'
   MAP KEYS TERMINATED BY '='
stored as textfile
location <your_location>

then select map keys of your interest

create table another_table as
select map_col['colAAA'] as colAAA, ...etc
  from some_table

Upvotes: 1

Related Questions