CODEWITHSUNDEEP
hadoophivehiveql
user3118746
user3118746

Reputation:

Complex Data Type issue in Hive

I am trying to create a table in Hive using complex data types.

One of my column is an array of strings and the other is an array of maps.

After I have loaded the data into the table, when I try to query the data, I don't get the desired result in the third column which is an array of maps.

The following is my Hive query:

Step 1:

create table transactiondb2(order_id int,billtype array<string>,paymenttype array<map<string,int>>)ROW FORMAT
DELIMITED FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY '|'
MAP KEYS TERMINATED BY '#';

Step 2:

load data local inpath '/home/xyz/data.txt' overwrite into table transactiondb2;

Step 3:

select * from transactiondb2;

And my output is as follows:

OK

1   ["A","B"]   [{"credit":null,"10":null},{"cash":null,"25":null},{"emi":null,"30":null}]
2   ["C","D"]   [{"credit":null,"157":null},{"cash":null,"45":null},{"emi":null,"35":null}]
3   ["X","Y"]   [{"credit":null,"25":null},{"cash":null,"38":null},{"emi":null,"50":null}]
4   ["E","F"]   [{"credit":null,"89":null},{"cash":null,"105":null},{"emi":null,"85":null}]
5   ["Z","A"]   [{"credit":null,"7":null},{"cash":null,"79":null},{"emi":null,"105":null}]
6   ["D","Y"]   [{"credit":null,"30":null},{"cash":null,"100":null},{"emi":null,"101":null}]
7   ["A","Z"]   [{"credit":null,"50":null},{"cash":null,"9":null},{"emi":null,"85":null}]
8   ["B","Z"]   [{"credit":null,"70":null},{"cash":null,"38":null},{"emi":null,"90":null}]

And my input file data is as follows:

1       A|B     credit#10|cash#25|emi#30
2       C|D     credit#157|cash#45|emi#35
3       X|Y     credit#25|cash#38|emi#50
4       E|F     credit#89|cash#105|emi#85
5       Z|A     credit#7|cash#79|emi#105
6       D|Y     credit#30|cash#100|emi#101
7       A|Z     credit#50|cash#9|emi#85
8       B|Z     credit#70|cash#38|emi#90

Upvotes: 1

Views: 1372

Answers (2)

Subramanyam S
Subramanyam S

Reputation: 59

Create the table as shown below and load the data, then you will get the desired output.

create table complex(id int,bill array<string>,paytype map<string,int>)
ROW FORMAT
DELIMITED FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY '|'
MAP KEYS TERMINATED BY '#';

Upvotes: 0

user3118746
user3118746

Reputation:

I solved it myself.

We need not mention an array of maps explicitly by default it takes values from one map after the other

Upvotes: 1

Related Questions