大数据开发之Hive篇7-Hive临时表

た入场券 2022-12-31 04:26 141阅读 0赞

备注:  
Hive 版本 2.1.1

# 一.Hive临时表介绍 #

作为临时表创建的表将只对当前会话可见。数据将存储在用户的scratch目录中，并在会话结束时删除。

如果用数据库中已经存在的永久表的数据库/表名创建了一个临时表，那么在该会话中，对该表的任何引用都将解析为临时表，而不是永久表。如果不删除临时表或将其重命名为不冲突的名称，用户将无法在该会话中访问原始表。

临时表存在如下限制:

1.  不支持分区列
2.  不支持创建索引

Hive的临时表在数据加载的过程中会频繁使用到，对于复杂的业务逻辑，可以将数据先存储在临时表，然后再从临时表取值进行进一步计算。

# 二.Hive临时表测试 #

语法:

CREATE TEMPORARY TABLE ...

## 2.1 Hive临时表数据存储设置 ##

从Hive1.1开始临时表可以存储在内存或SSD，使用hive.exec.temporary.table.storage参数进行配置，该参数有三种取值：memory、ssd、default。

如果内存足够大，将中间数据一直存储在内存，可以大大提升计算性能。

**测试记录:**  
将hive临时表存储路径改为memory(内存)

hive> 
        > set hive.exec.temporary.table.storage;
    hive.exec.temporary.table.storage=default
    hive> 
        > set hive.exec.temporary.table.storage = memory;
    hive> set hive.exec.temporary.table.storage;
    hive.exec.temporary.table.storage=memory
    hive>

## 2.1 Hive临时表测试 ##

代码:

set hive.exec.temporary.table.storage = memory;
    -- 创建临时表 存储在内存中
    create temporary table tmp_fact_sale as
    select * from ods_fact_sale limit 1000000;

测试记录:

hive> set hive.exec.temporary.table.storage = memory;
    hive> create temporary table tmp_fact_sale as
        > select * from ods_fact_sale limit 1000000;
    Query ID = root_20201211145008_ce21643b-765b-4869-8069-9ef340924fbb
    Total jobs = 1
    Launching Job 1 out of 1
    Number of reduce tasks determined at compile time: 1
    In order to change the average load for a reducer (in bytes):
      set hive.exec.reducers.bytes.per.reducer=<number>
    In order to limit the maximum number of reducers:
      set hive.exec.reducers.max=<number>
    In order to set a constant number of reducers:
      set mapreduce.job.reduces=<number>
    Starting Job = job_1606698967173_0180, Tracking URL = http://hp1:8088/proxy/application_1606698967173_0180/
    Kill Command = /opt/cloudera/parcels/CDH-6.3.1-1.cdh6.3.1.p0.1470567/lib/hadoop/bin/hadoop job  -kill job_1606698967173_0180
    Hadoop job information for Stage-1: number of mappers: 117; number of reducers: 1
    2020-12-11 14:50:17,943 Stage-1 map = 0%,  reduce = 0%
    2020-12-11 14:50:27,293 Stage-1 map = 2%,  reduce = 0%, Cumulative CPU 12.2 sec
    2020-12-11 14:50:33,505 Stage-1 map = 3%,  reduce = 0%, Cumulative CPU 18.16 sec
    2020-12-11 14:50:39,715 Stage-1 map = 5%,  reduce = 0%, Cumulative CPU 36.02 sec
    2020-12-11 14:50:44,867 Stage-1 map = 6%,  reduce = 0%, Cumulative CPU 41.96 sec
    2020-12-11 14:50:45,890 Stage-1 map = 7%,  reduce = 0%, Cumulative CPU 47.92 sec
    2020-12-11 14:50:51,029 Stage-1 map = 8%,  reduce = 0%, Cumulative CPU 53.84 sec
    2020-12-11 14:50:52,058 Stage-1 map = 9%,  reduce = 0%, Cumulative CPU 59.73 sec
    2020-12-11 14:50:58,223 Stage-1 map = 10%,  reduce = 0%, Cumulative CPU 71.63 sec
    2020-12-11 14:51:03,359 Stage-1 map = 11%,  reduce = 0%, Cumulative CPU 77.7 sec
    2020-12-11 14:51:04,388 Stage-1 map = 12%,  reduce = 0%, Cumulative CPU 83.54 sec
    2020-12-11 14:51:09,525 Stage-1 map = 13%,  reduce = 0%, Cumulative CPU 89.35 sec
    2020-12-11 14:51:10,551 Stage-1 map = 14%,  reduce = 0%, Cumulative CPU 95.31 sec
    2020-12-11 14:51:15,688 Stage-1 map = 15%,  reduce = 0%, Cumulative CPU 101.28 sec
    2020-12-11 14:51:20,823 Stage-1 map = 16%,  reduce = 0%, Cumulative CPU 113.07 sec
    2020-12-11 14:51:21,844 Stage-1 map = 17%,  reduce = 0%, Cumulative CPU 118.94 sec
    2020-12-11 14:51:26,978 Stage-1 map = 18%,  reduce = 0%, Cumulative CPU 124.9 sec
    2020-12-11 14:51:28,003 Stage-1 map = 19%,  reduce = 0%, Cumulative CPU 130.83 sec
    2020-12-11 14:51:33,141 Stage-1 map = 20%,  reduce = 0%, Cumulative CPU 136.74 sec
    2020-12-11 14:51:34,158 Stage-1 map = 21%,  reduce = 0%, Cumulative CPU 142.68 sec
    2020-12-11 14:51:40,312 Stage-1 map = 22%,  reduce = 0%, Cumulative CPU 154.46 sec
    2020-12-11 14:51:45,442 Stage-1 map = 23%,  reduce = 0%, Cumulative CPU 160.54 sec
    2020-12-11 14:51:46,467 Stage-1 map = 24%,  reduce = 0%, Cumulative CPU 166.32 sec
    2020-12-11 14:51:51,597 Stage-1 map = 25%,  reduce = 0%, Cumulative CPU 172.2 sec
    2020-12-11 14:51:52,621 Stage-1 map = 26%,  reduce = 0%, Cumulative CPU 178.17 sec
    2020-12-11 14:51:58,769 Stage-1 map = 27%,  reduce = 0%, Cumulative CPU 190.11 sec
    2020-12-11 14:52:03,894 Stage-1 map = 28%,  reduce = 0%, Cumulative CPU 196.14 sec
    2020-12-11 14:52:04,919 Stage-1 map = 29%,  reduce = 0%, Cumulative CPU 202.0 sec
    2020-12-11 14:52:09,020 Stage-1 map = 30%,  reduce = 0%, Cumulative CPU 207.92 sec
    2020-12-11 14:52:10,046 Stage-1 map = 31%,  reduce = 0%, Cumulative CPU 213.76 sec
    2020-12-11 14:52:15,172 Stage-1 map = 32%,  reduce = 0%, Cumulative CPU 219.74 sec
    2020-12-11 14:52:21,319 Stage-1 map = 33%,  reduce = 0%, Cumulative CPU 231.61 sec
    2020-12-11 14:52:22,345 Stage-1 map = 34%,  reduce = 0%, Cumulative CPU 237.48 sec
    2020-12-11 14:52:27,469 Stage-1 map = 35%,  reduce = 0%, Cumulative CPU 243.45 sec
    2020-12-11 14:52:28,496 Stage-1 map = 36%,  reduce = 0%, Cumulative CPU 249.35 sec
    2020-12-11 14:52:33,612 Stage-1 map = 37%,  reduce = 0%, Cumulative CPU 255.3 sec
    2020-12-11 14:52:34,639 Stage-1 map = 38%,  reduce = 0%, Cumulative CPU 261.1 sec
    2020-12-11 14:52:40,785 Stage-1 map = 39%,  reduce = 0%, Cumulative CPU 272.88 sec
    2020-12-11 14:52:46,937 Stage-1 map = 41%,  reduce = 0%, Cumulative CPU 284.69 sec
    2020-12-11 14:52:53,095 Stage-1 map = 43%,  reduce = 0%, Cumulative CPU 296.82 sec
    2020-12-11 14:52:58,223 Stage-1 map = 44%,  reduce = 0%, Cumulative CPU 308.7 sec
    2020-12-11 14:53:04,368 Stage-1 map = 46%,  reduce = 0%, Cumulative CPU 320.54 sec
    2020-12-11 14:53:10,513 Stage-1 map = 48%,  reduce = 0%, Cumulative CPU 332.43 sec
    2020-12-11 14:53:16,666 Stage-1 map = 50%,  reduce = 0%, Cumulative CPU 344.36 sec
    2020-12-11 14:53:22,821 Stage-1 map = 51%,  reduce = 0%, Cumulative CPU 356.42 sec
    2020-12-11 14:53:28,967 Stage-1 map = 53%,  reduce = 0%, Cumulative CPU 367.86 sec
    2020-12-11 14:53:35,116 Stage-1 map = 55%,  reduce = 0%, Cumulative CPU 379.76 sec
    2020-12-11 14:53:40,245 Stage-1 map = 56%,  reduce = 0%, Cumulative CPU 385.66 sec
    2020-12-11 14:53:46,384 Stage-1 map = 58%,  reduce = 0%, Cumulative CPU 403.65 sec
    2020-12-11 14:53:52,533 Stage-1 map = 60%,  reduce = 0%, Cumulative CPU 415.55 sec
    2020-12-11 14:53:58,675 Stage-1 map = 62%,  reduce = 0%, Cumulative CPU 427.62 sec
    2020-12-11 14:54:04,822 Stage-1 map = 63%,  reduce = 0%, Cumulative CPU 439.61 sec
    2020-12-11 14:54:10,960 Stage-1 map = 65%,  reduce = 0%, Cumulative CPU 451.7 sec
    2020-12-11 14:54:17,103 Stage-1 map = 67%,  reduce = 0%, Cumulative CPU 463.6 sec
    2020-12-11 14:54:23,247 Stage-1 map = 68%,  reduce = 0%, Cumulative CPU 475.61 sec
    2020-12-11 14:54:29,385 Stage-1 map = 70%,  reduce = 0%, Cumulative CPU 487.57 sec
    2020-12-11 14:54:34,506 Stage-1 map = 72%,  reduce = 0%, Cumulative CPU 499.7 sec
    2020-12-11 14:54:40,655 Stage-1 map = 74%,  reduce = 0%, Cumulative CPU 511.71 sec
    2020-12-11 14:54:46,797 Stage-1 map = 75%,  reduce = 0%, Cumulative CPU 523.65 sec
    2020-12-11 14:54:52,942 Stage-1 map = 77%,  reduce = 0%, Cumulative CPU 535.63 sec
    2020-12-11 14:54:59,085 Stage-1 map = 79%,  reduce = 0%, Cumulative CPU 547.45 sec
    2020-12-11 14:55:05,210 Stage-1 map = 80%,  reduce = 0%, Cumulative CPU 559.48 sec
    2020-12-11 14:55:11,350 Stage-1 map = 82%,  reduce = 0%, Cumulative CPU 571.5 sec
    2020-12-11 14:55:17,490 Stage-1 map = 83%,  reduce = 0%, Cumulative CPU 577.53 sec
    2020-12-11 14:55:22,609 Stage-1 map = 84%,  reduce = 0%, Cumulative CPU 583.64 sec
    2020-12-11 14:55:25,693 Stage-1 map = 84%,  reduce = 13%, Cumulative CPU 601.24 sec
    2020-12-11 14:55:28,777 Stage-1 map = 85%,  reduce = 13%, Cumulative CPU 607.09 sec
    2020-12-11 14:55:31,848 Stage-1 map = 85%,  reduce = 17%, Cumulative CPU 614.74 sec
    2020-12-11 14:55:37,991 Stage-1 map = 85%,  reduce = 24%, Cumulative CPU 629.05 sec
    2020-12-11 14:55:41,065 Stage-1 map = 86%,  reduce = 24%, Cumulative CPU 635.01 sec
    2020-12-11 14:55:44,139 Stage-1 map = 86%,  reduce = 29%, Cumulative CPU 642.69 sec
    2020-12-11 14:55:47,204 Stage-1 map = 87%,  reduce = 29%, Cumulative CPU 648.56 sec
    2020-12-11 14:55:53,349 Stage-1 map = 88%,  reduce = 29%, Cumulative CPU 654.39 sec
    2020-12-11 14:55:58,483 Stage-1 map = 89%,  reduce = 29%, Cumulative CPU 660.45 sec
    2020-12-11 14:56:01,557 Stage-1 map = 89%,  reduce = 30%, Cumulative CPU 662.63 sec
    2020-12-11 14:56:04,630 Stage-1 map = 90%,  reduce = 30%, Cumulative CPU 668.38 sec
    2020-12-11 14:56:10,763 Stage-1 map = 91%,  reduce = 30%, Cumulative CPU 674.14 sec
    2020-12-11 14:56:23,051 Stage-1 map = 92%,  reduce = 30%, Cumulative CPU 686.08 sec
    2020-12-11 14:56:26,125 Stage-1 map = 92%,  reduce = 31%, Cumulative CPU 686.23 sec
    2020-12-11 14:56:29,197 Stage-1 map = 93%,  reduce = 31%, Cumulative CPU 692.1 sec
    2020-12-11 14:56:35,339 Stage-1 map = 94%,  reduce = 31%, Cumulative CPU 698.13 sec
    2020-12-11 14:56:41,482 Stage-1 map = 95%,  reduce = 31%, Cumulative CPU 704.08 sec
    2020-12-11 14:56:44,556 Stage-1 map = 95%,  reduce = 32%, Cumulative CPU 704.21 sec
    2020-12-11 14:56:46,604 Stage-1 map = 96%,  reduce = 32%, Cumulative CPU 710.03 sec
    2020-12-11 14:56:52,742 Stage-1 map = 97%,  reduce = 32%, Cumulative CPU 718.18 sec
    2020-12-11 14:57:05,022 Stage-1 map = 98%,  reduce = 32%, Cumulative CPU 730.17 sec
    2020-12-11 14:57:08,092 Stage-1 map = 98%,  reduce = 33%, Cumulative CPU 730.49 sec
    2020-12-11 14:57:11,164 Stage-1 map = 99%,  reduce = 33%, Cumulative CPU 736.39 sec
    2020-12-11 14:57:17,311 Stage-1 map = 100%,  reduce = 33%, Cumulative CPU 742.22 sec
    2020-12-11 14:57:20,387 Stage-1 map = 100%,  reduce = 67%, Cumulative CPU 747.55 sec
    2020-12-11 14:58:20,783 Stage-1 map = 100%,  reduce = 67%, Cumulative CPU 809.07 sec
    2020-12-11 14:58:35,129 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 823.71 sec
    MapReduce Total cumulative CPU time: 13 minutes 43 seconds 710 msec
    Ended Job = job_1606698967173_0180
    Moving data to directory hdfs://nameservice1/tmp/hive/root/e6af3fe0-c2f2-4bc5-8d3f-54edbc9af7ac/_tmp_space.db/488a6e38-d7a0-425c-892a-73547bdfec51
    MapReduce Jobs Launched: 
    Stage-Stage-1: Map: 117  Reduce: 1   Cumulative CPU: 823.71 sec   HDFS Read: 4798139962 HDFS Write: 41066220 HDFS EC Read: 0 SUCCESS
    Total MapReduce CPU Time Spent: 13 minutes 43 seconds 710 msec
    OK
    Time taken: 507.819 seconds