本文详细介绍了Hive的DDL（数据定义语言）和DML（数据管理语言）操作，包括创建和管理数据库、表以及分区，表的修改、数据的插入、删除等操作。内容涵盖库的创建、切换、删除，表的创建、修改、删除，以及数据加载、清空、导出等。此外，还讲解了内外部表、分区表、分桶表的特性及使用，以及数据查询中的排序和连接操作。
hive的操作
ddl操作：（data define language）
库的操作：
-
创建库：create database name；
-
切换库：use name；
-
查看库列表：show databases；
-
查看数据库的描述信息：desc database name；
-
查看正在使用的库：select current_database();
-
删除库：drop database name；（数据库中为空时才能删除）
drop database name restrict；严格模式下的删除库，会进行库的检查
drop database name cascade：级联删除数据库，会删除非空数据库
表的操作：
-
创建表：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]
[STORED AS file_format]
[LOCATION hdfs_path]
说明：
-
EXTERNAL ：加上这个关键字，建的表是外部表；不加上，建的表是内部表
-
IF NOT EXISTS：防止报错
-
COMMENT：指定列或表的描述信息
-
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
-
PARTITIONED BY（分区字段名 分区字段类型 COMMENT 字段描述信息）
注意：分区字段不能在建表字段中
-
[CLUSTERED BY (col_name, col_name, ...)：分桶字段
注意：分桶字段一定实在建表字段中
-
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
-
SORTED BY 指每一个桶表中的排序规则
-
INTO num_buckets BUCKETS 指定桶的个数
-
[ROW FORMAT row_format]：指定分隔符
-
fields terminated by 列分割符
-
line terminated by 行分割符
-
map keys terminated
-
STORED AS file_format 指定原始数据的存储格式
-
textfile 文本格式 默认的方式
-
rcfile 行列格式
-
SequenceFile 二进制格式
-
LOCATION 指定原始数据的存储位置
-
hive原始数据存储的配置
1）、hive-default.xml 2)、hive-site.xml 3)、建表语句 LOCATION
-
查看表的描述信息
desc tablename; 只能查看表的字段信息
desc extended tablename； 查看表的详细信息
desc formatted tablesname； 格式化显示表的详细信息
-
查看数据库信息
show tables； 查看当前数据库的表信息
show tables in dbname；查看指定数据库的表信息
show tables like 'stu*';
show partitions tablename;查询指定表下所有的分区信息
show partitions tablename partition();
show create table table_name; 查看详细建表语句
desc database extended db_name；查看数据库的详细信息
-
表的修改
表的重命名：alter table tablename rename to newname;
修改表的列：
1、添加列：alter table tablename add columns (name type);
2、修改列：alter table tablename change oldname newname type; 修改列名
alter table tablename change text text int; 修改列的类型
alter table tablename change grade grade string;
hive 2.0 版本中对类型转换做限制: 允许 小类型------》大类型
修改分区信息：
添加分区根据分区字段添加
-
手动添加分区：alter table tablename add partition (name=value);
-
一次添加多个分区：alter table tablename add partition(name=value) partition(name=value)...
修改分区的存储位置：
手动指定某一分区的存储路径
1）添加分区时指定
alter table tablename add partition(name=value) location '/user/data/1308';
2）、对于已经添加的分区修改存储位置添加数据时才会生效
alter table tablename partition(name=value) set location '/user/data'
5）.表数据的清空
truncate table tablename;
truncate table tablename partition(name=value);清空表的分区
6）删除表
drop table tablename;
案例：
1）、创建一个内部表
create table student
(grade int,stu_id int,name string,yuwen string,shuxue string,yingyu string) COMMENT 'student score'
row format delimited fields terminated by '\t'
lines terminated by '\n'
stored as textfile
location '/user/data/student';
2）、创建一个外部表
create external table student_external
(grade int,stu_id int,name string,yuwen string,shuxue string,yingyu string) COMMENT 'student score'
row format delimited fields terminated by '\t'
lines terminated by '\n'
stored as textfile
location '/user/data/student_external';
3）、创建一个分区表
选择一个分区字段：grade
create external table if not exists student_ptn
(stu_id int,name string,yuwen string,shuxue string,yingyu string)
COMMENT 'student score in partitions grade'
partitioned by (grade int)
row format delimited fields terminated by '\t'
location '/usr/data/student_ptn';
4）、创建一个分桶表
分桶字段：name 排序：yuwen shuxue yiingyu desc
create table if not exists student_buk
(grade int,stu_id int,name string,yuwen string,shuxue string,yingyu string)
clustered by (name) sorted by (yuwen desc,shuxue desc,yingyu desc)
into 3 buckets
row format delimited fields terminated by '\t'
location '/user/data/student_buk';
5）、进行表复制
关键字 like
只复制表的结构，表的属性（表的存储位置，表的属性）不会复制
create table if not exists stu_like like student;
DML操作：（data managed language）
表的数据插入
1）、load方式
load data [local] inpath 'path' into table tabename;
说明：
local：加上local关键字代表数据从本地导入，不加代表数据从hdfs导入
hive中的表就是hdfs一个目录的数据的管理者，只要在这个目录下添加数据，数据不管是否符合规则都会被hive加载到
数据从hdfs加载时
注意项：
1）数据移动还是复制：数据是移动的过程
2）同名的数据进行加载的时候，文件名相同不会冲突，会对重名的文件重命名
2）、insert方式：1、insert into table tablename values();
2、 insert into table tablename select * from tablename1；
3、from tablename
insert into table table1 select ... where...
insert into table table2 select....where...
注意：这种Hive的插入会转换为MR任务，执行效率低
将数据先插入到一个临时表中，再将这个临时表中的数据读取写出到hive表的管理空间中
分区表的数据插入：
1）、静态分区数据插入
静态：表的分区值手动指定
指在数据插入的时候需要手动指定分区
1.load方式
load data [local] inpath '' into table tablename partition(name=value);
注意：这种方式数据加载时，不会检查数据
用这种方式进行加载数据的时候必须确定数据是这个分区
2.insert的方式 可以添加过滤条件 从非分区表中获取数据插入到分区表中(该方式会检查数据)
1）、单重数据插入
insert into table student_ptn partition (grade=1304) select （不包含分区字段的字段） from student_external where grade=1304;
2）、多重数据插入
from student_external
insert into table student_ptn partition(grade=1305) select stu_id,name,yuwen,shuxue,yingyu where grade=1305
insert into table student_ptn partition(grade=1306) select stu_id,name,yuwen,shuxue,yingyu where grade=1306;
缺点：数据足够大，分区足够多的时候，分区值不确定时，静态分区过于麻烦
3）、动态分区数据插入
分区的值随着数据的插入动态生成的
数据插入方式只能使用insert的方式，不能使用load方式
动态分区中需要查询分区字段,将分区字段放到查询字段的最后
动态分区需要关闭严格模式
set hive.exec.dynamic.partition.mode=nonstrict
语法：
insert into table tablename partition(分区字段，不需要赋值)
select .... from tablename
案例：
insert into student_ptn partition(grade)
select stu_id,name,yuwen,shuxue,yingyu,grade from student_external;
动态分区和静态分区的区别
1）、静态分区分区手动指定
动态分区分区根据数据自动生成
3）、静态分区存在一个分区数据为空
动态分区的每个分区不存在数据为空的可能
4）、动态分区比较消耗性能
动态分区中如果设置reducetask的个数，针对的是每一个动态分区都有reducetask
分桶表数据插入：
1、load方式不支持load的方式加载数据时不会进行数据字段的检查，无法匹配、识别字段
2、insert into table student_buk select * from student_external;
运行日志
Number of reducers (= 3) is more than
自动按照分桶个数，启动reducetask个数
注意：
分桶算法：
分桶字段string默认分桶字段.hash%桶的个数
分桶字段为数值类型的时候 分桶字段%分桶个数
内部表和外部表的区别：
1)、本质上，内部表数据自己管理的，进行删除时，原始数据和元数据一起删除；外部表只是对hdfs的一个目录的数据进行关联， 仅仅有使用权，没有删除的权利，原始数据不会删除
外部表的数据如何彻底删除：
drop table tablename；
在删除hdfs上对应的数据存储目录：hadoop fs -rm -r -f path
2)、应用场景：外部表一般用于存储原始数据公共数据
内部表一般用于存储某一个模块的中间结果数据
3）、存储目录：
外部表一般在进行建表的时候需要手动指定表的数据目录为共享资源目录
内部表无严格要求，一般存储在默认目录
DML其它操作
数据导出：
1）、单模式导出 单重数据导出
insert overwrite [local] directory directory1 select * from student_buk where grade=1303
数据查询：
语法：
2)、join:内连接、外连接、半连接
a：id name
b: id age
内连接：select a.*,b.age from a join b on a.id=b.id;
外连接：
左外连接：以左表为主表，右表为辅表，右表有的会关联上，没有就会补null
select * from a left [outer] join b on a.id=b.id;
右外连接：以右表为主表，左表为辅表，左表有的会关联上，没有的就会补null
select * from a right [outer] join b on a.id=b.id;**
全外连接：左表和右表中的并集，右表和左表中的数据都会关联上
select * from a full outer join b on a.id=b.id;
半连接： 解决 in/exists 转换为MR任务的时候新能极低问题
左半连接： left semi join，判断左表中的关联键是否在右表中存在，存在则返回相关数据
select * from a left semi join b on a.id=b.id;
3）、hive中的排序
order by 排序字段 asc | desc
全局排序，针对所有的reducetask进行全局排序
select * from student_buk order by stu_id desc;
运行过程中：
一个reducetask的吞吐量 256M
In order to change the average load for a reducer (in bytes):
set hive.exec.reducers.bytes.per.reducer=<number>
设置全局的启动的所有的reducetask的个数
In order to limit the maximum number of reducers:
set hive.exec.reducers.max=<number>
设置reducetask的个数
In order to set a constant number of reducers:
set mapreduce.job.reduces=<number>
sort by
针对每一个reducetask的结果进行排序 不保证全局排序
进行数据分区的时候，每次随机选择的一个字段进行分区（随机字段.hash%reducetask个数）
distribute by
分桶对查询结果进行分桶
分桶个数=reducetask个数
select * from student_test distribute by age desc;
每个桶的数据分配依据：
string分桶字段.hash%reducetask个数
数值类型 分桶字段%reducetask个数
查询的时候，如果想指定某个字段分桶，对分桶数据排序
distribute by 分桶字段+sort by 字段
cluster by （默认升序）
当distribute by 和 sort by的字段相同时=cluster by
扫一扫
1276
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
