Hive 高级编程——深入浅出学Hive
目录：
初始Hive
Hive安装与配置
Hive 内建操作符与函数开发
Hive JDBC
hive参数
Hive 高级编程
Hive QL
Hive Shell 基本操作
hive 优化
Hive体系结构
Hive的原理
配套视频课程
第一部分：产生背景
产生背景
?为了满足客户个性化的需求，Hive被设计成一个很开放的系统，很多内容都支持用户定制，包括：
?文件格式：Text File，Sequence File
?内存中的数据格式：Java Integer/String, Hadoop IntWritable/Text
?用户提供的map/reduce 脚本：不管什么语言，利用stdin/stdout 传输数据
?用户自定义函数
自定义函数
?虽然Hive提供了很多函数，但是有些还是难以满足我们的需求。因此Hive提供了自定义函数开发
?自定义函数包括三种UDF、UADF、UDTF
?UDF(User-Defined-Function)
?UDAF（User- Defined Aggregation Funcation）
?UDTF(User-Defined Table-Generating Functions) 用来解决输入一行输出多行(On-to-many maping) 的需求。
HIVE中使用定义的函数的三种方式
?在HIVE会话中add 自定义函数的jar文件，然后创建function，继而使用函数
?在进入HIVE会话之前先自动执行创建function，不用用户手工创建
?把自定义的函数写到系统函数中，使之成为HIVE的一个默认函数，这样就不需要create temporary function
第二部分：UDF
UDF用法
?UDF(User-Defined-Function)
?UDF函数可以直接应用于select语句，对查询结构做格式化处理后，再输出内容
?编写UDF函数的时候需要注意一下几点
?自定义UDF需要继承org.apache.hadoop.hive.ql.UDF
?需要实现evaluate函数
?evaluate函数支持重载
?UDF只能实现一进一出的操作，如果需要实现多进一出，则需要实现UDAF
UDF用法代码示例
import org.apache.Hadoop.hive.ql.exec.UDF
public class Helloword extends UDF{
public String evaluate(){
return "hello world!";
}
public String evaluate(String str){
return "hello world: " + str;
}
}
开发步骤
?开发代码
?把程序打包放到目标机器上去
?进入hive客户端
?添加jar包：hive>add jar /run/jar/udf_test.jar;
?创建临时函数：hive>CREATE TEMPORARY FUNCTION my_add AS 'com.hive.udf.Add ‘?查询HQL语句：
?SELECT my_add (8, 9) FROM scores;
?SELECT my_add (scores.math, scores.art) FROM scores;
?销毁临时函数：hive> DROP TEMPORARY FUNCTION my_add ;
?细节
?在使用UDF的时候，会自动进行类型转换，例如：
SELECT my_add (8,9.1) FROM scores;
?结果是17.1，UDF将类型为Int的参数转化成double。类型的饮食转换是通过UDFResolver 来进行控制的
第三部分：UDAF
UDAF
?Hive查询数据时，有些聚类函数在HQL没有自带，需要用户自定义实现
?用户自定义聚合函数: Sum, Average……n –1
?UDAF（User- Defined Aggregation Funcation）
用法
?一下两个包是必须的import org.apache.hadoop.hive.ql.exec.UDAF和org.apache.hadoop.hive.ql.exec.UDAFEvaluator
开发步骤
?函数类需要继承UDAF类，内部类Evaluator实UDAFEvaluator接口
?Evaluator需要实现init、iterate、terminatePartial、merge、terminate这几个函数
a）init函数实现接口UDAFEvaluator的init函数。
b）iterate接收传入的参数，并进行内部的轮转。其返回类型为boolean。
c）terminatePartial无参数，其为iterate函数轮转结束后，返回轮转数据，terminatePartial类似于hadoop的Combiner。
d）merge接收terminatePartial的返回结果，进行数据merge操作，其返回类型为boolean。e）terminate返回最终的聚集函数结果。
执行步骤
?执行求平均数函数的步骤
a）将java文件编译成Avg_test.jar。
b）进入hive客户端添加jar包：
hive>add jar /run/jar/Avg_test.jar。
c）创建临时函数：
hive>create temporary function avg_test 'hive.udaf.Avg';
d）查询语句：
hive>select avg_test(scores.math) from scores;
e）销毁临时函数：
hive>drop temporary function avg_test;
UDAF代码示例
public class MyAvg extends UDAF {
public static class AvgEvaluator implements UDAFEvaluator {
}
public void init() {}
public boolean iterate(Double o) {}
public AvgState terminatePartial() {}
public boolean terminatePartial(Double o) { }
public Double terminate() {}
}
第四部分：UDTF
UDTF
?UDTF(User-Defined Table-Generating Functions) 用来解决输入一行输出多行(On-to-many maping) 的需求。
开发步骤
?UDTF步骤：
?必须继承org.apache.Hadoop.hive.ql.udf.generic.GenericUDTF
?实现initialize, process, close三个方法
?UDTF首先会
?调用initialize方法，此方法返回UDTF的返回行的信息（返回个数，类型）
初始化完成后，会调用process方法，对传入的参数进行处理，可以通过forword()方法把结果返回
?最后close()方法调用，对需要清理的方法进行清理
使用方法
?UDTF有两种使用方法，一种直接放到select后面，一种和lateral view一起使用
?直接select中使用：select explode_map(properties) as (col1,col2) from src;
?不可以添加其他字段使用：select a, explode_map(properties) as (col1,col2) from src
?不可以嵌套调用：select explode_map(explode_map(properties)) from src
?不可以和group by/cluster by/distribute by/sort by一起使用：select explode_map(properties) as (col1,col2) from src group by col1, col2
?和lateral view一起使用：select src.id, mytable.col1, mytable.col2 from src lateral view explode_map(properties) mytable as col1, col2;
此方法更为方便日常使用。执行过程相当于单独执行了两次抽取，然后union到一个表里。lateral view
? Lateral View语法
?lateralView: LATERAL VIEW udtf(expression) tableAlias AS columnAlias (',' columnAlias)* fromClause: FROM baseTable (lateralView)*
?Lateral View用于UDTF(user-defined table generating functions)中将行转成列，例如explode(). ?目前Lateral View不支持有上而下的优化。如果使用Where子句，查询可能将不被编译。解决方法见：
此时，在查询之前执行set hive.optimize.ppd=false;
? 例子
?pageAds。它有两个列
string pageid
Array
" front_page"
[1, 2, 3]
"contact_page "
[ 3, 4, 5]
?SELECT pageid, adid FROM pageAds LATERAL VIEW explode(adid_list) adTable AS adid;
?将输出如下结果
string pageid int adid
"front_page" 1
…….
“contact_page" 3
代码示例
public class MyUDTF extends GenericUDTF{
public StructObjectInspector initialize(ObjectInspector[] args) {}
public void process(Object[] args) throws HiveException { }
}
实现：切分”key:value;key:value;”这种字符串，返回结果为key, value 两个字段
Hive日志分析的大数据存储优化探讨 摘要信息化发展水平的提升，使数据成为现代生产生活中不可或缺的关键要素，但社会中很多生产领域产生的数据量都较大，如何实现可用信息转化是当前数据利用与研究的关键问题。文章基于Hive日志分析，对优化大数据的存储进行了探究，希望能够提高Hive日志信息查询效率，优化其整体功能，从而在实际应用中发挥更大的作用。 关键词Hive日志；大数据存储；存储优化 信息数据是当前社会发展领域的重要基础，一切生产与发展活动都要将信息数据作为依据与支持，而信息中数据内含量大，还存在隐含信息数据，对信息数据的充分挖掘与利用，能够有开发出信息数据的更多价值。当前信息技术对数据的开发与利用水平有了提升，但对于很多大数据的存储仍是难题，为此，对Hive 日志分析的大数据存储优化探讨对我国调整存储结构，提高大数据读写效率有着重要意义。 1 基于Hive优化大数据存储策略简述 Hive是隶属于Hadoop的数据仓库工具，其主要发挥的作用是利用HFDS进行大数据存储，然后根据用户的实际要求映射数据，成为数据表；另外，利用其自带的数据查询功能能够快速的为用户提供数据信息，并通过查询内容提交到计算程序中完成用户布置的任务，这项功能也是Hive日志的优势体现，利用这项功能能够快速进行数据信息查询、信息数据分析。所以，在Hive日志的基础特性上展开数据存储优化探究，应重视对日志分析方法的利用，具体的优化可以并从以下几个方面着手：一是，对日志中常用的功能以及查询服务进行全面的分析，也就是通过对用户使用习惯的数据统计，明确用户常用的功能，然后合理分化数据结构，为用户提供更为便利的服务[1]。二是，优化数据导入格式，使用每种数据的专用存储结构。三是，对数据字段进行压缩，但不能改变其数据表的顺序以及字段的物理意义。四是，将数据表作为字段取值的参照标准，然后深入优化存储类型。五是，编写UDF，在不对用户的日常使用习惯造成任何影响的基础上，优化存储数据，从而能够有效提升日志查询功能的效率，并且能够优化数据占据的空间面积。 2 科学分化日志查询区域，优化查询效率 Hive日志本身具备记录功能，也就是在通常情况下，Hive日志能够自动对自身的运行进行记录，这样操作人员减少了很多复杂的操作步骤，能够有效提高操作效率，操作人员可以利用对Hive的标写来具体分析日志，然后根据其具备的EXPLAIN特性，得到抽象与简化后的查询语句语法树，从而提高查询的效率，完善了查询服务功能。利用正则表达式进行特征数据获取，能够获得准确的语法结构或语句结构，从而详细的进行了shell脚本编写，这时工具可以同时或批量执行使用者通过EXPLAIN传递的指令，然后日志在快速时间内利用对用户使用
HIVE从入门到精通 目录 HIVE介绍 (2) 二、hive的安装和配置 (8) 三、hive与hbase集成 (13) 四、HIVE创建目录和表 (16) 六、HIVE查询 (23) 七、HIVE视图 (29) 八、索引 (30) 九、hive schema (30) 十、Hive join (33) 十一、Hive基本语法 (37) 十二、Hive操作语句 (40) 十三、数据操作语句 (50) Hive 优化 (56)
HIVE介绍 主要介绍 背景及体系结构 1背景 应用于工业的商务智能收集分析所需的数据集正在大量增长，使 得传统的数据仓库解决方案变得过于昂贵。Hadoop 是一个流行的开源map-reduce实现，用于像yahoo, Facebook一类的公司。来存储和处 理商用硬件上的大范围数据集。然而map-reduce程序模型还是处于很 低级别，即需要开发者来书写客户程序，这些程序往往难于维护与重用。 用hbase做数据库，但由于hbase没有类sql查询方式,所以操作 和计算数据非常不方便，于是整合hive，让hive支撑在hbase数据库层面的hql查询。hive也叫做数据仓库。 2定义 Hive是基于Hadoop(HDFS, MapReduce)的一个数据仓库工具，可 以将结构化的数据文件映射为一张数据库表，并提供类SQL查询功能。 本质是将SQL转换为MapReduce程序。 3体系结构 Hive本身建立在Hadoop的体系结构上，可以将结构化的数据文 件映射为一张数据库表，并提供完整的sql查询功能，可以将sql语 句转换为MapReduce任务进行。并按照该计划生成MapReduce任务后 交给Hadoop集群处理，Hive的体系结构如图1-1所示：
进入hive shell #hive或者hive --service cli Hive 的启动方式: hive 命令行模式，直接输入/hive/bin/hive的执行程序，或者输入hive –service cli hive web界面的启动方式，hive –service hwi hive 远程服务(端口号10000) 启动方式，hive --service hiveserver hive 远程后台启动(关闭终端hive服务不退出): nohup hive -–service hiveserver & 显示所有函数： hive> show functions; 查看函数用法： hive> describe function substr; 查看hive为某个查询使用多少个MapReduce作业 hive> Explain select a.id from tbname a; -------------------------------------------------------------------------- 表结构操作： 托管表和外部表 托管表会将数据移入Hive的warehouse目录；外部表则不会。经验法则是，如果所有处理都由Hive完成， 应该使用托管表；但如果要用Hive和其它工具来处理同一个数据集，则使用外部表。 创建表(通常stored as textfile)： hive> create table tbName (id int,name string) stored as textfile; 创建表并且按分割符分割行中的字段值(即导入数据的时候被导入数据是以该分割符划分的，否则导入后为null，缺省列为null)； hive> create table tbName (id int,name string) row format delimited fields terminated by ','; 创建外部表: hive>create external table extbName(id int, name string); 创建表并创建单分区字段ds(分区表指的是在创建表时指定的partition的分区空间。): hive> create table tbName2 (id int, name string) partitioned by (ds string); 创建表并创建双分区字段ds: hive> create table tbname3 (id int, content string) partitioned by (day string, hour string); 表添加一列: hive> alter table tbName add columns (new_col int); 添加一列并增加列字段注释: hive> alter table tbName add columns (new_col2 int comment 'a comment'); 更改表名: hive> alter table tbName rename to tbName3; 删除表(删除表的元数据，如果是托管表还会删除表的数据): hive>drop table tbName; 只删除内容(只删除表的内容，而保留元数据，则删除数据文件)： hive>dfs –rmr ‘warehouse/my-table’; 删除分区，分区的元数据和数据将被一并删除： hive>alter table tbname2 drop partition (dt='2008-08-08', hour='09'); -------------------------------------------------------------------------- 元数据存储(从HDFS中将数据导入到表中都是瞬时的):
优化时，把hive sql当做map reduce程序来读，会有意想不到的惊喜。 理解hadoop的核心能力，是hive优化的根本。这是这一年来，项目组所有成员宝贵的经验总结。 长期观察hadoop处理数据的过程，有几个显著的特征: 1.不怕数据多，就怕数据倾斜。 2．对jobs数比较多的作业运行效率相对比较低，比如即使有几百行的表，如果多次关联多次汇总，产生十几个jobs，没半小时是跑不完的。map reduce作业初始化的时间是比较长的。 3.对sum，count来说，不存在数据倾斜问题。 4.对count(distinct ),效率较低，数据量一多，准出问题，如果是多count(distinct )效率更低。 优化可以从几个方面着手： 1. 好的模型设计事半功倍。 2. 解决数据倾斜问题。 3. 减少job数。 4. 设置合理的map reduce的task数，能有效提升性能。(比如，10w+级别的计算，用160个reduce，那是相当的浪费，1个足够)。 5. 自己动手写sql解决数据倾斜问题是个不错的选择。set hive.groupby.skewindata=true;这是通用的算法优化，但算法优化总是漠视业务，习惯性提供通用的解决方法。 Etl开发人员更了解业务，更了解数据，所以通过业务逻辑解决倾斜的方法往往更精确，更有效。 6. 对count(distinct)采取漠视的方法，尤其数据大的时候很容易产生倾斜问题，不抱侥幸心理。自己动手，丰衣足食。
7. 对小文件进行合并，是行至有效的提高调度效率的方法，假如我们的作业设置合理的文件数，对云梯的整体调度效率也会产生积极的影响。 8. 优化时把握整体，单个作业最优不如整体最优。 迁移和优化过程中的案例： 问题1：如日志中，常会有信息丢失的问题，比如全网日志中的user_id，如果取其中的user_id和bmw_users关联，就会碰到数据倾斜的问题。 方法：解决数据倾斜问题 解决方法1. User_id为空的不参与关联，例如： Select * From log a Join bmw_users b On https://www.doczj.com/doc/7a15932424.html,er_id is not null And https://www.doczj.com/doc/7a15932424.html,er_id = https://www.doczj.com/doc/7a15932424.html,er_id Union all Select * from log a where https://www.doczj.com/doc/7a15932424.html,er_id is null. 解决方法2 ： Select * from log a
实验报告（四）
Hive自定义函数说明函数清单：
用法： getID 通过UUID来生成每一行的唯一ID： select getid() ; oracle_concat hive的concat函数遇到空值的情况下会直接返回空，而在oracle中对于字符串类型空字符串与null是等价对待的 select default.oracle_concat('ff-',null,'','--cc'); Select concat('ff-',null,'','--cc'); getBirthDay 从身份证号码中截取生日信息，返回日期格式为’yyyy-MM-dd’
getGoodsInfo self_date_format 为格式化来自oracle的时间格式，将格式为’yyyy/MM/dd’和’yyyy/MM/dd HH:mm:ss’的日期格式转换为’yyyy-MM-dd’ Select default. self_date_format(‘2012-12-12’); Select default. self_date_format(‘20121212’,’yyyyMMdd’); oracle_months_between 由于当前版本hive不带months_between函数，所以添加 oracle_decode hive中的decode函数为字符编码函数和encode对应。Oracle中decode函数类似case when 函数，添加oracle_decode函数减少sql的改写。与为与oracle功能同步，本函数将null和字符串’’等价对待。 select default.oracle_decode('',null,1,2) r1, default.oracle_decode(null,'',1,2) r2, default.oracle_decode('aaa','','Nnull','aaa','is a*3','aaa') r3, default.oracle_decode('ccc','', 'Nnull','aaa','is a*3','aaa') r4, default.oracle_decode('','', 'Nnull','aaa','is a*3','aaa') r5; BinomialTest _FUNC_(expr1, expr2, p_value, alternativeHypothesis) alternativeHypothesis: 接受指定值的字符串 取值：TWO_SIDED , GREATER_THAN , LESS_THAN 二项分布检测函数。实现oracle中的二项分布检测功能。 计算expr1 等于exper2 的值占数据总数的二项分布检测结果，类型依据alternativeHypothesis 确定
Hive 基础（2）：库、表、字段、交互式查询的基本操作目录[-] ?1、命令行操作 ?2、表操作 ?3、列操作 ?4、查看变量 ?5、一个完整的建库、表例子 ?6、常用语句示例 ?7、Refer： 1、命令行操作 （1）打印查询头，需要显示设置： sethive.cli.print.header=true; （2）加"--"，其后的都被认为是注释，但CLI 不解析注释。带有注释的文件只能通过这种方式执行： hive -f script_name （3）-e后跟带引号的hive指令或者查询，-S去掉多余的输出： hive -S -e "select * FROM mytable LIMIT 3" > /tmp/myquery （4）遍历所有分区的查询将产生一个巨大的MapReduce作业，如果你的数据集和目录非常多， 因此建议你使用strict模型，也就是你存在分区时，必须指定where语句hive> set hive.mapred.mode=strict;
（5）显示当前使用数据库 set hive.cli.print.current.db=true; （6）设置Hive Job 优先级 setmapred.job.priority=VERY_HIGH | HIGH | NORMAL | LOW | V ERY_LOW (VERY_LOW=1,LOW=2500,NORMAL=5000,HIGH=7500,VERY _HIGH=10000) set mapred.job.map.capacity=M设置同时最多运行M个map 任务 set mapred.job.reduce.capacity=N设置同时最多运行N个red uce任务 （7）Hive 中的Mapper个数的是由以下几个参数确定的：mapred.min.split.size ，mapred.max.split.size ，dfs.block.siz e splitSize = Math.max(minSize, Math.min(maxSize, blockSiz e)); map个数还与inputfilles的个数有关，如果有2个输入文件，即使总大小小于blocksize,也会产生2个map mapred.reduce.tasks用来设置reduce个数。 2、表操作 （1）查看某个表所有分区 SHOW PARTITIONS ext_trackflow
目录 一、关系运算： (4) 1. 等值比较: = (4) 2. 不等值比较: <> (4) 3. 小于比较: < (4) 4. 小于等于比较: <= (4) 5. 大于比较: > (5) 6. 大于等于比较: >= (5) 7. 空值判断: IS NULL (5) 8. 非空判断: IS NOT NULL (6) 9. LIKE比较: LIKE (6) 10. JAVA的LIKE操作: RLIKE (6) 11. REGEXP操作: REGEXP (7) 二、数学运算： (7) 1. 加法操作: + (7) 2. 减法操作: - (7) 3. 乘法操作: * (8) 4. 除法操作: / (8) 5. 取余操作: % (8) 6. 位与操作: & (9) 7. 位或操作: | (9) 8. 位异或操作: ^ (9) 9．位取反操作: ~ (10) 三、逻辑运算： (10) 1. 逻辑与操作: AND (10) 2. 逻辑或操作: OR (10) 3. 逻辑非操作: NOT (10) 四、数值计算 (11) 1. 取整函数: round (11) 2. 指定精度取整函数: round (11) 3. 向下取整函数: floor (11) 4. 向上取整函数: ceil (12) 5. 向上取整函数: ceiling (12) 6. 取随机数函数: rand (12) 7. 自然指数函数: exp (13) 8. 以10为底对数函数: log10 (13) 9. 以2为底对数函数: log2 (13) 10. 对数函数: log (13) 11. 幂运算函数: pow (14) 12. 幂运算函数: power (14) 13. 开平方函数: sqrt (14) 14. 二进制函数: bin (14)
Hive性能优化 1.概述 本人在工作中总结Hive的常用优化手段和在工作中使用Hive出现的问题。下面开始本篇文章的优化介绍。 2.介绍 首先，我们来看看hadoop的计算框架特性，在此特性下会衍生哪些问题？ ?数据量大不是问题，数据倾斜是个问题。 ? jobs数比较多的作业运行效率相对比较低，比如即使有几百行的表，如果多次关联多次汇总，产生十几个jobs，耗时很长。原因是map reduce作业初始化的时间是比较长的。 ? sum,count,max,min等UDAF，不怕数据倾斜问题,hadoop在map 端的汇总合并优化，使数据倾斜不成问题。 ? count(distinct ),在数据量大的情况下，效率较低，如果是多count(distinct )效率更低，因为count(distinct)是按group by 字段分组，按distinct字段排序，一般这种分布方式是很倾斜的。举个例子：比如男uv,女uv，像淘宝一天30亿的pv，如果按性别分组，分配2个reduce,每个reduce处理15亿数据。
面对这些问题，我们能有哪些有效的优化手段呢？下面列出一些在工作有效可行的优化手段： ?好的模型设计事半功倍。 ?解决数据倾斜问题。 ?减少job数。 ?设置合理的map reduce的task数，能有效提升性能。(比如，10w+级别的计算，用160个reduce，那是相当的浪费，1个足够)。 ?了解数据分布，自己动手解决数据倾斜问题是个不错的选择。 set hive.groupby.skewindata=true;这是通用的算法优化，但算法优化有时不能适应特定业务背景，开发人员了解业务，了解数据，可以通过业务逻辑精确有效的解决数据倾斜问题。 ?数据量较大的情况下，慎用count(distinct)，count(distinct)容易产生倾斜问题。 ?对小文件进行合并，是行至有效的提高调度效率的方法，假如所有的作业设置合理的文件数，对云梯的整体调度效率也会产生积极的正向影响。 ?优化时把握整体，单个作业最优不如整体最优。 而接下来，我们心中应该会有一些疑问，影响性能的根源是什么？ 3.性能低下的根源
一、文档说明 熟悉Hive功能，了解基本开发过程，及在项目中的基本应用。 注意：本文档中但凡有hive库操作的语句，其后面的“；”是语句后面的，非文档格式需要。每个hive语句都要以“；”来结束，否则将视相邻两个分号“；”之间的所有语句为一条语句。 二、Hive（数据提取）概述 Hive是构建在HDFS 和Map/Reduce之上的可扩展的数据仓库。是对HADOOP的Map-Reduce进行了封装，类似于sql语句（hive称之为HQL）计算数据从而代替编写代码对mapreduce的操作，数据的来源还是HDFS上面的文件。 Hive中的表可以分为托管表和外部表，托管表的数据移动到数据仓库目录下，由Hive管理，外部表的数据在指定位置，不在Hive 的数据仓库中，只是在Hive元数据库中注册。创建外部表采用“create external tablename”方式创建，并在创建表的同时指定表的位置。 Hive本身是没有专门的数据存储格式，也没有为数据建立索引，只需要在创建表的时候告诉Hive数据中的列分隔符和行分隔符，Hive就可以解析数据。所以往Hive表里面导入数据只是简单的将数据移动到表所在的目录中（如果数据是在HDFS上；但如果数据是在本地文件系统中，那么是将数据复制到表所在的目录中）。
三、Hive的元数据 Hive中的元数据包括表的名字，表的列和分区及其属性，表的属性（是否为外部表等），表的数据所在目录等。由于Hive的元数据需要不断的更新、修改，而HDFS系统中的文件是多读少改的，这显然不能将Hive的元数据存储在HDFS中。目前Hive将元数据存储在数据库中，如Mysql、Derby中。 Hive metastore 三种存储方式： Hive的meta 数据支持以下三种存储方式，其中两种属于本地存储，一种为远端存储。远端存储比较适合生产环境。 1、使用derby数据库存储元数据（内嵌的以本地磁盘作为存储），这称为“内嵌配置”。 这种方式是最简单的存储方式，只需要在或做如下配置便可。使用derby存储方式时，运行hive会在当前目录生成一个derby文件和一个metastore_db目录。这种存储方式的弊端是在同一个目录下同时只能有一个hive客户端能使用数据库，否则会提示如下错误（这是一个很常见的错误）。 2、使用本机mysql服务器存储元数据，这称为“本地metastore”。这种存储方式需要在本地运行一个mysql服务器， 3、使用远端mysql服务器存储元数据。这称为“远程metastore”。这种存储方式需要在远端服务器运行一个mysql服务器，并且需要在Hive服务器启动meta服务。
HiveQL详解 HiveQL是一种类似SQL的语言, 它与大部分的SQL语法兼容, 但是并不完全支持SQL标准, 如HiveQL不支持更新操作, 也不支持索引和事务, 它的子查询和join操作也很局限, 这是因其底层依赖于Hadoop云平台这一特性决定的, 但其有些特点是SQL所无法企及的。例如多表查询、支持create table as select和集成MapReduce脚本等, 本节主要介绍Hive的数据类型和常用的HiveQL操作。 1.hive client命令 a.hive命令参数 -e: 命令行sql语句 -f: SQL文件 -h, --help: 帮助 --hiveconf: 指定配置文件 -i: 初始化文件 -S, --silent: 静态模式（不将错误输出） -v,--verbose: 详细模式 b.交互模式 hive> show tables; #查看所有表名 hive> show tables 'ad*' #查看以'ad'开头的表名 hive>set命令 #设置变量与查看变量； hive>set-v #查看所有的变量 hive>set hive.stats.atomic #查看hive.stats.atomic变量 hive>set hive.stats.atomic=false #设置hive.stats.atomic变量 hive> dfs -ls #查看hadoop所有文件路径 hive> dfs -ls /user/hive/warehouse/ #查看hive所有文件 hive> dfs -ls /user/hive/warehouse/ptest #查看ptest文件 hive> source file
Hive性能优化总结 介绍 首先，我们来看看Hadoop的计算框架特性，在此特性下会衍生哪 些问题？ ?数据量大不是问题，数据倾斜是个问题。 ?jobs数比较多的作业运行效率相对比较低，比如即使有几百行的表，如果多次关联 多次汇总，产生十几个jobs，耗时很长。原因是map reduce作业初始化的时间是 比较长的。 ?sum,count,max,min等UDAF，不怕数据倾斜问题,hadoop在map端的汇总合并优化， 使数据倾斜不成问题。 count(distinct ),在数据量大的情况下，效率较低，如果是多count(distinct )效率更低，因为count(distinct)是按group by 字段分组，按distinct字段排序，一般这种分布方式是很倾斜的。举个例子：比如男uv,女uv，像淘宝一天30亿的pv，如果按性别分组，分配2个reduce,每个reduce处理15亿数据。 面对这些问题，我们能有哪些有效的优化手段呢？下面列出一些在工作有效可行的优化手段： 好的模型设计事半功倍。 ?解决数据倾斜问题。 ?减少job数。 ?设置合理的map reduce的task数，能有效提升性能。(比如，10w+级别的计算，用 160个reduce，那是相当的浪费，1个足够)。 ?了解数据分布，自己动手解决数据倾斜问题是个不错的选择。set hive.groupby.skewindata=true;这是通用的算法优化，但算法优化有时不能适应特定 业务背景，开发人员了解业务，了解数据，可以通过业务逻辑精确有效的解决数据 倾斜问题。 ?数据量较大的情况下，慎用count(distinct)，count(distinct)容易产生倾斜问题。 ?对小文件进行合并，是行至有效的提高调度效率的方法，假如所有的作业设置合理 的文件数，对云梯的整体调度效率也会产生积极的正向影响。 优化时把握整体，单个作业最优不如整体最优。 而接下来，我们心中应该会有一些疑问，影响性能的根源是什么？
常用函数大全 mysql_affected_rows
mysql_affected_rows — 取得前一次 MySQL 操作所影响的记录行数 mysql_fetch_array —从结果集中取得一行作为关联数组或数字数组或二者兼 有:
mysql_fetch_array($result, MYSQL_NUM) , MYSQL_NUM 可用 MYSQL_BOTH 或
MYSQL_ASSOC 代替，也可以不写，默认为 MYSQL_BOTH
mysql_fetch_row — 从结果集中取得一行作为枚举数组： mysql_fetch_row（$result); mysql_fetch_assoc（$result)
mysql_fetch_row()从和指定的结果标识关联的结果集中取得一行数据并作为数组返回。每个结果 的列储存在一个数组的单元中，偏移量从 0 开始。 依次调用 mysql_fetch_row()将返回结果集中的下一行，如果没有更多行则返回 FALSE。 mysql_fetch_assoc — 从结果集中取得一行作为关联数组 ：
mysql_fetch_assoc() 和用 mysql_fetch_array() 加上第二个可选参数 MYSQL_ASSOC 完全相同。它 仅仅返回关联数组。这也是 mysql_fetch_array()起初始的工作方式。如果在关联索引之外还需要数字 索引，用 mysql_fetch_array()。 如果结果中的两个或以上的列具有相同字段名，最后一列将优先。要访问同名的其它列，要么用 mysql_fetch_row()来取得数字索引或给该列起个别名。参见 mysql_fetch_array() 例子中有关别名说 明。 有一点很重要必须指出，用 mysql_fetch_assoc()并不明显 比用 mysql_fetch_row()慢，而且还提供了 明显更多的值。
mysql_query()
仅对 SELECT，SHOW，EXPLAIN 或 DESCRIBE 语句返回一个资源标识符，
如果查询执行不正确则返回 FALSE。对于其它类型的 SQL 语句，mysql_query()在执行成功时返回 TRUE，出错时返回 FALSE。非 FALSE 的返回值意味着查询是合法的并能够被服务器执行。这并不说明 任何有关影响到的或返回的行数。 很有可能一条查询执行成功了但并未影响到或并未返回任何行。
Hive规则及常用语法 一.hive介绍 hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张数据库表，并提供简单的sql查询功能，可以将sql语句转换为MapReduce任务进行运行。其优点是学习成本低，可以通过类SQL语句快速实现简单的MapReduce统计，不必开发专门的MapReduce应用，十分适合数据仓库的统计分析。 二.Hive规则 1.建表规则 表名使用小写英文以字符串模块名称，项目以BI开头(数据集市以DM)以’_’分割主业务功能块名,然后详细业务名,最后以数据表类别结尾,数据表存放在相应的表空间 例如: 数据仓库层_业务大类_业务小类-*** 示例 dw_mobile_start_week dw_mobile_start_mon dw_mobile_start_day 1）在建立长期使用的表时，需要给每个字段Column填写Comment栏，加上中文注释方便查看。对于临时表在表名后加上temp; 2）对运算过程中临时使用的表，用完后即使删除，以便及时的回收空间。临时表如果只用来处理一天数据并且每天都要使用不要按时间建分区。避免造成临时表数据越来越大。 3）字段名应使用英文创建，字段类型尽量使用string.避免多表关联时关联字段类型不一致。 4）多表中存在关联关系的字段，名称，字段类型保持一致。方便直观看出关联关系及连接查询。 5）建表时能够划分分区的表尽量划分分区。 6）建表时为表指定表所在数据库中。 2.查询规则 1.多表执行join操作时，用户需保证连续查询中的表的大小从左到右是依次增加的。也 可以使用/*+STREAMTABLES(表别名)*/来标记大表。
1Hive使用入门： 主要流程为： 1.运行putty等ssh客户端连接hive服务器； 2.运行hive命令进入hive shell环境； 3.执行HQL语句进行查询； 本流程中以putty为例，如果使用别的SSH客户端，界面上会不同，基本过程相似。 我们当前使用的hive版本为0.9.0。由于hive本身还在不断开发、升级中，不同版本的hive对各种语句、命令行参数等的支持均不同，请大家注意某个特性从哪一个版本开始支持。Hive官方网站上的教材中有些命令需要到0.10.0等更高版本才支持！ 1.1安装ssh 客户端Putty 软件位置： \\cn1\ctrip\商业智能部\部门公用\SoftWare\putty.zip 解压所可以得到文件 Putty ssh客户端
1.2登录安装hive的机器 1.2.1运行putty 输入ip地址192.168.83.96 和端口号信息1022，如下图：注：一般默认的SSH端口是22，此处必须修改！ 1.2.2登录linux 单击open按钮，按提示输入用户名，并回车，然后按提示输入密码，并回车，例如：
用户名为ppj 密码为HgeeGxR5 提示：可选中复制到剪贴板后，用鼠标右键粘贴 如果用户名、密码正确，则登录成功，顺利进入linux 的bash 环境。 注：此环境类似于运行windows的cmd进入的dos环境。 1.2.3输入hive，进入hive 的shell 环境：
1.2.4查询 执行如下查询语句： Use test_wqd; Select * from pageview limit 5; 屏幕输出即为查询语句的结果。 注意：hive的查询语句以分号作为各条命令的分隔符，结尾的分号不能省略。这一点和SQL Server的T-SQL差异比较大！
关系运算 等值比较: = 语法:A = B 操作类型: 所有基本类型 描述: 如果表达式A与表达式B相等，则为TRUE；否则为FALSE 举例： hive> select 1 from dual where 1=1; 1 不等值比较: <> 语法: A <> B 操作类型: 所有基本类型 描述: 如果表达式A为NULL，或者表达式B为NULL，返回NULL；如果表达式A 与表达式B不相等，则为TRUE；否则为FALSE 举例： hive> select 1 from dual where 1 <> 2; 1 小于比较: < 语法: A < B 操作类型: 所有基本类型 描述: 如果表达式A为NULL，或者表达式B为NULL，返回NULL；如果表达式A 小于表达式B，则为TRUE；否则为FALSE 举例：
hive> select 1 from dual where 1 < 2; 1 小于等于比较: <= 语法: A <= B 操作类型: 所有基本类型 描述: 如果表达式A为NULL，或者表达式B为NULL，返回NULL；如果表达式A 小于或者等于表达式B，则为TRUE；否则为FALSE 举例： hive> select 1 from dual where 1 <= 1; 1 大于比较: > 语法: A > B 操作类型: 所有基本类型 描述: 如果表达式A为NULL，或者表达式B为NULL，返回NULL；如果表达式A 大于表达式B，则为TRUE；否则为FALSE 举例： hive> select 1 from dual where 2 > 1; 1 大于等于比较: >= 语法: A >= B 操作类型: 所有基本类型 描述: 如果表达式A为NULL，或者表达式B为NULL，返回NULL；如果表达式A 大于或者等于表达式B，则为TRUE；否则为FALSE
Hive update实现方案 1.问题 由于hive数仓的特性，不容许数据进行修改，造成hive中的数据更新活着删除很困难的问题，自hive 0.11版本之后，hive也尝试在测试环境允许进行update和delte操作，但这些操作还不成熟，不敢在生产环境放心使用，其中也有一样不足。所以就需要找一种可靠的方案实现hive的数据更新或者删除。 2.方案 2.1.创建数据表 创建两张数据结构一模一样的hive数据表TEST、TEST_TEMP，其中TEST表的存储格式为“ORCFILE”（性能高），TEST_TEMP 表的存储格式为“TEXTFILE”（方便数据加载）。 ID NAME AGE 主键姓名年龄 create table TEST_TEMP ( id string, name string, age string ) comment '临时表' partitioned by (y string,m string,d string) row format delimited fields terminated by',' stored as textfile create table TEST (
id string, name string, age string ) comment '最终表' row format delimited fields terminated by',' stored as orcfile 2.2.初始化 1.通过hive数据load的方式先把数据加载到TEST_TEMP表中 （此处也可以通过sqoop进行数据抽取，不再详述）。 load data local inpath '/home/hadoop/a.txt' overwrite intotable TEST_TEMP 2.通过hive insert overwrite的方式把临时表的数据加载到最终 表TEST中。 insertintotable TEST select id,name,age from TEST_TEMP 2.3.日常 1.通过hive数据load的方式先把数据加载到TEST_TEM表中 （此处也可以通过sqoop进行数据抽取，不再详述）。 load data local inpath '/home/hadoop/b.txt' overwrite intotable TEST_TEMP 2.通过数据比对方式，找出非更新和非增量的数据，人后把这 部分数据覆盖到TEST表中，即保证TEST中的数据和 TEST_TEMP中的没有重复(前提是表中必须有主键)。 INSERT OVERWRITE TABLE TEST SELECT id,name,age FROM TEST a LEFT JOIN TEST_TEMP b on a.id=b.id WHERE b.id is null; 注：上述语句其实就是not in的逻辑，如果日常数据上包含增、 删、改标识，则只需在关联时在TEST_TEMP表上加条件判
一、join优化 Join查找操作的基本原则：应该将条目少的表/子查询放在Join 操作符的左边。原因是在Join 操作的Reduce 阶段，位于Join 操作符左边的表的内容会被加载进内存，将条目少的表放在左边，可以有效减少发生内存溢出错误的几率。 Join查找操作中如果存在多个join，且所有参与join的表中其参与join的key都相同，则会将所有的join合并到一个mapred程序中。 案例： SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1) 在一个mapre程序中执行join SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2) 在两个mapred程序中执行join Map join的关键在于join操作中的某个表的数据量很小，案例： SELECT /*+ MAPJOIN(b) */ a.key, a.value FROM a join b on a.key = b.key Mapjoin 的限制是无法执行a FULL/RIGHT OUTER JOIN b，和map join相关的hive参数：hive.join.emit.interval hive.mapjoin.size.key hive.mapjoin.cache.numrows 由于join操作是在where操作之前执行，所以当你在执行join时，where条件并不能起到减少join数据的作用；案例： SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key) WHERE a.ds='2009-07-07' AND b.ds='2009-07-07' 最好修改为： SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key AND b.ds='2009-07-07' AND a.ds='2009-07-07') 在join操作的每一个mapred程序中，hive都会把出现在join语句中相对靠后的表的数据stream化，相对靠前的变的数据缓存在内存中。当然，也可以手动指定stream化的表：SELECT /*+ STREAMTABLE(a) */ a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1) 二、group by 优化 Map端聚合，首先在map端进行初步聚合，最后在reduce端得出最终结果，相关参数：? hive.map.aggr = true是否在Map 端进行聚合，默认为True ? hive.groupby.mapaggr.checkinterval = 100000在Map 端进行聚合操作的条目数目 数据倾斜聚合优化，设置参数hive.groupby.skewindata = true，当选项设定为true，生成的查询计划会有两个MR Job。第一个MR Job 中，Map 的输出结果集合会随机分布到Reduce 中，每个Reduce 做部分聚合操作，并输出结果，这样处理的结果是相同的Group By Key 有可能被分发到不同的Reduce 中，从而达到负载均衡的目的；第二个MR Job 再