[ 知识点 ] Hive基础知识笔记
Hive基础知识笔记
-
一、Hive内、外部表
-
- （一）Hive内、外部表的异同
- （二）创建内、外部表
-
二、Hive分区表
-
- （一）分区表的基本操作
- （二）多级分区
- （三）动态分区
- （四）Hive动态、静态分区的异同
-
三、Hive分桶表
-
- （一）了解
- （二）设置
- （三）操作
-
四、常用命令
-
- （一）操作数据库的一般流程
- （二）删除表的操作
- （三）修改表的操作
- （四）修改表中的列的操作
- （五）创建临时表
- （六）装载数据
-
五、Hive中with...as的用法
-
- （一）基础格式
- （二）同级格式
- （三）嵌套格式
- （四）只能在一条SQL语句中使用
-
六、CTAS
-
- （一）了解
- （二）使用CTAS创建表的好处
- （三）使用CTAS创建表时需要注意的地方
-
七、使用CTAS创建表的代码
-
- （一）直接使用CTAS创建表,不指定创建部分的约束
- （二）在使用CTAS创建表时，指定表的存储格式等属性
- （三）使用CTE (CTAS with Common Table Expression)创建创建表
- （四）使用CTAS创建视图
- （五）查看使用CTAS建表后的表结构
-
八、半自动化模式create table like...
-
- （一）了解
- （二）操作
-
九、Storage SerDe
前言：下文都是一些常见的命令及用法，没有过多生僻的、技巧性的东西。
一、Hive内、外部表
（一）Hive内、外部表的异同
1.内、外部表表结构都由hive控制
2.数据控制权不同
（1）内部表由hive控制。
（2）外部表不由hive控制（可以是hdfs、hbase）。
3.删除表产生的结果不同
（1）删除内部表结构和数据一起删除。
（2）删除外部表只能删除表结构。
（二）创建内、外部表
#创建一个内部表
create table if not exists student(
id int, name string
)
row format delimited fields terminated by '\t'
#创建一个外部表
create external table if not exists employee_external (
name string,
work_place ARRAY<string>,
sex_age STRUCT<sex:string,age:int>,
skills_score MAP<string,int>,
depart_title MAP<string,ARRAY<string>>
)
comment 'This is an external table'
row format delimited fields terminated by '|'
collection items terminated by ','
map keys terminated by ':'
stored as textfile
location '/home/hadoop/hive/warehouse/employee';
二、Hive分区表
（一）分区表的基本操作
分区表实际上就是对应一个 HDFS 文件系统上的独立的文件夹，该文件夹下
是该分区所有的数据文件。Hive 中的分区就是分目录，把一个大的数据集根据业
务需要分割成小的数据集。
1.创建分区表
create table A(
deptno int,
dname string,
loc string
)
partitioned by (month string)
row format delimited fields terminated by '\t';
2.加载数据到分区表中
load data local inpath 'PATH' into table A partition(month='201909');
3.查询分区表中数据
# 单分区查询
select * from A where month='201909';
# 多分区联合查询
select * from A where month='201909'
union all
select * from A where month='201908'
union all
select * from A where month='201907';
4.添加分区
# 创建单个分区
alter table A add partition(month='201906') ;
# 同时创建多个分区
alter table A add
partition(month='201905')
partition(month='201904');
5.删除分区
# 删除单个分区
alter table A drop
partition(month='201904');
# 同时删除多个分区
alter table A drop
partition(month='201905'),
partition(month='201906');
6.查看分区表有多少分区
show partitions A;
7.查看分区表结构
desc formatted A;
（二）多级分区
1.创建二级分区表（还可以创建多级分区）
create table A2 (
deptno int,
dname string,
loc string
)
partitioned by (month string, day string)
row format delimited fields terminated by '\t';
2.加载数据到二级分区表中
load data local inpath 'PATH' into table A2 partition(month='201909', day='13');
3.查询分区表数据
select * from A2 where month='201909' and day='13';
（三）动态分区
1.当使用静态分区时，在想分区表中插入数据时，我们要指定具体分区列的值，而在动态分区中插入数据时，只要指定分区字段，不用指定具体的分区列值。
2.动态分区在Hive 2.3.4版本前都是禁用的，所以要将hive.exec.dynamic.partition设置为true。
（四）Hive动态、静态分区的异同
1.建表方式一致
2.分区和模式不同
（1）静态分区表动态分区关闭且模式严格，通常默认是静态分区。
（2）动态分区表需要开启动态分区却设置模式非严格。
set hive.exec.dynamic.partition=true 默认为false
set hive.exec.dynamic.partition.mode=nonstrict 默认为strict
3.载入数据方式不同
（1）静态分区只能通过load方式一次一个分区装载数据。
alter table TABLE_NAME add [if not exists] partition(FIELD='VALUE'...) location 'PATH';
load data [local] inpath 'DATA_FILE PATH' [overwrite] into table TABLE_NAME partition(FIELD='VALUE'...)
（2）动态分区可以通过二级转换方式一次性添加多个分区数据。
create table TABLE_NAME ...
load ... into TEST_TABLE
insert into table PARTITION_TABLE partition(PARTITION_FIELDS ...) select ... from TEST_TABLE
三、Hive分桶表
（一）了解
1.分桶常作为一种优化的手段、一种补充技术。
2.可以将 Hive 中的分桶原理理解成 MapReduce 中的 HashPartitioner 的原理，都是基于 hash 值对数据进行分桶。
（二）设置
1.MR：按照key的hash值除以reduceTask个数进行取余(reduce_id =key.hashcode % reduce.num)。
2.Hive：按照分桶字段(列)的hash值，除以分桶的个数进行取余(bucket_id=column.hashcode % bucket.num)。
3.设置 Hive 的分桶开关
4.打开 Hive 客户端，并在会话终端开启 Hive 分桶的开关：
set hive.enforce.bucketing=true;
(此设置为临时设置，一旦退出会话终端，再打开就会恢复默认设置 false)
5.此开关打开之后，会自动根据 bucket个数自动分配Reduce task的个数，Reduce个数与bucket个数一致。( 此外， Reduce的个数还可以通过mapred.reduce.tasks进行设置，但是这方法不推荐在Hive分桶中使用)
（三）操作
1.建表
#建表语句
#注意分桶字段只能是建表中已有的字段
#而分区表的字段必须是建表中没有的字段
create table A(
id int,
name string,
age int
)
clustered by(age) into 4 buckets
row format delimited fields terminated by '|';
2.向分桶表中插入数据
insert into table A
select id,name,age from B;
3.查看分桶是否成功
如果分桶成功，因为我们建的是内部表，所以在Hive的数据仓库位置下找到A文件夹，在该文件夹下应该有4个文件（一个bucket代表着一个文件）。
四、常用命令
（一）操作数据库的一般流程
create database if not exists A; #创建名为A的数据库，加了if not exists，如果创建之前存在此数据库，则会报错。
use A; #使用数据库A
show databases; #显示所有数据库
describe database default; #可以查看数据库更多的描述信息
alter database A set owner user B; #将数据库A的所属权给用户B
drop database if exists A cascade; #如果数据库A里有表，在drop的时候命令末尾要加一个cascade
（二）删除表的操作
drop table if exists A[purge]; #purge表示直接删除，否则会放到Trash目录
truncate table A; #清空表数据
（三）修改表的操作
alter table A rename to B; #将表A重命名为B
alter table A tblproperties('comment'='New name,comments');
alter table A tblproperties('filed.delim'='$');
alter table A set fileformat rcfile; #修正文件格式
（四）修改表中的列的操作
alter table A change old_B new_C; #修改列名
alter table A add columns (B TYPE); #添加列
alter table A replace columns (B TYPE); #替换列
（五）创建临时表
create temporary table TABLE_NAME1;
create temporary table TABLE_NAME2 as ...;
create temporary table TABLE_NAME3 like ...;
（六）装载数据
load用于在Hive中移动数据。
#加local关键字，表示原始文件位于Linux本地，执行后为拷贝数据
#加上overwrite表示将A表中原有的数据删除，并将'data'中的数据写入A表中
load data local inpath 'PATH' into table A;
load data local inpath 'PATH' overwrite into table A;
load data loacl inpath 'PATH' overwrite into table A partition(B,C);
#没有local关键字，表示文件位于HDFS文件系统中，执行后为直接移动数据
#加上overwrite表示将A表中原有的数据删除，并将'data'中的数据写入A表中
load data inpath 'PATH' into table A partition(B,C);
load data inpath 'PATH' overwrite into table A partition(B,C);
五、Hive中with…as的用法
1.Hive可以通过with…as方法来提高查询性能，先通过with语法将数据查询结果存到with后的表的内存中，后续的sql均可以访问这个with结果,作用与视图或临时表类似。
2.当这个表中的数据会被经常用到的话，用with…as这个方法会比较方便。
（一）基础格式
with A as (select * from B)
select * from A;
（二）同级格式
同级的多个表之间用,分割with只需要一次,as后的子句必须用()。
with A as (selesc B from C),
B as (select D from E)
select * from (select XXX from A) a;
（三）嵌套格式
with A as(
with B as (
select * from C
)
select * fron B
)
select * from A;
#其间只在结尾处有一个英文封号。
（四）只能在一条SQL语句中使用
with A as(
select * from B
)
select * from A;
select XXX from A; #此处就会报错，一个封号就代表一个with语句结束
六、CTAS
（一）了解
CTAS : create table [view] …as select…。
（二）使用CTAS创建表的好处
1.CTAS语句中使用查询的结果，创建和填充表，由CTAS创建的表是原子的，这意味着在填充所有查询结果之前，其他用户不会看到此表。因此，其他用户要么看到带有完整查询结果的表，要么根本看不到表。
2.CTAS中，select选择部分可以支持HQL的任何选择语句，create创建部分从select选择部分中获取结果，并可以使用SerDe和存储格式等其他表述下创建指定的目标表。
（三）使用CTAS创建表时需要注意的地方
1.在Hive中使用CTAS创建表时，不管源表是否为分区表，所创建的表都是非分区表，所以在创建时要注意分区功能的丢失。不过创建表以后可以添加分区，成为分区表。
2.在Hive中使用CTAS创建表时，源表是分区表，则新建的表会多字段，具体多的字段个数和名称，就是源表分区的个数和名称。
3.如果源表的储存格式不是TXTFILE，则使用CTAS建表时，表的存储格式会默认为TEXTFILE。比如源表是RCFILE，而新表则为TEXTFILE。
4.CTAS不能创建partition, external, bucket table。
七、使用CTAS创建表的代码
（一）直接使用CTAS创建表,不指定创建部分的约束
create table TABLE_TEST as
select
A.1 as a1,
A.2 as a2,
A.3 as a3,
A.4 as a4
from B b
inner join C c
on b.x=c.y;
（二）在使用CTAS创建表时，指定表的存储格式等属性
create table A
row format delimited fileds terminated by '|'
store as textfile
as
select * from B;
（三）使用CTE (CTAS with Common Table Expression)创建创建表
create table A as
with B as (select XXX from C)
select * from B;
（四）使用CTAS创建视图
create view A as
with B as (select XXX from C)
select * from B;
select * from A;
（五）查看使用CTAS建表后的表结构
0: jdbc:hive2://192.168.59.130:10000> create table A1 as select * from visit;
0: jdbc:hive2://192.168.59.130:10000> show create table A1;
INFO : OK
+----------------------------------------------------+--+
|createtab_stmt|
+----------------------------------------------------+--+
|CREATE TABLE `A1`(|
|---|
|`shop` string)|
|ROW FORMAT SERDE|
|'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'|
|STORED AS INPUTFORMAT|
|'org.apache.hadoop.mapred.TextInputFormat'|
|OUTPUTFORMAT|
|'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'|
|LOCATION|
|'hdfs://192.168.59.130:9000/opt/software/hadoop/hive110/warehouse/a1'|
|TBLPROPERTIES (|
|'COLUMN_STATS_ACCURATE'='true',|
|'numFiles'='1',|
|'numRows'='0',|
|'rawDataSize'='0',|
|'totalSize'='0',|
|'transient_lastDdlTime'='1607834463')|
+----------------------------------------------------+--+
八、半自动化模式create table like…
（一）了解
1.create table 创建表的形似允许您精确地复制现有表的定义（不复制其数据），其除了创建的表名和源表不一样外，其余所有的细节都是医用的，不过没有源表中的数据。
2.create table like的形式非常适合对源表模式的复制。
（二）操作
#建表
0: jdbc:hive2://192.168.59.130:10000> create table A2 like select * from visit;
#查询表结构
0: jdbc:hive2://192.168.59.130:10000> show create table A2;
INFO : OK
+----------------------------------------------------+--+
|createtab_stmt|
+----------------------------------------------------+--+
|CREATE TABLE `A2`(||
|---|---|
|`shop` string)||
|ROW FORMAT SERDE||
|'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'||
|WITH SERDEPROPERTIES (||
|'field.delim'='|',|
|'serialization.format'='|')|
|STORED AS INPUTFORMAT||
|'org.apache.hadoop.mapred.TextInputFormat'||
|OUTPUTFORMAT||
|'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'||
|LOCATION||
|'hdfs://192.168.59.130:9000/opt/software/hadoop/hive110/warehouse/a2'||
|TBLPROPERTIES (||
|'transient_lastDdlTime'='1607834504')||
+----------------------------------------------------+--+
#查看源表结构
0: jdbc:hive2://192.168.59.130:10000> show create table visit;
INFO : OK
+----------------------------------------------------+--+
|createtab_stmt|
+----------------------------------------------------+--+
|CREATE EXTERNAL TABLE `visit`(||
|---|---|
|`shop` string)||
|ROW FORMAT SERDE||
|'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'||
|WITH SERDEPROPERTIES (||
|'field.delim'='|',|
|'serialization.format'='|')|
|STORED AS INPUTFORMAT||
|'org.apache.hadoop.mapred.TextInputFormat'||
|OUTPUTFORMAT||
|'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'||
|LOCATION||
|'hdfs://192.168.59.130:9000/opt/software/hadoop/hive110/warehouse/visit'||
|TBLPROPERTIES (||
|'COLUMN_STATS_ACCURATE'='true',||
|'numFiles'='1',||
|'totalSize'='95',||
|'transient_lastDdlTime'='1605252908')||
+----------------------------------------------------+--+
九、Storage SerDe
SerDe：Serializer and Deserializer，序列化与反序列化。
| Hive支持不同类型的Storage SerDe |
|---|
| LazySimpleSerDe: TEXTFILE |
| BinarySerializerDeserializer: SEQUENCEFILE |
| ColumnarSerDe: ORC, RCFILE |
| ParquetHiveSerDe: PARQUET |
| AvroSerDe: AVRO |
| OpenCSVSerDe: for CST/TSV |
| JSONSerDe |
| RegExSerDe |
| HBaseSerDe |