基础语法

Create语句

完整的Hive的建表语句

分区, 分桶, 切割, 存储方式, 存储位置, 表属性

create [external] table 表名(
		字段名 字段类型  Comment'字段的描述信息',
  	字段名 字段类型  Comment'字段的描述信息',
    字段名 字段类型  Comment'字段的描述信息'  -- 最后一行没有逗号
)Comment '表的描述信息'
partitioned by 中不存在的字段 字段类型)  -- 分区
clustered by (表中已有字段) sorted by (表中已有字段 desc/asc) into 桶的个数 buckets  -- 分桶
row format delimited fields terminated by ','  -- 切割
stored as orc  -- (stored as TestFile  行存储方式)  orc是列存储方式
location 'HDFS目录'
tblproperties ('orc.compress'='snappy');  -- 为snappy压缩协议，还有zlib,Gzip,Bzip等协议

注意事务表的第三个成立条件(需要在最后的表属性后面指定, 事务操作是True)

1	TBLPROPERTIES ('orc.compress'='SNAPPY','transactional'='true')

Drop语句

Hive默认不支持Delete和Update操作；

事务表可以支持此操作：不推荐使用事务表，因为会产生大量的小文件。

事务表

1、ORCFile

2、表分桶(但不要sorted)

3、建表时指定：TBLPROPERTIES (‘orc.compress’=’SNAPPY’,’transactional’=’true’)

4、开启事务支持：
1
2
3
4
5
set hive.support.concurrency=true;
set hive.exec.dynamic.partition.mode=nonstrict;
-- org.apache.hadoop.hive.ql.lockmgr.DummyTxnManager
set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;
set hive.compactor.initiator.on=true;
5、使用事务表：
1
2
3
4
INSERT into sqooptohive.emp_trans
SELECT * from sqooptohive.emp_hive;
DELETE FROM sqooptohive.emp_hive WHERE id=1201;
DELETE FROM sqooptohive.emp_trans WHERE id=1201;

Insert into语句

insert into 其中table可以省略

insert overwrite 中的table不可以省略

向分区表中插入数据

静态分区:insert [into | overwrite] table 表名 partition(分区字段=值) select 列1, 列2... from 表名

动态分区(需要关闭严格模式-必须至少有一个静态分区)

insert [into | overwrite] table 表名 partition(分区字段) select 列1, 列2... from 表名

insert into sqooptohive.inner_table
values (1, '张三', 50000), (), ();

INSERT into sqooptohive.inner_table
select id, name, salary from sqooptohive.emp_hive;

INSERT overwrite table sqooptohive.inner_table
select id, name, salary from sqooptohive.emp_hive;

Select语句

查询姓名为空的数据
- 查询空值数据时，不能使用=null，而是 is null

1 2	select * from emp where name IS NULL; select * from emp where name IS not NULL;

不等值条件，把今天过滤掉，null值也被过滤掉了, 符号为<>

1	SELECT * FROM web_chat_ems WHERE begin_time <> '2022-07-15 10:10:10'

模糊查询

1	select * from emp where deg like '%read%';

当条件既包含and又包含or的时候，要按照逻辑将条件括起来

1	select * from emp where name='khalil' and (deg='php dev' or salary=30000);

备份和恢复、迁移

Hive表包含了两部分：1、表数据(HDFS)；2、元数据(mysql,可以指定)

备份

将数据从Hive中迁移到HDFS上

1	EXPORT TABLE sqooptohive.emp_hive TO '/test/export';

恢复

将HDFS的数据导入到Hive中

1 2	IMPORT EXTERNAL TABLE sqooptohive.emp FROM '/test/export'; SELECT * FROM emp;

迁移

需要把Hive中的数据迁移到MySQL中

使用Sqoop

需要将Hive中的数据迁移到Hive中

Export

基础函数

字符串相关函数

select substr('abs123',1,3);  -- 提取函数的子串，（字符串，起始位置，子串个数) 效果和substring
select split('12,34,45',',');  -- 将字符串以,进行切割
select concat('12','nihao');  -- 将字符串进行拼接（使用默认方式
select concat_ws('-','12','44');  -- 将字符串以自定义字符方式进行拼接(第一个参数为拼接符
select length('agfa');  -- 获取字符串的长度
select upper('aaaAA');  -- 将字符串转大写
select lower('HAGu');  -- 将字符串转小写
select trim('  hg  jhh   ');  -- 移除字符串的首尾空格（字符串中间的空格不用管
select regexp_replace('100-200','\\d+','你好');  -- 将字符串中的数字替换为指定字符
select parse_url('http://www.itcast.cn/path/p1.php?query=1','HOST');  -- 提取url的域名
select parse_url('http://www.itcast.cn/path/p1.php?query=1','QUERY');  -- 提取url的请求
select parse_url('http://www.itcast.cn/path/p1.php?query=1','PATH');  -- 提取url的路径
select parse_url('http://www.itcast.cn/path/p1.php?username=admin01&password=pwd111','QUERY','username')  --提取url中键username的值
select get_json_object('{"name":"小威","age":"18"}','$.name');  -- 获取json字符串中某个对象的值
select get_json_object('[{"name":"小威","age":"18"},{"name":"小李","age":"18"}]','$.[0].name');  -- 获取某个数组中字典某个键的值

数字相关函数

select rand();  -- 产生一个0.0-1.0之间的随机数，如果不指定种子seed则随机数一直会不确定
select round('4.1');  -- 取整函数，四舍五入（四舍五入的原理是什么？加0.5取地板数
select floor(4.3+0.5);  -- 取地板数
select ceil(3.4);  -- 取天花板函数
select abs(-23);  -- 取绝对值

日期相关函数

将日期格式化为指定的格式

1	date_format(日期, 'yyyy-MM-dd HH:mm:ss')

获取给定日期的某个时间段, 使用方式为year(时间) 比如year(‘2023-07-28’)得到2023

year() -- 获取年
quarter()  -- 获取季度
month()  -- 获取月
day()  -- 获取天
hour()  -- 获取小时

跟时间戳相关的函数unix_timestamp()、from_unixtime()比较重要

select current_date();  -- 获取当前的日期
select unix_timestamp();  -- 获取当前时间戳
select unix_timestamp('2023-01-23 18:09:23');  -- 获取给定日期的时间戳
select unix_timestamp('2022/01/12','YYYY/mm/dd');  -- 获取给定日期的时间戳,并且指定格式
select from_unixtime(1684500647);  -- 根据指定时间戳获取日期，注意是再加上8小时，因为是东八区时间
select date_add('2023-05-19',2);  -- 日期加2,实际上写-2就是日期减二，也就是说date_sub用不到了
select date_sub('2023-05-19',2);  -- 日期减掉2
select datediff('2023-05-19','2023-05-20');  -- 日期比较 前-后
-- 判断该年是否为闰年 （提示：如果该年是闰年的话，二月有29天，如果不是闰年2月有28天
-- 数学中判断该年是否为闰年的方式是 能被4整除但不能被100整除的年份是闰年，或者能被400整除也是闰年
select dayofmonth(date_add('2022-03-01',-1));  -- 2022年3月1日的前一天是28，所以2022年不是闰年

判断条件

-- if判断方式
if(判断条件, true返回值, false返回值)
-- case when判断方式
case 
	when 判断条件  then  返回值
  when 判断条件2 then  返回值2
  ......
  else   返回值n
end as 别名

文件格式和压缩算法

文件格式：

总结:
行存储方式
- 1、增删改更快；2、select *更快
- 数据存储密度低, 磁盘利用率低
列存储方式
- 存储密度高, 磁盘利用率高
- 1、查询分析更快；2、select 单个字段更快

1、OrcFile：快；按行分块、按列存储，具有行式存储和列式存储的优点；
行式存储：1、增删改更快；2、select *更快
列式存储：1、查询分析更快；2、select 单个字段更快
2、TextFile；

压缩格式：

1、Zlib；2、Snappy（快）

Hive内部表和外部表

内部表：删除表或库以后，数据也会被删掉；
		默认创建的就是内部表；
外部表：删库删表删分区，数据都还在；
		建表关键字： create external table；
		OverWrite 会覆盖掉原始的数据；
Location 可以修改表数据的HDFS存储路径。默认路径：/user/hive/warehouse/库名/表名

注意事项

外部表：删表、删库、删分区，都删不掉数据；
外部表的数据，只能通过HDFS删除，需要有HDFS权限，在HDFS中删除；
默认路径：/user/hive/warehouse/库名/表名
外部表，使用overwrite覆盖插入时，也会成功删除旧数据，
能不用overwrite，就不要用

共用表表达式

也就是传说中的CTE表达式 Common Table Expression

Hive、Presto、Oracle等都支持，但是Mysql不支持。

创建方式

with CTE表达式的别名 as (
    被CTE所存储的内容, 即: SQL查询语句
)
select ... from  cte表达式别名;

CTE风格

from风格

with t1 as (
    select * from stu
)
from t1 select name, age;

链式风格

with t1 as (select * from stu),
     t2 as (select * from t1 where id > 95010),
     t3 as (select id, name, gender,age from t2 where id > 95010)
select name, gender from t3;

CTE和union,视图结合及永久存储

-- 4. CTE表达式结合 union 使用.
with t1 as (
    select * from stu
)
select * from t1
union  all      -- 合并, 不去重.
select * from t1 limit 3;       -- 44条 => 3条

-- 5. 用表 把 CTE的结果 永久存储.
create table hg1 as
with t1 as (
    select * from stu
)
select id, name, age from t1;

select * from hg1;

-- 6. 用视图 把 CTE的结果 "永久"存储.
create view hg2 as
with t1 as (
    select * from stu
)
select id, name, age from t1;

select * from hg2;

分组

几个注意点

count(1)的效率最高, 其次是count(id), 最后是count(*)

count(id)，如果id为null，不会计算进去，等同于+0；有值等同于+1；
count(*)， 任何一列有值，都会算进去
count(0)，0不是null，等同于+1；
count(0)，count(1)，count("abc")，等同于+1
count(null)，等同于+0，返回0。
count(0)有多少行，就返回几；sum(0)的值，永远都是0；sql中涉及科学运算，要避免出现NULL值，整个结果可能返回NULL
sum、max、min、avg聚合函数都会跳过NULL值

报错Invalid use of group function

情况1: SELECT sum(count(1)) FROM order
- sum()聚合函数中又嵌套了count()聚合函数
情况2: where筛选表中已经存在的字段且后面不能跟聚合函数
- 要想使用 sum avg等集合函数需要使用 having, 或者group by后order by后面也能跟聚合函数

GroupBy后面字段的关系对分组的影响

父子关系，可以把父辈都加入到groupby，不影响分组结果，最终都是按照最小辈分分组的；
非父子关系，加入groupby以后，分组会产生变化，影响最终的结果。
group by中的顺序不影响结果，但是会影响性能，高基数维度(子辈)在前，低基数维度(父辈)在后，性能最高；

举个栗子

1
2

-- 按月统计时，可以加入年、季度，不影响统计结果；但是如果加入了天的分组，结果就变了，不再按月统计，而是按天统计了。
Group BY year_month, year, quarter, day;

空值问题

当第一个参数不为空时，返回自身，否则返回第二个参数
为第一个参数设置空值默认值
mysql中用ifnull()，hive中用nvl()，通用的为coalesce()
coalesce返回第一个不为null的参数，如果所有参数都为null，则返回null

select if(5>3,'郑州','信阳');  -- if条件判断（条件，如果满足，如果不满足
select isnull(null);  -- 非空校验
select isnull('nihao');  -- 判断不为空，结果为false
select isnotnull('nihao'); -- 跟isnull判断结果相反
select nvl(null,'nihao');  -- 判断第一个值是否为空，是空就用第二个，且只能传入2个参数
select coalesce(null,null,'nihao') ; -- coalesce 是合并的意思,从后到前找到第一个不为空的值
select
    case 3
        when 5 then '周五'
        when 3 then '周三'
    end as week;  -- case when 条件判断

排序操作

OrderBy

order by默认正序排序,必要时可以指定使用降序排序desc
order by性能优化：
- 高基数维度在前(比如年月日的日算是高基维)，低基数维度在后，注意会影响结果的顺序
- limit 能够大幅提升order by的性能

1	SELECT * from sqooptohive.emp ORDER BY dept asc, salary DESC;

几种排序的区别(sortBy, OrderBy, distributed By, ClusterBy)

OrderBy是对数据进行全局排序, 但是只有一个reducer会导致严重的数据倾斜问题, 计算速度相对较慢
distribute By是对数据进行分组, sortBy是对组内数据进行排序
ClusterBy对数据既分组又排序相当于distributeBy + sortBy

Demo

-- sort by
-- 模拟大数据量时，多个reduce的情况；
-- Sort By的结果会受到reduce数量的影响；
-- 而Order by不受reduce数量的影响，永远都只有一个reduce；
-- Hive使用时，此值是每次运行时根据数据量和SQL自动分配的，如果手动设置，记得每次运行前都设置一下，清空缓存。
set mapred.reduce.tasks=2;

SELECT * from sqooptohive.emp SORT BY dept, salary DESC;

-- sort by可以和distribute by配合使用
-- 意思是，先按照distribute by字段分组，然后再按照sort by字段排序
SELECT * from sqooptohive.emp 
    distribute by dept SORT BY dept, salary DESC;

SELECT * from sqooptohive.emp 
    distribute by salary SORT BY salary DESC;

-- cluster by salary 相当于distribute by salary + sort by salary
-- 前提是cluster by指定的列要和distribute by + sort by指定的列要一致。
-- cluster by只支持升序，不支持降序
-- cluster by性能比order by高
SELECT * from sqooptohive.emp 
    CLUSTER BY salary;

group by 和 distribute by都有分组功能，
group by会影响聚合函数结果；
distribute by会影响排序的结果；

groupby => 聚合函数(count/sum/min/max) => distributeby => sort by

建表时，可以指定插入的数据如何排序
create table name
......
sorted by id
......

表连接

内连接

inner join 其中inner可以省略

也可以使用,进行隐式内连接, where后面写条件

外连接

左外, 右外, 满外, 左半连接, 右半连接

大概是累了…

笛卡尔积

join后面不写条件

where后面不写条件

笛卡尔积一般不使用(两表数据相乘)

行拼接

Union

Union ：排序去重，按照所有字段去重，只要有一个字段值不一样，就不会去重
Union All：简单的拼接，速度性能较快，一般用这个。
前提：两边的字段数量要一致，字段的类型顺序也要一致。

举个栗子

1
2
3

select id, name from emp where id <= 1205
union all
select id, name from emp where id >= 1205;

Full Join和Union的区别

详细见https://weiswift.github.io/2023/06/02/2023.06.02/