hive之HQL常用命令

Hive之HQL常用命令

一、DDL数据定义

以下主要用于个人练习，所以方法的描述并不十分详细，适合有一定基础的人练习。

! ls;
-- !进入本地文件系统执行命令
dfs -ls /;
-- 执行hdfs命令，相当于hadoop fs或hdfs dfs。
desc function extended upper;
-- 详细显示系统自带的函数的用法

1、创建数据库：

create database if not exists db_hive location '/db_hive.db';

if not exists判断是否表存在；除了可以更建库结合还可以跟建表结合。

2、查询数据库：

2.1显示数据库：

show databases;
show database like 'db_hive*';

2.2查看数据库详情：

desc database db_hive;
desc database extended db_hive; --查看数据库扩展信息

2.3切换数据库：

use db_hive;

3、修改数据库：

-- 修改数据库的扩展信息，可以使用extended查看。
alter database db_hive set dbproperties('createtime'='20170830');

4、删除数据库：

4.1删除空数据库：

drop database if exists db_hive;

4.2数据库不为空：强制删除（谨慎使用）

drop database if exists db_hive cascade;

5、创建表：

5.1建表语法：

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name 
[(col_name data_type [COMMENT col_comment], ...)] 
[COMMENT table_comment] 
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] 
[CLUSTERED BY (col_name, col_name, ...) 
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS] 
[ROW FORMAT row_format] 
[STORED AS file_format] 
[LOCATION hdfs_path]
-- LIKE复制表结构但不复制数据

5.2管理表：又称为内部表，删除管理表也会删除表中数据，external外部表则不会。

create table if not exists student(
id int,
name string)
row format delimited
fields terminated by '\t'
stored as textfile
location '/user/hive/warehouse/student';

5.3外部表：删除外部表只会删除元数据而不会删除表存储的数据。

create extended table extended_db(id string);
-- 出了extended标识所创建的表是外部表外，其它和管理表一样。

5.4根据查询结果创建表:查询结果会添加到新表

create table if not exists student1 as select * from student;

5.5根据已经存在的表结构创建表

create table if not exists student2 like student;

5.6查询表的类型：

desc formatted student;

5.7查看表：

show tables;

5.8向表导入数据：

load data local inpath '/tmp/student.txt' into table default.student;
-- local表示从本地文件系统导入，默认从hdfs中导入。

5.9查询结果：

select id from student;

5.10管理表和外部表的互换:
修改内部表为外部表：

alter table student set tblproperties('EXTERNAL','TRUE');
-- 将EXTERNAL改为FALSE即为内部表，区分大小写。

6、分区表：

语法：

create table dept_partition(
deptno int, dname string, loc string
)
partitioned by (month string)
row format delimited 
fields terminated by '\t';

6.1加载数据到分区表：

load data local inpath '/tmp/dept.txt' into table default.dept_partition partition(month='201709');

6.2查询分区表中的数据：

select * from dept_partition where month='201709'
union
select * from dept_partition where month='201708'
union
select * from dept_partition where month='201707';

6.3增加分区：

alter table dept_partition add partition(month='201705') partition(month='201704');

6.4删除分区：

alter table dept_partition drop partition (month='201705'), partition (month='201706');

6.5查看分区表有多少分区表：

show partitions dept_partition;

6.6创建二级分区表：

create table dept_partition2(
deptno int, dname string, loc string
)
partitioned by (month string, day string)
row format delimited fields terminated by '\t';

6.7二级分区表的加载数据和查询：

-- 加载数据
load data local inpath '/tmp/dept.txt' into table
 default.dept_partition2 partition(month='201709', day='13');
-- 查询
select * from dept_partition2 where month='201709' and day='13';

7、修改表：

7.1重命名表：

alter table dept_partition2 rename to dept_partition3;

7.2添加列：

alter table dept_partition add columns(col_name string);

7.3更新列：

alter table dept_partition change column col_name desc int;

7.4替换列：

alter table dept_partition replace columns(deptno string, dname
 string, loc string);

8、删除表：

drop table dept_partition;

二、DML数据操作

1、向表中加载数据：

load data [local] inpath '/tmp/student.txt' [overwrite] into table student [partition (partcol1=val1,…)];
-- overwrite将原有数据覆盖

将hdfs中的数据加载到hive表中：

load data inpath '/student.txt' overwrite into table default.student;

2、插入数据：

-- 直接插入一个数据
insert into table  student partition(month='201709') values(1,'wangwu');
-- 根据多张表查询结果插入数据
from student
insert overwrite table student partition(month='201707')
select id, name where month='201707'
insert overwrite table student partition(month='201706')
select id, name where month='201706';

3、import的导入导出：

-- 先用export导出后，再将数据导入
-- hdfs导入到hive表
import table student2 partition(month='201709') from
 '/student';

4、数据导出

4.1insert导出：

-- 将查询结果格式化导出到本地
insert overwrite local directory '/tmp/student1'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
select * from student;
-- 没有local则导出到hdfs上。

4.2hadoop命令导出到本地：

dfs -get /user/hive/warehouse/student/month=201709/000000_0
/tmp/student3.txt;

4.3hive shell命令导出：

bin/hive -e 'select * from default.student;' >
 /tmp/student4.txt;
 -- > linux上面的重定向符。

4.4export导出到活动方式上：

export table default.student to
 '/user/hive/warehouse/export/student';

4.5sqoop导出：将mysql数据与hive互相导入导出。

5、清除表中数据：

truncate table student;
-- 只能删除管理表中的数据，不能删除外部表数据。

三、查询

1、查询：

 -- 全表查询
 select * from emp;
 -- 特定列查询(as 别名)
 select eid as id,ename as name from emp;

2、算术运算符：+、-、*、/、%、&、|、^、~

-- 所有员工薪水+1后显示
select sal +1 from emp;

3、常用函数：

select count(*),max(sal),min(sal),sum(sal),avg(sal) from emp;

4、limit语句：限制返回的函数，在sql语句最后一行；where语句：筛选条件；

select * from emp where sal>1000 limit 10;

5、比较运算符：between、in、is null、not is/in，=、!=、<、>、<=、between start and end、 like、 rlike

-- 工资是1500或5000的员工信息
select * from emp where sal IN (1500, 5000);
-- like是一个类sql的模糊匹配，rlike基于Java正则，提供更强大的功能。
select * from emp where sal LIKE '_2%';

6、逻辑运算符：and、or、not

select * from emp where deptno not IN(30, 20);

7、分组：

-- having针对group by分组后的每一组数据
select deptno, avg(sal) avg_sal from emp group by deptno having
 avg_sal > 2000;

8、join语句：只支持等值查询，不支持非等值连接

-- 内连接（默认）：只有两个表中都存在匹配的数据才会被保留下来
select e.empno, e.ename, d.deptno from emp e 
join dept d on e.deptno = d.deptno;
-- 左外连接：左表符合on条件的数据会被记录下来
select e.empno, e.ename, d.deptno from emp e 
left join dept d on e.deptno = d.deptno;
-- 满外连接：将会返回所有表中符合WHERE语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用NULL值替代。
select e.empno, e.ename, d.deptno from emp e 
full join dept d on e.deptno = d.deptno;
-- 多表连接查询：
SELECT e.ename, d.deptno, l. loc_name FROM emp e 
JOIN dept d ON d.deptno = e.deptno 
JOIN location l ON d.loc = l.loc;
-- 笛卡尔积：
select empno, dname from emp, dept;
-- 连接谓词on不支持or

9、排序

-- order by 全局排序，一个reducer，asc升序（默认），desc降序
select * from emp order by sal desc;
-- 多个列及按别名排序
select ename name, deptno, sal from emp order by name, sal;
-- Sort By：每个Reducer内部进行排序，对全局结果集来说不是排序。
insert overwrite local directory '/tmp/sortby-result'
select * from emp sort by deptno desc;
-- Distribute By：类似MR中partition，进行分区，结合sort by使用。
set mapreduce.job.reduces=3;
hive (default)> insert overwrite local directory '/tmp/distribute-result' select * from emp distribute by deptno sort by empno desc;
-- 当distribute by和sorts by字段相同时，可以使用cluster by方式。cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是升序排序，不能指定排序规则为ASC或者DESC。
select * from emp cluster by deptno;

10、分桶：分区针对的是数据的存储路径；分桶针对的是数据文件。

-- 创建分桶表4个
create table stu_buck(id int, name string)
clustered by(id) 
into 4 buckets
row format delimited fields terminated by '\t';
-- 分桶需要设置属性：
set hive.enforce.bucketing=true;
set mapreduce.job.reduces=-1;
insert into table stu_buck
select id, name from stu;
-- 分桶抽样查询，语法：TABLESAMPLE(BUCKET x OUT OF y)；x的值必须小于等于y的值
select * from stu_buck tablesample(bucket 1 out of 4 on id);

11、其它常用查询函数

-- NVL：给值为NULL的数据赋值,语法：NVL( string1, replace_with)
select nvl(comm,-1) from emp;
-- case when：case sex when '男' then 1 else 0 end
select 
  dept_id,
  sum(case sex when '男' then 1 else 0 end) male_count,
  sum(case sex when '女' then 1 else 0 end) female_count
from 
  emp_sex
group by
  dept_id;
-- 行转列：
-- CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串;
-- CONCAT_WS(separator, str1, str2,...)：它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;
-- COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段。
select t1.base, concat_ws('|', collect_set(t1.name)) name
from (select name, concat(constellation, ",", blood_type) base from person_info) t1
group by t1.base;
-- 列转行：
-- EXPLODE(col)：将hive一列中复杂的array或者map结构拆分成多行。
-- LATERAL VIEW 用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias 解释：用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。
select movie, category_name
from movie_info lateral view explode(category) table_tmp as category_name;
-- 窗口函数：
-- OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化
-- CURRENT ROW：当前行
-- n PRECEDING：往前n行数据
-- n FOLLOWING：往后n行数据
-- UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING表示到后面的终点
-- LAG(col,n)：往前第n行数据
-- LEAD(col,n)：往后第n行数据
-- NTILE(n)：把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。注意：n必须为int类型。
select name,orderdate,cost, 
sum(cost) over() as sample1,--所有行相加 
sum(cost) over(partition by name) as sample2,--按name分组，组内数据相加 
sum(cost) over(partition by name order by orderdate) as sample3,--按name分组，组内数据累加 
sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and current row ) as sample4 ,--和sample3一样,由起点到当前行的聚合 
sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING and current row) as sample5, --当前行和前面一行做聚合 
sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING AND 1 FOLLOWING ) as sample6,--当前行和前边一行及后面一行 
sum(cost) over(partition by name order by orderdate rows between current row and UNBOUNDED FOLLOWING ) as sample7 --当前行及后面所有行 
from business;
-- rank排序函数：
--RANK() 排序相同时会重复，总数不会变
--DENSE_RANK() 排序相同时会重复，总数会减少
--ROW_NUMBER() 会根据顺序计算
select name, subject, score,
rank() over(partition by subject order by score desc) rp,
dense_rank() over(partition by subject order by score desc) drp,
row_number() over(partition by subject order by score desc) rmp
from score;

四、总结

hive的hql提供了sql大多数的类似功能，并且允许编写自定义函数扩充hive的业务能力。因为hive使用mapreduce集群运行较慢，所以在学习阶段练习sql或许也还是一个不错的方法，当然hadoop底层也是很重要的。