Hive SQL 几乎是每一位互联网分析师的必备技能，相信每一位面试过大厂的童鞋都有被面试官问到 Hive 优化问题的经历。所以掌握扎实的 HQL 基础尤为重要，既能帮分析师在日常工作中如鱼得水提高效率，也能在跳槽时获得一份更好的工作 offer。

本篇为 Hive 入门篇，主要为 Hive SQL 基础语法介绍，文章争取做到言简意赅，让大家 30 分钟入门 HQL。

文中视角多处 HQL 对比 关系型数据库 SQL，适合有一定 SQL 基础的童鞋。（未掌握基础 SQL 童鞋请移步「w3c school - SQL」快速上手 SQL）

---------- Hive 优化篇敬请期待

一、Hive 介绍

简单来说，Hive 是基于 Hadoop 的一个数据仓库工具。

Hive 的计算基于 Hadoop 实现的一个特别的计算模型 MapReduce，它可以将计算任务分割成多个处理单元，然后分散到一群家用或服务器级别的硬件机器上，降低成本并提高水平扩展性。

Hive 的数据存储在 Hadoop 一个分布式文件系统上，即 HDFS。

需明确的是，Hive 作为数仓应用工具，对比 RDBMS（关系型数据库） 有3个不能：

1. 不能像 RDBMS 一般实时响应，Hive 查询延时大；
2. 不能像 RDBMS 做事务型查询，Hive 没有事务机制；
3. 不能像 RDBMS 做行级别的变更操作（包括插入、更新、删除）。

另外，Hive 相比 RDBMS 是一个更宽松的世界，比如：

• Hive 没有定长的 varchar 这种类型，字符串都是 string；
• Hive 是读时模式，它在保存表数据时不会对数据进行校验，而是在读数据时校验不符合格式的数据设置为NULL。

二、Hive 查询语句

Hive select 常规语法与 Mysql 等 RDBMS SQL 几乎无异，下面附注语法格式，具体不做详细讲解。本节重点介绍 Hive 中出现的一些比较特殊且日常中我有用到的一些技巧给到大家参考。

2.1 附注 select 语法及语序，

SELECT[ALL|DISTINCT] select_expr, select_expr, ...FROMtable_reference
[WHEREwhere_condition]
[GROUPBYcol_list]
[ORDERBYorder_condition]
[DISTRIBUTEBYdistribute_condition [SORTBYsort_condition] ]
[LIMITnumber]

2.2 多维度聚合分析 grouping sets/cube/roolup，

以一个示例来说明 3者作用及区别。request 表为后端请求表，现需分别统计3种不同维度的聚合：总共多少请求？不同系统、设备多少请求？不同城市多少请求。

不使用多维聚合方法，

SELECTNULL,NULL,NULL,COUNT(*)FROMrequestsUNIONALLSELECTos, device,NULL,COUNT(*)FROMrequestsGROUPBYos, deviceUNIONALLSELECTnull,null, city,COUNT(*)FROMrequestsGROUPBYcity;

使用 grouping sets，

SELECTos, device, city ,COUNT(*)FROMrequestsGROUPBYos, device, cityGROUPINGSETS((os, device), (city), ());

cube 会枚举指定列的所有可能组合作为 grouping sets，而 roolup 会以按层级聚合的方式产生 grouping sets。如，

GROUPBYCUBE(a, b,c)  
--等价于以下语句。GROUPINGSETS((a,b,c),(a,b),(a,c),(b,c),(a),(b),(c),())GROUPBYROLLUP(a, b,c)
--等价于以下语句。GROUPINGSETS((a,b,c),(a,b),(a), ())

2.3 正则方法指定 select 字段列

说是指定，其实是排除，如：`(num|uid)? . ` 排除 num 和 uid 字段列。

另外，where 使用正则可以如此：where A Rlike B、where A Regexp B。

2.4 Lateral View（一行变多行）

Lateral View 和表生成函数（例如Split、Explode等函数）结合使用，它能够将一行数据拆成多行数据，并对拆分后的数据进行聚合。

假设您有一张表pageAds，它有两列数据，第一列是pageid string，第二列是adid_list，即用逗号分隔的广告ID集合。

现需要统计所有广告在所有页面的出现次数，则先用 Lateral View explode 做处理，即可正常分组聚合统计。

SELECTpageid, adidFROMpageAdsLATERALVIEWexplode(adid_list) adTableASadid;

2.5 窗口函数

Hive 的窗口函数非常丰富，这在很多 RDBMS 中是少见的。（至少在 mysql 的较早期版本一直没有支持窗口函数，一个分组排序得用上非常复杂的 SQL 自定义变量）

其中最常用的窗口函数当属 row_number() over(partition by col order col_2)，它可以实现按指定字段的分组排序。

其它更丰富的窗口函数，我这不赘述，篇幅太大，完全可以重开一篇新的文章。建议参考阿里云 MaxCompute 的这份「窗口函数」的文档，写得非常详细，强烈推荐！

2.6 代码复用

• CTE复用：with t1 as();
• 阿里云 MaxCompute 支持创建 SQL Script 脚本：允许使用 @var:= 方式创建变量，实现复用。

witht1as(selectuser_idfromuserwhere...
)
@var:=selectshop_idfromshopwhere...;select*fromuser_shopwhereuser_idin(select*fromt1)andshop_idin(select*from@var);

三、Hive 定义语句（DDL）

3.1 Hive 建表语句格式，

方法一：独立声明

CREATE[EXTERNAL]TABLE[IFNOTEXISTS] table_name
[(col_name data_type [DEFAULTvalue] [COMMENTcol_comment], ...)]
[COMMENTtable_comment]
[PARTITIONEDBY(col_name data_type [COMMENTcol_comment], ...)]
[CLUSTEREDBY(col_name [, col_name, ...]) [SORTEDBY(col_name [ASC|DESC] [, col_name [ASC|DESC] ...])]INTOnumber_of_buckets BUCKETS] 
[STOREDBYStorageHandler]-- 仅限外部表[WITHSERDEPROPERTIES (Options)]-- 仅限外部表[LOCATION OSSLocation];-- 仅限外部表[LIFECYCLE days]
[AS select_statement]

方法二：从已有表直接复制

CREATETABLE[IFNOTEXISTS] table_nameLIKEexisting_table_name

下面对当中关键的声明语句做解释：

• [EXTERNAL]：声明为外部表，往往在该表需要被多个工具共享时声明，外部表删表不会删数据，只会删元数据。
• col_name datatype：data_type 一定要严谨定义，避免 bigint、double 等等统统用 string 的偷懒做法，否则不知某天数据就出错了。（团队内曾有同事犯过此错误）
• [if not exists]：创建时不指定，若存在同名表则返回出错。指定此选项，若存在同名表忽略后续，不存在则创建。
• [DEFAULT value]：指定列的默认值，当INSERT操作不指定该列时，该列写入默认值。
• [PARTITIONED BY]：指定表的分区字段，当利用分区字段对表进行分区时，新增分区、更新分区内数据和读取分区数据均不需做全表扫描，可以提高处理效率。
• [LIFECYCLE]：是表的生命周期，分区表则每个分区的生命周期与表生命周期相同
• [AS select_statement]：意味着可直接跟 select 语句插入数据

简单示例：创建表sale_detail来保存销售记录，该表使用销售时间 sale_date 和销售区域 region 作为分区列。

createtableifnotexistssale_detail
(
shop_namestring,
customer_idstring,
total_pricedouble)
partitionedby(sale_datestring, regionstring);

创建成功的表可以通过 desc 查看定义信息，

desc