2

I have a hive table as follows:

0: jdbc:hive2://Desktop:10000> desc tb_test2;
+-------------+-------------------------+----------+
|  col_name   |        data_type        | comment  |
+-------------+-------------------------+----------+
| name        | string                  |          |
| score_list  | array<map<string,int>>  |          |
+-------------+-------------------------+----------+

I want to insert data like this:

A   [{"math":100,"english":90,"history":85}]  
B   [{"math":95,"english":80,"history":100}]  
C   [{"math":80,"english":90,"histroy":100}]

I have tried like this:

0: jdbc:hive2://Desktop:10000> insert into tb_test2 values("A",Map("math":100,"english":90,"history":85));

but got the error:

Error: Error while compiling statement: FAILED: ParseException line 1:42 cannot recognize input near '"math"' ':' '100' in constant (state=42000,code=40000)
mck
  • 40,932
  • 13
  • 35
  • 50
needhelp
  • 37
  • 7

1 Answers1

3

I think comma should be used, and you need to specify array too.

insert into tb_test2 select "A", array(map("math",100,"english",90,"history",85)) from (select 1) x;
mck
  • 40,932
  • 13
  • 35
  • 50
  • Error: Error while compiling statement: FAILED: SemanticException [Error 10293]: Unable to create temp file for insert values Expression of type TOK_FUNCTION not supported in insert/values (state=42000,code=10293) – needhelp Jan 02 '21 at 09:32
  • Thanks for your reply,but this answer give me above Error.... – needhelp Jan 02 '21 at 09:33
  • @needhelp try updated answer? it seems necessary to select from a dummy table, as described [here](https://stackoverflow.com/questions/52330141/getting-error-10293-while-inserting-a-row-to-a-hive-table-having-array-as-one-of) – mck Jan 02 '21 at 09:35
  • thanks for your help....is there a way to put 3 maps into only one map? – needhelp Jan 02 '21 at 09:45
  • I don't understand "put 3 maps into one map". Could you show an example of the desired output? – mck Jan 02 '21 at 09:46
  • now I got was:[{"math":100},{"english":90},{"history":85}] ,there are 3 maps,is there a way to put them into one map? I learning a complicated `hive UDF experiment`,so I need this structure.Thanks – needhelp Jan 02 '21 at 09:46
  • like this:[{"math":100,"english":90,"history":85}] – needhelp Jan 02 '21 at 09:46
  • @needhelp sure, I've edited my answer to achieve that. – mck Jan 02 '21 at 09:47
  • Well done man! that `from (select 1) x;` was the missing piece between me and actual `hive` stored data – WestCoastProjects Nov 11 '22 at 01:06