hive-it十八掌

回顾：
hive
--------------------------------
数据仓库OLAP   在线分析处理，延迟较高
数据库：OLTP   在线事务处理，事务支持

运行在hadoop 类sql运行，sql，hql，mr运算
结构化数据
schema（模式，元信息，存放到数据库中）HDFS 文件  derby，mysql
数据库和表都是文件夹路径。
hive
------------------------------------------
类似mysql  结构化数据
配置hive   bin/env.sh
schematool -initshcema --dertype derby   //初始化模式
create database

常见hive命令：
0.启动hadoop
1.初始化schema库
  hive/bin/schematool -initschema -dbtype derby
2.完成后，在当前目录下创建一个metastore_db（元数据库）
3.进入hive shell
      hive
hive常见命令，类似于mysql
------------------------------------------
     >show databases；
     >create databases myhive;
     >user myhive;
     >show tables;
     //常见表
     >create table if not exists myhive.employee
     （eid int，name string,salary string，destination string）
                  comment 'employee details'
                  row format delimited
                  fields terminated BY  '\t'
                  lines  terminated by  '\n'
                  stored as textfile;
//加载数据到hive（hdfs）
      >load data (local) inpath 'filepath' (overwrite) into table tablename(partition (partcol1=val1,partcol2=val...))
      准备数据employee 数据
      1201  gopal       40000    technical  manager
      1202     manisha     45000    proof      reader
      1203     masthancali 400000   technical  writer
      1204     krian       4000     hr         admin
      1205    kranthi     30000    op         admin
          /t
show tables;  查看表
desc employee;
ok              int
name            string
salary          string
destination     string
//加载数据
---------------------------------------------------------
hive>load data local inpath '/home/ubuntu/desktop/employees.txt'  into table employee
//插入数据=====上传
insert 
select * from employee

hdfs dfs -cat /user/hive/warehouse/myhive.db/employee/employees.txt

select eid,name from employee 0rder by eid limit 2,4;

//支持插入，不支持删除和更新

//修改表alter table
启动hiveserver2服务-----接受多个客户端连接请求
hive --service hiveserver2 start
查看作业
jobs
netstat -ano|more 10000号端口

在eclipse中创建maven项目，使用jdbc连接操作hive数据仓库
-------------------------------------------------------

查看表
dfs -LSR /;

常用聚集函数
------------------------------------------------------
count()
sum()
max()
min()

解决beeline命令行终端的上下键导航历史命令的bug
-----------------------------------------------------
修改行
if((!$(ps -o stat=-p $$)=~ +));then
改为：
if((!$(ps -o stat=-p $$)=~ '+'));then

hive命令
-----------------------------------------------------
$hive>dfs -lsr                                 //执行dfs命令
$hive>!clear;                                  //执行shell脚本
$hive -e “select * from test”                  //-e execute 执行的意思
$hive -S -e “select * from test” >/tmp/myquery/ 重定向   -S execute 静默模式  输出的时候去掉ok等行
$hive >  tab键tab键                            //528个可能么 y(显示函数和关键字)
$hive >this is a comment 
$hive >hive -f /x/x/a.sql                      //-f 执行一个文件，通常用于批处理，调度
$hive>--this is a comment                      //显示所有命令
$hive>set hive.cli.print.header=true           //显示字段名称

$hive>create database if exists not xxx       
$hive>drop database if exists xxx               //存在即删除
$hive>alter database hive3 set dbpropertues('author'='you')     
$hive>drop database if exists xxx               //存在即删除        
$hive>drop database if exists xxx cascde        //级联删除 
$hive>create database hive2 location '/user/ununtu/'   
$hive>create database hive3 with dbpropertime('author'='xupc','createtime'='today')   //创建指定表，指定属性

[创建表语法]
create (temporary)(external)table(if not exists)(db_name.)table_name
((col_name data_type (commebt col_comment),....))
(comment table_comment)
(row format row_format)
(stored as file_format)

例如：
create table if not exists employee(eid int,name string,salary string,destination string)
      comment 'employee details'   //注释
      row format delimited fields terminated by '\t'   //字符结束符
      lines terminated by '\n'   //行结束符
      stored as textfile;        //存储何种文件
      
[带分区的创建表]
create  table hive1.test5(id int,name string,age int )
partitoned by (provence string,city string) 
      row format delimited fields terminated by '\t'   //字符结束符
      lines terminated by '\n'   //行结束符
      stored as textfile;
   
[加载数据===insert]
load data (loacal) inpath 'filepath'(overwrite)into table tablename(partition (partcol1=val1,partcol=val2...))
[加载数据===例子]
load data local inpath '/home/user/sample/txt' overwrite into table employee partitoned by (provence string,city string);


[创建分区]
create  table hive1.test5(id int,name string,age int )partitoned by (provence string,city string);//按照省份和城市分区
[加载数据到指定分区]
load data local inpath '/home/user/sample/employee' overwrite into table hive1.test5 partitoned by (provence='shanxi',city='jizhong');


[手动增加分区]
alter table hive1.test5 add partition(provice='henan',city='piingdingshan');
alter table hive1.test5 add partition(area='hebei',provice='henan',city='piingdingshan');//增加不存在的分区列，是非法的。

[修改表]
$hive>alter table hive1.test5 rename to hive1.test6;                                               //重命名
$hive>alter table hive1.test5 add partion(provive='hebei',city='zhangjiakou')  location 'xxx'      //添加多个分区
                            partion(provive='hebei',city='zhangjiakou')
                            partion(provive='hebei',city='zhangjiakou')
                            partion(provive='hebei',city='zhangjiakou')
$hive>alter table hive1.test5 add partion(provive='hebei',city='zhangjiakou') set  location 'xxx'  //移动分区
$hive>alter table hive1.test6 add columns(birth string,fire string);                                     //增加列
$hive>alter table hive1.test6 replace columns(birth string,fire string);                                     //增加列
$h$hive>alter table hive1.test5 set tblproperties('a'='x',......)          //修改表属性

[启用归档]
$hive>set hive.archive.enabled=true    //设置可归档，默认false
insert into hive2.test5 partition(province='hebei',city='baoding') select * from hive1.test2;//
insert overwrite.table employees partition(country='us',state='or')  select * from stated_employee se where se .cnty='us' and se.st='or';//字段个数要相同
查询时候，分区通过where字句指定
//插入时，分区用partition指定   overwrite 覆盖
[动态分区]
insert overwrite table  hive1.test6 partition(province,city) select id,...,province,city,from table2; //动态分区
hdfs dfs   -lsr /; 查看路径

[查看数据hdfs]
/user/hive/warehouse/hive1.test5/provice=hebei/city=baoding/employee.txt
[分区表的查询模式:strict/nostrict]
set hive.mapred.mode=strict //严格模式,默认是nostrict
show pattitions hive1.test5;
show pattitions hive1.test5.partition(provice='hebei');



$hive>desc extended hive1.test1; //显示扩展信息
$hive>desc database hive2;       //描述数据库
$hive>desc formatted hive1.test1;   //显格式化信息
$hive>create table hive2.test4 like hive1.test1;//复制表
$hive>show tables in hive2;  //显示指定数据库的表的集合
hive控制元数据，删除托管表时，数据不被删除


$hive>create external table hive1.test3 like hive1.test1;             //创建外部表external，只复制表结构，没有有数据
$hive>create external table hive1.test4 as select * from hive1.test1; //创建外部表external，只复制表结构，有数据


$hive> use hive3
使用beeline客户端可以实现远程jdbc连接
-----------------------------------------------------
1.连接
       $hive --service beeline -u jdbc：hive2：//s100:10000/hive1；
       $beeline -u jdbc:hive2://s100:10000/hive1；
       $beeline>!sh  clear；
       $beeline>show databases；
       $beeline>!help  
       $beeline>!dbinfo    //帮助   
                           
配置hive的仓库位置
-----------------------------------------------------
hive-site.xml
hive-metastore.warehouse.dir=/user/hive/warehouse/

hive的数据类型
------------------------------------------------------
bytes：                           案例
tinyint       1                     20  
smallint      2                     20
int           4                     20
bigint        8                     20
boolean       true or false         true
float         single pricision floating point   3.14
double        double pricision floating point   3.14

string 'now is the time','for all good men'
timestamp
binary   字节数组
集合类型
struct struct('john','doe')
hap    map('first','hohn','last','doe')
array  array ('john','doe')
mysql>select * from tbls
hive>show tables;
创建表：
create table hive.test1(id int,name string,age int) tblproperties('author'='you');

hive 所谓的读模式
------------------------------------------------------

托管表
--------------------------------------------------------
hive默认的表都是托管表，hive控制数据的生命周期，删除托管表时候，元数据和数据都被删除
外部表
-------------------
hive控制元数据，删除托管表时，数据不被删除
create external table hive1.test3 like hive1.test1;
create external table hive1.test4 as select * from hive1.test1; 有数据

-------------------------------------------------------------------------------
创建分区表
--------------
create  external table hive1.test2(id int,name string,age int)
        partitioned by (province string,city string)
        row format delimted
            field terminated by '\t'
            lines terminated by '\n'
        store as testfile;
        
手动添加分区
-------------------------------------------------------
alter table hive1.test2 add partition(provice='hebei',city='baoding')
插入数据
------------------------------------------------------
insert into hive1.test2 partition(province='hebei',city=baoding')
selet * from hive1.test6 where province='hebei' and city='shijiazhuang' and id >5

---------------------------------------------------------------------------------
insert overwrite table test2 partition(provice='hebei',city='baoding')select id,name,,age from test1;


动态分区：
--------------------------------------------------------------------------------------
创建test3分区表
create table test3(id int,name string,age int)
prititioned by (province string,city string)
            row format delimted
            field terminated by '\t'
            lines terminated by '\n'
            store as testfile;
动态分区，复制一个表数据到分区表，动态创建分区
如果两个都是动态分区需要关闭严格模式
set hive.exec.dynamic.partition.mode=nonstrict;  //关闭动态分区的严格模式
insert overwrite table hive1.test6 partition(provice,city) select id,name,age'henan' as provice,'kaifeng' as city table2;
使用分区的动态和静态的混合
insert overwrite table hive1.test6 partition(provice='henan',city) select id,name,age'henan' as provice,'kaifeng' as city table2;


查询期间动态创建，并将数据写入创建表中
create tbale test3 as select id ,name from test2 where province='hebei' and city='baoding'

导出hive的数据到本地目录(下载)
---------------------------------------------------------------------------------------------
insert overwrite local directory '/home/ubuntun/a.txt' select * from test2 where province='hebei'

导出hive的数据到HDFS目录(下载)
---------------------------------------------------------------------------------------------
insert overwrite local directory 'hdfs:s100:8020/user/ubuntu/xxx' select * from test2 where province='hebei'


查询数据向多个目录同时输出
from test2 t
insert overwrite local directory '/home/ubuntu/hebei' select * where t.province='hebei'
insert overwrite local directory '/home/ubuntu/henan' select * where t.province='henan';
查询
---------------------
查询，投影查询
select col1,col2,...from table t;
查询，投影查询，指定表的别名
select upper(name) from test2;
select lowper(name) from test2;

select id，name,-age from table
数学函数
--------------------------------------------
select round(12.345)  //四舍五入  12
select ceil(12.345)   13  //天花板
select floor(12.345)  12  //地板
select round(10)  随机数

聚集函数
------------------------------------------------
select count(*) from test2;
select max(*) from test2;
select min(*) from test2;
select avg(*) from test2;
select sum(*) from test2;

去重，distinct;
--------------------------------------------------
select count(distinct name) from test2;
表生成函数
-----------------------------------------------
select explode(array('tom','tomas','tomslee')) from test2;   tom   tomas  tomslee

ascii函数，字符串收个字母ascii值
----------------------------------------------------------------------------------
select ascii('abc');  返回第一个97

base64字符串编码,需要二进制数
binary 函数，可以将字符串转换成二进制数据
select base64(binary('http://localhost:8080/helloworld'));

类型转换
----------------
select cast('120' as bigint)
字符串连接
-------------------------------------
select concat('120','200')  120200

limit 分页查询
--------------------------------------
select * from test2,limit1,3  //offset,length
嵌套查询
------------------------------------------
from(select * from test2 where province='hebei')e select e.id,e.name,e.age where e.city='baoding';
case when then
---------------------------------------------
select id,name,
case  when age<=12 then 'young'
      when age>12 and age<=13 then 'middle' 
      when age>13 and age<=15 then 'old'
      else 'too old'
      end as yearstate from test2;
      
select id ,name n,age from test2 where n like '%t'  //语法错误，where中不能使用字段名

浮点数比较的规避的方案
----------------------------------------------------------------------
select cast(0.2 as float);

hive 的join操作，只支持等值连接

创建customers和orders表 一对多关系
customers
create table customers(id int,name string,age int)
row format delimited
fields terminated by '\t'
lines terminated by '\n'
stored as textfile;

orders
create table orders(id int,orderno string,price float,cid int)
row format delimited
fields terminated by '\t'
lines terminated by '\n'
stored as textfile;

customers.txt 数据

1  tom1  12
2  tom2  13
3  tom3  14

orders.txt 数据

1  NO001    121.34   1
2  NO002    123      1
3  NO003    232      1
4  NO004    32       2
5  NO005    324      2
6  NO006    234      2
7  NO007    5654     null


内连接  join  on
--------------------------------------------------------------------------------------
select a.id,a.name,b.id,b.orderno,b.price from customers a join orders b on a.id=b.cid;

连接查询的优惠手段，查询表的大小从左到右是递增的。
select c.id,c.name,c.age,o.orderno,o.price from customers  c join orders o on c.id=o.cid where  ... //right
select c.id,c.name,c.age,o.orderno,o.price from orders o join customers  c on c.id=o.cid where  ... //wrong
------使用查询暗示
select /**streamtable(c)/c.id,c.name,c.age,o.orderno,o.price from orders o join customers  c on c.id=o.cid where  ... //wrong

left outer join
----------------------------
select c.id,c.name,c.age,o.orderno,o.price from customers a left outer join order b on c.id=o.cid;

right outer join
select c.id ,c.name,c.age,o.orderno,o.price from customers a full outer join order b on c.id=o.cid;

左半连接，select和where子句不能引用到右边的表字段
左表的记录在右表中一旦找到对应的记录，右侧表即停止扫描
-----------------------------------------------------
hive不支持右半连接    right semi join xxxx
--------------------------------------------------------

笛卡尔积m*n
----------------------------------------------------------
select c.id,c.name,o.orderno from customers c.join orders o;
map 连接，一张小表，通过mapper的时候，将小表完全载入内存中
select /++mapjoin (c)*/c.id ,c.name,o.orderno from customers c join orders o;


order by  全排序,对所有数据通过一个reduce进行排序
    select * from orders order by asc,price desc; ----全局排序
sort by  局部排序 每个reduce进行排序(局部排序)
select  * from orders sort by cid asc,price desc; ----局部排序
distrbute by 等价于自定义分区函数
select * from orders distribute cid sort by price desc;  局部排序

cluster by 排序 =====districte by ... sort by.............

分桶采样
select * from orders tablesample(bucket 3 out of 10 on number );
按照数据块百分比采样，100块，抽取10块，如果总共1块，没有采样。
select* from orders tablesample(0.1 percent);


union all 联合操作 ,字段的类型和个数需要匹配。
------------------------------------------------------------------
select id,name from customers union all select id ,order select id ,orderno from orders;

-------view视图---降低查询的复杂度   --创建视图
create view as select...
create view view1 as select c.id,c.name,c.age,o.id,o.orderno,o.price from customers c left outer join orders o on c.id=o.cid;
通过视图直接查询
select * from view1 where price>200;

使用like方式创建视图
create view view2 like view;

删除视图
drop view if exists v2;



hive 索引  创建索引，deferred rebuild 该选项时，索引为空白状态，需要rebuild才能够初始化。
-----------------------------------------------------------------
create index idx_test2_d on table customers(id) as 'org.apache.hadoop.hive.ql.index.compact.compactindexhandler' with deferred 
rebuild idxprpperties ('creator'='me') in table customers_index comment 'this is a comment';

alter index idx_customers_id on customers rebuild;  --------------重建索引

删除索引
drop index idx_customers_id on table customers;

----------桶表------------------------------------bucket
分区是路径，是目录，是文件逻辑隔离，有效降低查询量

桶表  是文件,
创建桶表
create table ... clustered by (filed_name) into n buckers;
create table  orderitems (id int,itemanme string,oid int ) clusrered by (oid)into 3 buckets row delimited field terminated by '\t' lines terminated by '\n' sored as textfile;











hive在什么情况下可以避免mr操作
----------------------------------------------
不是mr的作业就是本地模式
1.全表扫描，没有where字句
   select * from test2
2.where 子句作用只有分区字段。也不需要mr
3.设置hive.exec.model.local.auto=true
  该属性hive会尽量使用local模式查询
4.其余所有的查询都会转换成mr


group by分组查询
------------------------------------------------
select count(*),province from test2 group by province;
select count(*),as c,province from test2 group by province having c>3;//having组内过滤

hive的join操作，只支持等值连接
--------------------------------------------------


回顾2018.10.3
hive
------------------------------------------------------
1.内部表
      数据生命周期
2.外部表
      删除外部表，比没有删除数，删掉了schema（rdbms）
3.分区表
      表目录的子目录
      create table xx(...)pratitioned()
      load data local inpath... into table xxx partition(....)
4.bucket表
数据文件

调优
------------------------------------------------------
1. explain
   解释执行计划
   explain select sum(age) from test2;
2.启用limit调优，避免全表扫描，使用抽样机制
   select * from xxx limit1,2
   hive.limit.optimize.enable=true
3.join 
   使用map端连接(/**streamtable(table)*/)
连接查询表的大小是从左至右依次增长
4.设置本地模式，在单台机器上处理所有任务。
   适用于小数据情况
5.并行执行job
如果job之间没有依赖关系，可以并发执行，缩短执行时间
set.hive.exec.parallel=true
6.严格模式
       set hive.mapred.strict=true   //不推荐，早期使用
       set hive.strict.checks.large.query=false  //默认false,该设置会禁用一下操作
                                                 //1.不指定limit的orderby
                                                   2.对enquiry表不指定分区进行查询
                                                   3.对分区表不指定分区进行查询，和数据量无关，只是个 查询模式
       set hive.strict.checks.type.safety=true;//严格类型的安全检查，不允许以下操作
                                                1.bigint和string之间比较
                                                2.bigint和double之间比较
       
       set hive.strict.checks.checks.cartesian.product=true//不允许笛卡尔积连接
7.调整map,reduce个数
set hive.exec.reducers.bytes.per.reducer=256000000//每个reduce task的字节数
set hive.exec.reducers.max=1009                   //每个reduce task的最大值，属性为负数时，会使用该属性

8.jvm重用
使同一个jvm在一个job(map*,reduce* )中执行多次,避免启动jvm的开销
set mapreduce.job.ubertask.enable=true;//是否启用uber，jvm重用
set mapreduce.job.ubertask.maxmaps=9  //uber，降低
set mapreduce.job.ubertask.maxreduces=1 //uber

set hive.exec.reducers.bytes.per.reducer=100000000
set hive.exec.reducers.max=5
set mapreduce.job.reduces=3

9.索引
   使用index
10.动态分区严格模式
   set hive.exec.dynamic.partition.mode=strict  //动态分区严格模式
   set hive.max.dynamic.partitions=300000       //设置最大分区
   set hive.max.dynamic.partitions.pernode=1000 //设置每个节点的最大分区数
11.推测执行，让mapreduce多个实例并发执行
  set maprduce.map.speculative=true   //map推测
  set mapreduce.reduce.speculative=true //reduce推测
12.多个分组优化
//若多个group by 操作使用的是一个公共的字段。则这些groupby 可以生成一个mr
hive.multigroupby.singlereducer=true； //默认true
13.虚拟列
hive.exec.rowoffset=false //是否使用虚拟列
select input_file_name,block_offset_inside_file from test2;//



压缩
------------------------------------------------------------------------
1.查看压缩的编解码器
 set io.compression.codecs
2.set hive.exec.compress.intermediate=true
.set mapred.compress.map.output=
set mapred.map.output.compression.codec=
3.修改map输出结构的压缩，默认defaultcodec
4.设置最终的job输出结果是否为压缩
set hive.exec.compress.output.=true
5.使用seqencefule 作为存储格式
create table seqfile(...)stored as sequencefuile //使用序列文件存储
insert into t_seq select * from test1k 复制test1数据到t_seq

6.控制sequencefule中map端输出文件的压缩类型,使用block压缩
set mapred.map.output.compression.codec=org.apache.hadoop.io.compress.snappycodec;
set hive.exec.compress.intermediate=true;
set hive.exec.compress.output=true;
set mapred.output.compression.codec=org.apache.hadoop.io.compress.gzipcodec;
set mapred.outout.comoression.type.Block;
7.对分区进行归档
------------------------------------------------------------------------
1.启用hive分区档
set hive archive enabled=true;是否允许归档操作
2.对标的指定分区进行归档(只能对内部分区表进行归档，外部表不可以)
alter table test2 archaive partition ('proveince='hebei',city='baoding')
3.操作
create table test3 as select id ,name,age from test2;
注意：运行时缺少hadooparchive.class类，查看日志/tmp/ubuntu/hive/hive.log 可见
  复制${hadoop_home}/shared/hadoop/tools/hadoops-archive-xxx.jar hive/auxlib/
  复制${hadoop_home}/shared/hadoop/tools/hadoops-archive-xxx.jar hive/lib/
函数（udf:user）
------------------------
1.procedure+function
2.函数操作 show function
        desc(ribe) function case; //查看函数的帮助
        desc  extended function |;//查看函数的扩展帮助
        desc  function concat;     
  abs()绝对值
  
3.udf
输入一行或者多行时，输出单行。
4.udaf：user define aggregate function 用户自定义聚集函数
一行或者多行的n个列作为输入，输出一个值
5.UDTF表生成函数
  n个输入,多行作为输入或者多行作为输入
  select explode(arry（1，2,3）);//
6.自定义函数
1、创建一个类 
2.将函数导出jar包
eclipse --exoort---jar
2.通过hive命令将jar添加到hive的类路径
add jar home/ubuntu/desktop/todate.jar
3.注册函数
create temporary function to_date as 'com.it18zhang.myhive210.func.todate';
4.调用函数
desc function to_date;
自定义表生成函数
1.创建一个类UDTF
2.编译，打包，导出
mvn package -dskuotests
3.复制jar到ubuntu
4.使用hive的add jar命令，添加jar到classpath
5.创建临时函数
create temporary function forx as 'x.x.x.xxx.forudtf'
6.调用函数 
select forx(1,5);