Hive 提供了类 SQL 语法 HQL，封装了底层的 MapReduce 过程。
目录
概述
操作数据库 创建、修改、删除数据库，查看数据库信息
操作数据表 创建、修改、删除表，查看表的信息，数据类型，复杂数据类型用例
插入数据 INSERT 关键字的使用
加载数据 LOAD 关键字的使用
多表插入 扫描一遍源表就可以生成多个不相交的输出
动态分区插入 在插入数据到分区表时，根据源表的列值自动创建分区
导入导出表数据 EXPORT 和 IMPORT 关键字的使用
保存查询结果 保存查询结果到本地、HDFS 或 Hive 表
清空表数据 TRUNCATE 关键字的使用
查询数据 SELECT 关键字的使用
子查询部分 将子查询部分产生的结果集保存在内存中，供整个 SQL 使用
UNION 将多个查询结果合并为一个结果集
聚合函数 COUNT、MAX、MIN、SUM、AVG 聚合函数的使用
分组 GROUP BY 和 HAVING 关键字的使用
排序 ORDER BY、S BY、DIRIBUTE BY、CLUSTER BY 关键字的使用
连接 内连接、外连接、多表连接、分桶表连接
数据抽样 块抽样、分桶抽样、随机抽样
常用内置函数 日期函数、字符函数、解析json、条件函数、窗口函数、排名函数、行列转换
案例 复制分区表、统计分析与排名、ETL
本文主要介绍 HiveQL，可以在 Hive CLI 或 Beeline 中执行这些语句。
hive/ 1 2 3 4 5 6 $ nohup bin/hive --service metastore 2>&1 & $ nohup bin/hive --service hiveserver2 2>&1 & $ bin/beeline -n root -p root -u jdbc:hive2://hadoopserver:10000
数据定义语言DDL 用来定义数据库对象，比如数据库，表，列等。
操作数据库 创建数据库 创建 Hive 数据库，可以指定创建位置：
1 2 3 4 5 create database if not exists myhive;create database myhive2 location '/myhive2' ;
Hive 数据库默认存放位置的修改可以参考 数据库默认存放位置
查看数据库信息 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 hive> show databases; default myhive hive> desc database myhive; + | db_name | comment | location | owner_name | owner_type | parameters | + | myhive2 | | hdfs:/ / node01:8020 / user / hive/ warehouse/ myhive.db | root | USER | | + hive> desc database extended myhive; + | db_name | comment | location | owner_name | owner_type | parameters | + | myhive2 | | hdfs:/ / node01:8020 / user / hive/ warehouse/ myhive.db | root | USER | {createtime= 20190611 } | +
修改数据库 修改数据库的部分属性：
1 alter database myhive set dbproperties('createtime' = '20190611' );
注：数据库的大部分元数据信息，包括数据库的名称以及数据库所在的位置，是不可更改的。
删除数据库 1 2 3 4 5 drop database myhive2;drop database myhive cascade;
操作数据表 创建表 1 2 3 4 5 6 7 8 9 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 EXISTS 选项来忽略这个异常。EXTERNAL：创建一个外部表，需要在建表的同时指定一个指向数据实际存放位置的路径。LIKE：复制现有的表结构，但是不复制表数据 。PARTITIONED BY/CLUSTERED BY：Hive 引入 Partition（分区）和 Bucket（桶）的概念，可以在创建表时进行设置，这样做可以让查询发生在小范围的数据上，提高效率。ROW FORMAT <row_format>|ROW FORMAT SERDE <serde_name>：创建表时可以自定义 SerDe（Serializer/Deserializer）或者使用默认的 SerDe（ROW FORMAT DELIMITED）。STORED AS <file_format>：如果文件数据是纯文本，可以使用 STORED AS TEXTFILE；如果数据需要压缩，使用STORED AS SEQUENCEFILE。
内部表与外部表
如果数据只由 Hive 使用，可以创建内部表 （managed table，又称托管表），数据由 Hive 管理。删除内部表时，元数据和表数据会被一起删除 。
如果数据是公共的，需要创建外部表 （external table），数据由 HDFS 管理，一般是在数据的存放位置处创建外部表（使用 location 指定该目录）。删除外部表时，只删除元数据，不删除表数据 。
创建表时（无论内部表还是外部表 ），如果不指定 location，那么会在 Hive 的仓库目录（/user/hive/warehouse/databasename.db）下生成一个与表名相同的目录，表中的数据会被存放在该目录下。
加载数据（load data）时，如果加载的是本地文件系统中的文件（load data local inpath），则该文件会从本地文件系统复制 到 HDFS 中的表目录中；如果是加载的是 HDFS 中的文件（load data inpath），则该文件会被移动 到 HDFS 中的表目录中。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 use myhive; create table if not exists student(id string, name string, score double ) row format delimited fields terminated by '\t' stored as textfile tblproperties('skip.header.line.count' = '1' ); load data local inpath '/data/stu.csv' into table student2; load data inpath '/hdfsdata/stu.csv' overwrite into table student2; create table student3 as select * from student2;create table student4 like student2;create external table stu(id string, name string, score double ) row format delimited fields terminated by '\t' location '/hivetable/stu/' ;
分区表 Hive 把表组织成分区，可以将数据根据日期、时间等进行粗粒度的划分，这样在查询的时候可以直接查询某个分区，提高查询速度。分区在 HDFS中 表现为目录 。创建分区表：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 create table result ( sid string COMMENT '学号' , cid string COMMENT '课程编号' , score double COMMENT '分数' ) COMMENT '学生成绩表' partitioned by (month string COMMENT '考试日期' ) row format delimited fields terminated by '\t' stored as parquet; create table result2(sid string, cid string, score double ) partitioned by (year string, month string, day string) row format delimited fields terminated by '\t' stored as parquet;
分区在 HDFS 中表现为目录 ：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 $ hdfs dfs -ls -R /user/hive/warehouse/myhive.db/result drwxrwxrwt - root hive 0 2020-06-16 15:26 /user/hive/warehouse/myhive.db/result/month=202001 -rwxrwxrwt 3 root hive 402 2020-06-16 15:26 /user/hive/warehouse/myhive.db/result/month=202001/result.csv drwxrwxrwt - root hive 0 2020-06-16 15:26 /user/hive/warehouse/myhive.db/result/month=202005 -rwxrwxrwt 3 root hive 402 2020-06-16 15:26 /user/hive/warehouse/myhive.db/result/month=202005/result.csv $ hdfs dfs -ls -R /user/hive/warehouse/myhive.db/result2 drwxrwxrwt - root hive 0 2020-06-16 15:27 /user/hive/warehouse/myhive.db/result2/year=2020 drwxrwxrwt - root hive 0 2020-06-16 15:26 /user/hive/warehouse/myhive.db/result2/year=2020/month=01 drwxrwxrwt - root hive 0 2020-06-16 15:26 /user/hive/warehouse/myhive.db/result2/year=2020/month=01/day=11 -rwxrwxrwt 3 root hive 402 2020-06-16 15:26 /user/hive/warehouse/myhive.db/result2/year=2020/month=01/day=11/result.csv drwxrwxrwt - root hive 0 2020-06-16 15:27 /user/hive/warehouse/myhive.db/result2/year=2020/month=05 drwxrwxrwt - root hive 0 2020-06-16 15:27 /user/hive/warehouse/myhive.db/result2/year=2020/month=05/day=01 -rwxrwxrwt 3 root hive 402 2020-06-16 15:27 /user/hive/warehouse/myhive.db/result2/year=2020/month=05/day=01/result.csv
外部分区表 场景：现有 HDFS 集群，会在对应的日期文件夹下生成文件 result.csv，如 /hivetable/score/month=202005/result.csv。文件为公共的，不能移动。
需求：创建对应的 Hive 表，将 result.csv 中的数据加载到表中；删除表后，数据不能删除。
数据准备：
1 2 $ hdfs dfs -mkdir -p /hivetable/result/month=202005 $ hdfs dfs -put result.csv /hivetable/result/month=202005/
实现：
创建外部分区表，指定数据文件存放目录：
1 2 3 4 5 6 7 8 9 create external table result3(sid string, cid string, score double ) partitioned by (month string) row format delimited fields terminated by '\t' location '/hivetable/result' ; select * from result3;result3.sid result3.cid result3.score result3.month
有两种方式可以让外部分区表能够查询到数据：
1 msck repair table result3;
1 2 alter table result3 add partition (month = '202005' );
查看元数据
创建外部分区表指定 location，或是以 dfs -put 的方式“加载”数据进分区表目录，数据都是无法被查询到的，原因就是没有生成 Hive 的元数据，而它存储在 MySQL 中：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 mysql> use metastore; mysql> select * from PARTITIONS; + | PART_ID | CREATE_TIME | LAST_ACCESS_TIME | PART_NAME | SD_ID | TBL_ID | + | 5179 | 1592292400 | 0 | month = 202001 | 7821 | 2661 | | 5180 | 1592292404 | 0 | month = 202005 | 7822 | 2661 | | 5181 | 1592292419 | 0 | year = 2020 / month = 01 / day = 11 | 7823 | 2662 | | 5182 | 1592292422 | 0 | year = 2020 / month = 05 / day = 01 | 7824 | 2662 | | 5185 | 1592292738 | 0 | month = 202101 | 7828 | 2661 | | 5186 | 1592292741 | 0 | year = 2021 / month = 01 / day = 11 | 7829 | 2662 | | 5187 | 1592292749 | 0 | month = 202105 | 7830 | 2661 | | 5191 | 1592292941 | 0 | month = 202005 | 7836 | 2665 | +
分桶表 表或分区可以进一步分为桶（bucket），将数据根据列进行细粒度的划分，桶在 HDFS 中表现为文件 。
分桶的好处：
获得更高效的查询效率。分桶为数据提供额外的结构，两个在相同列上划分了桶的表，可以使用 Map 端 join 高效地实现连接操作。详见 *分桶表的连接 *
使取样（sample）更高效。详见 *分桶抽样 *
开启Hive的强制分桶（推荐）
1 set hive.enforce.bucketing =true ;
设置Reduce的个数 -1，Hive 会自动计算出应该启动多少个 Reduce，也可以在本次会话中根据桶数指定 Reduce 个数：
1 set mapreduce.job.reduces =2;
创建分桶表 （CLUSTERED BY）sid 字段进行分桶的表，桶的个数为 3：
1 2 3 4 create table result4(sid string, cid string, score double ) clustered by (sid) into 3 buckets row format delimited fields terminated by '\t' ;
加载数据 load data 插入数据并不会进行 MapReduce，只生成了一个数据文件，无法得到分桶表。因此分桶表只能通过 insert 来插入数据：
1 2 3 4 5 6 7 8 9 10 11 12 create table result4_common (sid string, cid string,score double ) row format delimited fields terminated by '\t' ; load data local inpath '/data/result.csv' into table result4_common; insert into table result4 select sid,cid,score from result4_common cluster by (sid);
查看分桶表 result4 的目录结构，可以看到表数据以三个文件的形式进行保存：
1 2 3 4 5 $ hdfs dfs -ls -R /user/hive/warehouse/myhive.db/result4/ hdfs dfs -ls -R /user/hive/warehouse/myhive.db/result4/ -rwxrwxrwt 3 root hive 160 2020-06-16 15:41 /user/hive/warehouse/myhive.db/result4/000000_0 -rwxrwxrwt 3 root hive 120 2020-06-16 15:41 /user/hive/warehouse/myhive.db/result4/000001_0 -rwxrwxrwt 3 root hive 120 2020-06-16 15:41 /user/hive/warehouse/myhive.db/result4/000002_0
查看文件内容，发现数据被分为了两份，并且 sid 相同的数据被划分进同一个桶：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 $ hdfs dfs -cat /user/hive/warehouse/myhive.db/result4/000000_0 141360130 4576 79.5 141360139 4576 68.0 141360136 8848 79.5 141360136 4576 69.0 141360139 8848 88.5 141360133 8848 87.5 141360133 4576 85.5 141360130 8848 89.5 $ hdfs dfs -cat /user/hive/warehouse/myhive.db/result4/000001_0 141360131 4576 59.0 141360134 4576 94.5 141360131 8848 99.0 141360134 8848 84.5 141360137 4576 89.0 141360137 8848 69.0 $ hdfs dfs -cat /user/hive/warehouse/myhive.db/result4/000002_0 141360135 4576 72.5 141360135 8848 72.5 141360138 8848 59.5 141360138 4576 69.5 141360132 8848 86.5 141360132 4576 86.0
数据划分的依据，就是根据分桶时指定的字段 sid 进行 Hash 散列，对桶的个数进行取模。
查看表 查看与表相关的信息，包括表结构、表分区、建表语句等。
查看库中的表 1 2 3 4 5 6 7 8 9 hive> show tables in myhive; student result result2 hive> show tables like 'stu*' ; student
查看建表语句 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 hive> show create table sannaha.user_bymonth; CREATE TABLE `sannaha.user_bymonth`( `id` int , `name` string PARTITIONED BY ( `month ` string) ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat' LOCATION 'hdfs://Cluster1/user/hive/warehouse/sannaha.db/user_bymonth' TBLPROPERTIES ( 'parquet.compression' = 'SNAPPY' , 'spark.sql.create.version' = '2.2 or prior' , 'spark.sql.sources.schema.numPartCols' = '1' , 'spark.sql.sources.schema.numParts' = '1' , 'spark.sql.sources.schema.part.0' = '{...}' , 'spark.sql.sources.schema.partCol.0' = 'month' , 'transient_lastDdlTime' = '1601488677' )
查看表的描述信息 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 hive> desc student; + | col_name | data_type | comment | + | id | string | | | name | string | | | score | double | | + hive> desc result2; + | col_name | data_type | comment | + | sid | string | | | cid | string | | | score | double | | | year | string | | | month | string | | | day | string | | | | NULL | NULL | | # Partition Information | NULL | NULL | | # col_name | data_type | comment | | | NULL | NULL | | year | string | | | month | string | | | day | string | | + hive> desc formatted stu; + | col_name | data_type | comment | + | # col_name | data_type | comment | | | NULL | NULL | | id | string | | | name | string | | | score | double | | | | NULL | NULL | | # Detailed Table Information | NULL | NULL | | Database: | myhive | NULL | | OwnerType: | USER | NULL | | Owner: | root | NULL | | CreateTime: | Tue Jun 16 15 :13 :08 CST 2020 | NULL | | LastAccessTime: | UNKNOWN | NULL | | Retention: | 0 | NULL | | Location: | hdfs:/ / devcdh1.cdh.com:8020 / hivetable/ stu | NULL | | Table Type: | EXTERNAL_TABLE | NULL | | Table Parameters: | NULL | NULL | | | EXTERNAL | TRUE | | | numFiles | 1 | | | totalSize | 200 | | | transient_lastDdlTime | 1592291588 | | | NULL | NULL | | # Storage Information | NULL | NULL | | SerDe Library: | org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe | NULL | | InputFormat: | org.apache.hadoop.mapred.TextInputFormat | NULL | | OutputFormat: | org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat | NULL | | Compressed: | No | NULL | | Num Buckets: | -1 | NULL | | Bucket Columns: | [] | NULL | | Sort Columns: | [] | NULL | | Storage Desc Params: | NULL | NULL | | | field.delim | \t | | | serialization.format | \t | +
查看表的分区信息 1 2 3 4 5 6 hive> show partitions dailylog; partition type= study type= sport type= relax
修改表 修改表名 1 2 3 4 alter table student rename to tb_student;alter table db1.tb1 rename to db2.tb1;
修改表属性 1 2 3 4 5 6 7 8 9 10 11 12 13 14 alter table tb set tblproperties('comment' = '表说明' ); alter table tb_name set tblproperties('skip.header.line.count' = '1' );alter table tb_student set TBLPROPERTIES('EXTERNAL' = 'true' );alter table tb_student set TBLPROPERTIES('EXTERNAL' = 'false' );alter table tb_student set fileformat parquet;
修改字段 1 2 3 4 5 6 7 8 9 alter table tb_student add columns (mycol1 string, mycol2 string);alter table tb_student change column mycol2 mycol_changed int ;alter table myuser change column account account map< string,string> ;alter table tb change column field1 field1 string comment '字段说明' ;
修改分区 1 2 3 4 5 6 7 8 9 10 11 alter table result add partition (month = '202101' );alter table result2 add partition (year = '2021' ,month = '01' ,day = '11' );alter table result add partition (month = '202105' ) partition (month = '202201' );alter table result drop partition (month = '202201' );
删除表 删除表数据，保留表的定义：
对于内部表，可以使用 truncate；
对于外部表，可以利用 Hive CLI 中能够使用 hadoop / hdfs 命令的特性，使用 dfs -rmr 命令删除外部表数据所在的目录。
1 2 hive> truncate table student; hive> dfs - rmr / data/ student2/
删除表的定义：
数据类型 Hive 支持的数据类型如下：
分类 类型 描述 示例 数值类型 TINYINT 1字节有符号整数，-128~127 1 SMALLINT 2字节有符号整数，-32768~32767 2 INT/INTEGER 4字节有符号整数 3 BIGINT 8字节有符号整数 4 FLOAT 4字节单精度浮点数 5.01 DOUBLE 8字节双精度浮点数 6.23 DECIMAL decimal(int,int)。高精度有符号小数，括号中的两个int分别表示数字位数的上限和小数位数，默认为decimal(10,0) 7.456789 字符串类型 STRING 无上限可变长度字符串 "abc" VARCHAR 可变长度字符串 "def" CHAR char(length)。 固定长度字符串，需要指定长度 "ghi" 日期/时间类型 TIMESTAMP 精度到纳秒的时间戳 1577808000, 2020-01-02 03:04:05.123456789 DATE 日期，以 YYYY-MM-DD 的形式描述特定的年月日 '2013-01-01' INTERVAL 时间频率间隔 INTERVAL '1' DAY 其他类型 BOOLEAN true/false true, false BINARY 字节数组 可以包含任意字节，并且Hive不会将这些字节解析为数字或字符串。 复杂数据类型 ARRAY ARRAY<data_type>。有序的同类型的集合 array(1,2) MAP MAP<primitive_type, data_type>。一组无序的键-值对。键必须是原始类型，值可以是任意类型。同一个映射的键的类型必须相同，值的类型也必须相同 map(‘a’,1,‘b’,2) STRUCT STRUCT<col_name : data_type [COMMENT col_comment], ...>。一组字段集合，字段的类型可以不同 struct(‘a’,1,1.0), named_stract(‘col1’,‘1’,‘col2’,1,‘clo3’,1.0) UNION UNIONTYPE<data_type, data_type, ...>。值的数据类型可以使多个被定义的数据类型中的任意一个，这个值通过一个整数（零索引）来标记其为联合类型中的哪个数据类型 created_union(1,‘a’,63)
说明：
Hive 中的基本数据类型是对 Java 中的接口的实现。行为细节与 Java 中对应的类型完全一致；
Hive 的 STRING 类型相当于 MySQL 数据库的 varchar 类型，是一个可变的字符串，不过它不能声明最多存储多少个字符，理论上最大容量为 2 GB。
Hive 的 ARRAY 和 MAP 与 Java 中的 Array 和 Map 类似，而 STRUCT 与 C 语言中的 Struct 类似；
Hive 的 TIMESTAMP 的值可以是整数，表示距离 Unix 新纪元时间（1970 年 1 月 1 日 0 时）的秒数；也可以是浮点数，表示精确到纳秒；还可以是字符串，即时间字符串格式 YYYY-MM-DD hh:mm:ss.fffffffff；
Hive 的 BINARY 类型和许多关系型数据库中的 varbinary 类似，但和 blob 类型不同。BINARY 类型的列是存储在记录中的，而 blob 则是将定位器存储在行。
Hive 对复杂数据类型的支持仍不完整。在JOIN、WHERE 和GROUP BY子句中引用复杂数据类型字段的查询将失败，并且 Hive 没有定义语法来提取复杂数据类型的标记或值字段。
如有复杂数据类型的需求可以将其转为 json 格式字符串进行存储，Hive 提供了 get_json_object 和 json_tuple 函数用于解析 json 字符串。
数据类型转换 Hive 的基本数据类型是可以进行隐式转换的，类似于 Java 的类型转换，例如：某表达式使用 INT 类型，TINYINT 会自动转换为 INT 类型；但是 Hive 不能进行反向转化，例如：某表达式使用 TINYINT 类型，INT 不会自动转换为 TINYINT 类型，它会返回错误，除非使用 CAST。
隐式转换规则
任何整数类型都可以隐式地转换为一个范围更广的类型，如 TINYINT 可以转换成 INT，INT 可以转换成 BIGINT。
所有整数类型、浮点类型和字符串类型都可以隐式地转换成 DOUBLE。
TINYINT、SMALLINT、INT 都可以转换成 FLOAT。
BOOLEAN 类型不可以转换成任何其他类型。
强制转换
可以使用 CAST 显式地进行数据类型转换。如 CAST('1' AS INT) 可以把字符串 1 转换成整数 1；如果强制类型转换失败，如 CAST('X' AS INT)，表达式返回 NULL。
复杂数据类型用例 需求 某表需要存储的数据结构如下：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 { "name" : "sannaha" , "friends" : [ "Asuka" , "Ayanami" ], "account" : { "steam" : 76561198213678672 , "epic" : 65782119236786761 }, "address" : { "city" : "Beijing" , "country" : "China" } } {"duration" : 288 , "dest_phone_number" : "17701282133" , "cellid" : 111 , "flag" : "called" , "lac" : 6148 }
由于 Hive 需要按行读取数据，基于上述结构，对数据进行处理，得到便于 Hive 处理的结构，并复制得到两条数据：
1 2 sannaha,Asuka_Ayanami,steam:76561198213678672_epic:65782119236786761,Beijing_China sannaha2,Asuka_Ayanami,steam:76561198213678672_epic:65782119236786761,Beijing_China
其中保存 friends 使用 Array，account 使用 Map，address 使用 Struct。这里使用 _ 作为分隔符将 Map、Struct 和 Array 中的多个元素隔开。
1 2 sannaha,Asuka_Ayanami,steam:76561198213678672_epic:65782119236786761,Beijing_China sannaha2,Asuka_Ayanami,steam:76561198213678672_epic:65782119236786761,Beijing_China
创建用户表 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 create table myuser2( name string, friends array < string> , account map< string,int > , address struct< street:string, city:string> ) row format delimited fields terminated by ',' collection items terminated by '_' map keys terminated by ':' lines terminated by '\n' ; load data local inpath '/data/myuser.txt' into table myuser;
查询数据 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 select * from myuser;myuser.name myuser.friends myuser.account myuser.address sannaha ["Asuka","Ayanami"] {"steam":null ,"epic":null } {"street":"Beijing","city":"China"} sannaha2 ["Asuka","Ayanami"] {"steam":null ,"epic":null } {"street":"Beijing","city":"China"} alter table myuser change column account account map< string,string> ;select * from myuser;myuser.name myuser.friends myuser.account myuser.address sannaha ["Asuka","Ayanami"] {"steam":"76561198213678672","epic":"65782119236786761"} {"street":"Beijing","city":"China"} sannaha2 ["Asuka","Ayanami"] {"steam":"76561198213678672","epic":"65782119236786761"} {"street":"Beijing","city":"China"} select friends[0 ] as friends from myuser;friends Asuka Asuka select account['epic' ] as account from myuser;account 65782119236786761 65782119236786761 select address.street from myuser;street Beijing Beijing
注：CDH 6.1.1的 Hive 的 hive.fetch.task.conversion 默认为 minimal，会对查询进行优化，某些情况的查询可以不触发 MapReduce 计算。对于 Map、Struct 和 Array 这种复杂数据类型内数据的查询， minimal 级别会触发 MapReduce 计算，如果更改级别为 more，则不会触发 MapReduce 计算。
数据操作语言DML 用来对数据库中表的记录进行更新。
插入数据 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 create table result5 like result ;insert into table result5 partition (month = '202001' ) values ('141360130' ,'1551' ,100 ); insert into table result5 partition (month = '202005' ) select sid, cid, score from result ; insert overwrite table tb_new select * from tb_ori where field_name != 'field_name' ; insert overwrite table result5 partition (month = '202005' ) select sid, cid, score from result ; insert overwrite local directory '/loca/path/' row format delimited fields terminated by '\t' select * from stu; insert overwrite directory '/hdfs/path/' row format delimited fields terminated by '\t' select * from stu;
Hive 数据仓库主要是用来将 HDFS 中文件的数据映射成表数据，尽量避免使用 insert 逐条插入数据，因为：
每次插入会执行一次 MR 任务，插入速度慢。
每次插入会生成一个小文件来记录本次插入的数据，大量小文件的元数据信息会占用 NameNode 的内存空间。
验证 ：load 了 stu.csv 文件进 stu 表后，再 insert 两条数据，产生两个小文件。
加载数据 将数据文件加载到表中，语法格式：
1 LOAD DATA [LOCAL ] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1= val1,partcol2= val2 ...)]
说明：
filepath 可以是：
相对路径，如 project/file1。；
绝对路径，如 /data/project/file1。；
完整的 URL，如 hdfs://devcdh1.cdh.com:9000/data/project/file1。
目标可以是一个表或是一个分区。如果目标表是分区表，必须指定是要加载到哪个分区；
filepath 可以是一个文件，也可以是一个目录（会将目录下的所有文件都加载）；如果命令中带 LOCAL，表示：
LOAD 命令从本地文件系统中加载数据，可以是相对路径，也可以是绝对路径。对于本地文件系统，也可以使用完整的 URL，如 file:///data/project/file1；LOAD 命令会根据指定的本地文件系统中的 filepath 复制文件到目标文件系统，然后再移到对应的表。
如果命令中没有 LOCAL，表示从 HDFS 加载文件，会移动文件到指定目录（而非复制） 。filepath 可以使用完整的 URL 方式，或者使用 fs.default.name 定义的值；
命令带 OVERWRITE 时加载数据之前会先清空目标表或分区中的内容，否则就是追加的方式；
加载数据时不会对文件格式进行转换 ，表的存储格式需要与文件格式一致，否则会无法使用这些数据，遇到这种情况可以修改表的存储格式。
加载数据到表 1 2 3 4 5 6 7 8 9 10 11 12 load data local inpath '/data/result.csv' into table result5 partition (month = '202001' ); load data local inpath '/data/result.csv' OVERWRITE into table result5 partition (month = '202001' ); alter table tb_name set tblproperties('skip.header.line.count' = '1' );
加载数据到分区表 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 load data local inpath '/data/result.csv' into table result partition (month = '202001' ); load data local inpath '/data/result.csv' into table result partition (month = '202005' ); load data local inpath '/data/result.csv' into table result2 partition (year = '2020' ,month = '01' ,day = '11' ); load data local inpath '/data/result.csv' into table result2 partition (year = '2020' ,month = '05' ,day = '01' );
多表插入 多表插入，只需要扫描一遍源表就可以生成多个输出，将一张表的数据插入到多张表：
1 2 3 4 5 from source_table insert < into / overwrite> table table1 select field1,field2... [where ...] insert < into / overwrite> table table2 select field2,field3... [where ...]
这样可以减少表的扫描，减少 STAGE 数量，达到优化的目的。可以与动态分区插入共同使用。
动态分区插入 在插入数据到分区表时，只需在分区子句中指定分区列名列表，而列值是可选的。如果给出分区列值，我们将其称为静态分区；如果不指定分区列值，而是根据源表的列值自动生成 ，我们称为动态分区 。
每个动态分区列都有来自 SELECT 语句的相应的投影列，分区列表中动态分区列应位于静态分区列之后，并对应 PARTITION 子句中的顺序。
在Hive3.0.0(hive-19083)中，不需要为动态分区指定分区列。如果未指定分区规范，Hive将自动生成该分区规范。在Hive 0.9.0之前默认禁用动态分区插入，在Hive 0.9.0及更高版本中默认启用动态分区插入。下面是支持动态分区插入的相关配置属性：
配置属性
默认值
注释
hive.exec.dynamic.partition
true
需要设置为true来启用动态分区插入
hive.exec.dynamic.partition.mode
strict
在strict模式下，用户必须指定至少一个静态分区的情况下，防止不小心将覆盖所有分区，在nonstrict模式下，允许所有分区是动态的。
hive.exec.max.dynamic.partitions.pernode
100
允许在每个MAPPER/REDUCER节点创建动态分区的最大数目
hive.exec.max.dynamic.partitions
1000
允许创建动态分区的最大数目
hive.exec.max.created.files
100000
在MapReduce作业中所有MAPPER/REDUCER创建HDFS文件的最大数量
hive.error.on.empty.partition
false
当动态分区插入产生空结果时，是否抛出一个异常
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 create table result_source(sid string, cid string, score double ) row format delimited fields terminated by '\t' location '/data/result' ; > select * from result_source;result_source.sid result_source.cid result_source.score 141360130 8848 89.5 141360131 8848 99.0 141360132 8848 86.5 141360133 8848 87.5 141360134 8848 84.5 141360135 8848 72.5 141360136 8848 79.5 141360137 8848 69.0 141360138 8848 59.5 141360139 8848 88.5 141360130 4576 79.5 141360131 4576 59.0 141360132 4576 86.0 141360133 4576 85.5 141360134 4576 94.5 141360135 4576 72.5 141360136 4576 69.0 141360137 4576 89.0 141360138 4576 69.5 141360139 4576 68.0 create table result_partition(sid string,score double ) partitioned by (month string, cid string) row format delimited fields terminated by '\t' ; insert overwrite table result_partition partition (month = '202005' ,cid) select sid, score, cid where score > 85 ; > select * from result_partition;result_partition.sid result_partition.score result_partition.month result_partition.cid 141360132 86.0 202005 4576 141360133 85.5 202005 4576 141360134 94.5 202005 4576 141360137 89.0 202005 4576 141360130 89.5 202005 8848 141360131 99.0 202005 8848 141360132 86.5 202005 8848 141360133 87.5 202005 8848 141360139 88.5 202005 8848
注意：在进行动态分区插入时，分区列需要放在 select 字段列表的末尾 ，如果将 score 放在了最后，那么会错误地根据 score 的值创建 cid 分区。验证如下：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 truncate table result_partition;from result_source insert overwrite table result_partition partition (month = '202005' ,cid) select sid, cid, score where score > 85 ; > select * from result_partition;result_partition.sid result_partition.score result_partition.month result_partition.cid 141360133 4576.0 202005 85.5 141360132 4576.0 202005 86.0 141360132 8848.0 202005 86.5 141360133 8848.0 202005 87.5 141360139 8848.0 202005 88.5 141360137 4576.0 202005 89.0 141360130 8848.0 202005 89.5 141360134 4576.0 202005 94.5 141360131 8848.0 202005 99.0
扩展阅读： Hive 官方手册翻译 – Hive DML(数据操纵语言)
导入导出表数据 使用 export 导出表数据到 HDFS：
1 2 export table result to '/hdfsdata/result' ;
查看导出文件：
1 2 3 4 5 6 7 $ hdfs dfs -ls -R /hdfsdata/result/ -rwxr-xr-x 3 root supergroup 6312 2020-06-16 16:24 /hdfsdata/result/_metadata drwxr-xr-x - root supergroup 0 2020-06-16 16:24 /hdfsdata/result/month=202001 -rwxr-xr-x 3 root supergroup 402 2020-06-16 16:24 /hdfsdata/result/month=202001/result.csv drwxr-xr-x - root supergroup 0 2020-06-16 16:24 /hdfsdata/result/month=202005 -rwxr-xr-x 3 root supergroup 402 2020-06-16 16:24 /hdfsdata/result/month=202005/result.csv
使用 import 将 HDFS 文件导入为表数据：
1 2 3 4 create table result2 like result ;import table result2 from '/hdfsdata/result' ;
保存查询结果 保存查询结果到本地 方法一：调用 hive 的标准输出，将查询结果写到指定的文件中：
1 2 3 4 $ hive -e "select * from dbname.tablename" > /local /path/filename.txt $ hive -f select.sql > /local /path/filename.txt
方法二：使用 INSERT OVERWRITE LOCAL DIRECTORY 保存结果到本地：
1 2 3 4 5 6 7 8 9 10 11 insert overwrite local directory '/data/exporthive/myuser' row format delimited fields terminated by '\t' collection items terminated by '#' select * from myuser; $ cat / data/ exporthive/ myuser/ 000000 _0 sannaha Asuka#Ayanami steam76561198213678672#epic65782119236786761 Beijing#China sannaha2 Asuka#Ayanami steam76561198213678672#epic65782119236786761 Beijing#China
保存查询结果到HDFS 使用 INSERT OVERWRITE DIRECTORY 保存结果到 HDFS：
1 2 3 4 5 6 insert overwrite directory '/hdfsdata/exporthive' row format delimited fields terminated by '\t' collection items terminated by '#' select * from myuser;
保存查询结果到Hive表 如果表已建好，使用 INSERT OVERWRITE TABLE 将结果写入 Hive 表：
1 2 3 4 5 6 7 8 9 10 11 12 insert overwrite table result5 partition (month = '202005' ) select sid, cid, score from result ; insert into table result5 partition (month = '202005' ) select sid, cid, score from result ; create table student3 as select * from student2;create table student4 like student2;
清空表数据 只能用于清空内部表，清空外部表会报错：
1 truncate table student2;
数据查询语言DQL 用来查询数据库中表的记录。
查询数据 语法结构:
1 2 3 4 5 6 7 8 9 10 SELECT [ALL | DISTINCT ] select_expr, select_expr, ... FROM table_reference [WHERE where_condition] [GROUP BY col_list] [ORDER BY col_list] [CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY col_list] ] [LIMIT [offset ,] rows ]
注意：
GROUP BY 分组统计，通常与 count(*) 等聚合函数搭配使用，按照一个或者多个字段对结果进行分组，然后对每个组执行聚合操作。SELECT 语句只能查询 GROUP BY 子句中包含的字段（或包含其他字段的聚合函数）。ORDER BY 会做全局排序，因此只有一个 Reducer。当数据的规模较大时，需要较长的计算时间。SORT BY 只保证每个 Reducer 内有序。如果用 SORT BY 进行排序，并且设置mapred.reduce.tasks>1，则 SORT BY 不保证全局有序 。DISTRIBUTE BY 根据指定的字段将数据分发到不同的 Reducer，指定字段的值相同的行会被分发到同一个 Reducer。CLUSTER BY 用作 DISTRIBUTE BY+SORT BY 的替代写法。除了具有 DISTRIBUTE BY 的分发功能外，还会对该字段进行排序。
全表查询 Hive 对查询进行过优化，像这种不涉及计算的查询可以不触发 MapReduce（hive.fetch.task.conversion 设置为 minimal 也不触发）：
1 2 3 4 5 6 7 8 9 10 11 12 13 > select * from stu;OK stu.id stu.name stu.score 141360131 Jerry 90.5 141360132 Asuka 99.0 141360133 Wade 100.0 141360134 Tabitha 100.0 141360135 Madeline 70.5 141360136 Albert 91.0 141360137 Vinny 67.5 141360138 Lucinda 79.0 141360139 Gillian 78.0 Time taken: 0.179 seconds, Fetched: 9 row (s)
查询指定列 可以用 AS 指定列别名：
1 select id as stuid ,name as stuname from stu;
不触发 MR
使用查询条件筛选 1 select * from stu where id < 3 ;
minimal 触发 MR，more 不触发
限制返回行数 1 select * from stu limit 2 ;
不触发 MR
模糊查询
LIKE 运算选择类似的值。% 代表零个或多个字符，_ 代表一个字符。RLIKE 是 Hive 提供的对 LIKE 的扩展，通过 Java 的正则表达式来指定匹配条件。
1 2 3 4 5 6 7 8 select * from result where score like '8%' ;select * from result where score like '_9%' ;select * from result where cast (score as string) rlike '[5]' ;
minimal 触发 MR，more 不触发
关系运算符 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 > select * from stu where id = '141360131' ;stu.id stu.name stu.score 141360131 Jerry 90.5 > select * from stu where id between '141360131' and '141360133' ;stu.id stu.name stu.score 141360131 Jerry 90.5 141360132 Asuka 99.0 141360133 Wade 100.0 > select * from stu where name is null ;> select * from stu where id in ('141360131' , '141360133' );stu.id stu.name stu.score 141360131 Jerry 90.5 141360133 Wade 100.0
minimal 触发 MR，more 不触发
下表详细介绍了关系运算符，这些运算符同样可以用于 JOIN ON 和 HAVING 语句中。
运算符
支持的数据类型
描述
A=B, A==B
基本数据类型
如果A等于B则返回TRUE，反之返回FALSE
A<=>B
基本数据类型
如果A和B都为NULL，则返回TRUE；如果任意一个为NULL则结果为NULL；其他情况的和等号（=）运算符的结果一致
A<>B, A!=B
基本数据类型
A或者B为NULL则返回NULL；如果A不等于B，则返回TRUE，反之返回FALSE
A<B
基本数据类型
A或者B为NULL，则返回NULL；如果A小于B，则返回TRUE，反之返回FALSE
A<=B
基本数据类型
A或者B为NULL，则返回NULL；如果A小于等于B，则返回TRUE，反之返回FALSE
A>B
基本数据类型
A或者B为NULL，则返回NULL；如果A大于B，则返回TRUE，反之返回FALSE
A>=B
基本数据类型
A或者B为NULL，则返回NULL；如果A大于等于B，则返回TRUE，反之返回FALSE
A [NOT] BETWEEN B AND C
基本数据类型
如果A，B或者C任一为NULL，则结果为NULL。如果A的值大于等于B而且小于或等于C，则结果为TRUE，反之为FALSE。如果使用NOT关键字则可达到相反的效果。
A IS NULL
所有数据类型
如果A等于NULL，则返回TRUE，反之返回FALSE
A IS NOT NULL
所有数据类型
如果A不等于NULL，则返回TRUE，反之返回FALSE
IN(数值1, 数值2)
所有数据类型
使用 IN运算显示列表中的值
A [NOT] LIKE B
STRING 类型
B是一个SQL下的简单正则表达式，如果A与其匹配的话，则返回TRUE；反之返回FALSE。B的表达式说明如下：‘x%’表示A必须以字母‘x’开头，‘%x’表示A必须以字母’x’结尾，而‘%x%’表示A包含有字母’x’,可以位于开头，结尾或者字符串中间。如果使用NOT关键字则可达到相反的效果。
A RLIKE B, A REGEXP B
STRING 类型
B是一个正则表达式，如果A与其匹配，则返回TRUE；反之返回FALSE。匹配使用的是JDK中的正则表达式接口实现的，因为正则也依据其中的规则。例如，正则表达式必须和整个字符串A相匹配，而不是只需与其字符串匹配。
查询分区表数据 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 select * from result ;result.sid result.cid result.score result.month 141360130 8848 89.5 202001 141360131 8848 99.0 202001 ... select result.sid,result.cid,result.score,result.month as fenqu from result where month = '202005' union all select result2.sid,result2.cid,result2.score,result2.day as fenqu from result2 where year = '2020' and month = '05' and day = '01' ; _u1.sid _u1.cid _u1.score _u1.fenqu 141360130 8848 89.5 202005 141360131 8848 99.0 202005 ... 141360130 8848 89.5 01 141360131 8848 99.0 01 ...
子查询部分 在一个复杂 SQL 中，对于会被多次使用的中间结果我们可以创建临时表，或是使用子查询部分。Hive 中可以使用 WITH AS 建立子查询部分，会将这个片段产生的结果集保存在内存中，供整个 SQL 使用。既可以优化执行速度，又能提高 SQL 可读性：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 WITH sub_query_tmp1 AS ( SELECT field1 ,field2 ,... FROM source_table ), sub_query_tmp2 AS ( SELECT field1 ,field2 ,... FROM sub_query_tmp1 ) INSERT OVERWRITE TABLE target_tableSELECT * FROM sub_query_tmp2;
注意：
WITH 只需写一次，多个子查询之间用逗号分隔；子查询中不能使用 WITH AS 嵌套；
下面的子查询可以使用上面的子查询，比如 sub_query_tmp2 中就使用了 sub_query_tmp1；
子查询部分后面不能加分号，需要跟上主体的查询语句或者插入语句。
UNION UNION 用于将多个 SELECT 语句的结果合并为一个结果集，每一个 SELECT 语句必须具有相同数量的字段，且字段类型必须一致。
UNION 默认会从结果集中删除重复的行，而 UNION ALL 不会删除重复的行，结果集中包含每个 SELECT 语句匹配到的行。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 select 'sannaha' as name,25 as age union select 'sannaha' ,24 ; sannaha 25 sannaha 24 select 'sannaha' as name,25 as age union select 'sannaha' ,25 ; sannaha 25 select 'sannaha' as name,25 as age union all select 'sannaha' ,25 ; sannaha 25 sannaha 25
聚合函数 聚合函数都会触发 MR：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 select count (1 ) from stu;select max (id) from stu;select min (id) from stu;select sum (s_score) from stu;select avg (id) from stu;
分组 GROUP BY 语句通常会和聚合函数一起使用，按照一个或者多个字段对结果进行分组，然后对每个组执行聚合操作。SELECT 语句只能查询 GROUP BY 子句中包含的字段和包含其他字段的聚合函数。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 > select sid, avg (score) from result group by sid;sid _c1 141360130 84.5 141360131 79.0 141360132 86.25 141360133 86.5 141360134 89.5 141360135 72.5 141360136 74.25 141360137 79.0 141360138 64.5 141360139 78.25 > select sid , max (score) from result group by sid;sid _c1 141360130 89.5 141360131 99.0 141360132 86.5 141360133 87.5 141360134 94.5 141360135 72.5 141360136 79.5 141360137 89.0 141360138 69.5 141360139 88.5
HAVING 语句用于根据条件筛选结果。HAVING 与 WHERE 的不同点：
WHERE 的作用顺序早于 HAVING。WHERE 针对表中的字段发挥作用，剔除数据；HAVING 针对查询结果中的字段发挥作用，筛选数据。WHERE 后面不能使用分组函数，HAVING 后面可以使用分组函数。HAVING 只能 用于 GROUP BY 分组统计语句。
1 2 3 4 5 6 > select sid ,avg (score) avgscore from result group by sid having avgscore > 85 ;sid avgscore 141360132 86.25 141360133 86.5 141360134 89.5
排序 ORDER BY 全局排序，只有一个 Reducer 。ORDER BY 子句在SELECT语句的末尾，默认为ASC升序，DESC为降序。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 > SELECT s.id,s.name,r.cid,r.score FROM stu s LEFT JOIN result r ON s.id = r.sid WHERE r.month = '202001' ORDER BY r.score DESC ; 141360131 Jerry 8848 99.0 141360134 Tabitha 4576 94.5 141360137 Vinny 4576 89.0 141360139 Gillian 8848 88.5 141360133 Wade 8848 87.5 141360132 Asuka 8848 86.5 141360132 Asuka 4576 86.0 141360133 Wade 4576 85.5 141360134 Tabitha 8848 84.5 141360136 Albert 8848 79.5 141360135 Madeline 8848 72.5 141360135 Madeline 4576 72.5 141360138 Lucinda 4576 69.5 141360136 Albert 4576 69.0 141360137 Vinny 8848 69.0 141360139 Gillian 4576 68.0 141360138 Lucinda 8848 59.5 141360131 Jerry 4576 59.0 > SELECT sid ,avg (score) avg FROM result WHERE month = '202001' GROUP BY sid ORDER BY avg; sid avg 141360138 64.5 141360135 72.5 141360136 74.25 141360139 78.25 141360137 79.0 141360131 79.0 141360130 84.5 141360132 86.25 141360133 86.5 141360134 89.5 SELECT sid, avg (score) avg FROM result GROUP BY sid ORDER BY avg DESC ,sid DESC ; sid avg 141360134 89.5 141360133 86.5 141360132 86.25 141360130 84.5 141360137 79.0 141360131 79.0 141360139 78.25 141360136 74.25 141360135 72.5 141360138 64.5
SORT BY 每个 Reducer 内部进行排序，对全局结果集来说不保证有序 。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 set mapreduce.job.reduces;set mapreduce.job.reduces= 3 ;select * from result sort by score desc ;insert overwrite local directory '/data/sort/ResultSort' row format delimited fields terminated by '\t' select sid, cid, score from result where month = '202001' sort by sid desc ; $ cat / data/ sort/ ResultSort/ 000000 _0 141360139 8848 88.5 141360138 4576 69.5 141360138 8848 59.5 141360137 4576 89.0 141360137 8848 69.0 141360136 8848 79.5 141360135 8848 72.5 141360134 8848 84.5 141360134 4576 94.5 $ cat / data/ sort/ ResultSort/ 000001 _0 141360139 4576 68.0 141360136 4576 69.0 141360133 4576 85.5 141360133 8848 87.5 141360132 8848 86.5 141360131 4576 59.0 141360131 8848 99.0 141360130 4576 79.5 $ cat / data/ sort/ ResultSort/ 000002 _0 141360135 4576 72.5 141360132 4576 86.0 141360130 8848 89.5
DISTRIBUTE BY 类似 MapReduce 中 Partition，根据指定的字段将数据分发到不同的 Reducer，指定字段的值相同的行会被分发到同一个 Reducer。DISTRIBUTE BY 不会进行排序 ，但可以结合SORT BY语句实现 Reducer 内部排序。Hive 要求 DISTRIBUTE BY 语句要写在 SORT BY 语句之前，并且分发字段和排序字段可以不同 。
注意：DISTRIBUTE BY 同样需要设置多个 Reduce 进行处理，否则无法看到效果。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 insert overwrite local directory '/data/sort/ResultSort2' row format delimited fields terminated by '\t' select sid, cid, score from result where month = '202001' distribute by sid sort by score desc ; $ cat / data/ sort/ ResultSort2/ 000000 _0 141360130 8848 89.5 141360139 8848 88.5 141360133 8848 87.5 141360133 4576 85.5 141360136 8848 79.5 141360130 4576 79.5 141360136 4576 69.0 141360139 4576 68.0 $ cat / data/ sort/ ResultSort2/ 000001 _0 141360131 8848 99.0 141360134 4576 94.5 141360137 4576 89.0 141360134 8848 84.5 141360137 8848 69.0 141360131 4576 59.0 $ cat / data/ sort/ ResultSort2/ 000002 _0 141360132 8848 86.5 141360132 4576 86.0 141360135 8848 72.5 141360135 4576 72.5 141360138 4576 69.5 141360138 8848 59.5
CLUSTER BY 当DISTRIBUTE BY 和 SORT BY字段相同时，可以使用CLUSTER BY替代。排序结果为正序。
1 2 3 select * from result cluster by sid;select * from result distribute by sid sort by sid;
连接 等值JOIN Hive 支持通常的 SQL JOIN 语句，但是只支持等值连接，不支持非等值连接 。
1 2 3 4 SELECT s.s_id,s.s_score,stu.s_name,stu.s_birth FROM score s JOIN student stu ON s.s_id = stu.s_id;
内连接 进行内连接（INNER JOIN）的两个表，表中符合连接条件的数据才会被保留下来：
1 2 3 select * from teacher t inner join course c on t.t_id = c.t_id;
左、右外连接
左外连接（LEFT [OUTER] JOIN）：以左侧为基底，返回左表中所有行，如果右侧没有匹配的行，其提供的字段返回空值。
右外连接（RIGHT [OUTER] JOIN）：以右侧为基底，返回右表中所有行，如果左侧没有匹配的行，其提供的字段返回空值。
1 2 3 4 5 6 7 8 select * from teacher t left join course c on t.t_id = c.t_id; select * from teacher t right join course c on t.t_id = c.t_id;
满外连接 满外连接（FULL [OUTER] JOIN）返回所有表中符合 WHERE 子句的所有记录。如果任一表的指定字段没有符合条件的值的话，就使用NULL值替代。
1 SELECT * FROM teacher t FULL JOIN course c ON t.t_id = c.t_id ;
多表连接 多表连接查询。连接 n 个表，至少需要 n-1 个连接条件。
1 2 3 4 5 6 7 8 查询老师对应的课程，以及对应的分数，对应的学生 select * from teacher t left join course c on t.t_id = c.t_id left join score s on s.c_id = c.c_id left join student stu on s.s_id = stu.s_id;
大多数情况下，Hive 会对每对 JOIN 连接对象启动一个 MapReduce 任务。本例中会首先启动一个MapReduce Job 对techer表和course表进行连接操作，然后会再启动一个 MapReduce Job 将第一个 MapReduce Job 的输出和表score进行连接操作。
分桶表的连接 分桶为数据提供额外的结构，两个在相同列上划分了桶的表，可以使用 Map 端 join 高效地实现连接操作。原因是对相同列进行的 Hash 散列再根据桶数取模，只要两个表的桶数为整数倍，那么很容易根据一条数据在左表的桶号推断出在右表的哪个桶中。因此，Mapper 只需要获取那个桶即可进行连接。
数据抽样 在大规模数据量的数据分析及建模任务中，针对全量数据进行挖掘分析往往会十分耗时和占用集群资源，因此一般情况下只需要抽取一小部分数据进行分析及建模操作。
块抽样 使用 Hive 提供的 TABLESAMPLE() 函数来进行块抽样（Block Sampling）：
TABLESAMPLE (n PERCENT)：根据原始数据大小的百分比来抽样。如果以块抽样（百分比）的方式创建抽样表，会在 HDFS 块级别执行此操作，以便采样粒度为块大小。例如，如果块大小为 256 MB，即使 n％ 的输入大小仅为 100 MB，创建的抽样表中也会获得 256 MB的数据。TABLESAMPLE (nM)：根据样本大小来抽样，样本的大小为 n MB。TABLESAMPLE (n ROWS)：根据行数来抽样。这里行数，指的是在每个 InputSplit 中取样的行数，也就是每个 Map 中都取样 n 行。
实例
查询数据：
1 2 3 4 5 6 7 8 9 10 11 > select * from stu;stu.id stu.name stu.score 1 Sannaha 100.0 2 Jerry 90.5 3 Asuka 99.0 4 Wade 100.0 5 Tabitha 100.0 6 Madeline 70.5 7 Albert 91.0 8 Vinny 67.5 9 Lucinda 79.0
块抽样：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 > select * from stu tablesample (10 percent );stu.id stu.name stu.score 1 Sannaha 100.0 > select * from stu tablesample (20 percent );stu.id stu.name stu.score 1 Sannaha 100.0 2 Jerry 90.5 > select * from stu tablesample (50 percent );stu.id stu.name stu.score 1 Sannaha 100.0 2 Jerry 90.5 3 Asuka 99.0 4 Wade 100.0 5 Tabitha 100.0 > select * from stu tablesample (1 M);stu.id stu.name stu.score 1 Sannaha 100.0 2 Jerry 90.5 3 Asuka 99.0 4 Wade 100.0 5 Tabitha 100.0 6 Madeline 70.5 7 Albert 91.0 8 Vinny 67.5 9 Lucinda 79.0 > select * from stu tablesample (1 rows );stu.id stu.name stu.score 1 Sannaha 100.0 > select * from stu tablesample (5 rows );stu.id stu.name stu.score 1 Sannaha 100.0 2 Jerry 90.5 3 Asuka 99.0 4 Wade 100.0 5 Tabitha 100.0 create table stu_sample1 as select * from stu tablesample (10 percent );> select * from stu_sample1;stu_sample1.id stu_sample1.name stu_sample1.score 1 Sannaha 100.0 2 Jerry 90.5 3 Asuka 99.0 4 Wade 100.0 5 Tabitha 100.0 6 Madeline 70.5 7 Albert 91.0 8 Vinny 67.5 9 Lucinda 79.0 create table stu_sample2 as select * from stu tablesample (1 M);create table stu_sample3 as select * from stu tablesample (5 rows );> select * from stu_sample3;stu_sample3.id stu_sample3.name stu_sample3.score 1 Sannaha 100.0 2 Jerry 90.5 3 Asuka 99.0 4 Wade 100.0 5 Tabitha 100.0
分桶抽样 通常，TABLESAMPLE 将扫描整个表并获取样本，这样并不高效。因此，可以使用 CLUSTERED BY 创建分桶表,如果 TABLESAMPLE 中指定的列与分桶表指定的列匹配，则 TABLESAMPLE 仅扫描表所需的哈希分区。
注：并非只有分桶表可以进行分桶抽样
语法：
1 TABLESAMPLE (BUCKET x OUT OF y [ON colname])
说明：
x：抽样的桶编号。桶的编号从 1 开始。y：可以理解为分桶抽样时“临时桶”的数量。如果是分桶表，建议是分桶表桶数的倍数或因数。colname：抽样的列，可以是分区列以外的任意列。
现有分桶表 source 有 32 个根据 id 字段创建的桶（CLUSTERED BY id INTO 32 BUCKETS）：
1 2 3 4 5 6 7 TABLESAMPLE (BUCKET 3 OUT OF 16 ON id)TABLESAMPLE (BUCKET 3 OUT OF 64 ON id)
实例
创建分桶表 stu2，根据 id 字段分为 3 桶，插入 stu 表中数据：
1 2 3 4 5 6 create table stu2(id string,name string,score double ) clustered by (id) into 3 buckets row format delimited fields terminated by '\t' ; insert into table stu2 select * from stu cluster by (id);
查询数据：
1 2 3 4 5 6 7 8 9 10 11 12 > select * from stu2;OK stu2.id stu2.name stu2.score 9 Lucinda 79.0 3 Asuka 99.0 6 Madeline 70.5 7 Albert 91.0 1 Sannaha 100.0 4 Wade 100.0 5 Tabitha 100.0 8 Vinny 67.5 2 Jerry 90.5
桶文件内容：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 $ hdfs dfs -cat /user/hive/warehouse/myhive.db/stu2/000000_0 9 Lucinda 79.0 3 Asuka 99.0 6 Madeline 70.5 $ hdfs dfs -cat /user/hive/warehouse/myhive.db/stu2/000001_0 7 Albert 91.0 1 Sannaha 100.0 4 Wade 100.0 $ hdfs dfs -cat /user/hive/warehouse/myhive.db/stu2/000002_0 5 Tabitha 100.0 8 Vinny 67.5 2 Jerry 90.5
分桶抽样：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 > select * from stu tablesample (bucket 1 out of 3 on id);stu.id stu.name stu.score 3 Asuka 99.0 6 Madeline 70.5 9 Lucinda 79.0 > select * from stu2 tablesample (bucket 1 out of 3 on id);stu2.id stu2.name stu2.score 9 Lucinda 79.0 3 Asuka 99.0 6 Madeline 70.5 > select * from stu2 tablesample (bucket 2 out of 3 on id);stu2.id stu2.name stu2.score 7 Albert 91.0 1 Sannaha 100.0 4 Wade 100.0 > select * from stu2 tablesample (bucket 3 out of 3 on id);stu2.id stu2.name stu2.score 5 Tabitha 100.0 8 Vinny 67.5 2 Jerry 90.5 > select * from stu2 tablesample (bucket 1 out of 6 on id);stu2.id stu2.name stu2.score 6 Madeline 70.5 > select * from stu2 tablesample (bucket 1 out of 4 on id);stu2.id stu2.name stu2.score 4 Wade 100.0 8 Vinny 67.5 > create table stu2_sample as select id,name,score from stu2 tablesample (bucket 1 out of 3 on id);> select * from stu2_sample;stu2_sample.id stu2_sample.name stu2_sample.score 9 Lucinda 79.0 3 Asuka 99.0 6 Madeline 70.5
随机抽样 常用内置函数 日期函数 获取当前日期 1 2 select CURRENT_DATE ;2021 -08 -19
格式化日期 1 2 3 4 5 6 date_format(date / timestamp / string ts, string fmt) select date_format('2020-05-01' , 'yyyy/MM/dd HH:mm:ss' );2020 / 05 / 01 00 :00 :00
获得日期部分 1 2 3 4 5 6 to_date(string timestamp ) select to_date('2020-05-01 12:34:56' );2020 -05 -01
获得年份 1 2 3 4 5 6 year (string date )select year ('2020-05-01 12:34:56' );2020
获得月份 1 2 3 4 5 6 month (string date )select month ('2020-05-01 12:34:56' );5
获得天 1 2 3 4 5 6 day (string date )select day ('2020-05-01 12:34:56' );1
获得小时 1 2 3 4 5 6 hour (string date )select hour ('2020-05-01 12:34:56' );12
获得分钟 1 2 3 4 5 6 minute (string date )select minute ('2020-05-01 12:34:56' );34
获得秒 1 2 3 4 5 6 second (string date )select second ('2020-05-01 12:34:56' );56
获得周 1 2 3 4 5 6 weekofyear(string date ) select weekofyear('2020-05-01 12:34:56' );18
日期增加天数 1 2 3 4 5 6 7 8 date_add(string startdate, int days) select date_add('2020-05-01' , 5 );2020 -05 -06 select date_add('2020-05-01' , -5 );2020 -04 -26
日期减少天数 1 2 3 4 5 6 7 8 date_sub(string startdate, int days) select date_sub('2020-05-01' , 5 );2020 -04 -26 select date_sub('2020-05-01 12:00:00' , 5 );2020 -04 -26
日期增加月份 1 2 3 4 5 6 add_months(string startdate,int months) select add_months('2020-05-01' ,-1 );2020 -04 -01
日期差 1 2 3 4 5 6 datediff(string enddate, string startdate) select datediff('2020-05-01' , '2020-01-01' );121
注：日期函数要求日期分隔符为 -，如果数据格式不符合要求，可以使用时间戳函数两次转换、字符的截取和拼接、正则替换等方式进行转换，或是在 UDF 中实现。
获取当前时间戳 1 2 3 4 5 6 unix_timestamp() select unix_timestamp();1626318568
日期转时间戳 1 2 3 4 5 6 7 8 9 10 11 unix_timestamp(string date [, string pattern ]) select UNIX_TIMESTAMP('2021-05-01 23:33:33' );1619912013 select UNIX_TIMESTAMP('2021-05-01' );NULL select UNIX_TIMESTAMP('20210501' , 'yyyyMMdd' );1619827200
时间戳转日期 1 2 3 4 5 6 7 8 9 from_unixtime(bigint unixtime[, string format]) select FROM_UNIXTIME(1619912013 );2021 -05 -01 23 :33 :33 select FROM_UNIXTIME(1619912013 , 'yyyy-MM-dd' );2021 -05 -01
组合使用日期函数 转换日期格式 1 2 select FROM_UNIXTIME(UNIX_TIMESTAMP('20210501' , 'yyyyMMdd' ), 'yyyy-MM-dd' );2021 -05 -01
获得上个月月初日期 1 2 select date_format(add_months(current_date ,-1 ),'yyyy-MM-01' );2021 -07 -01
获得上上个月月末日期 1 2 select date_sub(date_format(add_months(current_date ,-1 ),'yyyy-MM-01' ),1 );2021 -06 -30
字符函数 字符串拼接 1 2 3 4 5 6 7 8 9 10 11 12 13 14 concat(string| binary A, string| binary B...) concat_ws(string SEP, string A, string B...) concat_ws(string SEP, array < string> ) select concat('sannaha' ,'-' ,'blog' ,':' ,'sannaha.moe' );sannaha- blog:sannaha.moe select concat_ws('-' ,'sannaha' ,'blog' ,'sannaha.moe' );sannaha- blog- sannaha.moe select concat_ws('-' ,array ('sannaha' ,'blog' ,'sannaha.moe' ));sannaha- blog- sannaha.moe
字符串截取 1 2 3 4 5 6 7 8 9 10 substr(string| binary A, int start , int len) substring (string| binary A, int start , int len)select substr('www.sannaha.moe' ,5 ,7 );sannaha select concat_ws('-' ,substr('20171205' ,1 ,4 ),substr('20171205' ,5 ,2 ),substr('20171205' ,7 ,2 ));2017 -12 -05
字符串分割 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 split(string str, string pat) str_to_map(string str[, delimiter1, delimiter2]) select split("sannaha-blog-sannaha.moe",'-' );["sannaha","blog","sannaha.moe"] select str_to_map("blog=sannaha.moe,homepage=thinklong.me",',' ,'=' );{"homepage":"thinklong.me","blog":"sannaha.moe"} select str_to_map("blog:sannaha.moe,homepage:thinklong.me");{"homepage":"thinklong.me","blog":"sannaha.moe"}
正则提取和替换 1 2 3 4 5 6 7 8 9 10 11 12 regexp_extract(string str, string pattern , int index) regexp_replace(string str, string PATTERN , string REPLACEMENT) select regexp_extract("https://www.sannaha.moe/Hive-Part2/", "(http(s)?:\/\/)?(www\.)?([a-zA-Z0-9]{0,62}\.[a-zA-Z0-9]{0,62})",4 );sannaha.moe select regexp_replace('2020/05/01,2020 05 01' , '\\s|/' , '-' );2020 -05 -01 ,2020 -05 -01
可视化观察正则表达式：*Regulex *
从URL提取信息 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 parse_url(string url, string partToExtract [, string keyToExtract]) select parse_url("https://root@sannaha.moe:8080/Hive-Part2/", "HOST");sannaha.moe select parse_url("https://root@sannaha.moe:8080/Hive-Part2/", "AUTHORITY");root@sannaha .moe:8080 select parse_url("https://root@sannaha.moe:8080/Hive-Part2/", "USERINFO");root select parse_url("https://sannaha.moe/Hive-Part2/login?u=root#字符函数", "PATH");/ Hive- Part2/ loginselect parse_url("https://sannaha.moe/Hive-Part2/login?u=root&p=123#字符函数", "QUERY");u= root& p= 123 select parse_url("https://sannaha.moe/Hive-Part2/login?u=root&p=123#字符函数", "QUERY", "u");root select parse_url("https://sannaha.moe/Hive-Part2/login?u=root#字符函数", "REF");字符函数 select parse_url("https://sannaha.moe/Hive-Part2/login?u=root#字符函数", "PROTOCOL");https select parse_url("https://sannaha.moe/Hive-Part2/正则-匹配域名.png", "FILE");/ Hive- Part2/ 正则- 匹配域名.png
去除空格 1 2 3 4 5 6 7 8 9 10 11 12 13 14 trim (string A)ltrim(string A) rtrim(string A) select trim (' sannaha ' );sannaha select ltrim(' sannaha ' );sannaha select rtrim(' sannaha ' ); sannaha
字符串长度 1 2 3 4 5 6 length(string A) select length('sannaha' );7
千分位表示 1 2 3 4 5 6 format_number(number x, int d) select format_number(1919810.114514 , 5 );1 ,919 ,810.11451
MD5 1 2 3 4 5 6 7 8 9 10 11 12 13 14 md5(string A) md5(concat(string A, string salt)) md5(string A, string salt) select md5('sannaha' );c3dd0670c1e786b889db56908515ca2a select md5(concat('sannaha' ,'salt' ));16 f1137fa58b56ea2955d28b47ac5106select md5('sannaha' || 'salt' );16 f1137fa58b56ea2955d28b47ac5106
解析json Hive 提供了 get_json_object 和 json_tuple 两个内置函数来解析 json 字符串。
解析一个字段 get_json_object 每次可以解析一个字段：
1 2 3 4 5 6 7 8 9 get_json_object(string json_str,string $.key) hive> select get_json_object('{"movie":"流浪地球","rate":"5"}' ,'$.movie' ) AS movie, get_json_object('{"movie":"流浪地球","rate":"5"}' ,'$.rate' ) AS rate; movie rate 流浪地球 5
解析多个字段 json_tuple 是个 UDTF 函数，可以一次解析多个 json 字段，经常与 lateral view 一起使用：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 json_tuple(string json_str,string key1,string key2,...) hive> select json_tuple('{"movie":"流浪地球","rate":"5"}' ,'movie' ,'rate' ) as movie,rate; c0 c1 流浪地球 5 hive> select 'sannaha' AS u_name,json_tuple('{"movie":"流浪地球","rate":"5"}' ,'movie' ,'rate' ); FAILED: SemanticException [Error 10081 ]: UDTF’s are not supported outside the SELECT clause, nor nested in expressions hive> SELECT t1.u_name, t2.movie, t2.rate FROM douban t1 lateral view json_tuple (t1.json_str, 'movie' , 'rate' ) t2 AS movie, rate; t1.u_name t2.movie t2.rate sannaha 流浪地球 5
解析数组 对于 json 数组，可以先用 explode 函数将 json 数组里的元素按行的形式输出，再用 get_json_object 或 json_tuple 函数解析里面的字段。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 hive> SELECT explode( split( regexp_replace( regexp_replace( '[{"website":"sannaha.moe","name":"博客"},{"website":"thinklong.me","name":"主页"}]' ,'\\[|\\]' ,'' ), '\\}\\,\\{' ,'\\}\\;\\{' ), '\\;' ) ); col {"website":"sannaha.moe","name":"博客"} {"website":"thinklong.me","name":"主页"} SELECT json_tuple(json_str,'website' ,'name' ) FROM ( SELECT explode( split( regexp_replace( regexp_replace( '[{"website":"sannaha.moe","name":"博客"},{"website":"thinklong.me","name":"主页"}]' ,'\\[|\\]' ,'' ), '\\}\\,\\{' ,'\\}\\;\\{' ), '\\;' ) ) as json_str ) t; c0 c1 sannaha.moe 博客 thinklong.me 主页
条件函数 空字段赋值 1 2 3 4 5 6 7 8 nvl(T value , T default_value) select nvl(null ,'sannaha' );sannaha select nvl('hello' ,'sannaha' );hello
首个非空 1 2 3 4 5 6 COALESCE (T v1, T v2, ...)select coalesce (null ,null ,'sannaha' ,'blog' );sannaha
是否为空 1 2 3 4 5 6 7 8 9 10 11 12 13 14 isnull(a) isnotnull(a) select isnull(null );true select isnull('' );false select isnotnull(null );false select isnotnull('' );true
if 1 2 3 4 5 if(boolean testCondition, T valueTrue, T valueFalseOrNull) select if('sannaha' = 'admin' ,'是管理员' ,'不是管理员' );不是管理员
case 1 2 3 4 5 6 7 8 9 10 CASE a WHEN b THEN c [WHEN d THEN e]... [ELSE f] END CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END select case 7 when 4 then 4 when 7 then 7 when 5 then 5 else 1 end ; 7 select case when 'sannaha' = 'admin' then '是管理员' when 1 + 1 = 0 then 'WTF' else '不是管理员' end ;不是管理员
实例
在需要进行条件选择的场景下，可以使用 CASE WHEN 或 IF 进行选择。
统计员工表中不同 level 下男性和女性各自的人数。表数据如下：
emp.csv 1 2 3 4 5 6 7 8 风清扬 P14 male 逍遥子 P14 male 三丰 P10 male 无忌 P10 male 赵敏 P10 female 苗人凤 M10 male 语嫣 M10 female 苏荃 M10 female
创建员工表并导入数据：
1 2 3 4 5 create table emp(name string, level string, sex string) row format delimited fields terminated by '\t' ; load data local inpath '/data/emp.csv' into table emp;
两种方式实现查询：
1 2 3 4 5 6 7 8 9 10 11 12 13 select level, sum (case sex when 'male' then 1 else 0 end ) male_count, sum (case sex when 'female' then 1 else 0 end ) female_count from emp group by level; select level, sum (if(sex= 'male' ,1 ,0 )) male_count, sum (if(sex= 'female' ,1 ,0 )) female_count from emp group by level;
百分位数函数 计算指定列的百分位数。百分位数表示在一组数据中某一个百分比处的数值：
percentile(col, p)：返回 col 列的第 p 百分位的值。col 列的数据类型必须是整型，p 的取值范围为 0.0 到 1.0；
percentile_approx(col, p[, accuracy])：返回 col 列的第 p 百分位的近似值。col 列可以是浮点数，accuracy 参数用来控制精度，代表 bucket 的数量，默认值为 10000。
1 2 3 4 5 6 7 8 9 10 11 12 select percentile(total_visit_cnt, 0.1 ) p_01, percentile(total_visit_cnt, 0.2 ) p_02, percentile(total_visit_cnt, 0.3 ) p_03, percentile(total_visit_cnt, 0.4 ) p_04, percentile(total_visit_cnt, 0.5 ) p_05, percentile(total_visit_cnt, 0.6 ) p_06, percentile(total_visit_cnt, 0.7 ) p_07, percentile(total_visit_cnt, 0.8 ) p_08, percentile(total_visit_cnt, 0.9 ) p_09 from app_usage_total where month = '202311' and app_type= 'game' ;
窗口函数 相关函数：
OVER()：指定分析函数工作的数据窗口大小，该窗口大小会随着行的变化而变化。
CURRENT ROW：当前行
n PRECEDING：往前 n 行数据
n FOLLOWING：往后 n 行数据
UNBOUNDED PRECEDING：从起始行开始
UNBOUNDED FOLLOWING：到结尾行结束
LAG(col,n)：往前第 n 行数据
LEAD(col,n)：往后第 n 行数据
NTILE(n)：用于将有序分区中的数据切分成 n 片，返回当前记录所在的切片值，切片值从 1 开始。
窗口函数实例
需求：
查询在 2020 年 6 月份进行过消费的用户以及消费用户的数量。
查询用户的购买明细，并统计所有用户月消费总额。
在 2 的基础上，将消费额按照日期进行累加。
查询用户上次的购买时间
查询前 20% 时间的订单信息
准备数据：
steammarket.csv 1 2 3 4 5 6 7 8 9 10 11 12 13 14 ZywOo P90 | 精英之作 2017-07-18 1.21 SANNAHA M4A1 消音型 | 多变迷彩 2017-07-18 4.60 ZywOo Tec-9 | 竹林 2017-07-19 1.57 SANNAHA M4A4 | 杀意大名 2017-07-24 16.01 SANNAHA AK-47 | 精英之作 2017-09-14 8.56 ZywOo M4A1 消音型 | 毁灭者 2000 2017-09-14 85.23 SANNAHA 格洛克 18 型 | 荒野反叛 2017-09-17 251.00 SANNAHA AUG | 席德.米德 2020-06-18 23.47 ZywOo AWP | 蠕虫之神 2020-06-18 10.69 SANNAHA AWP | 猫猫狗狗 2020-06-20 19.02 SANNAHA 沙漠之鹰 | 科林斯遗产 2020-06-20 10.26 s1mple AK-47 | 深海复仇 2020-06-22 143.08 s1mple M4A4 | X 光 2020-06-23 80.01 s1mple AWP | 暴怒野兽 2020-06-23 301.27
创建表并加载数据：
1 2 3 4 5 6 7 8 9 create table steammarket( id string, item string, listeddate string, price double ) row format delimited fields terminated by '\t' ; load data local inpath '/data/steammarket.csv' into table steammarket;
实现需求：
查询在2020年6月份进行过消费的用户以及消费用户的数量。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 > select id,count (* ) over () from steammarket where substring (listeddate,1 ,7 )= '2020-06' group by id; id count_window_0 s1mple 3 ZywOo 3 SANNAHA 3 > select id,count (* ) from steammarket where substring (listeddate,1 ,7 )= '2020-06' group by id; id _c1 SANNAHA 3 ZywOo 1 s1mple 3 > select id,count (* ) over () from steammarket where substring (listeddate,1 ,7 )= '2020-06' ; id count_window_0 s1mple 7 s1mple 7 s1mple 7 SANNAHA 7 SANNAHA 7 ZywOo 7 SANNAHA 7
查询用户的购买明细，并统计所有用户月消费总额。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 > select id, item, listeddate, price, sum (price) over (partition by month (listeddate)) from steammarket; id item listeddate price sum_window_0 s1mple AWP | 暴怒野兽 2020 -06 -23 301.27 587.8000000000001 s1mple M4A4 | X 光 2020 -06 -23 80.01 587.8000000000001 s1mple AK-47 | 深海复仇 2020 -06 -22 143.08 587.8000000000001 SANNAHA 沙漠之鹰 | 科林斯遗产 2020 -06 -20 10.26 587.8000000000001 SANNAHA AWP | 猫猫狗狗 2020 -06 -20 19.02 587.8000000000001 ZywOo AWP | 蠕虫之神 2020 -06 -18 10.69 587.8000000000001 SANNAHA AUG | 席德.米德 2020 -06 -18 23.47 587.8000000000001 ZywOo P90 | 精英之作 2017 -07 -18 1.21 23.39 SANNAHA M4A4 | 杀意大名 2017 -07 -24 16.01 23.39 ZywOo Tec-9 | 竹林 2017 -07 -19 1.57 23.39 SANNAHA M4A1 消音型 | 多变迷彩 2017 -07 -18 4.6 23.39 ZywOo M4A1 消音型 | 毁灭者 2000 2017 -09 -14 85.23 344.79 SANNAHA AK-47 | 精英之作 2017 -09 -14 8.56 344.79 SANNAHA 格洛克 18 型 | 荒野反叛 2017 -09 -17 251.0 344.79
在 2 的基础上，将消费额按照日期进行累加。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 select id,item,listeddate,price, sum (price) over () as sum1, sum (price) over (distribute by id) as sum2, sum (price) over (distribute by id sort by listeddate) as sum3, sum (price) over (distribute by id sort by listeddate rows between UNBOUNDED PRECEDING and current row ) as sum4, sum (price) over (distribute by id sort by listeddate rows between 1 PRECEDING and current row ) as sum5, sum (price) over (distribute by id sort by listeddate rows between 1 PRECEDING and 1 FOLLOWING) as sum6, sum (price) over (distribute by id sort by listeddate rows between current row and UNBOUNDED FOLLOWING) as sum7 from steammarket;
查询用户的上次消费时间。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 select id,item,listeddate,price, lag (listeddate,1 ,'1900-01-01' ) over (partition by id order by listeddate) as lastdate1, lag (listeddate,2 ) over (partition by id order by listeddate) as lastdate2 from steammarket; id item listeddate price lastdate1 lastdate2 SANNAHA M4A1 消音型 | 多变迷彩 2017 -07 -18 4.6 1900 -01 -01 NULL SANNAHA M4A4 | 杀意大名 2017 -07 -24 16.01 2017 -07 -18 NULL SANNAHA AK-47 | 精英之作 2017 -09 -14 8.56 2017 -07 -24 2017 -07 -18 SANNAHA 格洛克 18 型 | 荒野反叛 2017 -09 -17 251.0 2017 -09 -14 2017 -07 -24 SANNAHA AUG | 席德.米德 2020 -06 -18 23.47 2017 -09 -17 2017 -09 -14 SANNAHA 沙漠之鹰 | 科林斯遗产 2020 -06 -20 10.26 2020 -06 -18 2017 -09 -17 SANNAHA AWP | 猫猫狗狗 2020 -06 -20 19.02 2020 -06 -20 2020 -06 -18 ZywOo P90 | 精英之作 2017 -07 -18 1.21 1900 -01 -01 NULL ZywOo Tec-9 | 竹林 2017 -07 -19 1.57 2017 -07 -18 NULL ZywOo M4A1 消音型 | 毁灭者 2000 2017 -09 -14 85.23 2017 -07 -19 2017 -07 -18 ZywOo AWP | 蠕虫之神 2020 -06 -18 10.69 2017 -09 -14 2017 -07 -19 s1mple AK-47 | 深海复仇 2020 -06 -22 143.08 1900 -01 -01 NULL s1mple M4A4 | X 光 2020 -06 -23 80.01 2020 -06 -22 NULL s1mple AWP | 暴怒野兽 2020 -06 -23 301.27 2020 -06 -23 2020 -06 -22
查询时间在前 20% 的订单信息。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 > select id,item,listeddate,price from ( select id,item,listeddate,price, ntile (5 ) over (order by listeddate) ntile_5 from steammarket) t where ntile_5= 1 ; id item listeddate price ZywOo P90 | 精英之作 2017 -07 -18 1.21 SANNAHA M4A1 消音型 | 多变迷彩 2017 -07 -18 4.6 ZywOo Tec-9 | 竹林 2017 -07 -19 1.57 > select id,item,listeddate,price, ntile (5 ) over (order by listeddate) ntile_5 from steammarket; id item listeddate price ntile_5 ZywOo P90 | 精英之作 2017 -07 -18 1.21 1 SANNAHA M4A1 消音型 | 多变迷彩 2017 -07 -18 4.6 1 ZywOo Tec-9 | 竹林 2017 -07 -19 1.57 1 SANNAHA M4A4 | 杀意大名 2017 -07 -24 16.01 2 SANNAHA AK-47 | 精英之作 2017 -09 -14 8.56 2 ZywOo M4A1 消音型 | 毁灭者 2000 2017 -09 -14 85.23 2 SANNAHA 格洛克 18 型 | 荒野反叛 2017 -09 -17 251.0 3 SANNAHA AUG | 席德.米德 2020 -06 -18 23.47 3 ZywOo AWP | 蠕虫之神 2020 -06 -18 10.69 3 SANNAHA AWP | 猫猫狗狗 2020 -06 -20 19.02 4 SANNAHA 沙漠之鹰 | 科林斯遗产 2020 -06 -20 10.26 4 s1mple AK-47 | 深海复仇 2020 -06 -22 143.08 4 s1mple M4A4 | X 光 2020 -06 -23 80.01 5 s1mple AWP | 暴怒野兽 2020 -06 -23 301.27 5
排名函数 排名函数也属于窗口函数。
相关函数：
RANK()：返回分组中的排名，数据相同时排名会重复，会留下空位。
DENSE_RANK()：返回分组中的排名，数据相同时排名会重复，不会留下空位。
ROW_NUMBER()：返回分组中的排名，数据相同时会按照数据集中的顺序给予不同的排名。
排名函数实例
需求：现有游戏评分数据，计算不同游戏在各家媒体中的排名。
准备数据：
gamescore.csv 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 THE LAST OF US IGN 100 THE LAST OF US Gamespot 80 THE LAST OF US Metacritic 95 THE LAST OF US UserScore 91 THE LAST OF US PART II IGN 100 THE LAST OF US PART II IGN Japan 70 THE LAST OF US PART II Gamespot 80 THE LAST OF US PART II Metacritic 95 THE LAST OF US PART II UserScore 91 PERSONA 5 ROYAL IGN 100 PERSONA 5 ROYAL IGN Japan 89 PERSONA 5 ROYAL Gamespot 100 PERSONA 5 ROYAL Metacritic 95 PERSONA 5 ROYAL UserScore 81 FINAL FANTASY VII REMAKE IGN 80 FINAL FANTASY VII REMAKE IGN Japan 80 FINAL FANTASY VII REMAKE Gamespot 100 FINAL FANTASY VII REMAKE Metacritic 87 FINAL FANTASY VII REMAKE UserScore 80 ORI AND THE WILL OF THE WISPS IGN 90 ORI AND THE WILL OF THE WISPS Metacritic 88 ORI AND THE WILL OF THE WISPS UserScore 85
创建表并加载数据：
1 2 3 4 5 6 7 create table gamescore(name string, media string, score double ) row format delimited fields terminated by '\t' ; load data local inpath '/data/gamescore.csv' into table gamescore;
按需求查询数据：
1 2 3 4 5 select name,media,score, rank () over (partition by media order by score desc ) rp, dense_rank () over (partition by media order by score desc ) drp, row_number () over (partition by media order by score desc ) rmp from gamescore;
行转列 行转列，就是将某对象在同一列下的多行内容作为多个字段追加到一行中。如下图所示：
Hive 中的行列转换与 RDBMS 中的稍有区别，行转列是将多行内容拼接起来放在一列。
相关函数：
CONCAT(str1, str2…)：将多个字符串拼接起来，返回一个字符串。
CONCAT_WS(separator, str1, str2…)：使用连接符 separator 将多个字符串拼接起来，返回一个字符串。该函数会跳过分隔符参数之后的任何 NULL 和空字符串。如果分隔符为 NULL，返回值也为 NULL。
COLLECT_SET(col)：将 col 字段的值去重汇总，返回 Array 类型数据。该函数为聚合函数，只接受基本数据类型。
函数用法示例：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 > select concat(name,'-' ,level) name_level from emp;name_level 风清扬- P14 逍遥子- P14 三丰- P10 无忌- P10 赵敏- P10 苗人凤- M10 语嫣- M10 苏荃- M10 > select concat_ws(',' ,name,level) from emp;name_level 风清扬,P14 逍遥子,P14 三丰,P10 无忌,P10 赵敏,P10 苗人凤,M10 语嫣,M10 苏荃,M10 > select collect_set(level) levels from emp;levels ["P14","P10","M10"]
行转列示例
需求：将 emp 员工表中 level 和 sex 一样的员工归类到一起。
实现：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 > select level_sex, concat_ws(',' ,collect_set(name)) names from ( select concat(level,',' ,sex) level_sex, name from emp ) t1 group by level_sex; level_sex names M10,female 语嫣,苏荃 M10,male 苗人凤 P10,female 赵敏 P10,male 三丰,无忌 P14,male 风清扬,逍遥子 > select concat(level,',' ,sex) level_sex, name from emp; level_sex name P14,male 风清扬 P14,male 逍遥子 P10,male 三丰 P10,male 无忌 P10,female 赵敏 M10,male 苗人凤 M10,female 语嫣 M10,female 苏荃
列转行 列转行与行转列相反，将一列内容拆分为多行。
相关函数：
EXPLODE(col)：将 Hive 中一列复杂的 Array 或 Map 拆成多行。
LATERAL VIEW：和 SPLIT、EXPLODE 等 UDTF 一起使用，将一列数据拆成多行，并对拆分后的数据进行聚合。
用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias
函数用法示例：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 $ vi gameinfo.csv Sekiro: Shadows Die Twice 动作,潜行,类魂系列,冒险 Beat Saber 虚拟现实,节奏,音乐,独立 Half- Life: Alyx 虚拟现实,第一人称射击,恐怖 create table gameinfo(game string, category array < string> ) row format delimited fields terminated by '\t' collection items terminated by ',' ; load data local inpath '/data/gameinfo.csv' into table gameinfo; > select explode(category) categories from gameinfo;categories 动作 潜行 类魂系列 冒险 虚拟现实 节奏 音乐 独立 虚拟现实 第一人称射击 恐怖
列转行示例
需求：将游戏对应的分类拆分为多行。
实现：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 > select game,categories from gameinfo lateral view explode(category) gameinfo_temp as categories; game categories Sekiro: Shadows Die Twice 动作 Sekiro: Shadows Die Twice 潜行 Sekiro: Shadows Die Twice 类魂系列 Sekiro: Shadows Die Twice 冒险 Beat Saber 虚拟现实 Beat Saber 节奏 Beat Saber 音乐 Beat Saber 独立 Half- Life: Alyx 虚拟现实 Half- Life: Alyx 第一人称射击 Half- Life: Alyx 恐怖
案例 复制分区表
创建新表，与原表表结构相同：
1 create table test.tablename like sannaha.tablename;
通过 HDFS 复制原表数据：
1 2 $ hdfs dfs -cp /user/hive/warehouse/sannaha.db/tablename/* /user/hive/warehouse/test.db/tablename/
修复表结构：
1 msck repair table test.tablename;
蚂蚁森林 表数据 1 2 3 4 5 6 7 8 9 以下表记录了用户每天的蚂蚁森林低碳生活的记录流水。 table_name：user_low_carbon user_id data_dt low_ carbon用户 日期 减少碳排放（g） 蚂蚁森林植物换购表，用于记录申领环保植物所需要减少的碳排放量 table_name: plant_carbon plant_id plant_name low_ carbon植物编号 植物名 换购植物所需要的碳
低碳生活流水：
user_low_carbon.txt 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 u_001 2017/1/1 10 u_001 2017/1/2 150 u_001 2017/1/2 110 u_001 2017/1/2 10 u_001 2017/1/4 50 u_001 2017/1/4 10 u_001 2017/1/6 45 u_001 2017/1/6 90 u_002 2017/1/1 10 u_002 2017/1/2 150 u_002 2017/1/2 70 u_002 2017/1/3 30 u_002 2017/1/3 80 u_002 2017/1/4 150 u_002 2017/1/5 101 u_002 2017/1/6 68 u_003 2017/1/1 20 u_003 2017/1/2 10 u_003 2017/1/2 150 u_003 2017/1/3 160 u_003 2017/1/4 20 u_003 2017/1/5 120 u_003 2017/1/6 20 u_003 2017/1/7 10 u_003 2017/1/7 110 u_004 2017/1/1 110 u_004 2017/1/2 20 u_004 2017/1/2 50 u_004 2017/1/3 120 u_004 2017/1/4 30 u_004 2017/1/5 60 u_004 2017/1/6 120 u_004 2017/1/7 10 u_004 2017/1/7 120 u_005 2017/1/1 80 u_005 2017/1/2 50 u_005 2017/1/2 80 u_005 2017/1/3 180 u_005 2017/1/4 180 u_005 2017/1/4 10 u_005 2017/1/5 80 u_005 2017/1/6 280 u_005 2017/1/7 80 u_005 2017/1/7 80 u_006 2017/1/1 40 u_006 2017/1/2 40 u_006 2017/1/2 140 u_006 2017/1/3 210 u_006 2017/1/3 10 u_006 2017/1/4 40 u_006 2017/1/5 40 u_006 2017/1/6 20 u_006 2017/1/7 50 u_006 2017/1/7 240 u_007 2017/1/1 130 u_007 2017/1/2 30 u_007 2017/1/2 330 u_007 2017/1/3 30 u_007 2017/1/4 530 u_007 2017/1/5 30 u_007 2017/1/6 230 u_007 2017/1/7 130 u_007 2017/1/7 30 u_008 2017/1/1 160 u_008 2017/1/2 60 u_008 2017/1/2 60 u_008 2017/1/3 60 u_008 2017/1/4 260 u_008 2017/1/5 360 u_008 2017/1/6 160 u_008 2017/1/7 60 u_008 2017/1/7 60 u_009 2017/1/1 70 u_009 2017/1/2 70 u_009 2017/1/2 70 u_009 2017/1/3 170 u_009 2017/1/4 270 u_009 2017/1/5 70 u_009 2017/1/6 70 u_009 2017/1/7 70 u_009 2017/1/7 70 u_010 2017/1/1 90 u_010 2017/1/2 90 u_010 2017/1/2 90 u_010 2017/1/3 90 u_010 2017/1/4 90 u_010 2017/1/4 80 u_010 2017/1/5 90 u_010 2017/1/5 90 u_010 2017/1/6 190 u_010 2017/1/7 90 u_010 2017/1/7 90 u_011 2017/1/1 110 u_011 2017/1/2 100 u_011 2017/1/2 100 u_011 2017/1/3 120 u_011 2017/1/4 100 u_011 2017/1/5 100 u_011 2017/1/6 100 u_011 2017/1/7 130 u_011 2017/1/7 100 u_012 2017/1/1 10 u_012 2017/1/2 120 u_012 2017/1/2 10 u_012 2017/1/3 10 u_012 2017/1/4 50 u_012 2017/1/5 10 u_012 2017/1/6 20 u_012 2017/1/7 10 u_012 2017/1/7 10 u_013 2017/1/1 50 u_013 2017/1/2 150 u_013 2017/1/2 50 u_013 2017/1/3 150 u_013 2017/1/4 550 u_013 2017/1/5 350 u_013 2017/1/6 50 u_013 2017/1/7 20 u_013 2017/1/7 60 u_014 2017/1/1 220 u_014 2017/1/2 120 u_014 2017/1/2 20 u_014 2017/1/3 20 u_014 2017/1/4 20 u_014 2017/1/5 250 u_014 2017/1/6 120 u_014 2017/1/7 270 u_014 2017/1/7 20 u_015 2017/1/1 10 u_015 2017/1/2 20 u_015 2017/1/2 10 u_015 2017/1/3 10 u_015 2017/1/4 20 u_015 2017/1/5 70 u_015 2017/1/6 10 u_015 2017/1/7 80 u_015 2017/1/7 60
蚂蚁森林植物换购表：
plant_carbon.txt 1 2 3 4 p001 梭梭树 17 p002 沙柳 19 p003 樟子树 146 p004 胡杨 215
蚂蚁森林植物申领统计 2017 年 1 月 1 日开始记录低碳生活流水（user_low_carbon），2017 年 10 月 1 日之前满足申领条件的用户都申领了一棵 p004-胡杨，剩余的能量全部用来领取 p002-沙柳 。统计在 10 月 1 日累计申领 p002-沙柳 数量前 10 的用户，以及比后一名多领了几棵沙柳。得到的统计结果格式如下：
user_id
plant_count
less_count
u_007
66
3
u_013
63
10
u_008
53
7
实现：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 > select user_id, plant_count, plant_count - lead (plant_count, 1 , 0 ) over ( order by plant_count desc ) less_count from ( select t1.user_id, if( t1.carbon_sum >= t2.p004_carbon, floor ((t1.carbon_sum - t2.p004_carbon) / t3.p002_carbon), floor (t1.carbon_sum / t3.p002_carbon) ) plant_count from ( select user_id, sum (low_carbon) carbon_sum from user_low_carbon where date_format(regexp_replace(data_dt, '/' , '-' ), 'yyyy-MM' ) < '2017-10' group by user_id ) t1, ( select low_carbon p004_carbon from plant_carbon where plant_id = 'p004' ) t2, ( select low_carbon p002_carbon from plant_carbon where plant_id = 'p002' ) t3 ) t4 limit 10 ; user_id plant_count less_count u_007 66 3 u_013 63 10 u_008 53 7 u_005 46 1 u_010 45 1 u_014 44 5 u_011 39 2 u_009 37 5 u_006 32 9 u_002 23 1
蚂蚁森林低碳用户排名分析 查询 user_low_carbon 表中符合要求的每日流水记录，要求如下：
用户在 2017 年，连续三天（或以上），每天减少碳排放（low_carbon）之和都大于 100。
查询返回满足该条件的当天的所有流水记录。
举例说明，用户 u_002 的流水记录如下：
1 2 3 4 5 6 7 8 u_002 2017 /1 /1 10 u_002 2017 /1 /2 150 u_002 2017 /1 /2 70 u_002 2017 /1 /3 30 u_002 2017 /1 /3 80 u_002 2017 /1 /4 150 u_002 2017 /1 /5 101 u_002 2017 /1 /6 68
2017/1/2 ~ 2017/1/5 连续四天，每天减少的碳排放量之和都大于 100，因此查询结果应返回这四天的流水记录。
这个需求的重点是如何判断“连续三天（或以上）”，在这里提供了多种解法。
解法一
列举出每一种符合“前后三天”条件的情况：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 select t.user_id, t.data_dt, t.low_carbon from ( select user_id, data_dt from ( select user_id, data_dt, datediff(data_dt, lag2) lag2_diff, datediff(data_dt, lag1) lag1_diff, datediff(data_dt, lead2) lead2_diff, datediff(data_dt, lead1) lead1_diff from ( select user_id, data_dt, lag (data_dt, 2 , '1970-01-01' ) over ( partition by user_id order by data_dt ) lag2, lag (data_dt, 1 , '1970-01-01' ) over ( partition by user_id order by data_dt ) lag1, lead (data_dt, 2 , '9999-12-31' ) over ( partition by user_id order by data_dt ) lead2, lead (data_dt, 1 , '9999-12-31' ) over ( partition by user_id order by data_dt ) lead1 from ( select user_id, date_format(regexp_replace(data_dt, '/' , '-' ), 'yyyy-MM-dd' ) data_dt from user_low_carbon where substring (data_dt, 1 , 4 ) = '2017' group by user_id, data_dt having sum (low_carbon) > 100 ) t1 ) t2 ) t3 where ( lag2_diff = 2 and lag1_diff = 1 ) or ( lag1_diff = 1 and lead1_diff = -1 ) or ( lead1_diff = -1 and lead2_diff = -2 ) ) t4 left join user_low_carbon t on t4.user_id = t.user_id and date_format(t4.data_dt,'yyyy/M/d' ) = t.data_dt; t.user_id t.data_dt t.low_carbon u_002 2017 / 1 / 2 150 u_002 2017 / 1 / 2 70 u_002 2017 / 1 / 3 30 u_002 2017 / 1 / 3 80 u_002 2017 / 1 / 4 150 u_002 2017 / 1 / 5 101 u_005 2017 / 1 / 2 50 u_005 2017 / 1 / 2 80 u_005 2017 / 1 / 3 180 u_005 2017 / 1 / 4 180 u_005 2017 / 1 / 4 10 u_008 2017 / 1 / 4 260 u_008 2017 / 1 / 5 360 u_008 2017 / 1 / 6 160 u_008 2017 / 1 / 7 60 u_008 2017 / 1 / 7 60 u_009 2017 / 1 / 2 70 u_009 2017 / 1 / 2 70 u_009 2017 / 1 / 3 170 u_009 2017 / 1 / 4 270 u_010 2017 / 1 / 4 90 u_010 2017 / 1 / 4 80 u_010 2017 / 1 / 5 90 u_010 2017 / 1 / 5 90 u_010 2017 / 1 / 6 190 u_010 2017 / 1 / 7 90 u_010 2017 / 1 / 7 90 u_011 2017 / 1 / 1 110 u_011 2017 / 1 / 2 100 u_011 2017 / 1 / 2 100 u_011 2017 / 1 / 3 120 u_013 2017 / 1 / 2 150 u_013 2017 / 1 / 2 50 u_013 2017 / 1 / 3 150 u_013 2017 / 1 / 4 550 u_013 2017 / 1 / 5 350 u_014 2017 / 1 / 5 250 u_014 2017 / 1 / 6 120 u_014 2017 / 1 / 7 270 u_014 2017 / 1 / 7 20
解法二
若日期与等差数列的差值相等，则为“连续的天”。这种方式可以随意指定“前后n天”，而不必列举出每一种情况：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 select t.user_id, t.data_dt, t.low_carbon from ( select user_id, data_dt, count (* ) over (PARTITION by user_id, data_sub_rk) as date_count from ( select user_id, data_dt, date_sub(data_dt, rk) data_sub_rk from ( select user_id, data_dt, rank () over ( partition by user_id order by data_dt ) rk from ( select user_id, date_format(regexp_replace(data_dt, '/' , '-' ), 'yyyy-MM-dd' ) data_dt from user_low_carbon where substring (data_dt, 1 , 4 ) = '2017' group by user_id, data_dt having sum (low_carbon) >= 100 ) t1 ) t2 ) t3 ) t4 left join user_low_carbon t on t4.user_id = t.user_id and date_format(t4.data_dt,'yyyy/M/d' ) = t.data_dt where date_count >= 3 ;t.user_id t.data_dt t.low_carbon u_002 2017 / 1 / 2 150 u_002 2017 / 1 / 2 70 u_002 2017 / 1 / 3 30 u_002 2017 / 1 / 3 80 u_002 2017 / 1 / 4 150 u_002 2017 / 1 / 5 101 u_005 2017 / 1 / 3 180 u_005 2017 / 1 / 2 50 u_005 2017 / 1 / 2 80 u_005 2017 / 1 / 4 180 u_005 2017 / 1 / 4 10 u_008 2017 / 1 / 4 260 u_008 2017 / 1 / 6 160 u_008 2017 / 1 / 5 360 u_008 2017 / 1 / 7 60 u_008 2017 / 1 / 7 60 u_009 2017 / 1 / 4 270 u_009 2017 / 1 / 2 70 u_009 2017 / 1 / 2 70 u_009 2017 / 1 / 3 170 u_010 2017 / 1 / 4 90 u_010 2017 / 1 / 4 80 u_010 2017 / 1 / 5 90 u_010 2017 / 1 / 5 90 u_010 2017 / 1 / 6 190 u_010 2017 / 1 / 7 90 u_010 2017 / 1 / 7 90 u_011 2017 / 1 / 2 100 u_011 2017 / 1 / 2 100 u_011 2017 / 1 / 1 110 u_011 2017 / 1 / 3 120 u_013 2017 / 1 / 2 150 u_013 2017 / 1 / 2 50 u_013 2017 / 1 / 3 150 u_013 2017 / 1 / 4 550 u_013 2017 / 1 / 5 350 u_014 2017 / 1 / 5 250 u_014 2017 / 1 / 6 120 u_014 2017 / 1 / 7 270 u_014 2017 / 1 / 7 20
快视频 需求 统计快视频的常规指标，各种TopN指标：
统计视频观看次数 Top10
统计视频数量 Top10 的类别
统计观看次数 Top20 的视频所属类别及对应视频数量
统计观看次数 Top50 的视频所关联视频的类别排名
统计每个视频类别中的观看次数 Top10
统计每个视频类别中的热度 Top10
统计视频上传数量 Top10 的用户及上传的视频
统计每个视频类别中视频观看数 Top10
原始数据 1．数据结构
字段
说明
详细描述
videoid
视频唯一id
11位字符串
uploader
视频上传者
上传者用户名
age
视频年龄
视频在平台上的整数天
category
视频类别
上传视频指定的视频分类
length
视频长度
整型数字
views
观看次数
视频被浏览的次数
rate
视频评分
满分5分
rating
视频热度
整型数字
conment
评论数
整型数字
relatedId
相关视频id
相关视频的id，最多20个
数据示例
1 2 3 7D0Mf4Kn4Xk periurban 583 Music 201 6508 4 .19 687 312 e2 k0 h6 tPvGc yuO6 yjlvXe8 VqpnWBo-R4 E bdDskrr8 jRY y3 IDp2 n7 B48 JngPWhfCb2 M KQaUvH5 oiO4 NSzrwv5 MCwc NHB0 a0 xtLgU DlRodd4 s86 s EzKwOYLh-S0 eUIfRyrqwp8 AK8 Wtfwe-1 k Eq4 hGkIqBGw N1 lkLaLJHlc -uIffs-DHkM zpTorUhCd8 Y AvSK0 qPw7 EU WX5 KLMqY4 bM VKFqqoeMdjwMEvoy_owET8 smpfilms 736 Travel & Places 921 109673 4 .25 1181 774 YtX2 nwowMtU A5 dp02 FXDmM bGoUu4 gAHaI faDB-ToajhM srcg9 xLjtuE 9 aE4 eMVeUEw G5 fZky7 Nm1 k UEXvMJo3 ZAY sCTbH-VP7 mA WiriPTfpIP8 W-s_e61 hkys cQWtiU6 d99 w 93 LHxjgQ4 LE JEiATJFBWO0 JzjnhpqWIPs G_qfXiOkYPU Gd6 M-B3 FOaQ Y5 pMgbhyb18 7 jdAdCmMRkg yxbLFd6 Y38 ESDNkMu8ZT68 w00 dy911 630 People & Blogs 186 10181 3 .49 494 257 rjnbgpPJUks
ETL 通过观察原始数据形式，可以发现，视频可以有多个所属分类，每个所属分类用&符号分割，且分割的两边有空格字符，同时相关视频也是可以有多个元素，多个相关视频又用 \t 进行分割。为了分析数据时方便对存在多个子元素的数据进行操作，我们首先进行数据重组清洗操作。即：将所有的类别用“&”分割，同时去掉两边空格，多个相关视频id也使用“&”进行分割。
pom 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 <?xml version="1.0" encoding="UTF-8"?> <project xmlns ="http://maven.apache.org/POM/4.0.0" xmlns:xsi ="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation ="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd" > <modelVersion > 4.0.0</modelVersion > <groupId > moe.sannaha</groupId > <artifactId > QuickVideo</artifactId > <version > 1.0-SNAPSHOT</version > <dependencies > <dependency > <groupId > org.apache.hadoop</groupId > <artifactId > hadoop-client</artifactId > <version > 3.0.0</version > </dependency > <dependency > <groupId > org.apache.hadoop</groupId > <artifactId > hadoop-common</artifactId > <version > 3.0.0</version > </dependency > <dependency > <groupId > org.apache.hadoop</groupId > <artifactId > hadoop-hdfs</artifactId > <version > 3.0.0</version > </dependency > </dependencies > </project >
Utils 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 package moe.sannaha.utils;public class ETLUtil public static String etlStr (String originalStr) StringBuilder sb = new StringBuilder(); String[] fields = originalStr.split("\t" ); if (fields.length < 9 ) { return null ; } fields[3 ] = fields[3 ].replaceAll(" " , "" ); for (int i = 0 ; i < fields.length; i++) { if (i < 9 ) { if (i == fields.length - 1 ) { sb.append(fields[i]); } else { sb.append(fields[i]).append("\t" ); } } else { if (i == fields.length - 1 ) { sb.append(fields[i]); } else { sb.append(fields[i]).append("&" ); } } } return sb.toString(); } }
Mapper 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 package moe.sannaha.etl;import moe.sannaha.utils.ETLUtil;import org.apache.hadoop.io.LongWritable;import org.apache.hadoop.io.NullWritable;import org.apache.hadoop.io.Text;import org.apache.hadoop.mapreduce.Mapper;import java.io.IOException;public class ETLMapper extends Mapper <LongWritable , Text , NullWritable , Text > Text value = new Text(); @Override protected void map (LongWritable key, Text value, Context context) throws IOException, InterruptedException String originalStr = value.toString(); String etlStr = ETLUtil.etlStr(originalStr); if (etlStr == null ) { return ; } else { value.set(etlStr); } context.write(NullWritable.get(),value); } }
Main 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 package moe.sannaha.etl;import org.apache.hadoop.conf.Configuration;import org.apache.hadoop.conf.Configured;import org.apache.hadoop.fs.Path;import org.apache.hadoop.io.NullWritable;import org.apache.hadoop.mapreduce.Job;import org.apache.hadoop.mapreduce.lib.input.TextInputFormat;import org.apache.hadoop.mapreduce.lib.output.TextOutputFormat;import org.apache.hadoop.util.Tool;import org.apache.hadoop.util.ToolRunner;public class ETLMain extends Configured implements Tool public int run (String[] strings) throws Exception Job job = Job.getInstance(super .getConf(), "QuickVideoETL" ); job.setJarByClass(ETLMain.class); job.setInputFormatClass(TextInputFormat.class); TextInputFormat.addInputPath(job, new Path(strings[0 ])); job.setMapperClass(ETLMapper.class); job.setMapOutputKeyClass(NullWritable.class); job.setNumReduceTasks(0 ); job.setOutputFormatClass(TextOutputFormat.class); TextOutputFormat.setOutputPath(job,new Path(strings[1 ])); boolean b = job.waitForCompletion(true ); return b ? 0 : 1 ; } public static void main (String[] args) throws Exception Configuration configuration = new Configuration(); int run = ToolRunner.run(configuration, new ETLMain(), args); System.exit(run); } }
执行 视频数据存储在 HDFS 中的 /hdfsdata/quickvideo/video/ 下，执行 ETL：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 $ yarn jar QuickVideo-1.0-SNAPSHOT.jar moe.sannaha.etl.ETLMain /hdfsdata/quickvideo/video /hdfsdata/quickvideo/videoetl ... 20/07/01 23:27:27 INFO mapreduce.Job: Job job_1593614454710_0003 completed successfully 20/07/01 23:27:27 INFO mapreduce.Job: Counters: 33 File System Counters FILE: Number of bytes read =0 FILE: Number of bytes written=1087130 FILE: Number of read operations=0 FILE: Number of large read operations=0 FILE: Number of write operations=0 HDFS: Number of bytes read =213621137 HDFS: Number of bytes written=212238254 HDFS: Number of read operations=35 HDFS: Number of large read operations=0 HDFS: Number of write operations=10 HDFS: Number of bytes read erasure-coded=0 Job Counters Launched map tasks=5 Data-local map tasks=5 Total time spent by all maps in occupied slots (ms)=22147 Total time spent by all reduces in occupied slots (ms)=0 Total time spent by all map tasks (ms)=22147 Total vcore-milliseconds taken by all map tasks=22147 Total megabyte-milliseconds taken by all map tasks=22678528 Map-Reduce Framework Map input records=749361 Map output records=743569 Input split bytes=620 Spilled Records=0 Failed Shuffles=0 Merged Map outputs=0 GC time elapsed (ms)=1853 CPU time spent (ms)=14720 Physical memory (bytes) snapshot=1471266816 Virtual memory (bytes) snapshot=12901552128 Total committed heap usage (bytes)=1236795392 Peak Map Physical memory (bytes)=402010112 Peak Map Virtual memory (bytes)=2586484736 File Input Format Counters Bytes Read=213620517 File Output Format Counters Bytes Written=212238254
建表 创建视频表 quickvideo_video_orc，用户表 quickvideo_user_orc，使用 ORC 格式存储；以及用于加载数据的 quickvideo_video_ori 和 quickvideo_user_ori。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 create table quickvideo_video_orc( videoId string, uploader string, age int , category array < string> , length int , views int , rate float , rating int , comment int , relatedId array < string> ) clustered by (uploader) into 6 buckets row format delimited fields terminated by "\t" collection items terminated by "&" stored as orc; create table quickvideo_user_orc( uploader string, videos int , friend int ) row format delimited fields terminated by "\t" stored as orc; create table quickvideo_video_ori( videoId string, uploader string, age int , category array < string> , length int , views int , rate float , rating int , comment int , relatedId array < string> ) row format delimited fields terminated by "\t" collection items terminated by "&" stored as textfile; create table quickvideo_user_ori( uploader string, videos int , friend int ) row format delimited fields terminated by "\t" stored as textfile;
加载数据：
1 load data inpath "/hivetable/quickvideo/video" into table quickvideo_video_ori;
插入数据到 ORC 表：
1 2 insert into table quickvideo_video_orc select * from quickvideo_video_ori;insert into table quickvideo_user_orc select * from quickvideo_user_ori;
对比 TEXTFILE 和 ORC 两种存储格式占用空间的大小，可以发现 ORC 格式的数据压缩率还是很高的：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 $ hdfs dfs -du -h /user/hive/warehouse/myhive.db/quickvideo_video_ori 138.7 M 416.0 M /user/hive/warehouse/myhive.db/quickvideo_video_ori/part-m-00000 56.4 M 169.2 M /user/hive/warehouse/myhive.db/quickvideo_video_ori/part-m-00001 6.4 M 19.3 M /user/hive/warehouse/myhive.db/quickvideo_video_ori/part-m-00002 883.7 K 2.6 M /user/hive/warehouse/myhive.db/quickvideo_video_ori/part-m-00003 54.5 K 163.4 K /user/hive/warehouse/myhive.db/quickvideo_video_ori/part-m-00004 $ hdfs dfs -du -h /user/hive/warehouse/myhive.db/quickvideo_video_orc 17.4 M 52.3 M /user/hive/warehouse/myhive.db/quickvideo_video_orc/000000_0 17.6 M 52.9 M /user/hive/warehouse/myhive.db/quickvideo_video_orc/000001_0 17.3 M 51.9 M /user/hive/warehouse/myhive.db/quickvideo_video_orc/000002_0 17.2 M 51.7 M /user/hive/warehouse/myhive.db/quickvideo_video_orc/000003_0 17.3 M 51.9 M /user/hive/warehouse/myhive.db/quickvideo_video_orc/000004_0 17.2 M 51.6 M /user/hive/warehouse/myhive.db/quickvideo_video_orc/000005_0
业务分析 统计观看次数Top10 使用 order by 按照 views 字段全局排序，只显示前 10 条：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 > select videoId, uploader, age, category, length, views, rate, rating, comment from quickvideo_video_orc order by views desc limit 10 ; + | videoid | uploader | age | category | length | views | rate | rating | comment | + | dMH0bHeiRNg | judsonlaipply | 415 | ["Comedy"] | 360 | 42513417 | 4.68 | 87520 | 22718 | | 0 XxI- hvPRRA | smosh | 286 | ["Comedy"] | 194 | 20282464 | 4.49 | 80710 | 35408 | | 1 dmVU08zVpA | NBC | 670 | ["Entertainment"] | 165 | 16087899 | 4.79 | 30085 | 5945 | | RB- wUgnyGv0 | ChrisInScotland | 506 | ["Entertainment"] | 159 | 15712924 | 4.78 | 8222 | 1996 | | QjA5faZF1A8 | guitar90 | 308 | ["Music"] | 320 | 15256922 | 4.84 | 120506 | 38393 | | - _CSo1gOd48 | tasha | 190 | ["People","Blogs"] | 205 | 13199833 | 3.71 | 38045 | 9904 | | 49 IDp76kjPw | TexMachina | 381 | ["Comedy"] | 59 | 11970018 | 4.55 | 22579 | 5280 | | tYnn51C3X_w | CowSayingMoo | 516 | ["Music"] | 231 | 11823701 | 4.67 | 29479 | 10367 | | pv5zWaTEVkI | OkGo | 531 | ["Music"] | 184 | 11672017 | 4.83 | 42386 | 10082 | | D2kJZOfq7zk | mrWoot | 199 | ["People","Blogs"] | 185 | 11184051 | 4.82 | 42162 | 10819 | +
统计视频数量Top10的类别
统计每个类别有多少个视频，显示出包含视频最多的前10个类别。
要按照类别 group by 聚合，然后 count 组内的 videoid 个数。
一个视频对应一个或多个类别。所以如果要 group by 类别，需要先对类别进行 explode，然后再进行 count 即可。
按照视频数量排序，显示前 10 条。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 > select categories, videonum from (select categories, count (* ) videonum from (select videoid, categories from quickvideo_video_orc lateral view explode(category) quickvideo_video_orc_temp as categories) t1 group by categories) t2 order by videonum desc limit 10 ; + | categories | videonum | + | Music | 179049 | | Entertainment | 127674 | | Comedy | 87818 | | Film | 73293 | | Animation | 73293 | | Sports | 67329 | | Games | 59817 | | Gadgets | 59817 | | People | 48890 | | Blogs | 48890 | +
统计观看次数Top20的视频所属类别及对应视频数量
查询观看次数 Top20。
对这 20 条数据中的类别进行 explode。
根据分类进行分组计数。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 > select categories, count (* ) videonum from (select views, categories from (select views, category from quickvideo_video_orc order by views desc limit 20 ) t1 lateral view explode(category) quickvideo_video_orc_temp as categories) t2 group by categories; + | categories | videonum | + | Blogs | 2 | | Comedy | 6 | | Entertainment | 6 | | Music | 5 | | People | 2 | | UNA | 1 | +
统计观看次数Top50的视频所关联视频的类别排名
查询观看次数 Top50。
对这 50 条数据中的相关视频id进行 explode。
关联视频表，获得分类。
对分类进行 explode。
根据分类进行分组计数。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 > select categories, count (* ) categorynum from (select categories from (select t3.videoid, t3.category from (select relatedIds from (select relatedId, views from quickvideo_video_orc order by views desc limit 50 ) t1 lateral view explode(relatedId) quickvideo_video_orc_relatedId as relatedIds where relatedIds is not null ) t2 left join quickvideo_video_orc t3 on t2.relatedIds = t3.videoid) t4 lateral view explode(category) quickvideo_video_orc_category as categories) t5 group by categories order by categorynum desc ; + | categories | categorynum | + | Comedy | 237 | | Entertainment | 216 | | Music | 195 | | Blogs | 51 | | People | 51 | | Film | 47 | | Animation | 47 | | News | 24 | | Politics | 24 | | Games | 22 | | Gadgets | 22 | | Sports | 19 | | Howto | 14 | | DIY | 14 | | UNA | 13 | | Places | 12 | | Travel | 12 | | Animals | 11 | | Pets | 11 | | Autos | 4 | | Vehicles | 4 | +
统计每个视频类别中的观看次数Top10
创建新表，在视频表的基础上存放对分类进行 explode 后的数据。
插入数据到新表。
统计每个视频类别中的观看次数 Top10。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 > create table quickvideo_video_category( videoId string, uploader string, age int , categoryId string, length int , views int , rate float , rating int , comment int , relatedId array < string> ) row format delimited fields terminated by "\t" collection items terminated by "&" stored as orc; > insert into table quickvideo_video_category select videoId,uploader,age,categoryId,length,views,rate,rating,comment,relatedId from quickvideo_video_orc lateral view explode(category) quickvideo_video_orc_category as categoryId; > select categoryId, views, videoid from (select categoryId, views, videoid, dense_rank () over (partition by categoryId order by views desc ) drp from quickvideo_video_category) t1 where t1.drp <= 10 ; + | categoryid | views | videoid | + | Animation | 5840839 | sdUUx5FdySs | | Animation | 5147533 | 6 B26asyGKDo | | Animation | 3772116 | H20dhY01Xjk | | Animation | 3356163 | 55 YYaJIrmzo | | Animation | 3230774 | JzqumbhfxRo | | Animation | 3114215 | eAhfZUZiwSE | | Animation | 2866490 | h7svw0m- wO0 | | Animation | 2830024 | tAq3hWBlalU | | Animation | 2569611 | AJzU3NjDikY | | Animation | 2337238 | ElrldD02if0 | | Autos | 2803140 | RjrEQaG5jPM | | Autos | 2773979 | cv157ZIInUk | | Autos | 1832224 | Gyg9U1YaVk8 | | Autos | 1412497 | 6 GNB7xT3rNE | | Autos | 1347317 | tth9krDtxII | | Autos | 1262173 | 46 LQd9dXFRU | | Autos | 1013697 | pdiuDXwgrjQ | | Autos | 956665 | kY_cDpENQLE | | Autos | 942604 | YtxfbxGz1u4 | | Autos | 847442 | aCamHfJwSGU | ...
统计视频数量Top10的用户及他们上传的观看次数Top10的视频 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 > select uploader, uploadnum, videoId, views from (select uploader, uploadnum, videoId, views, dense_rank () over (partition by uploader order by views desc ) drp from (select t2.uploader, t2.uploadnum, t3.videoId, t3.views from (select uploader, uploadnum from (select uploader, count (* ) uploadnum from quickvideo_video_orc group by uploader) t1 order by uploadnum desc limit 10 ) t2 left join quickvideo_video_orc t3 on t2.uploader = t3.uploader) t4) t5 where t5.drp <= 10 order by uploadnum desc , uploader, views desc ; + | uploader | uploadnum | videoid | views | + | GRANHERMANO8 | 421 | PpH8LTg4ifo | 51872 | | GRANHERMANO8 | 421 | 8 vi6RNoOCL4 | 51829 | | GRANHERMANO8 | 421 | E0vb21MPEa4 | 36132 | | GRANHERMANO8 | 421 | pqAuixCAT7U | 30028 | | GRANHERMANO8 | 421 | gnjXMxB2jnM | 18115 | | GRANHERMANO8 | 421 | 4 _NUEKfy3kw | 14387 | | GRANHERMANO8 | 421 | ejoUmamV3N8 | 14364 | | GRANHERMANO8 | 421 | 3 CKkvlQZrwU | 13030 | | GRANHERMANO8 | 421 | igCG6a3vKy0 | 11460 | | GRANHERMANO8 | 421 | BRTNo_DCGt8 | 9857 | | CBS | 249 | 5 bry10DU8bo | 4085011 | | CBS | 249 | YgW7or1TuFk | 2899397 | | CBS | 249 | e0yX5Ns6gh4 | 2802303 | | CBS | 249 | NvQScRuZj9s | 2318862 | | CBS | 249 | pX5gRaxcviE | 1957631 | | CBS | 249 | 3 SCJLlSf21Y | 1779922 | | CBS | 249 | CuYD2cwMbpw | 1649511 | | CBS | 249 | DUaJLJlTZLk | 1333416 | | CBS | 249 | Vz8AuKmItwM | 1321534 | | CBS | 249 | c5P6MLiKEJI | 1247356 | ...
参考资料 Hive Data Types Hive Operators and User-Defined Functions (UDFs) Order, Sort, Cluster, and Distribute By HIVE Row Formats和SerDe LanguageManual DDL BucketedTables Hive之分区（Partitions）和桶（Buckets） LanguageManual Sampling Hive2.0函数大全(中文版) Hive 官方手册翻译 – Hive DML(数据操纵语言)
