HQL基本语法说明
更新时间 2026-10-12 11:43:55
最近更新时间: 2026-10-12 11:43:55
本章节介绍HQL基本语法。
创建数据库和表
本小节主要介绍如何使用HQL 创建数据库和表。在 Hive 中创建表主要有两种方式：
1、自定义表结构，同时使用 EXTERNAL 关键字来区分内部表和外部表：
1）内部表：数据的生命周期完全由 Hive 管理时，drop 命令会同时删除元数据和表数据；
2）外部表：当数据需要被多种工具（如 Pig 等）共同处理时，应该使用外部表。删除外部表时，只删除元数据。
2、根据已有表创建新表：
1）CREATE LIKE：该语句可以完全复制原有表的结构，包括存储格式，但不会导入数据；
2）CTAS：根据查询结果创建新表，使用 CREATE AS SELECT 语句可以在复制原表结构的同时指定要复制哪些字段。
Hive 建表基本语法如下：
CREATE [EXTERNAL] TABLE <table_name>
(<col_name> <data_type> [column_constraint_specification] [COMMENT col_comment],
[<col_name> <data_type> ...]
)
-- 表注释
[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 TEXTFILE|ORC|CSVFILE]
-- 指定表的位置
[LOCATION <file_path>]
-- 指定表属性
[TBLPROPERTIES ('<property_name>'='<property_value>', ...)]
-- 指定是否从查询中创建表
[AS select_statement];
file_format:
: SEQUENCEFILE
| TEXTFILE -- (Default, depending on configuration)
| RCFILE -- (Note: Available in Hive 0.6.0 and later)
| ORC -- (Note: Available in Hive 0.11.0 and later)
| PARQUET -- (Note: Available in Hive 0.13.0 and later)
| AVRO -- (Note: Available in Hive 0.14.0 and later)
| JSONFILE -- (Note: Available in Hive 4.0.0 and later)
| INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname示例如下：
-- 内部表示例
CREATE TABLE page_view
(
viewTime INT,
userid BIGINT,
page_url STRING,
referrer_url STRING,
ip STRING COMMENT 'IP Address of the User'
)
COMMENT 'This is the page view table'
PARTITIONED BY (dt STRING, country STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\001'
STORED AS SEQUENCEFILE;
-- 外部表示例
CREATE EXTERNAL TABLE page_view
(
viewTime INT,
userid BIGINT,
page_url STRING,
referrer_url STRING,
ip STRING COMMENT 'IP Address of the User',
country STRING COMMENT 'country of origination'
)
COMMENT 'This is the staging page view table'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\054'
STORED AS TEXTFILE
LOCATION '<hdfs_location>';插入数据
Hive 支持两种插入数据的方式：SQL 直接插入数据、从查询中插入数据。
SQL 直接插入数据的语法如下：
INSERT INTO TABLE <tablename>
[PARTITION (partcol1[=val1], partcol2[=val2] ...)]
VALUES values_row [, values_row...];一个简短的示例如下：
CREATE TABLE students
(
name VARCHAR(64),
age INT,
gpa DECIMAL(3, 2)
)
CLUSTERED BY (age) INTO 2 BUCKETS
STORED AS ORC;
INSERT INTO TABLE students
VALUES ('fred flintstone', 35, 1.28),
('barney rubble', 32, 2.32);Hive 也支持从查询结果中插入数据，它的基本语法如下：
INSERT OVERWRITE TABLE tablename1
[PARTITION (partcol1=val1, partcol2=val2 ...)
[IF NOT EXISTS]] select_statement1
FROM from_statement;
INSERT INTO TABLE tablename1
[PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1
FROM from_statement;一个简短的示例如下：
FROM page_view_stg pvs
INSERT OVERWRITE TABLE page_view PARTITION(dt='2008-06-08', country)
SELECT pvs.viewTime, pvs.userid, pvs.page_url, pvs.referrer_url, null, null, pvs.ip, pvs.cnt;查询数据
Hive 查询语法如下：
-- CTE
-- CTE(Common Table Expression)，即公共表表达式，是一种通过 WITH 子句定义的临时结果集，
-- 可以在查询的其余部分中被引用。CTE 提高了查询的可读性和复用性，
-- 尤其是在复杂查询或递归查询中非常有用。
[WITH CommonTableExpression (, CommonTableExpression)*]
-- DISINTCT 去重
SELECT [ALL | DISTINCT] <select_expr>, <select_expr>, ...
FROM <tbl_name>
-- 过滤
[WHERE where_condition]
-- 分组
[GROUP BY col_list]
-- 做全局排序，无论对 reducer 的个数做什么样的设置都会是 1
[ORDER BY col_list]
-- SORT BY 区内排序，对每一个 reducer 产生一个排序文件，每个 reducer 内部进行排序
-- DISTRIBUTE BY 控制Map端如何发送数据到Reduce端
-- 如果SORT BY 和 DISTRIBUTE BY 字段相同可以使用CLUSTER BY代替
[CLUSTER BY col_list
| [DISTRIBUTE BY col_list] [SORT BY col_list]
]
-- 输出数量限制
[LIMIT number]连接查询。Hive 支持多种 Join：
1、INNER JOIN：内关联，JOIN 关键字的默认行为，只能返回关联上的结果；
2、LEFT OUTER JOIN：左外关联，以关键字前面的表作为主表，和其他表进行关联，返回记录数和主表的一致，关联不上的字段置为 NULL；
3、RIGHT OUTER JOIN：右外关联，以关键词后面的表作为主表，和前面的表做关联，返回记录数和主表一致，关联不上的字段为 NULL；
4、FULL OUTER JOIN：全外关联，以两个表的记录为基准，返回两个表的记录去重之和，关联不上的字段为 NULL。
Hive 中 Join 的基本语法如下：
join_table:
table_reference [INNER] 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
| table_reference CROSS JOIN table_reference [join_condition] (as of Hive 0.10)
table_reference:
table_factor
| join_table
table_factor:
tbl_name [alias]
| table_subquery alias
| ( table_references )
join_condition:
ON expression一个简短的示例如下：
-- 多表内关联
SELECT *
FROM table1
JOIN table2
ON table1.id = table2.id;
-- 左外关联
SELECT *
FROM table1
LEFT JOIN table2
ON table1.id = table2.id;
-- 右外关联
SELECT *
FROM table1
RIGHT JOIN table2
ON table1.id = table2.id;
-- 全外关联
SELECT *
FROM table1
FULL OUTER JOIN table2
ON table1.id = table2.id;分组查询。Hive 使用 GROUP BY 关键字进行分组查询，它的语法如下：
SELECT expression (, expression)* FROM src groupByClause?一个简短的示例如下：
SELECT pv_users.gender, COUNT(DISTINCT pv_users.userid), COUNT(DISTINCT pv_users.ip)
FROM pv_users
GROUP BY pv_users.gender;注意
SELECT 后面的非聚合列必须出现在 GROUP BY 表达式中。
综合示例。以TPCDS Q23 为例展示一个综合查询示例：
WITH frequent_ss_items AS
(SELECT SUBSTR(i_item_desc, 1, 30) itemdesc, i_item_sk item_sk, d_date solddate, COUNT(*) cnt
FROM store_sales
, date_dim
, item
WHERE ss_sold_date_sk = d_date_sk
AND ss_item_sk = i_item_sk
AND d_year IN (1999, 1999 + 1, 1999 + 2, 1999 + 3)
GROUP BY SUBSTR(i_item_desc, 1, 30), i_item_sk, d_date
HAVING COUNT(*) > 4),
max_store_sales AS
(SELECT MAX(csales) tpcds_cmax
FROM (SELECT c_customer_sk, SUM(ss_quantity * ss_sales_price) csales
FROM store_sales
, customer
, date_dim
WHERE ss_customer_sk = c_customer_sk
AND ss_sold_date_sk = d_date_sk
AND d_year IN (1999, 1999 + 1, 1999 + 2, 1999 + 3)
GROUP BY c_customer_sk)),
best_ss_customer AS
(SELECT c_customer_sk, SUM(ss_quantity * ss_sales_price) ssales
FROM store_sales
, customer
WHERE ss_customer_sk = c_customer_sk
GROUP BY c_customer_sk
HAVING SUM(ss_quantity * ss_sales_price) > (95 / 100.0) * (SELECT *
FROM max_store_sales))
SELECT SUM(sales)
FROM (SELECT cs_quantity * cs_list_price sales
FROM catalog_sales
, date_dim
WHERE d_year = 1999
AND d_moy = 1
AND cs_sold_date_sk = d_date_sk
AND cs_item_sk IN (SELECT item_sk FROM frequent_ss_items)
AND cs_bill_customer_sk IN (SELECT c_customer_sk FROM best_ss_customer)
UNION ALL
SELECT ws_quantity * ws_list_price sales
FROM web_sales
, date_dim
WHERE d_year = 1999
AND d_moy = 1
AND ws_sold_date_sk = d_date_sk
AND ws_item_sk IN (SELECT item_sk FROM frequent_ss_items)
AND ws_bill_customer_sk IN (SELECT c_customer_sk FROM best_ss_customer))
LIMIT 100;
WITH frequent_ss_items AS
(SELECT SUBSTR(i_item_desc, 1, 30) itemdesc, i_item_sk item_sk, d_date solddate, COUNT(*) cnt
FROM store_sales
, date_dim
, item
WHERE ss_sold_date_sk = d_date_sk
AND ss_item_sk = i_item_sk
AND d_year IN (1999, 1999 + 1, 1999 + 2, 1999 + 3)
GROUP BY SUBSTR(i_item_desc, 1, 30), i_item_sk, d_date
HAVING COUNT(*) > 4),
max_store_sales AS
(SELECT MAX(csales) tpcds_cmax
FROM (SELECT c_customer_sk, SUM(ss_quantity * ss_sales_price) csales
FROM store_sales
, customer
, date_dim
WHERE ss_customer_sk = c_customer_sk
AND ss_sold_date_sk = d_date_sk
AND d_year IN (1999, 1999 + 1, 1999 + 2, 1999 + 3)
GROUP BY c_customer_sk)),
best_ss_customer AS
(SELECT c_customer_sk, SUM(ss_quantity * ss_sales_price) ssales
FROM store_sales
, customer
WHERE ss_customer_sk = c_customer_sk
GROUP BY c_customer_sk
HAVING SUM(ss_quantity * ss_sales_price) > (95 / 100.0) * (SELECT *
FROM max_store_sales))
SELECT c_last_name, c_first_name, sales
FROM (SELECT c_last_name, c_first_name, SUM(cs_quantity * cs_list_price) sales
FROM catalog_sales
, customer
, date_dim
WHERE d_year = 1999
AND d_moy = 1
AND cs_sold_date_sk = d_date_sk
AND cs_item_sk IN (SELECT item_sk FROM frequent_ss_items)
AND cs_bill_customer_sk IN (SELECT c_customer_sk FROM best_ss_customer)
AND cs_bill_customer_sk = c_customer_sk
GROUP BY c_last_name, c_first_name
UNION ALL
SELECT c_last_name, c_first_name, SUM(ws_quantity * ws_list_price) sales
FROM web_sales
, customer
, date_dim
WHERE d_year = 1999
AND d_moy = 1
AND ws_sold_date_sk = d_date_sk
AND ws_item_sk IN (SELECT item_sk FROM frequent_ss_items)
AND ws_bill_customer_sk IN (SELECT c_customer_sk FROM best_ss_customer)
AND ws_bill_customer_sk = c_customer_sk
GROUP BY c_last_name, c_first_name)
ORDER BY c_last_name, c_first_name, sales
LIMIT 100;常用函数
日期和时间函数：
1、CURRENT_DATE()：返回当前日期；
2、DATE_ADD()：将日期加上指定的天数；
3、DATE_SUB()：将日期减去指定的天数；
4、UNIX_TIMESTAMP()：返回当前时间的 Unix 时间戳；
5、FROM_UNIXTIME()：将 Unix 时间戳转换为日期格式。
字符串函数：
1、CONCAT()：连接多个字符串；
2、SUBSTRING()：返回字符串的子串；
3、LENGTH()：返回字符串的长度；
4、TRIM()：去掉字符串首尾的空格；
5、INSTR()：返回子字符串在字符串中的位置。
聚合函数：
1、COUNT()：返回某列的记录数；
2、SUM()：返回某列的总和；
3、AVG()：返回某列的平均值；
4、COLLECT_LIST()：返回包含所有元素的列表（不去重）；
5、MAX()：返回某列的最大值。
集合函数。此类函数用于处理数组、映射等数据结构：
1、SIZE()：返回集合的大小；
2、ARRAY()：创建数组；
3、MAP()：创建键值对映射；
4、MAP_KEYS()：返回映射的所有键；
5、MAP_VALUES()：返回映射的所有值。
其他函数：
1、CAST()：将一种数据类型转换为另一种；
2、NVL()：如果第一个参数为 NULL，返回第二个参数；
3、CASE WHEN：实现复杂的条件判断；
4、ROUND()：四舍五入；
5、CEIL()：向上取整。
函数使用注意事项：
1、TO_DATE() 函数将时间戳转换成 DATE 类型，只保留年月日，如果想要精确到秒，需要使用 UNIX_TIMESTAMP() 或 FROM_UNIXTIME()；
2、所有的字符串函数如果参数有 null，则结果必为 null，因此使用时需要 IFNULL() 或 COALESCE() 来处理；
3、SUBSTR() 方法的索引从 1 开始，而不是从 0 开始；
4、插入 STRUCT 类型数据时要用 namestruct 函数，而不是 struct() 函数；
5、HIVE 类型转换时需要注意 BOOLEAN 类型，该类型只能和 void 类型以及自身进行隐式转换；
6、针对数组类型，访问越界时会返回 NULL；
7、/ 运算符是浮点数运算，DIV 函数是整数除法，结果会去除小数部分；
8、窗口函数依赖于 ORDER BY 和 PARTITION BY 来确定结果的顺序和分组。如果顺序不正确，结果可能不符合预期；
9、对于 NULL 处理，COALESCE() 是更好的选择，它会返回第一个非 NULL 值；
10、CURRENT_TIMESTAMP 默认采用的是 UTC 时间。
函数使用示例。前置准备如下：
-- 1. 创建测试表
CREATE TABLE test_table
(
id INT,
name STRING,
salary DOUBLE,
join_date DATE,
scores ARRAY<INT>,
attributes MAP<STRING, STRING>
);-- 2. 插入一些测试数据
INSERT INTO test_table
VALUES (1, 'Alice', 5000.50, '2022-01-15', ARRAY(80, 90, 95), MAP('age', '30', 'city', 'New York')),
(2, 'Bob', 6000.75, '2021-11-23', ARRAY(70, 85), MAP('age', '28', 'city', 'Los Angeles')),
(3, 'Charlie', 7000.00, '2023-03-10', ARRAY(95, 85, 92), MAP('age', '35', 'city', 'Chicago'));测试用例1：测试 CURRENT_DATE() 和 DATE_ADD()，返回当前日期和加入天数后的日期：
SELECT current_date() AS now,
DATE_ADD(join_date, 10) AS date_after_10_days,
DATE_SUB(join_date, 5) AS date_before_5_days
FROM test_table;测试结果如下：
-- | 2024-10-25 | 2022-01-25 | 2022-01-10 |
-- | 2024-10-25 | 2021-12-03 | 2021-11-18 |
-- | 2024-10-25 | 2023-03-20 | 2023-03-05 |测试用例2：测试 UNIX_TIMESTAMP() 和 FROM_UNIXTIME()：
SELECT UNIX_TIMESTAMP() AS current_unix_time,
FROM_UNIXTIME(UNIX_TIMESTAMP()) AS current_time_in_readable_format
FROM test_table;测试结果如下：
-- | 1729825446 | 2024-10-25 04:24:06 |
-- | 1729825446 | 2024-10-25 04:24:06 |
-- | 1729825446 | 2024-10-25 04:24:06 |测试用例3：测试 CONCAT()、LENGTH()、SUBSTRING()、TRIM() 和 INSTR()：
SELECT name,
CONCAT('Employee: ', name) AS full_name,
LENGTH(name) AS name_length,
SUBSTRING(name, 1, 3) AS name_first_3_chars,
TRIM(' Alice ') AS trimmed_name,
INSTR(name, 'li') AS position_of_substring
FROM test_table;测试结果如下：
-- | Alice | Employee: Alice | 5 | Ali | Alice | 2 |
-- | Bob | Employee: Bob | 3 | Bob | Alice | 0 |
-- | Charlie | Employee: Charlie | 7 | Cha | Alice | 5 |测试用例4：测试 COUNT()、SUM()、AVG()、COLLECT_LIST()、MAX()：
SELECT COUNT(*) AS total_employees,
SUM(salary) AS total_salary,
ROUND(AVG(salary), 2) AS average_salary,
MAX(salary) AS highest_salary,
COLLECT_LIST(name) AS employee_names
FROM test_table;测试结果如下：
-- | 3 | 18001.25 | 6000.42 | 7000.0 | ["Alice","Bob","Charlie"] |测试用例5：测试 SIZE()、ARRAY()、MAP()、MAP_KEYS()、MAP_VALUES()：
SELECT id,
SIZE(scores) AS number_of_scores,
ARRAY(1, 2, 3) AS new_array,
MAP('key1', 'value1', 'key2', 'value2') AS new_map,
MAP_KEYS(attributes) AS attribute_keys,
MAP_VALUES(attributes) AS attribute_values
FROM test_table;测试结果如下：
-- | 1 | 3 | [1,2,3] | {"key1":"value1","key2":"value2"} | ["age","city"] | ["30","New York"] |
-- | 2 | 2 | [1,2,3] | {"key1":"value1","key2":"value2"} | ["age","city"] | ["28","Los Angeles"] |
-- | 3 | 3 | [1,2,3] | {"key1":"value1","key2":"value2"} | ["age","city"] | ["35","Chicago"] |测试用例6：测试 CAST()、NVL()、CASE WHEN、ROUND()、CEIL()：
SELECT id,
CAST(salary AS STRING) AS salary_as_string,
NVL(NULL, 'Default Value') AS nvl_test,
CASE
WHEN salary > 6000 THEN 'High'
ELSE 'Low'
END AS salary_category,
ROUND(salary, 0) AS rounded_salary,
CEIL(salary) AS ceil_salary
FROM test_table;测试结果如下：
-- | 1 | 5000.5 | Default Value | Low | 5001 | 5001 |
-- | 2 | 6000.75 | Default Value | High | 6001 | 6001 |
-- | 3 | 7000.0 | Default Value | High | 7000 | 7000 |测试用例7：综合测试。先插入更多的数据：
INSERT OVERWRITE TABLE test_table
VALUES (1, 'Alice', 5000.50, '2022-01-15', ARRAY(80, 90, 95), MAP('age', '30', 'city', 'New York')),
(2, 'Bob', 6000.75, '2021-11-23', ARRAY(70, 85), MAP('age', '28', 'city', 'Los Angeles')),
(3, 'Charlie', 7000.00, '2023-03-10', ARRAY(95, 85, 92), MAP('age', '35', 'city', 'Chicago')),
(4, 'David', NULL, '2023-06-01', ARRAY(88, 76), MAP('age', '27', 'city', 'Houston')),
(5, 'Eva', 8000.00, '2020-09-05', ARRAY(95, 100), MAP('age', '32', 'city', 'Seattle'));再执行综合查询：
SELECT id,
name,
salary,
join_date,
DATE_ADD(join_date, 30) AS join_date_plus_30_days, -- 日期加上30天
DATE_SUB(join_date, 15) AS join_date_minus_15_days, -- 日期减去15天
UNIX_TIMESTAMP(join_date) AS join_unix_timestamp, -- 入职日期的Unix时间戳
FROM_UNIXTIME(UNIX_TIMESTAMP(join_date)) AS join_date_readable, -- Unix时间戳转为可读格式
CONCAT(name, ' has a salary of $', CAST(salary AS STRING)) AS salary_info, -- 连接字符串
LENGTH(name) AS name_length, -- 获取姓名长度
TRIM(name) AS trimmed_name, -- 去掉姓名的空格
INSTR(name, 'a') AS position_of_a, -- 查找字母 'a' 在姓名中的位置
COUNT(*) OVER () AS total_employees, -- 计算总员工数
AVG(salary) OVER () AS average_salary, -- 计算平均薪资
SIZE(scores) AS score_count, -- 计算绩效评分的数量
MAP_KEYS(attributes) AS attribute_keys, -- 获取属性映射的所有键
MAP_VALUES(attributes) AS attribute_values, -- 获取属性映射的所有值
CASE
WHEN salary IS NULL THEN 'Salary not provided'
ELSE 'Salary available'
END AS salary_status, -- 判断薪资是否提供
ROUND(salary, 0) AS rounded_salary, -- 四舍五入薪资
CEIL(salary) AS ceil_salary -- 向上取整薪资
FROM test_table;删除库表
删除数据库的语法如下：
DROP DATABASE [IF EXISTS] database_name [RESTRICT | CASCADE];参数说明如下：
1、IF EXISTS：添加该参数会校验数据库是否存在，如果数据库不存在则不执行任何操作；
2、CASCADE：如果指定该参数，删除数据库的同时会删除数据库中所有的表；
3、RESTRICT：删除数据库的默认行为，如果数据库不为空，删除操作将会抛出异常。
Hive 删除表的语法如下：
DROP TABLE [IF EXISTS] table_name [PURGE];1、IF EXISTS：添加该参数会校验表是否存在，如果表不存在则不执行任何操作；
2、PURGE：删除表的默认行为是将数据移动到 .Trash/Current，如果指定该参数则会直接删除数据。
一个简单示例如下：
-- 1. 使用创建好的测试数据库 test_drop 创建测试表, 如果数据库不存在则执行 create database test_drop;
CREATE TABLE test_drop.test_drop_table
(
id INT,
val STRING
) STORED AS ORC;-- 2. 插入数据
INSERT INTO test_drop.test_drop_table (id, val)
VALUES (1, 'a'),
(2, 'b');-- 3. 尝试删除数据库
-- 抛出异常，Error while processing statement: FAILED: Execution Error, return code 1
-- from org.apache.hadoop.hive.ql.exec.DDLTask. InvalidOperationException(message:Database test_drop is not empty.
DROP DATABASE test_drop RESTRICT;-- 4. 删除表
DROP TABLE test_drop.test_drop_table;-- 5. 再次删除数据库，成功删除
DROP DATABASE test_drop RESTRICT;-- 6. 重新执行步骤1～步骤2；使用 CASCADE 选项删除数据库，可以成功删除数据库
DROP DATABASE test_drop CASCADE;其他重要语句
-- 显示所有数据库名称
show databases;-- 显示所有表名称
show tables;-- 显示数据库详细信息
desc database extended <database_name>;-- 切换数据库
use <database_name>;
