Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

insert complex data in hive table

avatar
New Contributor

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.

1 ACCEPTED SOLUTION

avatar
Expert Contributor

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';

View solution in original post

6 REPLIES 6

avatar
Explorer

@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!

avatar
Expert Contributor

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] |

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

avatar
New Contributor

Thank you so much!

avatar
Expert Contributor

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

avatar
New Contributor

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.

avatar
Expert Contributor

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';