hive转16进制unhex_Hive，

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 '%百度%' ;

select

Hive-SQL的基本结构同标准SQL相差不大,结构如下:

select * from employee

where sex_age.sex='Male'

/*支持使用distinct去重*/

select distinct name,sex_age.sex as sex from employee;

select distinct * from employee; // supported after release 1.1.0

/*hive 0.7.0之后，支持having子句*/

SELECT col1 FROM t1 GROUP BY col1 HAVING SUM(col2) > 10;

/*支持limit子句，限制返回行数*/

SELECT * FROM t1 LIMIT 5; // 随机返回5行

/*返回Top K行*/

SET mapred.reduce.tasks = 1

SELECT * FROM sales SORT BY amount DESC LIMIT 5

在Hive-SQL中,也支持嵌套查询及子查询,但是,不允许出现多重嵌套或多重子查询的情况,常用的子查询方式如下:

/*通过with语句,子查询必须申明别名*/

with t1 as (

select * from employee

where sex_age.sex='Male'

)

select name,work_place from t1;

/*子查询放置在from子句,子查询必须申明别名*/

select name,sex_age.sex from

(

select * from employee

where sex_age.sex='Male'

) t1;

/*

子查询放置在where子句,在where子句中的子查询支持in,not in,exists,not exists,但需要注意的是,使用in或not in时,

子查询只能返回一个字段，且使用in/not in不能同时涉及内/外部表

*/

select name,sex_age.sex from

employee a // 外部表必须申明别名，不然Hive报错

where a.name in (

select name from employee

where sex_age.sex='Male'

);

/*

ERROR:

in/not in不能同时涉及内/外部表，exists/not exists没有这个限制

*/

select name,sex_age.sex from

employee a

where a.name in (

select name from employee

where sex_age.sex!=a.sex_age

);

另外，where子句可以支持任何Boolean表达式及User Define Function。

Partition Based Queries

想要触发hive的分区查询，必须满足以下两个条件：

- 建表时使用了partitioned by

- where条件中包含分区字段，或者join语句的on子句包含分区字段

因为hive只会扫描特定分区的数据，所以，分区查询可以提高查询的速度。

REGEX Column Specification

hive支持通过正则表达式来声明查询字段。

SELECT `(ds|hr)?+.+` FROM sales

Hive 操作数据库语句总结

2017年06月14日 14:53:14

阅读数：1944

转载地址：http://blog.csdn.net/xiaoshunzi111/article/details/48727831

1、创建一个表，字段之间用 \t 分隔；

Hive>create  table  student (id  int,  name  string) row  format  delimited  fields  terminated  by '\t' ;

2、将本地一个数据提交到hive里去

hive>load data local inpath '/home/student.txt' into  table  student ;

2.1 增加分区表数据

alter table ZHIYOUBAO.CHECK_RECORD add  partition (years='xxxx',months='xx',days='xx') location                         '/ZYB/CHECK_RECORD/yy=xxxx/mm=xx/dd=xx/';

3、查询表里的数据：

hive>select  *  from  student ;

4、只查询前两条：

hive>select  * from  student  limit  2 ;

5、统计一个表的行数：

hive>select  count(*)  from student ;

6、求一个表id字段的id 之和：

hive>select  sum(id)  from  student ;

7、创建外部表：

hive>create  external  table  ext_student  (id int, name string) row format delimited  fields  terminated  by  ' \t  '  location  ' /data ' ;     //这样就不必将文件放到hive里去 就可以对其进行操作了   ，只需要将文件放到hdfs上的/data目录下面。

8、内部表先有表后有数据；外部表先有数据后有表。

9、创建分区表：

hive>create external table beauties  (id bigint, name string,  size  double)  partitioned by (nation  string)  row  format  delimited fields  terminated  by  '\t'  location  '\beauty' ;

hive>load  data  local  inpath  '/home/b.c'  into  table  beauties  partition(nation='China') ;

hive>alter  table  beauties  add  partition  (nation='Japan') ;

hive>select  *  from  beauties ;

hive>select  *  from  beauties  where  nation='China' ;           //查找某一分区的数据内容；

10、多表关联：

hive>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 ;

11、存储过程没有返回值，函数有返回值

12、在linux环境下一次访问hive：

[hh@master ~]$ hive  -e  "selcte  *  from  mytable   limit  3" ;

13、[hh@master ~]$ hive -f 1.hql

14、打印表的字段信息：

hive>describe  yourtable ;

15、创建数据库：

hive>create  database  financials ;

hive>create  database  if  not  exists  financials ;

16、过滤数据库：

hive>show  databases  like  " f . * " ;

17、添加描述信息：

hive> create database test092302 with dbproperties ('creator'='Mark', 'date'='2015-09-23');

hive> describe database extended test092302;

18、删除数据库：

hive> drop database if exists human_resources;   或者

hive> drop database human_resources;

19、删除存在表的数据库：

hive> drop database test0923 cascade;      //在后面加上cascade关键字

20、创建数据库时添加描述信息：

hive> create database test092302 comment 'Holds all test tables';     //使用comment，创建表时也可以用

21、去重查询：group   by的使用

hive>select  *  from  mytable  group  by  uid ;

22、独立UID总数：

hive>select  count(distinct(uid)) from  mytable ; (高效) 或者   hive>select  count(*) from(select  *  from mytable  group  by  uid)  a ;

23、查询频度排名(频度最高的前50)：

hive> select keyword,count(*) as cnt from sogou_1w group by keyword order by cnt desc limit 50;

24、将查询的结果放入另一个表中：

hive> create table uid_cnt (uid string, cnt int) row format delimited fields terminated by '\t';       //先创建临时表 uid_cnt

hive> insert overwrite table sogou.uid_cnt select uid,count(*) from sogou_1w group by uid;     //再将查询的数据结果放入临时表中

25 修改列名：

hive> alter table test

> column ·stuname·  name string;“ · ”右上角的~键

describe test;

26 增加列：

hive> alter table test add columns(

> height int);

hive>describe test;

27替换列：

hive> alter table test replace columns(

> id int,

> name string,

> age int);

28 为表添加属性：

hive> alter table test set tblproperties (

> 'note'='hello welcome');

show create table test;

========================================

29 创建带有分区的内部表：

hive> create table testpar(

> id int,

> name string,age int) PARTITIONED BY (day string)

> ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'

> location '/testpar';

30 为带有分区的内部表加载数据：

hive> load data local inpath '/home/test' into table testpar

> partition (day='0925');

31 添加防止删除的保护：

hive> alter table testpar

> partition (day='0925') enable no_drop;

32 测试：删除分区

hive> alter table testpar drop if exists partition (day='0925');

33 删除添加的"删除"保护：

hive> alter table testpar

> partition (day='0925') disable no_drop;

34 添加防止查询的保护：

hive> alter table testpar

> partition (day='0925') enable offline;

35 删除防止查询的保护:

hive> alter table testpar

> partition (day='0925') disable offline;

select * from testpar;

================================================

36 按条件向分区表插入数据

hive>from test_1  ts