初识HIVE

晚棠'.ㅅ'

已于 2023-10-31 20:32:02 修改

阅读量89

点赞数

文章标签： hive

于 2023-10-08 21:37:44 首次发布

本文链接：https://blog.csdn.net/hxm_bbh/article/details/133690667

版权

数据格式

根据学生数据创建对应的表存储方式选择 TextFile

create table student_text(
id String comment 'id',
name String comment '姓名',
age int comment '年龄',
gender String Comment '性别',
clazz String comment '班级'
)
stored as textfile;

hdfs dfs -put students.txt /user/hive/warehouse/bigdata.db/student_text/
--如果不给定列分隔符，那么直接上传数据，会出现列没有分割数据情况
--  

drop table student_text;
create table student_text(
id String comment 'id',
name String comment '姓名',
age int comment '年龄',
gender String Comment '性别',
clazz String comment '班级'
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
stored as textfile;

特点：对数据没有任何压缩一般情况下，作为源表存储的数据格式

SEQUENCEFILE

create table student_seq(
id String comment 'id',
name String comment '姓名',
age int comment '年龄',
gender String Comment '性别',
clazz String comment '班级'
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
stored as SEQUENCEFILE;

insert into table student_seq select * from student_text;

Time taken: 27.131 seconds

46.71 MB

AVRO

create table student_avro(
id String comment 'id',
name String comment '姓名',
age int comment '年龄',
gender String Comment '性别',
clazz String comment '班级'
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
stored as AVRO;

insert into table student_avro select * from student_text;

Time taken: 24.912 seconds 38.79 MB

RCFILE

create table student_rcfile(
id String comment 'id',
name String comment '姓名',
age int comment '年龄',
gender String Comment '性别',
clazz String comment '班级'
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
stored as RCFILE;

insert into table student_rcfile select * from student_text;

Time taken: 20.835 seconds 30.84 MB

ORCFILE

create table student_orcfile(
id String comment 'id',
name String comment '姓名',
age int comment '年龄',
gender String Comment '性别',
clazz String comment '班级'
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
stored as ORCFILE;

insert into table student_orcfile select * from student_text;

Time taken: 21.143 seconds 137.65 KB

PARQUET

create table student_par(
id String comment 'id',
name String comment '姓名',
age int comment '年龄',
gender String Comment '性别',
clazz String comment '班级'
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
stored as PARQUET;
insert into table student_par select * from student_text;

Time taken: 21.699 seconds 3.01 MB

在实际开发过程中，通常使用TextFile 以及OrcFile ，OrcFile主要用于保存生成的结果数据

HIVE 创建数据库

建库标准语法

CREATE DATABASE [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...)];

创建普通库

创建库指定存储位置

CREATE DATABASE test_location LOCATION '/test_location'

创建库指定配置信息

CREATE DATABASE test_1 WITH DBPROPERTIES ('author'='act_xing')

查看库

基本语法
```
DESC DATABASE 库名;
```
详细语法
```
DESC DATABASE EXTENDED 库名;
```
查看带有配置信息的库

HIVE表操作

创建表

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name --[IF NOT EXISTS]如果不存在那么创建  -- EXTERNAL 外部
  [(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]  -- 设定分隔符 （HIVE默认的列分隔符为 \001 行分隔符为 \n）
   [STORED AS file_format]  -- 设定存储格式 
   | STORED BY 'storage.handler.class.name' [ WITH SERDEPROPERTIES (...) ]  (Note:  only available starting with 0.6.0)
  ]
  [LOCATION hdfs_path] -- 指定当前表的存储位置 
  [TBLPROPERTIES (property_name=property_value, ...)]  (Note:  only available starting with 0.6.0) -- 指定配置信息 
  [AS select_statement]  (Note: this feature is only available starting with 0.5.0.) -- 通过查询语句创建表 
  [LIKE table_name]  --复制表结构创建另外一张表

ROW FORMAT 用法

create table test (
 id String,
 name String
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
insert into table test values ('1','zhangsan');
insert into table test values ('2','li,si');

create table test2 (
 id String,
 name String
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001' 
insert into table test2 values ('1','zhangsan');

create table test3 (
 id String,
 name String
);
insert into table test3 values ('1','zhangsan');

HIVE中默认分隔符为 \001 为什么使用该分隔符？

因为当列分隔符和列中的数据存在的符号一致时，会导致列错位情况，那么如果给定的数据，列分隔符和列中的数据符号存在有部分一致，怎么处理?

LOCATION 用法

create table student(
id String comment 'id',
name String comment '姓名',
age int comment '年龄',
gender String Comment '性别',
clazz String comment '班级'
)
LOCATION '/data/student';
insert into table student select * from student_text limit 10;

hdfs dfs -rm -r -f /data/student/000000_0

create table student1000(
id String comment 'id',
name String comment '姓名',
age int comment '年龄',
gender String Comment '性别',
clazz String comment '班级'
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION '/data/student'; 
-- 创建表时，可以指定一个有数据的目录  多个表也可以指定同一个目录 
hdfs dfs -put student1000.txt /data/student/

EXTERNAL 用法

drop table student;
-- 对于drop语法可以删除一张表，同时也会删除 HDFS中对应表目录

create EXTERNAL table student(
id String comment 'id',
name String comment '姓名',
age int comment '年龄',
gender String Comment '性别',
clazz String comment '班级'
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION '/data/student';

hdfs dfs -put student1000.txt /data/student/

-- 对于外部表删除时，只删除了表的元数据信息，对于HDFS中的数据没有影响 
drop table student;

-- 作用：为了防止误删表，同时一般情况下和 LOCATION 搭配使用

STORED AS 用法

用于设置表的存储格式，默认使用TextFile格式

对于源表一般使用TextFile，对生成的结果表一般使用ORCFile格式

TBLPROPERTIES 用法

create EXTERNAL table student_pro(
id String comment 'id',
name String comment '姓名',
age int comment '年龄',
gender String Comment '性别',
clazz String comment '班级'
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
TBLPROPERTIES ('create_author'='act_xing');


-- 通过参数设置的方式构建外部表 
create table student_pro2_external(
id String comment 'id',
name String comment '姓名',
age int comment '年龄',
gender String Comment '性别',
clazz String comment '班级'
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
TBLPROPERTIES ('EXTERNAL'='TRUE');

AS 用法

create  table student_clazz_num_as as select clazz,count(*) as clazz_num from student1000 GROUP BY clazz;
-- EXTERNAL 对于外部表 不能使用as select 创建

create table clazz_num_as like student_clazz_num_as;

PARTITIONED BY 分区

-- PARTITION BY 中给定分区列的列信息 
create table student_partition(
id String comment 'id',
name String comment '姓名',
age int comment '年龄',
gender String Comment '性别'
)
PARTITIONED BY (clazz String comment '班级');

insert into table student_partition partition(clazz='wenke1ban') select id,name,age,gender from student where clazz = '文科一班';

insert into table student_partition partition(clazz='wenke2ban') select id,name,age,gender from student where clazz = '文科二班';

-- 对于普通表做数据过滤时，需要将整个表中的数据加载到内存，再做数据过滤，得到结果
select id,name,age,gender from student where clazz = '文科二班';  

-- 对于分区表来说，如果对分区字段进行过滤，那么只会加载部分分区中的数据，不会做全表扫描 提升了过滤速度
select * from student_partition where clazz='wenke2ban';

查看表

DESC 表名
DESC FORMATTED 表名
DESC EXTENDED 表名
SHOW CREATE TABLE 表名;

删除表

DROP TABLE

DROP TABLE 表名;
-- 对于外部表可以删除其元数据，但是不能删除HDFS中的真实数据

TRUNCATE TABLE

TRUNCATE TABLE 表名;
-- 可以直接清空表中的数据  对于分区表数据可以删除，但是分区目录依旧存在 
-- 对于外部表不能直接清空数据

修改表

change

alter table student change 旧字段名 新字段名 列类型 [comment '描述信息'];

alter table student change age age String comment 'age';

alter table student change age new_age String comment 'age';

-- change 注意是对列信息进行修改

alter table rename to

-- 修改表名称
alter table student_par rename to student_par1;

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

-- 修改表的属性信息
alter table student_pro2_external set  TBLPROPERTIES ('EXTERNAL'='False');

add

-- add 是针对分区表进行操作的 添加分区，在HDFS中会对应多一个分区目录
alter table student_partition add partition(clazz='wenke3ban');

drop

-- drop 是针对分区表进行操作的 删除分区，在HDFS中会对应删除一个分区目录
alter table student_partition drop partition(clazz='wenke3ban');

分区表

分区表创建

create table student_partition_age(
id String comment 'id',
name String comment '姓名',
gender String Comment '性别',
clazz String comment '班级'
)
PARTITIONED BY (age int Comment '年龄');

-- 注意分区列不能和普通的列名称相同

分区表插入数据

1500100001  施笑槐 22  女   文科六班
1500100002  吕金鹏 24  男   文科六班

insert into table student_partition_age values ('1500100001','施笑槐','女','文科六班','22');

insert into table student_partition_age values (1500100002,'施笑槐','女','文科六班','22');

-- 动态数据插入模式：
insert into table student_partition_age select id,name,gender,clazz,new_age from student where new_age ='23'; 

-- 静态数据插入模式：
insert into table student_partition_age partition(age='24') select id,name,gender,clazz from student where new_age ='24';

注意：使用动态插入时，分区列需要再所有列信息最后

-put 方法上传数据

drop table student_partition_gender;
create table student_partition_gender(
id String comment 'id',
name String comment '姓名',
age int Comment '年龄',
clazz String comment '班级'
)
PARTITIONED BY (gender String Comment '性别')
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
;

alter table student_partition_gender add partition(gender='man');

1500100001,施笑槐,22,文科六班

hdfs dfs -mkdir /user/hive/warehouse/bigdata.db/student_partition_gender/gender=nv
hdfs dfs -put gender.txt /user/hive/warehouse/bigdata.db/student_partition_gender/gender=nv
-- 使用put上传数据时，需要使用add 命令将HIVE中的元数据信息进行修改，才能识别出来

查询分区

-- 通过该方式查询分区表的分区信息
show partitions student_partition_gender;

强制删除分区

-- 对于一个分区表及是分区表 也是一个外部表  想要删除其部分分区 怎么处理 
-- 外部表：删除时只能删除元数据信息，不能对数据进行操作 


create external table student_partition_gender_external(
id String comment 'id',
name String comment '姓名',
age int Comment '年龄',
clazz String comment '班级'
)
PARTITIONED BY (gender String Comment '性别')
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
;
alter table student_partition_gender_external add partition(gender='man');
hdfs dfs -put gender.txt /user/hive/warehouse/bigdata.db/student_partition_gender_external/gender=man

-- 方式1：
    --  先将元数据信息删除，之后再去通过HDFS命令删除分区目录
    alter table student_partition_gender_external  drop partition(gender='man');
    hdfs dfs -rm -r -f  /user/hive/warehouse/bigdata.db/student_partition_gender_external/gender=man
-- 方式2：
    --  先将表修改成普通表，之后再通过drop命令删除分区
    alter table student_partition_gender_external set TBLPROPERTIES('EXTERNAL'='false');
    alter table student_partition_gender_external  drop partition(gender='man');
    alter table student_partition_gender_external set TBLPROPERTIES('EXTERNAL'='TRUE'); 

-- 方式3：
    alter table student_partition_gender_external  drop partition(gender='man') PURGE;

恢复被删除分区 msck repair table 表名

创建多级分区

create external table student_partition_gender_clazz(
id String comment 'id',
name String comment '姓名',
age int Comment '年龄'
)
PARTITIONED BY (gender String Comment '性别',clazz String comment '班级')
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
;

insert into table student_partition_gender_clazz partition(gender='nan',clazz='wenke1ban')  select id,name,new_age from student where gender='男' and clazz='文科一班';

-- 多级分区的目录层级关系是由 分区字段定义的顺序决定的 
insert into table student_partition_gender_clazz partition(gender='女',clazz='文科一班')  select id,name,new_age from student where gender='女' and clazz='文科一班';

insert into table student_partition_gender_clazz select id,name,new_age,gender,clazz from student 

-- 注意：对于分区信息，如果存在有中文，那么需要对hive元数据信息表中的分区相关的表设置字符集为utf8 排序格式为 utf8_ganerical

注意具体修改方式在 HIVE开启中文支持文件中

覆盖原先分区中的数据

insert overwrite table student_partition_gender_clazz partition(gender='女',clazz='文科一班')  select id,name,new_age from student where gender='男' and clazz='文科二班';


select * from  student_partition_gender_clazz where gender='女' and clazz='文科一班';

增加动态分区操作

-- 在HIVE 1.X版本中 需要手动设置动态分区的操作 
-- 在HIVE 3.x版本中，不需要手动开启动态分区 可以直接使用 
-- 动态分区：（在插入数据时，可以自动识别分区信息为插入数据的末尾列）
insert into table student_partition_gender_clazz select id,name,new_age,gender,clazz from student 

-- HIVE 1.X版本 中设置方式：


insert into table student_partition_gender_clazz select id,name,new_age,gender,clazz from student 
-- 执行错误
FAILED: SemanticException 1:18 Need to specify partition columns because the destination table is partitioned. Error encountered near token 'student_partition_gender_clazz'

FAILED: SemanticException [Error 10096]: Dynamic partition strict mode requires at least one static partition column. To turn this off 
set hive.exec.dynamic.partition.mode=nonstrict

-- 如果要开启动态分区，那么需要执行如下命令设置对应参数值
hive>set hive.exec.dynamic.partition=true;  -- 开启分区
hive>set hive.exec.dynamic.partition.mode=nostrict;  -- 设置当前模式为非严格模式 （动态分区）
hive>set hive.exec.max.dynamic.partitions.pernode=1000;  -- 设置当前最大分区数


-- hive.exec.max.dynamic.partitions.pernode=100  hive中默认最大分区数为100个，如果表数据较多，那么对应需要将最大分区数进行修改

分区表的作用

应用场景：

通常用于保存大批量数据，根据某一些字段进行设置分区，通常为一个时间字段以日期作为分区字段

比如对于交通领域中的过车数据一天的数据量大概为1500万条，这时可以以日期作为分区字段，将数据按天分别保存下来

分区字段的选择：

1.通常为一个日期字段

2.分区字段通常用于做数据的过滤

分区表的作用：

将数据按分区目录进行保存，在数据做查询过滤时，就可以根据分区字段进行选择部分分区目录进行加载数据，提高加载数据的速度，同时减少不必要的数据提高效率

注意：

在实际开发过程中，需要先查看表的相关信息，比如分区表（分区信息：分区字段，分区值）外部表存储位置等等

分桶表

分区表是以目录作为划分数据的依据，分桶表是以文件作为划分数据的依据

建表语句

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
     [(col_name data_type [COMMENT col_comment], ...)]
    [CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS] --根据 给定的列进行 分桶排序 指定存入 N 个桶中

需求

CREATE TABLE IF NOT EXISTS bucket_table(
id int,name STRING
)
CLUSTERED BY(id) INTO 4 BUCKETS
ROW FORMAT DELIMITED FIELDS TERMINATED BY ",";

-- 在HIVE1.x版本中需要使用INSERT INTO语法将数据插入到桶中能生效
-- INSERT INTO TABLE bucket_table values (1,'student1'),(2,'student2'),(3,'student3'),(4,'student4'),(5,'student5'),(6,'student6'),(7,'student7'),(8,'student8'); 

1,student1
2,student2
3,student3
4,student4
5,student5
6,student6
7,student7
8,student8

-- 将数据放入input目录下
hdfs dfs -put ./id_name.txt  /input
-- 加载HDFS中的数据到HIVE中 
-- 在HIVE3.X版本中需要使用load语句对数据加载至桶表中 
load data inpath "/input/id_name.txt" into table bucket_table;

注意

1.对于分桶表，那么其分桶字段，需要是在表括号中已经定义过的字段，和分区表不同

2.分桶表可以和分区表同时使用

create table student_partition_gender_clazz_BUCKET_age(
id String comment 'id',
name String comment '姓名',
age int Comment '年龄'
)
PARTITIONED BY (gender String Comment '性别',clazz String comment '班级')
CLUSTERED BY(age) INTO 4 BUCKETS
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
;

INSERT INTO TABLE student_partition_gender_clazz_BUCKET_age select id,name,new_age,gender,clazz from student;

-- alter table student_partition_gender_clazz_BUCKET_age set TBLPROPERTIES('EXTERNAL'='false');
hdfs dfs -put student1000.txt  /input
load data inpath '/input/student1000.txt' into table student_partition_gender_clazz_BUCKET_age;
-- load 语句在任何场景下对于数据加载到桶表中是可以生效的

桶表的作用

1.对于数据进行划分，分别存放在不同的文件目录中，后续如果要对分桶的列进行做数据的过滤，只需要加载部分文件就可以了提高查询效率

数据加载

通过load加载数据

-- 1.从本地加载数据到HDFS表中
load data local inpath '本地目录' into table 表名
-- 2.从HDFS中加载数据到对应表目录下
load data inpath 'HDFS目录' into table 表名

CREATE TABLE IF NOT EXISTS load_tbl(
id int,name STRING
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ",";

load data local inpath '/usr/local/soft/hive-3.1.2/data/id_name.txt' into table load_tbl;
-- 对于桶表使用load 加载本地文件到 表中时，会启动 MapReduce 具体读取数据时 使用的是本地文件系统对象 读取对应MapTask本地的路径 这时在从节点node1 node2中并未找到对应的路径 
-- 对于不使用MR过程，那么就相当于使用 put 命令上传数据到 HDFS表目录下

-- 使用load命令加载数据到表中，就相当于使用了HDFS中的MV命令将数据移动到表目录下
load data inpath'/input/id_name.txt' into table load_tbl;
-- 如果加载过程中出现MR的流程，那么就不是直接移动，而是做数据读取  原路径中的文件不会消失

dfs -put

通过HDFS命令将数据上传到表对应目录下
INSERT 方式

INSERT INTO TABLE 表名 Values (数据)

INSERT OVERWRITE TABLE 表名 Values (数据)

INSERT INTO TABLE 表名 SELECT 查询语句

INSERT OVERWRITE TABLE 表名 SELECT 查询语句

AS SELECT

-- 将查询的结果保存成一个表
create table groupBy_clazz_age as
SELECT
clazz,
age,
count(*) as num
FROM student1000
GROUP BY age,clazz

LOCATION

当HDFS中已经有数据存在一个固定的目录中时，如果需要对数据进行处理，那么可以直接创建表，指定其存储位置 LOCATION ，同时最好配合外部表使用，防止数据误删

IMPORT TABLE

import table 表名 from "HDFS路径"
-- IMPORT TABLE 将export导出的数据直接进行导入到某个表中

import table new_bucket_table from '/out/bucket_table'

数据导出

INSERT OVERWRITE

INSERT OVERWRITE LOCAL DIRECTORY  "/usr/local/soft/hive-3.1.2/data/bucket_table" ROW FORMAT DELIMITED FIELDS TERMINATED BY ","  select * from bucket_table

HDFS get 方法

通过HDFS命令直接下载对应的文本文件信息

hive -e >

-- 该方式不适合大批量数据的导出
-- 通过该方式可以执行一个SQL语句将数据写出到某个文件中，并且注意要给表名加上库名称
hive -e "select * from bigdata.bucket_table" > bucket_table.txt

hive -f >

-- 通过-f选项，执行一个SQL脚本的文件  该方式在一些比较原始的开发环境中经常使用  
hive -f select.sql > bucket_table2.txt

export方法

export table 表名 to "HDFS路径"
-- 直接将表中的元数据信息和对应的表目录下所有的目录和文件复制到对应HDFS生成目录中 
export table bucket_table to '/out/bucket_table';

基本数据类型

整型

TINYINT — 微整型，只占用1个字节，只能存储0-255的整数。 SMALLINT– 小整型，占用2个字节，存储范围–32768 到 32767。 INT– 整型，占用4个字节，存储范围-2147483648到2147483647。 BIGINT– 长整型，占用8个字节，存储范围-2^63到2^63-1。

drop table SMALLINT_T;
create table SMALLINT_T(
a TINYINT,
b SMALLINT,
c INT,
d BIGINT   
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ",";

insert into table SMALLINT_T values (10,20,30,40);
insert into table SMALLINT_T values (256,20,30,40);

-- 对于数据插入时，如果存在有不符合规范的数据时，会使用默认数据0替代
-- 对于通过展示HDFS中已有的数据时，不符合规范数据，会展示为NULL

布尔型BOOLEAN — TRUE/FALSE

浮点型FLOAT– 单精度浮点数。 DOUBLE– 双精度浮点数。字符串型STRING– 不设定长度

create table other_t(
a BOOLEAN,
b FLOAT,
c DOUBLE,
d STRING   
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ",";
-- 对于布尔类型，其数据形式为小写的true或false

日期类型

1，Timestamp 格式“YYYY-MM-DD HH：MM：SS.fffffffff”（9位小数位精度） 2，Date DATE值描述特定的年/月/日，格式为YYYY-MM-DD。

create table time_tbl(
a Timestamp,
b Date 
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ",";
-- 对于date类型可以包容 Timestamp 而Timestamp对于时间格式要求比较严格

复杂数据类型

ARRAY 使用

create table stu_arr (
name String,
scores array<int>
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
COLLECTION ITEMS TERMINATED BY ',';


zhangsan 70,80,90

load data local inpath '/usr/local/soft/hive-3.1.2/data/arr.txt' into table stu_arr;

-- 数组数据取出 需要通过下标进行取值
select name,scores,scores[0] from stu_arr

select name,scores,scores[4],size(scores) from stu_arr  -- 取值没有则返回null

MAP使用

create table stu_map (
name String,
scores map<String,int>
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS TERMINATED BY ':'
;

zhangsan    yingyu:70,yuwen:80,shuxue:90

-- 取值

select name,scores['yingyu'] from stu_map
select name,scores['yingyu'],size(scores) from stu_map -- 计算Map中的元素数量

Struct的使用

create table stu_struct (
name String,
scores struct<object_name:String,score:int>
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
COLLECTION ITEMS TERMINATED BY ','
;

load data local inpath '/usr/local/soft/hive-3.1.2/data/struct.txt' into table stu_struct;

-- 取值
select name,scores.object_name,scores.score  from stu_struct

类型转换

-- HIVE中对于String类型的数据，可以在计算时直接转换成 int 类型再做计算

select cast(1 as float),1;
select cast(1.634 as int);

select cast('2023-10-09 15:49:30' as date);

简单查询

注意：写SQL时需要按照SQL的执行顺序进行编写

过滤

having where

-- 需求：对各班级中各年龄段的学生人数进行过滤 取出大于50 
select
clazz
,new_age
,count(*) as num
FROM student
GROUP BY clazz,new_age
having num > 20;

-- having是在 GROUP BY 之后对数据进行过滤
-- 以上语句的数据处理逻辑：


1500100982  钱若芹 21  女   理科二班
1500100983  左傲薇 22  女   理科四班
1500100984  殷景逸 23  男   理科二班
1500100985  申飞珍 21  女   文科一班
1500100986  左天曼 22  女   文科一班
1500100987  双昆杰 24  男   文科四班
1500100988  余鸿云 22  男   文科六班
1500100989  柏盼香 24  女   理科六班
1500100990  扈旭鹏 23  男   理科三班
1500100991  冉飞昂 22  男   理科一班
1500100992  莫运盛 24  男   理科六班
1500100993  衡从蕾 21  女   理科二班
1500100994  相凌青 24  女   理科四班
1500100995  寿芷卉 24  女   理科五班
1500100996  厉运凡 24  男   文科三班
1500100997  陶敬曦 21  男   理科六班
1500100998  容昆宇 22  男   理科四班
1500100999  钟绮晴 23  女   文科五班
1500101000  符瑞渊 23  男   理科六班

理科二班 21
1500100982  钱若芹 21  女   理科二班
1500100993  衡从蕾 21  女   理科二班
...
之后统计Value的个数，之后再去对个数进行过滤

-- on 做数据过滤时，需要搭配Join使用

-- 创建并加载 dept 表和 emp表

select
*
FROM dept  JOIN emp on dept.deptno = emp.deptno

select
*
FROM dept JOIN emp on dept.deptno = emp.deptno

-- 对给定的范围进行过滤
-- 其范围是左右包含的 
select
*
from emp where sal  2450 and 3000

in / not in

select
*
from emp where deptno in (20,30)

select
*
from emp where deptno in (select deptno from dept where deptno > 10);

-- 通过in 可以判断某个数据是否在 对应的集合中，其中集合可以使用 () 给定固定值，也可以使用 select 语句 

-- not in 尝试

is NULL/IS NOT NULL

-- 对于NULL值进行判断 空值和0 都不是NULL
select
*
from emp where bonus is null; 

-- IS NOT NULL 尝试

< /> / <> / = / !=

select
*
from emp where sal >= 2450 and sal <= 3000

select
*
from emp where deptno <> 10

select
*
from emp where deptno != 10

关联

join

-- 内连接
-- 如果关联时，不添加关联条件，那么会产生笛卡尔积 

-- join 尝试


select 
*
from emp t1,dept t2 where t1.deptno=t2.deptno  -- 不推荐使用该方式

left join / right join

-- 左右连接
-- 左右连接和内连接的区别？
-- 对于左连接那么以left join左边的表为主表 关联右边的表，关联到那么保存右边的数据，关联不到会保留左边的表数据 对于右表未关联到的数据使用 NULL进行替代

select
*
FROM dept t1 left JOIN emp t2 ON t1.deptno=t2.deptno

注意：在实际开发过程中对于SQL语句中的select 不能使用 *

分组 GROUP BY

sql 中如果有 GROUP BY 对其中部分字段进行分组，那么在select 选择字段时，只能在分组字段中进行选择，同时使用聚合函数对其他字段进行操作

排序

ORDER BY

-- 全局排序
-- 需求：对于emp表中的部门和薪资进行排序  部门以升序排序  薪资以降序排序
SELECT
ename
,deptno
,sal
FROM emp
ORDER BY deptno,sal desc

-- desc 表示为一个降序

注意：小技巧字段名称前加,

sort by

SELECT
ename
,deptno
,sal
FROM emp
SORT BY deptno,sal desc

-- set mapreduce.job.reduces; 可以查看当前Reduce数量的默认值 如果为-1 表示数量由任务根据数据量来设定 
-- set mapreduce.job.reduces=2; 模拟数据量较多，产生两个Reduce 
-- 通过再次执行上述的SQL语句可以看到 整个结果是无序的 

INSERT OVERWRITE LOCAL DIRECTORY  "/usr/local/soft/hive-3.1.2/data/sortby" ROW FORMAT DELIMITED FIELDS TERMINATED BY "," 
SELECT
ename
,deptno
,sal
FROM emp
SORT BY deptno,sal desc

-- 通过将查询的结果保存成一个目录，查看有两个结果文件，在两个结果文件中分别是有序的
-- 对于ORDER BY 是一个全局排序函数，但是使用的Reduce始终是一个，所以当数据量较大的情况下，Reduce的数据处理量非常大 容易导致任务执行失败

distribute by + sort by

-- 对于sort by 来说单独使用时，如果Reduce数量较多，那么最终结果是无序的 
--  所以需要使用distribute by 

SELECT
ename
,deptno
,sal
FROM emp
distribute by deptno SORT BY deptno,sal desc
-- distribute by 是指定分区列 (MR中对于数据分发到Reduce中是按照数据的分区来决定的，而分区计算是依据Key来确定的，所以可以通过 distribute by 指定其按照固定列信息做数据的分区  相同部门编号的数据都会在一个分区中 ->  一个部门中所有的数据都在一个Reduce中处理了，排序后部门内部sal有序，之后再对部门进行排序即可)

cluster by

SELECT
ename
,deptno
,sal
FROM emp
cluster by deptno 

cluster by deptno  => distribute by deptno SORT BY deptno

去重

-- distinct
-- GROUP BY


set mapreduce.job.reduces=-1;

set mapreduce.job.reduces=2;
SELECT
distinct id
FROM student_text
-- Time taken: 17.195 seconds

SELECT
id
FROM student_text
group by id
--Time taken: 16.111 seconds

拼接

UNION：去重
UNION ALL：不去重
-- UNION ALL是不会启动Reduce过程 效率高


select
*
from emp 
UNION 
select
*
from emp 

select
*
from emp 
UNION ALL
select
*
from emp

with as

-- with as 用法可以将某个查询结果保存成一个临时表 主要用于提升数据的处理效率，以及SQL语法简洁

-- 语法：
with 表名1 AS (
 SELECT 查询语句
)
[, 表名2 AS (
  SELECT 查询语句
)]
SELECT 
...
FROM 表1 JOIN 表2  

-- 缺点：对于内存要求比较高，如果对应的结果数据较大，建议不要使用该方法，用CREATE table 创建一个临时表 用于存储数据


WITH  goods_id_sum_income_1804_tmp AS (
SELECT
id
,sum(income)  as sum_income -- 每个产品的月收入总和
FROM goods t1
WHERE substr(t1.dt,1,7) = '2018-04' 
GROUP BY id
)
,goods_id_sum_income_1804_join_tmp as (

SELECT
T1.max_sum_income
,T2.id
FROM (
SELECT
max(sum_income) as max_sum_income
FROM  goods_id_sum_income_1804_tmp
) T1 JOIN goods_id_sum_income_1804_tmp T2 ON T1.max_sum_income = T2.sum_income
)

SELECT
T2.*
,T1.max_sum_income
FROM goods_id_sum_income_1804_join_tmp T1 JOIN goods T2 ON T1.id = T2.id
WHERE substr(T2.dt,1,7) = '2018-04'

函数

查看函数

show functions
show functions like 模糊查询函数名
desc function + 函数名
desc function extended + 函数名查看带有样例的解释

判断函数

if函数

-- 三目运算
if(判断条件,true返回值,false返回值)

select if(1=2,1,0);

CASE 函数重点

-- CASE 函数 判断函数
-- CASE WHEN 判断条件1 THEN 判断条件1为True的返回值 
--      WHEN 判断条件2 THEN 判断条件2为True的返回值 
--      ...
--      ELSE 以上条件都不满足情况下，返回值  END

-- 对于 goods_id_sum_income_1804 表中的总收入 进行划分等级 -> 23000 低收入 23000-25000 中等收入 25000 -> 高收入

SELECT
*
,CASE WHEN sum_income <= 23000 THEN '低收入'
WHEN sum_income > 23000 AND sum_income <= 25000 THEN '中等收入'
ELSE '高收入' END flag_income
FROM goods_id_sum_income_1804

COALESCE

-- 返回给定的N个列中，第一个不为NULL的结果数据
create table test_tbl(
a STRING,
b STRING,
c STRING,
d STRING
)
insert into table test_tbl values ('1',NULL,NULL,NULL),(NULL,'2','3',NULL),(NULL,NULL,'3',NULL),(NULL,NULL,NULL,'4');

-- 需求：返回该4列中，第一个不为NULL的列 


SELECT 
COALESCE(a,b,c,d)
FROM test_tbl

NVL

-- 给定列中如果出现NULL值则返回默认值，否则返回具体列数据
-- NVL(列,默认值)
SELECT
NVL(c,0)
FROM test_tbl

日期函数

1 表示必须记住 2表示熟悉 3表示有印象

from_unixtime 2转化时间戳为当前设置的时间格式

select from_unixtime(13230843,'yyyyMMdd')

unix_timestamp 2 需求：取出当前的系统时间按小时 2023-10-10 11 格式化

unix_timestamp 2 可以将当前时间转化为时间戳，将指定日期时间转为时间戳

-- 将 20111207 13:01:03 转换成标准的时间格式 yyyy-MM-dd HH:mm:ss
select unix_timestamp('20111207 13:01:03','yyyyMMdd HH:mm:ss')
-- 自己完成
select from_unixtime(1323262863,'yyyy-MM-dd HH:mm:ss')

to_date 1返回2011-12-08 10:03:01 中2011-12-08的部分

select to_date('2011-12-08 10:03:01')

year 1返回日期中的年份

select year('2023-10-10 20:30:05')

month 2=> 使用字符串切分代替返回日期中的月份

select month('2023-10-10 20:30:05')

day hour minute second3返回天，小时，分钟，秒
weekofyear 3返回给定的日期是第几周
datediff 1日期A与日期B之间相差的天数

select datediff('2023-10-10','1978-09-03')

date_add date_sub 1日期A加上(减去）B天之后的日期

select date_add('2023-10-10',10)
select data_sub('2023-10-10',10)

current_date 1获取当天时间

select current_date()

months_between 3
last_day 1给定月的最后一天

select last_day("2023-10-10")

trunc 1 返回当年或者当月的第一天

select trunc("2023-10-10",'YY')--返回当年的第一天
select trunc("2023-10-10",'MM')--返回当月的第一天

字符串函数

length 2返回字符串的长度

select length('BAEKHYUN')

reverse 2 反转函数

select reverse('BAEKHYUN')
-- 需求： 对于一个不固定长度的字符串 www.xxx.gmail.com12345678 其中 1-8是用户id 其长度固定，那么如何通过SQL取出ID ?
-- 自己完成
select
reverse(
substr(reverse('www.xxx.gmail.com12345678'),1,8));

concat 1拼接字符串的结果

select concat('a','b,'c','d')

concat_ws 1返回带指定分隔符的字符串

select concat_ws('分隔符','a',table.字段)

substr,substring 1字符串截断

select substr('字符串','起始位置','长度')(从1开始)

upper,ucase 3大写
trim 2 去除字符串两边空格

select trim(' abd ')

rtrim ltrim 3去除左右两边的空格
regexp_replace 2将字符串A的符合正则的B部分替换为c(有可能需要转义符)

select regexp_replace('ahjsdkhbjficsjdf','hjsdkh|jfi|sjdf','')
--结果abc

regexp_extract 2返回给定字符串的正则
parse_url 3
get_json_object 2解析json的字符串json_string,返回path指定的内容。如果输入的json字符串无效，那么返回

NULL。
ascii 3
split 1以给定字符切分字符串

select split('abtcdtefgtsf','t')
['ab','cd','efg','sf']

find_in_set 2找出指定字符串第一次出现的位置n
regexp 2根据正则匹配的结果返回 true 或者 false
substring_index 2给定字符串与切分符号n取出n:

集合函数

COLLECT_LIST

-- 将给定列中的数据汇集到一个集合中，通常搭配GROUP By使用

-- 需求：将emp表中相同部门的员工编号 汇集到一个集合中

SELECT
deptno
,collect_list(empno) as empno_list
FROM emp
GROUP BY deptno

SELECT
collect_list(deptno) as all_deptno
FROM emp



SELECT
deptno
-- 使用concat_ws可以对集合中的数据进行转换成字符串，但是要求 集合中的元素必须是一个String
,concat_ws("|",collect_list(cast(empno as string))) as empno_list
FROM emp
GROUP BY deptno

COLLECT_SET

-- SET 是一个去重无序的方式
-- 和COLLECT_LIST相比 添加了一个去重功能，其他都一样
SELECT
collect_set(deptno) as all_deptno
FROM emp

行列互换

多行转一行


-- 需求：需要对学生各班级中的学生ID和学生姓名进行汇集，将学生ID和学生姓名使用|进行拼接，不同学生间使用，进行分割
--     结果样例： 理科四班 1500100998|容昆宇,1500100994|相凌青,...
SELECT
clazz
,concat_ws(',',collect_list(concat(id,'|',name))) as concat_res
FROM student
GROUP BY clazz

一行转多行


create table words(
 word string
);
insert into table words values("hello,world"),("hello,hive"),("hello,hadoop");

WITH split_tbl AS (
SELECT
split(word,',')[0] as split_words
FROM words
UNION ALL 
SELECT
split(word,',')[1] as split_words
FROM words
)
SELECT
split_words
,count(*)
FROM split_tbl
GROUP BY split_words

insert into table words values("hello,java,python");

-- EXPLODE 可以对一个集合进行做拓展，将集合中的多个数据，转换成多行数据

SELECT
explode(split(word,',')) as split_explode
FROM words

["hello","world"]
转换如下：
hello
world

-- 对于电影数据进行转换
CREATE TABLE movies (
name String,
type String
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ","

load data local inpath '/usr/local/soft/hive-3.1.2/data/moive.txt' into table movies;

犯罪/剧情

犯罪
剧情

SELECT
name
,explode(split(type,'/')) as explode_type
FROM movies

肖申克的救赎  犯罪/剧情
肖申克的救赎  犯罪
肖申克的救赎  剧情

-- LATERAL VIEW EXPLODE(Array/Map)  表名 as 列1[,列2]

SELECT
name
,view.explode_type
FROM movies LATERAL VIEW EXPLODE(split(type,'/')) view as explode_type
-- LATERAL VIEW 表示为一个侧写表 
-- EXPLODE(split(type,'/')) 作为表中的数据  
-- view 表示为测斜表的 别名
-- explode_type 表中列名称 可以在select中进行选择该列


肖申克的救赎  犯罪/剧情  =>   explode_type
                           犯罪
                           剧情
肖申克的救赎 犯罪
肖申克的救赎 剧情
-- 关联过程对每一行都是一个笛卡尔积
-- EXPLODE中可以给定Array或者Map

窗口函数

非常重要

排名

row_number函数


-- 需求：统计各班级中学生分数排名前10的学生，要求展示明细数据
CREATE TABLE scores(
id STRING,
object_id STRING,
score int
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ",";

load data local inpath '/usr/local/soft/hive-3.1.2/data/score.txt' into table scores;

Create table student_score as
SELECT
T2.*
,T1.sum_score
FROM(
SELECT
id
,sum(score) as sum_score
FROM scores
GROUP BY id
)T1 JOIN student T2 ON T1.id = T2.id


-- Mysql思路 

SELECT
T3.*
FROM (
SELECT
T1.*
,(SELECT count(*) FROM student_score T2 WHERE T1.clazz = T2.clazz and T2.sum_score > T1.sum_score) as pm
FROM student_score T1 
)T3 where T3.pm <= 10
主
1500100991  冉飞昂 22  男   理科一班    76  
1500100992  莫运盛 24  男   理科六班    407
1500100993  衡从蕾 21  女   理科二班    386
1500100994  相凌青 24  女   理科四班    310
1500100995  寿芷卉 24  女   理科五班    227
1500100996  厉运凡 24  男   文科三班    355
1500100997  陶敬曦 21  男   理科六班    293
1500100998  容昆宇 22  男   理科四班    398
1500100999  钟绮晴 23  女   文科五班    371
1500101000  符瑞渊 23  男   理科六班    379

从
1500100991  冉飞昂 22  男   理科一班    76
1500100992  莫运盛 24  男   理科六班    407
1500100993  衡从蕾 21  女   理科二班    386
1500100994  相凌青 24  女   理科四班    310
1500100995  寿芷卉 24  女   理科五班    227
1500100996  厉运凡 24  男   文科三班    355
1500100997  陶敬曦 21  男   理科六班    293
1500100998  容昆宇 22  男   理科四班    398
1500100999  钟绮晴 23  女   文科五班    371
1500101000  符瑞渊 23  男   理科六班    379

-- Time taken: 61.205 seconds 消耗时间

SELECT
T1.*
FROM (
SELECT
*
,row_number() over(partition by clazz order by sum_score desc) as pm 
FROM student_score
)T1 WHERE T1.pm <= 10
-- Time taken: 19.562 seconds

rank/dense_rank

over可以开启一个窗口，窗口就相当于是当前表的一个副本

如果over中没有添加任何内容，表示整个表的大小

partition by clazz 可以将窗口中的数据进行限制大小依据是当前计算行中的班级数据和窗口中对应班级列数据相同

order by sum_score 对当前窗口中的数据按照指定列进行排序

row_number 可以根据当前行的数据在窗口中找到对应下标的位置就可以得到其排名


SELECT
*
,row_number() over(partition by clazz order by sum_score desc) as row_pm 
,dense_rank() over(partition by clazz order by sum_score desc) as dense_pm
,rank()  over(partition by clazz order by sum_score desc) rank_pm
FROM student_score
 
1500100635  蓬怀绿 23  女   理科四班    534 1   1   1
1500100590  逄中震 24  男   理科四班    530 2   2   2
1500100939  耿智杰 23  男   理科四班    530 3   2   2
1500100501  松迎梅 22  女   理科四班    515 4   3   4
1500100853  林鸿朗 24  男   理科四班    515 5   3   4

-- 三者的不同点
① row_number() 排名连续 不重复
② dense_rank() 排名连续 且重复
③ rank() 排名不连续 且重复


SELECT
*
,row_number() over(order by sum_score desc) as row_pm 
FROM student_score

percent_rank/ cume_dist/NTILE

--percent_rank：(rank的结果-1)/(分区内数据的个数-1) 看排名在这个分区中的占比
-- cume_dist：计算某个窗口或分区中某个值的累积分布。
--假定升序排序，则使用以下公式确定累积分布：
--  小于等于当前值x的行数 / 窗口或partition分区内的总行数。其中，x 等于 order by 子句中指定的列的当前行中的值。

--NTILE(n)：对分区内数据再分成n组，然后打上组号
SELECT
*
,rank()  over(partition by clazz order by sum_score desc) rank_pm
,percent_rank() over(partition by clazz order by sum_score desc) as percent_rank
,cume_dist() over(partition by clazz order by sum_score desc) as cume_dist
,NTILE(3)  over(partition by clazz order by sum_score desc) as NTILE
FROM student_score

统计窗口

count sum max min avg

-- 需求：要求求出每个班级学生的总人数，并且展示班级学生明细信息

SELECT
T2.*
,T1.num
FROM (
SELECT
clazz
,count(*) as num
FROM student
GROUP BY clazz
) T1 JOIN student T2 ON T1.clazz = T2.clazz


-- 使用窗口函数

SELECT
*
,count(*) over(partition by clazz) as num
,sum(sum_score) over(partition by clazz) as sum_score
,max(sum_score) over(partition by clazz) as max_score
,min(sum_score)  over(partition by clazz) as min_score
,round(avg(sum_score) over(partition by clazz),2) as avg_score
FROM student_score


-- count 添加 order by 可以实现rank排序效果
SELECT
*
,count(*) over(partition by clazz order by sum_score desc) as num
FROM student_score

-- sum 添加 order by 可以实现累计求和的效果
SELECT
*
,sum(sum_score) over(partition by clazz order by sum_score desc) as num
FROM student_score

-- max 和 min 都是根据累计排序后 求最大值和最小值 
SELECT
*
,max(sum_score) over(partition by clazz order by sum_score) as num
FROM student_score


-- avg 添加 order by 可以实现累计求平均值的效果
SELECT
*
,avg(sum_score) over(partition by clazz order by sum_score) as num
FROM student_score

窗口帧

作用是用于调整窗口大小

(ROWS | RANGE) BETWEEN (UNBOUNDED | [num]) PRECEDING AND ( CURRENT ROW ）| (UNBOUNDED | [num]) FOLLOWING)

UNBOUNDED ：表示没有限制

num：具体行值或数据值

PRECEDING: 往前取

CURRENT ROW : 表示当前行

FOLLOWING：往后取值

-- 方式：
-- 1.按行限制窗口大小
--  ROWS BETWEEN num PRECEDING AND CURRENT ROW =>      往前取 num 行到当前行结束  
--  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW => 往前不限制行数 到 当前行结束
--  ROWS BETWEEN num PRECEDING AND num FOLLOWING => 往前 num行 往后 num 行 


--需求：对于各班级学生成绩按倒序排序，再取出前3名和后3名学生的平均分

SELECT
*
,avg(sum_score) over(partition by clazz order by sum_score ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING) as  avg_sum_score
FROM student_score

-- 2.按值限制窗口大小
-- 对于range来说必须要使用 order by 并对order by中的列计算其范围 

-- 需求：对于各班级学生成绩按倒序排序,再取出比当前学生分数多10分和少10分的学生人数个数

SELECT
*
,count(*) over(partition by clazz order by sum_score RANGE BETWEEN 10 PRECEDING AND 10 FOLLOWING) as  avg_sum_score
FROM student_score

窗口取行

Lag/LEAD

-- LAG(col,n,default)：往前第n行数据,default如果参数不给，那么默认返回NULL
-- LEAD(col,n,default)：往后第n行数据


-- 学生成绩数据中，对前一名学生的成绩和后一名学生成绩进行做比较 计算成绩差

SELECT
T1.*
,T1.lag_score - T1.sum_score as score_diff
FROM(
SELECT
*
,row_number() over(partition by clazz order by sum_score desc) as pm
,lag(sum_score,1,0) over(partition by clazz order by sum_score desc) as lag_score
FROM student_score
)T1


SELECT
T1.*
,T1.sum_score - T1.lead_score as score_diff
FROM(
SELECT
*
,row_number() over(partition by clazz order by sum_score desc) as pm
,LEAD(sum_score,2,0) over(partition by clazz order by sum_score desc) as lead_score
FROM student_score
)T1

FIRST_VALUE /LAST_VALUE

-- FIRST_VALUE：取分组内排序后，截止到当前行，第一个值
-- LAST_VALUE：取分组内排序后，截止到当前行，最后一个值，对于并列的排名，取最后一个


SELECT
*
,FIRST_VALUE(sum_score) over(partition by clazz order by sum_score desc) as first_value
,LAST_VALUE(sum_score) over(partition by clazz order by sum_score desc) as last_value
FROM student_score

卡口流量分析

需求1：查询每月的设备及其违法总数

CREATE TABLE veh_pass(
sbbh STRING,
wfsj STRING,
num int
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ",";
load data local inpath '/usr/local/soft/hive-3.1.2/data/emp.txt' into table emp;

-- 分析：
--    

SELECT
substr(wfsj,1,7)
,sbbh
,sum(num) as sum_num
FROM veh_pass
GROUP BY substr(wfsj,1,7),sbbh

需求2：查询所有流量明细及所有设备月流量总额

-- 方式1：GROUP BY 


SELECT
T2.*
,T1.sum_num
FROM(
SELECT
substr(wfsj,1,7) as wfsj_month
,sbbh
,sum(num) as sum_num
FROM veh_pass
GROUP BY substr(wfsj,1,7),sbbh
)T1 JOIN veh_pass T2 ON T1.wfsj_month = substr(T2.wfsj,1,7) and T1.sbbh = T2.sbbh


with sum_veh_pass as (
SELECT
substr(wfsj,1,7) as wfsj_month
,sbbh
,sum(num) as sum_num
FROM veh_pass
GROUP BY substr(wfsj,1,7),sbbh
)

SELECT
T2.*
,T1.sum_num
FROM sum_veh_pass T1 
JOIN veh_pass T2 ON T1.wfsj_month = substr(T2.wfsj,1,7) and T1.sbbh = T2.sbbh -- 关联自身 



-- 方式2：窗口函数


SELECT
*
,sum(num) over(partition by substr(wfsj,1,7),sbbh) as sum_num
FROM veh_pass

需求3：按设备编号、日期顺序展示明细

1）从第一天开始到当前天数对流量进行累加

-- 1.按行限制窗口大小
--  ROWS BETWEEN num PRECEDING AND CURRENT ROW =>      往前取 num 行到当前行结束  
--  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW => 往前不限制行数 到 当前行结束
--  ROWS BETWEEN num PRECEDING AND num FOLLOWING => 往前 num行 往后 num 行 



SELECT
*
,sum(num) over(partition by sbbh order by wfsj ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as sum_num
FROM veh_pass


SELECT
*
,sum(num) over(partition by sbbh order by wfsj) as sum_num
FROM veh_pass

2）昨天与当前天流量累加


-- 分析：
--   对于日期可以表示成一个任意的日期作为开始时间，经过了N个时间段，所得到的结果
--   可以对表中的日期进行做时间跨度的计算 假设以 2022-06-01 作为开始时间 计算时间差，那么昨天就相当于比当前时间差少1天  
--   为什么要使用时间跨度 -> range 语法中要求给定一个数值进行做范围的划分 

SELECT
*
,sum(num) over(partition by sbbh order by datediff(wfsj,'2022-06-01') RANGE BETWEEN 1 PRECEDING AND CURRENT ROW )
FROM veh_pass

-- 1 PRECEDING 表示对于时间差进行 减1
-- CURRENT ROW 表示取到当前行位置 
-- 1 FOLLOWING 表示对于时间差进行 加1

3）当前天数的前一天与后一天流量累加

SELECT
*
,sum(num) over(partition by sbbh order by datediff(wfsj,'2022-06-01') RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING )
FROM veh_pass

4）当前天与下一天的累加和

SELECT
*
,sum(num) over(partition by sbbh order by datediff(wfsj,'2022-06-01') RANGE BETWEEN CURRENT ROW AND 1 FOLLOWING )
FROM veh_pass

5）当前天数与之后所有天流量累加和

SELECT
*
,sum(num) over(partition by sbbh order by wfsj ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) as sum_num
FROM veh_pass

需求4：查询每个设备编号上次有数据日期和下一次有数据日期 -->

SELECT
sbbh
,wfsj
,num
,datediff(lead_wfsj,wfsj) as dt_diff
FROM(
SELECT
*
,lag(wfsj,1,wfsj) over(partition by sbbh order by wfsj) as lag_wfsj
,lead(wfsj,1,wfsj) over(partition by sbbh order by wfsj) as lead_wfsj
FROM veh_pass
)T1

案例

设备异常

-- 数据使用base_vio_sbbh表中的数据

SELECT
T1.*
, datediff(T1.lead_wfsj,T1.wfsj) as sjc
FROM(
SELECT
*
,lead(wfsj,1,wfsj) over(partition by sbbh order by wfsj) as lead_wfsj
FROM base_vio_sbbh
)T1 


-- 箱线图 
-- 数学统计中的一种用于表现数据分布的 计算方式
-- 上四分位值：Q3  下四分位值：Q1   上下四分位跨度 IQR=(Q3-Q1)  
-- 上下边界值：上边界：1.5IQR + Q3   下边界： Q1-1.5IQR

create table base_vio_sbbh_sjc as 
SELECT
T1.*
, datediff(T1.lead_wfsj,T1.wfsj) as sjc
FROM(
SELECT
*
,lead(wfsj,1,current_date) over(partition by sbbh order by wfsj) as lead_wfsj
FROM base_vio_sbbh
)T1 


-- 通过箱线图的方式，将时间差异常的数据取出

-- 使用箱线图的要求： 
--     1.对数据进行排序 按照降序对时间差进行排序 
--     2.由于需要计算上下四分位数据，那么需要获取数据的下标，那么可以通过数据的排名来进行计算  -> 需要获取时间差的排名
--     3.根据排名 获取上下四分位的下标位置 
--     4.根据下标位置获取数据的值 对应该值就是 上下四分位值 于是可以计算出上下边界值
--     5.将大于上边界的异常数据过滤出来

create table base_vio_sbbh_sjc_Q1_Q3 as 
SELECT
*
FROM (
SELECT
T1.*
,round(cnt/4) as Q3
,round( 3 * cnt/4) as Q1
FROM(
SELECT
*
,row_number() over(partition by sbbh order by sjc desc) as pm
,count(*) over(partition by sbbh) as cnt
FROM base_vio_sbbh_sjc
) T1
) T2 WHERE T2.Q3 = T2.pm or T2.Q1=T2.pm
                                            sjc         Q3      Q1      
451000000000071113  2020-06-18  2020-06-23  5   11  43  11.0    32.0  1 
451000000000071113  2020-06-14  2020-06-15  1   32  43  11.0    32.0  0

451000000000072222  2020-06-18  2020-06-23  5   11  43  11.0    32.0
451000000000072222  2020-07-29  2020-07-30  1   32  43  11.0    32.0


-- 计算上下边界值
-- Q3+1.5(Q3-Q1)
--  对于同一个设备 要对Q3和Q1 获取到同一行之后再对数据进行计算  
--  对于一个设备 要求只存在一个上边界值  那么两行数据要求变成一行  所以需要使用 GROUP BY
--  根据实际情况，只需要获取其上边界即可 

SELECT
TT2.*
,TT1.lag_value
FROM(
SELECT
sbbh
,Q3_value + 1.5 * (Q3_value - Q1_value) lag_value
FROM(
SELECT
sbbh
,max(sjc) as Q3_value  -- 根据数据的特征来处理
,min(sjc) as Q1_value
FROM base_vio_sbbh_sjc_Q1_Q3
GROUP BY sbbh
)T1
)TT1 JOIN base_vio_sbbh_sjc TT2 ON TT1.sbbh = TT2.sbbh
WHERE TT2.sjc >  TT1.lag_value



-- 根据列之间的关系，进行计算
SELECT
sbbh
,max(if(Q3=pm,sjc,0)) as Q3_value
,min(if(Q1=pm,sjc,0)) as Q1_value
FROM base_vio_sbbh_sjc_Q1_Q3

问题：为什么不对整个表中的数据进行排序取整个表中的上边界，之后再对整个表取异常值，而针对每个设备进行取值？

不同设备的时间差数据分布情况一样，所以需要按照不同设备进行分别处理

问题：为什么乘以 1.5 ？

1.5是一个专家值，其中IQR表示一个数据中中心分布的情况，拿中间数据的跨度乘以1.5 模拟边缘数据的跨度，比正常跨度要大的数据，那么认为是异常的

连续交易

-- 方式2：
1,2019-02-08,6214.23 + 6247.32   2019-02-07  1  2019-02-06 + 1
1,2019-02-09,85.63 + 967.36      2019-02-08  1  2019-02-06 + 2
1,2019-02-10,85.69               2019-02-09  1  2019-02-06 + 3
1,2019-02-12,769.85              2019-02-10  2  2019-02-06 + 4
1,2019-02-13,943.86              2019-02-11  2  2019-02-06 + 5
1,2019-02-15,                    2019-02-12  3  2019-02-06 + 6

2,2019-02-08,6214.23 + 6247.32   2019-02-07  1
2,2019-02-09,85.63 + 967.36      2019-02-08  1
2,2019-02-10,85.69               2019-02-09  1


-- 方式3：
1   2019-02-08  12461.55   1  2019-02-07
1   2019-02-09  1052.99    2  2019-02-07
1   2019-02-10  85.69      3  2019-02-07
1   2019-02-12  769.85     4  2019-02-08
1   2019-02-13  943.86     5  2019-02-08
1   2019-02-14  538.42     6  2019-02-08
1   2019-02-15  369.76     7  2019-02-08
1   2019-02-16  369.76
1   2019-02-18  795.15
1   2019-02-19  715.65
1   2019-02-21  537.71
2   2019-02-08  12461.55
2   2019-02-09  1052.99



-- 1.统计用户连续交易的总额、
-- 对于该需求，如果要求总额，并且是对连续日期进行统计，那么怎么样将连续的日期放入一组 通过GROUP BY 对 交易额进行求SUM
--  1.怎么样判断是否是连续日期 
    -- 方式1：
        -- 对日期进行排序，拿上一行和下一行进行判断日期间隔是否为1天 虽然可以判断出连续，但是对于后续计算并不是太方便 
    -- 方式2：
        -- 对于每一个用户，给定一个连续的日期，再将交易日期减去连续日期，如果间隔天数一样，说明数据规律一样，那么就可以根据间隔天数进行分组 
        -- 对于连续日期，可以看成是一个固定的日期 加上一个连续的序列 该方式可以通过窗口函数来实现 
    -- 方式3：
        -- 将日期看成是一个天数+某个开始日期 ，那么日期的连续就变成天数数值是否连续，那么可以直接拿一个连续的数值和该天数进行比较，判断其规律是否一致，如果一致，那么就说明是连续的 


-- 方式2：

create table deal_tb(
    id string  COMMENT '用户ID'
    ,datestr string   COMMENT '交易日期'
    ,amount string COMMENT '交易额'
)row format delimited fields terminated by ',';

SELECT
id
,min(datestr) as start_date
,max(datestr) as end_date
,sum(sum_amount) as continue_sum_amount
FROM(
SELECT
*
, date_add(min_date,pm) as continue_date
, datediff(datestr,date_add(min_date,pm)) continue_flag
FROM (
SELECT
*
,min(datestr) over(partition by id) as min_date
,row_number() over(partition by id order by datestr) as pm
FROM (
SELECT
id
,datestr
,sum(amount) as sum_amount
FROM deal_tb
GROUP BY id,datestr
)T1 
)TT1
)TT2 GROUP BY id,continue_flag

-- 方式3
CREATE TABLE continu_res as 
SELECT
TT1.id
,min(datestr) as start_date
,max(datestr) as end_date
,sum(sum_amount) as continue_sum_amount
,count(*) as continue_cnt
FROM (
SELECT
*
,row_number() over(partition by id order by datestr) as pm
,date_sub(datestr,row_number() over(partition by id order by datestr)) as flag_date 
FROM (
SELECT
id
,datestr
,sum(amount) as sum_amount
FROM deal_tb
GROUP BY id,datestr
)T1
) TT1 GROUP BY TT1.id,TT1.flag_date

-- 4.间隔天数
-- 求两个连续交易区间的间隔多少天没有交易
1   2019-02-08  2019-02-10  13600.23    3          2019-02-12  3
1   2019-02-12  2019-02-16  2991.6500000000005  5  2019-02-18  3
1   2019-02-18  2019-02-19  1510.8  2
1   2019-02-21  2019-02-21  537.71  1

2   2019-02-08  2019-02-10  13600.23    3
2   2019-02-12  2019-02-15  3026.6499999999996  4
2   2019-02-18  2019-02-19  1510.8  2
2   2019-02-21  2019-02-21  537.71  1

3   2019-02-08  2019-02-10  13600.23    3
3   2019-02-12  2019-02-16  2730.04 5
3   2019-02-18  2019-02-19  1510.8  2
3   2019-02-21  2019-02-21  537.71  1


SELECT
*
,lead(start_date,1,current_date) over(partition by id) next_date
,datediff(lead(start_date,1,current_date) over(partition by id),end_date) as sjc
FROM continu_res

自定义函数

UDF

按行读取数据，一行执行一次，并返回一行结果 substr
UDTF

按行读取数据，一行执行一次函数，并返回多行结果 exploede
UDAF

按行读取数据，多行执行一次函数，并返回一行结果 sum

<dependencies>
        <dependency>
            <groupId>org.apache.hive</groupId>
            <artifactId>hive-exec</artifactId>
            <version>3.1.2</version>
        </dependency>
</dependencies>

HIVE 优化

1.分区分桶

-- 对于大批量数据的存储和查询可以使用分区分桶方式
-- 分区：
--     使用分区表：
            1.需要构建分区表和分区字段 分区字段选择：选择后续业务中经常能被过滤的数据字段，并且该字段的数据分布较为均匀 同时通常分区字段为一个时间字段 时间的维护为天 月 年 小时
            2.对于分区表做查询时，需要先去查看其分区信息，之后在SELECT的WHERE语句中对分区字段进行过滤 避免全表扫描，提高加载速度 
            3.对分区表做数据插入时，如果存在有错误数据插入时，直接将对应分区使用最新的正确数据做覆盖写入即可
--     使用分桶表：
            1.构建分桶表时，需要选择分桶字段，通常是选择数值列，对数据值列中的数据进行取余计算得到多个分桶结果（多个文件） 同时分桶字段通常也是业务中经常过滤的字段  分桶表通常可以和分区表一起使用
            2.对于分桶表做数据查询时，选择分桶字段，在WHERE语句中进行过滤，避免全表扫描

2.使用外部表

-- 外部表：
--      在表数据删除时，只会删除其元数据信息，而不会删除HDFS中的具体数据 
--      应用场景：
--             如果作为源表(从其他业务系统中抽取过来的数据所存放的表)，一般情况下需要将其设置成外部表形式

3.选择适当的文件压缩格式

-- HIVE中的表格式： 
--    对于源表数据通常使用TextFile进行数据的保存，方便后续的数据排查
--    对于计算后的结果数据，通常使用ORCFile对数据进行保存，其格式压缩效率和查询效率较高

4.命名规范

1.库名称

由于不同数据库中存储的数据类型是不一样的，所以需要给不同类型取对应的名称

如 ods dwd dws ods 等。根据数据功能进行划分

2.表名称

① 所有字母都需要使用小写，不同单词（标记）之间使用下划线分割

② 命名组成

1、使用库名称作为开头

2、对于数据的描述信息，根据不同的业务选择不同的标记，根据具体公司业务规范来制定

3、如果当前表是一个分区表那么使用 pt 标记

4、对于统计结果表的统计维度使用 day year 进行标记

5、对于中间表或者临时表等类型表使用对应的标记信息如中间表 mid 临时表 tmp

3.字段命名

① 对于字段通常使用业务系统中的字段名

② 字段名称要求小写用于表示一列中数据内容使用业务名称

③ 字段需要加上注释信息
5.数据分层，表分离，但是也不要分的太散

1.数据分层

对于不同的功能，对数据类型进行划分，分别存放在不同的数据库中方便业务管理

2.表分离

对于业务系统中获取的表信息，将对应的大表字段进行拆分，可以减少数据的冗余

同时对于冗余字段，如果该部分字段在后续的业务查询中，不被使用到，那么可以将该部分数据拆分成多个表，在后续的业务查询中，直接选择非冗余字段表即可提高查询效率

注意：表不要分的过于分散，过于分散在做数据查询时，会做大量的JOIN操作，同时会产生Shuffle过程，造成大量的网络IO，导致执行较慢
6.分区裁剪 where过滤，先过滤，后join

对于分区表需要先对分区字段进行过滤，将过滤后的结果作为一个字查询，再去关联其他表中的数据

表1 分区字段 dt 取10月份数据表2 num字段要求过滤大于200

FROM 表1 T1 JOIN 表2 T2 ON T1.id = T2.id

WHERE substr(T1.dt,1,7) = '2023-10' and T2.num > 200

优化：

FROM (SELECT * FROM 表1 WHERE substr(dt,1,7) = '2023-10' ) T1

JOIN (SELECT * FROM 表2 WHERE num > 200) T2 ON T1.id = T2.id

对于优化后的SQL写法可以加载部分数据后，再对数据进行做关联操作，减少Shuffle的数据量

7.mapjoin

-- 使用MapJOIN的前提 ：必须是大表和小表进行关联 


1）设置自动选择Mapjoin
set hive.auto.convert.join = true; 默认为true
（2）大表小表的阈值设置（默认25M以下认为是小表）：
set hive.mapjoin.smalltable.filesize = 25000000;

-- 当两张表进行关联时，那么默认会开启mapJoin，同时如果小表数据量达到25M，那么会使用mapJoin

explain 
SELECT
*
FROM scores T1 JOIN student T2 ON T1.id = T2.id

-- explain 命令可以查看SQL的执行过程 
-- set hive.auto.convert.join = false; 当关闭自动设置mapjoin后，通过查看其执行计划，使用的是 Merge Join 
-- 在MapReduce执行引擎中，还可以看到当前加载表的数据量 
-- 如果当一个表的大小为 30M 左右，那么可以通过调整阈值来自动开启MapJoin


explain 
SELECT
/*+MapJOIN(T2)*/
*
FROM scores T1 JOIN student T2 ON T1.id = T2.id
-- 在MapReduce引擎中可以通过 hint语法 在执行过程添加描述信息，指定mapjoin对应的表
---   在Tez框架中没有生效

8.合并小文件

1）在map执行前合并小文件，减少map数：CombineHiveInputFormat 具有对小文件进行合并的功能（系统默认的格式）。HiveInputFormat没有对小文件合并功能。
set hive.input.format = org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;
2）在Map-Reduce的任务结束时合并小文件的设置：
在map-only任务结束时合并小文件，默认true
SET hive.merge.mapfiles = true;
在map-reduce任务结束时合并小文件，默认false
SET hive.merge.mapredfiles = true;
合并文件的大小，默认256M
SET hive.merge.size.per.task = 268435456;
当输出文件的平均大小小于该值时，启动一个独立的map-reduce任务进行文件merge
SET hive.merge.smallfiles.avgsize = 128000000;

-- 注意：
--    在其他框架中，也会使用HIVE中的结果表，但是对于其他框架，并没有合并小文件的自动优化，所以这时，就需要我们在HIVE中对其进行优化
--    对于HIVE来说，可以通过查询一张表的数据，将该表数据放入至另外一个表中，达到合并小文件的功能

9.排序优化

-- 全局排序
    对于全局排序，通常使用order by 但是如果数据量较多情况下，order by中结果有可能运行不出来，或者执行较慢 
-- 分区排序
    对于分区排序，可以使用多个Reduce，通过distribute by + sort by 对要排序的多列进行先分区，再对分区内的数据进行排序得到最终结果

10.数据倾斜优化

原因:
    在SQL的执行过程中，存在有GROUP BY / JOIN 等操作，那么会产生Shuffle过程，将数据分发到多个Reduce中进行处理，但是每个Reduce中分配的数据并不一定均匀，那么这时每个Reduce执行时间不一致，导致有长尾现象（部分任务执行完，少部分任务依旧在执行） 整体任务执行较慢。

表现：
    在SQL执行过程中，查看Yarn的执行过程，找到对应的ApplicationID,再查看其中的Reduce执行过程 通过Reduce的执行进度，进行判断其是否是一个数据倾斜问题
    注意：如果每个Reduce执行都比较缓慢，那么不是数据倾斜问题  
    
解决方式：
    1.过滤
        将关联过程或者聚合过程中，不需要的数据，或者产生笛卡尔积数据进行过滤 
    2.添加随机数 (二次聚合)
        在对数据进行做分组统计时，可以对分组字段进行添加随机值，将数据随机打散到多个Reduce中处理，之后再将多个Reduce的处理结果再进行一次Reduce聚合 得到最后的结果
    SQL:
   SELECT
   split(concat_id,'_')[0]
   ,sum(cnt) as sum_cnt
   FROM(
   SELECT
   concat(id,'_',(round(rand() * 100))%3) as concat_id
   ,count(*) as cnt
   FROM  table GROUP BY  concat(id,'_',(round(rand() * 100))%3)
   ) T1 GROUP BY split(concat_id,'_')[0]
   
     3.通过参数进行调整
     
     1）开启Map端聚合参数设置
        set hive.map.aggr = true
        set hive.groupby.mapaggr.checkinterval = 100000
        set hive.groupby.skewindata = true 
    通过预聚合的方式 将部分数据在Map端就进行一次Reduce的业务逻辑，这样减少Shuffle过程，同时也降低了Reduce端处理数据的压力

11.合理设置Reduce个数

-- 通过查看Yarn中的MapReduce执行过程，如果发现执行过程中Reduce都执行很慢，那么这时可以调整Reduce的数量

合理设置Reduce数
1）调整reduce个数方法一
（1）每个Reduce处理的数据量默认是256MB
hive.exec.reducers.bytes.per.reducer=256000000
（2）每个任务最大的reduce数，默认为1009
hive.exec.reducers.max=1009
（3）计算reducer数的公式
N=min(参数2，总输入数据量/参数1)

2）调整reduce个数方法二
在hadoop的mapred-default.xml文件中修改
设置每个job的Reduce个数
set mapreduce.job.reduces = N;


注意事项：reduce个数并不是越多越好
（1）过多的启动和初始化reduce也会消耗时间和资源；
（2）另外，有多少个reduce，就会有多少个输出文件，如果生成了很多个小文件，那么如果这些小文件作为下一个任务的输入，则也会出现小文件过多的问题；
在设置reduce个数的时候也需要考虑这两个原则：处理大数据量利用合适的reduce数；使单个reduce任务处理数据量大小要合适；

12.设置严格模式

Hive可以通过设置防止一些危险操作：

1）分区表不使用分区过滤将hive.strict.checks.no.partition.filter设置为true时，对于分区表，除非where语句中含有分区字段过滤条件来限制范围，否则不允许执行。换句话说，就是用户不允许扫描所有分区。进行这个限制的原因是，通常分区表都拥有非常大的数据集，而且数据增加迅速。没有进行分区限制的查询可能会消耗令人不可接受的巨大资源来处理这个表。 2）使用order by没有limit过滤将hive.strict.checks.orderby.no.limit设置为true时，对于使用了order by语句的查询，要求必须使用limit语句。因为order by为了执行排序过程会将所有的结果数据分发到同一个Reducer中进行处理，强制要求用户增加这个LIMIT语句可以防止Reducer额外执行很长一段时间。 3）笛卡尔积将hive.strict.checks.cartesian.product设置为true时，会限制笛卡尔积的查询。对关系型数据库非常了解的用户可能期望在执行JOIN查询的时候不使用ON语句而是使用where语句，这样关系数据库的执行优化器就可以高效地将WHERE语句转化成那个ON语句。不幸的是，Hive并不会执行这种优化，因此，如果表足够大，那么这个查询就会出现不可控的情况。