Hive基础知识

Hive简介

  • Hive是什么

Hive是基于Hadoop的一个数据仓库工具,==可以将结构化的数据文件映射为一张数据库表==,并提供类SQL查询功能。其本质是将SQL转换为MapReduce的任务进行运算,底层由HDFS来提供数据的存储,说白了hive可以理解为一个将SQL转换为MapReduce的任务的工具,甚至更进一步可以说hive就是一个MapReduce的客户端

  • Hive与数据库的区别

  • Hive 具有 SQL 数据库的外表,但应用场景完全不同。
  • ==Hive 只适合用来做海量离线数据统计分析,也就是数据仓库==。

优缺点

  • ==优点==

    • 操作接口采用类SQL语法,提供快速开发的能力(简单、容易上手)。

    • 避免了去写MapReduce,减少开发人员的学习成本。

    • Hive支持用户自定义函数,用户可以根据自己的需求来实现自己的函数。

  • ==缺点==

    • Hive 不支持记录级别的增删改操作
    • Hive 的查询延迟很严重
      • hadoop jar xxxx.jar xxx.class /input /output
        • 进行任务的划分,然后进行计算资源的申请
        • map 0% reduce 0%
        • map 10% reduce 0%
    • Hive 不支持事务

架构原理

  • 1、用户接口:Client
# CLI(hive shell)、JDBC/ODBC(java访问hive)、WEBUI(浏览器访问hive)
# 第一种 hive Client
bin/hive

# 第二种
nohup  bin/hive --service hiveserver2  &
# 
bin/beeline
beeline> !connect jdbc:hive2://node03:10000
    eg:sql
        bin/hive -e "show databases"
        bin/hive -f hive.sql  # 执行文件 
  • 2、元数据:Metastore

    • 元数据包括:表名、表所属的数据库(默认是default)、表的拥有者、列/分区字段、表的类型(是否是外部表)、表的数据所在目录等;

      • 默认存储在自带的derby数据库中,==推荐使用MySQL存储Metastore==
  • 3、Hadoop集群

    • 使用HDFS进行存储,使用MapReduce进行计算。
  • 4、Driver:驱动器

    • 解析器(SQL Parser)
      • 将SQL字符串转换成抽象语法树AST
      • 对AST进行语法分析,比如表是否存在、字段是否存在、SQL语义是否有误
    • 编译器(Physical Plan):将AST编译生成逻辑执行计划
    • 优化器(Query Optimizer):对逻辑执行计划进行优化
    • 执行器(Execution):把逻辑执行计划转换成可以运行的物理计划。对于Hive来说默认就是mapreduce任务

hive1

数据类型

基本数据类型

类型名称 描述 举例
boolean true/false true
tinyint 1字节的有符号整数 1
smallint 2字节的有符号整数 1
==int== 4字节的有符号整数 1
==bigint== 8字节的有符号整数 1
float 4字节单精度浮点数 1.0
==double== 8字节单精度浮点数 1.0
==string== 字符串(不设长度) “abc”
varchar 字符串(1-65355长度,超长截断) “abc”
timestamp 时间戳 1563157873
date 日期 20190715

复合数据类型

类型名称 描述 举例
array 一组有序的字段,字段类型必须相同 array(元素1,元素2) : array[0] Array(1,2,3)
map 一组无序的键值对 map(k1,v1,k2,v2) :map[‘a’] Map(‘a’,1,’b’,2)
struct 一组命名的字段, col3 struct\<a:string,b:int,c:double> : c.a Struct(‘a’,1,2,0)

Hive的DDL操作

hive的数据库DDL操作

-- 1. 创建数据库
hive > create database db_hive;
# 或者
hive > create database if not exists db_hive;

-- 查询数据库    
show databases like 'db_hive*';

-- 查看数据库详情
desc database db_hive;

-- 显示数据库详细信息
desc database extended db_hive;

-- 删除为空的数据库
hive> drop database db_hive;

-- 如果删除的数据库不存在,最好采用if exists 判断数据库是否存在
hive> drop database if exists db_hive;

-- #如果数据库中有表存在,这里需要使用cascade强制删除数据库
hive> drop database if exists db_hive cascade;

hive的表DDL操作

官网地址:https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL

-- 通过AS 查询语句完成建表:将子查询的结果存在新表里,有数据 
create table if not exists myhive.stu1 as select id, name from stu;

-- 根据已经存在的表结构创建表
create table if not exists myhive.stu2 like stu;

-- 查询表的类型
desc formatted myhive.stu;

-- eg:external 加上为外部表
create  [external] table if not exists myhive.stu3(id int ,name string)
row format delimited fields terminated by '\t' stored as textfile location       '/user/stu2';

-- 加载数据 [local]:加上代表本地路径,不加代表hdfs路径
load data [local] inpath '/kfly/install/hivedatas/teacher.csv' into table myhive.teacher;

-- 内部表转换为外部表
alter table stu set tblproperties('EXTERNAL'='TRUE');

-- 注意: 内部表由于删除表的时候会同步删除HDFS的数据文件,所以确定如果一个表仅仅是你独占使用,其他人不适用的时候就可以创建内部表,如果一个表的文件数据,其他人也要使用,那么就创建外部表
-- 一般外部表都是用在数据仓库的ODS层
-- 内部表都是用在数据仓库的DW层
hive的分区表
-- 创建分区表
create table score(s_id string,c_id string, s_score int) partitioned by (month string) row -format delimited fields terminated by '\t';

-- 创建一个表带多个分区
create table score2 (s_id string,c_id string, s_score int) partitioned by (year string,month string,day string) row format delimited fields terminated by '\t';

-- 加载数据到分区表
load data  local inpath '/kfly/install/hivedatas/score.csv' into table score partition  (month='201806');

-- 展示分区
show  partitions  score;
-- 添加一个 / 多个分区
alter table score add partition(month='201805');
alter table score add partition(month='201804') partition(month = '201803');
-- 删除分区
alter table score drop partition(month = '201806');

-- 进行表的修复,建立我们表与我们数据文件之间的一个关系映射,本地文件数据与元数据之间不同步时执行
msck  repair   table  score4;

2019-07-15_11-35-37

hive的分桶表

  • 分桶是相对分区进行更细粒度的划分。

  • ==分桶将整个数据内容安装某列属性值取hash值进行区分,具有相同hash值的数据进入到同一个文件中==

    • 比如按照name属性分为3个桶,就是对name属性值的hash值对3取摸,按照取模结果对数据分桶。
      • 取模结果为==0==的数据记录存放到一个文件
      • 取模结果为==1==的数据记录存放到一个文件
      • 取模结果为==2==的数据记录存放到一个文件
      • 取模结果为==3==的数据记录存放到一个文件
  • ==作用==

    • 取样sampling更高效。没有分区的话需要扫描整个数据集。
    • 提升某些查询操作效率,例如map side join

    • 在创建分桶表之前要执行的命令

      • ==set hive.enforce.bucketing=true;== 开启对分桶表的支持
      • ==set mapreduce.job.reduces=4;== 设置与桶相同的reduce个数(默认只有一个reduce)
      -- 进入hive客户端然后执行以下命令
      use myhive;
      set mapreduce.job.reduces=4;  
      set hive.enforce.bucketing=true; 
      --分桶表
      create table myhive.user_buckets_demo(id int, name string)
      clustered by(id) 
      into 4 buckets 
      row format delimited fields terminated by '\t';
      
      -- 加载数据到普通表 user_demo 中
      load data local inpath '/user_bucket.txt'  overwrite into table user_demo; 
      
      -- 在hive客户端当中加载数据
      load data local inpath '/user_bucket.txt' into table user_demo;
      
      -- 加载数据到桶表user_buckets_demo中
      insert into table user_buckets_demo select * from user_demo;
      
  • 抽样查询桶表的数据
    • tablesample抽样语句,语法:tablesample(bucket x out of y)
      • x表示从第几个桶开始取数据(x 为start y为步长)
      • y表示桶数的倍数,一共需要从 ==桶数/y== 个桶中取数据
select * from user_buckets_demo tablesample(bucket 1 out of 2)

-- 需要的总桶数=4/2=2个
-- 先从第1个桶中取出数据
-- 再从第1+2=3个桶中取出数据

Hive修改表结构

-- 修改表名称语法
alter table  old_table_name  rename to  new_table_name;
alter table stu3 rename to stu4;
-- 增加列
alter table stu4 add columns(address string);
-- 修改列
alter table stu4 change column address address_id int;

数据导入

-- 数据导入
load data [local] inpath 'dataPath' overwrite | into table student [partition (partcol1=val1,…)]; 

insert overwrite table score5 partition(month = '201806') select s_id,c_id,s_score from score;

-- 查询语句创建表,并加载数据
create table score6 as select * from score;

-- 查询语句中创建表并加载数据(as select)
create external table score (s_id string,c_id string,s_score int) row format delimited fields terminated by '\t' location '/myscore7';
msck repair table score;

-- 数据导入导出
hive (myhive)> create table teacher2 like teacher;
hive (myhive)> import table teacher2 from '/kfly/teacher';

数据导出

-- 将查询的结果导出到本地
insert overwrite local directory '/kfly/install/hivedatas/stu' select * from stu;
-- 将查询的结果格式化导出到本地
insert overwrite local directory '/kkb/install/hivedatas/stu2' row format delimited fields terminated by  ',' select * from stu;
-- 将查询的结果导出到HDFS上==(没有local)==
insert overwrite  directory '/kfly/hivedatas/stu'  row format delimited fields terminated by  ','  select * from stu;

-- shell 命令导出
hive -e "sql语句" >   file
hive -f  sql文件   >    file
bin/hive -e 'select * from myhive.stu;' > /kfly/install/hivedatas/student1.txt
-- 导出到hdfs上
export table  myhive.stu to '/kfly/install/hivedatas/stuexport';

静态、动态分区

-- 静态分区
  create table order_partition(order_number string,order_price  double,order_time string)
  partitioned BY(month string)
  row format delimited fields terminated by '\t';

  load data local inpath '/kfly/install/hivedatas/order.txt' overwrite into table order_partition partition(month='2019-03');

-- 动态分区
--创建普通表
create table t_order(
    order_number string,
    order_price  double, 
    order_time   string
)row format delimited fields terminated by '\t';
load data local inpath '/kkb/install/hivedatas/order_partition.txt' overwrite into table t_order;

--创建目标分区表
create table order_dynamic_partition(
    order_number string,
    order_price  double    
)partitioned BY(order_time string)
row format delimited fields terminated by '\t';


-- 要想进行动态分区,需要设置参数
-- 开启动态分区功能
hive> set hive.exec.dynamic.partition=true; 
-- 设置hive为非严格模式
hive> set hive.exec.dynamic.partition.mode=nonstrict; 
hive> insert into table order_dynamic_partition partition(order_time) select order_number,order_price,order_time from t_order;
-- 查看分区
bin/hive>  show partitions order_dynamic_partition;

排序

 -- 全局排序
 order by 

 --局部排序,每个reduce内部进行排序
 sort by    
 set mapreduce.job.reduces=3;

 -- 分区排序,类似MR中partition,==采集hash算法,在map端将查询的结果中hash值相同的结果分发到对应的reduce文件中==。
 distribute by
 -- 当distribute by和sort by字段相同时,可以使用cluster by方式
 cluster by

Hive java Api

 <dependency>
   <groupId>org.apache.hive</groupId>
   <artifactId>hive-exec</artifactId>
   <version>1.1.0-cdh5.14.2</version>
</dependency>
<dependency>
  <groupId>org.apache.hive</groupId>
  <artifactId>hive-jdbc</artifactId>
  <version>1.1.0-cdh5.14.2</version>
</dependency>
<dependency>
  <groupId>org.apache.hive</groupId>
  <artifactId>hive-cli</artifactId>
  <version>1.1.0-cdh5.14.2</version>
</dependency>
<dependency>
  <groupId>org.apache.hadoop</groupId>
  <artifactId>hadoop-common</artifactId>
  <version>2.6.0-cdh5.14.2</version>
</dependency>
Class.forName("org.apache.hive.jdbc.HiveDriver");
//获取数据库连接
Connection connection = DriverManager.getConnection(url, "hadoop","");
//定义查询的sql语句
String sql="select * from stu";
PreparedStatement ps = connection.prepareStatement(sql);
ResultSet rs = ps.executeQuery();