Scribd
Upload
6 views2 pages

Hive Table Creation and Joins Guide

The document discusses various Hive operations like creating and loading tables, partitioning tables, setting dynamic partition properties, performing joins between tables using MAPJOINs and different types of outer joins like left and right outer joins.

Uploaded by

dharmendard
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
6 views2 pages

Hive Table Creation and Joins Guide

The document discusses various Hive operations like creating and loading tables, partitioning tables, setting dynamic partition properties, performing joins between tables using MAPJOINs and different types of outer joins like left and right outer joins.

Uploaded by

dharmendard
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

1. Creating table in Hive.

> create table w1 (wban int, date string, stationtype string)


> row format delimited
> fields terminated by ',';
2. load data inpath '/usr/hdfs/weather/[Link]' into table w1;
3. creating partitioning table.
>create table part1(date string, stationtype string)
> partitioned by (wban int)
> row format delimited fields terminated by ',';
4. Dynamic partition Properties.
>SET [Link] = true;

>SET [Link] = nonstrict;


5. partitioning with table w1
> insert overwrite table part1 partition(wban='03011')
> select date, stationtype from w1 where wban='03011';
6. For auto join conversion
> set [Link]=true;
7. Map Join in hive.
---> MAPJOINs are processed by loading the smaller table into an in-memory hash map and
matching keys with the larger table as they are streamed through.
8. > create table mpj (wban int)
> row format delimited
> fields terminated by ',';
9. Join query
> insert overwrite table mpj
> select count(*) from
> w1 JOIN mont2 on ([Link] = [Link]);
10. OUTER JOINS
→ Left Outer Join:
> create table monthly (WBAN int, YearMonth string, AvgMaxTemp string)
row format delimited
fields terminated by ',';
>load data local inpath '/home/vis/Documents/weather/[Link]' into table
monthly;
>create table hourly (WBAN int, Date string, Stationtype string)
row format delimited
fields terminated by ',';
LOJ join query:
> select [Link], [Link] from monthly LEFT OUTER JOIN hourly
ON ([Link]=[Link])
where [Link]='20130101';
→ create table lftjoin(wban int, stationtype string) row format delimited fields
terminated by ',';

>insert overwrite table lftjoin


> select [Link], [Link] from hourly
> LEFT OUTER JOIN monthly ON ([Link]=[Link])
> where [Link]='20130104';
11. Right Outer JOIN

You might also like