Tuesday, November 24, 2015

INTRODUCTION

Usually, we will be loading the data into hive table from HDFS file system while creating the table.

This post will help to insert values into hive table directly.

ARRAY COLUMN

Create Table Query

DROP TABLE IF EXISTS student;

CREATE TABLE student(id STRING, name STRING, class STRING, subjects ARRAY<STRING>);

The latest hive version will not support adding the values directly using below query.

INSERT INTO student (id,name,class,subjects) VALUES ('100','Ranjith','Mathematics',ARRAY('Mathematics','Biology','Physiscs','Chemistry')

But we can use STACK function to insert here.

INSERT INTO TABLE student

SELECT STACK(3,

'100','Ranjith','Mathematics',ARRAY('Mathematics','Biology','Physiscs','Chemistry'),

'200','Sekar','Computer Science',ARRAY('Mathematics','Computer Science','Physiscs','Chemistry'),

'300','Bala','Commerce',ARRAY('Political Science','Accounts','Commerce')

)

FROM empinfo

LIMIT 3;

Result

MAP COLUMN

Create Table Query

DROP TABLE IF EXISTS stud_mark1;

CREATE TABLE stud_mark1(id STRING, name STRING, class STRING, mark MAP<STRING,INT>);

Insert query.

INSERT INTO TABLE stud_mark1

SELECT STACK(2,

'100','Ranjith','Mathematics',map("Mathematics",78,"Biology",88,"Physics",89,"Chemistry",90),

'200','Sekar','Science',map("Botany",71,"Zoolagy",98,"Physics",79,"Chemistry",89)

)

FROM empinfo

LIMIT 2;

Result