HiveQL是Hive查询语言，它可能和mysql最相近，但是也会存在差异。本文会从数据库的操作和表的操作来说明。
文章目录
一、Database
1.1 创建数据库
1.2 查询数据库
1.2.1 查询数据库列表
1.2.2 过滤查询数据库
1.2.3 查询数据库信息
1.2.4 使用数据库
1.3 修改数据库
1.4 删除数据库
二、创建表
2.1 建表语法
2.2 字段详细说明
2.3 管理表
2.3.1 理论
2.3.2 实例
2.4 外部表
2.4.1 理论
2.4.2 实例
2.5 内部表和外部表相互转化
2.6 分区表
2.7 分桶表
2.8 倾斜表
2.9 临时表
2.10 CTAS方式创建表
2.11 复制表结构
2.12 加载数据到表
三、修改表
3.1 重命名表
3.2 修改列
3.3 新增替换列
四、清空表和删除表
4.1 清空表
4.2 删除表
一、Database
1.1 创建数据库
语法：
CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name --DATABASE|SCHEMA 是等价的
[COMMENT database_comment] --数据库注释
[LOCATION hdfs_path] --存储在 HDFS 上的位置
[WITH DBPROPERTIES (property_name=property_value, ...)]; --指定额外属性
示例：
a)、创建一个数据库，数据库在 HDFS 上的默认存储路径是/user/hive/warehouse/*.db。注：这个路径可以在配置文件中修改。
hive> CREATE DATABASE IF NOT EXISTS hive_test
> COMMENT 'hive database for test'
> WITH DBPROPERTIES ('create'='niuniu');
我们在浏览器上查询可以看到成功新建了hive_test数据库。
b)、如果数据库已经存在，可能会创建失败，这里我们使用“IF NOT EXITSTS”来避免报错。
hive> create database if not exists hive_test;
c)、创建一个数据库，我们可以修改数据库在 HDFS 上的默认存储路径
hive> CREATE DATABASE IF NOT EXISTS hive_test01 location '/mytest';
1.2 查询数据库
1.2.1 查询数据库列表
hive> show databases;
1.2.2 过滤查询数据库
如果数据非常多的话，我们可以利用正则匹配来筛选出我们需要的数据库数据库，比如筛选出hive开头的数据库。
hive> show databases like 'hive_*';
1.2.3 查询数据库信息
可以通过describe命令查看数据库信息，比如存放路径等，加上extended会显示更加详细的信息。
hive> describe database hive_test;
hive> describe database extended hive_test;
1.2.4 使用数据库
hive> use hive_test;
1.3 修改数据库
用户可以使用 ALTER DATABASE 命令为某个数据库的 DBPROPERTIES 设置键-值对
属性值，来描述这个数据库的属性信息。 数据库的其他元数据信息都是不可更改的，包括数
据库名和数据库所在的目录位置。
我们发现成功的修改了创建时间
1.4 删除数据库
语法：
DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];
其中IF EXISTS 判断数据库是否存在
默认行为是 RESTRICT，如果数据库中存在表则删除失败。要想删除库及其中的表，可以使用 CASCADE 级联删除。
hive> DROP DATABASE IF EXISTS hive_test01 CASCADE;
二、创建表
2.1 建表语法
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name --表名
[(col_name data_type [COMMENT col_comment],
... [constraint_specification])] --列名 列数据类型
[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
] --分桶表分桶规则
[SKEWED BY (col_name, col_name, ...) ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)
[STORED AS DIRECTORIES]
] --指定倾斜列和值
[
[ROW FORMAT row_format]
[STORED AS file_format]
| STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]
] -- 指定行分隔符、存储文件格式或采用自定义存储格式
[LOCATION hdfs_path] -- 指定表的存储位置
[TBLPROPERTIES (property_name=property_value, ...)] --指定表的属性
[AS select_statement]; --从查询结果创建表
2.2 字段详细说明
（1）CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项来忽略这个异常。
（2）EXTERNAL关键字可以让用户创建一个外部表，在建表的同时可以指定一个指向实际数据的路径（LOCATION），在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。
（3）COMMENT：为表和列添加注释。
（4）PARTITIONED BY创建分区表
（5）CLUSTERED BY创建分桶表
（6）SORTED BY不常用，对桶中的一个或多个列另外排序
（7）ROW FORMAT
DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char]
[MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
| SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]
用户在建表的时候可以自定义SerDe或者使用自带的SerDe。如果没有指定ROW FORMAT 或者ROW FORMAT DELIMITED，将会使用自带的SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的SerDe，Hive通过SerDe确定表的具体的列的数据。
SerDe是Serialize/Deserilize的简称， hive使用Serde进行行对象的序列与反序列化。
（8）STORED AS指定存储文件类型
常用的存储文件类型：SEQUENCEFILE（二进制序列文件）、TEXTFILE（文本）、RCFILE（列式存储格式文件）
如果文件数据是纯文本，可以使用STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCEFILE。
（9）LOCATION ：指定表在HDFS上的存储位置。
（10）AS：后跟查询语句，根据查询结果创建表。
（11）LIKE允许用户复制现有的表结构，但是不复制数据。
2.3 管理表
2.3.1 理论
我们目前直接创建的表都是所谓的管理表，有时也被称为内部表。因为这种表，Hive会（或多或少地）控制着数据的生命周期。Hive默认情况下会将这些表的数据存储在由配置项hive.metastore.warehouse.dir(例如，/user/hive/warehouse)所定义的目录的子目录下。 当我们删除一个管理表时，Hive也会删除这个表中数据。管理表不适合和其他工具共享数据。
2.3.2 实例
管理表（内部表）的创建
hive> CREATE TABLE emp(
> empno INT,
> ename STRING,
> job STRING,
> mgr INT,
> hiredate TIMESTAMP,
> sal DECIMAL(7,2),
> comm DECIMAL(7,2),
> deptno INT)
> ROW FORMAT DELIMITED FIELDS TERMINATED BY "t";
2.4 外部表
2.4.1 理论
因为表是外部表，所以 Hive 并非认为其完全拥有这份数据。 删除该表并不会删除掉这
份数据，不过描述表的元数据信息会被删除掉。
2.4.2 实例
外部表的创建
hive> CREATE EXTERNAL TABLE emp_external(
> empno INT,
> ename STRING,
> job STRING,
> mgr INT,
> hiredate TIMESTAMP,
> sal DECIMAL(7,2),
> comm DECIMAL(7,2),
> deptno INT)
> ROW FORMAT DELIMITED FIELDS TERMINATED BY "t"
> LOCATION '/hive/emp_external';
注；可以通过desc formmated emp_external 查看格式化表的详细数据:
hive> desc formatted emp_external;
2.5 内部表和外部表相互转化
hive> alter table emp_external set tblproperties('EXTERNAL'='FALSE');
通过设置属性成功让外部表转化成内部表，同理，也可以让内部表转换成外部表。
2.6 分区表
分区表实际上就是对应一个HDFS文件系统上的独立的文件夹，该文件夹下是该分区所有的数据文件。Hive中的分区就是分目录，把一个大的数据集根据业务需要分割成小的数据集。在查询时通过WHERE子句中的表达式选择查询所需要的指定的分区，这样的查询效率会提高很多。
分区表的创建
hive> CREATE EXTERNAL TABLE emp_partition(
> empno INT,
> ename STRING,
> job STRING,
> mgr INT,
> hiredate TIMESTAMP,
> sal DECIMAL(7,2),
> comm DECIMAL(7,2)
> )
> PARTITIONED BY (deptno INT) -- 按照部门编号进行分区
> ROW FORMAT DELIMITED FIELDS TERMINATED BY "t"
> LOCATION '/hive/emp_partition';
2.7 分桶表
分区提供一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可形成合理的分区。对于一张表或者分区，Hive 可以进一步组织成桶，也就是更为细粒度的数据范围划分。
- 分桶是将数据集分解成更容易管理的若干部分的另一个技术。
- 分区针对的是数据的存储路径；分桶针对的是数据文件。
分桶表的创建
hive> CREATE EXTERNAL TABLE emp_bucket(
> empno INT,
> ename STRING,
> job STRING,
> mgr INT,
> hiredate TIMESTAMP,
> sal DECIMAL(7,2),
> comm DECIMAL(7,2),
> deptno INT)
> CLUSTERED BY(empno) SORTED BY(empno ASC) INTO 4 BUCKETS --按照员工编号散列到四个 bucket 中
> ROW FORMAT DELIMITED FIELDS TERMINATED BY "t"
> LOCATION '/hive/emp_bucket';
2.8 倾斜表
通过指定一个或者多个列经常出现的值（严重偏斜），Hive 会自动将涉及到这些值的数据拆分为单独的文件。在查询时，如果涉及到倾斜值，它就直接从独立文件中获取数据，而不是扫描所有文件，这使得性能得到提升。
倾斜表的创建
hive> CREATE EXTERNAL TABLE emp_skewed(
> empno INT,
> ename STRING,
> job STRING,
> mgr INT,
> hiredate TIMESTAMP,
> sal DECIMAL(7,2),
> comm DECIMAL(7,2)
> )
> SKEWED BY (empno) ON (66,88,100) --指定 empno 的倾斜值 66,88,100
> ROW FORMAT DELIMITED FIELDS TERMINATED BY "t"
> LOCATION '/hive/emp_skewed';
2.9 临时表
临时表仅对当前 session 可见，临时表的数据将存储在用户的暂存目录中，并在会话结束后删除。如果临时表与永久表表名相同，则对该表名的任何引用都将解析为临时表，而不是永久表。临时表还具有以下两个限制：
- 不支持分区列；
- 不支持创建索引。
临时表的创建
hive> CREATE TEMPORARY TABLE emp_temp(
> empno INT,
> ename STRING,
> job STRING,
> mgr INT,
> hiredate TIMESTAMP,
> sal DECIMAL(7,2),
> comm DECIMAL(7,2)
> )
> ROW FORMAT DELIMITED FIELDS TERMINATED BY "t";
2.10 CTAS方式创建表
CTAS是通过查询，然后根据查询的结果来建立表格的一种方式。显然通过这种方式可以省去定义表结构的方法，而且在创建表的同时导入数据。
CREATE TABLE emp_copy AS SELECT * FROM emp WHERE deptno='20';
2.11 复制表结构
语法：
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name --创建表表名
LIKE existing_table_or_view_name --被复制表的表名
[LOCATION hdfs_path]; --存储位置
示例：
CREATE TEMPORARY EXTERNAL TABLE IF NOT EXISTS emp_co LIKE emp
2.12 加载数据到表
加载数据到表中属于 DML 操作，这里为了方便大家测试，先简单介绍一下加载本地数据到表中：
-- 加载数据到 emp 表中
load data local inpath "/usr/file/emp.txt" into table emp;
其中 emp.txt 的内容如下，你可以直接复制使用
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800.00 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975.00 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850.00 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450.00 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 1500.00 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000.00 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100.00 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950.00 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000.00 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300.00 10
加载成功后，我们进行相应的查询
三、修改表
3.1 重命名表
语法：
ALTER TABLE table_name RENAME TO new_table_name;
示例：
ALTER TABLE emp_temp RENAME TO new_emp; --把 emp_temp 表重命名为 new_emp
3.2 修改列
语法：
ALTER TABLE table_name [PARTITION partition_spec] CHANGE [COLUMN] col_old_name col_new_name column_type
[COMMENT col_comment] [FIRST|AFTER column_name] [CASCADE|RESTRICT];
示例：
-- 修改字段名和类型
ALTER TABLE emp_temp CHANGE empno empno_new INT;
-- 修改字段 sal 的名称 并将其放置到 empno 字段后
ALTER TABLE emp_temp CHANGE sal sal_new decimal(7,2) AFTER ename;
-- 为字段增加注释
ALTER TABLE emp_temp CHANGE mgr mgr_new INT COMMENT 'this is column mgr';
3.3 新增替换列
示例：
ALTER TABLE emp_temp ADD|REPLACE COLUMNS (address STRING COMMENT 'home address');
注：ADD是代表新增一字段，字段位置在所有列后面(partition列前)，REPLACE则是表示替换表中所有字段。
四、清空表和删除表
4.1 清空表
语法：
-- 清空整个表或表指定分区中的数据
TRUNCATE TABLE table_name [PARTITION (partition_column = partition_col_value, ...)];
- 目前只有内部表才能执行 TRUNCATE 操作，外部表执行时会抛出异常
Cannot truncate non-managed table XXXX。
示例：
TRUNCATE TABLE emp_mgt_ptn PARTITION (deptno=20);
4.2 删除表
语法：
DROP TABLE [IF EXISTS] table_name [PURGE];
- 内部表：不仅会删除表的元数据，同时会删除 HDFS 上的数据；
- 外部表：只会删除表的元数据，不会删除 HDFS 上的数据；
- 删除视图引用的表时，不会给出警告（但视图已经无效了，必须由用户删除或重新创建）。
