Hive complex data types

0 votes
Could you please explain hive complex data types with example?
Jul 5 in Big Data Hadoop by Tina
19 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.

image

answered Jul 5 by Gani

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,240 points
58 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
• 67,120 points
227 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
• 67,120 points
408 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
• 67,120 points
214 views
0 votes
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
• 10,110 points
2,047 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
• 10,110 points
196 views
0 votes
10 answers

hadoop fs -put command?

copy command can be used to copy files ...READ MORE

answered Dec 7, 2018 in Big Data Hadoop by Sujay
10,477 views
0 votes
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,240 points
764 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
3,221 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 3, 2018 in Big Data Hadoop by Vardhan
• 11,490 points
55 views