01 Hive SQL基础知识

admin管理员组

文章数量:1794759

01 Hive SQL基础知识

前言

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

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 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地址'] ;

内外部表的区别：

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【自主性高，代码耦合性高】

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

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

埋点的内容

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

本文标签： 基础知识HiveSQL