HIve学习笔记

Hive入门

Hive简介

    Hive是建立在 Hadoop 上的数据仓库基础构架。它提供了一系列的工具，可以用来进行数据提取转化加载（ETL）。这是一种可以存储、查询和分析存储在 Hadoop 中的大规模数据的机制。Hive 基于简单的类 SQL 查询语言（HQL）来读、写和管理数据。

    Hive不是关系型数据库，不适合实时进行查询、更新或者进行OLTP操作。

    Hive在数据库中存放schema,数据存放在hdfs文件中，数据库和表都是路径。

    Hive 查询操作过程遵守Hadoop MapReduce 的作业执行模型，Hive将用户的HiveQL 语句通过解释器转换为MapReduce 作业提交到Hadoop 集群上，Hadoop 监控作业执行过程，然后返回作业执行结果给用户。Hive 并非为联机事务处理而设计，Hive 并不提供实时的查询和基于行级的数据更新操作。

Hive工作原理

如上图所示，具体为：
    1.用户提交查询任务给编译器Driver;
    2.编译器（Complier）获取查询任务；
    3.编译器基于查询任务去MetaStore(元数据)中获取需要的原始数据信息；
    4.编译器获取到元数据信息，对任务进行编译转换为MapReduce形式；
    5.编译器将转换以后的任务提交给Driver;
    6.Driver将计划转交给ExecutionEngine(引擎)执行，获取元数据，提交给JobTracher执行任务，读取HDFS文件进行数据提取；
    7.将获取到的数据返回给Driver,最后展示给用户。

Hive安装

待补充

Hive简单命令

hql和sql的语法类似，具体如下：
1.进入hive

hive

2.查看数据库

show databases;

3.表查看
查看数据库中表格,当没有新建数据库时候，查看的是default数据库中的表格情况

show tables

4.新建数据库
dbname代表新建数据库的名称

create database dbname;

5.使用数据库

use dbname;

6.创建表格

create table tbname(id int, name string);

稍微复杂的表格创建

create table if not exist myhive.employee(eid int, name string)
comment ‘employ’                                                                              -- 注释
row format delimited fields terminated by ‘\t’ lines terminated by ‘\n’ -- 字段按照\t分割，行按照\n分割
stored as textfile;                                                                               -- 存储为文本文件

7.数据导入

load data local inpath ‘/home/employee.txt’into table employee;     -- 从本地导入数据到表employee中

8.数据获取

select * from employee;

9.清屏

！clear

10.查看hdfs中信息

hdfs –lsr /

11.表信息复制

create table myhive.employee2 as select * from myhive.employee;

11.hive本地模式执行操作

set hive.exec.mode.local.auto = true;

Hive表分类

托管表

托管表又叫内部表，当进行表删除的时候，元数据和数据均会被删除。
1.内部表创建

create table if not exist myhive.employee(eid int, name string)
comment ‘employ’                                                                              -- 注释
row format delimited fields terminated by ‘\t’ lines terminated by ‘\n’ -- 字段按照\t分割，行按照\n分割
stored as textfile;                                                                               -- 存储为文本文件

2.从mysql中查看内部表的元数据

select * from TBLS;

3.查看数据

hdfs dfs –ls –R /usr/hive

4.删除表,此时元数据和数据均被删除

drop table myhive.employee;

外部表

hive控制元数据，创建外部表，删除托管表的时候，数据不被删除
1.外部表创建

create external  if not exist myhive.employee(eid int, name string)  -- 多了一个external
comment ‘employ’                                                                              -- 注释
row format delimited fields terminated by ‘\t’ lines terminated by ‘\n’ -- 字段按照\t分割，行按照\n分割
stored as textfile;                                                                               -- 存储为文本文件

2.删除表，元数据被删除，数据不被删除

drop table myhive.employee;

分区表

    Hive在对数据进行查询的时候一般会对整个表进行扫描，当表很大的时候会消耗很多时间，有时候只是需要对表中的一部分数据进行扫描，因此Hive引入分区。
    Hive的分区是在Hive的表结构下面根据分区的字段设置将数据按照目录进行存放，相当于简单的索引功能。
    查询时候分区用where选取数据，插入数据的时候，分区用partion选取
1.创建分区表

create table if not exist myhive.employee(eid int, name string)
comment ‘employ’   
portioned by(country string, state string)   -- 按照国家、地区分区
row format delimited fields terminated by ‘\t’ lines terminated by ‘\n’  
stored as textfile;                -- 存储为文本文件

2.数据加载到指定分区

load data local inpath ‘/home/Hadoop/employee.txt’into
table myhive.employee partion (country = ‘china’, state = ‘sanxi’);

3.查看HDFS

hdfs dfs –cat / /usr/hive/warehouse/ myhive.db/ employee /country = china/state = sanxi/ employee.txt

4.数据按照分区查询

select * from myhive.employee where country = ‘china’and state = ‘sanxi;

5.分区查询模式
默认为非严格模式，即可以按照分区查询，也可以不按照分区查询。
严格模式切换

set hive.mapred.mode = strict;

非严格模式切换

set hive.mapred.mode = unstrict;

6.分区信息查看
查看表信息

desc myhive.employee

查看分区信息

show partitions myhive.employee;    --查看字段、分区
show partitions myhive.employee partition (state = ‘sanxi);  -- 具体分区信息查看
desc extended myhive.employee;   --分区信息扩展：字段、分区、详细表信息

7.手动扩展分区信息

alter table myhive.employee add partition (country = ‘china’, state = ‘henan’);

8.修改表
    大多数的表属性可以通过ALTER TABLE来进行修改，这中操作会修改元数据但是不会修改表数据本身。
表重新命名

alter table myhive.employee rename to myhive.employee1;

添加多个分区

alter table myhive.employee add if not exists
 partition (country = ‘china’, state = ‘henan’) 
 partition (country = ‘china’, state = ‘sichuan’);

移动分区的位置

alter table myhive.employee partition (country = ‘china’, state = ‘henan’) 
set location ‘usr/hive/warehouse/myhive.db/test/country = china/state = henan1’;

增加列

alter table myhive.employee add columns (birth string, fire string);

复制数据到分区表

insert into myhive.employee partion(country = ‘china’, state = ‘henan’) s
elect * from myhive.employee1;

部分复制

insert into myhive.employee partion(country = ‘china’, state = ‘henan’) 
select * from myhive.employee1 where country = china and state = henan; 

hive数据类型

    hive支持不同长度的整型和浮点数据类型，也支持布尔型和字符串类型。

hive基本数据类型

hive基本数据类型

类型	说明	例子
tinyint	1byte有符号的整数	20
smalint	2byte有符号的整数	20
int	4byte有符号的整数	20
bigint	8byte有符号的整数	20
boolean	布尔类型true或false	TRUE
float	单精度	3.1415
double	双精度	3.1415
string	字符序列，单引号和双引号都可以	‘zhang’；“ashakjds”
timestamp	时间戳，浮点或者字符串	‘158030219111’
binary	字节数组	和关系型varbinary类似

注意：hive进行数据比较时候，会隐式的将类型转换为两个数据类型中值较大的类型。如float和double比较的时候，会将float类型转换为double然后进行比较。最好在比较的时候用cast将数据转换为相同的类型

cast (str as INT);    -- 将str数据类型转换为INT

hive 集合数据类型

hive也提供复合数据的存储模式。

struct

和golang中的stuct数据结构类似，可以存储不同类型的数据。
1.数据表创建

create table tb 
(field1 struct<name:string,age:int> comment "test field",  field2 string)
row format delimited fields terminated by ","
collection items terminated by ":";

2.数据插入
直接插入

insert into tb (field1) values(named_struct("name",'zhangsan',"age",25));

本地导入

load data local inpath 'path/test.txt' into table tb;

3.数据获取
使用.查询对应的字段值

select field1.name,   field1.age from tb;  -- 查询对应字段的值
select * from tb;                                      -- 所有信息查询

map

类似于python中的字典数据格式，为键-值对保存
1.数据表创建

create table tb(id int, field2 map<string,string>) 
row format delimited fields terminated by ','
collection items terminated by "|" map keys terminated by ":";

2.数据插入

insert into tb(field2)values(str_to_map("name:zhangsan,age:25")),(str_to_map("name:lisi,age:23"));

3.数据获取
通过[ ]获取对应的值

select field2["name"] as name,field2["age"] as age from tb;

array

类似于python中列表数据，array中的数据为相同类型。
1.数据表创建

create table test4(field4 array<string>);

2.数据插入

insert into test4(field4)values(array("zhangsan","lisi","wangwu"));

3.数据获取
array的数据是以0开始进行索引的

select field4[0] from test4;    --zhangsan

hive向表中装载数据

查询语句添加数据

insert into 和 insert overwrite区别
    insert into 将获取到的数据以追加的方式写入到原始数据后面；
    insert overwrite 如果原始表中有内容会被覆盖掉重新写入；

insert into tabe employee
partion (county = 'US')
select * from employees e
where country = 'US';

    当出现一个表中数据存入多个分区时候，可以使用

from stage_employees se
insert into table employees
    partion(country = 'US')
    select * where se.country = 'US'
insert into table employees
    partion(country = 'China')
    select * where se.country = 'China';

本地导入

LOAD DATA LOCAL INPATH "/home/hadoopUser/data/test1.txt"  
INTO TABLE test1;  

当使用load data ，没有local时候是将数据转移到目标位置。

动态分区插入

当分区过多的时候，Hive可以提供动态分区功能，基于查询的参数推断出需要建立分区的名称。
注意：
     1）静态分区需要载动态分区之前。
     2）动态分区默认是关闭的。开启以后，默认是按照严格模式（至少有一个分区是静态的执行的）
1.动态分区

insert overwrite table employees
partion(country, state)
select .., se.country, se.state
from stage_employees se;

2.混合用动态和静态分区

insert overwrite table employees
partion(country = ‘US’, state)
select .., se.country, se.state
from stage_employees se
where country = 'US';

3.动态分区设置

属性名称	默认值	描述
hive.exec.dynamic.partion	FALSE	设置成true时候代表开启分区功能
hive.exec.dynamic.partion.mode	strict	设置为nonstrict ,代表允许所有分区都是动态的
hive.exec.dynamic.partions.permode	100	每一个mapper或者reducer可以允许创建的最大分区数
hive.exec.dynamic.partions.permode	+1000	一个动态分区可以创建的最大动态分区个数
hive.exec.max.created.files	100000	全局可以创建的最大文件个数

动态分区属性设置

创建表格并加载数据

在一个语句中完成数据表创建并导入数据

create table em
as 
select * from employees;

数据导出

1.拷贝文件到新的文件中

hadoop fs -cp old_path target_path

2.从数据库中导出

insert overwrite local directory '/tmp/data'
select *from employees;

hive 查询

join语句

hive支持join连接，只是他只支持等值的连接。
注意：1）hive on字句只支持数据相等的情况；
           2）hive不支持在on子句的谓词中使用OR。
           2）hive一般情况让最大的表放在最后。

inner join

inner join ,内连接，提取两个表中相匹配的数据。

select s.name s.age ,info.grade
from student s join student_info info on
s.name = info.name
where info.grade > 60;

当有一张很大的表的时候，可以使用/*+STREAMTALBLE（table）*/来设定对应的大表为驱动表。

  select /*+STREAMTALBLE（s）*/ s.name s.age ,info.grade
    from student s join student_info info on
    s.name = info.name
    where info.grade > 60;

outer join

外连接，连接两个表中其中一部分存在的信息。需要注意其where的使用
注意：
1）on 语句中分区过滤条件对外连接是无效的，对内连接是有效的
2）where是表连接完以后进行筛选的操作，其应该注意过滤哪些非NULL值的列，否则容易出错

left outer join

左外连接，左边表符合where的语句都会被保留，右边表如果没有找到匹配的数据就返回NULL。

   select s.name s.age ,info.grade
    from student s left outer join student_info info on
    s.name = info.name
    where info.grade > 60;

right outer join

右外连接，和左外连接相反，右边表的数据满足where条件的都会被保留

select s.name s.age ,info.grade
    from student s right outer join student_info info on
    s.name = info.name
    where info.grade > 60;

full outer join

完全外连接，返回所有的符合where条件的记录，当其中一个表中没有符合的值的话，就用NULL替代。

select s.name s.age ,info.grade
    from student s full outer join student_info info on
    s.name = info.name
    where info.grade > 60;

left semi-join

左半开连接，返回左边表格中满足左边表格的记录在右边表格中的条件。
注意：1）其叫inner join的区别是，当查找到一条记录符合左边的字段，立即停止扫描，不会出现重复的数据
2）where 和 select语句中都不能出现右边表的字段
hive中不允许的格式：

  select s.name s.age
        from student s 
        where s.name  in
         (select info.name from  student_info info);

hive实现的方式

select s.name s.age 
        from student s 
        left simi-join student_info info 
        on s.name  = info.name;

笛卡尔积 join

返回两个表的笛卡尔积结果，得到结果等于左边表格的行数 * 右边表格的行数
注意：其不是并行执行的，会产生大量的数据，不可以进行计算优化。在执行这个时候需要注意数据的量及必要性。

select * from stocks join stocks_1;

map-side join

当所有表中有一张表比较小的时候，可以在mapper过程中将小表放入内存中。因为hive在map执行中，可以逐一和内存中的小表进行匹配，可以省略连接中需要的reduce过程。
Map Join的优点：
    1.不消耗集群的reduce资源。
    2.减少了reduce操作，加快了程序执行。
    3.降低网络负载。
Map Join的缺点：
    1.占用内存(所以加载到内存中的表不能过大，因为每个计算节点都会加载一次)。
    2.生成较多的小文件。
注意：hive对右外连接和全外连接不支持这个优化。
设置方式一：
通过/*+ MAPJOIN（table）*/来设定小表

  select /* +mapjoin(s) */s.name s.age ,info.grade
    from student s join student_info info on
    s.name = info.name
    where info.grade > 60;

设置方式二：

 set hive.auto.convert.join = true   -- 启动小表加入内存优化
 hive.mapioin.smalltable.filesize = 250000000 -- 设置小表的大小阀值

hive 数据排序

order by

order by 全排序，对所有数据通过一个reduce进行排序。不能并行进行，耗时较长。

select * from orders order by cid asc,price desc; --全局排序

sort by

sort by 局部排序，每个reduce都会进行排序
hive中一般要求其和distribute by 联合使用

select * from orders sort by cid asc,price desc; --局部排序

distribute by

distribute by 等价于自定义分区函数，控制map输出在reducer中的划分方式，要写在sort by 之前。

select * from orders distribute by cid sort by cid asc,price desc; --先按照cid分区，再局部排序