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.