Hive学习实战
--------------------------------------------------------------
Hive从入门到实战【40讲】---笔记记录
--------------------------------------------------------------
hive命令
1、show tables;
2、show databases;
3、 desc login; ---查看表结构。
4、 show partitions test5; --查看分区
create table login_struct(
ip string,
user struct<uid:bigint,name :string>
)
partitioned by (dt string)
row format delimited
fields terminated by ','
collection items terminated by '|'
stored as textfile;
load data local inpath
'/hadoop/hive0.9/testHive/login_struct.txt'
into table login_struct partition (dt='2015-02-07');
第27天: lateral View语法
lateral view 用于split explode等
UDTF一起使用。
它能够将一行数据拆成多行数据,
在此基础上可以对拆分后的数据进行聚合
lateral view首先为原始表的每行调用
UDTF ,UDTF会把一行拆分
成一或者多行,lateral view
在把结果组合,产生一个支持别名表的虚拟表。
例子
create table pageAds(
pageid string,
ad_list array<int>
)
row format delimited
fields terminated by ','
collection items terminated by ':'
front_page,1:2:3
contact_page,3:4:4
load data local inpath
'/hadoop/hive/testhivew/a3.txt'
into table pageAds ;
select pageid, adid
from pageAds lateral view
explode(ad_list) adTable as adid;
hive> select * from pageAds;
front_page [1,2,3]
contact_page [3,4,4]
经过latera view explode之后得到的结果
front_page 1
front_page 2
front_page 3
contact_page 3
contact_page 4
contact_page 4
把列转为行了。。牛逼。
接下来是聚和统计
select adid, count(0)
from pageAds lateral view
explode (ad_list) adTable as adid
group by adid;
结果
2 1
4 2
1 1
3 2
------------------------------------------
------------------------------------------
create table baseTable(
col1 array<int>,
col2 array<string>
)
row format delimited
fields terminated by ','
collection items terminated by ':'
load data local inpath
'/hadoop/hive/testhivew/a4.txt'
into table baseTable ;
第一列拆分
select myCol1,col2
from baseTable lateral view
explode (col1) adTable as myCol1 ;
第二列拆分分
select myCol1,myCol2
from baseTable lateral view
explode (col1) adTable as myCol1
lateral view
explode (col2) adTable as myCol2 ;
--------------------------------------------------------------
Hive从入门到实战【40讲】---笔记记录
--------------------------------------------------------------
hive命令
1、show tables;
2、show databases;
3、 desc login; ---查看表结构。
4、 show partitions test5; --查看分区
create table login_struct(
ip string,
user struct<uid:bigint,name :string>
)
partitioned by (dt string)
row format delimited
fields terminated by ','
collection items terminated by '|'
stored as textfile;
load data local inpath
'/hadoop/hive0.9/testHive/login_struct.txt'
into table login_struct partition (dt='2015-02-07');
第27天: lateral View语法
lateral view 用于split explode等
UDTF一起使用。
它能够将一行数据拆成多行数据,
在此基础上可以对拆分后的数据进行聚合
lateral view首先为原始表的每行调用
UDTF ,UDTF会把一行拆分
成一或者多行,lateral view
在把结果组合,产生一个支持别名表的虚拟表。
例子
create table pageAds(
pageid string,
ad_list array<int>
)
row format delimited
fields terminated by ','
collection items terminated by ':'
front_page,1:2:3
contact_page,3:4:4
load data local inpath
'/hadoop/hive/testhivew/a3.txt'
into table pageAds ;
select pageid, adid
from pageAds lateral view
explode(ad_list) adTable as adid;
hive> select * from pageAds;
front_page [1,2,3]
contact_page [3,4,4]
经过latera view explode之后得到的结果
front_page 1
front_page 2
front_page 3
contact_page 3
contact_page 4
contact_page 4
把列转为行了。。牛逼。
接下来是聚和统计
select adid, count(0)
from pageAds lateral view
explode (ad_list) adTable as adid
group by adid;
结果
2 1
4 2
1 1
3 2
------------------------------------------
------------------------------------------
create table baseTable(
col1 array<int>,
col2 array<string>
)
row format delimited
fields terminated by ','
collection items terminated by ':'
load data local inpath
'/hadoop/hive/testhivew/a4.txt'
into table baseTable ;
第一列拆分
select myCol1,col2
from baseTable lateral view
explode (col1) adTable as myCol1 ;
第二列拆分分
select myCol1,myCol2
from baseTable lateral view
explode (col1) adTable as myCol1
lateral view
explode (col2) adTable as myCol2 ;