hive基础部分

问题收集：
Hive调试模式：
通过hive -hiveconf hive.root.logger=DEBUG,console 进入Hive，即调试模式，可以看到明细日志。
比如遇到如下卡住不动、或其他任何报错时，都可以这样排查：

2、8032 是ResourceManager的端口，如何报错是之前添加过Yarn的残留造成

http://www.tuicool.com/articles/BB3eArJ
解决办法：
rm -rf /etc/hadoop/conf/yarn-site.xml
rm -rf /etc/hadoop/conf.cloudera/yarn
然后重启服务即可。
可见：集群中同时添加mapreduce和yarn会冲突，优先用的是yarn。
Hive
Hive在企业里用的最多！
提供Mapreduce框架的sql使用接口！
把sql解析成MR的Job Java类，提交给Jobtracker进行执行。

用户接口：
CLI（command line命令行, hive shell），强烈建议，Hive自身推荐的方式
JDBC：可以通过jdbc访问hive，强烈不建议，Hive自身也不推荐。基于HiveServer2 服务
WebUI ：忽略
元数据：表名、列名、字段名。。。
解释器：解析sql
编译器：把sql编译成MR类
优化器：进行优化。不同的人写的sql习惯不同
执行器：提交Job给Jobtracker
hive和Hadoop的关系：
基于Hadoop的一个框架或产品。Hive是MR的一个高级框架。
Hive的数据都存在 HDFS 上，sql（hql）会转化为 MapReduce job进行计算。
元数据库：
默认是PostgreDB，企业里通常会用mysql
MR的特点：高延迟性，处理海量数据
select * from aa ; 很快的原因：
Select * 无where 条件或where里只有分区字段时，不生成MR
desc aa;
Starting Job = job_201603181758_0001, Tracking URL = http://master:50030/jobdetails.jsp?jobid=job_201603181758_0001
Kill Command = /opt/cloudera/parcels/CDH-5.1.0-1.cdh5.1.0.p0.53/lib/hadoop/bin/hadoop job -kill job_201603181758_0001
如果执行错误，可以通过以下语句把作业杀掉
[hadoop@master ~]$ hadoop job -kill job_201603181758_0002
DDL：对库表结构的操作，alter table相关
DML：对数据的操作，通常的sql功能，增删改查
元数据管理:
Gateway角色：控制访问Hive的权限，方式是访问黑、白名单（对IP或IP段）
为啥不用Gateway？
因为企业里控制访问权限有一个体系，通常是服务器防火墙控制IP段 。

Hive的mysql元数据库字符集是latin1，否则会造成转码问题：
alter database hive character set latin1;
Mysql的驱动jar放到hive的lib下：
/opt/cloudera/parcels/CDH/lib/hive/lib parcels安装方式
在线rpm安装方式默认路径是/usr/lib/hive/lib 下
Hive的配置文件：
/etc/hive/conf 下:
hive-env.sh 设置环境变量相关
hive-site.xml：属性参数配置
Hadoop的配置文件路径：
/etc/hadoop/conf
设置宿主机的hosts：

Hive的字段类型
整型 int,bigint
字符串型 string
金额类 ：double
像date和。。其他类型都可以不用，都用string代替即可。
表：
内部表（托管表）：
Drop 表的时候，数据会跟着删除。
外边表（external ）：重要数据建议用外部表
Drop 表的时候，数据不会跟着删除。
默认分隔符：列 为 \001 行分隔符为 \n

使用特性上：普通表和分区表
CREATE TABLE page_view(viewTime INT, userid BIGINT,
page_url STRING, referrer_url STRING,
ip STRING COMMENT ‘IP Address of the User’)
COMMENT ‘This is the page view table’
PARTITIONED BY(dt STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘\t’
STORED AS SEQUENCEFILE;
什么场景用分区表？
增量数据的表
Insert 核心操作之一，另一个是select
Insert有2种方式：
1、append 追加，方式基本不用，不安全
2、overwrite 覆盖（先删后插入），强烈推荐，基本垄断
重点：
首先创建track_log表，再将2015082818文件用rz命令上传到、opt/cloudy/data目录下
Load：加载文件到hive表
hive> LOAD DATA LOCAL INPATH ‘/opt/cloudy/data’ INTO TABLE track_log PARTITION （这个字段代表分区表）(ds=’2015-08-28’); (ds=’2015-08-28’ 是指定字段的格式) 加载该目录下全部文件
或者
hive> LOAD DATA LOCAL INPATH ‘/opt/cloudy/data/2015082818’ INTO TABLE track_log PARTITION (ds=’2015-08-28’);
如上两个方式是append方式，overwrite 的方式如下：
hive> LOAD DATA LOCAL INPATH ‘/opt/cloudy/data/2015082818’ overwrite INTO TABLE track_log PARTITION (ds=’2015-08-28’);
查询分区表的时候需要带着分区条件：如果不带where是查询全表，数据量很大。
hive> select url,ip from track_log where ds=’2015-08-28’ limit 10;
Insert语法如何用overwrite 方式：
Insert overwrite table page…
无overwrite 关键词的话，就是append方式。
思考一个问题：
如果对一个表需要多次insert时，怎么处理？通常用分区表
如果对单个分区需要多次insert时，如何处理？用二级分区，如日期、小时
LOAD DATA LOCAL INPATH ‘/opt/cloudy/data/2015082818’ overwrite INTO TABLE track_log PARTITION (ds=’2015-08-28’,hour=’18’);
LOAD DATA LOCAL INPATH ‘/opt/cloudy/data/2015082819’ overwrite INTO TABLE track_log PARTITION (ds=’2015-08-28’,hour=’19’);

一、Mysql分区和Hive分区的区别：
Mysql分区：
1、首先创建好分区及每个分区的范围，数据进来的时候根据范围自动落到每个分区里。
2、insert的时候不需要指定分区。查询的时候不用指定哪个分区。
Hive分区：
insert数据的时候必须指定分区，不指定分区就报错。
查询的时候带分区范围，比如ds=’’
二、Hive的数据存储

2级分区的存储：

把hive查询的数据落地到HDFS或hive其他表，如何做？
create database rpt location ‘/user/hive/warehouse/rpt’; 创建一个数据库，指定一个存储路径
create table visit_daily //创建一个表
( pv bigint,
uv bigint
) partitioned by (ds string);
//将default.track_log表中查出的数据放到visit_daily表中
insert overwrite table visit_daily partition (ds=’2015-08-28’)
select
count(url) pv ,
count(distinct guid) uv
from default.track_log where ds=’2015-08-28’;
Hive Cli
hive -e “select * from visit_daily” 是在linux环境下执行，而不是在hive命令行执行。
在/opt/cloudy/bin目录下创建visit_daily.sql文件
[root@master bin]# cat visit_daily.sql
select * from rpt.visit_daily;
select * from rpt.visit_daily;
select * from rpt.visit_daily;
//这时就可以使用hive -f命令
hive -f ./visit_daily.sql 记住路径是在当前目录下 是在linux环境下执行，执行一个sql文件
set 查看Hive当前shell的全部属性
set system:user.home=/opt; 设置
set system:user.home; 查看
DDL
desc visit_daily;
desc extended visit_daily;
show tables ‘daily‘; //通配符
查看表有多少分区：partitions关键字
hive> show partitions homework_log;
OK
ds=2015-08-28/hour=18
ds=2015-08-28/hour=19
DML
有insert、select。
无delete、update ，在新版本0.14里有提供，但根本不需要，个人感觉是画龙点睛。
因为hive的数据是文件存储的，天生不支持update；
也不可能支持一个文件中delete一行或多行。
如果需要delete（删除）一个分区的话，
是用hive> alter table track_log drop partition (ds=’2015-08-28’,hour=’18’) ;
创建视图：注意视图同样是已表的形式存在
hive> create view visit_daily_view as select * from visit_daily;
Insert通常是和select一起用，不可单独用。无这样的语法：
Insert into table values (…) ;
Insert 可以写到HDFS上或本地磁盘。
导入到HDFS
hive> insert overwrite directory ‘/user/root/cc1’
select
count(url) pv,
count(distinct guid) uv
from default.homework_log
where ds=’2015-08-28’;
导入到本地磁盘：注意要加上local 原先在/opt/cloudy/tmp目录下的文件要被全部覆盖
hive>insert overwrite local directory ‘/opt/cloudy/tmp’
select
count(url) pv,
count(distinct guid) uv
from default.homework_log
where ds=’2015-08-28’;
课堂未解决问题：set 的用法
alter table cc set location ‘/user/hive/warehouse/rpt/cc1’; 执行时报错

查询
HAVING
SELECT col1 FROM t1 GROUP BY col1 HAVING SUM(col2) > 10
取访问深度（pv）大于20的人都guid和pv数：
select guid,count(url) pv
from default.track_log a
where ds=’2015-08-28’
group by guid
having pv>20 limit 10 ;
或 把count(url)放在下边是一样的结果
select guid
from default.track_log a
where ds=’2015-08-28’
group by guid
having count(url)>20 limit 10 ;
//自己练习
select guid,count(url) pv
from default.homework_log a
where ds=’2015-08-28’
group by guid
having pv>20 limit 10;
排序相关
Order by ：全局排序
Sort by：分区局部排序
Distribute By：和Sort by类似
问题：
下来查看Order by与group by和having## 标题 ##的用法
Join：
Hive 低版本不支持：Select * from a , b where a.id=b.a_id ，0.13后才支持，但不建议这么用。
Select a.* from a join b on a.id=b.a_id
On条件必须是=，不支持<>和like
不好语法案例：
Select a.,b. from
A join b on a.bid=b.id and b.id>10 (或者 b.id=0， 或者 a.name is not null) ;
改为：
Select a.,b. from
A join b on a.bid=b.id
Where b.id>10 ;
关联关系：
允许 1对多、1对1
不可以多对多，此时会出现数据翻倍。
MapJoin：
SELECT /+ MAPJOIN(b) / a.key, a.value
FROM a JOIN b ON a.key = b.key ;
会把b表放内存里，在map端进行join。需要b表的数据量不要过大。
Hive的计算都是基于硬盘的！
执行计划，hql解析成MR的规则
hive> explain //在语句前边加上explain关键字

select guid,count(url) pv
from default.homework_log a
where ds=’2015-08-28’
group by guid
having pv>20 limit 10;
作为key字段场景有：
Group by 字段
Count(distinct 字段)
Join 的on字段
执行计划，可以看出作业的mapreduce job数。

复杂的hql可以解析成多个Mapreduce job执行。

//Hive语法详解二，及自带函数一览及使用
函数
hive> show functions;
自己做日期函数和分切
select split(guid,’_’)[0],
year(‘2015-08-28’),month(‘2015-08-28’),day(‘2015-08-28’)
from
default.homework_log where ds=’2015-08-28’ limit 10;
Case when 案例：
select split(guid,’_’)[0],year(‘2015-08-28’),month(‘2015-08-28’),day(‘2015-08-28’),
case when year(‘2015-08-28’)>’2015’ then ‘2016’
when year(‘2015-08-28’)=’2015’ then ‘2015 case’
when year(‘2015-08-28’)<’2015’ then ‘2016’
when year(‘2015-08-28’)>=’2015’ then ‘2016’
else year(‘2015-08-28’)
end year_value ,
case when year(‘2015-08-28’)>’2015’ then ‘2016’ else NULL end ,
case when year(‘2015-08-28’)<’2015’ then ‘2014’ else NULL end
from
default.track_log where ds=’2015-08-28’ limit 10;
函数例子2：
select guid,reverse(guid),length(guid),concat(guid,’-‘,’zhuijia’,’-‘,ip),
substr(guid,0,10),substr(guid,10),upper(guid),lower(guid),
trim(guid),ltrim(guid),rtrim(guid)
from
default.track_log where ds=’2015-08-28’ limit 10;
下午答疑提问：

INSERT OVERWRITE local DIRECTORY ‘/opt/cloudy/tmp’
select
count(url) pv ,
count(distinct guid) uv
from default.track_log a
where ds=’2015-08-28’ limit 2;
其中/opt/cloudy/tmp 是目录，不可以是文件。