2021-02-03 HiveSQL：DDL和DML

一、DDL
Hive中的DDL和MySQL中基本一致。

1.数据库的操作 数据库操作.png

2.表的操作
2.1创建表
相较于MySQL，Hive没有索引、主键。但是会有各种类型的表，如内部表、外部表、分区表、分桶表等。

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] 表名
[(字段名 类型 COMMENT 字段注释，...)]
[COMMENT  表注释]
[PARTITIONED BY (字段名 类型  COMMENT  字段注释,...)]
[CLUSTERED BY (字段名 类型 COMMENT  字段注释,....)]
[LOCATION HDFS 路径]

具体解释：
--CREATE  TABLE 创建表必选，如果指定的表已存在会报错，可结合IF NOT EXISTS使用避免报错；
--EXTERNAL配合LOCATION使用；
--COMMENT表/字段添加注释；
--PARTITIONED BY创建分区表；
--CLUSTERED BY创建分桶表；
--LOCATION用来指定表在HDFS上的存储位置，如果不指定，会默认放在/user/hive/warehouse下。

2.2内部表
未被EXTERNAL修饰的表是管理表，即内部表。
2.2.1存储路径

hive.metastore.warehouse.dir文件
(默认:/user/hive/warehouse/库名.db/表名)

2.2.2创建内部表：

-- 创建表
create table if not exists student_info(
 student_id int,
 student_name string
);
-- 根据查询结果创建表(MySQL适用)
create table if not exists student_info1 
as select * from student_info;
-- 复制已有表结构,仅复制表结构无数据(MySQL适用)
create table if not exists student_info2 like student_info;

-- 查询表
desc 表名;
-- 查询表(除了表中字段,可以查看表的其他信息(如是不是内部表)(mysql不可用))
desc formatted 表名;

2.2.3删除内部表
删除内部表，会把所有数据全部删除。

2.3外部表
被EXTERNAL修饰的表。
2.3.1存储路径
可通过location自行指定。(不指定的话，会默认在/user/hive/warehouse/数据库名.db/表名，并将属于这个表的数据存放在这里)
2.3.2创建外部表
可以直接创建或复制已有表结构（不能根据查询结果创建）

-- 创建外部表
create external table if not exists class_info1 like class_info;
-- 导入数据
load data [local] inpath 路径 [overwrite] into table 表名;
-- 查询表结构
desc formatted class_info;
-- Table Type:    EXTERNAL_TABLE   

2.3.3删除外部表
删除外部表，仅删除元数据(描述数据属性的数据)，hdfs文件不会被删除。
2.3.4内部表和外部表的转换

-- 将外部表设置为内部表
alter table 表名 set tblproperties('EXTERNAL'='FALSE');
-- 将内部表设置为外部表
alter table 表名 set tblproperties('EXTERNAL'='TRUE');
-- ⚠️：只能使用单引号，并且EXTERNAL这里区分大小写。

2.4 分区表
分区表是指hdfs文件系统上的独立文件夹。其下，是分区表中的所有数据文件。
数据导入：将数据源文件上传到hdfs中并直接放到数据库的表下。该命令必须在linux终端中输入，不能在hive输入。
在linux端导入数据后，需要在hive中修复分区表后，数据才可在hive中查询到。

hdfs dfs -put /root/datas/表名/* /user/hive/warehouse/库.db/表名
msck repair table 表名;  

2.5表操作

-- 修改表名：
alter table 旧表名 rename to 新表名;
-- 删除表：    
drop table 表名;

二、DML的数据导入和导出 DML.png

1.数据导入—load

load data [local] inpath 路径 [overwrite] into table 表名;
-- load data:加载数据
-- local:从本地加载到hive表(复制)，不写是从hdfs中加载(移动/剪切)
-- inpath:数据文件的路径
-- overwrite覆盖已有数据，不写就是在原有数据追加
-- into table:要加载的目标表

2.数据导出—hive shell命令导出

-- 适合简单的查询
bin/hive -e "sql查询语句" >> /root/文件名.txt

-- 适合复杂的查询(使用vi/vim编辑一个sql文件)
bin/hive -f /root/sql文件名.sql >> /root/文件名.txt

finalshell中找到文件之间下载：
root/datas/数据文件，右击下载。