数据仓库Hive应用实战- 课件 模块5 分析导出仓库数据

理解SELECT…FROM语句的详细语法理解WHERE语句的详细语法及其作用能使用SELECT…FROM语句熟练查询全表、指定列，集合中元素能使用常用函数结合SELECT…FROM玉壶熟练完成特定的查询计算能使用WHERE语句中谓词操作符和逻辑运算符筛选出符合条件的查询结果能使用Limit语句结合SELECT..FROM语句熟练输出查询结果的指定列智能选址目的Hive仓库中创建好了ODS层和DWD层仓库，并且在ODS层表中存储了各业务数据，在DWD层中只有表定义而无表数据，在此任务中要分析并获取到DWD层的行业信息分类表数据、基站基础属性汇总表数据和基站指标汇总表数据。本任务以“学生信息系统”项目、“大数据商业智能选址”项目为实操载体，完成Hive查询分析语句的使用，包括SELECT语句查询指定字段、LIMIT语句限定返回的查询结果、CASE语句对查询结果列数据进行条件判断、WHERE语句对查询进行条件过滤、GROUPBY语句实现字段分组、JOIN语句实现多张表连接等学习目标。任务1分析仓库数据5.1.1SELECT语句 SELECT是SQL中的射影算子，FROM子句标识了从哪个表、视图或嵌套查询中选择记录。任务1分析仓库数据5.1.1

基本语法SELECT[ALL|DISTINCT]select_expr,select_expr,...FROMtable_reference[WHEREwhere_condition][GROUPBYcol_list[HAVINGcondition]][ORDERBYcol_list][CLUSTERBYcol_list|[DISTRIBUTEBYcol_list][SORTBYcol_list]][LIMIT[offset,]rows];任务1分析仓库数据5.1.1基本语法–查询全表全表查询即查询表中所有的列的所有行数据，如下例所示：【例5-1】查询studentdb数据库中student表中的所有列hive(studentdb)>select*fromstudent;任务1分析仓库数据5.1.1基本语法-查询指定列、指定列别名1）查询指定列【例5-2】查询studentdb数据库中student表中的stname和class列。SELECTstname,classFROMstudent;任务1分析仓库数据5.1.1基本语法-查询指定列、指定列别名2）查询指定列并指定列别名：

列别名，通常用于重命名一个列，便于计算，别名可以紧跟列名，也可以在列名和别名之间加入关键字as。【例5-3】查询studentdb数据库中student表中的course_scores并指定为别名cs。SELECTcourse_scoresAScsFROMstudent;任务1分析仓库数据5.1.1基本语法-查询指定列、指定列别名3）查询处理后产生的新列并指定别名

在查询时经常使用运算符或者函数对某些数据进行处理，处理后会新产生结果列，这个结果列在原表中是不存在的，通常有必要给这些新产生的列起一个别名。【例5-4】查询studentdb数据库中student表中学生总人数SELECTcount(stname)AStotalFROMstudent;任务1分析仓库数据5.1.1基本语法-查询集合数据类型当用户选择的列是集合数据类型时，Hive会使用JSON语法应用于输出。1）查询Array元素 student表中的roommate列为一个string类型的数组，数组下标从0开始，其值使用一个被括在[...]内的以逗号分隔的列表表示。

查询引用一个不存在的元素将会返回NULL。提取出的STRING数据类型的值将不再加引号。注意：集合中的字符串元素有引号，而基本数据类型STRING的列值没有引号任务1分析仓库数据5.1.1基本语法-查询集合数据类型当用户选择的列是集合数据类型时，Hive会使用JSON语法应用于输出。1）查询Array元素

【例5-5】查询studentdb数据库中student表中每条数据的stname和roommate列的第一个元素。SELECTstname,roommate[0]ASfirst_roommateFROMstudentlimit2;任务1分析仓库数据5.1.1基本语法-查询集合数据类型2）查询Map元素

student表中course_scores列是MAP数据类型，其使用JSON格式来表示MAP，即使用一个被括在{}内的以逗号分隔的“键:值”对列表表示。

查询一个MAP元素，可以使用ARRAY数组查询元素的语法，但[]中使用的是键而不是整数索引。任务1分析仓库数据5.1.1基本语法-查询集合数据类型2）查询Map元素

【例5-6】查询studentdb数据库中student表中的stname和每位学生的Hive课程成绩。SELECTstname,course_scores['Hive']ASHive_scoreFROMstudentlimit2;

任务1分析仓库数据5.1.1基本语法-查询集合数据类型3）查询Struct元素 student表中address列是STRUCT数据类型，其也是使用JSON格式表示数据。

查询STRUCT中的某个元素，应使用“点”符号，类似于“表名.列名”的用法。

WHERE子句中同样可以使用这些引用方式，将在后续“WHERE语句”中讨论。任务1分析仓库数据5.1.1基本语法-查询集合数据类型3）查询Struct元素

【例5-7】查询studentdb数据库中student表中的stname和每个学生来自的city信息

SELECTstname,address.cityFROMstudentLIMIT2;任务1分析仓库数据5.1.1基本语法-算数运算符 Hive中支持所有典型的算术运算符，算术运算符接受任意的数值类型。如果数据类型不同，两种类型中值范围较小的那个数据类型将转换为其他范围更广的数据类型。范围更广在某种意义上就是指一个类型具有更多的字节从而可以容纳更大范围的值。例如，对于INT类型和BIGINT类型运算，INT类型会转换提升为BIGINT类型；对于INT类型和FLOAT类型运算，INT类型会转换提升为FLOAT类型。下表5-1描述了在Hive中可用的算术运算符：任务1分析仓库数据5.1.1基本语法-算数运算符

运算符操作描述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|B所有数字类型A和B的按位或结果A^B所有数字类型A和B的按位异或结果~A所有数字类型A按位取反的结果表5-1算术运算符任务1分析仓库数据5.1.1基本语法-算数运算符

【例5-8】求student表中每个学生的课程平均分。

SELECTstname,(course_scores['Hive']+course_scores['Python'])/2ASav_scoreFROMstudentLIMIT2;任务1分析仓库数据5.1.1基本语法-常用函数 Hive中有很多函数，将在后续任务中专门讲述各函数，这里只介绍常用的求总行数count函数、求最大值max函数、求最小值min函数、求总和sum函数、求平均值avg函数。

注意，默认在执行count(*)操作时返回结果为0。其原因是hive中设置项pute.query.using.stats，其默认值为true，表示读取表级统计信息中的数据；设置为false，将不读取统计数据，而执行MR任务。任务1分析仓库数据5.1.1基本语法-常用函数

【例5-9】使用count函数求出student表中总的数据行数。

setpute.query.using.stats=false;

SELECTcount(*)countFROMstudent;

任务1分析仓库数据5.1.1基本语法-常用函数【例5-10】使用count函数求出student表中参加了Hive课程学习的人数。SELECTcount(course_scores['Hive'])hive_scoreFROMstudent;

任务1分析仓库数据5.1.1基本语法-常用函数【例5-11】使用count函数统计出学生是来自几个不同的城市。SELECTcount(DISTINCTaddress.city)citysFROMstudent;DISTINCT关键字用于过滤掉重复记录只保留一条。因此，student表中相同的city在统计时只算一条。任务1分析仓库数据5.1.1基本语法-常用函数【例5-12】使用avg函数求出student表中Hive课程和Python课程的平均分。SELECTavg(course_scores['Hive'])ASavg_Hive, avg(course_scores['Python'])ASavg_PythonFROMstudent;任务1分析仓库数据5.1.1基本语法-常用函数【例5-13】使用min函数和max函数求出student表中Python课程的最低分和最高分。SELECTmin(course_scores['Python'])ASmin_Python, max(course_scores['Python'])ASmax_PythonFROMstudent;任务1分析仓库数据5.1.2

LIMIT语句使用查询会返回表中符合查询条件的所有数据。那如何只显示出查询结果中的某几条数据呢？通过LIMIT子句可以限制返回的行数。显示查询结果中限定的前n条数据，或从索引n开始之后的m条数据语法：SELECTcol_name,[col_name]FROMtable_nameLIMITn[,m];【例5-14】查询student表中的第3/4/5条数据的stname和class。SELECTstname,classFROMstudentLIMIT2,3;任务1分析仓库数据5.1.3SELECT嵌套语句使用avg函数能分别统计student表中每个班级Hive课程的平均分。当更进一步，需要获取Hive课程平均分最高的班级时，如何实现这个功能呢？以下按照先求班级平均分后再求最大值的顺序执行发生了错误：输入语句：hive(studentdb)>selectclass,max(avg(course_scores['Hive'])asavg_Hive)fromstudentgroupbyclass;结果报错：FAILED:ParseExceptionline1:16cannotrecognizeinputnear'max''(''count'inexpressionspecification任务1分析仓库数据5.1.3SELECT嵌套语句原因和解决办法： Hive和MySQL一样，不支持聚合函数的嵌套，我们需要使用嵌套查询任务1分析仓库数据5.1.3SELECT嵌套语句【例5-15】使用嵌套查询统计student表中各班级Hive课程平均分的最高分。FROM(SELECTclass,avg(course_scores['Hive'])ASavg_HiveFROMstudentdb.studentGROUPBYclass)ASrsSELECTmax(rs.avg_Hive)ASmax_avg_Hive;代码中首先将查询语句selectclass,avg(course_scores['Hive'])asavg_Hivefromstudentgroupbyclass所查询到的班级及个班级的平均分存入临时结果表rs中，然后再对临时结果表使用max函数获取各班级平均分中的最大值。任务1分析仓库数据5.1.3SELECT嵌套语句【例5-16】使用嵌套查询统计student表中来自不同城市的学生Python课程平均分的最高分。FROM(SELECTaddress.city,avg(course_scores['Python'])ASavg_PythonFROMstudentdb.studentGROUPBYaddress.city)ASrsSELECTmax(rs.avg_Python)ASmax_avg_Python;任务1分析仓库数据5.1.4CASE---WHEN---THEN---ELSE--END语句CASE…WHEN…THEN…ELSE…END语句和if语句类似，用来处理单个列的查询结果。可以对某列的结果进行条件判断。语法如下：SELECTcol_name_1,col_name_2,...CASE WHENcol_name_n满足条件1THEN结果1 WHENcol_name_n满足条件2THEN结果2 ... ELSE结果nENDas列别名FROMtable_name；任务1分析仓库数据5.1.4CASE---WHEN---THEN---ELSE--END语句【例5-17】查询student表中的stname、class及Hive课程等级，课程等级划分条件为课程分数大于等于90的为“A”，课程分数大于等于80而小于90的为“B”，课程分数大于等于60而小于80的为“C”，其余的为“D”。SELECTstname,class,CASE WHENcourse_scores['Hive']>=90THEN'A’ WHENcourse_scores['Hive']<90ANDcourse_scores['Hive']>=80THEN'B’ WHENcourse_scores['Hive']<80ANDcourse_scores['Hive']>=60THEN'C’ ELSE'D'ENDASHive_levelFROMstudentdb.student;任务1分析仓库数据5.1.5WHERE语句如果使用SELECT语句但不使用WHERE子句在表中查询数据，则会获取表中的所有行记录。但在很多场景下，只需要查询一些特定的符合条件数据。比如学生表里保存的是历年学生的数据，而通常只需要查询当前学年学期的学生数据，这时就需要使用WHERE语句，将不满足条件的行过滤掉。SELECT语句用于选取字段，WHERE语句用于过滤条件，两者结合使用可以查找到符合过滤条件的记录。和SELECT语句一样，在介绍WHERE语句之前已经在很多简单例子中使用过该语句了。任务1分析仓库数据5.1.5WHERE语句WHERE语句使用谓词表达式，当有几种谓词表达式时使用AND和OR相连接。当谓词表达式计算结果为true时相应的行将被保留并输出。注意，WHERE子句必须紧随FROM子句。【例5-18】查询出student表中BigData1802班级的所有学生信息。SELECT*FROMstudentWHEREclass='BigData1802';任务1分析仓库数据5.1.5WHERE语句（1）谓词操作符WHERE语句使用谓词表达式，当谓词表达式的结果为false时，该行会被过滤掉，这些操作符同样可以用于JOIN…ON和HAVING语句中。下表列出了hive中支持的谓词操作符。表5-2谓词操作符操作符支持的数据类型描述A=B基本数据类型如果A等于B则返回TRUE，反之返回FALSEA<=>B基本数据类型如果A和B都为NULL，则返回TRUE，其他的和等号（=）操作符的结果一致，如果任一为NULL则结果为NULL任务1分析仓库数据5.1.5WHERE语句表5-2谓词操作符操作符支持的数据类型描述A<>B,A!=B基本数据类型A或者B为NULL则返回NULL；如果A不等于B，则返回TRUE，反之返回FALSEA<B基本数据类型A或者B为NULL，则返回NULL；如果A小于B，则返回TRUE，反之返回FALSEA<=B基本数据类型A或者B为NULL，则返回NULL；如果A小于等于B，则返回TRUE，反之返回FALSEA>B基本数据类型A或者B为NULL，则返回NULL；如果A大于B，则返回TRUE，反之返回FALSE任务1分析仓库数据5.1.5WHERE语句表5-2谓词操作符操作符支持的数据类型描述A>=B基本数据类型A或者B为NULL，则返回NULL；如果A大于等于B，则返回TRUE，反之返回FALSEA[NOT]BETWEENBANDC基本数据类型如果A，B或者C任一为NULL，则结果为NULL。如果A的值大于等于B而且小于或等于C，则结果为TRUE，反之为FALSE。如果使用NOT关键字则可达到相反的效果。AISNULL所有数据类型如果A等于NULL，则返回TRUE，反之返回FALSE任务1分析仓库数据5.1.5WHERE语句表5-2谓词操作符操作符支持的数据类型描述AISNOTNULL所有数据类型如果A不等于NULL，则返回TRUE，反之返回FALSEIN(数值1,数值2)所有数据类型使用IN运算显示列表中的值A[NOT]LIKEBSTRING类型B是一个SQL下的简单正则表达式，如果A与其匹配的话，则返回TRUE；反之返回FALSE。B的表达式说明如下：‘x%’表示A必须以字母‘x’开头，‘%x’表示A必须以字母’x’结尾，而‘%x%’表示A包含有字母’x’,可以位于开头，结尾或者字符串中间。类似地，下划线‘_’匹配单个字符。B必须要和整个字符串A相匹配才行。如果使用NOT关键字则可达到相反的效果。任务1分析仓库数据5.1.5WHERE语句表5-2谓词操作符操作符支持的数据类型描述ARLIKEB,AREGEXPBSTRING类型B是一个正则表达式，如果A与其匹配，则返回TRUE；反之返回FALSE。匹配使用的是JDK中的正则表达式接口实现的，因为正则也依据其中的规则。例如，正则表达式必须和整个字符串A相匹配，而不是只需与其子字符串匹配。注意，没有“A==B”的语法，Hive中使用“=”表示等于，而不是“==”。任务1分析仓库数据5.1.5WHERE语句【例5-19】查询出student表中address居住城市为“Zhuzhou”的stname和class。SELECTstname,classFROMstudentWHEREaddress.city='Zhuzhou';任务1分析仓库数据5.1.5WHERE语句【例5-20】查询出student表中Hive课程分数在70到90的之间的学生姓名及Hive课程分数。SELECTstname,course_scores['Hive']Hive_scoreFROMstudentWHEREcourse_scores['Hive']BETWEEN70AND90;任务1分析仓库数据5.1.5WHERE语句【例5-21】查询出student表中Python课程分数是95或85或75的学生姓名及Python课程分数。SELECTstname,course_scores['Python']Python_scoreFROMstudentWHEREcourse_scores['Python']IN(95,85,75);任务1分析仓库数据5.1.5WHERE语句【例5-22】查询出student表中除了Network1801班和Network1802班以外的学生信息。SELECTstname,classFROMstudentWHEREclassNOTIN('Network1801','Network1802');任务1分析仓库数据5.1.5WHERE语句【例5-23】查询出student表中有未参加课程考试的学生姓名及班级，即课程分数为NULL，则表明该生未参加该课程考试。SELECTstname,class,course_scoresFROMstudentWHEREcourse_scores['Hive']ISNULLORcourse_scores['Python']ISNULL;任务1分析仓库数据5.1.5WHERE语句2）逻辑运算符运算符是逻辑运算符即为逻辑表达式，逻辑表达式返回TRUE或FALSE。Hive支持的逻辑运算符如下表5-3。表5-3逻辑运算符运算符操作描述AANDBbooleanTRUE，如果A和B都是TRUE，否则FALSE。A&&Bboolean类似于AANDB。AORBbooleanTRUE，如果A或B或两者都是TRUE，否则FALSE。A||Bboolean类似于

AORB。NOTAbooleanTRUE，如果A是FALSE，否则FALSE。!Aboolean类似于

NOTA。任务1分析仓库数据5.1.5WHERE语句【例5-24】查询student表中Network1802班来自“Hunan”省的学生姓名和所在省份。SELECTstname,vinceFROMstudent

WHEREclass='Network1802'ANDvince='Hunan';【例5-25】查询student表中Hive课程大于80分，或者Python课程大于80分的学生姓名及Hive和Python两门课程的分数。SELECTstname,course_scores['Hive']Hive_score,course_scores['Python']Python_scoreFROMstudentWHEREcourse_scores['Hive']>80ORcourse_scores['Python']>80;任务1分析仓库数据5.1.5WHERE语句（3）Like和RLike1、like表5-2描述了LIKE和RLIKE谓词操作符。LIKE是一个标准的SQL操作符，其可以让我们通过字符串的开头或结尾，以及指定特定的子字符串，或当子字符串出现在字符串内的任何位置时进行匹配。使用LIKE运算选择类似的值，选择条件可以包含字符或数字，比如：%代表零个或多个字符(任意个字符)。_代表一个字符。任务1分析仓库数据5.1.5WHERE语句【例5-26】查询出student表中姓名以“Wang”开头的学生信息。SELECTstnameFROMstudentWHEREstnameLIKE'Wang%';【例5-27】查询出student表中姓名的第二个字母为“h”的学生信息。SELECTstnameFROMstudentWHEREstnameLIKE'_h%';任务1分析仓库数据5.1.5WHERE语句（2）rlikeRLIKE子句是Hive中这个功能的一个扩展，其可以通过更强大的Java正则表达式语言来指定匹配条件。【例5-28】查询student表中姓名中含有‘a’字母的学生信息。SELECTstnameFROMstudentWHEREstnameRLIKE'[a]';任务1分析仓库数据5.1.5WHERE语句【例5-29】查询student表中居住城市包含‘zhou’和‘sha’字符的学生信息。SELECTstname,address.cityFROMstudentWHEREaddress.cityRLIKE'.*(zhou|sha).*';任务1分析仓库数据5.1.5WHERE语句上例中关键字RLIKE后面的的字符串表达如下含义:字符串中的点号(.)表示和任意的字符匹配，星号(*)表示重复“左边的字符串”零次到无数次。表达式(x|y)表示和x或者y匹配。不过，‘zhou’或‘sha’字符串前可能没有其他任何字符，而且它们后面也可能不含有其他任何字符。当然，上例中也可以通过2个LIKE子句来改写为如下所示：SELECTstname,address.cityFROMstudentWHEREaddress.cityLIKE'%zhou%'ORaddress.cityLIKE'%sha%';任务1分析仓库数据5.1.6GROUPBY语句1、GROUPBY语句GROUPBY从字面上理解，GROUP表示分组，BY后面跟字段名，且可以是多个字段名，表示根据哪些字段进行分组。该语句通常和聚合函数一起使用，按照一个或者多个列队结果进行分组，然后对每个组执行聚合操作。任务1分析仓库数据5.1.6GROUPBY语句1、GROUPBY语句【例5-30】查询student表中所有的班级名称。SELECTclassFROMstudentGROUPBYclass;任务1分析仓库数据5.1.6GROUPBY语句但是在执行以下语句时将会报错：由于数据分组后，如下图5-33中下方的结果每一行记录除了GROUPBY后的字段外，其他的字段每一行可以理解为有多个值。比如class为BigData1801的stname对应有“Zhangdan”和“Zhoumei”共2人。而SELECT语法只允许每行有一个值，因此语句SELECT*FROMstudentGROUPBYclass;报错。那么如何处理由于GROUPBY后产生的每一行的多个值呢？就需要使用聚合函数：比如count、sum、avg等函数。任务1分析仓库数据5.1.6GROUPBY语句任务1分析仓库数据5.1.6GROUPBY语句【例5-31】查询student表中学生信息，按班级进行分组，求每个班级中Hive课程的最高分。SELECTclass,max(course_scores['Hive’])FROMstudentGROUPBYclass;任务1分析仓库数据5.1.6GROUPBY语句【例5-32】查询student表中学生信息，按班级进行分组，使用groupby+collect_list函数显示student表中每个班级的所有学生Hive成绩。SELECTclass,collect_list(course_scores['Hive'])Hive_scoreFROMstudentGROUPBYclass;上述collect_list函数实现将分组中的某列不去重并转为一个数组返回。任务1分析仓库数据5.1.6GROUPBY语句2、HAVING语句HAVING语句允许用户通过一个简单的语法完成原本需要通过子查询才能对GROUPBY语句产生的分组进行条件过滤的任务。【例5-33】查询student表中学生信息，按班级进行分组，输出班级中Hive课程的最高分在85分以上的。SELECTclass,max(course_scores['Hive'])max_Hive_85FROMstudentGROUPBYclassHAVIGmax_Hive_85>85;任务1分析仓库数据5.1.6GROUPBY语句如果没使用HAVING子句，该查询将需要使用一个嵌套SELECT子查询实现，代码如下：SELECTrs.class,rs.maxFROM(SELECTclass,max(course_scores['Hive'])ASmaxFROMstudentGROUPBYclass)rsWHERErs.max>85;任务1分析仓库数据5.1.7JOIN语句JOIN语句是基于两个或多个表中列之间的关系，将这些表进行连接。JOIN语句的语法格式如下SELECT...FROMtable_reference[join_condition][INNER]JOIN|{LEFT|RIGHT|FULL}[OUTER]JOIN|CROSSJOINtable_reference[join_condition]ONexpression语法解释如下：任务1分析仓库数据5.1.7JOIN语句关键字语法解释table_reference表示连接的表名join_condition表示连接的条件，例如使用WHERE子句等[INNER]JOIN表示内连接，其中INNER为可选。根据关联列将左表和右表中能关联起来的数据连接后返回，返回的结果就是两个表中所有相匹配的数据。LEFT[OUTER]JOIN表示左外连接，其中OUTER为可选。根据关联列保留左表完全值，若右表中存在与左表中匹配的值，则保留；若右表中不存在与左表中匹配的值，则以NULL代替。RIGHT[OUTER]JOIN表示右外连接，其中OUTER为可选。根据关联列保留右表完全值，若左表中存在与右表中匹配的值,则保留；若左表中不存在与右表中匹配的值，则以NULL代替。任务1分析仓库数据5.1.7JOIN语句表5-4JOIN语句关键字解释关键字语法解释FULL[OUTER]JOIN表示全外连接，其中OUTER为可选。根据关联列返回左表和右表中的所有数据，若关联不上则以NULL代替。CROSSJOIN表示笛卡儿积关联，返回左表与右表的笛卡儿积结果，两张表的所有行都会交叉连接。ONexpression通过ON子句指定表之间的共同列expression。任务1分析仓库数据5.1.7JOIN语句1、内连接内连接，即INNERJOIN，只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。【例5-34】根据数据te_courses.txt，在studentdb数据库中创建表te_courses，并导入数据te_courses.txt到te_courses表中。第1步，创建表te_courses。CREATETABLEstudentdb.te_courses(tenameSTRING,te_coursesSTRING,te_classSTRING)ROWFORMATDELIMITEDFIELDSTERMINATEDBY'\t'LINESTERMINATEDBY'\n'STOREDASTEXTFILETBLPROPERTIES("skip.header.line.count"="1");任务1分析仓库数据5.1.7JOIN语句第2步，导入数据te_courses.txt到te_courses表。LOADDATALOCALINPATH'/home/hadoop/hivedata/student/te_courses.txt'OVERWRITEINTOTABLEstudentdb.te_courses;第3步，查询表te_courses中数据。任务1分析仓库数据5.1.7JOIN语句【例5-35】查询student表和te_courses表，找到每位学生的任课教师姓名。SELECTt1.class,t2.te_class,t1.stname,t2.tenameFROMstudentdb.studentt1INNERJOINstudentdb.te_coursest2ONt1.class=t2.te_class;上述命令中，通过关联表student中的class列和te_courses表中的te_class列进行内连接。从图5-39可以看出，内连接会根据左表和右表的关联列，只返回相匹配的数据。因此，再查询结果集中不会出现te_class为BigData1803和Network1803的数据。任务1分析仓库数据5.1.7JOIN语句2、左外连接左外连接，即LEFTOUTERJOIN，会返回左边表所有符合WHERE语句的记录。右表中匹配不上的字段值用NULL代替。【例5-36】查询student表和te_courses表，左外连接找到每位学生的任课教师姓名。SELECTt1.class,t2.te_class,t1.stname,t2.tenameFROMstudentdb.studentt1LEFTOUTERJOINstudentdb.te_coursest2ONt1.class=t2.te_class;从图5-40可以看出，左外连接返回左表student的全部数据，右表te_courses通过关联匹配不到的数据以NULL代替。如在右表te_courses中没有关于班级Network1802的任课教师信息，因此左表student中来自Network1802的Zhaolin和Yuanwei两位学生所在行的te_class、tename列为NULL。任务1分析仓库数据5.1.7JOIN语句3、右外连接右外连接，即RIGHTOUTERJOIN，会返回右表所有符合WHERE语句的记录。左表中匹配不上的字段值用NULL代替。【例5-37】查询student表和te_courses表，右外连接找到每位学生的任课教师姓名。SELECTt1.class,t2.te_class,t1.stname,t2.tenameFROMstudentdb.studentt1RIGHTOUTERJOINstudentdb.te_coursest2ONt1.class=t2.te_class;从图5-41可以看出，右外连接返回右表的全部数据，左表student通过关联匹配不到的数据以NULL代替。如在右表te_courses中有班级BigData1803和Network1803的任课教师信息，而左表student中没有班级BigData1803和Network1803的学生信息，因此返回结果中班级BigData1803和Network1803的class和stname列为NULL。任务1分析仓库数据5.1.7JOIN语句4、完全外连接完全外连接，即FULLOUTERJOIN，会返回左表和右表的全部数据，两个表中通过关联列匹配不上的字段值用NULL代替。【例5-38】查询student表和te_courses表，完全外连接找到每位学生的任课教师姓名。SELECTt1.class,t2.te_class,t1.stname,t2.tenameFROMstudentdb.studentt1FULLOUTERJOINstudentdb.te_coursest2ONt1.class=t2.te_class;从图5-42可以看出，完全外连接返回左表student和右表te_courses的全部数据，两个表中通过关联匹配不到的字段值以NULL代替。任务1分析仓库数据5.1.7JOIN语句5、笛卡尔积笛卡尔积，即CROSSJOIN，左表的每一行都会与右表的所有行进行交叉连接。左表的行数乘以右表的行数等于笛卡尔结果集的行数。注意，首先，执行笛卡尔积连接时不需要指定关联列。然后，Hive为保障集群的稳定性，避免join操作导致网络瘫痪，Hive默认不支持笛卡尔积连接，因此需要先执行“setduct=false;”命令开启笛卡尔积连接功能。任务1分析仓库数据5.1.7JOIN语句【例5-39】笛卡尔积连接方式查询student表和te_courses表。SELECTt1.class,t2.te_class,t1.stname,t2.tenameFROMstudentdb.studentt1CROSSJOINstudentdb.te_coursest2;任务1分析仓库数据5.1.7JOIN语句任务1分析仓库数据【例5-39】笛卡尔积连接方式查询student表和te_courses表。5.1.8排序语句1、ORDERBYORDERBY语句会对查询结果集执行全局排序，即所有数据都通过一个Reducer进行处理的过程。对于大数据集，建议ORDERBY和LIMIT子句一同使用，以控制排序后的输出显示条数。因为全局排序只有一个Reducer处理最终的排序输出，当输出结果集行数过大时，要消耗漫长的时间来执行。在ORDERBY子句中可以指定任意字段进行排序，字段后添加ASC关键字表示按升序排序，也是默认值，或添加DESC关键字，表示按降序排序。ORDERBY子句写在SELECT语句的结尾处。任务1分析仓库数据5.1.8排序语句【例5-40】查询studentdb数据库中student表的学生姓名、Python成绩及来自省份信息，并按Python成绩升序排列。SELECTstname,course_scores['Python’]ASPython,vinceFROMstudentORDERBYPython;任务1分析仓库数据5.1.8排序语句【例5-41】查询studentdb数据库中student表的学生姓名和课程平均成绩，按课程平均成绩进行降序排列。SELECTstname,(course_scores['Hive']+course_scores['Python'])/2ASavgFROMstudentORDERBYavgDESC;【例5-42】查询studentdb数据库中student表的学生姓名、班级和Hive课程成绩，先按班级升序排序，再按Hive课程成绩降序排列。SELECTstname,class,course_scores['Hive']ASHiveFROMstudentORDERBYclass,HiveDESC;任务1分析仓库数据5.1.8排序语句2、SORTBYSORTBY用于对查询结果做局部排序，其只会在每个Reducer中对数据进行排序，也就是执行一个局部排序过程。这可以保证每个Reducer的输出数据都是有序的，即局部有序，但是不能保证所有数据都是有序的，除非Reducer的个数为1。SORTBY子句中，与ORDERBY子句相同，在字段后添加ASC关键字表示按升序排序，也是默认值，或添加DESC关键字，表示按降序排序。任务1分析仓库数据5.1.8排序语句【例5-43】查询studentdb数据库中student表的学生姓名、Python成绩、来自省份，使用SORTBY按Python成绩升序排列。SELECTstname,course_scores['Python’]ASPython,vinceFROMstudentSORTBYPython;由于默认情况下Reduce的个数为1，局部排序也就是全局排序，所以此题与例5-40的查询结果一样。但是当使用的Reduce的个数大于1时，输出结果的排序就大不一样了。Mapreduce参数中的mapreduce.job.reduces用于设置默认启动的Reduce数，其默认值为1，可以手动修改Reduce的个数。任务1分析仓库数据5.1.8排序语句【例5-44】将mapreduce.job.reduces参数设置为3后，查询studentdb数据库中student表的学生姓名、Python成绩、来自省份，使用SORTBY按Python成绩升序排列。从以上输出结果发现，由于Reducer的个数设置为3，因此查询显示的数据排序在全局上是无序的。为了能看到每个Reducer的局部排序结果，我们将以上每个Reducer排序结果输出保存到本地的/home/hadoop/dataoutput/e.g.5-44中。任务1分析仓库数据5.1.8排序语句第1步，执行以下代码。INSERTOVERWRITELOCALDIRECTORY'/home/hadoop/dataoutput/e.g.5-44'ROWFORMATDELIMITEDFIELDSTERMINATEDBY","SELECTstname,course_scores['Python']ASPython,vinceFROMstudentSORTBYPython;第2步，查看/home/hadoop/dataoutput/e.g.5-44目录下文件数据排序情况。从上述输出结果可以看到，SORTBY对三个Reducer进行了单独排序，三个Reducer的输出结果按照Python成绩升序排列。但是排序结果中每个省份学生的Python课程是无序的，如果需要对各个省份的学生Python课程分数进行排序，则需要使用DISTRIBUTEBY子句。任务1分析仓库数据5.1.8排序语句【例5-45】将mapreduce.job.reduces参数设置为3后，查询studentdb数据库中student表的学生姓名、Python成绩及所来自的省份，使用distributeby按省份划分，使用sortby按Python成绩升序排列结果，并将输出结果保存到本地目录/home/hadoop/dataoutput/e.g.5-45中。第1步，执行以下代码。INSERTOVERWRITELOCALDIRECTORY'/home/hadoop/dataoutput/e.g.5-45'ROWFORMATDELIMITEDFIELDSTERMINATEDBY","SELECTstname,course_scores['Python']ASPython,vinceFROMstudentDISTRIBUTEBYprovinceSORTBYPython;任务1分析仓库数据5.1.8排序语句第2步，查看/home/hadoop/dataoutput/e.g.5-45目录下文件数据排序情况。任务1分析仓库数据5.1.8排序语句从上述输出结果可以看到，DISTRIBUTEBY子句和SORTBY子句成功对不同省份的学生Python成绩进行了排序，其中Guangdong省最高分为95分，Hunan省最高分为85分。以上在SORTBY子句中添加DISTRIBUTEBY子句是根据分区字段province将相同省份的数据分发到同一个Reducer中，由于数据中只有Guangdong和Hunan两个省份，因而输出结果中文件000001_0为空。DISTRIBUTEBY语句通常与sortby语句结合使用。DISTRIBUTEBY与GROUPBY类似，控制着Reducer如何接受一行行数据，而SORTBY则控制着Reducer内数据如何进行排序。注意，Hive要求DISTRIBUTEBY语句要写在SORTBY语句之前。任务1分析仓库数据5.1.8排序语句3、ClusterBy当DISTRIBUTEBY和SORTSBY两个子句中涉及到的列完全一致，而且是采用升序排序方式，则可以使用CLUSTERBY代替。默认情况下，CLUSTERBY只支持升序排序，不支持降序排序。例如，以下命令1与命令2等价。命令1：命令2：SELECTstname,vince

SELECTstname,vinceFROMstudent

FROMstudentDISTRIBUTEBYprovince

CLUSTERBYprovince;SORTBYprovince;任务1分析仓库数据在第3章任务1中已经在dwd_site仓库中创建好了基站指标汇总表dwd_bts_factor、基站基础属性汇总表dwd_bts_info、行业信息分类表dwd_industry_category共3个表。本任务将根据智能选址的实际业务需要进行数据分析后将获得的数据载入至此三张表中，为下个一个任务做好DWD层的数据准备。任务1分析仓库数据5.1.9获取dwd_site的行业信息分类表数据任务1分析仓库数据ods_site层的ods_code_industry_category表存储着行业大类和行业小类信息，ods_industry_info表存储着各商业的详细信息，包括商业所属的行业小类、商业名称、地址、经度和纬度。根据ods_industry_info表的行业编码字段与ods_code_industry_category表的行业小类编码字段关联做LEFTJOIN操作，获取每个商业的行业大类编码及名称，汇总生成dwd_site层的dwd_industry_category行业信息分类表数据。5.1.9获取dwd_site的行业信息分类表数据任务1分析仓库数据执行以下代码获取数据并将数据载入dwd_industry_category表：INSERTOVERWRITETABLEdwd_site.dwd_industry_categorySELECTb.category_code,b.category_name,b.sub_category_code,b.sub_category_name, ,a.address,a.longitude,a.latitudeFROMods_site.ods_industry_infoaLEFTJOINods_site.ods_code_industry_categorybONa.sub_category_code=b.sub_category_code;验证并查看行业信息分类表dwd_industry_category中的数据：5.1.10获取dwd_site的基站基础属性汇总表数据任务1分析仓库数据ods_site层的ods_bts_info表存储着每个基站的详细信息，包括基站归属地市编码、归属地市、归属区县编码、归属区县、基站编码、基站名称、位置区编码、位置区编码4G、经度、纬度、基站类型编码、基站类型名称、高铁标识、公园标识、区域类型等信息，ods_bts_score表存储着各基站的分数。根据ods_bts_info表的基站编码字段与ods_bts_score表的基站编码字段关联做JOIN操作，获取每个基站的详细信息及基站分数，汇总生成dwd_site层的dwd_bts_info基站基础属性汇总表数据。执行以下代码获取数据并将数据载入dwd_bts_info表：5.1.10获取dwd_site的基站基础属性汇总表数据任务1分析仓库数据INSERTOVERWRITETABLEdwd_site.dwd_bts_infoSELECTa.city_code,a.city_name, a.district_code,a.district_name,a.bts_id,a.enodebid,a.longitude,a.latitude,a.lac,a.tac,a.area_type, a.bts_type,a.bts_type_name,b.bts_score,a.is_highrail,a.is_parkFROMods_site.ods_bts_infoaJOINods_site.ods_bts_scorebONa.bts_id=b.bts_id;验证并查看基站基础属性汇总表dwd_bts_info中的数据：5.1.11获取dwd_site的基站指标汇总表数据任务1分析仓库数据1、创建mid_bts_industry基站行业中间表ods_site层的ods_bts_industry表存储着每个基站所对应的行业数量信息，表中包含基站编码、行业分类编码、行业数量三个字段。其中行业分类编码字段的详细信息如下表：字段值字段值详细信息024商业消费025餐饮026银行027酒店宾馆028生活便捷030大学/政府044五大银行数据量045其他银行数据量5.1.11获取dwd_site的基站指标汇总表数据任务1分析仓库数据ods_bts_industry表中数据按照基站编码字段分类汇总，统计出各基站对应的行业数量总和，并将数据存入临时仓库site_temp的基站对应行业信息汇总表mid_bts_industry中。mid_bts_industry数据表字段名定义如下列表所示：字段名称字段类型描述bts_idString基站编码commercial_numint商业消费catering_numint餐饮bank_numint银行hotel_numint酒店宾馆life_convenientInt生活便捷traffic_convenient_numint交通便捷gov_numint大学/政府five_bank_numint五大银行数据量other_bank_numint其他银行数据量5.1.11获取dwd_site的基站指标汇总表数据任务1分析仓库数据创建临时仓库temp_site：CREATEDATABASEtemp_site;创建基站行业中间表mid_bts_industry：CREATETABLEIFNOTEXISTStemp_site.mid_bts_industry( bts_idstringCOMMENT'基站编码',commercial_numintCOMMENT'商业消费',catering_numintCOMMENT'餐饮’, bank_numintCOMMENT'银行',hotel_numintCOMMENT'酒店宾馆',life_convenient_numintCOMMENT'生活便捷’, traffic_convenient_numintCOMMENT'交通便捷’, gov_numintCOMMENT'大学/政府’, five_bank_numintCOMMENT'五大银行数据量',other_bank_numintCOMMENT'其他银行数据量')COMMENT'基站行业中间表'ROWFORMATDELIMITEDFIELDSTERMINATEDBY'\u0001'NULLDEFINEDAS''STOREDASRCFILE;5.1.11获取dwd_site的基站指标汇总表数据任务1分析仓库数据执行以下代码获取数据并将数据载入基站行业中间表mid_bts_industry表：INSERTOVERWRITETABLEtemp_site.mid_bts_industrySELECTa.bts_id,sum(CASEWHENa.category_code='024'THENnvl(a.num,0)ELSE0END)AScommercial_num,sum(CASEWHENa.category_code='025'THENnvl(a.num,0)ELSE0END)catering_num,sum(CASEWHENa.category_code='026'THENnvl(a.num,0)ELSE0END)bank_num,sum(CASEWHENa.category_code='027'THENnvl(a.num,0)ELSE0END)hotel_num,sum(CASEWHENa.category_code='028'THENnvl(a.num,0)ELSE0END)life_convenient_num,sum(CASEWHENa.category_code='029'THENnvl(a.num,0)ELSE0END)traffic_convenient_num,sum(CASEWHENa.category_code='030'THENnvl(a.num,0)ELSE0END)gov_num,sum(CASEWHENa.category_code='044'THENnvl(a.num,0)ELSE0END)five_bank_num,sum(CASEWHENa.category_code='045'THENnvl(a.num,0)ELSE0END)other_bank_numFROMods_site.ods_bts_industryaGROUPBYa.bts_id;5.1.11获取dwd_site的基站指标汇总表数据任务1分析仓库数据上述代码中的nvl函数用于判断是否为空值，例如：nvl(expr1,expr2)，当expr1为NULL，返回值为expr2，否则返回expr1。nvl函数适用于数字型、字符型和日期型，且expr1和expr2的数据类型必须为同类型。验证并查看基站行业中间表mid_bts_industry中的数据：5.1.11获取dwd_site的基站指标汇总表数据任务1分析仓库数据2、获取dwd_bts_factor基站指标汇总表数据依次汇总银行App明细ods_bank_app表、基站常住人口ods_resident_pop表、基站流动人口ods_floating_pop、消费信息ods_consumption表及基站行业中间mid_bts_industry表，得到基站指标汇总表。由于前四个表为按月分区表，在本实验中只汇总201805即2018年5月份的数据，执行代码如下所示： INSERTOVERWRITETABLEdwd_site.dwd_bts_factorSELECTa.bts_id,a.floating_app_num,a.resident_app_num,b.resident_num,c.floating_num,d.high_num,d.mid_num,d.low_num,e.Commercial_num,e.catering_num,e.bank_num,e.hotel_num,e.life_convenient_num,e.traffic_convenient_num,_num,e.five_bank_num,e.other_bank_numFROM(SELECT*FROMods_site.ods_bank_appWHEREmonth='201805')aLEFTJOIN(SELECT*FROMods_site.ods_resident_popWHEREmonth='201805')bONa.bts_id=b.bts_idLEFTJOIN(SELECT*FROMods_site.ods_floating_popWHEREmonth='201805')cONa.bts_id=c.bts_idLEFTJOIN(SELECT*FROMods_site.ods_consumptionWHEREmonth='201805')dONa.bts_id=d.bts_idLEFTJOINtemp_site.mid_bts_industryeONa.bts_id=e.bts_id;任务小结任务1分析仓库数据首先，通过任务我们已经掌握到所有的数据分析技能，并且开始应用分析技获取到了智能选址中的DWD层数据；然后，在获取DWD层数据时，我们遇到了相对复杂的宽表和多表联合查询，在难度提升时学习者需要调整好心态，分析技术本身不难，但结合业务需求处理问题时必然会复杂，着力理解好数据本身的业务信息是要点；最后，通过本模块的“实践创新”深入研读“大数据智慧旅游”数据处理流程及逻辑模型设计文档，根据已有的业务数据完成分析任务，在独立实践探索中创新并磨练数据分析的技能。本任务的业务目标是以DWD层数据为基础，在DM层数据库首先统计出已建址银行的经纬度信息，然后以已建址银行经纬度坐标为圆心，1200米为覆盖半径，统计此覆盖范围内的基站信息，最后根据统计的覆盖范围内的基站信息，实现计算出已建址银行的评价要素值的任务目标。数据处理流程如下图所示：任务2计算仓库数据任务2计算仓库数据为了分析智能选址中已建址银行要素，在此任务实施中将依次创建用于存储分析结果数据的DM层数据库，统计出已建址银行的经纬度信息，统计出已建址银行覆盖基站信息，最后计算出已建址银行评价要素值。5.2.1创建“大数据商业智能选址”DM层数据库任务2计算仓库数据根据模块二的图2-5“大数据商业智能选址”数据仓库模型构建流程图，在数据仓库Hiv中创建DM库，经过分析计算已建址要素、分析清除不可建址区域、分析计算选址点、算并评分选址点四大任务，将最终在DM中的获得选址结果表。创建“大数据商业智能选址”DM层数据库：CREATEDATABASEdm_siteCOMMENT'Businessintelligencesiteselectionproject'LOCATION'/project/warehouse/intelligent_site/dm_site.db'WITHDBPROPERTIES('ProjectLeader'='Ms.Wang','Editor'='Mr.Liu','Date'='2022-02-10');5.2.2统计已建址银行经纬度信息任务2计算仓库数据从dwd_site仓库的行业信息分类表dwd_industry_category中，根据名称name字段找到已建工商银行的经度和纬度位置信息，不包含自助ATM机，并将数据写入到dm_site库中的已建址银行经纬度表dm_existing_bank，该数据表字段名定义如下列表所示：字段名称字段类型描述bank_nameSTRING银行名称longitudeDECIMAL(10,6)经度latitudeDECIMAL(10,6)纬度5.2.2统计已建址银行经纬度信息任务2计算仓库数据创建已建址银行经纬度信息表dm_existing_bank，代码如下所示：CREATETABLEdm_site.dm_existing_bank( bank_namestringCOMMENT'银行名称’, longitudeDECIMAL(10,6)COMMENT'经度’, latitudeDECIMAL(10,6)COMMENT'纬度')COMMENT'已建址银行表'ROWFORMATDELIMITEDFIELDSTERMINATEDBY'\u0001'NULLDEFINEDAS''STOREDASRCFILE;5.2.2统计已建址银行经纬度信息任务2计算仓库数据查询dwd_site层dwd_industry_category表中工商银行建址信息的名称、经度、纬度三个字段数据，导入到已建址银行经纬度信息表dm_existing_bank中，代码如下：INSERTINTOdm_site.dm_existing_bankSELECTname,longitude,latitudeFROMdwd_site.dwd_industry_cate