1. Toy模板网首页
  2. > Toy博客

Hive基础知识（十六）：Hive-SQL分区表使用与优化

7月前 作者：依晴无旧 分类：Toy博客 阅读(29) 违法举报

这篇具有很好参考价值的文章主要介绍了Hive基础知识（十六）：Hive-SQL分区表使用与优化。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

1. 分区表

分区表实际上就是对应一个 HDFS 文件系统上的独立的文件夹，该文件夹下是该分区所有的数据文件。Hive 中的分区就是分目录，把一个大的数据集根据业务需要分割成小的数据集。在查询时通过 WHERE 子句中的表达式选择查询所需要的指定的分区，这样的查询效率会提高很多。

2. 分区表基本操作

1）引入分区表（需要根据日期对日志进行管理,通过部门信息模拟）

Hive基础知识（十六）：Hive-SQL分区表使用与优化,大数据,hive,hadoop,数据仓库

2）创建分区表语法

hive (hive3)> create table dept_par(deptno int , dname string, loc string) partitioned by (day string) row format delimited fields terminated by'';
OK
Time taken: 2.547 seconds

注意：分区字段不能是表中已经存在的数据，可以将分区字段看作表的伪列。

3）加载数据到分区表中

hive (hive3)> load data local inpath '/home/zzdq/hive/dept_20200401.log' into table dept_par partition(day='20200401');
Loading data to table hive3.dept_par partition (day=20200401)
OK
Time taken: 1.763 seconds
hive (hive3)> load data local inpath '/home/zzdq/hive/dept_20200402.log' into table dept_par partition(day='20200402');
Loading data to table hive3.dept_par partition (day=20200402)
OK
Time taken: 0.962 seconds
hive (hive3)> load data local inpath '/home/zzdq/hive/dept_20200403.log' into table dept_par partition(day='20200403');
Loading data to table hive3.dept_par partition (day=20200403)
OK
Time taken: 0.869 seconds

4）查找全部数据（可以看到多了个分区字段，但这个字段不是放在表中，而是放在目录上，所以条件查分区的效率会高很多）

hive (hive3)> select * from  dept_par;
OK
dept_par.deptno dept_par.dname  dept_par.loc  dept_par.day
10  ACCOUNTING 1700 20200401
20  RESEARCH 1800 20200401
30  SALES 1900 20200402
40  OPERATIONS 1700 20200402
50  TEST 2000 20200403
60  DEV 1900 20200403
NULL  NULL  NULL 20200403
Time taken: 3.684 seconds, Fetched: 7 row(s)

5）条件查询

hive (hive3)> select * from  dept_par where day = 20200401;
OK
dept_par.deptno dept_par.dname  dept_par.loc  dept_par.day
10  ACCOUNTING 1700 20200401
20  RESEARCH 1800 20200401
Time taken: 2.749 seconds, Fetched: 2 row(s)

另外：分区的信息也存放在mysql的partition表中。

3. 分区的增删改查

单分区查询

hive (default)> select * from dept_partition where day='20200401';

多分区联合查询

hive (default)> select * from dept_partition where day='20200401'
 union
 select * from dept_partition where day='20200402'
 union
 select * from dept_partition where day='20200403';
hive (default)> select * from dept_partition where day='20200401' or day='20200402' or day='20200403';

5）增加分区

创建单个分区

hive (default)> alter table dept_partition add partition(day='20200404');

创建多个分区

hive (hive3)> alter table dept_par add partition(day='20200404') partition(day='20200405') partition(day='20200406');
OK
Time taken: 0.844 seconds

Hive基础知识（十六）：Hive-SQL分区表使用与优化,大数据,hive,hadoop,数据仓库

6）删除分区

删除单个分区

hive (default)> alter table dept_partition drop partition (day='20200406');

同时删除多个分区

hive (hive3)> alter table dept_par drop partition(day='20200404'),partition(day='20200405'),partition(day='20200406');
Dropped the partition day=20200404
Dropped the partition day=20200405
Dropped the partition day=20200406
OK
Time taken: 0.981 seconds

7）查看分区表有多少分区

hive (hive3)> show partitions dept_par;
OK
partition
day=20200401
day=20200402
day=20200403
Time taken: 0.296 seconds, Fetched: 3 row(s)

8）查看分区表结构

hive (hive3)> desc formatted dept_par;
# Partition Information 
# col_name  data_type  comment 
day  string

4. 二级分区

思考: 如何一天的日志数据量也很大，如何再将数据拆分?

1）创建二级分区表

hive (hive3)> create table dept_par2(deptno int , dname string , loc string) partitioned by (day string , hour string) row format delimited fields terminated by '';
OK
Time taken: 0.172 seconds

2）正常的加载数据

hive (hive3)> load data local inpath "/home/zzdq/hive/dept_20200401.log" into table dept_par2 partition(day='001',hour='401');
Loading data to table hive3.dept_par2 partition (day=001, hour=401)
OK
Time taken: 1.221 seconds
hive (hive3)> load data local inpath "/home/zzdq/hive/dept_20200402.log" into table dept_par2 partition(day='001',hour='402');
Loading data to table hive3.dept_par2 partition (day=001, hour=402)
OK
Time taken: 0.899 seconds
hive (hive3)> load data local inpath "/home/zzdq/hive/dept_20200403.log" into table dept_par2 partition(day='001',hour='403');
Loading data to table hive3.dept_par2 partition (day=001, hour=403)
OK
Time taken: 1.011 seconds
hive (hive3)> load data local inpath "/home/zzdq/hive/dept_20200403.log" into table dept_par2 partition(day='002',hour='404');
Loading data to table hive3.dept_par2 partition (day=001, hour=404)
OK
Time taken: 0.878 seconds

Hive基础知识（十六）：Hive-SQL分区表使用与优化,大数据,hive,hadoop,数据仓库

查询

hive (hive3)> select * from dept_par2 where day = '001' and hour = '404';
OK
dept_par2.deptno  dept_par2.dname dept_par2.loc  dept_par2.day  dept_par2.hour
50  TEST 2000 001 404
60  DEV 1900 001 404
NULL  NULL  NULL 001 404
Time taken: 0.692 seconds, Fetched: 3 row(s)

3）把数据直接上传到分区目录上，让分区表和数据产生关联的三种方式

（1）方式一：上传数据后修复

上传数据

hive (default)> dfs -mkdir -p /user/hive/warehouse/mydb.db/dept_partition2/day=20200401/hour=13;
hive (default)> dfs -put /opt/module/datas/dept1.txt /user/hive/warehouse/mydb.db/dept_partition2/day=20200401/hour=13;

查询数据（查询不到刚上传的数据）

hive (default)> select * from dept_partition2 where day='20200401' and hour='13';

让分区信息产生关联的操作：执行修复命令

hive> msck repair table dept_partition2;

再次查询数据

hive (default)> select * from dept_partition2 where day='20200401' and hour='13';

（2）方式二：上传数据后添加分区

上传数据

hive (default)> dfs -mkdir -p /user/hive/warehouse/mydb.db/dept_partition2/day=20200401/hour=14; 
hive (default)> dfs -put /opt/module/hive/datas/dept_20200401.log /user/hive/warehouse/mydb.db/dept_partition2/day=20200401/hour=14;

执行添加分区

hive (default)> alter table dept_partition2 add  partition(day='201709',hour='14');

查询数据

hive (default)> select * from dept_partition2 where day='20200401' and  hour='14';

（3）方式三：创建文件夹后,使用load

load 数据到分区创建目录

hive (default)> dfs -mkdir -p /user/hive/warehouse/mydb.db/dept_partition2/day=20200401/hour=15;

上传数据

hive (default)> load data local inpath '/opt/module/hive/datas/dept_20200401.log' into table dept_partition2 partition(day='20200401',hour='15');

查询数据

hive (default)> select * from dept_partition2 where day='20200401' and  hour='15';

5. 动态分区调整

关系型数据库中，对分区表 Insert 数据时候，数据库自动会根据分区字段的值，将数据插入到相应的分区中，Hive 中也提供了类似的机制，即动态分区(Dynamic Partition)，只不过，使用 Hive 的动态分区，需要进行相应的配置。

1）开启动态分区参数设置

（1）开启动态分区功能（默认 true，开启）

hive.exec.dynamic.partition=true

（2）设置为非严格模式（动态分区的模式，默认 strict，表示必须指定至少一个分区为静态分区，nonstrict 模式表示允许所有的分区字段都可以使用动态分区。）

hive (hive3)> set hive.exec.dynamic.partition.mode=nonstrict;

（3）在所有执行 MR 的节点上，最大一共可以创建多少个动态分区。默认1000

hive.exec.max.dynamic.partitions=1000

（4）在每个执行 MR 的节点上，最大可以创建多少个动态分区。该参数需要根据实际的数据来设定。比如：源数据中包含了一年的数据，即 day 字段有365 个值，那么该参数就需要设置成大于365，如果使用默认值100，则会报错。（4）在每个执行 MR 的节点上，最大可以创建多少个动态分区。该参数需要根据实际的数据来设定。比如：源数据中包含了一年的数据，即 day 字段有365 个值，那么该参数就需要设置成大于365，如果使用默认值100，则会报错。

hive.exec.max.dynamic.partitions.pernode=100

（5）整个 MR Job 中，最大可以创建多少个 HDFS 文件。默认100000

hive.exec.max.created.files=100000

（6）当有空分区生成时，是否抛出异常。一般不需要设置。默认 false

hive.error.on.empty.partition=false

2）案例实操

需求：将 dept 表中的数据按照地区（loc 字段），插入到目标表 dept_partition 的相应分区中。

（1）创建目标分区表

hive (hive3)> create table dept_no_par(dname string, loc string) partitioned by (deptno int) row format delimited fields terminated by '';
OK
Time taken: 0.185 seconds

（2）设置动态分区

hive (hive3)> set hive.exec.dynamic.partition.mode=nonstrict;

(3)导入数据

hive (hive3)>insert into table dept_no_par partition(deptno) select dname,loc,deptno from dept;
OK
Time taken: 0.062 seconds
Query ID = atguigu_20211219145313_ad874c4a-da69-43f2-a0bb-6e0e9ea0ce6c
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1
2021-12-19 14:53:33,924 Stage-1 map = 0%, reduce = 0%
2021-12-19 14:53:41,638 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 2.93 sec
2021-12-19 14:53:51,240 Stage-1 map = 100%, reduce = 100%, Cumulative CPU 4.7 sec
MapReduce Total cumulative CPU time: 4 seconds 700 msec
Ended Job = job_1639880318289_0007
MapReduce Jobs Launched: 
Stage-Stage-1: Map: 1  Reduce: 1  Cumulative CPU: 4.7 sec  HDFS Read: 14680 HDFS Write: 773 SUCCESS
Total MapReduce CPU Time Spent: 4 seconds 700 msec
OK
dname  loc  deptno
Time taken: 42.654 seconds

（4）查看目标分区表的分区情况

hive (hive3)>  show partitions dept_no_par;
OK
partition
deptno=10
deptno=20
deptno=30
deptno=40
deptno=70
Time taken: 0.39 seconds, Fetched: 5 row(s)

动态分区也可以这么写（3.0新增）：文章来源地址https://www.toymoban.com/news/detail-799811.html

hive (hive3)> create table dept_no_par2(dname string, loc string) partitioned by (deptno int) row format delimited fields terminated by '';
OK
Time taken: 0.188 seconds
hive (hive3)> insert into table dept_no_par2 select dname,loc,deptno from dept;
hive (hive3)>  show partitions dept_no_par2;
OK
partition
deptno=10
deptno=20
deptno=30
deptno=40
Time taken: 0.137 seconds, Fetched: 4 row(s)

到了这里，关于Hive基础知识（十六）：Hive-SQL分区表使用与优化的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处： 如若内容造成侵权/违法违规/事实不符，请点击违法举报进行投诉反馈，一经查实，立即删除！

分享到：
领支付宝红包 赞助服务器费用

相关文章

  • hive-sql保留n位小数

            hive保留4位小数的几种方法。同时求助计算结果小数位数少于4位时，可以在结尾补0的方法。 1. 四舍五入 （1）round(待转换结果, n)         n:保留小数位数         缺点：round有时会将计算结果展示成科学计数法。会有精度丢失现象，产生奇怪的结果。         其他

    2024年02月11日
    浏览(23)
  • 【前端知识】React 基础巩固(二十六)——Portals 的使用

    【前端知识】React 基础巩固(二十六)——Portals 的使用

    通常，组件会渲染到 root 节点下。可使用 Portals 将组件渲染至其他节点。 添加 id 为 more、modal 的 div 元素 构建 Modal.jsx, 通过 createPortal 把组件渲染到指定的 modal 节点下 构建 App.jsx 查看渲染结果

    2024年02月16日
    浏览(31)

  • 【Hive-SQL】Hive Select 选择语句排除一列或多列

    查看 除了sample_date 以外的所有字段信息 查看 除了sample_date 和 msgtype 以外的所有字段信息 上面的 set hive.support.quoted.identifiers=none; 可以替换操作： 在 \\\'hive-site.xml\\\' 中添加以下配置： hive.support.quoted.identifiers=none

    2024年02月10日
    浏览(28)
  • 【前端知识】React 基础巩固(三十六)——RTK中的异步操作

    【前端知识】React 基础巩固(三十六)——RTK中的异步操作

    引入RTK中的createAsyncThunk，在extraReducers中监听执行状态 在界面中引入所需的异步操作Action 查看运行结果 extraReducer还可以传入一个函数，函数接受一个builder参数： 查看运行结果，与之前的写法结果一致

    2024年02月15日
    浏览(34)
  • 【前端知识】React 基础巩固(四十六)——自定义Hook的应用

    【前端知识】React 基础巩固(四十六)——自定义Hook的应用

    自定义Hook本质上只是一种函数代码逻辑的抽取，严格意义上而言，它并不算React的特性。 实现组件创建/销毁时打印日志 实现Context共享 封装 TokenContext 和 UserContext 两个Context在自定义Hook useUserToken 中，通过使用 userUserToken 同时获取两个Context的内容： 实现获取滚动位置 封装滚

    2024年02月14日
    浏览(31)
  • 【STM32】基础知识 第十六课 窗口看门狗 WWDG 深入浅出

    【STM32】基础知识 第十六课 窗口看门狗 WWDG 深入浅出

    在嵌入式开发中, 可靠性和稳定性是至关重要的. 这就是为什么许多单片机, 比如 STM32, 提供了窗口看门狗 (Window Watchdog, WWDF) 的功能. WWDG 是一种硬件定时器, 其目的在于防止软件错误导致的系统故障. WWDG 是通过监控软件运行的正常新, 并在检测到异常情况时自动重启系统, 从而

    2024年02月16日
    浏览(21)
  • Hive基础知识

    Hive基础知识

      Hive是由Facebook开源用于解决海量 结构化 日志的数据统计工具。Hive是基于Hadoop的一个 数据仓库工具 ，可以将结 构化的数据文件映射为一张表 ，并提供 类SQL 查询功能。 Hive的本质是将HQL转化成MapReduce程序。 Hive处理的数据存储在HDFS。 Hive分析数据底层的实现是MapReduce。

    2024年02月11日
    浏览(24)
  • hive 基础知识

    hive 基础知识

    在本节前我们需要明确 hive 是什么 上面两个代码块，左边的是 mapreduce 的代码块，右边的是hive 的代码块 很容易看出来，右边的 hive 写起来要更容易更快些，而执行效率，右边的 hive 只比左边多一个翻译的过程，就是将写的 HQL语句 翻译成 mapreduce 去执行 简单来说 hive 就是一

    2024年02月09日
    浏览(23)
  • 一百八十六、大数据离线数仓完整流程——步骤五、在Hive的DWS层建动态分区表并动态加载数据

    一百八十六、大数据离线数仓完整流程——步骤五、在Hive的DWS层建动态分区表并动态加载数据

    经过6个月的奋斗，项目的离线数仓部分终于可以上线了，因此整理一下离线数仓的整个流程，既是大家提供一个案例经验，也是对自己近半年的工作进行一个总结。 1、Hive的DWS层建库建表语句 --如果不存在则创建hurys_dc_dws数据库 create database if not exists hurys_dc_dws; --使用hurys_

    2024年02月07日
    浏览(32)
  • Hive基础知识（十一）：Hive的数据导出方法示例

    Hive基础知识（十一）：Hive的数据导出方法示例

    1）将查询的结果导出到本地 2）将查询的结果格式化导出到本地（加上一个以“,”隔开数据的格式） 3）将查询的结果导出到 HDFS 上(没有 local) 基本语法：（hive -f/-e 执行语句或者脚本 file） 导出的数据中有两个数据源，其中除了主信息之外，还包括记录主数据信息的元数据

    2024年01月22日
    浏览(28)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包，优惠每天领

二维码1

领取红包

二维码2

领红包