hive学习笔记1

hive学习笔记

1. hive的基本概念

1.1 hive简介

Hive在底层的工作是将用户输入的SQL语句转换成MapReduce的任务进行运算，底层是有HDFS来提供数据存储。所以大致可以理解hive为一个将SQL语句转成MapReduce任务的工具

1.2 hive的实现过程

hive架构.png

这里的映射关系较为重要在后面的学习中在着重理解

1.3 什么是hive（面试题）

1、hive是数据仓库建模的工具之一。

2、可以向hive传入一条交互式sql，在海量数据中查询分析得到结果的平台

1.4 为什么学习hive

在前面hadoop的学习中，我们可以感知其代码的复杂，对于复杂的数据查询，对于开发人员的代码能力要求非常高，但如果使用hive的话，可以操作接口采用类似SQL语法去查询，不仅学习难度降低，免去了我们写MapReduce，因为他会在底层自己构建MapReduce。同时它的功能拓展很方便（比如开窗函数）

1.5 hive的特点

① 可拓展性

​ Hive可以自由的扩展集群的规模，一般情况下不需要重启服务

② 延伸性

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

③ 容错

​ 即使节点出现错误，SQL仍然可以完成任务

Hive的优缺点

优点：

​ 1、操作接口采用类sql语法，提供快速开发的能力（简单、容易上手）

​ 2、避免了去写MapReduce,减少开发人员的学习成本

​ 3、Hive的延迟性比较高，因此Hive常用于数据分析，适用于对实时性要求不高的场合

​ 4、Hive 优势在于处理大数据，对于处理小数据没有优势，因为 Hive 的执行延迟比较高。（不断地开关 JVM虚拟机）

​ 5、Hive 支持用户自定义函数，用户可以根据自己的需求来实现自己的函数。

​ 6、集群可自由扩展并且具有良好的容错性，节点出现问题SQL仍可以完成执行

缺点：

​ 1、Hive的HQL表达能力有限

​ （1）迭代式算法无法表达 （反复调用，mr之间独立，只有一个map一个reduce，反复开关）

​ （2）数据挖掘方面不擅长

​ 2、Hive 的效率比较低

​ （1）Hive 自动生成的 MapReduce 作业，通常情况下不够智能化

​ （2）Hive 调优比较困难，粒度较粗 （hql根据模板转成mapreduce，不能像自己编mapreduce 一样精细，无法控制在map处理数据还是在reduce处理数据）

1.6 Hive和传统数据库的对比

hive 和mysql的区别（问题可能不是很专业，但可能出现在面试中）

image-20220531213145918

1.7 Hive的应用场景

日志分析：大部分互联网公司使用hive进行日志分析，如百度、淘宝等。

​ 统计一个网站一个时间段内的pv,uv，SKU,SPU,SKC

​ 多维度数据分析（数据仓库）

海量结构化(关系型)数据离线分析

构建数据仓库

知识点补充

PV（Page View）访问量, 即页面浏览量或点击量，衡量网站用户访问的网页数量；在一定统计周期内用户每打开或刷新一个页面就记录1次，多次打开或刷新同一页面则浏览量累计。

UV（Unique Visitor）独立访客，统计1天内访问某站点的用户数(以cookie为依据);访问网站的一台电脑客户端为一个访客。可以理解成访问某网站的电脑的数量。网站判断来访电脑的身份是通过来访电脑的cookies实现的。如果更换了IP后但不清除cookies，再访问相同网站，该网站的统计中UV数是不变的。如果用户不保存cookies访问、清除了cookies或者更换设备访问，计数会加1。00:00-24:00内相同的客户端多次访问只计为1个访客。

2 Hive架构

Hive是底层封装了 Hadoop 的数据仓库处理工具，它运行在 Hadoop基础上，其系统架构组成主要包含4个部分，分别是用户接口、跨语言服务、底层的驱动引擎以及元数据存储系统。

  1. 用户接口
    主要分为 3 个，分别是 CLI 、 JDBC / ODBC 和 WebUI 。

其中， CLI 即 Shell 终端命令行，它是最常用的方式。

JDBC / ODBC 是 Hive 的 Java 实现，与使用传统数据库 JDBC 的方式类似

WebUI 指的是通过测览器访问 Hive 。

  1. 跨语言服务( Thrift Server )
    Thrift 是 Facebook 开发的一个软件框架，可以用来进行可扩展且跨语言的服务。

Hive 集成了该服务，能让不同的编程语言调用 Hive 的接口。

  1. 底层的驱动引擎（Driver）
    主要包含解析器( SQL Parser )，编译器( Compiler )，优化器( Optimizer )和执行器( Executor )

解析器（SQL Parser）：将SQL字符串转换成抽象语法树AST（从3.x版本之后，转换成一些的stage），这一步一般都用第三方工具库完 成，比如ANTLR；对AST进行语法分析，比如表是否存在、字段是否存在、SQL语义是否有误。

编译器（Physical Plan）：将AST编译（从3.x版本之后，转换成一些的stage）生成逻辑执行计划。

优化器（Query Optimizer）：对逻辑执行计划进行优化。

执行器（Execution）：把逻辑执行计划转换成可以运行的物理计划。对于Hive来说，就是 MR/Spark/flink。

它们用于完成 HQL 查询语句从词法分析、语法分析、编译、优化以及查询计划的生成，生成的查询计划存储在 HDFS 中，并在随后由 MapReduce 调用执行。

  1. 元数据存储系统( Metastore )
    Hive 中的元数据通常包含表名、列、分区及其相关属性，表数据所在目录的位置信息， Metastore 默认存在自带的 Derby 数据库中。

由于 Derby 数据库不适合多用户操作，并且数据存储目录不固定，不方便管理，因此，通常都将元数据存储在 MySQL 数据库。

Hive的三种交互方式

1）第一种交互方式

shell交互Hive，用命令hive启动一个hive的shell命令行，在命令行中输入sql或者命令来和Hive交互。

服务端启动metastore服务（后台启动）：nohup hive --service metastore &
进入命令:hive
退出命令行：quit;

2）第二种交互方式

Hive启动为一个服务器，对外提供服务，其他机器可以通过客户端通过协议连接到服务器，来完成访问操作，这是生产环境用法最多的

服务端启动hiveserver2服务：
nohup hive --service metastore &
nohup hiveserver2 &

需要稍等一下，启动服务需要时间：
进入命令:1)先执行： beeline ，再执行： !connect jdbc:hive2://master:10000 
        2)或者直接执行：  beeline -u jdbc:hive2://master:10000 -n root
退出命令行：!exit

3）第三种交互方式

使用 –e 参数来直接执行hql的语句

bin/hive -e "show databases;"

使用 –f 参数通过指定文本文件来执行hql的语句

特点：执行完sql后，回到linux命令行。

vim hive.sql

create database bigdata30_test;
use bigdata30_test;

create table test1
(
    id bigint,
    name string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

show tables;

hive -f hive.sql

4）hive cli和beeline cli的区别

image-20220531230402802

Hive元数据

Hive元数据中一些重要的表结构及用途，方便Impala、SparkSQL、Hive等组件访问元数据库的理解。

1、存储Hive版本的元数据表(VERSION)，该表比较简单，但很重要,如果这个表出现问题，根本进不来Hive-Cli。比如该表不存在，当启动Hive-Cli的时候，就会报错“Table 'hive.version' doesn't exist”

2、Hive数据库相关的元数据表(DBS、DATABASE_PARAMS)

​ DBS：该表存储Hive中所有数据库的基本信息。

​ DATABASE_PARAMS：该表存储数据库的相关参数。

3、Hive表和视图相关的元数据表

​ 主要有TBLS、TABLE_PARAMS、TBL_PRIVS，这三张表通过TBL_ID关联。
​ TBLS:该表中存储Hive表，视图，索引表的基本信息。
​ TABLE_PARAMS:该表存储表/视图的属性信息。
​ TBL_PRIVS：该表存储表/视图的授权信息。
4、Hive文件存储信息相关的元数据表

​ 主要涉及SDS、SD_PARAMS、SERDES、SERDE_PARAMS，由于HDFS支持的文件格式很多，而建Hive表时候也可以指定各种文件格式，Hive在将HQL解析成MapReduce时候，需要知道去哪里，使用哪种格式去读写HDFS文件，而这些信息就保存在这几张表中。
​ SDS：该表保存文件存储的基本信息，如INPUT_FORMAT、OUTPUT_FORMAT、是否压缩等。TBLS表中的SD_ID与该表关联，可以获取Hive表的存储信息。
​ SD_PARAMS: 该表存储Hive存储的属性信息。
​ SERDES:该表存储序列化使用的类信息。
​ SERDE_PARAMS:该表存储序列化的一些属性、格式信息，比如:行、列分隔符。
5、Hive表字段相关的元数据表

​ 主要涉及COLUMNS_V2：该表存储表对应的字段信息。

3 Hive的基本操作

3.1 hive库操作

3.1.1 创建数据库

①创建一个数据库，数据库在HDFS上的默认存储路径是/hive/warehouse/*.db。

create database testdb;

②避免要创建的数据库已经存在错误，增加if not exists判断

create database [if not exists] testdb; 

create database if not exists testdb;

创建数据库和位置

create database if not exists bigdata30_test3 location '/bigdata30/luyunlongdb';

修改数据库

数据库的其他元数据信息都是不可更改的，包括数据库名和数据库所在的目录位置。（重点关注哪些不能改，以及为什么！！）

alter database dept set dbproperties('createtime'='20220531');

数据库详细信息

1）显示数据库（show）

show databases;

2）可以通过like进行过滤

show databases like 't*';

3）查看详情（desc）

desc database testdb;

4）切换数据库（use）

use testdb;

删除数据库（将删除的目录移动到回收站中）

1）最简写法

drop database testdb;

2）如果删除的数据库不存在，最好使用if exists判断数据库是否存在。否则会报错：FAILED: SemanticException [Error 10072]: Database does not exist: db_hive

drop database if exists testdb;

3)如果数据库不为空，使用cascade命令进行强制删除。报错信息如下FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. InvalidOperationException(message:Database db_hive is not empty. One or more tables exist.)

drop database if exists testdb cascade;

3.2 Hive数据类型

基础数据类型

类型 Java数据类型 描述
TINYINT byte 8位有符号整型。取值范围：-128~127。
SMALLINT short 16位有符号整型。取值范围：-32768~32767。
INT int 32位有符号整型。取值范围：-2 31 ~2 31 -1。
BIGINT long 64位有符号整型。取值范围：-2 63 +1~2 63 -1。
BINARY 二进制数据类型，目前长度限制为8MB。
FLOAT float 32位二进制浮点型。
DOUBLE double 64位二进制浮点型。
DECIMAL(precision,scale) 10进制精确数字类型。precision：表示最多可以表示多少位的数字。取值范围：1 <= precision <= 38。scale：表示小数部分的位数。取值范围： 0 <= scale <= 38。如果不指定以上两个参数，则默认为decimal(10,0)。
VARCHAR(n) 变长字符类型，n为长度。取值范围：1~65535。
CHAR(n) 固定长度字符类型，n为长度。最大取值255。长度不足则会填充空格，但空格不参与比较。
STRING string 字符串类型，目前长度限制为8MB。
DATE 日期类型，格式为yyyy-mm-dd。取值范围：0000-01-01~9999-12-31。
DATETIME 日期时间类型。取值范围：0000-01-01 00:00:00.000~9999-12-31 23.59:59.999，精确到毫秒。
TIMESTAMP 与时区无关的时间戳类型。取值范围：0000-01-01 00:00:00.000000000~9999-12-31 23.59:59.999999999，精确到纳秒。说明 对于部分时区相关的函数，例如cast( as string)，要求TIMESTAMP按照与当前时区相符的方式来展现。
BOOLEAN boolean BOOLEAN类型。取值：True、False。

复杂的数据类型

类型 定义方法 构造方法
ARRAY array<int>``array<struct<a:int, b:string>> array(1, 2, 3)``array(array(1, 2), array(3, 4))
MAP map<string, string>``map<smallint, array<string>> map(“k1”, “v1”, “k2”, “v2”)``map(1S, array(‘a’, ‘b’), 2S, array(‘x’, ‘y’))
STRUCT struct<x:int, y:int>struct<field1:bigint, field2:array<int>, field3:map<int, int>> named_struct(‘x’, 1, ‘y’, 2)named_struct(‘field1’, 100L, ‘field2’, array(1, 2), ‘field3’, map(1, 100, 2, 200))

Hive有三种复杂数据类型ARRAY、MAP 和 STRUCT。ARRAY和MAP与Java中的Array和Map类似，而STRUCT与C语言中的Struct类似，它封装了一个命名字段集合，复杂数据类型允许任意层次的嵌套。还有一个uniontype< 所有类型，所有类型… > 。

​ 数组：array< 所有类型 >；
​ Map < 基本数据类型，所有数据类型 >；
​ struct < 名：所有类型[注释] >;
​ uniontype< 所有类型，所有类型… >

3.3 Hive表操作

Hive的存储格式:

Hive没有专门的数据文件格式,常见的有以下几种:

​ TEXTFILE
​ SEQUENCEFILE
​ AVRO
​ RCFILE
​ ORCFILE
​ **PARQUET

TextFile:
       TEXTFILE 即正常的文本格式，是Hive默认文件存储格式，因为大多数情况下源数据文件都是以text文件格式保存（便于查看验数和防止乱码）。此种格式的表文件在HDFS上是明文，可用hadoop fs -cat命令查看，从HDFS上get下来后也可以直接读取。
        TEXTFILE 存储文件默认每一行就是一条记录，可以指定任意的分隔符进行字段间的分割。但这个格式无压缩，需要的存储空间很大。虽然可结合Gzip、Bzip2、Snappy等使用，使用这种方式，Hive不会对数据进行切分，从而无法对数据进行并行操作。
一般只有与其他系统由数据交互的接口表采用TEXTFILE 格式，其他事实表和维度表都不建议使用。

RCFile:
Record Columnar的缩写。是Hadoop中第一个列文件格式。能够很好的压缩和快速的查询性能。通常写操作比较慢，比非列形式的文件格式需要更多的内存空间和计算量。 RCFile是一种行列存储相结合的存储方式。首先，其将数据按行分块，保证同一个record在一个块上，避免读一个记录需要读取多个block。其次，块数据`列式存储`，有利于数据压缩和快速的列存取。

ORCFile:
Hive从0.11版本开始提供了ORC的文件格式，ORC文件不仅仅是一种列式文件存储格式，最重要的是有着很高的压缩比，并且对于MapReduce来说是可切分（Split）的。因此，在Hive中使用ORC作为表的文件存储格式，不仅可以很大程度的节省HDFS存储资源，而且对数据的查询和处理性能有着非常大的提升，因为ORC较其他文件格式压缩比高，查询任务的输入数据量减少，使用的Task也就减少了。ORC能很大程度的节省存储和计算资源，但它在读写时候需要消耗额外的CPU资源来压缩和解压缩，当然这部分的CPU消耗是非常少的。

Parquet:
通常我们使用关系数据库存储结构化数据，而关系数据库中使用数据模型都是扁平式的，遇到诸如数组、Map和自定义Struct的时候就需要用户在应用层解析。但是在大数据环境下，通常数据的来源是服务端的埋点数据，很可能需要把程序中的某些对象内容作为输出的一部分，而每一个对象都可能是嵌套的，所以如果能够原生的支持这种数据，这样在查询的时候就不需要额外的解析便能获得想要的结果。Parquet的灵感来自于2010年Google发表的Dremel论文，文中介绍了一种支持嵌套结构的存储格式，并且使用了列式存储的方式提升查询性能。Parquet仅仅是一种存储格式，它是语言、平台无关的，并且不需要和任何一种数据处理框架绑定。这也是parquet相较于orc的仅有优势：支持嵌套结构。Parquet 没有太多其他可圈可点的地方,比如他不支持update操作(数据写成后不可修改),不支持ACID等.

SEQUENCEFILE:
SequenceFile是Hadoop API 提供的一种二进制文件，它将数据以<key,value>的形式序列化到文件中。这种二进制文件内部使用Hadoop 的标准的Writable 接口实现序列化和反序列化。它与Hadoop API中的MapFile 是互相兼容的。Hive 中的SequenceFile 继承自Hadoop API 的SequenceFile，不过它的key为空，使用value 存放实际的值， 这样是为了避免MR 在运行map 阶段的排序过程。SequenceFile支持三种压缩选择：NONE, RECORD, BLOCK。 Record压缩率低，一般建议使用BLOCK压缩。 SequenceFile最重要的优点就是Hadoop原生支持较好，有API，但除此之外平平无奇，实际生产中不会使用。

AVRO:
Avro是一种用于支持数据密集型的二进制文件格式。它的文件格式更为紧凑，若要读取大量数据时，Avro能够提供更好的序列化和反序列化性能。并且Avro数据文件天生是带Schema定义的，所以它不需要开发者在API 级别实现自己的Writable对象。Avro提供的机制使动态语言可以方便地处理Avro数据。最近多个Hadoop 子项目都支持Avro 数据格式，如Pig 、Hive、Flume、Sqoop和Hcatalog。

Hive的四大常用存储格式存储效率及执行速度对比

image-20220531234505119

image-20220531234553385

结论：ORCFILE存储文件读操作效率最高

耗时比较：ORC<Parquet<RC<Text

image-20220531234659264

结论：ORCFILE存储文件占用空间少，压缩效率高

占用空间：ORC<Parquet<RC<Text

创建表

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]




字段解释说明:
- CREATE TABLE 
	创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项来忽略这个异常。

- EXTERNAL
	关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION）
	创建内部表时，会将数据移动到数据仓库指向的路径（默认位置）；
	创建外部表时，仅记录数据所在的路径，不对数据的位置做任何改变。在
	删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。

- COMMENT：
	为表和列添加注释。

- PARTITIONED BY
	创建分区表

- CLUSTERED BY
	创建分桶表

- SORTED BY
	不常用

- ROW FORMAT 
  DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char] [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char] | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]
	用户在建表的时候可以自定义SerDe或者使用自带的SerDe。
	如果没有指定ROW FORMAT 或者ROW FORMAT DELIMITED，将会使用自带的SerDe。
	在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的SerDe，Hive通过SerDe确定表的具体的列的数据。
	SerDe是Serialize/Deserilize的简称，目的是用于序列化和反序列化。

- STORED AS指定存储文件类型
	常用的存储文件类型：SEQUENCEFILE（二进制序列文件）、TEXTFILE（文本）、RCFILE（列式存储格式文件）
	如果文件数据是纯文本，可以使用STORED AS TEXTFILE。
	如果数据需要压缩，使用 STORED AS SEQUENCEFILE。

- LOCATION ：
	指定表在HDFS上的存储位置。
	需要搞明白映射的关系

- LIKE
	允许用户复制现有的表结构，但是不复制数据。
建表1：全部使用默认建表方式
create table students
(
    id bigint, 
    name string,
    age int,
    gender string,
    clazz string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','; // 必选，指定列分隔符
建表2：指定location （这种方式也比较常用）
create table IF NOT EXISTS students2
(
    id bigint,
    name string,
    age int,
    gender string,
    clazz string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION '/bigdata27/input1'; // 指定Hive表的数据的存储位置，一般在数据已经上传到HDFS，想要直接使用，会指定Location，通常Locaion会跟外部表一起使用，内部表一般使用默认的location


create table IF NOT EXISTS person_avg_counts
(
	name string,
	avg_count bigint
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/bigdata29/out4';
建表3：指定存储格式
create table IF NOT EXISTS test_orc_tb
(
    id bigint,
    name string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS ORC
LOCATION '/bigdata29/out6'; // 指定储存格式为orcfile，inputFormat:RCFileInputFormat,outputFormat:RCFileOutputFormat，如果不指定，默认为textfile，注意：除textfile以外，其他的存储格式的数据都不能直接加载，需要使用从表加载的方式。
建表4：create table xxxx as select_statement(SQL语句) (这种方式比较常用)
create table IF NOT EXISTS bigdata29.students
(id bigint,name string,age int,gender string,clazz string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

create table IF NOT EXISTS bigdata29.xuqiu2(clazz string,number bigint)ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

insert into students3_1 values(1002,'吴小康',19,'男','27期特训营');

create table students4 as select * from students2;
建表5：create table xxxx like table_name 只想建表，不需要加载数据
create table students5 like students;

简单用户信息表创建：

create table t_user(
id int,
uname string,
pwd string,
gender string,
age int
)
row format delimited fields terminated by ','
lines terminated by '\n';

1,admin,123456,男,18
2,zhangsan,abc123,男,23
3,lisi,654321,女,16

复杂人员信息表创建：

create table IF NOT EXISTS t_person(
name string,
friends array<string>,
children map<string,int>,
address struct<street:string ,city:string>
)
row format delimited fields terminated by ',' 
collection items terminated by '_'
map keys terminated by ':' 
lines terminated by '\n';  -- 行与行之间的换行符

songsong,bingbing_lili,xiao song:18_xiaoxiao song:19,beng bu_anhui
yangyang,caicai_susu,xiao yang:18_xiaoxiao yang:19,he fei_anhui

显示表

show tables;
show tables like 'u*';
desc t_person;
desc formatted students; // 更加详细

加载数据

1、使用hdfs dfs -put '本地数据' 'hive表对应的HDFS目录下'
2、使用 load data

下列命令需要在hive shell里执行

create table IF NOT EXISTS students3
(
    id bigint,
    name string,
    age int,
    gender string,
    clazz string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

// 将HDFS上的/input1目录下面的数据 移动至 students表对应的HDFS目录下，注意是 移动、移动、移动
load data inpath '/input1/students.txt' into table students;

// 清空表
truncate table students;
// 加上 local 关键字 可以将Linux本地目录下的文件 上传到 hive表对应HDFS 目录下 原文件不会被删除
load data local inpath '/usr/local/soft/data/students.txt' into table students;
// overwrite 覆盖加载
load data local inpath '/usr/local/soft/data/students.txt' overwrite into table students;
3、create table xxx as SQL语句
4、insert into table xxxx SQL语句 （没有as）
// 将 students表的数据插入到students2 这是复制 不是移动 students表中的表中的数据不会丢失
insert into table students2 select * from students;

// 覆盖插入 把into 换成 overwrite
insert overwrite table students2 select * from students;

修改列

查询表结构

desc students2;

添加列

alter table students2 add columns (education string);

查询表结构

desc students2;

更新列

alter table stduents2 change education educationnew string;

删除表

drop table students2;

3.4 Hive内外部表

3.4.1 hive内部表

当创建好表的时候，HDFS会在当前表所属的库中创建一个文件夹

当设置表路径的时候，如果直接指向一个已有的路径,可以直接去使用文件夹中的数据

当load数据的时候，就会将数据文件存放到表对应的文件夹中

而且数据一旦被load，就不能被修改

我们查询数据也是查询文件中的文件,这些数据最终都会存放到HDFS

当我们删除表的时候，表对应的文件夹会被删除，同时数据也会被删除

默认建表的类型就是内部表

// 内部表
create table students_internal
(
    id bigint,
    name string,
    age int,
    gender string,
    clazz string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION '/input2';

hive> dfs -put /usr/local/soft/data/students.txt /input2/;

3.4.2 hive外部表

外部表说明

​ 外部表因为是指定其他的hdfs路径的数据加载到表中来，所以hive会认为自己不完全独占这份数据

​ **删除hive表的时候，数据仍然保存在hdfs中，不会删除。

// 外部表
create external table students_external
(
    id bigint,
    name string,
    age int,
    gender string,
    clazz string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION '/input3';

hive> dfs -put /usr/local/soft/data/students.txt /input3/;

测试：当我们删除掉表students_external后，在数据库中会没有此表，mysql数据库表中也没有，但在hdfs中数据仍会存在

一般在公司中，使用外部表多一点，因为数据可以需要被多个程序使用，避免误删，通常外部表会结合location一起使用

外部表还可以将其他数据源中的数据 映射到 hive中，比如说：hbase，ElasticSearch......

设计外部表的初衷就是 让 表的元数据 与 数据 解耦

3.5 hive导出数据

将表中的数据备份

  • 将查询结果存放到本地
//创建存放数据的目录
mkdir -p /usr/local/soft/shujia

//导出查询结果的数据(导出到Node01上)
insert overwrite local directory '/usr/local/soft/shujia/person_data' select * from t_person;
  • 按照指定的方式将数据输出到本地
-- 创建存放数据的目录
mkdir -p /usr/local/soft/shujia

-- 导出查询结果的数据
insert overwrite local directory '/usr/local/soft/shujia/person' 
ROW FORMAT DELIMITED fields terminated by ',' 
collection items terminated by '-' 
map keys terminated by ':' 
lines terminated by '\n' 
select * from t_person;
  • 将查询结果输出到HDFS
-- 创建存放数据的目录
hdfs dfs -mkdir -p /shujia/bigdata17/copy

-- 导出查询结果的数据
insert overwrite local directory '/usr/local/soft/shujia/students_data2' ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' select * from students
  • 直接使用HDFS命令保存表对应的文件夹
// 创建存放数据的目录
hdfs dfs -mkdir -p /shujia/bigdata17/person

// 使用HDFS命令拷贝文件到其他目录
hdfs dfs -cp /hive/warehouse/t_person/*  /shujia/bigdata17/person

  • 将表结构和数据同时备份

    将数据导出到HDFS

    //创建存放数据的目录
hdfs dfs -mkdir -p /shujia/bigdata17/copy

//导出查询结果的数据
export table t_person to '/shujia/bigdata17/copy';

    删除表结构

    drop table t_person;

    恢复表结构和数据

    import from '/shujia/bigdata17';

    注意：时间不同步，会导致导入导出失败

4 Hive的分区与排序

1 Hive分区概述

分区目的：避免全表扫描，加快查询速度

在大数据中，最常见的一种思想就是分治，我们可以把大的文件切割划分成一个个的小的文件，这样每次操作一个个小的文件就会很容易了，同样的道理，在hive当中也是支持这种思想的，就是我们可以把大的数据，按照每天或者每小时切分成一个个小的文件，这样去操作小的文件就会容易很多了。

假如现在我们公司一天产生3亿的数据量，那么为了方便管理和查询，就做以下的事情。

​ 1）建立分区（可按照日期，部门等等具体业务分区）

​ 2）分门别类的管理

分区的分类.png

静态分区（SP）

静态分区（SP）static partition–partition by (字段 类型)

​ 借助于物理的文件夹分区，实现快速检索的目的。

​ 一般对于查询比较频繁的列设置为分区列。

​ 分区查询的时候直接把对应分区中所有数据放到对应的文件夹中。

单分区表

具体的操作步骤

CREATE TABLE IF NOT EXISTS t_student (
sno int,
sname string
) partitioned by(grade int)
row format delimited fields terminated by ',';
--  分区的字段不要和表的字段相同。相同会报错error10035


1,xiaohu01,1
2,xiaohu02,1
3,xiaohu03,1
4,xiaohu04,1
5,xiaohu05,1

6,xiaohu06,2
7,xiaohu07,2
8,xiaohu08,2

9,xiaohu09,3
10,xiaohu10,3
11,xiaohu11,3
12,xiaohu12,3
13,xiaohu13,3
14,xiaohu14,3
15,xiaohu15,3

16,xiaohu16,4
17,xiaohu17,4
18,xiaohu18,4
19,xiaohu19,4
20,xiaohu20,4
21,xiaohu21,4
-- 载入数据
-- 将相应年级一次导入
load data local inpath '/usr/local/soft/bigdata29/grade2.txt' into table t_student partition(grade=2);

-- 演示多拷贝一行上传，分区的列的值是分区的值，不是原来的值

多分区表

CREATE TABLE IF NOT EXISTS t_teacher (
tno int,
tname string
) partitioned by(grade int,clazz int)
row format delimited fields terminated by ',';

--注意：前后两个分区的关系为父子关系，也就是grade文件夹下面有多个clazz子文件夹。
1,xiaoge01,1,1
2,xiaoge02,1,1

3,xiaoge03,1,2
4,xiaoge04,1,2

5,xiaoge05,1,3
6,xiaoge06,1,3

7,xiaoge07,2,1
8,xiaoge08,2,1

9,xiaoge09,2,2

--载入数据
load data local inpath '/usr/local/soft/bigdata19/hivedata/teacher_1.txt' into table t_teacher partition(grade=1,clazz=1);

在进行分区后，我们应该怎么查询这些数据呢?

select * from t_student where grade = 1;

// 全表扫描，不推荐，效率低
select count(*) from students_pt1;

// 使用where条件进行分区裁剪，避免了全表扫描，效率高
select count(*) from students_pt1 where grade = 1;

// 也可以在where条件中使用非等值判断
select count(*) from students_pt1 where grade<3 and grade>=1;

查看分区

show partitions t_teacher;

添加分区

alter table t_student add partition (grade=6);

alter table t_teacher add partition (grade=3,clazz=1) location '/user/hive/warehouse/bigdata29.db/t_teacher/grade=3/clazz=1';

删除分区

alter table t_student drop partition (grade=5);

动态分区

  • 动态分区（DP）dynamic partition
  • 静态分区与动态分区的主要区别在于静态分区是手动指定，而动态分区是通过数据来进行判断。
  • 详细来说，静态分区的列是在编译时期通过用户传递来决定的；动态分区只有在SQL执行时才能决定。

在进行动态分区之前，我们应该进行相应的参数设置

# 表示开启动态分区
hive> set hive.exec.dynamic.partition=true;

# 表示动态分区模式：strict（需要配合静态分区一起使用）、nostrict
# strict： insert into table students_pt partition(dt='anhui',pt) select ......,pt from students;
hive> set hive.exec.dynamic.partition.mode=nonstrict;

===================以下是可选参数======================

# 表示支持的最大的分区数量为1000，可以根据业务自己调整
hive> set hive.exec.max.dynamic.partitions.pernode=1000;

其余的一些配置

设置为true表示开启动态分区的功能（默认为false）
--hive.exec.dynamic.partition=true;

设置为nonstrict，表示允许所有分区都是动态的（默认为strict）
-- hive.exec.dynamic.partition.mode=nonstrict; 
-- hive.exec.dynamic.partition.mode=strict; 

每个mapper或reducer可以创建的最大动态分区个数(默认为100) 
比如：源数据中包含了一年的数据，即day字段有365个值，那么该参数就需要设置成大于365，如果使用默认值100，则会报错
--hive.exec.max.dynamic.partition.pernode=100; 

一个动态分区创建可以创建的最大动态分区个数（默认值1000）
--hive.exec.max.dynamic.partitions=1000;

全局可以创建的最大文件个数（默认值100000）
--hive.exec.max.created.files=100000; 

当有空分区产生时，是否抛出异常（默认false） 
-- hive.error.on.empty.partition=false;

动态分区的实现

--创建外部表
CREATE EXTERNAL TABLE IF NOT EXISTS - (
sno int,
sname string,
grade int,
clazz int
) 
row format delimited fields terminated by ','
location "/bigdata30/teachs";

--创建分区表
CREATE TABLE IF NOT EXISTS t_student_d (
sno int,
sname string
) partitioned by (grade int,clazz int)
row format delimited fields terminated by ',';

数据

1,xiaohu01,1,1
2,xiaohu02,1,1
3,xiaohu03,1,1
4,xiaohu04,1,2
5,xiaohu05,1,2
6,xiaohu06,2,3
7,xiaohu07,2,3
8,xiaohu08,2,3
9,xiaohu09,3,3
10,xiaohu10,3,3
11,xiaohu11,3,3
12,xiaohu12,3,4
13,xiaohu13,3,4
14,xiaohu14,3,4
15,xiaohu15,3,4
16,xiaohu16,4,4
17,xiaohu17,4,4
18,xiaohu18,4,5
19,xiaohu19,4,5
20,xiaohu20,4,5
21,xiaohu21,4,5

将数据先传入 表t_student_d中

再利用传入表 t_student_e中

insert overwrite table t_student_d partition (grade,clazz) select * from t_student_e;

动态分区的优缺点

优点：不用手动指定了，自动会对数据进行分区

缺点：可能会出现数据倾斜(可能1年纪1班只有一个学生，而1年纪2班有八百万学生)

5 Hive分桶

数据分桶的适用场景：
分区提供了一个隔离数据和优化查询的便利方式，不过并非所有的数据都可形成合理的分区，尤其是需要确定合适大小的分区划分方式
不合理的数据分区划分方式可能导致有的分区数据过多，而某些分区没有什么数据的尴尬情况
分桶是将数据集分解为更容易管理的若干部分的另一种技术。
分桶就是将数据按照字段进行划分，可以将数据按照字段划分到多个文件当中去。(都各不相同)

2.2 数据分桶原理

  • Hive采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。
    • bucket num = hash_function(bucketing_column) mod num_buckets ( hash(name)%n == x )
    • 列的值做哈希取余 决定数据应该存储到哪个桶

2.3 数据分桶优势

方便抽样

​ 使取样（sampling）更高效。在处理大规模数据集时，在开发和修改查询的阶段，如果能在数据集的一小部分数据上试运行查询，会带来很多方便

提高join查询效率

​ 获得更高的查询处理效率。桶为表加上了额外的结构，Hive 在处理有些查询时能利用这个结构。具体而言，连接两个在（包含连接列的）相同列上划分了桶的表，可以使用 Map 端连接 （Map-side join）高效的实现。比如JOIN操作。对于JOIN操作两个表有一个相同的列，如果对这两个表都进行了桶操作。那么将保存相同列值的桶进行JOIN操作就可以，可以大大较少JOIN的数据量。

2.4 分桶实战

​ 首先，分区和分桶是两个不同的概念，很多资料上说需要先分区在分桶，其实不然，分区是对数据进行划分，而分桶是对文件进行划分。

​ 当我们的分区之后，最后的文件还是很大怎么办，就引入了分桶的概念。

将这个比较大的文件再分成若干个小文件进行存储，我们再去查询的时候，在这个小范围的文件中查询就会快很多。

​ 对于hive中的每一张表、分区都可以进一步的进行分桶。

​ 当然，分桶不是说将文件随机进行切分存储，而是有规律的进行存储。在看完下面的例子后进行解释，现在干巴巴的解释也不太好理解。它是由列的哈希值除以桶的个数来决定每条数据划分在哪个桶中。

创建顺序和分区一样，创建的方式不一样。

# 分区和分桶的区别
1、在HDFS上的效果区别，分区产生的是一个一个子文件夹，分桶产生的是一个一个文件

2、无论是分区还是分桶，在建表的时候都要指定字段，分区使用partitioned by指定分区字段，分桶使用clustered by指定分桶字段

3、partitioned by指定分区字段的时候，字段后面需要加上类型，而且不能在建表小括号中出现。clustered by指定分桶字段的时候，字段已经出现定义过了，只需要指定字段的名字即可。

4、分区字段最好选择固定类别的，分桶字段最好选择值各不相同的。

5、分桶不是必须要建立在分区之上，可以不进行分区直接分桶。

这是分桶后hdfs的存储形式

image-20240605144305310.png

这是分区后hdfs的存储形式

image-20240605144520235.png

首先我们需要开启分桶的支持

（依然十分重要，不然无法进行分桶操作！！！！）
set hive.enforce.bucketing=true;

数据准备（id,name,age）

1,tom,11
2,cat,22
3,dog,33
4,hive,44
5,hbase,55
6,mr,66
7,alice,77
8,scala,88

创建一个普通的表

create table person
(
id int,
name string,
age int
)
row format delimited
fields terminated by ',';

将数据load到这张表中

load data local inpath '文件在Linux上的绝对路径' into table psn31;

创建分桶表

create table person_bucket
(
id int,
name string,
age int
)
clustered by(age) into 4 buckets
row format delimited fields terminated by ',';

将数据insert到表psn_bucket中

(注意：这里和分区表插入数据有所区别，分区表需要select 和指定分区，而分桶则不需要)

insert into psn_bucket select * from person;

在HDFS上查看数据

image-20220601223434297

查询数据

我们在linux中使用Hadoop的命令查看一下（与我们猜想的顺序一致）

hadoop fs -cat /user/hive/warehouse/bigdata17.db/psn_bucket/*

这里设置的桶的个数是4 数据按照 年龄%4 进行放桶(文件)
11%4 == 3 -----> 000003_0
22%4 == 2 -----> 000002_0
33%4 == 1 -----> 000001_0
44%4 == 0 -----> 000000_0
...以此类推

6 Hive JDBC

首先要确保主服务器节点启动hiveserver2

nohup hiveserver2 &
或者
hiveserver2 &

代码如下：

package com.shujia;

import java.sql.*;

public class HiveJDBC {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("org.apache.hive.jdbc.HiveDriver");
        Connection connection = DriverManager.getConnection("jdbc:hive2://master:10000/bigdata30");
        Statement stat = connection.createStatement();
        ResultSet rs = stat.executeQuery("select * from students limit 10");
        while (rs.next()) {
            int id = rs.getInt(1);
            String name = rs.getString(2);
            int age = rs.getInt(3);
            String gender = rs.getString(4);
            String clazz = rs.getString(5);
            System.out.println(id + "," + name + "," + age + "," + gender + "," + clazz);
        }
        rs.close();
        stat.close();
        connection.close();
    }
}
posted @ 2024-06-05 16:52  Bigdata_Yuan  阅读(39)  评论(0)    收藏  举报