Hive complex data types

0 votes
Could you please explain hive complex data types with example?
Jul 5, 2019 in Big Data Hadoop by Tina
15,304 views

1 answer to this question.

0 votes

There are three complex types in hive,

arrays: It is an ordered collection of elements.The elements in the array must be of the same type.

map: It is an unordered collection of key-value pairs.Keys must be of primitive types.Values can be of any type.

struct: It is a collection of elements of different types.

Examples: complex datatypes

ARRAY:

$ cat >arrayfile

1,abc,40000,a$b$c,hyd

2,def,3000,d$f,bang


hive> create table tab7(id int,name string,sal bigint,sub array<string>,city string)

    > row format delimited

    > fields terminated by ','

    > collection items terminated by '$';


hive>select sub[2] from tab7 where id=1;


hive>select sub[0] from tab7;


MAP:


$ cat >mapfile

1,abc,40000,a$b$c,pf#500$epf#200,hyd

2,def,3000,d$f,pf#500,bang


hive>create table tab10(id int,name string,sal bigint,sub array<string>,dud map<string,int>,city string)

row format delimited

fields terminated by ','

collection items terminated by '$'

map keys terminated by '#';


hive> load data local inpath '/home/training/mapfile' overwrite into table tab10;


hive>select dud["pf"] from tab10;


hive>select dud["pf"],dud["epf"] from tab10;


STRUCT:


cat >mapfile

1,abc,40000,a$b$c,pf#500$epf#200,hyd$ap$500001

2,def,3000,d$f,pf#500,bang$kar$600038


hive> create table tab11(id int,name string,sal bigint,sub array<string>,dud map<string,int>,addr struct<city:string,state:string,pin:bigint>)

    > row format delimited

    > fields terminated by ','

    > collection items terminated by '$'

    > map keys terminated by '#';


hive> load data local inpath '/home/training/structfile' into table tab11;


hive>select addr.city from tab11;

I have attached one script file(complex_table.script) containing hive create table command to create a table with complex fields like map, array of strings, structures, and attached one sample dataset(complex.dat) containing complex data.

Now I have used comma "," to separate the fieldscolon ":" to separate the collection items, and question mark "?" to separate the map keys.

First load the dataset(attached here) in HDFS and then please go through the script and execute the commands one after another.

I have uploaded the dataset(only two rows) into HDFS, see in the screenshot below,

image

Now see I have created table ,then load the data from HDFS and executed the select query.

Master data integration and transformation with our comprehensive Informatica Course.

image

answered Jul 5, 2019 by Gani
Union is complex data type or not in hive
Hi, @There,

Do you want to know more about Union or data types?

Hey, @There,

UNION type in Hive is similar to the UNION in C. UNION types at any point of time can hold exactly one data type from its specified data types.

Hive complex data types such as arrays, maps, and structs are a composite of primitive or complex data types. Informatica Developer represents complex data types with the string data type and uses delimiters to separate the elements of the complex data type.

Related Questions In Big Data Hadoop

0 votes
1 answer

Why Java Code in Hadoop uses own Data Types instead of basic Data types?

Hadoop provides us Writable interface based data ...READ MORE

answered Apr 15, 2018 in Big Data Hadoop by kurt_cobain
• 9,390 points
868 views
0 votes
1 answer

Hadoop Hive: How to insert data in Hive table?

First, copy data into HDFS. Then create ...READ MORE

answered Nov 12, 2018 in Big Data Hadoop by Omkar
• 69,210 points
9,448 views
0 votes
1 answer

Hadoop Hive Hbase: How to insert data into Hbase using Hive (JSON file)?

You can use the get_json_object function to parse the ...READ MORE

answered Nov 15, 2018 in Big Data Hadoop by Omkar
• 69,210 points
2,626 views
0 votes
1 answer

Not able to insert data into hive table

Seems like a hive version problem.​ insert operation is ...READ MORE

answered Dec 14, 2018 in Big Data Hadoop by Omkar
• 69,210 points
2,602 views
+1 vote
1 answer

Hadoop Mapreduce word count Program

Firstly you need to understand the concept ...READ MORE

answered Mar 16, 2018 in Data Analytics by nitinrawat895
• 11,380 points
10,555 views
0 votes
1 answer

hadoop.mapred vs hadoop.mapreduce?

org.apache.hadoop.mapred is the Old API  org.apache.hadoop.mapreduce is the ...READ MORE

answered Mar 16, 2018 in Data Analytics by nitinrawat895
• 11,380 points
2,184 views
+2 votes
11 answers

hadoop fs -put command?

Hi, You can create one directory in HDFS ...READ MORE

answered Mar 16, 2018 in Big Data Hadoop by nitinrawat895
• 11,380 points
104,185 views
–1 vote
1 answer

Hadoop dfs -ls command?

In your case there is no difference ...READ MORE

answered Mar 16, 2018 in Big Data Hadoop by kurt_cobain
• 9,390 points
4,260 views
+2 votes
5 answers

How to transpose/pivot data in hive?

Below is also a way for Pivot SELECT ...READ MORE

answered Oct 12, 2018 in Big Data Hadoop by Rahul
19,056 views
+3 votes
1 answer

Getting Connection Error while loading data into table using cloudera hive

Hey Nafeesa, Itseems that Hive is not able ...READ MORE

answered Oct 4, 2018 in Big Data Hadoop by Vardhan
• 13,190 points
700 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP