讲义-hive英文名称为DataWarehouse可简写或DWH数据

数据仓库-英文名称为Data Warehoue，可简写为DW或DWH。数据仓库的目的是构建面向分析的集成化数据环境，为企业提供决策支持（Deciinpt）。可以理解为 数据仓库是面向的（Subject-Oriented）、集成的（Integrated）、非易失的（Non-Volatile）和时变的（Time-Variant）数据集合，用以支持管理决策。面向集非易失时变数据库与数据仓库的区别实际讲的 OLTP（On-LineTransactionProcessing，），也可以称面向交易分析型处理，叫联机分析处理OLAP（On-Line yticalProcessing）一般针对某些的历史数据进行分析，支持管理决策。数据库设计是尽量避免冗余，一般针对某一业务应用进行设计，比如一张简单的Use记录用户名、等简单数据即可，符合业务应用，但是不符合分析。数据仓库在设计是有意引入冗余，依照分析需求，分析维度、分析指标进行设计。按照数据流入流出的过程，数据仓库架构可分为三层——源数据、数据仓库、数据应用源数据层

数据仓库层

：也称为细节层，DW层的数据应该是一致的、准确的、干净的数据即对源系统数据进行 （去除了杂质）后的数据

Extra,转化Transfer装载Load）的过程，ETL是数据仓库的流水线，也可以认为是数据仓库的库的数据状态及ETL的任务运行状态。一般会通过元数据资料库（MetadataRepository）来统元数据可分为技术元数据和业务元数据。技术元数据为开发和管理数据仓库的IT人员使用，HiveHiveHiveCLI、CODC、WeGUI。其中，CLI(mmandlineierae为s命令行；CDC是ive的JA实现，与传统数据库DC类似；WeGI是通过浏览器访问ive。元数据：通常是在关系数据库如mysql/derby中。Hive将元数据在数据库中。Hive中的元数据包括表的名字，表的列和分区及其属性，表的属性（是否为外部表等），表的数据所在等。解释器、编译器、优化器、执行器完成HQL查询语句从词法分析、语法分析、编译、优化以及查询计划的生成。生成的查询计划在HDFS中，并在随后有MapReduce调用执Hive与Hadoop的关Hive这里我们选用hive的版本是2.1.1地址为：apache-hive-2.1.1-之后，将我们的安装包上传到第三台机器的/export/sowares 将我们的hive的安装包上传到第三台服务器的/export/sowares112cdzxvfapachehive2.1.1C第二步：安装yumyuminstallmysqlmysql-servermysql-/etc/init.d/mysqld/etc/init.d/mysqldgrantgrantallprivilegeson*.*grantroot@identifiedby123456flushcdcd/export/servers/apachehive2.1.1cphiveenv.sh.templatehiveHADOOP_HOME=/export/servers/hadoopexportHIVE_CONF_DIR=/export/servers/apachehive2.1.1修改hive-cdcd/export/servers/apachehive2.1.1vimhive<?xmlversion="1.0"encoding="UTF8"<?xmlstylesheettype="text/xsl" 将我们准备好的mysql-connector-java-5.1.38.jar这个jar sudovimexportHIVE_HOME=/export/servers/apachehive2.1.1exportHivecdcd/export/servers/apachehive2.1.1 createdatabaseifnotexists112cd/export/servers/apachehive2.1.1e"createdatabaseifnotexists1212cdcreatedatabaseifnotexistsusecreatetablestu(idint,name通过hive-f来执行我们的 bin/hivefHive创建数据库112createdatabaseifnotexists createdatabasemyhive2location createdatabasefoowithdbproperties describedatabaseextended alterdatabasefoosetdbproperties查看数据库详细信 descdatabaseextended删除数据库 dropdatabase dropdatabasemyhive创建表的语法createcreate[external]table[ifnotexists]table_namecol_namedata_type[comment'字段描述信息col_namedata_type[comment'字段描述信息[comment'表的描述信息[partitionedby(col_name[clusteredby[sortedby(col_name[ascdesc],...)intonum_buckets[rowformat[storted [location'指定表的路径createEXISTS选项来忽略这个异常。（LOCATION），Hive创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表示注释,默认不能使用中文partitioned 下 clusteredbyHive可以进一步组织成桶，也就是说桶是更为细粒度的数据范围划分。Hive也是针对某一列进行桶的组织。sorted stortedas指定表文件的格式,常用格式:SEQUENCEFILE,TEXTFILE,RCFILE,如果文件数据是纯文本，可以使用STOREDASTEXTFILE。如果数据需要压缩，使用stortedas创建表时,如果没有使用external关键字,则该表是内部表（managedtable）1字节的有符号整数-4个字节的带符号整数18字节带符号整数4字节单精度浮点数8字节双精度浮点数‘2016-03-key-value,key必须为原始类型，value可以任意类字段集合,类型可以不同建表入门11234usecreatetablestu(idint,namestring);insertintostuvalues(1,"zhangsan");select*fromstu; createtableifnotexistsstu2(idint,namestring)rowformatdelimitedfieldsterminatedby'\t'; createtableifnotexistsstu2(idint,namestring)rowformatdelimitedfieldsterminatedby'\t'location'/user/stu2'; createtablestu3asselect*fromstu2;# createtablestu4like descformatted. droptable每天将收集到的日志定期流入HDFS文本文件。在外部表（原始日志表）的基础上做大量 createexternaltableteacher(t_idstring,t_namestring)rowformatdelimitedfieldsterminatedby'\t'; createexternaltablestudent(s_idstring,s_namestring,s_birthstring,s_sexstring)rowformatdelimitedfieldsterminatedby'\t'; loaddatalocalinpath'/export/servers/hivedatas/student.csv'intotable loaddatalocalinpath'/export/servers/hivedatas/student.csv'overwriteintotablestudent;11234cdhdfshdfspputtecher.csvloaddatainpath'/hivedatas/techer.csv'intotable件，这样每次操作一个小的文件就会很容易了，同样的道理，在hive当中也是支持这种思想unionunion createtablescore(s_idstring,c_idstring,s_scoreint)partitionedby(monthstring)rowformatdelimitedfieldsterminatedby'\t'; createtablescore2(s_idstring,c_idstring,s_scoreint)partitionedby(yearstring,monthstring,daystring)rowformatdelimitedfieldsterminatedby'\t'; loaddatalocalinpath'/export/servers/hivedatas/score.csv'intotablescorepartition(month='201806'); loaddatalocalinpath'/export/servers/hivedatas/score.csv'intotablescore2partition(year='2018',month='06',day='01'); select*fromscorewheremonth='201806'unionallselect*fromscorewheremonth='201806'; showpartitions altertablescoreadd altertablescoredroppartition(month=： ：12hdfs12hdfshdfsmkdirpputscore.csv createexternaltablescore4(s_idstring,c_idstring,s_scoreint)partitionedby(monthstring)rowformatdelimitedfieldsterminatedby'\t'location'/scoredatas'; 建立表与数据文件之间的一个关系映射 msck table分桶表操作分桶，就是将数据按照指定的字段进行划分到多个文件当中去,分桶就是MapReduce中的分区开启Hive 开启Hive set设置Reduce set createtablecourse(c_idstring,c_namestring,t_idstring)clusteredby(c_id)into3bucketsrowformatdelimitedfieldsterminatedby'\t';桶表的数据加载，由于通标的数据加载通过hdfsdfsput文件或者通过loaddata均不好使，只能通过insertoverwrite创建普通表，并通过insertoverwriter的方式将普通表的数据通过查询的方式加载到桶表当中 create createmon(c_idstring,c_namestring,t_idstring)formatdelimitedfieldsterminatedby loaddatalocalinpath'/export/servers/hivedatas/course.csv'intotable通过insertoverwrite1insertoverwritetablecourseselect*mon重命名 altertableold_table_namerenameto altertablescore4renameto desc altertablescore5addcolumns(mycolstring,mysco altertablescore5changecolumnmyscomysconew droptable1.8hive1123createtablescore3likeinsertintotablescore3partition(month='201807')4 loaddatalocalinpath'/export/servers/hivedatas/score.csv'overwriteintotablescorepartition(month='201806');createcreatetablescore4likeinsertoverwritetablescore4partition(month='201806')selects_id,c_id,s_scorefromscore;Hive112345678SELECTDISTINCT]select_expr,select_expr,FROMtable_reference[WHEREwhere_condition][GROUPBYcol_list[HAVINGcondition]][CLUSTERBYcol_list[DISTRIBUTEBYcol_list][SORTORDERBY][LIMIT sortby不是全局排序，其在数据进入reducer前完成排序。因此，如果用sortby进行排序，并且设置mapred.reduce.tasks>1，则sortby只保证每个reducer的输出有序，不保证全distributeby(字段)根据指定的字段将数据分到不同的reducer，且分发算法是hashclusterby(字段)除了具有distributeby的功能外，还会对该字段进行排序clusterby=distributeclusterby=distributebysortsort select*from selects_id,c_idfrom重命名一个列。便于计算。 selects_idasmyid,c_idfrom求总行数 selectcount(1)from求分数的最大值 selectmax(s_score)from求分数的最小值 selectmin(s_score)from求分数的总和 selectsum(s_score)from求分数的平均值 selectavg(s_score)from select*fromscorelimitWHERE子句紧随FROM查询出分数大于60的数据 select*fromscorewheres_score>A[NOT]BANDC如果A，或者C任一为NLL，则结果为NLL。如果A而且小于或等于C，则结果为TR，反之为AS。如果使用关键字则可达到相反的效果。AIS数值LIKEB是一个SQL下的简单正则表达式，如果AR；反之返回AS。的表达式说明如下：‘x%’表示A以字母‘x’‘%x’表示A必须以字母’x’‘%x%’表示A包含有字母’关键字则可达到相反的效果。ARLIKEB,A 查询分数等于80的所有的数据 select*fromscorewheres_score=查询分数在80到100的所有数据 select*fromscorewheres_scorebetween80and select*fromscorewheres_scoreis查询成绩是80和90的数据 select*fromscorewheres_scoreLIKE和选择条件可以包含字符或数字%%代表零个或多个字符(任意个字符)_ 查找以8开头的所有成绩 select*fromscorewheres_scorelike1.查找第二个数值为9 select*fromscorewheres_scorelike1.查找s_id中含1 select*fromscorewheres_idrlike'[1]';#like select*fromscorewheres_score>80ands_id=查询成绩大于80，或者s_id是01 select*fromscorewheres_score>80ors_id=查询s_id01和02 select*fromscorewheres_idnotinGROUPBY语GROUPBY语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，然后对每个组执行聚合操作。案例： selects_id,avg(s_score)fromscoregroupby selects_id,max(s_score)fromscoregroupbyHAVING having只用于groupby案例 selects_id,avg(s_score)fromscoregroupby求每个学生平均分数大于85 selects_id,avg(s_score)avgscorefromscoregroupbys_idhavingavgscore>85;JOINHive支持通常的SQLJOIN语句，但是只支持等值连接，不支持非等值连接。案例操作:查询分数对应的11selectstuons.s_id=fromscoresjoin表的别 select*fromtechertjoincoursecont.t_id=内连 select*fromtechertinnerjoincoursecont.t_id=左外连左外连接：JOIN操作符左边表中符合WHERE select*fromtechertleftjoincoursecont.t_id=右外连 select*fromteachertrightjoincoursecont.t_id=多表连selectselect*fromteacherleftjoincourseont.t_id=leftjoinscoreons.c_id=leftjoinstudentons.s_id=MapReducejob对表techer和表course进行连接操作，然后会再启动一个MapReducejob将第一个MapReducejob的输出和表score;进行连接操作。全局排OrderBy：全局排序，一个ORDERBY子句排序ASC（ascend）:升序（默认）DESC（descend）:ORDERBY子句在SELECT SELECT*FROMstudentsLEFTJOINscorescoONs.s_id=sco.s_idORDERBYsco.s_scoreDESC; SELECT*FROMstudentsLEFTJOINscorescoONs.s_id=sco.s_idORDERBYsco.s_scoreasc; selects_id,avg(s_score)avgfromscoregroupbys_idorderby多个列排序 selects_id,avg(s_score)avgfromscoregroupbys_idorderby每个MapReduce内部排序（SortBy）局部排SortBy：每个MapReduce set1.查看设置reduce set1. select*fromscoresortby1.将查询结果导入到文件中（按照成绩降序排列 insertoverwritelocaldirectory'/export/servers/hivedatas/sort'select*fromscoresortbys_score;分区排序（DISTRIBUTEDistributeBy：类似MR中partition，进行分区，结合sortby使用。注意，Hive要求DISTRIBUTEBY语句要写在SORTBY语句之前。对于distributeby进试，一定要分配多reduce进行处理，否则无法看到distributeby的效 set通过distributeby insertoverwritelocaldirectory'/export/servers/hivedatas/sort'select*fromscoredistributebys_idsortbys_score;CLUSTER当distributeby和sortby字段相同时，可以使用clusterbyclusterby除了具有distributeby的功能外还兼具sortby的功能。但是排序只能是倒序排序，不selectselect*fromscoreclusterbyselect*fromscoredistributebys_idsortbyHive bin/hive[hiveconfx=y]*[<ifilename>]*[<ffilename><equerystring>][S]1-i从文件初始化HQL-f-f执行4-v输出执行的HQL5-pconnecttoHiveServeronport6-hiveconfx=yUsethistosethive/hadoopconfigurationvariables.设置hive运行时候的参数IC_DIRivesite.xl默认配置文件：IVCOFDI/ive-efalt.ml启动Hive（客户端或Server方式）时，可以在命令行添加-hiveconf bin/hivehiveconf set参数>命令行参数>配置文件参数Hive内容较多，见《Hive文档11 hive>show hive>descfunction hive>descfunctionextended4:常用1123456789#字符串连接函数：#带分隔符字符串连接函数：concat_wsselectcast(1.5as#get_json_object(json解析函数，用来处理json，必须是json格式selectselect 概述 Hive自带了一些函数，比如：max/min等，当Hive提供的内置函数你的业务处UDAF（User-DefinedAggregationFunction）类似于 UDTF（User-DefinedTable-GeneratingFunctions）如继承UDF开发实Step1Maven112345678/artifact/org.apache.hive/hive <artifactId>hive>9<artifactId>hadoop<artifactId>mavencompiler<encoding>UTFStep2Java类集112345publicclassextendspublicTextevaluate(finalstr){Stringtmp_str=if(str!=null&&Stringstr_rettmp_str.substring(0,1).toUpperCase()6789new}new}}Step3项目打包，并上传到hive的 Step4添加jarcdcd/export/servers/apachehive2.7.5mvoriginalday_10_hive_udf1.0SNAPSHOT.jarhive的客户端添们的jar addjar/export/servers/apachehive2.7.5Step5 createtemporaryfunctionmy_upperasStep6 select在实际工作当中，hive当中处理的数据，一般都需要经过压缩，前期我们在学习hadoop的时无否否是否无否无否对应的编码器500OnasinglecoreofaCorei7processorin64-bitmode,Snappycompressesat500ormore pressesat

cor参默认阶建pression.codecs（在core-site.xml中配置 Hadoop使用文件扩展名判断是否支输 输编器在输 press.输具或者编，gzip和输开启map输出阶段压缩可以减少job中map和Reducetask间数据传输量。具体配置如下： set set selectcount(1)from当ive将输出写入到表中时，输出内容同样可以进行压缩。属性 pess.otpt控制着这个功能。用户可能需要保持默认设置文件中的默认值alse，这样默认的输出就是非压缩的纯文本文件了。用户可以通过在查询语句或执行中设置这个值为te，来开启果压缩功能。案例 set 11setpress.codec set insertoverwritelocaldirectory'/export/servers/snappy'select*fromscoredistributebys_idsortbys_iddesc;Hive支持的数的格式主要有：TEXTFILE（行式）、SEQUENCEFILE(行式)、列式和行式行的特点：查询满足条件的一整行数据的时候，列则需要去每个的字段找到对列的特点：因为每个字段的数据，在查询只需要少数几个字段的时候，能大大常用数据格Orc(OptimizedRowColumnar)是hive0.11版里引入的新的格式组成，分别是IndexData,RowData,StripeFooter：rowData:真正的数据 通常情况下，在Parquet数据的时候会按照Block大小设置行组的大小，由于一般情况下每创建表，数据格式为createcreatetablelog_texttrack_timeurlsession_idrefereripend_user_idcity_id)ROWFORMATDELIMITEDFIELDSTERMINATEDBYSTOREDASTEXTFILE loaddatalocalinpath'/export/servers/hivedatas/log.data'intotablelog_text; dfsduh创建表，数据格式为createcreatetabletrack_timeurlsession_idrefereripend_user_idcity_id)ROWFORMATDELIMITEDFIELDSTERMINATEDBYSTOREDASorc insertintotablelog_orcselect*fromlog_text dfsduh创建表，数据格式为createcreatetabletrack_timeurlsession_idrefereripend_user_idcity_id)ROWFORMATDELIMITEDFIELDSTERMINATEDBYSTOREDASPARQUET insertintotablelog_parquetselect*fromlog_text dfsduhORC>Parquet> hive(default)>selectcount(*)fromlog_text;Timetaken:21.54seconds,Fetched:1hive(default)>selectcount(*)fromlog_orc;Timetaken:20.867seconds,Fetched:1row(s)3）Parquethive(default)>selectcount(*)fromTimetaken:22.922seconds,Fetched:1ORC>TextFile>highlevelcompression(oneofNONE,ZLIB,numberofbytesineachcompressionnumberofbytesineachnumberofrowsbetweenindexentries(must>=whethertocreaterowcommaseparatedlistofcolumnnamesforwhichbloomfiltershouldbecreatedfalsepositiveprobabilityforbloomfilter>0.0and1123456789createtablelog_orc_none(track_timestring,urlstring,session_idstring,refererstring,ipstring,end_user_idstring,city_idstring)ROWFORMATDELIMITEDFIELDSTERMINATEDBYSTOREDASorctblproperties insertintotablelog_orc_noneselect*fromlog_text dfsduh1123456789createtablelog_orc_snappy(track_timestring,urlstring,session_idstring,refererstring,ipstring,end_user_idstring,city_idstring)ROWFORMATDELIMITEDFIELDSTERMINATEDBYSTOREDASorctblproperties insertintotablelog_orc_snappyselect*fromlog_text dfsduh/user/hive/warehouse/myhive.db/log_orc_snappyFetchHive中对某些情况的查询可以不必使用MapReduce计算。例如：SELECT*FROMscore;在这种情况下，Hive可以简单地score对应的下的文件，然后输出查询结果到控制台。案例112345setselect*fromscore;selects_scorefromscore;selects_scorefromscorelimit112345setselect*fromscore;selects_scorefromscore;selects_scorefromscorelimit大多数的HadoopJob是需要Hadoop提供的完整的可扩展性来处理大数据集的。不过，有时案例setsetselect*fromscoreclusterbysetsetselect*fromscoreclusterby如果不指定MapJi或者不符合MapJi的条件，那么ive解析器会在ede阶段完成ji,容易发生数据倾斜。可以用MapJi把小表全部加载到内存在map端进行i，避免eer处理。设置自动选择 sethive.auto.convert.join=大表小表的阈值设置（默认25M以下认为是小表 set Group默认情况下，Ma阶段同一Key数据分发给一个ee，当一个keyede端完成，很多聚合操作都可以先在Ma端进行部分聚合，最后在ede端得出最终结果。开启Map端聚合参数设置是否在Map端进行聚合，默认为 sethive.map.aggr=在Map sethive.groupby.mapaggr.checkinterval=有数据倾斜的时候进行负载均衡（默认是 sethive.groupby.skewindata=true，生成的查询计划会有两个MRJob第一个MRJob中，Map的输出结果会随机分布到Reduce中，每个Reduce做部分聚合操作，并输出结果，这样处理的结果是相同的GroupByKey有可能被分发到不同的Reduce中，从而达到第二个MRJob再根据预处理的数据结果按照GroupByKey分布到Reduce中（这个过程可以保证相同的GroupByKey被分布到同一个Reduce中），最后完成最终的聚合操作。数据量小的时候无所谓，数据量大的情况下，由于COUNTDISTINCT操作需要用一个Reduce