总目录://www.greatytc.com/p/e406a9bc93a9
Hadoop - 子目录：//www.greatytc.com/p/9428e443b7fd
什么是DDL
DDL--数据定义语言，用来定义存储数据的概念，例如库，表等。
数据库的增删改查
- 增
create database 数据库名;
增加
hdfs
如果要在建库时判断是否重名就要加if not exists
同时也可以指定库存在的位置：
create database 库名 location '绝对路径';
-
查
show databases;
示例
还有模糊查询show databases like 'hive*';
示例
查询数据库信息
desc databases 库名;
示例
查询数据库详细信息
desc databases extended 库名;
注：因为一个库只有这些信息，所以详细信息和普通信息差不多，在表中可以看出来差别。 -
改
这里的改指的是添加数据库的信息（已有的信息不能修改）
alter database 库名 set dbproperties(key=value);
修改完后，要使用查询详细信息查询。
示例 -
删
drop database hive_1;
示例
可以使用if exists关键字判断这个库是否存在。
drop database if exists hive_1;
不存在也不报错
示例
如果数据库不为空，就需要使用cascade命令。
建个表
两次删除比较
建表语句
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]
（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 允许用户复制现有的表结构，但是不复制数据。
内外部表
-
什么是内部表
管理表，是指hive能够掌握着数据生产周期的表，hive会将其存储在自己的存储目录hive.metastore.warehouse.dir下。hive删除这个表时，会将数据一起删除，所以管理表不适合与其他工具共享数据。
hdfs
stu表中有两个数据块，现在我们把stu表删除。
drop table 表名;
删除
hdfs
hdfs上的stu目录就直接被删掉了，相对应的数据也没了。 -
外部表
外部表就可以理解成是简单外链上的数据，在hive上删除外部表只会删除该表的元数据不会影响原数据。
创建外部表
create external table if not exists 表名(...)...;
创建部门表
创建员工表
然后导入数据
数据源可以去我的公众号hx_bigdata回复1435获得。
导入数据源
查看表类型
desc formatted 表名;
示例 -
类型转换
将外部表转换为内部表
alter table 表名 set tblproperties('EXTERNAL'='FALSE');
示例
将内部表转换为外部表
alter table 表名 set tblproperties('EXTERNAL'='TRUE');
('EXTERNAL'='TRUE')和('EXTERNAL'='FALSE')为固定写法，必须大写。
- 使用场景举例
每天将收集到的网站日志定期流入 HDFS 文本文件。在外部表（原始日志表）的基础上做大量的统计分析，用到的中间表、结果表使用内部表存储，数据通过 SELECT+INSERT进入内部表。
分区表
分区表实际上就是对应一个 HDFS 文件系统上的独立的文件夹，该文件夹下是该分区所有的数据文件。
Hive 中的分区就是分目录，把一个大的数据集根据业务需要分割成小的数据集。
在查询时通过 WHERE 子句中的表达式选择查询所需要的指定的分区，这样的查询效率会提高很多。
分区表的简单实用
创建一个分区表
指定分区字段，然后导入数据时按照分区字段来分。
查看表数据。
查看hdfs上的数据。
类型是d，表示他们是目录。而三个目录的名字是他们各自的分区。
在数据库中的元数据。
数据库中存放了三条记录，他们对应的表ID相同，但是分区ID不同。
分区表的基础操作
-
增加分区
分区不止可以通过导入数据创建，还可以使用语句来创建。
alter table 表名 add partition(分区字段=分区名) ;
示例
hdfs
注：可以同时创建多个分区：
alter table 表名 add partition(分区字段=分区名1) partition(分区字段=分区名2) ; -
删除分区
alter table 表名 drop partition(分区字段=分区名) ;
示例
hdfs
注：可以同时删除多个分区
alter table 表名 drop partition(分区字段=分区名1) partition(分区字段=分区名2) ; -
查看分区数量
show partitions 表名;
分区数量
- 查看表结构
desc formatted 表名;
表结构
分区表的注意事项
-
创建二级分区
不止可以创建二级分区，也可以创建三级，四级等等。
create table 表名(字段名1，字段类型1...) partitioned by (一级分区 一级分区类型，二级分区 二级分区类型...) row format delimited fields terminated by '\t';
示例 -
导入数据到二级分区
load data local inpath '/usr/hive_data/dept.txt' into table default.dept_partition2 partition(month="202004",day="05");
示例
hdfs
在一级分区下，有一个二级分区目录。
- 让分区表与数据产生关联
方式一：上传数据后修复
上传数据
hive (default)> dfs -mkdir -p /user/hive/warehouse/dept_partition2/month=202004/day=03;
hive (default)> dfs -put /usr/hive_data/dept.txt /user/hive/warehouse/dept_partition2/month=202004/day=03;
查询数据（查询不到刚上传的数据）
hive (default)> select * from dept_partition2 where month='202004' and day='03';
执行修复命令
hive (default)> msck repair table dept_partition2;
再次查询数据
hive (default)> select * from dept_partition2 where month='202004' and day='03';
方式二：上传数据后添加分区
上传数据
hive (default)> dfs -mkdir -p /user/hive/warehouse/dept_partition2/month=202004/day=02;
hive (default)> dfs -put /usr/hive_data/dept.txt /user/hive/warehouse/dept_partition2/month=202004/day=02;
执行添加分区
hive (default)> alter table dept_partition2 add partition(month='202004',day='02');
查询数据
hive (default)> select * from dept_partition2 where month='202004' and day='02';
方式三：创建文件夹后 load 数据到分区
创建目录
hive (default)> dfs -mkdir -p /user/hive/warehouse/dept_partition2/month=202004/day=01;
上传数据
hive (default)> load data local inpath '/usr/hive_data/dept.txt' into table dept_partition2 partition(month='202004',day='01');
查询数据
hive (default)> select * from dept_partition2 where month='202004' and day='01';
表的修改
重命名表
alter table 旧表名 rename to 新表名
修改表结构
-
增加一列
alter table 表名 add columns(新增字段名 字段类型);
例子 -
修改一列类型
alter table 表名 change column 字段名 desc 修改后的字段类型;
例子
表的删除
drop table 表名;
