大数据开发之Hive篇2-Hive数据类型及文件格式

最新推荐文章于 2024-09-22 00:02:24 发布
最新推荐文章于 2024-09-22 00:02:24 发布
阅读量1.8k 收藏 6
点赞数 2
分类专栏： # Hive 大数据和数据仓库 文章标签： 大数据 hive 文件格式 orc text file
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/u010520724/article/details/111357996
版权

备注:
Hive 版本 2.1.1

文章目录

一.Hive的数据类型

1.1 数值类型

Hive的数值类型与关系型数据库的MySQL很类似。

数据类型长度范围
TINYINT1个字节-128 to 127
SMALLINT2个字节-32,768 to 32,767
INT4个字节-2,147,483,648 to 2,147,483,647
BIGINT8字节-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
FLOAT4字节浮点型
DOUBLE8字节浮点型
DECIMAL17字节38位，存储小数

2.2 字符类型

Hive的字符类型也与关系型数据库的MySQL类似。
实际使用中，String使用得最多，因为这个String类型对应java中一个String对象。

数据类型描述
String使用时通常用单引号(‘’)或者双引号（””）引用，Hive在String中使用c样式的转义
varchar变长字符串，最大长度为65535
char定长字符串， 最大长度255

2.3 日期时间类型

Hive支持的日期时间格式如下:

Timestamp精度高，Timestamp精度为9，可以满足对时间字段的要求。
如要使用日期时间计算，可以使用interval。

数据类型描述
Timestamp支持传统的UNIX时间戳，可选纳秒精度，精度为9
Date以YYYY-­MM-­DD格式存储年月日
intervalINTERVAL ‘1’ DAY 增加1天
INTERVAL ‘1-2’ YEAR TO MONTH 增加1年2个月
INTERVAL ‘1 2:3:4.000005’ DAY = INTERVAL ‘1’ DAY+ INTERVAL ‘1’ DAY+ INTERVAL ‘1’ DAY+ INTERVAL ‘4’ SECOND + INTERVAL ‘4’ SECOND +

2.4 布尔类型

布尔类型表示true或false。

数据类型描述
Booleantrue/false

2.5 复合数据类型

where和group by子句后面条件复合类型会失败。

数据类型描述举例
ARRAY有序并同类型的一组数据集合，并可用下标索引ARRAY(‘foo’,’bar’)
MAPKey-value对集合，采用key的名字索引取值MAP(‘first’,’John’,’last’,’Doe’)
STRUCT支持任意结构的组合，采用点号叏值。每一个字段都有名字，可以是任意类型STRUCT(‘John’,’Doe’)
UNIONTYPE一组异构类型集合

二.Hive文件格式

Hive的数据存储在HDFS上，并支持多种丌同文件格式
在创建表时，通过STORED AS 语句指定存储文件格式

文件格式类型存储备注
TextFile文本行存储
SequenceFile二进制行存储
RCFile二进制列存储
ORC二进制列存储ORC支持ACID事务及CBO优化器
Parquet二进制列存储
Avro二进制行存储
JSONFILE二进制列存储Hive 4.0.0 及以上版本才支持
STORED BY非hive的表，例如Hbase、Druid or Accumulo的表
INPUTFORMAT and OUTPUTFORMAT自定义的文件格式

行存储 VS 列存
行存储和列存储方式是指数据在磁盘中按照行或者列的方式迚行组织和物理存储

行存储
–适合增加、插入、删除、修改的事务处理处理
–对列的统计分析却需要耗费大量的I/O。对指定列迚行统计分析时，需要把整张表读叏到内存，然后再逐行对列迚行读叏分析操作

列存储
–对增加、插入、删除、修改的事务处理I/O高、效率低
–非常适合做统计查询类操作，统计分析一般是针对指定列迚行，只需要把指定列读叏到内存迚行操作

2.1 text格式

Hive默认格式（可通过hive.default.fileformat参数定义为默认其他格式）
可读性好，肉眼可读
磁盘开销大，数据解析开销大，不建议生产系统采用这种格式
可以配合压缩来缓解IO压力，比如lzo/gzip/bzip2，但压缩后不可Split
对于Schema的变更支持很弱，只能在最后字段新增字段，已存在的字段无法删除

Hive中Text文件，默认以\n为换行符，A作为字段间隔，B作为ARRAY或者STRUCT元素间隔，^C作为Map

测试:
建表语句

create table EMP_BAK
(
  empno    int,
  ename    string,
  job      string,
  mgr      int,
  hiredate date,
  sal      decimal(7,2),
  comm     decimal(7,2),
  deptno   int
)
row format delimited
fields terminated by '\001'
collection items terminated by '\002'
map keys terminated by '\003'
lines terminated by '\n'
stored as textfile;

测试记录:

hive> 
    > 
    > 
    > create table EMP_BAK
    > (
    >   empno    int,
    >   ename    string,
    >   job      string,
    >   mgr      int,
    >   hiredate date,
    >   sal      decimal(7,2),
    >   comm     decimal(7,2),
    >   deptno   int
    > )
    > row format delimited
    > fields terminated by '\001'
    > collection items terminated by '\002'
    > map keys terminated by '\003'
    > lines terminated by '\n'
    > stored as textfile;
create table EMP_BAK
(
  empno    int,
  ename    string,
  job      string,
  mgr      int,
  hiredate date,
  sal      decimal(7,2),
  comm     decimal(7,2),
  deptno   int
)
row format delimited
fields terminated by '\001'
collection items terminated by '\002'
map keys terminated by '\003'
lines terminated by '\n'
stored as textfile
OK
Time taken: 0.091 seconds
hive> 
    > insert into emp_bak  select * from emp;
Query ID = root_20201130165027_7ca66a61-87c8-4e62-9bae-3277c02e813d
Total jobs = 3
Launching Job 1 out of 3
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job_1606698967173_0006, Tracking URL = http://hp1:8088/proxy/application_1606698967173_0006/
Kill Command = /opt/cloudera/parcels/CDH-6.3.1-1.cdh6.3.1.p0.1470567/lib/hadoop/bin/hadoop job  -kill job_1606698967173_0006
Hadoop job information for Stage-1: number of mappers: 2; number of reducers: 0
2020-11-30 16:50:37,221 Stage-1 map = 0%,  reduce = 0%
2020-11-30 16:50:44,490 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 6.22 sec
MapReduce Total cumulative CPU time: 6 seconds 220 msec
Ended Job = job_1606698967173_0006
Stage-4 is filtered out by condition resolver.
Stage-3 is selected by condition resolver.
Stage-5 is filtered out by condition resolver.
Launching Job 3 out of 3
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job_1606698967173_0007, Tracking URL = http://hp1:8088/proxy/application_1606698967173_0007/
Kill Command = /opt/cloudera/parcels/CDH-6.3.1-1.cdh6.3.1.p0.1470567/lib/hadoop/bin/hadoop job  -kill job_1606698967173_0007
Hadoop job information for Stage-3: number of mappers: 1; number of reducers: 0
2020-11-30 16:50:56,864 Stage-3 map = 0%,  reduce = 0%
2020-11-30 16:51:03,072 Stage-3 map = 100%,  reduce = 0%, Cumulative CPU 1.57 sec
MapReduce Total cumulative CPU time: 1 seconds 570 msec
Ended Job = job_1606698967173_0007
Loading data to table test.emp_bak
MapReduce Jobs Launched: 
Stage-Stage-1: Map: 2   Cumulative CPU: 6.22 sec   HDFS Read: 11923 HDFS Write: 832 HDFS EC Read: 0 SUCCESS
Stage-Stage-3: Map: 1   Cumulative CPU: 1.57 sec   HDFS Read: 3936 HDFS Write: 695 HDFS EC Read: 0 SUCCESS
Total MapReduce CPU Time Spent: 7 seconds 790 msec
OK
Time taken: 37.272 seconds

查看hive表数据

[root@hp2 emp]# hadoop fs -cat /user/hive/warehouse/test.db/emp_bak/000000_0
7521WARDSALESMAN76981981-02-221250.00500.0030
7566JONESMANAGER78391981-04-022975.00\N20
7654MARTINSALESMAN76981981-09-281250.001400.0030
7698BLAKEMANAGER78391981-05-012850.00\N30
7782CLARKMANAGER78391981-06-092450.00\N10
7788SCOTTANALYST75661987-06-133000.00\N20
7839KINGPRESIDENT\N1981-11-175000.00\N10
7844TURNERSALESMAN76981981-09-081500.000.0030
7876ADAMSCLERK77881987-06-131100.00\N20
7900JAMESCLERK76981981-12-03950.00\N30
7902FORDANALYST75661981-12-033000.00\N20
7934MILLERCLERK77821982-01-231300.00\N10
7369SMITHCLERK79021980-12-17800.00\N20
7499ALLENSALESMAN76981981-02-201600.00300.0030

2.2 Sequence文件

SequenceFile是HadoopAPI 提供的一种二进制文件，它将数据以<key,value>的形式序列化到文件中。这种二迚制文件内部使用Hadoop的标准的Writable 接口实现序列化和反序列化
Hive 中的SequenceFile继承自HadoopAPI 的SequenceFile，它的key为空，使用value 存放实际的值，避免MR 在运行map 阶段的排序过程。
相比Text更紧凑，支持Split。
没有Metadata，只能新增字段

生产中基本不会用，k-v格式，比源文本格式占用磁盘更多

2.3 RCFile

RCFile是Hive推出的一种与门面向列的数据格式。它遵循“先按列划分，再垂直划分”的设计理念

生产中用的少，行列混合存储，ORC是他得升级版

2.4 ORC

ORC（OptimizedRCFile）存储源自亍RCFile这种存储格式
ORC在压缩编码，查询性能方面相比RCFile做了很多优化
Metadata用protobuf存储，支持schema的变劢，如新增或者删除字段

生产中最常用，列式存储

2.4.1 测试数据准备

MySQL 数据准备:

-- 建表语句
CREATE TABLE fact_sale (
  id bigint(8) NOT NULL AUTO_INCREMENT,
  sale_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  prod_name varchar(50) DEFAULT NULL,
  sale_nums int(11) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 批量加载数据的存储过程
-- 可以考虑先关闭binlog
delimiter //

CREATE PROCEDURE p_ins_fact_sal()
begin

  declare l_sal_date timestamp;
  declare l_prod_name varchar(50);
  declare l_sal_num   int;

  declare l_n int default 1;

  while l_n < power(10,9) DO
    
    set l_sal_date = adddate('2010-01-01',CEILING(RAND()*900+100));
    set l_prod_name = concat('PROD',CEILING(RAND()*9+1));
    set l_sal_num =  ceiling(rand()*9)+ceiling(rand()*90);

    insert into fact_sale(sale_date,prod_name,sale_nums) values (l_sal_date,l_prod_name,l_sal_num);
    
    if mod(l_n,1000) = 0 then
       commit;
    end if;

    set l_n = l_n + 1;
  end while; 

end;
//

delimiter ;

-- 运行存储过程
-- 我的运行到一半停了，就7亿多数据进行测试
set autocommit = 0;
call p_ins_fact_sal;

mysql> select count(*) from fact_sale;
+-----------+
| count(*)  |
+-----------+
| 767830000 |
+-----------+
1 row in set (2 min 32.75 sec)

mysql> desc fact_sale;
+-----------+-------------+------+-----+-------------------+-----------------------------+
| Field     | Type        | Null | Key | Default           | Extra                       |
+-----------+-------------+------+-----+-------------------+-----------------------------+
| id        | bigint(8)   | NO   | PRI | NULL              | auto_increment              |
| sale_date | timestamp   | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| prod_name | varchar(50) | YES  |     | NULL              |                             |
| sale_nums | int(11)     | YES  |     | NULL              |                             |
+-----------+-------------+------+-----+-------------------+-----------------------------+
4 rows in set (0.01 sec)

text 格式表:

-- 默认通过sqoop导入的表就是text格式
sqoop import \
--connect jdbc:mysql://10.31.1.122:3306/test \
--username root \
--password abc123 \
--table fact_sale \
--fields-terminated-by '\0001' \
--delete-target-dir \
--num-mappers 4 \
--hive-import \
--hive-database test \
--hive-table ods_fact_sale \
--hive-overwrite

ORC格式表

CREATE TABLE ods_fact_sale_orc(
  id bigint, 
  sale_date string, 
  prod_name string, 
  sale_nums int)
STORED AS ORC;

通过sqoop导入数据会是失败，因为文件格式的问题
此处直接通过text file的文件来insert 数据

insert into ods_fact_sale_orc select * from ods_fact_sale;

2.4.2 对比text和ORC格式的大小

可以看到同样一张表的数据，text file大小31G左右，而ORC file 大小21G左右，差不多节省了三分之一的空间

[root@hp3 ~]# hadoop fs -ls /user/hive/warehouse/test.db/ods_fact_sale  |awk -F ' ' '{print $5}'|awk '{a+=$1}END{print a}'
31421093662
[root@hp3 ~]# 
[root@hp3 ~]# 
[root@hp3 ~]# 
[root@hp3 ~]# 
[root@hp3 ~]# hadoop fs -ls /user/hive/warehouse/test.db/ods_fact_sale_orc  |awk -F ' ' '{print $5}'|awk '{a+=$1}END{print a}'  
2151733397
[root@hp3 ~]#

2.4.3 对比text和ORC的性能

以一个简单的 select count(*) 操作来初步判断下两者的性能
text file 用了差不多7分钟左右
ORC file 用了不到1分钟

hive> 
    > 
    > select count(*) from ods_fact_sale;
Query ID = root_20201202181601_ca362f14-e6f7-4471-a162-aadc248179b1
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_0012, Tracking URL = http://hp1:8088/proxy/application_1606698967173_0012/
Kill Command = /opt/cloudera/parcels/CDH-6.3.1-1.cdh6.3.1.p0.1470567/lib/hadoop/bin/hadoop job  -kill job_1606698967173_0012
Hadoop job information for Stage-1: number of mappers: 117; number of reducers: 1
2020-12-02 18:16:11,505 Stage-1 map = 0%,  reduce = 0%
2020-12-02 18:16:20,860 Stage-1 map = 1%,  reduce = 0%, Cumulative CPU 5.96 sec
2020-12-02 18:16:21,896 Stage-1 map = 2%,  reduce = 0%, Cumulative CPU 12.14 sec
2020-12-02 18:16:28,099 Stage-1 map = 3%,  reduce = 0%, Cumulative CPU 23.94 sec
2020-12-02 18:16:33,238 Stage-1 map = 4%,  reduce = 0%, Cumulative CPU 29.81 sec
2020-12-02 18:16:35,288 Stage-1 map = 5%,  reduce = 0%, Cumulative CPU 35.61 sec
2020-12-02 18:16:39,410 Stage-1 map = 6%,  reduce = 0%, Cumulative CPU 41.31 sec
2020-12-02 18:16:40,433 Stage-1 map = 7%,  reduce = 0%, Cumulative CPU 47.11 sec
2020-12-02 18:16:45,577 Stage-1 map = 8%,  reduce = 0%, Cumulative CPU 52.97 sec
2020-12-02 18:16:46,606 Stage-1 map = 9%,  reduce = 0%, Cumulative CPU 58.78 sec
2020-12-02 18:16:52,773 Stage-1 map = 10%,  reduce = 0%, Cumulative CPU 70.48 sec
2020-12-02 18:16:57,909 Stage-1 map = 11%,  reduce = 0%, Cumulative CPU 76.27 sec
2020-12-02 18:16:58,939 Stage-1 map = 12%,  reduce = 0%, Cumulative CPU 82.09 sec
2020-12-02 18:17:04,075 Stage-1 map = 13%,  reduce = 0%, Cumulative CPU 87.97 sec
2020-12-02 18:17:05,102 Stage-1 map = 14%,  reduce = 0%, Cumulative CPU 93.75 sec
2020-12-02 18:17:10,236 Stage-1 map = 15%,  reduce = 0%, Cumulative CPU 99.79 sec
2020-12-02 18:17:15,364 Stage-1 map = 16%,  reduce = 0%, Cumulative CPU 111.69 sec
2020-12-02 18:17:16,389 Stage-1 map = 17%,  reduce = 0%, Cumulative CPU 117.4 sec
2020-12-02 18:17:21,515 Stage-1 map = 18%,  reduce = 0%, Cumulative CPU 123.2 sec
2020-12-02 18:17:22,544 Stage-1 map = 19%,  reduce = 0%, Cumulative CPU 128.98 sec
2020-12-02 18:17:27,683 Stage-1 map = 20%,  reduce = 0%, Cumulative CPU 134.71 sec
2020-12-02 18:17:28,710 Stage-1 map = 21%,  reduce = 0%, Cumulative CPU 140.42 sec
2020-12-02 18:17:34,868 Stage-1 map = 22%,  reduce = 0%, Cumulative CPU 151.76 sec
2020-12-02 18:17:40,002 Stage-1 map = 23%,  reduce = 0%, Cumulative CPU 157.62 sec
2020-12-02 18:17:41,029 Stage-1 map = 24%,  reduce = 0%, Cumulative CPU 163.54 sec
2020-12-02 18:17:46,162 Stage-1 map = 25%,  reduce = 0%, Cumulative CPU 169.32 sec
2020-12-02 18:17:47,187 Stage-1 map = 26%,  reduce = 0%, Cumulative CPU 175.07 sec
2020-12-02 18:17:53,333 Stage-1 map = 27%,  reduce = 0%, Cumulative CPU 186.71 sec
2020-12-02 18:17:58,460 Stage-1 map = 28%,  reduce = 0%, Cumulative CPU 192.56 sec
2020-12-02 18:17:59,485 Stage-1 map = 29%,  reduce = 0%, Cumulative CPU 198.36 sec
2020-12-02 18:18:03,583 Stage-1 map = 30%,  reduce = 0%, Cumulative CPU 204.19 sec
2020-12-02 18:18:04,603 Stage-1 map = 31%,  reduce = 0%, Cumulative CPU 210.0 sec
2020-12-02 18:18:09,726 Stage-1 map = 32%,  reduce = 0%, Cumulative CPU 215.86 sec
2020-12-02 18:18:15,869 Stage-1 map = 33%,  reduce = 0%, Cumulative CPU 227.33 sec
2020-12-02 18:18:16,893 Stage-1 map = 34%,  reduce = 0%, Cumulative CPU 233.07 sec
2020-12-02 18:18:22,018 Stage-1 map = 35%,  reduce = 0%, Cumulative CPU 238.91 sec
2020-12-02 18:18:23,044 Stage-1 map = 36%,  reduce = 0%, Cumulative CPU 244.64 sec
2020-12-02 18:18:28,164 Stage-1 map = 37%,  reduce = 0%, Cumulative CPU 250.47 sec
2020-12-02 18:18:29,184 Stage-1 map = 38%,  reduce = 0%, Cumulative CPU 256.4 sec
2020-12-02 18:18:35,333 Stage-1 map = 39%,  reduce = 0%, Cumulative CPU 268.01 sec
2020-12-02 18:18:40,452 Stage-1 map = 40%,  reduce = 0%, Cumulative CPU 273.96 sec
2020-12-02 18:18:41,476 Stage-1 map = 41%,  reduce = 0%, Cumulative CPU 279.73 sec
2020-12-02 18:18:45,579 Stage-1 map = 42%,  reduce = 0%, Cumulative CPU 285.62 sec
2020-12-02 18:18:46,611 Stage-1 map = 43%,  reduce = 0%, Cumulative CPU 291.31 sec
2020-12-02 18:18:51,734 Stage-1 map = 44%,  reduce = 0%, Cumulative CPU 297.03 sec
2020-12-02 18:18:57,882 Stage-1 map = 45%,  reduce = 0%, Cumulative CPU 308.57 sec
2020-12-02 18:18:58,907 Stage-1 map = 46%,  reduce = 0%, Cumulative CPU 314.23 sec
2020-12-02 18:19:04,039 Stage-1 map = 47%,  reduce = 0%, Cumulative CPU 320.04 sec
2020-12-02 18:19:05,064 Stage-1 map = 48%,  reduce = 0%, Cumulative CPU 325.75 sec
2020-12-02 18:19:10,170 Stage-1 map = 49%,  reduce = 0%, Cumulative CPU 331.67 sec
2020-12-02 18:19:11,194 Stage-1 map = 50%,  reduce = 0%, Cumulative CPU 337.37 sec
2020-12-02 18:19:17,337 Stage-1 map = 51%,  reduce = 0%, Cumulative CPU 348.68 sec
2020-12-02 18:19:21,432 Stage-1 map = 52%,  reduce = 0%, Cumulative CPU 354.76 sec
2020-12-02 18:19:24,500 Stage-1 map = 53%,  reduce = 0%, Cumulative CPU 360.58 sec
2020-12-02 18:19:28,604 Stage-1 map = 54%,  reduce = 0%, Cumulative CPU 366.66 sec
2020-12-02 18:19:30,650 Stage-1 map = 55%,  reduce = 0%, Cumulative CPU 372.53 sec
2020-12-02 18:19:34,748 Stage-1 map = 56%,  reduce = 0%, Cumulative CPU 378.49 sec
2020-12-02 18:19:40,892 Stage-1 map = 57%,  reduce = 0%, Cumulative CPU 390.49 sec
2020-12-02 18:19:41,916 Stage-1 map = 58%,  reduce = 0%, Cumulative CPU 396.21 sec
2020-12-02 18:19:46,009 Stage-1 map = 59%,  reduce = 0%, Cumulative CPU 402.24 sec
2020-12-02 18:19:48,056 Stage-1 map = 60%,  reduce = 0%, Cumulative CPU 407.88 sec
2020-12-02 18:19:52,151 Stage-1 map = 61%,  reduce = 0%, Cumulative CPU 414.02 sec
2020-12-02 18:19:54,199 Stage-1 map = 62%,  reduce = 0%, Cumulative CPU 419.73 sec
2020-12-02 18:20:00,339 Stage-1 map = 63%,  reduce = 0%, Cumulative CPU 431.41 sec
2020-12-02 18:20:04,435 Stage-1 map = 64%,  reduce = 0%, Cumulative CPU 437.29 sec
2020-12-02 18:20:06,483 Stage-1 map = 65%,  reduce = 0%, Cumulative CPU 443.07 sec
2020-12-02 18:20:10,581 Stage-1 map = 66%,  reduce = 0%, Cumulative CPU 448.82 sec
2020-12-02 18:20:12,625 Stage-1 map = 67%,  reduce = 0%, Cumulative CPU 454.54 sec
2020-12-02 18:20:16,718 Stage-1 map = 68%,  reduce = 0%, Cumulative CPU 460.35 sec
2020-12-02 18:20:21,818 Stage-1 map = 69%,  reduce = 0%, Cumulative CPU 471.74 sec
2020-12-02 18:20:24,890 Stage-1 map = 70%,  reduce = 0%, Cumulative CPU 477.65 sec
2020-12-02 18:20:27,961 Stage-1 map = 71%,  reduce = 0%, Cumulative CPU 483.43 sec
2020-12-02 18:20:30,009 Stage-1 map = 72%,  reduce = 0%, Cumulative CPU 489.21 sec
2020-12-02 18:20:34,102 Stage-1 map = 73%,  reduce = 0%, Cumulative CPU 495.01 sec
2020-12-02 18:20:36,150 Stage-1 map = 74%,  reduce = 0%, Cumulative CPU 500.81 sec
2020-12-02 18:20:42,291 Stage-1 map = 75%,  reduce = 0%, Cumulative CPU 512.42 sec
2020-12-02 18:20:46,384 Stage-1 map = 76%,  reduce = 0%, Cumulative CPU 518.22 sec
2020-12-02 18:20:48,431 Stage-1 map = 77%,  reduce = 0%, Cumulative CPU 523.97 sec
2020-12-02 18:20:52,524 Stage-1 map = 78%,  reduce = 0%, Cumulative CPU 529.75 sec
2020-12-02 18:20:54,573 Stage-1 map = 79%,  reduce = 0%, Cumulative CPU 535.36 sec
2020-12-02 18:21:00,721 Stage-1 map = 80%,  reduce = 0%, Cumulative CPU 546.99 sec
2020-12-02 18:21:04,818 Stage-1 map = 81%,  reduce = 0%, Cumulative CPU 552.86 sec
2020-12-02 18:21:06,865 Stage-1 map = 82%,  reduce = 0%, Cumulative CPU 558.66 sec
2020-12-02 18:21:11,983 Stage-1 map = 83%,  reduce = 0%, Cumulative CPU 564.22 sec
2020-12-02 18:21:18,121 Stage-1 map = 84%,  reduce = 0%, Cumulative CPU 569.84 sec
2020-12-02 18:21:19,157 Stage-1 map = 84%,  reduce = 28%, Cumulative CPU 570.61 sec
2020-12-02 18:21:24,280 Stage-1 map = 85%,  reduce = 28%, Cumulative CPU 576.32 sec
2020-12-02 18:21:36,563 Stage-1 map = 86%,  reduce = 28%, Cumulative CPU 587.65 sec
2020-12-02 18:21:42,704 Stage-1 map = 87%,  reduce = 29%, Cumulative CPU 593.35 sec
2020-12-02 18:21:48,878 Stage-1 map = 88%,  reduce = 29%, Cumulative CPU 598.98 sec
2020-12-02 18:21:53,993 Stage-1 map = 89%,  reduce = 29%, Cumulative CPU 604.62 sec
2020-12-02 18:22:00,133 Stage-1 map = 90%,  reduce = 29%, Cumulative CPU 610.21 sec
2020-12-02 18:22:01,158 Stage-1 map = 90%,  reduce = 30%, Cumulative CPU 610.28 sec
2020-12-02 18:22:06,267 Stage-1 map = 91%,  reduce = 30%, Cumulative CPU 615.95 sec
2020-12-02 18:22:18,557 Stage-1 map = 92%,  reduce = 30%, Cumulative CPU 627.47 sec
2020-12-02 18:22:19,582 Stage-1 map = 92%,  reduce = 31%, Cumulative CPU 627.6 sec
2020-12-02 18:22:24,704 Stage-1 map = 93%,  reduce = 31%, Cumulative CPU 633.08 sec
2020-12-02 18:22:30,849 Stage-1 map = 94%,  reduce = 31%, Cumulative CPU 638.86 sec
2020-12-02 18:22:36,996 Stage-1 map = 95%,  reduce = 32%, Cumulative CPU 644.5 sec
2020-12-02 18:22:42,111 Stage-1 map = 96%,  reduce = 32%, Cumulative CPU 650.12 sec
2020-12-02 18:22:48,258 Stage-1 map = 97%,  reduce = 32%, Cumulative CPU 655.77 sec
2020-12-02 18:23:00,546 Stage-1 map = 98%,  reduce = 32%, Cumulative CPU 667.18 sec
2020-12-02 18:23:01,572 Stage-1 map = 98%,  reduce = 33%, Cumulative CPU 667.25 sec
2020-12-02 18:23:06,689 Stage-1 map = 99%,  reduce = 33%, Cumulative CPU 672.94 sec
2020-12-02 18:23:12,829 Stage-1 map = 100%,  reduce = 33%, Cumulative CPU 678.58 sec
2020-12-02 18:23:13,854 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 680.43 sec
MapReduce Total cumulative CPU time: 11 minutes 20 seconds 430 msec
Ended Job = job_1606698967173_0012
MapReduce Jobs Launched: 
Stage-Stage-1: Map: 117  Reduce: 1   Cumulative CPU: 680.43 sec   HDFS Read: 31436878698 HDFS Write: 109 HDFS EC Read: 0 SUCCESS
Total MapReduce CPU Time Spent: 11 minutes 20 seconds 430 msec
OK
767830000
Time taken: 433.478 seconds, Fetched: 1 row(s)
hive> 
    > select count(*) from ods_fact_sale_orc;
Query ID = root_20201202182429_b91ed86c-fb4c-4ad7-9910-8ed79baafd2d
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_0013, Tracking URL = http://hp1:8088/proxy/application_1606698967173_0013/
Kill Command = /opt/cloudera/parcels/CDH-6.3.1-1.cdh6.3.1.p0.1470567/lib/hadoop/bin/hadoop job  -kill job_1606698967173_0013
Hadoop job information for Stage-1: number of mappers: 9; number of reducers: 1
2020-12-02 18:24:37,761 Stage-1 map = 0%,  reduce = 0%
2020-12-02 18:24:44,983 Stage-1 map = 11%,  reduce = 0%, Cumulative CPU 5.26 sec
2020-12-02 18:24:46,016 Stage-1 map = 22%,  reduce = 0%, Cumulative CPU 10.79 sec
2020-12-02 18:24:51,160 Stage-1 map = 44%,  reduce = 0%, Cumulative CPU 21.18 sec
2020-12-02 18:24:56,303 Stage-1 map = 56%,  reduce = 0%, Cumulative CPU 25.09 sec
2020-12-02 18:24:57,330 Stage-1 map = 67%,  reduce = 0%, Cumulative CPU 30.35 sec
2020-12-02 18:25:01,455 Stage-1 map = 78%,  reduce = 0%, Cumulative CPU 35.53 sec
2020-12-02 18:25:02,478 Stage-1 map = 89%,  reduce = 0%, Cumulative CPU 40.69 sec
2020-12-02 18:25:07,613 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 45.64 sec
2020-12-02 18:25:09,667 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 48.16 sec
MapReduce Total cumulative CPU time: 48 seconds 160 msec
Ended Job = job_1606698967173_0013
MapReduce Jobs Launched: 
Stage-Stage-1: Map: 9  Reduce: 1   Cumulative CPU: 48.16 sec   HDFS Read: 1992431 HDFS Write: 109 HDFS EC Read: 0 SUCCESS
Total MapReduce CPU Time Spent: 48 seconds 160 msec
OK
767830000
Time taken: 41.173 seconds, Fetched: 1 row(s)
hive>

再来测试一个简单的group by操作
text 文件格式大概28分钟
ORC 文件格式大概14分钟

hive> 
    > 
    > 
    > create table dm_sale_text as
    > select t.sale_date ,
    >        t.prod_name ,
    >        sum(t.sale_nums) total_nums
    > from ods_fact_sale t
    > group by t.sale_date,t.prod_name ;
Query ID = root_20201202182650_643f7f45-555a-4b26-8cb0-1e0b84a2bea2
Total jobs = 1
Launching Job 1 out of 1
Number of reduce tasks not specified. Estimated from input data size: 469
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_0014, Tracking URL = http://hp1:8088/proxy/application_1606698967173_0014/
Kill Command = /opt/cloudera/parcels/CDH-6.3.1-1.cdh6.3.1.p0.1470567/lib/hadoop/bin/hadoop job  -kill job_1606698967173_0014
Hadoop job information for Stage-1: number of mappers: 117; number of reducers: 469
2020-12-02 18:26:57,369 Stage-1 map = 0%,  reduce = 0%
2020-12-02 18:27:11,823 Stage-1 map = 2%,  reduce = 0%, Cumulative CPU 22.84 sec
2020-12-02 18:27:22,132 Stage-1 map = 3%,  reduce = 0%, Cumulative CPU 33.49 sec
2020-12-02 18:27:32,419 Stage-1 map = 4%,  reduce = 0%, Cumulative CPU 55.0 sec
2020-12-02 18:27:34,472 Stage-1 map = 5%,  reduce = 0%, Cumulative CPU 65.8 sec
2020-12-02 18:27:42,698 Stage-1 map = 6%,  reduce = 0%, Cumulative CPU 76.52 sec
2020-12-02 18:27:43,729 Stage-1 map = 7%,  reduce = 0%, Cumulative CPU 87.32 sec
2020-12-02 18:27:52,994 Stage-1 map = 8%,  reduce = 0%, Cumulative CPU 98.23 sec
2020-12-02 18:27:55,058 Stage-1 map = 9%,  reduce = 0%, Cumulative CPU 108.95 sec
2020-12-02 18:28:04,312 Stage-1 map = 10%,  reduce = 0%, Cumulative CPU 130.29 sec
2020-12-02 18:28:14,582 Stage-1 map = 11%,  reduce = 0%, Cumulative CPU 141.19 sec
2020-12-02 18:28:15,610 Stage-1 map = 12%,  reduce = 0%, Cumulative CPU 152.01 sec
2020-12-02 18:28:24,852 Stage-1 map = 14%,  reduce = 0%, Cumulative CPU 173.5 sec
2020-12-02 18:28:35,100 Stage-1 map = 15%,  reduce = 0%, Cumulative CPU 195.12 sec
2020-12-02 18:28:45,357 Stage-1 map = 17%,  reduce = 0%, Cumulative CPU 216.32 sec
2020-12-02 18:28:54,609 Stage-1 map = 18%,  reduce = 0%, Cumulative CPU 227.0 sec
2020-12-02 18:28:55,638 Stage-1 map = 19%,  reduce = 0%, Cumulative CPU 237.79 sec
2020-12-02 18:29:04,874 Stage-1 map = 20%,  reduce = 0%, Cumulative CPU 248.66 sec
2020-12-02 18:29:05,898 Stage-1 map = 21%,  reduce = 0%, Cumulative CPU 259.26 sec
2020-12-02 18:29:16,142 Stage-1 map = 22%,  reduce = 0%, Cumulative CPU 280.76 sec
2020-12-02 18:29:25,357 Stage-1 map = 23%,  reduce = 0%, Cumulative CPU 291.55 sec
2020-12-02 18:29:26,382 Stage-1 map = 24%,  reduce = 0%, Cumulative CPU 302.35 sec
2020-12-02 18:29:35,610 Stage-1 map = 25%,  reduce = 0%, Cumulative CPU 313.22 sec
2020-12-02 18:29:36,637 Stage-1 map = 26%,  reduce = 0%, Cumulative CPU 323.79 sec
2020-12-02 18:29:45,854 Stage-1 map = 27%,  reduce = 0%, Cumulative CPU 345.37 sec
2020-12-02 18:29:55,086 Stage-1 map = 28%,  reduce = 0%, Cumulative CPU 356.28 sec
2020-12-02 18:29:56,111 Stage-1 map = 29%,  reduce = 0%, Cumulative CPU 366.71 sec
2020-12-02 18:30:05,330 Stage-1 map = 30%,  reduce = 0%, Cumulative CPU 377.54 sec
2020-12-02 18:30:06,356 Stage-1 map = 31%,  reduce = 0%, Cumulative CPU 388.15 sec
2020-12-02 18:30:15,578 Stage-1 map = 32%,  reduce = 0%, Cumulative CPU 398.98 sec
2020-12-02 18:30:25,828 Stage-1 map = 33%,  reduce = 0%, Cumulative CPU 420.29 sec
2020-12-02 18:30:26,855 Stage-1 map = 34%,  reduce = 0%, Cumulative CPU 430.98 sec
2020-12-02 18:30:35,053 Stage-1 map = 35%,  reduce = 0%, Cumulative CPU 441.77 sec
2020-12-02 18:30:36,075 Stage-1 map = 36%,  reduce = 0%, Cumulative CPU 452.28 sec
2020-12-02 18:30:45,291 Stage-1 map = 37%,  reduce = 0%, Cumulative CPU 463.1 sec
2020-12-02 18:30:46,322 Stage-1 map = 38%,  reduce = 0%, Cumulative CPU 473.65 sec
2020-12-02 18:30:56,539 Stage-1 map = 39%,  reduce = 0%, Cumulative CPU 494.81 sec
2020-12-02 18:31:05,749 Stage-1 map = 40%,  reduce = 0%, Cumulative CPU 505.54 sec
2020-12-02 18:31:06,774 Stage-1 map = 41%,  reduce = 0%, Cumulative CPU 516.16 sec
2020-12-02 18:31:15,993 Stage-1 map = 42%,  reduce = 0%, Cumulative CPU 526.68 sec
2020-12-02 18:31:17,016 Stage-1 map = 43%,  reduce = 0%, Cumulative CPU 537.22 sec
2020-12-02 18:31:25,214 Stage-1 map = 44%,  reduce = 0%, Cumulative CPU 547.95 sec
2020-12-02 18:31:35,462 Stage-1 map = 45%,  reduce = 0%, Cumulative CPU 569.48 sec
2020-12-02 18:31:36,486 Stage-1 map = 46%,  reduce = 0%, Cumulative CPU 579.88 sec
2020-12-02 18:31:45,704 Stage-1 map = 47%,  reduce = 0%, Cumulative CPU 590.57 sec
2020-12-02 18:31:46,724 Stage-1 map = 48%,  reduce = 0%, Cumulative CPU 600.98 sec
2020-12-02 18:31:55,939 Stage-1 map = 49%,  reduce = 0%, Cumulative CPU 611.68 sec
2020-12-02 18:31:56,967 Stage-1 map = 50%,  reduce = 0%, Cumulative CPU 621.97 sec
2020-12-02 18:32:08,242 Stage-1 map = 51%,  reduce = 0%, Cumulative CPU 642.7 sec
2020-12-02 18:32:15,410 Stage-1 map = 52%,  reduce = 0%, Cumulative CPU 654.54 sec
2020-12-02 18:32:19,510 Stage-1 map = 53%,  reduce = 0%, Cumulative CPU 665.58 sec
2020-12-02 18:32:25,670 Stage-1 map = 54%,  reduce = 0%, Cumulative CPU 676.44 sec
2020-12-02 18:32:29,767 Stage-1 map = 55%,  reduce = 0%, Cumulative CPU 687.43 sec
2020-12-02 18:32:36,956 Stage-1 map = 56%,  reduce = 0%, Cumulative CPU 698.43 sec
2020-12-02 18:32:48,242 Stage-1 map = 57%,  reduce = 0%, Cumulative CPU 720.13 sec
2020-12-02 18:32:51,318 Stage-1 map = 58%,  reduce = 0%, Cumulative CPU 730.83 sec
2020-12-02 18:32:59,546 Stage-1 map = 59%,  reduce = 0%, Cumulative CPU 742.01 sec
2020-12-02 18:33:00,572 Stage-1 map = 60%,  reduce = 0%, Cumulative CPU 752.5 sec
2020-12-02 18:33:09,800 Stage-1 map = 61%,  reduce = 0%, Cumulative CPU 763.61 sec
2020-12-02 18:33:10,824 Stage-1 map = 62%,  reduce = 0%, Cumulative CPU 774.48 sec
2020-12-02 18:33:22,092 Stage-1 map = 63%,  reduce = 0%, Cumulative CPU 795.95 sec
2020-12-02 18:33:32,331 Stage-1 map = 65%,  reduce = 0%, Cumulative CPU 817.44 sec
2020-12-02 18:33:41,552 Stage-1 map = 67%,  reduce = 0%, Cumulative CPU 838.93 sec
2020-12-02 18:33:51,793 Stage-1 map = 68%,  reduce = 0%, Cumulative CPU 860.92 sec
2020-12-02 18:34:03,054 Stage-1 map = 70%,  reduce = 0%, Cumulative CPU 882.33 sec
2020-12-02 18:34:13,297 Stage-1 map = 72%,  reduce = 0%, Cumulative CPU 904.19 sec
2020-12-02 18:34:24,564 Stage-1 map = 74%,  reduce = 0%, Cumulative CPU 925.88 sec
2020-12-02 18:34:33,787 Stage-1 map = 75%,  reduce = 0%, Cumulative CPU 947.33 sec
2020-12-02 18:34:44,028 Stage-1 map = 77%,  reduce = 0%, Cumulative CPU 969.09 sec
2020-12-02 18:34:54,277 Stage-1 map = 79%,  reduce = 0%, Cumulative CPU 990.74 sec
2020-12-02 18:35:04,524 Stage-1 map = 80%,  reduce = 0%, Cumulative CPU 1012.45 sec
2020-12-02 18:35:14,762 Stage-1 map = 82%,  reduce = 0%, Cumulative CPU 1033.93 sec
2020-12-02 18:35:25,011 Stage-1 map = 83%,  reduce = 0%, Cumulative CPU 1044.53 sec
2020-12-02 18:35:35,267 Stage-1 map = 84%,  reduce = 0%, Cumulative CPU 1055.7 sec
2020-12-02 18:35:45,523 Stage-1 map = 85%,  reduce = 0%, Cumulative CPU 1066.55 sec
2020-12-02 18:36:04,987 Stage-1 map = 86%,  reduce = 0%, Cumulative CPU 1087.88 sec
2020-12-02 18:36:15,242 Stage-1 map = 87%,  reduce = 0%, Cumulative CPU 1098.64 sec
2020-12-02 18:36:25,481 Stage-1 map = 88%,  reduce = 0%, Cumulative CPU 1109.09 sec
2020-12-02 18:36:34,708 Stage-1 map = 89%,  reduce = 0%, Cumulative CPU 1119.59 sec
2020-12-02 18:36:44,950 Stage-1 map = 90%,  reduce = 0%, Cumulative CPU 1129.91 sec
2020-12-02 18:36:55,196 Stage-1 map = 91%,  reduce = 0%, Cumulative CPU 1140.17 sec
2020-12-02 18:37:14,647 Stage-1 map = 92%,  reduce = 0%, Cumulative CPU 1160.61 sec
2020-12-02 18:37:24,895 Stage-1 map = 93%,  reduce = 0%, Cumulative CPU 1170.75 sec
2020-12-02 18:37:35,142 Stage-1 map = 94%,  reduce = 0%, Cumulative CPU 1180.9 sec
2020-12-02 18:37:45,390 Stage-1 map = 95%,  reduce = 0%, Cumulative CPU 1191.39 sec
2020-12-02 18:37:55,648 Stage-1 map = 96%,  reduce = 0%, Cumulative CPU 1202.18 sec
2020-12-02 18:38:04,867 Stage-1 map = 97%,  reduce = 0%, Cumulative CPU 1212.47 sec
2020-12-02 18:38:25,347 Stage-1 map = 98%,  reduce = 0%, Cumulative CPU 1233.08 sec
2020-12-02 18:38:35,601 Stage-1 map = 99%,  reduce = 0%, Cumulative CPU 1243.4 sec
2020-12-02 18:38:45,854 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 1253.91 sec
2020-12-02 18:38:50,981 Stage-1 map = 100%,  reduce = 1%, Cumulative CPU 1261.64 sec
2020-12-02 18:39:01,223 Stage-1 map = 100%,  reduce = 2%, Cumulative CPU 1275.42 sec
2020-12-02 18:39:09,409 Stage-1 map = 100%,  reduce = 3%, Cumulative CPU 1286.4 sec
2020-12-02 18:39:19,640 Stage-1 map = 100%,  reduce = 4%, Cumulative CPU 1299.76 sec
2020-12-02 18:39:29,881 Stage-1 map = 100%,  reduce = 5%, Cumulative CPU 1313.38 sec
2020-12-02 18:39:37,052 Stage-1 map = 100%,  reduce = 6%, Cumulative CPU 1324.08 sec
2020-12-02 18:39:47,289 Stage-1 map = 100%,  reduce = 7%, Cumulative CPU 1337.66 sec
2020-12-02 18:39:57,537 Stage-1 map = 100%,  reduce = 8%, Cumulative CPU 1351.17 sec
2020-12-02 18:40:05,731 Stage-1 map = 100%,  reduce = 9%, Cumulative CPU 1362.13 sec
2020-12-02 18:40:16,996 Stage-1 map = 100%,  reduce = 10%, Cumulative CPU 1375.71 sec
2020-12-02 18:40:25,186 Stage-1 map = 100%,  reduce = 11%, Cumulative CPU 1389.43 sec
2020-12-02 18:40:33,389 Stage-1 map = 100%,  reduce = 12%, Cumulative CPU 1400.21 sec
2020-12-02 18:40:44,669 Stage-1 map = 100%,  reduce = 13%, Cumulative CPU 1413.75 sec
2020-12-02 18:40:53,893 Stage-1 map = 100%,  reduce = 14%, Cumulative CPU 1427.49 sec
2020-12-02 18:41:05,159 Stage-1 map = 100%,  reduce = 15%, Cumulative CPU 1440.97 sec
2020-12-02 18:41:13,365 Stage-1 map = 100%,  reduce = 16%, Cumulative CPU 1454.58 sec
2020-12-02 18:41:21,598 Stage-1 map = 100%,  reduce = 17%, Cumulative CPU 1465.26 sec
2020-12-02 18:41:32,869 Stage-1 map = 100%,  reduce = 18%, Cumulative CPU 1478.9 sec
2020-12-02 18:41:41,066 Stage-1 map = 100%,  reduce = 19%, Cumulative CPU 1489.69 sec
2020-12-02 18:41:50,286 Stage-1 map = 100%,  reduce = 20%, Cumulative CPU 1503.53 sec
2020-12-02 18:42:00,540 Stage-1 map = 100%,  reduce = 21%, Cumulative CPU 1517.18 sec
2020-12-02 18:42:08,745 Stage-1 map = 100%,  reduce = 22%, Cumulative CPU 1528.23 sec
2020-12-02 18:42:17,978 Stage-1 map = 100%,  reduce = 23%, Cumulative CPU 1541.95 sec
2020-12-02 18:42:29,242 Stage-1 map = 100%,  reduce = 24%, Cumulative CPU 1555.66 sec
2020-12-02 18:42:37,437 Stage-1 map = 100%,  reduce = 25%, Cumulative CPU 1566.86 sec
2020-12-02 18:42:45,633 Stage-1 map = 100%,  reduce = 26%, Cumulative CPU 1580.6 sec
2020-12-02 18:42:56,911 Stage-1 map = 100%,  reduce = 27%, Cumulative CPU 1594.28 sec
2020-12-02 18:43:05,113 Stage-1 map = 100%,  reduce = 28%, Cumulative CPU 1605.14 sec
2020-12-02 18:43:14,339 Stage-1 map = 100%,  reduce = 29%, Cumulative CPU 1618.9 sec
2020-12-02 18:43:25,608 Stage-1 map = 100%,  reduce = 30%, Cumulative CPU 1632.51 sec
2020-12-02 18:43:33,815 Stage-1 map = 100%,  reduce = 31%, Cumulative CPU 1646.08 sec
2020-12-02 18:43:42,007 Stage-1 map = 100%,  reduce = 32%, Cumulative CPU 1657.0 sec
2020-12-02 18:43:53,278 Stage-1 map = 100%,  reduce = 33%, Cumulative CPU 1670.63 sec
2020-12-02 18:44:02,501 Stage-1 map = 100%,  reduce = 34%, Cumulative CPU 1684.44 sec
2020-12-02 18:44:10,700 Stage-1 map = 100%,  reduce = 35%, Cumulative CPU 1695.3 sec
2020-12-02 18:44:20,954 Stage-1 map = 100%,  reduce = 36%, Cumulative CPU 1709.16 sec
2020-12-02 18:44:30,182 Stage-1 map = 100%,  reduce = 37%, Cumulative CPU 1722.75 sec
2020-12-02 18:44:38,371 Stage-1 map = 100%,  reduce = 38%, Cumulative CPU 1733.62 sec
2020-12-02 18:44:49,649 Stage-1 map = 100%,  reduce = 39%, Cumulative CPU 1747.18 sec
2020-12-02 18:44:58,872 Stage-1 map = 100%,  reduce = 40%, Cumulative CPU 1760.84 sec
2020-12-02 18:45:06,057 Stage-1 map = 100%,  reduce = 41%, Cumulative CPU 1771.79 sec
2020-12-02 18:45:17,336 Stage-1 map = 100%,  reduce = 42%, Cumulative CPU 1785.47 sec
2020-12-02 18:45:26,571 Stage-1 map = 100%,  reduce = 43%, Cumulative CPU 1799.15 sec
2020-12-02 18:45:37,850 Stage-1 map = 100%,  reduce = 44%, Cumulative CPU 1812.68 sec
2020-12-02 18:45:45,035 Stage-1 map = 100%,  reduce = 45%, Cumulative CPU 1823.67 sec
2020-12-02 18:45:55,305 Stage-1 map = 100%,  reduce = 46%, Cumulative CPU 1837.35 sec
2020-12-02 18:46:05,560 Stage-1 map = 100%,  reduce = 47%, Cumulative CPU 1851.15 sec
2020-12-02 18:46:13,768 Stage-1 map = 100%,  reduce = 48%, Cumulative CPU 1862.38 sec
2020-12-02 18:46:24,020 Stage-1 map = 100%,  reduce = 49%, Cumulative CPU 1876.14 sec
2020-12-02 18:46:33,245 Stage-1 map = 100%,  reduce = 50%, Cumulative CPU 1889.96 sec
2020-12-02 18:46:41,453 Stage-1 map = 100%,  reduce = 51%, Cumulative CPU 1901.05 sec
2020-12-02 18:46:51,707 Stage-1 map = 100%,  reduce = 52%, Cumulative CPU 1914.44 sec
2020-12-02 18:47:01,963 Stage-1 map = 100%,  reduce = 53%, Cumulative CPU 1928.08 sec
2020-12-02 18:47:10,162 Stage-1 map = 100%,  reduce = 54%, Cumulative CPU 1939.28 sec
2020-12-02 18:47:19,392 Stage-1 map = 100%,  reduce = 55%, Cumulative CPU 1952.92 sec
2020-12-02 18:47:29,635 Stage-1 map = 100%,  reduce = 56%, Cumulative CPU 1966.72 sec
2020-12-02 18:47:37,835 Stage-1 map = 100%,  reduce = 57%, Cumulative CPU 1977.36 sec
2020-12-02 18:47:49,113 Stage-1 map = 100%,  reduce = 58%, Cumulative CPU 1991.28 sec
2020-12-02 18:47:57,296 Stage-1 map = 100%,  reduce = 59%, Cumulative CPU 2004.9 sec
2020-12-02 18:48:08,577 Stage-1 map = 100%,  reduce = 60%, Cumulative CPU 2018.65 sec
2020-12-02 18:48:16,772 Stage-1 map = 100%,  reduce = 61%, Cumulative CPU 2029.56 sec
2020-12-02 18:48:25,996 Stage-1 map = 100%,  reduce = 62%, Cumulative CPU 2043.3 sec
2020-12-02 18:48:37,287 Stage-1 map = 100%,  reduce = 63%, Cumulative CPU 2057.4 sec
2020-12-02 18:48:44,460 Stage-1 map = 100%,  reduce = 64%, Cumulative CPU 2068.28 sec
2020-12-02 18:48:53,680 Stage-1 map = 100%,  reduce = 65%, Cumulative CPU 2081.89 sec
2020-12-02 18:49:04,951 Stage-1 map = 100%,  reduce = 66%, Cumulative CPU 2095.48 sec
2020-12-02 18:49:13,144 Stage-1 map = 100%,  reduce = 67%, Cumulative CPU 2106.6 sec
2020-12-02 18:49:22,369 Stage-1 map = 100%,  reduce = 68%, Cumulative CPU 2120.11 sec
2020-12-02 18:49:32,633 Stage-1 map = 100%,  reduce = 69%, Cumulative CPU 2133.92 sec
2020-12-02 18:49:40,853 Stage-1 map = 100%,  reduce = 70%, Cumulative CPU 2144.89 sec
2020-12-02 18:49:50,056 Stage-1 map = 100%,  reduce = 71%, Cumulative CPU 2158.28 sec
2020-12-02 18:50:01,337 Stage-1 map = 100%,  reduce = 72%, Cumulative CPU 2172.13 sec
2020-12-02 18:50:10,574 Stage-1 map = 100%,  reduce = 73%, Cumulative CPU 2185.33 sec
2020-12-02 18:50:17,751 Stage-1 map = 100%,  reduce = 74%, Cumulative CPU 2196.36 sec
2020-12-02 18:50:29,031 Stage-1 map = 100%,  reduce = 75%, Cumulative CPU 2209.68 sec
2020-12-02 18:50:38,253 Stage-1 map = 100%,  reduce = 76%, Cumulative CPU 2223.44 sec
2020-12-02 18:50:46,458 Stage-1 map = 100%,  reduce = 77%, Cumulative CPU 2234.49 sec
2020-12-02 18:50:57,744 Stage-1 map = 100%,  reduce = 78%, Cumulative CPU 2248.28 sec
2020-12-02 18:51:05,950 Stage-1 map = 100%,  reduce = 79%, Cumulative CPU 2261.95 sec
2020-12-02 18:51:14,171 Stage-1 map = 100%,  reduce = 80%, Cumulative CPU 2273.23 sec
2020-12-02 18:51:25,456 Stage-1 map = 100%,  reduce = 81%, Cumulative CPU 2286.97 sec
2020-12-02 18:51:34,697 Stage-1 map = 100%,  reduce = 82%, Cumulative CPU 2300.68 sec
2020-12-02 18:51:41,871 Stage-1 map = 100%,  reduce = 83%, Cumulative CPU 2311.55 sec
2020-12-02 18:51:53,173 Stage-1 map = 100%,  reduce = 84%, Cumulative CPU 2325.35 sec
2020-12-02 18:52:02,401 Stage-1 map = 100%,  reduce = 85%, Cumulative CPU 2338.84 sec
2020-12-02 18:52:10,606 Stage-1 map = 100%,  reduce = 86%, Cumulative CPU 2349.74 sec
2020-12-02 18:52:21,892 Stage-1 map = 100%,  reduce = 87%, Cumulative CPU 2363.43 sec
2020-12-02 18:52:30,096 Stage-1 map = 100%,  reduce = 88%, Cumulative CPU 2376.94 sec
2020-12-02 18:52:41,358 Stage-1 map = 100%,  reduce = 89%, Cumulative CPU 2390.6 sec
2020-12-02 18:52:49,576 Stage-1 map = 100%,  reduce = 90%, Cumulative CPU 2401.49 sec
2020-12-02 18:52:58,808 Stage-1 map = 100%,  reduce = 91%, Cumulative CPU 2414.92 sec
2020-12-02 18:53:09,060 Stage-1 map = 100%,  reduce = 92%, Cumulative CPU 2428.0 sec
2020-12-02 18:53:17,275 Stage-1 map = 100%,  reduce = 93%, Cumulative CPU 2438.9 sec
2020-12-02 18:53:26,509 Stage-1 map = 100%,  reduce = 94%, Cumulative CPU 2452.64 sec
2020-12-02 18:53:37,794 Stage-1 map = 100%,  reduce = 95%, Cumulative CPU 2466.22 sec
2020-12-02 18:53:46,000 Stage-1 map = 100%,  reduce = 96%, Cumulative CPU 2476.98 sec
2020-12-02 18:53:54,221 Stage-1 map = 100%,  reduce = 97%, Cumulative CPU 2490.69 sec
2020-12-02 18:54:05,507 Stage-1 map = 100%,  reduce = 98%, Cumulative CPU 2504.55 sec
2020-12-02 18:54:13,744 Stage-1 map = 100%,  reduce = 99%, Cumulative CPU 2515.52 sec
2020-12-02 18:54:27,093 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 2534.91 sec
MapReduce Total cumulative CPU time: 42 minutes 14 seconds 910 msec
Ended Job = job_1606698967173_0014
Moving data to directory hdfs://nameservice1/user/hive/warehouse/test.db/dm_sale_text
MapReduce Jobs Launched: 
Stage-Stage-1: Map: 117  Reduce: 469   Cumulative CPU: 2534.91 sec   HDFS Read: 31438877522 HDFS Write: 327206 HDFS EC Read: 0 SUCCESS
Total MapReduce CPU Time Spent: 42 minutes 14 seconds 910 msec
OK
Time taken: 1659.51 seconds
hive> 
    > show create table dm_sale_text;
OK
CREATE TABLE `dm_sale_text`(
  `sale_date` string, 
  `prod_name` string, 
  `total_nums` bigint)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  'hdfs://nameservice1/user/hive/warehouse/test.db/dm_sale_text'
TBLPROPERTIES (
  'transient_lastDdlTime'='1606906469')
Time taken: 0.053 seconds, Fetched: 14 row(s)
hive> 
    > CREATE TABLE dm_sale_orc(
    >   sale_date string, 
    >   prod_name string, 
    >   total_nums bigint)
    > STORED AS ORC;
OK
Time taken: 0.082 seconds
hive> 
    > 
    > insert into dm_sale_orc
    >  select t.sale_date ,
    >         t.prod_name ,
    >         sum(t.sale_nums) total_nums
    >  from ods_fact_sale_orc t
    >  group by t.sale_date,t.prod_name ;
Query ID = root_20201202193354_a9fa419b-47b5-4128-8109-746c0b1f758a
Total jobs = 1
Launching Job 1 out of 1
Number of reduce tasks not specified. Estimated from input data size: 33
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_0017, Tracking URL = http://hp1:8088/proxy/application_1606698967173_0017/
Kill Command = /opt/cloudera/parcels/CDH-6.3.1-1.cdh6.3.1.p0.1470567/lib/hadoop/bin/hadoop job  -kill job_1606698967173_0017
Hadoop job information for Stage-1: number of mappers: 9; number of reducers: 33
2020-12-02 19:34:03,377 Stage-1 map = 0%,  reduce = 0%
2020-12-02 19:34:27,164 Stage-1 map = 1%,  reduce = 0%, Cumulative CPU 42.45 sec
2020-12-02 19:34:38,475 Stage-1 map = 2%,  reduce = 0%, Cumulative CPU 66.92 sec
2020-12-02 19:34:50,809 Stage-1 map = 3%,  reduce = 0%, Cumulative CPU 91.25 sec
2020-12-02 19:34:56,973 Stage-1 map = 4%,  reduce = 0%, Cumulative CPU 103.38 sec
2020-12-02 19:35:09,294 Stage-1 map = 5%,  reduce = 0%, Cumulative CPU 127.72 sec
2020-12-02 19:35:20,574 Stage-1 map = 6%,  reduce = 0%, Cumulative CPU 152.09 sec
2020-12-02 19:35:32,863 Stage-1 map = 7%,  reduce = 0%, Cumulative CPU 176.56 sec
2020-12-02 19:35:45,164 Stage-1 map = 8%,  reduce = 0%, Cumulative CPU 200.85 sec
2020-12-02 19:35:57,456 Stage-1 map = 10%,  reduce = 0%, Cumulative CPU 225.16 sec
2020-12-02 19:36:08,715 Stage-1 map = 11%,  reduce = 0%, Cumulative CPU 249.44 sec
2020-12-02 19:36:21,006 Stage-1 map = 12%,  reduce = 0%, Cumulative CPU 274.08 sec
2020-12-02 19:36:33,301 Stage-1 map = 13%,  reduce = 0%, Cumulative CPU 298.76 sec
2020-12-02 19:36:45,584 Stage-1 map = 14%,  reduce = 0%, Cumulative CPU 323.05 sec
2020-12-02 19:36:50,708 Stage-1 map = 18%,  reduce = 0%, Cumulative CPU 329.03 sec
2020-12-02 19:36:54,813 Stage-1 map = 22%,  reduce = 0%, Cumulative CPU 338.63 sec
2020-12-02 19:37:11,202 Stage-1 map = 23%,  reduce = 0%, Cumulative CPU 374.4 sec
2020-12-02 19:37:23,498 Stage-1 map = 24%,  reduce = 0%, Cumulative CPU 399.03 sec
2020-12-02 19:37:33,742 Stage-1 map = 25%,  reduce = 0%, Cumulative CPU 417.3 sec
2020-12-02 19:37:46,043 Stage-1 map = 26%,  reduce = 0%, Cumulative CPU 441.56 sec
2020-12-02 19:37:57,305 Stage-1 map = 27%,  reduce = 0%, Cumulative CPU 465.96 sec
2020-12-02 19:37:59,355 Stage-1 map = 28%,  reduce = 0%, Cumulative CPU 472.05 sec
2020-12-02 19:38:09,605 Stage-1 map = 29%,  reduce = 0%, Cumulative CPU 490.28 sec
2020-12-02 19:38:21,907 Stage-1 map = 30%,  reduce = 0%, Cumulative CPU 515.03 sec
2020-12-02 19:38:34,203 Stage-1 map = 31%,  reduce = 0%, Cumulative CPU 539.28 sec
2020-12-02 19:38:45,449 Stage-1 map = 32%,  reduce = 0%, Cumulative CPU 563.63 sec
2020-12-02 19:38:57,717 Stage-1 map = 33%,  reduce = 0%, Cumulative CPU 587.87 sec
2020-12-02 19:39:09,989 Stage-1 map = 34%,  reduce = 0%, Cumulative CPU 612.45 sec
2020-12-02 19:39:22,259 Stage-1 map = 35%,  reduce = 0%, Cumulative CPU 636.77 sec
2020-12-02 19:39:34,531 Stage-1 map = 36%,  reduce = 0%, Cumulative CPU 661.07 sec
2020-12-02 19:39:40,666 Stage-1 map = 44%,  reduce = 0%, Cumulative CPU 678.94 sec
2020-12-02 19:40:01,121 Stage-1 map = 46%,  reduce = 0%, Cumulative CPU 715.05 sec
2020-12-02 19:40:02,145 Stage-1 map = 47%,  reduce = 0%, Cumulative CPU 721.32 sec
2020-12-02 19:40:13,387 Stage-1 map = 48%,  reduce = 0%, Cumulative CPU 739.63 sec
2020-12-02 19:40:14,409 Stage-1 map = 49%,  reduce = 0%, Cumulative CPU 745.76 sec
2020-12-02 19:40:18,501 Stage-1 map = 50%,  reduce = 0%, Cumulative CPU 751.87 sec
2020-12-02 19:40:25,656 Stage-1 map = 51%,  reduce = 0%, Cumulative CPU 770.12 sec
2020-12-02 19:40:30,763 Stage-1 map = 53%,  reduce = 0%, Cumulative CPU 776.2 sec
2020-12-02 19:40:36,901 Stage-1 map = 57%,  reduce = 0%, Cumulative CPU 788.84 sec
2020-12-02 19:40:37,925 Stage-1 map = 58%,  reduce = 0%, Cumulative CPU 794.92 sec
2020-12-02 19:40:58,374 Stage-1 map = 59%,  reduce = 0%, Cumulative CPU 834.32 sec
2020-12-02 19:41:09,616 Stage-1 map = 60%,  reduce = 0%, Cumulative CPU 858.82 sec
2020-12-02 19:41:15,751 Stage-1 map = 61%,  reduce = 0%, Cumulative CPU 871.02 sec
2020-12-02 19:41:28,020 Stage-1 map = 62%,  reduce = 0%, Cumulative CPU 895.3 sec
2020-12-02 19:41:38,237 Stage-1 map = 63%,  reduce = 0%, Cumulative CPU 913.53 sec
2020-12-02 19:41:50,495 Stage-1 map = 64%,  reduce = 0%, Cumulative CPU 937.83 sec
2020-12-02 19:42:02,767 Stage-1 map = 65%,  reduce = 0%, Cumulative CPU 962.92 sec
2020-12-02 19:42:14,015 Stage-1 map = 66%,  reduce = 0%, Cumulative CPU 987.17 sec
2020-12-02 19:42:26,281 Stage-1 map = 67%,  reduce = 0%, Cumulative CPU 1011.42 sec
2020-12-02 19:42:33,462 Stage-1 map = 71%,  reduce = 0%, Cumulative CPU 1025.18 sec
2020-12-02 19:42:40,621 Stage-1 map = 72%,  reduce = 0%, Cumulative CPU 1037.31 sec
2020-12-02 19:42:53,904 Stage-1 map = 73%,  reduce = 0%, Cumulative CPU 1070.34 sec
2020-12-02 19:43:04,133 Stage-1 map = 74%,  reduce = 0%, Cumulative CPU 1088.68 sec
2020-12-02 19:43:10,269 Stage-1 map = 75%,  reduce = 0%, Cumulative CPU 1100.88 sec
2020-12-02 19:43:21,511 Stage-1 map = 79%,  reduce = 0%, Cumulative CPU 1124.34 sec
2020-12-02 19:43:24,578 Stage-1 map = 80%,  reduce = 0%, Cumulative CPU 1130.41 sec
2020-12-02 19:43:41,952 Stage-1 map = 81%,  reduce = 0%, Cumulative CPU 1169.77 sec
2020-12-02 19:43:54,210 Stage-1 map = 82%,  reduce = 0%, Cumulative CPU 1194.24 sec
2020-12-02 19:44:00,345 Stage-1 map = 83%,  reduce = 0%, Cumulative CPU 1206.42 sec
2020-12-02 19:44:12,614 Stage-1 map = 84%,  reduce = 0%, Cumulative CPU 1230.71 sec
2020-12-02 19:44:24,880 Stage-1 map = 85%,  reduce = 0%, Cumulative CPU 1255.04 sec
2020-12-02 19:44:36,118 Stage-1 map = 86%,  reduce = 0%, Cumulative CPU 1279.34 sec
2020-12-02 19:44:48,382 Stage-1 map = 87%,  reduce = 0%, Cumulative CPU 1304.0 sec
2020-12-02 19:45:00,653 Stage-1 map = 88%,  reduce = 0%, Cumulative CPU 1328.3 sec
2020-12-02 19:45:12,913 Stage-1 map = 89%,  reduce = 0%, Cumulative CPU 1352.56 sec
2020-12-02 19:45:21,089 Stage-1 map = 94%,  reduce = 0%, Cumulative CPU 1367.49 sec
2020-12-02 19:45:36,417 Stage-1 map = 95%,  reduce = 0%, Cumulative CPU 1385.68 sec
2020-12-02 19:45:37,447 Stage-1 map = 95%,  reduce = 1%, Cumulative CPU 1386.26 sec
2020-12-02 19:45:54,815 Stage-1 map = 96%,  reduce = 1%, Cumulative CPU 1404.78 sec
2020-12-02 19:46:05,043 Stage-1 map = 100%,  reduce = 1%, Cumulative CPU 1415.49 sec
2020-12-02 19:46:07,089 Stage-1 map = 100%,  reduce = 3%, Cumulative CPU 1418.25 sec
2020-12-02 19:46:09,132 Stage-1 map = 100%,  reduce = 6%, Cumulative CPU 1421.26 sec
2020-12-02 19:46:11,176 Stage-1 map = 100%,  reduce = 9%, Cumulative CPU 1424.3 sec
2020-12-02 19:46:13,219 Stage-1 map = 100%,  reduce = 12%, Cumulative CPU 1427.32 sec
2020-12-02 19:46:14,242 Stage-1 map = 100%,  reduce = 15%, Cumulative CPU 1430.27 sec
2020-12-02 19:46:16,290 Stage-1 map = 100%,  reduce = 18%, Cumulative CPU 1433.19 sec
2020-12-02 19:46:18,334 Stage-1 map = 100%,  reduce = 21%, Cumulative CPU 1436.19 sec
2020-12-02 19:46:20,380 Stage-1 map = 100%,  reduce = 24%, Cumulative CPU 1439.41 sec
2020-12-02 19:46:22,429 Stage-1 map = 100%,  reduce = 27%, Cumulative CPU 1442.41 sec
2020-12-02 19:46:24,478 Stage-1 map = 100%,  reduce = 30%, Cumulative CPU 1445.45 sec
2020-12-02 19:46:26,522 Stage-1 map = 100%,  reduce = 33%, Cumulative CPU 1448.44 sec
2020-12-02 19:46:28,570 Stage-1 map = 100%,  reduce = 36%, Cumulative CPU 1451.46 sec
2020-12-02 19:46:30,613 Stage-1 map = 100%,  reduce = 39%, Cumulative CPU 1454.39 sec
2020-12-02 19:46:32,666 Stage-1 map = 100%,  reduce = 42%, Cumulative CPU 1457.36 sec
2020-12-02 19:46:34,718 Stage-1 map = 100%,  reduce = 45%, Cumulative CPU 1460.31 sec
2020-12-02 19:46:36,760 Stage-1 map = 100%,  reduce = 48%, Cumulative CPU 1463.35 sec
2020-12-02 19:46:38,806 Stage-1 map = 100%,  reduce = 52%, Cumulative CPU 1466.45 sec
2020-12-02 19:46:40,854 Stage-1 map = 100%,  reduce = 55%, Cumulative CPU 1469.46 sec
2020-12-02 19:46:42,902 Stage-1 map = 100%,  reduce = 58%, Cumulative CPU 1472.48 sec
2020-12-02 19:46:44,949 Stage-1 map = 100%,  reduce = 61%, Cumulative CPU 1475.47 sec
2020-12-02 19:46:46,992 Stage-1 map = 100%,  reduce = 64%, Cumulative CPU 1478.46 sec
2020-12-02 19:46:49,034 Stage-1 map = 100%,  reduce = 67%, Cumulative CPU 1481.39 sec
2020-12-02 19:46:51,080 Stage-1 map = 100%,  reduce = 70%, Cumulative CPU 1484.33 sec
2020-12-02 19:46:53,124 Stage-1 map = 100%,  reduce = 73%, Cumulative CPU 1487.31 sec
2020-12-02 19:46:55,171 Stage-1 map = 100%,  reduce = 76%, Cumulative CPU 1490.28 sec
2020-12-02 19:46:57,218 Stage-1 map = 100%,  reduce = 79%, Cumulative CPU 1493.29 sec
2020-12-02 19:46:58,267 Stage-1 map = 100%,  reduce = 82%, Cumulative CPU 1496.23 sec
2020-12-02 19:47:00,313 Stage-1 map = 100%,  reduce = 85%, Cumulative CPU 1499.19 sec
2020-12-02 19:47:03,382 Stage-1 map = 100%,  reduce = 88%, Cumulative CPU 1502.29 sec
2020-12-02 19:47:04,406 Stage-1 map = 100%,  reduce = 91%, Cumulative CPU 1505.31 sec
2020-12-02 19:47:06,446 Stage-1 map = 100%,  reduce = 94%, Cumulative CPU 1508.42 sec
2020-12-02 19:47:08,494 Stage-1 map = 100%,  reduce = 97%, Cumulative CPU 1511.45 sec
2020-12-02 19:47:10,538 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 1514.41 sec
MapReduce Total cumulative CPU time: 25 minutes 14 seconds 410 msec
Ended Job = job_1606698967173_0017
Loading data to table test.dm_sale_orc
MapReduce Jobs Launched: 
Stage-Stage-1: Map: 9  Reduce: 33   Cumulative CPU: 1514.41 sec   HDFS Read: 2148850010 HDFS Write: 76049 HDFS EC Read: 0 SUCCESS
Total MapReduce CPU Time Spent: 25 minutes 14 seconds 410 msec
OK
Time taken: 798.193 seconds

2.5 Parquet

源自于googleDremel系统，Parquet相当亍Google Dremel中的数据存储引擎
Apache Parquet 最初的设计是存储嵌套式数据，比如Protocolbuffer，thrift，json等，将这类数据存储成列式格式，以方便对其高效压缩和编码，使用更少的IO操作取出需要的数据，这也是Parquet相比于ORC的优势，它能够透明地将Protobuf和thrift类型的数据中行列式存储
存储metadata，支持schema变更

生产中最常用，列式存储

2.6 Avro

Avro是一种用于支持数据密集型的二进制文件格式。它的文件格式更为紧凑，若要读取大量数据时，Avro能够提供更好的序列化和反序列化性能。并且Avro数据文件天生是带Schema定义的，所以它不需要开发者在API 级别实现自己的Writable对象。最近多个Hadoop子项目都支持Avro 数据格式，如Pig 、Hive、Flume、Sqoop和Hcatalog

生产中几乎不用

2.7 自定义文件格式

除了默认的几种文件格式，用户还可以自定义文件格式
通过继承InputFormat和OutputFormat来自定义文件格式
创建表时指定InputFormat和OutputFormat，来读取Hive中的数据

CREATE TABLE base64example(line string) STORED AS inputformat'org.apache.hadoop.hive.contrib.fileformat.base64.Base64TextInputFormat' outputformat'org.apache.hadoop.hive.contrib.fileformat.base64.Base64TextOutputFormat';

2.8 文件格式选择

以下为个人经验，如有不对，欢迎指正:

通过sqoop导入hive的ODS层数据，建议使用text file。
DWS、DWD、DM的数据，建议使用ORC file。
如存在非结构化的数据，建议使用Parquet file。

参考

1.https://cwiki.apache.org/confluence/display/Hive/LanguageManual

只是甲
关注
专栏目录
Hive学习笔记 —— Hive的数据类型
一只特立独行的猪
10-29 1005
Hive本质上是一个数据库，可以创建表，表有列组成，而列支持的主要类型有：基本数据类型、复杂数据类型、时间数据类型。 1. Hive的数据类型之基本数据类型 tinyint/smallint/int/bigint：整数类型 float/double：浮点数类型 boolean：布尔类型 string：字符串类型 varchar(20)：能存储小于 20 个字符的数据 char(20)：存储的...
关于hive当中的double的数据类型
weixin_30737433的博客
02-12 4695
最近发现通过sqoop将oracle数据库当中的数据到如到hive当中，发现oracle数据库当中number类型的数据变成了hive当中的double类型。不只是变成了double类型，数据好像稍微有点变化。 比如原来的：3769.14 变成了3769.1399999999999 ，还有就是1737.66 变成了1737.6600000000001 貌似有点数据变小了，有的数据变大了 这...
hive存储过程_hive3 存储过程
最新发布
2401_87298759的博客
09-22 198
【代码】hive存储过程_hive3 存储过程。
踩坑-HIVE double类型判空
qq_35757473的博客
02-28 2488
结论： hive中double类型数据 使用 <> '' （不等于空串），返回的是false，引以为戒。
Hive 数据类型 文件格式
松篁
01-19 159
Primitive Data Types --Hive 支持数据类型 Hive supports several sizes of integer and floating-point types, a Boolean type, and character strings of arbitrary length. Hive v0.8.0 added types for timestamp...
大数据编程林子雨实验hive安装包apache-hive-3.1.2-bin
01-16
4. **创建表**：使用HQL语句定义数据表的结构，指定字段、数据类型和分桶等特性。 5. **加载数据**：将数据文件从本地系统或HDFS加载到Hive表中，这通常通过`LOAD DATA`命令完成。 6. **查询数据**：使用HQL进行...
8-大数据技术之Hive
12-15
【大数据技术之Hive】 Hive是Facebook开源的一款基于Hadoop的数据仓库工具，它使得用户能够使用类SQL的查询语言（HQL）处理存储在HDFS中的大规模数据。Hive的出现主要是为了解决在Hadoop上进行复杂数据查询的难题，...
大数据视频_Hive视频教程（上）
06-12
在本课程中，你将学习到，Hive架构原理、安装配置、hiveserver2、数据类型、数据定义、数据操作、查询、自定义UDF函数、窗口函数、压缩和存储、企业级调优、以及结合谷粒影音项目需求，把整个Hive的核心知识点贯穿...
大数据技术之Hive.docx
03-03
大数据技术之Hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张表，并提供类SQL查询功能。本质是将HQL转化成MapReduce程序。Hive处理的数据存储在HDFS，分析数据底层的实现是MapReduce，执行...
Hive中的数据类型
Huc673619的博客
10-05 1302
1.基本数据类型 对于Hive的String类型相当于数据库的varchar类型，该类型是一个可变的字符串，不过它不能声明其中最多能存储多少个字符，理论上它可以存储2GB的字符数。 做一个数据类型测试 create table person(id int,name string,weight double,money bigint); insert into person values(1,'chaoge',70,9999999999999999); 2.集合数据类型 集合数据类型测试 songsong
【大数据入门核心技术-Hive】（二十一）Hive中double和decimal的区别
05-19 367
Decimal 类型的数据在计算机内部使用基于整数的算法来进行表示和计算，因此能够保证更高的精确度。存储空间：Double 使用固定的 8 个字节来存储数据，而 Decimal 则使用可变长度字节数组，根据实际数值的大小和精度来分配存储空间。在上面的示例代码中，我们创建了一个包含 Double 和 Decimal 列的表，并插入了一些数据。运算效率：由于 Decimal 需要进行更复杂的运算和精度保证，因此在一些计算密集型的操作中，Decimal 的运算效率可能会比 Double 较低。
Hive 中的数据类型
weixin_52346735的博客
11-29 274
基本数据类型 TINYINT 1byte有符号整数 20 SMALLINT 2byte有符号整数 20 BOOLEAN 布尔类型，true或者false TRUE FALSE FLOAT 单精度浮点数 3.14159 BINARY 字节数组 下面是些常用的 DOUBLE ** 双精度浮点数 3.14159（常用的） STRING ** 字符系列。可以指定字符集。可以使用单引号或者双引号。（常用的） ‘now is the time’ “for
Hive数据类型
热门推荐
惊鸿的博客
04-11 1万+
Hive数据类型支持的类型原始数据类型注意DATE类型整数类型小数、Boolean、二进制文本类型时间类型类型转换隐式转换显示转换（CAST函数）复杂数据类型详解ARRAY和MAPSTRUCTUNION实例 支持的类型 hive支持两种数据类型：原始数据类型、复杂数据类型 原始数据类型 原始数据类型包括数据型，布尔型，字符串型，具体如下表： 类型 描述 示例 TINYINT（tinyint） 一个字节（8位）有符号整数， -128~127 1 SMALLINT（smallint）
Hive支持的数据类型！基本数据类型！！复杂数据类型！！
陆山右的技术博客
11-15 2541
Hive支持的数据类型分为两类,即基本数据类型和复杂数据类型. 基本数据类型包括数值型、布尔型和字符串类型， 复杂数据类型包括数据组（ARRAY）、映射（MAP）、结构体（STRUCT）和共同体（UNION）.   其中，基础数据类型包括：TINYINT,SMALLINT,INT,BIGINT,BOOLEAN,FLOAT,DOUBLE,STRING,BINARY,TIMESTAMP,DEC...
HIVE使用手册（四）数据类型
Griezmann_7的博客
01-04 914
一、概述，包含一些常用的类型 1.数值类型： TINYINT（1字节有符号整数，从-128至127） SMALLINT（2字节有符号整数，从-32,768至32,767） INT / INTEGER（4字节有符号整数，从-2,147,483,648到2,147,483,647） BIGINT（8字节符号整数，从-9,223,372,036,854,775,808至 9,223,372,...
数据仓库Hive编程——数据类型和文件格式（三）：文本文件数据编码
冯·诺依曼
07-31 1万+
分类目录：商业智能《数据仓库Hive编程》总目录 下面我们一起来研究文件格式，首先举个最简单的例子，也就是文本格式文件。毫无疑问，用户应该很熟悉以逗号或者制表符分割的文本文件，也就是所谓的逗号分隔值（CSV）或者制表符分割值（TSV）。只要用户需要，Hive是支持这些文件格式的，在后面的文章将会介绍其具体使用方式。然而，这两种文件格式有一个共同的缺点，那就是用户需要对文本文件中那些不需要作为分隔符...
hive详解
qq_42681144的博客
06-17 949
一.什么是HIVE: Hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张数据库表，并提供类SQL查询功能。 工作流程：HIVE先定义一张表（这张表的结构和字段数据都存储在MYSQL或DERBY数据库中，这张表的信息就是元数据），再从本地或是HDFS上将文件加载到HIVE，通过mapreduce对数据进行计算，最后输出到HDFS上，形成一个或多个文件。最后用户可通过sq...
Hadoop大数据课程：Hive数据仓库与海量日志处理
"本文主要介绍了如何配置队列运行在海量数据处理中，特别是在Hive数据仓库的应用场景。通过学习中科普开的HADOOP大数据课程，可以了解到如何处理大规模的数据，例如暴风公司每天处理超过1.2TB的日志，执行3500+任务...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值