Hive 的基本语法介绍

一、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, ...)];  -- 一些额外信息
  1
  2
  3
  4

• 案例：

  create database db_hive2 location '/db_hive2.db';
  1

1.2 查询数据库

• 显示所有数据库：

  show databases;
  1

• 过滤查询数据库：

  show databases like 'hive*';
  1

• 显示数据库信息：

  desc database db_name;
  1

• 显示数据库详细信息：

  desc database extended db_name;
  1

• 切换数据库：

  use db_name;
  1

1.3 修改数据库

alter database db_hive set dbproperties(property1=value1,...);
1

1.4 删除数据库

• 删除空数据库

  drop database [if exists] db_name;
  1

• 删除非空数据库

  drop database db_name [if exists] cascade;
  1

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]
1
2
3
4
5
6
7
8
9
10
11

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';
  1
  2
  3
  4
  5
  6
  7

• 查看表类型

  desc formatted student;
  Table Type: MANAGED_TABLE -- 管理表
  1
  2

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';
  1
  2
  3
  4
  5
  6

• 查看表类型：

  desc formatted dept;
  Table Type: EXTERNAL_TABLE -- 外部表
  1
  2

2.1.5 内外表转换

-- 内部表转外部表
alter table student set tblproperties('EXTERNAL'='TRUE');

-- 外部表转内部表
alter table dept set tblproperties('EXTERNAL'='FALSE');
1
2
3
4
5

注意：(‘EXTERNAL’=‘TRUE’) 和 (‘EXTERNAL’=‘FALSE’) 为固定写法，区分大小写

2.2 修改表

2.2.1 重命名

ALTER TABLE table_name RENAME TO new_table_name -- new_table_name 必须不存在
1

2.2.2 修改列

修改的是表的元数据

• 增加列：代表新增字段，字段位置在所有列后面(partition 列前)

  ALTER TABLE table_name ADD COLUMNS (col_name data_type [COMMENT col_comment], ...);
  
  alter table dept add columns(deptdesc string);
  1
  2
  3

• 替换列：表示替换表中所有字段

  ALTER TABLE table_name REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...);
  
  alter table dept replace columns(deptno string, dname string, loc string);
  
  1
  2
  3
  4

• 更新列 (列名及类型)

  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;
  1
  2
  3

2.3 删除表

drop table tbl_name;  -- 内外表不同
1

二、DML 语法

1. 数据导入

1.1 Load 导入

1.1.1 语法

load data [local] inpath '数据的path' [overwrite] into table tbl_name [partition (partcol1=val1,…)];
1

• 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';
  1
  2
  3
  4
  5
  6

• 加载数据文件到表

  • 本地文件：复制操作

    load data local inpath '/opt/module/hive/datas/student.txt' into table student;
    1

  • HDFS 文件：剪切操作

    load data inpath 'hdfs:///user/myuser/hive/student.txt' into table student;
    
    1
    2

• 覆盖加载数据文件

  load data inpath 'hdfs:///user/myuser/hive/student.txt' overwrite into table student;
  
  1
  2

1.2 Insert 插入

1.2.1 基本插入

insert into table student values('1','wangwu'),('2','zhaoliu');
1

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
4
5
6
7

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
2
3
4
5
6

1.3 As Select 插入

根据查询结果创建表

create table if not exists student3
as select id, name from student;
1
2

1.4 指定 Location 导入

建表时指定加载数据路径导入，一般建外部表时使用

create external table if not exists student4 
(
	id int, 
    name string
)
row format delimited fields terminated by '\t'
location '/student4';
1
2
3
4
5
6
7

1.5 Import 导入

需要先用 export 导出后，再将数据导入，一般使用不多

-- 导入的表必须不能存在或为空表
import table student2 from 'hdfs:///user/hive/warehouse/export/student';
1
2

2. 数据导出

2.1 Insert 导出

• 将查询的结果导出到本地

  insert overwrite local directory 
  '/opt/module/hive/data/export/student'
  select * from student;
  
  1
  2
  3
  4

• 将查询的结果格式化导出到本地

  insert overwrite local directory 
  '/opt/module/hive/data/export/student1'
  ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
  select * from student;
  1
  2
  3
  4

• 将查询的结果导出到 HDFS 上

  insert overwrite directory 
  'hdfs:///user/atguigu/student2'
  ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' 
  select * from student;
  1
  2
  3
  4

2.2 Hadoop 命令导出

hadoop fs -get path1 path2
dfs -get path1 path2
1
2

2.3 Hive 命令导出

# 语法
hive -f/-e  执行语句或者脚本  > file

# 实例
hive -e 'select * from student;' > /opt/module/hive/data/export/student4.txt;
1
2
3
4
5

2.4 Export 导出

export 和 import 主要用于两个 Hadoop 平台集群之间 Hive 表迁移

export table tbl_name to 'path';
1

3. 数据清除

Truncate 只能删除管理表的 HDFS 数据 (保留目录和元数据)，不能删除外部表的数据

truncate table tbl_name;
1