首页 博客列表 精选博客 智能问答 chat-GPT 关于我
Hive 基本语法操练（一）：表操作
发布日期：2021-10-09 07:57:16 浏览次数：2 分类：技术文章

本文共 23723 字，大约阅读时间需要 79 分钟。

Hive 和 Mysql 的表操作语句类似，如果熟悉 Mysql，学习Hive 的表操作就非常容易了，下面对 Hive 的表操作进行深入讲解。

(1)先来创建一个表名为student的内部表

hive> create table if not exists student (sno INT, sname STRING, age INT, sex STRING) row format delimited fields terminated by ‘\t’ stored as textfile;

OK
Time taken: 0.985 seconds

建表规则如下：

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name

[(col_name data_type [COMMENT col_comment], …)]
[COMMENT table_comment]
[PARTITIONED BY (col_name data_type [COMMENT col_comment], …)]
[CLUSTERED BY (col_name, col_name, …)
[SORTED BY (col_name [ASC|DESC], …)] INTO num_buckets BUCKETS]
[ROW FORMAT row_format]
[STORED AS file_format]
[LOCATION hdfs_path]

•CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXIST 选项来忽略这个异常

•EXTERNAL 关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION）

•LIKE 允许用户复制现有的表结构，但是不复制数据

•COMMENT可以为表与字段增加描述

•ROW FORMAT DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char]

[MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]

| SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, …)]

用户在建表的时候可以自定义 SerDe 或者使用自带的 SerDe。如果没有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED，将会使用自带的 SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的 SerDe，Hive 通过 SerDe 确定表的具体的列的数据。

•STORED AS

SEQUENCEFILE

| TEXTFILE

| RCFILE

| INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname

如果文件数据是纯文本，可以使用 STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCE 。

（2）创建外部表

hive> create external table if not exists student2 (sno INT, sname STRING, age INT, sex STRING) row format delimited fields terminated by '\t' stored as textfile location '/user/external';OKTime taken: 0.089 secondshive> show tables;                             OKstudent1student2Time taken: 0.06 seconds, Fetched: 12 row(s)

（3）删除表

首先创建一个表名为test1的表

hive> create table if not exists test1(id INT, name STRING);OKTime taken: 0.064 seconds

然后查看一下是否有test1表

hive> show tables;OKstudentstudent2test1Time taken: 0.22 seconds, Fetched: 3 row(s)

用命令删test1表

hive> drop table test1;OKTime taken: 0.838 seconds

查看test1表是否删除

hive> show tables;OKstudentstudent2Time taken: 0.14 seconds, Fetched: 2 row(s)

（4）修改表的结构，比如为表增加字段

首先看一下student表的结构

hive> desc student;OKsno                     int                                         sname                   string                                      age                     int                                         sex                     string                                      Time taken: 0.142 seconds, Fetched: 4 row(s)

为表student增加两个字段

hive> alter table student add columns (address STRING, grade STRING);OKTime taken: 0.138 seconds

再查看一下表的结构，看是否增加

hive> desc student;OKsno                     int                                         sname                   string                                      age                     int                                         sex                     string                                      address                 string                                      grade                   string                                      Time taken: 0.145 seconds, Fetched: 6 row(s)

（5）修改表名student为student1

hive> alter table student rename to student1;OKTime taken: 0.15 seconds

查看一下

hive> show tables;OKstudent1student2Time taken: 0.028 seconds, Fetched: 2 row(s)

（6）创建和已知表相同结构的表

hive> create table copy_student1 like student1;OKTime taken: 0.092 seconds

查看一下

hive> show tables;OKcopy_student1student1student2Time taken: 0.03 seconds, Fetched: 3 row(s)

2、加入导入数据的方法，（数据里可以包含重复记录），只有导入了数据，才能供后边的查询使用

（1）加载本地数据load

首先看一下表的结构

hive> desc student1;OKsno                     int                                         sname                   string                                      age                     int                                         sex                     string                                      address                 string                                      grade                   string                                      Time taken: 0.118 seconds, Fetched: 6 row(s)

创建/home/hadoop/data目录，并在该目录下创建student1.txt文件，添加如下内容

[hadoop@master ~]$ cd /home[hadoop@master home]$ lltotal 4drwx------. 28 hadoop hadoop 4096 May 17 18:42 hadoop[hadoop@master home]$ cd hadoop/[hadoop@master ~]$ lltotal 32drwxr-xr-x. 2 hadoop hadoop 4096 Apr  3 18:12 Desktopdrwxr-xr-x. 2 hadoop hadoop 4096 Apr  3 18:12 Documentsdrwxr-xr-x. 2 hadoop hadoop 4096 Apr  3 18:12 Downloadsdrwxr-xr-x. 2 hadoop hadoop 4096 Apr  3 18:12 Musicdrwxr-xr-x. 2 hadoop hadoop 4096 Apr  3 18:12 Picturesdrwxr-xr-x. 2 hadoop hadoop 4096 Apr  3 18:12 Publicdrwxr-xr-x. 2 hadoop hadoop 4096 Apr  3 18:12 Templatesdrwxr-xr-x. 2 hadoop hadoop 4096 Apr  3 18:12 Videos[hadoop@master ~]$ sudo mkdir data/[hadoop@master ~]$ cd data/[hadoop@master data]$ sudo vim student1.txt  201501001       张三    22      男      北京    大三  201501003       李四    23      男      上海    大二  201501004       王娟    22      女      广州    大三  201501010       周王    24      男      深圳    大四  201501011       李红    23      女      北京    大三

加载数据到student1表中

hive> load data local inpath '/home/hadoop/data/student1.txt' into table student1;Loading data to table default.student1Table default.student1 stats: [numFiles=1, numRows=0, totalSize=300, rawDataSize=0]OKTime taken: 1.271 seconds

查看是否加载成功

hive> select * from student1;OK201501001   张三  22  男   北京  大三201501003   李四  23  男   上海  大二201501004   王娟  22  女   广州  大三201501010   周王  24  男   深圳  大四201501011   李红  23  女   北京  大三Time taken: 0.052 seconds, Fetched: 15 row(s)

（2）加载hdfs中的文件

首先将文件student1.txt上传到hdfs文件系统对应目录上

[hadoop@master hadoop-2.6.0]$ hadoop fs -put /home/hadoop/data/student1.txt /user/hive[hadoop@master hadoop-2.6.0]$ hadoop fs -ls /user/hiveFound 2 items-rw-r--r--   3 hadoop supergroup        193 2018-05-17 23:54 /user/hive/student1.txtdrwxr-xr-x   - hadoop supergroup          0 2018-05-17 23:10 /user/hive/warehouse

加载hdfs中的文件数据到copy_student1表中

hive> LOAD DATA INPATH '/user/hive/student1.txt' INTO TABLE copy_student1;Loading data to table default.copy_student1Table default.copy_student1 stats: [numFiles=1, totalSize=191]OKTime taken: 1.354 seconds

查看是否加载成功

hive> SELECT * FROM copy_student1;OK201501001   张三  22  男   北京  大三201501003   李四  23  男   上海  大二201501004   王娟  22  女   广州  大三201501010   周王  24  男   深圳  大四201501011   李红  23  女   北京  大三Time taken: 0.44 seconds, Fetched: 5 row(s)

（3）表插入数据（单表插入、多表插入）

1）单表插入

首先创建一个表copy_student2,表结构和student1相同

hive> create table copy_student2 like student1;OKTime taken: 0.691 seconds

查看一下是否创建成功

hive> show tables;OKcopy_student1copy_student2student1student2Time taken: 0.065 seconds, Fetched: 4 row(s)

看一下copy_student2表的表结构

hive> DESC copy_student2;OKsno                     int                                         sname                   string                                      age                     int                                         sex                     string                                      address                 string                                      grade                   string                                      Time taken: 0.121 seconds, Fetched: 6 row(s)

把表student1中的数据插入到copy_student2表中

hive> insert overwrite table copy_student2 select * from copy_student1;Query ID = hadoop_20180518000101_af36da39-e88b-4c1b-b89c-c000bf5f59ddTotal jobs = 3Launching Job 1 out of 3Number of reduce tasks is set to 0 since there's no reduce operatorStarting Job = job_1526553207632_0001, Tracking URL = http://master:8088/proxy/application_1526553207632_0001/Kill Command = /opt/modules/hadoop-2.6.0/bin/hadoop job  -kill job_1526553207632_0001Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 02018-05-18 00:01:16,715 Stage-1 map = 0%,  reduce = 0%2018-05-18 00:01:29,632 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 1.36 secMapReduce Total cumulative CPU time: 1 seconds 360 msecEnded Job = job_1526553207632_0001Stage-4 is selected by condition resolver.Stage-3 is filtered out by condition resolver.Stage-5 is filtered out by condition resolver.Moving data to: hdfs://ns/tmp/hive/hadoop/d6cb41c0-cc18-471e-861f-f08553caea48/hive_2018-05-18_00-01-00_086_4552315865937351442-1/-ext-10000Loading data to table default.copy_student2Table default.copy_student2 stats: [numFiles=1, numRows=5, totalSize=190, rawDataSize=185]MapReduce Jobs Launched: Stage-Stage-1: Map: 1   Cumulative CPU: 1.36 sec   HDFS Read: 403 HDFS Write: 268 SUCCESSTotal MapReduce CPU Time Spent: 1 seconds 360 msecOKTime taken: 35.015 seconds

查看数据是否插入

hive> select * from copy_student2;OK201501001   张三  22  男   北京  大三201501003   李四  23  男   上海  大二201501004   王娟  22  女   广州  大三201501010   周王  24  男   深圳  大四201501011   李红  23  女   北京  大三Time taken: 0.073 seconds, Fetched: 5 row(s)

2）多表插入

先创建两个表

hive> CREATE TABLE copy_student3 LIKE student1;OKTime taken: 0.21 secondshive> CREATE TABLE copy_student4 LIKE student1;OKTime taken: 0.099 seconds

向多表插入数据

hive> FROM student1 INSERT OVERWRITE TABLE copy_student3 SELECT * INSERT OVERWRITE TABLE copy_student4 SELECT *;(省略MapReduce过程)

查看结果

hive> select * from copy_student3;OK201501001   张三  22  男   北京  大三201501003   李四  23  男   上海  大二201501004   王娟  22  女   广州  大三201501010   周王  24  男   深圳  大四201501011   李红  23  女   北京  大三Time taken: 0.049 seconds, Fetched: 5 row(s)
hive> select * from copy_student4;OK201501001   张三  22  男   北京  大三201501003   李四  23  男   上海  大二201501004   王娟  22  女   广州  大三201501010   周王  24  男   深圳  大四201501011   李红  23  女   北京  大三Time taken: 0.049 seconds, Fetched: 5 row(s)

3、有关表的内容的查询

（1）查表的所有内容

hive> select * from student1;OK201501001   张三  22  男   北京  大三201501003   李四  23  男   上海  大二201501004   王娟  22  女   广州  大三201501010   周王  24  男   深圳  大四201501011   李红  23  女   北京  大三Time taken: 0.041 seconds, Fetched: 5 row(s)

（2）查表的某个字段的属性

hive> select sname from student1;OK张三李四王娟周王李红Time taken: 0.056 seconds, Fetched: 5 row(s)

（3）where条件查询

hive> SELECT * FROM student1 WHERE sno>201501004 AND address="北京";OK201501011   李红  23  女   北京  大三Time taken: 0.203 seconds, Fetched: 1 row(s)

（4）all和distinct的区别（这就要求表中要有重复的记录，或者某个字段要有重复的数据）

hive> select all age,grade from student1;OK22  大三23  大二22  大三24  大四23  大三Time taken: 0.054 seconds, Fetched: 5 row(s)
hive> select age,grade from student1;    OK22  大三23  大二22  大三24  大四23  大三Time taken: 0.053 seconds, Fetched: 5 row(s)
hive> select distinct age,grade from student1;Query ID = hadoop_20180518001414_fe7461b7-7edd-4661-abc4-14859e3dba91Total jobs = 1Launching Job 1 out of 1Number of reduce tasks not specified. Estimated from input data size: 1In order to change the average load for a reducer (in bytes):  set hive.exec.reducers.bytes.per.reducer=
   
    In order to limit the maximum number of reducers:  set hive.exec.reducers.max=
    
     In order to set a constant number of reducers:  set mapreduce.job.reduces=
     
      Starting Job = job_1526553207632_0004, Tracking URL = http://master:8088/proxy/application_1526553207632_0004/Kill Command = /opt/modules/hadoop-2.6.0/bin/hadoop job  -kill job_1526553207632_0004Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 12018-05-18 00:14:10,913 Stage-1 map = 0%,  reduce = 0%2018-05-18 00:14:22,260 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 1.27 sec2018-05-18 00:14:36,734 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 2.51 secMapReduce Total cumulative CPU time: 2 seconds 510 msecEnded Job = job_1526553207632_0004MapReduce Jobs Launched: Stage-Stage-1: Map: 1  Reduce: 1   Cumulative CPU: 2.51 sec   HDFS Read: 391 HDFS Write: 40 SUCCESSTotal MapReduce CPU Time Spent: 2 seconds 510 msecOK22  大三23  大三23  大二24  大四Time taken: 34.358 seconds, Fetched: 4 row(s)
     
    
   
hive> select distinct age from student1;      Query ID = hadoop_20180518001414_69278499-54b5-42b7-867c-4ebe8113a2f9Total jobs = 1Launching Job 1 out of 1Number of reduce tasks not specified. Estimated from input data size: 1In order to change the average load for a reducer (in bytes):  set hive.exec.reducers.bytes.per.reducer=
   
    In order to limit the maximum number of reducers:  set hive.exec.reducers.max=
    
     In order to set a constant number of reducers:  set mapreduce.job.reduces=
     
      Starting Job = job_1526553207632_0005, Tracking URL = http://master:8088/proxy/application_1526553207632_0005/Kill Command = /opt/modules/hadoop-2.6.0/bin/hadoop job  -kill job_1526553207632_0005Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 12018-05-18 00:14:56,548 Stage-1 map = 0%,  reduce = 0%2018-05-18 00:15:03,047 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 0.85 sec2018-05-18 00:15:10,390 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 1.98 secMapReduce Total cumulative CPU time: 1 seconds 980 msecEnded Job = job_1526553207632_0005MapReduce Jobs Launched: Stage-Stage-1: Map: 1  Reduce: 1   Cumulative CPU: 1.98 sec   HDFS Read: 391 HDFS Write: 9 SUCCESSTotal MapReduce CPU Time Spent: 1 seconds 980 msecOK222324Time taken: 23.181 seconds, Fetched: 3 row(s)

（5）limit限制查询

hive> SELECT * FROM student1 LIMIT 4;OK201501001   张三  22  男   北京  大三201501003   李四  23  男   上海  大二201501004   王娟  22  女   广州  大三201501010   周王  24  男   深圳  大四Time taken: 0.253 seconds, Fetched: 4 row(s)

(6) GROUP BY 分组查询

group by 分组查询在数据统计时比较常用，接下来讲解 group by 的使用。

1) 创建一个表 group_test，表的内容如下。

create table group_test(uid STRING, gender STRING, ip STRING) row format delimited fields terminated by '\t' stored as textfile;OKTime taken: 0.449 seconds
[hadoop@master test]$ sudo vim user.txt08  female  192.168.1.4201  male    192.168.1.2202  female  192.168.1.301  male    192.168.1.2603  male    192.168.1.508  female  192.168.1.6204  male    192.168.1.906  female  192.168.1.5206  female  192.168.1.708  female  192.168.1.2105  male    192.168.1.801  male    192.168.1.201  male    192.168.1.3205  male    192.168.1.2903  male    192.168.1.2306  female  192.168.1.20107  female  192.168.1.1108  female  192.168.1.88

向 group_test 表中导入数据。

hive> load data local inpath ‘/home/hadoop/test/user.txt’ into table group_test;

Loading data to table default.group_test
Table default.group_test stats: [numFiles=1, totalSize=193]
OK
Time taken: 0.865 seconds

2) 计算表的行数命令如下。

hive> select count(*) from group_test;Query ID = hadoop_20180518040808_a73617a5-dd9a-48c4-b2a9-0ce1dd4bf4cdTotal jobs = 1Launching Job 1 out of 1Number of reduce tasks determined at compile time: 1In order to change the average load for a reducer (in bytes):  set hive.exec.reducers.bytes.per.reducer=
   
    In order to limit the maximum number of reducers:  set hive.exec.reducers.max=
    
     In order to set a constant number of reducers:  set mapreduce.job.reduces=
     
      Starting Job = job_1526553207632_0013, Tracking URL = http://master:8088/proxy/application_1526553207632_0013/Kill Command = /opt/modules/hadoop-2.6.0/bin/hadoop job  -kill job_1526553207632_0013Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 12018-05-18 04:08:45,431 Stage-1 map = 0%,  reduce = 0%2018-05-18 04:08:58,184 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 1.59 sec2018-05-18 04:09:09,818 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 2.78 secMapReduce Total cumulative CPU time: 2 seconds 780 msecEnded Job = job_1526553207632_0013MapReduce Jobs Launched: Stage-Stage-1: Map: 1  Reduce: 1   Cumulative CPU: 2.78 sec   HDFS Read: 624 HDFS Write: 3 SUCCESSTotal MapReduce CPU Time Spent: 2 seconds 780 msecOK18Time taken: 34.896 seconds, Fetched: 1 row(s)hive> create table group_gender_sum(gender STRING, sum INT);   OKTime taken: 0.081 seconds

3) 根据性别计算去重用户数。

首先创建一个表 group_gender_sum

hive> create table group_gender_sum(gender STRING,sum INT);OKTime taken: 0.142 seconds

将表 group_test 去重后的数据导入表 group_gender_sum。

hive> insert overwrite table group_gender_sum select group_test.gender,count(distinct group_test.uid) from group_test group by group_test.gender;Query ID = hadoop_20180518041010_e51ae2fb-0b9e-4b5d-9a0c-87946496282fTotal jobs = 1Launching Job 1 out of 1Number of reduce tasks not specified. Estimated from input data size: 1In order to change the average load for a reducer (in bytes):  set hive.exec.reducers.bytes.per.reducer=
   
    In order to limit the maximum number of reducers:  set hive.exec.reducers.max=
    
     In order to set a constant number of reducers:  set mapreduce.job.reduces=
     
      Starting Job = job_1526553207632_0014, Tracking URL = http://master:8088/proxy/application_1526553207632_0014/Kill Command = /opt/modules/hadoop-2.6.0/bin/hadoop job  -kill job_1526553207632_0014Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 12018-05-18 04:10:44,336 Stage-1 map = 0%,  reduce = 0%2018-05-18 04:10:50,573 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 0.82 sec2018-05-18 04:10:58,903 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 3.12 secMapReduce Total cumulative CPU time: 3 seconds 120 msecEnded Job = job_1526553207632_0014Loading data to table default.group_gender_sumTable default.group_gender_sum stats: [numFiles=1, numRows=17, totalSize=371, rawDataSize=354]MapReduce Jobs Launched: Stage-Stage-1: Map: 1  Reduce: 1   Cumulative CPU: 3.12 sec   HDFS Read: 624 HDFS Write: 452 SUCCESSTotal MapReduce CPU Time Spent: 3 seconds 120 msecOKTime taken: 29.357 seconds

同时可以做多个聚合操作，但是不能有两个聚合操作有不同的 distinct 列。下面正确合法的聚合操作语句。

首先创建一个表 group_gender_agg

hive> create table group_gender_agg(gender STRING, sum1 INT, sum2 INT, sum3 INT);OKTime taken: 0.092 seconds

将表 group_test 聚合后的数据插入表 group_gender_agg。

hive> insert overwrite table group_gender_agg select group_test.gender,count(distinct group_test.uid),count(*),sum(distinct group_test.uid) from group_test group by group_test.gender;Query ID = hadoop_20180518041212_0cf81102-2c8f-4370-8cda-3b7d61c51877Total jobs = 1Launching Job 1 out of 1Number of reduce tasks not specified. Estimated from input data size: 1In order to change the average load for a reducer (in bytes):  set hive.exec.reducers.bytes.per.reducer=
   
    In order to limit the maximum number of reducers:  set hive.exec.reducers.max=
    
     In order to set a constant number of reducers:  set mapreduce.job.reduces=
     
      Starting Job = job_1526553207632_0015, Tracking URL = http://master:8088/proxy/application_1526553207632_0015/Kill Command = /opt/modules/hadoop-2.6.0/bin/hadoop job  -kill job_1526553207632_0015Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 12018-05-18 04:12:45,953 Stage-1 map = 0%,  reduce = 0%2018-05-18 04:12:52,218 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 0.81 sec2018-05-18 04:12:59,519 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 2.42 secMapReduce Total cumulative CPU time: 2 seconds 420 msecEnded Job = job_1526553207632_0015Loading data to table default.group_gender_aggTable default.group_gender_agg stats: [numFiles=1, numRows=17, totalSize=439, rawDataSize=422]MapReduce Jobs Launched: Stage-Stage-1: Map: 1  Reduce: 1   Cumulative CPU: 2.42 sec   HDFS Read: 624 HDFS Write: 520 SUCCESSTotal MapReduce CPU Time Spent: 2 seconds 420 msecOKTime taken: 21.103 seconds

但是，不允许在同一个查询内有多个 distinct 表达式。下面的查询是不允许的。

hive> insert overwrite table group_gender_agg select group_test.gender,count(distinct group_test.uid),count(distinct group_test.ip) from group_test group by group_test.gender;

这条查询语句是不合法的，因为 distinct group_test.uid 和 distinct group_test.ip 操作了uid 和 ip 两个不同的列。

(7) ORDER BY 排序查询

ORDER BY 会对输入做全局排序，因此只有一个 Reduce（多个 Reduce 无法保证全局有序）会导致当输入规模较大时，需要较长的计算时间。使用 ORDER BY 查询的时候，为了优化查询的速度，使用 hive.mapred.mode 属性。

hive.mapred.mode = nonstrict;(default value/默认值)hive.mapred.mode=strict;

与数据库中 ORDER BY 的区别在于，在 hive.mapred.mode=strict 模式下必须指定limit ，否则执行会报错。

hive> set hive.mapred.mode=strict;hive> select * from group_test order by uid limit 5;Query ID = hadoop_20180518041414_f4daefe3-60ec-43d3-ab5c-d7fa7518fc5cTotal jobs = 1Launching Job 1 out of 1Number of reduce tasks determined at compile time: 1In order to change the average load for a reducer (in bytes):  set hive.exec.reducers.bytes.per.reducer=
   
    In order to limit the maximum number of reducers:  set hive.exec.reducers.max=
    
     In order to set a constant number of reducers:  set mapreduce.job.reduces=
     
      Starting Job = job_1526553207632_0016, Tracking URL = http://master:8088/proxy/application_1526553207632_0016/Kill Command = /opt/modules/hadoop-2.6.0/bin/hadoop job  -kill job_1526553207632_0016Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 12018-05-18 04:14:18,047 Stage-1 map = 0%,  reduce = 0%2018-05-18 04:14:25,572 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 0.98 sec2018-05-18 04:14:31,896 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 2.07 secMapReduce Total cumulative CPU time: 2 seconds 70 msecEnded Job = job_1526553207632_0016MapReduce Jobs Launched: Stage-Stage-1: Map: 1  Reduce: 1   Cumulative CPU: 2.07 sec   HDFS Read: 624 HDFS Write: 121 SUCCESSTotal MapReduce CPU Time Spent: 2 seconds 70 msecOK01  male    192.168.1.3201  male    192.168.1.201  male    192.168.1.2201  male    192.168.1.2602  female  192.168.1.3Time taken: 22.228 seconds, Fetched: 5 row(s)

(8) SORT BY 查询

sort by 不受 hive.mapred.mode 的值是否为 strict 和 nostrict 的影响。sort by 的数据只能保证在同一个 Reduce 中的数据可以按指定字段排序。

使用 sort by 可以指定执行的 Reduce 个数（set mapred.reduce.tasks=< number>）这样可以输出更多的数据。对输出的数据再执行归并排序，即可以得到全部结果。

hive> set hive.mapred.mode=strict;                  hive> select * from group_test sort by uid ;Query ID = hadoop_20180518041616_68543eaf-2bac-4c35-bad6-dd286052ded6Total jobs = 1Launching Job 1 out of 1Number of reduce tasks not specified. Estimated from input data size: 1In order to change the average load for a reducer (in bytes):  set hive.exec.reducers.bytes.per.reducer=
   
    In order to limit the maximum number of reducers:  set hive.exec.reducers.max=
    
     In order to set a constant number of reducers:  set mapreduce.job.reduces=
     
      Starting Job = job_1526553207632_0017, Tracking URL = http://master:8088/proxy/application_1526553207632_0017/Kill Command = /opt/modules/hadoop-2.6.0/bin/hadoop job  -kill job_1526553207632_0017Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 12018-05-18 04:16:11,201 Stage-1 map = 0%,  reduce = 0%2018-05-18 04:16:19,537 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 0.83 sec2018-05-18 04:16:26,844 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 1.88 secMapReduce Total cumulative CPU time: 1 seconds 880 msecEnded Job = job_1526553207632_0017MapReduce Jobs Launched: Stage-Stage-1: Map: 1  Reduce: 1   Cumulative CPU: 1.88 sec   HDFS Read: 624 HDFS Write: 469 SUCCESSTotal MapReduce CPU Time Spent: 1 seconds 880 msecOK01  male    192.168.1.3201  male    192.168.1.201  male    192.168.1.2201  male    192.168.1.2602  female  192.168.1.303  male    192.168.1.503  male    192.168.1.2304  male    192.168.1.905  male    192.168.1.2905  male    192.168.1.806  female  192.168.1.706  female  192.168.1.5206  female  192.168.1.201   07  female  192.168.1.1108  female  192.168.1.8808  female  192.168.1.2108  female  192.168.1.6208  female  192.168.1.42Time taken: 26.065 seconds, Fetched: 18 row(s)

(9) DISTRIBUTE BY 排序查询

按照指定的字段对数据划分到不同的输出 Reduce 文件中，操作如下。

hive> insert overwrite local directory '/home/hadoop/djt/test' select * from group_test distribute by length(gender);

此方法根据 gender 的长度划分到不同的 Reduce 中，最终输出到不同的文件中。length 是内建函数，也可以指定其它的函数或者使用自定义函数。

hive> insert overwrite local directory '/home/hadoop/djt/test' select * from group_test order by gender  distribute by length(gender);

order by gender 与 distribute by length(gender) 不能共用。

(10) CLUSTER BY 查询

cluster by 除了具有 distribute by 的功能外还兼具 sort by 的功能。

以上就是博主为大家介绍的这一板块的主要内容，这都是博主自己的学习过程，希望能给大家带来一定的指导作用，有用的还望大家点个支持，如果对你没用也望包涵，有错误烦请指出。如有期待可关注博主以第一时间获取更新哦，谢谢！　

转载地址：https://blog.csdn.net/py_123456/article/details/80357783 如侵犯您的版权，请留言回复原文章的地址，我们会给您删除此文章，给您带来不便请您谅解！

上一篇：Hive 基本语法操练（二）：视图和索引操作
下一篇：Hive 环境的安装部署

发表评论

最新留言

网站不错 人气很旺了 加油
[***.192.178.218]2024年04月01日 18时35分38秒

关于作者

    喝酒易醉，品茶养心，人生如梦，品茶悟道，何以解忧？唯有杜康！
-- 愿君每日到此一游！

推荐文章

Unity获取系统信息SystemInfo（CPU、显卡、操作系统等信息） 2019-04-27
Unity中获取物体的尺寸（size）的三种方法 2019-04-27
Unity中的关节组件和绳子效果的实现 2019-04-27
Unity可视化编程插件: Bolt，可以像UE4的蓝图那样啦 2019-04-27
Android使用adb logcat时日志中文乱码问题，使用chcp 65001设置编码即可 2019-04-27
Android的.dex、.odex与.oat文件扫盲 2019-04-27
Unity移动应用如何在Bugly上查看崩溃堆栈 2019-04-27
unity3D 在屏幕边框创建碰撞框 2019-04-27
xml中常用的转义符 2021-06-30
关于MSDK的几个难点 2021-06-30
使用UnityEditor做工具 2021-06-30
Visual Studio我常用的快捷键 2021-06-30
写C# dll供Unity调用 2021-06-30
Linux制作run安装包 2021-06-30
一分钟学会C#解析XML 2021-06-30
unity AssetBundle的资源管理 2021-06-30
【转】Unity中HideInInspector和SerializeField一起使用 2019-04-27
单例模板类 2019-04-27
Unity与java相互调用 2019-04-27
android截屏代码 2019-04-27
白红宇的个人博客 - 记录点点滴滴的事 - 您是第 308096163 位访客
访问时间: 2024-04-26 17:38:02 访问IP: 3.19.56.45     Copyright © 2020 - 2023 blog.css8.cn 京ICP备2021015314号-1 手机版