Hive 介绍

Hive 介绍 一. 数据仓库介绍

• 专业定义
  • 英文名称为Data Warehouse，可简写为DW。
  • 是为企业所有级别的决策制定过程，提供所有类型数据支持的战略集合。
  • 它是单个数据存储，出于分析性报告和决策支持目的而创建。
  • 为需要业务智能的企业，提供指导业务流程改进、监视时间、成本、质量以及控制。
• 通俗解释
  • 面向分析的存储系统(面向数据分析的存储系统）
  • 一个面向主题的（Subject Oriented）、集成的（Integrate）、不可修改的（Non-Volatile）、反映历史变化（Time Variant）的数据集合，用于数据分析、辅助管理决策。
    • 面向主题：指数据仓库中的数据是按照一定的主题域进行组织。
    • 集成：指对原有分散的数据库数据经过系统加工, 整理得到的消除源数据中的不一致性。
    • 不可修改：指一旦某个数据进入数据仓库以后只需要定期的加载、刷新，不会更改。
    • 反映历史变化：指通过这些信息，对企业的发展历程和未来趋势做出定量分析预测。
• 数据仓库和数据库对比分析
  • 主要联系
    • 两者均是用来存储数据的，即均为数据的存储载体。
    • 数据仓库也是数据库，是数据库的一种衍生、延深应用。
    • 数库仓库和数据库之间存在数据交互，即你中有我、我中有你。
      • 数据库中的在线数据推送到离线的数据仓库用于分析处理
      • 数据仓库中分析处理的结果数据也通常推送到关系数据库中，便于前台应用的可视化展现应用。
    • 数据仓库的出现，并不是要取代数据库，且当下大部分数据仓库还是用关系数据库管理系统来管理的，即数据库、数据仓库相辅相成、各有千秋。
  • 主要区别
    • 数据库是面向事务的设计，数据仓库是面向主题设计的。
    • 数据库一般存储在线交易数据，实时性强存储空间有限，数据仓库存储的一般是历史数据，实时性弱但存储空间庞大。
    • 数据库设计是尽量避免冗余，数据仓库在设计是有意引入冗余。
    • 数据库是为捕获数据而设计，即实时性强吞吐量弱，数据仓库是为分析数据而设计，即吞吐量强实时性弱。

二. Hive架构设计与Hive运行流程详解

特别说明：hive2.2以后版本变化较大，去掉了HWI模块，HiveCLI模式也直接采用了beeline链接

三. Hive基础概念  1. hive数据模型

2. 数据类型 2.1 数值型

类型

说明

TINYINT

1-byte signed integer

from -128 to 127

SMALLINT

2-byte signed integer

from -32,768 to 32,767

INT

INTEGER

4-byte signed integer

from -2,147,483,648 to 2,147,483,647

BIGINT

8-byte signed integer

from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

FLOAT

4-byte single precision floating point number

DOUBLE

8-byte double precision floating point number

PRECISION 

DECIMAL

Decimal datatype was introduced in Hive0.11.0

(HIVE-2693) and revised in Hive 0.13.0 (HIVE-3976)

2.2 日期类型

类型

说明

TIMESTAMP

UNIX时间戳和可选的纳秒精度

DATE

描述特定的年/月/日，格式为YYYY-MM-DD

2.3 字符串

类型

说明

string

最常用的字符串格式，等同于java String

varchar

变长字符串，hive用的较多，最长为65535

char

定长字符串，比varchar更多一些，一般不要超过255个字符

2.4 布尔类型

类型

说明

boolean

等同于java的boolean用的很少

2.5 字节数组

类型

说明

binary

字节数组类型，可以存储任意类型的数据用的很少

2.6 复杂(集合)数据类型

数据类型

描述

字面语法示例

STRUCT

和C语言中的struct或者”对象”类似，都可以通过”点”符号访问元素内容。例如，如果某个列的数据类型是STRUCT{first STRING, lastdt STRING}，那么第1个元素可以通过字段名.first来引用

struct(

‘John’,

‘Doe’)

MAP

MAP是一组键-值对元组集合，使用数组表示法(例如[‘key’])可以访问元素。例如，如果某个列的数据类型是MAP，其中键->值对是’first’->’John’和’last’->’Doe’，那么可以通过字段名[‘last’]获取值’Doe’

map(‘first’, ‘John’,

‘last’,

‘Doe’)

ARRAY

数组是一组具有相同类型的变量的集合。这些变量称为数组的元素，每个数组元素都有一个编号，编号从零开始。例如，数组值为[‘John’, ‘Doe’]，那么第1个元素可以通过数组名[0]进行引用

ARRAY(

‘John’,

‘Doe’)

3. hive数据文件格式和压缩格式

• 文件格式

文件格式按面向的存储形式不同，分为面向行和面向列两大类文件格式。

面向行/列类型

类型名称

是否可切割计算

优点

缺点

适用场景

面向行

文本文件格式(.txt)

可以

查看、编辑简单

无压缩占空间大、传输压力大、数据解析开销大

学习练习使用

面向行

SequenceFile序列文件格式(.seq)

可以

自支持、二进制kv存储、支持行和块压缩

本地查看不方便：小文件合并成kv结构后不易查看内部数据

生产环境使用、map输出的默认文件格式

面向列

rcfile文件格式(.rc)

可以

数据加载快、查询快、空间利用率高、高负载能力

每一项都不是最高

学习、生产均可

面向列

orcfile文件格式(.orc)

可以

兼具rcfile优点、进一步提高了读取、存储效率、新数据类型的支持

每一项都不是最高

学习、生产均可

• 压缩格式 

压缩格式按其可切分独立性，分成可切分和不可切分两种。

可切分性

类型名称

是否原生支持

优点

缺点

适用场景

可切分

lzo(.lzo)

否

压缩/解压速度快

合理的压缩率

压缩率比gzip低

不原生、需要native安装

单个文件越大，lzo优点越越明显。压缩完成后>=200M为宜

可切分

bzip2(.bz2)

是

高压缩率超过gzip

原生支持、不需要native安装、用linux bzip可解压 *** 作

压缩/解压速率慢

处理速度要求不高、要求高压缩率（冷数据处理经常使用）

不可切分

gzip(.gz)

是

压缩/解压速度快

原生/native都支持使用方便

不可切分、对CPU要求较高

压缩完成后<=130M的文件适宜

不可切分

snappy(.snappy)

否

高速压缩/解压速度

合理的压缩率

压缩率比gzip低

不原生、需要native安装

适合作为map->reduce或是job数据流的中间数据传输格式

4. 数据 *** 作分类

*** 作分类

具体 *** 作

sql备注

DDL

•建表

•删除表

•修改表结构

•创建/删除视图

•创建数据库 

•显示命令

Create/Drop/Alter Database

Create/Drop/Truncate Table

Alter Table/Partition/Column

Create/Drop/Alter View

Create/Drop Index

Create/Drop Function

Show functions;

Describe function;

DML

•数据插入(insert,load)

load data...into table

insert overwrite table 

DQL

•数据查询(select)

四. Hive基本使用

直接输入hive回车，进入hive

常用命令：

•  查看所有数据库：show databases;
•  创建数据库bigdata，即为create database bigdata;
•  选择数据库  use databaseName;
•  查看某个数据库中所有表：show tables;
•  查询数据表中的数据: select * from 表名称
•  删除一个表：drop table 表名称

五. HiveSQL 1. DDL 1.1 建表说明

• 元数据:描述数据的数据
• 表分类：主要分内表和外表
• 内表：元数据和数据本身均被hive管理。删除表则全部删除。
• 外表：元数据被hive管理，数据本身存储在hdfs,不受hive管理。删除表则只删除元数据，数据本身不变。

1.2 建表模板

• CREATE [external] TABLE [IF NOT EXISTS] table_name
• [(col_name data_type [comment col_comment], ...)]
• [comment table_comment]
• [partitioned by (col_name data_type [comment col_comment], ...)]
• [clustered by (col_name, col_name, ...)
• [sorted by (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
• [row format row_format]
• [stored as file_format]
• [location hdfs_path]

关键词解释

• external: 创建内部表还是外部表，此为内外表的唯一区分关键字。
• comment col_comment: 给字段添加注释
• comment table_comment: 给表本身添加注释
• partitioned by: 按哪些字段分区，可以是一个，也可以是多个
• clustered by col_name... into num_buckets BUCKETS:按哪几个字段做hash后分桶存储
• row format:用于设定行、列、集合的分隔符等设置
• stored as : 用于指定存储的文件类型，如text,rcfile等
• location : 设定该表存储的hdfs目录，如果不手动设定，则采用hive默认的存储路径

示例：

• CREATE  TABLE practice_course_info(
• courseNo string,
• name string,
• duration int,
• techername string
• )
• comment 'practice_course_info'
• ROW FORMAT DELIMITED 
• FIELDS TERMINATED BY 't' 
• LINES TERMINATED BY 'n'
• STORED AS  textfile;

• 查看已存在表的详细信息：show create table或者desc tablename/desc formatted tablename
• 显示所有表：show tables;
•  更改表：alter table student rename to student2;
• 增加字段：alter table student2 add columns(age int comment "我是新增加的列");
• 创建视图：create view student2_view as select id,username from student2;
• 删除视图：drop view student2_view;

2. DML 2.1 加载数据脚本

LOAD DATA  INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]

示例：LOAD DATA INPATH '/user/yanyufei/practice_course_info.txt' OVERWRITE INTO TABLE practice_course_info;

 将查询结果插入到数据表中

示例：

• insert overwrite table name
• select name from practice_course_select
• left outer join practice_course_info
• on practice_course_select.courseNo= practice_course_info.courseNo;

 多插入模式（一次查询多次插入）

模板

• FROM from_statement
• INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol=val)] select_statement1  
• [INSERT OVERWRITE TABLE tablename2 [PARTITION ...] select_statement2
• ...

样例

• from student
• insert overwrite table student partition(come_date='20170906') select id,username,classid,classname where come_date='20170905'
• insert overwrite table student partition(come_date='20170907') select id,username,classid,classname where come_date='20170905'
• insert overwrite table student partition(come_date='20170908') select id,username,classid,classname where come_date='20170905'
• 动态分区模式（让分区成为被查询出来的结果表的字段名称变量）

脚本模板

• INSERT OVERWRITE TABLE tablename
• PARTITION (col_name) select_statement FROM from_statement
• 查看分区：show partitions student_outer;
• 清空表：truncate table student;

将查询结果写入hdfs目录

脚本模版

• INSERT OVERWRITE DIRECTORY directory1 SELECt ... FROM ...
• 样例-默认
• 数据写入文件系统时进行文本序列化，且每列用^A来区分，n换行
• insert overwrite directory "/tmp/output2/"
• select * from student where come_date='20170905';

3、DQL 3.1 脚本模板

• SELECt [DISTINCT] select_expr, select_expr, ...
• FROM table_reference
• [WHERe where_condition]
• [GROUP BY col_list [HAVINg condition]]
• [ CLUSTER BY col_list
•    | [DISTRIBUTE BY col_list] [SORT BY| ORDER BY col_list]
• ]
• [LIMIT number]

DISTRIBUTE BY col_list
        以指定字段作为key作hash partition,保证相同的key会到同一个reduce去处理。

Sort By col_list
         以指定字段作为单个reduce排序的key，保证单个reduce内的key有序排列输出。

Order By col_list
         只会生成一个reduce任务，对全部排序

CLUSTER BY col_list
         以指定字段作为key做hash partition，保证相同key会到同一个reduce去处理。该命令相当于distributed by col_list和sort by col_list的联合使用。

• 加入where查询条件
• select * from student where
• 加入limit限制
• select * from student where id='001' limit
• 升序降序：order by desc
• join查询
• 将多个表通过字段关联在一起，形成查询结果
• union all
• 将所有表数据，完全叠加在一起，不去重。
• union
• 将所有表数据，完全叠加在一起，总体去重。
• NULL值判断
• hql中用is NULL或者is not NULL来判断字段是否是NULL值,与""没有直接关系
• hive当中的子查询，必须要给予别名。

六、Hive系统函数

• 查看所有系统函数

     show functions

• 函数分类

            

使用示例

• 使用help解决一个函数不知道怎么用的问题
• desc function split;
• count统计纪录行数
• coalesce
• COALESCE( value1,value2,... )将参数列表中第1个不为null的值作为最后的值
• split将字符串拆分成一个数组
• explode：表成生成函数
• 将一个集合元素，打散成一行一行的组成，即将一行改成多行，换句话说行转列
• lateral view：
• 与explode联用，形成一张新表