6-Apache Hive SQL-DDL、DML

hadoop离线day06-Apache Hive SQL-DDL、DML

今日课程学习目标

#掌握HQL DDL建表语句
	理解Hive SerDe机制、分隔符语法
	掌握内外部表、分区表、分桶表创建使用
#理解HQL DDL其他语句
	修改、删除
#学会常见show语法使用
#掌握HQL load、insert加载插入语句

今日课程内容大纲

#1、HQL DDL 数据定义语言 针对表的
 核心：建表语句   直接决定了表和文件之间能否映射成功
 	数据类型
 	SerDe序列化机制
 	分隔符语法
 	内部表、外部表
 	数据存储路径
 	分区表
 	分桶表
  alter修改表

#2、HQL DML 数据操纵语言  针对表的数据
	load加载数据
	insert插入数据   insert+select
		多重插入
		动态分区插入
		数据导出
		
#3、HQL常用的show语法

知识点01：Apache Hive--DDL--概念与语法树介绍

![image-20210921211129630](/img/user/czc知识库/计算机/Hadoop技术栈/课程资料笔记/源/hadoop离线day06--Apache Hive SQL-DDL、DML/1、笔记、总结/hadoop离线day06-Apache Hive SQL-DDL、DML.assets/image-20210921211129630.png)

蓝色字体是建表语法的关键字，用于指定某些功能。
[ ]中括号的语法表示可选。
|表示使用的时候，左右语法二选一。
建表语句中的语法顺序要和语法树中顺序保持一致。

知识点02：Apache Hive--DDL--建表语句--表存在忽略异常

IF NOT EXISTS

知识点03：Apache Hive--DDL--建表语句--数据类型

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Types

知识点04：Apache Hive--DDL--建表语句--SerDe机制、分隔符指定语法

知识点05：Apache Hive--DDL--建表语句--默认分隔符

知识点06：Apache Hive--DDL--建表语句--内部表、外部表

--创建内部表
create table student_inner(Sno int,Sname string,Sex string,Sage int,Sdept string) row format delimited fields terminated by ',';

--创建外部表 关键字external
create external table student_external(Sno int,Sname string,Sex string,Sage int,Sdept string) row format delimited fields terminated by ',';

--上传文件到内部表、外部表中
hadoop fs -put students.txt /user/hive/warehouse/itheima.db/student_inner
hadoop fs -put students.txt /user/hive/warehouse/itheima.db/student_external

--好像没啥区别 都能映射成功 数据也都在HDFS上

--针对内部表、外部表 进行drop删除操作
drop table student_inner;  --内部表在删除的时候 元数据和数据都会被删除
drop table student_external; --外部表在删除的时候 只删除元数据  而HDFS上的数据文件不会动

知识点07：Apache Hive--DDL--建表语句--location存储位置

存储路径由hive.metastore.warehouse.dir 属性指定。默认值是：/user/hive/warehouse

知识点08：Apache Hive--DDL--建表语句--分区表创建、静态数据加载、分区裁剪

create table t_all_hero(
   id int,
   name string,
   hp_max int,
   mp_max int,
   attack_max int,
   defense_max int,
   attack_range string,
   role_main string,
   role_assist string
)
row format delimited
fields terminated by "\t";

--上传文件
hadoop fs -put archer.txt assassin.txt mage.txt support.txt tank.txt warrior.txt /user/hive/warehouse/itheima.db/t_all_hero

select * from t_all_hero;


--查询role_main主要定位是射手并且hp_max最大生命大于6000的有几个

select count(*) from t_all_hero where role_main="archer" and hp_max >6000;

--思考：上述查询sql底层应该如何去查询数据？ 要不要全表扫描？

--问题：要进行过滤 就需要全表扫描 不全表扫描呢 又得不出正确结果？性能如何优化呢？
--优化要求  如何才能够减少全表扫描 而结果又正确。

--经过大脑分析 我们认为应该根据角色主定位进行分区 所以分区的字段就是role_main
create table t_all_hero_part(
   id int,
   name string,
   hp_max int,
   mp_max int,
   attack_max int,
   defense_max int,
   attack_range string,
   role_main string,
   role_assist string
)
partitioned by(role_main string)
row format delimited
fields terminated by "\t";



--错误说 分区字段重复了 好家伙
Error: Error while compiling statement: FAILED: SemanticException [Error 10035]: Column repeated in partitioning columns (state=42000,code=10035)


--分区表建表 
create table t_all_hero_part(
   id int,
   name string,
   hp_max int,
   mp_max int,
   attack_max int,
   defense_max int,
   attack_range string,
   role_main string,
   role_assist string
) partitioned by (juesedingwei string)--注意哦 这里是分区字段
row format delimited
fields terminated by "\t";

--查询分区表 发现分区字段也显示出来了
select * from t_all_hero_part;

--静态加载分区表数据
load data local inpath '/root/hivedata/archer.txt' into table t_all_hero_part partition(juesedingwei='sheshou');

load data local inpath '/root/hivedata/assassin.txt' into table t_all_hero_part partition(juesedingwei='cike');
load data local inpath '/root/hivedata/mage.txt' into table t_all_hero_part partition(juesedingwei='fashi');
load data local inpath '/root/hivedata/support.txt' into table t_all_hero_part partition(juesedingwei='fuzhu');
load data local inpath '/root/hivedata/tank.txt' into table t_all_hero_part partition(juesedingwei='tanke');
load data local inpath '/root/hivedata/warrior.txt' into table t_all_hero_part partition(juesedingwei='zhanshi');

--查询一下验证是否加载成功
select * from t_all_hero_part;

load data local inpath '/root/hivedata/warrior.txt' into table t_all_hero_part partition(juesedingwei='666');

思考：如果分区很多 一个一个加载，效率如何？

因为静态分区的时候 分区值是用户手动写死的 有写错的风险。

知识点09：Apache Hive--DDL--建表语句--动态分区插入数据

--非分区表 全表扫描过滤查询
select count(*) from t_all_hero where role_main="archer" and hp_max >6000;

--分区表 先基于分区过滤 再查询
select count(*) from t_all_hero_part where juesedingwei="sheshou" and hp_max >6000;

知识点10：Apache Hive--DDL--建表语句--多重分区及分区表注意事项

分区表注意事项

/user/hive/warehouse/数据库.db/表
/分区字段=分区值1
xxxx.txt
/分区字段=分区值2
zzzz.txt


- 分区表是一种优化表，建表的时候可以不使用，但是，当==创建分区表之后，使用分区字段查询可以减少全表扫描，提高查询的效率==。

- 企业中常用的分区字段

- 地域维度：省，市
- 时间维度：day,month,year

----

多重分区表

- 分区表支持基于多个字段进行分区

partitioned by(字段1 ，字段2....)


- 多个分区之间是一种==递进关系==，可以理解为在==前一个分区的基础上继续分区==；从底层来说就是文件夹下面继续划分子文件夹；

- ==常见的多分区就是2个分区==；

```sql
--以国家、省创建分区表
create table t_user_double_p(id int,name string,country string) partitioned by(guojia string,sheng string) row format delimited fields terminated by  ',';

--加载数据到多分区表中
load data local inpath '/root/hivedata/china_sh.txt'  into table t_user_double_p partition(guojia="zhongguo",sheng="shanghai");

load data local inpath '/root/hivedata/china_sz.txt'  into table t_user_double_p partition(guojia="zhongguo",sheng="shenzhen");

load data local inpath '/root/hivedata/usa_dezhou.txt'  into table t_user_double_p partition(guojia="meiguo",sheng="dezhou");

--查询来自于中国深圳的用户有哪些？
select * from t_user_double_p where guojia="zhongguo"and sheng="shenzhen";

知识点11：Apache Hive--DDL--建表语句--分桶表语法、创建、加载

CLUSTERED BY xxx INTO N BUCKETS
--根据xxx字段把数据分成N桶
--根据表中的字段把数据文件成为N个部分

t_user(id int,name string);
--1、根据谁分？
 CLUSTERED BY xxx ；  xxx必须是表中的字段
--2、分成几桶？
 N BUCKETS   ；N的值就是分桶的个数
--3、分桶的规则？
clustered by id into 3 bucket

hashfunc(分桶字段)  %  N bucket  余数相同的来到同一个桶中
1、如果分桶的字段是数字类型的字段，hashfunc(分桶字段)=分桶字段本身
2、如果分桶的字段是字符串或者其他字段，hashfunc(分桶字段) = 分桶字段.hashcode

CREATE TABLE itheima.t_usa_covid19_bucket(
      count_date string,
      county string,
      state string,
      fips int,
      cases int,
      deaths int)
CLUSTERED BY(state) INTO 5 BUCKETS; --分桶的字段一定要是表中已经存在的字段

--根据state州分为5桶 每个桶内根据cases确诊病例数倒序排序
CREATE TABLE itheima.t_usa_covid19_bucket_sort(
     count_date string,
     county string,
     state string,
     fips int,
     cases int,
     deaths int)
CLUSTERED BY(state)
sorted by (cases desc) INTO 5 BUCKETS;--指定每个分桶内部根据 cases倒序排序

--step1:开启分桶的功能 从Hive2.0开始不再需要设置
set hive.enforce.bucketing=true;

--step2:把源数据加载到普通hive表中
CREATE TABLE itheima.t_usa_covid19(
       count_date string,
       county string,
       state string,
       fips int,
       cases int,
       deaths int)
row format delimited fields terminated by ",";

--将源数据上传到HDFS，t_usa_covid19表对应的路径下
hadoop fs -put us-covid19-counties.dat /user/hive/warehouse/itheima.db/t_usa_covid19

--step3:使用insert+select语法将数据加载到分桶表中
insert into t_usa_covid19_bucket select * from t_usa_covid19;

select * from t_usa_covid19_bucket limit 10;

--基于分桶字段state查询来自于New York州的数据
--不再需要进行全表扫描过滤
--根据分桶的规则hash_function(New York) mod 5计算出分桶编号
--查询指定分桶里面的数据 就可以找出结果  此时是分桶扫描而不是全表扫描
select *
from t_usa_covid19_bucket where state="New York";

知识点12：Apache Hive--DDL--建表语句--分桶表的好处、注意事项

知识点13：Apache Hive--DDL--库、表、分区其他操作

因为Hive建表、加载数据及其方便高效；在实际的应用中，如果建表有问题，通常可以直接drop删除重新创建加载数据。时间成本极低。

如果表是外部表的话，更加完美了。

--创建数据库
create database if not exists itcast
comment "this is my first db"
with dbproperties ('createdBy'='Allen');

--描述数据库信息
describe database itcast;
describe database extended itcast;
desc database extended itcast;

--切换数据库
use default;
use itcast;
create table t_1(id int);

--删除数据库
--注意 CASCADE关键字慎重使用
DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];
drop database itcast cascade ;


--更改数据库属性
ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, ...);
--更改数据库所有者
ALTER (DATABASE|SCHEMA) database_name SET OWNER [USER|ROLE] user_or_role;
--更改数据库位置
ALTER (DATABASE|SCHEMA) database_name SET LOCATION hdfs_path;


--下面这两个需要记住
--查询指定表的元数据信息
desc formatted itheima.t_all_hero_part;
show create table t_all_hero_part;

--1、更改表名
ALTER TABLE table_name RENAME TO new_table_name;
--2、更改表属性
ALTER TABLE table_name SET TBLPROPERTIES (property_name = property_value, ... );
--更改表注释
ALTER TABLE student SET TBLPROPERTIES ('comment' = "new comment for student table");
--3、更改SerDe属性
ALTER TABLE table_name SET SERDE serde_class_name [WITH SERDEPROPERTIES (property_name = property_value, ... )];
ALTER TABLE table_name [PARTITION partition_spec] SET SERDEPROPERTIES serde_properties;
ALTER TABLE table_name SET SERDEPROPERTIES ('field.delim' = ',');
--移除SerDe属性
ALTER TABLE table_name [PARTITION partition_spec] UNSET SERDEPROPERTIES (property_name, ... );

--4、更改表的文件存储格式 该操作仅更改表元数据。现有数据的任何转换都必须在Hive之外进行。
ALTER TABLE table_name  SET FILEFORMAT file_format;
--5、更改表的存储位置路径
ALTER TABLE table_name SET LOCATION "new location";

--6、更改列名称/类型/位置/注释
CREATE TABLE test_change (a int, b int, c int);
// First change column a's name to a1.
ALTER TABLE test_change CHANGE a a1 INT;
// Next change column a1's name to a2, its data type to string, and put it after column b.
ALTER TABLE test_change CHANGE a1 a2 STRING AFTER b;
// The new table's structure is:  b int, a2 string, c int.
// Then change column c's name to c1, and put it as the first column.
ALTER TABLE test_change CHANGE c c1 INT FIRST;
// The new table's structure is:  c1 int, b int, a2 string.
// Add a comment to column a1
ALTER TABLE test_change CHANGE a1 a1 INT COMMENT 'this is column a1';

--7、添加/替换列
--使用ADD COLUMNS，您可以将新列添加到现有列的末尾但在分区列之前。
--REPLACE COLUMNS 将删除所有现有列，并添加新的列集。
ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type,...);

比较重要的是增加分区、删除分区操作

--1、增加分区
--step1: 创建表 手动加载分区数据
drop table if exists t_user_province;
create table t_user_province (
    num int,
    name string,
    sex string,
    age int,
    dept string) partitioned by (province string);

load data local inpath '/root/hivedata/students.txt' into table t_user_province partition(province ="SH");


--step2:手动创建分区的文件夹 且手动上传文件到分区中 绕开了hive操作  发现hive无法识别新分区
hadoop fs -mkdir /user/hive/warehouse/itheima.db/t_user_province/province=XM
hadoop fs -put students.txt /user/hive/warehouse/itheima.db/t_user_province/province=XM


--step3：修改hive的分区，添加一个分区元数据
ALTER TABLE t_user_province ADD PARTITION (province='XM') location
    '/user/hive/warehouse/itheima.db/t_user_province/province=XM';


----此外还支持一次添加多个分区
ALTER TABLE table_name ADD PARTITION (dt='2008-08-08', country='us') location '/path/to/us/part080808'
    PARTITION (dt='2008-08-09', country='us') location '/path/to/us/part080809';


--2、重命名分区
ALTER TABLE t_user_province PARTITION (province ="SH") RENAME TO PARTITION (province ="Shanghai");

--3、删除分区
ALTER TABLE table_name DROP [IF EXISTS] PARTITION (dt='2008-08-08', country='us');
ALTER TABLE table_name DROP [IF EXISTS] PARTITION (dt='2008-08-08', country='us') PURGE; --直接删除数据 不进垃圾桶 有点像skipTrash

--4、修复分区
MSCK [REPAIR] TABLE table_name [ADD/DROP/SYNC PARTITIONS];
--详细使用见课件资料

--5、修改分区
--更改分区文件存储格式
ALTER TABLE table_name PARTITION (dt='2008-08-09') SET FILEFORMAT file_format;
--更改分区位置
ALTER TABLE table_name PARTITION (dt='2008-08-09') SET LOCATION "new location";

知识点14：Apache Hive--常见的show语法

show databases 数据库

show tables 表

show partitions 表的所有分区 注意必须是分区表才可以执行该语法

desc formatted table_name; 查看表的元数据信息

show create table table_name; 获取表的DDL建表语句

show functions; 函数方法

--1、显示所有数据库 SCHEMAS和DATABASES的用法 功能一样
show databases;
show schemas;

--2、显示当前数据库所有表/视图/物化视图/分区/索引
show tables;
SHOW TABLES [IN database_name]; --指定某个数据库

--3、显示当前数据库下所有视图
--视图相当于没有数据临时表  虚拟表
Show Views;
SHOW VIEWS 'test_*'; -- show all views that start with "test_"
SHOW VIEWS FROM test1; -- show views from database test1
SHOW VIEWS [IN/FROM database_name];

--4、显示当前数据库下所有物化视图
SHOW MATERIALIZED VIEWS [IN/FROM database_name];

--5、显示表分区信息，分区按字母顺序列出，不是分区表执行该语句会报错
show partitions table_name;
show partitions itheima.student_partition;

--6、显示表/分区的扩展信息
SHOW TABLE EXTENDED [IN|FROM database_name] LIKE table_name;
show table extended like student;
describe formatted itheima.student;

--7、显示表的属性信息
SHOW TBLPROPERTIES table_name;
show tblproperties student;

--8、显示表、视图的创建语句
SHOW CREATE TABLE ([db_name.]table_name|view_name);
show create table student;

--9、显示表中的所有列，包括分区列。
SHOW COLUMNS (FROM|IN) table_name [(FROM|IN) db_name];
show columns  in student;

--10、显示当前支持的所有自定义和内置的函数
show functions;

--11、Describe desc
--查看表信息
desc extended table_name;
--查看表信息（格式化美观）
desc formatted table_name;
--查看数据库相关信息
describe database database_name;

知识点15：Apache Hive--DML--load加载数据（注意local含义）

知识点16：Apache Hive--DML--insert插入语法

insert+select

在hive中，insert主要是结合 select 查询语句使用，将查询结果插入到表中。

--step1:创建一张源表student
drop table if exists student;
create table student(num int,name string,sex string,age int,dept string)
row format delimited
fields terminated by ',';
--加载数据
load data local inpath '/root/hivedata/students.txt' into table student;

select * from student;

--step2：创建一张目标表  只有两个字段
create table student_from_insert(sno int,sname string);
--使用insert+select插入数据到新表中
insert into table student_from_insert
select num,name from student;

select *
from student_from_insert;

Multi Inserts 多重插入

Dynamic partition inserts 动态分区插入

知识点17：Apache Hive--DML--insert导出数据操作

今日课程作业

#1、Hive DDL 建表语句  重点之重点
	数据类型
	SerDe序列化机制（Hive怎么读写HDFS文件的）
	分隔符指定
	默认分隔符
	内外部表
	存储路径location
	
	分区表
	分桶表
		分区、分桶是什么
		解决了什么问题
		如何创建
		如何加载
		如何使用
		注意事项
#2、Hive DDL Alter
	partition的增加、修改、修复

#3、常见的show
	库  
	表
	分区
	建表语句
	表元数据信息
	函数方法
    
#4、hive DML  重点之重点
	load
		local本地是哪里？
		如何理解load是一个纯操作？纯是什么意思？
		什么时候是复制  什么时候是加载
	insert
		insert+select
		多重插入  优化
		动态分区插入   #这个很重要
			什么叫动态 静态
			动态分区的严格模式  非严格模式是啥
		数据导出操作
			注意overwrite覆盖