大数据存储与访问Hive复习

目录

一、 数据库

1、 创建数据库db_hive

2、 显示数据库

3、 过滤显示查询的数据库，查询以db_hive开头的数据库

显示数据库详细信息

5、 切换当前数据库

6、 设置数据库键值对属性值

7、 删除空数据库

若数据库不为空则采用cascade命令强制删除数据库

二、 创建表

1、创建非分区表 最普通的

2、创建分桶表

3、创建分区表

4、创建外部表ext_t1（id int,name String）

三、 常用函数

1、 求总行数(count)

2、 求工资的最大值（max）

3、 求工资的最小值（min）

4、 求工资最大差值

5、 求工资的总和（sum）

6、 求工资的平均值（avg）

四、 比较运算符、逻辑运算符、算术运算符

（一）比较运算符

1、A =B 查询出薪水等于5000的所有员工编号，姓名，部门编号

2、A 查询工资和提成佣金comm之和小于5000的员工编号，姓名，工作和部门编号<>

3、BETWEEN…AND…查询工资在500到1000的员工信息

4、is null查询comm为空的所有员工信息

5、查询sal和comm都不为空的员工编号，姓名，工作，部门编号

6、in 查询工资是1500或5000的员工信息

7、 LIKE查找名字以 A开头 的员工信息

（二）逻辑运算符

1、 查询薪水大于1000且部门编号是30的员工信息

2、 查询工资大于3000，或者部门编号是30的员工信息

3、 查询除了20部门和30部门以外的员工信息

1、A 和B 相加

2、A 减去B

3、A 和B 相乘，A 除以B，A对B取余

4、A和B按位取与（A&B）——同为1则取1，同为0则取0，一个1一个0则取0

5、A和B按位取或（A|B）——二者中有一个1则取1

6、A和B按位取异或（A^B）——两者不同则取1，两者相同则取0

7、 A按位取反（~A）

五、 分组Group by

1、 将emp表按照部门分组（输出人员姓名，人员编号，工作）

2、求每个部门的平均工资

3、按照部门和入职时间进行分组

4、 按照部门分组并计算每组的人数

六、Having语句

1、求每个部门的平均工资

2、求每个部门的平均薪水大于2000的部门

七、 limit和offset

1、 返回emp表前5行

2、 返回emp表从第6行开始的10行内容

3、 输出emp表中跳过前4行之后的3行员工的员工编号，员工姓名，工作

八、排序函数

（一）全局排序（Order By）

1、查询员工信息按工资升序排列

2、查询员工信息按工资降序排列

3、按照员工薪水的2倍排序

4、按照部门和工资升序排序

（二）每个Reduce内部排序（Sort By）

1、设置reduce个数

2、查看设置reduce个数

3、根据部门编号降序查看员工信息

4、将查询结果导入到文件中（按照部门编号降序排序）

九、DATE_FORMAT函数 改变日期的格式

将日期 orderdate 由'2017/1/21转换为'2017-01'形式

十、拼接字符串函数

CONCAT

CONCAT_WS CONCAT_WS 的第一个参数是分隔符

---

一、 数据库

1、 创建数据库db_hive

Create database if not exists db_hive;

2、 显示数据库

Show databases;

3、 过滤显示查询的数据库，查询以db_hive开头的数据库

Show databases like ‘db_hive*’;

4、 显示数据库信息

Desc database db_hive；

5、显示数据库详细信息

Desc database extended db_hive；

6、 切换当前数据库

Use db_hive；

7、 设置数据库键值对属性值

Alter database db_hive set dbproperties(‘createtime’=‘20240419’)； Desc database extended db_hive;

8、 删除空数据库

Drop database db_hive_2;

9、若数据库不为空则采用cascade命令强制删除数据库

Drop database db_hive cascade；

二、 创建表

1、创建非分区表 最普通的

create table if not exists employee (eid int,name String,salary String,destination String)  
row format delimited fields terminated by '\t';

2、创建分桶表

create table if not exists stu_buck(id int, name string) 
clustered by(id) into 4 buckets
row format delimited fields terminated by '\t';

3、创建分区表

create table if not exists employee_partitioned (eid int, name String, salary String)  
partitioned by (destination String)  
row format delimited fields terminated by '\t';

4、创建外部表ext_t1（id int,name String）

create external table if not exists ext_t1(id int,name string)   
row format delimited fields terminated by '\t'    
location '存储路径';

三、 常用函数

1、 求总行数(count)

select count(*) from emp;

2、 求工资的最大值（max）

select max(sal) max_sal from emp;

3、 求工资的最小值（min）

select min(sal) min_sal from emp;

4、 求工资最大差值

select max(sal)- min(sal) from emp；

5、 求工资的总和（sum）

select sum(sal) sum_sal from emp;

6、 求工资的平均值（avg）

select // from emp;

四、 比较运算符、逻辑运算符、算术运算符

（一）比较运算符

1、A =B 查询出薪水等于5000的所有员工编号，姓名，部门编号

select empno,ename,deptno from emp where sal=5000;

2、A <B查询工资和提成佣金comm之和小于5000的员工编号，姓名，工作和部门编号

select empno,ename,job,deptno from emp where sal+comm<5000;

3、BETWEEN…AND…查询工资在500到1000的员工信息

select * from emp where sal between 500 and 1000;

4、is null查询comm为空的所有员工信息

select * from emp where comm is null;

5、查询sal和comm都不为空的员工编号，姓名，工作，部门编号

select empno,ename,job,deptno from emp where sal is not null and comm is not null;

6、in 查询工资是1500或5000的员工信息

select * from emp where sal IN (1500, 5000);

7、 LIKE查找名字以 A开头 的员工信息

select * from emp where ename LIKE 'A%';

查找名字中 第二个字母为A 的员工信息

select * from emp where ename LIKE '_A%';

查找名字中 带有A 的员工信息

select * from emp where ename RLIKE '[A]';

select * from emp where ename LIKE '%A%';

（二）逻辑运算符

1、 查询薪水大于1000且部门编号是30的员工信息

select * from emp where sal>1000 and deptno=30;

2、 查询工资大于3000，或者部门编号是30的员工信息

select * from emp where sal>3000 or deptno=30;

3、 查询除了20部门和30部门以外的员工信息

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

（三）算术运算符

1、A 和B 相加

将emp表中sal和comm两列数据相加并输出

select sal+comm as total_income from emp;

2、A 减去B

将emp表中sal数值减去50.55，并将其数据与empno、ename一起输出

select empno,ename,sal-50.55 as new_sal from emp;

3、A 和B 相乘，A 除以B，A对B取余

输出对emp表中sal的2倍，sal除以5，sal对3取余的计算结果

select sal*2,sal/5,sal%3 from emp;

创建demo表（id string），并向其中加载一个空格

4、A和B按位取与（A&B）——同为1则取1，同为0则取0，一个1一个0则取0

4&8

5、A和B按位取或（A|B）——二者中有一个1则取1

4|8

6、A和B按位取异或（A^B）——两者不同则取1，两者相同则取0

4^8

7、 A按位取反（~A）

~6

五、 分组Group by

1、 将emp表按照部门分组（输出人员姓名，人员编号，工作）

Select deptno from emp group by deptno;

2、求每个部门的平均工资

select deptno, avg(sal) from emp group by deptno;

3、按照部门和入职时间进行分组

Select deptno,hiredate from emp group by deptno,hiredate;

4、 按照部门分组并计算每组的人数

Select deptno,count(empno) from emp group by deptno;

六、Having语句

1、求每个部门的平均工资

hive (default)> select deptno, avg(sal) from emp group by deptno;

2、求每个部门的平均薪水大于2000的部门

hive (default)> select deptno, avg(sal) avg_sal from emp

group by deptno

having avg_sal > 2000;

七、 limit和offset

1、 返回emp表前5行

select * from emp limit 5;

2、 返回emp表从第6行开始的10行内容

select * from emp limit 5,10;

3、 输出emp表中跳过前4行之后的3行员工的员工编号，员工姓名，工作

select empno,ename,job from emp limit 3 offset 4;

八、排序函数

（一）全局排序（Order By）

ASC（ascend）: 升序（默认） DESC（descend）: 降序

1、查询员工信息按工资升序排列

select * from emp order by sal;

2、查询员工信息按工资降序排列

select * from emp order by sal desc;

3、按照员工薪水的2倍排序

select ename, sal*2 twosal from emp order by twosal;

4、按照部门和工资升序排序

select ename, deptno, sal from emp order by deptno, sal;

（二）每个Reduce内部排序（Sort By）

1、设置reduce个数

set mapreduce.job.reduces=3;

2、查看设置reduce个数

set mapreduce.job.reduces;

3、根据部门编号降序查看员工信息

select * from emp sort by deptno desc;

4、将查询结果导入到文件中（按照部门编号降序排序）

insert overwrite local directory '/export/module/data/sortby-result' select * from emp sort by deptno desc;

九、DATE_FORMAT函数 改变日期的格式

将日期 orderdate 由'2017/1/21转换为'2017-01'形式

Date format(regexp_replace(‘2017/1/21’,’/’,’-’),’yyyy-MM’);

十、拼接字符串函数

CONCAT

select concat(name, ' (', constellation, ', ', blood_type, ')') from people;

CONCAT_WS CONCAT_WS 的第一个参数是分隔符

select concat(' (', name, constellation, ',', blood_type, ')') from people;

行转列 列转行 CONCAT_WS、CONCAT、collect_set、explode()

日期的转换（substring、date_format、regexp_replace）

内部表外部表的转换操作

怎么建一个外部表

数据库的删除 怎么删除空的数据库 怎么删除非空的数据库

where条件查询

聚合函数 sum min

Group by 分组

排序 order by 降序desc

开窗函数 先分区在降序 是让用RANK()函数还是ROW_NUMBER()函数来排序

创建分区表 创建外部表

数组类型 如果那一列是包含数组的 该怎么去创建表

创建分区表的时候 在分区表里面添加两个分区 怎么添加 怎么删除

展示全部的分区 展示一个特定的分区 怎么用where语句

in and or

表的重命名

load data 路径要写好