insert complex data in hive table

[#1]

Hi!

I have created a table in hive like this:

create table if not exists TablaCatalogo1 (name string, species string, varietal string, provenance string, toast string, process string, formatt array<string>) row format delimited fields terminated by ':' collection items terminated by ',' lines terminated by '\n';

in which formatt is a array of string.

Now, I want to insert a new value in this array for a certain product. I tried to use a dummy table to insert the value but I didn´t get. Any way to do it?

Thank you so much.

[#2]

@Carlos Pascual

hi there,

you can add new values to the table by using update command... as i am giving an example,

UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value

Note: the WHERE clause in the UPDATE syntax: The WHERE clause specifies which record or records that should be updated. If you omit the WHERE clause, all records will be updated!

[#3]

You can create a data file with delimiters and use load command to load the data into table as mentioned in below example.

eg., create table test1(name string, age int, dept array<string>) row format delimited fields terminated by ':' collection items terminated by ',' stored as textfile;

Content in the text file should be delimited as follows

a:25:sales,marketing

b:26:dev,qa

...

name(string):age(int):dept(array<string> delimieted with ,)

0: jdbc:hive2://abcd:10000> load data inpath 'file_path' into table test1;

0: jdbc:hive2://abcd:10000> select * from test1;

+-----------+----------------+--------------------+--+

| test1.name | test1.age | test1.dept |

+-----------+----------------+--------------------+--+

| a | 25 | [sales,marketing] |

| b | 26 | [dev,qa] |

+-----------+----------------+--------------------+--+

[#4]

Thank you so much!

[#5]

Glad to know that your problem is resolved. Can you please accept this answer.

[#6]

I think I didn´t explain my problem well. Apologies in advance. I will try to explain it well.

I have the next table:

create table test1(name string, age int, dept array<string>) row format delimited fields terminated by ':' collection items terminated by ',' stored as textfile;

Now, I load this next file:

a:25:sales,marketing

b:26:dev,qa

With this file loaded, I want to update the table so that, for example, in the first line the array dept is [marketing, FINANCE].

I mean, I want to add a new value in this array.

Thank you in advance.

[#7 · Accepted solution]

you can try below command for updating existing array data

If you want to overwrite existing value with new value for the array column, you can use below update statement

update test1 set dept=array('marketing', 'FINANCE') where name='a';

If you want to append new value, then you can use below update statement.

update test1 set dept=split(concat_ws(',',dept,'FINANCE'),',') where name ='a';