本文目录（本文约六万五千字） （一）基本概念、安装、数据类型 （二）DDL数据定义、DML数据操作 （三）查询、分区表和分桶表 （四）函数、压缩和存储 （五）企业级调优、Hive实战（一）基本概念、安装、数据类型1基本概念1。1什么是Hive （1）Hive是由Facebook开源用于解决海量结构化日志的数据统计工具； （2）Hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张表，并提供类SQL查询功能； （3）Hive的本质是将HQL转化成MapReduce程序。 （4）Hive处理的数据存储在HDFS； （5）Hive分析数据底层的实现是MapR （6）执行程序运行在Yarn上。 1。2Hive的优缺点 优点 （1）操作接口采用类SQL语法，提供快速开发的能力（简单、容易上手）。 （2）避免了去写MapReduce，减少开发人员的学习成本。 （3）Hive的执行延迟比较高，因此Hive常用于数据分析，对实时性要求不高的场合。 （4）Hive优势在于处理大数据，对于处理小数据没有优势，因为Hive的执行延迟比较 高。 （5）Hive支持用户自定义函数，用户可以根据自己的需求来实现自己的函数。 缺点 （1）Hive的HQL表达能力有限，迭代式算法无法表达，数据挖掘方面不擅长，由于MapReduce数据处理流程的限制，效率更高的算法却无法实现。 （2）Hive的效率比较低，Hive自动生成的MapReduce作业，通常情况下不够智能化，Hive调优比较困难，粒度较粗。1。3Hive架构原理 （1）用户接口：Client CLI（commandlineinterface）、JDBCODBC（jdbc访问hive）、WEBUI（浏览器访问hive） （2）元数据：Metastore 元数据包括：表名、表所属的数据库（默认是default）、表的拥有者、列分区字段、表的类型（是否是外部表）、表的数据所在目录等； 默认存储在自带的derby数据库中，推荐使用MySQL存储Metastore （3）Hadoop 使用HDFS进行存储，使用MapReduce进行计算。 （4）驱动器 解析器（SQLParser）：将SQL字符串转换成抽象语法树AST，这一步一般都用第三方工具库完成，比如对AST进行语法分析，比如表是否存在、字段是否存在、SQL语义是否有误。 编译器（PhysicalPlan）：将AST编译生成逻辑执行计划。 优化器（QueryOptimizer）：对逻辑执行计划进行优化。 执行器（Execution）：把逻辑执行计划转换成可以运行的物理计划。对于Hive来说，就是MRSpark。 Hive通过给用户提供的一系列交互接口，接收到用户的指令（SQL），使用自己的Driver，结合元数据（MetaStore），将这些指令翻译成MapReduce，提交到Hadoop中执行，最后，将执行返回的结果输出到用户交互接口。1。4Hive和数据库比较 由于Hive采用了类似SQL的查询语言HQL（HiveQueryLanguage），因此很容易将Hive理解为数据库。其实从结构上来看，Hive和数据库除了拥有类似的查询语言，再无类似之处。本文将从多个方面来阐述Hive和数据库的差异。数据库可以用在Online的应用中，但是Hive是为数据仓库而设计的，清楚这一点，有助于从应用角度理解Hive的特性。 （1）查询语言 由于SQL被广泛的应用在数据仓库中，因此，专门针对Hive的特性设计了类SQL的查询语言HQL。熟悉SQL开发的开发者可以很方便的使用Hive进行开发。 （2）数据更新 由于Hive是针对数据仓库应用设计的，而数据仓库的内容是读多写少的。因此，Hive中不建议对数据的改写，所有的数据都是在加载的时候确定好的。而数据库中的数据通常是需要经常进行修改的，因此可以使用INSERTINTOVALUES添加数据，使用UPDATESET修改数据。 （3）执行延迟 Hive在查询数据的时候，由于没有索引，需要扫描整个表，因此延迟较高。另外一个导致Hive执行延迟高的因素是MapReduce框架。由于MapReduce本身具有较高的延迟，因此在利用MapReduce执行Hive查询时，也会有较高的延迟。相对的，数据库的执行延迟较低。当然，这个低是有条件的，即数据规模较小，当数据规模大到超过数据库的处理能力的时候，Hive的并行计算显然能体现出优势。 （4）数据规模 由于Hive建立在集群上并可以利用MapReduce进行并行计算，因此可以支持很大规模的数据；对应的，数据库可以支持的数据规模较小。2Hive安装2。1Hive安装部署 1。把apachehive3。1。2bin。tar。gz上传到linux的optsoftware目录下 2。解压apachehive3。1。2bin。tar。gz到optmodule目录下面〔Tomhadoop102software〕tarzxvfoptsoftwareapachehive3。1。2bin。tar。gzCoptmodule1 3。修改apachehive3。1。2bin。tar。gz的名称为hive3。1。2〔Tomhadoop102software〕mvoptmoduleapachehive3。1。2binoptmodulehive3。1。21 4。修改etcprofile。dmyenv。sh，添加环境变量〔Tomhadoop102software〕sudovimetcprofile。dmyenv。sh1 5。添加内容HIVEHOMEexportHIVEHOMEoptmodulehive3。1。2exportPATHPATH：HIVEHOMEbin123 6。解决日志Jar包冲突〔Tomhadoop102software〕mvHIVEHOMEliblog4jslf4jimpl2。10。0。jarHIVEHOMEliblog4jslf4jimpl2。10。0。bak1 7。初始化元数据库〔Tomuhadoop102hive3。1。2〕binschematooldbTypederbyinitSchema1 8。启动并使用Hive〔Tomhadoop102hive3。1。2〕binhivehive（default）hive（default）hive（default）createtabletest（idint）；hive（default）insertintotestvalues（1）；hive（default）OKtest。id1Timetaken：0。39seconds，Fetched：1row（s）12345678910 在CRT窗口中开启另一个窗口，开启Hive，在tmpatguigu目录下监控hive。log文件Causedby：ERRORXSDB6：AnotherinstanceofDerbymayhavealreadybootedthedatabaseoptmodulehivemetastoredb。atorg。apache。derby。iapi。error。StandardException。newException（UnknownSource）atorg。apache。derby。iapi。error。StandardException。newException（UnknownSource）atorg。apache。derby。impl。store。raw。data。BaseDataFileFactory。privGetJBMSLockOnDB（UnknownSource）atorg。apache。derby。impl。store。raw。data。BaseDataFileFactory。run（UnknownSource）。。。1234567 原因在于Hive默认使用的元数据库为derby，开启Hive之后就会占用元数据库，且不与其他客户端共享数据，所以我们需要将Hive的元数据地址改为MySQL。2。2MySQL安装 1。检查当前系统是否安装过MySQL〔Tomhadoop102〕rpmqagrepmariadbmariadblibs5。5。562。el7。x8664如果存在通过如下命令卸载〔Tomhadoop102〕sudorpmenodepsmariadblibs1234 2。将MySQL安装包拷贝到optsoftware目录下，并解压MySQL安装包〔Tomhadoop102software〕tarxfmysql5。7。281。el7。x8664。rpmbundle。tarrwrr。1TomTom6095564806月3022：20mysql5。7。281。el7。x8664。rpmbundle。tarrwrr。1TomTom451093649月302019mysqlcommunityclient5。7。281。el7。x8664。rpmrwrr。1TomTom3187689月302019mysqlcommunitycommon5。7。281。el7。x8664。rpmrwrr。1TomTom70370969月302019mysqlcommunitydevel5。7。281。el7。x8664。rpmrwrr。1TomTom493291009月302019mysqlcommunityembedded5。7。281。el7。x8664。rpmrwrr。1TomTom233549089月302019mysqlcommunityembeddedcompat5。7。281。el7。x8664。rpmrwrr。1TomTom1368378169月302019mysqlcommunityembeddeddevel5。7。281。el7。x8664。rpmrwrr。1TomTom43743649月302019mysqlcommunitylibs5。7。281。el7。x8664。rpmrwrr。1TomTom13533129月302019mysqlcommunitylibscompat5。7。281。el7。x8664。rpmrwrr。1TomTom2086948249月302019mysqlcommunityserver5。7。281。el7。x8664。rpmrwrr。1TomTom1331299929月302019mysqlcommunitytest5。7。281。el7。x8664。rpm123456789101112 3。在安装目录下执行rpm安装〔Tomhadoop102software〕sudorpmivhmysqlcommunitycommon5。7。281。el7。x8664。rpmsudorpmivhmysqlcommunitylibs5。7。281。el7。x8664。rpmsudorpmivhmysqlcommunitylibscompat5。7。281。el7。x8664。rpmsudorpmivhmysqlcommunityclient5。7。281。el7。x8664。rpmsudorpmivhmysqlcommunityserver5。7。281。el7。x8664。rpm123456 注意：按照顺序依次执行。如果Linux是最小化安装的，在安装mysqlcommunityserver5。7。281。el7。x8664。rpm时可能会出现如下错误〔Tomhadoop102software〕sudorpmivhmysqlcommunityserver5。7。281。el7。x8664。rpm警告：mysqlcommunityserver5。7。281。el7。x8664。rpm：头V3DSASHA1Signature，密钥ID5072e1f5：NOKEY错误：依赖检测失败：libaio。so。1（）（64bit）被mysqlcommunityserver5。7。281。el7。x8664需要libaio。so。1（LIBAIO0。1）（64bit）被mysqlcommunityserver5。7。281。el7。x8664需要libaio。so。1（LIBAIO0。4）（64bit）被mysqlcommunityserver5。7。281。el7。x8664需要123456 通过yum安装缺少的依赖，然后重新安装mysqlcommunityserver5。7。281。el7。x8664即可〔Tomhadoop102software〕yuminstallylibaio1 4。删除etcmy。cnf文件中datadir指向的目录下的所有内容，如果有内容的情况下： 查看datadir的值〔mysqld〕datadirvarlibmysql12 删除varlibmysql目录下的所有内容：〔Tomhadoop102mysql〕cdvarlibmysql〔Tomhadoop102mysql〕sudormrf。12 5。初始化数据库〔Tomhadoop102opt〕sudomysqldinitializeusermysql1 6。查看临时生成的root用户的密码〔Tomhadoop102opt〕sudocatvarlogmysqld。log1 7。启动MySQL服务〔Tomhadoop102opt〕sudosystemctlstartmysqld1 8。登录MySQL数据库〔Tomhadoop102opt〕mysqlurootpEnterpassword：输入临时生成的密码12 9。必须先修改root用户的密码，否则执行其他的操作会报错mysqlsetpasswordpassword（新密码）；1 修改mysql库下的user表中的root用户，允许任意ip连接mysqlupdatemysql。122。3Hive元数据配置到MySQL 1。拷贝驱动 将MySQL的JDBC驱动拷贝到Hive的lib目录下〔Tomhadoop102software〕cpoptsoftwaremysqlconnectorjava5。1。37。jarHIVEHOMElib1 2。配置Metastore到MySQL （1）在HIVEHOMEconf目录下新建hivesite。xml文件〔Tomhadoop102software〕vimHIVEHOMEconfhivesite。xml1 添加如下内容？xmlversion1。0？？xmlstylesheettypetextxslhrefconfiguration。xsl？configuration！jdbc连接的URLpropertynamejavax。jdo。option。ConnectionURLnamevaluejdbc：mysql：hadoop102：3306metastore？useSSLfalsevalueproperty！jdbc连接的Driverpropertynamejavax。jdo。option。ConnectionDriverNamenamevaluecom。mysql。jdbc。Drivervalueproperty！jdbc连接的usernamepropertynamejavax。jdo。option。ConnectionUserNamenamevaluerootvalueproperty！jdbc连接的passwordpropertynamejavax。jdo。option。ConnectionPasswordnamevalue000000valueproperty！Hive元数据存储版本的验证propertynamehive。metastore。schema。verificationnamevaluefalsevalueproperty！元数据存储授权propertynamehive。metastore。event。db。notification。api。authnamevaluefalsevalueproperty！Hive默认在HDFS的工作目录propertynamehive。metastore。warehouse。dirnamevalueuserhivewarehousevalueproperty1234567891011121314151617181920212223242526272829303132333435363738394041424344 （2）登录MySQL〔Tomhadoop102software〕mysqlurootp0000001 （3）新建Hive元数据库12 （4）初始化Hive元数据库〔Tomhadoop102software〕schematoolinitSchemadbTypemysqlverbose1 3。再次启动Hive （1）启动Hive〔Tomhadoop102hive3。1。2〕binhive1 （2）使用Hhivecreatetabletest（idint）；hiveinsertintotestvalues（1）；12345 （3）在CRT窗口中开启另一个窗口开启H1232。4使用元数据服务的方式访问Hive 1。在hivesite。xml文件中添加如下配置信息！指定存储元数据要连接的地址propertynamehive。metastore。urisnamevaluethrift：hadoop102：9083valueproperty12345 2。启动metastore〔Tomhadoop202hive3。1。2〕hiveservicemetastore2020082816：58：08：StartingHiveMetastoreServer12 注意：启动后窗口不能再操作，需打开一个新的shell窗口做别的操作 （3）启动Hive〔Tomhadoop103hive3。1。2〕binhive12。5使用JDBC方式访问Hive （1）在hivesite。xml文件中添加如下配置信息！指定hiveserver2连接的hostpropertynamehive。server2。thrift。bind。hostnamevaluehadoop102valueproperty！指定hiveserver2连接的端口号propertynamehive。server2。thrift。portnamevalue10000valueproperty1234567891011 （2）启动hiveserver2〔Tomhadoop102hive3。1。2〕binhiveservicehiveserver21 （3）启动beeline客户端（需要多等待一会）〔Tomhadoop102hive3。1。2〕binbeelineujdbc：hive2：hadoop102：10000nTom1 （4）看到如下界面Connectingtojdbc：hive2：hadoop102：10000Connectedto：ApacheHive（version3。1。2）Driver：HiveJDBC（version3。1。2）Transactionisolation：TRANSACTIONREPEATABLEREADBeelineversion3。1。2byApacheHive0：jdbc：hive2：hadoop102：10000123456 （5）编写hive服务启动脚本〔Tomhadoop102hive3。1。2〕vimHIVEHOMEbinhiveservices。sh1！binbashHIVELOGDIRHIVEHOMElogsif〔！dHIVELOGDIR〕thenmkdirpHIVELOGDIRfi检查进程是否运行正常，参数1为进程名，参数2为进程端口functioncheckprocess（）｛pid（psef2devnullgrepvgrepgrepi1awk｛print2｝）ppid（netstatnltp2devnullgrep2awk｛print7｝cutdf1）echopid〔〔pidppid〕〕〔ppid〕return0return1｝functionhivestart（）｛metapid（checkprocessHiveMetastore9083）cmdnohuphiveservicemetastoreHIVELOGDIRmetastore。log21〔zmetapid〕evalcmdechoMetastroe服务已启动server2pid（checkprocessHiveServer210000）cmdnohuphiveserver2HIVELOGDIRhiveServer2。log21〔zserver2pid〕evalcmdechoHiveServer2服务已启动｝functionhivestop（）｛metapid（checkprocessHiveMetastore9083）〔metapid〕killmetapidechoMetastore服务未启动server2pid（checkprocessHiveServer210000）〔server2pid〕killserver2pidechoHiveServer2服务未启动｝case1instart）；stop）；restart）hivestopsleep2；status）checkprocessHiveMetastore9083devnullechoMetastore服务运行正常echoMetastore服务运行异常checkprocessHiveServer210000devnullechoHiveServer2服务运行正常echoHiveServer2服务运行异常；；）echoInvalidArgs！echoUsage：（basename0）；esac123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354 添加执行权限〔Tomhadoop102hive3。1。2〕chmodxHIVEHOMEbinhiveservices。sh1 启动Hive后台服务〔Tomhadoop102hive3。1。2〕hiveservices。shstart12。6Hive常用交互命令〔Tomhadoop102hive3。1。2〕binhivehelpusage：hived，definekeyvalueVariablesubstitutiontoapplytoHivecommands。e。g。dABordefineABdatabasedatabasenameSpecifythedatabasetouseequotedquerystringSQLfromcommandlineffilenameSQLfromfilesH，helpPrinthelpinformationhiveconfpropertyvalueUsevalueforgivenpropertyhivevarkeyvalueVariablesubstitutiontoapplytoHivecommands。e。g。hivevarABifilenameInitializationSQLfileS，silentSilentmodeininteractiveshellv，verboseVerbosemode（echoexecutedSQLtotheconsole）123456789101112131415 1。e不进入hive的交互窗口执行sql语句〔Tomhadoop102hive3。1。2〕1 2。f执行脚本中的sql语句 （1）在optmodulehive下创建datas目录，并在datas目录下创建hivef。sql文件〔Tomhadoop102datas〕touchhivef。sql1 （2）文件中写入正确的sql语句1 （3）执行文件中的sql语句〔Tomhadoop102hive3。1。2〕binhivefoptmodulehivedatashivef。sql1 （4）执行文件中的sql语句并将结果写入文件中〔Tomhadoop102hive3。1。2〕binhivefoptmodulehivedatashivef。sqloptmoduledatashiveresult。txt1 3。退出Hive窗口hive（default）hive（default）12 4。在hivecli命令窗口中如何查看hdfs文件系统hive（default）1 5。查看在hive中输入的所有历史命令 （1）进入到当前用户的根目录root或homeTom （2）查看。hivehistory文件〔Tomhadoop102〕cat。hivehistory12。7Hive常见属性配置 1。Hive运行日志信息配置 （1）Hive的log默认存放在tmpTomhive。log目录下（当前用户名下） （2）修改hive的log存放日志到optmodulehive3。1。2logs 修改optmodulehiveconfhivelog4j2。properties。template文件名称为hivelog4j2。properties〔Tomhadoop102conf〕pwdoptmodulehive3。1。2conf〔Tomhadoop102conf〕mvhivelog4j2。properties。templatehivelog4j2。properties123 在hivelog4j2。properties文件中修改log存放位置hive。log。diroptmodulehivelogs1 2。打印当前库和表头 在hivesite。xml中加入如下两个配置：propertynamehive。cli。print。headernamevaluetruevaluepropertypropertynamehive。cli。print。current。dbnamevaluetruevalueproperty12345678 3。参数配置方式 （1）查看当前所有的配置信息1 （2）参数的配置三种方式 默认配置文件：hivedefault。xml 用户自定义配置文件：hivesite。xml 注意：用户自定义配置会覆盖默认配置。另外，Hive也会读入Hadoop的配置，因为Hive是作为Hadoop的客户端启动的，Hive的配置会覆盖Hadoop的配置。配置文件的设定对本机启动的所有Hive进程都有效。 命令行参数方式 启动Hive时，可以在命令行添加hiveconfparamvalue来设定参数。如：〔Tomhadoop102hive3。1。2〕binhivehiveconfmapred。reduce。tasks10；1 注意：仅对本次hive启动有效 查看参数设置：hive（default）setmapred。reduce。1 参数声明方式 可以在HQL中使用SET关键字设定参数，如：〔Tomhadoop102hive3。1。2〕binhivehiveconfmapred。reduce。tasks100；1 注意：仅对本次hive启动有效 查看参数设置：hive（default）setmapred。reduce。1 上述三种设定方式的优先级依次递增。即配置文件命令行参数参数声明。注意某些系统级的参数，例如log4j相关的设定，必须用前两种方式设定，因为那些参数的读取在会话建立以前已经完成了。3Hive数据类型3。1基本数据类型 对于Hive的String类型相当于数据库的varchar类型，该类型是一个可变的字符串，不过它不能声明其中最多能存储多少个字符，理论上它可以存储2GB的字符数。3。2集合数据类型 Hive有三种复杂数据类型：ARRAY、MAP和STRUCT。ARRAY和MAP与Java中的Array和Map类似，而STRUCT与C语言中的Struct类似，它封装了一个命名字段集合，复杂数据类型允许任意层次的嵌套。 案例实操 （1）假设某表有如下一行，我们用JSON格式来表示其数据结构。在Hive下访问的格式为 （2）基于上述数据结构，我们在Hive里创建对应的表，并导入数据。 创建本地测试文件test。txtsongsong，bingbinglili，xiaosong：18xiaoxiaosong：19，huilongguanbeijingyangyang，caicaisusu，xiaoyang：18xiaoxiaoyang：19，chaoyangbeijing12 注意MAP，STRUCT和ARRAY里的元素间关系都可以用同一个字符表示，这里用。 （3）Hive上创建测试表testhive（default）createtabletest（namestring，friendsarraystring，childrenmapstring，int，addressstructstreet：string，city：string）rowformatdelimitedfieldsterminatedby，collectionitemsterminatedbymapkeysterminatedby：OKTimetaken：0。933seconds123456789101112 字段解释： rowformatdelimitedfieldsterminatedby‘，’列分隔符 collectionitemsterminatedby‘’MAPSTRUCT和ARRAY的分隔符（数据分割符号） mapkeysterminatedby‘：’MAP中的key与value的分隔符 linesterminatedby‘’；行分隔符 （4）导入文本数据到测试表hive（default）loaddatalocalinpathoptmodulehive3。1。2datatest。Loadingdatatotabledefault。testOKTimetaken：0。893seconds1234 （5）访问三种集合列里的数据，以下分别是ARRAY，MAP，STRUCT的访问方式hive（default）selectfriends〔1〕，children〔xiaosong〕，address。OKc0c1citylili18beijingTimetaken：0。386seconds，Fetched：1row（s）1234563。3类型转换 Hive的原子数据类型是可以进行隐式转换的，类似于Java的类型转换，例如某表达式使用INT类型，TINYINT会自动转换为INT类型，但是Hive不会进行反向转化，例如，某表达式使用TINYINT类型，INT不会自动转换为TINYINT类型，它会返回错误，除非使用CAST操作。 1。隐式类型转换规则如下 （1）任何整数类型都可以隐式地转换为一个范围更广的类型，如TINYINT可以转换成INT，INT可以转换成BIGINT。 （2）所有整数类型、FLOAT和STRING类型都可以隐式地转换成DOUBLE。 （3）TINYINT、SMALLINT、INT都可以转换为FLOAT。 （4）BOOLEAN类型不可以转换为任何其它的类型。 2。可以使用CAST操作显示进行数据类型转换 例如CAST（‘1’ASINT）将把字符串’1’转换成整数1；如果强制类型转换失败，如执行CAST（‘X’ASINT），表达式返回空值NULL。1基本概念1。1什么是Hive （1）Hive是由Facebook开源用于解决海量结构化日志的数据统计工具； （2）Hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张表，并提供类SQL查询功能； （3）Hive的本质是将HQL转化成MapReduce程序。 （4）Hive处理的数据存储在HDFS； （5）Hive分析数据底层的实现是MapR （6）执行程序运行在Yarn上。 1。2Hive的优缺点 优点 （1）操作接口采用类SQL语法，提供快速开发的能力（简单、容易上手）。 （2）避免了去写MapReduce，减少开发人员的学习成本。 （3）Hive的执行延迟比较高，因此Hive常用于数据分析，对实时性要求不高的场合。 （4）Hive优势在于处理大数据，对于处理小数据没有优势，因为Hive的执行延迟比较 高。 （5）Hive支持用户自定义函数，用户可以根据自己的需求来实现自己的函数。 缺点 （1）Hive的HQL表达能力有限，迭代式算法无法表达，数据挖掘方面不擅长，由于MapReduce数据处理流程的限制，效率更高的算法却无法实现。 （2）Hive的效率比较低，Hive自动生成的MapReduce作业，通常情况下不够智能化，Hive调优比较困难，粒度较粗。1。3Hive架构原理 （1）用户接口：Client CLI（commandlineinterface）、JDBCODBC（jdbc访问hive）、WEBUI（浏览器访问hive） （2）元数据：Metastore 元数据包括：表名、表所属的数据库（默认是default）、表的拥有者、列分区字段、表的类型（是否是外部表）、表的数据所在目录等； 默认存储在自带的derby数据库中，推荐使用MySQL存储Metastore （3）Hadoop 使用HDFS进行存储，使用MapReduce进行计算。 （4）驱动器 解析器（SQLParser）：将SQL字符串转换成抽象语法树AST，这一步一般都用第三方工具库完成，比如对AST进行语法分析，比如表是否存在、字段是否存在、SQL语义是否有误。 编译器（PhysicalPlan）：将AST编译生成逻辑执行计划。 优化器（QueryOptimizer）：对逻辑执行计划进行优化。 执行器（Execution）：把逻辑执行计划转换成可以运行的物理计划。对于Hive来说，就是MRSpark。 Hive通过给用户提供的一系列交互接口，接收到用户的指令（SQL），使用自己的Driver，结合元数据（MetaStore），将这些指令翻译成MapReduce，提交到Hadoop中执行，最后，将执行返回的结果输出到用户交互接口。1。4Hive和数据库比较 由于Hive采用了类似SQL的查询语言HQL（HiveQueryLanguage），因此很容易将Hive理解为数据库。其实从结构上来看，Hive和数据库除了拥有类似的查询语言，再无类似之处。本文将从多个方面来阐述Hive和数据库的差异。数据库可以用在Online的应用中，但是Hive是为数据仓库而设计的，清楚这一点，有助于从应用角度理解Hive的特性。 （1）查询语言 由于SQL被广泛的应用在数据仓库中，因此，专门针对Hive的特性设计了类SQL的查询语言HQL。熟悉SQL开发的开发者可以很方便的使用Hive进行开发。 （2）数据更新 由于Hive是针对数据仓库应用设计的，而数据仓库的内容是读多写少的。因此，Hive中不建议对数据的改写，所有的数据都是在加载的时候确定好的。而数据库中的数据通常是需要经常进行修改的，因此可以使用INSERTINTOVALUES添加数据，使用UPDATESET修改数据。 （3）执行延迟 Hive在查询数据的时候，由于没有索引，需要扫描整个表，因此延迟较高。另外一个导致Hive执行延迟高的因素是MapReduce框架。由于MapReduce本身具有较高的延迟，因此在利用MapReduce执行Hive查询时，也会有较高的延迟。相对的，数据库的执行延迟较低。当然，这个低是有条件的，即数据规模较小，当数据规模大到超过数据库的处理能力的时候，Hive的并行计算显然能体现出优势。 （4）数据规模 由于Hive建立在集群上并可以利用MapReduce进行并行计算，因此可以支持很大规模的数据；对应的，数据库可以支持的数据规模较小。2Hive安装2。1Hive安装部署 1。把apachehive3。1。2bin。tar。gz上传到linux的optsoftware目录下 2。解压apachehive3。1。2bin。tar。gz到optmodule目录下面〔Tomhadoop102software〕tarzxvfoptsoftwareapachehive3。1。2bin。tar。gzCoptmodule1 3。修改apachehive3。1。2bin。tar。gz的名称为hive3。1。2〔Tomhadoop102software〕mvoptmoduleapachehive3。1。2binoptmodulehive3。1。21 4。修改etcprofile。dmyenv。sh，添加环境变量〔Tomhadoop102software〕sudovimetcprofile。dmyenv。sh1 5。添加内容HIVEHOMEexportHIVEHOMEoptmodulehive3。1。2exportPATHPATH：HIVEHOMEbin123 6。解决日志Jar包冲突〔Tomhadoop102software〕mvHIVEHOMEliblog4jslf4jimpl2。10。0。jarHIVEHOMEliblog4jslf4jimpl2。10。0。bak1 7。初始化元数据库〔Tomuhadoop102hive3。1。2〕binschematooldbTypederbyinitSchema1 8。启动并使用Hive〔Tomhadoop102hive3。1。2〕binhivehive（default）hive（default）hive（default）createtabletest（idint）；hive（default）insertintotestvalues（1）；hive（default）OKtest。id1Timetaken：0。39seconds，Fetched：1row（s）12345678910 在CRT窗口中开启另一个窗口，开启Hive，在tmpatguigu目录下监控hive。log文件Causedby：ERRORXSDB6：AnotherinstanceofDerbymayhavealreadybootedthedatabaseoptmodulehivemetastoredb。atorg。apache。derby。iapi。error。StandardException。newException（UnknownSource）atorg。apache。derby。iapi。error。StandardException。newException（UnknownSource）atorg。apache。derby。impl。store。raw。data。BaseDataFileFactory。privGetJBMSLockOnDB（UnknownSource）atorg。apache。derby。impl。store。raw。data。BaseDataFileFactory。run（UnknownSource）。。。1234567 原因在于Hive默认使用的元数据库为derby，开启Hive之后就会占用元数据库，且不与其他客户端共享数据，所以我们需要将Hive的元数据地址改为MySQL。2。2MySQL安装 1。检查当前系统是否安装过MySQL〔Tomhadoop102〕rpmqagrepmariadbmariadblibs5。5。562。el7。x8664如果存在通过如下命令卸载〔Tomhadoop102〕sudorpmenodepsmariadblibs1234 2。将MySQL安装包拷贝到optsoftware目录下，并解压MySQL安装包〔Tomhadoop102software〕tarxfmysql5。7。281。el7。x8664。rpmbundle。tarrwrr。1TomTom6095564806月3022：20mysql5。7。281。el7。x8664。rpmbundle。tarrwrr。1TomTom451093649月302019mysqlcommunityclient5。7。281。el7。x8664。rpmrwrr。1TomTom3187689月302019mysqlcommunitycommon5。7。281。el7。x8664。rpmrwrr。1TomTom70370969月302019mysqlcommunitydevel5。7。281。el7。x8664。rpmrwrr。1TomTom493291009月302019mysqlcommunityembedded5。7。281。el7。x8664。rpmrwrr。1TomTom233549089月302019mysqlcommunityembeddedcompat5。7。281。el7。x8664。rpmrwrr。1TomTom1368378169月302019mysqlcommunityembeddeddevel5。7。281。el7。x8664。rpmrwrr。1TomTom43743649月302019mysqlcommunitylibs5。7。281。el7。x8664。rpmrwrr。1TomTom13533129月302019mysqlcommunitylibscompat5。7。281。el7。x8664。rpmrwrr。1TomTom2086948249月302019mysqlcommunityserver5。7。281。el7。x8664。rpmrwrr。1TomTom1331299929月302019mysqlcommunitytest5。7。281。el7。x8664。rpm123456789101112 3。在安装目录下执行rpm安装〔Tomhadoop102software〕sudorpmivhmysqlcommunitycommon5。7。281。el7。x8664。rpmsudorpmivhmysqlcommunitylibs5。7。281。el7。x8664。rpmsudorpmivhmysqlcommunitylibscompat5。7。281。el7。x8664。rpmsudorpmivhmysqlcommunityclient5。7。281。el7。x8664。rpmsudorpmivhmysqlcommunityserver5。7。281。el7。x8664。rpm123456 注意：按照顺序依次执行。如果Linux是最小化安装的，在安装mysqlcommunityserver5。7。281。el7。x8664。rpm时可能会出现如下错误〔Tomhadoop102software〕sudorpmivhmysqlcommunityserver5。7。281。el7。x8664。rpm警告：mysqlcommunityserver5。7。281。el7。x8664。rpm：头V3DSASHA1Signature，密钥ID5072e1f5：NOKEY错误：依赖检测失败：libaio。so。1（）（64bit）被mysqlcommunityserver5。7。281。el7。x8664需要libaio。so。1（LIBAIO0。1）（64bit）被mysqlcommunityserver5。7。281。el7。x8664需要libaio。so。1（LIBAIO0。4）（64bit）被mysqlcommunityserver5。7。281。el7。x8664需要123456 通过yum安装缺少的依赖，然后重新安装mysqlcommunityserver5。7。281。el7。x8664即可〔Tomhadoop102software〕yuminstallylibaio1 4。删除etcmy。cnf文件中datadir指向的目录下的所有内容，如果有内容的情况下： 查看datadir的值〔mysqld〕datadirvarlibmysql12 删除varlibmysql目录下的所有内容：〔Tomhadoop102mysql〕cdvarlibmysql〔Tomhadoop102mysql〕sudormrf。12 5。初始化数据库〔Tomhadoop102opt〕sudomysqldinitializeusermysql1 6。查看临时生成的root用户的密码〔Tomhadoop102opt〕sudocatvarlogmysqld。log1 7。启动MySQL服务〔Tomhadoop102opt〕sudosystemctlstartmysqld1 8。登录MySQL数据库〔Tomhadoop102opt〕mysqlurootpEnterpassword：输入临时生成的密码12 9。必须先修改root用户的密码，否则执行其他的操作会报错mysqlsetpasswordpassword（新密码）；1 修改mysql库下的user表中的root用户，允许任意ip连接mysqlupdatemysql。122。3Hive元数据配置到MySQL 1。拷贝驱动 将MySQL的JDBC驱动拷贝到Hive的lib目录下〔Tomhadoop102software〕cpoptsoftwaremysqlconnectorjava5。1。37。jarHIVEHOMElib1 2。配置Metastore到MySQL （1）在HIVEHOMEconf目录下新建hivesite。xml文件〔Tomhadoop102software〕vimHIVEHOMEconfhivesite。xml1 添加如下内容？xmlversion1。0？？xmlstylesheettypetextxslhrefconfiguration。xsl？configuration！jdbc连接的URLpropertynamejavax。jdo。option。ConnectionURLnamevaluejdbc：mysql：hadoop102：3306metastore？useSSLfalsevalueproperty！jdbc连接的Driverpropertynamejavax。jdo。option。ConnectionDriverNamenamevaluecom。mysql。jdbc。Drivervalueproperty！jdbc连接的usernamepropertynamejavax。jdo。option。ConnectionUserNamenamevaluerootvalueproperty！jdbc连接的passwordpropertynamejavax。jdo。option。ConnectionPasswordnamevalue000000valueproperty！Hive元数据存储版本的验证propertynamehive。metastore。schema。verificationnamevaluefalsevalueproperty！元数据存储授权propertynamehive。metastore。event。db。notification。api。authnamevaluefalsevalueproperty！Hive默认在HDFS的工作目录propertynamehive。metastore。warehouse。dirnamevalueuserhivewarehousevalueproperty1234567891011121314151617181920212223242526272829303132333435363738394041424344 （2）登录MySQL〔Tomhadoop102software〕mysqlurootp0000001 （3）新建Hive元数据库12 （4）初始化Hive元数据库〔Tomhadoop102software〕schematoolinitSchemadbTypemysqlverbose1 3。再次启动Hive （1）启动Hive〔Tomhadoop102hive3。1。2〕binhive1 （2）使用Hhivecreatetabletest（idint）；hiveinsertintotestvalues（1）；12345 （3）在CRT窗口中开启另一个窗口开启H1232。4使用元数据服务的方式访问Hive 1。在hivesite。xml文件中添加如下配置信息！指定存储元数据要连接的地址propertynamehive。metastore。urisnamevaluethrift：hadoop102：9083valueproperty12345 2。启动metastore〔Tomhadoop202hive3。1。2〕hiveservicemetastore2020082816：58：08：StartingHiveMetastoreServer12 注意：启动后窗口不能再操作，需打开一个新的shell窗口做别的操作 （3）启动Hive〔Tomhadoop103hive3。1。2〕binhive12。5使用JDBC方式访问Hive （1）在hivesite。xml文件中添加如下配置信息！指定hiveserver2连接的hostpropertynamehive。server2。thrift。bind。hostnamevaluehadoop102valueproperty！指定hiveserver2连接的端口号propertynamehive。server2。thrift。portnamevalue10000valueproperty1234567891011 （2）启动hiveserver2〔Tomhadoop102hive3。1。2〕binhiveservicehiveserver21 （3）启动beeline客户端（需要多等待一会）〔Tomhadoop102hive3。1。2〕binbeelineujdbc：hive2：hadoop102：10000nTom1 （4）看到如下界面Connectingtojdbc：hive2：hadoop102：10000Connectedto：ApacheHive（version3。1。2）Driver：HiveJDBC（version3。1。2）Transactionisolation：TRANSACTIONREPEATABLEREADBeelineversion3。1。2byApacheHive0：jdbc：hive2：hadoop102：10000123456 （5）编写hive服务启动脚本〔Tomhadoop102hive3。1。2〕vimHIVEHOMEbinhiveservices。sh1！binbashHIVELOGDIRHIVEHOMElogsif〔！dHIVELOGDIR〕thenmkdirpHIVELOGDIRfi检查进程是否运行正常，参数1为进程名，参数2为进程端口functioncheckprocess（）｛pid（psef2devnullgrepvgrepgrepi1awk｛print2｝）ppid（netstatnltp2devnullgrep2awk｛print7｝cutdf1）echopid〔〔pidppid〕〕〔ppid〕return0return1｝functionhivestart（）｛metapid（checkprocessHiveMetastore9083）cmdnohuphiveservicemetastoreHIVELOGDIRmetastore。log21〔zmetapid〕evalcmdechoMetastroe服务已启动server2pid（checkprocessHiveServer210000）cmdnohuphiveserver2HIVELOGDIRhiveServer2。log21〔zserver2pid〕evalcmdechoHiveServer2服务已启动｝functionhivestop（）｛metapid（checkprocessHiveMetastore9083）〔metapid〕killmetapidechoMetastore服务未启动server2pid（checkprocessHiveServer210000）〔server2pid〕killserver2pidechoHiveServer2服务未启动｝case1instart）；stop）；restart）hivestopsleep2；status）checkprocessHiveMetastore9083devnullechoMetastore服务运行正常echoMetastore服务运行异常checkprocessHiveServer210000devnullechoHiveServer2服务运行正常echoHiveServer2服务运行异常；；）echoInvalidArgs！echoUsage：（basename0）；esac123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354 添加执行权限〔Tomhadoop102hive3。1。2〕chmodxHIVEHOMEbinhiveservices。sh1 启动Hive后台服务〔Tomhadoop102hive3。1。2〕hiveservices。shstart12。6Hive常用交互命令〔Tomhadoop102hive3。1。2〕binhivehelpusage：hived，definekeyvalueVariablesubstitutiontoapplytoHivecommands。e。g。dABordefineABdatabasedatabasenameSpecifythedatabasetouseequotedquerystringSQLfromcommandlineffilenameSQLfromfilesH，helpPrinthelpinformationhiveconfpropertyvalueUsevalueforgivenpropertyhivevarkeyvalueVariablesubstitutiontoapplytoHivecommands。e。g。hivevarABifilenameInitializationSQLfileS，silentSilentmodeininteractiveshellv，verboseVerbosemode（echoexecutedSQLtotheconsole）123456789101112131415 1。e不进入hive的交互窗口执行sql语句〔Tomhadoop102hive3。1。2〕1 2。f执行脚本中的sql语句 （1）在optmodulehive下创建datas目录，并在datas目录下创建hivef。sql文件〔Tomhadoop102datas〕touchhivef。sql1 （2）文件中写入正确的sql语句1 （3）执行文件中的sql语句〔Tomhadoop102hive3。1。2〕binhivefoptmodulehivedatashivef。sql1 （4）执行文件中的sql语句并将结果写入文件中〔Tomhadoop102hive3。1。2〕binhivefoptmodulehivedatashivef。sqloptmoduledatashiveresult。txt1 3。退出Hive窗口hive（default）hive（default）12 4。在hivecli命令窗口中如何查看hdfs文件系统hive（default）1 5。查看在hive中输入的所有历史命令 （1）进入到当前用户的根目录root或homeTom （2）查看。hivehistory文件〔Tomhadoop102〕cat。hivehistory12。7Hive常见属性配置 1。Hive运行日志信息配置 （1）Hive的log默认存放在tmpTomhive。log目录下（当前用户名下） （2）修改hive的log存放日志到optmodulehive3。1。2logs 修改optmodulehiveconfhivelog4j2。properties。template文件名称为hivelog4j2。properties〔Tomhadoop102conf〕pwdoptmodulehive3。1。2conf〔Tomhadoop102conf〕mvhivelog4j2。properties。templatehivelog4j2。properties123 在hivelog4j2。properties文件中修改log存放位置hive。log。diroptmodulehivelogs1 2。打印当前库和表头 在hivesite。xml中加入如下两个配置：propertynamehive。cli。print。headernamevaluetruevaluepropertypropertynamehive。cli。print。current。dbnamevaluetruevalueproperty12345678 3。参数配置方式 （1）查看当前所有的配置信息1 （2）参数的配置三种方式 默认配置文件：hivedefault。xml 用户自定义配置文件：hivesite。xml 注意：用户自定义配置会覆盖默认配置。另外，Hive也会读入Hadoop的配置，因为Hive是作为Hadoop的客户端启动的，Hive的配置会覆盖Hadoop的配置。配置文件的设定对本机启动的所有Hive进程都有效。 命令行参数方式 启动Hive时，可以在命令行添加hiveconfparamvalue来设定参数。如：〔Tomhadoop102hive3。1。2〕binhivehiveconfmapred。reduce。tasks10；1 注意：仅对本次hive启动有效 查看参数设置：hive（default）setmapred。reduce。1 参数声明方式 可以在HQL中使用SET关键字设定参数，如：〔Tomhadoop102hive3。1。2〕binhivehiveconfmapred。reduce。tasks100；1 注意：仅对本次hive启动有效 查看参数设置：hive（default）setmapred。reduce。1 上述三种设定方式的优先级依次递增。即配置文件命令行参数参数声明。注意某些系统级的参数，例如log4j相关的设定，必须用前两种方式设定，因为那些参数的读取在会话建立以前已经完成了。3Hive数据类型3。1基本数据类型 对于Hive的String类型相当于数据库的varchar类型，该类型是一个可变的字符串，不过它不能声明其中最多能存储多少个字符，理论上它可以存储2GB的字符数。3。2集合数据类型 Hive有三种复杂数据类型：ARRAY、MAP和STRUCT。ARRAY和MAP与Java中的Array和Map类似，而STRUCT与C语言中的Struct类似，它封装了一个命名字段集合，复杂数据类型允许任意层次的嵌套。 案例实操 （1）假设某表有如下一行，我们用JSON格式来表示其数据结构。在Hive下访问的格式为 （2）基于上述数据结构，我们在Hive里创建对应的表，并导入数据。 创建本地测试文件test。txtsongsong，bingbinglili，xiaosong：18xiaoxiaosong：19，huilongguanbeijingyangyang，caicaisusu，xiaoyang：18xiaoxiaoyang：19，chaoyangbeijing12 注意MAP，STRUCT和ARRAY里的元素间关系都可以用同一个字符表示，这里用。 （3）Hive上创建测试表testhive（default）createtabletest（namestring，friendsarraystring，childrenmapstring，int，addressstructstreet：string，city：string）rowformatdelimitedfieldsterminatedby，collectionitemsterminatedbymapkeysterminatedby：OKTimetaken：0。933seconds123456789101112 字段解释： rowformatdelimitedfieldsterminatedby‘，’列分隔符 collectionitemsterminatedby‘’MAPSTRUCT和ARRAY的分隔符（数据分割符号） mapkeysterminatedby‘：’MAP中的key与value的分隔符 linesterminatedby‘’；行分隔符 （4）导入文本数据到测试表hive（default）loaddatalocalinpathoptmodulehive3。1。2datatest。Loadingdatatotabledefault。testOKTimetaken：0。893seconds1234 （5）访问三种集合列里的数据，以下分别是ARRAY，MAP，STRUCT的访问方式hive（default）selectfriends〔1〕，children〔xiaosong〕，address。OKc0c1citylili18beijingTimetaken：0。386seconds，Fetched：1row（s）1234563。3类型转换 Hive的原子数据类型是可以进行隐式转换的，类似于Java的类型转换，例如某表达式使用INT类型，TINYINT会自动转换为INT类型，但是Hive不会进行反向转化，例如，某表达式使用TINYINT类型，INT不会自动转换为TINYINT类型，它会返回错误，除非使用CAST操作。 1。隐式类型转换规则如下 （1）任何整数类型都可以隐式地转换为一个范围更广的类型，如TINYINT可以转换成INT，INT可以转换成BIGINT。 （2）所有整数类型、FLOAT和STRING类型都可以隐式地转换成DOUBLE。 （3）TINYINT、SMALLINT、INT都可以转换为FLOAT。 （4）BOOLEAN类型不可以转换为任何其它的类型。 2。可以使用CAST操作显示进行数据类型转换 例如CAST（‘1’ASINT）将把字符串’1’转换成整数1；如果强制类型转换失败，如执行CAST（‘X’ASINT），表达式返回空值NULL。（二）DDL数据定义、DML数据操作1DDL数据定义1。1创建数据库CREATEDATABASE〔IFNOTEXISTS〕databasename〔COMMENTdatabasecomment〕〔LOCATIONhdfspath〕〔WITHDBPROPERTIES（propertynamepropertyvalue，。。。）〕；1234 （1）创建一个数据库，数据库在HDFS上的默认存储路径是userhivewarehouse。db。hive（default）OKTimetaken：0。12seconds123 （2）避免要创建的数据库已经存在错误，增加ifnotexists判断。（标准写法）hive（default）FAILED：ExecutionError，returncode1fromorg。apache。hadoop。hive。ql。exec。DDLTask。Databasedbhivealreadyexistshive（default）OKTimetaken：0。044seconds12345 （3）创建一个数据库，指定数据库在HDFS上存放的位置hive（default）createdatabasedbhive2locationdbhive2。OKTimetaken：0。07seconds123 1。2查询数据库 1。显示数据库 （1）显示数据库hive（default）OKdatabasenamedbhivedbhive2defaultTimetaken：0。054seconds，Fetched：3row（s）1234567 （2）过滤显示查询的数据库hive（default）OKdatabasenamedbhivedbhive2Timetaken：0。036seconds，Fetched：2row（s）123456 2。查看数据库详情 （1）显示数据库信息hive（default）OKdbnamecommentlocationownernameownertypeparametersdbhivehdfs：hadoop102：8020userhivewarehousedbhive。dbTomUSERTimetaken：0。057seconds，Fetched：1row（s）12345 （2）显示数据库详细信息，extendedhive（default）OKdbnamecommentlocationownernameownertypeparametersdbhivehdfs：hadoop102：8020userhivewarehousedbhive。dbTomUSERTimetaken：0。074seconds，Fetched：1row（s）12345 3。切换当前数据库hive（default）OKTimetaken：0。057secondshive（dbhive）12341。3修改数据库 用户可以使用ALTERDATABASE命令为某个数据库的DBPROPERTIES设置键值对属性值来描述这个数据库的属性信息。hive（dbhive）alterdatabasedbhivesetdbproperties（createtime20210828）；OKTimetaken：0。101seconds1234 在hive中查看修改结果hive（dbhive）OKdbnamecommentlocationownernameownertypeparametersdbhivehdfs：hadoop102：8020userhivewarehousedbhive。dbTomUSER｛createtime20210828｝Timetaken：0。072seconds，Fetched：1row（s）123451。4删除数据库 （1）删除空数据库hive（dbhive）dropdatabasedbhive2；OKTimetaken：0。095seconds123 （2）如果删除的数据库不存在，最好采用ifexists判断数据库是否存在hive（dbhive）dropdatabasedbhive2；FAILED：SemanticException〔Error10072〕：Databasedoesnotexist：dbhive2hive（dbhive）dropdatabaseifexistsdbhive2；OKTimetaken：0。025seconds12345 （3）如果数据库不为空，可以采用cascade命令，强制删除hive（dbhive）FAILED：ExecutionError，returncode1fromorg。apache。hadoop。hive。ql。exec。DDLTask。InvalidOperationException（message：Databasedbhiveisnotempty。Oneormoretablesexist。）hive（dbhive）OKTimetaken：0。364seconds123451。5创建表 1。建表语法CREATE〔EXTERNAL〕TABLE〔IFNOTEXISTS〕tablename〔（colnamedatatype〔COMMENTcolcomment〕，。。。）〕〔COMMENTtablecomment〕〔PARTITIONEDBY（colnamedatatype〔COMMENTcolcomment〕，。。。）〕〔CLUSTEREDBY（colname，colname，。。。）〔SORTEDBY（colname〔ASCDESC〕，。。。）〕INTOnumbucketsBUCKETS〕〔ROWFORMATrowformat〕〔STOREDASfileformat〕〔LOCATIONhdfspath〕〔TBLPROPERTIES（propertynamepropertyvalue，。。。）〕〔ASselectstatement〕1234567891011 2。字段解释说明 （1）CREATETABLE：创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常，用户可以用IFNOTEXISTS选项来忽略这个异常。 （2）EXTERNAL关键字：可以让用户创建一个外部表，在建表的同时，可以指定一个指向实际数据的路径（LOCATION），在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。 （3）COMMENT：为表和列添加注释。 （4）PARTITIONEDBY：创建分区表。 （5）CLUSTEREDBY：创建分桶表。 （6）SORTEDBY：不常用，对桶中的一个或多个列另外排序。 （7）ROWFORMAT DELIMITED〔FIELDSTERMINATEDBYchar〕〔COLLECTIONITEMSTERMINATEDBYchar〕 〔MAPKEYSTERMINATEDBYchar〕〔LINESTERMINATEDBYchar〕 SERDEserdename〔WITHSERDEPROPERTIES（propertynamepropertyvalue，propertynamepropertyvalue，）〕 用户在建表的时候可以自定义SerDe或者使用自带的SerDe。如果没有指定ROWFORMAT或者ROWFORMATDELIMITED，将会使用自带的SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的SerDe，Hive通过SerDe确定表的具体的列的数据。 SerDe是SerializeDeserilize的简称，hive使用Serde进行行对象的序列与反序列化。 （8）STOREDAS：指定存储文件类型 常用的存储文件类型：SEQUENCEFILE（二进制序列文件）、TEXTFILE（文本）、RCFILE（列 式存储格式文件） 如果文件数据是纯文本，可以使用STOREDASTEXTFILE。如果数据需要压缩，使用STORED ASSEQUENCEFILE。 （9）LOCATION：指定表在HDFS上的存储位置。 （10）AS：后跟查询语句根据查询结果创建表。 （11）LIKE：允许用户复制现有的表结构，但是不复制数据。1。5。1管理表 1。理论 默认创建的表都是所谓的管理表，有时也被称为内部表。因为这种表，Hive会（或多或少地）控制着数据的生命周期。Hive默认情况下会将这些表的数据存储在由配置hive。metastore。warehouse。dir（例如，userhivewarehouse）所定义的目录的子目录下。 当我们删除一个管理表时，Hive也会删除这个表中数据。管理表不适合和其他工具共享数据。 2。案例实操 由于Hive是针对数据仓库应用设计的，而数据仓库的内容是读多写少的。因此，Hive中不建议对数据的改写，所有的数据都是在加载的时候确定好的。而数据库中的数据通常是需要经常进行修改的，因此可以使用INSERTINTOVALUES添加数据，使用UPDATESET修改数据。 （1）普通创建表hive（default）createtableifnotexistsstudent（idint，namestring）OKTimetaken：0。397seconds12345678 （2）根据查询结果创建表（查询的结果会添加到新创建的表中）hive（default）createtableifnotexistsstudent2asselectid，1 （3）根据已经存在的表结构创建表hive（default）createtableifnotexistsstudent3OKTimetaken：0。145seconds123 （4）查询表的类型hive（default）descformattedstudent2；TableType：MANAGEDTABLE121。5。2外部表 1。理论 因为表是外部表，所以Hive并非认为其完全拥有这份数据。删除该表并不会删除掉这份数据，不过描述表的元数据信息会被删除掉。 2。管理表和外部表的使用场景 每天将收集到的网站日志定期流入HDFS文本文件。在外部表（原始日志表）的基础上做大量的统计分析，用到的中间表、结果表使用内部表存储，数据通过SELECTINSERT进入内部表。 3。案例实操 创建学生表 student：1001ss11002ss21003ss31004ss41005ss51006ss61007ss71008ss81009ss91010ss101011ss11012ss121013ss131014ss141015ss151016ss1612345678910111213141516 （1）上传数据到HDFShive（default）hive（default）dfsputoptmodulehive3。1。2datastudent。12 （2）创建表并载入数据hive（default）createexternaltableifnotexistsstudent（idint，namestring）OKTimetaken：0。091secondshive（default）loaddatainpathstudentstudent。txtintotabledefault。Loadingdatatotabledefault。studentOKTimetaken：0。419seconds1234567891011 （3）查看表的类型hive（default）TableType：EXTERNALTABLE12 （4）删除外部表hive（default）OKTimetaken：0。168seconds123 外部表删除后，hdfs中的数据还在，但是metadata中dept的元数据已被删除。1。5。3管理表与外部表的互相转换 （1）查询表的类型hive（default）descformattedstudent2；TableType：MANAGEDTABLE12 （2）修改内部表student2为外部表hive（default）altertablestudent2settblproperties（EXTERNALTRUE）；OKTimetaken：0。174seconds123 （3）查询表的类型TableType：EXTERNALTABLE1 （4）修改外部表student2为内部表hive（default）altertablestudent2settblproperties（EXTERNALFALSE）；OKTimetaken：0。153seconds123 （5）查询表的类型TableType：MANAGEDTABLE1 注意：（‘EXTERNAL’‘TRUE’）和（‘EXTERNAL’‘FALSE’）为固定写法，区分大小写1。6修改表 1。重命名表 （1）语法ALTERTABLEtablenameRENAMETOnewtablename1 （2）实操案例hive（default）altertablestudent2renametostudent4；OKTimetaken：0。258seconds123 2。增加修改替换列信息 更新列ALTERTABLEtablenameCHANGE〔COLUMN〕cololdnamecolnewnamecolumntype〔COMMENTcolcomment〕〔FIRSTAFTERcolumnname〕12 增加和替换列ALTERTABLEtablenameADDREPLACECOLUMNS（colnamedatatype〔COMMENTcolcomment〕，。。。）12 ADD是代表新增一字段，字段位置在所有列后面（partition列前），REPLACE则是表示替换表中所有字段。 （1）查询表结构hive（default）OKcolnamedatatypecommentdeptnointdnamestringlocintTimetaken：0。099seconds，Fetched：3row（s）1234567 （2）添加列hive（default）altertabledeptaddcolumns（deptdescstring）；OKTimetaken：0。138seconds123 （3）查询表结构hive（default）OKcolnamedatatypecommentdeptnointdnamestringlocintdeptdescstringTimetaken：0。056seconds，Fetched：4row（s）12345678 （3）更新列hive（default）OKTimetaken：0。192seconds123 （3）查询表结构hive（default）OKcolnamedatatypecommentdeptnointdnamestringlocintdescstringTimetaken：0。064seconds，Fetched：4row（s）12345678 （3）替换列hive（default）altertabledeptreplacecolumns（deptnostring，dnamestring，locstring）；OKTimetaken：0。149seconds123 （3）查询表结构hive（default）OKcolnamedatatypecommentdeptnostringdnamestringlocstringTimetaken：0。064seconds，Fetched：3row（s）12345671。7删除表hive（default）OKTimetaken：1。901seconds1232DML数据操作2。1数据导入 1。向表中装载数据（load）hiveloaddata〔local〕inpath数据的pathoverwriteintotablestudent〔partition（partcol1val1，）〕；12 （1）loaddata：表示加载数据 （2）local：表示从本地加载数据到hive表，否则从HDFS加载数据到hive表 （3）inpath：表示加载数据的路径 （4）overwrite：表示覆盖表中已有数据，否则表示追加 （5）intotable：表示加载到哪张表 （6）student：表示具体的表 （7）partition：表示上传到指定分区 2。实操案例 （1）创建一张表hive（default）createtablestudent（idstring，namestring）OKTimetaken：0。878seconds1234 （2）加载本地文件到hivehive（default）loaddatalocalinpathoptmodulehive3。1。2datastudent。Loadingdatatotabledefault。studentOKTimetaken：2。725seconds1234 （3）加载HDFS文件到hive中hive（default）dfsputoptmodulehive3。1。2datastudent。hive（default）Loadingdatatotabledefault。studentOKTimetaken：0。712seconds12345 （4）加载数据覆盖表中已有的数据hive（default）dfsputoptmodulehive3。1。2datastudent。hive（default）loaddatainpathstudentstudent。Loadingdatatotabledefault。studentOKTimetaken：0。501seconds12345 3。通过查询语句向表中插入数据（Insert） （1）创建一张表hive（default）createtablestudentpar（idint，namestring）OKTimetaken：0。225seconds1234 （2）基本插入数据hive（default）insertintotablestudentparvalues（1，liubai），（2，junmo）；12 （3）基本模式插入（根据单张表查询结果）hive（default）insertoverwritetablestudentparselectid，namefromstudentwheremonth201609；12 insertinto：：以追加数据的方式插入到表或分区，原有数据不会删除 insertoverwrite：会覆盖表中已存在的数据 注意：insert不支持插入部分字段 （4）多表（多分区）插入模式（根据多张表查询结果）hive（default）fromstudentinsertoverwritetablestudentpartition（month201707）selectid，namewheremonth201709insertoverwritetablestudentpartition（month201706）selectid，namewheremonth201709；12345 4。查询语句中创建表并加载数据（AsSelect） 根据查询结果创建表（查询的结果会添加到新创建的表中）hive（default）createtableifnotexistsstudent3asselectid，12 5。创建表时通过Location指定加载数据路径 （1）上传数据到hdfs上hive（default）dfsputoptmodulehive3。1。2datastudent。1 （2）创建表，并指定在hdfs上的位置hive（default）createexternaltableifnotexistsstudent5（idint，namestring）Timetaken：0。111seconds123456 （3）查询数据hive（default）selectfromstudent5；OKstudent5。idstudent5。name1001ss11002ss21003ss31004ss41005ss51006ss61007ss71008ss81009ss91010ss101011ss111012ss121013ss131014ss141015ss151016ss16Timetaken：0。275seconds，Fetched：16row（s）1234567891011121314151617181920 6。Import数据到指定Hive表中 注意：先用export导出后，再将数据导入。hive（default）importtablestudent2Copyingdatafromhdfs：hadoop102：8020studentdataCopyingfile：hdfs：hadoop102：8020studentdatastudent。txtLoadingdatatotabledefault。student2OKTimetaken：1。234seconds12345672。2数据导出 1。Insert导出 （1）将查询的结果导出到本地hive（default）insertoverwritelocaldirectoryoptmodulehive3。1。212 （2）将查询的结果格式化导出到本地hive（default）insertoverwritelocaldirectoryoptmodulehive3。1。21234 （3）将查询的结果导出到HDFS上（没有local）hive（default）123 2。Hadoop命令导出到本地hive（default）dfsgetuserhivewarehousestudentstudent。txtoptmodulehive3。1。21 3。HiveShell命令导出 基本语法：hivefe执行语句或者脚本file〔Tomhadoop102hive3。1。2〕binhiveeselectfromdefault。optmodulehive3。1。2datastudentstudent2。1 4。Export导出到HDFS上 export和import主要用于两个Hadoop平台集群之间Hive表迁移。hive（default）exporttabledefault。OKTimetaken：1。434seconds1234 5。清除表中数据（truncate） 注意：Truncate只能删除管理表，不能删除外部表中数据hive（default）OKTimetaken：0。353seconds（三）查询、分区表和分桶表1查询 查询语句语法：SELECT〔ALLDISTINCT〕selectexpr，selectexpr，。。。FROMtablereference〔WHEREwherecondition〕〔GROUPBYcollist〕〔ORDERBYcollist〕〔CLUSTERBYcollist〕〔DISTRIBUTEBYcollist〕〔SORTBYcollist〕〔LIMITnumber〕123456781。1基本查询（SelectFrom）1。1。1全表和特定列查询 1。数据准备 （1）原始数据 dept：10ACCOUNTING170020RESEARCH180030SALES190040OPERATIONS17001234 emp：369SMITHCLERK790219801217800。00207499ALLENSALESMAN769819812201600。00300。00307521WARDSALESMAN769819812221250。00500。00307566JONESMANAGER78391981422975。00207654MARTINSALESMAN769819819281250。001400。00307698BLAKEMANAGER78391981512850。00307782CLARKMANAGER78391981692450。00107788SCOTTANALYST756619874193000。00207839KINGPRESIDENT198111175000。00107844TURNERSALESMAN76981981981500。000。00307876ADAMSCLERK778819875231100。00207900JAMESCLERK76981981123950。00307902FORDANALYST756619811233000。00207934MILLERCLERK778219821231300。00107534MILLERCLERK7782198210231300。0050123456789101112131415 （2）创建部门表createtableifnotexistsdept（deptnoint，dnamestring，locint）123456 （3）创建员工表createtableifnotexistsemp（empnoint，enamestring，jobstring，mgrint，hiredatestring，saldouble，commdouble，deptnoint）12345678910 （4）导入数据hive（default）loaddatalocalinpathoptmodulehive3。1。2datadept。hive（default）loaddatalocalinpathoptmodulehive3。1。2dataemp。12 2。全表查询hive（default）OKemp。empnoemp。enameemp。jobemp。mgremp。hiredateemp。salemp。commemp。deptno7369SMITHCLERK790219801217800。0NULL207499ALLENSALESMAN769819812201600。0300。0307521WARDSALESMAN769819812221250。0500。0307566JONESMANAGER78391981422975。0NULL207654MARTINSALESMAN769819819281250。01400。0307698BLAKEMANAGER78391981512850。0NULL307782CLARKMANAGER78391981692450。0NULL107788SCOTTANALYST756619874193000。0NULL207839KINGPRESIDENTNULL198111175000。0NULL107844TURNERSALESMAN76981981981500。00。0307876ADAMSCLERK778819875231100。0NULL207900JAMESCLERK76981981123950。0NULL307902FORDANALYST756619811233000。0NULL207934MILLERCLERK778219821231300。0NULL107534MILLERCLERK7782198210231300。0NULL50Timetaken：0。271seconds，Fetched：15row（s）hive（default）selectempno，ename，job，mgr，hiredate，sal，comm，OKempnoenamejobmgrhiredatesalcommdeptno7369SMITHCLERK790219801217800。0NULL207499ALLENSALESMAN769819812201600。0300。0307521WARDSALESMAN769819812221250。0500。0307566JONESMANAGER78391981422975。0NULL207654MARTINSALESMAN769819819281250。01400。0307698BLAKEMANAGER78391981512850。0NULL307782CLARKMANAGER78391981692450。0NULL107788SCOTTANALYST756619874193000。0NULL207839KINGPRESIDENTNULL198111175000。0NULL107844TURNERSALESMAN76981981981500。00。0307876ADAMSCLERK778819875231100。0NULL207900JAMESCLERK76981981123950。0NULL307902FORDANALYST756619811233000。0NULL207934MILLERCLERK778219821231300。0NULL107534MILLERCLERK7782198210231300。0NULL50Timetaken：0。323seconds，Fetched：15row（s）1234567891011121314151617181920212223242526272829303132333435363738 3。选择特定列查询hive（default）selectempno，OKempnoename7369SMITH7499ALLEN7521WARD7566JONES7654MARTIN7698BLAKE7782CLARK7788SCOTT7839KING7844TURNER7876ADAMS7900JAMES7902FORD7934MILLER7534MILLERTimetaken：0。279seconds，Fetched：15row（s）12345678910111213141516171819 注意： （1）SQL语言大小写不敏感。 （2）SQL可以写在一行或者多行 （3）关键字不能被缩写也不能分行 （4）各子句一般要分行写。 （5）使用缩进提高语句的可读性。1。1。2列别名 重命名一个列便于计算。命名方法为紧跟列名，也可以在列名和别名之间加入关键字AS。 实例：查询名称和部门hive（default）selectenameasname，OKnamednSMITH20ALLEN30WARD30JONES20MARTIN30BLAKE30CLARK10SCOTT20KING10TURNER30ADAMS20JAMES30FORD20MILLER10MILLER50Timetaken：0。289seconds，Fetched：15row（s）123456789101112131415161718191。1。3算术运算符 案例实操：查询出所有员工的薪水后加1显示。hive（default）selectsal1OKc0801。01601。01251。02976。01251。02851。02451。03001。05001。01501。01101。0951。03001。01301。01301。0Timetaken：0。79seconds，Fetched：15row（s）123456789101112131415161718191。1。4常用函数 （1）求总行数（count）hive（default）selectcount（）OKcnt15Timetaken：20。637seconds，Fetched：1row（s）12345 （2）求工资的最大值（max）hive（default）selectmax（sal）OKmaxsal5000。0Timetaken：19。305seconds，Fetched：1row（s）12345 （3）求工资的最小值（min）hive（default）selectmin（sal）OKminsal800。0Timetaken：31。402seconds，Fetched：1row（s）12345 （4）求工资的总和（sum）hive（default）selectsum（sal）OKsumsal30325。0Timetaken：8。185seconds，Fetched：1row（s）12345 （5）求工资的平均值（avg）hive（default）selectavg（sal）OKavgsal2021。6666666666667Timetaken：8。706seconds，Fetched：1row（s）123451。1。5Limit语句 典型的查询会返回多行数据。LIMIT子句用于限制返回的行数。hive（default）selectfromemplimit3；OKemp。empnoemp。enameemp。jobemp。mgremp。hiredateemp。salemp。commemp。deptno7369SMITHCLERK790219801217800。0NULL207499ALLENSALESMAN769819812201600。0300。0307521WARDSALESMAN769819812221250。0500。030Timetaken：0。318seconds，Fetched：3row（s）hive（default）selectsalfromemplimit5；OKsal800。01600。01250。02975。01250。0Timetaken：0。298seconds，Fetched：5row（s）123456789101112131415161。1。6Where语句 使用WHERE子句，将不满足条件的行过滤掉。 WHERE子句紧随FROM子句。WHERE子句中不能使用字段别名。 例：查询出薪水大于2000的所有员工：hive（default）selectfromempwheresal2000；OKemp。empnoemp。enameemp。jobemp。mgremp。hiredateemp。salemp。commemp。deptno7566JONESMANAGER78391981422975。0NULL207698BLAKEMANAGER78391981512850。0NULL307782CLARKMANAGER78391981692450。0NULL107788SCOTTANALYST756619874193000。0NULL207839KINGPRESIDENTNULL198111175000。0NULL107902FORDANALYST756619811233000。0NULL20Timetaken：0。324seconds，Fetched：6row（s）123456789101。1。7比较运算符（BetweenInIsNull） 下面表中描述了谓词操作符，这些操作符同样可以用于JOINON和HAVING语句中。 例：（1）查询出薪水等于5000的所有员工hive（default）selectfromempwheresal5000；OKemp。empnoemp。enameemp。jobemp。mgremp。hiredateemp。salemp。commemp。deptno7839KINGPRESIDENTNULL198111175000。0NULL10Timetaken：0。312seconds，Fetched：1row（s）12345 （2）查询工资在800到950的员工信息hive（default）selectfromempwheresalbetween800and950；OKemp。empnoemp。enameemp。jobemp。mgremp。hiredateemp。salemp。commemp。deptno7369SMITHCLERK790219801217800。0NULL207900JAMESCLERK76981981123950。0NULL30Timetaken：0。267seconds，Fetched：2row（s）123456 （3）查询comm为空的所有员工信息hive（default）OKemp。empnoemp。enameemp。jobemp。mgremp。hiredateemp。salemp。commemp。deptno7369SMITHCLERK790219801217800。0NULL207566JONESMANAGER78391981422975。0NULL207698BLAKEMANAGER78391981512850。0NULL307782CLARKMANAGER78391981692450。0NULL107788SCOTTANALYST756619874193000。0NULL207839KINGPRESIDENTNULL198111175000。0NULL107876ADAMSCLERK778819875231100。0NULL207900JAMESCLERK76981981123950。0NULL307902FORDANALYST756619811233000。0NULL207934MILLERCLERK778219821231300。0NULL107534MILLERCLERK7782198210231300。0NULL50Timetaken：0。283seconds，Fetched：11row（s）123456789101112131415 （4）查询工资是1500或5000的员工信息hive（default）selectfromempwheresalin（1500，5000）；OKemp。empnoemp。enameemp。jobemp。mgremp。hiredateemp。salemp。commemp。deptno7839KINGPRESIDENTNULL198111175000。0NULL107844TURNERSALESMAN76981981981500。00。030Timetaken：0。299seconds，Fetched：2row（s）1234561。1。8Like和RLike （1）使用LIKE运算选择类似的值。 （2）选择条件可以包含字符或数字。 代表零个或多个字符任意个字符。 代表一个字符。 （3）RLIKE子句是Hive中这个功能的一个扩展，其可以通过Java的正则表达式这个更强大的语言来指定匹配条件。 案例实操 查找名字以A开头的员工信息hive（default）selectfromempwhereenamelikeA；OKemp。empnoemp。enameemp。jobemp。mgremp。hiredateemp。salemp。commemp。deptno7499ALLENSALESMAN769819812201600。0300。0307876ADAMSCLERK778819875231100。0NULL2012345 查找名字中第二个字母为A的员工信息hive（default）selectfromempwhereenamelikeA；OKemp。empnoemp。enameemp。jobemp。mgremp。hiredateemp。salemp。commemp。deptno7521WARDSALESMAN769819812221250。0500。0307654MARTINSALESMAN769819819281250。01400。0307900JAMESCLERK76981981123950。0NULL30Timetaken：0。301seconds，Fetched：3row（s）1234567 查找名字中带有A的员工信息hive（default）selectfromempwhereenamerlike〔A〕；OKemp。empnoemp。enameemp。jobemp。mgremp。hiredateemp。salemp。commemp。deptno7499ALLENSALESMAN769819812201600。0300。0307521WARDSALESMAN769819812221250。0500。0307654MARTINSALESMAN769819819281250。01400。0307698BLAKEMANAGER78391981512850。0NULL307782CLARKMANAGER78391981692450。0NULL107876ADAMSCLERK778819875231100。0NULL207900JAMESCLERK76981981123950。0NULL30Timetaken：0。247seconds，Fetched：7row（s）12345678910111。1。9逻辑运算符（AndOrNot） 案例实操 （1）查询薪水大于1000，部门是30hive（default）selectfromempwheresal1000anddeptno30；OKemp。empnoemp。enameemp。jobemp。mgremp。hiredateemp。salemp。commemp。deptno7499ALLENSALESMAN769819812201600。0300。0307521WARDSALESMAN769819812221250。0500。0307654MARTINSALESMAN769819819281250。01400。0307698BLAKEMANAGER78391981512850。0NULL307844TURNERSALESMAN76981981981500。00。030Timetaken：0。294seconds，Fetched：5row（s）123456789 （2）查询薪水大于1000，或者部门是30hive（default）selectfromempwheresal1000ordeptno30；OKemp。empnoemp。enameemp。jobemp。mgremp。hiredateemp。salemp。commemp。deptno7499ALLENSALESMAN769819812201600。0300。0307521WARDSALESMAN769819812221250。0500。0307566JONESMANAGER78391981422975。0NULL207654MARTINSALESMAN769819819281250。01400。0307698BLAKEMANAGER78391981512850。0NULL307782CLARKMANAGER78391981692450。0NULL107788SCOTTANALYST756619874193000。0NULL207839KINGPRESIDENTNULL198111175000。0NULL107844TURNERSALESMAN76981981981500。00。0307876ADAMSCLERK778819875231100。0NULL207900JAMESCLERK76981981123950。0NULL307902FORDANALYST756619811233000。0NULL207934MILLERCLERK778219821231300。0NULL107534MILLERCLERK7782198210231300。0NULL50Timetaken：0。232seconds，Fetched：14row（s）123456789101112131415161718 （3）查询除了20部门和30部门以外的员工信息hive（default）selectfromempwheredeptnonotin（20，30）；OKemp。empnoemp。enameemp。jobemp。mgremp。hiredateemp。salemp。commemp。deptno7782CLARKMANAGER78391981692450。0NULL107839KINGPRESIDENTNULL198111175000。0NULL107934MILLERCLERK778219821231300。0NULL107534MILLERCLERK7782198210231300。0NULL50Timetaken：0。257seconds，Fetched：4row（s）123456781。2分组1。2。1GroupBy语句 GROUPBY语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，然后对每个组执行聚合操作。 案例实操 （1）计算emp表每个部门的平均工资hive（default）selectt。deptno，avg（t。sal）avgsalfromemptgroupbyt。OKt。deptnoavgsal102916。6666666666665202175。0301566。6666666666667501300。0Timetaken：33。449seconds，Fetched：4row（s）12345678 （2）计算emp每个部门中每个岗位的最高薪水hive（default）selectt。deptno，t。job，max（t。sal）maxsalfromemptgroupbyt。deptno，t。OKt。deptnot。jobmaxsal20ANALYST3000。010CLERK1300。020CLERK1100。030CLERK950。050CLERK1300。010MANAGER2450。020MANAGER2975。030MANAGER2850。010PRESIDENT5000。030SALESMAN1600。0Timetaken：10。678seconds，Fetched：10row（s）1234567891011121314151。2。2Having语句 1。having与where不同点 （1）where后面不能写分组函数而having后面可以使用分组函数。 （2）having只用于groupby分组统计语句。 2。案例实操 求每个部门的平均薪水大于2000的部门hive（default）selectdeptno，avg（sal）avgsalfromempgroupbydeptnohavingavgsal2000；OKdeptnoavgsal102916。6666666666665202175。0Timetaken：21。843seconds，Fetched：2row（s）hive（default）selectdeptno，avgsalfrom（selectdeptno，avg（sal）avgsalfromempgroupbydeptno）t1whereavgsal2000；OKdeptnoavgsal102916。6666666666665202175。0Timetaken：35。768seconds，Fetched：2row（s）123456789101112131415161。3Join语句1。3。1等值Join Hive支持通常的SQLJOIN语句。 例：根据员工表和部门表中的部门编号相等，查询员工编号、员工名称、部门编号和部门名称hive（default）selecte。empno，e。ename，d。deptno，d。dnamefromempejoindeptdone。deptnod。OKe。empnoe。enamed。deptnod。dname7499ALLEN30SALES7521WARD30SALES7654MARTIN30SALES7698BLAKE30SALES7844TURNER30SALES7900JAMES30SALES7369SMITH20RESEARCH7566JONES20RESEARCH7788SCOTT20RESEARCH7876ADAMS20RESEARCH7902FORD20RESEARCH7782CLARK10ACCOUNTING7839KING10ACCOUNTING7934MILLER10ACCOUNTINGTimetaken：44。828seconds，Fetched：14row（s）1234567891011121314151617181。3。2表的别名 好处：使用别名可以简化查询；使用表名前缀可以提高执行效率。1。3。3内连接 内连接：只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。hive（default）selecte。empno，e。ename，d。deptnofromempejoindeptdone。deptnod。OKe。empnoe。enamed。deptno7499ALLEN307521WARD307654MARTIN307698BLAKE307844TURNER307900JAMES307369SMITH207566JONES207788SCOTT207876ADAMS207902FORD207782CLARK107839KING107934MILLER10Timetaken：27。323seconds，Fetched：14row（s）1234567891011121314151617181。3。4左外连接 左外连接：JOIN操作符左边表中符合WHERE子句的所有记录将会被返回。hive（default）selecte。empno，e。ename，d。deptnofromempeleftjoindeptdone。deptnod。OKe。empnoe。enamed。deptno7499ALLEN307521WARD307654MARTIN307698BLAKE307844TURNER307900JAMES307369SMITH207566JONES207788SCOTT207876ADAMS207902FORD207782CLARK107839KING107934MILLER107534MILLERNULLTimetaken：14。959seconds，Fetched：15row（s）123456789101112131415161718191。3。5右外连接 右外连接：JOIN操作符右边表中符合WHERE子句的所有记录将会被返回。hive（default）selecte。empno，e。ename，d。deptnofromemperightjoindeptdone。deptnod。OKe。empnoe。enamed。deptnoNULLNULL407782CLARK107934MILLER107839KING107499ALLEN307900JAMES307844TURNER307698BLAKE307654MARTIN307521WARD307369SMITH207902FORD207876ADAMS207788SCOTT207566JONES20Timetaken：14。322seconds，Fetched：15row（s）123456789101112131415161718191。3。6满外连接 满外连接：将会返回所有表中符合WHERE语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用NULL值替代。hive（default）selecte。empno，e。ename，d。deptnofromempefulljoindeptdone。deptnod。OKe。empnoe。enamed。deptno7934MILLER107782CLARK107839KING107369SMITH207566JONES207788SCOTT207876ADAMS207902FORD207521WARD307844TURNER307499ALLEN307900JAMES307698BLAKE307654MARTIN30NULLNULL407534MILLERNULLTimetaken：23。816seconds，Fetched：16row（s）12345678910111213141516171819201。3。7多表连接 注意：连接n个表，至少需要n1个连接条件。例如：连接三个表，至少需要两个连接条件。 数据准备：1700Beijing1800Shanghai1900Wuhan123 （1）创建位置表hive（default）createtableifnotexistslocation（locint，locnamestring）OKTimetaken：0。538seconds1234567 （2）导入数据hive（default）loaddatalocalinpathoptmodulehive3。1。2dataloc。Loadingdatatotabledefault。locationOKTimetaken：0。837seconds1234 （3）多表连接查询hive（default）selecte。ename，d。dname，l。locnamefromempejoindeptdond。deptnoe。deptnojoinlocationlond。locl。OKe。enamed。dnamel。locnameALLENSALESWuhanWARDSALESWuhanMARTINSALESWuhanBLAKESALESWuhanTURNERSALESWuhanJAMESSALESWuhanSMITHRESEARCHShanghaiJONESRESEARCHShanghaiSCOTTRESEARCHShanghaiADAMSRESEARCHShanghaiFORDRESEARCHShanghaiCLARKACCOUNTINGBeijingKINGACCOUNTINGBeijingMILLERACCOUNTINGBeijingTimetaken：34。34seconds，Fetched：14row（s）1234567891011121314151617181920212223 大多数情况下Hive会对每对JOIN连接对象启动一个MapReduce任务。本例中会首先启动一个MapReducejob对表e和表d进行连接操作，然后会再启动一个MapReducejob将第一个MapReducejob的输出和表l进行连接操作。 注意：为什么不是表d和表l先进行连接操作呢？ 这是因为Hive总是按照从左到右的顺序执行的。 优化：当对3个或者更多表进行join连接时，如果每个on子句都使用相同的连接键的话，那么只会产生一个MapReducejob。1。3。8笛卡尔积 笛卡尔集会在下面条件下产生： （1）省略连接条件 （2）连接条件无效 （3）所有表中的所有行互相连接 例：hive（default）selectempno，dnamefromemp，OKempnodname7369ACCOUNTING7369OPERATIONS7369SALES7369RESEARCH7499ACCOUNTING7499OPERATIONS7499SALES7499RESEARCH7521ACCOUNTING7521OPERATIONS7521SALES7521RESEARCH7566ACCOUNTING7566OPERATIONS7566SALES7566RESEARCH7654ACCOUNTING7654OPERATIONS7654SALES7654RESEARCH7698ACCOUNTING7698OPERATIONS7698SALES7698RESEARCH7782ACCOUNTING7782OPERATIONS7782SALES7782RESEARCH7788ACCOUNTING7788OPERATIONS7788SALES7788RESEARCH7839ACCOUNTING7839OPERATIONS7839SALES7839RESEARCH7844ACCOUNTING7844OPERATIONS7844SALES7844RESEARCH7876ACCOUNTING7876OPERATIONS7876SALES7876RESEARCH7900ACCOUNTING7900OPERATIONS7900SALES7900RESEARCH7902ACCOUNTING7902OPERATIONS7902SALES7902RESEARCH7934ACCOUNTING7934OPERATIONS7934SALES7934RESEARCH7534ACCOUNTING7534OPERATIONS7534SALES7534RESEARCHTimetaken：21。636seconds，Fetched：60row（s）123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263641。4排序1。4。1全局排序（OrderBy） OrderBy：全局排序，只有一个Reducer （1）使用ORDERBY子句排序 ASC（ascend）：升序（默认） DESC（descend）：降序 （2）ORDERBY子句在SELECT语句的结尾 案例实操： 查询员工信息按工资升序排列hive（default）selectfromemporderbysallimit5；OKemp。empnoemp。enameemp。jobemp。mgremp。hiredateemp。salemp。commemp。deptno7369SMITHCLERK790219801217800。0NULL207900JAMESCLERK76981981123950。0NULL307876ADAMSCLERK778819875231100。0NULL207521WARDSALESMAN769819812221250。0500。0307654MARTINSALESMAN769819819281250。01400。030Timetaken：30。52seconds，Fetched：5row（s）123456789 查询员工信息按工资降序排列hive（default）selectfromemporderbysaldesclimit5；OKemp。empnoemp。enameemp。jobemp。mgremp。hiredateemp。salemp。commemp。deptno7839KINGPRESIDENTNULL198111175000。0NULL107788SCOTTANALYST756619874193000。0NULL207902FORDANALYST756619811233000。0NULL207566JONESMANAGER78391981422975。0NULL207698BLAKEMANAGER78391981512850。0NULL30Timetaken：10。401seconds，Fetched：5row（s）1234567891。4。2按照别名排序 按照员工薪水的2倍排序hive（default）selectename，sal2twosalfromemporderbytwosallimit5；OKenametwosalSMITH1600。0JAMES1900。0ADAMS2200。0WARD2500。0MARTIN2500。0Timetaken：9。991seconds，Fetched：5row（s）1234567891。4。3多个列排序 按照部门和工资升序排序hive（default）selectename，deptno，salfromemporderbydeptno，sallimit5；OKenamedeptnosalMILLER101300。0CLARK102450。0KING105000。0SMITH20800。0ADAMS201100。0Timetaken：9。81seconds，Fetched：5row（s）1234567891。4。4每个Reduce内部排序（SortBy） SortBy：对于大规模的数据集orderby的效率非常低。在很多情况下并不需要全局排序，此时可以使用sortby。Sortby为每个reducer产生一个排序文件。每个Reducer内部进行排序，对全局结果集来说不是排序。 （1）设置reduce个数并查看hive（default）setmapreduce。job。mapreduce。job。reduces1hive（default）setmapreduce。job。reduces3；hive（default）setmapreduce。job。mapreduce。job。reduces312345 （2）根据部门编号降序查看员工信息hive（default）selectename，OKenamedeptnoMILLER50MARTIN30TURNER30BLAKE30SCOTT20CLARK10KING10WARD30ALLEN30JAMES30JONES20ADAMS20MILLER10FORD20SMITH20Timetaken：15。318seconds，Fetched：15row（s）12345678910111213141516171819 （3）将查询结果导入到文件中（按照部门编号降序排序）hive（default）insertoverwritelocaldirectoryoptmodulehive3。1。2datasortbyresultselectename，121。4。5分区（DistributeBy） DistributeBy：在有些情况下，我们需要控制某个特定行应该到哪个reducer，通常是为了进行后续的聚集操作。distributeby子句可以做这件事。distributeby类似MR中partition（自定义分区），进行分区，结合sortby使用。 对于distributeby进行测试，一定要分配多reduce进行处理，否则无法看到distributeby的效果。 例：先按照部门编号分区，再按照员工编号降序排序。hive（default）insertoverwritelocaldirectoryoptmodulehive3。1。212 注意：distributeby的分区规则是根据分区字段的hash码与reduce的个数进行模除后，余数相同的分到一个区。Hive要求DISTRIBUTEBY语句要写在SORTBY语句之前。1。4。6ClusterBy 当distributeby和sortsby字段相同时，可以使用clusterby方式。 clusterby除了具有distributeby的功能外还兼具sortby的功能。但是排序只能是升序排序，不能指定排序规则为ASC或者DESC。 以下两种写法等价：hive（default）hive（default）12 注意：按照部门编号分区，不一定就是固定死的数值，可以是20号和30号部门分到一个分区里面去。2分区表和分桶表2。1分区表 分区表实际上就是对应一个HDFS文件系统上的独立的文件夹，该文件夹下是该分区所有的数据文件。Hive中的分区就是分目录，把一个大的数据集根据业务需要分割成小的数据集。在查询时通过WHERE子句中的表达式选择查询所需要的指定的分区，这样的查询效率会提高很多。2。1。1分区表基本操作 1。创建分区表hive（default）createtableifnotexistsdeptpartition（deptnoint，dnamestring，locstring）partitionedby（daystring）OKTimetaken：0。238seconds1234567 注意：分区字段不能是表中已经存在的数据，可以将分区字段看作表的伪列。 2。加载数据到分区表中 （1）数据准备：dept1。txt：10ACCOUNTING170020RESEARCH1800dept2。txt：30SALES190040OPERATIONS1700dept3。txt：50TEST200060DEV19001234567891011 （2）加载数据：hive（default）loaddatalocalinpathoptmodulehive3。1。2datadept1。txtintotabledeptpartitionpartition（day20210901）；hive（default）loaddatalocalinpathoptmodulehive3。1。2datadept2。txtintotabledeptpartitionpartition（day20210902）；hive（default）loaddatalocalinpathoptmodulehive3。1。2datadept3。txtintotabledeptpartitionpartition（day20210903）；12345678 注意：分区表加载数据时，必须指定分区 3。查询分区表中数据 单分区查询deptpartition。deptnodeptpartition。dnamedeptpartition。locdeptpartition。day10ACCOUNTING17002021090120RESEARCH180020210901123 多分区联合查询hive（default）selectfromdeptpartitionwhereday20210901unionselectfromdeptpartitionwhereday20210902unionselectfromdeptpartitionwhereday20210903；hive（default）selectfromdeptpartitionwhereday20210901orday20210902orday20210903；1234567 4。增加分区 创建单个分区hive（default）altertabledeptpartitionaddpartition（day20210904）；OKTimetaken：0。348seconds123 同时创建多个分区hive（default）altertabledeptpartitionaddpartition（day20210905）partition（day20210906）；OKTimetaken：3。758seconds123 5。删除分区 删除单个分区hive（default）altertabledeptpartitiondroppartition（day20210906）；Droppedthepartitionday20210906OKTimetaken：1。283seconds1234 同时删除多个分区hive（default）altertabledeptpartitiondroppartition（day20210904），partition（day20210905）；Droppedthepartitionday20210904Droppedthepartitionday20210905OKTimetaken：0。764seconds12345 6。查看分区表有多少分区hive（default）OKpartitionday20210901day20210902day20210903Timetaken：0。636seconds，Fetched：3row（s）1234567 7。查看分区表结构hive（default）PartitionInformationcolnamedatatypecommentdaystring12342。1。2二级分区 思考：如果一天的日志数据量也很大，如何再将数据拆分？ 1。创建二级分区表hive（default）createtabledeptpartition2（deptint，dnamestring，locstring）partitionedby（daystring，hourstring）OKTimetaken：0。209seconds1234567 2。正常的加载数据 （1）加载数据到二级分区表中hive（default）loaddatalocalinpathoptmodulehive3。1。2datadept1。txtintotabledeptpartition2partition（day20210901，hour16）；Loadingdatatotabledefault。deptpartition2partition（day20210901，hour16）OKTimetaken：1。622seconds12345 （2）查询分区数据hive（default）selectfromdeptpartition2whereday20210901andhour16；OKdeptpartition2。deptdeptpartition2。dnamedeptpartition2。locdeptpartition2。daydeptpartition2。hour10ACCOUNTING1700202109011620RESEARCH18002021090116Timetaken：3。496seconds，Fetched：2row（s）123456 3。把数据直接上传到分区目录上，让分区表和数据产生关联的三种方式 （1）方式一：上传数据后修复 上传数据hive（default）dfsmkdirpuserhivewarehousedeptpartition2day20210901hour17；hive（default）dfsputoptmodulehive3。1。2datadept1。txtuserhivewarehousedeptpartition2day20210901hour17；12 查询数据（查询不到刚上传的数据）hive（default）selectfromdeptpartition2whereday20210901andhour17；OKdeptpartition2。deptdeptpartition2。dnamedeptpartition2。locdeptpartition2。daydeptpartition2。hourTimetaken：0。431seconds1234 执行修复命令hive（default）msckrepairtabledeptpartition2；OKPartitionsnotinmetastore：deptpartition2：day20210901hour17Repair：Addedpartitiontometastoredeptpartition2：day20210901hour17Timetaken：0。314seconds，Fetched：2row（s）12345 再次查询数据hive（default）selectfromdeptpartition2whereday20210901andhour17；OKdeptpartition2。deptdeptpartition2。dnamedeptpartition2。locdeptpartition2。daydeptpartition2。hour10ACCOUNTING1700202109011720RESEARCH18002021090117Timetaken：0。34seconds，Fetched：2row（s）123456 （2）方式二：上传数据后添加分区 上传数据hive（default）dfsmkdirpuserhivewarehousedeptpartition2day20210901hour18；hive（default）dfsputoptmodulehive3。1。2datadept1。txtuserhivewarehousedeptpartition2day20210901hour18；12 执行添加分区hive（default）altertabledeptpartition2addpartition（day20210901，hour18）；OKTimetaken：0。198seconds123 查询数据hive（default）selectfromdeptpartition2whereday20210901andhour18；OKdeptpartition2。deptdeptpartition2。dnamedeptpartition2。locdeptpartition2。daydeptpartition2。hour10ACCOUNTING1700202109011820RESEARCH18002021090118Timetaken：0。438seconds，Fetched：2row（s）123456 （3）方式三：创建文件夹后load数据到分区 创建目录hive（default）dfsmkdirpuserhivewarehousedeptpartition2day20210901hour19；1 上传数据hive（default）loaddatalocalinpathoptmodulehive3。1。2datadept1。txtintotabledeptpartition2partition（day20210901，hour19）；Loadingdatatotabledefault。deptpartition2partition（day20210901，hour19）OKTimetaken：0。778seconds12345 查询数据hive（default）selectfromdeptpartition2whereday20210901andhour19；OKdeptpartition2。deptdeptpartition2。dnamedeptpartition2。locdeptpartition2。daydeptpartition2。hour10ACCOUNTING1700202109011920RESEARCH18002021090119Timetaken：0。313seconds，Fetched：2row（s）1234562。1。3动态分区调整 关系型数据库中，对分区表Insert数据时候，数据库自动会根据分区字段的值，将数据插入到相应的分区中，Hive中也提供了类似的机制，即动态分区（DynamicPartition），只不过，使用Hive的动态分区，需要进行相应的配置。 1。开启动态分区参数设置 （1）开启动态分区功能（默认true，开启）hive（default）sethive。exec。dynamic。1 （2）设置为非严格模式（动态分区的模式，默认strict，表示必须指定至少一个分区为静态分区，nonstrict模式表示允许所有的分区字段都可以使用动态分区。）hive（default）sethive。exec。dynamic。partition。1 （3）在所有执行MR的节点上，最大一共可以创建多少个动态分区。默认1000hive（default）sethive。exec。max。dynamic。partitions1000；1 （4）在每个执行MR的节点上，最大可以创建多少个动态分区。该参数需要根据实际的数据来设定。比如：源数据中包含了一年的数据，即day字段有365个值，那么该参数就需要设置成大于365，如果使用默认值100，则会报错。hive（default）sethive。exec。max。dynamic。partitions。pernode100；1 （5）整个MRJob中，最大可以创建多少个HDFS文件。默认100000hive（default）sethive。exec。max。created。files100000；1 （6）当有空分区生成时，是否抛出异常。一般不需要设置。默认falsehive（default）sethive。error。on。empty。1 2。案例实操 需求：将dept表中的数据按照地区（loc字段）插入到目标表deptpartition的相应分区中。 （1）创建目标分区表hive（default）createtabledeptpartitionby（idint，namestring）partitionedby（locint）OKTimetaken：0。097seconds12345 （2）设置动态分区hive（default）sethive。exec。dynamic。partition。hive（default）insertintotabledeptpartitionbypartition（loc）selectdeptno，dname，123 （3）查看目标分区表的分区情况hive（default）OKpartitionloc1700loc1800loc1900Timetaken：0。152seconds，Fetched：3row（s）12345672。2分桶表 分区提供一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可形成合理的分区。对于一张表或者分区，Hive可以进一步组织成桶，也就是更为细粒度的数据范围划分。 分桶是将数据集分解成更容易管理的若干部分的另一个技术。 分区针对的是数据的存储路径；分桶针对的是数据文件。 1。创建分桶表 （1）数据准备1001ss11002ss21003ss31004ss41005ss51006ss61007ss71008ss81009ss91010ss101011ss111012ss121013ss131014ss141015ss151016ss1612345678910111213141516 （2）创建分桶表hive（default）createtablestubuck（idint，namestring）clusteredby（id）into4OKTimetaken：0。132seconds123456 （3）查看表结构hive（default）NumBuckets：412 （4）导入数据到分桶表中，load的方式hive（default）loaddatainpathstudentdatastudent。1 （5）查看创建的分桶表中是否分成4个桶 （6）查询分桶的数据hive（default）OKstubuck。idstubuck。name1016ss161012ss121008ss81004ss41013ss131009ss91005ss51001ss11014ss141010ss101006ss61002ss21015ss151011ss111007ss71003ss3Timetaken：0。274seconds，Fetched：16row（s）1234567891011121314151617181920 （7）分桶规则： 根据结果可知：Hive的分桶采用对分桶字段的值进行哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。 2。分桶表操作需要注意的事项： （1）reduce的个数设置为1，让Job自行决定需要用多少个reduce，或者将reduce的个数设置为大于等于分桶表的桶数。 （2）从hdfs中load数据到分桶表中，避免本地文件找不到问题。 （3）不要使用本地模式。 3。insert方式将数据导入分桶表hive（default）12。3抽样查询 对于非常大的数据集，有时用户需要使用的是一个具有代表性的查询结果而不是全部结果。Hive可以通过对表进行抽样来满足这个需求。 语法：TABLESAMPLE（BUCKETxOUTOFy） 查询表stubuck中的数据。hive（default）selectfromstubucktablesample（bucket1outof4onid）；OKstubuck。idstubuck。name1016ss161004ss41009ss91002ss21003ss3Timetaken：0。161seconds，Fetched：5row（s）123456789 若总共有n桶，则抽取ny桶数据，所以y需要是n的因子或倍数。从第x桶开始抽。本例中抽取441桶数据，从第1桶开始抽。 注意：x的值必须小于等于y的值，否则hive（default）selectfromstubucktablesample（bucket4outof1onid）；FAILED：SemanticException〔Error10061〕：Numeratorshouldnotbebiggerthandenominatorinsamplecl（四）函数、压缩和存储1函数1。1系统内置函数 1。查看系统自带的函数hive（default）1 2。显示自带的函数的用法hive（default）1 3。详细显示自带的函数的用法hive（default）11。2常用内置函数1。2。1空字段赋值 （1）函数说明 NVL：给值为NULL的数据赋值，它的格式是NVL（value，defaultvalue）。它的功能是如果value为NULL，则NVL函数返回defaultvalue的值，否则返回value的值。如果两个参数都为NULL，则返回NULL。 （2）数据准备：采用员工表 （3）查询：如果员工的comm为NULL，则用1代替hive（default）selectcomm，nvl（comm，1）OKcommc1NULL1。0300。0300。0500。0500。0NULL1。01400。01400。0NULL1。0NULL1。0NULL1。0NULL1。00。00。0NULL1。0NULL1。0NULL1。0NULL1。0NULL1。0Timetaken：2。891seconds，Fetched：15row（s）12345678910111213141516171819 （4）查询：如果员工的comm为NULL，则用领导id代替hive（default）selectcomm，nvl（comm，mgr）OKcommc1NULL7902。0300。0300。0500。0500。0NULL7839。01400。01400。0NULL7839。0NULL7839。0NULL7566。0NULLNULL0。00。0NULL7788。0NULL7698。0NULL7566。0NULL7782。0NULL7782。01234567891011121314151617181。2。2CASEWHENTHENELSEEND （1）数据准备（人名来自电视剧《将夜》）namedeptidsex夫子A男观主B男李慢慢A男余帘A女叶红鱼B女君陌A男叶青B男宁缺A男木柚A女12345678910 （2）需求：求出不同部门男女各多少人。 （3）创建hive表并导入数据hive（default）createtableempsex（namestring，deptidstring，sexstring）OKTimetaken：0。64seconds1234567 （4）按需求查询数据hive（default）selectdeptid，sum（casesexwhen男then1else0end）malecount，sum（casesexwhen女then1else0end）OKdeptidmalecountfemalecountA42B21Timetaken：9。155seconds，Fetched：2row（s）123456789101。2。3行转列 1。相关函数说明 CONCAT（stringAcol，stringBcol）：返回输入字符串连接后的结果，支持任意个输入字符串； CONCATWS（separator，str1，str2，）：它是一个特殊形式的CONCAT（）。第一个参数表示剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是NULL，返回值也将为NULL。这个函数会跳过分隔符参数后的任何NULL和空字符串。分隔符将被加到被连接的字符串之间； 注意：CONCATWSmustbestringorarray COLLECTSET（col）：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生Array类型字段。 2。数据准备（人名来自于电视剧《将夜》）nameconstellationbloodtype柳白白羊座A讲经首座射手座A陈某白羊座B陈皮皮白羊座A莫山山射手座A熊初墨白羊座B柯浩然射手座A12345678 3。需求 把星座和血型一样的人归类到一起。 4。创建hive表并导入数据hive（default）createtablepersoninfo（namestring，constellationstring，bloodtypestring）OKTimetaken：0。848secondshive（default）loaddatalocalinpathoptmodulehive3。1。2datapersoninfo。Loadingdatatotabledefault。personinfoOKTimetaken：0。682seconds123456789101112 5。按需求查询数据hive（default）selectt1。cb，concatws（，collectset（t1。name））from（selectname，concatws（，，constellation，bloodtype）cbfrompersoninfo）t1groupbyt1。OKt1。cbc1射手座，A讲经首座莫山山柯浩然白羊座，A柳白陈皮皮白羊座，B陈某熊初墨Timetaken：24。49seconds，Fetched：3row（s）123456789101。2。4列转行 1。函数说明 EXPLODE（将hive一列中复杂的Array或者Map结构拆分成多行。 LATERALVIEW 用法：LATERALVIEWudtf（expression）tableAliasAScolumnAlias 解释：用于和split，explode等UDTF一起使用它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。 2。数据准备moviescategory《流浪地球》悬疑，动作，科幻，剧情《警察故事》悬疑，警匪，动作，心理，剧情《战狼2》战争，动作，灾难1234 3。需求：将电影分类中的数组数据展开。 4。创建hive表并导入数据hive（default）createtablemovieinfo（moviestring，categorystring）OKTimetaken：0。143secondshive（default）loaddatalocalinpathoptmodulehive3。1。2datamovieinfo。Loadingdatatotabledefault。movieinfoOKTimetaken：0。518seconds1234567891011 5。按需求查询数据hive（default）selectmovie，categorynamefrommovieinfolateralviewexplode（split（category，，））OKmoviecategoryname《流浪地球》悬疑《流浪地球》动作《流浪地球》科幻《流浪地球》剧情《警察故事》悬疑《警察故事》警匪《警察故事》动作《警察故事》心理《警察故事》剧情《战狼2》战争《战狼2》动作《战狼2》灾难Timetaken：0。132seconds，Fetched：12row（s）123456789101112131415161718191。2。5窗口函数（开窗函数） 1。相关函数说明 OVER（）：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化。 CURRENTROW：当前行 nPRECEDING：往前n行数据 nFOLLOWING：往后n行数据 UNBOUNDED：起点，UNBOUNDEDPRECEDING表示从前面的起点，UNBOUNDEDFOLLOWING表示到后面的终点。 LAG（col，n，defaultval）：往前第n行数据 LEAD（col，n，defaultval））：往后第n行数据 NTILE（n）：把有序窗口的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。注意：n必须为int类型。 2。数据准备：name，orderdate，costjack，20170101，10tony，20170102，15jack，20170203，23tony，20170104，29jack，20170105，46jack，20170406，42tony，20170107，50jack，20170108，55mart，20170408，62mart，20170409，68neil，20170510，12mart，20170411，75neil，20170612，80mart，20170413，941234567891011121314 3。创建hive表并导入数据hive（default）createtablebusiness（namestring，orderdatestring，costint）rowformatdelimitedfieldsterminatedby，；OKTimetaken：0。864secondshive（default）loaddatalocalinpathoptmodulehive3。1。2databusiness。Loadingdatatotabledefault。businessOKTimetaken：0。777seconds123456789101112 4。按需求查询数据 （1）查询在2017年4月份购买过的顾客及总人数hive（default）selectname，count（）over（）frombusinesswheresubstring（orderdate，1，7）201704OKnamecountwindow0jack2mart2123456789 （2）查询顾客的购买明细及月购买总额hive（default）selectname，orderdate，cost，sum（cost）over（partitionbymonth（orderdate））OKnameorderdatecostsumwindow0jack2017010110205tony2017010215205tony2017010429205jack2017010546205tony2017010750205jack2017010855205jack201702032323mart2017041394341mart2017040862341mart2017040968341mart2017041175341jack2017040642341neil201705101212neil201706128080Timetaken：31。353seconds，Fetched：14row（s）12345678910111213141516171819 （3）将每个顾客的cost按照日期进行累加hive（default）selectname，orderdate，cost，sum（cost）over（）assample，sum（cost）over（partitionbyname）assample2，sum（cost）over（partitionbynameorderbyorderdate）assample3，sum（cost）over（partitionbynameorderbyorderdaterowsbetweenunboundedprecedingandcurrentrow）assample4，sum（cost）over（partitionbynameorderbyorderdaterowsbetween1precedingandcurrentrow）assample5，sum（cost）over（partitionbynameorderbyorderdaterowsbetween1precedingand1following）assample6，sum（cost）over（partitionbynameorderbyorderdaterowsbetweencurrentrowandunboundedfollowing）assample7OKnameorderdatecostsamplesample2sample3sample4sample5sample6sample7jack201701011066117610101056176jack2017010546661176565656111166jack2017010855661176111111101124120jack20170203236611761341347812065jack2017040642661176176176656542mart2017040862661299626262130299mart2017040968661299130130130205237mart2017041175661299205205143237169mart201704139466129929929916916994neil2017051012661921212129292neil2017061280661929292929280tony2017010215661941515154494tony2017010429661944444449479tony2017010750661949494797950Timetaken：26。212seconds，Fetched：14row（s）1234567891011121314151617181920212223242526 sample表示所有行相加，sample2表示按name分组，组内数据相加，sample3表示按name分组，组内数据累加，sample4和sample3一样，由起点到当前行的聚合，sample5表示当前行和前面一行聚合，sample表示当前行和前边一行及后面一行聚合，sample7表示当前行及后面所有行聚合。 rows必须跟在orderby子句之后，对排序的结果进行限制，使用固定的行数来限制分区中的数据行数量。 （4）查看顾客上次的购买时间hive（default）selectname，orderdate，cost，lag（orderdate，1，19000101）over（partitionbynameorderbyorderdate）astime1，lag（orderdate，2）over（partitionbynameorderbyorderdate）astime2OKnameorderdatecosttime1time2jack201701011019000101NULLjack201701054620170101NULLjack20170108552017010520170101jack20170203232017010820170105jack20170406422017020320170108mart201704086219000101NULLmart201704096820170408NULLmart20170411752017040920170408mart20170413942017041120170409neil201705101219000101NULLneil201706128020170510NULLtony201701021519000101NULLtony201701042920170102NULLtony201701075020170104201701021234567891011121314151617181920 time1表示顾客上次购买的时间，没有的话用’19000101’代替，time2表示顾客前两次购买的时间。 （5）查询前20时间的订单信息hive（default）selectfrom（selectname，orderdate，cost，ntile（5）over（orderbyorderdate）sortedfrombusiness）twheresorted1；OKt。namet。orderdatet。costt。sortedjack20170101101tony20170102151tony2017010429112345678910 将数据按时间分为5个组，取第一个组的数据。1。2。6Rank 1。函数说明 RANK（）：排序相同时会重复，总数不会变 DENSERANK（）：排序相同时会重复，总数会减少 ROWNUMBER（）：会根据顺序计算 2。数据准备（人名来自电视剧《将夜》）柯浩然语文87柯浩然数学95柯浩然英语68余帘语文94余帘数学58余帘英语84颜瑟语文64颜瑟数学86颜瑟英语84王景略语文65王景略数学85王景略英语78123456789101112 3。创建hive表并导入数据hive（default）createtablescore（namestring，subjectstring，scoreint）OKTimetaken：0。672secondshive（default）loaddatalocalinpathoptmodulehive3。1。2datascore。Loadingdatatotabledefault。scoreOKTimetaken：0。48seconds123456789101112 4。计算每门学科成绩排名hive（default）selectname，subject，score，rank（）over（partitionbysubjectorderbyscoredesc）rp，denserank（）over（partitionbysubjectorderbyscoredesc）drp，rownumber（）over（partitionbysubjectorderbyscoredesc）OKnamesubjectscorerpdrprmp柯浩然数学95111颜瑟数学86222王景略数学85333余帘数学58444余帘英语84111颜瑟英语84112王景略英语78323柯浩然英语68434余帘语文94111柯浩然语文87222王景略语文65333颜瑟语文64444Timetaken：18。441seconds，Fetched：12row（s）12345678910111213141516171819201。3自定义函数 （1）Hive自带了一些函数，比如maxmin等，但是数量有限，自己可以通过自定义UDF来方便的扩展。 （2）当Hive提供的内置函数无法满足你的业务处理需要时此时就可以考虑使用用户自定义函数（UDF：userdefinedfunction）。 （3）根据用户自定义函数类别分为以下三种： UDF（UserDefinedFunction）：一进一出 UDAF（UserDefinedAggregationFunction）：聚集函数，多进一出，类似于countmaxmin UDTF（UserDefinedTableGeneratingFunctions）：一进多出，如lateralviewexplode（） （4）官方文档地址 https：cwiki。apache。orgconfluencedisplayHiveHivePlugins （5）编程步骤： 1）继承Hive提供的类 org。apache。hadoop。hive。ql。udf。generic。GenericUDF org。apache。hadoop。hive。ql。udf。generic。GenericUDTF； 2）实现类中的抽象方法 3）在hive的命令行窗口创建函数 添加jar，addjarlinuxjarpath 创建function，create〔temporary〕function〔dbname。〕functionnameAS 4）在hive的命令行窗口删除函数 drop〔temporary〕function〔ifexists〕〔dbname。〕1。4自定义UDF函数 1。需求：自定义一个UDF实现计算给定字符串的长度 如：hive（default）selectmylen（abcd）412 2。创建一个Maven工程Hive 3。导入依赖dependenciesdependencygroupIdorg。apache。hivegroupIdhiveexecartifactIdversion3。1。2versiondependencydependencies1234567 4。创建一个类packagecom。Tom。importorg。apache。hadoop。hive。ql。exec。UDFArgumentEimportorg。apache。hadoop。hive。ql。exec。UDFArgumentLengthEimportorg。apache。hadoop。hive。ql。exec。UDFArgumentTypeEimportorg。apache。hadoop。hive。ql。metadata。HiveEimportorg。apache。hadoop。hive。ql。udf。generic。GenericUDF；importorg。apache。hadoop。hive。serde2。objectinspector。ObjectIimportorg。apache。hadoop。hive。serde2。objectinspector。primitive。PrimitiveObjectInspectorFpublicclassMyUDFextendsGenericUDF｛OverridepublicObjectInspectorinitialize（ObjectInspector〔〕arguments）throwsUDFArgumentException｛判断输入参数的个数if（arguments。length！1）｛thrownewUDFArgumentLengthException（InputArgsLengthError！！！）；｝判断输入参数的类型if（！arguments〔0〕。getCategory（）。equals（ObjectInspector。Category。PRIMITIVE））｛thrownewUDFArgumentTypeException（0，InputArgsTypeError！！！）；｝函数本身返回值为int，需要返回int类型的鉴别器对象returnPrimitiveObjectInspectorFactory。javaIntObjectI｝OverridepublicObjectevaluate（DeferredObject〔〕arguments）throwsHiveException｛if（arguments〔0〕。get（）null）｛return0；｝returnarguments〔0〕。get（）。toString（）。length（）；｝OverridepublicStringgetDisplayString（String〔〕children）｛｝｝1234567891011121314151617181920212223242526272829303132333435363738394041 5。打成jar包上传到服务器optmodulehive3。1。2myudf。jar 6。将jar包添加到hive的classpathhive（default）addjaroptmodulehive3。1。2hiveDemo1。0SNAPSHOT。1 7。创建临时函数与开发好的javaclass关联hive（default）createtemporaryfunctionmylenascom。Tom。udf。MyUDF；1 8。即可在hql中使用自定义的函数hive（default）selectename，mylen（ename）OKenameenamelenSMITH5ALLEN5WARD4JONES5MARTIN6BLAKE5CLARK5SCOTT5KING4TURNER6ADAMS5JAMES5FORD4MILLER6MILLER6Timetaken：0。807seconds，Fetched：15row（s）123456789101112131415161718191。5自定义UDTF函数 1。需求：自定义一个UDTF实现将一个任意分割符的字符串切割成独立的单词 例如：hive（default）selectmyudtf（hello，world，hadoop，hive，，）；helloworldhadoophive12345 2。代码实现packagecom。Tom。importorg。apache。hadoop。hive。ql。exec。UDFArgumentEimportorg。apache。hadoop。hive。ql。metadata。HiveEimportorg。apache。hadoop。hive。ql。udf。generic。GenericUDTF；importorg。apache。hadoop。hive。serde2。objectinspector。ObjectIimportorg。apache。hadoop。hive。serde2。objectinspector。ObjectInspectorFimportorg。apache。hadoop。hive。serde2。objectinspector。StructObjectIimportorg。apache。hadoop。hive。serde2。objectinspector。primitive。PrimitiveObjectInspectorFimportjava。util。ArrayLimportjava。util。LpublicclassMyUDTFextendsGenericUDTF｛privateArrayListStringoutListnewArrayListString（）；OverridepublicStructObjectInspectorinitialize（StructObjectInspectorargOIs）throwsUDFArgumentException｛1。定义输出数据的列名和类型ListStringfieldsNamesnewArrayListString（）；ListObjectInspectorfieldOIsnewArrayListObjectInspector（）；2。添加输出数据的列名和类型fieldsNames。add（lineToWord）；fieldOIs。add（PrimitiveObjectInspectorFactory。javaStringObjectInspector）；returnObjectInspectorFactory。getStandardStructObjectInspector（fieldsNames，fieldOIs）；｝Overridepublicvoidprocess（Object〔〕args）throwsHiveException｛1。获取原始数据Stringargargs〔0〕。toString（）；2。获取数据传入的第二个参数，此处为分隔符StringsplitKeyargs〔1〕。toString（）；3。将原始数据按照传入的分隔符进行切分String〔〕fieldsarg。split（splitKey）；4。遍历切分后的结果，并写出for（Stringfield：fields）｛集合为复用的，首先清空集合outList。clear（）；将每一个单词添加至集合outList。add（field）；将集合内容写出forward（outList）；｝｝Overridepublicvoidclose（）throwsHiveException｛｝｝1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859 3。打成jar包上传到服务器optmodulehive3。1。2myudf。jar 4。将jar包添加到hive的classpath下hive（default）addjaroptmodulehive3。1。2myudtf。1 5。创建临时函数与开发好的javaclass关联hive（default）createtemporaryfunctionmyudtfascom。Tom。udtf。MyUDTF；1 6。使用自定义的函数hive（default）selectmyudtf（hello，world，hadoop，hive，，）；OKlinetowordhelloworldhadoophiveTimetaken：0。704seconds，Fetched：4row（s）123456782压缩和存储2。1Hadoop压缩配置 1。MR支持的压缩编码 为了支持多种压缩解压缩算法，Hadoop引入了编码解码器，如下表所示： 压缩格式 对应的编码解码器 DEFLATE org。apache。hadoop。io。compress。DefaultCodec gzip org。apache。hadoop。io。compress。GzipCodec bzip2 org。apache。hadoop。io。compress。BZip2Codec LZO com。hadoop。compression。lzo。LzopCodec Snappy org。apache。hadoop。io。compress。SnappyCodec 压缩性能的比较： 2。压缩参数配置 要在Hadoop中启用压缩可以配置如下参数（mapredsite。xml文件中）： 2。2开启Map输出阶段压缩（MR引擎） 开启map输出阶段压缩可以减少job中map和Reducetask间数据传输量。具体配置如下： 案例实操 （1）开启hive中间传输数据压缩功能hive（default）sethive。exec。compress。1 （2）开启mapreduce中map输出压缩功能hive（default）setmapreduce。map。output。1 （3）设置mapreduce中map输出数据的压缩方式hive（default）setmapreduce。map。output。compress。codecorg。apache。hadoop。io。compress。SnappyC1 （4）执行查询语句hive（default）selectcount（ename）OKname15Timetaken：33。563seconds，Fetched：1row（s）123452。3开启Reduce输出阶段压缩 当Hive将输出写入到表中时，输出内容同样可以进行压缩。属性hive。exec。compress。output控制着这个功能。用户可能需要保持默认设置文件中的默认值false，这样默认的输出就是非压缩的纯文本文件了。用户可以通过在查询语句或执行脚本中设置这个值为true，来开启输出结果压缩功能。 案例实操 （1）开启hive最终输出数据压缩功能hive（default）sethive。exec。compress。1 （2）开启mapreduce最终输出数据压缩hive（default）setmapreduce。output。fileoutputformat。1 （3）设置mapreduce最终数据输出压缩方式hive（default）setmapreduce。output。fileoutputformat。compress。codecorg。apache。hadoop。io。compress。SnappyC1 （4）设置mapreduce最终数据输出压缩为块压缩hive（default）setmapreduce。output。fileoutputformat。compress。typeBLOCK；1 （5）测试一下输出结果是否是压缩文件hive（default）insertoverwritelocaldirectoryoptmodulehive3。1。212〔Tomhadoop102distributeresult〕ll总用量4rwrr。1TomTom4709月510：430000000。snappy1232。4文件存储格式 1。列式存储和行式存储 如图所示左边为逻辑表，右边第一个为行式存储，第二个为列式存储。 （1）行存储的特点 查询满足条件的一整行数据的时候，列存储则需要去每个聚集的字段找到对应的每个列的值，行存储只需要找到其中一个值，其余的值都在相邻地方，所以此时行存储查询的速度更快。 （2）列存储的特点 因为每个字段的数据聚集存储，在查询只需要少数几个字段的时候，能大大减少读取的数据量；每个字段的数据类型一定是相同的，列式存储可以针对性的设计更好的设计压缩算法。 TEXTFILE和SEQUENCEFILE的存储格式都是基于行存储的； ORC和PARQUET是基于列式存储的。 2。TextFile格式 默认格式，数据不做压缩，磁盘开销大，数据解析开销大。可结合Gzip、Bzip2使用，但使用Gzip这种方式，hive不会对数据进行切分，从而无法对数据进行并行操作。 3。Orc格式 Orc（OptimizedRowColumnar）是Hive0。11版里引入的新的存储格式。 如下图所示，可以看到每个Orc文件由1个或多个stripe组成，每个stripe一般为HDFS的块大小，每一个stripe包含多条记录，这些记录按照列进行独立存储，对应到Parquet中的rowgroup的概念。每个Stripe里有三部分组成，分别是IndexData，RowData，StripeFooter： （1）IndexData：一个轻量级的index，默认是每隔1W行做一个索引。这里做的索引应该只是记录某行的各字段在RowData中的offset。 （2）RowData：存的是具体的数据，先取部分行，然后对这些行按列进行存储。对每个列进行了编码，分成多个Stream来存储。 （3）StripeFooter：存的是各个Stream的类型，长度等信息。 每个文件有一个FileFooter，这里面存的是每个Stripe的行数，每个Column的数据类型信息等；每个文件的尾部是一个PostScript，这里面记录了整个文件的压缩类型以及FileFooter的长度信息等。在读取文件时，会seek到文件尾部读PostScript，从里面解析到FileFooter长度，再读FileFooter，从里面解析到各个Stripe信息，再读各个Stripe，即从后往前读。 4。Parquet格式 Parquet文件是以二进制方式存储的，所以是不可以直接读取的，文件中包括该文件的数据和元数据，因此Parquet格式文件是自解析的。 （1）行组（RowGroup）：每一个行组包含一定的行数，在一个HDFS文件中至少存储一个行组，类似于orc的stripe的概念。 （2）列块（ColumnChunk）：在一个行组中每一列保存在一个列块中，行组中的所有列连续的存储在这个行组文件中。一个列块中的值都是相同类型的，不同的列块可能使用不同的算法进行压缩。 （3）页（Page）：每一个列块划分为多个页，一个页是最小的编码的单位，在同一个列块的不同页可能使用不同的编码方式。 通常情况下，在存储Parquet数据的时候会按照Block大小设置行组的大小，由于一般情况下每一个Mapper任务处理数据的最小单位是一个Block，这样可以把每一个行组由一个Mapper任务处理，增大任务执行并行度。Parquet文件的格式： 上图展示了一个Parquet文件的内容，一个文件中可以存储多个行组，文件的首位都是该文件的MagicCode，用于校验它是否是一个Parquet文件，Footerlength记录了文件元数据的大小，通过该值和文件长度可以计算出元数据的偏移量，文件的元数据中包括每一个行组的元数据信息和该文件存储数据的Schema信息。除了文件中每一个行组的元数据，每一页的开始都会存储该页的元数据，在Parquet中，有三种类型的页：数据页、字典页和索引页。数据页用于存储当前行组中该列的值，字典页存储该列值的编码字典，每一个列块中最多包含一个字典页，索引页用来存储当前行组下该列的索引，目前Parquet中还不支持索引页。 5。主流文件存储格式对比实验 从存储文件的压缩比和查询速度两个角度对比。 存储文件的压缩比测试： （1）TextFile，首先创建表，指定存储格式，然后加载数据，最后查看表中数据大小hive（default）createtablelogtext（tracktimestring，urlstring，sessionidstring，refererstring，ipstring，enduseridstring，cityidstring）OKTimetaken：0。241secondshive（default）loaddatalocalinpathoptmodulehive3。1。2datalog。Loadingdatatotabledefault。logtextOKTimetaken：2。62secondshive（default）18。1M54。4Muserhivewarehouselogtextlog。data123456789101112131415161718192021 （2）Orc（设置orc存储不使用压缩）hive（default）createtablelogorc（tracktimestring，urlstring，sessionidstring，refererstring，ipstring，enduseridstring，cityidstring）rowformatdelimitedfieldsterminatedbystoredasorctblproperties（orc。compressNONE）；OKTimetaken：0。149secondshive（default）hive（default）7。7M23。1Muserhivewarehouselogorc000000012345678910111213141516171819 （3）Parquethive（default）createtablelogparquet（tracktimestring，urlstring，sessionidstring，refererstring，ipstring，enduseridstring，cityidstring）OKTimetaken：0。164secondshive（default）hive（default）13。1M39。3Muserhivewarehouselogparquet0000000123456789101112131415161718 存储文件的对比总结：ORCParquettextFile 存储文件的查询速度测试： （1）TextFilehive（default）insertoverwritelocaldirectoryoptmodulehive3。1。2datalogtextselectsubstring（url，1，4）OKc0Timetaken：18。312seconds1234 （2）Orchive（default）insertoverwritelocaldirectoryoptmodulehive3。1。2datalogorcselectsubstring（url，1，4）OKc0Timetaken：10。412seconds1234 （3）Parquethive（default）insertoverwritelocaldirectoryoptmodulehive3。1。2datalogparquetselectsubstring（url，1，4）OKc0Timetaken：9。799seconds1234 存储文件的查询速度总结：查询速度相近。2。5存储和压缩结合 官网：https：cwiki。apache。orgconfluencedisplayHiveLanguageManualORC ORC存储方式的压缩： 注意：所有关于ORCFile的参数都是在HQL语句的TBLPROPERTIES字段里面出现 1。创建一个ZLIB压缩的ORC存储方式 （1）建表语句hive（default）createtablelogorczlib（tracktimestring，urlstring，sessionidstring，refererstring，ipstring，enduseridstring，cityidstring）rowformatdelimitedfieldsterminatedbystoredasorctblproperties（orc。compressZLIB）；OKTimetaken：0。118seconds1234567891011121314 （2）插入数据hive（default）1 （3）查看插入后数据hive（default）2。8M8。3Muserhivewarehouselogorczlib000000012 2。创建一个SNAPPY压缩的ORC存储方式 （1）建表语句hive（default）createtablelogorcsnappy（tracktimestring，urlstring，sessionidstring，refererstring，ipstring，enduseridstring，cityidstring）rowformatdelimitedfieldsterminatedbystoredasorctblproperties（orc。compressSNAPPY）；OKTimetaken：0。138seconds1234567891011121314 （2）插入数据hive（default）1 （3）查看插入后数据hive（default）3。7M11。2Muserhivewarehouselogorcsnappy000000012 ZLIB比Snappy压缩的还小。原因是ZLIB采用的是deflate压缩算法。比snappy压缩的压缩率高。 3。创建一个SNAPPY压缩的parquet存储方式 （1）建表语句hive（default）createtablelogparquetsnappy（tracktimestring，urlstring，sessionidstring，refererstring，ipstring，enduseridstring，cityidstring）rowformatdelimitedfieldsterminatedbystoredasparquettblproperties（parquet。compressionsnappy）；OKTimetaken：0。972seconds1234567891011121314 （2）插入数据hive（default）1 （3）查看插入后数据hive（default）6。4M19。2Muserhivewarehouselogparquetsnappy000000012 4。存储方式和压缩总结 在实际的项目开发当中，hive表的数据存储格式一般选择orc或parquet。压缩方式一般选择snappy或lzo。（五）企业级调优、Hive实战1企业级调优1。1执行计划（Explain） 1。基本语法 EXPLAIN〔EXTENDEDDEPENDENCYAUTHORIZATION〕query 2。案例实操 （1）查看下面这条语句的执行计划 没有生成MR任务的hive（default）OKExplainPlanoptimizedbyCBO。Stage0FetchOperatorlimit：1SelectOperator〔SEL1〕Output：〔col0，col1，col2，col3，col4，col5，col6，col7〕TableScan〔TS0〕Output：〔empno，ename，job，mgr，hiredate，sal，comm，deptno〕Timetaken：3。189seconds，Fetched：10row（s）1234567891011121314 有生成MR任务的hive（default）explainselectdeptno，avg（sal）OKExplainPlanoptimizedbyCBO。VertexdependencyinrootstageReducer2Map1（SIMPLEEDGE）Stage0FetchOperatorlimit：1Stage1Reducer2vectorizedFileOutputOperator〔FS12〕SelectOperator〔SEL11〕（rows1width7030）Output：〔col0，col1〕GroupByOperator〔GBY10〕（rows1width7030）Output：〔col0，col1，col2〕，aggregations：〔sum（VALUE。col0），count（VALUE。col1）〕，keys：KEY。col0Map1〔SIMPLEEDGE〕vectorizedSHUFFLE〔RS9〕PartitionCols：col0GroupByOperator〔GBY8〕（rows1width7030）Output：〔col0，col1，col2〕，aggregations：〔sum（sal），count（sal）〕，keys：deptnoSelectOperator〔SEL7〕（rows1width7030）Output：〔sal，deptno〕TableScan〔TS0〕（rows1width7030）defaultemp，emp，Tbl：COMPLETE，Col：NONE，Output：〔sal，deptno〕Timetaken：1。893seconds，Fetched：25row（s）1234567891011121314151617181920212223242526272829 （2）查看详细执行计划hive（default）hive（default）explainextendedselectdeptno，avg（sal）121。2Fetch抓取 Fetch抓取是指，Hive中对某些情况的查询可以不必使用MapReduce计算。例如SELECTFROM在这种情况下Hive可以简单地读取employee对应的存储目录下的文件然后输出查询结果到控制台。在hivedefault。xml。template文件中hive。fetch。task。conversion默认是more，老版本hive默认是minimal，该属性修改为more以后，在全局查找、字段查找、limit查找等都不走mapreduce。propertynamehive。fetch。task。conversionnamevaluemorevaluedescriptionExpectsoneof〔none，minimal，more〕。SomeselectqueriescanbeconvertedtosingleFETCHtaskminimizinglatency。Currentlythequeryshouldbesinglesourcednothavinganysubqueryandshouldnothaveanyaggregationsordistincts（whichincursRS），lateralviewsandjoins。0。none：disablehive。fetch。task。conversion1。minimal：SELECTSTAR，FILTERonpartitioncolumns，LIMITonly2。more：SELECT，FILTER，LIMITonly（supportTABLESAMPLEandvirtualcolumns）descriptionproperty123456789101112 案例实操： （1）把hive。fetch。task。conversion设置成none，然后执行查询语句，都会执行mapreduce程序。hive（default）sethive。fetch。task。hive（default）hive（default）hive（default）selectenamefromemplimit3；1234 （2）把hive。fetch。task。conversion设置成more，然后执行查询语句，如下查询方式都不会执行mapreduce程序。hive（default）sethive。fetch。task。hive（default）hive（default）hive（default）selectenamefromemplimit3；12341。3本地模式 大多数的HadoopJob是需要Hadoop提供的完整的可扩展性来处理大数据集的。不过，有时Hive的输入数据量是非常小的。在这种情况下，为查询触发执行任务消耗的时间可能会比实际job的执行时间要多的多。对于大多数这种情况，Hive可以通过本地模式在单台机器上处理所有的任务。对于小数据集，执行时间可以明显被缩短。 用户可以通过设置hive。exec。mode。local。auto的值为true，来让Hive在适当的时候自动启动这个优化。sethive。exec。mode。local。开启本地mr设置localmr的最大输入数据量，当输入数据量小于这个值时采用localmr的方式，默认为134217728，即128Msethive。exec。mode。local。auto。inputbytes。max50000000；设置localmr的最大输入文件个数，当输入文件个数小于这个值时采用localmr的方式，默认为4sethive。exec。mode。local。auto。input。files。max10；123456 案例实操 （1）关闭本地模式（默认是关闭的），并执行查询语句hive（default）selectcount（）OKc03561Timetaken：8。67seconds，Fetched：4row（s）12345678 （2）开启本地模式，并执行查询语句hive（default）sethive。exec。mode。local。hive（default）selectcount（）OKc03561Timetaken：9。457seconds，Fetched：4row（s）1234567891。4表的优化1。4。1小表大表Join（MapJOIN） 将key相对分散，并且数据量小的表放在join的左边，可以使用mapjoin让小的维度表先进内存。在map端完成join。 实际测试发现：新版的hive已经对小表JOIN大表和大表JOIN小表进行了优化。小表放在左边和右边已经没有区别。 1。需求介绍 测试大表JOIN小表和小表JOIN大表的效率。 2。开启MapJoin参数设置 （1）设置自动选择Mapjoin，默认为truehive（default）sethive。auto。convert。1 （2）大表小表的阈值设置（默认25M以下认为是小表）：hive（default）sethive。mapjoin。smalltable。filesize25000000；1 3。MapJoin工作机制 4。建大表、小表和JOIN后表的语句创建大表hive（default）createtablebigtable（idbigint，tbigint，uidstring，keywordstring，urlrankint，clicknumint，clickurlstring）OKTimetaken：0。193seconds创建小表hive（default）createtablesmalltable（idbigint，tbigint，uidstring，keywordstring，urlrankint，clicknumint，clickurlstring）OKTimetaken：0。162seconds创建join后的表hive（default）createtablejointable（idbigint，tbigint，uidstring，keywordstring，urlrankint，clicknumint，clickurlstring）OKTimetaken：0。127seconds1234567891011121314151617181920 5。分别向大表和小表中导入数据hive（default）loaddatalocalinpathoptmodulehive3。1。2Loadingdatatotabledefault。bigtableOKTimetaken：27。455secondshive（default）loaddatalocalinpathoptmodulehive3。1。2Loadingdatatotabledefault。smalltableOKTimetaken：1。51seconds123456789 6。小表JOIN大表语句hive（default）insertoverwritetablejointableselectb。id，b。t，b。uid，b。keyword，b。urlrank，b。clicknum，b。clickurlfromsmalltablesjoinbigtablebonb。ids。Loadingdatatotabledefault。jointableOKb。idb。tb。uidb。keywordb。urlrankb。clicknumb。clickurlTimetaken：97。527seconds123456789 7。大表JOIN小表语句hive（default）insertoverwritetablejointableselectb。id，b。t，b。uid，b。keyword，b。urlrank，b。clicknum，b。clickurlfrombigtablebjoinsmalltablesons。idb。Loadingdatatotabledefault。jointableOKb。idb。tb。uidb。keywordb。urlrankb。clicknumb。clickurlTimetaken：94。968seconds1234567891。4。2大表Join大表 1。空KEY过滤 有时join超时是因为某些key对应的数据太多，而相同key对应的数据都会发送到相同的reducer上，从而导致内存不够。此时我们应该仔细分析这些异常的key，很多情况下这些key对应的数据是异常数据，我们需要在SQL语句中进行过滤。例如key对应的字段为空，操作如下： （1）配置历史服务器 配置mapredsite。xml！历史服务器端地址propertynamemapreduce。jobhistory。addressnamevaluehadoop102：10020valueproperty！历史服务器web端地址propertynamemapreduce。jobhistory。webapp。addressnamevaluehadoop102：19888valueproperty12345678910 启动历史服务器sbinmrjobhistorydaemon。shstarthistoryserver1 查看jobhistory http：hadoop102：19888jobhistory （2）创建原始数据空id表hive（default）createtablenullidtable（idbigint，tbigint，uidstring，keywordstring，urlrankint，clicknumint，clickurlstring）OKTimetaken：0。151seconds12345 （3）分别加载原始数据和空id数据到对应表中hive（default）loaddatalocalinpathoptmodulehive3。1。2Loadingdatatotabledefault。nullidtableOKTimetaken：11。346seconds1234 （4）测试不过滤空idhive（default）insertoverwritetablejointableselectn。fromnullidtablenleftjoinbigtableoonn。ido。OKn。idn。tn。uidn。keywordn。urlrankn。clicknumn。clickurlTimetaken：182。221seconds12345 （5）测试过滤空idhive（default）insertoverwritetablejointableselectn。from（selectfromnullidtablewhereidisnotnull）nleftjoinbigtableoonn。ido。OKn。idn。tn。uidn。keywordn。urlrankn。clicknumn。clickurlTimetaken：152。913seconds12345 2。空key转换 有时虽然某个key为空对应的数据很多，但是相应的数据不是异常数据，必须要包含在join的结果中，此时我们可以表a中key为空的字段赋一个随机的值，使得数据随机均匀地分不到不同的reducer上。例如： 不随机分布空null值： （1）设置5个reduce个数hive（default）setmapreduce。job。reduces5；1 （2）JOIN两张表hive（default）insertoverwritetablejointableselectn。fromnullidtablenleftjoinbigtablebonn。idb。12 结果：如下图所示，可以看出来，出现了数据倾斜，某些reducer的资源消耗远大于其他reducer。 随机分布空null值 （1）设置5个reduce个数hive（default）setmapreduce。job。reduces5；1 （2）JOIN两张表insertoverwritetablejointableselectn。fromnullidtablenfulljoinbigtableoonnvl（n。id，rand（））o。123 （4）结果：如下图所示 3。SMB（SortMergeBucketjoin） （1）创建第二张大表hive（default）createtablebigtable2（idbigint，tbigint，uidstring，keywordstring，urlrankint，clicknumint，clickurlstring）OKTimetaken：0。368secondshive（default）loaddatalocalinpathoptmodulehive3。1。2databigtableintotablebigtable2；12345678910111213 测试大表直接JOINhive（default）insertoverwritetablejointableselectb。id，b。t，b。uid，b。keyword，b。urlrank，b。clicknum，b。clickurlfrombigtablesjoinbigtable2bonb。ids。OKb。idb。tb。uidb。keywordb。urlrankb。clicknumb。clickurlTimetaken：396。839seconds12345678 （2）创建分桶表1，桶的个数不要超过可用CPU的核数hive（default）createtablebigtablebuck1（idbigint，tbigint，uidstring，keywordstring，urlrankint，clicknumint，clickurlstring）clusteredby（id）sortedby（id）into6OKTimetaken：0。537secondshive（default）loaddatalocalinpathoptmodulehive3。1。2databigtableintotablebigtablebuck1；123456789101112131415161718 （3）创建分桶表2，桶的个数不要超过可用CPU的核数hive（default）createtablebigtablebuck2（idbigint，tbigint，uidstring，keywordstring，urlrankint，clicknumint，clickurlstring）clusteredby（id）sortedby（id）into6hive（default）loaddatalocalinpathoptmodulehive3。1。2databigtableintotablebigtablebuck2；12345678910111213141516 （4）设置参数hive（default）sethive。optimize。hive（default）sethive。optimize。bucketmapjoin。hive（default）sethive。input。formatorg。apache。hadoop。hive。ql。io。BucketizedHiveInputF123 （5）测试hive（default）insertoverwritetablejointableselectb。id，b。t，b。uid，b。keyword，b。urlrank，b。clicknum，b。clickurlfrombigtablebuck1sjoinbigtablebuck2bonb。ids。123451。4。3GroupBy 默认情况下，Map阶段同一Key数据分发给一个reduce，当一个key数据过大时就倾斜了。 并不是所有的聚合操作都需要在Reduce端完成，很多聚合操作都可以先在Map端进行部分聚合，最后在Reduce端得出最终结果。 开启Map端聚合参数设置 （1）是否在Map端进行聚合，默认为Truehive（default）sethive。map。1 （2）在Map端进行聚合操作的条目数目hive（default）sethive。groupby。mapaggr。checkinterval100000；1 （3）有数据倾斜的时候进行负载均衡（默认是false）hive（default）sethive。groupby。1 当选项设定为true，生成的查询计划会有两个MRJob。第一个MRJob中，Map的输出结果会随机分布到Reduce中，每个Reduce做部分聚合操作，并输出结果，这样处理的结果是相同的GroupByKey有可能被分发到不同的Reduce中，从而达到负载均衡的目的；第二个MRJob再根据预处理的数据结果按照GroupByKey分布到Reduce中（这个过程可以保证相同的GroupByKey被分布到同一个Reduce中），最后完成最终的聚合操作。StageStage1：Map：1Reduce：5CumulativeCPU：21。7secHDFSRead：37846HDFSWrite：495SUCCESSTotalMapReduceCPUTimeSpent：21seconds700msecOKdeptno10203050Timetaken：119。241seconds，Fetched：4row（s）123456789 优化以后hive（default）sethive。groupby。hive（default）StageStage1：Map：1Reduce：5CumulativeCPU：18。73secHDFSRead：35806HDFSWrite：552SUCCESSStageStage2：Map：1Reduce：5CumulativeCPU：14。54secHDFSRead：22374HDFSWrite：495SUCCESSTotalMapReduceCPUTimeSpent：33seconds270msecOKdeptno10203050Timetaken：104。013seconds，Fetched：4row（s）1234567891011121。4。4Count（Distinct）去重统计 数据量小的时候无所谓，数据量大的情况下，由于COUNTDISTINCT操作需要用一个ReduceTask来完成，这一个Reduce需要处理的数据量太大，就会导致整个Job很难完成，一般COUNTDISTINCT使用先GROUPBY再COUNT的方式替换，但是需要注意groupby造成的数据倾斜问题。 案例实操 （1）创建一张大表hive（default）createtablebigtable（idbigint，timesbigint，uidstring，keywordstring，urlrankint，clicknumint，clickurlstring）OKTimetaken：0。221seconds12345 （2）加载数据hive（default）loaddatalocalinpathoptmodulehive3。1。2Loadingdatatotabledefault。bigtableOKTimetaken：11。977seconds1234 （3）设置5个reduce个数hive（default）setmapreduce。job。reduces5；1 （4）执行去重id查询hive（default）selectcount（distinctid）StageStage1：Map：1Reduce：5CumulativeCPU：21。11secHDFSRead：129179350HDFSWrite：580SUCCESSStageStage2：Map：1Reduce：1CumulativeCPU：4。07secHDFSRead：8535HDFSWrite：105SUCCESSTotalMapReduceCPUTimeSpent：25seconds180msecOKc099947Timetaken：96。086seconds，Fetched：1row（s）12345678 （5）采用GROUPby去重idhive（default）selectcount（id）from（selectidfrombigtablegroupbyid）a；StageStage1：Map：1Reduce：5CumulativeCPU：29。09secHDFSRead：129191861HDFSWrite：2033820SUCCESSStageStage2：Map：1Reduce：5CumulativeCPU：19。62secHDFSRead：2053225HDFSWrite：580SUCCESSStageStage3：Map：1Reduce：1CumulativeCPU：3。58secHDFSRead：8543HDFSWrite：105SUCCESSTotalMapReduceCPUTimeSpent：52seconds290msecOKc099947Timetaken：154。433seconds，Fetched：1row（s）123456789 虽然会多用一个Job来完成，但在数据量大的情况下，这个绝对是值得的。1。4。5笛卡尔积 尽量避免笛卡尔积，join的时候不加on条件，或者无效的on条件，Hive只能使用1个reducer来完成笛卡尔积。1。4。6行列过滤 列处理：在SELECT中，只拿需要的列，如果有分区，尽量使用分区过滤，少用SELECT。 行处理：在分区剪裁中，当使用外关联时，如果将副表的过滤条件写在 where后面，那么就会先全表关联，之后再过滤，比如： （1）测试先关联两张表，再用where条件过滤hive（default）selecto。idfrombigtablebjoinbigtableoono。idb。idwhereo。id10；Timetaken：53。697seconds，Fetched：1081row（s）1234 （2）通过子查询后，再关联表hive（default）selectb。idfrombigtablebjoin（selectidfrombigtablewhereid10）oonb。ido。Timetaken：50。658seconds，Fetched：1081row（s）1231。5合理设置Map及Reduce数 （1）通常情况下，作业会通过input的目录产生一个或者多个map任务。 主要的决定因素有：input的文件总个数，input的文件大小，集群设置的文件块大小。 （2）是不是map数越多越好？ 答案是否定的。如果一个任务有很多小文件（远远小于块大小128m），则每个小文件也会被当做一个块，用一个map任务来完成，而一个map任务启动和初始化的时间远远大于逻辑处理的时间，就会造成很大的资源浪费。而且，同时可执行的map数是受限的。 （3）是不是保证每个map处理接近128M的文件块，就高枕无忧了？ 答案也是不一定。比如有一个127M的文件，正常会用一个map去完成，但这个文件只有一个或者两个小字段，却有几千万的记录，如果map处理的逻辑比较复杂，用一个map任务去做，肯定也比较耗时。 针对上面的问题2和3，我们需要采取两种方式来解决：即减少map数和增加map数；1。5。1复杂文件增加Map数 当input的文件都很大，任务逻辑复杂，map执行非常慢的时候，可以考虑增加Map数，来使得每个map处理的数据量减少，从而提高任务的执行效率。 增加map的方法为：根据computeSliteSize（Math。max（minSize，Math。min（maxSize，blocksize）））blocksize128M公式，调整maxSize最大值。让maxSize最大值低于blocksize就可以增加map的个数。 案例实操： （1）执行查询hive（default）selectcount（）HadoopjobinformationforStage1：numberofmappers：1；numberofreducers：112 （2）设置最大切片值为100个字节hive（default）setmapreduce。input。fileinputformat。split。maxsize100；HadoopjobinformationforStage1：numberofmappers：7；numberofreducers：1121。5。2小文件进行合并 （1）在map执行前合并小文件，减少map数：CombineHiveInputFormat具有对小文件进行合并的功能（系统默认的格式）。HiveInputFormat没有对小文件合并功能。hive（default）sethive。input。formatorg。apache。hadoop。hive。qi。io。CombineHiveInputF1 （2）在MapReduce的任务结束时合并小文件的设置： 在maponly任务结束时合并小文件，默认truehive（default）sethive。merge。1 在mapreduce任务结束时合并小文件，默认falsehive（default）sethive。merge。1 合并文件的大小，默认256Mhive（default）sethive。merge。size。per。task268435456；1 当输出文件的平均大小小于该值时，启动一个独立的mapreduce任务进行文件mergehive（default）sethive。merge。smallfiles。avgsize16777216；11。5。3合理设置Reduce数 1。调整reduce个数方法一 （1）每个Reduce处理的数据量默认是256MBhive（default）sethive。exec。reducers。bytes。per。reducer256000000；1 （2）每个任务最大的reduce数，默认为1009hive（default）sethive。exec。reducers。max1009；1 （3）计算reducer数的公式Nmin（参数2，总输入数据量参数1）1 2。调整reduce个数方法二 在hadoop的mapreddefault。xml文件中修改 设置每个job的Reduce个数hive（default）setmapreduce。job。reduces15；1 3。reduce个数并不是越多越好 （1）过多的启动和初始化reduce也会消耗时间和资源； （2）另外，有多少个reduce，就会有多少个输出文件，如果生成了很多个小文件，那么如果这些小文件作为下一个任务的输入，则也会出现小文件过多的问题；在设置reduce个数的时候也需要考虑这两个原则：处理大数据量利用合适的reduce数；使单个reduce任务处理数据量大小要合适。1。6并行执行 Hive会将一个查询转化成一个或者多个阶段。这样的阶段可以是MapReduce阶段、抽样阶段、合并阶段、limit阶段。或者Hive执行过程中可能需要的其他阶段。默认情况下，Hive一次只会执行一个阶段。不过，某个特定的job可能包含众多的阶段，而这些阶段可能并非完全互相依赖的，也就是说有些阶段是可以并行执行的，这样可能使得整个job的执行时间缩短。不过，如果有更多的阶段可以并行执行，那么job可能就越快完成。 通过设置参数hive。exec。parallel值为true，就可以开启并发执行。不过，在共享集群中需要注意下，如果job中并行阶段增多，那么集群利用率就会增加。hive（default）sethive。exec。打开任务并行执行hive（default）sethive。exec。parallel。thread。number16；同一个sql允许最大并行度，默认为812 当然，得是在系统资源比较空闲的时候才有优势，否则，没资源，并行也起不来。1。7严格模式 Hive可以通过设置防止一些危险操作： 1。分区表不使用分区过滤 将hive。strict。checks。no。partition。filter设置为true时，对于分区表，除非where语句中含有分区字段过滤条件来限制范围，否则不允许执行。换句话说，就是用户不允许扫描所有分区。进行这个限制的原因是，通常分区表都拥有非常大的数据集，而且数据增加迅速。没有进行分区限制的查询可能会消耗令人不可接受的巨大资源来处理这个表。 2。使用orderby没有limit过滤 将hive。strict。checks。orderby。no。limit设置为true时，对于使用了orderby语句的查询，要求必须使用limit语句。因为orderby为了执行排序过程会将所有的结果数据分发到同一个Reducer中进行处理，强制要求用户增加这个LIMIT语句可以防止Reducer额外执行很长一段时间。 3。笛卡尔积 将hive。strict。checks。cartesian。product设置为true时，会限制笛卡尔积的查询。对关系型数据库非常了解的用户可能期望在执行JOIN查询的时候不使用ON语句而是使用where语句，这样关系数据库的执行优化器就可以高效地将WHERE语句转化成那个ON语句。不幸的是，Hive并不会执行这种优化，因此，如果表足够大，那么这个查询就会出现不可控的情 况。2Hive实战2。1需求描述 统计硅谷影音视频网站的常规指标，各种TopN指标： 1统计视频观看数Top10 2统计视频类别热度Top10 3统计出视频观看数最高的20个视频的所属类别以及类别包含Top20视频的个数 4统计视频观看数Top50所关联视频的所属类别排序 5统计每个类别中的视频热度Top10，以Music为例 6统计每个类别视频观看数Top10 7统计上传视频最多的用户Top10以及他们上传的视频观看次数在前20的视频2。2数据结构 1。视频表 2。用户表 2。3准备工作2。3。1准备表 1。需要准备的表 创建原始数据表：gulivideoori，gulivideouserori， 创建最终表：gulivideoorc，gulivideouserorc 1。创建原始数据表 （1）gulivideoorihive（default）createtablegulivideoori（videoIdstring，uploaderstring，ageint，categoryarraystring，lengthint，viewsint，ratefloat，ratingsint，commentsint，relatedIdarraystring）OKTimetaken：0。131seconds12345678910111213141516 （2）创建原始数据表：gulivideouserorihive（default）createtablegulivideouserori（uploaderstring，videosint，friendsint）OKTimetaken：0。131seconds123456789 2。创建orc存储格式带snappy压缩的表： （1）gulivideoorchive（default）createtablegulivideoorc（videoIdstring，uploaderstring，ageint，categoryarraystring，lengthint，viewsint，ratefloat，ratingsint，commentsint，relatedIdarraystring）rowformatdelimitedfieldsterminatedbycollectionitemsterminatedbystoredasorctblproperties（orc。compresssnappy）；OKTimetaken：0。144seconds1234567891011121314151617 （2）gulivideouserorchive（default）createtablegulivideouserorc（uploaderstring，videosint，friendsint）rowformatdelimitedfieldsterminatedbystoredasorctblproperties（orc。compresssnappy）；OKTimetaken：0。127seconds123456789 （3）向ori表插入数据hive（default）loaddatalocalinpathoptmodulehive3。1。2Loadingdatatotabledefault。gulivideooriOKTimetaken：4。183secondshive（default）loaddatalocalinpathoptmodulehive3。1。2datauser。Loadingdatatotabledefault。gulivideouseroriOKTimetaken：6。083seconds123456789 （4）向orc表插入数据hive（default）hive（default）122。3。2安装Tez引擎 Tez是一个Hive的运行引擎，性能优于MR。 用Hive直接编写MR程序，假设有四个有依赖关系的MR作业，上图中，绿色是ReduceTask，云状表示写屏蔽，需要将中间结果持久化写到HDFS。 Tez可以将多个有依赖的作业转换为一个作业，这样只需写一次HDFS，且中间节点较少，从而大大提升作业的计算性能。 （1）将tez安装包拷贝到集群，并解压tar包〔Tomhadoop102software〕mkdiroptmoduletez〔Tomhadoop102software〕tarzxvfoptsoftwaretez0。10。1SNAPSHOTminimal。tar。gzCoptmoduletez12 （2）上传tez依赖到HDFS〔Tomhadoop102software〕hadoopfsmkdirtez〔Tomhadoop102software〕hadoopfsputoptsoftwaretez0。10。1SNAPSHOT。tar。gztez12 （3）新建tezsite。xml〔Tomhadoop102software〕vimHADOOPHOMEetchadooptezsite。xml1 添加如下内容：？xmlversion1。0encodingUTF8？？xmlstylesheettypetextxslhrefconfiguration。xsl？configurationpropertynametez。lib。urisnamevalue｛fs。defaultFS｝teztez0。10。1SNAPSHOT。tar。gzvaluepropertypropertynametez。use。cluster。hadooplibsnamevaluetruevaluepropertypropertynametez。am。resource。memory。mbnamevalue1024valuepropertypropertynametez。am。resource。cpu。vcoresnamevalue1valuepropertypropertynametez。container。max。java。heap。fractionnamevalue0。4valuepropertypropertynametez。task。resource。memory。mbnamevalue1024valuepropertypropertynametez。task。resource。cpu。vcoresnamevalue1valuepropertyconfiguration1234567891011121314151617181920212223242526272829303132 （4）修改Hadoop环境变量〔Tomhadoop102software〕vimHADOOPHOMEetchadoopshellprofile。dtez。sh1 添加Tez的Jar包相关信息hadoopaddprofiletezfunctiontezhadoopclasspath｛hadoopaddclasspathHADOOPHOMEetchadoopafterhadoopaddclasspathoptmoduletezafterhadoopaddclasspathoptmoduletezlibafter｝1234567 （5）修改Hive的计算引擎〔Tomhadoop102software〕vimHIVEHOMEconfhivesite。xml1propertynamehive。execution。enginenamevaluetezvaluepropertypropertynamehive。tez。container。sizenamevalue1024valueproperty12345678 （6）解决日志Jar包冲突〔Tomhadoop102software〕rmoptmoduletezlibslf4jlog4j121。7。10。jar12。4业务分析2。4。1统计视频观看数Top10hive（default）selectvideoId，viewsfromgulivideoorcorderbyviewsdesclimit10；OKvideoidviewsdMH0bHeiRNg425134170XxIhvPRRA202824641dmVU08zVpA16087899RBwUgnyGv015712924QjA5faZF1A815256922CSo1gOd481319983349IDp76kjPw11970018tYnn51C3Xw11823701pv5zWaTEVkI11672017D2kJZOfq7zk11184051Timetaken：15。721seconds，Fetched：10row（s）12345678910111213142。4。2统计视频类别热度Top10 思路 （1）即统计每个类别有多少个视频，显示出包含视频最多的前10个类别。 （2）我们需要按照类别groupby聚合，然后count组内的videoId个数即可。 （3）因为当前表结构为：一个视频对应一个或多个类别。所以如果要groupby类别，需要先将类别进行列转行（展开），然后再进行count即可。 （4）最后按照热度排序，显示前10条。hive（default）selectt1。categoryname，count（t1。videoId）hotfrom（selectvideoId，categorynamefromgulivideoorclateralviewexplode（category）gulivideoorctmpascategoryname）t1groupbyt1。categorynameorderbyhotdesclimit10；OKt1。categorynamehotMusic5375Entertainment4557Comedy4443Film2953Animation2953People2208Blogs2208News2070Politics2070Sports1710Timetaken：23。159seconds，Fetched：10row（s）1234567891011121314151617182。4。3统计出视频观看数最高的20个视频的所属类别以及类别包含Top20视频的个数 思路： （1）先找到观看数最高的20个视频所属条目的所有信息，降序排列 （2）把这20条信息中的category分裂出来（列转行） （3）最后查询视频分类名称和该分类下有多少个Top20的视频hive（default）selectt2。categoryname，count（t2。videoId）videosumfrom（selectt1。videoId，categorynamefrom（selectvideoId，views，categoryfromgulivideoorcorderbyviewsdesclimit20）t1lateralviewexplode（t1。category）t1tmpascategoryname）t2groupbyt2。OKt2。categorynamevideosumBlogs2Comedy6Entertainment6Music5People2UNA1Timetaken：20。196seconds，Fetched：6row（s）1234567891011121314152。4。4统计视频观看数Top50所关联视频的所属类别排序hive（default）selectcategoryname，count（）ctfrom（selectexplode（category）categorynamefrom（selectg。categoryfrom（selectexplode（relatedId）relatedidfrom（selectrelatedId，viewsfromgulivideoorcorderbyviewsdesclimit50）t1）t2joingulivideoorcgont2。relatedidg。videoId）t3）t4OKcategorynamectComedy203Entertainment181Music154Animation65Film65People49Blogs49UNA19Politics14Travel14Sports14News14Places14DIY10Howto10Games9Gadgets9Animals6Pets6Autos2Vehicles2Timetaken：41。837seconds，Fetched：21row（s）123456789101112131415161718192021222324252627282930312。4。5统计每个类别中的视频热度Top10，以Music为例 思路： （1）要想统计Music类别中的视频热度Top10，需要先找到Music类别，那么就需要将category展开，所以可以创建一张表用于存放categoryId展开的数据。 （2）向category展开的表中插入数据。 （3）统计对应类别（Music）中的视频热度。hive（default）createtablegulivideoorccategory（videoIdstring，uploaderstring，ageint，categorystring，lengthint，viewsint，ratefloat，ratingsint，commentsint，relatedIdarraystring）storedasorctblproperties（orc。compresssnappy）；OKTimetaken：0。944secondshive（default）insertintotablegulivideoorccategoryselectvideoId，uploader，age，categoryname，length，views，rate，ratings，comments，relatedIdfromgulivideoorclateralviewexplode（category）hive（default）selectvideoId，viewsfromgulivideoorccategorywherecategoryMusicorderbyviewsdesclimit10；OKvideoidviewsQjA5faZF1A815256922tYnn51C3Xw11823701pv5zWaTEVkI116720178bbTtPL1jRs9579911UMf40daefsI7533070HSoVKUVOnfQ6193057NINJQ5LRh03794886FLn457Pn2Y3604114seGhTWE98DU3296342eiiUFky18s3269875Timetaken：16。568seconds，Fetched：10row（s）1234567891011121314151617181920212223242526272829303132333435362。4。6统计每个类别视频观看数Top3hive（default）selectcategory，videoId，viewsfrom（selectcategory，videoId，views，rank（）over（partitionbycategoryorderbyviewsdesc）rkfromgulivideoorccategory）t1whererk3；OKcategoryvideoidviewsAnimalsl9l19D2sIHI1422837AnimalsLHyJH1yGKZY1085020AnimalsQznZixWX6Q1027067AnimationsdUUx5FdySs5840839Animation6B26asyGKDo5147533Animation55YYaJIrmzo3356163Autos46LQd9dXFRU1262173AutospdiuDXwgrjQ1013697AutosaCamHfJwSGU847442BlogsCSo1gOd4813199833BlogsD2kJZOfq7zk11184051BlogsLB84A3zcmVo4866739ComedydMH0bHeiRNg42513417Comedy0XxIhvPRRA20282464Comedy49IDp76kjPw11970018DIYhut3VRL5XRE2684989DIY6gmP4nk0EOE1353059DIYrZb2VlDyYvk1084141Entertainment1dmVU08zVpA16087899EntertainmentRBwUgnyGv015712924Entertainmentvr3xRRJdd410786529FilmsdUUx5FdySs5840839Film6B26asyGKDo5147533Film55YYaJIrmzo3356163GadgetsGxSdKF5Fd382468395GadgetsgPutYwiiE0o1633482Gadgets7wt5FiZQrgM1399531GamesGxSdKF5Fd382468395GamesgPutYwiiE0o1633482Games7wt5FiZQrgM1399531Howtohut3VRL5XRE2684989Howto6gmP4nk0EOE1353059HowtorZb2VlDyYvk1084141MusicQjA5faZF1A815256922MusictYnn51C3Xw11823701Musicpv5zWaTEVkI11672017Newshr23tpWX8lM4706030NewsqSM3fyiaxM2291369NewsqdS5lkeN882091042PeopleCSo1gOd4813199833PeopleD2kJZOfq7zk11184051PeopleLB84A3zcmVo4866739Petsl9l19D2sIHI1422837PetsLHyJH1yGKZY1085020PetsQznZixWX6Q1027067PlacesbNFP281Uu45231539PlacesAlPqL7IUT6M845180Places5QUdvUhCZc819974Politicshr23tpWX8lM4706030PoliticsqSM3fyiaxM2291369PoliticsqdS5lkeN882091042Sportsq8t7iSGAKik2735003Sports7vL19q8yL542527713SportsPbWsOKh982268107TravelbNFP281Uu45231539TravelAlPqL7IUT6M845180Travel5QUdvUhCZc819974UNAaRNzWyD7C9o8825788UNALIhbap3FlGc2849832UNAR0049tDAU81204982Vehicles46LQd9dXFRU1262173VehiclespdiuDXwgrjQ1013697VehiclesaCamHfJwSGU847442Timetaken：27。612seconds，Fetched：63row（s）123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869702。4。7统计上传视频最多的用户Top10以及他们上传的视频观看次数在前20的视频 思路： （1）求出上传视频最多的10个用户 （2）关联gulivideoorc表，求出这10个用户上传的所有的视频，按照观看数取前20hive（default）selectuploader，videoId，viewsfrom（selectuploader，videoId，views，rank（）over（partitionbyuploaderorderbyviewsdesc）rkfrom（selectt1。uploader，videoId，viewsfrom（selectuploaderfromgulivideouserorcorderbyvideosdesclimit10）t1joingulivideoorcgont1。uploaderg。uploader）t2）t3whererk20；
鸡蛋这么吃太香嫩，就是有点儿挑战厨艺，但是做两回就能出师了小本创业，开一个机制木炭厂，或者做烧烤食材供应商？都可以试试郑和航海图之满剌加国扼关锁钥，中转之地宝宝老是呛奶，喂奶时乳头疼？警惕舌系带过短，早期手术效果好聊聊银行理财亏本和利率下行这两件事险被零封，147先生福德出局，中国名将丁俊晖第4次晋级英锦赛生完孩子后，为何会长妊娠纹？若不做好这些预防，以后会难以忍受德国队凯文和超模未婚妻度假太燃！腹肌绝了，未婚妻穿比基尼好辣1小时直达，2万株红枫林，就在重庆主城区单车上的幸福电商平台接口拒绝被坑，品牌商家可以这样做中车金租拟引战增资年内多家金租公司股权拟变更