前言

本文主要讲的是一些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的执行逻辑

  1. 左右表形成笛卡尔积的数据集【a*b条数】
  2. 根据关联条件过滤数据集
  3. 添加外部行，补全主表【即left join 添加所有右表为null的左表；full join 添加所有右表为null的左表和所有左表为null的右表】

关键字执行顺序

select  a.user_id,
        count(distinct b.guid) 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
  1. from:获取源数据
  2. join:将两份源数据进行合并
  3. where:过滤源数据【注：左表的where条件会优先于join执行】
  4. select:选取数据列
  5. group by:分组 聚合函数
  6. having:对聚合后数据集进行过滤
  7. distinct:去重
  8. order by:对col2排序
  9. select:选取数据列

执行计划

SQL优化

join的优化

  1. 关联表要保证结果集的最小化
  2. 尽量将小数据集的表作为关联主表

count(distinct **) 与 count(**) from (select ** from group by **) a 

两者的差别在于：

  1. group by 会让数据先进行去重处理，避免了数据倾斜的问题
  2. group by 会将数据分配到多个reduce上计算，节省了reduce计算的时间

数据倾斜导致的查询缓慢

原因：mapreduce时 因数据在节点上分布不均匀，导致单节点承载数据过大，节点执行效率低，拉低整体的执行效率

  1. 数据本身分布不均（例如当数据属于极小方差的正态分布，数据大量堆积在均值上。原因： a.表的设计存在问题，即本身粒度存在问题 b.业务数据本身客观存在导致）
  2. sql中小大表join时，主表的key过于集中或空值过多（空值由一个reduce进行处理）

解决方法：

  1. 将主表中过于集中的值单独提取出来计算
  2. 重构主表，加入随机字段和主键合并

特殊运算

位运算：（二进制计算）

& 按位与：5 & 3 = 1【101 & 11 = 1】

| 按位或：5 | 3 = 7 【101 | 11 = 111】

^异或：5 ^ 3 = 6 【101 ^ 011 = 110】

位运算在留存计算中的运用：【适用于要同时计算多个留存率的需求】

select  a1.pt,
		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.pt,
				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  pdw.xxx
				 where  pt >= '2020-01-01'
				   and  pt <= '2020-01-31'
				) a 
		 group  by a.pt,
				a.user_id
		) a1  
 group  by a1.pt

取余：%

取整：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.id,
        a.array_col,
	    tmp.tmp_col,
        a.array_col2,
	    tmp.tmp_col2
  from  pdw.xxx 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地址'] ;

内外部表的区别：

  1. hdfs文件的区别：外部表是作为hdfs文件的一种映射，内部表是与hdfs文件一一对应
  2. 删除操作的区别：外部表只是删除表的映射关系（即元数据），数据源不做删除动作；内部表是删除hdfs文件和删除表
  3. 加载数据的区别：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【自主性高，代码耦合性高】

可视化埋点：埋点有控台配置【自主性中，代码耦合性低】

全埋点：开发规定内容，返回所有用户行为信息【自主性低，代码耦合性低，信息全】

埋点的内容

一条完整的埋点应包含 用户信息（设备信息），用户行为，行为对应的业务信息，行为的来源

更多推荐

01 Hive SQL基础知识