四 DDL数据定义

1 创建数据库

create database [if not exists] 库名 
[comment '库的注释']
[location '库在hdfs上存放的路径']
[with dbproperties(属性名=属性值，...)]

注意： location可以省略，默认存放在/user/hive/warehouse/库名.db目录下

​ dbproperties中只能存放string类型的属性
例如:

create database db_hive2 location '/db_hive.db';

2 删除数据库

drop database [if exists] 库名 [cascade]

删除库时，是两步操作：

​ ①在mysql的DBS表中删除库的元数据

​ ②删除hdfs上库存放的路径

以上操作只能删除空库(库中没有表)！如果库中有表，是无法删除的，如果要强制删除，需要添加cascade关键字.

drop database db_hive cascade;

3 查询数据库

切换库

use 库名

查看库的描述

desc database 库名

查看库的详细描述：

desc database extended  mydb2

查看库中的表

show tables in 库名

查看当前库下的表

show tables

4 修改数据库

只能改location和dbproperties属性！

ALTER DATABASE 库名 SET DBPROPERTIES (property_name=property_value, ...);

​ 在改库的属性时，同名的属性会覆盖，不存在的属性会新增！

5 创建表

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] 
-- 表在hdfs上存储的位置
[LOCATION hdfs_path]
-- 指定表的某些属性
[TBLPROPERTIES]

（1）CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项来忽略这个异常。

（2）EXTERNAL关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION），Hive创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。

（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的简称，目的是用于序列化和反序列化。

（8）STORED AS指定存储文件类型
常用的存储文件类型：SEQUENCEFILE（二进制序列文件）、TEXTFILE（文本）、RCFILE（列式存储格式文件）
如果文件数据是纯文本，可以使用STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCEFILE。

（9）LOCATION ：指定表在HDFS上的存储位置。

（10）LIKE允许用户复制现有的表结构，但是不复制数据。

5.1 管理表和外部表

在创建表时，如果加了EXTERNAL，那么创建的表的类型为外部表！

如果没有指定EXTERNAL,那么创建的表的类型为内部表或管理表！

区别：

①如果当前表是外部表，那么意味指hive是不负责数据生命周期管理的！如果删除了hive中的表，那么只会删除表的schame信息，而不会删除表目录中的数据！

②如果为管理表，意味着hive可以管理数据的生命周期！如果删除了hive中的表，那么不仅会删除mysql中的schame信息，还会删除hdfs上的数据！

通常情况下，在公司创建的都是外部表！

表是廉价的，数据是珍贵的！

5.2 管理表和外部表的转换

可以通过查看表的Table Type属性，来判断表的类型！

在hive中除了属性名和属性值，其他不区分大小写！

MANAGED_TABLE—>EXTERNAL_TABLE

alter table 表名 set TBLPROPERTIES('EXTERNAL'='TRUE')

EXTERNAL_TABLE—>MANAGED_TABLE

alter table 表名 set TBLPROPERTIES('EXTERNAL'='FALSE')

5.3 分区表

5.3.1 作用

​ 分区表的目的是为了分散数据！将表的数据，按照一个指定的分区字段，分散到表目录的子目录中！

​ 作用： 在查询时，可以根据分区字段，过滤数据，减少查询时MR输入的数据量！

5.3.2 注意

​ 一旦一个表创建为了分区表，那么在向表中加载数据时，必须把数据加载到表的最后一级分区目录下！

​ 一旦一个表是分区表，那么会为每个分区目录下的数据自动添加所对应的分区的字段名和字段值！

​ 分区字段和普通的列的字段是不同的！

​ 分区目录的名称必须是分区字段名=分区字段值

5.3.3 创建

5.3.3.1 建表

①创建一个一级分区（只有一个分区字段）表

create table t2(id int,name string,sex string) partitioned by(province string)

②准备数据

1^ATom^Amale
2^AJack^Amale
3^AMarry^Afemale

5.3.3.2put导入

③导入数据

put方式： 只能上传数据，无法对分区表生成元数据！

④手动创建分区

手动创建分区，不仅可以生成分区目录，还会生成分区的元数据

alter table 表名 add partition(分区字段名=分区字段值)

查看表的分区元数据

show partitions 表名

⑤或使用命令自动修复分区的元数据

msck repair table 表名

5.3.3.3 load(load)

load data local inpath '/home/hivedatas/t2.data' into table t2 partition(province='guangxi')

load的方式不仅可以帮我们将数据上传到分区目录，还可以自动生成分区的元数据！

分区的元数据存放在metastore.PARTITIONS表中！

5.3.4删除分区

alter table 表名 drop patition(),patition()

删除分区一定会删除分区的元数据，如果表是管理表，还会删除分区目录！

5.3.5 多级分区表

表是一个分区表，但是有多个分区字段！

create table t3(id int,name string,sex string) partitioned by(province string,city string,area string)

加载数据：

 load data local inpath '/home/hivedatas/t2.data' into table t3 partition(province='guangxi',city='nanning',area='buzhidao')

5.4 分桶表

-- 指定表中的数据在分桶时以什么字段进行分桶操作
[CLUSTERED BY (col_name, col_name, ...) 
-- 表中的数据在分桶时，以什么字段作为排序的字段
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS] 

5.4.1 作用

​ 分桶表的目的也是分散数据，分散数据是为了更好地抽样调查！

​ 分桶和MR中的分区是一个概念！指在向表中使用insert 语句导入数据时， insert语句会翻译为一个MR程序，MR程序在运行时，可以根据分桶的字段，对数据进行分区！

​ 同一种类型的数据，就可以分散到同一个文件中！可以对文件根据类型进行抽样查询！

5.4.2 注意

​ ①如果需要实现分桶，那么必须使用Insert的方式向表中导入数据！只有insert会运行MR！

​ ②分桶的字段是基于表中的已有字段进行选取

​ ③如果要实现分桶操作，那么reduceTask的个数需要>1

5.4.3 案例

①准备数据

1001    ss1
1002    ss2
1003    ss3
1004    ss4
1005    ss5
1006    ss6
1007    ss7
1008    ss8
1009    ss9
1010    ss10
1011    ss11
1012    ss12
1013    ss13
1014    ss14
1015    ss15
1016    ss16

上面的数据复制可能有错,中间是tab键隔离数据.
②创建分桶表

create table stu_buck(id int, name string)
clustered by(id) 
into 4 buckets
row format delimited fields terminated by '\t';

③创建临时表

create table stu_buck_tmp(id int, name string)
row format delimited fields terminated by '\t';

④先把数据load到临时表

load data local inpath '/home/hivedatas/t4.data' into table stu_buck_tmp;

⑤使用insert 语句向分桶表导入数据

​ 导入数据之前，需要打开强制分桶的开关：

set hive.enforce.bucketing=true;

​ 需要让reduceTask的个数=分的桶数，但是此时不需要额外设置！默认reduceTask的个数为-1，-1代表由hive自动根据情况设置reduceTask的数量！

set mapreduce.job.reduces=-1

​ 导入数据

insert overwrite table  stu_buck select * from  stu_buck_tmp

5.4.4 排序

​ 通过指定sorted by来指定分桶后的数据按照什么字段进行排序！

①创建分桶表，指定按照id进行降序排序

create table stu_buck2(id int, name string)
clustered by(id) 
SORTED BY (id desc)
into 4 buckets
row format delimited fields terminated by '\t';

②向表中导入数据

​ 如果需要执行排序，提前打开强制排序开关

set hive.enforce.sorting=true;

​ 导入数据

insert overwrite table  stu_buck2 select * from  stu_buck_tmp

5.4.5 抽样查询

​ 基于分桶表进行抽样查询，表必须是分桶表！

select * from 分桶表 tablesample(bucket x out of y on 分桶字段);

​ 假设当前分桶表，一共分了z桶！

​ x: 代表从当前的第几桶开始抽样

​ 0<x<=y

​ y: z/y 代表一共抽多少桶！

​ y必须是z的因子或倍数(如果不是,抽出来的数据是无规律的)！

​ 怎么抽： 从第x桶开始抽，当y<=z每间隔y桶抽一桶，直到抽满 z/y桶

举例1：

select * from stu_buck2 tablesample(bucket 1 out of 2 on id);

​ 从第1桶开始抽，每间隔2桶抽一桶，一共抽2桶！

​ 桶号： x+y*(n-1) 抽0号桶和2号桶

举例2：

select * from stu_buck2 tablesample(bucket 1 out of 1 on id);

​ 从第1桶开始抽，每间隔1桶抽一桶，一共抽4桶！

​ 抽0,1,2,3号桶

举例3：

select * from stu_buck2 tablesample(bucket 2 out of 8 on id);

​ 从第2桶开始抽，一共抽0.5桶！

​ 抽1号桶的一半

5.5 基于现有表创建表

①基于源表，复制其表结构，创建新表，表中无数据

create table 表名 like 源表名

②基于一条查询语句，根据查询语句中字段的名称，类型和顺序创建新表,表中有数据

create table 表名 as ‘select语句’

注意：不能创建分区表

6.删除

drop table [if exists] 表名

清空表中的数据（表必须是管理表）

truncate table 表名

7.查询

查看表的描述

desc  表名

查看表的详细描述

desc extended 表名

格式化表的详细描述

desc formatted 表名

查看表的建表语句

show create table 表名

8.修改

8.1 修改表的某个属性

alter table 表名 set TBLPROPERTIES('属性名'='属性值')

8.2 修改列的信息

ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]

8.3 重命名表

ALTER TABLE table_name RENAME TO new_table_name

8.4 重置表的所有列

ALTER TABLE table_name REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...) 

8.5 添加列

ALTER TABLE table_name ADD COLUMNS (col_name data_type [COMMENT col_comment], ...)