学习目标: . 了解SELECT句式的语法,能够描述SELECT句式中不同子句的作用并查询数据 表中的数据。 . 掌握Hive运算符的使用,能够在SELECT 句式中灵活使用关系运算符、算术运算 符、逻辑运算符和复杂运算符查询数据表中的数据。 . 熟悉公用表表达式的使用,能够使用公用表表达式基于临时结果集进行查询。 . 掌握分组操作,能够对数据表进行分组查询。 . 掌握排序操作,能够对数据表的查询结果进行排序。 . 熟悉UNION 语句使用,能够将多个SELECT句式的查询结果集进行合并的操作。 . 熟悉JOIN 语句的使用,能够将不同数据表的数据进行合并的操作。 . 熟悉抽样查询的使用,能够灵活运用SELECT句式实现随机抽样查询、分桶抽样查 询和数据块抽样查询的操作。 数据查询语言(DataQueryLanguage,DQL)是用于从数据库中查询数据的计算机语 言。在Hive中可以通过HiveQL语言查询数据,查询数据的结果会存储在结果集中。本章 详细讲解Hive数据查询语言的相关操作。 5.1 SELECT句式分析 Hive使用SELECT句式实现查询,在第4章对SELECT 句式的基本查询语法进行了 讲解,不过在实际使用中,仅对基础查询语法进行学习显然是不够的。本节详细讲解 SELECT句式的完整格式,有关SELECT句式的完整语法格式如下。 [WITH CommonTableExpression (, CommonTableExpression)*] 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 number] Hive数据仓1 36 库应用 上述语法的具体讲解如下。 . WITHCommonTableExpression:可选,表示公用表达式。 . ALL|DISTINCT:可选,默认是ALL,即显示全部查询结果;若使用DISTINCT, 则显示去重后的查询结果,去重是指去除重复数据。 .select_expr:表示查询语句的表达式。 .table_reference:表示实际的查询对象,可以是数据表、视图或子查询。 . WHEREwhere_condition:可选,指定查询条件。 . GROUPBYcol_list:可选,用于对查询结果根据指定列col_list进行分组处理。 . ORDERBYcol_list:可选,用于对查询结果根据指定列col_list进行排序处理。 . CLUSTERBYcol_list|[DISTRIBUTEBYcol_list][SORTBYcol_list]:可选, 用于排序处理,CLUSTERBY的功能等于DISTRIBUTEBY+ SORTBY。 . LIMITnumber:可选,用于限制查询结果返回的行数。 接下来,在虚拟机Node_03中使用Hive客户端工具Beeline,远程连接虚拟机Node_02 的HiveServer2服务操作Hive,讲解SELECT句式的实际使用,具体操作步骤如下。 (1)在虚拟机Node_02的目录/export/data/hive_data下执行“viemployess.txt”命令, 创建员工信息数据文件employess.txt,在数据文件employess.txt中添加如下内容。 Lilith Hardy,30,6000,50,Finance Department Byron Green,36,5000,25,Personnel Department Yvette Ward,21,4500,15.5, Arlen Esther,28,8000,20,Finance Department Rupert Gold,39,10000,66,R&D Department Deborah Madge,41,6500,0,R&D Department Tim Springhall,22,6000,36.5,R&D Department Olga Belloc,36,5600,10,Sales Department Bruno Wallis,43,6700,0,Personnel Department Flora Dan,27,4000,35,Sales Department 上述内容中,每一行数据从左到右依次表示员工姓名、年龄、薪资、迟到扣款和所属 部门。 (2)将虚拟机Node_02目录/export/data/hive_data下的数据文件employess.txt上传 到HDFS的/hive_data/employess目录,上传文件前需要在HDFS创建目录/hive_data/ employess,有关创建目录和上传数据文件的命令如下。 #创建目录 $ hdfs dfs -mkdir -p /hive_data/employess #上传数据文件 $ hdfs dfs -put /export/data/hive_data/employess.txt /hive_data/employess (3)根据数据文件employess.txt的数据格式,在数据库hive_database中创建员工信 息表employess_table,该表中包含列staff_name(员工姓名)、staff_age(员工年龄)、staff_ salary(员工薪资)、late_deduction(迟到扣款)和staff_dept(员工所属部门),在虚拟机Node _03的Hive客户端工具Beeline中执行如下命令创建员工信息表employess_table。 第5章 Hive数据查询语言1 37 CREATE EXTERNAL TABLE IF NOT EXISTS hive_database.employess_table( staff_name STRING, staff_age INT, staff_salary FLOAT, late_deduction FLOAT, staff_dept STRING ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' STORED AS textfile LOCATION '/hive_data/employess'; 从上述命令可以看出,创建的员工信息表employess_table 为外部表,并且通过 LOCATION 子句指定员工信息表employess_table在HDFS的数据存储路径/hive_data/ employess,由于在创建员工信息表之前将数据文件employess.txt上传到HDFS的/hive_ data/employess目录,所以在创建员工信息表employess_table 同时会加载数据文件 employess.txt。 (4)查询员工信息表employess_table包含几种部门,具体命令如下。 SELECT DISTINCT staff_dept from hive_database.employess_table; 上述命令中,通过DISTINCT子句对列staff_dept(员工所属部门)的数据进行去重,上 述命令的执行效果如图5-1所示。 图5-1 查询员工信息表employess_table包含几种部门 从图5-1可以看出,员工信息表employess_table中包含5种部门,分别是Finance Department、PersonnelDepartment、R&DDepartment、SalesDepartment和一个没有名称 的部门,这是因为数据文件中存在空值导致。 多学一招:HAVING子句 HAVING子句的用法与WHERE子句的用法一致,都是在查询语句中指定查询条件, Hive数据仓1 38 库应用 不同的是HAVING子句中可以使用聚合函数,并且HAVING 子句必须配合GROUPBY 子句一同使用,而WHERE 子句中不能使用聚合函数,例如查询每个部门平均薪资大于 5000的部门,命令如下。 SELECT staff_dept FROM employess_table GROUP BY staff_dept HAVING AVG(staff_salary) > 5000; 5.2 Hive运算符 Hive内置的运算符主要分为4类,分别是关系运算符、算术运算符、逻辑运算符和复杂 运算符,本节针对这4类运算符进行详细讲解。 5.2.1 关系运算符 关系运算符通常在SELECT句式的WHERE子句中使用,用来比较两个操作数,下面 通过表5-1来介绍Hive内置的常用关系运算符。 表5-1 Hive内置的常用关系运算符 关系运算符支持数据类型示 例示例描述 = 所有基本数据类型A=B 表示如果A与B相等,则为true,否则为false != 所有基本数据类型A! =B 表示如果A 与B 不相等,则为true,否则 为false < 所有基本数据类型A 所有基本数据类型A>B 表示如果A大于B,则为true,否则为false >= 所有基本数据类型A>=B 表示如果A 大于或等于B,则为true,否则 为false ISNULL 所有基本数据类型AISNULL 表示如果A为NULL,则为true,否则为false ISNOTNUL 所有基本数据类型AISNOTNULL 表示如果A 不为NULL,则为true,否则 为false [NOT]LIKE 字符串类型A [NOT]LIKEB 表示通过SQL正则匹配A 与B是否相等,若 相等,则为true,反之为false;使用关键字 NOT匹配A与B不相等 RLIKE 字符串类型ARLIKEB 表示通过Java正则匹配A与B相等的查询结 果,若相等,则为true,反之为false 在表5-1中,若关系运算符比较操作数的结果为true,则返回查询结果,否则返回空。 接下来,在虚拟机Node_03中使用Hive客户端工具Beeline,远程连接虚拟机Node_02 的HiveServer2服务操作Hive,讲解关系运算符=、[NOT]LIKE和RLIKE的实际使用, 具体操作步骤如下。 (1)查询员工信息表employess_table中员工年龄为36岁的员工信息,具体命令如下。 第5章 Hive数据查询语言1 39 SELECT * FROM hive_database.employess_table WHERE staff_age=36; 上述命令使用关系运算符“=”比较列staff_age与36相等的值,返回比较结果为true 的查询结果。上述命令在Hive客户端工具Beeline中的执行效果如图5-2所示。 图5-2 员工年龄为36岁的员工信息 从图5-2可以看出,员工信息表employess_table中有两个员工的年龄为36岁,员工姓 名分别是ByronGreen和OlgaBelloc。 (2)查询员工信息表employess_table中部门PersonnelDepartment的员工信息,具体 命令如下。 SELECT * FROM hive_database.employess_table WHERE staff_dept LIKE "Per%"; 上述命令使用关系运算符LIKE比较列staff_dept与Per%相等的字符串,返回比较结果 为true的查询结果,其中“%”表示通配符,用来匹配任意字符,因此Per%表示字符串开头为 Per的任意字符串。上述命令在Hive客户端工具Beeline中的执行效果如图5-3所示。 图5-3 部门PersonnelDepartment的员工信息 从图5-3可以看出,员工信息表employess_table中部门PersonnelDepartment包含两 个员工,员工姓名分别是ByronGreen和BrunoWallis。 (3)查询员工信息表employess_table中员工姓名首字母为A 或D 的员工信息,具体 命令如下。 SELECT * FROM hive_database.employess_table WHERE staff_name RLIKE "^A|^D.*"; 上述命令中,通过关系运算符RLIKE比较列staff_name与Java正则表达式“^A|^D.*” 相等的字符串,这里使用的正则表达式表示匹配字符串首字母以A 或D开头的字符串。上 述命令在Hive客户端工具Beeline中的执行效果如图5-4所示。 Hive数据仓1 40 库应用 图5-4 员工姓名首字母为A 或D 的员工信息 从图5-4可以看出,员工信息表employess_table中有两名员工姓名首字母为A 或D。 多学一招:Hive中的NULL Hive的数据存储在HDFS时,默认会将空值转换为字符串,因此使用ISNULL或者 ISNOTNULL关系运算符时无法匹配到表中存在的空值,需要通过数据表的属性 serialization.null.format 格式化表中的空值,例如,执行“altertablehive_database. employess_tablesetserdeproperties ('serialization.null.format' = ' ');”命令格式化表 employess_table中的空字符串为NULL,或者执行“altertablehive_database.employess_ tablesetserdeproperties(s' erialization.null.format' = 'NULL');”命令格式化表employess _table中的字符串"NULL"为NULL。 也可以在创建表时使用“NULLDEFINEDAS ''”命令指定表中空值的序列化方式,具 体实例代码如下。 CREATE TABLE IF NOT EXISTS table1( col1 INT , col2 STRING ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' COLLECTION ITEMS TERMINATED BY '_' MAP KEYS TERMINATED BY ':' LINES TERMINATED BY '\n' NULL DEFINED AS '' STORED AS textfile TBLPROPERTIES("comment"="This is a managed table"); 5.2.2 算术运算符 算术运算符是用来计算两个数值的操作,下面通过表5-2来介绍Hive内置的常用算术 运算符。 表5-2 Hive内置的常用算术运算符 算术运算符支持数据类型示 例示例描述 + 所有数字数据类型A + B A加B的计算结果 - 所有数字数据类型A - B A减B的计算结果 续表 第5章 Hive数据查询语言1 41 算术运算符支持数据类型示 例示例描述 * 所有数字数据类型A * B A乘以B的计算结果 / 所有数字数据类型A/B A除以B的计算结果 % 所有数字数据类型A % B A除以B产生余数的计算结果 & 所有数字数据类型A & B A和B按位与的计算结果 | 所有数字数据类型A|B A和B按位或的计算结果 ^ 所有数字数据类型A ^ B A和B按位异或的计算结果 ~ 所有数字数据类型~A A按位非的计算结果 接下来,在虚拟机Node_03中使用Hive客户端工具Beeline,远程连接虚拟机Node_02 的HiveServer2服务操作Hive,讲解算术运算符“-”和“/”的实际使用,具体操作步骤如下。 (1)计算员工信息表employess_table中所有员工的实际工资,具体命令如下。 SELECT staff_name, staff_salary-late_deduction actual_salary FROM hive_database.employess_table; 上述命令中,通过算术运算符“-”计算员工薪资(staff_salary)减迟到扣款(late_ deduction)得出每个员工的实际工资,指定实际工资的列名为actual_salary。上述命令在 Hive客户端工具Beeline中的执行效果如图5-5所示。 图5-5 计算员工实际工资 (2)计算员工信息表employess_table中每位员工每天的薪资,以单月工作日为20天 计算,具体命令如下。 Hive数据仓1 42 库应用 SELECT staff_name, staff_salary/20 everyday_salary FROM hive_database.employess_table; 上述命令中,通过算术运算符“/”计算员工薪资(staff_salary)除以20得出每位员工每 天的薪资,指定每天薪资的列名为everyday_salary。上述命令在Hive客户端工具Beeline 中的执行效果如图5-6所示。 图5-6 计算每位员工每天的薪资 5.2.3 逻辑运算符 逻辑运算符(又称为逻辑联结词)可以将两个或多个关系表达式合并为一个表达式或者 反转表达式的逻辑。下面通过表5-3来介绍Hive内置的常用逻辑运算符。 表5-3 Hive内置的常用逻辑运算符 逻辑运算符支持数据类型示 例示例描述 AND Boolean数据类型AANDB 逻辑与,表达式A和表达式B必须都为true, 合并后的整体表达式结果才为true && Boolean数据类型A&& B 逻辑与,含义与逻辑运算符AND相同 OR Boolean数据类型AORB 逻辑或,表达式A 或表达式B 至少一个为 true,才能使合并后的整体表达式结果为true || Boolean数据类型A||B 逻辑或,含义与逻辑运算符OR相同 ! Boolean数据类型! A 逻辑非,反转表达式A的“真相”,若表达式A 为true则反转后的结果为false NOT Boolean数据类型NOTA 逻辑非,含义与逻辑运算符! 相同 接下来,在虚拟机Node_03中使用Hive客户端工具Beeline,远程连接虚拟机Node_02 的HiveServer2服务操作Hive,查询员工信息表employess_table中薪资大于或等于5000, 第5章 Hive数据查询语言1 43 并且薪资小于或等于8000的员工信息,具体命令如下。 SELECT * FROM hive_database.employess_table WHERE staff_salary >= 5000 AND staff_salary <= 8000; 上述命令通过逻辑运算符AND 将表达式“staff_salary >= 5000”和表达式“staff_ salary<=8000”合并为一个整体表达式。上述命令在Hive客户端工具Beeline中的执行 效果如图5-7所示。 图5-7 薪资大于或等于5000,并且薪资小于或等于8000的员工信息 从图5-7可以看出,员工信息表employess_table中有7名员工薪资大于或等于5000, 并且薪资小于或等于8000。 5.2.4 复杂运算符 复杂运算符用于操作Hive中集合数据类型的列,集合数据类型包括ARRAY、MAP或 STRUCT,下面通过表5-4来介绍Hive内置的复杂运算符。 表5-4 Hive内置的复杂运算符 复杂运算符支持数据类型描 述 A[n] ARRAY 返回A(ARRAY)的第n个元素的值 M[key] MAP 返回M(MAP)中指定key的value S.x STRUCT 返回S(STRUCT)中x字段的值 接下来,在虚拟机Node_03中使用Hive客户端工具Beeline,远程连接虚拟机Node_02 的HiveServer2服务操作Hive,讲解复杂运算符的实际使用,具体操作步骤如下。 (1)在虚拟机Node_02的目录/export/data/hive_data下执行“vistudent_exam.txt”命 令,创建学生考试成绩文件student_exam.txt,在数据文件student_exam.txt中添加如下 内容。 Mandy,Peking University-Wuhan University-Nankai University,Chemistry:90-Physics:98- Biology:83,126-135-140 Jerome,Tsinghua University-Fudan University-Nanjing University,History:89-Politics:92- Geography:87,130-116-128 Delia,Nanjing University-Wuhan University-Nankai University,Chemistry:87-Physics:95- Biology:73,102-123-112 Hive数据仓1 44 库应用 Ben,Tianjin Universit-Peking University-Fudan University,Chemistry:92-Physics:88- Biology:79,98-142-106 Carter,Tsinghua University-Fudan University-Tianjin Universit,History:90-Politics:91- Geography:80,109-111-140 Vivian,Fudan University-Nanjing University-Nankai University,Chemistry:83-Physics:86- Biology:90,120-140-132 上述内容中,每一行数据从左到右依次表示学生姓名、意向大学、文综(政治、历史、地 理)/理综(生物、化学、物理)成绩和综合(语、数、外)成绩。 (2)将虚拟机Node_02目录/export/data/hive_data下的数据文件student_exam.txt 上传到HDFS的/hive_data/student_exam 目录,上传文件前需要在HDFS上创建目录 /hive_data/student_exam,有关创建目录和上传数据文件的命令如下。 $ hdfs dfs -mkdir -p /hive_data/student_exam $ hdfs dfs -put /export/data/hive_data/student_exam.txt /hive_data/student_exam (3)根据数据文件student_exam.txt的数据格式,在数据库hive_database中创建学生 考试成绩表student_exam_table,该表中包含列student_name(学生姓名)、intent_ university(意向大学)、humanities_and_sciences(文/理综成绩)和comprehensive(综合成 绩),在虚拟机Node_03的客户端工具Beeline中执行如下命令。 CREATE EXTERNAL TABLE IF NOT EXISTS hive_database.student_exam_table( student_name STRING, intent_university ARRAY, humanities_or_sciences MAP, comprehensive STRUCT ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' COLLECTION ITEMS TERMINATED BY '-' MAP KEYS TERMINATED BY ':' LINES TERMINATED BY '\n' STORED AS textfile LOCATION '/hive_data/student_exam'; 上述命令中,创建的学生考试成绩表student_exam_table 为外部表,并且通过 LOCATION 子句指定学生考试成绩表student_exam_table在HDFS的数据存储路径 /hive_data/student_exam,因此在创建学生考试成绩表student_exam_table同时会加载数 据文件student_exam.txt。学生考试成绩表student_exam_table包含3个集合数据类型的 列,分别是intent_university、humanities_or_sciences和comprehensive。 (4)查询学生考试成绩表student_exam_table中所有学生的第一意向大学,具体命令如下。 SELECT student_name, intent_university[0] first FROM hive_database.student_exam_table;