day 46 hive 分区/桶 JDBC 数据类型 开窗函数（连续登陆问题，top N）

已于 2022-02-13 21:47:40 修改
阅读量1k 收藏 2
点赞数
文章标签： sql mysql
于 2021-11-26 19:50:41 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_41464008/article/details/121545385
版权
本文详细介绍了Hive中的分区技术，包括默认分区、动态分区和多级分区的创建、管理与查询。同时讲解了分桶的概念，以及如何通过HiveJDBC进行Java连接操作。此外，还探讨了Hive中的数据类型，如基本类型、时间类型和复杂类型，并展示了行、列转换以及开窗函数的应用。最后，通过实例展示了如何统计连续登陆最大天数和实现topN查询。
摘要由CSDN通过智能技术生成

文章目录

I know, i know
地球另一端有你陪我




一、分区


1、默认分区

实际上是在表的目录下在以分区命名，建子目录
作用：能够避免全表扫描，减少MapReduce处理的数据量，提高效率

需要在建表时加上分区字段，通常按日期、地域分区，一般不超过三级目录，避免 task 过多

本质上也是一个字段，所以不能和普通字段重复


1、建立分区表

    create external table students_pt1
    (
        id bigint,
        name string,
        age int,
	    gender string,
        clazz string
		)
	/ 分区字段在这里，即 pt
    PARTITIONED BY(pt string)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

2、手动增加、删除分区（子目录）

	/	添加分区（目录），名称为 20210904
    alter table students_pt1 add partition(pt='20210904');
	
	/	删除名称为 20210904 的分区（目录）
    alter table students_pt drop partition(pt='20210904');

3、查询表的所有分区

	/	会从元数据中获取信息
    show partitions students_pt;

4、向分区（子目录）中手动插入数据
      会自动创建不存在的分区，注入（insert）的话，会在数据中自动添加一列分区信息

    insert into table students_pt partition(pt='20210902') 
    select * from students;

    load data local inpath '/usr/local/soft/data/students.txt' 
    into table students_pt partition(pt='20210902');

5、 查询分区数据
       分区字段当做普通字段使用

    select count(*) from students_pt where pt='20210101';

2、动态分区

hive 能够自动识别数据中的设置好的分区字段
自动进行分区（子目录）的创建，和数据划分
需要使用 insert 加载数据


1、开启动态分区

/	表示开启动态分区
    set hive.exec.dynamic.partition=true;
	
/	表示动态分区模式：strict（需要配合静态分区一起使用）、nostrict
    set hive.exec.dynamic.partition.mode=nostrict;
	
/	最大分区数
    set hive.exec.max.dynamic.partitions.pernode=1000;

2、建一个原始表存数据，再建一个分区表造

    create table students_dt
    (
        id bigint,
        name string,
        age int,
        gender string,
        clazz string,
        dt string
    )
    ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

    create table students_dt_p
    (
        id bigint,
        name string,
         age int,
         gender string,
        clazz string
    )
    PARTITIONED BY(dt string)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

3、使用动态分区插入数据

/	原始数据载入

    load data local inpath '/usr/local/data/students_dt.txt' 
    into table students_dt;

/	hive 识别分区字段锁死从最后向前读

    insert into table students_dt2 partition(dt) 
    select id,name,age,gender,clazz,dt from students_dt;

/ 比如下面这条语句会使用age作为分区字段，而不会使用student_dt中的dt作为分区字段

    insert into table students_dt2 partition(dt) select
    id,name,age,gender,dt,age from students_dt;

3、多级分区（多级子目录）

    create table students_year_month_pt
    (
        id bigint,
        name string,
        age int,
        gender string,
        clazz string
    )
    PARTITIONED BY(year string,month string)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';


insert into table students_year_month_pt partition(year,month) 
select id,name,age,gender,clazz,year,month from students_year_month;

二、分桶

分区是创建子目录保存数据，分桶是以普通字段作为标准，将数据切分为子文件
本质上是通过 MapReduce 中的 reduce task 读取不同的 key 来将数据划分
所以可能会出现部分桶出现过多数据，而部分桶没有数据，无法绝对平均


1、开启分桶
      hive 默认关闭分桶

    set hive.enforce.bucketing=true;

2、建立分桶表
      以普通字段作为分桶标准

    create table students_buck
    (
        id bigint,
        name string,
        age int,
        gender string,
        clazz string
    )
    CLUSTERED BY (clazz) into 12 BUCKETS
    ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

3、往分桶里倒垃圾
      需要使用 insert

	insert into students_buks select * from students;

三、Hive JDBC

java 连接 hive
本质上是连接 hadoop ，所以操作都差不多


1、先在 linux 中打开服务

    hiveserver2 &

package day46;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class HiveTest {

    public static void main(String[] args) throws Exception{

        Class.forName("org.apache.hive.jdbc.HiveDriver");

        Connection conn
                = DriverManager.getConnection(
                //此处要写明库名
                "jdbc:hive2://master:10000/fghdata");

        String sql = "select * from students";

        PreparedStatement ps = conn.prepareStatement(sql);

        ResultSet rs = ps.executeQuery();

        while(rs.next()){
            String name = rs.getString("name");
            System.out.println(name);
        }

        rs.close();
        ps.close();
        conn.close();
    }
}

四、一些数据类型


1、基本数据类型

整型：TINYINT、SMALLINT、INT、BIGINT
           — 对应 byte、short、int、long

浮点：FLOAT、DOUBLE

布尔类型：BOOL （False/True）

字符串：STRING

2、时间类型

时间戳 timestamp

日期 date

1 时间戳转换为对应格式的时间格式

    select from_unixtime(1630915221,'yyyy年MM月dd日 HH时mm分ss秒')

2 时间字符串转换为时间戳

/	文字必须对应相同
    select unix_timestamp('2021年09月06日 16时00分21秒'
    ,'yyyy年MM月dd日 HH时mm分ss秒');
    
    select unix_timestamp('2021-01-14 14:24:57.200');

以上两个可以组合使用，将字符串转换为时间格式

from_unixtime(unix_timestamp('2021-01-14 14:24:57.200','yyyy-MM-dd hh:mm:ss'),'yyyy-MM-dd HH:mm:ss') as end_date

3 时间做差

做差只能精确到时间，无法到小时等等
select datediff('2018-09-02','2018-09-01')

小时等的可以尝试转换为时间戳,再除60

3、复杂数据类型

1 array

	定义一个字符串数组，数组内元素用 ‘,’ 隔开

    create table array(
        name string,
        weight array<string>
    )row format delimited fields terminated by ' '
    COLLECTION ITEMS terminated by ',';

输入的数据 be like

	zs 110,120,130
	ls 220,240,290

查询语句

    select name,weight[0] from testArray;

2 map

    create table map(
        name string,
        score map<string,int> 
    )ROW FORMAT DELIMITED FIELDS TERMINATED BY ' '
    COLLECTION ITEMS TERMINATED BY ','
    MAP KEYS TERMINATED BY ':';

输入的数据 be like

	zs 语文:110,数学:120,英语:130
	ls 语文:120,数学:110,英语:120

查询语句

    select name,score['语文'] from map;

3 struct

    create table struct(
        name string,
        state struct<level:int,atk:int,hp:int> 
    )ROW FORMAT DELIMITED FIELDS TERMINATED BY ' '
    COLLECTION ITEMS TERMINATED BY ',';

输入的数据 be like

    ralts 5,9,15
    piplup 5,10,18

查询语句

    select name,state.level,state.atk from struct;

4、行、列转换

1 行转列

    create table testarray(
        name string,
        weight array<string>
    )row format delimited fields terminated by ' '
    collection items terminated by ',';

数据如下，写在一个文件中

	a "150","170","180"
	b "150","180","190"

 // 导入
 load data local inpath '/usr/local/data/test' into table testarray

侧视表 lateral view

类似笛卡尔积（join），将 [150,170,180] 与原表做匹配，即 a

    select name,col1  from testarray 
    lateral view explode(weight) t1 as col1;

	a	150
	a	170
	a	180
	b	150
	b	180
	b	190

2 列转行

    create table testarray2(
        name string,
        num int
    )row format delimited 
    fields terminated by ' ';

	a 150
	a 120
	a 110
	b 140
	b 110
	b 180

MapReduce 按照 key 直接丢到对应的 reduce task

    select name,collect_list(num) 
    from testarray2 group by name;

a	[150,120,110]
b	[140,110,180]

五、开窗函数

在使用 sql 查询语句的时候，经常会用到聚合函数（group by 等）
而所得到的数据中，只会显示一行数据，往往会丢失我们真正需要的数据
为此，出现了开窗函数，保证用户可以在保留原数据的基础上，使用函数

1、统计连续登陆最大天数

建表

    create table login
    (
        id string,
        day string
    ) row format delimited fields terminated by ',';

数据（千万不要加引号，会导致计算识别错误）

	001,2020-04-20
	001,2020-04-21
	001,2020-04-25
	001,2020-04-26
	001,2020-05-10
	001,2020-05-11
	001,2020-05-12
	001,2020-05-30
	001,2020-06-17
	001,2020-06-25
	002,2020-10-20
	002,2020-10-21
	002,2020-10-22
	002,2020-10-23
	002,2020-11-10
	002,2020-11-11
	002,2020-11-17
	002,2020-11-30
	002,2020-12-17
	002,2020-12-25
	003,2020-10-01
	003,2020-10-02
	003,2020-10-11
	003,2020-10-31
	003,2020-11-01
	003,2020-11-02
	003,2020-11-03
	003,2020-11-04
	003,2020-12-17
	003,2020-12-18

1 按照行号添加行数

select id,day,row_number() over(partition by id order by day) as row1
from login;

2 行号和日期做差（如果是连续的，差会连续相同，计算的逻辑基础在这里）

select id,day,row1,date_sub(day,row1) as origin
from
    (select id,day,row_number() over(partition by id order by day) as row1
    from login) as t1;

3 id进行分组,统计次数

select id,count(origin) as continuity
from
    (select id,day,row1,date_sub(day,row1) as origin
    from
        (select 
            id,day,row_number() over(partition by id order by day) as row1
        from login)as t1
        )as t2
group by id,origin;

4 取出最大值，即最大连续登陆天数

select id,max(continuity)
from
    (select id,count(origin) as continuity
    from
        (select id,day,row1,date_sub(day,row1) as origin
        from
            (select 
            id,day,row_number() over(partition by id order by day) as row1
            from login) as t1
        ) as t2
    group by id,origin
    )as t3
group by id;

1、top N

1 关联学生表和成绩表，获得基础信息

select students.*,sum from students
inner join(
    select students.id,sum(score) as sum from students
    inner join score
    on students.id=score.id
    group by students.id) as t1
on students.id=t1.id;

2 加上列，获得排名

select
   students.*,sum,row_number() over(partition by clazz order by sum) as rank 
from students
    inner join(
        select students.id,sum(score) as sum from students
        inner join score
        on students.id=score.id
        group by students.id) as t1
    on students.id=t1.id;

3 不能直接使用 having，需要再嵌套一次查询

select * from(
  select 
    students.*,sum,row_number() over(partition by clazz order by sum) as rank
  from students
  inner join(
      select students.id,sum(score) as sum from students
      inner join score
      on students.id=score.id
   group by students.id) as t1
on students.id=t1.id) as t2
where rank<=3;

总结

1、查询后台服务

    ps aux | grep hive/redis...

2、包导不进去，就退出重进，完了刷新一下
在这里插入图片描述

韭菜盒子123
关注
HIVE數據仓库操作（数据库操作，表操作，开窗函数，炸裂函数，外部表，内部表，分区表，分桶表）
徐磊的博客
01-29 1941
HIVE介绍 Hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张表，并提供类SQL查询功能。本质： 将HQL转化成MapReduce程序 Hive在Hadoop生态中的位置如下图所示： hive和hadoop的关系： 1）Hive处理的数据存储在HDFS 2）Hive分析数据底层的实现是MapReduce 3）执行程序运行在Yarn上 Hive的基本操作 ...
Day57 Hive 分区表、数据加载和导出，简单数据查询以及函数
m0_64350718的博客
04-13 2549
分区表的创建，数据的加载，导出，数据类型及简单数据查询 各种基本函数及其使用
深入理解 Hive 分区分桶 （Inceptor）
热门推荐
little prince，blue coder
08-31 3万+
大数据核心原理与实践专栏 为何分区分桶 我们知道传统的DBMS系统一般都具有表分区的功能，通过表分区能够在特定的区域检索数据，减少扫描成本，在一定程度上提高查询效率，当然我们还可以通过进一步在分区上建立索引进一步提升查询效率。在此就不赘述了。 在Hive数仓中也有分区分桶的概念，在逻辑上分区表与未分区表没有区别，在物理上分区表会将数据按照分区键的列值存储在表目录的子目录中，目录名=“分区...
【sql】使用开窗函数求连续数据的分析思路
weixin_43990455的博客
02-03 590
【sql】ROW_NUMBER() OVER(PARTITION BY ... ORDER BY ...)
Hive分区和分桶
SunnyRivers
08-06 1975
Hive把表组织成分区（partition）。这是一种根据分区列（partition column，如日期）的值对表进行粗略的划分机制。使用分区可以加快数据分片（slice）的查询速度。 表或分区可以进一步划分为桶（bucket）。它会为数据提供额外的结构以获取更高效的查询处理。例如，通过根据用户ID来划分桶，我们可以在所有用户集合的随机样本上快速计算基于用户的查询。 分桶适用场景： 数据抽...
hive的分区与分桶
weixin_40645193的博客
02-08 215
【动态分区】通过股票案例进行说明(编程指南5.1)： 下面介绍动态分区，因为按照上面的方法向分区表插入数据，如果源数据量很大，针对一个分区就要写一个INSERT，非常麻烦。 况且在之前的版本中，必须先手动创建好所有的分区后才能插入，这就更麻烦了，必须要知道源数据中有什么样的数据才能创建分区。 使用动态分区可以很好的解决以上问题，动态分区可以根据查询得到数据自动匹配到相应的分区中去 1)整理数据stocks.cvs 上传到hdfs $>hadoop fs -put sto
Day56 Hive的安装与JDBC基本命令
m0_64350718的博客
04-10 2381
Hive的安装 前提： 安装hive所需要的虚拟机环境为虚拟机安装有Hadoop并且集群成功，同时Hadoop需要在启动状态下，同时需要安装有mysql。不需要有zookeeper和HA，由于HA中含有大量进程，启动会占用很多资源，建议不要有HA 安装步骤： 上传 将hive-3.1.2上传到虚拟机中的/usr/local/soft目录下 解压hive-3.1.2 输入命令： tar -zxvf apache-hive-3.1.2-bin.tar.gz 解压文件： 重命名： .
hive 修改分桶数 分桶表_hive 学习之路（五)、Hive的分区表与分桶表
weixin_32451529的博客
12-30 415
Hive将表划分为分区(partition)表和分桶(bucket)表。分区可以让数据的部分查询变得更快，也就是说，在加载数据的时候可以指定加载某一部分数据，并不是全量的数据。分桶表通常是在原始数据中加入一些额外的结构，这些结构可以用于高效的查询，例如，基于ID的分桶可以使得用户的查询非常的块。Hive分区表什么是分区表？Hive分区是指按照数据表的某一个字段或多个字段进行统一归类，并存储在在hd...
Hive编程指南-JDBC连接、脚本执行
Evankaka的专栏
02-14 3830
摘要：本文主要讲了如何通过java来连接Hive,以及如何执行hive脚本
SQL高频面试题_实现用户连续登陆的最大天数(Hive/开窗函数/Oracle)
清平乐的技术专栏
03-25 2179
题目：某张表两个字段分别是用户ID和登录时间。例如记录了用户id为001和002、003的登陆日期，现在问题来了：我们如何统计处两个用户各自连续登陆的天数最大值？也就是001可能会有连续登陆3天，7天，10天的这样记录，但是这其中只有10天是001连续登陆的最长天数。 一、数据集准备 这里数据集以Hive为例 -- 表创建 DROP TABLE IF EXISTS test.user_login; CREATE TABLE test.user_login ( user_id string, login_d
3 Hive的数据类型和文件格式、数据单元、基本数据类型、复合类型、JDBC数据类型、内置运算符和函数、HIVE命令和CLI（命令设置，Resource、命令行操作、批处理模式）、HIVE文件格式
涂作权的博客
01-19 1502
3Hive的数据类型和文件格式等 3.1数据单元 Databases:命名空间的功能，用于避免表，视图，分区，列的命名冲突，Databases也可以用于增强一个用户或用户组的的安全性。 Tables: 具有相同schema数据存储的位置。 Partitions:每个表可以拥有一个或多个partitions keys，这些key用于决定数据是如何存储的。 Buckets (or Clusters):...
Hive学习——开窗函数精讲
雷神乐乐的博客
02-24 2729
Hive中开窗函数十分重要，很多业务需求都可以用开窗函数解决
Hive(三)--窗口函数
jiandanbuguo的博客
09-25 321
窗口函数 窗口函数是用于分析用的一类函数，要理解窗口函数要先从聚合函数说起。聚合函数是将某列中多行的值合并为一行，比如sum、count等。 而窗口函数则可以在本行内做运算，得到多行的结果，即每一行对应一行的值。 通用的窗口函数可以用下面的语法来概括： Function() Over (Partition By Column1，Column2，Order By Column3) 窗口函数又分为以下三类： 聚合型窗口函数、分析型窗口函数、取值型窗口函数 数据准备 saml,2018-01-01,10 ton
mysql中的where和having子句的区别(有时易与Hive中的开窗函数混淆)
weixin_43390992的博客
02-27 548
下面以一个例子来具体的讲解： where和having都可以使用的场景 1）select addtime,name from dw_users where addtime> 1500000000 2）select addtime,name from dw_users having addtime> 1500000000 解释：上面的having可以用的前提是我已经筛选出了ad...
用偏移类开窗函数 统计每日新老客户数量
apple_任
09-02 718
CREATE TABLE 订单表 ( 购买时间 VARCHAR2(20), 用户名 VARCHAR2(20)); insert into 订单表 (购买时间, 用户名) values (‘2017-01-01 22:01:23’, ‘张三’); insert into 订单表 (购买时间, 用户名) values (‘2017-01-02 14:20:02’, ‘张三’); insert into 订单表 (购买时间, 用户名) values (‘2017-01-02 23:13:55’, ‘李四’);
查询连续登陆7天以上的用户 Mysql
lipeitong333的博客
02-24 1904
查询7天连续登陆用户这个问题很经典，解决方法也有很多，这里我讲一下笔者的方法，希望对大家有帮助。 具体思路: 1、因为每天用户登录次数可能不止一次，所以需要先将用户每天的登录日期去重。 2、再用row_number() over(partition by _ order by _)函数将用户id分组，按照登陆时间进行排序。 3、计算登录日期减去第二步骤得到的结果值，用户连续登陆情况下，每次相减的结果都相同。 4、按照id和日期分组并求和，筛选大于等于7的即为连续7天登陆的用户。 表信息如下图：
hive之连续登录问题
weixin_43597208的博客
02-27 4475
目录1、开窗函数的格式2、窗口范围图例连续登录问题：限制时间段内登录次数问题： 1、开窗函数的格式 FUNCTION_NAME([argument_list]) OVER ( [PARTITION BY window_partition,…] [ORDER BY window_ordering, … [ASC|DESC]]) [ { ROWS | RANGE } BETWEEN frame_start AND frame_end ] ); FUNCTION_NAME：函数名称。如row_numbe
mysql as用法_一文搞懂 MySQL 的窗口函数
weixin_39980711的博客
11-28 1024
窗口函数在统计类的需求中很常见，稍微复杂一点的查询需求就有可能用到它，使用窗口函数可以极大的简化我们的 SQL 语句。像 Oracle、SQL Server 这些数据库在较早的版本就支持窗口函数了，MySQL 直到 8.0 版本后才支持它。本文将介绍一些常用的窗口函数的用法。窗口函数按照实现方式分成两种：一种是非聚合窗口函数，另外一种是聚合窗口函数。非聚合窗口函数是相对于聚合窗口函数来说...
Hive分区、分桶操作及其比较
epitomizelu的专栏
12-13 2万+
1，Hive分区。 是指按照数据表的某列或某些列分为多个区，区从形式上可以理解为文件夹，比如我们要收集某个大型网站的日志数据，一个网站每天的日志数据存在同一张表上，由于每天会生成大量的日志，导致数据表的内容巨大，在查询时进行全表扫描耗费的资源非常多。那其实这个情况下，我们可以按照日期对数据表进行分区，不同日期的数据存放在不同的分区，在查询时只要指定分区字段的值就可以直接从该分区查找。
hive分区/分桶的意思
最新发布
07-03
Hive 分区（Partitioning）和分桶（Bucketing）是Hadoop生态系统中的一个重要概念，特别是在大数据分析工具Hive中，它们被用来提高数据查询效率和管理大规模数据。 1. **分区**（Partitioning）： 在Hive中，分区...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值