Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

insert complex data in hive table

Solved Go to solution

insert complex data in hive table

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

Accepted Solutions
Highlighted

Re: insert complex data in hive table

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
Highlighted

Re: insert complex data in hive table

New Contributor

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

Highlighted

Re: insert complex data in hive table

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

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

Highlighted

Re: insert complex data in hive table

New Contributor

Thank you so much!

Highlighted

Re: insert complex data in hive table

Expert Contributor

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

Highlighted

Re: insert complex data in hive table

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.

Highlighted

Re: insert complex data in hive table

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

Don't have an account?
Coming from Hortonworks? Activate your account here