Hive(一)

//下面创建的是内部表（管理表）
create table product(
name string,price double,time string,market string,
province string,city string)
row format delimited
fields terminated by '\t'
location '/in/product';


hadoop fs -put product.txt /in/product/
select * from produce limit 10;
create table stu(
name string,age int,sex string);
===========================================
创建搜狗日志表
show databases;  显示所有的数据库
use test; 指定当前使用的数据库
show tables; 显示当前数据库下所有的数据表
//创建外部表
create external table if not exists sogou
(time string,uid string,keyword string,rank int,
order int,url string)
row format delimited
fields terminated by '\t'
location '/in/sogou';


show create table sogou; //显示创建表的信息
desc[ribe] sogou;  //显示表结构信息


内部表与外部表的区别：
1、删除内部表时，与表相对应的数据文件也会随之删除；
删除外部表时，与表相对应的数据文件不会删除，只是删除了表对
数据文件的引用。
2、创建外部表，是对某一数据文件进行引用；
   创建内部表，数据文件事前不能存在，若存在，会报异常。


表重命名:alter table 旧名 rename to 新名；
alter table product rename to pro;
删除表：drop table 表名
drop table pro;


添加数据的方法：
1、上传数据到hdfs目录下
hadoop fs -put stu /user/hive/warehouse/test.db/stu
2、加载linux本地数据
load data local inpath '/home/zkpk/stu' 
overwrite into table stu;
3、通过查询语句向表中插入数据
insert overwrite table sogou_201608 select * from sogou limit 10;
4、单个查询语句中创建表并加载数据，注意as关键字
create table sogoutest as select * from sogou_201608;


create table if not exists sogou_201608
(time string,uid string,keyword string,rank int,
order int,url string)
row format delimited
fields terminated by '\t';


创建表，表的结构与已知表相同
create table stu2 like stu;
表重命名
alter table stu2 rename to stu3;
修改列
alter table stu3 change column name stuname int;
增加列
alter table stu3 add columns (score int);
替换列
alter table stu3 replace columns (
name string,age int,sex string);
===============================================
搜狗日志分析系统：
一、实现数据分析需求一：条数统计
数据总条数:需要用到聚合函数count()统计计数
select count(*) from sogou;
关键字非空查询条数
select count(*) from sogou 
where keyword is not null and keyword !='';
无重复总条数（根据time、uid、keyword、url）
select count(*) from 
(select * from sogou group by time,uid,keyword,url 
 having count(*)=1) s;
group by 分组查询，也具有去重的功能；后面可以跟多个查询列
having 分组筛选，需要在分组查询的基础之上进行筛选
独立UID总数
select count(distinct(uid)) from sogou;


二、实现数据分析需求二：关键词分析
查询关键词长度统计：length()
select keyword,length(keyword) from sogou;
查询关键词的平均长度：avg()
select keyword,avg(s.cnt) from 
(select keyword,length(keyword) as cnt from sogou) s
group by keyword;
as关键词用于给列起别名
查询频度排名（频度最高的前50词）
select keyword,count(*) as cnt from sogou
group by keyword order by cnt desc limit 50;


三、实现数据分析需求三：UID分析
1、UID的查询次数分布（查询1次的UID个数，…查询N次的UID个数）
count()计数
sum()求和
if(条件判断,条件成立执行这部分,条件不成立执行这部分)


select
sum(if(s.cnt=1,1,0)),
sum(if(s.cnt=2,1,0)),
sum(if(s.cnt=3,1,0)),
sum(if(s.cnt>3,1,0)) from 
(select uid,count(*) as cnt from sogou group by uid) s;


UID平均查询次数    uid的查询次数之和/uid的总数
select sum(s.cnt)/count(s.uid) from 
(select uid,count(*) as cnt from sogou group by uid) s;


查询次数大于2次的用户总数
select count(s.uid) from 
(select uid,count(*) as cnt from sogou 
group by uid having cnt >2)s;


查询次数大于2次的用户占比
A UID总数：
select count(distinct(uid)) from sogou;
B UID 2次以上的数量：
select count(s.uid) from
(select uid,count(*) as cnt from sogou
group by uid having cnt >2)s
结果C=B/A


查询次数大于2次的前50条数据展示(join)
第一个数据集：查询次数大于2次的用户
第二个数据集：sogou全部的数据
select b.* from 
(select uid,count(*) as cnt from sogou group by uid
having cnt>2) a join sogou b on a.uid=b.uid limit 50;


join(内联接):返回两个数据集中的共同项（匹配项）


统计搜索过“%仙剑奇侠传%”的用户还搜索过哪些关键词(join)
第一个数据集：搜索过“%仙剑奇侠传%”的用户
第二个数据集：sogou全部的数据
select n.uid,n.keyword from 
(select distinct(uid) from sogou 
where keyword like '%仙剑奇侠传%') m join sogou n 
on m.uid=n.uid where n.keyword not like '%仙剑奇侠传%';


四、实现数据分析需求四：用户行为分析
点击次数与Rank之间的关系分析


Rank在10以内的点击次数占比
A：select count(*) from sogou where rank < 11;
B：select count(*) from sogou;
占比：A/B


直接输入URL作为查询词的比例
（1）直接输入URL查询的比例
A：select count(*) from sogou where keyword like '%www%';
B：select count(*) from sogou;
占比：A/B
（2）直接输入URL的查询中，点击的结果就是用户输入的URL的网址所占的比例
C：直接输入URL的查询中，点击的结果就是用户输入的URL网址的个数
instr():字符或字符串在另一个字符串中的位置
sum()
if()
select sum(if(instr(url,keyword)>0,1,0)) from 
(select * from sogou where keyword like '%www%') s;
占比：C/A
独立用户行为分析
（1）查询搜索过”仙剑奇侠传“的uid，并且次数大于3
select uid,count(*) as cnt from sogou 
where keyword like '%仙剑奇侠传%'
group by uid having cnt >3;


（2）查找uid是653d48aa356d5111ac0e59f9fe736429
和e11c6273e337c1d1032229f1b2321a75的相关搜索记录


select * from sogou where uid ='653d48aa356d5111ac0e59f9fe736429';
select * from sogou where uid='e11c6273e337c1d1032229f1b2321a75';


五、实现数据分析需求五：实时数据
每个UID在当天的查询点击次数
（1）创建临时表
create table if not exists sogoutemp
(uid string,cnt int)
row format delimited
fields terminated by '\t';
（2）查询并插入
insert overwrite table sogoutemp 
select uid,count(*) as cnt from sogou 
group by uid limit 50;