一、启动beeline
-
启动metastore [hadoop@master ~]$ hive --service metastore&
-
启动hiveserver2 [hadoop@master ~]$ hive --service hiveserver2&
-
启动beeline [hadoop@master hive]$ cd bin
[hadoop@master bin]$ ll
total 48
-rwxr-xr-x 1 hadoop hadoop 881 Aug 23 2019 beeline
-rwxr-xr-x 1 hadoop hadoop 252 Jul 13 13:10 beeline.exp
drwxrwxr-x 3 hadoop hadoop 4096 Jul 13 10:47 ext
-rwxr-xr-x 1 hadoop hadoop 10158 Aug 23 2019 hive
-rwxr-xr-x 1 hadoop hadoop 1900 Aug 23 2019 hive-config.sh
-rwxr-xr-x 1 hadoop hadoop 885 Aug 23 2019 hiveserver2
-rwxr-xr-x 1 hadoop hadoop 880 Aug 23 2019 hplsql
-rwxr-xr-x 1 hadoop hadoop 3064 Aug 23 2019 init-hive-dfs.sh
-rwxr-xr-x 1 hadoop hadoop 832 Aug 23 2019 metatool
-rwxr-xr-x 1 hadoop hadoop 884 Aug 23 2019 schematool
[hadoop@master bin]$ expect beeline.exp
spawn beeline
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/opt/hive/lib/log4j-slf4j-impl-2.10.0.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/opt/hadoop/share/hadoop/common/lib/slf4j-log4j12-1.7.30.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html
SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/opt/hive/lib/log4j-slf4j-impl-2.10.0.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/opt/hadoop/share/hadoop/common/lib/slf4j-log4j12-1.7.30.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html
SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
Beeline version 3.1.2 by Apache Hive
beeline> !connect jdbc:hive2://master:10000
Connecting to jdbc:hive2://master:10000
Enter username for jdbc:hive2://master:10000: hive
Enter password for jdbc:hive2://master:10000: ****
Connected to: Apache Hive (version 3.1.2)
Driver: Hive JDBC (version 3.1.2)
Transaction isolation: TRANSACTION_REPEATABLE_READ
0: jdbc:hive2://master:10000>
二、数据库操作
查看数据库
0: jdbc:hive2:
INFO : Compiling command(queryId=hadoop_20210720100303_fc55bfb3-42b5-4b7d-ae49-b6c448642077): show databases
INFO : OK
INFO : Concurrency mode is disabled, not creating a lock manager
+
| database_name |
+
| default | |
+
3 rows selected (1.938 seconds)
0: jdbc:hive2:
创建数据库
0: jdbc:hive2:
INFO : Compiling command(queryId=hadoop_20210719164318_a71ee0eb-6631-4948-a86f-bd2fbc131c40): create database myhive
INFO : Concurrency mode is disabled, not creating a lock manager
INFO : Semantic Analysis Completed (retrial = false)
INFO : Returning Hive schema: Schema(fieldSchemas:null, properties:null)
INFO : Completed compiling command(queryId=hadoop_20210719164318_a71ee0eb-6631-4948-a86f-bd2fbc131c40); Time taken: 0.038 seconds
INFO : Concurrency mode is disabled, not creating a lock manager
INFO : Executing command(queryId=hadoop_20210719164318_a71ee0eb-6631-4948-a86f-bd2fbc131c40): create database myhive
INFO : Starting task [Stage-0:DDL] in serial mode
OK
INFO : Completed executing command(queryId=hadoop_20210719164318_a71ee0eb-6631-4948-a86f-bd2fbc131c40); Time taken: 1.604 seconds
INFO : OK
INFO : Concurrency mode is disabled, not creating a lock manager
No rows affected (1.657 seconds)
查看新建myhive信息
0: jdbc:hive2:
删除数据库myhive
0: jdbc:hive2:
三、内部表操作
未被external修饰的是内部表(managed table),内部表又称管理表,内部表数据存储的位置由hive.metastore.warehouse.dir参数决定(默认:/user/hive/warehouse),删除内部表会直接删除元数据(metadata)及存储数据,因此内部表不适合和其他工具共享数据。
3.1 hive创建内部表
- 创建表stu,并插入数据
0: jdbc:hive2:
0: jdbc:hive2:
0: jdbc:hive2:
0: jdbc:hive2:
INFO : OK
INFO : Concurrency mode is disabled, not creating a lock manager
+
| stu.id | stu.name |
+
| 10001 | Eric |
+
1 row selected (3.902 seconds)
- 创建表并指定分隔符
0: jdbc:hive2:
- 根据查询结构创建表
0: jdbc:hive2:
- 查询表类型
0: jdbc:hive2:
3.2 hive删除内部表
0: jdbc:hive2:
查看数据库和HDFS,发现删除内部表之后,所有的内容全部删除
四、外部表操作
在创建表的时候可以指定external关键字创建外部表,外部表对应的文件存储在location指定的hdfs目录下,向该目录添加新文件的同时,该表也会读取到该文件(当然文件格式必须跟表定义的一致)。
外部表因为是指定其他的hdfs路径的数据加载到表当中来,所以hive表会认为自己不完全独占这份数据,所以删除hive外部表的时候,数据仍然存放在hdfs当中,不会删掉。
4.1 hive创建外部表
语法:
create external table table_name(column1 string,colum2 string) row format delimited fields terminated by '\t';
创建一张外部表employee
0: jdbc:hive2:
0: jdbc:hive2:
+
| tab_name |
+
| employee |
| stu |
+
3 rows selected (0.241 seconds)
4.2 hive数据装载命令load
语法:
load data [local] inpath '/opt/export/data/data.txt' [overwrite] | into table data_table [partition (partcol1=val1,…)];
参数:
-
load data:表示加载数据 -
local:表示从本地加载数据到hive表;否则从HDFS加载数据到hive表 -
inpath:表示加载数据的路径 -
overwrite:表示覆盖表中已有数据,否则表示追加 -
into table:表示加载到哪张表 -
student:表示具体的表 -
partition:表示上传到指定分区
4.3 从hdfs文件系统加载数据
其实就是一个移动文件操作,提前将数据上传到hdfs文件系统。
创建数据文件employee.txt,并将employee上传到hdfs。
[hadoop@master data]$ pwd
/opt/export/data
[hadoop@master data]$ touch employee.txt
[hadoop@master data]$ vim employee.txt
10000 Stff
10001 Joe
10002 Ruby
10003 Betty
移动到hdfs
[hadoop@master data]$ hadoop fs -put employee.txt
[hadoop@master data]$ ll
total 8
-rw-rw-r-- 1 hadoop hadoop 44 Jul 20 14:28 employee.txt
-rw-rw-r-- 1 hadoop hadoop 72 Jul 16 16:02 test.input
装载employee数据
0: jdbc:hive2:
Loading data to table myhive.employee
...
INFO : OK
查看数据
0: jdbc:hive2://master:10000> select * from employee;
+--------------+----------------+
| employee.id | employee.name |
+--------------+----------------+
| 10000 | Stff |
| 10001 | Joe |
| 10002 | Ruby |
| 10003 | Betty |
+--------------+----------------+
4 rows selected (0.584 seconds)
如果删掉employee表,hdfs的数据仍然存在,并且重新创建表之后,表中就直接存在数据了,因为我们的employee表使用的是外部表,drop table之后,表当中的数据依然保留在hdfs上。
0: jdbc:hive2:
0: jdbc:hive2:
+
| tab_name |
+
| employee |
| stu |
+
2 rows selected (0.22 seconds)
重建employee表
0: jdbc:hive2:
查询employee数据,数据依然存在
0: jdbc:hive2:
+
| employee.id | employee.name |
+
| 10000 | Stff |
| 10001 | Joe |
| 10002 | Ruby |
| 10003 | Betty |
+
五、Hive分区表
5.1 Hive分区表概述
1、Hive官方网站对Hive partition的介绍:
可以使用Partitioned BY子句创建分区表。一个表可以有一个或多个分区列,并且为分区列中的每个不同的值组合创建一个单独的数据目录。此外,可以使用按列聚集的方式对表或分区进行存储,并且可以通过按列排序的方式在存储区内对数据进行排序。这可以提高某些查询的性能。
2、在大数据中,最常用的一种思想就是分治,分区表实际就是对应hdfs文件系统上的的独立的文件夹,该文件夹下是该分区所有数据文件。
3、分区可以理解为分类,通过分类把不同类型的数据放到不同的目录下。
4、分类的标准就是分区字段,可以一个,也可以多个。
5、分区表的意义在于优化查询。查询时尽量利用分区字段。如果不使用分区字段,就会全部扫描。
6、在查询是通过where子句查询来指定所需的分区。
7、在hive中,分区就是分文件夹
5.2 分区表语法
创建分区表语法:
create table stu(id string,name string) partitioned by (month string) row format delimited fields terminated by '\t';
修改分区表语法:
1、新增分区
ALTER TABLE table_name ADD [IF NOT EXISTS] PARTITION partition_spec [LOCATION 'location'][, PARTITION partition_spec [LOCATION 'location'], ...];
partition_spec:
: (partition_column = partition_col_value, partition_column = partition_col_value, ...)
2、重命名分区
ALTER TABLE table_name PARTITION partition_spec RENAME TO PARTITION partition_spec;
3、删除分区
ALTER TABLE table_name DROP [IF EXISTS] PARTITION partition_spec;
4、删除分区-回收站不保留,直接删除
ALTER TABLE table_name DROP [IF EXISTS] PARTITION partition_spec PURGE;
5、归档和还原归档分区表
ALTER TABLE table_name ARCHIVE PARTITION partition_spec;
ALTER TABLE table_name UNARCHIVE PARTITION partition_spec;
5.3 静态分区
静态分区中,可以根据partitioned by,一个表可以拥有一个或多个分区,每个分区以文件夹的形式单独存在表文件夹的目录下。
分区是以字段的形式在表结构中存在,通过describe table命令可以查看到字段存在,但是该字段不存放实际的数据内容,仅仅是分区的表示。
分区建表分为2种,一种是单分区,也就是说在表文件夹目录下只有一级文件夹目录。另外一种是多分区,表文件夹下出现多文件夹嵌套模式。
5.3.1 单分区
创建分区表
0: jdbc:hive2:
查看分区表
0: jdbc:hive2:
往分区表中录入数据
0: jdbc:hive2:
0: jdbc:hive2:
0: jdbc:hive2:
新增分区
0: jdbc:hive2:
重命名分区
0: jdbc:hive2:
查看分区的存储
可以看到一份分区一个文件夹,一个分区下可能有0个或多个文件
查看数据结果
0: jdbc:hive2:
INFO : Completed executing command(queryId=hadoop_20210720163707_a3998ec0-8ba4-4450-b25d-06a9d0af96ad); Time taken: 0.0 seconds
INFO : OK
INFO : Concurrency mode is disabled, not creating a lock manager
+
| salary.employee_id | salary.employee_nm | salary.salary | salary.data_dt |
+
| 10002 | Jhon | 100000 | 2020-07-20 |
| 10003 | Roy | 90000 | 2020-07-21 |
| 10001 | Eric | 150000 | 2021-07-19 |
+
5.3.2 多分区
创建分区表salary2
0: jdbc:hive2:
查看分区表salary2
0: jdbc:hive2:
插入数据
0: jdbc:hive2:
0: jdbc:hive2:
0: jdbc:hive2:
新增分区
0: jdbc:hive2:
查看分区的存储
重命名分区
0: jdbc:hive2:
可以看到多级分区目录为多级,每一个dept_id文件夹下面,每一个data_dt是一个目录。 多分区显然会产生比较多的文件,如果数据量不大的情况下,谨慎使用
查看分区表数据
0: jdbc:hive2:
5.4 动态分区
官方文档对静态分区和动态分区的简单解释:
静态分区(SP)列:在涉及多个分区列的DML/DDL中,这些列的值在编译时已知(由用户给出)。 动态分区 (DP)列:在执行时才知道其值的列。
上面使用静态分区的时候,可以看到操作分区表的时候一定要指定分区,动态分区就解决了这个问题。
5.4.1 开启动态分区
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
5.4.2 动态分区测试
创建表结构脚本:
[hadoop@master sql_script]$ pwd
/opt/export/sql_script
[hadoop@master sql_script]$ vim 01_dynamic_partition_salary.sql
添加以下内容
use myhive;
CREATE TABLE salary_dynamic_partition (
employee_id string,
employee_nm string,
salary int
)
PARTITIONED BY (dept_id string);
执行
[hadoop@master sql_script]$ hive -f 01_dynamic_partition_salary.sql
OK
Time taken: 7.765 seconds
查看表
0: jdbc:hive2:
OK
+
| tab_name |
+
| employee |
| salary_dynamic_partition |
| stu |
+
3 rows selected (1.898 seconds)
插入数据
0: jdbc:hive2:
可以看到根据dept_id自动创建了3个分区
5.5 静态分区和动态分区混合使用
静态分区和动态分区是可以混合使用的。
需要注意的是,动态分区不能在静态分区前面,在select 中位置顺序出现在最后。
创建可执行sql脚本:
use myhive;
create table static_mix(user_id int,prod_name string,apply_status string);
alter table static_mix SET TBLPROPERTIES('comment' = '动静态分区测试-1-静态分区表');
insert into static_mix values (1,'Prod1','申请中');
insert into static_mix values (2,'Prod2','审批中');
insert into static_mix values (3,'Prod3','完成');
insert into static_mix values (4,'Prod4','拒绝中');
create table mix_partition
(user_id int,
prod_name string
)
partitioned by (dt string,apply_status string);
ALTER TABLE mix_partition SET TBLPROPERTIES('comment' = '动静态分区表测试');
insert into mix_partition partition(dt='2020-12-29',apply_status)
select user_id,prod_name,apply_status
from static_mix;
~
执行脚本
[hadoop@master sql_script] hive -f mix_partition.sql
查看结果
5.6 恢复分区
语法:
MSCK [REPAIR] TABLE table_name [ADD/DROP/SYNC PARTITIONS];
msck repair table命令执行后Hive会检测如果HDFS目录下存在 但表的metastore中不存在的partition元信息,更新到metastore中。如果有一张表已经存放好几年了,用这个命令去执行的话 半天都反应不了,所以这个命令太暴力了,生产中不推荐使用。可以用Add partition来添加分区。
5.7 归档分区
语法:
ALTER TABLE table_name ARCHIVE PARTITION partition_spec;
ALTER TABLE table_name UNARCHIVE PARTITION partition_spec;
归档分区可以减轻 name node的压力,当然,数据量非常大、文件非常多(千万级别)的时候,再考虑做归档。
5.8 交换分区
语法:
ALTER TABLE <dest_table> EXCHANGE PARTITION (<[partial] partition spec>) WITH TABLE <src_table>
约束:
- 目标表不能包含要交换的分区。
- 如果有索引,操作将失败。
- 交换分区不允许将事务表作为源或目标。或者,使用LOAD DATA或INSERT OVERWRITE命令在事务性表之间移动分区。
- 此命令要求源表名和目标表名具有相同的表模式。
- 如果模式不同,抛出以下异常:
The tables have different schemas. Their partitions cannot be exchanged
参考
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-PartitionedTables
六、 分桶表
6.1 数据分桶简介
Hive是基于Hadoop的一个数据仓库,可将结构化的数据文件映射为一张数据库表,并提供类SQL查询功能。其实,Hive的本质是将HiveSQL语句转化成MapReduce任务执行。
Hive中,分区提供了一个隔离数据和优化查询的便利方式,不过并非所有的数据都可形成合理的分区,尤其是需要确定合适大小的分区划分方式(有的数据分区数据过大,有的很少,即我们常说的数据倾斜)。
我们可以将Hive中的分桶原理理解成MapReduce中的HashPartitioner的原理。都是基于hash值对数据进行分桶。
MR:按照key的hash值除以reduceTask个数进行取余(reduce_id = key.hashcode % reduce.num)。
Hive:按照分桶字段(列)的hash值除以分桶的个数进行取余(bucket_id = column.hashcode % bucket.num)。
6.2 数据分桶作用
进行抽样:
在处理大规模数据集时,在开发和修改查询的阶段,可以使用整个数据集的一部分进行抽样测试查询、修改。可以使得开发更高效。
map-side join:
获得更高的查询处理效率。桶为表加上了额外的结构,Hive 在处理有些查询时能利用这个结构。
具体而言,连接两个在(包含连接列的)相同列上划分了桶的表,可以使用 Map 端连接 (Map-side join)高效的实现。比如JOIN操作。对于JOIN操作两个表有一个相同的列,如果对这两个表都进行了桶操作。那么将保存相同列值的桶进行JOIN操作就可以,可以大大较少JOIN的数据量。
6.3 创建分桶表
开启hive的桶表功能
set hive.enforce.bucketing=true;
此开关打开之后,会自动根据bucket个数自动分配Reduce task的个数,Reduce个数与bucket个数一致。(此外,Reduce的个数还可以通过mapred.reduce.tasks进行设置,但是这方法不推荐在Hive分桶中使用)。
也可以直接设置reduce的个数
set mapreduce.job.reduces=3;
创建分桶表
桶表的数据加载,不能通过hdfs dfs -put文件或者load data,只能通过insert overwrite的方式将普通表的数据通过查询的方式加载到桶表当中去。
use myhive;
create table bucket(
id int,
name string,
age int
)
clustered by (age) into 4 buckets
row format delimited fields terminated by '\t';
create table bucket_com(
id int,
name string,
age int
)row format delimited fields terminated by '\t';
执行
[hadoop@master sql_script]$ hive -f bucket.sql
Logging initialized using configuration in file:/opt/hive/conf/hive-log4j2.properties Async: true
Hive Session ID = c68d555b-6160-4099-899f-aee6bca54b00
OK
Time taken: 1.609 seconds
OK
Time taken: 8.896 seconds
OK
Time taken: 2.39 seconds
查看表
0: jdbc:hive2:
OK
+
| tab_name |
+
| bucket |
| bucket_com |
| employee |
| mix_partition |
| salary_dynamic_partition |
| static_mix |
| stu |
+
7 rows selected (1.283 seconds)
0: jdbc:hive2:
创建数据源文件bucket_input.txt
1 Tom 19
2 Eric 20
3 Hive 21
4 Hadoop 66
5 Spark 77
将数据插入中间表bucket_com表中
0: jdbc:hive2:
Loading data to table myhive.bucket_com
OK
No rows affected (7.168 seconds)
0: jdbc:hive2:
OK
+
| bucket_com.id | bucket_com.name | bucket_com.age |
+
| 1 | Tom | 19 |
| 2 | Eric | 20 |
| 3 | Hive | 21 |
| 4 | Hadoop | 66 |
| 5 | Spark | 77 |
+
通过insert 给桶表中加载数据
0: jdbc:hive2:
HDFS Read: 19585 HDFS Write: 971859 SUCCESS
Total MapReduce CPU Time Spent: 11 seconds 820 msec
OK
No rows affected (598.574 seconds)
0: jdbc:hive2:
OK
+
| bucket.id | bucket.name | bucket.age |
+
| 2 | Eric | 20 |
| 2 | Eric | 20 |
| 3 | Hive | 21 |
| 5 | Spark | 77 |
| 5 | Spark | 77 |
| 3 | Hive | 21 |
| 4 | Hadoop | 66 |
| 4 | Hadoop | 66 |
| 1 | Tom | 19 |
| 1 | Tom | 19 |
+
10 rows selected (3.918 seconds)
0: jdbc:hive2:
查看分桶是否成功
|