Hive的语法有哪些

发布时间：2021-12-10 09:57:03 作者：小新
来源：亿速云阅读：209

# Hive的语法有哪些

## 目录
1. [Hive简介](#1-hive简介)
2. [Hive数据定义语言(DDL)](#2-hive数据定义语言ddl)
   - [数据库操作](#21-数据库操作)
   - [表操作](#22-表操作)
3. [Hive数据操作语言(DML)](#3-hive数据操作语言dml)
   - [数据加载](#31-数据加载)
   - [数据查询](#32-数据查询)
   - [数据导出](#33-数据导出)
4. [Hive查询语法](#4-hive查询语法)
   - [基础查询](#41-基础查询)
   - [聚合函数](#42-聚合函数)
   - [JOIN操作](#43-join操作)
   - [子查询](#44-子查询)
5. [Hive函数](#5-hive函数)
   - [内置函数](#51-内置函数)
   - [窗口函数](#52-窗口函数)
   - [自定义函数](#53-自定义函数)
6. [Hive分区与分桶](#6-hive分区与分桶)
   - [分区表](#61-分区表)
   - [分桶表](#62-分桶表)
7. [Hive高级特性](#7-hive高级特性)
   - [视图](#71-视图)
   - [索引](#72-索引)
   - [事务](#73-事务)
8. [总结](#8-总结)

## 1. Hive简介

Apache Hive是建立在Hadoop上的数据仓库基础架构，它提供了数据查询和分析的功能。Hive使用类似SQL的查询语言——HiveQL（HQL），允许熟悉SQL的用户查询存储在Hadoop分布式文件系统（HDFS）中的数据。

## 2. Hive数据定义语言(DDL)

### 2.1 数据库操作

```sql
-- 创建数据库
CREATE DATABASE [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...)];

-- 示例
CREATE DATABASE IF NOT EXISTS sales_db
COMMENT 'Sales database'
LOCATION '/user/hive/warehouse/sales.db';

-- 查看数据库
SHOW DATABASES;
SHOW DATABASES LIKE 'sales*';

-- 使用数据库
USE database_name;

-- 删除数据库
DROP DATABASE [IF EXISTS] database_name [RESTRICT|CASCADE];

2.2 表操作

-- 创建表语法
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]
[TBLPROPERTIES (property_name=property_value, ...)];

-- 示例：创建内部表
CREATE TABLE employees (
  id INT,
  name STRING,
  salary FLOAT,
  department STRING
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE;

-- 示例：创建外部表
CREATE EXTERNAL TABLE external_employees (
  id INT,
  name STRING,
  salary FLOAT
)
LOCATION '/user/hive/external/employees';

-- 查看表
SHOW TABLES;
SHOW TABLES IN database_name;
DESCRIBE [EXTENDED|FORMATTED] table_name;

-- 修改表
ALTER TABLE table_name RENAME TO new_table_name;
ALTER TABLE table_name ADD COLUMNS (col_name data_type [COMMENT col_comment], ...);
ALTER TABLE table_name DROP [COLUMN] column_name;

-- 删除表
DROP TABLE [IF EXISTS] table_name;

3. Hive数据操作语言(DML)

3.1 数据加载

-- 从本地文件加载
LOAD DATA LOCAL INPATH '/path/to/local/file' 
[OVERWRITE] INTO TABLE table_name 
[PARTITION (part_col1=val1, part_col2=val2 ...)];

-- 从HDFS加载
LOAD DATA INPATH '/hdfs/path/to/file' 
[OVERWRITE] INTO TABLE table_name;

-- 示例
LOAD DATA LOCAL INPATH '/home/user/employee_data.csv' 
OVERWRITE INTO TABLE employees;

-- 插入数据
INSERT INTO TABLE table_name 
VALUES (value1, value2, ...);

-- 从查询结果插入
INSERT [OVERWRITE|INTO] TABLE table_name 
SELECT select_statement FROM from_statement;

3.2 数据查询

-- 基本查询
SELECT [ALL|DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[HAVING having_condition]
[ORDER BY col_list [ASC|DESC]]
[LIMIT number];

-- 示例
SELECT name, salary FROM employees WHERE salary > 5000;

3.3 数据导出

-- 导出到本地
INSERT OVERWRITE LOCAL DIRECTORY '/path/to/local/dir'
SELECT * FROM table_name;

-- 导出到HDFS
INSERT OVERWRITE DIRECTORY '/hdfs/path/to/dir'
SELECT * FROM table_name;

-- 使用多行插入
FROM from_statement
INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)]
SELECT select_statement1
[INSERT OVERWRITE TABLE tablename2 [PARTITION ...] SELECT select_statement2] ...;

4. Hive查询语法

4.1 基础查询

-- 选择特定列
SELECT column1, column2 FROM table_name;

-- 使用别名
SELECT column1 AS alias1, column2 alias2 FROM table_name;

-- 使用表达式
SELECT salary * 1.1 AS new_salary FROM employees;

-- 条件查询
SELECT * FROM employees WHERE department = 'IT' AND salary > 6000;

-- 模糊查询
SELECT * FROM employees WHERE name LIKE 'J%';

-- 范围查询
SELECT * FROM employees WHERE salary BETWEEN 5000 AND 8000;

-- IN查询
SELECT * FROM employees WHERE department IN ('IT', 'HR');

4.2 聚合函数

-- 常用聚合函数
SELECT 
  COUNT(*) AS total_count,
  AVG(salary) AS avg_salary,
  MAX(salary) AS max_salary,
  MIN(salary) AS min_salary,
  SUM(salary) AS total_salary
FROM employees;

-- GROUP BY分组
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

-- HAVING过滤
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 6000;

4.3 JOIN操作

-- 内连接
SELECT e.name, d.department_name
FROM employees e JOIN departments d
ON e.department_id = d.id;

-- 左外连接
SELECT e.name, d.department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.department_id = d.id;

-- 右外连接
SELECT e.name, d.department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.department_id = d.id;

-- 全外连接
SELECT e.name, d.department_name
FROM employees e FULL OUTER JOIN departments d
ON e.department_id = d.id;

-- 交叉连接
SELECT e.name, d.department_name
FROM employees e CROSS JOIN departments d;

4.4 子查询

-- WHERE子句中的子查询
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- FROM子句中的子查询
SELECT dept.avg_salary
FROM (
  SELECT department, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
) dept
WHERE dept.avg_salary > 7000;

-- IN子查询
SELECT name
FROM employees
WHERE department_id IN (
  SELECT id FROM departments WHERE location = 'New York'
);

5. Hive函数

5.1 内置函数

-- 数学函数
SELECT ABS(-10), ROUND(3.1415, 2), CEIL(3.14), FLOOR(3.14);

-- 字符串函数
SELECT 
  CONCAT('Hello', ' ', 'World'),
  SUBSTR('Hello World', 1, 5),
  TRIM('   Hello   '),
  UPPER('hello'),
  LOWER('WORLD');

-- 日期函数
SELECT 
  CURRENT_DATE(),
  YEAR('2023-10-15'),
  MONTH('2023-10-15'),
  DATEDIFF('2023-12-31', '2023-01-01');

-- 条件函数
SELECT 
  IF(salary > 5000, 'High', 'Low') AS salary_level,
  CASE 
    WHEN salary < 3000 THEN 'Low'
    WHEN salary BETWEEN 3000 AND 7000 THEN 'Medium'
    ELSE 'High'
  END AS salary_category
FROM employees;

5.2 窗口函数

-- ROW_NUMBER()
SELECT 
  name, 
  salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_dept
FROM employees;

-- RANK()和DENSE_RANK()
SELECT 
  name,
  salary,
  RANK() OVER (ORDER BY salary DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

-- 聚合窗口函数
SELECT 
  name,
  department,
  salary,
  AVG(salary) OVER (PARTITION BY department) AS avg_dept_salary,
  salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg
FROM employees;

5.3 自定义函数

-- 创建临时函数
CREATE TEMPORARY FUNCTION function_name AS 'class_name';

-- 示例：创建UDF
-- 1. 编写Java类继承UDF
-- 2. 打包为JAR
-- 3. 在Hive中注册
ADD JAR /path/to/udf.jar;
CREATE TEMPORARY FUNCTION my_upper AS 'com.example.MyUpperUDF';

-- 使用UDF
SELECT my_upper(name) FROM employees;

-- 创建永久函数
CREATE FUNCTION database_name.function_name AS 'class_name'
USING JAR 'hdfs:///path/to/jar';

6. Hive分区与分桶

6.1 分区表

-- 创建分区表
CREATE TABLE employee_partitioned (
  id INT,
  name STRING,
  salary FLOAT
)
PARTITIONED BY (department STRING, year INT)
STORED AS ORC;

-- 加载数据到特定分区
LOAD DATA LOCAL INPATH '/path/to/data' 
INTO TABLE employee_partitioned
PARTITION (department='IT', year=2023);

-- 动态分区
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;

INSERT INTO TABLE employee_partitioned
PARTITION (department, year)
SELECT id, name, salary, department, 2023 AS year
FROM employees;

-- 查看分区
SHOW PARTITIONS employee_partitioned;

-- 添加分区
ALTER TABLE employee_partitioned ADD PARTITION (department='HR', year=2023);

-- 删除分区
ALTER TABLE employee_partitioned DROP PARTITION (department='HR', year=2023);

6.2 分桶表

-- 创建分桶表
CREATE TABLE employee_bucketed (
  id INT,
  name STRING,
  salary FLOAT,
  department STRING
)
CLUSTERED BY (department) INTO 4 BUCKETS
STORED AS ORC;

-- 启用分桶
SET hive.enforce.bucketing = true;

-- 插入数据到分桶表
INSERT INTO TABLE employee_bucketed
SELECT * FROM employees;

-- 分桶抽样查询
SELECT * FROM employee_bucketed TABLESAMPLE(BUCKET 1 OUT OF 4 ON department);

7. Hive高级特性

7.1 视图

-- 创建视图
CREATE VIEW [IF NOT EXISTS] view_name [(column_name [COMMENT column_comment], ...)]
[COMMENT view_comment]
[TBLPROPERTIES (property_name=property_value, ...)]
AS SELECT ...;

-- 示例
CREATE VIEW high_paid_employees AS
SELECT name, salary FROM employees WHERE salary > 8000;

-- 查询视图
SELECT * FROM high_paid_employees;

-- 删除视图
DROP VIEW [IF EXISTS] view_name;

7.2 索引

-- 创建索引
CREATE INDEX index_name
ON TABLE table_name (column_name, ...)
AS 'index.handler.class.name'
[WITH DEFERRED REBUILD]
[IDXPROPERTIES (property_name=property_value, ...)]
[IN TABLE index_table_name]
[PARTITIONED BY (column_name, ...)]
[
   [ ROW FORMAT ...] STORED AS ...
   | STORED BY ...
]
[LOCATION hdfs_path]
[TBLPROPERTIES (...)]
[COMMENT "index comment"];

-- 重建索引
ALTER INDEX index_name ON table_name REBUILD;

-- 删除索引
DROP INDEX [IF EXISTS] index_name ON table_name;

7.3 事务

-- 启用事务支持
SET hive.support.concurrency=true;
SET hive.enforce.bucketing=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
SET hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;
SET hive.compactor.initiator.on=true;
SET hive.compactor.worker.threads=1;

-- 创建支持ACID的表
CREATE TABLE transactional_table (
  id INT,
  name STRING
)
CLUSTERED BY (id) INTO 2 BUCKETS
STORED AS ORC
TBLPROPERTIES ('transactional'='true');

-- 事务操作
START TRANSACTION;
INSERT INTO transactional_table VALUES (1, 'Alice');
UPDATE transactional_table SET name = 'Bob' WHERE id = 1;
COMMIT;

8. 总结

本文详细介绍了Hive的各种语法，包括： - 数据定义语言(DDL)：数据库和表的创建、修改、删除 - 数据操作语言(DML)：数据的加载、查询和导出 - 丰富的查询语法：基础查询、聚合函数、JOIN操作和子查询 - 内置函数和自定义函数 - 分区与分桶技术 - 高级特性如视图、索引和事务

Hive的语法与SQL非常相似，使得熟悉SQL的用户能够快速上手。同时，Hive也提供了许多特有的功能来优化大数据处理，如分区、分桶和MapReduce集成等。掌握这些语法对于高效使用Hive进行大数据分析至关重要。

随着Hive的不断发展，新的功能和语法也在不断加入。建议读者定期查阅官方文档以获取最新的语法特性。 “`