Hive SQL语法详解

关注

原创最新推荐文章于 2026-04-01 03:20:18 发布 2k 阅读 AI 写同款· GEO 优化 ›

SQL 语句解析：Hive 通过 ANTLR 生成语法树，解析 SQL 语句。作者：禅与计算机程序设计艺术 1.简介 Hive 是基于 Hadoop 的一个开源数据仓库系统，其存储数据采取了 HDFS (Hadoop 文件系统)作为底层文件存储。由于 Hive ql 中定义的查询语言 SQL 在运行时需要将 SQL 转换为 MapReduce 操作并执行，因此性能上存在一些限制，阅读详情

文章参考博客链接-------Hadoop Hive sql语法详解
文章参考官方文档-------Hive/LanguageManual

hive依赖于HDFS存储数据，Hive将HQL转换成MapReduce执行，所以说Hive是基于Hadoop的一个数据仓库工具，实质就是一款基于HDFS的MapReduce计算框架，对存储在HDFS中的数据进行分析和管理
Hive 是基于Hadoop 构建的一套数据仓库分析系统，它提供了丰富的SQL查询方式来分析存储在Hadoop 分布式文件系统中的数据，可以将结构化的数据文件映射为一张数据库表，并提供完整的SQL查询功能，可以将SQL语句转换为MapReduce任务进行运行，通过自己的SQL 去查询分析需要的内容，这套SQL 简称Hive SQL，使不熟悉mapreduce 的用户很方便的利用SQL 语言查询，汇总，分析数据。而mapreduce开发人员可以把己写的mapper 和reducer 作为插件来支持Hive 做更复杂的数据分析。

一、DDL（data definition language）

HiveQL DDL包括：

CREATE DATABASE/SCHEMA, TABLE, VIEW, FUNCTION, INDEX
DROP DATABASE/SCHEMA, TABLE, VIEW, INDEX
TRUNCATE TABLE
ALTER DATABASE/SCHEMA, TABLE, VIEW
MSCK REPAIR TABLE (or ALTER TABLE RECOVER PARTITIONS)
SHOW DATABASES/SCHEMAS, TABLES, TBLPROPERTIES, VIEWS, PARTITIONS, FUNCTIONS, INDEX[ES], COLUMNS, CREATE TABLE
DESCRIBE DATABASE/SCHEMA, table_name, view_name

1.建表

建表语句如下

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name 
  [(col_name data_type [COMMENT col_comment], ...)] 
  [COMMENT table_comment] 
  [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] 
  [CLUSTERED BY (col_name, col_name, ...) 
  [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS] 
  [ROW FORMAT row_format] 
  [STORED AS file_format] 
  [LOCATION hdfs_path]

CREATE TABLE 创建一个指定名字的表，如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXIST 选项来忽略这个异常。
EXTERNAL 关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION）（外部表在删除时只会删除外部表的元数据不会删除数据）
LIKE允许用户复制现有的表结构，但是不复制数据
COMMENT可以为表与字段增加描述
表可以包含一个或多个分区列，并为分区列中的每个不同值组合创建单独的数据目录。此外，可以使用CLUSTERED BY列对表或分区进行分区，并且可以通过SORT BY列在该存储桶中对数据进行排序。这可以提高某些类型的查询的性能

ROW FORMAT 是用来设置创建的表在加载数据的时候，支持的列分隔符

  用户在建表的时候可以自定义 SerDe （序列化/反序列化）或者使用自带的 SerDe。
  如果没有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED，将会使用自带的 SerDe。
  在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的 SerDe，
  Hive 通过 SerDe 确定表的具体的列的数据。

这段可参考官网说明Hive—RowFormats&SerDe

STORED AS（存储为）SEQUENCEFILE | TEXTFILE | RCFILE | ORC | PARQUET | AVRO | JSONFILE
如果文件数据是纯文本，可以使用 STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCE 。

2.删表

语句及功能如下：

DROP TABLE [IF EXISTS] table_name [PURGE];   //删除表
TRUNCATE TABLE table_name [PARTITION partition_spec];   //从表或分区中删除所有行

3.改变表

语句及功能如下：

ALTER TABLE table_name RENAME TO new_table_name;   //重命名表
ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]  //这个命令可以允许改变列名、数据类型、注释、列位置或者它们的任意组合
ALTER TABLE table_name ADD | REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)   //ADD是代表新增一字段，字段位置在所有列后面(partition列前)REPLACE则是表示替换表中所有字段。
ALTER TABLE table_name SET FILEFORMAT file_format    //改变文件格式
ALTER TABLE table_name ADD [IF NOT EXISTS] partition_spec [ LOCATION 'location1' ] partition_spec [ LOCATION 'location2' ] ...
      partition_spec:: PARTITION (partition_col = partition_col_value, partition_col = partiton_col_value, ...)    //增加分区
ALTER TABLE table_name DROP partition_spec, partition_spec,...       //删除分区
ALTER TABLE table_name SET TBLPROPERTIES table_properties table_properties:: (property_name = property_value, property_name = property_value, ... )   //用户可以用这个命令向表中增加metadata
MSCK [REPAIR] TABLE table_name [ADD/DROP/SYNC PARTITIONS];  //恢复分区   这将为尚未存在此类元数据的分区更新有关分区的元数据到Hive Metastore

4.视图

创建视图：

CREATE VIEW [IF NOT EXISTS] [db_name.]view_name [(column_name [COMMENT column_comment], ...) ]
  [COMMENT view_comment]
  [TBLPROPERTIES (property_name = property_value, ...)]
  AS SELECT ...;

举个例子：

CREATE VIEW onion_referrers(url COMMENT 'URL of Referring page')
  COMMENT 'Referrers to The Onion website'
  AS
  SELECT DISTINCT referrer_url
  FROM page_view
  WHERE page_url='http://www.theonion.com';

删改视图：

DROP VIEW [IF EXISTS] [db_name.]view_name;   //删除视图
ALTER VIEW [db_name.]view_name SET TBLPROPERTIES table_properties;
table_properties:: (property_name = property_value, property_name = property_value, ...)    //更改视图属性
ALTER VIEW [db_name.]view_name AS select_statement;           //将视图更改为选择
//更改视图选择更改视图的定义，该视图必须存在。语法类似于CREATE VIEW，其效果与CREATE OR REPLACE VIEW相同。
//注意：视图必须已存在，如果视图具有分区，则无法将其替换为Alter View As Select。

5.索引

创建索引：

CREATE INDEX index_name
  ON TABLE base_table_name (col_name, ...)
  AS index_type
  [WITH DEFERRED REBUILD]
  [IDXPROPERTIES (property_name=property_value, ...)]
  [IN TABLE index_table_name]
  [
     [ ROW FORMAT ...] STORED AS ...
     | STORED BY ...
  ]
  [LOCATION hdfs_path]
  [TBLPROPERTIES (...)]
  [COMMENT "index comment"];
  //默认情况下，索引分区与基表的分区匹配。PARTITIONED BY子句可用于指定表的分区列的子集
  //（此列列表可以为空以指示索引跨越表的所有分区）。例如，即使索引仅按日期划分（每个索引分
  //区跨越所有区域）， 也可以按日期+区域对表进行分区。
  //无法在视图上创建索引。我们将（最终）在非本机表上支持它们（在相应的存储处理程序指示它支持它们的情况下）。

删改索引：

DROP INDEX [IF EXISTS] index_name ON table_name;     //删除索引
ALTER INDEX index_name ON table_name [PARTITION partition_spec] REBUILD;    //ALTER INDEX ... REBUILD构建使用WITH DEFERRED REBUILD子句创建的索引，或者重建以前构建的索引。如果指定了PARTITION，则仅重建该分区。

6.展示

语句如下：

SHOW (DATABASES|SCHEMAS) [LIKE 'identifier_with_wildcards'];    //展示数据库
SHOW TABLES [IN database_name] ['identifier_with_wildcards'];     //展示表
SHOW VIEWS [IN/FROM database_name] [LIKE 'pattern_with_wildcards'];    //展示视图
SHOW PARTITIONS table_name;    //展示分区
SHOW CREATE TABLE ([db_name.]table_name|view_name);    //展示创建表

描述：DESCRIBE .....
...

二、DML（data manipulation language）

1.文件导入

在将数据加载到表中时，Hive不会进行任何转换。加载操作当前是纯复制/移动操作，它将数据文件移动到与Hive表对应的位置。

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]
LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)] [INPUTFORMAT 'inputformat' SERDE 'serde'] (3.0 or later)

3.0之前Load 操作只是单纯的复制/移动操作，将数据文件移动到 Hive 表对应的位置。Hive 3.0以后支持其他加载操作，因为Hive在内部将加载重写为INSERT AS SELECT。

filepath
相对路径，例如：project/data1
绝对路径，例如： /user/hive/project/data1
包含模式的完整 URI，例如：hdfs://namenode:9000/user/hive/project/data1
例如：
LOAD DATA LOCAL INPATH ‘./examples/files/kv1.txt’ OVERWRITE INTO TABLE pokes;

2.从查询中将数据插入Hive表

语句如下：

INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]] select_statement1 FROM from_statement;

INSERT OVERWRITE将覆盖表或分区中的任何现有数据
INSERT INTO将附加到表或分区，保持现有数据不变。（注意：INSERT INTO语法仅在0.8版本中可用。）
可以对表或分区执行插入操作。如果表已分区，则必须通过指定所有分区列的值来指定表的特定分区。如果hive.typecheck.on.insert设置为true，则验证，转换和规范化这些值以符合其列类型（Hive 0.12.0以后）。
可以在同一查询中指定多个插入子句（也称为多表插入）。
每个select语句的输出都写入选定的表（或分区）。目前，OVERWRITE关键字是必需的，意味着所选表或分区的内容将替换为相应select语句的输出。

3.将查询结果写入HDFS文件系统

语句如下：

INSERT OVERWRITE [LOCAL] DIRECTORY directory1 SELECT ... FROM ...
//数据写入文件系统时进行文本序列化，且每列用^A 来区分，\n换行

4.从SQL插入值到表中

INSERT … VALUES语句可用于直接从SQL将数据插入表中。（INSERT … VALUES从Hive 0.14开始可用。）

INSERT INTO TABLE tablename [PARTITION (partcol1[=val1], partcol2[=val2] ...)] VALUES values_row [, values_row ...]

VALUES子句中列出的每一行都插入到表tablename中。
必须为表中的每个列提供值。尚不支持允许用户仅将值插入某些列的标准SQL语法。为了模仿标准SQL，可以为用户不希望为其赋值的列提供空值。
Hive不支持复杂类型（数组，映射，结构，联合）的文字

UPDATE tablename SET column = value [, column = value ...] [WHERE expression]
//0.14

引用的列必须是要更新的表的列。
分配的值必须是Hive在select子句中支持的表达式。因此支持算术运算符，UDF，强制转换，文字等。不支持子查询。
只会更新与WHERE子句匹配的行。

DELETE FROM tablename [WHERE expression]
//0.14

只会删除与WHERE子句匹配的行。
在Hive 0.14中，成功完成此操作后，将自动提交更改。

MERGE INTO <target table> AS T USING <source expression/table> AS S
ON <boolean expression1>
WHEN MATCHED [AND <boolean expression2>] THEN UPDATE SET <set clause list>
WHEN MATCHED [AND <boolean expression3>] THEN DELETE
WHEN NOT MATCHED [AND <boolean expression4>] THEN INSERT VALUES<value list>
//2.2

合并允许根据与源表的连接结果对目标表执行操作。
在Hive 2.2中，成功完成此操作后，将自动提交更改。

三、DQL （data Query Language）

这里是查询语句

1.基本的Select 操作

语句如下：

[WITH CommonTableExpression (, CommonTableExpression)*]    (Note: Only available starting with Hive 0.13.0)
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
  FROM table_reference
  [WHERE where_condition]
  [GROUP BY col_list]
  [ORDER BY col_list]
  [CLUSTER BY col_list
    | [DISTRIBUTE BY col_list] [SORT BY col_list]
  ]
 [LIMIT [offset,] rows]

SELECT语句可以是联合查询的一部分或另一个查询的子查询。
table_reference表示查询的输入表。它可以是常规表，视图，连接构造或子查询。
表名和列名不区分大小写。在Hive 0.12及更早版本中，表名和列名中只允许使用字母数字和下划线字符。
在Hive 0.13及更高版本中，列名可以包含任何 Unicode 字符（请参阅 HIVE-6013）。在backticks（`）中指定的任何列名都按字面处理。在反引号字符串中，使用双反引号（``）来表示反引号字符。
使用ALL和DISTINCT选项区分对重复记录的处理。默认是ALL，表示查询所有记录。DISTINCT表示去掉重复的记录
Where 条件，类似我们传统SQL的where 条件，目前支持 AND,OR ,0.9版本支持between,IN, NOT IN，不支持EXIST ,NOT EXIST
ORDER BY与SORT BY的不同，ORDER BY 全局排序，只有一个Reduce任务，SORT BY 只在本机做排序
Limit 可以限制查询的记录数
实现Top k 查询，下面的查询语句查询销售记录最大的 5 个销售代表。

SET mapred.reduce.tasks = 1 
  SELECT * FROM test SORT BY amount DESC LIMIT 5

SELECT 语句可以使用正则表达式做列选择，下面的语句查询除了 ds 和 hr 之外的所有列：
SELECT (ds|hr)?+.+ FROM test

2.基于分区的查询

通常，SELECT查询扫描整个表（除了采样）。如果使用PARTITIONED BY子句创建表，则查询可以执行分区修剪并仅扫描与查询指定的分区相关的表的一小部分。如果在WHERE子句或JOIN中的ON子句中指定了分区谓词，则Hive当前会执行分区修剪。例如，如果在页面日期对表page_views进行了分区，则以下查询将在2008-03-01和2008-03-31之间的几天内检索行。

SELECT page_views.*
FROM page_views
WHERE page_views.date >= '2008-03-01' AND page_views.date <= '2008-03-31'

一般 SELECT 查询会扫描整个表，使用 PARTITIONED BY 子句建表，查询就可以利用分区剪枝（input pruning）的特性
Hive 当前的实现是，只有分区断言出现在离 FROM 子句最近的那个WHERE 子句中，才会启用分区剪枝

3.Join一览

语句如下：

   table_reference JOIN table_factor [join_condition] 
  | table_reference {LEFT|RIGHT|FULL} [OUTER] JOIN table_reference join_condition 
  | table_reference LEFT SEMI JOIN table_reference join_condition

Hive 只支持等值连接（equality joins）、外连接（outer joins）和（left semi joins）。Hive 不支持所有非等值的连接，因为非等值连接非常难转化到 map/reduce 任务
可以 join 多于 2 个表，例如

  SELECT a.val, b.val, c.val FROM a JOIN b 
    ON (a.key = b.key1) JOIN c ON (c.key = b.key2)

LEFT，RIGHT和FULL OUTER关键字用于处理join中空记录的情况
LEFT SEMI JOIN 是 IN/EXISTS 子查询的一种更高效的实现（LEFT SEMI JOIN 的限制是， JOIN 子句中右边的表只能在ON 子句中设置过滤条件）

SELECT a.key, a.value 
  FROM a 
  WHERE a.key in 
   (SELECT b.key 
    FROM B);
       可以被重写为：
      SELECT a.key, a.val 
   FROM a LEFT SEMI JOIN b on (a.key = b.key)

join 时，每次 map/reduce 任务的逻辑是这样的：reducer 会缓存 join 序列中除了最后一个表的所有表的记录，再通过最后一个表将结果序列化到文件系统
实践中，应该把最大的那个表写在最后
UNION ALL：用来合并多个select的查询结果，需要保证select中字段须一致

    select_statement UNION ALL select_statement UNION ALL select_statement ...

四、HiveQL与SQL

1、两表内联时

SQL中对两表内联可以写成：

select * from dual a,dual b where a.key = b.key;

Hive中应为

select * from dual a join dual b on a.key = b.key;

而不是传统的格式：

SELECT t1.a1 as c1, t2.b1 as c2FROM t1, t2 WHERE t1.a2 = t2.b2

2、分号字符

分号是SQL语句结束标记，在HiveQL中也是，但是在HiveQL中，对分号的识别没有那么智慧，例如：

select concat(key,concat(';',key)) from dual;

但HiveQL在解析语句时提示：

FAILED: Parse Error: line 0:-1 mismatched input '<EOF>' expecting ) in function specification

解决的办法是，使用分号的八进制的ASCII码进行转义，那么上述语句应写成：

select concat(key,concat('\073',key)) from dual;

3、IS [NOT] NULL

SQL中null代表空值, 值得警惕的是, 在HiveQL中String类型的字段若是空(empty)字符串, 即长度为0, 那么对它进行IS NULL的判断结果是False.

4、Hive不支持将数据插入现有的表或分区中

仅支持覆盖重写整个表，示例如下：

INSERT OVERWRITE TABLE t1
SELECT * FROM t2;

5、hive不支持INSERT INTO, UPDATE, DELETE操作

-这样的话，就不要很复杂的锁机制来读写数据。

 INSERT INTO syntax is only available starting in version 0.8。INSERT INTO就是在表或分区中追加数据。

6、hive支持嵌入mapreduce程序，来处理复杂的逻辑

如：

FROM (
MAP doctext USING 'python wc_mapper.py' AS (word, cnt)
FROM docs
CLUSTER BY word
) a
REDUCE word, cnt USING 'python wc_reduce.py';

doctext: 是输入
word, cnt: 是map程序的输出
CLUSTER BY: 将wordhash后，又作为reduce程序的输入
并且map程序、reduce程序可以单独使用，如：

FROM (
FROM session_table
SELECT sessionid, tstamp, data
DISTRIBUTE BY sessionid SORT BY tstamp
) a
REDUCE sessionid, tstamp, data USING 'session_reducer.sh';

DISTRIBUTE BY: 用于给reduce程序分配行数据

7、hive支持将转换后的数据直接写入不同的表，还能写入分区、hdfs和本地目录。

这样能免除多次扫描输入表的开销。

FROM t1
 
INSERT OVERWRITE TABLE t2
SELECT t3.c2, count(1)
FROM t3
WHERE t3.c1 <= 20
GROUP BY t3.c2
 
INSERT OVERWRITE DIRECTORY '/output_dir'
SELECT t3.c2, avg(t3.c1)
FROM t3
WHERE t3.c1 > 20 AND t3.c1 <= 30
GROUP BY t3.c2
 
INSERT OVERWRITE LOCAL DIRECTORY '/home/dir'
SELECT t3.c2, sum(t3.c1)
FROM t3
WHERE t3.c1 > 30
GROUP BY t3.c2;