hive查询语句

Hive查询

1、hive模糊搜索表
show tables like '*name*';
2、查看表结构信息
desc formatted tablename;
desc table_name
3、查看分区信息
show partitions tablename;
4、根据分区查询数据
select table_coulm from tablename where partitionname = '2016-02-25';
5、 删除分区
alter table test drop partition(dt='2016-03-01');
alter table  test drop partition(dt='2016-01-17')
6、 杀死某个任务  不在hive shell中执行
Hadoop job -kill job_201403041453_58315
7、 hive命令行操作
执行一个查询,在终端上显示mapreduce的进度，执行完毕后，最后把查询结果输出到终端上，接着hive进程退出，不会进入交互模式。
hive -e 'select table_cloum from table'
-S，终端上的输出不会有mapreduce的进度，执行完毕，只会把查询结果输出到终端上。这个静音模式很实用，,通过第三方程序调用，第三方程序通过hive的标准输出获取结果集。
hive -S -e 'select tablecloum from table'
执行sql文件
hive -f hive_sql.sql
8、查看文件大小及删除文件
Hive>dfs –du /xx/xx
Hive>dfs –rmr /xx/xx
9、mapjoin的使用 应用场景：1.关联操作中有一张表非常小 2、不等值的链接操作
select /*+ mapjoin(A)*/ f.a,f.b from A t join B f  on ( f.a=t.a and f.ftime=20110802) 
10、hive开启简单模式不启用mr
set hive.fetch.task.conversion=more;
11、hive修改表名
ALTER TABLE oldtablename RENAME TO newtablename;
12、 hive添加字段
alter table temp add columns(current_session_timelenth_count bigint comment '页面停留总时长');
ALTER temp CHANGE current_session_timelenth current_session_timelenth bigint comment '当前会话停留时间';
------------------------------------------------------------------------------------------ 
Hive支持大量SQL数据定义语言（Data Manipulation Language，DML）中的现有功能，包括以下各种语句：
使用where条件过滤表的行
使用列或子查询的select表达式
使用等值连接，连接多张表
合并表的所有行或子查询
针对多个“分组”的列进行聚合计算
将查询结果存入另一张表
导出表中的内容到本地目录或HDFS目录中
以下参考自点击打开链接
1、只查询前两条：
select  * from  student  limit  2 ;
2、统计一个表的行数：
select  count(*)  from student ;
3、求一个表id字段的id 之和：
select  sum(id)  from  student ;
4、查询分区表
select  *  from  beauties  where  nation='China' ;     
5、多表关联：
select  t . account ,  u . name , t . income , t . expenses , t . surplus from  user_info  u  join  (select  account ,  sum(income)  as  income ,  sum(expenses) as  expenses , sum(income-expenses)  as  surplus  from 
trade_detail  group  by  account)  t  on  u . account  =  t . account ;
别名
select count(distinct e.uid) from (select * from tablenamewhere
rank <=3 and order =1) e;
小括号中返回的也是一个表,它只是临时的 别名为e
查搜索过"奥巴马" 的用户所搜过的关键字
select  m.uid,m.keyword  from (select  distinct n.uid from
tablenamewhere keyword like '%奥巴马%' n ) m  
where m.uid=n.uid;
查搜索过"奥巴马" 的用户所搜过的不包含"奥巴马"本身的关键字
select m.uid,m.keyword from sogou_20111230 m join (select distinct uid from sogou_20111230 where keyword like '%奥巴马%') n on m.uid=n.uid where m.keyword not like '%奥巴马%';
UNION ALL可以将2个或多个表进行合并
select count(distinct e.uid)from(
select * from tablename where rank<11 
union all 
select * from ext_sogou_20111230_limit3 where rank < 11) e;
6、去重查询：group   by的使用
select  *  from  mytable  group  by  uid ;
Group by 语句通常会和聚合函数一起使用,按照一个或者多个对结果进行分组,然后对每个组执行聚合操作
select year(ts), avg(rank) from tablename where ts like '%2011' group by year(ts);
7、独立UID总数：
select  count(distinct(uid)) from  mytable ; （高效） 或者    select  count(*) from(select  *  from mytable  group  by  uid)  a ;
8、查询频度排名（频度最高的前50）：
select keyword,count(*) as cnt from test group by keyword order by cnt desc limit 50;
9、 添加防止删除的保护：
alter table tablename
> partition (day='0925') enable no_drop;
删除添加的"删除"保护：
alter table tablename
> partition (day='20161207') disable no_drop;
10、添加防止查询的保护：
alter table tablename
> partition (day=20161207') enable offline;
删除防止查询的保护:
alter table tablename
> partition (day='20161207') disable offline;
11、强转:
select cast(rank as DOUBLE) from tablename limit 10;
12、 拼接:
select concat(uid,url) from tablename limit 10;
13、 查找url字符串中的5位置之后字符串str第一次出现的位置
select locate("str",url,5) from tablename limit 100;
14、 抽取字符串str中符合正则表达式url的第5个部分的子字符串
select regexp_extract("str",url,5) from tablename limit 100; 
15、  按照正则表达式"0"分割字符串uid,并将分割后的部分以字符串数组的方式返回
select split(uid,"0") from tablename limit 100;
16、  对字符串url,从0处开截取长度为3的字符串,作为其子字符串
select substr(url,0,3) from tablename limit 3; 
17、 将字符串url中所有的字母转换成大写字母
select upper(url) from tablename limit 3;
18、 where  ..and  或者 where ....or   where的 两种条件查询 
select * from  tablename where rank<=3 and order =1 limit 3;
select * from  tablenamewhere rank !=0 or order =1 limit 3;
19、like 过滤字符串 
select *  from  tablename where url like '%http%' limit 10;
rlike 通过Java的正则表达式过滤  *与%功能一样 ，它是hive中扩展功能的操作符
select * from tablenamewhere url rlike  ' .*http.* ' limit 3; 
20、left semi-join 左半表 semi 半挂的 半独立的
select * from be where rank in(1,2,5);
select  * from  tablenamem left semi join  ext_sogou_20111230_limit3  n on m.rank=n.rank;
21、视图 hive只支持逻辑视图 作用降低查询复杂度
创建视图
create view sogou_view  as 
select * from tablenamewhere rank <=3;
22、 索引 
Hive的索引需要单独创建表实现
创建索引
CREATE INDEX employees_index ON TABLE employees (name) AS 
'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler'
WITH DEFERRED REBUILD IDXPROPERTIES('creator' = 'me','
created_at '='some  time') IN TABLE employees_index_table;
23、上午7-9点之间，搜索过“百度”的用户，哪些用户直接点击了百度的URL
老师:
select distinct n.uid from (select * from sogou_view where keyword ='百度')
and  substr(ts,9,2) in ('07','08','09')) n where n.url like '%baidu.com%';
select uid  from sogou_view where (cast(substr(ts,9,2) 
as int)>7  or cast(substr(ts,9,2) as int)<9) and url 
like '%www.ganji.com%' or keyword like '%百度%' ;
select uid  from sogou_view where substr(ts,9,2) in ('07','08','09') and url
like '%www.ganji.com%' and  keyword like '%百度%' ;