day04-HiveSQL
一、映射表
1-1 数据的分类
根据数据的结构形式,可以将数据分为三类
- 结构化数据
- 表数据
- 表的元数据
- 字段名、字段类型、字段约束、表的名字等等
- 表的行数据
- 表中一行一行的数据内容
id (int) | name(string) | age(int) |
---|---|---|
1 | 张三 | 20 |
2 | 李四 | 22 |
3 | 王五 | 18 |
- 半结构化数据
- 没有数据类型的约束
xml
数据
<user>
<id> 1 </id>
<name> 张三 </name>
<age> 20 </age>
</user>
josn
数据
{
"id":1,
"name":"张三"
"age":20,
}
- 结构化数据
- 文本
- 图片
- 视频
- 音频
1-2 结构化数据
结构化数据本质就是一张表数据
- 两部分组成
- 表的元数据(字段名,字段类型,字段约束,表的名字等等)
- 表的行数据(表中实际存储的内容)
结构化数据支持SQL语法操作
SQL : Structure Query Language 结构化查询语言,SQL语法就是对结构化数据的操作
SQL通过
表的元数据
获取表的相关数据内容select 字段名 from 表名 select 字段名,sum(字段名) from 表名 group by 分组字段名
元数据:描述数据的数据
50 50 代表的含义是什么? 原本 50 是没有含义的,他只是冰冷冷的数字 但是如果他是 age 50 weight 50 50 便有了含义,含义的本身也是数据 age weight,他们叫做元数据
1-3 映射表
数仓中的数据是在
hdfs
中以文件的方式存储起来,属于非结构化数据
那如何使用 SQL 操作非结化数据?
Hive
可以将非结构化的文本数据转为结构化数据,也就是表数据
二、Hive架构
三、Hive服务启动及连接
3-1 服务启动
Hive
服务启动钱需要保证hadoop
服务全部启动
start-all.sh
Hive
在使用时,需要启动两个服务注意启动顺序
- Metastore 服务
- 管理
Hive
表的元数据,实现元数据的查询和保存
nohup hive --service metastore &
- hiveserver2 服务
- 提供了
JDBC
连接接口
nohup hive --service hiveserver2 &
查看服务启动情况
jps
hiveserver2 启动的时间会比较长,如何确认hiveserver2是否真正启动?
hiveserver2 使用的是10000
端口,可以查看10000
端口是否被占用,然后确认是否启动
lsof -i:10000
四、Hive的DDL语法
Hive
中支持https://cwiki.apache.org/confluence/display/Hive/LanguageManual
DDL语法(Data Definition Language - 数据定义语言)
- 主要是进行定义/改变表的结构、数据类型、表之间的链接等操作
- 库表的创建
DML语法(Data Manipulation Language - 数据操纵语言)
- 主要是对数据进行增加、删除、修改操作
- 对表的元数据的修改调整
- 增加字段
- 修改字段
- 删除字段
DQL语法(Data Query Language - 数据查询语言)
- 主要是对数据进行查询操作
- select的查询计算
DDL DML 名称 数据定义语言 数据操纵语言 区别 对数据库内部的对象进行创建、删除、修改操作 只是对表内部数据进行操作 (不涉及到表的定义、结构的修改,也不涉及到其他对象)
4-1 库的创建
- 格式
create database 数据库名
-- 数据库的创建 create database itcast; -- 创建数据库时进行判断,如果存在则不创建,不存在则创建 create database if not exists itheima;
4-2 库的切换
- 格式
-- 直接命令切换 use 数据名 -- DataGrip图像化界面操作
4-3 库的删除
- 格式
drop database 数据库名
-- 删除空数据库 drop database itheima; -- 强制删除,如果库下已经存在表,则不能直接删除,要使用cascade drop database itcast cascade; -- 判定是否已经被删除 drop database if exists itcast;
4-4 表的创建
- 格式
create table 表名( 字段名 字段类型 comment '字段描述', 字段名 字段类型, ... ) comment '表描述'
- 字段类型
- 整数数据 int || bigint
- 小数数据 float || decimal
- 字符串类型 string
- 日期类型 date
- 日期时间类型 timestamp
-- 表的创建 -- 如果已经切换了数据库,可以直接指定表名 create table tb_stu1( id int comment '用户id', name string comment '用户姓名', height float comment '用户身高', brithday date comment '生日' )comment '用户表'; -- 如果没有切换数据库,可以在指定数据库下创建表 create table itcast.tb_stu2( id int comment '用户id', name string comment '用户姓名', height float comment '用户身高', brithday date comment '生日' )comment '用户表'; -- 建表是判断表是否已经创建 create table if not exists itcast.tb_stu3( id int comment '用户id', name string comment '用户姓名', height float comment '用户身高', brithday date comment '生日' )comment '用户表';
4-5 表的数据写入
追加写入数据
- 格式
-- 写入数据 insert into 表名(字段) values(数据) -- 写入其它表数据 insert into 表名(字段) select 字段 from 表
-- 数据写入(写在一行也没关系,我这里分行了) insert into tb_stu1(id,name,height,brithday) values(1,'张三',1.75,'2002-10-10'); -- 如果对所有字段进行数据写入可以省略字段 insert into tb_stu1 values(2,'李四',1.75,'2002-10-11'); -- 如果需要将一张表的数据写入另一张表 -- 将tb_stu1表中的数据进行查询然后写入tb_stu2,注意查询的数据字段要和写入的字段保持一致 insert into tb_stu2 select * from tb_stu1; -- 查看数据 select * from tb_stu1; select * from tb_stu2;
覆盖写入
- 格式
insert overwrite table 表名(字段) values(数据) insert overwrite table 表名(字段) select 字段 from 表
-- 覆盖写入数据 insert overwrite table tb_stu1 values (3,'王五',1.75,'2002-10-11'); insert overwrite table tb_stu2 select * from tb_stu1; select * from tb_stu1; select * from tb_stu2;
数据文件上传数据表目录
hive
的表最终是将数据保存在hdfs
中默认的保存路径:
/user/hive/warehouse/数据库/数据表/数据文件
查询表的时候,会从
hdfs
的表目录中读取对应的数据文件,进而获取表的每一行数据所以,创建完表后,可以将一个已经存在的数据文件直接上传表目录中
如果使用一个已经存在的文件作为表的数据文件,则需要再建表时指定字段的分割符
1.有一个数据文件
1,张三,20,男 2,李四,22,男 3,王五,25,男
- 根据文件中的数据类型,创建对应表,并且指定分隔符
create table tb_stu4( id int, name string, age int, gender string )-- 指定字段分隔符 row format delimited fields terminated by ',';
- 将数据文件上传到表的目录下(此处为网页端操作
hdfs
)
- 查询数据
select * from tb_stu4;
4-6 表的删除
数据清空
- 只是清空行数据,表的元数据依旧存在
-- 清空表中的数据 truncate table tb_stu4; -- 再次查询检测是否清空 select * from tb_stu4;
删除表
- 删除表的所有数据
-- 删除表 drop table tb_stu4;
五、Hive表的分类
- 内部表
- 默认创建表时就是创建的内部表
drop
删除内部表时,会将内部表中所有数据删除- 外部表
- 创建外部表需要指定
external
关键字drop
删除外部表时,只删除表的元数据,hdfs
上的文件行数不删除- 一般用于
数据共享表
,比较安全- 外部表的创建格式
create external table 表名( 字段名 字段类型 comment '字段描述', 字段名 字段类型, ... ) comment '表描述'
-- 创建外部表 create external table tb_stu4( id int, name string, age int, gender string )-- 指定字段分隔符 row format delimited fields terminated by ','; -- 查询外部表 select * from tb_stu4;
- 建表后如何查看一张表的元数据?(怎么看是不是外部表)
--- 查看表信息 desc formatted tb_stu4;
内部表删除
外部表删除
六、Hive的中文乱码
在
hive
的表的元数据中如果出现中文,或将中文识别为乱码比如在
comment
中进行了中文描述
hive
表的元数据是在mysql
中存储,所以需要对mysql
数据进行修改调整
DataGrip
连接完mysql
后(此处省略了DataGrip连接mysql操作),执行以下配置命令use hive3; alter table COLUMNS_V2 modify column COMMENT varchar(256) haracter set utf8; alter table TABLE_PARAMS modify column PARAM_VALUE varchar(4000) haracter set utf8; alter table PARTITION_PARAMS modify column PARAM_VALUE archar(4000) character set utf8; alter table PARTITION_KEYS modify column PKEY_COMMENT archar(4000) character set utf8; alter table INDEX_PARAMS modify column PARAM_VALUE varchar(4000) haracter set utf8;
已经创建过的表,不会改变,新创建的表才会显示
七、Hive表的数据拆分
hive
表的数据量较大时,如果进行数据的查询,那查询的速度会比较慢进行
hive
数据存储时可以将数据拆分,把数据存储在不同的位置中,进而提升查询速度如果所有数据都在一个文件下存储,查询效率太低,所以需要进行数据的拆分
7-1 Hive的数据拆分
- 方法一:通过分区形式进行拆分(可以理解为分类)
- 将数据拆分到不同的目录(文件中)中
- 方法二:通过分桶形式进行拆分
- 将数据拆分到不同的文件中
- 要想实现两种方式,需要在建表时指定创建
分区表
或者创建为分桶表
7-2 分区表使用
- 创建一个原始数据表
create table tb_stu6 (
id int,
name string,
gender string,
age int,
cls string
)-- 指定分割符
row format delimited fields terminated by ',';
- 上传数据文件(网页端
HDFS
操作)
- 创建分区表
create table tb_stu_partition(
id int,
name string,
gender string,
age int,
cls string
)-- 指定分区字段 分区字段名不能和表中的字段名一样
partitioned by(dt string)
-- 按照,号分割存储数据
row format delimited fields terminated by ',';
- 分区数据写入
- 在写入分区数据时,需要指定一个分区的依据字段
-- 向分区表中写入数据,在写入数据时,会按照数据内容进行分区
-- 需要在 select 指定分区字段按照哪个字段的数据内容进行拆分,将需要拆分字段放在最后
-- 需要设置分区数据写入
set hive.exec.dynamic.partition.mode=nonstrict;
insert into tb_stu_partition partition(dt) select *,cls from tb_stu6;
- 查看拆分的数据目录
不能对中文字段的内容进行分区操作,不然会失败
如果一定要对中文字段进行分区,可以将中文数据进行转化
男 – 1 女 – 2
7-3 分桶表使用
- 创建分桶表
create table tb_stu_bucket(
id int,
name string,
gender string,
age int,
cls string
)-- 指定分桶字段,分桶字段必须是表中存在的字段
clustered by(cls) into 3 buckets -- into 3 buckets 表示将数据拆分成几个文件
-- 按照,号分割存储数据
row format delimited fields terminated by ',';
- 写入数据
insert into tb_stu_bucket select * from tb_stu6;
- 查看分桶数据文件
7-4 分桶原理
对分桶的字段进行hash取余计算
余数相同的数据会放在一起
- 算法
hash(分桶字段) % 分桶数 = 余数
IS 和 CS 的余数一样,所以被放入同一个文件
MA 的余数与其他两者不同,所以放入其它文件中
八、补充比较运算符
操作符 | 支持的数据类型 | 描述 |
---|---|---|
A = B | 基本数据类型 | 如果A等于B,则返回TRUE;否则FALSE |
A<>b,A!=B | 基本数据类型 | A 或者 B为NULL,则返回NULL;如果A不等于B,则返回TRUE,否则FALSE |
A < B | 基本数据类型 | A 或者 B为NULL,则返回NULL;如果A小于B,则返回TRUE,否则FALSE |
A <= B | 基本数据类型 | A 或者 B为NULL,则返回NULL;如果A小于等于B,则返回TRUE,否则FALSE |
A > B | 基本数据类型 | A 或者 B为NULL,则返回NULL;如果A大于B,则返回TRUE,否则FALSE |
A >= B | 基本数据类型 | A 或者 B为NULL,则返回NULL;如果A大于等于B,则返回TRUE,否则FALSE |
A [NOT] BETWEEN B AND C | 基本数据类型 | 如果任意A,B,C为NULL,则返回NULL;如果A的值大于等于B且小于或等于C,则返回TRUE,否则FALSE。(如果使用NOT关键字则可达到相反的效果) |