Hive（二）DDL DML DQL

一. 数据库DDL

Hive中DATABASE的概念和RDBMS中类似，我们称之为数据库。在Hive中， DATABASE和SCHEMA是可互换的，使用DATABASE或SCHEMA都可以
CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]

[WITH DBPROPERTIES (property_name=property_value, …)];
COMMENT：数据库的注释说明语句

LOCATION：指定数据库在HDFS存储位置，默认/user/hive/warehouse

WITH DBPROPERTIES：用于指定一些数据库的属性配置

create database if not exists gzhu
comment "this is my first db"
with dbproperties ('createdBy'='kun');

注意：使用location指定路径的时候，最好是一个新创建的空文件夹

1.Hive中的DESCRIBE DATABASE语句用于显示Hive中数据库的名称，其注释（如果已设置）及其在文件系统上的位置等信息

DESCRIBE DATABASE/SCHEMA [EXTENDED] db_name;

EXTENDED：用于显示更多信息

2.Hive中的USE DATABASE语句用于选择特定的数据库,切换当前会话使用哪一个数据库进行操作

USE database_name;

3.Drop database
Hive中的DROP DATABASE语句用于删除（删除）数据库
默认行为是RESTRICT，这意味着仅在数据库为空时才删除它。要删除带有表的数据库，我们可以使用CASCADE

DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];

4.Alter database
Hive中的ALTER DATABASE语句用于更改与Hive中的数据库关联的元数据

--更改数据库属性
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;

二.表DDL

数据定义语言 (Data Definition Language, DDL)，是SQL语言集中对数据库内部的对象结构进行创建，删除，修改等的操作语言，这些数据库对象包括database（schema）、table、view、index等。核心语法由CREATE、ALTER与DROP三个所组成。DDL并不涉及表内部数据的操作

1.Hive数据类型

Hive中的数据类型指的是Hive表中的列字段类型

Hive数据类型整体分为两个类别：原生数据类型和复杂数据类型

原生数据类型包括：数值类型、时间类型、字符串类型、杂项数据类型

复杂数据类型包括：array数组、map映射、struct结构、union联合体

Create  table  complex(col1 ARRAY< INT >,
Col2 MAP<STRING, INT >,
Col3 STRUCT<a:STRING,b : INT ,c: DOUBLE >);

关于Hive的数据类型，需要注意：

• 英文字母大小写不敏感

• 除SQL数据类型外，还支持Java数据类型，比如：string

• int和string是使用最多的，大多数函数都支持

• 复杂数据类型的使用通常需要和分隔符指定语法配合使用

• 如果定义的数据类型和文件不一致，hive会尝试隐式转换，但是不保证成功

2.Hive读写文件流程

Hive读取文件机制：首先调用InputFormat（默认TextInputFormat），返回一条一条KV键值对记录（默认是一行对应一条记录）。然后调用SerDe（默认LazySimpleSerDe）的Deserializer，将一条记录中的value根据分隔符切分为各个字段，SerDe是Serializer、Deserializer的简称，目的是用于序列化和反序列化

Hive写文件机制：将Row写入文件时，首先调用SerDe（默认LazySimpleSerDe）的Serializer将对象转换成字节序列，然后调用OutputFormat将数据写入HDFS文件中

需要注意的是，”key”部分在读取时会被忽略，而在写入时key始终是常数。基本上行对象存储在"value"中

数据分隔符的指定

LazySimpleSerDe是Hive默认的序列化、反序列化类，包含4种子语法，分别用于指定字段之间、集合元素之间、map映射KV之间、换行的分隔符号。在建表的时候可以根据数据的特点灵活搭配使用，不指定ROW FORMAT默认采用’\001’分隔符

使用ROW FORMAT DELIMITED就可以让我们完成对数据的处理，我们也可以用ROW FORMAT SERDE来自定义序列化类

3.建表案例

1.对如下数据建立Hive表 — 简单类型映射

use gzhu; // 指定数据库

create table t_shooter(
         id int comment "ID",
         name string comment "英雄名称",
         hp_max int comment "最大生命",
         mp_max int comment "最大法力",
         attack_max int comment "最高物攻",
         defense_max int comment "最大物防",
         attack_range string comment "攻击范围",
         role_main string comment "主要定位",
         role_assist string comment "次要定位"
) comment "王者荣耀射手信息"
    row format delimited
        fields terminated by "\t";   // 默认按行读取，以\t分割

上传文件到HDFS该路径下

2. 复杂数据建表

id name 胜率 map<String,int>

create table t_hot_hero_skin(
    id int,
    name String,
    win_rate int,
    skin_price map<STRING,INT>
) row format delimited
fields terminated by ","  // 字段之间用,分割
collection items terminated by "-"  // 集合之间用 - 分割
map keys terminated by ":" // KV用:分割

4.内部表外部表

内部表（Internal table）也称为被Hive拥有和管理的托管表（Managed table）。默认情况下创建的表就是内部表，Hive拥有该表的结构和文件。换句话说，Hive完全管理表（元数据和数据）的生命周期，类似于RDBMS中的表。删除内部表时，它会删除数据以及表的元数据

可以使用DESCRIBE FORMATTED table;来获取表的描述信息，从中可以看出表的类型

外部表（External table）中的数据不是Hive拥有或管理的，只管理表元数据的生命周期。要创建一个外部表，需要使用EXTERNAL语法关键字
删除外部表只会删除元数据，而不会删除实际数据。在Hive外部仍然可以访问实际数据
而且外部表更为方便的是可以搭配location语法指定数据的路径

5.分区表

1.建表时要指定分区

CREATE TABLE IF NOT EXISTS dm.dwd_tfc_show_page_hi (
`device_id` string COMMENT '设备ID，同一APP下唯一标识一台设备',
`global_id` string COMMENT '设备指纹ID，集团内跨APP唯一设备标识',
`user_id` bigint COMMENT '用户ID，统一账号体系内唯一标识一个用户',
) 
COMMENT 'a'
PARTITIONED BY (
`p_date` string COMMENT '日期 格式:yyyyMMdd eg:20200101',
`p_hour` string COMMENT '小时 格式:HH eg:08',
`product` string COMMENT '产品',
`func_channel` string COMMENT '功能通道'
)

动静结合

insert overwrite table dm.dwd_tfc_show_page_hi partition (p_date = '{{ds_nodash}}',p_hour = '{{hour}}',product,func_channel)
select device_id,global_id,user_id,product,func_channel
from table
where p_date = "" and p_hour = ""

1.为什么分区

假如一张表的数据中，角色定位有射手、辅助、中单，我想查询射手的信息，毫无疑问，那么会进行全表扫描来判断每一条数据是不是射手，是非常耗时的，这时候分区就有用了，我可以根据角色定位这一列进行分区，射手分区，辅助分区，中单分区，这样我在查询的时候即可以定位到射手分区，其他的都不用管了，也即分区裁剪，极大的节省了时间！

2.怎么分区

关键字partitioned by指定分区列，这里有疑问了，它怎么知道根据哪一列呢，因为有好几个string类型的列，它当然不知道，我们后续才会指定，这里只是告诉Hive，我将根据某一列进行分区，那我直接相同的名字指定它不就知道哪一列了吗？不可以同名，规定

3.分区静态数据加载

我们将要被分区的文件放在HDFS上

使用load关键字将文件静态加载（这里实际上是剪切操作）

load data inpath 'hdfs://hadoop102:8020/hive_part/archer.txt' into table t_all_hero partition(role_main_part='archer');
load data inpath 'hdfs://hadoop102:8020/hive_part/assassin.txt' into table t_all_hero partition(role_main_part='assassin');
load data inpath 'hdfs://hadoop102:8020/hive_part/mage.txt' into table t_all_hero partition(role_main_part='mage');
load data inpath 'hdfs://hadoop102:8020/hive_part/support.txt' into table t_all_hero partition(role_main_part='support');
load data inpath 'hdfs://hadoop102:8020/hive_part/tank.txt' into table t_all_hero partition(role_main_part='tank');
load data inpath 'hdfs://hadoop102:8020/hive_part/warrior.txt' into table t_all_hero partition(role_main_part='warrior');

文件被剪切到了对应表的路径下，并且名字是分区列 = 分区值的文件


实际的表中会多处一列

4.分区动态数据加载

往hive分区表中插入加载数据时，如果需要创建的分区很多，则需要复制粘贴修改很多sql去执行，效率低。因为hive是批处理系统，所以hive提供了一个动态分区功能，其可以基于查询参数的位置去推断分区的名称，从而建立分区

所谓动态分区指的是分区的字段值是基于查询结果自动推断出来的。核心语法就是insert+select

启用hive动态分区，需要在hive会话中设置两个参数：

set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;

第一个参数表示开启动态分区功能，第二个参数指定动态分区的模式。分为nonstick非严格模式和strict严格模式。strict严格模式要求至少有一个分区为静态分区

insert into table t_all_hero_part_dynamic partition(role) 
select tmp.*,tmp.role_main from t_all_hero tmp;

动态分区就是查询某张已经存在的表，然后赋值

实际的表也是多了一列

文件路径也和静态装载一样

5.多重分区

当然，我们可以进行多重分区

多重分区下，分区之间是一种递进关系，可以理解为在前一个分区的基础上继续分区。从HDFS的角度来看就是文件夹下继续划分子文件夹。比如：把全国人口数据首先根据省进行分区，然后根据市进行划分，如果你需要甚至可以继续根据区县再划分，此时就是3分区表

单分区表，按省份分区

create table t_user_province (id int, name string,age int) partitioned by (province string);

双分区表，按省份和市分区

create table t_user_province_city (id int, name string,age int) partitioned by (province string, city string);

三分区表，按省份、市、县分区

create table t_user_province_city_county (id int, name string,age int) partitioned by (province string, city string,county string);

6.分区注意事项

分区表不是建表的必要语法规则，是一种优化手段表，可选

分区字段不能是表中已有的字段，不能重复

分区字段是虚拟字段，其数据并不存储在底层的文件中，但表中有字段

分区字段值的确定来自于用户价值数据手动指定（静态分区）或者根据查询结果位置自动推断（动态分区）

Hive支持多重分区，也就是说在分区的基础上继续分区，划分更加细粒度

6.分桶表

1.怎么分桶

在分桶时，我们要指定根据哪个字段将数据分为几桶（几个部分），底层通过多个Reduce来实现

默认规则是：Bucket number = hash_function(bucketing_column) mod num_buckets

可以发现桶编号相同的数据会被分到同一个桶当中

hash_function取决于分桶字段bucketing_column的类型：
如果是int类型，hash_function(int) == int
如果是其他类型，比如bigint，string或者复杂数据类型，hash_function比较棘手，将是从该类型派生的某个数字，比如hashcode值

分桶表建表语句

CREATE [EXTERNAL] TABLE [db_name.]table_name
[(col_name data_type, ...)]
CLUSTERED BY (col_name)
INTO N BUCKETS;

其中CLUSTERED BY (col_name)表示根据哪个字段进行分
INTO N BUCKETS表示分为几桶（也就是几个部分）
需要注意的是，分桶的字段必须是表中已经存在的字段

create table t_all_hero_bucketed(
        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
)
clustered by(id) sorted by (hp_max) into 5 BUCKETS 

因为分桶表的数据加载来源于已经存在的表，所以只需要指定分桶字段（排序可选）和分桶数

2.分桶数据加载

insert into t_all_hero_bucketed select * from t_all_hero;

可以看到，每个桶对应一个文件

3.分桶的作用

1.基于分桶字段查询时，减少全表扫描

基于分桶字段查询，可直接定位到具体的文件

2.JOIN时可以提高MR程序效率，减少笛卡尔积数量

对于两张表，根据JOIN字段分桶，会进行桶与桶之间进行JOIN，提高了效率

3.分桶表数据进行抽样

当数据量特别大时，对全体数据进行处理存在困难时，抽样就显得尤其重要了。抽样可以从被抽取的数据中估计和推断出整体的特性，是科学实验、质量检验、社会调查普遍采用的一种经济有效的工作和研究方法

7.事务

虽然Hive支持了具有ACID语义的事务，但是在使用起来，并没有像在MySQL中使用那样方便，有很多局限性。原因很简单，毕竟Hive的设计目标不是为了支持事务操作，而是支持分析操作，且最终基于HDFS的底层存储机制使得文件的增加删除修改操作需要动一些小心思。具体限制如下：

• 尚不支持BEGIN，COMMIT和ROLLBACK。所有语言操作都是自动提交的
• 仅支持ORC文件格式（STORED AS ORC）
• 默认情况下事务配置为关闭。需要配置参数开启使用
• 表必须是分桶表（Bucketed）才可以使用事务功能
• 表参数transactional必须为true
• 外部表不能成为ACID表，不允许从非ACID会话读取/写入ACID表

事务表原理

HDFS初始文件作为base，然后进行增删改会再创建新的文件来表示

insert会再创建一个delta目录

delete操作的目录前缀是delete_delta

update的过程是先删除、后插入
查询的时候是base和delta合并进行查询

合并器

小合并：当增量文件太多，会将这些delta文件合并为一个增量文件，默认触发条件为10个

大合并：也可以多个增量文件和基础文件进行合并，默认触发条件为delta文件响应与基础文件占比10%

不是事务表，不支持修改、删除操作

Hive中事务表的创建使用

开启事务配置（可以使用set设置当前session生效 也可以配置在hive-site.xml中）

set hive.support.concurrency = true; --Hive是否支持并发
set hive.exec.dynamic.partition.mode = nonstrict; --动态分区模式  非严格
set hive.txn.manager = org.apache.hadoop.hive.ql.lockmgr.DbTxnManager
set hive.compactor.initiator.on = true; --是否在Metastore实例上运行启动线程和清理线程
set hive.compactor.worker.threads = 1; --在此metastore实例上运行多少个压缩程序工作线程

创建Hive事务表

create table trans_student(
    id int,
    name String,
    age int
)clustered by (id) into 2 buckets stored as orc TBLPROPERTIES('transactional'='true');

8.视图

Hive中的视图（view）是一种虚拟表，只保存定义，不实际存储数据。通常从真实的物理表查询中创建生成视图，也可以从已经存在的视图上创建新视图。

创建视图时，将冻结视图的架构，如果删除或更改基础表，则视图将失败，并且视图不能存储数据，操作数据，只能查询

概况起来就是：视图是用来简化操作的，它其实是一张虚表，在视图中不缓冲记录，也没有提高查询性能

create view v_shooter as select * from t_shooter limit 5; -- 创建视图

show views ; -- 显示视图

select * from v_shooter; -- 查询

show create table v_shooter; -- 显示视图创建时的语句

drop view v_shooter; -- 删除视图

select * from v_shooter; -- 查询

alter view v_shooter as select * from t_shooter limit 6; -- 修改视图定义

物化视图

在传统的数据库领域基本已经都实现了物化视图, 属于数据库的高级功能。物化视图（Materialized View）是一个包括查询结果的数据库对像，可以用于预先计算并保存表连接或聚集等耗时较多的操作的结果。这样，在执行查询时，就可以避免进行这些耗时的操作，而从快速的得到结果。使用物化视图的目的就是通过预计算，提高查询性能，当然需要占用一定的存储空间

Hive引入物化视图的目的就是为了优化数据查询访问的效率,相当于从数据预处理的角度优化数据访问

1.视图是虚拟的，逻辑存在的，只有定义没有存储数据
2.物化视图是真实的，物理存在的，里面存储着预计算的数据
3.视图的目的是简化降低查询的复杂度，而物化视图的目的是提高查询性能

物化视图要基于事务表（事务表要基于分桶表）

// 1.开启事务支持
set hive.support.concurrency = true;
set hive.exec.dynamic.partition.mode = nonstrict;
set hive.txn.manager = org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;
set hive.compactor.initiator.on = true;
set hive.compactor.worker.threads = 1;

// 2.创建分桶事务表
create table trans_student(
          id int,
          name String,
          age int
)clustered by (id) into 2 buckets stored as orc TBLPROPERTIES('transactional'='true');

// 3.插入数据
insert overwrite table trans_student
select id,name,age from t_person;

![// 4.查询
select count(*)
from trans_student where age > 16;](https://img-blog.csdnimg.cn/66440086eb984d43b0bb5eeda61dea90.png)

竟然耗时2分钟！！！

// 5.创建物化视图
CREATE MATERIALIZED VIEW mv_trans_student
AS select count(*) from trans_student where age > 16;

// 6.再次查询
select count(*)
from trans_student where age > 16;

命中物化视图，仅用时2秒，缩短了70倍

explain分新，可以看到扫描的是物化视图表

物化视图是实实在在的表

9.表的其他DDL

1.显示详细信息

describe formatted [db_name.]table_name;
describe extended [db_name.]table_name;

如果指定了EXTENDED关键字，则它将以Thrift序列化形式显示表的所有元数据。如果指定了FORMATTED关键字，则它将以表格格式显示元数据

2.drop

DROP TABLE删除该表的元数据和数据。如果已配置垃圾桶（且未指定PURGE），则该表对应的数据实际上将移动到.Trash/Current目录，而元数据完全丢失。删除EXTERNAL表时，该表中的数据不会从文件系统中删除，只删除元数据

如果指定了PURGE，则表数据不会进入.Trash/Current目录，跳过垃圾桶直接被删除。因此如果DROP失败，则无法挽回该表数据

DROP TABLE [IF EXISTS] table_name [PURGE];    -- (Note: PURGE available in Hive 0.14.0 and later)

3.Truncate table

从表中删除所有行。可以简单理解为清空表的所有数据但是保留表的元数据结构。如果HDFS启用了垃圾桶，数据将被丢进垃圾桶，否则将被删除

TRUNCATE [TABLE] table_name;

4.alter

2.4．Alter table
--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,...);

10.分区DDL

1.Add partition

分区值仅在为字符串时才应加引号。位置必须是数据文件所在的目录

ADD PARTITION会更改表元数据，但不会加载数据。如果分区位置中不存在数据，查询将不会返回任何结果

alter table t_all_hero_part_dynamic add partition(role = 'add')
    location '/user/hive/warehouse/gzhu.db/t_all_hero_part_dynamic/role=add'

2.rename partition

ALTER TABLE table_name PARTITION (dt='2008-08-09') RENAME TO PARTITION (dt='20080809');

3.delete partition
可以使用ALTER TABLE DROP PARTITION删除表的分区。这将删除该分区的数据和元数据

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; --直接删除数据 不进垃圾桶

4.msck partition

Hive将每个表的分区列表信息存储在其metastore中。但是，如果将新分区直接添加到HDFS（例如通过使用hadoop fs -put命令）或从HDFS中直接删除分区文件夹，则除非用户ALTER TABLE table_name ADD/DROP PARTITION在每个新添加的分区上运行命令，否则metastore（也就是Hive）将不会意识到分区信息的这些更改
但是，用户可以使用修复表选项运行metastore check命令

MSCK [REPAIR] TABLE table_name [ADD/DROP/SYNC PARTITIONS];

• ADD：使用此选项，它将把HDFS上存在但元存储中不存在的所有分区添加到元存储中
• DROP PARTITIONS选项将从已经从HDFS中删除的metastore中删除分区信息
• SYNC PARTITIONS选项等效于调用ADD和DROP PARTITIONS

5.alter partition

--更改分区文件存储格式
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";

三.DML

1.load加载数据

我们知道，我们建立了表必须要把数据放到对应的表路径下，或者通过location指定文件才可以

创建三张表测试load

create table student_local(num int,name string,sex string,age int,dept string) row format delimited fields terminated by ',';
--建表student_HDFS  用于演示从HDFS加载数据
create external table student_HDFS(num int,name string,sex string,age int,dept string) row format delimited fields terminated by ',';
--建表student_HDFS_p 用于演示从HDFS加载数据到分区表
create table student_HDFS_p(num int,name string,sex string,age int,dept string) partitioned by(country string) row format delimited fields terminated by ',';

方法一 本地加载 复制操作，本地文件不会消失

本地指的是HiveServer2所在的机器的linux文件系统

load data local inpath "/tmp/students.txt" into table student_local;

方法二 HDFS加载 剪切操作，HDFS元路径下的文件小时

load data inpath "hdfs://hadoop102:8020/hive_part/students.txt" into table student_HDFS;

演示三 分区静态加载

load data local inpath "/tmp/students.txt" into table student_hdfs_p partition (country = 'CN');

2.insert

insert可以吗？可以，但我们可以测试一下

插入三个字段竟然用了2分钟，这是因为底层执行了MR程序

但是并不意味着insert语法在Hive中没有使用地位了，通常在Hive中我们使用insert+select语句。即插入表的数据来自于后续select查询语句返回的结果

多重插入，核心是：一次扫描，多次插入。其功能也体现出来了就是减少扫描的次数

--当前库下已有一张表student
select * from student;
--创建两张新表
create table student_insert1(sno int);
create table student_insert2(sname string);
--多重插入
from student  --from在前
insert overwrite table student_insert1
select num
insert overwrite table student_insert2
select name;

导出

可以把一些查询结果以文件形式保存到HDFS或者本地，注意这是overwrite操作，一定要保证路径是干净的！！！！！

--当前库下已有一张表student
select * from student;

--1、导出查询结果到HDFS指定目录下
insert overwrite directory '/tmp/hive_export/e1' select * from student;

--2、导出时指定分隔符和文件存储格式
insert overwrite directory '/tmp/hive_export/e2' row format delimited fields terminated by ','
stored as orc
select * from student;

--3、导出数据到本地文件系统指定目录下
insert overwrite local directory '/root/hive_export/e1' select * from student;

四.DQL

1.基础DQL

CREATE TABLE gzhu.t_usa_covid19(
       count_date string,
       county string,
       state string,
       fips int,
       cases int,
       deaths int)
row format delimited fields terminated by ",";

--将源数据load加载到t_usa_covid19表对应的路径下
load data local inpath '/root/hivedata/us-covid19-counties.dat' into table t_usa_covid19;

--创建分区表 并动态加载数据
CREATE TABLE gzhu.t_usa_covid19_par(
       county string,
       fips int,
       cases int,
       deaths int)partitioned by (count_date string,state string)
    row format delimited fields terminated by ",";

set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;

insert into table gzhu.t_usa_covid19_par partition(count_date ,state )
select county,fips,cases,deaths,count_date,state from t_usa_covid19;

1.select_expr
每个select_expr表示您要检索的列。必须至少有一个 select_expr

--select_expr
--查询所有字段或者指定字段
select * from t_usa_covid19_p;
select county, cases, deaths from t_usa_covid19_p;
--查询使用函数
select count(county) from t_usa_covid19_p;

2. ALL 、DISTINCT
ALL和DISTINCT选项指定是否应返回重复的行。如果没有给出这些选项，则默认值为ALL（返回所有匹配的行）。DISTINCT指定从结果集中删除重复的行

--ALL DISTINCT
--返回所有匹配的行
select state
from t_usa_covid19_p;
--相当于
select all state
from t_usa_covid19_p;
--返回所有匹配的行 去除重复的结果
select distinct state
from t_usa_covid19_p;
--多个字段distinct 整体去重
select distinct county,state from t_usa_covid19_p;

3.WHERE

WHERE条件是一个布尔表达式。在WHERE表达式中，您可以使用Hive支持的任何函数和运算符，但聚合函数除外
从Hive 0.13开始，WHERE子句支持某些类型的子查询

select * from t_usa_covid19_p where state ="California" and deaths > 1000;
select * from t_usa_covid19_p where 1 > 2;  -- 1 > 2 返回false
select * from t_usa_covid19_p where 1 = 1;  -- 1 = 1 返回true

--where条件中使用函数 找出州名字母超过10个
select * from t_usa_covid19_p where length(state) >10 ;

--WHERE子句支持子查询
SELECT *
FROM A
WHERE A.a IN (SELECT foo FROM B);

--where条件中不能使用聚合函数
--报错 SemanticException:Not yet supported place for UDAF 'sum'
select state,sum(deaths)
from t_usa_covid19_p where sum(deaths) >100 group by state;

那么为什么不能在where子句中使用聚合函数呢？
因为聚合函数要使用它的前提是结果集已经确定。而where子句还处于“确定”结果集的过程中，因而不能使用聚合函数

4. GROUP BY

GROUP BY 语句用于结合聚合函数，根据一个或多个列对结果集进行分组。需要注意的是，出现在GROUP BY中select_expr的字段：要么是GROUP BY分组的字段；要么是被聚合函数应用的字段。原因很简单，避免出现一个字段多个值的歧义

分组字段出现select_expr中，一定没有歧义，因为就是基于该字段分组的，同一组中必相同

被聚合函数应用的字段，也没歧义，因为聚合函数的本质就是多进一出，最终返回一个结果

--根据state州进行分组

--SemanticException:Expression not in GROUP BY key 'deaths'
--deaths不是分组字段 报错
--state是分组字段 可以直接出现在select_expr中
select state,deaths
from t_usa_covid19_p where count_date = "2021-01-28" group by state; --报错

--被聚合函数应用
select state,count(deaths)
from t_usa_covid19_p where count_date = "2021-01-28" group by state;

5.HAVING

在SQL中增加HAVING子句原因是，WHERE关键字无法与聚合函数一起使用

HAVING子句可以让我们筛选分组后的各组数据，并且可以在Having中使用聚合函数，因为此时where，group by已经执行结束，结果集已经确定

--having
--统计死亡病例数大于10000的州
--where语句中不能使用聚合函数 语法报错
select state,sum(deaths)
from t_usa_covid19_p
where count_date = "2021-01-28" and sum(deaths) >10000 group by state;

--先where分组前过滤（此处是分区裁剪），再进行group by分组（含聚合）， 分组后每个分组结果集确定 再使用having过滤
select state,sum(deaths)
from t_usa_covid19_p
where count_date = "2021-01-28"
group by state
having sum(deaths) > 10000;

--这样写更好 即在group by的时候聚合函数已经作用得出结果 having直接引用结果过滤 不需要再单独计算一次了
select state,sum(deaths) as cnts
from t_usa_covid19_p
where count_date = "2021-01-28"
group by state
having cnts> 10000;

having与where的区别

having是在分组后对数据进行过滤，此having后面可以使用聚合函数，having后面也可以跟数据库字段，但前提是前面SELECT语句查询了该字段！

where是在分组前对数据进行过滤，聚合函数的结果已确定，where后面不可以使用聚合。where后面只能跟数据库字段，别名也不可以！

6. LIMIT

LIMIT子句可用于约束SELECT语句返回的行数
LIMIT接受一个或两个数字参数，这两个参数都必须是非负整数常量
第一个参数指定要返回的第一行的偏移量（从 Hive 2.0.0开始），第二个参数指定要返回的最大行数。当给出单个参数时，它代表最大行数，并且偏移量默认为0

--limit
--没有限制返回2021.1.28 加州的所有记录
select * from t_usa_covid19_p
where count_date = "2021-01-28"
and state ="California";

--返回结果集的前5条
select * from t_usa_covid19_p
where count_date = "2021-01-28"
and state ="California"
limit 5;

--返回结果集从第1行开始 共3行
select * from t_usa_covid19_p
where count_date = "2021-01-28"
  and state ="California"
limit 2,3; --注意 第一个参数偏移量是从0开始的

2.排序

--手动设置reduce task个数
set mapreduce.job.reduces =2;

①order by会对输入做全局排序，因此只有一个reducer，会导致当输入规模较大时，需要较长的计算时间

select * from t_usa_covid19_p
where count_date = "2021-01-28"
and state ="California"
order by deaths; --默认asc null first

②sort by不是全局排序，其在数据进入reducer前完成排序。因此，如果用sort by进行排序，并且设置mapred.reduce.tasks>1，则sort by只保证每个reducer的输出有序，不保证全局有序

③distribute by 字段根据指定字段将数据分到不同的reducer，分发算法是hash散列

--案例：把学生表数据根据性别分为两个部分，每个分组内根据年龄的倒序排序。
select * from student distribute by sex sort by sage desc;

④Cluster by(字段) 除了具有Distribute by的功能外，还会对该字段进行排序（默认正序）
如果distribute和sort的字段是同一个时，此时，cluster by = distribute by + sort by

分组规则：hash_func(col_name) % reduce task nums

select * from student cluster by sno; --根据sno分组并且排序

3.Union

UNION用于将来自多个SELECT语句的结果合并为一个结果集。语法如下

select_statement UNION [ALL | DISTINCT] select_statement UNION [ALL | DISTINCT] select_statement ...

使用DISTINCT关键字与只使用UNION默认值效果一样，都会删除重复行

使用ALL关键字，不会删除重复行，结果集包括所有SELECT语句的匹配行（包括重复行）

每个select_statement返回的列的数量和名称必须相同（MySQL 不需要相同，列名以前面的为主）

--union
--使用DISTINCT关键字与使用UNION默认值效果一样，都会删除重复行
select num,name from student_local
UNION
select num,name from student_hdfs;
--和上面一样
select num,name from student_local
UNION DISTINCT
select num,name from student_hdfs;

--使用ALL关键字会保留重复行
select num,name from student_local
UNION ALL
select num,name from student_hdfs;

--如果要将ORDER BY，SORT BY，CLUSTER BY，DISTRIBUTE BY或LIMIT应用于单个SELECT
--请将子句放在括住SELECT的括号内
SELECT sno,sname FROM (select sno,sname from student_local LIMIT 2) subq1
UNION
SELECT sno,sname FROM (select sno,sname from student_hdfs LIMIT 3) subq2

--如果要将ORDER BY，SORT BY，CLUSTER BY，DISTRIBUTE BY或LIMIT子句应用于整个UNION结果，请将ORDER BY，SORT BY，CLUSTER BY，DISTRIBUTE BY或LIMIT放在最后一个之后
select sno,sname from student_local
UNION
select sno,sname from student_hdfs
order by sno desc; --放在最后

4.子查询

在Hive0.12版本，仅在FROM子句中支持子查询。而且必须要给子查询一个名称，因为FROM子句中的每个表都必须有一个名称

子查询返回结果中的列必须具有唯一的名称。子查询返回结果中的列在外部查询中可用，就像真实表的列一样。子查询也可以是带有UNION的查询表达式。Hive支持任意级别的子查询，也就是所谓的嵌套子查询

Hive 0.13.0和更高版本中的子查询名称之前可以包含可选关键字“ AS”

--from子句中子查询（Subqueries）
--子查询
SELECT num
FROM (
         select num,name from student_local
     ) tmp;

--包含UNION ALL的子查询的示例
SELECT t3.name
FROM (
         select num,name from student_local
         UNION distinct
         select num,name from student_hdfs
     ) t3;

where子句中子查询

从Hive 0.13开始，WHERE子句支持某些类型的子查询

--where子句中子查询（Subqueries）
--不相关子查询，相当于IN、NOT IN,子查询只能选择一个列
--（1）执行子查询，其结果不被显示，而是传递给外部查询，作为外部查询的条件使用
--（2）执行外部查询，并显示整个结果
SELECT *
FROM student_hdfs
WHERE student_hdfs.num IN (select num from student_local limit 2);

--相关子查询，指EXISTS和NOT EXISTS子查询
--子查询的WHERE子句中支持对父查询的引用
SELECT A
FROM T1
WHERE EXISTS (SELECT B FROM T2 WHERE T1.X = T2.Y);

MySQL的相关子查询和非相关子查询

相关子查询是指引用了外部查询列的子查询，即子查询会对外部查询的每行进行一次运算（也即先执行外部查询，将外部查询的每一行代入子查询看是否符合，符合外查询才会输出）
多数情况下是子查询的WHERE子句中引用了外部查询的表，引用了别名

非相关子查询是独立于外部查询的子查询，子查询总共执行一次，执行完毕后将值传递给外部查询

5.CTE

公用表表达式（CTE）是一个临时结果集，该结果集是从WITH子句中指定的简单查询派生而来的，该查询紧接在SELECT或INSERT关键字之前

CTE仅在单个语句的执行范围内定义。一个或多个CTE可以在Hive SELECT，INSERT， CREATE TABLE AS SELECT或CREATE VIEW AS SELECT语句中使用

--选择语句中的CTE
with q1 as (select sno,sname,sage from student where sno = 95002)

select *
from q1;

-- from风格
with q1 as (select sno,sname,sage from student where sno = 95002)
from q1
select *;

-- chaining CTEs 链式
with q1 as ( select * from student where sno = 95002),
     q2 as ( select sno,sname,sage from q1)
     
select * from (select sno from q2) a;


-- union案例
with q1 as (select * from student where sno = 95002),
     q2 as (select * from student where sno = 95004)
select * from q1 union all select * from q2;

--视图，CTAS和插入语句中的CTE
-- insert
create table s1 like student;

with q1 as ( select * from student where sno = 95002)

insert overwrite table s1
select * from q1;

select * from s1;

-- ctas
create table s2 as
with q1 as ( select * from student where sno = 95002)

select * from q1;

-- view
create view v1 as
with q1 as ( select * from student where sno = 95002)

select * from q1;

select * from v1;

6.join

1.Hive inner join

内连接是最常见的一种连接，它也被称为普通连接，而关系模型提出者E.FCodd（埃德加•科德）最早称之为自然连接。其中inner可以省略。inner join == join 等价于早期的连接语法
内连接，只有进行连接的两个表中都存在与连接条件相匹配的数据才会被留下来

--1、inner join
select e.id,e.name,e_a.city,e_a.street
from employee e inner join employee_address e_a
on e.id =e_a.id;
--等价于 inner join=join
select e.id,e.name,e_a.city,e_a.street
from employee e join employee_address e_a
on e.id =e_a.id;

--等价于 隐式连接表示法
select e.id,e.name,e_a.city,e_a.street
from employee e , employee_address e_a
where e.id =e_a.id;

2.Hive left join

left join中文叫做是左外连接(Left Outer Jion)或者左连接，其中outer可以省略，left outer join是早期的写法
left join的核心就在于left左。左指的是join关键字左边的表，简称左表
通俗解释：join时以左表的全部数据为准，右边与之关联；左表数据全部返回，右表关联上的显示返回，关联不上的显示null返回

--2、left join
select e.id,e.name,e_conn.phno,e_conn.email
from employee e left join employee_connection e_conn
on e.id =e_conn.id;

--等价于 left outer join
select e.id,e.name,e_conn.phno,e_conn.email
from employee e left outer join  employee_connection e_conn
on e.id =e_conn.id;

3. Hive right join
right join中文叫做是右外连接(Right Outer Jion)或者右连接，其中outer可以省略。
right join的核心就在于Right右。右指的是join关键字右边的表，简称右表
通俗解释：join时以右表的全部数据为准，左边与之关联；右表数据全部返回，左表关联上的显示返回，关联不上的显示null返回
很明显，right join和left join之间很相似，重点在于以哪边为准，也就是一个方向的问题

--3、right join
select e.id,e.name,e_conn.phno,e_conn.email
from employee e right join employee_connection e_conn
on e.id =e_conn.id;

--等价于 right outer join
select e.id,e.name,e_conn.phno,e_conn.email
from employee e right outer join employee_connection e_conn
on e.id =e_conn.id;

4. Hive full outer join

full outer join 等价 full join ,中文叫做全外连接或者外连接
包含左、右两个表的全部行，不管另外一边的表中是否存在与它们匹配的行 在功能上，它等价于对这两个数据集合分别进行左外连接和右外连接，然后再使用消去重复行的操作将上述两个结果集合并为一个结果集

--4、full outer join
select e.id,e.name,e_a.city,e_a.street
from employee e full outer join employee_address e_a
on e.id =e_a.id;
--等价于
select e.id,e.name,e_a.city,e_a.street
from employee e full  join employee_address e_a
on e.id =e_a.id;

5. Hive left semi join

左半开连接（LEFT SEMI JOIN）会返回左边表的记录，前提是其记录对于右边的表满足ON语句中的判定条件
从效果上来看有点像inner join之后只返回左表的结果

--5、left semi join
select *
from employee e left semi join employee_address e_addr
on e.id =e_addr.id;

--相当于 inner join 只不过效率高一些
select e.*
from employee e inner join employee_address e_addr
on e.id =e_addr.id;

6.Hive cross join

交叉连接cross join，将会返回被连接的两个表的笛卡尔积，返回结果的行数等于两个表行数的乘积。对于大表来说，cross join慎用
在SQL标准中定义的cross join就是无条件的inner join。返回两个表的笛卡尔积,无需指定关联键
在HiveSQL语法中，cross join 后面可以跟where子句进行过滤，或者on条件过滤

--6、cross join
--下列A、B、C 执行结果相同，但是效率不一样：
--A:
select a.*,b.* from employee a,employee_address b where a.id=b.id;
--B:
select * from employee a cross join employee_address b on a.id=b.id;
select * from employee a cross join employee_address b where a.id=b.id;

--C:
select * from employee a inner join employee_address b on a.id=b.id;

--一般不建议使用方法A和B，因为如果有WHERE子句的话，往往会先进行笛卡尔积返回数据然后才根据WHERE条件从中选择
--因此，如果两个表太大，将会非常非常慢，不建议使用