HiveSQL基础命令03

目录

一、分桶查询

 概述:

示例

二、随机抽样

概述:

示例

三、正则查询

四、union联合查询

五、CTE表达式

1.格式：

2.cte结合union使用

3.cte结合full outer join使用

4.cte结果设置永久存储

六、Hive函数划分

概述：

1.UDF 普通函数

2.UDAF 聚合函数 

3.UDTF 表生成函数

---

一、分桶查询

 概述:

分桶查询是根据分桶字段, 把表数据分成n份, 是逻辑划分, 类似于分组, 就是根据分桶字段值 进行分组, HDFS上文件还是1个.
分桶建表,是物理划分HDFS上存储数据的时候, 已经变成了N个文件

分桶查询需要用到 
set mapreduce.job.reduces = n; 这个n就是ReduceTask任务的数量, 即: 分几个桶.
mapreduce.job.reduces 参数的值默认是 -1, 即: 程序会按照数据量, 任务量自动分配ReduceTask的个数, 一般是1个, 即: 1个桶.

格式:
        cluster by 分桶排序字段 | distribute by 分桶字段 sort by 排序字段 asc | desc

示例

建表
create table student(
sid int,
name string,
sex string,
age int,
major string
) 
row format delimited fields terminated by',';

分桶查询
set mapreduce.job.reduces =3; --分桶个数

select * from student distributed by sid sort by major; 
--sid会分为3个桶，桶内按major排序 默认升序可加desc

select * from student cluster by sid; 
--sid分3个桶并桶内排序,只能升序

二、随机抽样

概述:

随机采样指的是 tablesample()函数, 通过它, 我们可以用 类似于分桶的思路, 对数据进行采样.

格式:
        select .. from 表名 tablesample(bucket x out of y on 列名 | rand());

 1. y表示: 把数据分成y个桶.   逻辑分桶.
        2. x表示: 从y个分桶中, 获取第x份数据.
        3. 如果是列名方式, 列名采样, 在列名等其它条件不发生改变的情况下, 每次采样获取的数据都是一样的.
        4. 如果是rand()函数, 随机采样, 则: 每次获取的数据都是不一样的.
        5. x 不能大于 y

示例

select * from student tablesample (bucket 2 out of 3 on rand());
-- 随机采样, 分成3个桶, 取第2份数据.每次获取的数据条数都不一样.

三、正则查询

常用的正则规则(通用):
        .       代表任意的1个字符
        \.      代表1个普通的. 没有任何的特殊含义.
        [abc]   代表a,b,c中任意的1个字符.
        [^abc]  代表除了a,b,c以外, 任意的1个字符
        \d      代表任意的1个数字, 等价于 [0-9]
        \w      代表1个单词字符, 即: 数字, 字母, 下划线, 等价于: [a-zA-Z0-9_]
        \S      代表任意的1个非空字符
        \\      代表1个\
        ^       代表开头
        $       代表结尾
        ?       数量词, 代表前边的内容出现0次或者1次.
        *       数量词, 代表前边的内容出现0次或多次.
        +       数量词, 代表前边的内容出现1次或者多次.
        a{n}    数量词, 代表a恰好出现n次, 多一次少一次都不行.
        a{n,}   数量词, 代表a至少出现n次, 至多出现无数次.
        a{n,m}  数量词, 代表a至少出现n次, 至多出现m次, 包括n和m
    HiveSQL中, 正则校验格式如下:
        字符串 rlike '正则表达式'

-- 1. 校验字符串是否以a开头.
select '1abc' rlike '^a.*';   -- false

-- 2. 校验字符串以x结尾.
select 'abcx1' rlike '.*x$';    -- false

-- 3. 校验字符串中是否包含连续的3个a
select '11aaaabc' rlike '^.*a{3}.*$';   -- true

-- 4. 校验字符串是否不以a,b,c任意的1个字母开头.
select 'a123' rlike '^[^a,b,c].*$';     -- false

-- 5. 校验手机号是否合法.
-- 规则: 1. 纯数字组成.  2.长度必须是11位.  3.第1位数字必须是1.  4.第2位数字可以是: 3-9
select '13112345678' rlike '^1[3-9]\\d{9}$';
select '13112345678' rlike '^1[3-9][0-9]$';

-- 6. 完成如下的需求
-- 6.1 查找广东省的数据
select * from orders where userAddress like '广东省%';        -- 模糊查询
select * from orders where userAddress rlike '^广东省.$';     -- 正则查询

-- 6.2 查找用户地址是：xx省 xx市 xx区的数据
select * from orders where userAddress rlike '.*省.*市.*区';     -- 正则查询

-- 6.3 查找用户姓为张、王、邓
select * from orders where userName rlike '^[张王邓]\\s+';     -- \S: 代表任意的1个非空白字符
select * from orders where userName rlike '^[张王邓].+';

-- 6.4 查找手机号符合：188****0*** 规则
select * from orders where userPhone rlike '^188\\d{4}0\\d{3}$';

四、union联合查询

对表数据做纵向（上下）拼接 ，同一条列，列名可以不一样但是类型要一样   

union distinct： distinct可以省略，合并去重

union all：合并不去重

full outer join：outer可以省略，横向合并

select * from student
union  --去重
select * from studnet_buckets;
-------------------------------
select * from student
union all --全部数据
select * from studnet_buckets;

--  如果条件写到下边的 查询语句后, 则: 作用于全局.
select * from student                       -- 22条
union all
select * from student_buckets limit 3;      -- 3条;

--  如果仅仅是单独作用于某1个SQL, 则可以用括号括起来.
select * from student                         -- 22条
union all
(select * from student_buckets limit 3);      -- 25条;

五、CTE表达式

1.格式：

with cte表达式的名字 as(
    select 语句，需要被临时存储的内容
)
select ... from cte表达式的名字;

2.cte结合union使用

with t1 as (select * from student),
     t2 as (select * from t1 limit 10)
select * from t1
union all
select * from t2;
---student的全部内容下面拼接上student的前10条内容

3.cte结合full outer join使用

with t1 as (select * from student),
     t2 as (select * from t1 limit 10)
select * from t1
full join t2
on t1.sid = t2.sid;
---student内容全部输出后，右边通过sid拼接上student前10条数据

4.cte结果设置永久存储

create table aa as
with t1 as (
    select * from student
)
select sid, name, age from t1 limit 10; 

六、Hive函数划分

概述：

最初Hive的函数分为 内置函数 和 用户自定义函数两大类, 而用户自定义函数又分为 UDF, UDAF, UDTF三种.
        后来人们(程序员)发现用 UDF, UDAF, UDTF来划分hive函数太方便精准了, 于是在Hive2.X版本中提出了1个概念: 函数标准扩大化.
        即: 从Hive2.4X开始, Hive中的函数就只有: UDF, UDAF, UDTF三种了.
    分类:
        UDF:    全称 user defined functions, 也叫: 普通函数.
            核心: 一进一出.  即: 给函数10行数据, 处理之后, 返回10行.
            例如: abs(), round()...
        UDAF:   全称 user defined Aggregate functions, 也叫: 聚合函数.
            核心: 多进一出, 即: 给函数10行数据, 处理之后, 返回1行.
            例如: count(), max(), sum(), avg(), min()
        UDTF:   全称 user defined Table Generating functions, 也叫: 表生成函数.
            核心: 一进多出, 即: 给函数1条, 它返回多条.
            例如: explode() 爆炸函数(炸裂函数)

1.UDF 普通函数

select abs(-10);    -- 10
select round(10.123, 2);    -- 10.12

2.UDAF 聚合函数 

select count(sid) from student;     -- 22条

3.UDTF 表生成函数

select explode(array('aa', 'bb', 'cc'));

函数基础语法.
show functions ;        -- 查看hive所有的函数
describe function extended abs;     -- 查看函数的说明文档.