1.数据库基本操作
指令 | 含义 |
---|---|
show databases | 显示数据库 |
desc database [extended] 数据库名 | 显示数据库【详细】信息 |
alter database 数据库名 set dbproperties() | 修改数据库的描述信息 |
drop database 数据库名 | 删除空数据库 |
drop database 数据库名 casdece | 强制删除非空数据库 |
2.表基本操作
指令 | 含义 |
---|---|
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name [(col_name data_type [COMMENT col_comment], …)] [ROW FORMAT row_format] | 建表语法 |
load data [local] inpath ‘路径’ into table 表名 | 向外部表中导入数据 |
desc formatted 表名 | 查看表格式化数据 |
alter table 表名 set tblproperties(‘EXTERNAL’=‘TRUE’) | 内部表转化为外部表 |
alter table 表名 set tblproperties(‘EXTERNAL’=‘FALSE’) | 外部表转化为内部表 |
load data [local] inpath ‘路径’ into table 表名 partition(分区信息) | 加载数据到分区表 |
select * from 表名 where 分区信息 | 单分区查询 |
alter table dept_partition add partition(month=‘201706’) | 创建单个分区 |
alter table dept_partition add partition(month=‘201705’) partition(month=‘201704’) | 创建多个分区 |
hive (default)> alter table dept_partition drop partition (month=‘201704’) | 删除单个分区 |
hive (default)> alter table dept_partition drop partition (month=‘201705’), partition (month=‘201706’) | 删除多个分区 |
show partitions dept_partition | 查看分区表有多少分区 |
msck repair table 表名 | 修复表 |
ALTER TABLE 旧表名 RENAME TO 新表名 | 重命名表 |
hive (default)> alter table dept_partition add columns(deptdesc string) | 添加列 |
hive (default)> alter table dept_partition change column deptdesc desc int | 更新列 |
hive (default)> alter table dept_partition replace columns(deptno string, dname string, loc string) | 替换列 |
drop table 表名 | 删除表 |
insert into table student partition(month=‘201709’) values(1,‘wangwu’) | 插入数据 |
create table if not exists student3 as select id, name from student | 根据查询结果创建表 |
insert overwrite local directory ‘/opt/module/datas/export/student’ select * from student | 将查询的结果导出到本地 |
truncate table 表名 | 清除表中数据(只能删除管理表,不能删除外部表中数据) |
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 | 分桶表属性设置 |
3.查询语句
常用函数
函数 | 含义 |
---|---|
count | 总个数 |
max | 最大值 |
min | 最小值 |
sum | 值求和 |
avg | 平均值 |
比较运算符
操作符 | 支持的数据类型 | 描述 |
---|---|---|
A=B | 基本数据类型 | 如果A等于B则返回TRUE,反之返回FALSE |
A<=>B | 基本数据类型 | 如果A和B都为NULL,则返回TRUE,其他的和等号(=)操作符的结果一致,如果任一为NULL则结果为NULL |
A<>B, A!=B | 基本数据类型 | A或者B为NULL则返回NULL;如果A不等于B,则返回TRUE,反之返回FALSE |
A<B | 基本数据类型 | A或者B为NULL,则返回NULL;如果A小于B,则返回TRUE,反之返回FALSE |
A<=B | 基本数据类型 | A或者B为NULL,则返回NULL;如果A小于等于B,则返回TRUE,反之返回FALSE |
A>B | 基本数据类型 | A或者B为NULL,则返回NULL;如果A大于B,则返回TRUE,反之返回FALSE |
A>=B | 基本数据类型 | A或者B为NULL,则返回NULL;如果A大于等于B,则返回TRUE,反之返回FALSE |
A [NOT] BETWEEN B AND C | 基本数据类型 | 如果A,B或者C任一为NULL,则结果为NULL。如果A的值大于等于B而且小于或等于C,则结果为TRUE,反之为FALSE。如果使用NOT关键字则可达到相反的效果。 |
A IS NULL | 所有数据类型 | 如果A等于NULL,则返回TRUE,反之返回FALSE |
A IS NOT NULL | 所有数据类型 | 如果A不等于NULL,则返回TRUE,反之返回FALSE |
IN(数值1, 数值2) | 所有数据类型 | 使用 IN运算显示列表中的值 |
A [NOT] LIKE B | STRING 类型 | B是一个SQL下的简单正则表达式,如果A与其匹配的话,则返回TRUE;反之返回FALSE。B的表达式说明如下:‘x%’表示A必须以字母‘x’开头,‘%x’表示A必须以字母’x’结尾,而‘%x%’表示A包含有字母’x’,可以位于开头,结尾或者字符串中间。如果使用NOT关键字则可达到相反的效果。 |
A RLIKE B, A REGEXP B | STRING 类型 | B是一个正则表达式,如果A与其匹配,则返回TRUE;反之返回FALSE。匹配使用的是JDK中的正则表达式接口实现的,因为正则也依据其中的规则。例如,正则表达式必须和整个字符串A相匹配,而不是只需与其字符串匹配。 |
常用命令
命令 | 含义 |
---|---|
select * from emp limit 5 | 返回前5行 |
select * from emp where sal >1000 | 过滤不满足条件的行 |
elect * from emp where sal LIKE ‘2%’ | 查找以2开头薪水的员工信息(% 代表零个或多个字符(任意个字符)。_ 代表一个字符) |
group by | 按照一个或者多个列队结果进行分组 |
having | 与where类似,但where针对表中的列发挥作用,查询数据;having针对查询结果中的列发挥作用,筛选数据;且having只用于group by分组统计语句 |
join(select e.empno, e.ename, d.deptno, d.dname from emp e join dept d on e.deptno = d.deptno) | 连接,只支持等值连接 |
select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno | 内连接 |
left[right/full] join | 左【右/满】外连接 |
select * from stu_buck tablesample(bucket x out of y on id) | 分桶抽样查询(y必须是table总bucket数的倍数或者因子,x表示从哪个bucket开始抽取,x的值必须小于等于y的值) |
4.排序
命令 | 含义 |
---|---|
order by(select * from emp order by sal) | 全局排序(一个reducer ASC(ascend): 升序(默认)DESC(descend): 降序 |
set mapreduce.job.reduces=3; | 设置reducer个数,默认为-1 |
sort by(select * from emp sort by deptno desc) | 每个Reducer内部进行排序 |
distribute by(select * from emp distribute by deptno sort by empno desc) | 分区排序 |
cluser by | 当distribute by和sorts by字段相同时,可以使用cluster by方式(只支持升序) |
5.其他常用函数
(1)空字段赋值
NVL:给值为NULL的数据赋值,它的格式是NVL( string1, replace_with)。它的功能是如果string1为NULL,则NVL函数返回replace_with的值,否则返回string1的值,如果两个参数都为NULL ,则返回NULL。
如果员工的comm为NULL,则用-1代替
hive (default)> select nvl(comm,-1) from emp;
(2) CASE WHEN
- 数据准备
name dept_id sex
悟空 A 男
大海 A 男
宋宋 B 男
凤姐 A 女
婷姐 B 女
婷婷 B 女
2.需求
求出不同部门男女各多少人。结果如下:
A 2 1
B 1 2
3.创建本地emp_sex.txt,导入数据
[atguigu@hadoop102 datas]$ vi emp_sex.txt
悟空 A 男
大海 A 男
宋宋 B 男
凤姐 A 女
婷姐 B 女
婷婷 B 女
4.创建hive表并导入数据
create table emp_sex(
name string,
dept_id string,
sex string)
row format delimited fields terminated by "\t";
load data local inpath '/opt/module/datas/emp_sex.txt' into table emp_sex;
5.按需求查询数据
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;
(3)行转列
1.相关函数说明
CONCAT(string A/col, string B/col…):返回输入字符串连接后的结果,支持任意个输入字符串;
CONCAT_WS(separator, str1, str2,…):它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL,返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;
COLLECT_SET(col):函数只接受基本数据类型,它的主要作用是将某字段的值进行去重汇总,产生array类型字段。
2.数据准
name constellation blood_type
孙悟空 白羊座 A
大海 射手座 A
宋宋 白羊座 B
猪八戒 白羊座 A
凤姐 射手座 A
3.需求
把星座和血型一样的人归类到一起。结果如下:
射手座,A 大海|凤姐
白羊座,A 孙悟空|猪八戒
白羊座,B 宋宋
4.创建本地constellation.txt,导入数据
[atguigu@hadoop102 datas]$ vi constellation.txt
孙悟空 白羊座 A
大海 射手座 A
宋宋 白羊座 B
猪八戒 白羊座 A
凤姐 射手座 A
5.创建hive表并导入数据
create table person_info(
name string,
constellation string,
blood_type string)
row format delimited fields terminated by "\t";
load data local inpath “/opt/module/datas/person_info.txt” into table person_info;
6.按需求查询数据
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;
(4)列转行
1.函数说明
EXPLODE(col):将hive一列中复杂的array或者map结构拆分成多行。
LATERAL VIEW
用法:LATERAL VIEW udtf(expression) tableAlias AS columnAlias
解释:用于和split, explode等UDTF一起使用,它能够将一列数据拆成多行数据,在此基础上可以对拆分后的数据进行聚合。
2.数据准备
movie category
《疑犯追踪》 悬疑,动作,科幻,剧情
《Lie to me》 悬疑,警匪,动作,心理,剧情
《战狼2》 战争,动作,灾难
3.需求
将电影分类中的数组数据展开。结果如下:
《疑犯追踪》 悬疑
《疑犯追踪》 动作
《疑犯追踪》 科幻
《疑犯追踪》 剧情
《Lie to me》 悬疑
《Lie to me》 警匪
《Lie to me》 动作
《Lie to me》 心理
《Lie to me》 剧情
《战狼2》 战争
《战狼2》 动作
《战狼2》 灾难
4.创建本地movie.txt,导入数据
[atguigu@hadoop102 datas]$ vi movie.txt
《疑犯追踪》 悬疑,动作,科幻,剧情
《Lie to me》 悬疑,警匪,动作,心理,剧情
《战狼2》 战争,动作,灾难
5.创建hive表并导入数据
create table movie_info(
movie string,
category array<string>)
row format delimited fields terminated by "\t"
collection items terminated by ",";
load data local inpath "/opt/module/datas/movie.txt" into table movie_info;
6.按需求查询数据
select
movie,
category_name
from
movie_info lateral view explode(category) table_tmp as category_name;
(5)窗口函数
1.相关函数说明
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类型。
2.数据准备:
name,orderdate,cost
jack,2017-01-01,10
tony,2017-01-02,15
jack,2017-02-03,23
tony,2017-01-04,29
jack,2017-01-05,46
jack,2017-04-06,42
tony,2017-01-07,50
jack,2017-01-08,55
mart,2017-04-08,62
mart,2017-04-09,68
neil,2017-05-10,12
mart,2017-04-11,75
neil,2017-06-12,80
mart,2017-04-13,94
3.需求
(1)查询在2017年4月份购买过的顾客及总人数
(2)查询顾客的购买明细及月购买总额
(3)上述的场景,要将cost按照日期进行累加
(4)查询顾客上次的购买时间
(5)查询前20%时间的订单信息
4.创建本地business.txt,导入数据
[atguigu@hadoop102 datas]$ vi business.txt
5.创建hive表并导入数据
create table business(
name string,
orderdate string,
cost int
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
load data local inpath "/opt/module/datas/business.txt" into table business;
6.按需求查询数据
(1)查询在2017年4月份购买过的顾客及总人数
select name,count(*) over ()
from business
where substring(orderdate,1,7) = '2017-04'
group by name;
(2)查询顾客的购买明细及月购买总额
select name,orderdate,cost,sum(cost) over(partition by month(orderdate)) from
business;
(3)上述的场景,要将cost按照日期进行累加
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;
(4)查看顾客上次的购买时间
select name,orderdate,cost,
lag(orderdate,1,'1900-01-01') over(partition by name order by orderdate ) as time1, lag(orderdate,2) over (partition by name order by orderdate) as time2
from business;
(5)查询前20%时间的订单信息
select * from (
select name,orderdate,cost, ntile(5) over(order by orderdate) sorted
from business
) t
where sorted = 1;
(6)Rank
RANK() 排序相同时会重复,总数不会变
DENSE_RANK() 排序相同时会重复,总数会减少
ROW_NUMBER() 会根据顺序计算