hive查询

最新推荐文章于 2024-11-07 19:50:24 发布

肥肥鲨356

最新推荐文章于 2024-11-07 19:50:24 发布

阅读量751

点赞数 23

文章标签： hive hadoop 数据仓库

本文链接：https://blog.csdn.net/weixin_65082744/article/details/141096038

版权

语法结构:

类sql基本查询

基础查询格式: select distinct 字段名 from 表名;
           注意: *代表所有字段 distinct去重 as给表或者字段起别名
条件查询格式: select distinct 字段名 from 表名 where 条件;
           比较运算符: > < >= <= != <>
逻辑运算符: and or not
模糊查询: %代表任意0个或者多个字符 _代表任意1个字符
空判断: 为空is null 不为空is not null
范围查询: x到y的连续范围:between x and y x或者y或者z类的非连续范围: in(x,y,z)
排序查询格式: select distinct 字段名 from 表名 [where 条件] order by 排序字段名 asc|desc ;
           asc : 升序默认升序
           desc: 降序
聚合查询格式: select 聚合函数(字段名) from 表名;
           聚合函数: 又叫分组函数或者统计函数
           聚合函数: count() sum() avg() max() min()
分组查询格式: select 分组字段名,聚合函数(字段名) from 表名 [where 非聚合条件] group by 分组字段名 [having 聚合条件];
           注意: 当分组查询的时候,select后的字段名要么在groupby后出现过,要么放在聚合函数内,否则报错
           where和having区别?
           区别1: 书写顺序不同,where在group by关键字前,having在group by关键字后
           区别2: 执行顺序不同,where在分组之前过滤数据,having在分组之后过滤数据
           区别3: 筛选数据不同,where只能在分组之前过滤非聚合数据,having在分组之后主要过滤聚合数据
           区别4: 操作对象不同,where底层操作伪表,having底层操作运算区
分页查询格式: select 字段名 from 表名 [ order by 排序字段名 asc|desc] limit x,y;
           x: 起始索引默认从0开始,如果x为0可以省略计算格式: x=(页数-1)*y
           y: 本次查询记录数

类sql多表查询

交叉连接格式: select 字段名 from 左表 cross join 右表;
注意: 交叉连接产生的结果叫笛卡尔积,此种方式慎用!!!

内连接格式: select 字段名 from 左表 inner join 右表 on 左右表关联条件;
特点: 相当于只取两个表的交集

左外连接格式: select 字段名 from 左表 left outer join 右表 on 左右表关联条件;
特点: 以左表为主,左表数据全部保留,右表只保留和左表有交集的部分

右外连接格式: select 字段名 from 左表 right outer join 右表 on 左右表关联条件;
特点: 以右表为主,右表数据全部保留,左表只保留和右表有交集的部分

自连接: 本质是一个特殊的内外连接,最大特点就是左右表是同一个表
应用场景: 比较局限,场景1: 存储省市县三级数据的区域表场景2: 存储上下级信息的员工表

子查询: 本质是一个select语句作为另外一个select语句的一部分(表或者条件)
注意: 子查询作为表使用的话必须取别名

hive其他join操作

全外连接: 左表 full [outer] join 右表 on 条件

左半开连接: 左表 left semi join 右表 on 条件

抽样查询

TABLESAMPLE抽样好处: 尽可能实现随机抽样,并且不走MR,查询效率相对较快

基于随机分桶抽样格式: SELECT 字段名 FROM tbl TABLESAMPLE(BUCKET x OUT OF y ON(字段名 | rand()))
y:决定将表数据随机划分成多少份
x:决定从第几份数据开始采样
| : 或者
字段名: 表示随机的依据基于某个列的值,每次按相关规则取样结果都是一致
rand(): 表示随机的依据基于整行,每次取样结果不同

正则模糊查询

sql模糊查询关键字: like 任意0个或者多个: % 任意1个: _

正则模糊查询关键字: rlike 任意0个或者多个: .* 任意1个: . 正则语法还有很多......

union联合查询

union联合查询: 就是把两个select语句结果合并成一个临时结果集,整体可以用于其他sql操作

注意: union合并的多个结果集字段顺序,个数,类型都要一一对应,如果本身不对应,可以手动添加如:null as 列名

union [distinct]: 去重,只是省略了distinct
union all : 不去重

CTE表达式

CTE: 公用表表达式（CTE）是一个在查询中定义的临时命名结果集将在from子句中使用它。
注意: 每个CTE仅被定义一次（但在其作用域内可以被引用任意次）,仅适用于当前运行的sql语句
语法如下:
with 临时结果集的别名1 as (子查询语句),
        临时结果集的别名2   as (子查询语句)
        ...
select 字段名 from (子查询语句);

根据实际使用次数可以决定用"CTE表达式"还是"视图"还是"建表": with 临时结果名 as (select语句) > create view 视图名 as select语句 > create table 表名 as select语句

内置虚拟列

虚拟列是Hive内置的可以在查询语句中使用的特殊标记，可以查询数据本身的详细参数。
Hive目前可用3个虚拟列：
INPUT__FILE__NAME:显示数据行所在的具体文件
BLOCK__OFFSET__INSIDE__FILE:显示数据行所在文件的偏移量
ROW__OFFSET__INSIDE__BLOCK:显示数据所在HDFS块的偏移量
注意: 此虚拟列需要设置：SET hive.exec.rowoffset=true 才可使用

函数分类标准[重点]

原生分类标准: 内置函数和用户定义函数(UDF,UDAF,UDTF)

分类标准扩大化: 用户定义函数分类标准的扩大化,本来，UDF 、UDAF、UDTF这3个标准是针对用户自定义函数分类的；但是，现在可以将这个分类标准扩大到hive中所有的函数，包括内置函数和自定义函数；

目前hive三大标准
UDF:（User-Defined-Function）普通函数: 特点是一进一出(输入一行数据输出一行数据) 举例: split
UDAF:（User-Defined Aggregation Function）聚合函数: 特点是多进一出(输入多行输出一行) 举例: count sum max min avg
UDTF:（User-Defined Table-Generating Functions）表生成函数: 特点是一进多出(输入一行输出多行) 举例: explode

查询所有hive函数名称: show functions;
查看某函数使用帮助文档: desc function [extended] 函数名; 注意: 加上extended关键字能查看详细信息示例

复杂类型函数

hive复杂类型: array struct map

array类型: 又叫数组类型,存储同类型的单数据的集合
   取值: 字段名[索引] 注意: 索引从0开始
   获取长度的函数: size(字段名) 常用
   判断是否包含某个数据的函数: array_contains(字段名,某数据) 常用
   对数组进行排序的函数: sort_array(数组)

struct类型: 又叫结构类型,可以存储不同类型单数据的集合
   取值: 字段名.子字段名n

map类型: 又叫映射类型,存储键值对数据的映射(根据key找value)
   取值: 字段名[key]
   获取长度的函数: size(字段名) 常用
   获取所有key的函数: map_keys() 常用
   获取所有value的函数: map_values() 常用

字符串函数[练习]

字符串常见的函数:
concat: 字符串紧凑拼接到一起生成新字符串
concat_ws: 字符串用指定分隔符拼接到一起生成新字符串常用
length: 获取字符串长度常用
lower: 把字符串中的字母全部变成小写
upper: 把字符串中的字母全部变成大写
trim: 把字符串两端的空白去除常用

拓展字符串函数
substr: 截取字符串常用
replace: 替换字符串常用
regexp_replace: 正则替换字符串
parse_url: 解析url(统一资源定位符) 组成: 协议/主机地址:端口号/资源路径?查询参数
get_json_object: 获取json对象解析对应数据