一、hive基础概念
1、Hive是什么
Hive 是建立在 Hadoop 上的数据仓库基础构架。它提供了一系列的工具,可以用来进行数据提取转化加载(ETL
Hive是SQL解析引擎,它将SQL语句转译成M/R Job然后在Hadoop执行。
Hive的表其实就是HDFS的目录,按表名把文件夹分开。如果是分区表,则分区值是子文件夹,可以直接在M/R Job里使用这些数据。
Hive相当于hadoop的客户端工具,部署时不一定放在集群管理节点中,可以放在某个节点上
2、hive与传统数据库比较
3、Hive的存储格式
Hive的数据存储基于Hadoop HDFS。
TEXTFILE SEQUENCEFILE AVRO RCFILE ORCFILE PARQUET
4、Hive操作客户端
二、hive基础语法(hive基础语法与MySQL差不多)
1、建库,删库;
create database 库名;
切换test1数据库:
use
删库
DROP DATABASE IF EXISTS ycc ;(删除空数据库)
DROP DATABASE IF EXISTS ycc CASCADE;(删除库之前清空库中表)
2、建表;
create [EXTERNAL] table students
(
id bigint,
name string,
age int comment,
gender string,
clazz string
)
PARTITIONED BY ( 非必选;创建分区表
dt string)
clustered by (userid) into 3000 buckets // 非必选;分桶子
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' // 必选;指定列之间的分隔符
STORED AS rcfile // 非必选;指定文件的读取格式,默认textfile格式
location '/testdata/'; //非必选;指定文件在hdfs上的存储路径,如果已经有文件,会自动加载 ,默认在hive的warehouse下
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]
// 指定Hive储存格式:textFile、rcFile、SequenceFile 默认为:textFile
[STORED AS file_format]
| STORED BY 'storage.handler.class.name' [ WITH SERDEPROPERTIES (...) ] (Note: only available starting with 0.6.0)
]
// 指定储存位置
[LOCATION hdfs_path]
// 跟外部表配合使用,比如:映射HBase表,然后可以使用HQL对hbase数据进行查询,当然速度比较慢
[TBLPROPERTIES (property_name=property_value, ...)] (Note: only available starting with 0.6.0)
[AS select_statement] (Note: this feature is only available starting with 0.5.0.)
A、全部使用默认格式
create table students
(
id bigint,
name string,
age int,
gender string,
clazz string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','; // 必选,指定列分隔符
指定location (这种方式也比较常用)
create table students2
(
id bigint,
name string,
age int,
gender string,
clazz string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION '/input1'; // 指定Hive表的数据的存储位置,一般在数据已经上传到HDFS,想要直接使用,会指定Location,通常Locaion会跟外部表一起使用,内部表一般使用默认的location
指定存储格式
create table students3
(
id bigint,
name string,
age int,
gender string,
clazz string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS rcfile; // 指定储存格式为rcfile,inputFormat:RCFileInputFormat,outputFormat:RCFileOutputFormat,如果不指定,默认为textfile,注意:除textfile以外,其他的存储格式的数据都不能直接加载,需要使用从表加载的方式。
D、create table xxxx as select_statement(SQL语句) (这种方式比较常用)
create table students4 as select * from students2;
E、create table xxxx like table_name 只想建表,不需要加载数据
create table students5 like students;
2.1、内部表:
1、内部表基础建表语句一:(默认指定文件类型为TextFile,HDFS路径为/user/hive/warehouse/库/下)
格式:
create table 表名
(
字段名1 字段类型1,
字段名2 字段类型2,
...
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '分隔符'; // 必选指定列之间的分隔符
2、内部表基础建表语句二:(HDFS路径为/user/hive/warehouse/库/下)
格式:
create table 表名
(
字段名1 字段类型1,
字段名2 字段类型2,
...
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '分隔符' // 必选指定列之间的分隔符
STORED AS file_format;
概述:
STORED AS:指定具体的从HDFS获取数据的格式,格式不匹配无法获取(默认为TextFile)
3、内部表基础建表语句三:(HDFS路径为/user/hive/warehouse/库/下)
格式:
create table 表名
(
字段名1 字段类型1,
字段名2 字段类型2,
...
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '分隔符' // 必选指定列之间的分隔符
STORED AS file_format
location 'HDFS路径';
概述:
location:表示指定hive数据在hdfs的路径, 如果路径不存在会自动创建,存在就直接绑定,不能通过hdfs路径判断是否是hive表
注意:
默认情况下 删除表会把数据也删除
2.2、外部表external
格式:
create external table 表名
(
字段名1 字段类型1,
字段名2 字段类型2,
...
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '分隔符' // 必选指定列之间的分隔符
STORED AS file_format
location 'HDFS路径';
注意:
删除外部表不会删除HDFS上的元数据
2.3、内部表和外部表的区别
hdfs上创建目录,上传数据文件
指定创建的路径建表
删除表以后元数据对比
删除表
drop table students_internal;
删除内部表的时候,表中的数据(HDFS上的文件)会被同表的元数据一起删除,删除外部表的时候,只会删除表的元数据,不会删除表中的数据(HDFS上的文件)
一般在公司中,使用外部表多一点,因为数据可以需要被多个程序使用,避免误删,通常外部表会结合location一起使用外部表还可以将其他数据源中的数据映射到 hive中,比如说:hbase,ElasticSearch......
设计外部表的初衷就是让表的元数据 与 数据 解耦
3、加载数据(上传到hive表,以文件的方式上传)
a、上传数据方式1:
格式:
hadoop dfs -put linux路径 hdfs路径
b、上传数据方式2:(直接在hive命令行操作)
格式:
dfs -put linux路径 hdfs路径
优点:比在hadoop操作hdfs快的多
c、上传数据方式3:(直接在hive命令行操作)
格式:
load data inpath '/HDFS路径' into 表名(自动找到hdfs的路径)
注意:对于hive来说是加载
对于HDFS来说是移动
d、上传数据方式4:(直接在hive命令行操作)
格式:
load data local inpath '/本地路径' into 表名(自动找到本地的路径)
注意:从本地上传数据
4、加载数据(表对表,将数据转换成与之对应的格式)
create table IF NOT EXISTS students_test1 as select * from students //这种加载创建表不能指定格式
insert [overwrite] into table students_test2 select * from students;
打开查看students表中内容不能直接查看明文
overwrite和into的区别
、
5、分区(以目录的形式对数据分割,避免全局扫描)
注意:分区字段和普通字段没有什么区别,所以不能和普通字段名重复
建表语句:
CREATE TABLE students_pt(id bigint,name string,age int comment,gender string,clazz string
) PARTITIONED BY (year STRING, month STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ',';
用法:
select * from students_pt where year=“2021” and month =“01”
未分区前
分区以后:
create external table studentsPation(
id bigint,
name string,
age int,
gender string,
clazz string
) PARTITIONED BY (dt string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
STORED AS rcfile;
添加分区:
alter table 表名 add partition(分区字段='值');
删除分区:
alter table 表名 drop partition(分区字段='值');
查看分区:
select DISTINCT 分区字段 from 表;//建议使用
show partitions 表;
插入数据:
load data local inpath '路径' into table 表名 partiton(分组字段='值');
分区不存在自动创建
增加分区:
向指定分区上传数据
6、动态分区
有的时候我们原始表中的数据里面包含了
> hive默认不开启动态分区
> 动态分区:根据数据中某几列的不同的取值 划分 不同的分区
# 表示开启动态分区
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=nostrict;
# 表示支持的最大的分区数量为1000,可以根据业务自己调整
hive> set hive.exec.max.dynamic.partitions.pernode=1000;
建立原始表并加载数据
create table students(
id bigint,
name string,
age int,
gender string,
clazz string,
dt string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
建立分区表并加载数据
create table students01
(
id bigint,
name string,
age int,
gender string,
clazz string
)
PARTITIONED BY(dt string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
使用动态分区插入数据
// 分区字段需要放在 select 的最后,如果有多个分区字段 同理,它是按位置匹配,不是按名字匹配
insert into table students01 partition(dt) select id,name,age,gender,clazz,dt from students_dt;(按时间分区)
// 比如下面这条语句会使用age作为分区字段,而不会使用student_dt中的dt作为分区字段
insert into table students01 partition(dt) select id,name,age,gender,dt,age from students_dt;(按年龄分区)
7、多级分区(分区最多三级分区)
案例:按年月进行的动态分区
create table students2(
id bigint,
name string,
age int,
gender string,
clazz string,
year string,
month string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
创建分区表:
create table students02(
id bigint,
name string,
age int,
gender string,
clazz string
) PARTITIONED BY (dt_year string,dt_month string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
8、分桶(动态的,直接操作数据)
Hive会自动根据bucket个数自动分配Reduce task的个数 Reduce个数与bucket个数一致
格式:
create external table 表名
(
字段名1 字段类型1,
字段名2 字段类型2,
...
)clustered by (分桶字段) into 分桶数量 buckets
ROW FORMAT DELIMITED FIELDS TERMINATED BY '分隔符' // 必选指定列之间的分隔符
注意:
分桶字段来源普通字段,分同数量是明确的
8.1、开启分桶开关
hive>set hive.enforce.bucketing=true;
8.2、创建分桶表
create table students03(
id bigint,
name string,
age int,
gender string,
clazz string
) clustered by (clazz) into 12 buckets
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
8.3、加载数据
load:不会触发分桶
insert into:自动触发分桶
分析:发现有的分桶后有的文件是空,有的文件clazz占了几个,这是为什么呢
因为分桶机制是按key机制去区分,这里以clazz为key作为区分,正常来说每个reduce处理一个key,但是处理机制对key值进行取值取余处理,可能两个班级的处理之后相同,就会进入到一个reduce中,那么就会有reduce为空
8.5、好处:
1、减少map端数据倾斜,资源平均处理,数据跑的快一点
2、方便数据抽样,在每个分桶抽取数据
不常用,因为对文件进行切分产生过多的小文件