※直接上干货
刚开始学数仓时候都被一直灌输HIVE数仓不是数据库而是数据仓库,既不产生数据也不存储数据,只分析数据,起初不是很理解,直到自己长久的使用了之后才慢慢的理解,HIVE基于Hadoop,这就给HIVE提供了可靠的数据来源,而分析的话,很早之前是基于MR的,但是现在MR几乎已经被淘汰了,改为了基于Spark来计算分析了。
基于数仓说直白点就是写各种hive SQL,SQL语言之间都大同小异,在经过了长久的SQL学习使用之后,对于hive SQL也就很得心应手了,以下是我认为的数仓所特有的一些知识点:
1、SQL中各字段的执行顺序:
from-where-group by-having -select-order by -limit
(7) SELECT
(8) DISTINCT <select_list>
(1) FROM <left_table>
(3) <join_type> JOIN <right_table>
(2) ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(6) HAVING <having_condition>
(9) ORDER BY <order_by_condition>
(10) LIMIT <limit_number>
2、创建内部表的两种方式:
-- 1、as
create table copy_student1 as select * from student;
select * from copy_student1;
desc copy_student1;
-- 2、like
create table copy_student2 like student;
select * from copy_student2;
desc copy_student2;
对于as来说,创建的内部表是原表的全部,对于like来说,创建的内部表只是原表的元数据
3、增加一个字段
alter table test_db.person
add columns (gender string, height double);
4、修改字段
-- hive sql中没有modify操作,只有change
alter table test_db.person
change gender sex string;
5、修改表名
-- 自定义位置的表名不会修改
alter table test_db.person
rename to test_db.person_tb;
6、hive中表的复制
如果是将数据从文件上传到对应的数据表下,则只是简单的数据的上传操作。而在hive中对表的复制,操作完之后,原位置不再有数据,即数据都迁移到了复制的位置,此时为表的mv操作。
7、内部表与外部表
-- 进行删除操作时候,如果表为内部表,则会将元数据以及表内的
-- 数据都给删除,如果表为外部表,则只会删除元数据信息,
-- 根本上来说数据是由HDFS存储管理的
8、分区与分桶
分区就类似于按照层级将数据分开存储,一部分存储一块数据,分开存储。
分桶就类似于将数据分成若干块,采用哈希排序的方法进行分桶
分区:
在建表时候添加一个字段: partitioned by (month int) #指定分区
分区表的数据加载方式: partition (year='2021', month='12',day='31') 在原来的加载方式上添加一个分区字段
※多级分区表必须多个层级间有从属关系,例如年月日, 省市区
※多级分区表的层级一般不超过三级,因为数据层级过多,就会产生大量的小文件.
-- 增加分区
load data local inpath '/root/hive_data/score.txt' into table score_parts partition (year='2021', month='12',day='30');
-- 删除分区
alter table score_parts drop partition (year='2021', month='12',day='30');
-- 修改分区名称
alter table score_parts partition (year='2021', month='12',day='30') rename to partition (year='2021', month='12',day='3');
分桶:
桶内排序就是将一个桶内的数据按照一定规则排序,所以操作顺序也是先分桶再排序
-- 当我们按照同一个字段分桶且排序,并且进行升序排列时
#不能指定分桶,只能指定MR的任务数来达到分桶的效果
set mapreduce.job.reduces = 3;
-- cluster by = distribute by + sort by
distribute by:仅分桶
sort by:仅排序
distribute by (仅分桶) + sort by (仅排序)
----- cluster by 与 distribute by + sort by比较:
-- cluster by 具有局限性, 分桶和排序字段必须相同,且只能使用升序排列
-- 所以 distribute by + sort by 更加灵活
分区表与分桶表的异同:
异:1、分区表使用的是元数据信息,分桶使用的是实体字段
2、分区表将数据拆分成不同的目录存放,分桶是将数据分组存放
3、分区表主要是提高了查询效率,分桶主要是提高了连接效率
同:1、分区表、分桶表都是对表的优化手段
2、都提高了查询效率
9、concat_ws
-- concat_ws 按照指定分隔符进行字符串连接
-- 格式: concat_ws(分隔符, 字符串1, 字符串2.....)
select concat_ws('❤', '中国');
10、trim
--trim 裁剪字符串左右两侧的空格,不清除字符串中间的空白
select trim(' hello world! ');
11、split
-- split 将字符串拆分[拆分之后,字符串将变为一个数组]
-- 格式: split(被拆分的字符串, 拆分使用的分隔符)
12、get_json_object
-- json数据我们可以先理解为 map类型和array类型的互相嵌套
-- 格式: get_json_object(json数据, 数据提取规则)
-- $ 代表整条json数据
-- $.name 代表当前json数据中 key为name的键值对所对应的值
select get_json_object('{"name":"杨过", "age":"18"}', '$.name'); -- 杨过, $表示json对象
-- $.[1].name 代表json数据中下标为1的元素中键name所对应的值
select get_json_object('[{"name":"杨过", "age":"18"}, {"name":"小龙女", "age":"26"}]', '$.[1].name');
select get_json_object('[{"name":"杨过", "age":"18"}, {"name":"小龙女", "age":"26"}]', '$.[*].name');
13、nvl
-- nvl 函数,获取该数据,如果该数据为空则获取默认值
-- 格式 : nvl(目标字段, 默认字段);
select nvl(12, 0);
select nvl(null, 0);
14、coalesce
-- 获取数据集中第一个不为null的数据
select coalesce(1, 2, 3, null);
-- 我们可以判断array类型数据,但是其会作为一个数据看待
select coalesce(`array`(null, 'chuanzhi', 'boke'));
15、array_contains
-- 判断元素是否在数组中
select array_contains(`array`(1, 4, 7, 12), 13);
-- 判断数据元素是否在map的值内部
select array_contains(map_values(`map`('name', '小明', 'age', 18, 'gender', '男')),'男')