question Re: insert complex data in hive table in Support Questions

insert complex data in hive table

carlospascual92 — Sat, 09 Dec 2017 07:34:28 GMT

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.

Re: insert complex data in hive table

22Sultan — Sat, 09 Dec 2017 16:07:21 GMT

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

Re: insert complex data in hive table

nramanaiah — Mon, 11 Dec 2017 13:25:43 GMT

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

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

Re: insert complex data in hive table

carlospascual92 — Mon, 11 Dec 2017 18:09:18 GMT

Thank you so much!

Re: insert complex data in hive table

nramanaiah — Mon, 11 Dec 2017 18:17:32 GMT

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

Re: insert complex data in hive table

carlospascual92 — Mon, 11 Dec 2017 18:31:25 GMT

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.

Re: insert complex data in hive table

nramanaiah — Mon, 11 Dec 2017 19:10:26 GMT

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