create database mydb;
mydb是自定义数据库名称
create database if not exists mydb comment 'create my db named dbname'
with dbproperties('createtime'='2022-03-19')
if not exists避免要创建的数据库已经存在错误
comment是给数据库加注释
with dbproperties后面跟的键值，描述该数据库的属性信息，eg：作者是谁，创建日期是什么时候等；
create database mydb location '/db_hive.db'
创建数据库指定数据库在HDFS上存放地位置
查询数据库/表
查看建表语句
show create database mydb
显示数据库
show databases
过滤显示查询地数据库
show databses like 'db_hive*'
查看数据库详情
desc database db_hive
显示数据库详细信息
desc database extended db_hive
切换当前数据库
use mydb
查看数据库地数据表
show tables
show tables in mydb
查询表结构
desc formatted hive_table
删除数据库
删除空数据库
drop database db_hive
如果删除的数据库不存在，采用if exists判断
drop database if exists db_hive
如果数据库不为空，采用cascade，强制删除
drop databse db_hive cascade
修改数据库
alter database db_hive set dbproperties('createtime'='2022-03-20')
数据库表操作
建表语法
1、create table 创建一个指定名字的表
2、if not exists 忽略已存在表异常
3、external 关键字可以让用户创建一个外部表
4、comment 为表和列添加注释
5、partitioned by 创建分区表
6、clustered by 创建分桶表
7、sorted by 不常用
8、row format delimited 指定序列化的分隔符
9、stored as 指定存储文件类型
10、location 指定表在HDFS上的存储位置
11、like 允许用户复制现有的表结构，但是不复制数据
加载本地文件到Hive
load data local inpath 'xx/xx.txt' into table default.hive_table
加载HDFS文件搭配Hive中
load data inpath 'user/hive/xx.txt' into table default.hive_table
加载数据覆盖表中已有的数据
load data inpath 'user/hive/xx.txt' overwrite into table default.hive_table
通过查询语句向表中插入数据（Insert）
基本插入数据
insert into table hive_table partition(month='202203')
values(1,'zhangsan')
根据单表查询结果插入数据
insert overwrite table hive_table partition(month='202203')
select id,name from mytable where month='202202'
根据多张表查询结果
from hive_table
insert overwrite table hive_table partition(month='202202')
select id,name where month='202202'
insert overwrite table hive_table partition(month='202201')
select id,name where month='202202'
创建表并加载数据（As Select）
创建表并指定在HDFS上的位置
create table if not exists hive_table(id int,name string)
row format delimited fields terminated by '\t'
location '/user/hive/warehouse/hive_table'
上传数据到HDFS上
dfs -put /opt/modele/datas/test.txt
/user/hive/warehouse/hive_table
Import数据到指定Hive表中
import table hive_table partition(month='202203')
from '/user/hive/warehouse/hive_table'
Insert数据导出
将查询的结果导出到本地
insert overwrite local directory '/opt/module/datas/export/hive_local'
select * from hive_table
将查询的结果格式化导出到本地
insert overwrite local directory '/opt/module/datas/export/hive_local'
row format delimited fields terminated by '\t'
select * from hive_table
将查询到的结果导出到HDFS上（没有local）
insert overwrite directory '/opt/module/datas/export/hive_local'
row format delimited fields terminated by '\t'
select * from hive_table
全表查询
select * from hive_table
特定列查询
select id,name from hive_table
列别名
select id as sid,name nm from hive_table
算术运算符
运算符
描述
A+B
A和B 相加
A-B
A减去B
A*B
A和B 相乘
A/B
A除以B
A%B
A对B取余
A&B
A和B按位取与
A|B
A和B按位取或
A^B
A和B按位取异或
~A
A按位取反
常用函数
求总行数 count
select count(*) cnt from emp
求最大值 max
select max(sal) max_sal from emp
求最小值 min
select min(sal) min_sal from emp
求和 sum
select sum(sal) sum_sal from emp
求平均值 avg
select avg(sal) avg_sal from emp
Limit语句
Limit子句用于限制返回的行数
select * from emp limit 5;
Where语句
使用where子句，将不满足条件的行过滤掉
selsect * from emp where sal>1000
1、使用Like运算选择类似的值
select * from emp where sal LIKE '2%';
2、选择条件可以包含字符或数字 R表示正则匹配
select * from emp where sal RLIKE '[2]';
逻辑运算符
操作符
含义
AND
逻辑并
OR
逻辑或
NOT
逻辑否
select * from emp where sal>1000 and deptno=30;
select * from emp where sal>1000 or deptno=30;
select * from emp where deptno not IN(30, 20);
Group By
Group by 语句通常会和聚合函数一起，按照一个或者多个列队结果进行分组，
然后对每个组执行聚合操作
select t.deptno,avg(t.sal) avg_sal from emp t group by t.deptno
Having
having和where不同点
1、where针对表中的列发挥作用，查询数据；
having针对查询结果中的列发挥作用，筛选数据分组函数
2、where后面不能分组函数，而having后面可以使用
3、having只用于group by分组统计语句
select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal > 2000;
Join
Hive支持通常的SQL Join语句，但是只支持等值连接，不支持非等值连接
内连接 只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。
select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno
= d.deptno;
左外连接 JOIN操作符左边表中符合WHERE子句的所有记录将会被返回
select e.empno, e.ename, d.deptno from emp e left join dept d on e.deptno
= d.deptno;
右外连接 JOIN操作符右边表中符合WHERE子句的所有记录将会被返回
select e.empno, e.ename, d.deptno from emp e right 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;
排序
Order by 全局排序，一个Reduce
select * from emp order by sal
ASC 升序（默认）
select * from emp order by sal asc
DESC 降序
select * from emp order by sal desc
多列排序
select ename, deptno, sal from emp order by deptno, sal ;
每个MapReduce内部排序（Sort by）
select * from emp sort by empno desc;
分区排序 Distribute By
Distribute By 类似MR中partition，进行分区，结合sort by使用
注意：Hive要求Distribute by语句要写在Sort by语句之前
select * from emp distribute by deptno sort by empno desc
分桶排序Cluster By
当distribute by和sort by字段相同时，可以使用cluster by方式
cluster by 除了具有disteibute by的功能外还兼具sort by的功能。
但是排序只能升序排序，不能指定排序规则为ASC或者DESC
等价写法
select * from emp cluster by deptno
select * from emp disterbute by deptno sort by deptno
分桶抽样查询
TABLESAMPLE(BUCKET x OUT OF y) 。
y必须是table总bucket数的倍数或者因子
select * from stu_buck tablesample(bucket 1 out of 4 on id)
空字段赋值
NVL：给值为NULL的数据赋值，她的格式是NVL(string1,replace_with)
它的功能是如果string1为Null，则NVL函数返回replace_with的值，否则返回string1的值，
如果两个参数都为NULL，则返回NULL
select nvl(comm,-1) from emp
case when then else end
判断方法 ， 类似if else用法
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;
case sex when '男' then 1 else 0 end 判断条件，是男是则返回1 否则返回0