一、DDL 语法
1. 数据库 DDL
1.1 创建数据库
- 语法：
CREATE DATABASE [IF NOT EXISTS] database_name
[COMMENT database_comment] -- 注释
[LOCATION hdfs_path] -- 指定库在HDFS的路径
[WITH DBPROPERTIES (property_name=property_value, ...)]; -- 一些额外信息- 案例：
create database db_hive2 location '/db_hive2.db';1.2 查询数据库
- 显示所有数据库：
show databases;- 过滤查询数据库：
show databases like 'hive*';- 显示数据库信息：
desc database db_name;- 显示数据库详细信息：
desc database extended db_name;- 切换数据库：
use db_name;1.3 修改数据库
alter database db_hive set dbproperties(property1=value1,...);1.4 删除数据库
- 删除空数据库
drop database [if exists] db_name;- 删除非空数据库
drop database db_name [if exists] cascade;2. 数据表 DDL
2.1 创建表
2.1.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]
[TBLPROPERTIES (property_name=property_value, ...)]
[AS select_statement]2.1.2 语法说明
EXTERNAL：表示创建一个外部表，在建表的同时可以指定一个指向实际数据的路径（LOCATION）， 在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除实际数据COMMENT：为表和列添加注释PARTITIONED BY：创建分区表CLUSTERED BY：创建分桶表SORTED BY：不常用，对桶中的一个或多个列另外排序ROW FORMAT DELIMITED：指定行的格式
-
FIELDS TERMINATED BY char：指定每行字段之间的分隔符 -
COLLECTION ITEMS TERMINATED BY char：指定字段值为集合时元素之间的分隔符 -
MAP KEYS TERMINATED BY char：指定 Map 里 Key 和 value 间的分隔符 -
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 进行行对象的序列与反序列化STORED AS：指定存储文件类型
- 常用的存储文件类型： SEQUENCEFILE （二进制序列文件）、 TEXTFILE （文本）、 RCFILE （列式存储格式文件）
- 如果文件数据是纯文本，可以使用 STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCEFILE
- 可以自定义 stored as 的类型，其底层就是自定义 InputFormat 和 OutputFormat
LOCATION：指定表在 HDFS 上的存储位置AS：后跟查询语句，根据查询结果创建表LIKE：允许用户复制现有的表结构，但是不复制数据
2.1.3 内部表
又称为管理表，当执行 drop 删除一个管理表时，这个表的元数据和 HDFS 上的实际目录和数据都会被删除，因此管理表不适合和其他工具共享数据
- 创建：
create table if not exists student(
id int,
name string
)
row format delimited fields terminated by '\t'
stored as textfile
location '/user/hive/warehouse/student';- 查看表类型
desc formatted student;
Table Type: MANAGED_TABLE -- 管理表2.1.4 外部表
当执行 drop 删除一个外部表时，只是删除这个表的元数据， HDFS 上的实际数据会保留
- 创建：
create external table if not exists dept(
deptno int,
dname string,
loc int
)
row format delimited fields terminated by '\t';- 查看表类型：
desc formatted dept;
Table Type: EXTERNAL_TABLE -- 外部表2.1.5 内外表转换
-- 内部表转外部表
alter table student set tblproperties('EXTERNAL'='TRUE');
-- 外部表转内部表
alter table dept set tblproperties('EXTERNAL'='FALSE');注意：(‘EXTERNAL’=‘TRUE’) 和 (‘EXTERNAL’=‘FALSE’) 为固定写法，区分大小写
2.2 修改表
2.2.1 重命名
ALTER TABLE table_name RENAME TO new_table_name -- new_table_name 必须不存在2.2.2 修改列
修改的是表的元数据
- 增加列：代表新增字段，字段位置在所有列后面(partition 列前)
ALTER TABLE table_name ADD COLUMNS (col_name data_type [COMMENT col_comment], ...);
alter table dept add columns(deptdesc string);- 替换列：表示替换表中所有字段
ALTER TABLE table_name REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...);
alter table dept replace columns(deptno string, dname string, loc string);- 更新列 (列名及类型)
ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name];
alter table dept change deptdesc dept string;2.3 删除表
drop table tbl_name; -- 内外表不同二、DML 语法
1. 数据导入
1.1 Load 导入
1.1.1 语法
load data [local] inpath '数据的path' [overwrite] into table tbl_name [partition (partcol1=val1,…)];-
load data：表示加载数据 -
local：表示从本地加载数据到 hive 表；否则从 HDFS 加载数据到 hive 表 -
inpath：表示加载数据的路径 -
overwrite：表示覆盖表中已有数据，否则表示添加 (非追加) -
into table：表示加载到哪张表 -
partition：表示上传到指定分区
1.1.2 案例
- 创建一张表
create table student
(
id string,
name string
)
row format delimited fields terminated by '\t';- 加载数据文件到表
- 本地文件：复制操作
load data local inpath '/opt/module/hive/datas/student.txt' into table student;- HDFS 文件：剪切操作
load data inpath 'hdfs:///user/myuser/hive/student.txt' into table student;- 覆盖加载数据文件
load data inpath 'hdfs:///user/myuser/hive/student.txt' overwrite into table student;1.2 Insert 插入
1.2.1 基本插入
insert into table student values('1','wangwu'),('2','zhaoliu');1.2.2 模式插入
-- insert into：以追加数据的方式插入到表或分区，原有数据不会删除
insert into table student
select id, name from student;
-- insert overwrite：会覆盖表中已存在的数据(本质是先查询出结果然后删除原表中数据再插入)
insert overwrite table student
select id, name from student; -- 查询自己插入自己1.2.3 多表（多分区）插入模式
--实现一次读取多次插入，减少 MR job 个数
from student
insert overwrite table student partition(month='201707')
select id, name where month='201709'
insert overwrite table student partition(month='201706')
select id, name where month='201709';1.3 As Select 插入
根据查询结果创建表
create table if not exists student3
as select id, name from student;1.4 指定 Location 导入
建表时指定加载数据路径导入，一般建外部表时使用
create external table if not exists student4
(
id int,
name string
)
row format delimited fields terminated by '\t'
location '/student4';1.5 Import 导入
需要先用 export 导出后，再将数据导入，一般使用不多
-- 导入的表必须不能存在或为空表
import table student2 from 'hdfs:///user/hive/warehouse/export/student';2. 数据导出
2.1 Insert 导出
- 将查询的结果导出到本地
insert overwrite local directory
'/opt/module/hive/data/export/student'
select * from student;- 将查询的结果格式化导出到本地
insert overwrite local directory
'/opt/module/hive/data/export/student1'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
select * from student;- 将查询的结果导出到 HDFS 上
insert overwrite directory
'hdfs:///user/atguigu/student2'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
select * from student;2.2 Hadoop 命令导出
hadoop fs -get path1 path2
dfs -get path1 path22.3 Hive 命令导出
# 语法
hive -f/-e 执行语句或者脚本 > file
# 实例
hive -e 'select * from student;' > /opt/module/hive/data/export/student4.txt;2.4 Export 导出
export 和 import 主要用于两个 Hadoop 平台集群之间 Hive 表迁移
export table tbl_name to 'path';3. 数据清除
Truncate 只能删除管理表的 HDFS 数据 (保留目录和元数据)，不能删除外部表的数据
truncate table tbl_name;
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
