Hive数据库操作

创建数据库

hive的数据库置默认存放在/user/hive/warehouse目录

create database myhive; 

create database if not exists myhive; 

创建数据库-指定存储路径,location ：用来指定数据库的存放目录

create database myhive location '/myhive';

查看数据库

show databases; 

# like过滤
show databases like 'my*';

查看数据库详细信息

desc database myhive;

更多详细信息

desc database extended myhive;

切换数据库

use myhive;

删除数据库

删除一个空数据库,如果数据库下面有数据表,那么就会报错

drop database myhive;

drop database if exists myhive;

强制删除数据库,包含数据库下面的表一起删除

drop database myhive cascade;
rop database if exists myhive  cascade;

修改数据库属性

使用ALTER DATABASE命令为某个数据库的DBPROPERTIES设置键-值对属性值，来描述这个数据库的属性信息

修改数据库的属性，但是元数据信息不可更改（数据库名称以及数据库所在位置）

alter database myhive2 set dbproperties('createtime'='20220202');

Hive数据类型

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

基础数据类型

复制数据类型

Hive数据表操作

数据库编码问题

创建表出现异常：

FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:An exception was thrown while adding/validating class(es) : Column length too big for column 'PARAM_VALUE' (max = 21845); use BLOB or TEXT instead
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Column length too big for column 'PARAM_VALUE' (max = 21845); use BLOB or TEXT instead

异常解决：数据库一般默认编码是utf-8或utfmb4，修改hive数据库编码位latin1即可

语法格式

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]

CREATE TABLE

创建指定名字的表。如果相同名字的表已存在，则抛出异常；可以用IF NOT EXISTS 忽略这个异常。

EXTERNAL

EXTERNAL关键字可以创建一个外部表，在建表的同时指定一个指向实际数据的路径LOCATION

Hive 创建内部表时，会将数据移动到数据仓库指向的路径

若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。

在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。

COMMENT

COMMENT后面的字符串是给表字段或者表内容添加注释说明的，是为了后期的维护，实际开发都是必须要加COMMENT的。

PARTITIONED BY

是给表做分区，决定了表是否是分区表。Hive中所谓分区表就是将表里新增加一个字段，就是分区的名字，这样在操作表中的数据时，可以按分区字段进行过滤。

CLUSTERED BY

对于每一个表（table）或者分区， Hive可以进一步组织成桶，桶是更为细粒度的数据范围划分。Hive也针对某一列进行桶的组织。Hive采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中

SORTED BY

对表的数据按某个字段进行排序

ROW FORMAT

指定表存储中各列的划分格式，默认是逗号分隔符，还可以指定其他列的分隔符。如： row format delimited fields terminated by '\t'

STORED AS

STORED AS  SEQUENCEFILE|TEXTFILE|RCFILE

hive存储的三种文件格式；如果文件数据是纯文本，可以使用 STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCEFILE。

LOCATION

定义hive表的数据在hdfs上的存储路径，内部表不需要定义，如果定义的是外部表，则需要直接指定一个路径，或不指定使用默认路径

LIKE

允许用户复制现有的表结构，但是不复制数据

创建数据表

 create table tb_test(id int,name string);

create table tb_test(id int,name string)
# 指定字段分隔符
row format delimited fields terminated by ','
# 指定行分隔符
lines terminated by '\n'

复杂表

create table IF NOT EXISTS tb_test(
id int,
name string,
col1 array<string>,
col2 map<string,int>,
col3 struct<col4:string ,col5:string>
)

查看数据表

show tables;
show tables like 'test';

查询表类型

desc formatted tb_test;

查询表结构

desc tableName;

插入数据

insert into tb_test values (1,"test");

查询数据

select * from tb_test;

修改数据表

修改表名称

alter table old_table_name rename to new_table_name;

添加列

alter table tableName add columns (mycol1 string, mycol2 string);

更新列

alter table tableName change column mycol  newmycol int;

删除数据表

drop table tb_test;

内部表与外部表

在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。

外部表是把指定的hdfs路径的数据加载到表当中来，hive表不完全独占这份数据，所以删除hive表的时候，数据仍然存放在hdfs当中，不会删掉

创建内部表

创建内部表并指定字段之间的分隔符

create table if not exists tb_test(id int ,name string) row format delimited fields terminated by '\t'

根据查询结果创建表（通过复制表结构和表内容创建新表）

create table tb_test2 as select * from tb_test;

根据已经存在的表结构创建表

create table tb_test3 like tb_test;

创建外部表

创建外部表并指定字段之间的分隔符

create external table if not exists tb_test(id int ,name string) row format delimited fields terminated by '\t' location '/usr/local/hive/test.csv';

数据加载与导出

数据加载

load data [local] inpath 'datapath' [overwrite] into table tb_name [partition (partcol1=val1,…)];

load data：加载数据

local：本地，不加Local就是从HDFS,如果是HDFS，将会删除掉原来的数据

inpath：数据路径

datapath：具体的路径，要参考本地还是HDFS

overwrite：覆盖

into table：加入到表

tb_test：表的名字

partition (year='2022',month='02'):分区

1.加载linux本地数据

加载数据(将原数据复制到HDFS表目录下)

load data local inpath '/usr/local/hive/test.csv' into table tb_test;

加载数据并覆盖已有数据

load data local inpath '/usr/local/hive/test.csv' overwrite into table tb_test;

2.加载HDFS数据

从hdfs文件系统向表中加载数据(从HDFS处剪切文件到HDFS表目录下)

hdfs dfs -mkdir -p /hivedatas
hdfs dfs -put test.csv/hivedatas/
load data inpath '/hivedatas/test.csv' into table tb_test;

3.通过查询插入数据形式加载数据表

查询结果插入一张表

insert overwrite table tb_test1 select * from tb_test;

查询结果一次性存放到多张表

from tb_test
insert overwrite table tb_test1 select name
insert overwrite table tb_tes2 select age;

数据导出

1.导出到本地

创建数据存放目录

mkdir -p /hive/back

将查询结果数据存放到本地

insert overwrite local directory '/var/hive/tb_test' select * from tb_test;

按照指定的方式将数据输出到本地

insert overwrite local directory '/var/hive/tb_test'
ROW FORMAT DELIMITED fields terminated by ','
collection items terminated by '-'
map keys terminated by ':'
lines terminated by '\n'
select * from tb_test;

导出到HDFS

查询结果输出到HDFS

# 创建数据存放目录
hdfs dfs -mkdir -p /lzj/copy

# 导出查询结果数据
insert overwrite directory '/hive/back/tb_test'
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
select * from tb_test;

使用HDFS命令拷贝文件到其他目录

hdfs dfs -cp /hive/warehouse/tb_test/* /hive/back/tb_test

将表结构和数据同时备份

# 导出数据
export table tb_test to '/hive/back/tb_test';

# 删除表结构
drop table tb_test;

# 恢复表结构和数据
import from '/hive/back/tb_test';

分区表

把大的数据，按照一定规则（年，月，天，时）进行切分成一个个的小的文件，然后操作小的文件

创建分区表

创建一个表带1个分区，分区的字段不能和表的字段相同

create table tb_test1(id int,name string) partitioned by (month string) row format delimited fields terminated by '\t'

创建一个表带多个分区，前后两个分区的关系为父子关系

create table tb_test2(id int,name string) partitioned by (year string,month string,day string) row format delimited fields terminated by '\t'

加载数据到分区表

load data local inpath '/usr/local/hive/test.csv' into table tb_test1 partition (month='202202');

load data local inpath '/usr/local/hive/test.csv' into table tb_test2 partition (year='2022',month='02',day='01');

分区表的查询

分区表关联查询

select * from tb_test1;

select * from tb_test1 where month = '202202';

select * from tb_test1 where month = '202202' union all select * from tb_test1 where month = '202203';

直接插入数据

insert into table tb_test1 partition(month ='202202') values (123,'Hive');

通过查询插入数据

load方式

load data local inpath '/usr/local/hive/test.csv' overwrite into table tb_test1 partition(month ='202202')

select方式

create table tb_test_temp like tb_test1;
insert overwrite table tb_test_temp  partition(month = '202202') select id,name from tb_test1;

查看分区情况

show partitions tb_test1;

添加一个分区

alter table tb_test1 add partition(month='202203');

删除分区

alter table tb_test1 drop partition(month = '202205');

动态分区

上述操作属于静态分区

静态分区与动态分区的主要区别在于静态分区是手动指定，而动态分区是通过数据来进行判断

静态分区的列是在编译时期通过用户传递来决定的；动态分区只有在SQL执行时才能决定

开启动态分区首先要在hive会话中设置开启

set hive.exec.dynamic.partition=true;

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

其他参数：

hive.exec.dynamic.partition=true; 设置为true表示开启动态分区的功能（默认为false）

hive.exec.dynamic.partition.mode=nonstrict; 设置为nonstrict，表示允许所有分区都是动态的（默认为strict）

hive.exec.max.dynamic.partition.pernode=100; 每个mapper或reducer可以创建的最大动态分区个数(默认为100)

hive.exec.max.dynamic.partitions=1000; 一个动态分区创建可以创建的最大动态分区个数（默认值1000）

hive.exec.max.created.files=100000; 全局可以创建的最大文件个数（默认值100000）

hive.error.on.empty.partition=false; 当有空分区产生时，是否抛出异常（默认false）

创建分区表

create table tb_test1(id int,name string) partitioned by (year string,month string) row format delimited fields terminated by '\t'

创建外部表

CREATE EXTERNAL TABLE IF NOT EXISTS tb_test2(
id int,name string,
year string,
month string
)
row format delimited fields terminated by ','
location "/hive/dynamicTest";

如果使静态分区，插入数据必须指定分区的值，如果有多个不同分区值则将执行多次

insert overwrite table tb_test1 partition (year='2022',month ='02') select * from tb_test2;

如果使用动态分区，动态分区会根据select的结果自动判断数据应该load到那个分区

insert overwrite table tb_test1  partition (year,month) select * from tb_test2;

分桶表

将数据按照指定的字段进行划分，分到多个桶（多个文件）中。

Hive采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中

开启分桶功能

set hive.enforce.bucketing=true;

设置Reduce个数

set mapreduce.job.reduces;
mapreduce.job.reduces=-1 # -1代表不限制个数

set mapreduce.job.reduces=3;

创建桶表

create table tb_test (id int,name string) clustered by(id) into 3 buckets row format delimited fields terminated by '\t';

创建普通表

创建普通表，通过insert overwrite的方式将普通表的数据通过查询的方式加载到桶表当中去

create table tb_test_temp (id int,name string)  row format delimited fields terminated by '\t';

普通表加载数据

load data local inpath '/usr/local/hive/test.csv' into table tb_test_temp;

桶表加载数据

通过insert overwrite给桶表中加载数据

insert overwrite table tb_test select * from tb_test_temp cluster by(id);

数据抽样

数据块抽样: Hive随机抽取N行数据，数据总量的百分比（n百分比）或N字节的数据

SELECT * FROM <Table_Name> TABLESAMPLE(N PERCENT|ByteLengthLiteral|N ROWS) s;

tablesample(n percent) 根据hive表数据的大小按比例抽取数据，并保存到新的hive表中
tablesample(n M) 指定抽样数据的大小，单位为M
tablesample(n rows) 指定抽样数据的行数，其中n代表每个map任务均取n行数据

# select语句不能带where条件且不支持子查询，可通过新建中间表解决
create table tb_temp as select * from test_bucket tablesample(10 percent)

桶表抽样

SELECT * FROM <Table_Name> TABLESAMPLE(BUCKET x OUT OF y)

x:从哪个bucket开始抽取;

y:必须是table总bucket数的倍数或者因子。hive根据y的大小，决定抽样的比例

如：
select * from test_bucket tablesample(bucket 3 out of 12 on id);
bucket数为12，tablesample(bucket 3 out of 6)，表示总共抽取（12/6=）2个bucket的数据，分别为第3个bucket和第（3+6=）9个bucket的数据

随机抽样

SELECT * FROM <Table_Name> DISTRIBUTE BY RAND() SORT BY RAND() LIMIT <N rows to
sample>;

#使用RAND()函数和LIMIT关键字来获取样例数据
#使用DISTRIBUTE和SORT关键字来保证数据是随机分散到mapper和reducer
#ORDER BY RAND()语句可以获得同样的效果，但是性能没这么高

select * from test_bucket DISTRIBUTE BY RAND() SORT BY RAND() LIMIT 10;

Hive查询

语法格式

SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list [HAVING condition]]
[CLUSTER BY col_list
| [DISTRIBUTE BY col_list] [SORT BY| ORDER BY col_list]
]
[LIMIT number]

order by

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

sort by

局部排序，非全局排序，在数据进入reducer前完成排序

如果用sort by排序，则mapred.reduce.tasks>1才行，只保证每个reducer的输出有序，不保证全局有序

distribute by

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

Cluster by

具有Distribute by的功能，还有对字段进行排序的功能

查询语法

select * from tb_test;

select id,name from tb_test;

列别名

select id as myid,name from tb_test;

select id myid,name from tb_test;

LIMIT

LIMIT语句，限制返回的行数

select * from tb_test limit 5;

WHERE

WHERE语句，条件过滤

select * from tb_test  where id> 5;

LIKE 和 RLIKE

LIKE与在MySQL中的用法一致

RLIKE子句是Hive中这个功能的一个扩展，可以通过Java正则表达式指定匹配条件

% 代表零个或多个字符(任意个字符)

_ 代表一个字符

select * from tb_test where name like 'hive%';

select * from tb_test where name  like '_ive%';

 # like '%hive%'
select * from tb_test where name rlike '[hive]';

常用函数

总行数（ count）

select count(1) from tb_test;

最大值（ max）

select max(id) from tb_test;

最小值（ min）

select min(id) from tb_test;

总和（ sum）

select sum(id) from tb_test;

平均值（ avg）

select avg(id) from tb_test;

比较运算符

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

select * from tb_test where id between 1 and 5;

select * from tb_test where name is null;

逻辑运算符

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

AND  逻辑并
OR   逻辑或
NOT  逻辑否

select * from tb_test where id >2 and id<6;

select * from tb_test  where id > 2 or id=5;

select * from tb_test  where id  not in (2,3,4);

分组

GROUP BY语句

GROUP BY 通常和聚合函数一起使用，按照一个或者多个列结果进行分组，然后对每个组执行聚合操作

select id,avg(id) from tb_test  group by id;

select id,max(id) from tb_test  group by id;

HAVING 语句

where针对表中的列，且where后面不能写分组函数

having针对查询结果中的列，且having后面可以使用分组函数

having只用于group by分组统计语句

select id,max(id) maxId from tb_test  group by id having maxId > 8;

JOIN语句

Hive支持通常的SQL JOIN语句，但是只支持等值连接，不支持非等值连接。

等值 JOIN

SELECT a.id,a.name,b.score FROM student a LEFT JOIN course b ON a.id = b.id

内连接，连接两表中都存在与连接条件相匹配的数据将会被返回

select * from student a inner join course b on a.id = b.id;

左外连接，左边表中符合WHERE子句的所有记录将会被返回

select * from student a left join course b on a.id = b.id;

右外连接，右边表中符合WHERE子句的所有记录将会被返回

select * from teacher a right join course b  on a.id = b.id;

多表连接，至少需要n-1个连接条件

多数情况，Hive会对每对JOIN连接对象启动一个MapReduce任务

例如：首先启动一个MapReduce job对tb_a 和tb_b 进行连接操作，然后再启动一个MapReduce job将第一个MapReduce job的输出和tb_c 进行连接操作

select * from tb_a a 
left join tb_b b on a.id = b.id
left join tb_c c on c.id = a.id

排序

全局排序

Order By是全局排序，对应一个reduce，因此输入规模较大时，需要较长计算时间；

order by放在select语句的结尾；使用ORDER BY子句排序

ASC：升序（默认）

DESC：降序

select * from  tb_test ORDER BY id ASC;

select * from  tb_test ORDER BY id DESC;

别名排序

select id,max(id) max from tb_test group by id order by max;

多列排序

select id,max(id) max from tb_test group by id order by id,max;

Sort By局部排序

Sort By局部排序，是对每个MapReduce内部排序，数据进入reducer前完成排序

设置reduce个数
set mapreduce.job.reduces=2;

查看设置reduce个数
set mapreduce.job.reduces;

按id排序
select * from tb_test sort by id;

导出查询结果
insert overwrite local directory '/usr/local/hive/' select * from tb_test  sort by id;

分区排序（DISTRIBUTE BY）
distribute by（字段）: 根据指定的字段将数据分到不同的reducer，且分发算法是hash散列

需要结合sort by使用，且distribute by要在sort by之前

设置reduce个数
set mapreduce.job.reduces=5;

导出查询结果,以distribute by分区再以sort by排序
insert overwrite local directory '/usr/local/hive/' select * from tb_test  distribute by id sort by name;

分区并排序 CLUSTER BY

当distribute by和sort by字段相同时，可以使用cluster by方式

CLUSTER BY有distribute by与sort by的功能。但是排序只能是倒序排序，不能指定排序规则为ASC
或者DESC

select * from tb_test  cluster by id;

select * from tb_test  distribute by id sort by id;

Hive函数

内置函数

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-Built-inFunctions

查看系统自带的函数

show functions;

显示自带的函数的用法

desc function md5;

详细显示自带的函数的用法

desc function extended md5;

自定义函数

https://cwiki.apache.org/confluence/display/Hive/HivePlugins

当Hive内置函数无法满足业务需要时，可以自定义UDF来方便的扩展

自定义函数分三类：

一进一出
多进一出
一进多出

添加依赖

        <dependency>
            <groupId>org.apache.hadoop</groupId>
            <artifactId>hadoop-common</artifactId>
            <version>3.3.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.hive</groupId>
            <artifactId>hive-exec</artifactId>
            <version>3.1.2</version>
        </dependency>

编写函数

public class MyUDF extends UDF {
    public Text compareTo(LongWritable a, LongWritable b) {
        if (a.compareTo(b) > 0) {
            return new Text(a + " > " + b);
        } else if (a.compareTo(b) == 0) {
            return new Text(a + " = " + b);
        } else {
            return new Text(a + " < " + b);
        }
    }
}

打包上传到hive

docker cp myudf-1.0-SNAPSHOT.jar hadoop:/usr/local/hive

hive客户端添加jar包

mv myudf-1.0-SNAPSHOT.jar myudf.jar

add jar /usr/local/hive/lib/myudf.jar;

设置函数与自定义函数关联

create temporary function myudf as 'cn.ybzy.udf.MyUDF';

使用自定义函数

select myudf(5,9);