HIVE的操作

小植2023-10-302023-11-11

实验一：创建表

一、实验目的

1、理解Hive的实验原理

2、熟悉创建Hive表的基础语法

3、掌握Hive的三种方式创建日志表

4、完成相关实验任务

二、实验内容

1、启动Hive shell端

2、使用Hive的三种方式创建日志表

3、创建员工表和部门表

三、准备知识

Hive实验原理

Hive没有专门的数据存储格式，也没有为数据建立索引，用户可以非常自由的组织 Hive中的表，只需要在创建表的时候告诉Hive数据中的列分隔符和行分隔符，Hive就可以解析数据。

Hive中所有的数据都存储在HDFS中，Hive中包含以下数据模型：表(Table)，外部表(ExternaTable)，分区(Partition)，桶(Bucket)。

Hive中Table和数据库中 Table在概念上是类似的，每一个Table在Hive中都有一个相应的目录存储数据。例如，一个表 pvs，它在 HDFS 中的路径为：/wh/pvs，其中，wh是在hive-site.xml中由${hive.metastore.warehouse.dir}指定的数据仓库的目录，所有的 Table数据（不包括 ExternaTable）都保存在这个目录中。

创建Hive表的三种方式？

1. 创建基础表

指定表的类型和表的名称

1	CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name

指定每列的名称和类型

1	[(col_name data_type [COMMENT col_comment], ...)]

对表的说明

1	[COMMENT table_comment]

指定分区表的列名称，列类型

1	[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]

指定每列的分隔符、数据类型的类型

[

 [ROW FORMAT row_format] 

 [STORED AS file_format]

  | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)] 

]

指定数据文件存储在HDFS的什么位置

1	[LOCATION hdfs_path]

表的属性设置

1	[TBLPROPERTIES (property_name=property_value, ...)]

子查询

1	[AS select_statement];

hive中的表需要映射到hdfs文件中，所以需要定义文件中的每一行之间的分隔符，每一列之间的分隔符。增加行分隔符的语句如下：

1	ROW FORMAT DELIMITED FIELDS TERMINATED BY ' '

这句的意思是以空格来分隔行数据，那么这一行中的数据只要遇到一个空格就可以划分为一个数据。这里的分隔符可以是其他字符，比如",","#","|"等，一般只要用数据文件中可以区分每一行中的不同数据即可。

列与列直接的分隔符通常是以换行符来区分，可以用如下的语句来指定：

1	`COLLECTION ITEMS TERMINATED BY '\n'

通常列与列直接的分隔符是不需要写的。

[STORED AS file_format]是指定文件的类型，保存在hive中的文件的类型有多种，一般简单就保存为文本格式，即TEXTFILE，但是企业中一般不使用这种格式来保存数据，主要是因为文本格式占的空间比较大，不利于大数据分析。企业中一般使用ORC和PARQUET两种文件类型来保存，具体的会在后面讲解。

通过[LOCATION hdfs_path]可以在创建表的时候指定该表映射到到hdfs的文件路径，默认是映射到/user/hive/warehouse目录下。

2. 创建子表

[AS select_statement]这个语句是用来通过查询已有的表来创建一张新表，这样可以根据已有的表来创建子表，对于数据分析和优化都是有很大的好处的。

这种方式是把一张表的某些字段抽取出来，创建成一张新表。

这种方式需要注意以下几点：

as只会复制属性以及属性值到新的表中

使用as创建的表，并不会带原表的分区（分区丢失），并且分区的字段变成一个正常的字段，没有分区的效用，还有一些字段的约束等（可以通过describe formatted查看）

3. 创建相同结构的表

这种方式实质就是复制表结构。在工作中，如果你有一个已经存在的表，现在想创建一个一模一样的表，这个时候这个方法就很省时省力了。复制时，只会复制表结构，不会复制表中属性值（包括表的分区以及存储格式之类的）

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name

 ``LIKE existing_table_or_view_name

 ``[LOCATION hdfs_path]

四、实验步骤

演练Hive创建表的三种方式

创建基础表：以常用的日志为例来创建page_view表

CREATE TABLE IF NOT EXISTS default.page_view(
viewTime INT, 
userid BIGINT,
page_urSTRING,
referrer_urSTRING,
ip STRING COMMENT 'IP Address of the User')
COMMENT 'This is the page view table'
PARTITIONED BY(dt STRING, country STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ' '
STORED AS SEQUENCEFILE;

创建子表：创建page_view_sl表如下

1	create table IF NOT EXISTS default.page_view_sas select viewTime,userid,ip from default.page_view;

创建相同结构的表：创建default.page_view_s2

1	create table page_view_s2 like page_view;

对Hive表的基础操作

显示Hive中的所有表

1	show tables;

显示表（正则查询），同MySQL中操作一样，Hive也支持正则查询，比如显示以sl结尾的表。

1	DESC page_view;

查看表详细属性

1	desc formatted page_view;

退出

quit;

实验二：分区操作

一、实验目的

1、理解Hive分区的作用

2、理解Hive分区的技术

3、掌握Hive的分区的两种方式

4、掌握Hive分区的实例操作

5、完成相关实验任务

二、实验内容

1、启动Hive shell端

2、数据准备

3、Hive分区对表初始化

4、查询分区目录

5、完成实验任务

三、准备知识

（一）为什么有分区？

随着系统运行时间增长，表的数据量越来越大，而hive查询时通常是是全表扫描，这样将导致大量的不必要的数据扫描，从而大大减低查询效率。

可以将用户的整个表的数据在存储时划分到多个子目录，从而在查询时可以指定查询条件（子目录以分区变量的值来命名）

（二）怎么分区？

根据业务，通常按照年、月、日、地区等进行分区。

（三）分区的技术？

PARTIONED BY(col_name data_type)

hive的分区字段使用的是表外字段。而mysql使用的是表内字段。

hive的分区名区分大小写
hive的分区本质是在表目录下面创建目录，但是该分区字段是一个伪列，不真实存在于数据中
一张表可以有一个或者多个分区，分区下面也可以有一个或者多个分区

（四）分区注意事项

在hive中的数据是存储在hdfs中的，我们知道hdfs中的数据是不允许修改只能追加的，那么在hive中执行数据修改的命令时，就只能先找到对应的文件，读取后执行修改操作，然后重新写一份文件。如果文件比较大，就需要大量的IO读写。在hive中采用了分桶的策略，只需要找到文件存放对应的桶，然后读取再修改写入即可。

（五）分区的两种方式

hive中分区分为：单值分区、范围分区。

单值分区：静态分区动态分区

单值静态分区：导入数据时需要手动指定分区

单值动态分区：导入数据时，系统可以动态判断目标分区

如下所示，现在有一张persionrank表，记录每个人的评级，有id、name、score字段。我们可以创建分区rank（rank不是表中的列，我们可以把它当做虚拟列），并将相应的数据导入指定分区（将数据插入指定目录）。
hive中分区分为：单值分区、范围分区。

单值分区：静态分区动态分区

如下所示，现在有一张persionrank表，记录每个人的评级，有id、name、score字段。我们可以创建分区rank（rank不是表中的列，我们可以把它当做虚拟列），并将相应的数据导入指定分区（将数据插入指定目录）。

（六）静态分区常用语句

静态分区创建：

– 直接在PARTITI1ONED BY后面跟上分区键、类型即可（指定的分区键不能出现在定义列名中）

CREATE [EXTERNAL] TABLE <table_name>

  (<col_name> <data_type> [, <col_name> <data_type> ...])

  -- 指定分区键和数据类型

  PARTITIONED BY (<partition_key> <data_type>, ...) 

  [CLUSTERED BY ...] 

  [ROW FORMAT <row_format>] 

  [STORED AS TEXTFILE|ORC|CSVFILE]

  [LOCATION '<file_path>']  

  [TBLPROPERTIES ('<property_name>'='<property_value>', ...)];

静态分区写入：

– 覆盖写入

INSERT OVERWRITE TABLE <table_name> 

  PARTITION (<partition_key>=<partition_value>[, <partition_key>=<partition_value>, ...]) 

  SELECT <select_statement>;

– 追加写入

INSERT INTO TABLE <table_name> 

  PARTITION (<partition_key>=<partition_value>[, <partition_key>=<partition_value>, ...])

  SELECT <select_statement>;

添加分区：

– 只能添加分区列的值，不能添加分区列，如果是多个分区列，不能单独添加其中一个

1	alter table tablename add partition(col=value)

删除分区：

– 可以删除一个分区列，但是会把表中所有包含当前分区列的数据全部删除

1	alter table tablename drop partition(col=value)

修复分区：

– 手动向hdfs中创建分区目录，添加数据，创建好hive的外表之后，无法加载数据，
– 元数据中没有相应的记录

1	msck repair table tablename

（七）动态分区常用语句

1.动态分区创建

– 创建方式与静态分区表完全一样，一张表可同时被静态分区和动态分区键分区，只是动态分区键需要放在静态分区键的后面（HDFS上的动态分区目录下不能包含静态分区的子目录），如下spk即static partition key（静态分区键），dpk为dynamic partition key（动态分区键）

CREATE TABLE <table_name>

 PARTITIONED BY ([<spk> <data_type>, ... ,] <dpk> <data_type>, [<dpk>

<data_type>,...]);

2.动态分区写入

根据表中的某一个列值来确定hdfs存储的目录：

优点：动态可变，不需要人为控制。假如设定的是日期，那么每一天的数据会单独存储在一个文件夹中
缺点：

需要依靠MR完成，执行比较慢

静态分区键要用 = 指定分区值；动态分区只需要给出分出分区键名称。

开启动态分区支持，并设置最大分区数

set hive.exec.dynamic.partition=true;

//set hive.exec.dynamic.partition.mode=nostrict;

set hive.exec.max.dynamic.partitions=2000;

insert into table1 select 普通字段 分区字段 from table2

（八）范围分区常用语句

单值分区每个分区对应于分区键的一个取值，而每个范围分区则对应分区键的一个区间，只要落在指定区间内的记录都被存储在对应的分区下。分区范围需要手动指定，分区的范围为前闭后开区间 [最小值, 最大值)。最后出现的分区可以使用 MAXVALUE 作为上限，MAXVALUE 代表该分区键的数据类型所允许的最大值。

CREATE [EXTERNAL] TABLE <table_name>

  (<col_name> <data_type>, <col_name> <data_type>, ...)

  PARTITIONED BY RANGE (<partition_key> <data_type>, ...) 

    (PARTITION [<partition_name>] VALUES LESS THAN (<cutoff>), 

      [PARTITION [<partition_name>] VALUES LESS THAN (<cutoff>),

       ...

      ]

      PARTITION [<partition_name>] VALUES LESS THAN (<cutoff>|MAXVALUE) 

    )

`  `[ROW FORMAT <row_format>] [STORED AS TEXTFILE|ORC|CSVFILE]

  [LOCATION '<file_path>']  

  [TBLPROPERTIES ('<property_name>'='<property_value>', ...)];

多个范围分区键的情况：

DROP TABLE IF EXISTS test_demo;

CREATE TABLE test_demo (value INT)

PARTITIONED BY RANGE (id1 INT, id2 INT, id3 INT)

(

-- id1在(--∞,5]之间，id2在(-∞,105]之间，id3在(-∞,205]之间

PARTITION p5_105_205 VALUES LESS THAN (5, 105, 205),

-- id1在(--∞,5]之间，id2在(-∞,105]之间，id3在(205,215]之间

PARTITION p5_105_215 VALUES LESS THAN (5, 105, 215),

PARTITION p5_115_max VALUES LESS THAN (5, 115, MAXVALUE),

PARTITION p10_115_205 VALUES LESS THAN (10, 115, 205),

PARTITION p10_115_215 VALUES LESS THAN (10, 115, 215),

PARTITION pall_max values less than (MAXVALUE, MAXVALUE, MAXVALUE)

);

四、实验步骤

数据准备

创建数据存放路径

1	mkdir /usr/local/software/hive-3.1.2/data

1	cd /usr/local/software/hive-3.1.2/data

创建partition.txt文件

1	vi partition.txt

将下述内容添加到文件中

2018    ddd
2018    eee
2018    fff
2018    ggg