前言
本文主要讲的是一些Hive SQL的基础知识，希望能帮助到想要接触大数据分析和刚接触大数据分析的同学。实际永远比所见的复杂，但万丈高楼平地起，一切来源于基础。
关键字：初步了解sql每个关键字的功能
执行顺序/执行计划：看懂执行步骤，有助优化sql和排查sql执行遇到的问题
SQL优化/特殊运算/特殊函数：提高SQL的执行效率
补充知识：表结构处理语句/SQL书写规范/数据仓库基础知识
关键字
-
select a.col1,
-
count(distinct b.col2) as col2 --count:聚合函数，依据gourp条件汇总计算
-
from (
-
select * --select:选取数据列
-
from table1 --from:获取源数据
-
where col1 > 5 --where:过滤源数据
-
and (col2 < 5
-
or col3 = 5)
-
) as a --as:赋予表别名
-
left join table2 b --left/right/inner/full join:将两份源数据进行合并
-
on a.col1 = b.col1 --on:关联条件
-
inner join table3 c
-
on a.col1 = c.col1
-
full join table4 d
-
on a.col1 = d.col1
-
group by a.col1 --group by:分组,聚合运算的汇总依据【类比excel上的透视表】
-
having count(distinct b.col2) > 0 --having:对聚合后的数据结果进行过滤
-
order by col2 --order by:对col2排序
join的执行逻辑
- 左右表形成笛卡尔积的数据集【a*b条数】
- 根据关联条件过滤数据集
- 添加外部行，补全主表【即left join 添加所有右表为null的左表；full join 添加所有右表为null的左表和所有左表为null的右表】
关键字执行顺序
-
select a.user_id,
-
count(distinct ) as cnt
-
from table1 a
-
left join table1 b
-
on a.user_id = b.user_id
-
where b.last_login_date >= '2020-01-01'
-
group by a.user_id
-
having cnt > 1
-
order by cnt desc
- from:获取源数据
- join:将两份源数据进行合并
- where:过滤源数据【注：左表的where条件会优先于join执行】
- select:选取数据列
- group by:分组 聚合函数
- having:对聚合后数据集进行过滤
- distinct:去重
- order by:对col2排序
- select:选取数据列
执行计划
SQL优化
join的优化
- 关联表要保证结果集的最小化
- 尽量将小数据集的表作为关联主表
count(distinct **) 与 count(**) from (select ** from group by **) a
两者的差别在于：
- group by 会让数据先进行去重处理，避免了数据倾斜的问题
- group by 会将数据分配到多个reduce上计算，节省了reduce计算的时间
数据倾斜导致的查询缓慢
原因：mapreduce时 因数据在节点上分布不均匀，导致单节点承载数据过大，节点执行效率低，拉低整体的执行效率
- 数据本身分布不均（例如当数据属于极小方差的正态分布，数据大量堆积在均值上。原因： a.表的设计存在问题，即本身粒度存在问题 b.业务数据本身客观存在导致）
- sql中小大表join时，主表的key过于集中或空值过多（空值由一个reduce进行处理）
解决方法：
- 将主表中过于集中的值单独提取出来计算
- 重构主表，加入随机字段和主键合并
特殊运算
位运算：（二进制计算）
& 按位与：5 & 3 = 1【101 & 11 = 1】
| 按位或：5 | 3 = 7 【101 | 11 = 111】
^异或：5 ^ 3 = 6 【101 ^ 011 = 110】
位运算在留存计算中的运用：【适用于要同时计算多个留存率的需求】
-
select ,
-
count(1) as login_user_cnt,
-
sum(a1.is_keep_1d) as keep_user_cnt_1d,
-
sum(a1.is_keep_2d) as keep_user_cnt_2d,
-
sum(a1.is_keep_3d) as keep_user_cnt_3d,
-
sum(a1.is_keep_in_3d) as keep_user_cnt_in_3d
-
from (
-
select ,
-
a.user_id,
-
max(case when a.keep_bit_num & power(2, a.currnt_num + 1) > 0 then 1 else 0 end) as is_keep_1d,
-
max(case when a.keep_bit_num & power(2, a.currnt_num + 2) > 0 then 1 else 0 end) as is_keep_2d,
-
max(case when a.keep_bit_num & power(2, a.currnt_num + 3) > 0 then 1 else 0 end) as is_keep_3d,
-
max(case when a.keep_bit_num & (power(2, a.currnt_num + 3 + 1) - 1) >= power(2, a.currnt_num + 1) then 1 else 0 end) as is_keep_in_3d
-
from (
-
select pt,
-
user_id,
-
datediff(pt, '2020-01-01') as currnt_num,
-
sum(power(2, datediff(pt, '2020-01-01'))) over (partition by user_id order by pt desc) as keep_bit_num
-
from
-
where pt >= '2020-01-01'
-
and pt <= '2020-01-31'
-
) a
-
group by ,
-
a.user_id
-
) a1
-
group by
取余：%
取整：ceil向上取整 floor向下取整
特殊函数
窗口函数:在不改变数据集条数的情况下，按照parttition by 进行汇总统计
func(*) over (partition by * order by * )
常用窗口函数：
- lead 分组领先 / lag 分组滞后
- row_number() 同值异序连续 123 / rank() 同值同序不连续 113 / dense_rank() 同值同序连续 112
特殊用法：
有无order by的区别：
sum(1) over (partition by pb order by num) 按pb分组按num顺序逐步累计求值
sum(1) over (partition by pb) 按pb分组累计求值
grouping sets:按照group by条件，根据不同grouping sets条件进行多次聚合运算
区别union+group by：使用union操作会增加IO开销,会减少cpu和内存的开销,使用grouping sets会减少IO开销,会增加cpu和内存的消耗
lateral view:行转列，将一行array拆分成多行，其他字段重复【不能与where共用】
-
select ,
-
a.array_col,
-
tmp.tmp_col,
-
a.array_col2,
-
tmp.tmp_col2
-
from a
-
lateral view explode(split(a.array_col, '、')) tmp as tmp_col
-
lateral view explode(split(a.array_col2, '、')) tmp as tmp_col2
表结构处理语句
create
-
create [external] table 表名 --external外部表
-
(
-
非分区字段名 字段类型 comment'注释' -- 字段类型：string int bigint double array<string>
-
[,非分区字段名 字段类型 comment'注释']
-
)
-
comment '表注释'
-
partitioned by (分区字段名 字段类型 comment'分区字段')
-
row format delimited fields terminated by '\t' --列分割依据
-
stored as textfile --存储的数据格式
-
[location 'hdfs地址'] ;
内外部表的区别：
- hdfs文件的区别：外部表是作为hdfs文件的一种映射，内部表是与hdfs文件一一对应
- 删除操作的区别：外部表只是删除表的映射关系（即元数据），数据源不做删除动作；内部表是删除hdfs文件和删除表
- 加载数据的区别：load data 只做映射不迁移数据，内部表会迁移数据
insert
-
-- 非分区表
-
insert overwrite/into table 表名
-
select ...
-
-
-- 分区表
-
insert overwrite/into table 表名 partition (分区字段=分区值)
-
select ...
-
-
-- 分区表-动态分区插入(ps:动态分区插入存在上限)
-
insert overwrite/into table 表名 partition (分区字段)
-
select ...,分区字段
overwrite的处理顺序：先对覆盖数据进行删除，再插入
alter
-
-- 新增字段
-
alter table 表名 add columns (字段名 字段类型 comment '注释' [,字段名 字段类型 comment '注释'])
-
-
-- 修改单列字段
-
alter table 表名 change column 修改字段 修改后字段 修改后字段类型 [修改后注释] [after 前一个字段名] CASCADE
-
-
-- 修改表名
-
alter table 旧表名 rename to 新表名
-
-
-- 按字段顺序修改字段
-
alter table 表名 replace columns (保留/修改后字段 保留/修改后类型 [保留/修改后注释]) CASCADE
-
-
-- 删除分区
-
alter table 表名 drop partition (需要删除的分区条件) 注：对应的hdfs文件也会被删除
分区表新增字段问题：分区表中新增字段后会出现 外部表序号和hdfs列序号无法对应 导致数据无法显示
解决方案：alter table 表名 add columns (字段名 字段类型 comment '注释' ) cascade
show & desc
-
-- 显示table_name的分区
-
show partitions table_name
-
-
-- 显示table_name的建表语句
-
show create table table_name
-
-
-- 显示table_name的字段明细
-
desc table_name
SQL书写规范
因人而异，但要一直保持一致的规范。【关键字和字段或表之间空两格，以便分清字段和表；逗号在后；运算符前后空一格；tab标识4个空格...】
以下是我根据自己的书写规范编的工具：sql_format(链接：百度网盘 请输入提取码 提取码：w412)
数据仓库基础知识
数仓的常见层级划分
ods层：原始数据层，存储从业务库同步过来的数据，不做任何处理，不对数据开发以外的人开放
dwd层：明细数据层，对ods层的数据进行解析清洗
dws层：轻度汇总层，对dwd层的数据对常见的指标进行轻度聚合，粒度仍是用户级别
st层：报表层，根据业务需求定制的报表，粒度以日期为主，例如天/月/年
建表规范命名
层级+业务id+表内容+更新方式【尽量英文，单词过长用缩写】
字段规范命名
原子指标+修饰词【修饰词：时间修饰词等其他】
与原定数仓命名规范保持一致【原定数仓命名规范高于自认为的命名规范】
埋点基础知识
埋点的意义
记录用户行为，由数据分析师处理分析，供于运营/产品人员使用
埋点的类型
前端代码埋点：由埋点人员设计及规范内容格式，埋点存在js【自主性高，代码耦合性高】
可视化埋点：埋点有控台配置【自主性中，代码耦合性低】
全埋点：开发规定内容，返回所有用户行为信息【自主性低，代码耦合性低，信息全】
埋点的内容
一条完整的埋点应包含 用户信息（设备信息），用户行为，行为对应的业务信息，行为的来源