关于语法,推荐去看官方文档,里面更详细。
Hive读取的数据本质是到hdfs文件系统上对应的目录中读取到的数据文件内容;也就是说,只要Hive关联的hdfs目录中有数据文件,那么Hive的表中就可以读取到内容。
设置reducer个数为-1时,表示由Hive框架决定运行时开启多少个reducer;
设置reducer个数自定义时,表示运行时开启不多余指定数量的reducer处理;
对于全局排序来说,只会使用一个reducer;
一、数据类型
1. 基本数据类型
Hive数据类型 | Java中类似的数据类型 | 长度 |
TINYINT | byte | 1字节 |
SMALLINT | short | 2字节 |
INT | int | 4字节 |
BIGINT | long | 8字节 |
FLOAT | float | 单精度浮点数 |
DOUBLE | double | 双精度浮点数 |
BOOLEAN | boolean | 布尔值 |
STRING | String | 字符串 |
TIMESTAMP | 时间 | |
BINARY | 字节数组 |
上表中标红加粗的数据类型最为常用。
2. 集合数据类型
集合类型 | 声明语法 | 声明示例 |
STRUCT | fieldName STRUCT<f1:T1,f2:T2,...> | address STRUCT<city:STRING,street:STRING> |
MAP | fieldName MAP<T1,T2> | person MAP<STRING,STRING> |
ARRAY | fieldName ARRAY<T> | cards ARRAY<STRING> |
(1) 创建表
CREATE TABLE IF NOT EXISTS person(
id STRING COMMENT '身份证号码',
baseinfo STRUCT<gender:STRING,age:INT> COMMENT '个人基本信息:性别、年龄',
members MAP<STRING,STRING> COMMENT '家庭成员',
works ARRAY<STRING> COMMENT '以前做过的工作'
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS TERMINATED BY ':'
LINES TERMINATED BY '\n';
(2) 准备数据文件
# 数据文件全路径为:/opt/moudle/hive-3.1.2/data-files/person.data
# 数据文件内容如下:
145222200210030016 man,20 father:bob,mother:sally,son:mike job1,job2
145222200210030016 man,22 father:bb,mother:saly,son:mke job1,job2
145222200210030016 man,23 father:b,mother:sly,son:ke job1,job2
(3) 导入数据
LOAD DATA LOCAL INPATH '/opt/moudle/hive-3.1.2/data-files/person.data' INTO TABLE person;
(4) 从表中集合访问数据
-- 从STRUCT集合中读取格式为:集合.字段名
-- 从MAP集合中读取的格式为:集合[key]
-- 从ARRAY中读取的格式为:集合[0],下标索引从0开始
SELECT id, baseinfo.gender, members['mother'], works[0] FROM person;
3. 数据类型之间的转换方式
(1) 隐形数据类型转换(自动转换)
a. 所有数值类型都会隐式类型转换为范围更大的数值类型;
b. 一串数字字符串会被隐式类型转换为DOUBLE类型;
c. BOOLEAN类型无法转换为任何其他类型;
(2) 显式数据类型转换(强制转换)
-- 强制类型转换语法格式为:CAST(value AS trans-type)
-- 如果强制类型转换失败,返回结果为NULL
-- 如果不强制类型转换,会输出:123.0;
-- 现在进行了强制类型转换,会输出:123
select CAST('123' AS INT);
二、DDL-数据定义语言
1. 数据库
(1) 建库
a. 语法
CREATE DATABASE [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...)];
-- COMMENT database_comment: 对库的描述信息
-- LOCATION hdfs_path: 指定hive创建的数据库和hdfs文件系统中目录的对应关系;如果没指定,就会在默认路径下创建库的对应目录;如果要指定,格式为:目录/数据库名
-- WITH DBPROPERTIES (property_name=property_value, ...): 指定库的一些属性,可自定义属性名
b. 使用示例
-- 执行下面的建库语句后
-- hdfs文件系统会出现:/mydb.db
-- 这个目录对应于hive的mydb
CREATE DATABASE IF NOT EXISTS mydb
COMMENT '这是一个测试数据库'
LOCATION '/mydb.db'
WITH DBPROPERTIES ("create_time"="2022-07-24");
(2) 改库
a. 语法
-- 修改数据库的属性
ALTER DATABASE database_name SET DBPROPERTIES (property_name=property_value, ...);
-- 修改数据库在hdfs的路径
-- hdfs_path必须是完整的uri路径
ALTER DATABASE database_name SET LOCATION hdfs_path;
b. 使用示例
-- 修改库的属性值
ALTER DATABASE mydb SET DBPROPERTIES ("create_time"="2021-06-14");
-- 修改库在hdfs的映射目录
-- 之前的映射目录不会被删除,新修改的映射目录也不会立刻创建
-- 当在该库中创建表的时候,会直接在新的路径下创建对应目录
-- 要求完整的uri路径
ALTER DATABASE mydb SET LOCATION "hdfs://hadoop101:8020/user/hive/warehouse/mydb.db";
(3) 查看库信息
-- 查看有那些库,语法为:
SHOW DATABASES;
-- 查看库的详细信息:语法为:
DESC DATABASE [EXTENDED] database_name;
-- 加上EXTENDED,可以看到库属性信息
(4) 使用库
-- 语法
USE database_name;
(5) 删库
-- 语法为:
DROP DATABASE [IF EXISTS] database_name [CASCADE];
-- 如果库中有数据(表的对应目录下必须有数据文件),需要添加CASCADE才能够删除
2. 表
(1) 建表
-- 创建表语法格式一:
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]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, ...) INTO num_buckets BUCKETS]
[ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS TERMINATED BY ':']
[STORED AS file_format]
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)]
[AS select_statement];
-- EXTERNAL表示是否创建为外部表,不指定表示创建的是内管理表;删除管理表会将hdfs真实目录一起删除、删除外部表不会删除
-- PARTITIONED是创建分区表,需要指定分区字段,这些分区字段不是语法第二行指定的字段,是额外需要创建的字段,所以需要指定类型
-- CLUSTERED是创建分桶表,是对数据的更细致划分,将数据存在几个桶中;使用的字段是语法第二行指定的字段,所以不需要指定类型;如果已经是分区表,那么此时就会对分区内数据再分桶
-- STORED是指定数据文件在hdfs的存储格式,常用的有:TEXTFILE ORC PARQUET;文件存储格式直接书写,不加引号
-- AS select_statement,从别的表查出数据,将这些数据在建表的时候直接放入,不支持外部表使用该语法; 使用改语法不能指定列描述
-- 创建表语法二:
-- 只创建具有相同组织结构的表,不网里面导入数据
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
LIKE existing_table_or_view_name
[LOCATION hdfs_path];
(2) 改表
-- 改表名:
ALTER TABLE table_name RENAME TO new_table_name;
-- 改表属性值:
ALTER TABLE table_name SET TBLPROPERTIES (property_name = property_value, property_name = property_value, ... );
-- 改表的描述信息:
ALTER TABLE table_name SET TBLPROPERTIES ('comment' = new_comment);
-- 新的列类型范围必须大于或者等于原列类型
-- 修改列:
ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]
-- 添加列:
ALTER TABLE table_name ADD COLUMNS (col_name data_type [COMMENT col_comment], ...)
-- 替换所有列:
-- 列的新旧类型、新旧名称、新旧列数量可以不同,不需要遵守新列的类型大于或者等于原列类型
ALTER TABLE table_name REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)
(3) 查看表信息
-- 查看有哪些表
SHOW TABLES;
-- 查看表的详细信息
-- 加上FORMATTED可以看到更加详细的表信息
DESC [FORMATTED] table_name;
(4) 删表
DROP TABLE [IF EXISTS] table_name;
(5) 管理表和外部表的转换
-- 管理表->外部表
ALTER TABLE managed_table_name SET TBLPROPERTIES ('EXTERNAL'='TRUE');
-- 外部表->管理表
ALTER TABLE managed_table_name SET TBLPROPERTIES ('EXTERNAL'='FALSE');
三、DML-数据操纵语言
1. 清空表数据
-- 1. 清空全表数据; 2. 清空分区数据;
TRUNCATE [TABLE] table_name [(partition_column = partition_col_value, partition_column = partition_col_value, ...)];
2. 数据导入
-- 使用LOAD导入数据
-- LOAD本地数据是复制,LOAD集群数据是移动文件
LOAD DATA [LOCAL] INPATH 'file_path' [OVERWRITE] INTO TABLE table_name [PARTITION (partcol1=val1,…)];
-- 使用INSERT导入数据
INSERT INTO TABLE table_name VALUES (v1,v2,...),(v1,v2,v3),...;
INSERT INTO TABLE table_name select_statement;
INSERT OVERWRITE TABLE table_name select_statement;
-- 建表的时候通过LOCATION导入数据
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
[COMMENT table_comment]
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
[CLUSTERED BY (col_name, col_name, ...) INTO num_buckets BUCKETS]
[ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS TERMINATED BY ':']
[STORED AS file_format]
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)]
-- 建表导入数据
CREATE TABLE [IF NOT EXISTS] [db_name.]table_name
[COMMENT table_comment]
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
[CLUSTERED BY (col_name, col_name, ...) INTO num_buckets BUCKETS]
[ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS TERMINATED BY ':']
[STORED AS file_format]
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)]
[AS select_statement];
-- 直接上传文件到hdfs导入数据
hdfs dfs -put local_data_file_path hdfs_path
-- 使用IMPORT导入数据
IMPORT [[EXTERNAL] TABLE new_or_original_tablename [PARTITION (part_column="value"[, ...])]]
FROM 'source_path'
[LOCATION 'import_target_path']
-- Sqoop导入
3. 数据导出
-- INSERT导出
-- 如果表中某个字段的值为null,导出之后null会被输出为\N
INSERT OVERWRITE [LOCAL] DIRECTORY directory
[ROW FORMAT row_format]
SELECT ... FROM ...
-- Hive shell导出
hive -e "sql"
hive -f sql-file
-- hdfs shell导出
hdfs dfs -get hdfs-path local-path
-- EXPORT导出
EXPORT TABLE tablename [PARTITION (part_column="value"[, ...])] TO 'export_target_path';
-- Sqoop导出
四、SQL操作
Hive中SQL语法为:
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[HAVING have_condition]
[ORDER BY col_list]
[CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY col_list] ]
[LIMIT number1[,number2]]
-- 1. ALL 和 DISTINCT 可以写在聚合函数内
-- 2. 当分区字段和排序字段一样且为升序排序的时候,CLUSTER BY col_list 等价于 DISTRIBUTE BY col_list SORT BY col_list,本质就是MR的分区+区内排序
-- 3. LIMIT后最多跟2个数字,跟一个数字表示读取数据的条数;跟两个数字表示:偏移量,数据条数;偏移量如果不指定,默认为0;
常用SQL语句的大概执行流程为:
FROM -- 从指定表中读取数据,生成虚拟表1
JOIN -- 连接虚拟表1和其他表,得到虚拟表2
WHERE -- 对虚拟表1进行过滤,得到虚拟表3
GROUP BY -- 对虚拟表3进行分组,得到虚拟表4
HAVING -- 对分组后的虚拟表4进行过滤,得到虚拟表5
SELECT -- 作用是提取字段,把需要的字段从虚拟表5提取出来,生成虚拟表6
ORDER BY -- 对虚拟表6中的数据进行排序,生成虚拟表7
LIMIT -- 对虚拟表7操作,截取指定范围的数据,生成最终表
1. 全部列和特定列查询
(1) 全部列查询
SELECT * FROM table_name;
(2) 特定列查询
SELECT field1, field2, .. FROM table_name;
2. 列别名
-- 注意:SELECT中定义的列别名,只能在比SELECT执行顺序更迟的语句中使用
-- 比如:SELECT中定义的列别名无法在GROUP BY和HAVING中使用
SELECT field_name [AS] alias FROM table_name;
3. 算数运算符
运算符 | 描述 |
a+b | a和b想加 |
a-b | a减去b |
a*b | a乘以b |
a/b | a除以b |
a%b | a取余b |
a&b | a和b按位取余 |
a|b | a和b按位取或 |
a^b | a和b按位取异或 |
~a | a按位取反 |
4. 逻辑运算符
运算符 | 描述 |
condition1 AND condition2 | 只有当condition1和condition2都是TRUE, 才会返回TRUE; 有一方为FALSE, 就返回FALSE; |
conditin1 OR condition2 | 只有当condition1和condition2都是FALSE, 才会返回FALSE; 有一方为TRUE, 就返回TRUE; |
NOT condition | 对condition的真假性取反; |
5. 比较运算符
运算符 | 描述 |
a=b | 如果a=b, 返回TRUE;否则返回FALSE; |
a>b | 如果a>b, 返回TRUE;否则返回FALSE; 如果有一方为NULL, 返回NULL; |
a>=b | 如果a大于或者等于b, 返回TRUE; 否则返回FALSE; 如果有一方为NULL, 返回NULL; |
a<b | 如果a小于b, 返回TRUE; 否则返回FALSE; 如果有一方为NULL, 返回NULL; |
a<=b | 如果a小于或者等于b, 返回TRUE; 否则返回FALSE; 如果有一方为NULL, 返回NULL; |
a<>b 或者 a!=b | 如果a不等于b, 返回TRUE; 否则返回FALSE; 如果有一方为NULL, 返回NULL; |
a [NOT] BETWEEN b AND c | 等价于[NOT] (a>=b AND a<=c); 如果有一方为NULL, 返回NULL; |
a IS NULL | 如果a是NULL, 就返回TRUE; f否则返回FALSE; |
a IS NOT NULL | 如果a不是NULL, 就返回TRUE; 否则返回FALSE; |
a<=>b | 如果a和b都是NULL, 就返回TRUE;否则返回FALSE; |
v IN(v1,v2,...) | 如果v在IN后面的集合中,就返回TRUE;否则返回FALSE; |
a [NOT] LIKE 'pattern' | 模糊匹配,pattern为匹配的格式; %: 代表任意数量的字符 _: 代表单个任意字符 |
a [NOT] RLIKE 'regexp' | 正则匹配 |
6. 常用聚合函数
聚合函数可以使用的时机:顺序在GROUP BY之后的语句可以使用
-- 计数
COUNT(expr)
-- 求和
SUM(expr)
-- 平均值
AVG(expr)
-- 最大值
MAX(expr)
-- 最小值
MIN(expr)
7. 表连接
(1) 内连接
最终结果集为:两表的交集
SELECT *
FROM a
INNER JOIN b ON a.key = b.key;
(2) 左\右外连接
最终结果集为:主表的所有数据连接上从表中可以和主表对应的数据
-- 左外连接
-- A是主表
SELECT *
FROM A
LEFT OUTER JOIN B ON A.key = B.key;
-- 右外连接
-- B是主表
SELECT *
FROM A
RIGHT OUTER JOIN B ON A.key = B.key;
(3) 满外连接
最终结果集为:两表的所有数据
SELECT *
FROM A
FULL OUTER JOIN B ON A.key = B.key;
(4) SQL连接图
SELECT *
FROM A
INNER JOIN B ON A.key = B.key;
SELECT *
FROM A
LEFT OUTER JOIN B ON A.key = B.key;
SELECT *
FROM A
LEFT OUTER JOIN B ON A.key = B.key
WHERE B.key IS NULL;
SELECT *
FROM A
RIGHT OUTER JOIN B ON A.key = B.key;
SELECT *
FROM A
RIGHT OUTER JOIN B ON A.key = B.key
WHERE A.key IS NULL;
# 思路一:
SELECT *
FROM A
FULL OUTER JOIN B ON A.key = B.key
# 思路二:
SELECT *
FROM A
LEFT OUTER JOIN B ON A.key = B.key
UNION
SELECT *
FROM A
RIGHT OUTER JOIN B ON A.key = B.key
WHERE A.key IS NULL;
# 思路一
SELECT *
FROM A
FULL OUTER JOIN B ON A.key = B.key
WHERE A.key IS NULL OR B.key IS NULL;
# 思路二
SELECT *
FROM A
LEFT OUTER JOIN B ON A.key = B.key
WHERE B.key IS NULL
UNION
SELECT *
FROM B
LEFT OUTER JOIN A ON B.key = A.key
WHERE A.key IS NULL;
(5) 多表连接
SELECT *
FROM A
join_statement1
join statement2
...
;
8. 排序
(1) 全局排序-ORDER BY
ORDER BY底层只会启动一个Reducer进行排序处理;
(2) 分区排序
a. 原生MR程序的分区步骤
1. 写Partitioner,在Partitioner内编写分区逻辑;
2. Job设置自定义的分区类;
3. Job设置Reducer的个数;
b. Hive中的分区排序
-- 分区步骤:
-- 1. 客户端内决定要分几个区:set mapreduce.job.reduces=分区个数;
-- 该值默认配置为-1
-- 2. 决定要分区的字段,该字段跟在DISTRIBUTE BY后面
-- 3. 决定区内排序的字段,该字段跟在SORT BY后面
-- 4. 决定排序的方式:ASC(升序) DESC(降序)
-- 例子
...
DISTRIBUTE BY pf SORT BY kf DESC
...
-- CLUSTER BY无法指定排序方式:升序还是降序;默认升序
-- 当分区字段和排序字段相同,且是升序排序时,可以简写为:
...
CLUSTER BY f
...
-- Hive的分区没有原生MR灵活,原生MR可以更加详细的自定义分区算法
-- Hive中的分区只能确定分区使用字段是哪个,使用一定算法,计算出该字段的一个值
-- 使用计算出的值取余分区个数,从而计算出分区编号
五、分区表、分桶表
1. 分区表
分区表的本质就是在表所在的目录下继续分目录;分区目录的名称为:分区字段名=值。
在查询的时候,可以将分区字段当成是一个普通字段进行使用;并且,如果使用分区字段当作查询条件的话,Hive只会到对应分区内查询数据,而不是查询整个表的数据。
(1) 创建分区表语法
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]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, ...) INTO num_buckets BUCKETS]
[ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS TERMINATED BY ':']
[STORED AS file_format]
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)]
[AS select_statement];
-- PARTITIONED BY内指定多个字段,就是创建多级分区表
(2) 查看表的分区信息
SHOW PARTITIONS table_name;
(3) 对表分区的操作
-- 添加分区
ALTER TABLE table_name ADD [IF NOT EXISTS] PARTITION partition_spec [LOCATION 'location'][, PARTITION partition_spec [LOCATION 'location'], ...];
-- 删除分区
ALTER TABLE table_name DROP [IF EXISTS] PARTITION partition_spec[, PARTITION partition_spec, ...]
partition_spec:
: (partition_column = partition_col_value, partition_column = partition_col_value, ...)
(4) 向分区内导入数据
a. 向静态分区内导入数据
必须手动指定分区名和分区值
-- LOAD方式
LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename PARTITION (partcol1=val1, partcol2=val2 ...)
-- INSERT方式
INSERT (INTO|OVERWRITE) TABLE tablename PARTITION (partcol1=val1, partcol2=val2 ...) select_statement FROM from_statement;
b. 向动态分区内导入数据
动态分区会开启MR计算数据应该放入哪一个分区,在准备数据时,分区也作为一个字段;解析的时候,按照顺序解析。
动态分区的配置项有:
配置项 | 默认值 | 描述 |
hive.exec.dynamic.partition | true | 是否开启动态分区,true表示开启,false表示关闭; |
hive.exec.dynamic.partition.mode | strict | strict表示必须先手动创建一个分区后才可以自动计算数据所属分区;nonstrict表示不需要手动创建分区就可以自动计算数据所属分区; |
hive.exec.max.dynamic.partitions.pernode | 100 | 每个执行MR的的节点最多允许创建的动态分区数量 |
hive.exec.max.dynamic.partitions | 1000 | 所有执行MR节点能允许创建的最大动态分区数量 |
hive.exec.max.created.files | 100000 | 一个Job中,允许所有执行MR节点创建的hdfs文件的最大数量 |
hive.error.on.empty.partition | false | 有空分区是否抛出异常,false表示不抛出异常,true表示抛出异常 |
导入数据语法:
-- LOAD方式
LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename;
-- INSERT方式
INSERT (INTO|OVERWRITE) TABLE tablename1 select_statement1 FROM from_statement;
(5) 分区和数据的关联方式
分区和数据的关联必须满足以下条件:
1. hdfs文件系统上存在分区目录,且分区目录名称规范,内有数据;
2. Hive中表的元数据存在关于上面分区的维护信息;
当分区存在,但是元数据没有维护该信息时,若要互相关联起来,本质就是让Hive中表的元数据添加关于
该分区的元数据信息,提供以下方案:
1. 添加分区:ADD PARTITION;
2. 客户端内执行命令:MSCK REPAIR TABLE table_name;
3. LOAD,将数据LOAD到分区内;
推荐使用2方案,一键导入所有未关联分区。
2. 分桶表
(1) 概念:
可以对一个表或者已分区表的每一个分区进行分桶;
分桶的本质就是将整体的数据集根据指定桶的数量,分成对应桶数量的多个文件存储,实际上就是分区,一个桶代表一个分区。
(2) 语法格式
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]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, ...) INTO num_buckets BUCKETS]
[ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS TERMINATED BY ':']
[STORED AS file_format]
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)]
[AS select_statement];
-- CLUSTERED就是分桶
(3) 抽样查询——TABLESAMPLE
-- 1. 方式1
-- 将一堆数据根据分桶方式(根据字段或者随机)一共分成y个桶
-- 抽取其中第x个桶的数据,产生一张虚拟表
-- 根据表内字段
-- 放在FROM后,会产生一个虚拟表
TABLESAMPLE (BUCKET x OUT OF y [ON colname])
-- 随机
放在FROM后,会产生一个虚拟表
TABLESAMPLE(BUCKET x OUT OF y ON rand()) s
-- 2.方式2
-- 随机抽取百分比率的数据
-- 放在FROM后,会产生一个虚拟表
TABLESAMPLE (n PERCENT)
六、函数
1. UDF
UDF函数,在哪用都行,就是往里面传值,函数根据这些值返回对应的结果。
(1) 数值计算类
返回值 | 函数签名 | 描述 |
DOUBLE | round(DOUBLE a) | 返回a四舍五入后的值 |
DOUBLE | round(DOUBLE a, INT d) | 返回a四舍五入后的值,保留d位小数点 |
BIGINT | floor(DOUBLE a) | 向下取整 |
BIGINT | ceil(DOUBLE a) | 向上取整 |
DOUBLE | rand(), rand(INT seed) | 返回一个0~1的随机小数 |
DOUBLE | pow(DOUBLE a, DOUBLE p) | 返回a的p次方 |
DOUBLE | sqrt(DOUBLE|DECIMAL a) | 开方 |
DOUBLE | abs(DOUBLE a) | 返回a的绝对值 |
(2) 时间日期类
Hive默认识别的日期格式为:yyyy-MM-dd HH:mm:ss
DATE类型的时间格式为:yyyy-MM-dd HH:mm:ss
TIMESTAMP类型的时间格式为:yyyy-MM-dd HH:mm:ss.SSS
返回值 | 函数签名 | 描述 |
STRING | from_unixtime(BIGINT unixtime[, STRING format]) | 根据时间戳[和自定义的日期格式]获取日期字符串 |
BIGINT | unix_timestamp() | 获取现在的时间戳 |
BIGINT | unix_timestamp(STRING date) | 根据日期字符串获取对应的时间戳 |
BIGINT | unix_timestamp(STRING date, STRING pattern) | date是形如pattern的日期字符串,根据日期字符串获取对应的时间戳 |
DATE | to_date(STRING datetime) | 只获取datetime中的yyyy-MM-dd |
INT | year(STRING date) | 获取年 |
INT | month(STRING date) | 获取月 |
INT | day(STRING date) | 获取天 |
INT | hour(STRING date) | 获取时 |
INT | minute(STRING date) | 获取分 |
INT | second(STRING date) | 获取秒 |
INT | weekofyear(STRING date) | 获取date所在天属于改年的第几个周 |
INT | datediff(STRING enddate, STRING startdate) | 计算两个日期相差几天:enddate - startdate |
DATE | date_add(DATE|TIMESTAMP|STRING startdate, TINYINT|SMALLINT|INT days) | 获取startdate加上days天后的日期 |
DATE | date_sub(DATE|TIMESTAMP|STRING startdate, TINYINT|SMALLINT|INT days) | 获取startdate减去days天后的日期 |
DATE | current_date | 返回当前日期 |
TIMESTAMP | current_timestamp | 返回yyyy-MM-dd HH:mm:ss.SSS形式的时间戳 |
STRING | add_months(STRING start_date, INT num_months[, STRING output_date_format]) | [更具指定输出的日期格式,]获取start_data加上num_months个月的日期字符串 |
STRING | last_day(STRING date) | 获取date所在月的最后一天的日期 |
DOUBLE | months_between(enddate, startdate) | 获取两个日期之间相差的月数,顺序为:enddate-startdate |
STRING | date_format(DATE|TIMESTAMP|STRING ts, STRING output_fmt) | 使用指定输出格式output_fmt格式化输出ts |
(3) 字符串处理类
使用正则表达式时,如果发现\s不行,就用\\s
字符串从0索引开始行,从1索引开始也行,但是推荐从1索引开始,因为对于locate这样的子串定位函数来说,找不到返回0,不像其他语言返回负值,因此推荐默认字符串索引从1开始。
返回值 | 函数签名 | 描述 |
STRING | concat(STRING|BINARY A, STRING|BINARY B...) | 字符串连接 |
STRING | concat_ws(STRING SEP, STRING A, STRING B...) | 使用指定分隔符SEP连接字符串 |
STRING | concat_ws(STRING SEP, ARRAY<STRING> str_list) | 使用指定分隔符SEP连接字符串 |
STRING | format_number(NUMBER x,INT d) | 进制转换,返回将数值x转换为d进制的字符串 |
INT | length(STRING s) | 返回字符串s的长度 |
INT | locate(STRING substr,STRING str[,INT pos]) | [从字符串str的pos位置开始,]返回第一次出现子串substr的索引位置 |
STRING | lower(STRING s) | 返回将字符串s中所有字母小写的字符串 |
STRING | lpad(STRING str,INT len,STRING pad) | 使用pad将str左补齐到len长度 |
STRING | ltrim(STRING s) | 去除字符串左边的空格 |
STRING | regexp_extract(STRING s,STRING r,INT group_index) | 通过正则表达式r提取字符串s,group_index是组号; 组号为0,可以获取到第一个满足正则表达式的子字符串(前提是可以在字符串s中找到); 组号大于0,提取正则表达式用()括起来的捕获组内容(前提是可以在字符串s中找到) |
STRING | regexp_replace(STRING s,String r,String replace) | 根据正则表达式r,使用replace替换字符串s中所有满足正则表达式的内容 |
STRING | rpad(STRING str,INT len,STRING pad) | 使用pad将str右补齐到len长度 |
STRING | rtrim(STRING s) | 去除字符串右边的空格 |
ARRAY | split(STRING str, STRING r) | r为正则表达式,根据正则表达式切分字符串 |
STRING | substr(STRING|BINARY A, INT start) | 切分字符串,范围为:start~末尾 |
STRING | substr(STRING|BINARY A, INT start, INT len) | 切分len长度的字符串,从start开始切 |
STRING | trim(STRING s) | 去除字符串左右两边的空格 |
STRING | upper(STRING s) | 返回s全字母大写的字符串 |
(4) 集合类
返回类型 | 函数签名 | 描述 |
INT | size(ARRAY<T>|MAP<K,V> collection) | 返回集合内元素的个数 |
ARRAY<K> | map_keys(MAP<K,V> m) | 返回Map的键数组 |
ARRAY<V> | map_values(MAP<K,V> m) | 返回Map的值数组 |
BOOLEAN | array_contains(ARRAY<T>,value) | 如果value包含在数组中,返回TRUE; 否则返回FALSE |
ARRAY<T> | sort_array(ARRAY<T>) | 对数组排序,自然升序 |
(5) 条件判断类
返回类型 | 函数签名 | 描述 |
T | if(BOOLEAN testCondition, T valueTrue,T valueFalse) | 如果testCondition为TRUE,返回valueTrue; 否则返回valueFalse |
T | nvl(T value,T default_value) | 如果value是NULL,返回default_value; 否则返回value |
T | CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END | 如果a=b, 返回c; [如果a=d,返回e;] ... [否则,返回f] |
T | CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END | 如果a为TRUE,返回b; [如果c为TRUE,返回d;] ... [否则,返回e] |
2.UDTF
UDTF函数,不能在任意地方使用,(1)在LATERAL VIEW之后使用;(2) 直接跟在SELECT后使用;
(1) 使用方式
SELECT ...
FROM table_name
LATERAL VIEW UDTF table_alias AS columnAlias (, columnAlias)*
-- 可以近似认为,LATERAL VIEW和FROM的执行时机完全一样
(2) 详细解释
使用最常用的UDTF: explode(ARRAY<T> col)来解释说明
假设有一张表movie,movie的字段设计如下:
字段 | 解释 |
id INT | 电影编号 |
types ARRAY<STRING> | 电影类型,是一个数组 |
表内数据有:
id | types |
1 | ["惊悚","悬疑","恐怖"] |
2 | ["动作","爱情","喜剧"] |
使用explode函数:
SELECT id,tp
FROM movie
LATERAL VIEW explode(types) t AS tp;
/*
你也可以这么访问字段
本质就是将原表和t表连接起来形成一张新表
SELECT movie.id, t.tp
FROM movie
LATERAL VIEW explode(types) t AS tp;
*/
会产生一张虚拟表连接起来的新表,表内容如下:
movie.id | movie.types | t.tp |
1 | ["惊悚","悬疑","恐怖"] | 惊悚 |
1 | ["惊悚","悬疑","恐怖"] | 悬疑 |
1 | ["惊悚","悬疑","恐怖"] | 恐怖 |
2 | ["动作","爱情","喜剧"] | 动作 |
2 | ["动作","爱情","喜剧"] | 爱情 |
2 | ["动作","爱情","喜剧"] | 喜剧 |
t是LATERAL VIEW通过UDTF产生的新表,tp是新产生的列;其他的UDTF函数可能会产生多列。
3. UDAF
UDAF函数,不能在任意地方使用,使用的地方有两个:组内(没有group by,默认所有数据一个组)、数据窗口内。
返回类型 | 函数签名 | 描述 |
BIGINT | count(*) | 返回记录个数 |
BIGINT | count(expr) | 返回表达式expr不为NULL的记录个数 |
BIGINT | count(DISTINCT expr[, expr...]) | 返回去重后,表达式不为NULL的记录个数 |
DOUBLE | sum(col) | 计算col列的和 |
DOUBLE | sum(DISTINCT col) | 返回去重后,col列的和 |
DOUBLE | avg(col) | 计算col列的平均值 |
DOUBLE | avg(DISTINCT col) | 返回去重后,col列的平均值 |
DOUBLE | max(col) | 返回col列的最大值 |
DOUBLE | min(col) | 返回col列的最小值 |
ARRAY | collect_set(col) | 将col列内容加入集合中,集合内容会去重 |
ARRAY | collect_set(col) | 将col列内容加入集合中,集合内容不去重 |
5. 窗口函数
窗口函数只能在开窗后使用,放在OVER()之前;
函数签名 | 描述 |
LAG(col,n,default_val) | 以当前行为基准,获取之前的第n条数据的col内容,如果col内容为NULL,返回default_value |
LEAD(col,n,default_val) | 以当前行为基准,获取之后之后的第n条数据的col内容,如果col内容为NULL,返回default_value |
FIRST_VALUE(col,BOOLEAN) | 在当前数据窗口的指定数据范围内,从第一行数据开始,向下寻找满足条件的第一个col列并返回内容;第二个参数指定是否跳过NULL值,TRUE表示跳过,FALSE表示不跳过 |
LAST_VALUE(col,BOOLEAN) | 在当前数据窗口的指定数据范围内,从最后一行数据开始,向上寻找满足条件的第一个col列并返回内容;第二个参数指定是否跳过NULL值,TRUE表示跳过,FALSE表示不跳过 |
6. 分析函数
分析函数只能在开窗后使用,放在OVER()之前;
返回类型 | 函数签名 | 描述 |
INTEGER | ntile(INTEGER x) | 将数据平均分成多个桶,并为每一条数据计算出一个桶号;可以用来提取部分数据。 |
INTEGER | rank() | 排名,最后一位的排名序号不等于总数据条数(在数据窗口内排序才会有使用的意义) |
INTEGER | dense_rank() | 排名,最后一位的排名序号等于总数据条数(在数据窗口内排序才会有使用的意义) |
INTEHER | row_number() | 列出每一行数据是第几行 |
7. 开窗——数据窗口
SELECT后可以跟多条OVER(),但是数据窗口大小必须相同,数据窗口的指定数据范围可以不同。
SELECT ...
OVER([PARTITION BY col1[,col2,...] ORDER BY col1[,col2,...] [(ASC|DESC)] ROWS BETWEEN position1 AND position2])
FROM ...
;
-- 逻辑上理解开窗处理处理过程:
-- PARTITION BY: 根据列,将整体数据分成几个区,按照顺序将分好的区依次放入OVER进行处理,每一个放入OVER的分区都是互相独立的数据窗口,如果省略PARTITION,可以看作整个表的数据是一个分区。
-- ORDER BY: 根据指定列,对数据窗口记录排序
-- ROWS BETWEEN: 用来确定哪些记录将会作为分析函数、聚合函数或者窗口函数的计算数据集合
-- UNBOUNDED PRECEDING:数据窗口的第一条记录的位置
-- UNBOUNDED FOLLOWING:数据窗口的最后一条记录的位置
-- CURRENT ROW: 当前正在处理的记录位置
-- n PRECEDING: 以当前记录为准,之前第n条数据的位置
-- n FOLLOWING: 以当前记录为准,之后第n条数据的位置
-- 当前记录是哪条记录?
-- 类比于shell工具awk,awk处理输入的每一条记录,当前记录就是正在处理的记录
-- 开窗也是一样,处理数据窗口中的每一条记录,当前记录就是正在处理的记录
8. 自定义函数
(1) 引入依赖为:
<dependencies>
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-exec</artifactId>
<version>3.1.2</version>
</dependency>
</dependencies>
(2) 自定义UDF
import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
import org.apache.hadoop.hive.ql.exec.UDFArgumentLengthException;
import org.apache.hadoop.hive.ql.exec.UDFArgumentTypeException;
import org.apache.hadoop.hive.ql.metadata.HiveException;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDF;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspectorFactory;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;
import org.apache.hadoop.io.BooleanWritable;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
/**
* 自定义的分割字符串函数,可以指定多个分隔符进行分割
* 可以传入一个布尔值来指名切割字符串的方式:(1) 同时使用切割符进行切割; (2) 使用每个切割符进行切割的过程相互独立;
*/
public class SplitStringUDF extends GenericUDF {
/**
* 初始化方法,作用:(1) 检验传入的函数的参数个数;(2) 检验传入函数的参数类型是否正确; (3) 设置函数的返回类型
* @param objectInspectors 对象鉴别器对象,可以从该对象中提取出传入函数参数的类型
* @return 规定了函数的返回类型
* @throws UDFArgumentException
*/
@Override
public ObjectInspector initialize(ObjectInspector[] objectInspectors) throws UDFArgumentException {
// 判断参数的个数,传入参数为3个
if (objectInspectors.length != 3){
throw new UDFArgumentLengthException("参数传入个数不正确,应该传入的参数个数为:3");
}
// 判断传入参数的类型是否正确:STRING STRING BOOLEAN
for (int i = 0; i < 2; i++) {
ObjectInspector inspector = objectInspectors[i];
if (!inspector.getTypeName().equalsIgnoreCase(PrimitiveObjectInspectorFactory.javaStringObjectInspector.getTypeName())){
throw new UDFArgumentTypeException(i, "类型不匹配,需要类型为:" + inspector.getTypeName());
}
}
if (!objectInspectors[objectInspectors.length - 1].getTypeName().equalsIgnoreCase(PrimitiveObjectInspectorFactory.javaBooleanObjectInspector.getTypeName())){
throw new UDFArgumentTypeException(objectInspectors.length - 1, "类型不匹配,需要类型为:" + objectInspectors[objectInspectors.length - 1].getTypeName());
}
// 规定函数的返回值类型,返回Hive中对应的ARRAY<STRING>类型
// 找基础类型鉴别器就去PrimitiveObjectInspectorFactory找
// 找集合类型鉴别器就去ObjectInspectorFactory找
return ObjectInspectorFactory.getStandardListObjectInspector(PrimitiveObjectInspectorFactory.javaStringObjectInspector);
}
/**
* 核心处理方法
* @param deferredObjects 传入的参数,这些传入的参数类型包装了原本的类型,使用get方法就可以的得到实际类型,使用的实际类型是Hadoop封装的序列化类型(xxxWritable)
* 字符串类型使用的是LazyString类型,可以toString()获取字符串
* @return 处理之后的结果
* @throws HiveException
*/
@Override
public Object evaluate(DeferredObject[] deferredObjects) throws HiveException {
// 非空判断
if (Arrays.stream(deferredObjects).anyMatch(deferredObject -> {
try {
Object o = deferredObject.get();
return o == null;
} catch (HiveException e) {
e.printStackTrace();
}
return true;
})) {
throw new HiveException("传入参数不能为:NULL");
}
// 获取传入的字符串
String str = deferredObjects[0].get().toString();
// 获取分隔符
String seps = deferredObjects[1].get().toString();
// 获取传入的布尔值
Boolean condition = ((BooleanWritable)(deferredObjects[2].get())).get();
if (condition){
return str.split("[" + seps + "]");
}else {
List<String> res = new ArrayList<>();
for (int i = 0; i < seps.length(); i++) {
String sep = seps.substring(i, i + 1);
for (String s : str.split(sep)) {
res.add(s);
}
}
return res;
}
}
@Override
public String getDisplayString(String[] strings) {
return "";
}
}
(3) 自定义UDTF
import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
import org.apache.hadoop.hive.ql.exec.UDFArgumentLengthException;
import org.apache.hadoop.hive.ql.exec.UDFArgumentTypeException;
import org.apache.hadoop.hive.ql.metadata.HiveException;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspectorFactory;
import org.apache.hadoop.hive.serde2.objectinspector.StructField;
import org.apache.hadoop.hive.serde2.objectinspector.StructObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
/**
* 输入内容为:k1:v1,k2:v2,...
* 需要指定kv之间的分隔符和kv对之间的分隔符
* 原数据位:
* ... k1:v1,k2:v2
* 将数据炸裂为:
* ... k1 v1
* ... k2 v2
*/
public class ExplodeStringUDTF extends GenericUDTF {
/**
* 初始化方法,作用为:(1) 检查传入参数的个数;(2) 检查传入参数的类型; (3) 确定函数返回类型以及返回的结构(有几列,每一列的名称和类型是什么);
* @param argOIs 传入参数的类型鉴别器
* @return
* @throws UDFArgumentException
*/
@Override
public StructObjectInspector initialize(StructObjectInspector argOIs) throws UDFArgumentException {
List<? extends StructField> structFieldRefs = argOIs.getAllStructFieldRefs();
// 判断参数个数:3
if (structFieldRefs.size() != 3){
throw new UDFArgumentLengthException("参数个数不正确,参数个数应为:3");
}
// 判断传入参数类型:String String String
for (int i = 0; i < structFieldRefs.size(); i++) {
StructField structField = structFieldRefs.get(i);
if (!structField.getFieldObjectInspector().getTypeName().equalsIgnoreCase(PrimitiveObjectInspectorFactory.javaStringObjectInspector.getTypeName())) {
throw new UDFArgumentTypeException(i, "参数类型不匹配,需要的参数类型为:" + PrimitiveObjectInspectorFactory.javaStringObjectInspector.getTypeName());
}
}
// 确定函数返回类型以及返回的结构
// 指定列名,列名不要指定为:_c1,_c2,...
List<String> structFieldNames = new ArrayList<>();
structFieldNames.add("key");
structFieldNames.add("value");
List<ObjectInspector> structFieldObjectInspectors = new ArrayList<>();
// 两列的类型
structFieldObjectInspectors.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);
structFieldObjectInspectors.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);
return ObjectInspectorFactory.getStandardStructObjectInspector(structFieldNames, structFieldObjectInspectors);
}
/**
* 核心处理方法
* @param args 传入的参数,实际为Hadoop的序列化类型;字符串类型为LazyString类型,toString()就可以获取字符串内容
* @throws HiveException
*/
@Override
public void process(Object[] args) throws HiveException {
// 非空判断
if (Arrays.stream(args).anyMatch(o -> o == null)) {
throw new HiveException("传入参数不能为NULL");
}
// 获取要炸开的字符串
String str = args[0].toString();
// 获取kv对的分隔符
String sep = args[1].toString();
// 获取kv的分隔符
String division = args[2].toString();
// 要输出的一行数据(当前列为2,所以输出两个)
List<String> row = new ArrayList<>();
for (String pair : str.split(sep)) {
String[] kv = pair.split(division);
for (String s : kv) {
row.add(s);
}
// 输出处理完的一个kv
forward(row.toArray());
// 输出之后,清空
row.clear();
}
}
/**
* 释放资源
* @throws HiveException
*/
@Override
public void close() throws HiveException {
}
}
(4) 在Hive中使用自定义函数
a. 将jar包中的类和函数关联:
-- 将编写好的jar包路径添加到Hive的类路径下,操作步骤是进入客户端键入:
add jar jar包所在完整路径(包括jar包)
-- 此时Hive就会从改路径下寻找函数对应的类了
CREATE [TEMPORARY] FUNCTION function_name AS class_name;
-- TEMPORARY: 创建临时函数,只在本次客户端中有效
-- 接下来就可以使用自定义函数了
b. 删除函数
DROP [TEMPORARY] FUNCTION [IF EXISTS] function_name;